Excel函數學習37:SMALL函數

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

什麼情況下使用SMALL函數?

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

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

  • 求n個最小值之和

  • 返回單元格區域中的負值

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

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

  • SMALL函數語法

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

  • SMALL(array,k)

  • array: 代表要求第k個最小值點的數值數組或者數值區域。

  • k: 指定要求第幾個最小值。

  • 如果參數array中包含任何非數字值,則會忽略該值。如果包含的值均為錯誤值,則返回其中的第1個錯誤值。如果包含相等的數值,那麼會依次顯示該值。

  • 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)或者註明出處。

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

    推薦閱讀:

    Oh,NO!你竟以為Excel求和函數只有SUM?
    新手進階篇:Excel之如何通過出生年份自動生成生肖屬相
    [Excel技巧]使用數組公式
    VLOOKUP函數怎麼用?
    Excel公式與函數之美03:有趣的函數

    TAG:數學 | 學習 | 函數 | Excel | Excel函數 |