在工作中,有很多数据处理分析的应用场景,需要耗费我们很多时间,将Excel和数据分析工具结合起来使用,可以大大提高效率。
以某物流企业部分内部测试题为例,我们来看下使用excel和数据分析工具BDP分别如何实现
题目一:【多表合并】
将从系统中导出的80个采购单数据表,快速转换为格式统一的入库单表
使用Excel:
1、复制粘贴 。最简单和不费脑的方式,就是根据结果表格式,将多个表进行复制粘贴,但是面对几十个表,估计手都要废了
2、VBA 。可以采用宏语言的方式进行多表合并,这个方法要求有一定编程基础。
使用数据分析工具BDP:
进行多表合并,只需要一个「追加合并」合表即可
操作流程:
1、将采购表全部上传至BDP
2、点击创建合表-追加合并
3、选择批量追加,将全部采购表添加至界面,保存即可
题目二:【行列转换】
将存在多列城市字段的二维表,转换为标准格式的一维表
二维表:
一维表:
二维表更符合我们日常的阅读习惯,信息更浓缩,适合展示分析结果,但作为源数据进行数据分析时,就需要一维表。
使用Excel:
Excel中的二维转一维,2016版本或者2019版本可以使用 Power Query的逆透视功能的方法,这里我们介绍2个其他方法。
1、公式法
G2单元格内输入公式:
=INDEX(A:A,(ROW(A1)-1)/4+2)
H2单元格内输入公式:
=INDEX(B$1:E$1,MOD(ROW(A1)-1,4)+1)
I2单元格内输入公式:
=OFFSET($A$1,(ROW()-2)/4+1,MOD(ROW()-2,4)+1)
2、数据透视表
依次点击Alt、D、P三键(注意是依次点击,不是一起按),调出数据透视表和数据透视图向导;
接着勾选“多重合并计算数据区域”—“下一步”—“下一步”,选定区域框选二维表区域,最后点击“下一步”—“确定”。
在建立的透视表界面中,将字段“列”拖动到行区域,之后点击【设计】,报表布局中依次点击”以表格形式显示“、”重复所有项目标签“;总计中点击”对行和列禁用“;分类汇总中点击”不显示分类汇总“,二维表转换完成~
此方法选自数据小E的文章 - 知乎
使用数据分析工具BDP:
日常使用BDP进行数据分析时,建议上传一维数据表,所以为大家提供了二维转一维的功能
操作步骤:
1、上传二维表
2、点击-创建合表-二维转一维
3、勾选需要合并的几列字段,并设置合并后的字段名称
合并项:年份
合并值:数量
题目三:【ABC分类】
通过商品价格表,将所有商品进行ABC分类
A类:贡献前95%利润额的SKU
B类:贡献95%-99%利润额的SKU
C类:贡献99%-100%利润额的SKU
使用Excel:
在物流仓储或电商等行业内,ABC分类法是常用的分析方法,主要思路是:
1、根据分类标准进行排序
2、按照排序对数值进行累积计算
3、根据分类标准进行分类
操作方法:
1、按照金额降序排列
2、计算累计销售金额
3、计算累计占比
4、根据占比划分分类
使用数据分析工具BDP:
在BDP中,思路和excel一样,需要先使用公式,添加所需的计算字段,我们还可以利用拖拽的方式,制作帕累托图,来可视化展示数据情况
公式添加:
1、排序字段:
公式:row_number() over(partition by 1 order by [订单金额] desc)
2、累计金额:
公式:SUM([订单金额]) OVER(PARTITION BY 1 ORDER BY [排序])
3、金额总和字段:
公式:SUM([订单金额]) OVER(PARTITION BY 1)
4、累计占比:
公式:[累计销售金额]/[金额总和]
分类时可选择添加分组字段来完成:
在实际分析场景中,选择合适的工具,提高分析效率是大家的追求,欢迎大家分享更多分析场景的需求,期待一起讨论找到更加有效率的分析方法。
热门跟贴