2014年12月25日 星期四

Excel-表格欄、列、資料的重組(MATCH,OFFSET)

在 Excel 中的資料表是由欄位名稱和記錄所構成,所以包含了三種元素:欄、列、資料。有時需要將一個表格的三個元素重新排列組合,參考以下二個例子。

【範例一】

根據上圖,原來的資料為「A、B、C」為主,要如何調整為「早、中、夜」?

儲存格G2:=IFERROR(OFFSET($B$1,,MATCH(G$1,$B2:$D2,0)-1),"")

MATCH(G$1,$B2:$D2,0):找出儲存格G1的內容在儲存格B2:D2中的位置。

OFFSET($B$1,,MATCH(G$1,$B2:$D2,0)-1):將上式傳回值(1、2、3)代入 OFFSET 函數查詢到對應的「早、中、夜」。

最後利用 OFFSET 函數將上式因查詢不到資料所傳回的錯誤訊息,改以空白顯示。

複製儲存格G2,貼至儲存格G2:L8。

 

【範例二】

第二個範例比較複雜,主要是將範例一的垂直和水平予以交換。

儲存格G2:=IFERROR(OFFSET($A$1,,MATCH($F2,INDIRECT("B"&COLUMN(B:B)
&":D"&COLUMN(B:B)),0)),"")

使用的解法和範例一完全相同,只是因為欄列互換,所以使用 INDIRECT 函數來轉換位址。主要的改變在於將:

MATCH(G$1,$B2:$D2,0)

改寫成:

MATCH($F2,INDIRECT("B"&COLUMN(B:B)&":D"&COLUMN(B:B))

其中:INDIRECT("B"&COLUMN(B:B)&":D"&COLUMN(B:B),因為 COLUMN(B:B) 向右複製時 COLUMN(B:B)=2→COLUMN(C:C)=3→COLUMN(D:D)=4、…,所以會傳回:B2:D2位址→B3:D3位址→B4:D4位址。

其餘請參考範例一的說明。

沒有留言:

張貼留言

檢視其他文章

好康東東