☰

在 Excel 中，我需要找到两个数组之间的数据匹配 - 一个水平，一个垂直

tag-icon • tag-icon microsoft-excel microsoft-excel-2007 microsoft-excel-2010 worksheet-function array
在 Excel 中，我需要找到两个数组之间的数据匹配 - 一个水平，一个垂直

在 Excel 2010（或 2007 - 我都有，尽管我的操作系统只有 Win7 32 位，因为我们运行的一些旧版应用程序的限制）中，我需要找到如何从两个数据数组中查找并返回匹配的值。

我有两个电子表格。一个是来自分层 OLAP 多维数据集维度的大型平面文件（来自 SAP BPC 的 37,000 行）。另一个是我需要匹配的值表。我需要将第二个电子表格中的匹配值返回到第一个工作表（平面文件）中的 ColumnA 中。

挑战在于，由于它是一个层次结构，我无法从 Sheet1 中选择单个列进行匹配——匹配项可能在每行的任何列中。所以，基本上，我需要将 Sheet 1 单行与 Sheet 2 列之间匹配的内容作为一个数组（我认为）。

用英语来说，我希望 Excel：对于 Sheet1 中每一行有数据的地方，查看整行的所有内容（例如，范围 B2:R2 - 我将 A 列留空，作为公式/匹配值）。如果那里的任何内容与报告类别列表中的任何内容相匹配（即 Sheet 2 的 A 列，范围 A1:A42），则将 Sheet2 的值返回到 Sheet1!A2（我为匹配而创建的空白列）。

这是带有食物寓言的数据样本。请注意，我创建了一个空白的 ColumnA，并且每行中的数据都按照分类层次向上发展，其中 ColB 是基础级别，并且如果需要，它会重复，以便终端父级位于 ColF 中。：

在此处输入图片描述

现在，下图是我想要使用的报告格式。你看，有时我们需要某些层级的数据，有时我们需要其他层级的数据。

在此处输入图片描述

最后，我的电子表格将填充我想要的自定义报告类别（然后我可以根据这些类别获取汇总数据）。在此处输入图片描述

我一直通过庞大的 vlookup 公式来实现这一点，但我想知道是否有其他更简单或至少资源密集程度更低的方法，因为 37,000 行带有 8 层嵌套的 vlookup 语句会使 Excel 经常崩溃。因此，使用我的真实报告类别（sheet2 称为 All_Budget_Units），以下是我目前使用的方法：

=IFERROR(VLOOKUP(IFERROR(VLOOKUP(IFERROR(VLOOKUP(IFERROR(VLOOKUP(IFERROR(VLOOKUP(IFERROR(VLOOKUP(C2,所有预算单位!$A$1:$A$39,1,FALSE),D2),所有预算单位!$A$1:$A$39,1,FALSE),E2),所有预算单位!$A$1:$A$39,1,FALSE),F2),所有预算单位!$A$1:$A$39,1,FALSE),G2),所有预算单位!$A$1:$A$39,1,FALSE),H2),所有预算单位!$A$1:$A$39,1,FALSE),I2)

答案1

YMMV，但查看 -> 宏，添加一个宏。尝试一下（根据需要更改单元格引用）：

Dim data, reference As Range

Set reference = Worksheets("Sheet2").Range("A1", "A42")
Set data = Worksheets("Sheet1").Range("B2", "F6")

For Each dataCell In data
    For Each referenceCell In reference
        If dataCell.Value = referenceCell.Value Then
                Worksheets("Sheet1").Cells(dataCell.Row, 1).Value = dataCell.Value
        End If
    Next
Next

[编辑：如果此方法有效，您可以在找到匹配项时停止搜索行，从而加快速度。（假设每行只有一个可能的匹配项）。例如：

Sub newtest()
    Dim data, reference As Range
    Dim skipsome As Boolean
    skipsome = False

    Set reference = Worksheets("Sheet2").Range("A1", "A7")

    Set data = Worksheets("Sheet1").Range("B2", "F6")
    For Each dataCell In data
        For Each referenceCell In reference
            If dataCell.Value = referenceCell.Value Then
                    Worksheets("Sheet1").Cells(dataCell.Row, 1).Value = dataCell.Value
                    skipsome = True
                    Exit For
            End If

            If skipsome = True Then
                skipsome = False
                Exit For
            End If
        Next
    Next
End Sub

仅根据您的 5 行测试数据，单元格比较测试就会从 175 降至 132。] [编辑 2：使代码正常运行]

答案2

您不需要对此使用宏解决方案；您可以使用数组公式。

{=INDEX(All_Budget_Units!$A$1:$A$39, MAX(IFERROR(MATCH(C2:I2, All_Budget_Units!$A$1:$A$39, 0), 0)))}

这是假设 I2 中的值也在主列表中，而您的原始公式并未假设这一点。如果不存在或可能不存在，请使用以下公式：

{=IFERROR(INDEX(All_Budget_Units!$A$1:$A$39, MAX(IFERROR(MATCH(C2:H2, All_Budget_Units!$A$1:$A$39, 0), -1))), I2)}

如果您以前没有使用过数组公式，则无需自己输入括号 {}：输入公式的其余部分，然后按 CTRL+SHIFT+ENTER 将其作为数组公式输入。如果您操作正确，括号将出现在公式框中。

其工作原理是，我们使用MATCH函数创建一个匹配结果数组。除了其中一个之外，其余都是，#N/A所以我们用 将其包装起来，以IFERROR将它们转换为 0（或在第二个版本中转换为 -1）。任何实际匹配都将是一个正数，因此取MAX数组的 会找到单个匹配。然后我们使用函数INDEX将其转换为值。在第二个版本中，如果根本没有匹配，我们将尝试使用INDEX位置 -1，这将产生错误，因此我们使用IFERROR来返回默认值。

相关内容