Excel函數學習37:SMALL函數

2021-02-13 完美Excel

微信公眾號:excelperfect

 

使用SMALL函數,可以獲得一組數值中指定第幾小的數值。

 

什麼情況下使用SMALL函數?

SMALL函數數據組中第k個最小值。它能夠:

獲取單元格區域中第3個最小值

求n個最小值之和

返回單元格區域中的負值

動態排序單元格區域中的數值

獲取單元格區域中唯一值列表

 

SMALL函數語法

SMALL函數有2個參數,其語法如下:

 

SMALL函數陷阱

如果指定的最小值數大於數組總數,則會返回#NUM!錯誤。

 

示例1: 獲取單元格區域中第3個最小值

要獲取單元格區域A1:A5中第3小的值,使用公式:

=SMALL(A1:A5,3)

返回數值3,即該區域中第3小的值為3。

下面的公式獲取數組中第3小值:

=SMALL({50,20,30,60,50,10},3)

返回30。

 

示例2: 求n個最小值之和

下圖所示工作表中,命名區域Data為A1:A9。求區域Data中前3個最小值之和的公式為:

=SUM(SMALL(Data,{1,2,3}))

這是一個數組公式,因此輸入完成後要按Ctrl+Shift+Enter組合鍵。公式依次獲取Data區域中第1個、第2個、第3個最小值,然後將它們相加。

也可以使用數組公式:

=SUM(SMALL(Data,ROW(INDIRECT("1:3"))))

相比於上面的公式,這個公式使用了ROW函數和INDIRECT函數,將更靈活。

 

示例3: 返回單元格區域中的負值

單元格區域A2:A10命名為「Num」。將該區域中所有負值提了出來的公式為:

=INDEX(Num,SMALL(IF(Num<0,ROW(INDIRECT("1:"& ROWS(Num)))),ROW(INDIRECT("1:" & ROWS(Num)))))

該公式為數組公式,因此選取與Num區域相同大小的區域,輸入公式後按Ctrl+Shift+Enter組合鍵。

上述公式獲得的結果包含#NUM!錯誤值,可以使用IFERROR函數消除錯誤值:

=IFERROR(INDEX(Num,SMALL(IF(Num<0,ROW(INDIRECT("1:"& ROWS(Num)))),ROW(INDIRECT("1:" &ROWS(Num))))),"")

 

示例4: 動態排序單元格區域中的數值

如下圖所示的工作表,將單元格區域A1:A15命名為SortNum,現在要求對該區域中的數值態排序,即在該區域輸入數值時,列D中的排序會自動調整。

下面的數組公式由小到大排列SortNum區域的數值:

=IFERROR(SMALL(SortNum,ROW(INDIRECT("1:"& ROWS(SortNum)))),"")

 

示例5: 獲取單元格區域中唯一值列表

如下圖所示的工作表,單元格區域A1:A8命名為「DataValue」,可以看出,該區域中有許多重複值。下面的數組公式提取並返回該區域中唯一值列表:

=IFERROR(INDEX(DataValue,SMALL(IF(MATCH(DataValue,DataValue,0)=ROW(INDIRECT("1:"&ROWS(DataValue))),MATCH(DataValue,DataValue,0),""),ROW(INDIRECT("1:"& ROWS(DataValue))))),"")

 

本文屬原創文章,轉載請聯繫我(xhdsxfjy@163.com)或者註明出處。

歡迎在下面留言,完善本文內容,讓更多的人學到更完美的知識。

歡迎關注[完美Excel]微信公眾號:

方法1—在微信「添加朋友」或者通訊錄中搜索「完美Excel」或者「excelperfect」後點擊關注。

方法2—掃一掃下面的二維碼

相關焦點

  • excel中的small函數和large函數,與其他函數結合還有這功能!
    在excel中,large函數和small函數用的人比較少,但是用的少並不代表這兩個函數沒有用,反而十分,今天小編就專門寫了這篇文章來介紹一下這兩個函數,一起學習一下吧、一、基本用法。對於large函數和small函數,都只有兩個參數,分別為large(數值區域,返回的第幾個最大值),small(數值區域,返回的第幾個最小值)。如下圖所示,查找倒數第二名的成績,輸入的函數公式為=SMALL(C2:C20,2),表示查找C2到C20單元格中第2小的數,結果為62。
  • Excel公式與函數之美11:小而美的函數之SMALL函數
    圖1一鍵直達 >> Excel函數學習37:SMALL函數 SMALL函數之美SMALL函數的原理很簡單,但將其組合在公式中,能夠幫助我們按順序獲取數據,這可能就是SMALL函數的美妙之處。
  • excel函數實例視頻教程第二集-從零開始學習excel函數學習視頻教程
    excel函數學習視頻教程 會計excel教程視頻 excel函數教程視頻 excel函數實例視頻教程 excel函數實例視頻教程第二集-從零開始學習excel函數學習視頻教程Excel課程由部落窩教育滴答老師主講。
  • Excel函數學習28:TRIM函數
    本文來源於www.contextures.com,由完美Excel翻譯,稍有補充和修改,特輯錄於此,分享與大家共同學習。
  • Excel函數學習10:REPLACE函數
    本文來源於www.contextures.com,由完美Excel翻譯,稍有補充和修改,特輯錄於此,分享與大家共同學習。
  • Excel函數學習13:ADDRESS函數
    微信公眾帳號:excelperfect本文來源於www.contextures.com,由完美Excel
  • Excel函數sum、large、small、count和數組在案例中的組合用法
    今天的內容,我們將引入更多的Excel函數來加入其中,比如函數sum,函數small,函數count,還有昨天用到的函數large。現在我們就通過實例的形式,一步步腳踏實地地來調用上述函數來解決實際案例的問題。
  • Excel函數學習16:HYPERLINK函數
    本文來源於www.contextures.com,由完美Excel翻譯,稍有補充和修改,特輯錄於此,分享與大家共同學習。
  • Excel函數學習8:SEARCH函數
    本文來源於www.contextures.com,由完美Excel翻譯,稍有補充和修改,特輯錄於此,分享與大家共同學習。
  • Excel函數學習24:CELL函數
    本文來源於www.contextures.com,由完美Excel翻譯,稍有補充和修改,特輯錄於此,分享與大家共同學習。
  • Excel函數學習11:SUBSTITUTE函數
    本文來源於www.contextures.com,由完美Excel翻譯,稍有補充和修改,特輯錄於此,分享與大家共同學習。
  • Excel函數學習23:TRANSPOSE函數
    本文來源於www.contextures.com,由完美Excel翻譯,稍有補充和修改,特輯錄於此,分享與大家共同學習。
  • Excel函數學習3:CHOOSE函數
    本文來源於www.contextures.com,由完美Excel翻譯,稍有補充和修改,特輯錄於此,分享與大家共同學習。
  • Excel函數學習1:MATCH函數
    本文來源於www.contextures.com,由完美Excel翻譯,稍有補充和修改,特輯錄於此,分享與大家共同學習。
  • Excel函數學習6:HLOOKUP函數
    本文來源於www.contextures.com,由完美Excel翻譯,稍有補充和修改,特輯錄於此,分享與大家共同學習。
  • Excel函數學習4:VLOOKUP函數
    本文來源於www.contextures.com,由完美Excel翻譯,稍有補充和修改,特輯錄於此,分享與大家共同學習。
  • Excel函數學習14:INDIRECT函數
    本文來源於www.contextures.com,由完美Excel翻譯,稍有補充和修改,特輯錄於此,分享與大家共同學習。
  • index+small+if函數實現一對多查詢,或許有點難,但真的很實用
    Hello,大家好,當我們使用vlookup函數查找數據遇到重複值得時候,函數僅僅會返回第一個查找到的結果,但是在日常的工作中我們經常要根據一個值來查找到多個結果,這個時候vlookup函數就不能滿足我們需求了,今天就跟大家分享下在excel中如何實現一對多查詢
  • excel函數公式大全利用if函數and函數sumif函數實現多重條件匯總
    excel函數公式大全利用if函數and函數sumif函數實現多重條件匯總,excel函數與公式在工作中使用非常的頻繁,會不會使用公式直接決定了我們的工作效率,今天我們來學習一下提高我們工作效率的函數if函數、and函數、sumif函數,利用這三種函數的組合實現對多重條件數值的匯總求和
  • EXCEL函數公式大全用SUM函數IF函數HOUR函數MINUTE函數計算加班費
    EXCEL函數公式大全之利用SUM函數、IF函數、HOUR函數與MINUTE函數的組合計算員工加班費。excel函數與公式在工作中使用非常的頻繁,會不會使用公式直接決定了我們的工作效率,今天我們來學習一下提高我們工作效率的函數SUM函數、IF函數、HOUR函數和MINUTE函數。