Excel 動態(tài)范圍統(tǒng)計(jì):求和

在連載的上一篇文章中,我們制作了【開始月份】和【終止月份】對應(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)擊 O3P3 的合并單元格瘩例,錄入公式:

=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厚满,前者表示邏輯 “且” 府瞄,后者表示邏輯 “或” 。

?著作權(quán)歸作者所有,轉(zhuǎn)載或內(nèi)容合作請聯(lián)系作者
  • 序言:七十年代末碘箍,一起剝皮案震驚了整個(gè)濱河市遵馆,隨后出現(xiàn)的幾起案子,更是在濱河造成了極大的恐慌丰榴,老刑警劉巖货邓,帶你破解...
    沈念sama閱讀 218,386評論 6 506
  • 序言:濱河連續(xù)發(fā)生了三起死亡事件,死亡現(xiàn)場離奇詭異四濒,居然都是意外死亡换况,警方通過查閱死者的電腦和手機(jī),發(fā)現(xiàn)死者居然都...
    沈念sama閱讀 93,142評論 3 394
  • 文/潘曉璐 我一進(jìn)店門盗蟆,熙熙樓的掌柜王于貴愁眉苦臉地迎上來戈二,“玉大人,你說我怎么就攤上這事喳资【蹩裕” “怎么了?”我有些...
    開封第一講書人閱讀 164,704評論 0 353
  • 文/不壞的土叔 我叫張陵仆邓,是天一觀的道長鲜滩。 經(jīng)常有香客問我,道長节值,這世上最難降的妖魔是什么徙硅? 我笑而不...
    開封第一講書人閱讀 58,702評論 1 294
  • 正文 為了忘掉前任,我火速辦了婚禮察署,結(jié)果婚禮上闷游,老公的妹妹穿的比我還像新娘峻汉。我一直安慰自己贴汪,他們只是感情好,可當(dāng)我...
    茶點(diǎn)故事閱讀 67,716評論 6 392
  • 文/花漫 我一把揭開白布休吠。 她就那樣靜靜地躺著扳埂,像睡著了一般。 火紅的嫁衣襯著肌膚如雪瘤礁。 梳的紋絲不亂的頭發(fā)上阳懂,一...
    開封第一講書人閱讀 51,573評論 1 305
  • 那天,我揣著相機(jī)與錄音,去河邊找鬼岩调。 笑死巷燥,一個(gè)胖子當(dāng)著我的面吹牛,可吹牛的內(nèi)容都是我干的号枕。 我是一名探鬼主播缰揪,決...
    沈念sama閱讀 40,314評論 3 418
  • 文/蒼蘭香墨 我猛地睜開眼,長吁一口氣:“原來是場噩夢啊……” “哼葱淳!你這毒婦竟也來了钝腺?” 一聲冷哼從身側(cè)響起,我...
    開封第一講書人閱讀 39,230評論 0 276
  • 序言:老撾萬榮一對情侶失蹤赞厕,失蹤者是張志新(化名)和其女友劉穎艳狐,沒想到半個(gè)月后,有當(dāng)?shù)厝嗽跇淞掷锇l(fā)現(xiàn)了一具尸體皿桑,經(jīng)...
    沈念sama閱讀 45,680評論 1 314
  • 正文 獨(dú)居荒郊野嶺守林人離奇死亡瞳浦,尸身上長有42處帶血的膿包…… 初始之章·張勛 以下內(nèi)容為張勛視角 年9月15日...
    茶點(diǎn)故事閱讀 37,873評論 3 336
  • 正文 我和宋清朗相戀三年,在試婚紗的時(shí)候發(fā)現(xiàn)自己被綠了庸推。 大學(xué)時(shí)的朋友給我發(fā)了我未婚夫和他白月光在一起吃飯的照片骡显。...
    茶點(diǎn)故事閱讀 39,991評論 1 348
  • 序言:一個(gè)原本活蹦亂跳的男人離奇死亡,死狀恐怖浆西,靈堂內(nèi)的尸體忽然破棺而出粉私,到底是詐尸還是另有隱情,我是刑警寧澤近零,帶...
    沈念sama閱讀 35,706評論 5 346
  • 正文 年R本政府宣布诺核,位于F島的核電站,受9級特大地震影響久信,放射性物質(zhì)發(fā)生泄漏窖杀。R本人自食惡果不足惜,卻給世界環(huán)境...
    茶點(diǎn)故事閱讀 41,329評論 3 330
  • 文/蒙蒙 一裙士、第九天 我趴在偏房一處隱蔽的房頂上張望入客。 院中可真熱鬧,春花似錦腿椎、人聲如沸桌硫。這莊子的主人今日做“春日...
    開封第一講書人閱讀 31,910評論 0 22
  • 文/蒼蘭香墨 我抬頭看了看天上的太陽铆隘。三九已至,卻和暖如春南用,著一層夾襖步出監(jiān)牢的瞬間膀钠,已是汗流浹背掏湾。 一陣腳步聲響...
    開封第一講書人閱讀 33,038評論 1 270
  • 我被黑心中介騙來泰國打工, 沒想到剛下飛機(jī)就差點(diǎn)兒被人妖公主榨干…… 1. 我叫王不留肿嘲,地道東北人融击。 一個(gè)月前我還...
    沈念sama閱讀 48,158評論 3 370
  • 正文 我出身青樓,卻偏偏與公主長得像雳窟,于是被迫代替她去往敵國和親砚嘴。 傳聞我的和親對象是個(gè)殘疾皇子,可洞房花燭夜當(dāng)晚...
    茶點(diǎn)故事閱讀 44,941評論 2 355

推薦閱讀更多精彩內(nèi)容

  • 一.日期函數(shù) 1.day: 功能:返回一個(gè)月中的第幾天的數(shù)值涩拙,介于1到31之間际长。 語法:day(serial_nu...
    我在人間撿垃圾閱讀 1,347評論 0 0
  • 項(xiàng)目地址:https://github.com/datawhalechina/free-excel[https:/...
    小黃筆記本閱讀 418評論 0 0
  • 文本處理 1、TRIM(text):用于清除文本兩邊的空格兴泥; 2工育、CONCATENATE(text1, [text...
    挺子閱讀 613評論 0 2
  • 第1章 換個(gè)角度玩 Excel 就研究兩件事:一,設(shè)計(jì)一個(gè)標(biāo)準(zhǔn)搓彻、正確的源數(shù)據(jù)表如绸;二,“變”出N個(gè)分類匯總表旭贬。好的源...
    巴喬書摘閱讀 813評論 0 8
  • 基本知識 運(yùn)算符 : :范圍運(yùn)算符,對于一個(gè)區(qū)域時(shí)使用 , :單元格區(qū)域并集運(yùn)算符. :(單一空格)單元格區(qū)域交集...
    你說我對錢一往情深閱讀 1,086評論 0 0