基于Excel的数据分析思维与分析方法

数据分析一定要会Excel、SQL和Python？非常肯定地回答您，Python、R语言、Excel函数和VBA，以及高级数据分析软件，都学不到，您将学到：5个有效的数据分析利器，以及分析思维

一、描述性统计分析

在进行分析前，总需要有数据预处理，这里略。

1. 平均数、中位数

平均数和中位数通常结合来使用，主要用于观察数据的分布情况。

• 平均数>中位数：数据可能出现右偏（右长尾），即偏大的数据居多，拉高了整体均值。
• 平均数<中位数：数据可能出现左偏（左长尾），即偏小的数据居多，拉低了整体均值。

2. 方差、标准差

方差，指数据的离散程度。方差和标准差可以将微小的变化显著放大，即更直观地观察到业务的风险问题。

• 大方差和标准差：指标波动大，业务稳定性低，经营风险高。
• 小方差和标准差：指标波动小，业务稳定性高，经营风险低。

3. 分位数与异常值

通常使用IQR方法来检测异常值，IQR = Q3 - Q1，Q为n分位数，箱线图的上界：Q1 - 1.5 × IQR；箱线图的下界：Q1 + 1.5 × IQR；超出箱体上界或下界的即认为是异常值。

4. 使用Excel进行描述性统计分析

文件 》选项 》在“加载项”中找到“分析工具库” 》点击转到 》勾选“分析工具库”，点击确定

在“数据”选项卡找到“数据分析”，并点击，选择“描述性统计”，并填写相关信息

然后就可以看到数据的描述性统计了

而箱线图可以使用插入图表功能来绘制箱线图，略。

二、变化分析

指标变化，说明业务环境发生了变化。变化包括上升、下跌、不变三种，无论是哪种变化，不能说明绝对好坏，如不变不一定是好事。意味着我们对指标的3种变化都要进行分析。

2.1 同比

本期数据与同期数据对比，主要是消除季节性或周期性变化的影响，而忽略周期间的变化。周同比、月同比、季度同比、年同比等。

计算方式：（数据A - 数据B）/ 数据B
例子：第3周周一的访客数同比第1周周一增长了250%
适用场景：判断是否比去年好

2.2 环比

环比，与相邻时期（上一个时间段），主要是考察业务的连续波动情况，会被周期性波动影响。日环比、月环比、季度环比、年环比等。

计算方式：同上
例子：第二周周六周日的访客数分别为120和150，则第二周周日环比增长25%
适用场景：判断是否比上期好

2.3 使用Excel条件格式进行变化分析

除了使用公式计算环比和同比进行变化分析，也可以使用Excel的条件格式进行变化分析（包括数据条，色阶，突出规则等）

三、指标体系

3.1 指标

指标定义
MECE原则，即完全穷尽，相互独立，无重复，无遗漏。

• 非MECE分类：一个客户可能既是老客户又是 VIP，也可能既活跃又 VIP，互斥性不满足，且可能遗漏新客户。
• MECE分类：新客户、老客户（又可以分为活跃老客户、流失老客户）

指标的特性：

• 必须是数值，不能是文本、日期等字符
• 必须是通过汇总得来的，即复合指标。（除不能再拆分的原子指标外）
• 有清晰明确的业务含义的（强业务意义性）
• 动态变化

指标绝不可以轻易更改！！！

理论上，北极星指标应该只有一个，清晰、可衡量，并能对齐整个组织的努力方向。但在实际操作中，不同团队或子产品可以拥有自己的“局部北极星指标”作为支持性指标，构成完整的指标体系。

北极星指标、虚荣指标
北极星指标，又叫唯一关键指标（OMTM），是指在当前运营阶段最重要的指标，目的是通过北极星指标专注和聚焦在当前阶段最重要的问题上。

• 一个故事理解北极星指标：早在Facebook之前，MySpace已是互联网社交产品的领头羊，对于MySpace，其战略指标是“总注册用户数”，以表示MySpace的用户规模。反观Facebook，却用“活跃用户数”作为战略指标。很明显两家社交平台关注的战略指标完全不一样，一个是关注注册用户数，典型的规模类指标，很明显MySpace是在追求用户规模，对应的策略就是大量扩张新用户；一个是关注活跃用户数，典型的质量类指标，很明显Facebook更关心用户的质量，对应的策略就是从产品功能、运营策略上不断去满足用户需求。因为关注的战略指标不同，导致两个公司截然不同的经营策略，最终结果大家均知，MySpace已不知去向，而Facebook成为互联网社交大佬。

在产品运营中，与北极星指标相对应的是虚荣指标。北极星指标是当前产品阶段战略层面的指标，而虚荣指标是无法体现当前阶段战略方向的，仅仅是数字上很漂亮的指标，故谓之虚荣指标。最著名的虚荣指标就是累计注册用户数。虚荣指标有一个特别有趣的特性：只增不减

北极星指标并非固定不变！！！产品本身是有生命周期的，在不同的生命周期产品承担的使命也不同，在诞生期的产品，其核心目的是迅速扩大用户规模，尽快占有市场，以取得先发优势；在发展期的产品，其核心目的是提升用户质量，尽可能黏住用户并完成营收转化。所以在产品的不同生命周期，其产品特性、商业模式和经营策略都不尽相同。

3.2 维度

维度概念：维度是描述指标的不同角度，如地理维度（其中包括国家、地
区、省以及城市等级别的内容）、时间维度（其中包括年、季、月、
周、日等级别的内容），维度既可以是文本也可以是数值。
维度操作：

• 维度上卷：维度上卷是从明细数据到汇总数据进行分析的过程。例如在分析用户地区的销售额时，首先分析的是城市维度的销售额，然后逐步汇总到省份的销售额。通过维度上卷，可以从整体了解数据变化的影响程度，做出正确的决策。

• 维度下钻：维度下钻是从汇总计算的数据逐步拆解到明细数据的分析过程。例如在分析用户地区的销售额时，首先分析的是省份维度的销售额，然后逐步细分到城市的销售额。

3.3 如何用“指标体系”来分析指标变化的原因

寻找指标变化原因时，通常采用多维度分析方法，多维分析包括如
下两个层面：

3.3.1 基于指标的业务口径来寻找原因（规模）
例如，电商的收入规模类指标GMV，即成交金额，其标准业务口径包含已付款和拍下未付款两部分。当GMV发生变化时，按照指标的业务口径分析，即分析已付款和拍下未付款的变化分别是什么，可以分为以下几种情况：

1. GMV上涨，已付款上涨，拍下未付款上涨。
2. GMV上涨，已付款上涨，拍下未付款下降或不变。
3. GMV上涨，已付款下降或不变，拍下未付款上涨。
4. GMV下跌 … 等
5. …

然后再去寻找已付款由哪些部分组成，或拍下未付款由哪些部分
组成，追踪这两个指标的业务口径，继续排查原因。

3.3.2 基于指标关联的维度来寻找原因（即指标的构成角度）
例如，产品的DAU（每日活跃用户），按照指标的维度来分析，标准分析过程是按照新客、老客、沉默唤醒、流失召回四大客群的DAU分别进行分析，来寻找产品整体DAU的变化原因。整体DAU=新客DAU+老客DAU+沉默唤醒DAU。继续细分，排查原因。

四、相关性分析

4.1 定义

相关性分析，指对两个或多个指标进行分析，评估它们两两之间联系或相互影响的程度。相关性分析的应用范围非常广，除了互联网产品，传统行业和线下业务也可以用，原因在于相关性分析支持几乎所有的业务指标类型，包括连续型指标和离散型指标。离散型指标又分为二分类型、无序型、有序型。

！！！切记：相关性 ≠ 因果，相关性是指一个变量变化的同时，另一个变量也会伴随发生变化，但不能确定一个变量变化是不是另一个变量变化的原因。因果关系，是指一个变量的存在一定会导致另一个变量的产生。

4.2 相关性分析的3种算法

相关性分析的2个前提条件：无异常值、指标间务必相互独立，互不影响。

4.2.1 连续型指标 vs 连续型指标：皮尔逊相关系数（Pearson）
Pearson的前提条件：

• 指标服从正态分布
• 两个指标的数据要一一对应，成对出现
• 无异常值

说明： Pearson 相关系数 𝑟 衡量的是样本中两个变量之间的线性相关强度，但它仅仅是样本估计值，不能直接推断总体的关系是否显著，因此需要显著性检验（假设检验，通常使用t检验）来确认相关性是否可信。

4.2.2 有序离散型指标 vs 有序离散型指标：斯皮尔曼等级相关系数（Spearman）或肯德尔秩相关系数（Kendall）
前提条件：

• 指标不服从正态分布
• 两个指标的数据要一一对应，成对出现
• 无异常值

4.2.3 无序离散型指标和连续型指标的相关性算法：对不起，无相关性

4.3 在Excel中进行“相关性分析”

4.3.1 如何寻找对购买转化率贡献最大的渠道
目标：评估哪些渠道的用户对整体购买转化率贡献最大。
过程：

• “数据”选项卡找到“数据分析”，选择相关系数（只有默认的Pearson系数）进行相关性分析
  

• 相关性分析结果：渠道B购买转化率与整体的最相关，因此，需要重点关注发展渠道B的业务。

五、趋势预测

预测分析是典型的数据挖掘应用，通过分析序列进行合理预测，做到提前掌握未来发展趋势，为业务经营决策提供依据。大数据有4个特性，即4V：Volume（大量），Velocity（高速），Variety（多样）和Value（价值）。
可以“趋势预测”是因为：

• 足够大的样本量和足够丰富的样本维度。
• 考察概率而不是准确率！！！。置信度、置信区间、可能性的字眼，它们都是在说明分析的结果有多少可信度，而不是有多少准确性。
• 相关而非因果。影响指标变化的因素也是多维的，实际业务经营中寻找原因远比寻找相关因素要困难得多。

5.1 基于时间序列的趋势预测

时间序列预测，认为指标的历史数据是有一定延续性的，即历史上指标的趋势在未来也会大概率延续，同时接受一定程度的指标波动，认为是合理的随机事件，并把这种波动也考虑到未来走势中，所以时间序列预测输出的趋势以及波动性，是和历史数据的趋势与波动性大概率高度一致的。因此，时间序列预测属于定量分析，也叫方向性分析，是找出未来可能的走势方向，其输出的趋势值仅供参考。

5.2 基于回归分析的趋势预测

回归分析，是指定量分析两种或两种以上指标间相互依赖关系的分析方法。回归分析支持两个或多个指标间的关系。

5.3 在Excel中实现时间序列趋势预测

在Excel中，能够实现时间序列预测的叫预测工作表，这个工作表在“数据”选项卡。
操作流程：

1. 检查数据是否符合要求。要求数据为两列，第一列必须是时间序列，且间隔均匀，即必须是连续、等差的时间数据，不能出现缺漏的情况。

2. 选中所有数据，点击“数据”选项卡，找到并点击“预测工作表”，弹出时间序列配置窗口。
   

3. 通常Excel会自动分析数据的季节性和周期性，并输出预测线。检查各项配置无误后，单击“确定”按钮即可。

5.4 在Excel中实现回归分析趋势预测

在Excel中，能够实现回归分析的叫趋势线，是图表的附属功能。
操作步骤：

1. 选中所有数据，点击“插入”选项卡，选择柱状图
2. 在柱状图的数据柱上单击右键，找到“添加趋势线”，点击“添加趋势线”，弹出趋势线配置窗口
3. 勾选“显示R平方值”，切换不同的回归算法，选择R2接近于1的算法（一般情况下用线性或指数，其他算法较少使用）
4. 在趋势线配置窗口找到“向前”，输入预测周期