平時(shí)我們經(jīng)常使用Excel統(tǒng)計(jì)數(shù)據(jù)，如果統(tǒng)計(jì)的品類較多，在查詢統(tǒng)計(jì)結(jié)果時(shí)就需要在列表中進(jìn)行費(fèi)力地查找和排序。以下面的表格為例，如果要統(tǒng)計(jì)PVC-1產(chǎn)品銷售和，因?yàn)橛卸鄠€(gè)部門(mén)銷售，求和就需要先對(duì)產(chǎn)品進(jìn)行排序，然后再求和。重新排序后會(huì)破壞原來(lái)表格的排列，而且每查詢一個(gè)產(chǎn)品還需要重新排序、求和，操作起來(lái)十分不便。現(xiàn)在我們可以使用“數(shù)據(jù)驗(yàn)證的下拉列表+統(tǒng)計(jì)求和”的方式解決這個(gè)問(wèn)題，這樣只要在下拉列表選擇品類即可快速看到統(tǒng)計(jì)結(jié)果（圖1）。
圖1 下拉+統(tǒng)計(jì)示例
從示例圖表可以看到，這個(gè)組合主要是由“數(shù)據(jù)驗(yàn)證”+“求和”組成，因此要實(shí)現(xiàn)這一效果，我們需根據(jù)原始數(shù)據(jù)將對(duì)應(yīng)的類別整合在“數(shù)據(jù)有效性”下拉列表中，同時(shí)將對(duì)應(yīng)數(shù)值的和統(tǒng)計(jì)出來(lái)。
首先設(shè)置數(shù)據(jù)有效性下拉列表，因?yàn)檫@里有多個(gè)部門(mén)銷售同一種產(chǎn)品（比如銷售一部和銷售二部都有銷售PVC-1）。為了方便進(jìn)行篩選，先將表轉(zhuǎn)化為動(dòng)態(tài)表格，全選表格內(nèi)容后，點(diǎn)擊“插入→表格”，切換到“表格工具→設(shè)計(jì)”，勾選其中的“標(biāo)題行”、“鑲邊行”、“篩選按鈕”（圖2）。
圖2 轉(zhuǎn)換表格
接著復(fù)制D2:D25單元格中的內(nèi)容到M2:M25單元格，在M1單元格中輸入“序列”，選中M2:M25單元格中的內(nèi)容，點(diǎn)擊菜單欄的“數(shù)據(jù)→刪除重復(fù)項(xiàng)”，在打開(kāi)的窗口中勾選“全選”和“數(shù)據(jù)包含標(biāo)題”，點(diǎn)擊“確定”（圖3）。
圖3 刪除重復(fù)產(chǎn)品
經(jīng)過(guò)上面的操作后，重復(fù)產(chǎn)品的數(shù)據(jù)會(huì)自動(dòng)刪除，只保留其中唯一的產(chǎn)品值，這些數(shù)值就可以作為數(shù)據(jù)有效性的序列數(shù)據(jù)了（圖4）。
圖4 保留唯一產(chǎn)品值
定位到J1單元格中輸入“選擇查詢產(chǎn)品”，K1單元格中輸入“銷售額”，接著定位到J2單元格，點(diǎn)擊菜單欄的“數(shù)據(jù)→數(shù)據(jù)驗(yàn)證→設(shè)置”，在允許列表中選擇“序列”，在“來(lái)源”后面點(diǎn)擊數(shù)據(jù)源，接著選擇“M2:M6”數(shù)據(jù)（即上述去重后的序列數(shù)據(jù)），點(diǎn)擊“確定”完成設(shè)置（圖5）。
圖5 數(shù)據(jù)驗(yàn)證設(shè)置
現(xiàn)在從J2單元格展開(kāi)下拉列表中就可以依次選擇上述的產(chǎn)品內(nèi)容了。接下來(lái)就要在K2單元格中設(shè)置求和數(shù)值。求和借助SUMIF函數(shù)完成，定位到K2單元格中輸入公式“=SUMIF(表1[產(chǎn)品],J2,表1[金額])”，當(dāng)我們?cè)贘2單元格的下拉列表中選擇產(chǎn)品時(shí)，在K2單元格中就會(huì)自動(dòng)顯示對(duì)應(yīng)的金額，如此一來(lái)查詢數(shù)據(jù)明顯方便了很多（圖6）。
圖6 求和函數(shù)設(shè)置
小提示：
上述公式中，“表1[產(chǎn)品]”參數(shù)表示的是求和的條件范圍是在“表1的產(chǎn)品字段列”，這里的“表1”是上述執(zhí)行“插入→表格”操作中動(dòng)態(tài)表格默認(rèn)的名稱（切換到“表格工具→設(shè)計(jì)→表名稱”，可以自定義設(shè)置）。參數(shù)“J2” 表示條件，求和的條件是在產(chǎn)品序列里J2顯示的指定產(chǎn)品（隨著下拉列表的選擇會(huì)動(dòng)態(tài)進(jìn)行變化，求和條件也同步進(jìn)行變化）。參數(shù)“表1[金額]”求和范圍是“表1的產(chǎn)品金額列”，即在J2選擇產(chǎn)品后對(duì)H列對(duì)應(yīng)產(chǎn)品的金額進(jìn)行求和。
因?yàn)槲覀兪褂玫氖莿?dòng)態(tài)表格（求和條件和范圍是通過(guò)表格的字段來(lái)設(shè)置），完成上述設(shè)置后，以后如果需要添加數(shù)據(jù)，比如在A26:H26單元格中增加了PVC-6的銷售數(shù)據(jù)，那么K2單元格中的求和也會(huì)同步發(fā)生變化。
SUMIF是單條件的求和，如果是多條件的求和，我們還可以借助SUMIFS來(lái)完成。假設(shè)現(xiàn)在需要同時(shí)查詢部門(mén)和指定產(chǎn)品的銷售數(shù)據(jù)和，如查詢銷售一部的PVC-1銷售數(shù)據(jù)。同上在I1單元格中輸入“部門(mén)查詢”，在I2單元格中再設(shè)置一個(gè)數(shù)據(jù)有效性驗(yàn)證序列（序列的內(nèi)容為銷售一部到銷售三部）。定位到K2單元格輸入函數(shù)“=SUMIFS(表1[金額],表1[部門(mén)],I2,表1[產(chǎn)品],J2)”，即可同時(shí)對(duì)部門(mén)和產(chǎn)品兩個(gè)條件進(jìn)行查詢（圖7）。
圖7 多條件求和查詢
小提示：
參數(shù)“表1[金額]”表示“求和的范圍”是表1[金額]字段下的數(shù)值，參數(shù)“表1[部門(mén)]”表示條件的范圍是[部門(mén)]字段，參數(shù)“I2”表示求和的條件是單元格顯示的具體部門(mén)，參數(shù)“表1[產(chǎn)品],J2”則分別對(duì)應(yīng)范圍是[產(chǎn)品]字段，求和條件是“J2”顯示的產(chǎn)品名稱。如果有多個(gè)條件，繼續(xù)添加“條件范圍”、“條件”參數(shù)即可，比如可以添加“表1[訂單ID],N2”，增加產(chǎn)品對(duì)應(yīng)的訂單ID的查詢。
如果部門(mén)和產(chǎn)品很多，可以進(jìn)入“數(shù)據(jù)→數(shù)據(jù)驗(yàn)證→設(shè)置”，在允許列表中選擇“任意數(shù)值”，這樣只要在I2和K2單元格中自行輸入部門(mén)和產(chǎn)品數(shù)值即可進(jìn)行查詢。如果統(tǒng)計(jì)的報(bào)表很多，我們可以新建一個(gè)工作表專門(mén)用于查詢，同上在每個(gè)原來(lái)有數(shù)據(jù)的工作表中插入動(dòng)態(tài)表，比如在Sheet2中插入“表2”，依此類推。那么只要在“查詢表”中的C3單元格中輸入公式“=SUMIFS(表2[金額],表2[部門(mén)],A3,表2[產(chǎn)品],B3)”（需要查詢哪張表格數(shù)據(jù)，這里就將表名稱和字段修改為對(duì)應(yīng)的名稱即可），就可以在一張專用表中非常方便地查詢其他工作表的所有數(shù)據(jù)了（圖8）。
圖8 專門(mén)查詢表
