返回列表 上一主題 發帖

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

[發問] 搜尋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。

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

副條件2, 公式難???
EXCEL參考資料:
http://blog.xuite.net/smile1000mile/blog

TOP

本帖最後由 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改為MINLARGE改為SMALL,但答案變成空白^^"
不知道還要修改哪裡?
敬請指教和賜正!謝謝您^^

TOP

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)
EXCEL參考資料:
http://blog.xuite.net/smile1000mile/blog

TOP

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 於 2019-4-20 16:28 編輯

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

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

TOP

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

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

TOP

        靜思自在 : 有多少力量就做多少事,不要心存等待,等待才會落空。
返回列表 上一主題