☰

删除重复的条目，仅保留最新的

tag-icon • tag-icon microsoft-excel-2010
删除重复的条目，仅保留最新的

我有几张包含需要整合的信息的电子表格，每张表格都包含我希望创建的最终数据集的不同部分（有时是重叠的）。通常，只需将所有数据转储到一张工作表中并使用“删除重复项”，即可轻松合并包含类似数据的电子表格。但是，我想确保留下的行项目代表重复项的最新版本。

我将从每个电子表格导入以下数据：

  • 唯一标识符字段 (UID) 用于标识行项目的主题并用于发现输入表之间的重复项。
  • 状态字段 (STATUS) 将包含有关在唯一标识符字段中调用的项目的信息。
  • 日期字段 (DATE) 包含导入数据的最初写入日期。

我需要的是我的输出只包含每个 UID 的一行项目，并且来自 STATUS 字段的数据与输入表中该 UID 的最新 DATE 匹配。

在 Excel 中执行此操作的最简单的方法是什么？

答案1

我不知道这是否保证有效，但对我来说似乎有效（在 Excel 2007 中进行小规模测试）：获取合并的数据表，并按日期反向排序，因此最新行位于较旧行之上。然后删除重复项。

本网站证实了这种行为：“当 Excel 扫描表格时，它会删除任何具有与早期记录相同的产品 ID 的后续记录，即使其余数据不同。”

答案2

这里有几个步骤的解决方案，假设您可以手动完成其中的一些操作，而不需要单个完全自动化的解决方案:(如果您这样做，我相信您可以从这里开始......）

  1. Excel 不是数据库。
  2. 将所有数据转储到一张表中。（为了举例说明，我假设 A 列中有 UID，B 列中有 DATE，C 列中有 STATUS）。
  3. 在第二张表中，执行删除重复项在 UID 列上仅有的. （例如，仅复制过滤的唯一项，或复制整列然后执行标准的删除重复项）。

  4. 在 DATE 列中，添加以下 Array* 公式：

    {=MAX(IF(数据表！A：A=A1，数据表！B：B))}

    这基本上会选择每个 UID 的最新日期。（这当然是针对第一行，请确保用 A1、A2……填充所有其余行）

  5. 在 STATUS 列中，添加以下数组公式：

    {=INDEX(IF(数据表！A：A=A1,IF(数据表！B：B=B1,数据表！C：C)),MATCH(TRUE,IF(数据表！A：A=A1,IF(数据表！B：B=B1,TRUE)),0))}

（再次注意第一行，填充其余部分）。

这个比较复杂，我们来分解一下：

如果（数据表！A：A=A1，如果（数据表！B：B=B1，数据表！C：C））

此数组公式只是执行了具有两个条件的 SQL WHERE 子句的等效操作：对于所有同时匹配 UID（A 列）和 DATE（B 列）的行，返回 C 列（STATUS）中的行的值。

MATCH(TRUE,如果(数据表！A：A=A1，如果(数据表！B：B=B1，TRUE)),0)

第一个公式应该已经足够好了，但由于我们没有办法只提取非空（或非 FALSE）值，并且 Excel 没有 COALESCE 公式，所以我们需要采取一些间接方法。MATCH
公式搜索 IF 返回的数组（条件与上面相同，但如果匹配则返回 TRUE），以查找第一个 TRUE 值。第三个参数 0 要求完全匹配。
此公式仅返回与前面的条件（匹配 UID 和 DATE（匹配 UID 的最大日期））匹配的第一行（也是唯一一行）的索引。

{=索引（如果（往上看）， 匹配（往上看））}

现在很简单，从 MATCH 中获取匹配行的索引，并从 IF 数组中提取相应的 STATUS 值。这将返回一个值，即您的新 STATUS，保证（如果您正确完成了所有这些步骤）来自每个 UID 的最新日期。

6 Excel 不是数据库。

* 脚注：如果你不熟悉数组公式（虽然我认为你熟悉），看到这个：基本上，您输入应产生值数组的原始公式（不带波浪线 {}），然后按CTRL+ SHIFT+ ENTER。 Excel 会为您添加波浪线 {} ，并将所有值计算为一个数组。

* 脚注 #2：说真的，EXCEL 不是数据库。;-)

答案3

@AviD 是正确的，因为 Excel 不是数据库，但您可以通过 Microsoft Query 数据源将数据导入另一个电子表格。虽然有点丑陋，但可以让您访问 SQL 语句，从而获得所需的数据。

  1. 在新的电子表格中，转到“数据”选项卡，在“获取外部数据”组中选择“来自其他来源...”和“来自 Microsoft Query”。

在此处输入图片描述

  1. 选择 Excel 文件并选择您保存的数据
  2. 如果出现错误，提示找不到任何可见表格，只需单击“确定”，然后在“选项”对话框中从显示列表中选择“系统表”。这样您就可以访问工作表中的工作表

在此处输入图片描述

  1. 将 UID、状态和日期列添加到查询中

在此处输入图片描述

  1. 下一步...下一步...下一步并选择查看数据或在 Microsoft Query 中编辑查询并选择完成

在此处输入图片描述

  1. 现在您得到的工作表看起来有点像早期版本的 Access。

在此处输入图片描述

  1. 单击 SQL 按钮，您就可以访问查询本身，我认为您需要将其更改为如下所示的内容（使用 GROUP BY 和 MAX 获取最新日期）：

    从 .UID、.Status 中选择Sheet1$.UID、Sheet1$.Status、Max( Sheet1$.Latest) C:\Users\rgibson\Desktop\Book8.xlsx。按.UID、.StatusSheet1$ Sheet1$ 分组Sheet1$Sheet1$

    1. 您可以关闭查询并选择将数据导入到哪里：

在此处输入图片描述

答案4

如果其他方法都失败了，请将所有数据合并到一张工作表中，按日期排序（确保格式相同），然后将最新日期复制到新电子表格中，再将下一个最新日期复制到新电子表格中，依此类推。转到“数据”选项卡删除重复项。由于 Excel 会保留输入到电子表格中的第一个记录并删除下一个出现的重复项，因此这应该有效。

相关内容