excel计算出现次数的函数(1分钟教你excel统计人名个数)

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所示。

excel计算出现次数的函数(1分钟教你excel统计人名个数)

图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列数据的情形。

……

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

发表评论

登录后才能评论