Excel里動(dòng)態(tài)變化的累計(jì)求和，你會(huì)嗎？

?

作者：花花來源：部落窩教育發(fā)布時(shí)間：2022-04-06 17:52:16點(diǎn)擊：6553

分享到:

收藏收藏人氣：0人

版權(quán)說明：原創(chuàng)作品，禁止轉(zhuǎn)載。

編按：

哈嘍，小伙伴們，大家好！今天要跟大家分享一個(gè)特殊的累計(jì)求和案例，即自動(dòng)計(jì)算動(dòng)態(tài)月份的累計(jì)求和，趕快一起來看看吧！

讀者群中有位小伙伴提出想用公式計(jì)算月份累計(jì)求和，問題是需要根據(jù)J3單元格中會(huì)動(dòng)態(tài)變化的單元格值來實(shí)現(xiàn)自動(dòng)計(jì)算動(dòng)態(tài)月份的累積數(shù)據(jù)和，結(jié)果返回在J4單元格中。問題如下圖所示：

小伙伴期望實(shí)現(xiàn)的效果圖示如下：

接著我們來講解一下是如何實(shí)現(xiàn)這種動(dòng)態(tài)區(qū)域求和的，通俗簡(jiǎn)單一點(diǎn)的回答就是在J4單元格中輸入公式=SUM(INDIRECT("$E$7:"&ADDRESS(7,MATCH(SUBSTITUTE(J3,"累計(jì)",""),E6:P6,0)+4)))回車確定即可得到結(jié)果。

其實(shí)別看剛剛的公式那么長(zhǎng)一串，實(shí)際計(jì)算的結(jié)果其實(shí)和=SUM(E7:G7)相等。（前提是J4單元格的值等于“3月累計(jì)”）

為了實(shí)現(xiàn)動(dòng)態(tài)區(qū)域求和，我們把公式進(jìn)行了拆分標(biāo)注解釋，以=SUM(E7:G7)公式為例，我們知道變量在“E7:G7”這個(gè)區(qū)域上，所以我們開始對(duì)公式中的區(qū)域下手了，分別使用SUBSTITUTE、MATCH、ADDRESS、INDIRECT四大函數(shù)來變身動(dòng)態(tài)區(qū)域，因?yàn)?span>SUM函數(shù)的區(qū)域需要根據(jù)下圖J3單元格的內(nèi)容動(dòng)態(tài)變化進(jìn)行求和，所以我們需要通過①②③④的嵌套函數(shù)公式讓區(qū)域變成根據(jù)條件動(dòng)態(tài)更新，具體參考下圖右下角函數(shù)語(yǔ)法解釋理解。

下面我們分段拆解一下嵌套函數(shù)中每個(gè)函數(shù)在公式中所起的用途，如果有不理解函數(shù)語(yǔ)法意思的小伙伴記得回來看上圖解釋。

SUBSTITUTE替換文本函數(shù)是將J3單元格中“累計(jì)”文本替換為空，這樣我們替換完成后的值可以在第6行中的月份字段進(jìn)行匹配。

如果這里我們不想使用SUBSTITUTE函數(shù)來替換，其實(shí)可以使用之前跟大家分享過的自定義單元格格式的方法。我們輸入月份后，設(shè)置自定義單元格格式，自定義類型中輸入“@”月份””確定即可。這樣顯示的是帶累計(jì)的內(nèi)容，實(shí)際內(nèi)容編輯欄只有月份，這個(gè)方法在公式提取單元格內(nèi)容和批量給數(shù)據(jù)添加單位時(shí)經(jīng)常用到。

MATCH函數(shù)查找目標(biāo)值所在區(qū)域中的位置，而我們需要查找的目標(biāo)值就是SUBSTITUTE函數(shù)替換后的月份值即下圖的R5單元格中的內(nèi)容“3月”返回查找區(qū)域E6:P6中所在的位置。

ADDRESS函數(shù)是返回單元格地址，這里我們只簡(jiǎn)單的用到兩個(gè)參數(shù)，已知第一參數(shù)是數(shù)據(jù)對(duì)應(yīng)的第7行，第二參數(shù)返回列數(shù)因?yàn)槲覀兊臄?shù)據(jù)是從E列開始，所以要加上前4列最后加上MATCH值所在位置就是我們需要查找的單元格位置$G$7。

最后就是用INDIRECT返回引用區(qū)域，因?yàn)閰^(qū)域中的起始位置$E$7單元格是固定不變的，所以我們可以對(duì)E7單元格進(jìn)行絕對(duì)引用固定，后面&就是R7單元格中的$G$7。當(dāng)我們更改J3單元格中的內(nèi)容時(shí)對(duì)應(yīng)的R8單元格中值就會(huì)動(dòng)態(tài)求和顯示。