Airman

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

1^# 跳轉到 » 正序看帖

打印

字體大小: tT

發表於 2019-4-16 18:10 | 只看該作者

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

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

樓主熱帖

hcm19522

博士班 Rank: 6 Rank: 6

帖子: 1808
主題: 190
精華: 0
積分: 2003
點名: 0
作業系統: WIN
軟體版本: 2007
閱讀權限: 100
性別: 男
註冊時間: 2015-9-11
最後登錄: 2026-10-19

熱烈參與勳章

43^#

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

參考http://forum.twbts.com/viewthrea ... a=pageD1&page=2

google"EXCEL迷" blog 或google網址:https://hcm19522.blogspot.com/

TOP

Airman

中學生 Rank: 2

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

42^#

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

回復 41# hcm19522
h大:
感謝您的再次回應^^
不好意思，還是沒有符合需求^^"
簡單說就是將9#的貴前3大的解答公式，改為前3小(含71列也是取同欄對應數的較小者)。
或請詳見~
http://forum.twbts.com/thread-21687-1-1.html
謝謝您^^

TOP

hcm19522

博士班 Rank: 6 Rank: 6

帖子: 1808
主題: 190
精華: 0
積分: 2003
點名: 0
作業系統: WIN
軟體版本: 2007
閱讀權限: 100
性別: 男
註冊時間: 2015-9-11
最後登錄: 2026-10-19

熱烈參與勳章

41^#

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

回復 40# Airman

修正 {=RIGHT(MIN(IF(SMALL(IF(MATCH($B7:$AX7,$B7:$AX7,)=COLUMN($B:$AX)-1,$B7:$AX7),COLUMN(A1))=$B7:$AX7,$B7:$AX7/1%+$B$1:$AX$1)),2)

google"EXCEL迷" blog 或google網址:https://hcm19522.blogspot.com/

TOP

Airman

中學生 Rank: 2

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

40^#

發表於 2019-4-19 19:52 | 只看該作者

回復 39# hcm19522
h大:
呵~呵~公式竟然變成這麼簡短~~厲害~~
粗看應該是缺少名次有並排者，其71列同欄數字也相同時，則該並排者全取的條件。
小弟先研究再說。
謝謝您的不吝指導~感恩^^

TOP

hcm19522

博士班 Rank: 6 Rank: 6

帖子: 1808
主題: 190
精華: 0
積分: 2003
點名: 0
作業系統: WIN
軟體版本: 2007
閱讀權限: 100
性別: 男
註冊時間: 2015-9-11
最後登錄: 2026-10-19

熱烈參與勳章

39^#

發表於 2019-4-19 19:08 | 只看該作者

AZ7:BB7{=RIGHT(SMALL(IF(MATCH($B7:$AX7,$B7:$AX7,)=COLUMN($B:$AX)-1,$B7:$AX7/1%+$B$1:$AX$1),COLUMN(A1)),2)
複製到各行

google"EXCEL迷" blog 或google網址:https://hcm19522.blogspot.com/

TOP

Airman

中學生 Rank: 2

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

38^#

發表於 2019-4-19 17:44 | 只看該作者

本帖最後由 Airman 於 2019-4-19 17:46 編輯

回復 37# hcm19522
h大:
貴5欄一區段的解題大作，小弟已拜讀~佩服^^
另外~
能否請您再以35#的原公式作修改?
因為雖經過M大的諸多講解，對其公式是已知前3大和前3小的變化；
但應用到貴公式卻一直跑不出正確答案^^"
不好意思，給您增添麻煩，謝謝您^^
PS因為第一次遇到這種類型的解題公式，所以想多學習。

TOP

hcm19522

博士班 Rank: 6 Rank: 6

帖子: 1808
主題: 190
精華: 0
積分: 2003
點名: 0
作業系統: WIN
軟體版本: 2007
閱讀權限: 100
性別: 男
註冊時間: 2015-9-11
最後登錄: 2026-10-19

熱烈參與勳章

37^#

發表於 2019-4-19 16:58 | 只看該作者

回復 35# Airman

參考
https://blog.xuite.net/hcm19522/twblog/587392713

google"EXCEL迷" blog 或google網址:https://hcm19522.blogspot.com/

TOP

Airman

中學生 Rank: 2

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

36^#

發表於 2019-4-19 16:21 | 只看該作者

回復 9# hcm19522
附上範例檔供參~

前3小_EX-1.rar (6.87 KB)

TOP

Airman

中學生 Rank: 2

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

35^#

發表於 2019-4-19 16:01 | 只看該作者

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

回復 9# hcm19522
h大:
請教您~下列貴解答公式要如何改為前3小名次有並排時，則$B$71:$AX$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))
以上敬請賜教 !謝謝您^^

TOP