返回列表 上一主題 發帖

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

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

本帖最後由 Airman 於 2019-4-16 18:19 編輯

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

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
懇請各位先進惠予賜教!謝謝!

選擇 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 三格,右拉複製公式
{...} 表示需要用 CTRL+SHIFT+ENTER 三鍵輸入公式

TOP

上公式僅在 Sheet2 測試

先上班去了,等中午我再看看
{...} 表示需要用 CTRL+SHIFT+ENTER 三鍵輸入公式

TOP

1樓公式補充說明,{1;2;3} 是概估同數最多3個,依目前表格可擴充至 1~7
{...} 表示需要用 CTRL+SHIFT+ENTER 三鍵輸入公式

TOP

再補充
往下的複製要選擇 AZ7:BB9,一次一個區域複製
{...} 表示需要用 CTRL+SHIFT+ENTER 三鍵輸入公式

TOP

剛剛檢查前面公式有錯誤

選擇 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列位置,自行調整資料位置即可
{...} 表示需要用 CTRL+SHIFT+ENTER 三鍵輸入公式

TOP

回復 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

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

TOP

回復 6# ML089

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

TOP

=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),"")
隨意窩 "EXCEL迷"  blog  或 http://blog.xuite.net/hcm19522/twblog
已收集5000篇 EXCEL函數

TOP

回復 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];;")
{...} 表示需要用 CTRL+SHIFT+ENTER 三鍵輸入公式

TOP

        靜思自在 : 手心向下是助人,手心向上是求人;助人快樂,求人痛苦。
返回列表 上一主題