☰

我可以做些什么来避免不断改变我的 INDEX/MATCH 函数的第一个范围？

tag-icon • tag-icon microsoft-excel worksheet-function
我可以做些什么来避免不断改变我的 INDEX/MATCH 函数的第一个范围？

我创建了一个 INDEX/MATCH 函数，用于从主工作簿（供应主跟踪器）中的主供应风险工作表中检索数据，并将其填充到我的跟踪工作簿（个人供应跟踪器）的 20 列中

虽然我的公式有效，但当新行和新列添加到主工作簿时（每周发生一次），它似乎不会更新，因此我必须更新公式以反映新的列号。

这非常繁琐，因为我必须每周两次更改许多风险购买跟踪器工作簿中的 20 列公式，以匹配主工作表的新列号。我印象中 Index Match 函数允许添加新列。

我可以做些什么来避免不断更改 INDEX/MATCH 函数的第一个范围？也许将其链接到列的单独名称，即 (CE 注释) 与 (列 AK)？

示例：个人供应跟踪器：第 N 列（注释）单元格 N3 包含 =IFERROR(INDEX('[Supply Master Tracker.xlsx]FY21 Supply Risks'!$AH$2:$AH$263,MATCH($A3,'[Supply Master Tracker.xlsx]FY21 Supply Risks'!$C$2:$C$263,FALSE))&"","")

但是，跟踪器已更新，所以我不得不将其更改为： =IFERROR(INDEX('[Supply Master Tracker.xlsx]FY21 Supply Risks'!$AK$2:$AK$263,MATCH($A3,'[Supply Master Tracker.xlsx]FY21 Supply Risks'!$C$2:$C$263,FALSE))&"","")

如有任何建议，请告知。

编辑：数据按周跟踪供应缺口，因此每周都会添加三个新列

每周都会在主跟踪器工作表中增加三列新数据。它们包含当前周的跟踪、供应和收入。随着季度的进展，这些列会被隐藏，但是，每周增加这三列会随后改变添加行右侧显示的所有值。

表格格式——我不确定主表是否是表格格式，但我注意到第 300 行有数字参考，其中列出了 1-18 的列（CQ），然后它在添加 3 个每周列信息之前停止。

答案1

你的情况必须涉及获取列被转移的数据。也许这是从某个地方导出的数据，而这个地方有这样的变化，你无法影响它的创建或形式，或者你正在抓取一个网页。或者其他五十种方式。

我之所以这么说，是因为 INDEX/MATCH（以及较新的 XLOOKUP）确实可以避免此类列插入问题。Excel 会针对此类情况进行调整，并且不会失败。因此，数据必须已经放置好，这样它才会位于不同的列中。

没问题，因为可以解决这个问题。最简单的方法就是您问到的那个：根据不变的列标题搜索列。当然，它必须保持不变，否则您需要进一步调整一两个以方便使用，具体取决于具体情况。

那么，该怎么做呢？您将使用 match 获取您正在寻找的已知列标题，并对照标题行的范围（假设为第 1 行）以及当前数据范围中的所有列（可能目前远至 AZ 列，但比 AZ 列更靠右，假设为 BZ 列）。因此范围将是 A1:BZ1。您从 A 列开始，这样您的“匹配数”（标题所在列的列号）就易于使用。因此，如果数据范围实际上是 C1:AZ263（包括标题），您不必为 C 列（即范围中的第 1 列）获取匹配数，例如 1，然后添加 2 以获得更大范围中的第 3 列。更简单，无需调整。只要您的唯一列标题不在第 1 行的额外位置，这就是简单的方法。

如果您在列标题上找到了 MATCH 所得到的列，那么 ADDRESS 函数将使用该列和行号为您提供其 Excel 地址。您想从第 2 行开始查找查找值，因此您知道从第 2 行开始，并且正在学习为哪一列创建地址。今天，它变成了列 AK，也就是第 37 列。然后，ADDRESS 取第 2 行和第 37 列并输出地址 $AK$2。

您可以执行两次，一次给它第 2 行，一次给它第 263 行，得到 $AK$2 和 $AK$263。如果您将它们变成字符串并在&":"&它们之间插入，则得到：$AK$2:$AK$263。目前还不是“真正的男孩”，但 INDIRECT 函数会将这样的字符串变成一个真正的地址：INDIRECT($AK$2:$AK$263) 是 Excel 的真实地址，并将数据列返回到公式中。

您使用的 INDEX/MATCH 公式基本上分为两部分。内部部分是 MATCH 函数，它返回您需要的行；外部部分是 INDEX 部分，它获取该行并在其索引的数据列中查找该行。在下面的公式中，某人输入的查找值位于单元格 N3 中，它用于查找标记CE Comment为查找 N3 中查找值的索引（基本上是行）的列 AK。该索引在公式开头作为要返回的行传递给 INDEX 函数。INDEX 索引的范围是列 C，pig由于您没有给出其名称，因此我为其指定了标签。

因此，逻辑是：

  1. 在 N3 中输入查找值。

  2. 在标题列上使用 MATCH（两次）pig来查找其列号。

  3. 将其和起始行号传递给 ADDRESS 以获取查找值搜索范围的开始的正确地址。

  4. 将其和结束行号传递给 ADDRESS 以获取查找值搜索范围结束的正确地址。

  5. 将冒号插入上面的 3 和 4 之间。

  6. 将所有这些包装在 INDIRECT 中，使其成为 Excel 的实际范围。

  7. 使用 MATCH 和该范围的查找值来获取匹配值的索引。

  8. 如果高兴该列永远不需要这种查找，那么只需直接使用其 $C$2:$C$263 地址并跳过上述操作。

  9. INDEX/MATCH 的那半部分完成后，以完全相同的方式构建您告诉 INDEX 在其中查找答案的范围，只需使用列CE Comment标题。

  10. 所以现在，当列顺序发生变化时，您仍然可以CE Comment自动找到该列并始终得到正确的答案。

公式：

=INDEX(INDIRECT(ADDRESS(2,MATCH("CE Comment",A1:BZ1,0))&":"&ADDRESS(263,MATCH("CE Comment",A1:BZ1,0))),MATCH(N3,INDIRECT(ADDRESS(2,MATCH("pig",A1:BZ1,0))&":"&ADDRESS(263,MATCH("pig",A1:BZ1,0)))))

您需要更改列标题以匹配您的实际标题。其中的 2 看起来应该保持不变。如果表格向下增长，则可以更改 263。当然，您需要添加寻址字符串以放入引用，以便'[Supply Master Tracker.xlsx]FY21 Supply Risks'!正确完成 ADDRESS 生成的裸地址。如下所示：

INDIRECT("'[Supply Master Tracker.xlsx]FY21 Supply Risks'!" & ADDRESS(2,MATCH("pig",A1:BZ1,0)) & ":" &...etc.

这一切只是构建所需字符串的一部分。

在您开始工作后，有各种各样的方法可以“自动化”每一点。可以计算和/或定位起始行和结束行，等等。但这会让您顺利完成。

还有其他方法可以获取地址，如果您找到喜欢的方法，可以直接转换为该方法。

如果您有 XLOOKUP，那么就更容易了，在本例中是很多。如果您有 LET，那么对于查看公式的任何人来说，您可以让一切看起来更加清晰。

相关内容