excel函數應用技巧：這個銷售統計模板，能動態變色查詢

編按：哈嘍，大家好！今天向大家分享一個銷售統計表模板。該模板支持動態查詢功能，並且在查詢的時候，相應數據會變色，如此，查詢結果一目了然。統計模板將使用SUM、AND、COLUMN、MATCH、OFFSET函數並結合條件格式和數據驗證。學習更多技巧，請收藏關注部落窩教育excel圖文教程。

---

今天要和大家分享的是一個可以動態查詢銷售數據的統計模板。何為動態查詢呢，效果如動圖所示： 

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

公式用來實現銷售數據匯總，條件格式用來改變單元格顏色突出求和的數字區域。 

但是在這之前，先要設置三個數據驗證，分別是查詢區域、開始月和結束月，以下分別說明。

1.查詢區域的設置

這是數據驗證最基本的用法之一，在【允許】欄選擇序列，【來源】裡選擇對應的單元格區域即可，操作步驟見動圖演示。

2.開始月的設置

與前一項不同，開始月設置為只能輸入1到12之間的整數，並且設置提示信息，操作步驟見動圖演示。

3.結束月的設置

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

完成以上三個設置之後，首先來製作銷量合計的計算公式。

要實現按照查詢區域、開始月和結束月這三個條件進行合計的公式思路不是唯一的，這次我們使用比較常用的SUM-OFFSET函數組合，公式為：

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

這個功能的關鍵是OFFSET，在以前的教程中介紹過，OFFSET有五個參數，分別是起點、行偏移量、列偏移量、區域高度（行數）和區域寬度（列數）。不清楚這個函數的同學，可以學習這篇教程《Excel進階之路必學函數：動態統計之王——OFFSET（上篇）》

在本例中，我們以A1作為起始位置，行偏移量用MATCH(B16,A2:A14,0)來確定，也就是要查找的區域所在的行，列偏移量直接使用開始月份所對應的數字，區域高度為1，因為都是針對單個區域進行統計，所以區域寬度就是結束月-開始月+1，這裡面就是一些簡單的數字問題了。

簡單驗證一下，公式結果是正確的。

最後一步就是利用條件格式突出顯示要統計的單元格。學習更多技巧，請收藏關注部落窩教育excel圖文教程。

設置條件格式，大致需要三步，首先就是新建規則；

依次點擊【開始】-【條件格式】-【新建規則】

然後設置公式：

在編輯格式規則中，選中【使用公式確定要設置格式的單元格】，輸入預先編輯好的公式，再點【格式】進行設置。

案例中用的公式為：

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

（稍後會說明這個公式的含義）

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

點兩次確定退出條件格式的設置界面。

最後一步就是設置條件格式的生效範圍（如果是先選擇了數據區域再設置條件格式的話，這一步就無需進行了）。

打開管理規則，可以看到已經設置完成的規則，以及每個規則的應用範圍。

調整規則的生效範圍就能看到突出顯示的效果了，操作步驟如圖所示。

以上就是設置條件格式的步驟，最後簡單說一下這個公式的意思。

本例公式使用了AND，裡面有三個參數，也就是三個條件，只有當三個條件同時成立時，才會按照設置的格式去顯示。

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

重點是$在其中的作用，由于格式的應用區域是$B$2:$M$14，而各銷售區域名稱只在A列存在，因此要在列號前加$。

確定了哪一行要突出顯示後，還需要根據起始月份和終止月份來確定這一行中的哪幾列符合條件。

於是條件2和條件3就分別用列號與這兩個月份值作比較。

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

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

總結：今天分享的案例是一個綜合性非常強的應用，涉及到數據驗證的一些知識點，動態區域求和的公式套路，以及條件格式的應用。教程內容難度適中，所用到的知識點都非常實用，希望大家能夠多加練習。靈活利用Excel的這些功能，可以設計出各種帶查詢功能的統計表，大大提高工作效率。學習更多技巧，請收藏關注部落窩教育excel圖文教程。

---

＊＊＊＊部落窩教育-excel動態查詢統計表＊＊＊＊

原創：老菜鳥/部落窩教育（未經同意，請勿轉載）

更多教程：部落窩教育