☰

是否可以引用 Excel 中的两个单元格来查找第三个单元格？

tag-icon • tag-icon microsoft-excel microsoft-excel-2010 worksheet-function
是否可以引用 Excel 中的两个单元格来查找第三个单元格？

我有一个电子表格，上面有大量数据。我需要将其中一些数据返回到某些单元格。我需要返回的数据始终位于包含“附加组件”的单元格附近。问题是，有多个“附加组件”单元格。例如，我有两个部分，“第 1 部分”和“第 2 部分”，这两个部分中的每一个都有一个相对靠近的“附加组件”部分。它们所在的单元格也不会保持不变，否则我只会引用这些单元格。这是我目前使用的公式，用于返回一个部分的“附加组件”附近的数据：

=IFNA(INDEX(L15:R46,MATCH("Attached Components",M15:M46,0)+2,3),"0")

总而言之，我需要一个公式，该公式从引用“附加组件”的单元格返回数据，然后引用“部件#_”。

下面是“附加组件”的位置如何变化的示例，以及它相对于“部件 # 1”的位置。

在此处输入图片描述

这是一个非常具体的问题，我知道我的解释不是最清楚的。感谢您的帮助，欢迎随时询问更多具体细节！

答案1

我尝试在以下假设下让它发挥作用：

  • “附加组件”始终位于同一列。
  • 您实际上正在尝试查找每个“材料编号”的描述。

我将使用此表来完成以下工作：

例子

这可能不是您真正需要的，但我可以尝试根据您的评论来改进我的答案。

通过重复使用您的公式来确定列中“附加组件”的位置，然后添加 2，它给出了材料描述开始的相对行：

=MATCH("Attached Components",B1:B32,0)+2

结果如示例中的“7”所示。

之后，您需要确定描述所在的最后一行。要在正确的范围内搜索，公式需要根据“附加组件”所在的行进行更改。MATCH、ADDRESS、CONCATENATE 的组合将重新创建范围。

MATCH 为您提供相对行，ADDRESS 使用单元格名称（ADDRESS(1,1)="$A$1"）转换字符串中的行号和列号，CONCATENATE 将把字符串放在一起以创建一个范围。

CONCATENATE(ADDRESS(MATCH("Attached Components",B1:B32,0)+2,3),":",ADDRESS(MATCH("Attached Components",B1:B32,0)+20,3))

这将返回一个字符串，如“$C$7:$C$25”。因此，它涵盖了描述列，并从您有值的行开始到下 18 行。要覆盖更多或更少的行，只需将公式中的“+20”更改为适当的值。

查找最后一行只需使用 IF 和 MIN 查找第一个空单元格即可。

{=MIN(
    IF(
        INDIRECT(CONCATENATE(ADDRESS(MATCH("Attached Components",B1:B32,0)+2,3),":",ADDRESS(MATCH("Attached Components",B1:B32,0)+20,3)))="",
        ROW(INDIRECT(CONCATENATE(ADDRESS(MATCH("Attached Components",B1:B32,0)+2,3),":",ADDRESS(MATCH("Attached Components",B1:B32,0)+20,3))))
     )
)-1}

此公式是数组公式。因此，它带有括号（请勿键入括号，它们会在您输入公式并按 Ctrl+Shift+Enter 时出现）

INDIRECT 将我们构建的字符串转换为单元格引用。ROW 给出结果中的行号。MIN 将取返回范围内的最小值。末尾的“-1”表示最后一个描述的行号，而不是第一个空白行。

在示例中，此公式返回“9”。

现在我们有了第一个描述和最后一个描述的行号，即 7 到 9。我们可以按照需要使用 ADDRESS、CONCATENATE 和 INDIRECT 组合这些数字，以执行任何所需的操作。但这次您有一个特定的单元格引用可以使用。

例如，查找材料编号：

VLOOKUP 示例

在最后一个例子中，单元格包含

E2：

=MATCH("Attached Components",B1:B32,0)+2

F2（使用 Ctrl+Shift+Enter 输入）：

=MIN(
    IF(
        INDIRECT(CONCATENATE(ADDRESS(MATCH("Attached Components",B1:B32,0)+2,3),":",ADDRESS(MATCH("Attached Components",B1:B32,0)+20,3)))="",
        ROW(INDIRECT(CONCATENATE(ADDRESS(MATCH("Attached Components",B1:B32,0)+2,3),":",ADDRESS(MATCH("Attached Components",B1:B32,0)+20,3))))
    )
)-1

F7：

=VLOOKUP(E7,INDIRECT(CONCATENATE(ADDRESS(E2,1),":",ADDRESS(F2,3))),3,FALSE)

这样，当您在单元格 E7 中输入材料编号时，它会在单元格 F7 中显示描述。

编辑：

根据评论，可以这样解决问题：

使用更复杂的例子：

复杂的例子

行匹配只是 2 个 MATCH 函数的级联。使用第一个 MATCH 函数查找零件编号，然后使用第二个 MATCH 函数查找感兴趣的部分：

2 匹配

F3：您要查找的部分的字符串

F4：在第一列中查找“部件编号”的公式。

=MATCH($F$3,A1:A32,0)

F6：您要查找的部分的名称

F7：在之前确定的部件中查找节的公式。匹配在从“部件号”（存储在单元格 F4 中）的行开始的范围内进行。该范围使用与使用 INDIRECT、CONCATENATE、ADDRESS 相同的公式构建。然后，MATCH 返回的相对行被 F4-1 偏移以获得绝对行号。

=MATCH($F$6,INDIRECT(CONCATENATE(ADDRESS(F4,2,1),":",ADDRESS(F4+20,2,1))),0)+F4-1

现在，为了识别描述的第一行和最后一行，我们可以重复使用与以前相同的公式：

第一行和最后一行用于描述

F9：将“附加组件”行的行号加 2 以获得第一个描述行。

=F7+2

F10：查找描述范围内的第一个空白行（从 F9 中存储的行开始）。这是一个数组公式，需要使用CTRL+SHIFT+ENTER

=MIN(
    IF(
        INDIRECT(CONCATENATE(ADDRESS($F$9,3),":",ADDRESS($F$9+20,3)))="",
        ROW(INDIRECT(CONCATENATE(ADDRESS($F$9,3),":",ADDRESS($F$9+20,3))))
     )
)-1

然后，为了显示描述，我们可以使用 INDIRECT 和索引列：

显示矩阵

F15：

=IF($F$9+$E15-1<=$F$10,INDIRECT(ADDRESS($F$9+$E15-1,1)),"")

G15：

=IF($F$9+$E15-1<=$F$10,INDIRECT(ADDRESS($F$9+$E15-1,3)),"")

这些公式将显示 E 列中索引标识的行的材料编号和描述。IF 语句是为了确保我们不显示最后一行以下的行。在示例中，它仅显示 5 行，但您只需向下拖动第一行并添加新索引即可复制此公式以获得更多行。

相关内容