標題:
[發問]
前2大和前3大同欄的第1列對應數。
[打印本頁]
作者:
ziv976688
時間:
2019-4-7 11:23
標題:
前2大和前3大同欄的第1列對應數。
[attach]30350[/attach] [attach]30351[/attach]
測試檔:
[attach]30352[/attach]
以上需求煩請各位幫忙!
謝謝各位!
作者:
ziv976688
時間:
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的函數公式?
===================================================
小弟的函數底子粗淺，
一段一公式解就可以了，
這樣比較容易了解公式的運用。
謝謝幫忙^^
作者:
ziv976688
時間:
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
時間:
2019-4-8 09:45
AZ7:BA7=LOOKUP(1,0/(LARGE(B7:AX7,COLUMN(A1))=B7:AX7),B$1:AX$1)
AZ7:BA7往下複製
前3大同
作者:
ziv976688
時間:
2019-4-8 10:18
本帖最後由 ziv976688 於 2019-4-8 10:22 編輯
回復
4#
hcm19522
hcm19522您好!
少一個條件~有並列排名時，則選取71列同欄的較大數，並將71列同欄的較大數之該並列排名的同欄第1列數字填入AZ7:BB14。
不過~小弟也遺漏了一個狀況~當最大數或次大數或第三大輸有並列排名，且該並列的排名數，在71列同欄的數字也相同時，則將該有並列排名的同欄第1列數字都填入AZ7:BB14。
待重新整理完後再貼上來。
謝謝您!
作者:
ziv976688
時間:
2019-4-8 10:37
本帖最後由 ziv976688 於 2019-4-8 10:42 編輯
回復
4#
hcm19522
重新整理需求。
煩請各位大大指導和幫忙!
謝謝各位!
測試檔:
[attach]30356[/attach]
需求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大"工作表
作者:
ziv976688
時間:
2019-4-8 22:40
如果公式條件太多，公式條件3省略也可以。謝謝!
作者:
hcm19522
時間:
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 ...
作者:
ziv976688
時間:
2019-4-9 22:00
回復
8#
hcm19522
hcm19522:您好!
測試成功~您真是厲害!
非常感謝您的幫忙和指導^^
作者:
ziv976688
時間:
2019-4-10 09:25
回復
8#
hcm19522
hcm19522:您好!
可否再請教您:只要公式條件1和公式條件2的解題公式?
想研習有公式條件3和沒有公式條件3的差別>*<
謝謝您!
作者:
ziv976688
時間:
2019-4-10 11:34
回復
8#
hcm19522
hcm19522:您好!
因為想研習有公式條件3和沒有公式條件3的差別>*<
可否再請教您:只要公式條件1和公式條件2的解題公式?
即公式條件2只比大小，如果公式條件2有產生相同的數字時，就只取第1個的數字；所以解題公式只要填入AZ7:BB7，AZ14:BB14。AZ21:BB21，......
謝謝您!
作者:
ziv976688
時間:
2019-4-11 13:13
本帖最後由 ziv976688 於 2019-4-11 13:15 編輯
回復
8#
hcm19522
[attach]30363[/attach]
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))
h大大:
請教一下:
上述的解題公式，是依據條件1~3取第$B7:$AX7的最大,次大,第三大的數字，
但如果是要取$B2:$AX2的最大,次大,第三大數字；取$B3:$AX3的最大,次大,第三大數字；....其餘類推。
請問:公式要如何修改?
PS︰只要拉一列(不要往下拉)測試即可!謝謝您!
因為目前的解題公式是依據條件1~3取第$B7:$AX7的最大,次大,第三大的數字，所以我對公式中的
$AX$2:$AX7
和
ROW($1:6)
的範圍意涵不瞭解，無法自行修正^^"
煩請您不吝指正!
謝謝您!
作者:
ziv976688
時間:
2019-4-15 18:47
已自行修正完成。謝謝!
歡迎光臨 麻辣家族討論版版 (http://forum.twbts.com/)
