高級篩選,Excel高手的進階之路!

    來自：精進excel（ID：SeniorExcel）

水鏡老師今天給大家精選的文章是高級篩選。

篩選絕對能是Excel中最常用的功能，甚至沒有之一。

Excel基礎的自動篩選在工作中被經常使用，但是它有兩個痛點：

痛點一：不同欄位的篩選只能是交集（滿足一個欄位的情況下再去篩選另一個欄位）

痛點二：同一個欄位自定義篩選，最多只能設置兩個篩選條件。

這兩個痛點使得自動篩選能夠實現的功能大打折扣，高級篩選則全方位彌補了這兩個痛點。

一、高級篩選概述

001

高級篩選基礎

高級篩選之所以稱之為高級，是因為它能實現很多一般篩選無法完成的功能。如圖所示是高級篩選能夠實現的功能，夠厲害的吧！

高級篩選的核心在於條件的設置，而條件是在高級篩選的「條件區域」中進行設置的。打開「數據」選項卡，在「排序和篩選」分區可以看到有「高級」命令，它就是高級篩選的入口。

接下來會進入高級篩選面板，高級篩選的核心設置就在這個面板中，其中最重要的就是「條件區域」

002

高級篩選條件設置原理

首先高級篩選的條件是由標題和值組成的，需要將條件事先寫在單元格中，並在高級篩選窗口的「條件區域」中進行引用。

比如，這就是一個條件區域：第一行為標題，第二行、第三行分別為值。

高級篩選的條件有以下三個準則：

準則一：篩選條件的標題要和數據表中的標題一致

準則二：篩選條件中的值在同一行表示「且」的關係

準則三：篩選條件中的值在不同行表示「或」的關係

仍以本圖的條件區域為例，解釋三條準則：

第一行的標題，必須和要篩選的數據區域中的標題完全一樣；這個複雜的條件中，值「杭州」和值「>30」在同一行，表示篩選條件為杭州且銷售量>30；值「>30」和值「<20」不在同一行，表示篩選條件為銷售量>30或<20

這三條準則構成了高級篩選條件的基本原理，特別是後兩條準則，能推演出幾乎所有的高級篩選條件。

二、高級篩選應用案例

如圖所示是一段時期的銷售記錄表，我們基於此表進行數據的高級篩選。

001

多條件篩選

▌01 「且」條件篩選

例：要篩選滿足條件：銷售地為「北京」且銷售員為「李傑」的數據

因為是且的關係，因此條件區域中值應該寫在同一行中，條件區域如下：

要同時滿足多個條件，放在同一行上，作為高級篩選的條件區域。

▌02 「或」條件篩選

例：要篩選滿足條件：銷售地為「北京」或銷售量「>20」的數據

因為是或的關係，因此條件區域中值應該寫在不同行中，條件區域如下：

操作方法和「且」條件是一樣的，後文就不再進行GIF演示。

 

▌03 複合條件篩選

實際工作中的篩選場景遠比單獨的「且」條件、「或」條件複雜，但正如前文所述，這兩個條件是一切複雜條件的基礎，由他們可以延伸出很多變形。

例：要篩選滿足條件：「銷售地為杭州且銷售量大於30，或銷售人員為Lily且銷售量小於20」的數據

條件區域設置如下：

這個複雜條件的演變過程是這樣的：

①條件：杭州且銷售量>30，應寫在同一行中；

②條件：Lily且銷售量<20，應寫在同一行中；

③條件：①或條件②，是或的關係，應寫在不同行中。

002

將篩選結果複製到其他區域

高級篩選還能實現將篩選出來滿足條件的數據，複製到其他表格區域，這在一定程度上甚至有了SQL語言的影子。

當勾選「將篩選結果複製到其他位置」時，高級篩選窗口將激活「複製到」選項口，然後選擇一個希望將篩選結果複製到的區域。

GIF

▼

003

去除重複值篩選

關於重複這個概念，隱藏了很深的一個條件就是：「重複」是有範圍屬性的。

怎麼理解這句話呢？

舉一個簡單的例子：

如下圖的數據表，對於整張表的範圍（PQ兩列）來說，數據是沒有重複的，因為不存在銷售人員和銷售地完全一樣的記錄；但是對於銷售人員這個範圍來說，數據是重複的，及李傑重複了兩次。

這就是「重複」的範圍屬性，這個看似簡單的屬性，正式很多人對「重複」理解不透徹、甚至出錯的根源。

▌01 提取不重複的產品名

GIF

▼

看完這個GIF，你應該有疑問，為什麼這個案例中的篩選沒有設置「條件區域」？

因為我們勾選了「選擇不重複記錄」，這其實就是一個條件。

▌02 附加條件下提取不重複記錄

我們直接看動畫，然後我再解釋原理

GIF>>

如果同時設置了條件區域和「選擇不重複記錄」，其綜合效果就是在此篩選條件下，再對重複的數據進行剔除。

從這兩個案例中，我們可以得出結論：

「重複」的範圍屬性的範圍屬性是在「列表區域」進行設置的，如下圖列表區域是B1:C15，也就是說對這兩列數據進行重複是否的判斷。

004

自定義條件篩選

▌01 多列聯動篩選

高級篩選還可以實現數據間的比較，將滿足條件的數據篩選出來。

例：篩選出銷售額<銷售目標的數據，並複製到其他區域。

GIF>>

有兩點注意事項：

1、因為是使用的公式作為條件，條件區域的標題可以自定義書寫；

2、條件區域中=F2<E2，只需要對第一個單元格進行比較即可，篩選時會自動進行擴展。

▌02 用函數自定義條件

當然，我們還可以使用Excel自帶的函數設置條件，比如要篩選銷售額超過平均銷售額的記錄，並複製到其他區域。

操作方法是完全一樣的，這裡需要設置的條件為=F2>AVERAGE($F$2:$F$15)

005

高級篩選實現兩表間的核對

根據高級篩選的特性：設置篩選條件，然後在一組數據中篩選出滿足此條件的數據。利用這一特性，我們將它巧妙用於數據核對中。

如圖所示是兩組數據，這兩組數據有差異，但是用肉眼不容易找出差異點。

我們以第一個表A1:D12為列表區域，第二個表F1:I12為條件區域，對第一個進行篩選，如圖所示。

在篩選結果上，對第一個表的數據區域進行黃色底紋填充。

然後清除篩選，就會發現，有差異的數據即為沒有填充黃色的數據，一目了然地就核對出來了。

Excel高級篩選，止於此文！

分享知識  分享正能量

更多精彩內容

請長按識別下圖二維碼

關注公眾號

本公眾號致力於

給廣大讀提供excel系統知識

而不是散碎的知識點

學習excel要多練習

更要多複習

才能融會貫通

所以個別重要文章

會不定期重複發送

貴在堅持

難再堅持

量的積累

質的飛躍

希望遇到一個更精彩的你