☰

需要在文本或 Excel 中向系列添加缺失的迭代 ID 号。如何在不编写代码的情况下完成此操作？

tag-icon • tag-icon microsoft-excel regex
需要在文本或 Excel 中向系列添加缺失的迭代 ID 号。如何在不编写代码的情况下完成此操作？

我有一个文本、csv 或 excel 文件，如下所示

||--ID-----||--Name--||--Date of birth--||
    1            Jo          1/1/11
    32           Mo          2/2/12
    3382         Ro          3/3/10
    21,252       Do          4/4/09

实际数据集包含 1,000,000 行中的 280,000 行。我需要以某种方式添加所有缺失的 ID 号（它们都是连续的 +1 迭代）以及姓名和出生日期的空白字段。这样我得到的结果是：

||--ID-----||--Name--||--Date of birth--||
    1,           "Jo",     "1/1/11"
    2,           "",         ""
    3,           "",         ""
    4,           "",         ""

一直到 32，然后再重复，直到每个整数都存在。有没有简单的方法可以做到这一点？不用在循环中编写代码块？任何来自 Excel、某些应用程序或任何 Windows 文本编辑器的技巧都将不胜感激。

编辑：请忽略逗号、引号等。缺少的 ID 号是唯一关键的东西。

答案1

一种方法是：

  1. 将您的 CSV 导入到 Excel 作为表 1。
  2. 在工作表 2 上，使用公式（例如 a2: =a1+1）构建完整的 ID 号码列表，然后向下复制。
  3. 在其他两列中使用 vlookup 公式，参考工作表 1 上的表格。例如 b2：=VLOOKUP(A2,Sheet1!A2:C13,2,false)和 c2：，=VLOOKUP(A2,Sheet1!A2:C13,3,false)或者为了完全匹配您的请求，让我们将其包装到 IFNA 语句中，如果没有值则返回“”。b2：=IFNA(VLOOKUP(A2,Sheet1!A2:C13,2,FALSE),"")和 c2：（=IFNA(VLOOKUP(A2,Sheet1!A2:C13,3,FALSE),"")现在可以将这些公式复制到列下。）
  4. 填充后，将表 2 保存为 CSV。

注意：为了使其正常工作，需要按第 1 列对工作表 1 中的表格进行排序。从给出的信息来看，似乎是这样，但如果不是，则按第一列对信息进行排序。

对于一百万行数据，这实际上会在很多计算机上变得非常慢，我在编写公式之前没有注意到这一点。我曾经对一个大型数据集做过类似的事情，通过关闭自动重新计算来获取所有公式，然后进行手动重新计算。这花了几个小时，但确实正确完成了。

答案2

我将使用 Power Query 插件来解决这个问题。

我已经构建了一个原型，您可以查看或下载它 - 我的 One Drive 中的“Power Query 演示 - 将缺失的 ID 号添加到系列.xlsx”：

https://onedrive.live.com/redir?resid=4FA287BBC10EC562%21398

需要几个步骤才能实现，还需要使用 Power Query 语言 (M) 编写一些代码来调用 List.Numbers 函数（该函数未在 Power Query UI 中显示）。不过，这只是一行简单的代码 - 其余部分可以通过在 Power Query 中单击来构建。

基本上，我的技术是使用 List.Numbers 来生成一个 ID 号表，然后我添加一个合并来从输入数据（ID 号所在的位置）中获取列。

List.Numbers 的文档在这里：

http://office.microsoft.com/en-au/excel-help/list-numbers-HA104111648.aspx?CTT=5&origin=HA104122363

非常感谢 Matt Masson 提供的“设置”技术。

http://www.mattmasson.com/2014/04/defining-configurable-settings-for-your-queries/

注意 Power Query 可以直接从 CSV 文件读取，我将使用该文件作为“输入数据”查询的来源。最简单的方法是删除该查询，然后从 CSV 文件构建一个新查询并将其命名为“输入数据”。取消选中“加载到工作表”选项以节省资源。

相关内容