Excel中的水平列過濾

如果您不是一個新手用戶，那麼您一定已經註意到 Excel 中 99% 的所有內容都設計為使用垂直表，其中參數或屬性（字段）穿過列，並定位有關對像或事件的信息在行中。 數據透視表、小計、雙擊複製公式——一切都是專門為這種數據格式量身定制的。

但是，沒有任何規則沒有例外，並且經常有人問我，如果在工作中遇到具有水平語義方向的表或行和列具有相同權重的表，我該怎麼辦：

Excel中的水平列過濾

如果 Excel 仍然知道如何水平排序（使用命令 數據——排序——選項——列排序)，那麼過濾的情況就更糟了——根本沒有用於過濾列的內置工具，而不是 Excel 中的行。 因此，如果您面臨這樣的任務，您將不得不想出不同複雜程度的解決方法。

方法 1. 新增 FILTER 功能

如果您使用的是新版本的 Excel 2021 或 Excel 365 訂閱，則可以利用新引入的功能 過濾器 （篩選），它不僅可以按行過濾源數據，還可以按列過濾。 為了工作，這個函數需要一個輔助的水平一維數組行，其中每個值（TRUE 或 FALSE）決定我們是顯示還是隱藏表中的下一列。

讓我們在表格上方添加以下行，並在其中寫入每一列的狀態：

Excel中的水平列過濾

  • 假設我們總是想顯示第一列和最後一列（標題和總計），因此對於它們在數組的第一和最後一個單元格中，我們將值設置為 TRUE。
  • 對於剩餘的列，相應單元格的內容將是一個公式，它使用函數檢查我們需要的條件 И （和） or OR （要么）. 例如，總數在 300 到 500 之間。

之後，它仍然只使用該功能 過濾器 選擇我們的輔助數組具有 TRUE 值的列：

Excel中的水平列過濾

同樣，您可以按給定列表過濾列。 在這種情況下，該功能將有所幫助 COUNTIF （縣），它檢查允許列表中表頭中下一列名稱的出現次數：

Excel中的水平列過濾

方法 2. 數據透視表而不是通常的數據透視表

目前，Excel 僅在數據透視表中內置了按列進行的水平過濾，因此如果我們設法將原始表格轉換為數據透視表，我們可以使用此內置功能。 為此，我們的源表必須滿足以下條件：

  • 有一個“正確”的單行標題行，沒有空單元格和合併單元格 - 否則將無法構建數據透視表；
  • 不要在行和列的標籤中包含重複項——它們將在摘要中“折疊”成一個只有唯一值的列表；
  • 僅包含值範圍內的數字（在行和列的交叉處），因為數據透視表肯定會對它們應用某種聚合函數（求和、平均值等），這不適用於文本

如果滿足所有這些條件，那麼為了構建一個看起來像我們的原始表的數據透視表，它（原始的）將需要從交叉表擴展為平面表（標準化）。 最簡單的方法是使用 Power Query 加載項，這是自 2016 年以來內置於 Excel 中的強大數據轉換工具。 

這些是：

  1. 讓我們將表格轉換為“智能”動態命令 主頁 – 格式為表格 （首頁——表格格式）.
  2. 使用命令加載到 Power Query 數據 - 來自表/範圍（數據 - 來自表/範圍）.
  3. 我們用總計過濾行（摘要將有自己的總計）。
  4. 右鍵單擊第一列標題並選擇 展開其他列 （取消透視其他列）. 所有未選擇的列都轉換為兩列——員工的姓名和他的指標值。
  5. 使用進入該列的總數過濾該列 屬性.
  6. 我們使用命令根據生成的平面（標準化）表構建數據透視表 主頁 — 關閉並加載 — 關閉並加載... （首頁 - 關閉並加載 - 關閉並加載到...）.

現在您可以使用過濾數據透視表中可用列的功能 - 名稱和項目前面的常用複選標記 簽名過濾器 （標籤過濾器） or 按值過濾 （值過濾器）:

Excel中的水平列過濾

當然，在更改數據時，您需要使用鍵盤快捷鍵更新我們的查詢和摘要 按Ctrl+其他+F5 或團隊 數據 – 全部刷新 （數據——全部刷新）.

方法 3. VBA 中的宏

正如您很容易看到的那樣，所有以前的方法都不是完全過濾 - 我們不會隱藏原始列表中的列，而是使用原始列表中的一組給定列形成一個新表。 如果需要過濾（隱藏）源數據中的列，則需要一種根本不同的方法，即宏。

假設我們要動態過濾表頭中經理的姓名滿足黃色單元格 A4 中指定的掩碼的列，例如，以字母“A”開頭（即獲取“Anna”和“Arthur “ 因此）。 

與第一種方法一樣，我們首先實現一個輔助範圍行，在每個單元格中，我們的標準將由一個公式檢查，邏輯值 TRUE 或 FALSE 將分別顯示為可見列和隱藏列：

Excel中的水平列過濾

然後讓我們添加一個簡單的宏。 右鍵單擊工作表選項卡並選擇命令 資源 （源代碼）. 將以下 VBA 代碼複製並粘貼到打開的窗口中：

Private Sub Worksheet_Change(ByVal Target As Range) If Target.Address = "$A$4" Then For Each cell In Range("D2:O2") If cell = True Then cell.EntireColumn.Hidden = False Else cell.EntireColumn.Hidden = True End If Next Cell End If End Sub

其邏輯如下：

  • 一般來說，這是一個事件處理程序 工作表_更改，即此宏將在對當前工作表上的任何單元格進行任何更改時自動運行。
  • 對更改單元格的引用將始終在變量中 目標.
  • 首先，我們檢查用戶是否使用標準 (A4) 準確更改了單元格——這是由操作員完成的 if.
  • 然後循環開始 對於每個… 使用每列的 TRUE / FALSE 指標值迭代灰色單元格 (D2:O2)。
  • 如果下一個灰色單元格的值為TRUE（真），那麼該列不隱藏，否則我們隱藏它（屬性 Test).

  •  Office 365 中的動態數組函數：FILTER、SORT 和 UNIC
  • 使用 Power Query 的具有多行標題的數據透視表
  • 什麼是宏，如何創建和使用它們

 

發表評論