2016年11月13日 星期日

Excel-由資料陣列中反推對應的列標題和欄標題(OFFSET,SUMPRODUCT)

網友問到:在 Excel 中有一個資料表(參考下圖),如何由數值內容反推欄/列的標題?
例如:在儲存格J2中指定一個數值,要找出其人員為:『戊』,月份為:『三月』。.
【公式設計與解析】
1. 使用 SUMPRODUCT 函數
Excel-由資料陣列中反推對應的列標題和欄標題(OFFSET,SUMPRODUCT)
找出列標題:
儲存格J2:=OFFSET(A1,SUMPRODUCT((J1=B2:G11)*ROW(B2:G11))-1,0)
(1) SUMPRODUCT((J1=B2:G11)*ROW(B2:G11))
利用條件:J1=B2:G11,將其乘以ROW(B2:G11),可以傳回符合條件的列號。
(2) OFFSET(A1,SUMPRODUCT((J1=B2:G11)*ROW(B2:G11))-1,0)
將第(1)傳回的列號代入 OFFSET 函數,即可找出在A欄中對應的標題名稱。
找出欄標題:
儲存格J3:=OFFSET(A1,0,SUMPRODUCT((J1=B2:G11)*COLUMN(B2:G11))-1)
(1) SUMPRODUCT((J1=B2:G11)*COLUMN(B2:G11))
利用條件:J1=B2:G11,將其乘以COLUMN(B2:G11),可以傳回符合條件的欄號。
(2) OFFSET(A1,0,SUMPRODUCT((J1=B2:G11)*COLUMN(B2:G11))-1)
將第(1)傳回的列號代入 OFFSET 函數,即可找出在1列中對應的標題名稱。

2. 使用 SUM 函數+陣列公式
Excel-由資料陣列中反推對應的列標題和欄標題(OFFSET,SUMPRODUCT)
如果你想使用陣列公式,可以試試以下的公式:
儲存格J2:{=OFFSET(A1,SUM((J1=B2:G11)*ROW(B2:G11))-1,0)}
儲存格J3:{=OFFSET(A1,0,SUM((J1=B2:G11)*COLUMN(B2:G11))-1)}
陣列公式輸入完成要按 Ctrl+Shift+Enter 鍵,Excel 會自動輸入「{}」。

沒有留言:

張貼留言

檢視其他文章

好康東東