提问人：Seth William Gregg 提问时间：11/18/2016 最后编辑：Black catSeth William Gregg 更新时间：11/17/2023 访问量：663
Excel 数组 仅复制包含字符串的单元格的公式
Excel array Formula that copies only cells containing a string
问：
我更喜欢使用excel数组公式（但如果它只能在VBA中完成，那就这样吧）从包含特定文本的列数组中复制所有单元格。下图显示了我所追求的和我尝试过的。我越来越接近了（多亏了相似但不同的问题），但无法完全到达我想要的地方。目前，我只得到第一个单元格而不是所有单元格。在我的实际应用程序中，我正在搜索大约 20,000 个单元格，并将有几百个搜索词。我希望大多数搜索词会给我大约 8 - 12 个具有该值的单元格。
我正在使用的公式：
=INDEX($A$4:$A$10,MATCH(FALSE,ISERROR(SEARCH($C$1,$A$4:$A$10)),0))
答：
0赞
tigeravatar
11/18/2016
#1
为了有效地完成这项工作，我建议使用一个单独的单元格来保存结果计数（我使用了单元格 C2），其中包含以下公式：
=COUNTIF(A:A,"*"&C1&"*")
然后在单元格 C4 中向下复制使用此数组公式（只是因为标题行是第 3 行。如果标题行是第 1 行，则为）：-3-1
=IF(ROW(A1)>$C$2,"",INDEX($A$4:$A$21000,SMALL(IF(ISNUMBER(SEARCH($C$1,$A$4:$A$21000)),ROW($A$4:$A$21000)-3),ROW(C1))))
我用 A 列中的 21000 行数据对此进行了测试，每个搜索字符串平均有 30 个结果，并且将公式复制到 C 列的 60 个单元格中。有了这么多数据，大约需要 1-2 秒才能完成重新计算。重新计算时间可能会因工作簿中的其他因素（其他公式、嵌套依赖项、易失性函数的使用等）以及硬件而有很大差异。
或者，您可以只使用内置的过滤器功能，但我希望这会有所帮助。
评论
0赞
Seth William Gregg
11/18/2016
这似乎很有效。我曾在其他类似问题中看到过 ROW（），但无法弄清楚在这种情况下如何使用它
0赞
MacroMarc
11/20/2016
不要忘记勾选老虎的答案，因为它对您有所帮助
0赞
MacroMarc
11/18/2016
#2
您需要获取 ROWS.把它放在 C4 中并复制下来。
=IFERROR(AGGREGATE(15,6, IF(SEARCH($C$1, $A$4:$A$10)>0, ROW($A$4:$A$10)), ROW($C4)-ROW($A$4)+1), "")
数组公式，因此使用 ctrl-shift-Enter
评论
0赞
Seth William Gregg
11/18/2016
当我在单元格范围 C4：C4 中使用此公式时，我得到：4,6,7,10，这似乎是相对索引。还行。。我现在明白了。
0赞
MacroMarc
11/18/2016
所以：=IFERROR(INDEX($A:$A, AGGREGATE(15,6, IF(SEARCH($C$1, $A$4:$A$10)>0, ROW($A$4:$A$10)), ROW($C4)-ROW($A$4)+1)), "")
1赞
markwoollen
11/17/2023
#3
如何使用 FILTER：
=FILTER($A$4:$A$10,ISNUMBER(SEARCH($C$1,$A$4:$A$10)))
或。。。因为我通常建议使用表格......见 https://youtu.be/Ss3AY-oJHDo?si=93FfkMlm7d6lGyiZ...可以使用 Table[Column] 引用而不是固定单元格引用：
=FILTER(Table1[Array of Strings],ISNUMBER(SEARCH($C$1,Table1[Array of Strings])))
额外：您可以将其用于下拉列表（又名数据验证为列表）...见下图...只需将 Data Validation 添加到单元格 =$C$5# 中（请注意末尾的“#”，它告诉 Data Validation 使用动态数组。
=$C$5#
评论