編按：

哈嘍，大家好！今天向大家分享一個(gè)銷售統(tǒng)計(jì)表模板。該模板支持動(dòng)態(tài)查詢功能，并且在查詢的時(shí)候，相應(yīng)數(shù)據(jù)會(huì)變色，如此，查詢結(jié)果一目了然。統(tǒng)計(jì)模板將使用sum、and、column、match、offset函數(shù)并結(jié)合條件格式和數(shù)據(jù)驗(yàn)證。趕緊來(lái)看看吧！

今天要和大家分享的是一個(gè)可以用顏色指示查詢區(qū)域和結(jié)果的動(dòng)態(tài)查詢銷售數(shù)據(jù)的統(tǒng)計(jì)模板。何為動(dòng)態(tài)查詢呢，效果如動(dòng)圖所示：

要做這個(gè)模板，需要兩部分工作，公式和條件格式。

公式用來(lái)實(shí)現(xiàn)銷售數(shù)據(jù)匯總，條件格式用來(lái)改變單元格顏色突出求和的數(shù)字區(qū)域。

但是在這之前，先要設(shè)置三個(gè)數(shù)據(jù)驗(yàn)證，分別是查詢區(qū)域、開始月和結(jié)束月，以下分別說(shuō)明。

1.查詢區(qū)域的設(shè)置

這是數(shù)據(jù)驗(yàn)證最基本的用法之一，在【允許】欄選擇序列，【來(lái)源】里選擇對(duì)應(yīng)的單元格區(qū)域即可，操作步驟見動(dòng)圖演示。

2.開始月的設(shè)置

與前一項(xiàng)不同，開始月設(shè)置為只能輸入1到12之間的整數(shù)，并且設(shè)置提示信息，操作步驟見動(dòng)圖演示。

3.結(jié)束月的設(shè)置

與開始月的設(shè)置方法基本一致，只是需要將最小值設(shè)置為開始月所在的單元格，操作步驟見動(dòng)圖演示。

完成以上三個(gè)設(shè)置之后，首先來(lái)制作銷量合計(jì)的計(jì)算公式。

要實(shí)現(xiàn)按照查詢區(qū)域、開始月和結(jié)束月這三個(gè)條件進(jìn)行合計(jì)的公式思路不是唯一的，這次我們使用比較常用的SUM-OFFSET函數(shù)組合，公式為：

=SUM(OFFSET(A1,MATCH(B16,A2:A14,0),B17,1,B18-B17+1))

這個(gè)功能的關(guān)鍵是OFFSET，在以前的教程中介紹過(guò)，OFFSET有五個(gè)參數(shù)，分別是起點(diǎn)、行偏移量、列偏移量、區(qū)域高度（行數(shù)）和區(qū)域?qū)挾龋袛?shù)）。不清楚這個(gè)函數(shù)的同學(xué)，可以學(xué)習(xí)這篇教程《Excel進(jìn)階之路必學(xué)函數(shù)：動(dòng)態(tài)統(tǒng)計(jì)之王——OFFSET（上篇）》

在本例中，我們以A1作為起始位置，行偏移量用MATCH(B16,A2:A14,0)來(lái)確定，也就是要查找的區(qū)域所在的行，列偏移量直接使用開始月份所對(duì)應(yīng)的數(shù)字，區(qū)域高度為1，因?yàn)槎际轻槍?duì)單個(gè)區(qū)域進(jìn)行統(tǒng)計(jì)，所以區(qū)域?qū)挾染褪墙Y(jié)束月-開始月+1，這里面就是一些簡(jiǎn)單的數(shù)字問(wèn)題了。

簡(jiǎn)單驗(yàn)證一下，公式結(jié)果是正確的。

最后一步就是利用條件格式突出顯示要統(tǒng)計(jì)的單元格。

設(shè)置條件格式，大致需要三步，首先就是新建規(guī)則；

依次點(diǎn)擊【開始】-【條件格式】-【新建規(guī)則】

然后設(shè)置公式：

在編輯格式規(guī)則中，選中【使用公式確定要設(shè)置格式的單元格】，輸入預(yù)先編輯好的公式，再點(diǎn)【格式】進(jìn)行設(shè)置。

案例中用的公式為：

=AND($A2=$B$16,COLUMN(A2)>=$B$17,COLUMN(A2)<=$B$18)

（稍后會(huì)說(shuō)明這個(gè)公式的含義）

設(shè)置格式就很簡(jiǎn)單了，和平時(shí)設(shè)置單元格格式的方法是一樣的，包含數(shù)字格式、字體、邊框以及填充色，本例中只是設(shè)置了填充色，選擇一種反差比較大的顏色效果會(huì)更好。

點(diǎn)兩次確定退出條件格式的設(shè)置界面。

最后一步就是設(shè)置條件格式的生效范圍（如果是先選擇了數(shù)據(jù)區(qū)域再設(shè)置條件格式的話，這一步就無(wú)需進(jìn)行了）。

打開管理規(guī)則，可以看到已經(jīng)設(shè)置完成的規(guī)則，以及每個(gè)規(guī)則的應(yīng)用范圍。

調(diào)整規(guī)則的生效范圍就能看到突出顯示的效果了，操作步驟如圖所示。

以上就是設(shè)置條件格式的步驟，最后簡(jiǎn)單說(shuō)一下這個(gè)公式的意思。

本例公式使用了AND，里面有三個(gè)參數(shù)，也就是三個(gè)條件，只有當(dāng)三個(gè)條件同時(shí)成立時(shí)，才會(huì)按照設(shè)置的格式去顯示。

在公式=AND($A2=$B$16,COLUMN(A2)>=$B$17,COLUMN(A2)<=$B$18)中，條件1是$A2=$B$16。A2是數(shù)據(jù)源中的區(qū)域，B16是查詢條件中的區(qū)域，這個(gè)條件就是判定查詢條件的區(qū)域和數(shù)據(jù)源中的區(qū)域是否一致。

重點(diǎn)是$在其中的作用，由于格式的應(yīng)用區(qū)域是$B$2:$M$14，而各銷售區(qū)域名稱只在A列存在，因此要在列號(hào)前加$。

確定了哪一行要突出顯示后，還需要根據(jù)起始月份和終止月份來(lái)確定這一行中的哪幾列符合條件。

于是條件2和條件3就分別用列號(hào)與這兩個(gè)月份值作比較。

條件2：COLUMN(A2)>=$B$17

條件3：COLUMN(A2)<=$B$18

總結(jié)：今天分享的案例是一個(gè)綜合性非常強(qiáng)的應(yīng)用，涉及到數(shù)據(jù)驗(yàn)證的一些知識(shí)點(diǎn)，動(dòng)態(tài)區(qū)域求和的公式套路，以及條件格式的應(yīng)用。教程內(nèi)容難度適中，所用到的知識(shí)點(diǎn)都非常實(shí)用，希望大家能夠多加練習(xí)。靈活利用Excel的這些功能，可以設(shè)計(jì)出各種帶查詢功能的統(tǒng)計(jì)表，大大提高工作效率。

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

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

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

相關(guān)推薦：

Column函數(shù)的應(yīng)用《會(huì)用Column嗎？ 它讓公式不那么笨。》

用條件格式制圖《新同事用條件格式制作的圖表，竟然比我的還好看？》

OFFSET函數(shù)的應(yīng)用《Excel進(jìn)階之路必學(xué)函數(shù)：動(dòng)態(tài)統(tǒng)計(jì)之王——OFFSET（上篇）》

MATCH函數(shù)的應(yīng)用《MATCH：函數(shù)哲學(xué)家，找巨人做伴。新出道必學(xué)！》