同樣是高級篩選,這個卻插上了翅膀

  今天要講的是「篩選」按鈕旁邊右下角還有個「高級」按鈕,這就是「高級篩選」。

同樣是高級篩選,這個卻插上了翅膀

點擊「高級」按鈕,在彈出的對話框中可以設置「列表區域」、「條件區域」、「目標區域」,可以設置在原有區域顯示或者復制到其他區域,也可以篩選不重復記錄。

同樣是高級篩選,這個卻插上了翅膀

為什麼叫「高級篩選」?

因為它高級嘛!

其實主要是因為它的篩選條件多樣化,可以設置各種複雜的條件,可以「並且」、「或」兩種條件並存,而且同一個字段可以同時應用多種篩選條件,篩選結果還可以選擇去掉重復值,可以將篩選結果單獨保存。

為什麼說「這個卻插上了翅膀」?

因為,今天我要手把手地教大家錄制宏,並改造代碼,完成一個可以使用複雜條件的微型查詢系統。

小試牛刀

下面是一組數據,放在Data表中,我們以這個數據來小試一下高級篩選。

同樣是高級篩選,這個卻插上了翅膀

在Query表中設置查詢條件和查詢結果顯示區域。

同樣是高級篩選,這個卻插上了翅膀

比如我們要查詢銷售員名字包含「L」,並且數量>600;或者日期>2017-5-10號的數據。我們將「並且」的兩個條件放在一行、將「或者」的條件放在另外一行,如上圖所示。

點擊【數據】選項卡下面「排序和篩選」組中的「高級」,按照以下對話框設置。

選擇「將篩選結果復制到其他位置」

列表區域:Data!$A$1:$E$23

條件區域:Query!$A$2:$E$4

復制到:Query!$G$2:$K$2

同樣是高級篩選,這個卻插上了翅膀

點擊「確定」按鈕,我們就得到了如下的查詢結果。

同樣是高級篩選,這個卻插上了翅膀

這個高級篩選很好,但是還不夠完美。我們將查詢條件和查詢結果單獨存放在一個工作表中,不跟數據放在一個工作表中,如果要多次查詢的話,每次都需要進行設置,比較麻煩。

所以今天我們帶大家來錄制宏並改造成通用代碼,鏈接到按鈕上,設置好條件後,我們只需要點擊按鈕就可以完成查詢了。

錄制宏

我們在應用一次高級篩選後,就已經自動創建了名稱,下次再應用時,條件、篩選結果就會自動出現在高級篩選的對話框中。

我們先把這些名稱刪掉。

同樣是高級篩選,這個卻插上了翅膀

在【開發工具】選項卡中點擊「錄制宏」,保存為默認名稱即可。

同樣是高級篩選,這個卻插上了翅膀

將上面我們做的高級篩選步驟再重復操作一遍後停止錄制宏。

我們得到了如下宏代碼,其實就是一句話。

給大家用中文翻譯一下。

在「Data」工作表的A1:E23區域應用高級篩選,其中設置參數,「將篩選結果復制到其他位置」,篩選條件在當前工作表的A2:E4區域,復制到G2:K2開始的區域,不需要「選擇不重復記錄」。

看起來沒那麼複雜吧?

改造代碼

對這段代碼我們要進行以下改造:

1、數據源區域是可以動態變化的。當增加或減少數據時,可以自動識別。

2、條件區域是可以動態變化的。當增加或減少條件時,可以自動識別,否則篩選結果就不正確了。

同樣是高級篩選,這個卻插上了翅膀

1、改造數據源區域

將Sheets(”Data”).Range(”A1:E23”)更改為Sheets(”Data”).UsedRange。

說明:這裡使用UsedRange表示工作表中使用的區域,因為我們的數據表是從第一行第一列開始存放的。

我們還可以使用如下代碼。

Sheets(”Data”).Range(”A1”).CurrentRegion

說明:Range(”A1”)可以是數據源區域中任意一個單元格,CurrentRegion是的邊界是空行、空列。如果數據是在其他區域,則將A1改成其中一個單元格即可。

2、改造條件區域

條件區域上面有一行,右邊有一空列,所以我們不能直接用CurrentRegion方法,因為這樣會將第一行作為標題行(第一行只有在A1單元格有「查詢條件」4個字,顯然不是標題行)。

但是我們仍然可以用CurrentRegion的知識,如下。

Range(Cells(2, 1), Cells(Range(”A2”).CurrentRegion.Rows.Count, 5))

Cells(2, 1)是A2單元格,是條件區域的左上單元格。

Cells(Range(”A2”).CurrentRegion.Rows.Count, 5)是條件區域的右下單元格。

這樣就構成了整個條件區域。

如果沒有第一行,如下,我們可以直接用Range(”A1”).CurrentRegion。

同樣是高級篩選,這個卻插上了翅膀

改造完之後,將宏代碼重新命名為Query,這個可以自己定義一個名字。

鏈接到按鈕

插入一個矩形形狀作為按鈕,點擊右鍵菜單中的「指定宏」。

同樣是高級篩選,這個卻插上了翅膀

在彈出的對話框中選擇Query。

同樣是高級篩選,這個卻插上了翅膀

這樣我們就完成了整個設置。

數據源或條件區域增減,我們也不需要重新設置,只需點擊「查詢」按鈕就可以完成查詢。

看起來是不是感覺要更高級一點呢?

查詢條件設置

關於高級篩選的條件設置,下面列舉一些常用的做法。

1、使用=,表示「等於」,在單元格中輸入以下內容=”=Will”或者”=Will,如果是文本字段,不輸入=就表示「包含」。

下面表示:銷售員名字包含L並且數量>600;或者銷售員=Will;或者日期>2017-5-21。

銷售員銷售組日期區域數量

L

>600

=Will

>2017-5-21

同樣是高級篩選,這個卻插上了翅膀

2、使用比較運算符>、>=、<、<=、<>,後面可以加數字或日期等;

3、同一個字段可以同時使用多個條件,放在一行表示「並且」,放在不同行,表示「或」;

以下表示日期>2017-5-10並且<2017-5-15;或者日期>2017-5-20。

日期日期

>2017-5-10<2017-5-15

>2017-5-20

同樣是高級篩選,這個卻插上了翅膀

4、不同字段設置條件,放在一行表示「並且」,放在不同行,表示「或」;

5、使用通配符*、?,如果要查找通配符本身,則需要在前面加波浪號~;

單元格中輸入”=D???,注意前面有個單引號。

以下表示銷售員名字以D開頭並且只有四個字符。

銷售員

=D???

同樣是高級篩選,這個卻插上了翅膀

6、使用公式計算結果

隨便填,不要寫字段名

=E2>=LARGE(Data!$E$2:$E$23,3)

同樣是高級篩選,這個卻插上了翅膀

關於高級篩選就介紹這麼多。在原有的基礎上稍加改造,就可以使它更加便捷好用。

–End–