贊助廠商

///本部落格所有文章列表///

搜尋本部落格文章資料

網友提問:在 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)式傳回的列號來求得對應的儲存格內容。

文章標籤
創作者介紹

學不完.教不停.用不盡

vincent 發表在 痞客邦 PIXNET 留言(2) 人氣()


留言列表 (2)

發表留言
  • 周侊懋
  • 您好,想請教您一個問題
    B2:F6=I2
    為什麼它會變成一個數值呢?
    我該怎麼理解它呢?
  • B2:F6=I2是一個條件,結果會傳回TRUE/FALSE的陣列,若再執行「*ROW(A2:A6)」,其『*』相當執行邏輯AND運算,而TRUE/FALSE的陣列在運算過程中會自動轉換為1/0陣列。

    vincent 於 2017/08/05 10:35 回覆

  • 周侊懋
  • 謝謝您的回答。
  • 不客氣喔!

    vincent 於 2017/08/07 20:05 回覆

您尚未登入,將以訪客身份留言。亦可以上方服務帳號登入留言

請輸入暱稱 ( 最多顯示 6 個中文字元 )

請輸入標題 ( 最多顯示 9 個中文字元 )

請輸入內容 ( 最多 140 個中文字元 )

請輸入左方認證碼:

看不懂,換張圖

請輸入驗證碼