[發問] 修正公式，以利適用變動的內容。

Airman

第一列的對應數.rar (21.04 KB)

下載次數: 9

2019-4-16 18:10

AZ7==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,COLUMN($B:$AX)-1),MOD(ROW(A1),7))
陣列公式 ~~ 右拉到BB7再下拉填滿

目前AZ7的公式條件如下~
主條件=$B7:$AX7的前三大的數字(中式排名)；
如果主條件的前三大數字都為單1個時，則將其在$B1:$AX1的同欄對應數字依序填入AZ7︰BB7
副條件1=如果主條件1的其中某排名數，有2個(含)以上時，則選取其在$B71:$AX71的同欄對應數字較大之排名者，
並將選取後的前三大數字之在$B1:$AX1的同欄對應數字依序填入AZ7︰BB7
副條件2=如果主條件1的其中某排名數，有2個(含)以上，且其在$B71:$AX71的同欄對應數字亦相同時，則將該相同排名者全選取，
並將選取後的前三大數字之在$B1:$AX1的同欄對應數字依序填入AZ7︰BB7

修正原因︰
當$B1:$AX1的49個數字非由小而大排列(EX:Sheet1)時，其公式的答案無法隨$B1:$AX1的數字排列變動(EX:Sheet2&Sheet3)而變更。

PS︰這是小弟在本論壇擷取的函數公式，自己試了一天，無法讓AZ7公式都能適用於Sheet1~ Sheet3。

請問︰應該如何修正AZ7公式，以利公式都能適用於Sheet1~ Sheet3
懇請各位先進惠予賜教!謝謝!

ML089

選擇 AZ7:AZ9 三格
編輯欄貼上公式
=IFERROR(SMALL(IF(MAX(IF(COUNTIF($BC9:BC11,$B$1:$AX$1)=0,$B7:$AX7))=$B7:$AX7,$B$1:$AX$1),{1;2;3}),"")
陣列公式，三鍵輸入

選擇 AZ7:AZ9 三格，右拉複製公式

ML089

上公式僅在 Sheet2 測試

先上班去了，等中午我再看看

ML089

1樓公式補充說明，{1;2;3} 是概估同數最多3個，依目前表格可擴充至 1~7

ML089

再補充
往下的複製要選擇 AZ7:BB9，一次一個區域複製

ML089

剛剛檢查前面公式有錯誤

選擇 AZ7:AZ12 六格儲存格 ，貼上下列公式，以三鍵輸入公式                       
=IFERROR(SMALL(IF(MAX(IF(COUNTIF($AY7:AY12,$B$1:$AX$1)=0,$B7:$AX7))=$B7:$AX7,$B$1:$AX$1),{1;2;3;4;5;6}),"")                       
                       
選擇 AZ7:BB13 七格儲存格 ，下拉至底複製公式                       
                       
最後一組有異，是小計位置應該在70列位置，自行調整資料位置即可

Airman

回復 6# ML089

ML089版主︰午安!
呵~呵~好久沒有見識到"區域陣列"了~這讓小弟想起"臥龍"大師^^

公式OK~但這只寫到主條件=$B7:$AX7的前三大的數字(中式排名)的公式~
缺少再比對$B71:$AX71的副條件公式︰
副條件1=如果主條件1的其中某排名數，有2個(含)以上時，則選取其在$B71:$AX71的同欄對應數字較大之排名者，
並將選取後的前三大數字之在$B1:$AX1的同欄對應數字依序填入AZ7︰BB7

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

以上  謹請賜正!謝謝您^^

Airman

回復 6# ML089

不好意思，忘了附上範例~補上~
第一列的對應數_A.rar (18.82 KB)

下載次數: 2

2019-4-17 12:53

謝謝您!

hcm19522

=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))


OR
BD7:BF7 下拉=IFERROR(INDEX($1:$1,AZ7+1),"")

ML089

回復 8# Airman

我是看你的的答案做的，不是大部分相同嗎?

我有點閱讀障礙，文字敘述不是看得很懂。

你的是2003版EXCEL，沒有IFERROR函數，若可以接受數字文字型態時，公式可以改為
AZ7:AZ12 =TEXT(SMALL(IF(MAX(IF(COUNTIF($AY7:AY12,$B$1:$AX$1)=0,$B7:$AX7))=$B7:$AX7,$B$1:$AX$1,99),{1;2;3;4;5;6}),"[<>99];;")