2017年8月3日 星期四

Excel-由欄(列)標題和內容反求列(欄)標題(OFFSET,INDIRECT,SUMPRODUCT)

網友提問:在 Excel 中,如何透過欄(列)標題和內容來反求列(欄)標題?
【經由列標題和內容求得欄標題】
本例:透過列標題『丁』和內容『R』,要求得欄標題『寅』。
Excel-由欄(列)標題和內容反求列(欄)標題(OFFSET,INDIRECT,SUMPRODUCT)
1. 定義儲存格名稱
選取儲存格A2:F6,按 Ctrl+Shift+F3 鍵,勾選「最左欄」,定義名稱:甲、乙、丙、丁、戊。
2. 選取儲存格I2,設定「資料驗證」。設定準則:
儲存格內允許:清單。來源:=INDIRECT(H2)。
Excel-由欄(列)標題和內容反求列(欄)標題(OFFSET,INDIRECT,SUMPRODUCT)
結果如下:(當儲存格H2改變時,儲存格I2可以選取的清單也隨之變動。)
Excel-由欄(列)標題和內容反求列(欄)標題(OFFSET,INDIRECT,SUMPRODUCT)
3. 設定公式。
儲存格H3:=OFFSET(A1,0,SUMPRODUCT((B2:F6=I2)*COLUMN(B2:F6))-1)
(1) SUMPRODUCT((B2:F6=I2)*COLUMN(B2:F6))
利用 SUMPRODUCT 函數求得儲存格I2的欄號。
(2) OFFSET(A1,0,第(1)式-1)
在 OFFSET 函數中根據第(1)式傳回的列號來求得對應的儲存格內容。

【經由欄標題和內容求得列標題】
本例:透過欄標題『卯』和內容『S』,要求得列標題『丁』。
Excel-由欄(列)標題和內容反求列(欄)標題(OFFSET,INDIRECT,SUMPRODUCT)
1. 定義儲存格名稱
選取儲存格B1:F6,按 Ctrl+Shift+F3 鍵,勾選「頂端列」,定義名稱:子、丑、寅、卯、辰。
2. 選取儲存格I2,設定「資料驗證」。設定準則:
儲存格內允許:清單。來源:=INDIRECT(H2)。
Excel-由欄(列)標題和內容反求列(欄)標題(OFFSET,INDIRECT,SUMPRODUCT)
結果如下:(當儲存格H2改變時,儲存格I2可以選取的清單也隨之變動。)
Excel-由欄(列)標題和內容反求列(欄)標題(OFFSET,INDIRECT,SUMPRODUCT)
3. 設定公式。
儲存格H3:=OFFSET(A1,SUMPRODUCT((B2:F6=I2)*ROW(A2:A6))-1,0)
(1) SUMPRODUCT((B2:F6=I2)*ROW(A2:A6)).
利用 SUMPRODUCT 函數求得儲存格I2的欄號。
(2) OFFSET(A1,第(1)式-1,0)
在 OFFSET 函數中根據第(1)式傳回的列號來求得對應的儲存格內容。

沒有留言:

張貼留言

檢視其他文章

好康東東