Excel 做数据分析？是真的很强！

原文链接：怎样用 Excel 做数据分析？

知乎：Jackpop，粉丝 10 万+，每日更新精品内容！

做数据分析可能会用到很多工具，Python、R、Java 等等。

但是，无论如何都无法绕开 Excel。虽然涉及到生产的项目都会用编程语言或者 SQL 开发，但是日常的一些探查和分析都会用到 Excel。

如何用 Excel 进行数据分析，我认为可以拆成 2 个方面来看，一个是从 Excel 角度，一个是从数据分析角度。

因此，我把 Excel 分析拆解为两步：

• 掌握 Excel

• 数据分析

下面，就来从这两个方面来逐步介绍如何利用 Excel 进行数据分析。

一、 掌握 Excel

我认为，学习一样知识不应该马上一头扎进去学习，而是要理清学习计划，循序渐进，条例清晰。

针对 Excel 学习，我把它分为 4 个层级：

• 新手

• 中级

• 高级

• 专家

下面就逐级介绍一下要学习的内容。

新手

其实绝大多数自认为会 Excel 的同学都处于新手阶段。

对于新手阶段，我认为重点应该是学会 Excel 的基本操作，例如，最基本的新建、输入、保存、插入、新建 sheet，以及一些简单的函数，例如，SUM、IF、AVERAGE、COUNT、ROUND.

中级

针对文本、日期、数学运算能够轻松构建公式，此外，需要了解一些深入的功能，例如，数据透视表、vlookup 知道什么是插件以及如何安装插件，了解并且可以使用自动筛选功能，可以录制宏并使用，可以成功编辑/修改简单录制的宏。

中级公式：SUMIF、COUNTIF、VLOOKUP、CHOOSE、MID

高级

在高级阶段，需要重点把精力放在如下方面：

• 知道什么是数组公式以及如何使用

• 可以使用或禁用宏记录器创建和修改宏

• 知道如何构建可由事件触发的宏

• 熟悉 Excel 中的大多数函数

• 使用 Excel 进行简单的教学，能够有成体系的 Excel 知识

高级公式：SUMPRODUCT、INDIRECT、INDEX、MATCH、OFFSET

专家

对于 Excel 专家层面，可以通过 Excel 构建用于分发和广泛使用的加载项，而且，在这个层面，就不仅仅是会用 Excel 公式，而是能够清晰的认知到每个公式的优缺点，能够在使用中选择恰到好处的用法。

如何提升 Excel 水平？

我认为很多同学在学习 Excel 过程中都是需求推动学习，简单来说，就是遇到问题了，然后去单点的学习一下。

我个人认为，如果希望在某个方面深入学习，这并不是一个好的学习方法，时间花费的也不少，但是学的东西零零散散、不成体系。

我把学习 Excel 分为 2 个阶段：

• 基本知识学习

• 日常积累

基本知识学习

在基本知识学习阶段，我建议要系统、成体系的的学习 Excel、了解 Excel，知识它拥有哪些功能，以及每项功能是干什么用的。

这样，当我们遇到问题时，至少知道 Excel 是可以完成这项工作的，然后可以再回头去加深理解和印象。

在基础知识学习阶段，可以选择跟随书本或者一些在线教程学习。

不过，我个人更加推荐选择一本好的书籍，因为一本的好的书籍知识更加详细、全面。

这里给大家推荐 5 本我认为还不错的书籍，感兴趣的可以选择适合自己的看一下：

• 《你早该这么玩 excel》

• 《Excel 应用大全》

• 《别怕，Excel 函数其实很简单》

• 《Excel 函数与公式应用技巧》

• 《Excel VBA 其实很简单》

日常积累

就如同前面所说，书本知识让我们能够对 Excel 的基本知识有一个框架性的认识，如过真的想成长为 Excel 领域的专家，还是需要日常不断地加深和巩固。

针对加深和巩固，一方面是需要多用，把已掌握知识从会用变更熟练。二是不断汲取新知识，这个可以通过多留意一些论坛或者学习平台，“三人行，必有我师”，从与人交流中，能够看到很多自己并不了解的 Excel 用法。

这里，给大家推荐 2 个不错的论坛，不推荐太多，贪多嚼不烂。

[Excelhome](https://www.zhihu.com/question/Excel图表与图形 - ExcelHome技术论坛)

Excelhome 是一个知名老牌的 Excel 论坛，现在已经有 500 万左右用户，近千万帖子，从 Excel 基础知识，到函数公式、VBA、图标应有尽有，不仅资源丰富，而且大牛云集，有非常多优质原创的内容。

Excel精英论坛

和 Excelhome 一样，Excel 精英论坛也是一个专业的 Excel 交流与分享论坛，技巧、公式、VBA 应有尽有。论坛中有大量的帖子，罗列了 Excel 使用中的各种各样问题，里面也有很多 Excel 大牛回答。

从这些问题和解答中，我们可以接触并学习到很多在日常工作中用不到的场景，能够大大开阔有关 Excel 的知识面。

二、数据分析

作为最流行的数据分析工具之一，Excel 配置了很多围绕数据分析相关的功能，例如，数据透视表，就是一项非常受欢迎的分析工具。

下面，就来给大家介绍一下如何通过 Excel 导入、探索、清理、分析、可视化数据，深入讨论 Excel 中各种数据分析方法。

1. 排序

对数据进行排序是数据分析中非常关键和重要的部分，可以按多列或者单列对 Excel 数据进行排序。

排序动作主要分为按升序或降序进行排序。

单列排序

考虑以下数据：

让我们根据 C 列 T.Bills 对数据进行排序，要进行数据排序，需要进行如下操作：

• 单击要排序的列中的任何单元格。

• 选择“开始->排序和筛选”，对相关列进行排序

排序后的结果如下：

多列排序

单列排序比较常用也比较基础，下面就介绍稍微复杂一些的多列排序。

框选多列数据，点击“开始->排序和筛选”，选择“自定义排序”，可以添加多个排序列，针对每一列也可以选择升序或者降序，以及排序的主要依据、次要依据：

这样，当主要依据相同的情况下，会按照次要依据进行排序。

2. 筛选

当我们想要获取与特定条件匹配的数据时，我们就需要使用 s 筛选功能。

单击数据中的任何单个单元格。

转到数据选项卡 > 排序和筛选 > 筛选，这时候会发现每一列前面出现一个下拉小箭头，点击箭头即可进行筛选：

3. COUNTIF

COUNTIF 是一个非常常用的 Excel 函数，用于对满足单个条件的区域中的单元格进行计数。

句法：

=COUNTIF（范围，条件）

举个例子：

让我们统计 Stock 是小于 10%的数量：

这里就可以用到 COUNTIF，2 个参数分别是统计数据的范围，以及判断条件。

这只是一个简单的例子，大家可以举一反三，条件语句在编程语言中就会经常用到，在数据统计中使用同样非常频繁。

4. SUMIF

Excel SUMIF 函数返回满足单个条件的单元格的总和，用法和 COUNTIF 类似，都是统计分析中经常会用到的操作。

句法：

=SUMIF（范围，标准，[sum_range]）

5. 数据透视表

数据透视表是 Excel 中比较强大且用处非常广泛的一项功能了，我们可以使用它们来汇总存储在表中的数据。

通过数据透视表，可以组织和重新排列统计数据，进而可以更清晰的把精力投入到关键和有价值的信息上面。

假设，我们要对下面数据按照不同世纪（Cent）进行统计分析。

我们可以点击“插入->数据透视表”，创建一个透视表，然后选择自己关注的维度进行分析：

具体效果如下：

是不是看起来清晰多了？

当然，我们可以自由组织筛选器、行、列、统计值，以满足我们个性化的分析需求。

6. 使用求解器进行假设分析

假设分析是更改值以尝试公式的不同值（场景）的过程，你可以在一个或多个公式中使用几组不同的值来探索所有不同的结果。

求解器是一个 Microsoft Excel 插件程序，在假设分析中能够提供许多层面帮助。

我们可以使用此功能在一个单元格（称为目标单元格）中找到公式的最佳（最大值或最小值）值。 这受到工作表上其他公式单元格值的一些约束或限制。

求解器使用一组单元格，称为决策变量或简称为变量单元格，用于计算目标和约束单元格中的公式。 求解器还会调整决策变量单元格的值以处理约束单元格的限制。

因此，这有助于为目标单元产生所需的结果。

激活求解器插件

在文件选项卡上，单击选项，转到加载项，选择 Solver Add-in，然后单击 Go 按钮。

检查求解器插件，并单击 OK：

在数据选项卡的分析组中，可以看到添加了求解器选项。

如何在 Excel 中使用求解器？

在这个例子中，我们将尝试找到一个简单优化问题的解决方案。

问题：假设你是一家企业的所有者，并且希望你的收入为 3000 美元。

目标：计算要销售的单位和单位价格以实现目标。

例如，我们创建了以下模型：

• 在数据选项卡的分析组中，单击求解器按钮

• 在设定的目标中，选择收入单元格并将其值设置为 3000 美元

• 要更改变量单元格，请选择 C3、C4 和 C8 单元格

• 点击求解

会发现模型会发生一些变化：

看似非常简单的几步操作，其实它背后涉及负载的数学计算和优化问题。

而通过 Excel 求解器只需要简单的几步就可以完成，在这方面来说，Excel 在数据分析方面的确很强大。

7. 数据分析工具包

• 单击文件选项卡，单击选项，然后单击加载项类别

• 选择 Analysis ToolPak 并单击 Go 按钮

• 检查分析工具库并单击确定

在“数据”选项卡的“分析”组中，您现在可以单击“数据分析”：

添加图片注释，不超过 140 字（可选）

在数据分析工具包中，Excel 提供了非常多类似于求解器一样的大大解决数据分析难度的功能和设计。

8. 描述性统计

顾名思义，只需要给它简单的描述，它就可以根据需求进行分析数据，例如：

• 平均值、中位数、众数和范围

• 方差和标准差

假设，我们有一个击球手最近 10 场比赛的得分，要生成描述性分析，请按照以下步骤操作。

• 转到数据选项卡 > 分析组 > 数据分析

• 选择描述性统计并单击确定

• 选择输入的范围

• 选择要显示输出的范围

• 检查汇总统计信息

到此为止，描述性分析结果就出来了：

9. 方差分析

Excel 中的 ANOVA（方差分析）是一种统计方法，用于测试两个或多个平均值之间的差异，无论是用 Excel 还是 Python 或者 R，方差分析都是数据分析中常用的方法。

给出一份数据，下面是三名击球手最近 8 场比赛的得分：

要进行单因子方差分析，需要按照以下步骤操作：

• 转到数据选项卡 > 分析组 > 数据分析

• 选择 Anova: Single Factor 并单击 OK

• 选择输入和输出，然后点击确定

这样方差分析结果就好了：

10. 回归分析

在 Excel 中，我们使用回归分析来估计两个或多个变量之间的关系。

考虑以下数据，其中我们在特定月份销售了几个 COVID 病例和口罩。

• 转到数据选项卡 > 分析组 > 数据分析

• 选择回归并单击确定

它 UI 打开如下参数窗口：

选择输入 Y 范围作为销售的口罩数量，输入 X 范围作为 COVID 病例，检查残差并单击确定，我们会获得如下信息：

Multiple R 是衡量两个变量之间线性关系强度的相关系数。

R Square 表示确定系数，用作拟合优度的指标。 在 R Square 的帮助下，可以跟踪有多少点落在回归线上。

Standard Error 是另一种拟合优度度量，它显示了回归分析的精度。

通过这篇内容，我先介绍了如何学习和进阶 Excel，然后围绕 Excel 数据分析，从浅入深，其中有比较简单的排序、筛选，也有较复杂的求解器、方差分析、回归分析，这些都是数据分析工作中经常会用到的。在以往，我们需要通过编程语言实现复杂的算法，或者安装调用第三方工具包，其实通过 Excel 也能够轻松应对，而且更节省时间。因此，作为一款强大的数据分析工具，Excel 不仅能够处理一些常规的数据处理，还可以处理很多复杂的数据分析需求。

一篇文章无法面面俱到的介绍，希望通过这篇内容能够将 Excel 数据分析相关的知识点串起来，给感兴趣的同学一个基本的蓝图，感兴趣的同学可以再深入学习。