首页
学习
活动
专区
工具
TVP
发布
精选内容/技术社群/优惠产品,尽在小程序
立即前往

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生产计划,关注古哥计划!

  • 发表于:
  • 原文链接https://kuaibao.qq.com/s/20230216A00VWB00?refer=cp_1026
  • 腾讯「腾讯云开发者社区」是腾讯内容开放平台帐号(企鹅号)传播渠道之一,根据《腾讯内容开放平台服务协议》转载发布内容。
  • 如有侵权,请联系 cloudcommunity@tencent.com 删除。

扫码

添加站长 进交流群

领取专属 10元无门槛券

私享最新 技术干货

扫码加入开发者社群
领券