麻辣家族討論版版 - Powered by Discuz! Board

標題: [發問] 比大小&重複取其1的函數公式。 [打印本頁]

作者: ziv976688 時間: 2019-9-2 07:32 標題: 比大小&重複取其1的函數公式。

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

附件 : [attach]31216[/attach]

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

作者: ziv976688 時間: 2019-9-2 09:38

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

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

作者: ziv976688 時間: 2019-9-2 10:46

補充 :只要編寫下列儲存格的公式就可以了，其餘儲存格的公式，小弟自行套用填滿。謝謝 !
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

作者: ziv976688 時間: 2019-9-2 11:23

本帖最後由 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顯示空白)

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

作者: ziv976688 時間: 2019-9-2 14:55

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

作者: ziv976688 時間: 2019-9-2 16:22

本帖最後由 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大(小)都能以一般公式(部要鎮列)呈現?

誠請高手賜教 ! 謝謝 !

作者: ziv976688 時間: 2019-9-2 22:11

本帖最後由 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)) 非陣列
這式子之前看過，也曾收錄，是難得公式。】

作者: ziv976688 時間: 2019-9-5 21:36

本帖最後由 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))

作者: ziv976688 時間: 2019-9-5 23:39

本帖最後由 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))

作者: ML089 時間: 2019-9-6 22:05

還不是很完美，參考看看
陣列公式
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

作者: ML089 時間: 2019-9-6 22:08

要一式全部拉到底，需要將資料欄位對齊，不然公式會又臭又長。

作者: ziv976688 時間: 2019-9-6 23:45

回復 10# ML089
M版主 :您好!
AQ3 :AR3 兩式下拉 =>答案正確
複製 AQ3:AR7 至AS3:AV7 =>AS3:AV7都跟AQ3:AR7 相同 =>答案不正確
萬分感謝您一再的熱心指導^^
===========================
M版主 :
為了讓(順應)公式好編寫
不才將表格微調修改如下~
AP9剪下，貼上AP10
AP3:AP10的儲存格式改為=>自訂=>"列"#
詳如附件 :[attach]31231[/attach]
謹請您參考!
謝謝您!晚安^^

作者: ML089 時間: 2019-9-9 20:27

本帖最後由 ML089 於 2019-9-9 20:40 編輯

回復 12# ziv976688

剛好六日都有事沒有辦法立即回覆
(1)
AQ3 =MAX(--TEXT(INDEX($B:$G,$AP3,),"[>="&IF(AO3,AO3,999)&"]!0"))
AR3 =INDEX($B:$G,2,MATCH(AQ3,INDEX($B:$G,$AP3,),))
複製 AQ3:AR3
貼上 AQ4:AR7
複製 AQ3:AR7
貼上 AS3:AV7

(2)
複製 AQ3:AR3
貼上各區的左上角儲存格
將公式中的 $B:$G 修改為
  木($H:$P)
  水($Q:$X)
  火($Y:$AF)
  土($AG:$AN)

如(1) 複製及貼上該區公式

(3) 當然公式也可以自動抓取第一列中如下的文字，利用INDIRECT轉為區域，但不需付出超長公式作為代價，建議用手動修改比較容易維護公式。
  金($B:$G)
  木($H:$P)
  水($Q:$X)
  火($Y:$AF)
  土($AG:$AN)

[attach]31236[/attach]

作者: ziv976688 時間: 2019-9-10 02:36

回復 13# ML089
M版主:
謝謝您的耐心賜教!
有範例檔方便許多~不才會好好研究~感恩........^^

作者: ML089 時間: 2019-9-10 21:06

本帖最後由 ML089 於 2019-9-11 10:43 編輯

回復 14# ziv976688

(1) AQ3 =MAX(--TEXT(INDEX($B:$G,$AP3,),"[>="&IF(AQ$1="",AO3,999)&"]!0"))
AR3 =INDEX($B:$G,2,MATCH(AQ3,INDEX($B:$G,$AP3,),))
陣列公式

(2) AQ3 =MAX(--TEXT(INDEX($B:$G,$AP3,),"[>="&IF(AQ$1="",AO3,999)&"]!0"))
AR3 =SUBSTITUTE(TEXT(SUM(LARGE(IF(INDEX($B:$G,$AP3,)=AQ3,$B$2:$G$2,0),{1,2,3,4,5})*10^{0,2,4,6,8}),"00"&REPT("!,00",4)),"00,",)
陣列公式

(1)式的AR7會顯示 21 (數字)
(2)式的AR7會顯示 21,22 (文字)

[attach]31243[/attach]

作者: ziv976688 時間: 2019-9-10 23:39

本帖最後由 ziv976688 於 2019-9-10 23:52 編輯

回復 15# ML089
版主:您好!
您太厲害了，太神了!
將不才的最終目的都達成了!感恩.........^^

作者: ziv976688 時間: 2019-9-12 03:23

本帖最後由 ziv976688 於 2019-9-12 03:32 編輯

回復 15# ML089
測試檔 : [attach]31255[/attach]
M版主:您好!
很抱歉!因為貴公式超高階，已超越不才能理解的範圍N多，所以不才試改了近一天，終是沒轍^^///(汗顏)

冒昧再請問 :
空數前3小!AQ3:BC8的前3大公式((2)式)要如何改為前3小的公式?
誠請您賜教!謝謝您^^

作者: ML089 時間: 2019-9-12 22:54

回復 17# ziv976688

取大至小
AQ3 =MAX(--TEXT(INDEX($B:$G,$AP3,),"[>="&IF(AQ$1="",AO3,999)&"]!0"))

取小至大
AQ3 =MIN(--TEXT(INDEX($B:$G,$AP3,),"[<="&IF(AQ$1="",AO3,-1)&"]!9!9"))

作者: ziv976688 時間: 2019-9-12 23:41

回復 18# ML089
[attach]31256[/attach]
M版主:您好!
感謝您抽空回覆^^
AQ3=> OK了!
AR3的答案也會跟著對應更動成為"前3小"(很神^^)

請問一下:
為什麼AQ3:AV3下拉到AQ8:AV8
AQ3:AV7全部正確，AQ8答案也正確，
但AS8和AU8答案不正確。

同理
AX3:BB7全部正確，AX8答案也正確，
AZ8和BB8答案不正確。
誠請您再次指教!謝謝您^^

測試檔 : [attach]31257[/attach]

作者: ziv976688 時間: 2019-9-13 00:40

本帖最後由 ziv976688 於 2019-9-13 00:53 編輯

回復 18# ML089
[attach]31258[/attach]
M版主:您好!
瞭解不才在19樓的問題是出在哪裡了^^
是因為52列的第1小超過99了。
將公式中的"!9!9"改為"!99!9"或"!99!99"就可以了。

但因此又產生一個小問題~
因為次數的顯示值9999，會讓比大小的作用失真，所以是否能不要顯示"9999"，而能顯示"0"，
當然能顯示"空白"是最好不過了~但不敢強求，包括前3大的次數顯示"0"能改顯示"空白"亦同。
EX : 假設將B9:G9全部改為18，則AS3=0；AU3=0或AS=""；AU3=""

不好意思，問題一堆，誠請見諒! 謝謝您^^"

祝您 : 中秋節快樂!

作者: ziv976688 時間: 2019-9-13 03:47

本帖最後由 ziv976688 於 2019-9-13 03:53 編輯

回復 18# ML089
M版主:您好!
幾經深思之後，既然是0<ε，極小值就只能是=0值，不能是=""；又既然是ε>0，極大值也只能是>0值，所以也更不可能=""
要不然版主您應該也會設想周全才對。

本題18樓的貴解，應該是不才需求的極致答案了^^

再次萬分感謝您熱心和耐心的指導~感恩.........

作者: ML089 時間: 2019-9-13 08:12

回復 20# ziv976688

因為次數的顯示值9999，會讓比大小的作用失真，所以是否能不要顯示"9999"，而能顯示"0"，
當然能顯示"空白"是最好不過了~但不敢強求，包括前3大的次數顯示"0"能改顯示"空白"亦同。

我習慣用 999 9999 這樣的數字代表溢位
公式是看前面的數字用TEXT的數字大小篩選功能來決定哪些數字要留下，若前面是 "" 時公式就沒有判斷依據
當然你講的都能做，公式變複雜
不習歡看到 9999 也可以用儲存格格式功能來遮蔽 "[=9999] ;0"，或設定格式化條件當9999時字的顏色與底色相同。

作者: ziv976688 時間: 2019-9-13 10:12

回復 22# ML089
M版主:您好!
謝謝再次回覆說明^^
完全瞭解了~感恩...................

中秋假期快樂!

歡迎光臨麻辣家族討論版版 (http://forum.twbts.com/)