excel篩選技巧：如何做一個動態篩選自動化圖表？

編按：哈嘍，大家好！今天給大家分享一個老闆最愛的excel自動化表格，搞定老闆，升職加薪，不再是夢！

＊＊＊＊＊＊＊＊＊

數據源：

小玲老師從「中國養豬網」隨機選取了五個省份的豬肉價格，製成下表。（僅作excel演示使用）

*說明：

由三個品種雜交生產的豬叫三元豬；

外三元：全部選用外來品種雜交而成；

內三元：三個品種中有一到兩個我國的品種，則稱為內三元；

土雜豬：是指良種豬與本地豬的雜交品種。

目標樣式：

現在我們需要利用excel，將上表做成動態可篩選的模式，變成一個可匯報的動態數據表，數據表內容會隨被篩選欄位的變化而變化，如下圖所示：

分析思路：

在動手操作之前呢，我們先來分析一下「這道題」。

我一直信奉 「思路比方法更為重要」，學會分析，就學會了舉一反三的能力，遇到問題時，總能想到解決辦法，這是最重要的，也是自己競爭力的體現。

首先，我們來對比一下「目標數據表」與「數據源表」的區別，你會發現：

（1）功能上有區別：靜態表 vs 動態表，目標表多了篩選功能。

（2）樣式上有區別：目標表較數據源表，新增了三個篩選按鈕，且目標表沒有合併單元格。

提到「動態篩選」，其實是有固定套路的，給大家普及一下三種常用的套路：

（1）數據透視表/超級表+切片器

（2）數據有效性+公式

（3）開發工具-窗體控制項-單元格關聯+公式+高級篩選

這三種套路，希望大家可以熟記於心，並形成條件反射，日後一旦遇到動態表格製作，就直接拿來用，腦中想到這幾種固定搭配即可。

*本著讓大家深入理解的精神，給大家解釋一下，這幾個套路的形成思路（同類問題均可按此方法進行分析）：

1、「動態篩選」，可以拆分為：動態+篩選

2、涉及篩選部分，需要想到excel具有「篩選」功能的操作或按鍵有哪些：

（1）excel自帶的篩選

（2）數據透視表本身的篩選功能，外加切片器

（3）數據有效性

（4）開發工具中的窗體控制項

（5）高級篩選等

關於「動態」：

（1）數據透視表本身就是動態的；

（2）目標值通過公式連結到具體單元格，隨著單元格內容變化，目標值發生變化，也是動態的；

（3）宏、VBA也可以實現目標的動態化

3、再將篩選與動態結合起來，就形成了上述提到的三種常用固定搭配。

在本例中很明顯是使用數據透視表+切片器的搭配模式，實用性最高，操作起來最簡單。

具體操作：

※步驟一：處理數據源

取消數據源表中的合併單元格，並快速填充空白單元格。

*提示：數據源非常重要，是一切excel操作的來源，一定要保證數據源的準確性和規範性（無隱藏行或列、無合併單元格等）。關於這一點我們在之前的文章中詳細介紹過《函數技巧千千萬，如何制表才關鍵！（上篇）》

具體操作：取消合併單元格→按Ctrl+G打開定位條件→選擇「空值」→在編輯欄輸入公式「=B3」（=上方單元格）→按Ctrl+Enter組合鍵完成公式錄入→最後粘貼為數值。

操作請見如下GIF動圖

※步驟二：製作數據透視表

這個步驟會涉及到一些細節上的操作，需要保證最後呈現出的數據透視表與數據源表的內容、欄位排列一致。所以在製作透視表時，需先將所有的欄位均移至透視表欄位的「行」欄位下：

結果如下：

移動完成後，你會發現透視表的整體格式不符合我們初始的要求，需要進行以下操作步驟：

a. 更改數據透視表的布局

點擊透視表，單擊滑鼠右鍵→選擇「數據透視表選項」→在「顯示」欄中勾選「經典數據透視表布局（啟用網格中的欄位拖放）」。

或者也可以直接在「數據透視表工具」欄中，點擊「設計」→「報表布局」→選擇「以表格形式顯示」。

效果如下圖所示：

b. 取消分類匯總

點擊透視表，在「數據透視表工具」欄中 ，點擊「設計」→「分類匯總」→選擇「不顯示分類匯總」。效果如下圖所示：

c. 將省份列的空白處填充

點擊透視表，在「數據透視表工具」欄中，點擊「設計」→「報表布局」→選擇「重複所有項目標籤」。

d. 取消「展開與摺疊」按鈕

點擊透視表，單擊滑鼠右鍵→選擇「數據透視表選項」→在「顯示」欄中取消勾選「顯示展開/摺疊按鈕」。或者，在「數據透視表工具」欄中，點擊「分析」→點擊「+/-按鈕」。效果如圖所示：

設置完成後，是不是發現與源數據表的格式差不多了呢？

※步驟三：添加切片器，實現「傻瓜式」動態篩選

數據透視表有自帶的手動篩選功能，將需要篩選的欄位挪至「篩選」欄位下即可實現篩選。

但如果本例按此法操作，將作為篩選欄位的「省份、類型及較全國均價」，移至「篩選」欄位下，會發現這三列數據會在表格中消失，僅作為篩選按鈕出現在表格的上方。

導致這一現象的原因是由於數據透視表的篩選欄位與行欄位，是二選一，有我無他的關係。

若想解決，可以在數據源中添加輔助列（將需要篩選的欄位列重新複製一列），刷新數據透視表，然後將其中一份欄位放入篩選欄位下，另一份欄位放入行欄位下，即可。

但此方法較為繁瑣，且展示起來不夠簡潔明了。

此時，「切片器」的優勢就體現出來了，利用切片器，可以直觀的進行數據的篩選。

哪怕不懂數據透視表，在看到切片器後，就知道該如何使用了。

切片器的添加方式：點擊透視表，在「數據透視表工具」欄中，點擊「分析」- 「插入切片器」。然後根據需要，選擇作為篩選欄的欄位即可。

具體操作請見下方動圖：

最後再對切片器和數據透視表進行美化，就可以完成目標樣式中的動態圖表啦~

除以上excel技能外，小玲老師更多的是希望小夥伴們可以學會分析的思路。考慮到數據透視表的普及度，以及不受版本限制的特點，所以本例，老師採用透視表來做講解。若小夥伴們的excel是2013版及以上的，可以直接使用超級表，兩步就能完成。操作用法：點擊源數據表，使用快捷鍵Ctrl+T，調用出超級表，然後增加切片器功能即可。是不是很簡單，小夥伴快來試試吧！

＊＊＊＊部落窩教育－excel切片器應用技巧＊＊＊＊

原創：劉宏玲/部落窩教育（未經同意，請勿轉載）