2.1.3工具篇-Excel——用Excel统计和分析数据

一、核心函数的实战运用

1. IF函数：条件判断的小能手

IF函数是Excel中最基本的逻辑判断工具。其格式为IF(逻辑测试, 结果真, 结果假)。举个例子，假设我们有一列销售数据，想要判断销售额是否超过5000元，超过则标记为"达标"，否则标记为"未达标"，可以这样使用：

=IF(A2 > 5000, "达标", "未达标")

2. COUNTIFS & SUMIFS家族：多条件计数与求和

这两个函数能够实现对满足多个条件的数据进行统计或求和。

• COUNTIFS(判断区域1,判断条件1, [判断区域2,判断条件2], ...): 对满足所有给定条件的单元格数量进行计数。
  例如，统计销售额大于5000且产品类别为“A类”的记录数：

=COUNTIFS(B2:B100, ">5000", C2:C100, "A类")

• SUMIFS(求和区域, 判断区域1,判断条件1, [判断区域2,判断条件2], ...): 根据多个条件对指定区域求和。
  例如，计算销售额大于5000且产品类别为“A类”的总销售额：

=SUMIFS(D2:D100, B2:B100, ">5000", C2:C100, "A类")

3. VLOOKUP（垂直查找匹配数据）

功能：VLOOKUP函数在表或数组的第一列中查找指定值，并返回同一行中其他列的值。

语法：

VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])

• lookup_value：用什么找，在员工名单表中想找的某个员工姓名，用员工ID找。
• table_array：去哪里找，去整个员工名单表找，VLOOKUP会在这一区域的第一列中搜寻你的“lookup_value，所以员工ID就得在第一列
• col_index_num：要找的对象在什么位置，VLOOKUP就会返回这个匹配项所在的行中指定列的值。这就像找到单词后，告诉你它在哪一页（即那一列）的定义。
• [range_lookup]：这是一个可选参数，告诉Excel你希望查找的方式是精确还是近似。如果你说“FALSE”或“0”，那就表示必须完全一样才算是找到了；如果是“TRUE”或“1”，那只要比查找值小的最大值就可以。

示例：
销售明细表

员工表

要统计每个员工的销售额，需要在销售明细表中根据员工ID，去员工表中匹配到员工的姓名

用什么找：员工ID，C列，第二行写C2
去哪里找：员工表，员工ID要在第一列，所以从B列开始选，一直选到包含要找的对象
要找的对象在第一列，从B开始数，在第2列
查找的方式：精确查找，选0

4. MATCH（查找数据所处相对位置）

功能：MATCH函数返回某个值在一个数组中的相对位置或者匹配项的位置。

语法：

MATCH(lookup_value, lookup_array, [match_type])

• lookup_value：要找的目标，比如在一组数字中找特定的数字，或者在一列名字中找特定的名字。
• lookup_array：搜索范围，相当于你要翻阅的一本书的目录部分。
• [match_type]：控制查找方式，你可以指定是要求精确匹配（0），小于等于查找值的最大值（1），或者大于等于查找值的最小值（-1）。就好比是在书中找页码，既可以精确到某一页，也可以找到最后包含该主题的那一章。

示例：
如果要在员工表中查找“老六”的位置，可以使用以下公式：

"老六"在C列的第5行，所以结果是5

5. INDEX（在指定区域内，获取相对位置的数据值）

功能：INDEX函数返回指定数组或单元格区域中的元素值。

语法：

INDEX(array, row_num, [column_num])

• array：一排、一列或者一个大矩阵，你想从这里取出特定位置的数据。
• row_num：数组中的行号，好比你想去矩阵的第几层楼拿东西。
• [column_num]：数组中的列号，就像是你要打开哪一扇窗户才能拿到那个物品。

示例：

在B3：C5区域里面，找第3行，第2列，结果为”老六“

6. OFFSET（以某个区域为初始，进行上下偏移）

功能：OFFSET函数返回以某个参照单元格为基础，按照指定的行数和列数偏移后的新引用区域。

语法：

OFFSET(reference, rows, cols, [height], [width])

• reference：起点，也就是你从哪里开始移动的位置，可以理解为地图上的一个标记点。
• rows：向下或向上的步数，类似于从参照点往下走几步或者往上走几步。
• cols：向右或向左的步数，就如同从参照点往右跨几步或者往左跨几步。
• [height] 和 [width]：这两个参数定义了新的引用区域有多高多宽，像是确定一个新的视野范围大小。如果只是取单个单元格的值，则通常不需要这两个参数。

示例：
若要从A1单元格开始，向下偏移2行，向右偏移1列，然后得到同样大小的一个区域的值：

=OFFSET(A1, 2, 1)

这将返回A3单元格的值。

7. 函数嵌套

联合使用MATCH和INDEX的例子：

如果我们想在非首列查找的情况下模拟VLOOKUP的功能，例如，在E列查找特定员工ID并在同一行获取A列的入职日期，可以这样组合使用MATCH和INDEX：

=INDEX(A:A, MATCH(3, B:B, 0))

这个公式首先通过MATCH函数找到E列中3的位置，然后INDEX函数根据这个位置返回B列对应姓名。

二、数据分析利器：数据透视表制作与应用

1、数据透视表的创建步骤：

1. 选择数据源：首先，选定包含待分析数据的单元格范围。确保数据集有清晰的标题行，并且数据无重复或遗漏。

2. 插入数据透视表：点击“插入”菜单，选择“数据透视表”，在弹出对话框中确认数据区域和放置新透视表的位置（新的工作表或现有工作表中的指定位置）。

3. 布局字段：

   • 行区域：将字段拖拽到此处，代表分类变量，如产品类别和地区。
   • 列区域：定义报表横轴的分类，例如时间周期或销售员姓名。
   • 值区域：用于统计分析的数值字段，通常会被自动汇总，默认为求和，也可以更改为计数、平均值、最大值等。
   • 筛选器：对数据进行高级过滤，比如只查看特定季度的数据。

2、实例演示

假设我们有一份包含地区、产品类型、销售额的销售数据，可以通过数据透视表快速汇总各地区的不同产品类型的总销售额。只需将“地区”拖至行区，“产品类型”拖至列区，“销售额”拖至值区，即可生成交互式的统计报表。

地区	产品类别	销售额
北京	A类产品	5000
上海	B类产品	6000
北京	A类产品	7000
广州	C类产品	8000
上海	A类产品	9000

通过数据透视表，可以快速得到各地区各类产品的销售额总和。

3、适用场景

可以结合《1.3认知篇——数据分析的常见方法和思路》里面提到的对比分析法、交叉分析法、结构分析法以及平均分析法等进行分析

三、数据分析工具集

先把加载项打开

1. 描述性统计分析：

-在Excel的数据分析工具集中，选择“描述统计”，然后输入数据区域，可以选择是否需要输出偏度、峰度等参数，点击确定后会生成一个包含数据集各项描述性统计量的新工作表。
可以快速计算数据集的中心趋势（如平均数、中位数）、分散程度（如标准差、方差）以及其他基本统计量，如最大值、最小值、四分位数等。

• 直方图：创建数据分布的直方图，以图形方式展示数据频率分布。
• 抽样：从数据集中抽取样本，支持简单随机抽样、分层抽样等多种方法。

2. 相关性与回归分析：

• 相关系数： 选择“相关性”，指定两个变量的数据区域，可得到两变量之间的Pearson相关系数及其显著性检验结果。
• 单变量和多变量回归分析：通过建立数学模型来研究一个或多个自变量如何影响因变量的变化，得到回归方程并可以评估模型拟合优度和显著性。

3. 假设检验：

• T-Test：单样本T-Test用于测试样本均值是否等于已知总体均值；配对样本T-Test用来比较两组相关样本的均值差异；独立样本T-Test则对比两组独立样本的均值。

4. 方差分析 (ANOVA)：

• 在数据分析工具集中，选择“单因素方差分析”或“双因素方差分析”。指定输入数据区域、分组列等信息，运行后可以得到F统计值、P值以及各个组间的均值比较结果。