EXCEL教程 | 拜托三连了！全B站最用心（没有之一）的EXCEL免费课程！O

快速填充

• 方法一
  

  S03-快速填充，批量提取与组合数... P4 - 03:59

  
  第一个单元格输入应填内容后，选择 “快速填充” / Ctrl + E

• 方法二
  

  S03-快速填充，批量提取与组合数... P4 - 04:32

  
  使用填充柄（单元格右下角下拉 / 双击），然后再选择快速填充

如果在使用快速填充之后，发现自动填充的结果并非想要的，可以手动多输入几个单元格内容，然后再进行快速填充

参考使用场景

1. 产品规格
2. 名字与职位重组（如果报错，则数据不相连）使用快速填充时，附近必须有数据，不可单列全空
   

   S03-快速填充，批量提取与组合数... P4 - 07:21

   
3. 数据与文本共存
4. 身份证数字提取 若出现左右不对齐，则格式不同（可通过 “居中” 解决）

快速分析

• 选中表格，点击右下角图标 / Ctrl + Q
• 数据条、色阶、图标等
• 图标改变，可以选择 “条件格式” 的编辑规则
• 迷你图（快速分析 / “插入”）

Tip：选中需改变行高/列宽的，然后拖拽其中一个，即可全部改变

数据录入

• 快捷键：
• Tab ———— 向右移动一格
• Enter———— 下一列重新开始
• Shift + Tab—— 向左移动一格（输入错误时可用于纠错）
• Shift + Enter—— 向上一格
• 特殊数据录入
• 时间：2022-11-2 或者 2022/11/2 （标准格式）
• Ctrl + ; 显示日期
• Ctrl + Shift + ; 显示时间
• 前者 + 空格 + 后者 / =now()：日期 & 时间 （now 函数的时间会随表格修改时间而变化）
• 百分比：
  

  S05-EXCEL数据录入的正确姿势 P6 - 11:40

  
• 分数：0 + 你需要输入的分数内容
  

  S05-EXCEL数据录入的正确姿势 P6 - 13:04

  
• 长数据：
  

  S05-EXCEL数据录入的正确姿势 P6 - 13:49

  
• 类似身份证（超过 15 位单元格数据精度）的可以先切换成文本格式
• ' + 长数据（英文单引号）

原位填充

• Ctrl + Enter
  

  S05-EXCEL数据录入的正确姿势 P6 - 17:08

  
• 选中范围，输入所需内容，原位填充后，整个选中区域均显示相同内容
• 按住 Ctrl 任意选择单元格

网络数据获取

• 自动更新数据
  

  S06-如何从网上获取数据？ P7 - 08:14

  
• 手动右击，选择 “刷新”
• 自动刷新，选择 “全部刷新”——“连接属性”

清洗数据

• Shift—— 可连选列
• 排序时，需先将该列的文本格式改成小数（点击 “abc” 进行选择），然后再进行排序
• 将原有表格转化为超级表：全选 & Ctrl + T
• 全选表格时，表头第一行不要选（弹幕提示）
  

  S07-我们该如何利用数据清洗处理... P8 - 12:04

  
• 拆分列
  

  S07-我们该如何利用数据清洗处理... P8 - 14:29

  
• 如果是分号或者逗号，需要注意其是中文还是英文格式
• 在 powe query 里操作可记录下每次的步骤（删除某个步骤，其他不受影响）
• 增加实付金额：“添加列”——“自定义列”
  

  S07-我们该如何利用数据清洗处理... P8 - 17:04

  
• 公式：=if [金额]＞1000 then [金额]*0.95 else [金额]
• 选择 “金额”，然后 “插入”
• 以上步骤全部完成后，选择 “关闭并上载至”

作业

单元格格式

• 文本（左对齐）：不可参与计算
• 数值（右对齐）：计算精度 15 位
• 逻辑值（居中）：既可判断，也可计算

• 清除格式：“开始”——“清除”

• 跨列居中（可避免无法使用筛选等功能）
• 自动换行
• 网格线：“页面布局”——“网格线”

• 单元格显示 “########”，是因为宽度不足
• 手动拖拽调整宽度
• “自动调整列宽”

• 错误指示器
  

  S08-如何用单元格格式给表格化个... P9 - 19:45

  
• 数字格式转化为文本格式
• 手动编辑： ' + 内容
• 先修改成文本格式，再编辑

• 自定义格式 —— 常用数字格式代码
  

  S08-如何用单元格格式给表格化个... P9 - 21:37

  
• Ctrl + 1 —— 单元格格式
• 数字占位符：0、#、？
  

  S09-如何用单元格格式给表格化个... P10 - 01:35

  

• 文本占位符：@
• 输入几次 @，文本就重复几次
• 新增内容加上 " " （规范使用）

• 0;-0;0;@ 对应 正数；负数；零值；文本
  

  S09-如何用单元格格式给表格化个... P10 - 05:15

  
• 先按照上述自定义格式，确定后再次打开后进行删除需隐藏的内容即可
• 可以通过增加 “正”、“负”、“上”、“下”，修改表现形式
  

  S09-如何用单元格格式给表格化个... P10 - 06:45

  
• 颜色也可以通过代码进行修改
  

  S09-如何用单元格格式给表格化个... P10 - 08:47

  
• e.g. [绿色] 0;[红色]-0;0;@
• 条件代码（只能进行最多三个条件，且第三个区域是除此之外作为条件）
• 低版本的 excel 需要将颜色代码放置于条件代码前
• 参考弹幕，此处可使用 if 函数（以后再学）
• 日期代码
• yyyy 2008
• yy 08
• mmm Sep
• mm 09
• m 9
• dd 01
• d 1
• aaaa 星期一
• aa 一
• dddd Monday
• 上午 / 下午 下午
• AM/PM PM
• a/p P
• h:m:s 9:15:06
• hh:mm:ss 09:15:06

作业

要求1 将[部门]一列的文本后添加“部门”二字，如：肉类部门

要求2 将标题中的两个日期，在不改变数值的情况下，只显示月份，不显示年和日，如：11月

要求3 将[11月][12月][营收增长]三列中的数值，设置为货币专用显示，显示0位小数

要求4 将[营收增长]一列中正增长的数值，标记为红色向上箭头，如：↑¥10000，负增长的数值标为绿色向下箭头，如：↓¥-50000，隐藏零值

数据验证

• 为避免无信息时产生误解，可 “输入信息”
  

  S10-利用数据验证规范单元格输入！ P11 - 03:30

  
• 序列
  

  S10-利用数据验证规范单元格输入！ P11 - 09:19

  
• 可以将有效性列表放置新表中以防他人更改
  

  S10-利用数据验证规范单元格输入！ P11 - 10:42

  
• 快速找出不符合要求的数值
  

  S10-利用数据验证规范单元格输入！ P11 - 12:04

  
• 复制粘贴时会使数据验证失效：可以 “选择性粘贴”——“粘贴验证”

条件格式

• 找出不重复值，可以使用 “条件格式”——“重复值” 进行设置

定位

• 快捷键：Ctrl + G
• 插入的图片 ——“对象”
• 删除单元格
  

  S12-定位，快速定位目标数值 P13 - 07:45

  
• 全选表格：Ctrl + A
• 处理合并单元格
  

  S12-定位，快速定位目标数值 P13 - 10:02

  

视图

• Ctrl + N：新建工作簿
• 切换原工作簿：Alt + Tab / Ctrl + Tap / “视图”——“切换窗口”
• Alt + Tab：其实是切换任务
• Ctrl + Tap：直接切换不同的工作簿
• 同时查看多个工作簿：“并排查看” （系统自带的分屏应该也可）
• 影子工作簿
  

  S13-Excel里的视图到底有啥用？ P14 - 04:41

  
• 工作表的新建或在多个工作表中选择其中一个：多注意最下方的加号、小三角
• 快速跳转至工作表最后一行：任意选择一个单元格，然后双击该单元格的下边框（其他边框同理）/ 按住 Ctrl + 方向键
• Ctrl + Shift + 方向键：连选
• 冻结窗格
  

  S13-Excel里的视图到底有啥用？ P14 - 13:17

  

表格打印

• 区域打印：选择范围 ——“打印选定区域”
• 整表打印
• “缩放”——“将所有调整为一页”（列数太多则不推荐）
• 居中打印：
• “打印”——“页面设置”——“页边距”——“居中方式”（两个全部勾选）
• 行号打印
• “页面设置”——“工作表”——“打印”——“行和列标题”
• 标题打印
• “页面布局”——“打印标题”（2019 及以上版本）

函数

• sum 函数：求和
• 工作表求和：全选表格 —— Alt + =
• 在参数面板输入中文字符，系统自动输入英文双引号
• 输入你需要的函数名后，Ctrl + A 可以调出参数面板
• 输入函数名，敲击 Tap，进入该函数

• 直接向下拖拽单元格右下角，列不变，行变（向右拖拽同理）
• $ ：锁定符号
• $K5—— 锁定行
• K$5—— 锁定列
• $K$5—— 行列均不变
• 自动添加 $ 快捷键：Fn + F4（笔记本）—— 结果显示 $K$5
• 弹幕提示，笔记本可在 bios 中取消 fn

横着拖拽，固定行，$放在行最前

竖着拖拽，固定列，$放在列中间

• 绝对引用：行列均锁定
• 相对引用

Count 函数

• 该函数可以给出数字格式的单元格个数

Counta 函数

• 非空值的单元格个数

Countblank 函数

• 空值的单元格个数

多个函数共同使用：

Countif 函数

• 小于号需要在英文状态下
• ＜在 excel 中默认为文本格式，因此不可与单元格直接连用，可加上连接符 &
• "<82" / "<"&G47

Countifs 函数

通配符（countif 中使用）

• * 多个字符
• * 张 —— 结尾为张
• 张 *—— 姓为张
• ? 一个字符 英文状态
• 张？—— 两个字，且姓张
• ??—— 两个字
• ~ 转义字符取消特殊性质
• ~*—— 搜索 *

第二个字为“十”——?十*

姓名中含“三”——*三*

Sum 函数

• 单个单元格与部分区域
• 优势：
• 避免拼写错误
• 避免因引用文本引起 #VALUE! 错误
• 避免因删除行列引起 #REF! 错误
• 避免因插入行列引起计算错误

Sumif 函数

• sum_range：需求和的实际单元格（可只写该区域的第一个单元格）

Sumifs 函数

Sumproduct 函数

Average 函数

sum、average函数不会对空值进行计算（0并不是空值）

查找替换

• 查找 = Ctrl + F
• 替换 = Ctrl + H

分列

• 快速填充：Ctrl + E
• 分列一次只能处理一列
• 一定要注意是否需要将没处理的列设置成文本
• 分隔符也可以是文本

• 利用分列进行格式转换

筛选排序

• 如想筛选出姓名中包含 “壹” 或 “周” 的
• 先进行一次筛选 “壹”
• 再进行 “周”，然后勾选 “将当前所选内容添加到筛选器”

• rank 函数可排名

Vlookup 函数

• 0—— 精确匹配
• 1—— 模糊匹配
• 查找的内容在选择表格区域时，需放在该区域的首列
  

  S22-人人都说好的Vlookup函数，... P23 - 04:34

  
• 输入的 “2” 是指需返回的内容在选择的表格里排第二列
• 如果需要向下拖拽，则要将选择的表格区域锁定住

• 成绩一定是升序排列，否则报错

• 可以使用 match、Index
• 使用 vlookup 时，查找内容不在目标区域的首列，需要添加固定搭配为了调换列的顺序

IF({1,0},D56:D65,C56:C65)

• 此时返回的列与表格显示的不同