Excel返回最大M個數或最小N個數之和
在一組數據中,前提條件是不準排序,那麼如何求得最大的M個數之和或者是最小的N個數之和呢?
首先我們看看下錶,以圖代講。
如上圖,A1:B8這個範圍,是數據範圍,數據沒有規律,又不能排序,現在,擺在我們眼前的問題是,如果求得該範圍中的數據的最大3個數之和、最小3個數之和?
如果要口算,通過觀察,不難發現,最大的三個數分別是:100 100 99,那麼,最大三個數之和則為299;而最小三個數分別為:9 12 33,此三數之和則為:54
現在,我們要做的不是口算,而是要通過公式來解決問題,以不變應萬變,這樣遇到很多很多的數據的時候,我們才能在最短的時間內準確的計算出來。
所使用的方法,當然,離不開函數之間的組合使用。
一、最大三個數之和
看下圖的公式吧:公式是:=SUM(LARGE(A1:B8,ROW(A1:A3)))
這個時候你得注意,上圖的公式為數組公式,應該在=SUM(LARGE(A1:B8,ROW(A1:A3)))基礎上,讓公式在此基礎上,帶一對大括弧,輸入的方法為:先輸入公式:=SUM(LARGE(A1:B8,ROW(A1:A3)))後接著按下組合鍵Ctrl+Shift+Enter,這樣,大括弧就自動輸入了。注意是自動輸入,不是手動輸入,手動輸入的是無效的。
二、最小三個數之和
有了如上的基礎,我們要求最小三個數之和就簡單了。
公式為:=SUM(SMALL(A1:B8,ROW(A1:A3))),記得將其變為數組公式即可。
最後,我們為大家講解一下公式的含義:
知識擴展:
Sum是求和函數,而LARGE是求最大的第N個數的函數,SMALL是求最小的第N個數的函數。最後的函數ROW是返回行序號的函數。
在這裡,最難理解的就是ROW(A1:A3)這個地方了,這個地方返回的是一個數組,不是單個值,這裡的ROW(A1:A3))),相當於數組:{1,2,3},因此,上述的公式:
=SUM(SMALL(A1:B8,ROW(A1:A3))),該公式可使用如下的兩種公式代替,重點給大家講解第二種:
第一種:=SUM(SMALL(A1:B8,ROW(1:3)))
第二種:=SUM(SMALL(A1:B8,{1,2,3})),這裡的1,2,3代表三個。在整個公式中,代表求最大三個數之和。注意,此時的公式輸入好之後,不需要按下Ctrl+Shift+Enter。
至於在這裡,為什麼要使用ROW(A1:A3)而不使用{1,2,3}的原因就在於,前者靈活,後者不靈活,比如,要你求最大6個數之和,你就得構造{1,2,3,4,5,6}這樣的數組,萬一要求最大100個之和,你就不好構造數組數據了,如果使用後者,則可以簡單地寫為:ROW(1:100)即可。當然,也可寫為:ROW(A1:A100)
推薦閱讀:
※Excel一對多查找經典公式解讀
※你所不知道關於Excel日期格式大變身的秘密
※Excel日期公式的那些事,如何計算兩個日期相差年月日的問題
※[Excel小課堂] LOOKUP三兄弟,你都了解些啥?
※Excel數組簡介
TAG:Excel |