[發問] 前2大和前3大同欄的第1列對應數。

帖子: 678
主題: 147
精華: 0
積分: 799
點名: 0
作業系統: win 8
軟體版本: MS 2003
閱讀權限: 50
性別: 男
註冊時間: 2016-2-9
最後登錄: 2022-1-20

2^#

發表於 2019-4-7 14:04 | 只看該作者

本帖最後由 ziv976688 於 2019-4-7 14:11 編輯

需求的簡要說明:
需求1:
"前2大"工作表的AZ7:BA7，AZ14:BA14，...，AZ63:BA63，AZ69:BA69的函數公式?

需求2:
"前3大"工作表的AZ7:BB7，AZ14:BB14，...，AZ63:BB63，AZ69:BB69的函數公式?

===================================================
小弟的函數底子粗淺，
一段一公式解就可以了，
這樣比較容易了解公式的運用。
謝謝幫忙^^

帖子: 678
主題: 147
精華: 0
積分: 799
點名: 0
作業系統: win 8
軟體版本: MS 2003
閱讀權限: 50
性別: 男
註冊時間: 2016-2-9
最後登錄: 2022-1-20

3^#

發表於 2019-4-7 23:33 | 只看該作者

本帖最後由 ziv976688 於 2019-4-7 23:44 編輯

回復 2# ziv976688
以實例再作補充說明~~
需求1:
選取邏輯︰
將$B7:$F7的最大數(=C7=42)，則將$B1:F1的同欄數值(=C1=02)，填入AZ7(=02)
將$B7:$F7的次大數(=E7=41)，則將$B1:F1的同欄數值(=E1=04)，填入BA7(=04)
.........其餘類推

當最大數有2個(含)以上時，則以同欄71列的較大數作第2個選取條件~
EX:W35和Y35都為$V35:$Z35的最大數(=55)，則選取較大數的Y71(=308)，並將Y1的24填入AZ35(=24)
當次大數有2個(含)以上時，則以同欄71列的較大數作第2個選取條件~
EX:AC42和AE42都為$AA42:$AE42的次大數(=23)，則選取較大數的AE71(=287)，並將AE1的30填入BA42(=30)
........其餘類推
請問:AZ7和BA7等的函數公式?

需求2:
將選取範圍擴大為$B7:$AX7，$B14:$AX14，.......，$B63:$AX63，$B69:$AX69
取前3大數
選取邏輯同"需求1"
請問:AZ7和BB7等的函數公式?

以上供參!謝謝幫忙!

hcm19522

大學生 Rank: 4

帖子: 1391
主題: 4
精華: 0
積分: 1401
點名: 0
作業系統: WIN
軟體版本: 2007
閱讀權限: 50
性別: 男
註冊時間: 2015-9-11
最後登錄: 2024-12-16

熱烈參與勳章

4^#

發表於 2019-4-8 09:45 | 只看該作者

AZ7:BA7=LOOKUP(1,0/(LARGE(B7:AX7,COLUMN(A1))=B7:AX7),B$1:AX$1)
AZ7:BA7往下複製
前3大同

google"EXCEL迷" blog 或google網址:https://hcm19522.blogspot.com/

帖子: 678
主題: 147
精華: 0
積分: 799
點名: 0
作業系統: win 8
軟體版本: MS 2003
閱讀權限: 50
性別: 男
註冊時間: 2016-2-9
最後登錄: 2022-1-20

5^#

發表於 2019-4-8 10:18 | 只看該作者

本帖最後由 ziv976688 於 2019-4-8 10:22 編輯

回復 4# hcm19522
hcm19522您好!
少一個條件~有並列排名時，則選取71列同欄的較大數，並將71列同欄的較大數之該並列排名的同欄第1列數字填入AZ7:BB14。

不過~小弟也遺漏了一個狀況~當最大數或次大數或第三大輸有並列排名，且該並列的排名數，在71列同欄的數字也相同時，則將該有並列排名的同欄第1列數字都填入AZ7:BB14。

待重新整理完後再貼上來。
謝謝您!

帖子: 678
主題: 147
精華: 0
積分: 799
點名: 0
作業系統: win 8
軟體版本: MS 2003
閱讀權限: 50
性別: 男
註冊時間: 2016-2-9
最後登錄: 2022-1-20

6^#

發表於 2019-4-8 10:37 | 只看該作者

本帖最後由 ziv976688 於 2019-4-8 10:42 編輯

回復 4# hcm19522

重新整理需求。
煩請各位大大指導和幫忙!
謝謝各位!

測試檔:

前2大和前3大的對應數-NEW.rar (12.44 KB)

需求1︰
以下列10段範圍
$B7:$F7,$G14:$K14,$L21:$P21,$Q28:$U28,$V35:$Z35,$AA42:$AE42,$AF49:$AJ49,$AK56:$AO56,$AP63:$AT63,
$AU69:$AX69
在AZ7︰AZ11填入公式~
公式條件1=($B7:$F7的最大數)
公式條件2=($B7:$F7的最大數)如果有2個(含)以上時，則選取在71列數字較大的($B7:$F7的最大數)
公式條件3=($B7:$F7的最大數)如果有2個(含)以上時，且在71列數字也相同時，則將該($B7:$F7的最大數)都選取。
最後將符合上述條件的同欄第1列的數字填入AZ7:AZ11
請問:AZ7:AZ11的函數公式要如何編寫?
其餘AZ14:AZ18，AZ21:AZ25，......，AZ63:AZ67，AZ69:AZ73同理類推。

在BA7︰BA11填入公式~
公式條件1=($B7:$F7的次大數)
公式條件2=($B7:$F7的次大數)如果有2個(含)以上時，則選取在71列數字較大的($B7:$F7的次大數)
公式條件3=($B7:$F7的次大數)如果有2個(含)以上時，且在71列數字也相同時，則將該($B7:$F7的次大數)都選取。
最後將符合上述條件的同欄第1列的數字填入BA7:BA11
請問:BA7:BA11的函數公式要如何編寫?
其餘BA14:BA18，BA21:BA25，......，BA63:BA67，BA69:BA73同理類推。

詳如︰"前2大"工作表

需求2︰
以下列10段範圍
$B7:$AX7,$B14:$AX14,$B21:$AX21,$B28:$AX28,$B35:$AX35,$B42:$AX42,$B49:$AX49,$B56:$AX56,$B63:$AX63,
$B69:$AX69
在AZ7︰AZ11填入公式~
公式條件1=($B7:$AX7的最大數)
公式條件2=($B7:$AX7的最大數)如果有2個(含)以上時，則選取在71列數字較大的($B7:$AX7的最大數)
公式條件3=($B7:$AX7的最大數)如果有2個(含)以上時，且在71列數字也相同時，則將該($B7:$AX7的最大數)都選取。
最後將符合上述條件的同欄第1列的數字填入AZ7:AZ11
請問:AZ7:AZ11的函數公式要如何編寫?
其餘AZ14:AZ18，AZ21:AZ25，......，AZ63:AZ67，AZ69:AZ73同理類推。

在BA7︰BA11填入公式~
公式條件1=($B7:$AX7的次大數)
公式條件2=($B7:$AX7的次大數)如果有2個(含)以上時，則選取在71列數字較大的($B7:$AX7的次大數)
公式條件3=($B7:$AX7的次大數)如果有2個(含)以上時，且在71列數字也相同時，則將該($B7:$AX7的次大數)都選取。
最後將符合上述條件的同欄第1列的數字填入BA7:BA11
請問:BA7:BA11的函數公式要如何編寫?
其餘BA14:BA18，BA21:BA25，......，BA63:BA67，BA69:BA73同理類推。

在BB7︰BB11填入公式~
公式條件1=($B7:$AX7的第三大數)
公式條件2=($B7:$AX7的第三大數)如果有2個(含)以上時，則選取在71列數字較大的($B7:$AX7的第三大數)
公式條件3=($B7:$AX7的第三大數)如果有2個(含)以上時，且在71列數字也相同時，則將該($B7:$AX7的第三大數)都選取。
最後將符合上述條件的同欄第1列的數字填入BB7:BB11
請問:BB7:BB11的函數公式要如何編寫?
其餘BB14:BB18，BB21:BB25，......，BB63:BB67，BB69:BB73同理類推。

詳如︰"前3大"工作表

帖子: 678
主題: 147
精華: 0
積分: 799
點名: 0
作業系統: win 8
軟體版本: MS 2003
閱讀權限: 50
性別: 男
註冊時間: 2016-2-9
最後登錄: 2022-1-20

7^#

發表於 2019-4-8 22:40 | 只看該作者

如果公式條件太多，公式條件3省略也可以。謝謝!

hcm19522

大學生 Rank: 4

帖子: 1391
主題: 4
精華: 0
積分: 1401
點名: 0
作業系統: WIN
軟體版本: 2007
閱讀權限: 50
性別: 男
註冊時間: 2015-9-11
最後登錄: 2024-12-16

熱烈參與勳章

8^#

發表於 2019-4-9 15:52 | 只看該作者

AZ7:BB12{=SMALL(IF(MAX((LARGE((MATCH(OFFSET($B$1,LOOKUP(9^9,$AX$2:$AX7,ROW($1:6)),,,49),OFFSET($B$1,LOOKUP(9^9,$AX$2:$AX7,ROW($1:6)),,,49),)=COLUMN($B:$AX)-1)*OFFSET($B$1,LOOKUP(9^9,$AX$2:$AX7,ROW($1:6)),,,49),COLUMN(A1))=OFFSET($B$1,LOOKUP(9^9,$AX$2:$AX7,ROW($1:6)),,,49))*(OFFSET($B$1,LOOKUP(9^9,$AX$2:$AX7,ROW($1:6)),,,49)/1%%+$B$71:$AX$71))=OFFSET($B$1,LOOKUP(9^9,$AX$2:$AX7,ROW($1:6)),,,49)/1%%+$B$71:$AX$71,COLUMN($B:$AX)-1),MOD(ROW(A1),7))

AZ7:BB12格式化=(AZ7)

AZ7:BB12複製到AZ14:BB19 AZ21:BB26 ...

google"EXCEL迷" blog 或google網址:https://hcm19522.blogspot.com/