Q：拉登老師，有個(gè)動(dòng)態(tài)求和效果，你幫忙看看怎么實(shí)現(xiàn)的？

先看下數(shù)據(jù)：

第 1 頁(yè)，是銷售總和匯總，后面是每天的銷售統(tǒng)計(jì)。

第 1 頁(yè)整體的匯總倒是沒什么特別。

有意思的是，移動(dòng)「開始」和「結(jié)束」頁(yè)，前面的匯總數(shù)據(jù)就變了，自動(dòng)計(jì)算「開始」和「結(jié)束」的銷售總和，這是怎么回事？

跨工作表求和

這里考了一個(gè)知識(shí)點(diǎn)：單元格引用的設(shè)置。

在單元格引用當(dāng)中，冒號(hào)指的是起始和結(jié)束單元格位置。

這個(gè)位置當(dāng)中，可以包含工作表的名稱，也可以單元格的地址。

有了起始和結(jié)束位置，中間所有的區(qū)域的都會(huì)被算進(jìn)來(lái)求和。

所以這個(gè)公式當(dāng)中，「開始」和「結(jié)束」代表的就是把這兩個(gè)工作表中間的所有工作表，全部都進(jìn)行求和。

所以，當(dāng)我們移動(dòng)開始和結(jié)束工作表標(biāo)簽的時(shí)候，中間位置發(fā)生了變化，那么公式也會(huì)動(dòng)態(tài)的更新。

對(duì)于動(dòng)態(tài)求和還有幾種方法，給大家再普及一下。

區(qū)域自動(dòng)求和

表格新增數(shù)據(jù)的時(shí)候，動(dòng)態(tài)求和，這是比較普遍的一個(gè)需求。

SUM 函數(shù)求和的區(qū)域往往都是固定的，「怎樣把它構(gòu)建成一個(gè)動(dòng)態(tài)的區(qū)域呢？」

這里需要借助另外 2 個(gè)函數(shù)叫做：OFFSET 和 COUNTA。

上圖效果對(duì)應(yīng)的公式是這樣的：

=SUM(OFFSET(B3,,,COUNTA(B3:B27)))

公式復(fù)雜了一點(diǎn)，我們從內(nèi)往外拆解公式。

? COUNT 函數(shù)。

首先用 COUNT 函數(shù)，統(tǒng)計(jì)要求和的單元格數(shù)量：

= COUNTA(B3:B27)

? OFFSET 函數(shù)。

然后用 OFFSET 函數(shù)，動(dòng)態(tài)更新求和區(qū)域：

=OFFSET(B3,,,COUNTA(B3:B27))

OFFSET 的幾個(gè)參數(shù)含義如下：

? 參數(shù) 1：選區(qū)的起始位置，這里寫的是 B3；

? 參數(shù) 2：要向下偏移幾行，不偏移就寫 0 或空著；

? 參數(shù) 3：要向右偏移幾列，不偏移就寫 0 或空著；

? 參數(shù) 4：選區(qū)包含幾行，這里寫的是 COUNTA 計(jì)算出來(lái)的行數(shù)；

? 參數(shù) 5：選區(qū)要包含幾列，不寫就是和參數(shù) 1 一致。

? SUM 函數(shù)。

最后，外面套上 SUM 函數(shù)進(jìn)行求和：

=SUM(OFFSET(B3,,,COUNTA(B3:B27)))

如果你覺得這個(gè)公式太復(fù)雜，學(xué)不會(huì)！

想找更簡(jiǎn)單的方法？

很好，因?yàn)橥祽惺侨祟愡M(jìn)步的動(dòng)力。

這個(gè)時(shí)候，你需要知道另外一個(gè)很好用的功能叫做：「智能表格」。

來(lái)看一下效果：

非常簡(jiǎn)單，把數(shù)據(jù)轉(zhuǎn)成智能表格。

新增數(shù)據(jù)的時(shí)候，智能表格會(huì)自動(dòng)擴(kuò)展區(qū)域（看樣式就看出來(lái)了），SUM 函數(shù)的求和區(qū)也會(huì)自動(dòng)拓展。

整個(gè)過(guò)程不需要外加任何的函數(shù)，就可以實(shí)現(xiàn)動(dòng)態(tài)求和。

篩選自動(dòng)求和

還有一種情況，是篩選或者隱藏?cái)?shù)據(jù)之后，只對(duì)可見的單元格求和。

這顯然已經(jīng)完全超出了 SUM 能力，這個(gè)時(shí)候請(qǐng)出 SUM 的大表哥「SUBTOTAL」來(lái)實(shí)現(xiàn)這個(gè)效果啦！

上面的效果，對(duì)應(yīng) SUBTOTAL 的公式如下：

=SUBTOTAL(9,C3:C12)

結(jié)合這個(gè)公式，我們看一下 SUBTOTAL 的參數(shù)用法：

? 參數(shù) 1：計(jì)算的方式，用不同的數(shù)字代表不同的計(jì)算方式。公式中選擇的 9 - SUM。

? 參數(shù) 2：計(jì)算的區(qū)域。即公式中的 C3:C12。

這里的關(guān)鍵是參數(shù) 1，有很多的選項(xiàng)：

按照 Office 官方幫助解釋是這樣的：

數(shù)字 1-11 或 101-111，用于指定要為分類匯總使用的函數(shù)。

如果使用 1-11，將包括手動(dòng)隱藏的行；

如果使用 101-111，則排除手動(dòng)隱藏的行；始終排除已篩選掉的單元格。

不管了，你只要記住求和用 9 或者 109 就夠了。

有了前面一次偷懶的經(jīng)驗(yàn)，我猜你肯定在想更簡(jiǎn)單的方法吧？

沒錯(cuò)，方法是有的，還是用「智能表格」，輕松地解決這個(gè)問(wèn)題。

把區(qū)域轉(zhuǎn)成「智能表格」，在「設(shè)計(jì)」選項(xiàng)卡里面勾選「匯總行」：

注意！一定要注意！

睜開你的雙眼，這個(gè)時(shí)候奇跡發(fā)生了！

表格中自動(dòng)出現(xiàn)一個(gè)匯總行，每個(gè)單元格都會(huì)有一個(gè)下拉菜單，點(diǎn)擊就可以選擇匯總的方式，包括求和。

而這個(gè)求和默認(rèn)就是對(duì)可見單元格求和。

整個(gè)過(guò)程，我們只是鼠標(biāo)點(diǎn)了兩下，選擇了求和，就這么簡(jiǎn)單。

條件求和

還有一種自動(dòng)求和的方式，叫做：「條件求和」。

比如，現(xiàn)在我們只希望針對(duì)下面的正數(shù)求和。

求你把計(jì)算器拿遠(yuǎn)一點(diǎn)，行嗎？

「智能表格」也幫不上忙。

要用 SUMIF 函數(shù)，薩姆姨夫，懂嗎？直接看效果：

對(duì)應(yīng)公式如下：

=SUMIF(B2:B13,">0")

SUMIF 的作用，就是根據(jù)條件進(jìn)行求和，它有這么幾個(gè)參數(shù)：

? 參數(shù) 1：要求和的區(qū)域。

? 參數(shù) 2：求和的條件，這里寫的是「>0」，即大于 0 的數(shù)字才求和。

簡(jiǎn)單的不得了。

而且數(shù)據(jù)變化后，求和結(jié)果也自動(dòng)更新。

總結(jié)一下

好了，簡(jiǎn)單總結(jié)一下今天的自動(dòng)求和知識(shí)點(diǎn)。

? 跨表自動(dòng)求和，「SUM + 單元格引用」；

? 擴(kuò)展區(qū)域求和，「SUM+OFFSET」；

? 隱藏區(qū)域求和，「SUBTOTAL」；

? 根據(jù)條件求和，「SUMIF」。

什么是高手，高手就是可以把簡(jiǎn)單的事情做到極致；

可以吃個(gè)泡面，都能吃出 230 萬(wàn)的播放量！

刻意練習(xí)嘛！

?? 考考你：
好啦，那么最后考考你。

要計(jì)算每個(gè)產(chǎn)品的營(yíng)業(yè)額，應(yīng)該用什么函數(shù)呢？

評(píng)論區(qū)等你的答案！

說(shuō)到底，Excel 技巧就是個(gè)無(wú)底洞，10 年也學(xué)不完。

相比之下，在業(yè)務(wù)場(chǎng)景中，去恰到好處地運(yùn)用 Excel 技術(shù)，才能四兩撥千斤。

兩張表格，找出其中重復(fù)和缺失的數(shù)據(jù)，你要多久？

給一個(gè)編號(hào)，把所有相關(guān)信息列出來(lái)，你要用多久？

按照不同的業(yè)績(jī)級(jí)別，按不同比例計(jì)算獎(jiǎng)金，你要多久？

收集來(lái)的數(shù)據(jù)亂七八糟，全部整理好，你又要用多久？

如果你只會(huì)靠肉眼，拼手工，可以預(yù)見，擺在你面前的只有一條路。

職場(chǎng)過(guò)來(lái)人都知道 Excel 有多重要，那怎么提高 Excel 技能水平？

小 E 推薦你學(xué)習(xí)《3 天 Excel 集訓(xùn)營(yíng)》課程！

本文作者拉登老師會(huì)在這門課程里，助你全面、快速提升?Excel 技能，消滅工作中常見的效率黑洞！

上課方式為錄播+直播，手機(jī)電腦，隨時(shí)隨地都能學(xué)；還有專屬學(xué)員群、社群答疑服務(wù)！

3 天時(shí)間，每天 30 分鐘左右，你也有可能成為 Excel 高手！

《3 天 Excel 集訓(xùn)營(yíng)》

原價(jià)?99?

現(xiàn)在只需 1 元

掃碼報(bào)名還送

35 個(gè)函數(shù)說(shuō)明手冊(cè)

??????

*廣告