【原】这道题目除了FILTERXML函数，谁来了都挠头！

送人玫瑰，手有余香，请将文章分享给更多朋友

动手操作是熟练掌握EXCEL的最快捷途径！

【置顶公众号】或者【设为星标】及时接收更新不迷路

小伙伴们好，今天要来和大家分享一道稍复杂的统计求和题目。统计求和这类题目是我们日常中最为常见的题目类型之一，也是工作中最常见的情景，因此掌握好这类题目的操作技巧就显得尤为重要了。

题目是这样子的：

这个是一部分清单。题目要求按照颜色进行分类统计，并计算出每种颜色的销量总和。

朋友们有什么好的方法吗？思考十秒钟…

01

这种数据结构，还要按照一定的条件来统计和计算，最适用的函数就是FILTERXML函数了，也就是我们常说的海鲜大法。

由于我使用的EXCEL版本是2016版的，所有有一些高级函数不能使用。为了方便数据处理，我使用了一列辅助列。在B列插入一列辅助列，并输入半角逗号，向下填充至数据区域的倒数第二个单元格。

在单元格E2中输入公式“=SUM(FILTERXML("<a><b>"&SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(PHONETIC($A$2:$B$404),"A",""),"G",""),"[",""),"]",""),",","</b><b>"),D2,"<c/>")&"</b></a>","a/b[c][.*0=0]"))”，三键回车并像下拖曳即可。

思路：

• FILTERXML函数不能处理单元格区域，因此要把这些单元格合并起来。由于不能使用TEXTJOIN和CONCAT这些函数，我们只能插入辅助列，使用PHONETIC这个函数，PHONETIC($A$2:$B$404)部分，将源数据合并

• SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(PHONETIC($A$2:$B$404),"A",""),"G",""),"[",""),"]",""),",","")部分，连续使用SUBSTITUTE函数将源数据中的“A”，“G”，“[”和“]”替换为空值，将“,”替换为“”

• 将当前行对应的颜色在上述的字符串中替换为“<c/>”，这一点很重要，它为FILTERXML提供了条件

• 在左右两端添加“<a><b>”和“</b></a>”，满足FILTERXML函数对格式的要求

• 最后输入FILTERXML函数的第二参数，"a/b[c][.*0=0]"，含义是提取含有C对应的字段的值。而C对应的其实就是当前行的颜色，也就是提取符合条件的数值

• 最后最外侧嵌套SUM函数求和，得到正确答案。

好了朋友们，今天和大家分享的内容就是这些了！喜欢我的文章请分享、转发、点赞和收藏吧！如有任何问题可以随时私信我哦！

-END-