返回列表 上一主題 發帖

[發問] 比大小&重複取其1的函數公式。

[發問] 比大小&重複取其1的函數公式。

本帖最後由 ziv976688 於 2019-9-2 07:34 編輯

附件 : 比大小&重複取其1的函數公式.rar (16.4 KB)

詳如附件內範例的說明。
謹請各位大大參考和賜教 ! 謝謝各位 !

本帖最後由 ziv976688 於 2019-9-2 09:40 編輯

需求1_
Sheet1!AQ : BW各次數欄如說明條件邏輯的數值。
需求2_
Sheet2!AQ : BW各次數欄如說明條件邏輯的數值。
謝謝 !

TOP

補充 :只要編寫下列儲存格的公式就可以了,其餘儲存格的公式,小弟自行套用填滿。謝謝 !
Sheet1的
AQ3=B9:G9的最大數值,重複取其1                               
AS3=B9:G9的次大數值,重複取其1
AU3=B9:G9的第三大數值,重複取其1

Sheet2的
AQ3=B9:G9的最小數值,重複取其1
AS3=B9:G9的次小數值,重複取其1
AU3=B9:G9的第三小數值,重複取其1

TOP

本帖最後由 ziv976688 於 2019-9-2 11:29 編輯

重新整理需求 :
需求1_
Sheet1的
AQ3=B9:G9的最大數值,重複取其1                              
AS3=B9:G9的次大數值,重複取其1 (如果沒有次大值時,則AS3顯示空白)
AU3=B9:G9的第三大數值,重複取其1 (如果沒有第三大值時,則AU3顯示空白)

需求2_
Sheet2的
AQ3=B9:G9的最小數值,重複取其1
AS3=B9:G9的次小數值,重複取其1(如果沒有次小值時,則AS3顯示空白)
AU3=B9:G9的第三小數值,重複取其1(如果沒有第三小值時,則AU3顯示空白)

以上  誠請各位大大賜教 !謝謝各位 !

TOP

有爬文找到
https://tw.answers.yahoo.com/question/index?qid=20141006000015KK02830
但不知怎麼套用到第2大或小和第3大或小^^"

TOP

本帖最後由 ziv976688 於 2019-9-2 16:37 編輯

最大Sheet1!AQ3=MAX($B$9:$G$9);最小Sheet2!AQ3=MIN($B$9:$G$9) =>本來只會這組;

次大Sheet1!AS3=IF(MAX(IF(B9:G9<MAX(B9:G9),B9:G9))>0,MAX(IF(B9:G9<MAX(B9:G9),B9:G9)),"") 陣列;
次小Sheet2!AS3=IF(MIN(IF(B9:G9>MIN(B9:G9),B9:G9))>0,MIN(IF(B9:G9>MIN(B9:G9),B9:G9)),"") 陣列;

第3大Sheet1!AU3 & 第3小Sheet2!AU3  => =>抓破頭,還是套寫不出來^^///

★PS : 是否次大(小)和第3大(小)都能以一般公式(部要鎮列)呈現?

誠請高手賜教 !  謝謝 !

TOP

本帖最後由 ziv976688 於 2019-9-2 22:21 編輯

有Google到類似公式,經套寫後~
次大Sheet1!AS3=IF(MAX(IF(B9:G9<AQ3,B9:G9))>0,MAX(IF(B9:G9<AQ3,B9:G9)),"") 陣列
三大Sheet1!AU3=IF(MAX(IF(B9:G9<AS3,B9:G9))>0,MAX(IF(B9:G9<AS3,B9:G9)),"")  陣列

次小Sheet2!AS3=IF(MIN(IF(B9:G9>AQ3,B9:G9))>0,MIN(IF(B9:G9>AQ3,B9:G9)),"")  陣列
三小Sheet2!AU3=IF(MIN(IF(B9:G9>AS3,B9:G9))>0,MIN(IF(B9:G9>AS3,B9:G9)),"")  陣列
可惜都是陣列公式。

有爬到"准提版主"回答的相關的一般公式解答~~
https://tw.answers.yahoo.com/question/index?qid=20110720000015KK10105
只是我還沒有悟出如何將直式(ROW)公式,改為橫式(column)公式?
只能再慢慢研究~@~@~

PS :
其中有一個公式,好像很簡潔,只是不知如何改^^"
【=SUMPRODUCT(1/COUNTIF($A$1:$A$11,$A$1:$A$11)) 非陣列
這式子之前看過,也曾收錄,是難得公式。 】

TOP

本帖最後由 ziv976688 於 2019-9-5 21:48 編輯

仔細研讀https://tw.answers.yahoo.com/question/index?qid=20110720000015KK10105後~
=SUMPRODUCT(1/COUNTIF($A$1:A$11,$A$1:A$11)) 非陣列
上述公式只是在統計範圍內篩選重複值後的數值個數加總,不是排大小。

排大小的一般公式
次大Sheet1!AS3=IF(MIN($A$9:A9)>=MIN($B9:$G9),"",LARGE($B9:$G9,COUNTIF($B9:$G9,">="&A9)+2))
三大Sheet1!AU3=IF(MIN($A$9:A9)>=MIN($B9:$G9),"",LARGE($B9:$G9,COUNTIF($B9:$G9,">="&A9)+3))

次小Sheet2!AS3=IF(MAX($A$9:A9)>=MAX($B9:$G9),"",SMALL($B9:$G9,COUNTIF($B9:$G9,"<="&A9)+2))
三小Sheet2!AU3=IF(MAX($A$9:A9)>=MAX($B9:$G9),"",SMALL($B9:$G9,COUNTIF($B9:$G9,"<="&A9)+3))

TOP

本帖最後由 ziv976688 於 2019-9-5 23:40 編輯

不好意思,8樓排大小的一般公式沒有篩選重複值
修改為
次大Sheet1!AS3
=IF(MIN($B$9:$G$9)=AQ3,"",LARGE($B9:$G9,COUNTIF($B9:$G9,">="&AQ3)+1))
三大Sheet1!AU3
=IF(OR(MIN($B$9:$G$9)=AS3,AS3=""),"",LARGE($B9:$G9,COUNTIF($B9:$G9,">="&AS3)+1))

次小Sheet2!AS3
=IF(MAX($B$9:$G$9)=AQ3,"",SMALL($B9:$G9,COUNTIF($B9:$G9,"<="&AQ3)+1))
三小Sheet2!AU3
=IF(OR(MAX($B$9:$G$9)=AS3,AS3=""),"",SMALL($B9:$G9,COUNTIF($B9:$G9,"<="&AS3)+1))

TOP

還不是很完美,參考看看
陣列公式
AQ3 =MAX(--TEXT(INDEX(INDIRECT(MID($AQ$1,3,3)),MID($AP3,2,3),),"[>="&IF(AO13,AO13,999)&"]!0"))
AR3 =INDEX(INDIRECT(MID($AQ$1,3,3)),2,MATCH(AQ3,INDEX(INDIRECT(MID($AQ$1,3,3)),MID($AP3,2,3),),))
兩式下拉

複製 AQ3:AR7 至AS3:AV7
{...} 表示需要用 CTRL+SHIFT+ENTER 三鍵輸入公式

TOP

        靜思自在 : 太陽光大、父母恩大、君子量大,小人氣大。
返回列表 上一主題