高級過濾器和一些魔法

對於絕大多數 Excel 用戶來說，當“數據過濾”這個詞出現在他們的腦海中時，只有選項卡中常用的經典過濾器 數據 - 過濾器 （數據 - 過濾器）:

高級過濾器和一些魔法

毫無疑問，這樣的過濾器是一種熟悉的東西，並且在大多數情況下它會起作用。 但是，在某些情況下，您需要一次按多個列中的大量複雜條件進行過濾。 這里通常的過濾器不是很方便，我想要更強大的東西。 這樣的工具可能是 先進的過濾器，尤其是“用文件完成”（根據傳統）。

基地

首先，在您的數據表上方插入幾行空行並複製表頭 - 這將是一個帶有條件的範圍（為清楚起見以黃色突出顯示）：

高級過濾器和一些魔法

黃色單元格和原始表格之間必須至少有一個空行。

您需要在黃色單元格中輸入條件（條件），然後根據這些條件執行過濾。 例如，如果您需要在第三季度的莫斯科“歐尚”中選擇香蕉，那麼條件將如下所示：

高級過濾器和一些魔法

要進行篩選，請選擇包含源數據的區域中的任何單元格，打開選項卡 數據 然後點擊 另外 （數據——高級）. 在打開的窗口中，應該已經自動輸入了一個包含數據的範圍，我們只需要指定條件範圍，即 A1:I2：

高級過濾器和一些魔法

請注意，條件範圍不能“有邊距”分配，即不能選擇多餘的空黃線，因為條件範圍內的空單元格被 Excel 認為沒有條件，而整個空單元格行作為不加選擇地顯示所有數據的請求。

軸體 將結果復製到另一個位置 將允許您過濾不在此工作表上的列表（與常規過濾器一樣），而是將選定的行卸載到另一個範圍，然後需要在字段中指定 將結果放在範圍內. 在這種情況下，我們不使用這個函數，我們離開 過濾列表到位 並點擊 OK. 選定的行將顯示在工作表上：

高級過濾器和一些魔法

添加宏

“嗯，這裡哪裡方便？” 你問，你會是對的。 您不僅需要用手在黃色單元格中輸入條件，還需要打開一個對話框，在那裡輸入範圍，按 OK. 傷心，我同意！ 但是“一切都來了©”——宏！

使用一個簡單的宏可以大大加快和簡化高級過濾器的工作，該宏會在輸入條件時自動運行高級過濾器，即更改任何黃色單元格。 右鍵單擊當前工作表的選項卡並選擇命令 源文字 （源代碼）. 在打開的窗口中，複製並粘貼以下代碼：

Private Sub Worksheet_Change(ByVal Target As Range) If Not Intersect(Target, Range("A2:I5")) Is Nothing Then On Error Resume Next ActiveSheet.ShowAllData Range("A7").CurrentRegion.AdvancedFilter Action:=xlFilterInPlace, CriteriaRange :=Range("A1").CurrentRegion End If End Sub

噹噹前工作表上的任何單元格發生更改時，此過程將自動運行。 如果更改單元格的地址落入黃色範圍（A2:I5），則此宏將刪除所有過濾器（如果有）並將擴展過濾器重新應用於以 A7 開頭的源數據表，即所有內容都將立即過濾進入下一個條件後：

所以一切都好多了，對吧？ 🙂

實現複雜的查詢

現在所有內容都在動態過濾，我們可以更深入地研究細微差別並分解高級過濾器中更複雜查詢的機制。 除了輸入完全匹配之外，您還可以在一系列條件中使用各種通配符（* 和 ?）和數學不等號來實現近似搜索。 字符大小寫無關緊要。 為清楚起見，我在表格中總結了所有可能的選項：

標準 結果
gr* 或 gr 所有單元格以 GrIe Gr耳, Gr柚子, Gr分析 等。
=洋蔥 所有單元格都準確且僅帶有單詞 弓，即完全匹配
*liv* 或 *liv 細胞含有 麗芙 如何下劃線，即 О麗芙每, 麗芙ep, 據麗芙 等。
=p*v 開頭的詞 П 並以 В ie П第一в, П醚в 等。
作為 開頭的詞 А 並進一步包含 СIe А像素сin, А娜娜с, Asai 等。
=*s 以結尾的詞 С
=？？？？ 所有包含 4 個字符（字母或數字，包括空格）的文本的單元格
=m??????n 以 8 個字符開頭的所有單元格 М 並以 НIe М安達里н, М焦慮н  等。
=*n??a 所有以結尾的詞 А, 末尾的第 4 個字母在哪裡 НIe 光束нikа, 據нozа 等。
>=e 所有以開頭的單詞 Э, Ю or Я
<>*哦* 所有不包含字母的單詞 О
<>*維奇 除了結尾的所有單詞 艾滋病毒 （例如，按中間名過濾女性）
= 所有空單元格
<> 所有非空單元格
> = 5000 值大於或等於 5000 的所有單元格
5 或 =5 所有值為 5 的單元格
> = 3/18/2013 日期在 18 年 2013 月 XNUMX 日（含）之後的所有單元格

細微之處：

  • * 符號表示任意數量的任意字符，而 ? – 任何一個字符。
  • 處理文本和數字查詢的邏輯略有不同。 因此，例如，編號為 5 的條件單元格並不意味著搜索以 XNUMX 開頭的所有數字，但帶有字母 B 的條件單元格等於 B*，即將查找以字母 B 開頭的任何文本。
  • 如果文本查詢不以 = 符號開頭，那麼您可以在腦海中將 * 放在末尾。
  • 日期必須以美國格式月-日-年並透過分數輸入（即使您有 Excel 和區域設定）。

邏輯連接 AND-OR

寫在不同單元格但在同一行中的條件被認為是由邏輯運算符互連 И （和）:

高級過濾器和一些魔法

那些。 在第三季度為我過濾香蕉，正是在莫斯科，同時來自歐尚。

如果您需要將條件與邏輯運算符聯繫起來 OR （要么），那麼它們只需要在不同的行中輸入。 例如，如果我們需要查找經理 Volina 的莫斯科桃子的所有訂單和薩馬拉第三季度的所有洋蔥訂單，那麼可以在一系列條件中指定如下：

高級過濾器和一些魔法

如果您需要對一列施加兩個或多個條件，那麼您只需在條件範圍內復制列標題並在其下輸入第二個、第三個等。 條款。 因此，例如，您可以選擇從 XNUMX 月到 XNUMX 月的所有交易：

高級過濾器和一些魔法

一般來說，在“完成一個文件”之後，一個高級過濾器就會成為一個相當不錯的工具，在某些地方並不比經典的自動過濾器差。

  • 宏上的超級過濾器
  • 什麼是宏，在 Visual Basic 中插入宏代碼的位置和方法
  • Microsoft Excel 中的智能表格

發表評論