在Excel中，有几行数据，怎样快速得出含有某个特定组合的数据的行数？

Question

如图，左边有几行数据，每个数字在一个单元格，6个数字组成一行，右边有一个特定5个数字组合，如何快速计算出左边每行6个数据中包含右边特定5个全部数值的行数？必须5个数都包含在内，有四个相同的也不需要！（黄色是我自己标注的，意思是2行符合要求）

Answer 1

好吧，我写了一条很长的公式，让你一下子统计出来：

公式为：

=SUMPRODUCT(((A2:A8=H2)+(B2:B8=H2)+(C2:C8=H2)+(D2:D8=H2)+(E2:E8=H2)+(F2:F8=H2))*((A2:A8=I2)+(B2:B8=I2)+(C2:C8=I2)+(D2:D8=I2)+(E2:E8=I2)+(F2:F8=I2))*((A2:A8=J2)+(B2:B8=J2)+(C2:C8=J2)+(D2:D8=J2)+(E2:E8=J2)+(F2:F8=J2))*((A2:A8=K2)+(B2:B8=K2)+(C2:C8=K2)+(D2:D8=K2)+(E2:E8=K2)+(F2:F8=K2))*((A2:A8=L2)+(B2:B8=L2)+(C2:C8=L2)+(D2:D8=L2)+(E2:E8=L2)+(F2:F8=L2)))

追问

是固定6个数。不需要在意顺序，只要满足包含这个条件即可，谢谢

追答

固定顺序就简单了，不固定的话，也可以解决，但是公式挺长。

我就按不固定顺序给你解决一下，先看下面的模拟图：

G2单元格公式为：

=IF(ISNUMBER(MATCH(A2,I$2:M$2,))+ISNUMBER(MATCH(B2,I$2:M$2,))+ISNUMBER(MATCH(C2,I$2:M$2,))+ISNUMBER(MATCH(D2,I$2:M$2,))+ISNUMBER(MATCH(E2,I$2:M$2,))+ISNUMBER(MATCH(F2,I$2:M$2,))>=5,"包含","否")

然后选中G2，下拉填充公式，即可。

注意看第8行，这行数据不但是乱序，而且还被其它数字隔开了，这样的也能得出"包含"。

追问

感谢详尽的回答，这样得出的结果是单行包含数值对比，我想知道的是：显示为“包含”的单元格数量，这样仍需用COUNTIF公式再统计。有没有一个公式可以直接得出数值“3”（在区域中有三行符合全包含条件），再次请教，谢谢！

追答

用公式的话，如果不要辅助列，那就不好搞了。
上面还这样搞，G列算是辅助列就行了，然后用=COUNTIF(G2:G8,"包含")这条公式来统计一下就可以了。

如果不用辅助列，想直接得出统计数字，那么就得用VBA代码来解决了。

追问

非常感谢：-）

Answer 2

如果数据少，用sumproduct就可以（可以参考其他同学的答案，我不写了）

如果数据多，有几万或几十万行，那么用excel的power query是强项。建议把excel升到2016版以上，自带此功能。

1、先将原始表建立一个查询

增加一列，内容直接为=“a"

直接点“关闭并上载”右下角的箭头，选“关闭并上载至……”

再对要检测的数据建立第二个查询

也同样加一列，内容为="a"

将表2与表1进行关联

合并后，在上图中文字右上角有一个双箭头，点一下打开，选中除了“自定义”以外的所有列，最后结果会多出六列，结果如下图

再添加一个自定义列

内容是：List.ContainsAll({[表1.A],[表1.B],[表1.C],[表1.D],[表1.E],[表1.F]},{[检测数A],[检测数B],[检测数C],[检测数D],[检测数E]})

意思是，在A-F列中，必须包括有检测数A-E，是就显示true，否就显示false

筛选所有true结果的内容，再删除所有没有用的列，

结果如下

如果这样就行了，可以忽略下一步，如果要统计有几行，继续

选统计信息中的“值计数”

再转换到表，改列名为“计数”，再点“开始”的“关闭并上载到……”（同图4），把位置放在原表的位置边上就可以了。

以后每次改了前面两张表的内容，只要在绿色的表上右键点一下，刷新就可以得到新的结果了，对于海量数据特别有效。

追问

非常感谢您如此详尽的回答，power query苦手的人还得再学习学习，再次感谢☆⌒(*＾-＾)v THX!!

Answer 3

不知题主要求的“快速得出”后是要得到怎样的表示方法，是如图自动填充黄色背景吗？如是，那就可以用条件格式。方法如下：选择A2:F16，开始选项卡——条件格式——新建规则——使用公式确定要设置格式单元格，在为符合此公式的值设置格式（O）下面的框中输入：

=SUM(COUNTIF($I$2:$M$2,$A2:$F2))=5

格式：填充——黄色，确定，确定。效果如图：

如果要把选出后的结果提取到其他位置，也是可以的。

Answer 4

在图中的G2单元格中输入：
=IF(COUNT(MATCH($I$2:$M$2,A2:F2,0))=5,1,0)
然后按Ctrl+Shift+Enter完成数组公式输入（编辑栏中会自动包上花括号）
然后下拉填充。
对G列求和，所得就是满足条件的行数。

追问

感谢回答☆⌒(*＾-＾)v THX!!

Answer 5

似乎没有什么快速的办法。
excel本身在处理集合方面不是很强大。
建议用VBA来处理较好。
看你表中数据似乎行有序(每行数据从小到大排列)，那么也可以考虑用函数来实现，即最大的数在最后两列，最小的数在最前两列。

追问

感谢回答☆⌒(*＾-＾)v THX!!