☰

在 Excel 数组中查找（文本）值

tag-icon • tag-icon microsoft-excel microsoft-excel-2010 vba
在 Excel 数组中查找（文本）值

我在互联网上搜索并在 Excel 2010 中尝试了一下，但并没有找到答案。

为了整洁起见我做了一个摘要，但实际文件要大得多。

图片1

在另一张表中，我想查找文本 A 并获取相对时间的输出（B 列）。但是，如果第 2 行延伸到第 Z 列（甚至更多），我希望所有输出时间都返回相同的值，如下图所示的 A、B 和 C。

图像2

当我修复条件格式时，D 列可能会消失/隐藏。但也有可能“K”位于“H”之前，因此值可能不按顺序排列（如 F、G 和 H 所示）

这是第一个问题。我尝试使用 INDEX-MATCH 解决它，但对于未知数量的行和列，这非常繁琐。还有很多空白，所以我也需要为此构建保险箱。

仅 6 列的公式如下所示：

=IFERROR(INDEX(T0.02!$B$1:$B$100;IF(ISNA(MATCH($A3;T0.02!$D$1:$D$100;0))=FALSE;MATCH($A3;T0.02!$D$1:$D$100;0);IF(ISNA(MATCH($A3;T0.02!$E$1:$E$100;0))=FALSE;MATCH($A3;T0.02!$E$1:$E$100;0);IF(ISNA(MATCH($A3;T0.02!$F$1:$F$100;0))=FALSE;MATCH($A3;T0.02!$F$1: $F$100;0);如果(ISNA(MATCH($A3;T0.02!$G$1:$G$100;0))=FALSE;MATCH($A3;T0.02!$G$1:$G$100;0);如果(ISNA(MATCH($A3;T0.02!$H$1:$H$100;0))=FALSE;MATCH($A3;T0.02!$H$1:$H$100;0);如果(ISNA(MATCH($A3;T0.02!$I$1:$I$100;0))=FALSE;MATCH($A3;T0.02!$I$1:$I$100;0);"")))))));"")

对我来说，主要问题是：是否有可能在 Excel 中以更简单的方式完成此操作，或者是否有使用 VBA 的方法完成此操作。我不太擅长使用 VBA，所以我真的需要这方面的帮助。

答案1

您可以使用“数组公式”相对简单地完成此操作，而无需添加任何列，例如对于直到 Z 列的范围

=MIN(IF(T0.02!$D$2:$Z$100=$A3;T0.02!$B$2:$B$100))

确认CTRL+SHIFT+ENTER

如果没有匹配，则结果为零；如果您想要空白，则为了区分合法的零值，可以使用 SMALL 和 IFERROR，即

=IFERROR(SMALL(IF(T0.02!$D$2:$Z$100=$A3;T0.02!$B$2:$B$100);1);"")

答案2

这是一种使用一些 VBA 来简化查找而无需任何辅助单元的方法。

VBA 部分是这个用户定义函数（通常称为 UDF），其用途与您尝试用来查找文本所在行的 MATCH 函数相同：

Function FindRow(valueToFind, searchRange As Range, Optional IsCaseSensitive As Variant) As String
   Dim aCell As Range
   Dim lastCell As Range
   If IsMissing(IsCaseSensitive) Then
      IsCaseSensitive = False
   End If
   Set lastCell = searchRange.Cells(searchRange.Rows.Count, searchRange.Columns.Count)
   Set aCell = searchRange.Find(valueToFind, After:=lastCell, LookIn:=xlValues, MatchCase:=IsCaseSensitive, LookAt:=xlWhole)
   FindRow = aCell.Row - searchRange.Cells(1, 1).Row + 1
End Function

它之所以有用，是因为 VBA FIND 函数与工作表 MATCH 函数不同，它可以搜索跨越多列和多行的范围。与 MATCH 一样，此 VBA 函数返回在搜索范围内找到指定值的行，如果找不到该值，则返回 #VALUE! 错误。

FindRow 的前两个参数是需要查找的值（或该值所在的单元格）和要查找的范围。还有第三个可选参数：如果设置为 TRUE 或 1，则查找将区分大小写；如果省略或设置为 FALSE 或 0，则查找将不区分大小写。

在第二张表中，您将使用 INDEX 与 FindRow 结合来查找时间，例如，

=INDEX(Sheet1!$B$2:$B$8,FindRow(A3,Sheet1!$D$2:$Z$9))

FindRow 的第二个参数设置为包含注释值的范围。虽然我已显示搜索范围延伸至第 Z 列和第 9 行，但 FindRow 可以处理任意大小的范围。

要安装 VBA 函数，首先从功能区上的“开发人员”选项卡中选择“Visual Basic”。在左上角的项目窗格中选择工作簿名称（工作簿将被指定为“VBAProject（YourSheetName）”），然后使用顶部的菜单栏插入模块。最后，将函数代码粘贴到右侧出现的大代码窗格中。

答案3

我使用第一张表中的 C 列作为临时列：

  1. 输入表中的C列使用：
    =","&D3&","&E3&","&F3&","&G3&","&H3&","&I3&","&J3&","&K3&","&L3&","&M3&","&N3&","&O3&","&P3&","&Q3&","&R3&","&S3&","&T3&","&U3&","&V3&","&W3&","&X3&","&Y3&","&Z3
    - 将所有可能的评论合并到一个单元格中
  2. 在聚合表的 C3（和其他单元格）中：在辅助列中的所有组合单元格中搜索注释，并在索引函数中使用它来返回时间：
    =IFERROR(INDEX(Sheet1!$B:$B,MATCH(FALSE,ISERROR(FIND(","&A3&",",Sheet1!$C:$C)),0)),"")
    将其作为数组公式输入，即使用Ctrl- Shift- Enter。

呼呼！

相关内容