excel 問題求解

excel

wsj 2020-03-14 21:51:58 ‧ 1257 瀏覽

分享至

請問各位大大:
如下圖所示,A1~Q1為ㄧ數列,請各位協助如何求出次大值D3及位置D4,感恩!

看更多先前的討論...收起先前的討論...

歪歪 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 }} |

直播中

1 個回答

ccenjor

iT邦大師 9 級 ‧ 2020-03-15 16:12:13

最佳解答

如果次大值是要38
則D3公式為
=LARGE(A1:Q1,COUNTIF(A1:Q1,B3)+1)
D4公式為
=MATCH(D3,A1:R1,0)

回應 4
分享
檢舉

看更多先前的回應...收起先前的回應...

ccenjor iT邦大師 9 級 ‧ 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)

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

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

ccenjor iT邦大師 9 級 ‧ 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)