Board logo

標題: [發問] 求指定欄位的次小值及次大值(不包含0) [打印本頁]
作者: PJChen    時間: 2013-4-25 00:54     標題: 求指定欄位的次小值及次大值(不包含0)

大大們,
下表要求次小值及次大值(都不包含0)
欄C2~C24有以下數值,我想在D2輸入公式,求指定欄位的次小值?E2輸入公式求指定欄位的次大值? 請問有什麼公式可以求得答案?  TKS.

[attach]14776[/attach]
作者: Bodhidharma    時間: 2013-4-25 01:26

回復 1# PJChen

在你求眾數那帖中學到的方法
http://forum.twbts.com/viewthrea ... amp;page=6#pid52283

D2儲存格=SMALL(C:C,COUNTIF(C:C,"<"&C2))下拉
E2儲存格=LARGE(C:C,COUNTIF(C:C,">"&C2))下拉

0和#NUM!的問題對你來說應該不難，就請你自己處理囉~
作者: PJChen    時間: 2013-4-25 10:53

回復 2# Bodhidharma

感謝您的提示.
改成以下可解決#NUM!問題,不過次小值0不出現的問題就試不出來了!!

D2=IFERROR(SMALL(C:C,COUNTIF(C:C,"<"&C2)),"")

E2=IFERROR(LARGE(C:C,COUNTIF(C:C,">"&C2)),"")
作者: Hsieh    時間: 2013-4-25 11:31

本帖最後由 Hsieh 於 2013-4-25 11:35 編輯

回復 3# PJChen

試試看[attach]14781[/attach]
作者: PJChen    時間: 2013-4-25 12:15

回復 4# Hsieh

感謝大大.
很ok.
作者: ML089    時間: 2013-4-25 17:36

本帖最後由 Hsieh 於 2013-4-25 19:33 編輯

借4樓檔案測試

陣列公式

次小，就是小於該數的最大值，外加TEXT去0值
=MAX(IF($A$1:$A$222<A1,$A$1:$A$222))
=TEXT(MAX(IF($A$1:$A$222<A1,$A$1:$A$222)),"0;;")

次大，就是大於該數的最小值，外加TEXT去0值
=MIN(IF($A$1:$A$222>A1,$A$1:$A$222))
=TEXT(MIN(IF($A$1:$A$222>A1,$A$1:$A$222)),"0;;")

因TEXT回傳後為文字，後續使用需注意再轉為數值
作者: ML089    時間: 2013-4-25 17:37

借4樓檔案測試

陣列公式

次小，就是小於該數的最大值，外加TEXT去0值
  1. =MAX(IF($A$1A$222<A1,$A$1A$222))
  2. =TEXT(MAX(IF($A$1A$222<A1,$A$1A$222)),"0;;")
複製代碼
次大，就是大於該數的最小值，外加TEXT去0值
  1. =MIN(IF($A$1A$222>A1,$A$1A$222))
  2. =TEXT(MIN(IF($A$1A$222>A1,$A$1A$222)),"0;;")
複製代碼
因TEXT回傳後為文字，後續使用需注意再轉為數值
作者: ML089    時間: 2013-4-25 17:49

回復 4# Hsieh

超版
可否將 禁用表情 設為內定

6樓採用簡單模式下 發表回覆 就會有表情
7樓採用高級模式下 發表回覆 將 禁用表情 勾選(本就已經內定勾選)
以前我都是用6樓方式就不會出現"表情"這樣，不知這幾天都變成這樣真不方便。
作者: 小誌    時間: 2013-4-25 20:12

回復 8# ML089


    這不是系統問題,這是發言時會員應注意的原則
程式碼應該使用"代碼"包括起來,這樣程式碼內容就不會出錯,看的人也清楚,
所以請各位發佈程式碼時多加注意
[attach]14786[/attach]
借用你#7的發文,使用"代碼"標籤發文,看起來是不是清楚多了?
作者: ML089    時間: 2013-4-25 21:12

回復 3# PJChen
  1. =IFERROR(1/(1/SMALL(A:A,COUNTIF(A:A,"<"&A1))),"")
  2. =IFERROR(1/(1/LARGE(A:A,COUNTIF(A:A,">"&A1))),"")
複製代碼
作者: PJChen    時間: 2013-4-26 11:54

回復 10# ML089

感謝您的回覆.
很開心提問題後,有不同的作法回覆,也讓自己多了解一些不同的思維.
作者: Bodhidharma    時間: 2013-4-26 12:30

回復 10# ML089

算式丟分母，創造DIV!0的方式真的是很好用
不過這個式子沒有處理到"A欄為零，次大和次小值都應該是空白"的情形
在零的次大值會顯示1而不是空白
可以再乘個A1/A1來處理
  1. =IFERROR(1/(1/LARGE(A:A,COUNTIF(A:A,">"&A1))),"")
複製代碼
修正為
  1. =IFERROR(A1/A1*1/(1/LARGE(A:A,COUNTIF(A:A,">"&A1))),"")
複製代碼
另外Hiseh版主的公式也有一樣的問題
  1. {=IF(COUNTIF($A$1:$A$222,">"&A2)=0,"",SMALL($A$1:$A$222,COUNTIF($A$1:$A$222,"<="&A2)+1))}
複製代碼
可以乘個A1來處理(是說這個公式不需要用陣列吧)
  1. =IF(A1*COUNTIF($A$1:$A$222,">"&A1)=0,"",SMALL($A$1:$A$222,COUNTIF($A$1:$A$222,"<="&A1)+1))
複製代碼
作者: ML089    時間: 2013-4-26 18:19

回復 12# Bodhidharma


    考慮周詳，讚



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