12345
返回列表 上一主題 發帖

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

Airman

帖子
315 
主題
51 
精華
0 
積分
367 
點名
0  
作業系統
win7 
軟體版本
office2003 
閱讀權限
20 
性別
男 
註冊時間
2015-9-29 
最後登錄
2021-10-12 
21# 跳轉到 »
發表於 2019-4-18 03:18 | 只看該作者
回復 17# ML089
ML089版大︰
請問︰貴公式如何改為前三小?
將LARGE改為SMALL答案不對^^"

TOP

Airman

帖子
315 
主題
51 
精華
0 
積分
367 
點名
0  
作業系統
win7 
軟體版本
office2003 
閱讀權限
20 
性別
男 
註冊時間
2015-9-29 
最後登錄
2021-10-12 
22#
發表於 2019-4-18 09:03 | 只看該作者
本帖最後由 Airman 於 2019-4-18 09:04 編輯

回復 13# hcm19522

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,$B$1:$AX$1),MOD(ROW(A1),7))

hcm19522大大︰
請問︰貴公式如何改為最小,次小,第三小?
將MAX改為MIN；LARGE改為SMALL答案不對^^"
您謝謝^^

TOP

Airman

帖子
315 
主題
51 
精華
0 
積分
367 
點名
0  
作業系統
win7 
軟體版本
office2003 
閱讀權限
20 
性別
男 
註冊時間
2015-9-29 
最後登錄
2021-10-12 
23#
發表於 2019-4-18 09:07 | 只看該作者
本帖最後由 Airman 於 2019-4-18 09:21 編輯

回復 17# ML089
=TEXT(SMALL(IF(LARGE($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),{1;2;3;4;5;6}),"[<>99];;")

ML089版大︰
請問︰貴公式如何改為最小,次小,第三小?
將LARGE改為SMALL答案不全對^^"
您謝謝^^

另外~
數值型態的最大,次大,第三大,最小,次小,第三小公式要怎麼改?
您謝謝^^

TOP

ML089

帖子
2025 
主題
13 
精華
0 
積分
2053 
點名
0  
作業系統
WIN7 
軟體版本
Office2007 
閱讀權限
100 
性別
男 
來自
台北市 
註冊時間
2011-3-2 
最後登錄
2024-3-14 

論壇榮譽勳章論壇贊助勳章熱烈參與勳章2014馬年全勤獎章2015羊年全勤獎章2016猴年全勤獎章
24#
發表於 2019-4-18 09:39 | 只看該作者
本帖最後由 ML089 於 2019-4-18 09:45 編輯

回復 23# Airman

選 BH7:BH12，貼上公式，陣列輸入(三鍵輸入)
=TEXT(SMALL(IF(SMALL($B7:$AX7+1/$B$71:$AX$71,1+COUNTIF($B7:$AX7,"<="&INDEX(7:7,MATCH(--BG7,$1:$1,))) )=$B7:$AX7+1/$B$71:$AX$71,$B$1:$AX$1,99),{1;2;3;4;5;6}),"[<>99];;")
選 BH7:BH12 右拉
選 BH7:BJ13 下拉

僅測試一組，其他你對一對吧!
{...} 表示需要用 CTRL+SHIFT+ENTER 三鍵輸入公式

TOP

ML089

帖子
2025 
主題
13 
精華
0 
積分
2053 
點名
0  
作業系統
WIN7 
軟體版本
Office2007 
閱讀權限
100 
性別
男 
來自
台北市 
註冊時間
2011-3-2 
最後登錄
2024-3-14 

論壇榮譽勳章論壇贊助勳章熱烈參與勳章2014馬年全勤獎章2015羊年全勤獎章2016猴年全勤獎章
25#
發表於 2019-4-18 09:47 | 只看該作者
回復 23# Airman

區域陣列要改為數值型，我還沒有新想法，可以用你上次的方法

把主要的公式 SMALL(.....) 定義程名稱，比較好維護公式。
{...} 表示需要用 CTRL+SHIFT+ENTER 三鍵輸入公式

TOP

Airman

帖子
315 
主題
51 
精華
0 
積分
367 
點名
0  
作業系統
win7 
軟體版本
office2003 
閱讀權限
20 
性別
男 
註冊時間
2015-9-29 
最後登錄
2021-10-12 
26#
發表於 2019-4-18 11:14 | 只看該作者
回復 25# ML089
前3小.rar (10.35 KB)
ML089版大︰
不好意思，沒有細查到">="，所以沒改到^^"
但改了之後(以您PO的公式)，答案還是只有AZ欄的答案正確，
右拉後~BA和BB二欄的答案不對(與AZ欄相同)
是小弟有哪裡錯了?
請賜正!謝謝您^^
=========================
=IFERROR(SMALL(IF(MAX(IF(COUNTIF($BC9:BC11,$B$1:$AX$1)=0,$B7:$AX7))=$B7:$AX7,$B$1:$AX$1),{1;2;3}),"")
可否能麻煩您將上述公式再+$B71︰$AX71的比對條件之公式PO上嗎?
名稱定義小弟自行處理。
謝謝您^^

TOP

ML089

帖子
2025 
主題
13 
精華
0 
積分
2053 
點名
0  
作業系統
WIN7 
軟體版本
Office2007 
閱讀權限
100 
性別
男 
來自
台北市 
註冊時間
2011-3-2 
最後登錄
2024-3-14 

論壇榮譽勳章論壇贊助勳章熱烈參與勳章2014馬年全勤獎章2015羊年全勤獎章2016猴年全勤獎章
27#
發表於 2019-4-18 11:47 | 只看該作者
回復 26# Airman

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),{1;2;3;4;5;6}),"[<>99];;")
選AZ7:AZ12右拉


計算SMALL取數位置
        注意 --AY7 配合AZ7:AZ12公式的左一格位置，利用 INDEX(MATCH( 查詢上個大小數值，利用COUNTIF算出 <= 該數值有幾個。


71列資料的比較
        取大 $B$71:$AX$71%%
        取小 1/$B$71:$AX$71
{...} 表示需要用 CTRL+SHIFT+ENTER 三鍵輸入公式

TOP

Airman

帖子
315 
主題
51 
精華
0 
積分
367 
點名
0  
作業系統
win7 
軟體版本
office2003 
閱讀權限
20 
性別
男 
註冊時間
2015-9-29 
最後登錄
2021-10-12 
28#
發表於 2019-4-18 12:42 | 只看該作者
回復 27# ML089
ML089版大︰午安!
一時還沒辦法消化~有待小弟再多加研習。
謝謝您的耐心教導~感恩^^

TOP

Airman

帖子
315 
主題
51 
精華
0 
積分
367 
點名
0  
作業系統
win7 
軟體版本
office2003 
閱讀權限
20 
性別
男 
註冊時間
2015-9-29 
最後登錄
2021-10-12 
29#
發表於 2019-4-18 20:02 | 只看該作者
回復 27# ML089
ML089版大︰您好!
計算SMALL取數位置==>已瞭解~謝謝您!
但~
取大 $B$71:$AX$71%%
取小 1/$B$71:$AX$71
還是不太瞭解(第1次接觸到%%)^^"
以貴解公式為例︰
前3大
=TEXT(SMALL(IF(LARGE($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),{1;2;3;4;5;6}),"[<>99];;")
前3小
=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),{1;2;3;4;5;6}),"[<>99];;")

為何前3大和前3小都是$B$71:$AX$71%%?

又以h大的公式為例︰
=SMALL(IF(MIN((SMALL((MATCH(OFFSET($B$1,LOOKUP(9^9,$AX65:$AX69,ROW(64:68)),,,49),OFFSET($B$1,LOOKUP(9^9,$AX65:$AX69,ROW(64:68)),,,49),)=COLUMN($B:$AX)-1)*OFFSET($B$1,LOOKUP(9^9,$AX65:$AX69,ROW(64:68)),,,49),COLUMN(A64))=OFFSET($B$1,LOOKUP(9^9,$AX65:$AX69,ROW(64:68)),,,49))*(OFFSET($B$1,LOOKUP(9^9,$AX65:$AX69,ROW(64:68)),,,49)/1%%+$B$71:$AX$71))=OFFSET($B$1,LOOKUP(9^9,$AX65:$AX69,ROW(64:68)),,,49)/1%%+$B$71:$AX$71,$B$1:$AX$1),MOD(ROW(A64),7))
其%%的位置又要如何修改為"取小"?

以上 敬請賜教!謝謝您^^

TOP

ML089

帖子
2025 
主題
13 
精華
0 
積分
2053 
點名
0  
作業系統
WIN7 
軟體版本
Office2007 
閱讀權限
100 
性別
男 
來自
台北市 
註冊時間
2011-3-2 
最後登錄
2024-3-14 

論壇榮譽勳章論壇贊助勳章熱烈參與勳章2014馬年全勤獎章2015羊年全勤獎章2016猴年全勤獎章
30#
發表於 2019-4-18 22:34 | 只看該作者
回復 29# Airman


   % = 1/100
%% = 1/10000
123% = 1.23
123%% = 0.0123

目前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

當取大或取小時，你的副條件1沒有改變 "$B71:$AX71的同欄對應數字較大之排名者"，
$B7:$AX7      = ,,,,,81,,,,,71,,,,,63,,,,,,81,,,,,
$B71:$AX71 = ,,,,,412,,362,,,319,,,,412,,
$B7:$AX7 + $B71:$AX71%% = ,,,,,81.0412,,,,71.0362,,,,63.0319,,,,,81.0412
使用 MAX 或 LARGE(...., 1) 都可以取出 81.0412

再去比對 if( LARGE(...,1) = $B7:$AX7 + $B$71:$AX$71%% , 是取出 $B$1:$AX$1,  否 ....)

若，副條件1 改變 "$B71:$AX71的同欄對應數字較 "小" 之排名者"
就使用 $B7:$AX7 + 1/$B71:$AX71

1/100     = 0.01   小數反數為大 的小數
1/1000  =0.001  大數反數為小 的小數

其他相同不再贅言，有不懂再問吧
{...} 表示需要用 CTRL+SHIFT+ENTER 三鍵輸入公式

TOP

        靜思自在 : 不要隨心所欲，要隨心教育自己。
12345
返回列表 上一主題