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

Airman

測試檔~ 前3小_0420.rar (27.22 KB)

下載次數: 8

2019-4-20 09:01

備註︰
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。

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

准提部林

副條件2, 公式難???

Airman

回復 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，但答案變成空白^^"
不知道還要修改哪裡?
敬請指教和賜正!謝謝您^^

准提部林

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)

下載次數: 3

2019-4-20 14:03

ML089

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，下拉

Airman

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

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

ML089

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];;")

右拉下拉

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

Airman

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