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」儲存格,結果如下圖所示。
如果需要轉換後的欄數為6欄,可以修改公式為:「=INDIRECT("a"&6*ROW(A1)-5+COLUMN (A1))&""」。
思路解析:
5*ROW(A1)-4+COLUMN(A1) 這部分的計算結果為2。
公式向下複製填充時,ROW(A1)依次變為 ROW(A2)、ROW(A3)……計算結果分別為7、12……
即生成公差為5的自然數序列。
公式向右複製填充時,COLUMN(A1)依次變為COLUMN(B1)、COLUMN(C1)……計算結果分別為3、4……
即生成公差為1的自然數序列。
與字元"A"連接成一個儲存格位址「An」,最後用INDIRECT函數傳回文字字串所指定的參照,得到相應儲存格的內容。
公式向右、向下複製公式,完成多列5欄的轉換。
公式最後使用&""的目的是為了遮罩在行列轉換時,資料不足一行出現的0值。
當欄列轉換完成,再添加欄位標題,設置列印範圍就可以了。
要如何設定列印範圍:
步驟1:框選D1~H29範圍。
步驟2:點取「版面配置 > 版面設定 > 列印範圍 > 設定列印範圍」指令,如下圖所示。
留言列表