我要回覆

羅仔

6分

樓主

羅仔

個人積分：6分

文章編號：86880889

【Excel】函數公式題第二問

很謝謝大家的幫忙，我問的Excel函數公式第一題，我不但學會了，而且還學會了至少5種以上的方法，不得不配服各位大神的功力，在此我又發起了第二個問題，希望可以集眾大神的力量，集思廣益，【題目】給定表格甲，要如何利用函數的公式得出表格乙(得獎方式有兩種)，請不要回我「樞鈕分析表」，我是要練習寫出函數的公式，先謝謝大家

【Excel】函數公式題第二問

有需要此檔案的在這：
https://drive.google.com/drive/folders/164vHOditec1mJ1-QH4zFzS-N4DpTyPNL?usp=sharing

2023-02-16 13:56 #1

文章關鍵字

excel 函數公式題

victor800319

119分

2樓

victor800319

個人積分：119分

文章編號：86881779

1.排序
2.找出前六名
3.XLOOKUP

謝謝喔，能否可以再具體一點，麻煩你了

2023-02-16 17:03

錦子

2124分

3樓

錦子

個人積分：2124分

文章編號：86882536

在P2到P7建立各個國家(可以複製C3:C16到P2，再用資料>移除重複項產生)。
在Q2輸入
=IFERROR(SUMPRODUCT(SMALL(($C$3:$C$16=$P2)*($D$3:$D$16),COUNTIF($C$3:$C$16,"<>"&$P2)+COLUMN()-16)),"")
也可以
=IFERROR(SMALL(IF($C$3:$C$16=$P2,$D$3:$D$16),COLUMN()-16),"") 舊版本要按CTRL+SHIFT+ENTER完成輸入，365與2021按ENTER鍵成輸入。
或
=IFERROR(SMALL(FILTER($D$3:$D$16,$C$3:$C$16=$P2),COLUMN()-16),"")舊版本要按CTRL+SHIFT+ENTER完成輸入，365與2021按ENTER鍵成輸入。
將公式複製到Q2:T7

各國只能一個作法：
K3:
=INDEX(B$3:B$16,MATCH(SMALL($Q$2:$Q$7,$J3),$D$3:$D$16,0),0)
將公式複製到K3:M8

點我看大圖

各國只能2個作法：
K3:
=INDEX(B$3:B$16,MATCH(SMALL($Q$2:$R$7,$J3),$D$3:$D$16,0),0)
將公式複製到K3:M8

點我看大圖

錦色如月，子耀光芒。

錦子

謝謝，修正了。

2023-02-16 17:57

哇！我試成功了耶，太感謝您了，謝謝你把詳細的公式寫給我，超感動的

2023-02-16 18:20

Yaude Huang

5289分

4樓

Yaude Huang

個人積分：5289分

文章編號：86883974

得獎方式 A

實際步驟分析

第一步
=COUNTIFS(C3:C16, C3:C16, D3:D16,"<" & D3:D16)
先將相同國家排名順序列出來
透過countifs的方式，
C3:C16, C3:C16 ==> 把跟自己相同國家總數計算出來
& D3:D16,"<" & D3:D16 ==> 再過濾比自己成績數字還小的總數

第二步

過濾出每一個國家最小的成績
=FILTER(B3:D16,COUNTIFS(C3:C16, C3:C16, D3:D16,"<" & D3:D16) < 1)
就是把第一步的公式加上 < 1即可

第三步
排序

=SORT(FILTER(B3:D16,COUNTIFS(C3:C16, C3:C16, D3:D16,"<" & D3:D16) < 1), 3)
用第三個欄位排序

得獎方式B

前三步

稍微修改一下前面的 <1
改成<2
即可將每個國家的前兩名都陳列出來

=SORT(FILTER(B3:D16,COUNTIFS(C3:C16, C3:C16, D3:D16,"<" & D3:D16) < 2), 3)

第四步

請自己看圖片
不知為何這公式填進來後最後一段會亂掉
主要是只保留前六名，把秒數小於第七名的留下

剛發現樓主原來的範本，第一個秒數寫的是9.8，但答案裡卻是9.61
是樓主的答案有誤，得獎方式A與B第一個秒數不一樣，但B裡的答案秒數用的是A裡的

哇！讚讚讚，老師等級的說明，一看就懂，很謝謝您喔

2023-02-16 21:59

我已經修正好了喔，A與B的秒數一樣了，謝謝您的提醒，也非常謝謝您的函數公式

2023-02-16 23:05

星幻

13943分

5樓

星幻

個人積分：13943分

文章編號：86885803

嗯…

我是沒想到這會是學習題目。

首先第一步是在E3欄位輸入『=RANK(D3,D$3:D$20,1)』，並把公式向下拉，如此一來就會直接作好名次的排序。

第二步於I1欄位輸入1或2，代表取一名或兩名，當然…也可輸入更多。

然後於P3至16欄位輸入1~16名。

在Q3欄位中輸入『=IF(ISERR(INDEX($B$3:$B$20,SMALL(IF(P3=$E$3:$E$20,ROW($E$3:$E$20)-ROW($E$3)+1),1))),IF(ISERR(INDEX($B$3:$B$20,SMALL(IF(P3-1=$E$3:$E$20,ROW($E$3:$E$20)-ROW($E$3)+1),2))),INDEX($B$3:$B$20,SMALL(IF(P3-2=$E$3:$E$20,ROW($E$3:$E$20)-ROW($E$3)+1),3)),INDEX($B$3:$B$20,SMALL(IF(P3-1=$E$3:$E$20,ROW($E$3:$E$20)-ROW($E$3)+1),2))),INDEX($B$3:$B$20,SMALL(IF(P3=$E$3:$E$20,ROW($E$3:$E$20)-ROW($E$3)+1),1)))』，按下Ctrl+Shift+Enter，會發現公式前後加上了大括號。

這個公式又臭又長對吧，但它卻是有意義的，因為它可以讓秒數(成績)完全相同的情況下也正常顯示。

再來於R3欄位輸入『=INDEX($B:$E,MATCH($Q3,$B:$B,0),2)』，S3欄位輸入『=IF(COUNTIF(R$2:R2,R3)<=$I$1-1,MAX(S$2:S2)+1,"")』，並將Q3~S3欄位的公式下向拉，如此一來就會顯示排序後的名次，並會跳過超出條件的。

最後是在K3欄位輸入『=INDEX($P:$S,MATCH($J3,$S:$S,0),2)』、L3欄位輸入『=INDEX($B:$E,MATCH($K3,$B:$B,0),2)』、M3欄位輸入『=INDEX($B:$E,MATCH($K3,$B:$B,0),3)』，並同樣將公式往下拉。

結果就出來了。

哇，真的是太感謝了，而且相同數據也可以正常顯示，同時解說也非常詳細，真的是非常的謝謝您

2023-02-17 13:13

【Excel】函數公式題第二問

小惡魔新聞台

今日熱門文章 網友點擊推薦！

今日熱門文章　網友點擊推薦！