Excel:在行中搜索通配符并返回所有匹配的值

Excel:在行中搜索通配符并返回所有匹配的值

我正在尝试找出一个公式,帮助我在一行中搜索通配符文本(例如,w*)并返回所有匹配的值(在一个或多个单元格中)。

我所拥有的东西看起来像这样:
数据示例

我希望公式能实现以下功能:
结果示例 1

或者那个:
结果示例 2

到目前为止,我找到的解决方案是搜索列(通常没有通配符选项),但我无法让它搜索行。有人能帮忙吗?

答案1

我可以给你一个可行的临时解决方案。我相信有更好的解决方案,但我通常使用 LO Calc,它的行为非常不同,我唯一可以随时访问的 Excel 是 Excel online,它不执行数组公式。

此解决方案基于您的评论,即您有数十或数百个值列,最多有六个“w”值。它为每行返回六个“w”值,如果匹配项较少,则其中一些或全部可能为空白。它使用辅助列来表示中间值,您可以将其隐藏或粘贴在方便的位置。这是一个包含六个值列的示例,我将解释如何将其缩放到您的数据。

在此处输入图片描述

您的样本数据以及我添加的第 6 列位于 B:G 列(以及 A 列中的人员)。对于您自己的数据,请使用适当的列范围,只需观察美元符号即可进行绝对寻址。我的辅助列位于 I:N 中,结果位于 P:U 中。

辅助列中使用了两个公式,一个用于第一列,一个用于后续列。辅助列标识匹配的位置。同样,结果列也有两个公式,第一个和后续。输入这四个公式并根据需要复制。

第一助手列

I2 中的公式(根据需要复制下来):

=IFERROR(MATCH("w*",$B2:$G2,0),0)

这将找到第一个通配符匹配项,0如果该行没有匹配项则返回。

后续辅助列(2至6)

I3中的公式(复制到右边和下面):

=IF(SUM($I2:I2)=COLUMN($G2)-COLUMN($A2),0,IFERROR(MATCH("w*",OFFSET($B2:$G2,0,SUM($I2:I2)),0),0))

公式的核心与第一列的工作方式相同,但它不是查看整行,而是查看最后一个匹配项位置的右侧。第一个 IF 测试检查最后一个匹配项是否是最后一个值列,因此它不会尝试匹配不存在的列。对于您的实际数据,请将 COLUMN($G2) 替换为最后一个值列,将 COLUMN($A2) 替换为第一个值列之前的列。

第一个结果列

P2中的公式(根据需要复制下来):

=IFERROR(INDEX($B2:$G2,1,$I2),"")

这将从第一个匹配项标识的列中检索值,如果没有则显示空白。

后续结果列(2至6)

Q2中的公式(复制到右边和下面):

=IF(J2=0,"",IFERROR(INDEX($B2:$G2,1,SUM($I2:J2)),""))

同样,这个方法与第一个方法相同,但会将之前的匹配偏移量相加以找到位置。它还会检查是否没有匹配,如果是,则显示空白。

答案2

@Sherly,这是一个改进的答案,它W's按行而不是按列进行搜索,正如我之前所展示的那样。

在此处输入图片描述

结果:

在 中写入此数组(CSE)公式H141,最后以Ctrl+Shift+Enter,向下填充然后向右填充。

{=IFERROR(INDEX($B141:$G141, SMALL(IF(LEFT($B141:$G141,1)="w", MATCH(COLUMN($B141:$G141), COLUMN($B141:$G141)), ""),COLUMNS($A$1:A1))),"")}

根据需要调整公式中的单元格引用。

答案3

@Sherly,,我想建议一个数组(CSE)公式,将从指定范围中提取所有以 W 开头的值。

在此处输入图片描述

笔记,由于通配符不适用于数字,并且范围周围的数据W后面都是数字,因此我使用了一个技巧来解决这个问题,即,

(IF(LEFT(B$141:B$150,1)=B$153

在哪里左边拉动1st Character与之匹配B153,并且这仅支持公式拉取所有Ws后跟数字。

怎么运行的:

  • 填充B153 to G153(这是辅助行)。
  • 在单元格 B154 中写入此公式,最后 Ctrl+Shift+Enter,向下填充然后向右填充。

    {=IFERROR(INDEX(B$141:B$150, SMALL(IF(LEFT(B$141:B$150,1)=B$153, MATCH(ROW(B$141:B$150), ROW(B$141:B$150)), ""),ROWS($A$1:A1))),"")}
    

編輯:

搜索和提取W 的您需要在(向右然后向下)中Row写入此公式:B160

=IF(LEFT(B141:G141,1)="w",LEFT(B141:G141,1)&RIGHT(B141:G141,LEN(B141:G141)-2+1),"")

在此处输入图片描述

  • 这不是一个智能公式但最终它是有效的。
  • 根据需要调整公式中的单元格引用。

相关内容