Excel单元格模糊查找并匹配多个条件的合计、自动开列

Question

拜求EXCEL函数高人指点 ：1. 按照单元格的字符对指定单元格区域进行模糊，或类似关键字的查找匹配，同时满足多个单元格，之后进行的合计，请参考附图：求助12. 同上面的1.，将满足这些条件的项目，自动分别开列出来，序号，项目名称，数量，单价跪谢高人！

Answer 1

仔细看了下您的原始数据，出现问题的原因是记录数据的时候不规范。所以，首要的是把这个规范起来，免得后续还有这样的原始记录出现。

对于已有数据，我想到的一种办法是归纳原始数据中关键词出现的模式：都含有25。所以只要能确保有25出现的单元格绝大多数都是c25砼（如果有个别打破这个规则，可以手动改一下），直接用find之类的函数来匹配（不建议用sumif汇总，因为您后面还提到了要分开这些不同类别的数据）。

c30砼等其他关键词类似。如果要在一个公式里匹配所有关键词，则用if来嵌套。

我做了个简单示例：

如果除了C25砼和C30砼，还有其他类别，可以继续嵌套if。

有了这样的字段之后，您要汇总统计也好，分开不同类别的数据到不同的sheets也好，都可以通过数据透视表解决。

Answer 2

首先建议改变下工作表的记录方式，好的方式不仅体现工作的规范和严谨，而且也是便于Excel工作表后期数据处理的基础。

如混凝土的标号记录就很混乱，C20、C25、C30等都是完整的标示混凝土等级的等级符号，而工作表中的“C砼25”这决不是工程语言，C25砼则是规范的，砼C25也还说得过去。

工作表中没必要每行数据之间留空行，留空行不仅记录时麻烦，且无论是后期用数据透视表还是用公式都造成数据的冗余（空白数据冗余），如本问题中的公式，就要对所有空白行与数据行一样进行计算，白白浪费资源。

其次，对你的描述不是很清楚。特别是问题2，请你自己再看看，感觉描述与截图无法对应，所以没给你提供公式解答。而问题1中，公式则纯粹是凭对工程的了解，知道25是不能分开的（其实C25就是不分割的），而你B列中却如前说，有些混乱，所以把C和砼单独拿来用。

如图：

C27=SUMPRODUCT(ISNUMBER(FIND("25",B8:B26))*ISNUMBER(FIND("C",B8:B26))*ISNUMBER(FIND("砼",B8:B26))*(D8:D26=D27)*(E8:E26=E27)*C8:C26)

追答

再次看你的提问，你的问题应该是把已知条件的单元格错位了：图中文字描述与截图相比，向右偏移了1列。且“开列”这样的说法也颇令人费解，理解你是要把表1中满足B列同时含有C、25、砼，且表1的单位与D13一致，单价与E13一致的数据提取到表2中。提取数据，这是Excel中谁都理解的说法，而开列出来就是你的口语了，呵呵。

如图（因为公式的连续性，没按你的每行数据之间空一行的做法——且空一行的缺点已在前面的回答中说过）：

A15=INDEX(Sheet1!A:A,SMALL(IF(ISNUMBER(FIND("25",Sheet1!B$8:B$26))*ISNUMBER(FIND("C",Sheet1!B$8:B$26))*ISNUMBER(FIND("砼",Sheet1!B$8:B$26))*(Sheet1!D$8:D$26=D$13)*(Sheet1!E$8:E$26=E$13),ROW($8:$26),4^10),ROW(1:1)))&""

同时按Ctrl+Shift+Enter三键输入数组公式

B15=IF(A15="","",VLOOKUP($A15,Sheet1!$A$9:$E$27,COLUMN(B:B)))

右拉到E15，再选择A15:E15，一起下拉到出现空白行。

Answer 3

1图：

C27输入

=SUMPRODUCT((--ISNUMBER(FIND(LEFT(B27,1),B9:B25)))*(--ISNUMBER(FIND(MID(B27,2,2),B9:B25)))*(--ISNUMBER(FIND(RIGHT(B27,1),B9:B25)))*(D9:D25=D27)*(E9:E25=E27),C9:C25)

回车。

2图：

假设1图在Sheet1，在Sheet2的A15输入

=IFERROR(IF(MOD(ROW(),2)=0,"",INDEX(Sheet1!A$9:A$25,SMALL(IF((--ISNUMBER(FIND(LEFT($B$13,1),Sheet1!$B$9:$B$25)))*(--ISNUMBER(FIND(MID($B$13,2,2),Sheet1!$B$9:$B$25)))*(--ISNUMBER(FIND(RIGHT($B$13,1),Sheet1!$B$9:$B$25)))*(Sheet1!$D$9:$D$25=$D$13)*(Sheet1!$E$9:$E$25=$E$13),ROW($9:$25)-8,4^8),ROW(2:2)/2))),"")

数组公式，输入后先不要回车，按Ctrl+Shift+Enter结束计算，再向右和向下填充。

本回答被提问者采纳

Answer 4

C27用条件求和公式：
=SUMIFS(C9:C25,B9:B25,"*"&B27&"*",D9:D25,D27,E9:E25,E27)
第二个问题，直接用公式比较麻烦，且是数组公式，数据量大时，会影响运算速度，可直接对附录6按条件进行高级筛选，不过高级筛选只能对本工作表使用，如果一定要将结果提取到附录6.1中去，可以将条件写在附录6.1，用公式=到附录6中，对附录6进行高级筛选，再用公式将筛选结果=到附录6.1中去，所有的公式可以一次性设置在里面，不必每次重写，但条件变化后，每次都要在附录6中点下高级筛选。

Answer 5

用公式
1、C27
=SUM(IF(ISERROR(FIND(MID($B$27,1,1),$B$1:$B$26)*FIND(MID($B$27,2,1),$B$1:$B$26)*FIND(MID($B$27,3,1),$B$1:$B$26)*FIND(MID($B$27,4,1),$B$1:$B$26)),0,($C$1:$C$26)))
数组公式，按Ctrl+Shift+Enter结束输入
2、=INDEX(A$1:A$26,SMALL(IF(ISERROR(FIND(MID($B$27,1,1),$B$1:$B$26)*FIND(MID($B$27,2,1),$B$1:$B$26)*FIND(MID($B$27,3,1),$B$1:$B$26)*FIND(MID($B$27,4,1),$B$1:$B$26)),10^9,ROW($B$1:$B$26)),ROW(A1)))
数组公式，按Ctrl+Shift+Enter结束输入，下拉填充取数直到出现#REF!（如果不想出现这个，就套一个if或者iferror函数在外面）取的是A列
右拉填充取其它列

条件：B27含四个字符