本文是哔站视频笔记，指路：【EXCEL教程 | 拜托三连了！全B站最用心（没有之一）的EXCEL免费课程！OFFICE/WPS/表格/EXCEL函数/EXCEL技巧/数据分析/办公软件-哔哩哔哩】

输入第一个格子，然后：

1. 下拉单元格右下角的填充柄并设置填充规律，下面的栏目会自动按规律填充（如电子邮件号码右侧填收件人姓名）

【小提示：使用快速填充功能，实际上是从前面的栏中提取或合并信息，左边栏如果没有信息那可能提取不了】

选中图表，点击右下角快速分析图标或Ctrl+Q

1. 格式化：数据条，色阶，图标集，大于，文本包含，清除格式。（看不懂符号代表的意思→设计→条件格式→管理规则→编辑格式规则）
2. 汇总：求和，平均值，计数，汇总百分比，汇总。
3. 表格：表格（将普通表格转化为超级表），数据透视表（后面单独讲）
4. 迷你图：折线图，柱形图，盈亏。

1. 快捷录入方式：①输入第一行第一格→Tap键（向右跳一格）→输到最后一格→ Enter键（回到第二行第一格）②如果发现前面有数据输错了→ Tap+Shift（向左回一格）③其他快捷方式：Enter+Shift（向上跳一格）
2. 特殊格式数据录入方式：

• 时间：①使用/和-输入，方便日期筛选（如）②快速输入当天日期： Ctrl+；③快速输入此刻时间： Ctrl+Shift+；④快速输入此时日期与时间：输入函数NOW再回车
• 百分比：将单元格格式改为百分比，再输入数字
• 分数：①0+空格+1/5→分数格式的1/5②将单元格格式改为分数，再输入小数。
• 长数据：①将单元格格式改为文本格式，再输入数据②输入完长数据后，在数据最前方添加一个英文单引号，会自动将数据转化为文本格式（比如输入十八位身份证）

【小技巧：原位填充（ Ctrl+Enter），选择多个单元格，在其中一个输入数据，再利用这个键，能够在多个单元格输入同样的数据】

1. 获取静态数据：①复制粘贴，或②数据→［新建查询（16版）/获取数据（19版）→自其他源］→自网站→输入网址→确定、连接→导航器（表视图、web视图）→加载（默认加载到新的工作表里）/加载到（自己选择工作表）【13版本有点不同：数据→自网站→输入网址→点网上数据表格左上角的小箭头可全选数据→点右下角导入→选择导入位置】
2. 获取动态数据：重复以上操作→①19版：数据→全部刷新→连接属性→刷新频率（打勾并设置频率）②16版：数据→连接→连接属性→刷新频率

【小提示：Excel只能爬取表格化的数据，非结构化数据是很难爬取的】

在爬取网上数据之后，利用Power Query清洗数据【16及以上版本自带查询工具，13版本需要额外下载插件】：

1. 通过查询工具处理：点击表格→查询工具（ Power Query）→编辑→编辑器→修改表格每列标题名称、删除行或列等等

• 检查：检查原表中是否存在数据缺失异常值或不规范的情况
• 团队协商：和业务团队确认是否有收集信息的疏漏
• 清洗数据：将普通表转化为超级表（ Ctrl+T）数据→来自表格或区域→ Power Query编辑器→改标题、筛选、删除或添加行或列、改单元格格式、拆分等【在应用的步骤中，可以随时删除某个步骤，而不会影响其他步骤】

• 文本：默认左对齐，文本不限制长度，但不可参与计算
• 数值：默认右对齐，数值的计算精度是十五位
• 逻辑值：默认居中对齐，True和False既能用来判断也能参与计算

• 对齐：①居中：合并居中，无法选中合并后的小单元格；跨列居中，可以选中合并后的小单元格【选中想要居中的范围→开始→对齐方式右下角展开→设置单元格格式→对齐→水平对齐→跨列居中】。②自动换行：开始→对齐方式→自动换行
• 边框：开始→任意一个组下面的启动器→设置单元格格式→边框【区别边框与网格线，通过页面布局中的“查看网格线”可以隐藏工作表中的网格线，但不会隐藏边框】

时间、分数、百分比、数字、文本，在前面数据输入部分讲过，因此省略。这里重点讲一下自定义数字格式。

自定义数字格式：设置单元格格式→自定义→输入数字格式代码。

（1）数字或文本格式代码：

• 三个常见的数值占位符（0、#、?）：①0.0（小数位数多于0的数量自动四舍五入至0的数位）、②000.000（当数字比代码的数量少时，显示无意义的0）③###.###（#作为数字占位符，只显示有意义的数字）④???.???（?以空格替代0，可以用于小数点对齐及分数显示）
• 文本占位符（@）:可以在英文引号中加入文本，在文本前后增加数值

• 利用数字格式四个区域的定义对各区内容独立定义（正数；负数；零值；文本，即0;-0;0;@）：①在分号之间依次输入每个单元格对应的占位符，删掉某个占位符，对应的单元格内容会被隐藏。②占位符前后可以输入文本以自定义内容，也可以在删除占位符后替换成其他显示内容
• 可以使用颜色代码使得文字显示不同的颜色：①颜色代码必须在代码部分中的第一个项目②颜色的两边必须加上英文方括号③可供选择的颜色代码有：[黑色][白色][红色][绿色][蓝色][黄色][洋红色]等

• 条件组成比较运算符和值（<=>）：在实际代码应用中，只能在前两个区域中使用">""<""="等条件代码，第三个区域以除此外作为条件值，即[条件1];[条件2];[其他]

通过改变代码的数量和顺序改变日期的形式方式：

• 上午下午 → AM/PM【①上午/下午（下午）②AM/PM（PM）③a/p（p）】

【小技巧：打开设置单元格格式的快捷键→ Ctrl+1】

1. 数据验证的基本设置：

• 设置：选中一个单元格→数据→数据验证（设置允许、数据、最小值、最大值等）
• 输入信息：数据验证→输入信息→勾选选定单元格时显示输入信息、设置选定单元格时显示的信息
• 出错警告：数据验证→出错警告→勾选输入无效数据时显示出错警告、设置警告样式和信息

【小提示：警告的三种样式有所不同，停止（输错后清空数据），警告和信息（输错后不清空数据）】

• 选中单元格→数据→数据验证→在“允许”中选择“序列”→在“来源”中输入A,B,C（必须用英文逗号）

【小技巧：也可以将有效性列表放在另一个工作表中，然后在“来源”中选择对应工作表的区域】

• 在数据验证中设置完了最大值最小值等后→数据→点击数据验证下的三角展开→圈释无效数据

【小提示：数据验证只有在我们输入数据时起验证效果，但如果我们将没有数据验证格式的单元格复制粘贴到此处，它就无效了；但反过来说，数据验证格式也能够选择性粘贴到别处】

选中单元格区域→开始→样式→条件格式

1. 突出显示单元格规则：

• 设置大于、小于、介于、等于、文本包含、发生日期、重复值以及突显颜色等
• 若想修改当前规则→条件格式→管理规则→条件格式规则管理器→编辑规则
• 若想清除当前规则→条件格式→清楚规则→清除所选单元格的规则

【小技巧：快速找出重复值或不重复值→选择需要的区域→条件格式→重复值（可选择重复或唯一）】

2. 最前／最后规则：（略）

• 数据条越长，表示值越大；数据条越短，表示值越小
• 条件格式→数据条→选择渐变填充或实心填充
• 修改数据条颜色→条件格式→管理规则→编辑规则→条形图外观（设置填充、边框和颜色）

【小提示：如果想要设置负值坐标轴的条形图外观→编辑规则→负值和坐标轴（设置负值条形图的填充颜色、边框和坐标轴）】

• 开始→查找和选择→转到→定位条件→对象→框选删除
• 开始→查找与选择→选择对象→框选删除

2. 标记特殊单元格：

• 开始→查找与选择→定位条件（批注、常量、公式、空值、对象）→标记颜色

3. 复制粘贴非隐藏单元格：

• 选择需要复制的单元格→开始→查找与选择→定位条件→可见单元格→复制粘贴

• 选中需要的表格→开始→查找与选择→定位条件→空值→删除工作表行

5. 批量取消并填充合并单元格：

• 选中合并的单元格→开始→对齐方式→合并后居中→取消单元格合并
• 开始→查找与选择→定位条件→空值→在选中的一个单元格中输入"="，并选择第一个取向合并后有内容的单元格（可使用 Enter和Shift+Enter调整所在位置）→Ctrl+Enter原位填充

• 新建工作簿：①文件→新建空白工作簿，或②Ctrl+N
• 修改新建工作簿格式：文件→选项→修改新建工作簿时的字体、字号、默认视图（普通视图、分页预览、页面视图）以及包含的工作表数
• 工作簿视图：①视图→切换窗口②视图→并排查看→全部重排（平铺、水平并排、垂直并排、层叠）③视图→新建窗口→出现一个影子工作簿（可并排查看相同工作簿的不同工作表，在一个工作簿中做出的改动会同步在另一个做工作簿中）

• 新建工作表：①按下方+号，或②Ctrl+F1
• 多个工作表同时操作：按住Ctrl键连选几个工作表，在其中一个工作表中输入内容，敲击回车，其他工作表会出现同样的内容。
• 移动工作表内容：选中相应区域右击→移动或复制（如果要复制记得勾选建立副本）

3. 单元格选择技巧：

• 快速选择多个单元格：①Ctrl点选， Shift连选②选择一个单元格，再按 Ctrl加Shift加↑箭头，可以连选一列（上下左右箭头同理）
• 在表格中快速定位：①选中表格中的任意一个单元格，双击单元格的上边框，会回到表格最顶端；而双击单元格的下边框，会回到表格最底部；双击单元格的左边框，会回到表格最左边；双击单元格的右边框，会回到表格最右边②Ctrl加上下左右同理

【小技巧：查看表格时可以使用冻结窗格，使得滚动工作表其余部分时，保持行和列可见】

十一、打印的五个小技巧

1. 区域打印：选定需要打印的区域→文件→打印→打印活动工作表→打印选定区域
2. 整表打印：选中整个表格→文件→打印→发现表格不在同一页→无缩放→将所有列调整为一页（但列数多出太多不建议使用）
3. 居中打印：选中需要打印的区域→文件→打印→页面设置→页边距→居中方式（水平、垂直）
4. 行号打印：选中需要打印的区域→文件→打印→页面设置→工作表→行和列标题
5. 标题打印：页面布局→页面设置→工作表→打印标题→添加顶端标题行区域→选中整个表格→文件→打印

（一）函数是公式的升级版

（二）一些函数入门技巧

1. 选中需要显示计算结果的单元格→开始→自动求和→选择需要的常用函数→还可以点击其他函数→在搜索函数中输入简短的说明→转到（ Excel会自动为你推荐能够满足条件的函数）【我们也能够在公式选项卡上面找到对应的函数】
2. 若我们想对表格内所有内容求和（底部和右侧），可以选中整个表格，然后按Alt加=，所有结果能够一瞬间出来
3. 我们可以直接在单元格中输入等于加函数名称，输入过程中会弹出相关函数，可以用上下左右键选择需要的函数，然后再按tab键进入函数输入界面
4. 若对输入的函数不够了解，找到左上角的fx，进入函数参数界面，根据参数的介绍输入参数

（三）绝对引用与相对引用

1. 绝对引用：K4→$K$4（保持列和行不变）
2. 相对引用：K4（行列内容会随着引用的区域而规律变化）
3. 混合引用：K4→$K4（保持列不变）；K4→K$4（保持行不变）

【小技巧：选中需求区域，用好F4快捷键：F4一次→锁定行列，F4两次→只锁定行，F4三次→只锁定列】【笔记本电脑要Fn加F4】

【小提示：利用九九乘法表更易于理解】

十三、 Count系列统计函数

（一）count函数：

返回包含数字以及包含参数列表中的数字的单元格的个数，可以计算单元格区域或数字数组中数字字段（文本格式不能统计）的输入项个数。（统计数字个数）

返回参数列表中非空值的单元格个数，可以计算单元格区域或数组中包含数据的单元格个数。（统计非空个数）

返回参数列表中空值的单元格个数，可以计算单元格区域或数组中包含空格的单元格个数。（统计空单元格数）

• range：指需要计算其中满足条件的单元格数目的单元格区域。
• criteria：指确定哪些单元格将被计算在内的条件，其形式可为数字、表达式或文本。（例如，条件可以表示为32、"32"、">32"或" apples"）

• countif_range1：指需要计算其中满足条件的单元格数目的单元格区域。
• criteria1：指确定哪些单元格将被计算在内的条件，其形式可以为数字、表达式或文本。
• countif_range2：指需要计算其中满足条件的单元格数目的单元格区域。
• criteria2：指确定哪些单元格将被计算在内的条件，其形式可以为数字、表达式或文本。

• *：代表任意多个字符。（例：*张，意思是以张结尾的单元格）
• ?：代表一个字符。（例：张?，意思是姓张并且姓名为两个字的人）
• ~：代表转义字符。（例：~*，意思是需要搜索的是带星号的单元格，而不是任意字符的单元格）

十四、sum系列求和函数

• number：指求和的数值，可以将单个值、单元格引用或是区域相加，或者将三者的组合相加
• 使用sum函数而非直接相加求和的好处：避免拼写错误、避免因引用文本引起#VALUE!错误、避免因删除行列引起#REF!错误、避免因插入行列引起计算错误

【小技巧：区域快速求和： Ctrl+A（选中整个区域）→ Alt+=】

• number1：指要计算平均值的第一个数字、单元格引用或单元格区域
• number2：指要计算平均值的其他数字、单元格引用或单元格区域，最多可包含255个

【小提示： sum和average函数不会对空值和文本信息进行计算】

• range：只用于条件判断的单元格区域
• criteria：指确定哪些单元格将被相加求和的条件，其形式可以为数字表达式或文本（使用方法同countif）
• sum_range：只需要求和的实际单元格（可只写该区域的第一个单元格）

• sum_range：指用于条件判断的单元格区域
• criteria：指确定哪些单元格将被相加求和的条件，其形式可以为数字、表达式或文本（使用方法同countifs）
• range1：指需要求和的实际单元格（可只写该区域的第一个单元格)

• array1：指其相应元素需要进行相乘并求和的第一个数组参数

• 区域查找：选中一个区域，然后对该区域进行查找替换（开始→编辑→查找与选择→查找；快捷键）
• 更多条件：选项→范围（工作表、工作簿）；搜索（按行、按列）；查找范围（公式、值、注释、批注）；区分大小写；单元格匹配（打上勾之后，单元格内容完全匹配才能被找出）；区分全/半角；格式（格式、从单元格选择格式）
• 对查找区域内容求和：查找结束后，选中框中所有查找的内容，大页面右下角能看到求和结果；我们也可以在查找内容被选中后，在页面右上角修改所有查找内容的单元格名称，然后用sum(名称)求和

选择需要替换的区域，与查找操作类似。

【小技巧：可结合通配符进行查找替换】

• 选择需要分列的单元格→数据→数据工具→分列→文本分列向导→分隔符号或固定宽度→下一步→选择分隔符号或设置分列线→下一步→设置各列数据格式以及目标区域

• 分隔符不只是特殊符号，也可以是文本内容
• 分列功能可以把文本数值转化为数值
• 分列功能可以规范不标准日期

• 笔画排序：选择区域→排序→自定义排序→选项（区分大小写、按列排序、按行排序、字母排序、笔划排序）
• 自定义次序排序：选择区域→排序→自定义排序→次序→自定义序列→输入并添加序列（一个条件换一行）

• 选择区域→筛选→按颜色筛选（等于、不等于、开头是、结尾是、包含、不包含、自定义筛选），数字筛选（等于、不等于、大于、小于、介于、前10项、高于平均值、低于平均值、自定义筛选）

• 筛选再填充排名：对所需排序的分数进行升降序，再填1，然后填充序列（下拉填充；按单元格边缘+ctrl）
• Rank函数排名：Number（要进行排名的数字），Ref（需要进行排名的区域，如需要拖拽填充则需要绝对引用），Order（如果为0或忽略则是降序，非零值则是升序）

• lookup_value：指需要在数据表首列进行搜索的值，可以是数值、引用或字符串
• table_array：只要在其中搜索数据的文字、数字或逻辑值表，可以是对区域或区域名称的引用
• col_index_num：指返回匹配的序列号，表中首个直列的序号为1

2. 精确匹配、模糊匹配、反向查找，在实例中学习运用

Excel数据的输入 2.2 公式与函数 2.3 数组公式 2.4 创建图表 2.5数据分析与管理;2.1 Excel数据的输入与获取; 设置数据验证，是指建立一定的规则来限制单元格中输入数据的类型和范围，以提高单元格数据输入的速度和准确性。 1. 禁止输入重复数据 2. 将数据输入限制为下拉列表中的值 3. 圈释无效数据 ;1.禁止输入重复数据 禁止输入重复数据的操作步骤如下： （1）选择禁止输入重复数据的区域，例如B2:B60 （2）在“数据”选项卡下“数据工具”组中，单击“数据验证”命令，打开“数据验证”对话框，在“允许”下拉列表框中选择“自定义”选项，在“公式”文本框中输入公式，例如“=COUNTIF($B$2:$B$60,B2)=1” 。 ;120;（3）选择“出错警告”选项卡，在“标题”文本框中输入“错误提示”，在“错误信息”文本区中输入“数据重复” 。 ;2.1.1 数据验证;2. 将数据输入限制为下拉列表中的值 （1）选择输入有限选项数据的区域 （2）在“数据”选项卡下“数据工具”组中，单击“数据验证”命令，打开“数据有效性”对话框，在“允许”下拉列表框中选择“序列”选项，在“来源”文本框中输入序列，例如,“教授,副教授,讲师,助教” 。 ;124;（3）单击“确定”按钮，关闭“数据验证”对话框。返回工作表中，当在指定区域内任一单元格输入数据时，单元格右边显示一个下拉箭头，单击此下拉箭头，则弹出下拉选项，在其中选择一个值填入即可。 ;3. 圈释无效数据 （1）选择