查詢更高效　Excel動態求和更簡單

使用函數進行統計

在Excel中我們一般使用SUM函數求和，比如現在需要統計圖1中產品6的4～7月份的銷售數據，使用公式“=SUM（E7：H7）”即可。不過，這里的求和數據區域E7：H7根據查詢需求條件的數值而動態變化。這類動態求和的關鍵是，首先要確定求和的品名，接著根據品名選擇求和的數據區域，最后使用SUM函數進行求和。對于求和區域的選擇，可以借助OFFSET函數來進行動態引用，OFFSET函數需要設置起始位置、行偏移數、列偏移數、求和數據區域的高度和寬度等五個參數。

1設置品名和月份選擇

為了方便對數據進行查詢，這里我們先使用數據驗證來設置品名和月份選擇。定位到B19單元格，依次點擊“數據→數據驗證→設置→序列”，然后選擇數據來源區域為A2：A9，這樣在B19單元格中通過下拉列表就可以方便地選擇品名了（圖2）。

操作同上，在N1：N12區域中依次填充數字1～12，在B20、D20單元格中也使用數據驗證，“來源”選擇N1：N12區域，這樣在需要查詢品名及其月份的數據時，可以通過下拉列表進行快速選擇。

2確定起始位置參數

在數據求和區域A1：M9中，A1就是起始位置，因此第一個參數即為A1。

3確定行偏移數

定位到E20單元格并輸入公式“=MATCH（B19，A2：A9，0）”，公式的意思是使用MATCH函數定位B19單元格輸入的品名在A2：A9區域中的實際行號。比如B19是輸入產品6，通過函數可以確定這個品名在A2：A9區域中是在第6行，因此函數的引用結果為數字6，將其作為OFFSET函數的第二個參數值（圖3）。

4確定列偏移數

同理在F20單元格中輸入公式“=MATCH（B20&"月"，B1：M1，0）”，表示以B20單元格中輸入的數字為基準，將其和“月”字連接，然后在B1：M1月份區域中查找其偏移行數。比如B20輸入的是“4”（即4月），其在月份區域為向右偏移4列。

5確定數據區域的高度和寬度

根據求和條件可以知道，這里求和數據的高度是“1”（即只對指定品名一行數據求和），求和寬度則是“=D20-B20+1”，即“終止月份-初始月份+1”，比如輸入的是4～7月份，就是求“7-4+1=4”，即4、5、6、7這四個月份的數據。

6制作求和公式

通過上面的方法，我們確定了OFFSET函數的所有參數。繼續定位到G20單元格并輸入公式“=SUM（OFFSET（A1，E20，F20，1，D20-B20+1））”，即使用SUM函數對OFFSET函數引用的區域進行求和（圖4）。

這樣我們需要進行動態求和時，只要在B19單元格中下拉列表選擇品名，在B20、D20單元格中選擇起始月份，在G20單元格中就可以快速進行動態求和了。了解了上述的參數設置后，為了方便使用，也可以在E20單元格中直接輸入嵌套公式“=SUM（OFFSET（A1，MATCH（B19，A2：A9，0），MATCH（B20&"月"，B1：M1，0），1，D20-B20+1））”，這樣即可快速求和（圖5）。

使用透視表匯總求和

雖然通過函數的方法對動態區域進行求和很方便，但OFFSET函數只能對連續的動態區域進行引用。如果求和需要引用的是不連續的區域，比如要統計產品2、4及其4、6、9月份的銷售數據，求和函數的設置就非常復雜，對于這類求和，可以使用數據透視表來實現。

首先在原始數據表中選擇A1：M9區域，然后依次點擊“插入→數據透視表”，在彈出的窗口中選擇A12單元格放置透視表數據（圖6）。

點擊“確定”后，在A12單元格中就可以看到透視表了。在右側的透視表窗格中，按提示將“品名”字段拖拽到下方的篩選區域，將Σ數值拖拽到列區域，勾選任意月份數據，求和項會自動出現在Σ值區域中（圖7）。

這樣，當我們需要統計產品2、4及其4、6、9月份的銷售數據時，只要在B12單元格的品名篩選列表中勾選產品2、4，在求和項里勾選4、6、9月份的數據，即可完成求和了（圖8）。