2.1.3工具篇-Excel——用Excel统计和分析数据
- 一、核心函数的实战运用
- 1. IF函数：条件判断的小能手
- 2. COUNTIFS & SUMIFS家族：多条件计数与求和
- 3. VLOOKUP（垂直查找匹配数据）
- 4. MATCH（查找数据所处相对位置）
- 5. INDEX（在指定区域内，获取相对位置的数据值）
- 6. OFFSET（以某个区域为初始，进行上下偏移）
- 7. 函数嵌套
- 二、数据分析利器：数据透视表制作与应用
- 1、**数据透视表的创建步骤：**
- 2、**实例演示**
- 3、**适用场景**
- 三、数据分析工具集
- 1. **描述性统计分析**：
- 2. **相关性与回归分析**：
- 3. **假设检验**：
- 4. **方差分析 (ANOVA)**：
一、核心函数的实战运用
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、数据透视表的创建步骤：
选择数据源：首先，选定包含待分析数据的单元格范围。确保数据集有清晰的标题行，并且数据无重复或遗漏。
插入数据透视表：点击“插入”菜单，选择“数据透视表”，在弹出对话框中确认数据区域和放置新透视表的位置（新的工作表或现有工作表中的指定位置）。
布局字段：
- 行区域：将字段拖拽到此处，代表分类变量，如产品类别和地区。
- 列区域：定义报表横轴的分类，例如时间周期或销售员姓名。
- 值区域：用于统计分析的数值字段，通常会被自动汇总，默认为求和，也可以更改为计数、平均值、最大值等。
- 筛选器：对数据进行高级过滤，比如只查看特定季度的数据。
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值以及各个组间的均值比较结果。
