贊助廠商

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

搜尋本部落格文章資料

網友問到:在 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 會自動輸入「{}」。

創作者介紹

學不完.教不停.用不盡

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


留言列表 (6)

發表留言
  • 訪客
  • 謝謝大大耐心指導,謝謝!
  • 小事一件!

    vincent 於 2016/11/15 20:48 回覆

  • 訪客
  • (1) SUMPRODUCT((J1=B2:G11)*ROW(B2:G11))
    對不起,這個部份不太理解。
    計算乘積和的SUMPRODUCT函數,用在這裏可以導出所需列號,其中的運算過程是怎樣的呢?

    又,如果想從表中找出多個數字的位置,例如數值最高的頭十名,可是其中有數字重複的情況,例如(10,9,8,8,8,5,4,3,2,1)中第三至第五名均是8,導致表中查詢數值8的時候會有三個結果,這時上述函式好像會失效。請問這情況該如何解決呢?
  • 建議你使用『評估值公式』來看運算過程,即可理解。
    這個公式是適用於內容不重覆的資料。

    vincent 於 2016/11/15 19:56 回覆

  • 訪客
  • 請問當表格中數值出現重複時,該如何解決?

    例如說在乘數表中,想要從「24」這個數值,分別求得「3,8」「4,6」「6,4」「8,3」共四組的欄、列標題,有辨法嗎?
  • 訪客
  • 自行找到解決辨法了 ^_<

    以IF、SMALL、COUNTIF由陣列公式求出列號,即可解決表格內容重覆問題。
  • 恭喜你,這些都是很難的問題,能想通,實在很不簡單!

    vincent 於 2017/01/31 20:38 回覆

  • 訪客
  • 又發現以SMALL在陣列中篩選數字會造成欄號/列號數字因為經過排序而跑位的問題,想用CHOOSE來篩選數字,CHOOSE又不會忽略文字,而且選項也不能用陣列。SUMPRODUCT、SUM、SUMIF等等可以在陣列中忽略文字篩選出數字的函式,全都會把數字篩選出來後再作多餘的加工……

    好吧我知道其實那個加工才是那些函式的本來目的,但是我的表情已經囧了。
    Orz

    多番思考之下,發現完全找不到一個可以在比如{5,2,FALSE,4,FALSE}這類陣列中篩選數字,卻不再作加工,只由出現順序找出目標的函式。不是做不到而是總會做多了,這情況也太無言和無奈了吧。
    ┐(°⊿°)┌

    其實我就只是想要在一個有幾萬格的資料表中,找出符合我想要的數值的那幾個格子在哪裏而已,怎麼就這麼難!
    (┛`д´)┛ミ┻┻

    束手無策,求問老師有沒有辦法解決這種問題呢?
找更多相關文章與討論

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

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

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

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

請輸入左方認證碼:

看不懂,換張圖

請輸入驗證碼