excel图表如何统计员工负责的客户数？

这是Alpha提出的问题：

• 咨询个问题哦，A列内容包含了100个客户的身份证号，B列是管理这些客户的员工名字（有的人身份证号出现多次），共计160行。
• 用什么公式可以统计出每个员工负责多少个客户呀？（以一个身份证号为一个客户计算）
• 透视表可以做，但是每次数据变动都要更新透视表，很麻烦。用什么公式可以统计出每个员工负责多少个客户呀？（以一个身份证号为一个客户计算）

数据示例工作表如下图1所示。

图1

初次看到这个问题，我以为很简单，但在Alpha发来示例数据工作表后，才觉得普通的公式难以解决，因为员工对应的客户存在重复，例如“员工2”对应的“客户2”就有3条重复数据。这给这个问题增加了不小的难度！

但无论简单还是复杂的问题，首先要想到其解决思路，在编程中我们称作算法。其实，编写公式与编写程序代码一样，要针对每个具体问题找出其“算法”，再使用公式来解决就清晰了。

针对上面的问题，我最初的思路是：先获取员工及其对应的客户的不重复值列表，然后统计列表中每名员工的数量，即为该名员工负责的客户数。

1.获取员工及其对应客户的不重复值列表

在列H中构建辅助列，选择单元格区域H4:H25，输入数组公式：

=IFERROR(INDEX(B2:B25&A2:A25,SMALL(IF(MATCH(B2:B25&A2:A25,B2:B25&A2:A25,0)=ROW(INDIRECT(“1:”&ROWS(A2:A25))),MATCH(B2:B25&A2:A25,B2:B25&A2:A25,0),””),ROW(INDIRECT(“1:”&ROWS(A2:A25))))),””)

注意，由于是数组公式，要按Ctrl+Shift+Enter组合键才算完成公式输入。结果如下图2所示。

图2

注：上述公式是获取不重复值的一个通用公式，有兴趣的朋友可以记住其模式，在需要获取不重复值时使用。

2.统计辅助列中每名员工数量

可以看出，辅助列中每名员工出现的次数就是该名员工负责的客户数，可使用COUNTIF函数来进行统计。在单元格E4中输入公式：

=COUNTIF($H$4:$H$25,D4&”*”)

向下拖动复制至单元格E8，结果如下图3所示。

图3

然而，当我想消除辅助列，从而将这两个公式合并成一个公式时，却出现了问题，如下图4所示。

图4

Excel弹出这样的提示对找到公式存在的问题毫无帮助，只是反映出COUNTIF函数在此种情形下不支持数组公式。无奈之下，只得另找他法！

另一种思路：首先查找每名员工和每位客户的组合在数据表中员工与客户组合中出现的位置，得到由位置数字成的数组；然后获取这些数字在指定整数序列区间是否出现，其出现的次数之和即为对应的客户数。

在单元格E4中输入数组公式：

=SUMPRODUCT(–(FREQUENCY(IFERROR(MATCH(D4&$A$2:$A$25,$B$2:$B$25&$A$2:$A$25,0),0),ROW($A$2:$A$26)-ROW($A$2))>0))-1

然后拖动复制至单元格E8，结果如下图5所示。

图5

1.公式中：

MATCH(D4&$A$2:$A$25,$B$2:$B$25&$A$2:$A$25,0)

用来查找每名员工和每位客户的组合在数据表中员工与客户组合中出现的位置，解析为：

MATCH({“员工1客户1″;”员工1客户2″;”员工1客户3″;”员工1客户4″;”员工1客户5″;”员工1客户3″;”员工1客户9″;”员工1客户9″;”员工1客户8″;”员工1客户4″;”员工1客户4″;”员工1客户4″;”员工1客户10″;”员工1客户2″;”员工1客户12″;”员工1客户22″;”员工1客户2″;”员工1客户22″;”员工1客户12″;”员工1客户22″;”员工1客户5″;”员工1客户5″;”员工1客户15″;”员工1客户15”},{“员工1客户1″;”员工2客户2″;”员工3客户3″;”员工4客户4″;”员工5客户5″;”员工3客户3″;”员工1客户9″;”员工1客户9″;”员工1客户8″;”员工4客户4″;”员工4客户4″;”员工4客户4″;”员工4客户10″;”员工2客户2″;”员工2客户12″;”员工2客户22″;”员工2客户2″;”员工2客户22″;”员工2客户12″;”员工2客户22″;”员工5客户5″;”员工5客户5″;”员工5客户15″;”员工5客户15”},0)

MATCH函数查找的结果为数组：

{1;#N/A;#N/A;#N/A;#N/A;#N/A;7;7;9;#N/A;#N/A;#N/A;#N/A;#N/A;#N/A;#N/A;#N/A;#N/A;#N/A;#N/A;#N/A;#N/A;#N/A;#N/A}

重复的数据返回其第1次出现时的位置，如果找不到则返回错误值#N/A。

将上面的数组传递给IFERROR函数：

IFERROR(MATCH(D4&$A$2:$A$25,$B$2:$B$25&$A$2:$A$25,0),0)

转换为：

IFERROR({1;#N/A;#N/A;#N/A;#N/A;#N/A;7;7;9;#N/A;#N/A;#N/A;#N/A;#N/A;#N/A;#N/A;#N/A;#N/A;#N/A;#N/A;#N/A;#N/A;#N/A;#N/A},0)

得到数组：

{1;0;0;0;0;0;7;7;9;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0}

2.公式中：

ROW($A$2:$A$26)-ROW($A$2)

生成连续的整数组成的数组：

{0;1;2;3;4;5;6;7;8;9;10;11;12;13;14;15;16;17;18;19;20;21;22;23;24}

注意，第一个ROW函数的参数中为A26，比数据区域多了一个单元格，使生成的整数最大值与数据数量相同。

3.将上述生成的两个数组传递给FREQUENCY函数：

FREQUENCY(IFERROR(MATCH(D4&$A$2:$A$25,$B$2:$B$25&$A$2:$A$25,0),0),ROW($A$2:$A$26)-ROW($A$2))

转换为：

FREQUENCY({1;0;0;0;0;0;7;7;9;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0},{0;1;2;3;4;5;6;7;8;9;10;11;12;13;14;15;16;17;18;19;20;21;22;23;24})

返回第1个数组在第2个数组值区域中的频率分布:

{20;1;0;0;0;0;0;2;0;1;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0}

注：FREQUENCY函数是数组函数，可以一次生成多个值。

4.再将其转换为TRUE/FALSE值组成的数组：

FREQUENCY(IFERROR(MATCH(D4&$A$2:$A$25,$B$2:$B$25&$A$2:$A$25,0),0),ROW($A$2:$A$26)-ROW($A$2))>0

转换为：

{20;1;0;0;0;0;0;2;0;1;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0}>0

得到数组：

{TRUE;TRUE;FALSE;FALSE;FALSE;FALSE;FALSE;TRUE;FALSE;TRUE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE}

TRUE值表示存在该区间的数值，也就是代表有对应的客户。但第1个TRUE值除外，因为其代表的是0出现的次数，后面将会处理。

5.双减号将TRUE/FALSE值组成的数组转换成1/0组成的数组：

{1;1;0;0;0;0;0;1;0;1;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0}

传递给SUMPRODUCT函数对这个数组求和，即得到客户数。正如前面提到的，由于已将0值也统计在内（导致多算了1个），因此在最后的结果中减1剔除该值即得到最终正确的结果。