项目地址：https://github.com/datawhalechina/free-excel

学习进度：

1. 数据缘何而来
2. Excel数据格式
3. Excel的快捷操作
4. Excel的表合并
5. Excel的函数 - IF函数
6. Excel的函数 - 查找函数
7. Excel的函数 - 动态函数
8. Excel数据可视化
9. Excel数据透视
10. Excel数据看板

1. 数据缘何而来

1.1 了解Excel

• 工作簿
• 工作表
• 单元格

1.2 从文件导入数据

获取数据

1.3 从网页导入数据

从网页导入数据

1.3 练习

1. 使用Excel数据导入功能，完成data/chap1/Titanic_train.txt数据导入。

导入文本文件

载入中

导入结果

2. 将工作簿文件data/chap1/Titanic_test.xlsx转为表格数据。

从表格中转换

转换结果

3. 将“中国银行外汇牌价”通过网页导入到 Excel 表中。

自网站导入数据

导航器

导入结果

2. Excel数据格式

2.1 单元格数据类型

Excel有11种数据类型：

• 常规
• 数字
• 货币
• 会计专用
• 短日期
• 长日期
• 时间
• 百分比
• 分数
• 科学记数
• 文本

2.1.1 数字

• 真数值默认情况下是右对齐
• 假数值是数值型文本属于文本，左上角有绿色三角错误检查，无法完成数字运算操作
• 12 位数以上时，以科学计数法显示
• Excel 自带数据纠错功能，点击黄色感叹号，将文本转出数字，或者使用=VALUE()函数将文本转成数字

数字格式

2.1.2 文本

• 主要为文本字符串

2.1.3 日期

• Excel 的日期最早是1900年1月1日，对应的数值为1
• 通过两日期相减可以得到日期相差的天数

日期格式

2.2 单元格格式

自定义格式的用法：https://baijiahao.baidu.com/s?id=1642006746212954122&wfr=spider&for=pc

2.3 转换单元格数据

通过数据工具的分列功能能够巧妙的将数据进行转换。

2.3.1 数值和文本相互转换

通过分列把数值转为文本

2.3.2 日期和文本相互转换

通过分列把日期转为文本

2.4 练习

1. 将data/chap1/Titanic_train工作簿中的Pclass列的数值转文本。

见 2.3.1 节

2. 完成data/chap2/2.1.xlsx中案例1-3中的问题。

见 2.3.2 节

3. 完成data/chap2/2.2.xlsx中案例1-2中的问题。

练习3

3. Excel的快捷操作

3.1 快捷键简介

3.1.1 文件相关

• Ctrl + N 创建一个新的 Excel 工作簿
• Ctrl + W 关闭当前工作簿
• Ctrl + S 保存当前的 Excel 工作簿
• F12 将当前文件进行另存为
• Ctrl + P 打印当前工作表

3.1.2 通用快捷键

• Ctrl + Z 撤销当前操作
• Ctrl + Y 恢复撤销，可以用这个快捷键恢复被撤消的操作
• Ctrl + C 复制所选单元格
• Ctrl + X 剪切所选单元格
• Ctrl + V 粘贴所选单元格
• Ctrl + F 弹出查找和替换对话框
• Ctrl + G 显示定位对话框

3.1.3 表格选择

• Ctrl + A 全选表格
• Ctrl + → 移动到数据区域的右边缘
• Ctrl + ← 移动到数据区域的左边缘
• Ctrl + ↑ 移动到数据区域的上边缘
• Ctrl + ↓ 移动到数据区域的下边缘

3.1.4 单元格编辑

• F2 编辑当前单元格（跟双击单元格效果一致）
• Alt + Enter 在当前单元格内换行（直接按 Enter 会跳转到下一行）
• Enter 下移一个单元格
• Shift + Enter 上移一个单元格
• Tab 右移一个单元格
• Shift + Tab 左移一个单元格
• Ctrl + Enter 输入完成，并停留在当前单元格
• Ctrl + ; 输入当前日期（显示为年/月/日）
• Ctrl + Shift + : 输入当前时间 (显示为小时/分钟)
• Ctrl + E 快速填充

3.1.5 Excel格式化

• Ctrl + 1 设置单元格格式
• Ctrl + B 加粗或取消加粗
• Ctrl + i 加斜体或取消斜体

3.1.6 Excel公式

• F4 绝对引用和相对引用切换
• Alt + = 自动插入求和公式

3.2 Excel快捷键实践

3.2.1 自动插入求和公式

1. Ctrl + Shift + → + ↓ 选择求和区域
2. Alt + = 自动插入求和公式

[Alt] + [=] 自动插入求和公式

3.2.2 自动进行列差异比对

使用快捷键 Ctrl + \ 定位出差异的单元格

• 从左至右选择区域，以左列为参照
• 从右至左选择区域，以右列为参照

[Ctrl] + [\] 定位列差异

3.2.3 自动填充

快捷键 Ctrl + E 进行自动填充

[Ctrl] + [E] 进行自动填充

3.3 练习

1. 完成data/chap3/3.1.xlsx工作簿中的【案例2】【案例3】的快捷键自动求和任务。

[Alt] + [=] 求和练习

2. 完成data/chap3/3.2.xlsx工作簿中的【案例2】【案例3】的多行快速比对任务（提示：需要选中所有待比对表格框）。

[Ctrl] + [\] 多列对比

3. 完成data/chap3/3.3.xlsx工作簿中的【案例3】【案例4】的快速填充任务。

[Ctrl] + [E] 自动填充练习

4. Excel的表合并

4.1 多表行合并

3个消费表格，每个客户的消费金额不同，现在需要统计每个客户在消费的总金额：

1. 选择需要汇总表的起点单元格
2. 选择 Excel 中的数据栏
3. 点击合并计算功能
4. 选择合并计算函数中的求和
5. 点击引用位置右侧的引用项
6. 选择第一个数据表所在的范围
7. 点击添加
8. 将 3 个数据表添加到所有引用位置
9. 选择标签位置信息
10. 点击完成

多表行合并

4.2 多表列合并

3个不同的消费表格，每个客户的消费金额不同，现在需要你统计每个客户在3个不同消费的总金额，操作与4.1类似：

多表列合并

4.3 练习

1. 选择data/chap3/4.1.xlsx工作簿中的【案例1】得到不同客户的消费次数。

练习 2

2. 选择data/chap3/4.1.xlsx工作簿中的【案例2】得到不同客户的不同项目的消费次数。

练习 2

5. Excel的函数 - IF函数

5.1 IF函数

=IF(logical_test, [value_if_true], [value_if_false])

【案例1】需要对客户的原始得分进行校正，当客户的原始得分为负时，校正为0。

=IF(B2<0,0,B2)

快速填充

• 选择C2单元格右下角，当鼠标变成黑色十字，向下拖拽，对剩下的列进行填充;

  
  
  十字拖拽

• 选择C2单元格右下角，当鼠标变成黑色十字，双击，对剩下的列进行填充;

  
  
  双击十字
• 以C2单元格开始选择需要填充公式的区域，进入公式编辑状态，使用快捷键Ctrl + Enter进行批量填充。
  
  [CTRL]+[Enter]

5.2 IF函数与复杂逻辑

=AND([logical1], [logical2], ...)
=OR([logical1], [logical2], ...)

【案例2】现在有客户的1，2月消费金额，如果客户2个月的消费金额都超过400，那么就认为这个客户为高消费客户。

=IF(AND(B2>400,C2>400),"高消费客户","")

IF函数与逻辑函数

5.3 IFS函数

=IFS(logical_test1, value_if_true1, [logical_test2, value_if_true2], [logical_test3; ...)

【案例3】有员工的相关考核分，考核得分在4.8分以上，优秀；考核得分在3.5-4.7分，良好；考核得分在0-3.4分，一般。

=IFS(B2<=3.4,"一般",AND(B2>=3.5,B2<=4.7),"良好",B2>=4.8,"优秀")

IFS函数

5.4 练习

1. 选择data/chap5/5.1.xlsx工作簿中的【案例1】，将客户原始得分为1以下的校正为0。

练习1

2. 选择data/chap5/5.1.xlsx工作簿中的【案例2】，客户1月2月中只要有1个月消费超过400，即为"高消费客户"。

练习2

3. 选择data/chap5/5.1.xlsx工作簿中的【案例3】，能否不使用IFS函数，使用IF函数通过嵌套完成相应的任务。

练习3

6. Excel的函数 - 查找函数

6.1 VLOOKUP

=XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found], [match_mode], [search_mode])

1. 【案例1】根据岗位编号，得到该员工的应发工资。

=VLOOKUP(K2,A2:H50,8,FALSE)

1. K2 单元格为要查找的值
2. A2:H50 是查找的范围
3. 应发工资在查找范围中的第 8 列
4. FALSE 为精确匹配

案例1

2. 【案例2】根据岗位编号，得到该员工的应发工资。

要查找的“应发工资”在查询列“员工编号”的右边
VLOOKUP函数查找的区域在被查找值的左侧

列变换技巧： IF({1,0},...)

=VLOOKUP(J2,IF({1,0},H2:H19,G2:G19),2,FALSE)

1. J2 是要查找的值所在单元格
2. IF({1,0},H2:H19,G2:G19) 是查找区域，使用IF函数调换H，G两列的顺序
3. 返回值所在列为查找区域的第2列，即G列（H，G列顺序已交换）
4. FALSE 为精确匹配

案例2

3. 【案例3】根据性别与部门信息，获得员工编号。

技巧：通过&拼接两列的信息

=VLOOKUP(J2&K2,IF({1,0},C2:C20&D2:D20,A2:A20),2,0)

案例3

4. 【案例4】当不知道具体的信息，需要进行模糊查询。

使用通配符进行模糊查询：

Excel中的通配符匹配规则

=VLOOKUP("*"&K2,A2:B20,2,0)

1. "*"&K2 匹配以K2 单元格的值结尾的值
2. A2:B20 查找范围
3. 返回的值在查找范围的第2列
4. 0 即是 TRUE，模糊查找

案例4

6.2 XLOOKUP

=XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found], [match_mode], [search_mode])

其中，

• 第1~3个参数跟LOOKUP的参数非常接近，但将「查找区域」和「结果区域」独立出来了
• 第4参数（可选参数）：if_not_found，找不到结果，就返回第四参数，如果省略第四参数函数默认返回#N/A这个错误值
• 第5参数（可选参数）：match_mode，指定匹配类型
  • 参数为：0 ，精确匹配，未找到结果，返回 #N/A。 这是默认选项。
  • 参数为：-1，近似匹配，未找到结果，返回下一个较小的项。
  • 参数为：1，近似匹配，未找到结果，返回下一个较大的项。
  • 参数为：2 ，通配符匹配
• 第6参数（可选参数）：search_mode, 指定要使用的搜索模式
  • 参数为：1，从第一项开始执行搜索。 这是默认选项
  • 参数为：-1，从最后一项开始执行反向搜索
  • 参数为：2，根据 lookup_array 按升序排序的二进制搜索。 如果未排序，将返回无效结果
  • 参数为：-2，根据lookup_array 按降序排序的二进制搜索。 如果未排序，将返回无效结果

1. 【案例1】

=XLOOKUP(K2,A1:A50,H1:H50)

1. K2 单元格为要查找的值
2. A1:A50 是查找的列
3. H1:H50 是结果（应发工资）所在列
4. 默认查找不到则返回#N/A
5. 默认为精确匹配

案例1

2. 【案例2】

=XLOOKUP(J2,H1:H19,G1:G19)

案例2

3. 【案例3】

=XLOOKUP(J2&K2,C1:C20&D1:D20,A1:A20)

案例3

4. 【案例4】

=XLOOKUP("*"&K2,A2:A20,B2:B20,,2)

1. "*"&K2 匹配以K2 单元格的值结尾的值
2. A2:A20 查找列
3. B2:B20 返回值所在列
4. 若查找不到结果则返回#N/A
5. 参数 2 表示通配符匹配

案例4

5. 【案例5】姓名6在数据表中出现了2次，想知道他的最后销售额应该怎么操作？

案例5

=XLOOKUP(H2,B1:B20,F1:F20,0,0,-1)

1. 查找目标为 H2 单元格的值

2. B1:B20 为查找列

3. F1:F20 为结果所在列

4. 若找不到结果，输出0

5. 参数 0 表示精确查找

6. 参数 -1 表示反向搜索（此处从下到上）

7. 【案例6】这里有4个人，现在想知道这4个人的最大销售额。

=MAX(XLOOKUP(H2:H5,B2:B20,F2:F20))

案例6

7. 【案例7】当数据集以横向形式时，如果查询到需要的数据。

横向数据的查询方法与纵向数据并无太大区别。

=XLOOKUP(B6,B2:L2,B1:L1)

案例7

7. Excel函数 - 动态函数

7.1 FILTER函数

FILTER(array, include, [if_empty])

FILTER函数

7.1.1 单条件筛选

【例7.1】获得湖北区的相关数据。

=FILTER(A2:G30,C2:C30="湖北区")

FILTER函数 - 单条件筛选

7.1.2 返回选定的列

【例7.2】如果在实际的任务中不需要所有列，只需要其中几行，那么应该怎么操作？

=FILTER(F2:G30,C2:C30="湖北区")

FILTER函数 - 返回选定的列

现在需要知道湖北区的最大销售金额是多少?

=MAX(FILTER(F2:F30,C2:C30="湖北区"))

FILTER+MAX函数 - 湖北区的最大销售金额

7.1.3 多条件筛选

ANlD逻辑：FILTER(array, (range1=criteria1) * (range2=criteria2), "No results")
OR逻辑：FILTER(array, (range1=criteria1) + (range2=criteria2), "No results")

,
【例7.3】计算湖北区，湖南区中最大的销售金额。

=MAX(FILTER(F2:F30,(C2:C30="湖北区")+(C2:C30="湖南区")))

FILTER函数 - 多条件筛选1

【例7.4】计算湖北区商品2的最大销售金额。

=MAX(FILTER(F2:F30,(C2:C30="湖北区")*(D2:D30="商品2")))

FILTER函数 - 多条件筛选2

7.2 SUMIF函数

SUMIF(range, criteria, [sum_range])

SUMIF函数

【例1】统计数学成绩在90分以上的成绩之和。

=SUMIF(B:B,">=90",B:B)

• 第1参数为条件区域，因为要判断数学成绩，B:B表示选择B列，也可以为实际区域B2:B12
• 第2参数为判定条件，判定条件为大于等于90
• 第3参数为实际求和区，这是选填参数

例1

【例2】统计小米系列的产品销售额是多少？

=SUMIF(B:B,"小米*",C:C)

例2

• "小米*"匹配所有的小米系产品
• C:C为统计的目标列

【例3】统计1-3月的预计销售额和实际销售额。

=SUMIF($B$2:$G$2,H$2,B3:G3)
=SUMIF($B$2:$G$2,I$2,B3:G3)

$符号表示绝对引用，字母前面加$表示绝对引用列，数字前加$表示绝对引用行，2 个都加即表示绝对引用该单元格 。

例3(1)

例3(2)

7.3 SUMIFS函数

SUMIFS(sum_range, criteria_range1, criteria1, [criteria_range2, criteria2], ...)

【例】统计语文成绩在80分以上，90分以下同学的数学成绩之和。

=SUMIFS(C2:C13,B2:B13,">80",B2:B13,"<90")

SUMIFS函数

7.4 SUBTOTAL函数

SUBTOTAL(function_num,ref1,[ref2],...)

SUBTOTAL函数

`Function_num `

SUM函数与SUBTOTAL对比

7.5 练习

1. 打开data/chap7/7.1xlsx中的【案例1】计算河北区中商品1的最小销售额。

=MIN(FILTER(F2:F30,(C2:C30="河北区")+(D2:D30="商品1")))

练习1

2. 打开data/chap7/7.1xlsx中的【案例1】计算湖北中什么渠道下的什么商品具有最高销售额。

=FILTER(D2:E30,(C2:C30="湖北区")*(F2:F30=LARGE(F2:F30,1)))

练习2

3. 打开data/chap7/7.2xlsx中的【案例1】语文成绩在80分以上的同学成绩之和。

=SUMPRODUCT(array1, [array2], [array3], ...)

=SUMPRODUCT((B2:G12)*(C2:C12>80))

练习3

4. 打开data/chap7/7.2xlsx中的【案例2】统计所有苹果系列产品的销售额。

=SUMIF(B:B, "苹果*", C:C)

练习4

5. 打开data/chap7/7.2xlsx中的【案例5】完成所有手机的销售额求和。

=SUMIF($B$1:$K$1,"*手机",B2:J2)
=SUMIF($B$1:$K$1,"*笔记本",B2:J2)

练习5

8. Excel数据可视化

8.1 数据条

【例】为销售金额绘制数据条。

【开始】 -> 【条件格式】-> 【数据条】

数据条

如果想仅显示数据条，不显示数字：

选择要修改的区域 -> 【条件格式】 -> 【管理规则】 -> 双击要修改的【规则】 -> 勾选【仅显示数据条】

仅显示数据条

8.2 条件单元格格式

【例】标注没有超过今天的截止日期。

1. 选择需要标注的范围
2. 【条件格式】 -> 【新建规则】 -> 【使用公式确定要设置格式的单元格】
3. 填入公式 =B2>TODAY()
4. 【格式】设置格式，如【填充】选择填充颜色
5. 【确定】x3

输入的公式是一个被模拟的对象，它应该针对所选区域的左上角第一个单元格来设置。Excel将根据其引用形式（绝对引用与相对引用）自动复制运用到所选区域的其它单元格。

条件单元格格式

8.3 迷你图

迷你图是适合放在工作表中的单个单元格内的小型图表

【例】现知道不同业务员1-12月的销售额，获取销售额最差月份以及业务员的销售额的变化趋势。

1. 确定销售额最差月份：

=XLOOKUP(MIN(B2:M2),B2:M2,$B$1:$M$1)

XLOOKUP

2. 生成迷你图

1）选择插入范围
2）【插入】
3）【迷你图】 -> 【折线】
4）选择数据范围

生成迷你图

8.4 练习

1. 选择data/chap8/8.2.xlsx工作簿中的【案例1】，对拜访客户数大于5以上的业务员进行黄色填充。

练习1

9. Excel数据透视

9.1 Excel数据透视表

数据透视表是计算、汇总和分析数据的强大工具，可帮助了解数据中的对比情况、模式和趋势。

1. 【插入】
2. 【数据透视表】
3. 选择表格或区域
4. 选择放置透视表的位置
5. 如果想看各个地区每月的销售金额及汇总：

• 把【区域】放置在【列】
• 把【日期】放置在【行】
• 把【金额】放置到【值】（默认是汇总求和）

Excel数据透视表示例

若要添加筛选条件，可以使用【切片器】，如查看不同店员的销售金额汇总：

1. 在【数据透视表分析】栏中
2. 选择【插入切片器】
3. 选择筛选项【员工】
4. 点击切片便可查看不同员工的销售金额
5. 如果要查看多个员工的可点击右上角的【多选】

切片器示例

9.2 Excel数据透视图

在数据透视表的基础上，可通过数据透视图向数据添加数据可视化。

步骤类似于数据透视表

Excel数据透视图示例

若要更改图表类型，可以在图表范围点击右键，选择【更改系列图表类型】，然后在对话框中选择需要的图表类型。

更改系列图表类型示例

同样也可以添加【切片器】来筛选图表的曲线。

透视图与切片器示例

10. Excel数据看板

10.1 业务背景

• 数据源：某厂家在淘宝、拼多多、京东这3个电商销售平台的销售数据
• 目标：做一个数据周报看板，显示商品在不同渠道的每周销售情况

数据源

10.2 业务分析

1. 确定每天日期所在周数与每周的第几天
2. 根据确定周数对销售金额进行汇总
3. 对销售金额进行动态展示

10.3 实现过程

1. 确定日期的周数与星期

• WEEKNUM函数返回日期所在年份的第几周

WEEKNUM函数语法

=WEEKNUM(B2,2)

WEEKNUM函数示例

• WEEKDAY函数返回数字代表日期对应星期几

WEEKDAY函数语法

=WEEKDAY(B2,11)

WEEKDAY函数示例

2. 销售基础数据整理

1）本周开始日期

回忆一下XLOOKUP函数

XLOOKUP函数语法

=XLOOKUP(C2,数据源!H1:H50001,数据源!B1:B50001)

• C2为搜索的周数
• 数据源!H1:H50001表示在“数据源”sheet中的H列的搜索范围
• 数据源!B1:B50001表示在“数据源”sheet中的B列返回值的范围

XLOOKUP函数返回本周开始日期

注：需要把单元格格式设置为【日期】格式

2）本周结束日期

=XLOOKUP(C2,数据源!H1:H50001,数据源!B1:B50001,0,0,-1)

• 最后一个参数-1表示从反方向（从下而上）搜索，因为日期是升序排列，所以会返回该周的最后一天的日期

XLOOKUP函数返回本周结束日期

3）上周开始与结束日期

=C3-7
=C4-7

• 直接使用本周的起始的两个日期减去7天得到

上周开始日期

上周结束日期

4）本周与上周销售额

回忆一下SUMIFS函数，计算本周销售额

SUMIFS函数语法

=SUMIFS(数据源!$F:$F,数据源!$B:$B,">="&$C$3,数据源!$B:$B,"<="&$C$4)

• 数据源!$F:$F表示求和的范围（下单金额）
• 数据源!$B:$B,">="&$C$3,数据源!$B:$B,"<="&$C$4 表示介于本周开始日期（C3）与本周结束日期（C4）之间

本周销售额

同理计算上周销售额

=SUMIFS(数据源!$F:$F,数据源!$B:$B,">="&$E$3,数据源!$B:$B,"<="&$E$4)

上周销售额

5）本周与上周订单数

类似SUMIFS函数，使用COUNTIFS函数计算订单数

COUNTIFS函数语法

=COUNTIFS(数据源!$B:$B,">="&$C$3,数据源!$B:$B,"<="&$C$4)

本周订单数

=COUNTIFS(数据源!$B:$B,">="&$E$3,数据源!$B:$B,"<="&$E$4)

上周订单数

6）比较销售额和订单数的变化

使用TEXT函数输出百分比格式

=TEXT(((C5-E5)/E5)100,"0.00")&"%"
=TEXT(((C6-E6)/E6)100,"0.00")&"%"

销售额变化

订单数变化

• 设置单元格条件格式：当本周销售额/订单数比上周销售额/订单数大时，显示为红色；反之为绿色。

设置单元格颜色规则

单元格条件格式

3. 周销售数据变化趋势

1）统计一周每天的下单金额

=SUMIFS(数据源!$F:$F,数据源!$B:$B,">="&$C$3,数据源!$B:$B,"<="&$C$4,数据源!$I:$I,"="&计算过程!G3)

一周每天的下单金额

2）统计一周每天的下单数

=COUNTIFS(数据源!$B:$B,">="&$C$3,数据源!$B:$B,"<="&$C$4,数据源!$I:$I,"="&计算过程!G3)

一周每天的下单数

3）绘制图表

下单金额与下单数的折线图

4. 周销售渠道数据整理

1）不同渠道的销售金额

=SUMIFS(数据源!$F:$F,数据源!$E:$E,Q3,数据源!$B:$B,">="&$C$3,数据源!$B:$B,"<="&$C$4)

不同渠道的销售金额

2）不同渠道的下单数

=COUNTIFS(数据源!$E:$E,Q3,数据源!$B:$B,">="&$C$3,数据源!$B:$B,"<="&$C$4)

不同渠道的下单数

待续未完....