我们先来虚构这样一家大型服装公司，公司日常的分析工作主要集中在运营分析、商品分析和会员分析三大领域。随着市场竞争的加剧、业务需求的复杂化以及新型业务的不断增加，公司管理者对业务报表的专业度和时效性的要求也在不断提高。这时候我们该怎么做？1 传统数据分析的弊端零售行业作为一个古老的行业，其相关数据有着独特的属性，那就是——量大、细碎，这也是许多人对零售行业的总体印象。想要挖掘更好的分析工具和方法，就要先总结出传统零售行业中数据分析工作的困局。传统的零售数据分析方法可能会有以下几大弊端：（1）报表制作花费时间过长。数据分析师几乎没有时间对报表结果进行高价值的业务分析。（2）报表的可复用性较差。虽然日报、周报、月报的制作方法类似，甚至相似主题的分析报表其逻辑框架也基本相同，但很多企业的数据分析专员依然是每日重复相同的工作，长期来看，数据分析专员对企业产生的价值有限，自身能力毫无提升。（3）处理的数据量级有限。对于百万行量级的数据，由于超出了 Excel 工作表约 104 万行的限制，在 Excel 中甚至无法计算。在真实的企业环境下，如分析销售额背后商品、会员、单据等数据的变化规律，或者分析的时间区间足够大，比如分析数年的销售变化趋势，那么基础数据的量级很容易突破百万行。以上这些分析场景都是难以通过Excel 处理的。（4）分析的指标相对简单且数量有限。Excel 透视表中自带的分析指标只有求和、计数、平均值、最大值、最小值及统计中常用的方差、标准差等，对于业务逻辑稍微复杂的指标，需要在透视表外进行二次计算。以上是我们罗列的部分导致数据分析工作难以进行下去的原因。正是基于对传统零售数据分析所存在的种种痛点的深刻反思，借助微软强大的商业智能分析软件 Power BI，我们搭建了一套零售行业通用业务模型，以辅助业务人员准确、高效地进行零售数据分析，助力推进企业的数字化进程。本书将详细介绍利用 Power BI 搭建零售数据分析模型，以及运用数据分析模型指导企业业务运营的过程。 不同于绝大多数讲解 Power BI 技术的图书，本书的侧重点是讲解 Power BI 在零售行业内业务领域的实战。书中各章讲解的案例，“分”则是一个个独立的业务应用场景，“合”则是一整套前后衔接、逻辑清晰的零售业务解决方案。（关于Power BI的基础功能和使用方法请见图书第2章）2 Power BI 数据分析流程及案例演示Power BI 是微软推出的商业数据分析工具，能够帮助用户在复杂多变的商业环境下快速理清思路，发现数据中隐藏的见解和价值。对于企业，Power BI 是可以制定一整套商业智能解决方案的工具；对于个人，Power BI 是自助式商业智能分析软件，即 Power BI Desktop，可以快速实现自助式商业智能分析。本书的重点在于自助式商业分析，将着重介绍如何利用 Power BI Desktop 搭建数据分析模型。利用 Power BI 进行数据分析，通常分为 6 个环节，如下图所示。Power BI 数据分析流程下面我们以一个具体的零售案例来实践一下。2.1 业务理解案例以女装企业为研究对象，分析其销售额和折扣率两项指标的数据表现，从时间维度、区域维度、产品维度发现销售异常，从而采取相应的调改措施。根据业务需求，准备相应的数据源，分别为“门店信息表”、“产品信息表”、“日期表”和“销售表”，如表 2-1 ～表 2-4 所示（其中，吊牌价、金额的单位为元，后同）。数据源统一存放在本地 Excel 工作簿—“示例数据”中。表 2-1　门店信息表表 2-2　产品信息表表 2-3　日期表表 2-4　销售表基础数据准备完成，接下来需要将数据导入 Power Query。2.2 数据获取打开一个新的 Power BI Desktop 文档，单击“获取数据”，由于数据源存放在 Excel 工作簿中，选择“Excel 工作簿”，如图 2-7 所示。图 2-7 选择数据源类型通过文件路径找到目标 Excel 工作簿—“示例数据”，单击该工作簿后单击右下角“打开”，如图 2-8 所示。图 2-8　找到目标 Excel 工作簿此时，“导航器”窗口显示 Excel 工作簿下的所有工作表，根据建模需要进行选择。此处“示例数据”中的 4 张工作表都需要导入模型进行分析，全部勾选后单击右下方的“加载”或者“转换数据”。“加载”表示直接将工作表加载到 Power BI Desktop 中进行建模分析，“转换数据”则表示将工作表导入 Power Query，对数据进行处理后再加载到 Power BI Desktop中进行建模分析。一般情况下，建议选择“转换数据”，对工作表进行处理，检查无误后再加载到 Power BI Desktop 中，如果您对数据质量非常有信心，可以直接单击“加载”。此处我们单击“转换数据”，如图 2-9 所示。图 2-9　将数据源导入 Power Query2.3 数据转换图 2-10　“查询设置”窗格记录数据转换步骤数据源在 Power Query 中进行数据转换的步骤都记录在其操作界面右侧的“查询设置”窗格中。以销售表为例，可以看到图 2-10 中，Power Query 已经自动进行了“源”“导航”“提升的标题”“更改的类型”4 步操作。其中，“源”“导航”用于定位到目标查询，是查询的默认操作；“提升的标题”和“更改的类型”是通过在 Power Query中进行设置来确定是否由 Power Query 自动操作的，默认设置由 Power Query 自动完成操作。要修改检测类型，需要单击“文件”→“选项和设置”→“选项”，在“全局”→“数据加载”下的“类型检测”中，根据需要进行设置，如图 2-11 所示。图 2-11　在 Power Query 中设置“类型检测”由于数据源是从系统中导出的，数据结构非常规范，此处不需要进行其他的数据转换操作，唯一要做的就是检查 Power Query 自动更改的数据类型是否符合业务需求。数据中的订单 ID、吊牌价、数量、金额字段是整数类型，门店 ID、产品 ID 字段是文本类型，都和业务相符。日期字段此处自动修改为整数类型，与业务不符，需修改为日期类型，如图 2-12 所示。图 2-12 Power Query 数据类型检查图 2-13 Power Query 数据类型转换选中“日期”列，单击功能区中的“数据类型”，选择“日期”类型，如图 2-13 所示。此时，Power Query 提示是“替换当前转换”还是“添加新步骤”，选择“替换当前转换”，日期字段转换为日期类型。数据类型转换操作虽然相对基础、简单，却是非常重要的。不恰当的数据类型会导致模型占用的内存增多，或者后期建模过程中出现错误。其他查询报表的数据转换操作与上述方法类似。所有报表都转换完成后，单击左上角“关闭并应用”按钮，将报表加载至 Power BI Desktop。2.4 数据建模数据在 Power BI Desktop 中加载完成后，就可以进行建模、分析。首先在各表间建立关系。单击 Power BI Desktop 操作界面左侧的“模型”图标 ，进入“模型”视图。依据业务逻辑，在各表间建立关系。“销售表”作为事实表放在中间，“门店信息表”、“产品信息表”和“日期表”作为维度表放在事实表周围，分别和“销售表”建立一对多关系，如图 2-14 所示。关系建立完成后，要考虑模型中现有字段能否满足建模需求，如果不能满足，则需要新建计算表或计算列来丰富模型的分析维度。案例中的业务场景需要分析销售额和折扣率，折扣率 = 销售额 ÷ 吊牌金额。模型的“销售表”中只有金额字段，没有吊牌金额字段，所以需要新建计算列来计算吊牌金额。选择“数据”视图，在“字段”窗格中单击“销售表”，然后单击“新建列”，输入吊牌金额计算公式。销售表”中的计算列吊牌金额 =［吊牌价］×［数量］吊牌金额计算列建立完成，如图 2-15 所示。图 2-14 表间关系建立图 2-15 新建吊牌金额计算列吊牌金额也可以通过新建度量值的方式计算得到。度量值和计算列不同，计算列归属于某张具体表格，而度量值不归属于任何表格，它属于整个数据模型。所以理论上可以在任何表格中新建度量值，而不影响它的使用。此处我们在“产品信息表”下新建度量值，计算［销售额］和［折扣率］指标。单击“产品信息表”，在菜单栏依次单击“表工具”“新建度量值”, 在编辑栏中输入以下公式。“产品信息表”中的度量值销售额 =SUM ( '销售表'［金额］ )吊牌金额 =SUM ( '销售表'［吊牌金额］ )折扣率 =DIVIDE ( ［销售额］, ［吊牌金额］ )计算完［销售额］，接下来计算［销售额 同期］及［销售额 同比增长率］。产品信息表”中的度量值销售额 同期 =CALCULATE ( ［销售额］, SAMEPERIODLASTYEAR('日期表'［日期］ ) )销售额 同比增长率 =DIVIDE ( ［销售额］ - ［销售额 同期］, ［销售额 同期］ )这里要注意的是，每输入完一个度量值，要设置其格式。［销售额］、［销售额 同期］、［吊牌金额］为整数类型，［折扣率］、［销售额 同比增长率］为百分比类型。2.5 报告制作建模完成后，回到“报表”视图，制作可视化报告。业务需求是从时间维度、区域维度、产品维度对销售额进行分析的，根据业务需求逐一制作可视化图表。1．制作月度销售额趋势同期对比图对于时间类型的分析通常选择水平方向的可视化对象，在“可视化”窗格中单击“折线图”视觉对象按钮。将“日期表”中的月份名称字段拖入“X 轴”，将“产品信息表”中的度量值［销售额］和［销售额 同期］拖入“Y 轴”，快速生成月度销售额趋势对比图。由于没有筛选年份，因此本期和同期的销售额相同，如图 2-16 所示。图 2-16　快速生成月度销售额趋势同期对比图在“可视化”窗格中单击“切片器”视觉对象按钮，将“日期表”中的年字段拖入“字段”窗格，首先设置切片器的显示方式，单击“切片器”右上角的向下箭头，选择“列表”，如图 2-17 所示。然后设置切片器的显示方向，在“可视化”窗格中单击“设置视觉对象格式”“视觉对象”“切片器设置”“选项”“方向”，选择“水平”，然后将下方的“切片器标头”关掉，最后在“切片器”中选择“2019”。此时，“折线图”正确显示了月度本期和同期销售额，如图 2-18 所示。图 2-17　设置切片器显示方式为“列表”图 2-18 Year 切片器筛选“折线图”接下来简单进行图表美化。在“可视化”窗格下，单击“设置视觉对象格式”“视觉对象”，将“X 轴”和“Y 轴”的“标题”关掉；再单击“常规”，修改图表标题为“月度销售额趋势同期对比”，如图 2-19 所示。图 2-19　图表格式修改最后进行页面配色，推荐使用主题功能。通过修改报告的主题，快速对报告所有页面及图表进行统一的主题设置。在菜单栏中单击“视图”“主题”，选择适合报告展示场景的主题风格，此处选择“边界”，如图 2-20 所示。图 2-20 报告主题修改2．制作各季节商品销售额同期对比图在“可视化”窗格下，选择“折线和簇状柱形图”视觉对象按钮，将“产品信息表”中的季节字段拖入“X 轴”、度量值［销售额］和［销售额 同期］拖入“列y 轴”、［销售额 同比增长］拖入“行 y 轴”，简单进行美化，生成各季节商品销售额同期对比图，如图 2-21 所示。3．制作各品类销售额及折扣率对比图在“可视化”窗格下单击“簇状条形图”视觉对象按钮，将“产品信息表”中的品类字段拖入“Y轴”、度量值［销售额］拖入“X 轴”，简单美化后，生成各品类销售额对比图，如图 2-22 所示。我们还需进一步对比各品类的折扣率，找到折扣率偏低的品类。该场景的对比图可以参照图 2-21 中的各季节商品销售额同期对比图，使用水平方向的“折线和簇状柱形图”，将［销售额］放在“列 y 轴”、［折扣率］放在“行 y 轴”。此处我们使用另外一种方法，通过对条形图进行颜色设置，根据颜色深浅判断折扣率的高低。图 2-21　各季节商品销售额同期对比图图 2-22 各品类销售额对比图在“可视化”窗格中，选择“设置视觉对象格式”“视觉对象”“条形”“颜色”，单击“默认值”中的 ，如图 2-23 所示。进入“默认颜色 - 条形”界面，为数据颜色动态配色界面。在“格式样式”中选择“渐变”，在“应将此基于哪个字段？”中选择“折扣率”，在“最小值”和“最大值”中分别选择颜色，并勾选左下角的“添加中间颜色”，单击“确定”，如图 2-24 所示。图 2-23 数据颜色动态配色设置
图 2-24 各品类折扣率动态配色设置此时，图表展示各品类的销售额和折扣率。条形长度代表销售额，条形颜色代表折扣率，浅蓝色表示折扣率较低，红色表示折扣率居中，深蓝色表示折扣率较高。从图 2-25 中可以看出，女士防寒服的销售额和折扣率都非常高，裤套装、服饰配件的折扣率相对偏低。图 2-25 各品类销售额及折扣率对比4．制作各区域销售额同期对比图选择“折线和簇状柱形图”，将“门店信息表”中的区域字段拖入“X 轴”，将“产品信息表”中的度量值［销售额］和［销售额 同期］拖入“列 y 轴”、［销售额 同比增长率］拖入“行 y 轴”，简单进行美化，初步生成各区域销售额同期对比图，如图 2-26 所示。图 2-26 5．制作各经营业态销售额对比图选择“环形图”，将“门店信息表”中的经营业态字段拖入“图例”，将“产品信息表”中的度量值［销售额］拖入“值”，简单进行美化，生成各经营业态销售额对比图，如图2-27 所示。图2-27最后，对各图表的大小及位置进行微调，可视化报告制作完成，如图 2-28 所示。图 2-28　以上内容摘自《《Power BI 零售数据分析实战》，本书中还有更详细的案例步骤演示，并提供 Excel 数据源和 PBIX 源文件。

2023-04-18 10:22
来源:
部落窝教育发布于：四川省
编按：Excel最著名的是它处理数字的能力，它提供了大量的数字计算和统计函数，可以解决我们日常工作中遇到的各类数据统计问题，今天就和大家分享4个与平均值有关的问题。
问题1：根据指定的条件计算平均值
在Excel中，求一组数据的平均数一般是用AVERAGE函数来解决，但是，在计算具体问题的平均数时，往往有各种各样的限制条件。
下图是某公司的员工工资统计表，现在需要统计每个部门的平均工资，这时候就可以使用AVERAGEIF进行统计，公式为：=AVERAGEIF(C:C,F2,D:D)
这个函数的格式为：AVERAGEIF(条件区域，条件值，数据区域)，函数的基础用法可以参考往期教程，这里就不啰嗦了。
问题2：剔除一个最大值和一个最小值后求平均值
下图是某公司的员工技能大赛评分表，共有8名评委给选手打分，最终成绩是去掉一个最高分和一个最低分后计算平均分来确定的。
这种情况就需要用到TRIMMEAN函数，公式为：=TRIMMEAN(B2:I2,2/COUNT(B2:I2))
本例中在计算平均值的时候是去掉一个最大值和一个最小值，如果需要去掉n个最大值和n个最小值，再求平均值，则可使用如下公式：：
=TRIMMEAN(数据区域，2*n/COUNT(数据区域))。
对于这个函数会套用公式即可，具体原理有点复杂，就不解释了。
问题3：统计前三名的平均销量
在做销售分析的时候，有时候不是按照整体数据进行平均，而是对排名靠前的一部分品种去做平均。
例如下图所示的是11个商品在6个门店的销售数据，要统计每家店销量前三名的商品的平均销量，遇到这种情况，就需要使用AVERAGE+LARGE函数组合，公式为：=AVERAGE(LARGE(B2:B12,{1;2;3}))
本例中利用LARGE函数的k参数支持数组的特性，使用{1;2;3}作为LARGE函数的第2参数，提取销量中的前3个最大值，然后使用AVERAGE函数求其平均值，即得各店销售数量前3名的平均销量。
问题4：计算加权平均值
加权平均值，即各数值乘以相应的单位数，然后加总求和得到总体值，再除以总的单位数。
加权平均法计算的数据比较贴近实际，所以在计算一些要求比较精确的数据时都要求用加权平均法计算，例如产品平均毛利润率、进货平均单价等。
下图为某公司1月办公用品采购明细表，要计算各物品的加权平均单价需要使用SUMPRODUCT与SUMIF函数组合，公式为：
=SUMPRODUCT(($A$2:$A$100=F2)*$C$2:$C$100*$D$2:$D$100)/SUMIF($A$2:$A$100,F2,$C$2:$C$100)
公式看起来长，但是原理并不是很难理解，以商品A为例，用SUMPRODUCT(($A$2:$A$100=F2)*$C$2:$C$100*$D$2:$D$100)计算出采购商品A的总金额，也就是对商品A每次的采购数量*单价进行求和，再用SUMIF($A$2:$A$100,F2,$C$2:$C$100)计算出商品A的采购总数量，总金额/总数量就是加权后的平均进价。
做Excel高手，快速提升工作效率，部落窝教育《一周Excel直通车》视频和《Excel极速贯通班》直播课全心为你！
相关推荐：
将excel中的数据按照指定数量进行重复，非得用VBA才能实现吗？
如何在特定位置批量插入空行等12种实用办公技巧
4种删除excel重复值的小妙招，速收藏
Power Query的数据替换技巧比Excel函数更万能！
版权申明：
本文作者老菜鸟；同时部落窝教育享有专有使用权。若需转载请联系部落窝教育。返回搜狐，查看更多
责任编辑：