iT邦幫忙

0

excel 問題求解

wsj 2020-03-14 21:51:581124 瀏覽

請問各位大大:
如下圖所示,A1~Q1為ㄧ數列,請各位協助如何求出次大值D3及位置D4,感恩!
https://ithelp.ithome.com.tw/upload/images/20200314/20123369YumIad3Bjc.jpg

看更多先前的討論...收起先前的討論...
歪歪 iT邦新手 3 級 ‧ 2020-03-15 00:39:56 檢舉
次大值
=LARGE(A1:Q1,2)
wsj iT邦新手 5 級 ‧ 2020-03-15 13:40:03 檢舉
42假使是次大值,那位置16如何計算出來?
蟹老闆 iT邦大師 1 級 ‧ 2020-03-15 14:43:15 檢舉
=ADDRESS(1,MATCH(LARGE(A1:Q1,2),1:1,0),1,1)
wsj iT邦新手 5 級 ‧ 2020-03-15 15:41:34 檢舉
蟹老闆:請問D4=16是如何計算出來?謝謝回答
蟹老闆 iT邦大師 1 級 ‧ 2020-03-15 16:44:38 檢舉
Large=傳回資料集中第 K 個最大值
可參考https://support.office.com/zh-tw/article/large-%E5%87%BD%E6%95%B8-3af0af19-1190-42bb-bb8b-01672ec00a64

Match=函數會搜尋儲存格範圍中的指定項目,並傳回該項目於該範圍中的相對位置
可參考https://support.office.com/zh-tw/article/match-%E5%87%BD%E6%95%B8-e8dffd45-c762-47d6-bf89-533f4a37673a

ADDRESS=取得工作表中儲存格的位址
可參考https://support.office.com/zh-tw/article/address-%E5%87%BD%E6%95%B8-d0c26c0d-3991-446b-8de4-ab46431d4f89

要分析函數是如何運作需要自最內層的括號拆解起,所以
使用Large取得第二大的數,然後使用match去查找那個數的位置(match傳回的是位置資訊),之後再使用address將回傳的位置以欄位列號的方式呈現。
wsj iT邦新手 5 級 ‧ 2020-03-15 16:51:33 檢舉
謝謝您提供資訊!
圖片
  直播研討會
圖片
{{ item.channelVendor }} {{ item.webinarstarted }} |
{{ formatDate(item.duration) }}
直播中

1 個回答

0
ccenjor
iT邦高手 1 級 ‧ 2020-03-15 16:12:13
最佳解答

如果次大值是要38
則D3公式為
=LARGE(A1:Q1,COUNTIF(A1:Q1,B3)+1)
D4公式為
=MATCH(D3,A1:R1,0)
https://ithelp.ithome.com.tw/upload/images/20200315/20109881mNewdXepWh.png

看更多先前的回應...收起先前的回應...
ccenjor iT邦高手 1 級 ‧ 2020-03-15 16:20:05 檢舉

如果要使用42
則B3公式為
=QUOTIENT(SUMPRODUCT(LARGE((A1:Q1)*100+COLUMN(A1:Q1),1)),100)
B4公式為
=MOD(SUMPRODUCT(LARGE((A1:Q1)*100+COLUMN(A1:Q1),1)),100)
則D3公式為
=QUOTIENT(SUMPRODUCT(LARGE((A1:Q1)*100+COLUMN(A1:Q1),2)),100)
D4公式為
=MOD(SUMPRODUCT(LARGE((A1:Q1)*100+COLUMN(A1:Q1),2)),100)
https://ithelp.ithome.com.tw/upload/images/20200315/20109881zHCcCEX9PI.png

wsj iT邦新手 5 級 ‧ 2020-03-15 16:50:11 檢舉

請教大大:最大值位置是8,次大值位置是16,您給的解答是顛倒了,麻煩請再幫忙指教,感恩!

ccenjor iT邦高手 1 級 ‧ 2020-03-15 17:10:29 檢舉

如果要使用42
則B3公式為
=QUOTIENT(SUMPRODUCT(LARGE((A1:Q1)*100+100-COLUMN(A1:Q1),1)),100)
B4公式為
=100-MOD(SUMPRODUCT(LARGE((A1:Q1)*100+100-COLUMN(A1:Q1),1)),100)
則D3公式為
=QUOTIENT(SUMPRODUCT(LARGE((A1:Q1)*100+100-COLUMN(A1:Q1),2)),100)
D4公式為
=100-MOD(SUMPRODUCT(LARGE((A1:Q1)*100+100-COLUMN(A1:Q1),2)),100)
https://ithelp.ithome.com.tw/upload/images/20200315/20109881HXJUupvLWi.png

wsj iT邦新手 5 級 ‧ 2020-03-15 18:39:26 檢舉

感謝ccenjor大大協助解決問題,祝 一切順心!

我要發表回答

立即登入回答