我在 Excel 2016 中有一堆文本字符串。我试图搜索各种子字符串。为了对审阅者更有帮助，我想返回包含该子字符串的单词（以空格分隔）。
例如，如果我的字符串是 ，而Will be retiring in June我的子字符串是ret，我希望公式返回retiring。这将允许审阅者区分retired或。retiringreturn
我编写了以下公式。有多个主字符串需要搜索。它们位于 H - M 列中。有多个子字符串需要搜索；它们位于单元格 AQ1 - BF1 中。我希望找到的单词填充 AQ2 - BF60000。
AP1 将确定要搜索哪个主列。如果单元格 AP1 为 H，则 AQ2 将从单元格 $H2 返回找到 AQ$1 的单词。如果 AP1 更改为 I，则 AQ2 将返回单元格 $I2 中的单词。这只是为了减少所需的公式数量，一次只查看一列。
这是一张图片。
| H | 我 | ... | 美联社 | 空气质量 | 应收账款 | 作为 | ... | |
|---|---|---|---|---|---|---|---|---|
| 1 | 工作状态 1 | 工作状态 2 | ... | H | 保留 | 卷 | 乌内姆 | ... |
| 2 | 残疾问题 | FD 志愿者 | ... | ＃价值！ | ＃价值！ | ＃价值！ | ... | |
| 3 | 仍处于失业状态 | 2022 年 4 月归还 | ... | ＃价值！ | ＃价值！ | 失业 | ... | |
| 4 | 还没回来，做志愿者 | 将于 2023 年退休 | ... | 回 | 志愿服务 | ＃价值！ | ... | |
| 5 | 2017 年退役 | ... | 已退休 | ＃价值！ | ＃价值！ | ... |
但是如果我将 AP1 改为 I 而不是 H，表格就会变成
| H | 我 | ... | 美联社 | 空气质量 | 应收账款 | 作为 | ... | |
|---|---|---|---|---|---|---|---|---|
| 1 | 工作状态 1 | 工作状态 2 | ... | 我 | 保留 | 卷 | 乌内姆 | ... |
| 2 | 残疾问题 | FD 志愿者 | ... | ＃价值！ | 志愿者 | ＃价值！ | ... | |
| 3 | 仍处于失业状态 | 2022 年 4 月归还 | ... | 回 | ＃价值！ | ＃价值！ | ... | |
| 4 | 还没回来，做志愿者 | 将于 2023 年退休 | ... | 退休 | ＃价值！ | ＃价值！ | ... | |
| 5 | 2017 年退役 | ... | ＃价值！ | ＃价值！ | ＃价值！ | ... |
该公式有效，但速度有点慢，因为我总共需要计算大约一百万个单元格。有什么方法可以优化或完全重写它吗？这是一个工作项目，所以我无法升级到 Excel 2016 以上版本或安装任何东西。
=MID(INDIRECT($AP$1&ROW()),
这将找到单词的开头（预先找到第一个空格并加 1）。
IFERROR(FIND("|",
SUBSTITUTE(LEFT(INDIRECT($AP$1&ROW()),
SEARCH(AQ$1,INDIRECT($AP$1&ROW()))
),
" ",
"|",
LEN(LEFT(INDIRECT($AP$1&ROW()),
SEARCH(AQ$1,INDIRECT($AP$1&ROW()))
)
)
- LEN(SUBSTITUTE(LEFT(INDIRECT($AP$1&ROW()),
SEARCH(AQ$1,INDIRECT($AP$1&ROW()))
),
" ",
""
)
)
)
),
0
) + 1,
这决定了从单词开始到结束的字符数。
SEARCH(" ",
INDIRECT($AP$1&ROW()),
SEARCH(AQ$1,INDIRECT($AP$1&ROW()))
)
- IFERROR(FIND("|",
SUBSTITUTE(LEFT(INDIRECT($AP$1&ROW()),
SEARCH(AQ$1,INDIRECT($AP$1&ROW()))
),
" ",
"|",
LEN(LEFT(INDIRECT($AP$1&ROW()),
SEARCH(AQ$1,INDIRECT($AP$1&ROW()))
)
)
- LEN(SUBSTITUTE(LEFT(INDIRECT($AP$1&ROW()),
SEARCH(AQ$1,INDIRECT($AP$1&ROW()))
),
" ",
""
)
)
)
),
0
) - 1
)
答案1
我不知道就数据量而言这是否会比 VBA 或 Power Query 更快，但使用函数是一种不同的方法FILTERXML。
我添加了一个，IFERROR如果没有匹配则返回空白。我还必须小写自xpath参数区分大小写。
AP1我还通过要求包含列来简化公式数字而不是列姓名。
我建议您更改引用$A:$M以反映数据数组中可能的最大行数和列数。如果这样做，则需要更改 in 中的数字AP1，例如，如果您使用：$H$1:$M$300000那么您将使用1inAP1来表示 Column H。
AQ2: =IFERROR(
FILTERXML(
"<t><s>" &
LOWER(
SUBSTITUTE(INDEX($A:$M, ROWS($1:2), $AP$1), " ", "</s><s>")
) & "</s></t>",
"//s[contains(.,'" & AQ$1 & "')] "
),
""
)
然后根据需要向下拖动/填充。
数据
结果H(8)
结果I(9)
答案2
数据量太可怕了，也许可以将其分成几段。
但是，您可以简化公式并将其用作数组公式。
在下面屏幕截图中显示的提议中，整个范围都有一个数组公式。我在名称管理器中定义了名称“col”以指定所选数据列。
公式需要与原始数据匹配。建议的修改显示在屏幕截图中。
在范围内输入的数组公式F10:H13是
=TRIM(MID(SUBSTITUTE(col," ",REPT(" ",LEN(col))),
FLOOR(SEARCH($F$9:$H$9, SUBSTITUTE(col," ",REPT(" ",LEN(col)))),
LEN(col))+1, LEN(col)))
在 Excel 2016 中，您应该选择整个范围并按Ctrl+Shift+Enter。
Arkusz2是工作表名称。
