☰

通过有条件地从另一个表复制行来创建表 - 在 MS-Excel 中可以吗？

tag-icon • tag-icon microsoft-excel microsoft-excel-2007 worksheet-function
通过有条件地从另一个表复制行来创建表 - 在 MS-Excel 中可以吗？

每周，CRM 系统都会生成一个新的 Excel 文件，其文件名中包含日期戳，但放在固定位置（Sharepoint URL）的文件夹中。

该文件非常大，通常包含 25-30 千行。我需要根据此文件创建一个与我的目的相关的新表。

与我的目的相关的行必须与一个（或多个）关键字/关键短语相匹配，这些关键字/关键短语来自 30-40 个这样的关键字/关键短语列表。然而，这个关键字/关键短语列表也在增长，但速度很慢，每隔几个月就会添加一次新关键字。

有没有办法可以自动完成这项手动、繁琐且容易出错的任务？

答案1

因为需要检查三个不同的列（下面假设为B、D和），所以一个函数看起来很合适：FOR

=OR(ISNUMBER(MATCH(B1,keyarray,0)),
    ISNUMBER(MATCH(D1,keyarray,0)),
    ISNUMBER(MATCH(F1,keyarray,0)))

这样三列中的任何一个匹配项都会返回TRUE。将其放在数据所占第一行的“辅助”列中（例如第 1 行，或相应调整B1及D1上方F1）。

MATCH检查例如是否B1存在于的值keyarray，其中这是包含关键字/关键短语列表（数量约为 30-40）的范围的给定名称 - 不需要在同一张工作表或工作簿中，但如果不在同一张工作表或工作簿中，则必须指定完整路径，并建议在将公式应用于新一批数据时打开“其他”工作簿。

0仅强制精确匹配（或者-1对于大于或等于的最小值B1，或1最大值。）

MATCH返回数组中找到的值的位置（否则，使用参数0, #N/A）。这是数字，因此ISNUMBER测试数字（任何数字）——以排除#N/A结果。

因此，只要或中任何一个B1，结果将是– 否则。D1F1keyarrayTRUEFALSE

要方便地将公式向下复制到 25-30 千行（其中有些行包含合并单元格），请在最后一行和“辅助”列的交叉处放置一些内容（例如“end”）（以防止不必要地扩大电子表格的大小）。复制包含公式的单元格，选择其正下方的单元格，然后Ctrl+Shift+Down/Paste 向下填充所有占用行的辅助列，而不继续越过最后一行并覆盖“end”。

过滤“辅助”列TRUE，选择所有已占用的列，复制并粘贴到新工作表/工作簿中。删除新工作表/工作簿中的空白行并保存。（也可以选择从源中删除“辅助”列。）

确保偶尔向keyarray命名范围添加项目时能够覆盖添加的内容。

答案2

这是一项更大的任务，可以用各种各样的方法来解决。但简而言之，是的，你可以自动化它。

首先：

您是否绝对确定您的 CRM 系统无法直接提供您想要提取的信息？

通常它们基于数据库系统，如 SQL，并且因为您已经从中提取数据，所以您可能能够修改此输出以满足您的需要。

现在讨论 Excel 的可能性：

  1. 从 CRM 导入基础数据

您可能能够与 CRM 建立数据连接

您可以创建 VBA 代码来导入文件夹中的最新文件或更新与此新文件建立的数据连接

您可以通过 VBA 打开最新文件并将所需数据复制到目标工作表中

...

  1. 如果仍然需要，过滤数据

我建议您查看过滤器和高级过滤器、vlookup、countifs 和 vba 代码示例。这里和 stackoverflow 中有很多关于处理多条件过滤的问题，但您必须提供更多细节才能正确解决此问题。

建议：我会把这个任务分为获取基础数据和仅在必要时过滤数据。在我看来，应该可以在此过程中更早地获取过滤后的数据。或者告诉我其他方法 :)

相关内容