查詢更高效　Excel動態(tài)求和更簡單

使用函數(shù)進(jìn)行統(tǒng)計

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

1設(shè)置品名和月份選擇

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

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

2確定起始位置參數(shù)

在數(shù)據(jù)求和區(qū)域A1：M9中，A1就是起始位置，因此第一個參數(shù)即為A1。

3確定行偏移數(shù)

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

4確定列偏移數(shù)

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

5確定數(shù)據(jù)區(qū)域的高度和寬度

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

6制作求和公式

通過上面的方法，我們確定了OFFSET函數(shù)的所有參數(shù)。繼續(xù)定位到G20單元格并輸入公式“=SUM（OFFSET（A1，E20，F(xiàn)20，1，D20-B20+1））”，即使用SUM函數(shù)對OFFSET函數(shù)引用的區(qū)域進(jìn)行求和（圖4）。

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

使用透視表匯總求和

雖然通過函數(shù)的方法對動態(tài)區(qū)域進(jìn)行求和很方便，但OFFSET函數(shù)只能對連續(xù)的動態(tài)區(qū)域進(jìn)行引用。如果求和需要引用的是不連續(xù)的區(qū)域，比如要統(tǒng)計產(chǎn)品2、4及其4、6、9月份的銷售數(shù)據(jù)，求和函數(shù)的設(shè)置就非常復(fù)雜，對于這類求和，可以使用數(shù)據(jù)透視表來實現(xiàn)。

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

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

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