☰

查找最接近的匹配项（条件查找）

tag-icon • tag-icon microsoft-excel worksheet-function microsoft-excel-2010 vba search-indexing
查找最接近的匹配项（条件查找）

我正在尝试在 Excel (MacOS) 中进行条件查找。假设我有下面显示的表格，我想搜索 (type=alpha, length=165)。在表格中，没有完全匹配，因此我尝试对满足以下条件的行进行近似搜索：

  1. 条目的类型必须匹配。在本例中，条目必须具有 alpha 类型。
  2. 条目的长度必须在实际长度的 20 个单位之内。在本例中，实际长度为 165，因此条目的长度必须在 145 到 185 之间。
  3. 如果有多个条目满足条件，则返回长度最接近的条目。在本例中，第 1 行和第 2 行都符合条件，但第 2 行的长度最接近 165，因此选择第 2 行。

如果找到这样的条目，我希望代码返回该条目的 ID。因此，如果我查找 (type=alpha, length=165)，程序应该返回 al2。

我尝试使用索引和匹配。但由于我有一个相等条件和一个不等条件，我无法让它工作。有没有办法使用 excel 函数来解决这个问题，还是我需要使用 VBA？

一种） B（长度） C（内径）
1 α 150 al1
2 α 170 al2
3 α 190 al3
4 α 三十 al4
5 测试版 200 是1
6 测试版 40 是2
7 测试版 三十 是3
8 伽马 150 ga1
9 伽马 450 ga2

答案1

该问题标记了相对较旧的 Excel 版本，因此下面的解决方案中使用了辅助列。

在此处输入图片描述

如果类型和长度值满足指定输入类型且在指定输入长度的 20 以内的标准，则 Fit_Criteria 列中的值为 TRUE，否则为 FALSE。

如果 Fit_Criteria 为 FALSE，则 Length2 列中的值将取 21。否则，该值将设置为 Length 与指定输入长度之间的绝对差。找到 Length2 值最小的行（几乎）即可解决问题。

如果 Length2 中的值等于此列的最小值，则 nearest 中的值为 TRUE和Fit_Criteria 为 TRUE。

解决方案是第一个最接近为 TRUE 的行。如果最接近列中的所有条目都是 FALSE，则没有解决方案。

没有辅助列可以解决这个问题吗

辅助列 Fit_Criteria 依赖于输入的类型值（在单元格中）I3。此外，其他两个辅助列都依赖于 Fit_Criteria，因此更改输入的类型值将可能更改所有 3 个辅助列中的值。如果需要匹配多组输入，就会出现问题，因此有必要考虑是否有可能在不借助辅助列的情况下解决问题。

对于最新版本的 Excel (Office 365)，答案显然是“是”。解决方案如下所示，其中列E和F代表三组不同的输入，相应的输出位于列中G。

无助手截图

在扩展格式中，单元格中的公式G2为

=LET(
     criteria,(ABS(B$2:B$10-F2)<=20)*(A$2:A$10=E2),
     length2,IF(NOT(criteria),21,ABS(B$2:B$10-F2)),
     closest,(length2=MIN(length2))*criteria,
     INDEX(C$2:C$10,MATCH(1,closest,0))
    )

此公式只是使用非常强大的LET函数来有效地定义称为的变量criteria，length2以及closest与辅助列具有相同的用途。

为了便于解释，与之前的解决方案不同，我没有捕获“不匹配”条件，但IFERROR围绕最后一个INDEX参数包装一个函数很容易添加这个条件。

该LET函数在 Excel 2016 中不可用，那么是否可以在不使用的情况下避免使用辅助列LET？

答案可能是“是”。可能是因为下面建议的公式使用了数组函数（通过使用公式栏中的 Control、Shift 和 Enter 键输入），这在 Excel 2016 中是允许的，但我没有可用的版本来测试该公式。

同样，在扩展格式中，单元格的公式G2可以重写为

=IFERROR(
         INDEX(
               C$2:C$10,
               MATCH(
                     1,
                     (
                      (
                       (
                        (A$2:A$10=E2)*
                        (ABS(B$2:B$10-F2)<=20)
                       )*
                       ABS(B$2:B$10-F2)
                      )+
                      (1-
                         (
                          (A$2:A$10=E2)*
                          (ABS(B$2:B$10-F2)<=20)
                         )
                      )*
                      21=
                      MIN(
                          (
                           (
                            (A$2:A$10=E2)*
                            (ABS(B$2:B$10-F2)<=20)
                           )*
                           ABS(B$2:B$10-F2)
                          )+
                          (1-
                             (
                              (A$2:A$10=E2)*
                              (ABS(B$2:B$10-F2)<=20))
                          )*
                          21
                         )
                    )*
                    (A$2:A$10=E2)*
                    (ABS(B$2:B$10-F2)<=20),
                     0
                    )
              ),
         "No Match"
        )

请注意，该短语(A$2:A$10=E2)*(ABS(B$2:B$10-F2)<=20)在此公式中出现了五次。这实际上是criteria上一个函数中使用的数组LET。

相关内容