Airman

帖子: 315
主題: 51
精華: 0
積分: 367
點名: 0
作業系統: win7
軟體版本: office2003
閱讀權限: 20
性別: 男
註冊時間: 2015-9-29
最後登錄: 2021-10-12

1^# 跳轉到 » 倒序看帖

打印

字體大小: tT

發表於 2019-4-20 09:02 | 只看該作者

[發問] 搜尋49欄*10列比大小

本帖最後由 Airman 於 2019-4-20 09:04 編輯

測試檔~

前3小_0420.rar (27.22 KB)

備註︰
1_請以MS2003版的函數解題。
2_不增加輔助欄為解題原則。
3_各Sheet的內容皆為模擬。

需求︰
主條件=搜尋$B7:$AX7的前三小數字(中式排名)；
如果主條件的前三小數字是為單1個時，則將其在$B1:$AX1的同欄對應數字依序填入AZ7︰BB7。

副條件1=如果主條件其中的某排名數，有2個(含)以上時，則選取其在$B71:$AX71的同欄對應數字較小之排名者，
並將選取後的前三小數字之在$B1:$AX1的同欄對應數字依序填入AZ7︰BB7。

副條件2=如果主條件的其中某排名數，有2個(含)以上，且其在$B71:$AX71的同欄對應數字亦相同時，則將該相同排名者全選取，
並將選取後的前三小數字之在$B1:$AX1的同欄對應數字依序填入AZ7︰BB7。

以上懇請各位先進惠予賜教!謝謝!

樓主熱帖

准提部林

版主 Rank: 7 Rank: 7 Rank: 7

帖子: 2834
主題: 10
精華: 0
積分: 2890
點名: 0
作業系統: 〔略〕
軟體版本: 〔略〕
閱讀權限: 100
性別: 男
來自: 〔略〕
註冊時間: 2013-5-13
最後登錄: 2024-11-23

2^#

發表於 2019-4-20 11:25 | 只看該作者

副條件2, 公式難???

TOP

Airman

中學生 Rank: 2

帖子: 315
主題: 51
精華: 0
積分: 367
點名: 0
作業系統: win7
軟體版本: office2003
閱讀權限: 20
性別: 男
註冊時間: 2015-9-29
最後登錄: 2021-10-12

3^#

發表於 2019-4-20 12:02 | 只看該作者

本帖最後由 Airman 於 2019-4-20 12:06 編輯

回復 2# 准提部林

准大:
這是前3大(含71列也是取同欄對應數的較大者)的陣列公式~
=SMALL(IF(MAX((LARGE((MATCH(OFFSET($B$1,LOOKUP(9^9,$AX2:$AX7,ROW(1:6)),,,49),OFFSET($B$1,LOOKUP(9^9,$AX2:$AX7,ROW(1:6)),,,49),)=COLUMN($B:$AX)-1)*OFFSET($B$1,LOOKUP(9^9,$AX2:$AX7,ROW(1:6)),,,49),COLUMN(A1))=OFFSET($B$1,LOOKUP(9^9,$AX2:$AX7,ROW(1:6)),,,49))*(OFFSET($B$1,LOOKUP(9^9,$AX2:$AX7,ROW(1:6)),,,49)/1%%+$B$71:$AX$71))=OFFSET($B$1,LOOKUP(9^9,$AX2:$AX7,ROW(1:6)),,,49)/1%%+$B$71:$AX$71,$B$1:$AX$1),MOD(ROW(A1),7))
如何改成前3小(含71列也是取同欄對應數的較小者)的公式
小弟有將MAX改為MIN；LARGE改為SMALL，但答案變成空白^^"
不知道還要修改哪裡?
敬請指教和賜正!謝謝您^^

TOP

准提部林

版主 Rank: 7 Rank: 7 Rank: 7

帖子: 2834
主題: 10
精華: 0
積分: 2890
點名: 0
作業系統: 〔略〕
軟體版本: 〔略〕
閱讀權限: 100
性別: 男
來自: 〔略〕
註冊時間: 2013-5-13
最後登錄: 2024-11-23

4^#

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

AZ7:AZ12 六格區域陣列:
=TEXT(SUBSTITUTE(-SMALL(IF(MIN(IF($B7:$AX7>SUMIF($B$1:$AX$1,AY7,$B7:$AX7),$B7:$AX7))=$B7:$AX7,$B$71:$AX$71,4^8)/1%+$B$1:$AX$1,ROW($1:$6)),-MIN(IF(MIN(IF($B7:$AX7>SUMIF($B$1:$AX$1,AY7,$B7:$AX7),$B7:$AX7))=$B7:$AX7,$B$71:$AX$71)),),"[>]0;")

最多取六個:

前3小_0420-1.rar (31.06 KB)

TOP

ML089

版主 Rank: 7 Rank: 7 Rank: 7

帖子: 2025
主題: 13
精華: 0
積分: 2053
點名: 0
作業系統: WIN7
軟體版本: Office2007
閱讀權限: 100
性別: 男
來自: 台北市
註冊時間: 2011-3-2
最後登錄: 2024-3-14

論壇榮譽勳章論壇贊助勳章熱烈參與勳章 2014馬年全勤獎章 2015羊年全勤獎章 2016猴年全勤獎章

5^#

發表於 2019-4-20 14:50 | 只看該作者

AZ7:AZ12 六格區域陣列:
=TEXT(SMALL(IF(SMALL($B7:$AX7+$B$71:$AX$71%%,1+COUNTIF($B7:$AX7,"<="&INDEX(7:7,MATCH(--AY7,$1:$1,))) )=$B7:$AX7+$B$71:$AX$71%%,$B$1:$AX$1,99),ROW($1:$6)),"[<>99];;")

先選 AZ7:AZ12 ，編輯攔貼上公式，陣列輸入公式

選 AZ7:AZ12，右拉
選 AZ7:BB13，下拉

{...} 表示需要用 CTRL+SHIFT+ENTER 三鍵輸入公式

TOP

Airman

中學生 Rank: 2

帖子: 315
主題: 51
精華: 0
積分: 367
點名: 0
作業系統: win7
軟體版本: office2003
閱讀權限: 20
性別: 男
註冊時間: 2015-9-29
最後登錄: 2021-10-12

6^#

發表於 2019-4-20 16:23 | 只看該作者

本帖最後由 Airman 於 2019-4-20 16:28 編輯

回復 4# 准提部林
回復 5# ML089
感謝二位版主賜教的區域陣列公式^^"
可不可以請二位教小弟如何修改3#的一般陣列公式?
因為小弟多次有請教過該公式的原作者如何修正?也不知道是有什麼原因，有回覆，卻總是得不到"正確答案"的回覆。

不是小弟執著，而是只學會"比大"，卻沒學會"比小"~心裡很不是滋味很不踏實^^"
就如區域陣列比大比小的公式，因經過M版主的耐心教導，小弟才學會了。
謝謝您們二位^^

TOP

ML089

版主 Rank: 7 Rank: 7 Rank: 7

帖子: 2025
主題: 13
精華: 0
積分: 2053
點名: 0
作業系統: WIN7
軟體版本: Office2007
閱讀權限: 100
性別: 男
來自: 台北市
註冊時間: 2011-3-2
最後登錄: 2024-3-14

論壇榮譽勳章論壇贊助勳章熱烈參與勳章 2014馬年全勤獎章 2015羊年全勤獎章 2016猴年全勤獎章

7^#

發表於 2019-4-20 18:12 | 只看該作者

AZ7 陣列公式
=TEXT(SMALL((SMALL(OFFSET($B7,-LOOKUP(9,0/("小計"=$A1:$A7),{6,5,4,3,2,1,0}),,,49)+$B$71:$AX$71%%,1+COUNTIF(OFFSET($B7,-LOOKUP(9,0/("小計"=$A1:$A7),{6,5,4,3,2,1,0}),,,49),"<="&INDEX(OFFSET($A7,-LOOKUP(9,0/("小計"=$A1:$A7),{6,5,4,3,2,1,0}),,,50),MATCH(--OFFSET(AY7,-LOOKUP(9,0/("小計"=$A1:$A7),{6,5,4,3,2,1,0}),),$1:$1,))))<>OFFSET($B7,-LOOKUP(9,0/("小計"=$A1:$A7),{6,5,4,3,2,1,0}),,,49)+$B$71:$AX$71%%)*99+$B$1:$AX$1,LOOKUP(9,0/("小計"=$A1:$A7),{7,6,5,4,3,2,1})),"[<99];;")

右拉下拉

單單定位就花費很長的公式

{...} 表示需要用 CTRL+SHIFT+ENTER 三鍵輸入公式

TOP

Airman

中學生 Rank: 2

帖子: 315
主題: 51
精華: 0
積分: 367
點名: 0
作業系統: win7
軟體版本: office2003
閱讀權限: 20
性別: 男
註冊時間: 2015-9-29
最後登錄: 2021-10-12

8^#

發表於 2019-4-20 21:54 | 只看該作者

回復 7# ML089
版主:
謝謝您的再次賜教!謝謝您^^

TOP