☰

如何在 Excel 中查找 3 个搜索数字上方单元格中的值

tag-icon • tag-icon microsoft-excel microsoft-excel-2010 microsoft-excel-2007
如何在 Excel 中查找 3 个搜索数字上方单元格中的值

我想在数字列表中搜索其他数字的子列表。如果以相同的顺序找到子列表，则返回找到的子列表上方单元格中的值。

如果我使用这个公式 =INDEX(B3:B15,MATCH(D3,B3:B15,0)-1,1) （考虑 B 列中的数据表，我在输出中输入了以下公式），

输出仅显示一个数字。

但是，我需要输出所有 3 个连续数字，而不是每个数字。如果我在“3 个数字”单元格中输入 4,2,9，则输出应显示这 3 个数字。

在下面的例子中，我搜索三个数字 4、2、9。我第一次找到这些数字，并返回以黄色突出显示的 4，因为它位于我找到这些数字的位置上方。我还返回以黄色突出显示的 6，因为它位于我搜索的三个数字的上方。

单击此处获取示例数据

答案1

您可以使用宏来查找数字。按ALT+ F11。VBE 编辑器将打开。右键单击“此工作簿”，然后选择“插入”并选择“模块”。输入以下代码并按（或“播放”按钮）运行它。您也可以通过转到功能区的“开发人员”选项卡，单击“宏”，选择，然后单击 来F5启动宏。Find3NumbersRun

Sub FindNumberAbove3Cells()

    Dim i, j As Integer
    Dim fvalue, svalue, tvalue As Integer
    Dim found As Boolean

    'Clear the cells
    Range("F4") = ""
    Range("F5") = ""
    Range("F6") = ""

    found = False

    fvalue = Range("D4")
    svalue = Range("D5")
    tvalue = Range("D6")

    i = 4
    j = 4
    
    Do
        'Check if the 3 cells contain the 3 numbers we are searching
        If (Range("B" & i) = fvalue) And (Range("B" & (i + 1)) = svalue) And (Range("B" & (i + 2)) = tvalue) Then
            'Check of the content of the previous cell is a number
            If IsNumeric(Range("B" & (i - 1))) Then
                Range("F" & j) = Range("B" & (i - 1))
                j = j + 1
                found = True
            End If
        End If

        i = i + 1

        'Exit the loop when one of consecutive cells is empty
    Loop While Range("B" & (i + 2)) <> ""

    'If there are not values above the 3 consecutive cells, print #N/A on cell F4
    If found = False Then
        Range("F" & j) = "#N/A"

    End If
    
End Sub

找到数字后的结果

图片: 图片:

未找到号码时的结果

图片: 图片:

答案2

由于我没有 O365 并且 Excel Online，因此我无法测试某些内容FILTERXML，但可以E2尝试：

=FILTERXML("<t><s>"&TEXTJOIN("</s><s>",1,IF(MID(CONCAT(A2:A14),SEQUENCE(13),3)=CONCAT(C2:C4),INDEX(A1:A13,SEQUENCE(13)),""))&"</s></t>","//s")

我可以在线测试的东西（更丑陋）

=FILTER(IF(MID(CONCAT(A2:A14),SEQUENCE(13),3)=CONCAT(C2:C4),INDEX(A1:A13,SEQUENCE(13)),""),IF(MID(CONCAT(A2:A14),SEQUENCE(13),3)=CONCAT(C2:C4),INDEX(A1:A13,SEQUENCE(13)),"")<>"")

答案3

在此处输入图片描述

据我理解，目标是在较长的列表中找到搜索到的数字列表。如果找到搜索到的列表，则从较长的列表中返回找到的列表上方单元格中的数字。

我决定将问题分解为几个步骤。首先，使用公式来查找三个数字的列表。这使用 OFFSET，并且在某种程度上与 3 个数字有关，但它有效并且可以在必要时进行扩展。

这是我的图像 D 列中的公式：

=AND(C4=$A$4,OFFSET(C4,1,0)=OFFSET($A$4,1,0),OFFSET(C4,2,0)=OFFSET($A$4,2,0))

该公式的基本含义是“如果 C 列中当前行 (4) 的值等于 A 列中列表中的第一个值，并且该值下方的值等于第二个值，并且该值下方的值等于那等于第 3 个值，则为 TRUE，否则为 FALSE”。通过向下拖动，我们得到一列 TRUE 和 FALSE。TRUE 表示找到的子列表的顶部。

为了获取找到的子列表上方的数字，我们可以再次使用 OFFSET。我图片中 E 列的公式是：

=IF(D4,OFFSET(C4,-1,0),"")

因此，如果 D 列为 TRUE（我们找到了子列表），则从上面一行的单元格中获取值。

您可以将 D 列和 E 列的公式合并为一个公式：

=IF(AND($C4=$A$4,OFFSET($C4,1,0)=OFFSET($A$4,1,0),OFFSET($C4,2,0)=OFFSET($A$4,2,0)),OFFSET($C4,-1,0),"")

如果您需要将输出返回到连续单元格的短列表中，则可以在 Excel 365 之前的版本中使用此功能：

={IFERROR(IF(COUNTIF($E$3:$E$16,"?*")<1,"",INDEX(E:E,SMALL(IF($E$3:$E$16<>"",ROW($E$3:$E$16)),ROWS(E$3:E3)))),"")}

（说实话，这很困难而且模糊）

在此处输入图片描述

或者，如果您拥有最新版本的 Excel 365 并且可以使用动态数组，则您可以直接使用以下命令：

=FILTER($E$4:$E$16,$E$4:$E$16<>"")

当然，可以在 E 列或 F 列上完成。

编辑：

要将其扩展为四个数字的列表，只需向 AND 函数添加一个新子句：

=IF(AND($C4=$A$4,OFFSET($C4,1,0)=OFFSET($A$4,1,0),OFFSET($C4,2,0)=OFFSET($A$4,2,0),OFFSET($C4,3,0)=OFFSET($A$4,3,0)),OFFSET($C4,-1,0),"")

相关内容