編按：哈嘍，大家好！今天我們要說說，如何在excel中，統(tǒng)計(jì)前幾名數(shù)據(jù)的合計(jì)。這個(gè)問題難倒了不少小伙伴，尤其是遇到數(shù)據(jù)是雜亂無(wú)序的情況，那更是要了老命。不過，這對(duì)于excel大神來(lái)說，還是非常簡(jiǎn)單的，分分鐘列出一個(gè)公式，就完美解決了問題！今天我們就一起來(lái)破解一下面對(duì)這類問題時(shí)，大神都是怎么做的吧！

＊＊＊＊＊＊＊＊＊

什么叫做統(tǒng)計(jì)前幾名合計(jì)呢？先看看動(dòng)畫演示吧：

可以看到，數(shù)據(jù)按照銷售數(shù)量從高往低排列，選擇一個(gè)名次，就可以對(duì)前幾名的銷售數(shù)量求和。

這類問題在很多與數(shù)字排名有關(guān)的情況下都用得上，例如對(duì)前幾名考試成績(jī)求和，對(duì)前幾名的客戶訂貨量求和等等……

要實(shí)現(xiàn)動(dòng)畫1的這種求和非常容易，使用SUMIF函數(shù)就能得到結(jié)果，公式為：=SUMIF(D:D,"<="&H2,F:F)

簡(jiǎn)單說一下這個(gè)公式吧，SUMIF(條件區(qū)域，條件，求和區(qū)域)，關(guān)鍵是這里的條件，"<="&H2表示對(duì)序號(hào)這一列大于等于H2單元格數(shù)字的銷售數(shù)量求和。因?yàn)闂l件中同時(shí)存在比較符號(hào)<=和單元格引用H2，所以需要&符號(hào)進(jìn)行連接。

不過今天要說的重點(diǎn)不是SUMIF，因?yàn)榻酉聛?lái)遇到的數(shù)據(jù)并不是這么規(guī)范的從高往低排序，而是雜亂無(wú)序的一列數(shù)字。

如圖所示，AB兩列才是原始的數(shù)據(jù)源，使用公式=SUM(LARGE(B:B,ROW(INDIRECT("1:"&H2))))可以得到與文章開頭所示的SUMIF公式相同的結(jié)果。

我們今天的目的就是要搞清楚這個(gè)看起來(lái)有點(diǎn)奇怪的函數(shù)組合：SUM+LARGE+ROW+INDIRECT套路

在這個(gè)組合中，前三個(gè)函數(shù)大家并不陌生，SUM是求和函數(shù)，LARGE是獲取第幾個(gè)最大值的函數(shù)，ROW是得到行號(hào)的函數(shù)，唯一比較陌生或者說不太好理解的就是INDIRECT函數(shù)了。

下面結(jié)合示例來(lái)解釋這個(gè)公式的原理。

其實(shí)這個(gè)公式的核心部分是SUM和LARGE，例如需要對(duì)前三名求和，公式就是=SUM(LARGE(B2:B24,{1,2,3}))。

重點(diǎn)需要理解LARGE(B2:B24,{1,2,3})這部分，LARGE(數(shù)據(jù)區(qū)域,第幾名)，如果第二參數(shù)“第幾名”只是一個(gè)數(shù)字，就表示指定的這個(gè)名次，如果是多個(gè)數(shù)字，就表示每個(gè)數(shù)字所對(duì)應(yīng)的名次，因此公式LARGE(B2:B24,{1,2,3})會(huì)得到三個(gè)數(shù)，這一點(diǎn)我們可以使用F9功能鍵來(lái)理解，在編輯欄選中公式的這部分：

然后按一下F9鍵，就能看到LARGE得到的三個(gè)數(shù)字：

由SUM函數(shù)對(duì)這三個(gè)數(shù)字求和，就得到了前三名的合計(jì)。

在這種用法中，LARGE的第二參數(shù)使用了常量數(shù)組，也就是將多個(gè)值放在一組大括號(hào)中。

使用常量數(shù)組有一個(gè)弊端，例如要對(duì)前10名求和，就要將LARGE(B2:B24,{1,2,3})改成LARGE(B2:B24,{1,2,3,4,5,6,7,8,9,10})。如果要對(duì)前20名求和，公式就更啰嗦，因此就有了另一種寫法，將常量數(shù)組改為ROW函數(shù)來(lái)實(shí)現(xiàn)，還是以前三名為例進(jìn)行說明。

公式可以寫為=SUM(LARGE(B2:B24,ROW(1:3)))

這個(gè)公式需要同時(shí)按下CTRL+SHIFT+回車鍵完成輸入，公式兩邊的大括號(hào)是自動(dòng)生成的，并不是手工輸入的。

可以自己試試，選中ROW(1:3)，使用F9鍵，可以得到與{1,2,3}相同的結(jié)果。對(duì)于多個(gè)常量的話，使用ROW就更方便了，例如前20名，公式就是=SUM(LARGE(B2:B24,ROW(1:20)))，只需要修改ROW函數(shù)中的結(jié)束行號(hào)就可以。

可以看到，使用了ROW函數(shù)后，公式的可擴(kuò)展性要比使用常量數(shù)組方便很多，不過對(duì)于要計(jì)算的名次不是固定值的時(shí)候，公式就做不到了，或許你會(huì)認(rèn)為這樣的公式能達(dá)到效果：=SUM(LARGE(B2:B24,ROW(1:H2)))

但如果你真的使用的話，會(huì)得到這樣的回應(yīng)：

表面上看不出有什么問題，其實(shí)問題就出在ROW(1:H2)這部分。

ROW函數(shù)用法雖然簡(jiǎn)單，但是要求比較嚴(yán)格，參數(shù)只能使用單元格或單元格區(qū)域，也就是一個(gè)有效的引用。

1:H2顯然不滿足這個(gè)要求。

怎么辦？

這就需要INDIRECT這個(gè)非常獨(dú)特的函數(shù)出馬了。

其實(shí)公式在一開始就有了，寫成ROW(INDIRECT("1:"&H2))公式就沒有問題，至于為什么，咱們下篇接著聊。

＊＊＊＊部落窩教育－excel排名數(shù)據(jù)合計(jì)＊＊＊＊

原創(chuàng)：老菜鳥/部落窩教育（未經(jīng)同意，請(qǐng)勿轉(zhuǎn)載）

更多教程：部落窩教育（www.itblw.com）

微信公眾號(hào)：exceljiaocheng