刪除數據中的空行和列

在許多情況下，空行和空列可能會給表格帶來麻煩。 用於排序、過濾、匯總、創建數據透視表等的標準函數將空行和列視為表格中斷，而不會提取位於它們後面的數據。 如果有很多這樣的間隙，那麼手動刪除它們可能會非常昂貴，並且使用過濾器“批量”一次刪除所有這些是行不通的，因為過濾器也會在休息時“絆倒”。

讓我們看看解決這個問題的幾種方法。

方法 1. 搜索空單元格

這可能不是最方便的，但絕對值得一提的是最簡單的方法。

假設我們正在處理這樣一個表，其中包含許多空行和列（為清楚起見，突出顯示）：

假設我們確定表的第一列（B 列）總是包含一個城市的名稱。 那麼此列中的空單元格將是不必要的空行的標誌。 要快速刪除它們，請執行以下操作：

  1. 選擇城市範圍 (B2:B26)
  2. 按下鍵 F5 然後按 重點特色 （轉到特別版） 或在選項卡上選擇 主頁 — 查找和選擇 — 選擇一組單元格 （首頁 — 查找和選擇 — 轉到特別版）.
  3. 在打開的窗口中，選擇選項 空單元格 （空白） 並按下 OK – 應選擇表格第一列中的所有空單元格。
  4. 現在在選項卡上選擇 首頁 命令 刪除 - 從工作表中刪除行 （刪除 - 刪除行） 或按鍵盤快捷鍵 按Ctrl+減 ——我們的任務就解決了。

當然，您可以以完全相同的方式刪除空列，使用表頭作為基礎。

方法二：搜索空行

正如您可能已經發現的那樣，前一種方法僅在我們的數據必須包含完全填充的行和列時才有效，在搜索空單元格時可以掛鉤。 但是如果沒有這樣的信心，並且數據也可能包含空單元格怎麼辦？

例如，對於這種情況，請查看下表：

這裡的方法會有點棘手：

  1. 在單元格 A2 中輸入函數 COUNT (計數)，它將計算右側行中填充單元格的數量，並將此公式複製到整個表格中：
  2. 選擇單元格 A2 並使用命令打開過濾器 數據 - 過濾器 （數據 - 過濾器） 或鍵盤快捷鍵 按Ctrl+轉移+L.
  3. 讓我們通過計算列過濾掉零，即所有沒有數據的行。
  4. 剩下的就是選擇過濾的行並使用命令刪除它們 Home - Delete -' 從工作表中刪除行 （首頁——刪除——刪除行） 或鍵盤快捷鍵 按Ctrl+減.
  5. 我們關閉過濾器並獲取沒有空行的數據。

不幸的是，這個技巧不能再用列來完成——Excel 還沒有學會如何按列過濾。

方法 3. 刪除工作表上所有空行和列的宏

您還可以使用簡單的宏來自動執行此任務。 按鍵盤快捷鍵 其他+F11 或從選項卡中選擇 開發人員 — 視覺基礎 （開發者——Visual Basic 編輯器）. 如果選項卡 開發人員 不可見，您可以通過 文件 - 選項 - 功能區設置 （文件——選項——自定義功能區）.

在打開的 Visual Basic 編輯器窗口中，選擇菜單命令 插入 - 模塊 在出現的空模塊中，複製並粘貼以下行：

   Sub DeleteEmpty() Dim r As Long, rng As Range 'удаляем пустые строки For r = 1 To ActiveSheet.UsedRange.Row - 1 + ActiveSheet.UsedRange.Rows.Count If Application.CountA(Rows(r)) = 0 Then If rng 什麼都沒有然後設置 rng = Rows(r) 否則設置 rng = Union(rng, Rows(r)) End If Next r If Not rng Is Nothing Then rng.Delete 'удаляем пустые столбцы Set rng = Nothing For r = 1 To ActiveSheet.UsedRange.Column - 1 + ActiveSheet.UsedRange.Columns.Count If Application.CountA(Columns(r)) = 0 Then If rng is nothing Then Set rng = Columns(r) Else Set rng = Union(rng, Columns( r)) End If Next r If Not rng Is Nothing Then rng.Delete End Sub

關閉編輯器並返回 Excel。 

現在打組合 其他+F8 或按鈕 宏 選項卡 開發人員. 打開的窗口將列出當前可供您運行的所有宏，包括您剛剛創建的宏。 刪除空. 選擇它並單擊按鈕 跑 （跑） - 工作表上的所有空行和列將被立即刪除。

方法四：電源查詢

解決我們的問題的另一種方法和一個非常常見的場景是刪除 Power Query 中的空行和列。

首先，讓我們將表加載到 Power Query 查詢編輯器中。 您可以使用鍵盤快捷鍵 Ctrl+T 將其轉換為動態“智能”，或者只需選擇我們的數據范圍並為其命名（例如 數據) 在公式欄中，轉換為命名：

現在我們使用命令數據 - 獲取數據 - 從表/範圍（數據 - 獲取數據 - 從表/範圍）並將所有內容加載到 Power Query 中：

然後一切都很簡單：

  1. 我們使用命令 Home – Reduce lines – Delete lines – Delete empty lines (Home – Remove Rows – Remove empty rows) 刪除空行。
  2. 右鍵單擊第一個 City 列的標題，然後從上下文菜單中選擇 Unpivot Other Columns 命令。 我們的桌子，正如它在技術上正確的稱呼， 歸一化 – 轉換為三列：城市、月份和原始表中城市與月份交集的值。 Power Query 中此操作的特點是它會跳過源數據中的空單元格，這正是我們所需要的：
  3. 現在我們執行相反的操作——我們將結果表轉換回二維表，以便將其恢復為原始形式。 選擇帶有月份的列並在選項卡上 轉型 選擇一個團隊 樞軸柱 （變換——樞軸柱）. 在打開的窗口中，作為一列值，選擇最後一個（值），並在高級選項中 - 操作 不要聚合 （不要聚合）:
  4. 仍然使用命令將結果上傳回 Excel 主頁 — 關閉並加載 — 關閉並加載... （首頁 — 關閉並加載 — 關閉並加載到…）

  • 什麼是宏，它是如何工作的，在哪裡複製宏的文本，如何運行宏？
  • 用父單元格的值填充列表中的所有空單元格
  • 從給定範圍中刪除所有空單元格
  • 使用 PLEX 插件刪除工作表中的所有空行

發表評論