Board logo

標題: 尋找欄位區間最大值的對應欄位名稱 [打印本頁]
作者: clio    時間: 2019-3-18 19:17     標題: 尋找欄位區間最大值的對應欄位名稱

各位大大
小弟有一個問題想請教，如下圖A-E欄內對應每一個Row當作一個群組，比對這一個群組內數值最大者，然後於F欄秀出，對應的標頭欄位名稱，這個想了很久，還是找不出一個比較好的方法，特別來請教各位耶輩，還望幫忙，感謝您。
[attach]30238[/attach]
[attach]30239[/attach]
作者: ML089    時間: 2019-3-18 22:46

=LOOKUP(,0/(MAX(A2:E2)=A2:E2),A$1:E$1)
作者: p212    時間: 2019-3-19 08:58

回復 1# clio

=INDEX($A$1:$E$1,MATCH(MAX(A2:E2),A2:E2,))
作者: clio    時間: 2019-3-19 13:49

回復 2# ML089

感謝ML089大大…這個就是我要的解答…萬分感謝您
作者: clio    時間: 2019-3-19 15:04

回復 2# ML089

Hi ML089
小弟我想再請問一下用Lookup是找到最後一個符合的位置，那我試著用Index 想找到第一筆，但是我的式子是INDEX(A$1:E$1,,MATCH(,0/(A2:E2=MAX(A2:E2)),))，我點涵數進去看有看到結果，但是實際上的呈現確是#VALUE!，能否為小弟我解惑一下呢？感謝您。
作者: clio    時間: 2019-3-20 09:03

回復 3# p212

感謝p212大大
妳的方法也是可行的，太棒了，這個方法，可以找到第一筆符合的資料，太感謝您了，小弟也有用這個方法寫，但是跑出來有問題，『INDEX(A$1:E$1,,MATCH(,0/(A2:E2=MAX(A2:E2)),))』不知道p212是否知道小弟錯誤的地方，可否予以指正呢？感謝您
作者: ML089    時間: 2019-3-20 09:13

回復 5# clio

=INDEX(A$1:E$1,,MATCH(,0/(A2:E2=MAX(A2:E2)),))
你的公式沒有錯誤，但需要用陣列公式輸入才能運行

陣列公式輸入步驟
選擇儲存格 - 將公式鍵入(或貼上)工具列編輯欄 - 注意!游標仍需停留於編輯欄 - 先按CTRL及SHIFT不放 - 再按ENTER三鍵方式輸入公式，成功的話原先公式外會有 {....} 大跨弧

若只有單列或單欄，公式可以改為下列方式。(很微小差異)
=INDEX(A$1:E$1,MATCH(,0/(A2:E2=MAX(A2:E2)),))

註：VLOOKUP、MATCH、LOOKUP都能忽略計算錯誤組數，利用這些特性公式設計時故意讓不要的產生 "計算錯誤"，就能快速找到我們要的值。
例如：0/(A2:E2=MAX(A2:E2)) 為何要去除0，0/TRUE = 0；0/FALSE = #DIV/0! (錯誤值)，
{#DIV/0!,#DIV/0!,#DIV/0!,#DIV/0!,0} 第一組陣列只有一個0 其它為#DIV/0!，MATCH用0去找就很明確找到最後一個。
作者: clio    時間: 2019-3-21 09:20

回復 7# ML089

感謝ML089大大的回覆…太感謝您了
作者: clio    時間: 2019-3-21 09:30

回復 7# ML089

感謝ML089的解說，我可以再請教一個問題嗎？
我要怎麼判斷何時要用陣列的公式輸入呢？因為像【LOOKUP(,0/(MAX(A2:E2)=A2:E2),A$1:E$1)】這個好像就不用，而【INDEX(A$1:E$1,MATCH(,0/(A2:E2=MAX(A2:E2)),))】這個就需要，我大約知道【0/(A2:E2=MAX(A2:E2)】這個就是陣列的運用，是以這個在公式內的第一層或是第二層來作判斷嗎？
還要麻煩ML089再解說一下，感謝您。
作者: clio    時間: 2019-3-21 10:07

回復 7# ML089

Hi ML089
不好意思，由於我研究一下又發現一些問題，再麻煩您幫我解答一下，感謝您。
一、由【LOOKUP(,0/(MAX(A2:E2)=A2:E2),A$1:E$1)】與【INDEX($A$1:$E$1,MATCH(MAX(A2:E2),A2:E2,))】，讓我想到把Match代入Lookup內，所以我寫了【LOOKUP(MAX(A2:E2),A2:E2,A$1:E$1)】，但是這個出來的結果不一定都是正確的，我又細讀了一下參數要求，發現array需要是遞增排序的，所以我無法用Lookup搭配Match的方法來取得，正確的答案，只能用【LOOKUP(,0/(MAX(A2:E2)=A2:E2),A$1:E$1)】 的方法對嗎？
二、我也在網路上看到一個有關於Lookup的說明是【LOOKUP(1,0/(MAX(A2:E2)=A2:E2),A$1:E$1)】，第一個參數1或是大於0的任何數字，是查到最後一個符合的資料，要是為0則是查到符合的資料，但是不一定是最後一個符合的，這樣說法是正確的嗎？那在公式中【LOOKUP(,0/(MAX(A2:E2)=A2:E2),A$1:E$1)】，沒有輸入指定值，是指大於0，還是0呢？
作者: ML089    時間: 2019-3-21 11:40

回復 10# clio
一、由【LOOKUP(,0/(MAX(A2:E2)=A2:E2),A$1:E$1)】與【INDEX($A$1:$E$1,MATCH(MAX(A2:E2),A2:E2,))】，讓我想到把Match代入Lookup內，所以我寫了【LOOKUP(MAX(A2:E2),A2:E2,A$1:E$1)】，但是這個出來的結果不一定都是正確的，我又細讀了一下參數要求，發現array需要是遞增排序的，所以我無法用Lookup搭配Match的方法來取得，正確的答案，只能用【LOOKUP(,0/(MAX(A2:E2)=A2:E2),A$1:E$1)】 的方法對嗎？

獲得答案的方法很多種思路


二、我也在網路上看到一個有關於Lookup的說明是【LOOKUP(1,0/(MAX(A2:E2)=A2:E2),A$1:E$1)】，第一個參數1或是大於0的任何數字，是查到最後一個符合的資料，要是為0則是查到符合的資料，但是不一定是最後一個符合的，這樣說法是正確的嗎？那在公式中【LOOKUP(,0/(MAX(A2:E2)=A2:E2),A$1:E$1)】，沒有輸入指定值，是指大於0，還是0呢？


    LOOKUP(,0/(MAX(A2:E2)=A2:E2),A$1:E$1) 適用於陣列裡只有1個0情況，若陣列理有多個0時使用 LOOKUP(9,0/(MAX(A2:E2)=A2:E2),A$1:E$1) 找到最後一個0
作者: ML089    時間: 2019-3-21 11:47

回復 9# clio


    感謝ML089的解說，我可以再請教一個問題嗎？
我要怎麼判斷何時要用陣列的公式輸入呢？因為像【LOOKUP(,0/(MAX(A2:E2)=A2:E2),A$1:E$1)】這個好像就不用，而【INDEX(A$1:E$1,MATCH(,0/(A2:E2=MAX(A2:E2)),))】這個就需要，我大約知道【0/(A2:E2=MAX(A2:E2)】這個就是陣列的運用，是以這個在公式內的第一層或是第二層來作判斷嗎？
還要麻煩ML089再解說一下，感謝您。

大部分需要陣列處理的公式需要陣列公式輸入，
陣列處理中有  IF、IFERROR 一定要使用陣列公式輸入
除 LOOKUP、MMULT、SUMPRODUCT 可以處理陣列外，其它公式大部分需要陣列公式

剛開始需要試試看一般公式輸入及陣列公式輸入兩種，久了就可以判斷，有時我也需要試一下才能確定
作者: clio    時間: 2019-3-21 13:25

回復 12# ML089

感謝ML089大大的詳細解說，我再多研究看看，這個還是需要累績經驗值的…感謝您。



歡迎光臨 麻辣家族討論版版 (http://forum.twbts.com/)