☰

Excel 自动更新列

tag-icon • tag-icon microsoft-excel
Excel 自动更新列

我有以下两个 Excel 列。它们具有父子关系。例如，如果第 1 行单元格 D 的值为 1000000000，则它可能是另一行的父级。

我想要的是制作一个包含 1000 行的 Excel 表。我收到的 ID 结构不正确。我尝试将新 ID 从 1 开始并递增到 10，这样下一个 ID 就是 10。因此，使用这种模式，我想从 1 开始替换现有的新 ID，然后使用更新的新 ID 更新新 ParentID 列。

我试图编写一个公式来实现这一点，但我对 Excel 还很陌生。这是否必须使用宏来完成？有什么建议吗？

当前数据示例

 D           E
New ID      New Parent ID
1000000000  0
1100000000  1000000000
1100000001  1100000000
1100000002  1100000000
1100000003  1100000000
1100000004  1100000000
1100000005  1100000000
1100000006  1100000000
1100000007  1100000000
1200000000  1000000000
1200000001  1200000000
1200000002  1200000000
1200000003  1200000000
1200000004  1200000000
1200000005  1200000000
1200000006  1200000000
1200000007  1200000000
1200000008  1200000000
1200000009  1200000000
1200000010  1200000000
1200000011  1200000000
1200000012  1200000000
2000000000  0
2000000001  2000000000
2000000002  2000000000
3000000000  0
3100000000  3000000000
3100000001  3100000000
3100000002  3100000000
3100000003  3100000000
3100000004  3100000000

新数据示例 注意新模式。因此，新 ID 1000000000 被替换为 1，而 NewParentID 列中使用的 1000000000 也被替换为 1。依此类推。因此，本质上我可以轻松地在 NewID 中创建增量值，我只是不知道如何使用正确的 ID 更新第二列。

   New ID   New Parent ID
    1             0
    10            1
    1100000001    10
    1100000002    10
    1100000003    10
    1100000004    10
    1100000005    10
    1100000006    10
    1100000007    10

答案1

对于 Excel 新用户，除非必要，否则我不会使用 VBA。如果我正确理解了您的问题，您可以仅使用公式来解决它。我尝试使它们尽可能简单。

首先，我将通过处理源数据的图像来保留原始数据集。这对于审计目的很有用，并且可以帮助您更好地了解其工作原理。列将如下所示：

ID | 父母 ID | 新 ID | 新父母 ID
----------------------------------------------------
1000000000 | 0 | |
1100000000 | 1000000000 | |
1100000001 | 1100000000 | |
1200000000 | 1000000000 | |
1200000001 | 1200000000 | |
1200000002 | 1200000000 | |

（对于所有示例，我将使用相同的精简示例数据集。第一列是 A 列，列标题位于第 1 行。）

我们将在新 ID 列中为子元素赋予一个新 ID，并且其父 ID 将在新父 ID 列中自动赋值。

输入的 ID

现在，只需将 ID 值复制粘贴到新 ID 列中：

ID | 父母 ID | 新 ID | 新父母 ID
1000000000 | 0 | 1000000000 |
1100000000 | 1000000000 | 1100000000 |
1100000001 | 1100000000 | 1100000001 |
1200000000 | 1000000000 | 1200000000 |
1200000001 | 1200000000 | 1200000001 |
1200000002 | 1200000000 | 1200000002 |

然后在新父 ID 的第一个单元格中输入以下公式：=VLOOKUP($B2,$A:$C,3,FALSE)。此公式使 Excel 垂直向上查找。它读起来像这样：“在 A 列（ID 列）中查找单元格 B2（父 ID）的值，当找到完全匹配时，返回第三列（C 列，新 ID）中的值”。

考虑到我们的数据布局方式，它实际上意味着“在 ID 列中查找父 ID，当找到完全匹配时，返回其新 ID”。目前，新 ID 与原始 ID 相同，我们稍后会处理这个问题。如果您需要有关VLOOKUP在公式中使用函数（如）的更多说明，请单击fx公式栏旁边的按钮，然后单击“有关此函数的更多帮助”帮助链接。

只要您键入 Enter，公式就会被其结果替换，在这种情况下，就是错误值#N/A。这是因为在 ID 列中找不到第一个父代 ID。这是合乎逻辑的，因为最顶层的父代根据定义没有父代。

为了处理这种情况，我们将公式升级为=IF($B2=0,0,VLOOKUP($B2,$A:$C,3,FALSE))。它显示“如果单元格 B2 中的值为 0，则返回 0，否则查找 [...]”或“如果父 ID 为 0，则返回 0，否则查找其新 ID”。

现在是时候将此公式应用到整个列了，您只需轻松复制和粘贴即可完成：

ID | 父母 ID | 新 ID | 新父母 ID
1000000000 | 0 | 1000000000 | 0
1100000000 | 1000000000 | 1100000000 | 1000000000
1100000001 | 1100000000 | 1100000001 | 1100000000
1200000000 | 1000000000 | 1200000000 | 1000000000
1200000001 | 1200000000 | 1200000001 | 1200000000
1200000002 | 1200000000 | 1200000002 | 1200000000

这就是奇迹发生的地方

现在覆盖新 ID。如您的示例所示，将其替换1000000000为1。您将看到新父 ID 立即更新为新 ID。再次播放并将其替换1100000000为10：

ID | 父母 ID | 新 ID | 新父母 ID
1000000000 | 0 | 1 | 0
1100000000 | 1000000000 | 10 | 1
1100000001 | 1100000000 | 1100000001 | 10
1200000000 | 1000000000 | 1200000000 | 1
1200000001 | 1200000000 | 1200000001 | 1200000000
1200000002 | 1200000000 | 1200000002 | 1200000000

这很有趣！但重复一千次，就不值得了，对吧？

自动赋值 ID

这就是 Excel 的闪光点，因为一旦你完成了繁重的思考，它就会完成所有繁重的工作。在单元格 C4 中，输入以下公式：=C3+10。这将单元格 C3（即上面的单元格）中的值加 10，从而有效地将新 ID 增加 10。将其复制粘贴到列的末尾，一切就绪了：

ID | 父母 ID | 新 ID | 新父母 ID
1000000000 0 1 0
1100000000 1000000000 | 10 | 1
1100000001 1100000000 | 20 | 10
1200000000 1000000000 | 30 | 1
1200000001 1200000000 | 40 |三十
1200000002 1200000000 | 50 |三十

复制粘贴公式

最后要提醒一下。我猜你会在其他工作簿中使用新 ID 和新父 ID。在这种情况下，你可能想做一个特别的粘贴时只保留值，不保留公式。

假设您完成后想要删除原始 ID 和父 ID 列。在删除之前，请像平常一样复制新 ID 和新父 ID 列。然后，不要粘贴，而是使用命令并选择值，然后单击确定。右键单击目标以及其他地方即可使用special paste该命令。special paste

答案2

您可以使用宏来执行此操作，因为您想要的只是构建一个大型重复数据数组。我个人避免使用宏，大部分文本生成都在 Powershell 中完成。

现在，如果你想获取数据并对其进行一些计算，我们可以使用公式来实现。以下是我认为你需要的公式：

  • =IF(条件，真值，假值)——通过逻辑测试来做出决策。
  • =MOD(数据单元，除法器) - 返回除法后的余数。
  • =MROUND(数据单元，多个) - 四舍五入到最接近的定义倍数。

当我们使用公式时，我们需要列出我们想要按顺序采取哪些行动的逻辑。所有公式本质上都是程序性的，因此这有助于确定我们首先需要做什么。我假设所有输入都在“新 ID”列中，所有输出（又称回报）都在父 ID 列中。查看您的数据示例，我认为这就是您想要的。

  1. 查找为“1000000000”（10^9）因数的 ID 并返回 0。
  2. 查找为“100000000”（10^8）因数的 ID，并向下舍入为“1000000000”（10^9）因数的整数。
  3. 将剩余 ID 四舍五入为“100000000” (10^8) 的整数

需要计算的数字很大，因此这里有每个步骤的示例。

  • 步骤 1：700000000 返回 0
  • 步骤 2：770000000 返回 700000000
  • 步骤 3：770000007 返回 770000000

如果正确的话，让我们来设置您的功能：

步骤1

我们使用的第一个方程是“MOD”方程。如果我们将每个数字除以“1000000000”（10^9），我们可以查找余数为零的任何数字。我们找到的每个数字都是希望返回零的数字。为此，我们需要使用“IF”公式。以下是单元格 E2 中的公式：

=IF(MOD(D2,1000000000)=0,0,"false")

第一个 IF 参数测试 D2 中的数字除以 10^9 时是否有余数。如果没有，则返回零，如果没有，则返回文本“false”

第2步

现在我们已经处理了第一组数字，我们可以将 IF 语句添加到组合中以将逻辑链接在一起。现在我们需要“FLOOR”公式将数字四舍五入为有效数字。在本例中为八位数字。以下是单独使用公式时的样子：

=IF(MOD(D2,10000000)=0,MROUND(D2,1000000000),"false")

然后我们需要将其链接到步骤1中的公式中。用步骤2中的公式替换步骤1中的“false”。

步骤3

最后，我们只需要将剩余的数字向下舍入。我们可以使用另一个 MROUND 来完成此操作。以下是公式本身：

=MROUND(D2,100000000)

现在所有的事情都串联起来了：

=IF(MOD(D2,1000000000)=0,0,IF(MOD(D2,10000000)=0,MROUND(D2,1000000000),MROUND(D2,100000000)))

试一试，希望它能奏效。当我针对 D 列中的数据运行此公式时，我得到的数字与 E 列中的数字相同。

相关内容