答案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
答案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),"")
- 这不是一个智能公式但最终它是有效的。
- 根据需要调整公式中的单元格引用。