[發問] 求指定欄位的次小值及次大值(不包含0)

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

論壇榮譽勳章論壇贊助勳章熱烈參與勳章 2014馬年全勤獎章 2015羊年全勤獎章 2016猴年全勤獎章

13^#

發表於 2013-4-26 18:19 | 只看該作者

回復 12# Bodhidharma

考慮周詳，讚

{...} 表示需要用 CTRL+SHIFT+ENTER 三鍵輸入公式

Bodhidharma

中學生 Rank: 2

帖子: 186
主題: 6
精華: 0
積分: 218
點名: 0
作業系統: win7
軟體版本: office 2007
閱讀權限: 20
性別: 男
註冊時間: 2012-11-12
最後登錄: 2014-4-15

12^#

發表於 2013-4-26 12:30 | 只看該作者

回復 10# ML089

算式丟分母，創造DIV!0的方式真的是很好用
不過這個式子沒有處理到"A欄為零，次大和次小值都應該是空白"的情形
在零的次大值會顯示1而不是空白
可以再乘個A1/A1來處理

=IFERROR(1/(1/LARGE(A:A,COUNTIF(A:A,">"&A1))),"")

複製代碼

修正為

=IFERROR(A1/A1*1/(1/LARGE(A:A,COUNTIF(A:A,">"&A1))),"")

複製代碼

另外Hiseh版主的公式也有一樣的問題

{=IF(COUNTIF($A$1:$A$222,">"&A2)=0,"",SMALL($A$1:$A$222,COUNTIF($A$1:$A$222,"<="&A2)+1))}

複製代碼

可以乘個A1來處理(是說這個公式不需要用陣列吧)

=IF(A1*COUNTIF($A$1:$A$222,">"&A1)=0,"",SMALL($A$1:$A$222,COUNTIF($A$1:$A$222,"<="&A1)+1))

複製代碼

PJChen

　暱稱: PJ

大學生 Rank: 4

帖子: 913
主題: 150
精華: 0
積分: 1089
點名: 0
作業系統: win10
軟體版本: office 2019
閱讀權限: 50
性別: 女
註冊時間: 2011-8-28
最後登錄: 2023-7-19

論壇榮譽勳章論壇贊助勳章

11^#

發表於 2013-4-26 11:54 | 只看該作者

回復 10# ML089

感謝您的回覆.
很開心提問題後,有不同的作法回覆,也讓自己多了解一些不同的思維.

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

論壇榮譽勳章論壇贊助勳章熱烈參與勳章 2014馬年全勤獎章 2015羊年全勤獎章 2016猴年全勤獎章

10^#

發表於 2013-4-25 21:12 | 只看該作者

回復 3# PJChen

=IFERROR(1/(1/SMALL(A:A,COUNTIF(A:A,"<"&A1))),"")
=IFERROR(1/(1/LARGE(A:A,COUNTIF(A:A,">"&A1))),"")

複製代碼

{...} 表示需要用 CTRL+SHIFT+ENTER 三鍵輸入公式

小誌

　暱稱: 小誌
　頭銜: 曾經也是水電工

管理員 Rank: 9 Rank: 9 Rank: 9

帖子: 1176
主題: 394
精華: 113
積分: 1747
點名: 1
作業系統: Vista
軟體版本: Office 2003
閱讀權限: 200
性別: 男
來自: 台灣
註冊時間: 2010-5-1
最後登錄: 2024-5-26

2021牛年全勤獎章好學求知勳章熱烈參與勳章 2017 雞年全勤獎章 2018 狗年全勤獎章 2019 豬年全勤獎章 2020鼠年全勤獎章

9^#

發表於 2013-4-25 20:12 | 只看該作者

回復 8# ML089

這不是系統問題,這是發言時會員應注意的原則
程式碼應該使用"代碼"包括起來,這樣程式碼內容就不會出錯,看的人也清楚,
所以請各位發佈程式碼時多加注意
2013-4-25 下午 08-11-20.png

借用你#7的發文,使用"代碼"標籤發文,看起來是不是清楚多了?

心安，平安
發表任何無意義的感謝或灌水文章一律砍,珍惜帳號,請詳閱版規!!

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

論壇榮譽勳章論壇贊助勳章熱烈參與勳章 2014馬年全勤獎章 2015羊年全勤獎章 2016猴年全勤獎章

8^#

發表於 2013-4-25 17:49 | 只看該作者

回復 4# Hsieh

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

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

{...} 表示需要用 CTRL+SHIFT+ENTER 三鍵輸入公式

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

論壇榮譽勳章論壇贊助勳章熱烈參與勳章 2014馬年全勤獎章 2015羊年全勤獎章 2016猴年全勤獎章

7^#

發表於 2013-4-25 17:37 | 只看該作者

[版主管理留言]

小誌(2013-4-25 20:15): 使用"代碼"標籤,這樣是不是清楚多了

借4樓檔案測試

陣列公式

次小，就是小於該數的最大值，外加TEXT去0值

=MAX(IF($A$1A$222<A1,$A$1A$222))
=TEXT(MAX(IF($A$1A$222<A1,$A$1A$222)),"0;;")

複製代碼

次大，就是大於該數的最小值，外加TEXT去0值

=MIN(IF($A$1A$222>A1,$A$1A$222))
=TEXT(MIN(IF($A$1A$222>A1,$A$1A$222)),"0;;")

複製代碼

因TEXT回傳後為文字，後續使用需注意再轉為數值

{...} 表示需要用 CTRL+SHIFT+ENTER 三鍵輸入公式

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

論壇榮譽勳章論壇贊助勳章熱烈參與勳章 2014馬年全勤獎章 2015羊年全勤獎章 2016猴年全勤獎章

6^#

發表於 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回傳後為文字，後續使用需注意再轉為數值

{...} 表示需要用 CTRL+SHIFT+ENTER 三鍵輸入公式

PJChen

　暱稱: PJ

大學生 Rank: 4

帖子: 913
主題: 150
精華: 0
積分: 1089
點名: 0
作業系統: win10
軟體版本: office 2019
閱讀權限: 50
性別: 女
註冊時間: 2011-8-28
最後登錄: 2023-7-19

論壇榮譽勳章論壇贊助勳章

5^#

發表於 2013-4-25 12:15 | 只看該作者

回復 4# Hsieh

感謝大大.
很ok.