返回列表 上一主題 發帖

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

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

前2大.png
2019-4-7 11:19
  
前3大.png
2019-4-7 11:20

測試檔:
前2大和前3大的對應數.rar (5.52 KB)

以上需求煩請各位幫忙!
謝謝各位!

本帖最後由 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的函數公式?

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

TOP

本帖最後由 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等的函數公式?


以上供參!謝謝幫忙!

TOP

AZ7:BA7=LOOKUP(1,0/(LARGE(B7:AX7,COLUMN(A1))=B7:AX7),B$1:AX$1)
AZ7:BA7往下複製
前3大同
隨意窩 "EXCEL迷"  blog  或 http://blog.xuite.net/hcm19522/twblog
已收集5000篇 EXCEL函數

TOP

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

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

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

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

TOP

本帖最後由 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大"工作表

TOP

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

TOP

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  ...
隨意窩 "EXCEL迷"  blog  或 http://blog.xuite.net/hcm19522/twblog
已收集5000篇 EXCEL函數

TOP

回復 8# hcm19522
hcm19522:您好!
測試成功~您真是厲害!
非常感謝您的幫忙和指導^^

TOP

回復 8# hcm19522
hcm19522:您好!
可否再請教您:只要公式條件1和公式條件2的解題公式?
想研習有公式條件3和沒有公式條件3的差別>*<
謝謝您!

TOP

        靜思自在 : 生氣,就是拿別人的過錯來懲罰自己。
返回列表 上一主題