在連載的上一篇文章中,我們制作了【開始月份】和【終止月份】對應(yīng)的下拉菜單。本節(jié),小魚將根據(jù)起止月份進(jìn)行業(yè)績求和(見圖注 ①)蹂匹,同時(shí)將求和的范圍在表格中表示出來(見圖注 ②)。
下面凹蜈,我們就來開始今天的學(xué)習(xí)限寞!
一忍啸、動態(tài)范圍求和
動態(tài)范圍求和:用戶自主選擇起點(diǎn)、終點(diǎn)昆烁,求和范圍隨之變化吊骤。
1.1 定義名稱【終止月份】
由于求和的范圍會根據(jù)我們在下拉菜單中選擇的【起始月份】和【終止月份】而發(fā)生變化,因此使用 SUM 函數(shù)進(jìn)行求和時(shí)静尼,關(guān)鍵的步驟也就落到了【動態(tài)范圍引用】白粉。
獲取了動態(tài)范圍,直接作為參數(shù)傳入 SUM 求和即可鼠渺。通過上一篇文章的學(xué)習(xí)鸭巴,我們知道只要是動態(tài)范圍,我們要馬上想到 OFFSET + MATCH 函數(shù)拦盹。
在連載的上一篇文章中鹃祖,我們使用 MATCH 函數(shù)計(jì)算起始月份在月份標(biāo)題行中的位置,并將該公式定義成了名稱:
為了確定求和的范圍普舆,我們自然也需要確定【終止月份】的位置恬口,點(diǎn)擊上述窗口中的【新建】或者點(diǎn)擊【公式 - 定義名稱】,我們來定義【終止月份】的位置計(jì)算公式沼侣。
輸入名稱為【終止月份】祖能,在引用位置輸入框中,我們直接錄入公式 =MATCH(
蛾洛,點(diǎn)擊 P2
單元格錄入查找值养铸,填寫 ,
,繼續(xù)選擇 C2:N2
的區(qū)域以錄入查找區(qū)域轧膘,最后錄入 ,0)
钞螟。
溫馨提示:所有的標(biāo)點(diǎn)符號均應(yīng)在英文狀態(tài)下錄入;點(diǎn)擊拖拽【新建名稱】窗口的右下角谎碍,可以進(jìn)行窗口縮放鳞滨。
接下來,我們用定義的名稱【起始月份】和【終止月份】完成求和區(qū)域的動態(tài)引用蟆淀。
1.2 動態(tài)引用求和區(qū)域
提到動態(tài)范圍引用太援,除了 MATCH 函數(shù),還要立刻想到 OFFSET 函數(shù)扳碍。我們先來回顧一下 OFFSET 函數(shù)表達(dá)式:
=OFFSET(參考單元格, 行數(shù), 列數(shù), 高度, 寬度)
下面提岔,我們假設(shè)選定的月份為 4月~7月 ,則對于產(chǎn)品 a1 計(jì)算全年銷售額的求和范圍就為 F3:I3
的區(qū)域笋敞,如下圖黃色框所示:
參考單元格選定為 C3
碱蒙,則定位區(qū)域 F3:I3
只需要向右偏移 3
列,即起始月份 - 1:4-1=3
。引用的區(qū)域高度為 1
行赛惩,寬度為 4
列哀墓,其中高度是固定的,寬度為終止月份 - 起始月份 + 1:7-4+=3
喷兼。
溫馨提示:上述引用的【起始月份】和【終止月份】均為前面定義的名稱篮绰,表示起始月份/終止月份在月份標(biāo)題行中的位置。
下面季惯,我們在 Q3
單元格錄入 OFFSET
公式以驗(yàn)證參數(shù)是否正確吠各。點(diǎn)擊 Q3
單元格,錄入如下公式:
=OFFSET(C3,0,起始月份-1,1,終止月份-起始月份+1)
其中勉抓,參考單元格為 C3
贾漏,偏倚的行數(shù)為 0
,即上下不需要移動藕筋;向右偏移的行數(shù)為 起始月份-1
纵散,引用區(qū)域的高度為 1
行,寬度為 終止月份-起始月份+1
隐圾。
使用自動填充功能伍掀,完成所有產(chǎn)品的求和范圍計(jì)算,通過核對暇藏,引用區(qū)域符合我們的預(yù)期:均為各產(chǎn)品 4~7 月份的銷售額蜜笤。
最后,我們只需要將 OFFSET 函數(shù)返回的引用區(qū)域傳遞給 SUM
函數(shù)求和就可以啦叨咖。點(diǎn)擊 O3
和 P3
的合并單元格瘩例,錄入公式:
=SUM(OFFSET(C3,0,起始月份-1,1,終止月份-起始月份+1))
回車之后啊胶,使用自動填充計(jì)算剩余產(chǎn)品的銷售額統(tǒng)計(jì):
至此甸各,我們已經(jīng)完成了動態(tài)范圍求和,不過由于左側(cè)數(shù)據(jù)比較多焰坪,我們很難一眼看出來是哪幾列數(shù)據(jù)相加趣倾。為了進(jìn)一步提升報(bào)表的可讀性,我們將使用條件格式對求和所引用的區(qū)域進(jìn)行顏色標(biāo)注某饰。
二儒恋、條件格式標(biāo)注求和范圍
條件格式:可以根據(jù)條件自動變化的智能格式。
2.1 選中條件格式設(shè)置區(qū)域
點(diǎn)擊 C3
單元格黔漂,按住 SHIFT
鍵點(diǎn)擊 N22
單元格诫尽,即可選中 C3:N22
的區(qū)域。
我們將為該區(qū)域設(shè)置條件格式炬守,點(diǎn)擊【開始】選項(xiàng)卡中的【條件格式 - 新建規(guī)則】選項(xiàng)牧嫉。
彈出【新建規(guī)則】對話框,我們將在此對話框中,完成條件格式的設(shè)置:
- 使用公式設(shè)置規(guī)則
- 設(shè)置格式
2.2 使用公式設(shè)置規(guī)則
首先酣藻,選擇規(guī)則類型:【使用公式確定要設(shè)置格式的單元格】曹洽。
在設(shè)置條件格式中,我們經(jīng)常用到的兩個(gè)函數(shù)為:COLUMN()
ROW()
辽剧,前者用來返回公式所在單元格的列號送淆,后者返回行號。若使用參數(shù)指定了單元格怕轿,則返回該單元格的列(行)號偷崩。
此處,我們需要為選定區(qū)域中撤卢,位于求和范圍內(nèi)的列設(shè)置格式环凿,因此需要用到 COLUMN
函數(shù)。我們不妨先在四月和七月所在的列錄入 =COLUMN()
放吩,觀察一下返回值:
如上智听,四月和七月所在的列位于報(bào)表中的第 6
列和第 9
列。接下來渡紫,我們在其下方分別錄入上文定義的名稱 =起始月份
和 =終止月份
:
我們發(fā)現(xiàn)到推,用于動態(tài)范圍求和的【起始月份】和【終止月份】加 2
,就得到了我們需要標(biāo)注的范圍:列號位于【起始月份 + 2】~【終止月份 + 2】的區(qū)域惕澎。
用公式來表述就是:
COLUMN()>=起始月份+2 且 COLUMN()<=終止月份+2
將上述兩個(gè)條件放到邏輯函數(shù) AND 中莉测,即可實(shí)現(xiàn)并且的邏輯關(guān)系:
=AND(COLUMN()>=起始月份+2,COLUMN()<=終止月份+2)
為了驗(yàn)證公式的正確性,小魚在報(bào)表下方的 C26
單元格錄入該公式唧喉,使用自動填充捣卤,填充至 N26
單元格的位置。通過對照發(fā)現(xiàn)八孝,公式返回 TRUE
的單元格正好對應(yīng)的就是求和區(qū)域 5~7 月:
這樣就可以放心地將公式粘貼到格式規(guī)則的公式輸入框了:
接下來董朝,點(diǎn)擊【格式】按鈕,我們來為符合公式的單元格設(shè)置格式干跛。
2.3 設(shè)置格式
在彈出的【設(shè)置單元格格式】對話框中子姜,選擇【填充】選項(xiàng):
點(diǎn)擊【其他顏色】,選擇一個(gè)和我們的主題顏色接近的淺色:
點(diǎn)擊【確定】之后楼入,我們求和的動態(tài)范圍就被標(biāo)注出來了哥捕。
當(dāng)起始月份發(fā)生變化時(shí),不僅動態(tài)完成了求和統(tǒng)計(jì)嘉熊,而且求和的范圍也會被標(biāo)注出來遥赚,讓報(bào)表閱讀者對求和的過程一目了然。
以上就是 Excel 動態(tài)范圍求和的全部內(nèi)容啦~
三阐肤、總結(jié)
動態(tài)范圍求和:和動態(tài)范圍有關(guān)的問題凫佛,我們應(yīng)想到使用 OFFSET 函數(shù)和 MATCH 函數(shù)動態(tài)獲取數(shù)據(jù)引用,再使用 SUM 等統(tǒng)計(jì)函數(shù)完成最終的計(jì)算。
條件格式自動標(biāo)注選定范圍:條件格式即根據(jù)條件自動進(jìn)行格式設(shè)置御蒲,在使用公式設(shè)置條件格式時(shí)衣赶,經(jīng)常會用到 ROW 和 COLUMN 兩個(gè)函數(shù)。
邏輯函數(shù):表示多個(gè)條件之間的邏輯關(guān)系有兩個(gè)函數(shù) AND 和 OR厚满,前者表示邏輯 “且” 府瞄,后者表示邏輯 “或” 。