75 在几万条数据中查询指定产品对应的销量前5名并加上产品名称和数量

某计划运营在分析产品销量的问题：从ERP系统中导出今年全部产品对应不同货号的商品销售数量汇总。产品大类有几十种，如夹克、风衣、卫衣、羽绒服、皮衣等，每个类别下面有不同的商品，如夹克对应的商品命名有：“3-1C18807、3-1C88260、3-1C88616、夹克1J3302、夹克3E515-2”，每个销量对应的不同，现在领要求他快速的找出每个类别的“爆款”，也就是销量最大的前5名产品，分别销量是多少？

下图是手工一个一个填写上去的效果，因为原始数据库实在太多了，有几万条件记录，问古哥有没有好的办法，通过公式一次实现？

古老师仔细得看了一下手工统计的结果，有几个难点要解决：

第一：商品名和销量是在一个单元格，而这两个类别在源数据是分别是两列；

第二：销量和产品大类在源数据中没有规律，销量也不是按类别，按降序排列的；

第三：一个产品对应多个商品；

第四：源数据是一维数据，领导要求的结果是二维数据；

知道问题难点就方便解决多了，这类问题有一个特点，特定的问题特定函数：第一个问题属于合并单元格问题，用到函数TEXTJOIN; 第二个问题是数据没有规律，就用到排序函数SORT让其有规律，指定降序；第三个问题是一对多问题，用到TRANSPOSE+ FITER函数就可以了；最后一个问题，一维转二维，就用UNIQUE+TRANSPOSE+FITER 以及一些特定的查找引用函数即可；

解决步骤1：确定产品的唯一大类值，录入函数：=UNIQUE(A:A)

解决步骤2：确定销量前5名的名称，录入函数：=SEQUENCE(,5)

到这里为了方便大家理解，分开写函数步骤演示

解决步骤3：筛选出夹克对应的商品命名和销量，录入函数：

=FILTER($C:$D,$A:$A=$F2)

解决步骤4：对筛选出夹克对应的商品命名和销量进行排序，录入函数：

=SORT(FILTER($C:$D,$A:$A=$F2),2,-1)，参数-1代表降序，也就是对应销量的从大到小排序；

解决步骤5：对筛选出夹克对应的商品命名和销量进行排序后的数量进行取前5名，因为已经排序过了，所以用INDEX函数取第1列和第2列的，前5行就可以了，再用&符号连接起来就成为一个单元格了；

=INDEX(SORT(FILTER($C:$D,$A:$A=$F2),2,-1),SEQUENCE(5),1)&"，"&INDEX(SORT(FILTER($C:$D,$A:$A=$F2),2,-1),SEQUENCE(5),2)

解决步骤6：到步骤5基本上就解决了这个问题了，只需要加上转置函数就可以了,录入函数：

=TRANSPOSE(INDEX(SORT(FILTER($C:$D,$A:$A=$F2),2,-1),SEQUENCE(5),1)&"，"&INDEX(SORT(FILTER($C:$D,$A:$A=$F2),2,-1),SEQUENCE(5),2))

解决步骤7：有些产品的下面的商品没有前5项，只有1项或者2项的话，就会返回错误，此时加上屏蔽错误函数后，此问题得到解决：

=IFERROR(TRANSPOSE(INDEX(SORT(FILTER($C:$D,$A:$A=$F2),2,-1),SEQUENCE(5),1)&"，"&INDEX(SORT(FILTER($C:$D,$A:$A=$F2),2,-1),SEQUENCE(5),2)),"")

总结：在更新OFFICE365版本后，配合去重函数UNIQUE、筛选函数FILTER、排序函数SORT、转置函数TRANSPOSE以及动态数组的运用，可以非常方便去做一些数据分析的工作，而且非常高效、快速。

我是古哥：

从事制造行业18年，在企业运营、供应链管理、智能制造系统等方面具有丰富的实战经验。企业智能化，柔性化计划运营管理专家，擅长通过企业流程优化规范，企业管理、导入计划运营提升企业效率；对提高企业准时交货率，降低企业库存，输出智能制造人才有丰富的经验。学习PMC生产计划，关注古哥计划！