Excel公式练习101：统计一组数值在另一组数值中出现的次数

excelperfect

引言：本文的练习整理自chandoo.org。多一些练习，想想自己会怎么解决这个问题，看看别人又是怎样解决的，这样能够快速提高Excel公式编写水平。

本次的练习是：计算一个二维数值数组在另一个二维数值数组中出现的次数。

要求：

1.这是一个公式挑战。因此，只接受公式提交。

2.对于名为“nList”和“sList”的两个列表，统计“sList”在“nList”中出现的次数。

3.为了简单起见，我们假设nList和sList各有两列，如下图1所示。

4.为了简单起见，我们假设sList正好有两行。

5.为了简单起见，假设sList至少有一个非零值。

6.sList和nList可以位于工作表的任何位置。

7.nList的行数多于sList。

8.nList和sList包含不按任何顺序排列的值。

9.不能使用任何辅助单元格。

示例数据如下图1所示。

图1

请写下你的公式。

解决方案

下面列出一系列公式，有兴趣的朋友可以按照前面文章给的方法逐个研究。

公式1：

=SUM((OFFSET(sList,,,1,1)&OFFSET(sList,,1,1,1)=TRANSPOSE(OFFSET(nList,,,,1)&OFFSET(nList,,1,,1)))*(OFFSET(sList,1,,1,1)&OFFSET(sList,1,1,1,1)=TRANSPOSE(OFFSET(nList,1,,,1)&OFFSET(nList,1,1,,1))))

这是一个数组公式，输入完时要按Ctrl+Shift+Enter键。

公式2：数组公式。

=SUM(--((MMULT(IF((OFFSET(nList,,,ROWS(nList)-1)=(INDEX(sList,1,0))),1,),{1;1})+MMULT(IF((OFFSET(nList,1,,ROWS(nList)-1)=(INDEX(sList,2,0))),1,),{1;1}))=4))

公式3：数组公式。

=SUM(N(MMULT(N(nList&'|'&OFFSET(nList,1,)=OFFSET(sList,,,1)&'|'&OFFSET(sList,1,,1)),{1;1})=2))

公式4：数组公式。

=SUM(N(MMULT(N(nList&'|'&OFFSET(nList,1,)=INDEX(sList,1,)&'|'&INDEX(sList,2,)),{1;1})=2))

公式5：数组公式。

=SUM(N(MMULT((INDEX(sList,1,)=nList)*(INDEX(sList,2,)=OFFSET(nList,1,)),TRANSPOSE(COLUMN(sList))^0)>1))

这个公式适合多于2列数据的情形。

……

欢迎在下面留言，完善本文内容，让更多的人学到更完美的知识。

欢迎到知识星球：完美Excel社群，进行技术交流和提问，获取更多电子资料，并通过社群加入专门的微信讨论群，更方便交流。