按合并名单汇总数据

小伙伴们好，不知道我每天发的文章你们能看懂不，没人留言和反馈，我也不清楚，只能按我想到的或遇到的问题来发了。今天的问题如下：计算合并名单的总销量，左表是源表，右表是要计算答案的区域。如：顾初/左曼/肖雪，通过左表查询销量分别是1902、1963、644，汇总后得4509。

解答这个题目，有2种思路，每种思路又可以写好几种函数。下面分别来说：

思路1.在合并名单中查找每个姓名

也就是在D4单元格中查找A列的所有姓名，能找到的就是我们需要的，后面通过计算处理可以返回对应的销量，最后求和就可以了。

第1个公式为=SUMPRODUCT(ISNUMBER(FIND(A$3:A$12,D4))*B$3:B$12)。先看FIND(A$3:A$12,D4)这部分，就是在D4单元格中查找A$3:A$12的每个姓名，找到的返回数字，找不到的返回错误值，最后的结果为{1;#VALUE!;4;#VALUE!;#VALUE!;#VALUE!;#VALUE!;7;#VALUE!;#VALUE!}。

接下来用ISNUMBER判断上一步的结果是否是数字，是数字的返回true，不是数字的返回false，最后的结果为{TRUE;FALSE;TRUE;FALSE;FALSE;FALSE;FALSE;TRUE;FALSE;FALSE}。

然后用上一步的true与false组成的数组和B$3:B$12相乘，返回的结果为{1902;0;1963;0;0;0;0;644;0;0}。这样就把D4单元格中每个人的销量算出来了。最后用sumproduct求和就可以了。

第2个公式为=SUMPRODUCT(COUNTIF(D4,"*"&A$3:A$12&"*"),B$3:B$12)。还是在D4单元格中查找A列的每个姓名，只不过用的是countif的通配符用法。

第3个公式为=SUMPRODUCT((SUBSTITUTE(D4,A$3:A$12,"")<>D4)*B$3:B$12)。用的是substitute替换函数。举个简单的例子，将"顾初/左曼/肖雪"(D4单元格)中的"顾初"(A3单元格)替换为空，结果为"/左曼/肖雪"，和原来D4单元格的"顾初/左曼/肖雪"不相等，说明在D4单元格中可以找到A3单元格的值。

这3个公式的本质是一样的，都是在合并名单的单元格中查找A列的每个姓名。当然还可以用其他的函数来查找，比如match。

思路2.将合并名单拆分成数组

第2种思路是将合并名单中的多个姓名拆分成数组，比如将"顾初/左曼/肖雪"拆分为{"顾初","左曼","肖雪"}。然后再用sumif，sumproduct等函数计算就简单了。

这里主要介绍拆分成数组的方法，有两种。第一种用mid+rept函数，第二种用宏表函数evaluate。

第一种公式为=TRIM(MID(SUBSTITUTE(D10,"/",REPT(" ",99)),ROW($1:$9)*99-98,99))，返回的结果为{"顾初";"左曼";"肖雪";"";"";"";"";"";""}。将D10单元格的名单拆分成上面的数组。

首先用substitute将斜杠"/"替换为99个空格REPT(" ",99)，变成一个很长的字符串，然后用mid提取，从1,100,199......开始提取，提取的长度是99位。1,100,199......是一个以1开始，99为等差的等差数列，用ROW($1:$9)*99-98表示。提取出来的字符串包含多余的空格，用trim去除多余的空格。

这个数组拆分出来后，最后的求和公式为=SUMPRODUCT(SUMIF(A$3:A$12,TRIM(MID(SUBSTITUTE(D10,"/",REPT(" ",99)),ROW($1:$9)*99-98,99)),B$3:B$12))。

第二种拆分的公式用的evaluate，所以要用到定义名称。在【公式】—【定义名称】中新建一个名称“人员”，在引用位置输入公式=EVALUATE("{"""&SUBSTITUTE($D10,"/",""",""")&"""}")。注意公式中的双引号。

在第10行的任意空白单元格输入公式=人员，按F9看它返回的结果为{"顾初","左曼","肖雪"}。

SUBSTITUTE($D10,"/",""",""")这部分是将D10单元格的斜杠(/)替换为(",")，这样才好让每个名字被双引号包裹。如下图红色框中的效果。公式不能写成这样SUBSTITUTE($D10,"/","","")，也就是说如果要在双引号中还要放双引号，双引号要写2个（实际代表是1个）。这个公式SUBSTITUTE($D10,"/",""",""")中的2个红色引号实际代表1个，只是要写2次，不知道有没有说清楚。

上一步名字左右两侧还没有双引号，所以左右两侧也要连接双引号，公式为=""""&SUBSTITUTE($D10,"/",""",""")&""""，结果如下图红框所示。

但是还少左右大括号{}，所以把左右大括号也连接起来，公式为="{"""&SUBSTITUTE(D10,"/",""",""")&"""}"，结果如下图红框所示。这样看上去虽然构成了数组，但不是真正的数组。最外面套个evaluate才能转为真正的数组。

数组拆分好了，最后的求和公式为=SUMPRODUCT(SUMIF(A:A,人员,B:B))或者=SUMPRODUCT((A$3:A$12=人员)*B$3:B$12)。

大家还是边看文字边看表吧，源文件：

https://pan.baidu.com/s/18EjE-vrEo3KnhkFfbIyMUA

提取码：swim