編按：

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

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

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

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

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

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

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

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

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

在這個(gè)組合中，前三個(gè)函數(shù)大家并不陌生，SUM是求和函數(shù)，LARGE是獲取第幾個(gè)最大值的函數(shù)，ROW是得到行號(hào)的函數(shù)，唯一比較陌生或者說(shuō)不太好理解的就是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名求和，公式就更啰嗦，因此就有了另一種寫(xiě)法，將常量數(shù)組改為ROW函數(shù)來(lái)實(shí)現(xiàn)，還是以前三名為例進(jìn)行說(shuō)明。

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

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

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

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

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

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

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

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

怎么辦？

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

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

本文配套的練習(xí)課件請(qǐng)加入QQ群：747953401下載。

做Excel高手，快速提升工作效率，部落窩教育《一周Excel直通車(chē)》視頻和《Excel極速貫通班》直播課全心為你！

掃下方二維碼關(guān)注公眾號(hào)，可隨時(shí)隨地學(xué)習(xí)Excel：

相關(guān)推薦：

萬(wàn)金油公式《Excel萬(wàn)金油公式INDEX-SMALL-IF-ROW篩選函數(shù)公式解讀》

SUM函數(shù)的特殊用法《SUM函數(shù)特殊應(yīng)用：序號(hào)填充、提成比例計(jì)算、打印標(biāo)簽制作》

SUMIF函數(shù)《無(wú)往而不利的SUMIF面對(duì)這種條件求和竟然傻眼了！》