删除数据中的空行和列

在许多情况下，空行和空列可能会给表格带来麻烦。 用于排序、过滤、汇总、创建数据透视表等的标准函数将空行和列视为表格中断，而不会提取位于它们后面的数据。 如果有很多这样的间隙，那么手动删除它们可能会非常昂贵，并且使用过滤器“批量”一次删除所有这些是行不通的，因为过滤器也会在休息时“绊倒”。

让我们看看解决这个问题的几种方法。

方法 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 或按钮 宏 标签 开发人员. 打开的窗口将列出当前可供您运行的所有宏，包括您刚刚创建的宏。 删除空. 选择它并单击按钮 运行 （跑） - 工作表上的所有空行和列将被立即删除。

方法 4：电源查询

解决我们的问题的另一种方法和一个非常常见的场景是删除 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 插件删除工作表中的所有空行

发表评论