close

386

Excel函數實現行列轉換的技巧

示範檔

範例檔

386.XLSX

結果檔

今天在本單元中帶給大家的是一個用Excel函數實現欄列轉換的技巧。

如下圖所示,是一份某班級學生姓名表:

這個表中的姓名只有一欄,卻有138列。如果以這樣的版式列印,不僅浪費紙張,列印出的效果估計也能讓主管頭痛的,要如何教你。

因此在列印之前,需要將A欄姓名轉換為適合列印的5欄多列。

以前很多人可能學習過借助WORD實現一欄變多欄的技巧,今天再給大家說說用函數公式如何來實現這樣的效果。

步驟1:D2儲存格輸入公式:「=INDIRECT("a"&5*ROW(A1)-4+COLUMN(A1))&""」後,再按Enter鍵完成公式。

步驟2:將滑鼠指標移到「D2」儲存格右下角「拖拉方塊」上方,待指標變為「+」後,向下拖曳到「D29」儲存格。

步驟3:將滑鼠指標移到「D29」儲存格右下角「拖拉方塊」上方,待指標變為「+」後,向下拖曳到「H29」儲存格,結果如下圖所示。

image

如果需要轉換後的欄數為6欄,可以修改公式為:「=INDIRECT("a"&6*ROW(A1)-5+COLUMN (A1))&""」。

思路解析:

5*ROW(A1)-4+COLUMN(A1) 這部分的計算結果為2

公式向下複製填充時,ROW(A1)依次變為 ROW(A2)ROW(A3)……計算結果分別為712……

即生成公差為5的自然數序列。

公式向右複製填充時,COLUMN(A1)依次變為COLUMN(B1)COLUMN(C1)……計算結果分別為34……

即生成公差為1的自然數序列。

與字元"A"連接成一個儲存格位址「An」,最後用INDIRECT函數傳回文字字串所指定的參照,得到相應儲存格的內容。

公式向右、向下複製公式,完成多列5欄的轉換。

公式最後使用&""的目的是為了遮罩在行列轉換時,資料不足一行出現的0值。

當欄列轉換完成,再添加欄位標題,設置列印範圍就可以了。

要如何設定列印範圍:

步驟1框選D1~H29範圍。

步驟2點取「版面配置 > 版面設定 > 列印範圍 > 設定列印範圍」指令,如下圖所示。

image

 

 


arrow
arrow
    創作者介紹

    錦子老師 發表在 痞客邦 留言(0) 人氣()