解锁 Excel 无限可能！从基础到实例全掌握一.Excel Excel是对数据进行一些系列操作处理的工具，学习Exce

一.Excel

Excel是对数据进行一些系列操作处理的工具，学习Excel就是学习如何高效处理数据。

二.推荐版本

⑴推荐版本：excle 2013以上的版本

⑵查看版本：文件☞账户☞查看版本

⑶启动xecel的五种方式

1.开始菜单栏☞Excel文件☞打开

2.开始菜单栏☞Excel☞鼠标右键（找到更多☞固定到任务栏）

3.把Excel图标放在桌面上☞开始☞右键找到更多☞打开文件位置☞找到Excel直接右键☞找到桌面快捷方式

4.在桌面上找到Excel图标☞鼠标右键找到属性☞直接弹出一个快捷方式☞设置打开快捷键

5.Ctrl➕R（运行加打开）

6.Ctrl➕O

⑷定制属于自己的Excel界面

快速访问工具栏

功能区可以进行开发工具

鼠标右键单击任意一个菜单栏☞自定义功能区☞就可以开发工具

ctrl + F1 :快速折叠或显示工作区

⑸保存Excel的方式

F12 ：保存/另存为

Ctrl➕shift➕S

三. 填充，批量提取与组合数据

3.1 快速填充（Ctrl➕E）

快捷键：ctrl+E
开始里找到填充再在填充里找到快速填充。
单元格下出现黑十字标为填充柄，可以双击或下拉即可填充内容一样的单元格，也可以点填充后的自动填充选项中的快速填充。

作用：对数据进行拆分重组合并

重组数据：李达康+书记=李书记（对其他数据快速填充可以得到同样格式的数据）
拆分数据：罗佳1531=罗佳+1531

注意：填充位置需要和表格其他部分相连

快捷键

选中一列/一行：crtl+shift+方向键，该行/列到达某一方向边界的所有区域。
同时选中几个单元格：按住ctrl（mac按command）
Ctrl+R向右填充、Ctrl+D向下填充，Ctrl+E快速填充。

3.2 快速填充数据

开始☞填充☞快速填充（注意附近一个单元格，是一定要有数据的，不能出现单独一列，完全空白，即使是说颜色一致没有内容，一样识别不出来）

1.Ctrl➕E，快速填充，帮你合并拆分内容。

2.快速填充内容不合意？那就多输入几个内容

3.想用快速填充，附近一定要有数据

3.3 小结

四. 快速分析，EXCEL中超实用的可视化功能

4.1 快速分析方式：

i. 想要快速分析数据？选中目标区域后，直接Ctrl+Q（Ctrl + q :快速分析工具，选中区域后可以快速进行数据分析，如格式化，汇总，图表，表格，迷你图）。

ii. 选择全表->右下角小图标

4.2 快速分析功能

i. 格式化：也可通过【开始->条件格式】进行调整；

**条件格式：**进行数据可视化（数据条/色阶/图标）；可表现数据大小，变化规律等；条件格式-管理规则：可管理标记规则。

ii. 图表

iii. 汇总：求和、平均数、汇总、计数等；

iv. 表格：快速制作表格、制作数据透视表等；

**分析数据：**制作数据相关各类图表

v. 迷你图：表现趋势、对比、盈亏等，有折线图、柱形图、盈亏图。

**迷你图/图表：**选中全部数据-插入-图表/迷你图

4.3 小结

五. 数据录入

5.1 录入快捷键

Tab键 ：跳到下一个单元格（光标往右移动一个单元格）

enter键：回车键

可以先按tab键横向输入，按回车键enter回到第二行第一个单元格 (可以使得录入最后一个格后从下一行第一格开始输入数据；)

录入时推荐横着录入，用tab和enter键相互配合

shift➕tab键：回到上一个单元格 (往左横向录入表格，如果填写错误可以往右修改)

Shift+Enter：往上竖向录入表格；

快速录入数据：按tab键会向右移动一个单元格，按tab键连续填完一横排数据后再按enter键就可以转到下一横排的第一个单元格，单元格输错内容时可以按住shift + tab键就能回到上一个单元格（好处是再按enter键就可以跳转到下一横排最初的单元格），按住shift键和tab键则光标会向上移动一个单元格。

5.2 特殊内容录入方式

特殊格式数据（原位填充：Ctrl➕enter）

5.2.1 时间

录入时间：（如日期，时间）一般用短横线和斜线-，/

正确录入方式是：2021/12/16或者2021-12-16，这样会被EXCEL识别认定为日期，方便筛选查找；

如果一列中有日期，可以使用excel筛选日期
可以自动更改日期规范（全部改成年月日e.g.）。开始-数字-日期（长日期、短日期）。

**快捷键：**Ctrl➕；（分号）快速输入当前日期
Ctrl➕shift➕；快速输入当前时间

【ctrl+；+{空格} +ctrl+shift+；】快速输入当前日期与时间：先ctrl + ; 再点空格键再点ctrl + shift + ; :则可以显示当日日期空一格再显示当前时间。

【函数：=now（）】可以直接输入当前日期与时间

也可以直接输入函数： =Now（函数）再点击tab键和enter键也可以显示当前日期和时间。

5.2.2 百分比

录入百分比：【开始->数字->百分比】

将常规格式修改为所需格式，录入数据将自动调整格式
选项下方可以选择有效小数数位
单元格格式可以将一整列的数据改成%格式，点数据格式中的%则默认保留两位小数，也可以点数据格式下面的左右箭头来增加后减少%后保留的小数。

5.2.3 录入分数

先输入一个0再按一下空格然后再输入分数（几分之几之类的）

直接输入【1/5】会被默认为日期，在之前＋“0”以及空格【0+{空格}+1/5】便可输入分数；
【开始->数字->分数】

5.2.4 录入长数据（如身份证录入）

Excel中的数据精度是15位，超过15位则会显示科学计数法，在数据格式中找到文本，再点击，则可以输入18位的身份证号码，此时单元格的上方会出现小三角的符号，表示该单元格的数据为文本格式（但文本格式无法进行计算）
在一个单元格输入完身份证号码后，在该单元格的最前面输入英文状态下的’（逗号）也可以转化为文本格式（'123456789817 e.g.）

一个单元格：数字只能输入最长15位

想要输入18位（15个数字以上）把单元格调为文本格式

开始☞数字☞文本

输入完之后，忘记加文本格式（可以在最前面的那个数字加一个，英文模式下添加顿号或者撇号）也是和上面一样的

① 【开始->数字->文本】 ② 在第一个数字前＋英文“引号{‘}”

5.2.5 原位填充（停留在输入的单元格上）

Ctrl + enter :原位填充，可以在全选状态下将一个单元格的内容填充到全选的单元格（选中任意区域也可以进行填充）。

先Ctrl➕A全选（框出自己想要的部分空白单元格，在其中一个单元格中输入文字）然后再按Ctrl加enter键填充 \也可以选中任意的多个单元格，按Ctrl➕enter进行填充

5.3 小结

六. 如何从网上获取数据

6.1 如何从网上抓取数据

数据☞获取数据☞自网站

Excel中点击数据下面的自网站可以输入网址再将网址中的数据或表格导入到Excel中（点加载则默认导入到当前工作表，点加载到可以选择导入表的位置）
如果自网站导入数据后需要刷新数据，可以手动点击数据或表设计中的刷新，也可以在表设计的下拉栏中点击连接属性即可以设置数据刷新的时间。

输入网址，即可选择你想要抓去的数据

6.2 抓取的数据如何按时更新

①数据☞全部刷新☞连接属性☞刷新🔄频率

②抓取数据时要定时更新，需要找到刷新☞链接属性，设置刷新频率

③数据抓去需要网页本身自带表格，无表格则需要用爬虫工具或更深入知识

从网站中获取数据后将表导入Excel后，点击Excel中的查询中的编辑，再双击表的名字就可以修改表的名字

6.3 小结

七. 如何利用数据清洗处理数据

在处理数据前，还需要处理什么步骤？

数据清洗，帮你清除异常值

Ctrl➕T：创建超级表，再新建查询。可以将表格转化为超级表（如果要在Excel中对表格进行数据查询，则需要先将表格转化为超级表，转化为超级表后首选项会多出一个表设计选项）。

**创建超级表：**全选表格 - ctrl+T

我们怎么回到最初的表格呢，点击这个"设计"，工具组下方有一个转化为区域。

将超级表转化为普通表则只需要点击表设计的下方的转化为区域就可以将超级表转化为普通表了（即转化为普通区域）
全选表格后点击插入选项卡中的表格也可以将普通表格转化为超级表。
将表格转化为超级表后点击数据中的自区域，其中有拆分列，点击后可以将选中的单元格根据拆分符分割。
也可以在超级表的自区域中的添加列中填写编码来改变数据。

power query 修改数据，既能记住步骤，还能避免伤害原始数据。

使用power query，数据选项卡-来自表格区域。

筛选异常数据

手动按列筛选（可设置筛选条件）
或对超级表使用power query，然后进行数据筛选（power query可以记录并调整筛选步骤）

7.1 小结

Ctrl➕T创建超级表，再新建查询。
power query 修改数据，既能记住步骤，还能避免伤害原始数据。

八. 单元格格式

8.1 单元格内容

在单元格内填充的内容，可分为三种（开始-数字）

Excel在默认状态下输入文本内容的单元格是左对齐的，数值在默认状态下为右对齐，逻辑值则是默认居中对齐

文本（默认左对齐，文本不可参与计算），文本它是不限制长度的。
数值（默认右对齐，数字计算精度是15位）如果超出15位数字，15位以后的数字都默认为0
逻辑值（默认居中对齐，true和false既能用来判断也能用来参与计算）。
如何清除格式。清除内容，全选整个表格，然后直接按Delete删除；但是这样的操作，并不能清除我们的格式；删除内容+格式：开始-编辑-清除（清除内容、清除格式、全部清除）。
一般数值都是纯数字，逻辑值就是true和false，除了数值和逻辑值之外其它都是文本值。

8.2 单元格格式

在“开始”选项卡的功能组中，单击“对齐方式”旁的对话框启动器。或者，你可以在单元格区域右键-设置单元格格式。快捷键是Ctrl+1

8.2.1 常规格式（重点在外在效果）

指：开始☞字体组、对齐方式组、数字组
单元格区域右键设置单元格
设置单元格格式**（包括：对齐『自动换行、跨列居中〗，字体，填充，边框，保护，数字）**
对齐下面的跨列居中。不推荐使用合并居中，你將无法精确定位到另一个单元格；**如果在首行，将无法进行筛选/透视；**清除单元格的现有格式，还是先选中想要进行居中的范围，水平对齐下就有一个跨列居中，增加一个颜色以及加粗。首行合并后居中，筛选不出数据；使用跨列居中后，每个单元格的格式和数值不变，居中后的文字任然在原单元格。

在开始中点击对齐方式下的小箭头就可以跨列居中。

**自动换行。**对齐方式上面的自动换行，可以在开始下面点击自动换行后再点击格式中的自动调整行高（适用于单元格中的内容过长而被隐藏起来的，将隐藏内容显示出来）。单元格太窄可以直接拖拽。
**边框。**改变边框颜色可以点击开始样式下的小三角，再点击边框，然后改变颜色，点击外边框+内部即可。开始-边框-其它边框（ctrl+1），可以对边框进行详细编辑。

给课程表的表头做斜线:右键单击单元格-单元格设置-边框-设斜线，内容按两行输入Alt+回车，文字可以左对齐或右对齐。
**给课程表的表头做斜线需要2条斜线：**插入-形状-选右斜线-在单元格内手动划斜线-插入-文本框（依次拉多个文本框输入文字）-按Shift依次选中所有文本框-形状填充（无填充颜色）-开关轮廓（无轮廓）【选择无填充、无轮廓边框就看不到了】-最后按住Shift依次选中所有文本框-格式-排列-组合。

网格线。点击页面布局中的网格线中的查看即可显示或隐藏网格线，也可以点击打印去选择是否打印网格线（网格线默认不打印）。
移动。

交换两列顺序：选中前面一列（单行/列、多行/列），然后把鼠标放在边框线上，按住Shift键拖拽。
双击单元格上下左右边线，直接到达工作表的最上下左右方向。
选择Ctrl+Shift+上下左右箭头，该行/列到达某一方向边界的所有区域。

批注

首次使用必须添加更改形状按钮。
插入-形状-矩形-格式-编辑形状-更改形状-右-添加到快速访问工具栏。
更改批注形状的方法：编辑批注-选中边框-快速访问工具栏上更改形状按钮-改成云-把黄色的点拉到箭头上。
使用批注：右键单元格-插入批注（显示/隐藏批注）。

8.2.2 数字格式（重点在展示方式）

8.2.2.1 打开

在“开始”选项卡功能组中，单击数字旁对话框启动器。
在单元格区域右键☞设置单元格格式
快捷键Ctrl➕1

8.2.2.2 注意事项：

单元格宽度

单元格中显示#号代表单元格的宽度不够，需要拉宽单元格才会显示出原本的内容**（或者当鼠标移动到两个单元格之间双击也会自动调整到显示数据的宽度）。**
调整单元格宽度：选择需要宽度/长度的区域，在标题栏可以整体调整区域单元格的宽度/长度。
手动拖拽调整宽度/“自动调整列宽”。

错误指示器

单元格上的绿色小三角的名称是错误指示器（点击后显示错误原因）。
数字格式转化为文本格式：手动编辑： ' + 内容；先修改成文本格式，再编辑。

8.2.2.3 格式

**分数格式：**可以在分数前输入0+空格，避免被识别为日期（0 1/2 ）
**文本格式：**在数据前加入英文单引号，数据将自动变为文本格式（'123456789817）
**特殊格式：**选择特殊格式（120=一百二十）
**自定义格式：**可以自定格式（120=120元）

举例：

1、把日期转化为星期几，周几: 选择区域-右键-设置单元格格式-数字-自定义-aaaa（周aaa）

自定义-aaaa：将日期转换为星期几
自定义-周aaa：将日期转换为周几
同理日期2013/3/16，在自定义中写成yyyy-mm-dd aaaa，就变成了2013-03-16 星期六。

2、文本型的日期通过分列直接变成日期格式：选中单元格-数据-分列-固定宽度-日期-完成

3、可以给数字加上单位（例如：元）不影响运算

选择区域-右键-设置单元格格式-数字-自定义-0（0.00）单位

8.2.3 自定义格式

8.2.3.1 常见数值占位符

在Excel的开始的数据中点击自定义即可设置数据的单元格格式（如保留1位或2位小数）
数值类占位符。Excel中的0，#，？为常见的数据占位符，在开始的数据的自定义中打开，而@则为文本占位符，也在数据的自定义中打开。

**0：**0的效果是强制的站位符号。更改有效小数设置，可以将通用格式改为0.000或000.000等
**###.###：**只显示有意义的数字（如.825）
**???.???：**以空格代替无效0，可用于小数点对齐（如120. ， 6.25）

8.2.3.2 文本占位符

而@则为文本占位符（按住shift+2），也在数据的自定义中打开。

**@@@：**重复文本内容（张三张三张三e.g.）
@"同学"，"Hey"@：追加文本内容，注意使用英文双引号（张三同学，Hey张三 e.g.）

8.2.4 格式区域

0;-0;0;@ 分别对应：正数，负数，零值，文本

0代表数字本身，-0将修改数字为负数

隐藏内容：

在自定义格式中分别输入定义，再删除想要隐藏的定义类型（;-0;0;@会隐藏正数）

定义区域格式：

自定义格式中输入 "正" 0;"负" 0;"零";@（显示：正10，负6，零，Alice）

定义颜色：

在内容前加上[颜色]，如[红色]0;[绿色]-0;0;@

8.2.5 条件运算

使用运算符：

[绿色] [>=80]0;[>=60]0;[红色]0，则满足条件的自动使用目标颜色

日期格式

原格式：yyyy/mm/dd

其他格式：见下图

8.3 自定义序列

创建自定义序列。

第一种方法：
- 设置。文件——选项——高级——常规——编辑自定义列表。
- 添加/导入序列。
  - 添加序列。输入序列（手动添加序列内容）——添加——确定。
  - 导入序列。从单元格中导入序列——导入——确定。
- 确认设置。点击 “确定” 关闭 “自定义序列” 对话框，再点击 “确定” 关闭 “Excel 选项” 对话框。
第二种方法（推荐）：

参见下方数据验证的自定义序列。
使用自定义序列自动填充
- 在工作表中选中一个单元格，输入自定义序列中的某一项。
- 将鼠标指针移至该单元格右下角，待指针变为黑色十字（填充柄）时，按住鼠标左键向下拖动，即可按照自定义序列自动填充后续内容。

九. 数据验证

位置：数据——数据工具——数据验证

点中单元格再点击数据中的数据验证就可以设置单元格中要输入的数据类型及范围。

9.1 数据验证

1. 基础

限制数据类型或值域：数据-数据验证-设置
选定单元格时显示提示：数据验证-输入信息

点中单元格再点击数据中的数据验证中的输入信息，则可以输入提示信息，在点击单元格就会显示提示信息（如：请输入1-10之间的数据）

输入无效数据后设置出错警告：数据验证-出错警告（可以选择：停止/警告/信息）

点中单元格再点击数据中的数据验证中的出错警告，可以更改为停止，警告和信息模式，其程度依次降低。

❗️粘贴数据时会使数据验证失效，需要选择仅粘贴数据，或选择性粘贴中选择验证

复制后直接粘贴到单元格则数据验证的格式会消失，无法进行数据验证。

2. 实例

**举例1：**设置A列仅能输入500至1000之间的整数。

选A列，数据-数据验证，允许（整数），数据（介于），最小值500，最大值1000。

举例2：设置B列仅能输入字符长度为8位的产品编码。

选A列，数据-数据验证，允许（文本长度），数据（介于），最小值1，最大值8。

举例3：设置C列付款方式中仅能输入现金、转账、支票。

选A列，数据-数据验证，允许（序列），来源（现金,转账,支票）

举例4：禁止一个区域写入任何内容

选中区域，数据-数据验证，自定义，公式0

举例5：禁止输入重复数据

选中区域，数据-数据验证，自定义，公式

例如：=countif(C:C,C1)<2

9.2 自定义序列（下拉列表）

先选择需要下拉列表的行或列/选中需要验证的单元格。

数据—数据验证-设置-允许-序列，范围选择其他列表/手动输入。
输入选项：在 “来源” 框中输入下拉选项的值，用英文逗号隔开，如 “男，女”。也可点击右侧表格图标，从工作表中选择包含下拉选项的单元格区域。
手动输入需要英文逗号（肉类,蔬菜,水果 e.g.）【注意是英文逗号】。
确定：点击 “确定” 完成设置，选中单元格会出现下拉箭头，可从列表中选择预设选项。
之后需要验证的单元格变黄，可以每格从下拉小三角中选择选项。

9.3 圈释无效数据

设置完数据验证后在数据验证中点击圈释无效数据则可以将范围外的数据筛选处理。

首先设置数据验证条件（如数字小于60000）
设置完成后选择数据验证-圈释无效数据

选中一个设置好的单元格，点击复制后再选中一个或多个单元格点后，右键点击选择性粘贴，再点击其中的验证就可以将数据验证复制到别的单元格中。

十. 条件格式

开始—条件格式—各种条件格式种类

其实条件格式的最大作用，就是可以帮你直观的查看和分析数据。点击开始下的条件格式，选中所需单元格后可以点击突出显示单元格格式或者管理规则来筛选数据，也可以点击清除规则来删除所选单元格或整个工作表的格式。

10.1 突出显示

突出显示

将符合条件的内容高亮显示：选中表格区域-条件格式-突出显示单元格规则
可使用规则：大于小于，重复值，平均值对比等

图标集

可以根据规则设置不同图表
可以在管理规则中选择只显示图标

管理规则

条件格式-管理规则：可以任意编辑显示规则

迷你图

选中数据范围-插入-迷你图-选择图表显示范围
迷你图中可以选择显示特殊点（高点/低点e.g.）
可以修改迷你图格式，特殊点颜色，坐标轴e.g.

10.2 数据条

数据条

条件格式-数据条
设置样式：在管理规则或数据条-其他规则

10.3 快速找出不重复值

也可以点击条件格式的重复值下方的筛选唯一值再通过颜色筛选只显示唯一值。

删除重复值

可以选中列后点击数据中的删除重复值（注意该操作有损）

选中区域-数据-删除重复值
可以选择删除范围
❗️会删除原数据，最好在新建表格中进行

条件格式

条件格式-突出显示-重复值-选择显示唯一值
突出显示后可以使用筛选，筛选出已高亮的唯一值，方便找到所需数据

10.4 多重条件

注意：给谁改颜色，在谁的列上做条件格式，而数据只能做为条件

举例1:数量列大于100的项目把他的日期标为红色

选择“日期”一列
条件格式—新建规则—使用公式确定要设置格式的单元格

=H2>100

“日期”一列根据条件被标记颜色。

举例2:数量列大于100的项目把他的对应行标为红色

选择整个区域表格。
条件格式—新建规则—使用公式确定要设置格式的单元格

=$H2>100

满足条件的整行被标记颜色。

十一. 定位目标数值

点击开始选项卡下方的查找中的定位条件即可查找

11.1 定位

在名称框中快速选择区域

例如从2行至99行，A列至F列。在名称框中输入2：99或A：F
自定义常用区域：选一个区域，在名称框中输入常用区域，以后直接在名称框中“常用区域”就可以自动到达这个区域。公式名称管理器里删除取消。

开始-查找和选择-转到（第三个）
快捷键：ctrl+G
可以选择不同定位条件

11.2 批量删除图片

工作表中的图片、形状视为对象，使用定位条件-对象可批量选择删除。

移动图像。alt+鼠标拖动图像
定位条件中选择“对象”，可定位图片（可以按住ctrl取消勾选多余内容）。
或使用“查找与选择-选择对象”，然后框选。点击查找下方的选择对象即可选中图片而不会选中单元格。
Ctrl+G，定位条件，对象，确定，删除。

11.3 定位特殊单元格（定位条件中选择）

根据需要可使用定位条件，定位空值、公式、常量、可见单元格等。

批注：即单元格右上有红色三角的。
- 将带批注的单元格全部定位：Ctrl+G，定位条件，批注，确定。

公式：有公式的单元格
常量：没有单元格
可见单元格：只选中可见的，无视隐藏单元格
空值：选中空白格
- 注意只选中表格范围而非全部范围（快捷键：ctrl+A 全选表格）
- 删除有空值的整行：定位全部空值-删除-删除整行
- 如何使全部空值中重复上一格信息：定位空值，在空格中输入“=（点击上一格）”，然后Ctrl+enter 原位填充。
批量填充（定位空白单元格，按上一个填充）：Ctrl+G，定位条件，空值，确定。输入“=”按方向键“↑”，按Ctrl+回车。
空格批量填充公式：Ctrl+G，定位条件，空值，确定。任意一个空格输入“=公式”，不要按确定生成结果数值，直接按Ctrl+回车，所有空值全部得到公式数值。

举例：

选中所有空白单元格，任意一个空格输入“=公式”，直接按Ctrl+回车，所有空值全部得到公式数值。

十二. 视图

12.1 工作簿&工作表

工作簿

快速新建工作簿：Ctrl+n

视图

点击视图选项卡中的切换窗口就可以切换不同的工作簿（即不同的Excel文件）。

并排查看多个工作簿：视图-并排查看-选择需要的工作簿（Mac在重排窗口）
- 在打开多个工作簿的情况下点击视图中的并列重排就可以同时查看多个工作簿，还可以点击全部重排后更改排列方式。
并排查看多个工作表：视图-新建窗口，然后选择并排查看
- 新建工作簿作为影子工作簿，所作修改都会出现在原表格上
- 想要同时查看同一个工作簿中的不同工作表，可以点击新建窗口后在拖拽窗口，这样就会显示同一个工作簿的两个窗口，再点击并列重排就可以查看不同的工作表（创建该工作簿的方式也被称为影子工作簿，因为在创建好的工作簿上进行修改则原工作簿也会进行同步更改）。

工作表

Shift + F11 :新建工作表

切换工作表：

Ctrl + 工作表附近的左箭头：快速转到第一个工作表。
Ctrl + 工作表附近的右箭头：快速转到最后一个工作表。
点击左或右箭头后鼠标右键就可以选择要跳转的工作表。

**工作表组：**在多个工作表的同一位置进行相同操作（快捷键：按住Ctrl单击所需工作表）
- 按住ctrl再选中多个工作表即可创建工作表组，选中其中任一工作表的单元格后输入内容，则整个工作表组的工作表中的相同单元格也会出现相同内容，再点击工作表表组外的任一工作表就可以取消工作表组。
- 将鼠标放在工作表上右键就可以点击移动或复制来操作工作表。
- 直接在工作表上右键点击隐藏就可以隐藏工作表。
- 在任意工作表上右键点击取消隐藏就可以将隐藏的工作表显示出来。
（1）隐藏中间的行/列时，选择包含被隐藏区域的前后部分，在被选择区域右键取消隐藏即可。

（2）隐藏首行/首列时，需要选择区域后拉动到最边缘区域，在被选择区域右键取消隐藏。
- 也可以在工作表上右键点击给工作表添加颜色就可以给工作表加上不同的颜色来方便识别与查找工作表。

12.2 单元格选择技巧

选中任一单元格后在单元格的上边框上双击就可以跳转到该表格的第一行，选中任一单元格后在单元格的下边框上双击就可以跳转到该表格的最后一行。
选中任一单元格后在单元格的左边框上双击就可以跳转到该表格的第一列，选中任一单元格后在单元格的右边框上双击就可以跳转到该表格的最后一列。

选择技巧

选择多个所需单元格：
- 按住ctrl单击每格
- 按住ctrl键后点击电脑键盘的上下左右键就可以分别跳转到该表的最上下左右边的单元格上。
选择范围内所有单元格：

选择点击左上角单元格，按住shift，再点击右下角单元格

选择一列/行：
- Ctrl+shift+方向键
- 按住ctrl + shift + ↑/↓/←/→键就可以选中一整列或一整行进而通过快捷键来全选工作表。
跳转到行/列一端：
- 双击任意单元格对应方向的边框
- 或按住ctrl+方向键
使标题栏固定在视野中：

视图-冻结窗格
- 以选中单元格为基准，冻结周围行列
点击excel视图中的冻结首行或首列或窗口即可以冻结多个单元格来方便查看。

12.3 冻结窗格

12.3.1 基础

**视图-冻结窗格-以选中单元格为基准，冻结周围行列。确定你想要冻结的行和列的交叉点的右下角单元格。**例如，如果你想冻结前 3 行和前 3 列，就点击 D4 单元格；如果想冻结前 2 行和前 4 列，就点击 E3 单元格。
视图-冻结窗格-点击excel视图中的冻结首行或首列。
冻结多行和多列时，确定要冻结的列，选中要冻结的列的右侧那一列；确定要冻结的行，选中要冻结的行的下侧那一行；视图-冻结窗格-冻结拆分窗格。

12.3.2 举例

(1) 冻结首行: 视图-冻结窗格-冻结首行。如需取消-视图-冻结窗格-取消冻结窗格。

(2) **冻结前3行:**选中A4单元格-视图-冻结窗格-冻结拆分窗格（所是冻结你选中单元格的上面几行）

(3) 冻结A和B列: 选中C1单元格-视图-冻结窗格-冻结拆分窗格（所是冻结你选中单元格的上面几行）

(4) 冻结A列和前3行: 选中B4单元格-视图-冻结窗格-冻结拆分窗格

十三. 查找替换

13.1 查找

快捷键：ctrl+F
可以在选项中选择查找范围
**其他技巧：**可以选中查找范围内单元格后点击左上角名称栏，为所选单元格添加统一名称（后续可以快速选择，并在公式中使用）。
Ctrl + F :查找（可在工作表中查找，也可以在工作簿中查找，不点击单元格匹配则是查找包含的内容，点击单元格匹配则是查找完全相同的内容。也可以查找单元格的格式，打开查找面板后点击选项，再点击格式按鼠标右键就可以设置查找的格式或者点击从单元格选中格式后直接点击已经设置好格式的单元格就可以查找该格式）。
选中查找到的单元格后进行重命名就可以点击任一空单元格进行函数应用，如：=sum（”黄色”）

13.2 替换

快捷键：ctrl+H（从查找进入也可以）
单元格匹配：仅替换查找内容完全一致的单元格（否则凡是包含相应内容的都会被替换）
可以结合通配符

**举例1：**一列中即有北京，又有北京市，现在都替换成北京市

开始-查找和选择-替换-选项-勾选“单元格匹配”（必需整个单元格都是我们要查找的内容才进行替换）。

**举例2：**查找替换不同颜色的单元格

开始-查找和选择-替换-选项-格式-格式-填充（选择颜色）。

举例3：F替换成=F

举例4：换行空格替换成Ctrl+J

替换为中的输入框中输入“Ctrl+J”

举例5：日期把点换成横线。

13.3 结合通配符使用

举例1：所有姓张的员工都改为“经理的亲戚”

开始-查找和选择-替换-查找内容（张）-替换内容*（经理的亲戚）

举例2：姓张，且名字为两个字的改为“经理的亲戚”。

开始-查找和选择-替换-查找内容（张?）-替换内容（经理的亲戚），勾选“单元格匹配”。

举例3：仅将张**的替换为“经理的亲戚”

开始-查找和选择-替换-查找内容（张~ * *）-替换内容（经理的亲戚）**(加””后来的通配符当成普通字符)**

13.4 注意事项

单元格匹配即单元格内容与查找肉容完全匹配，完全一致，而非单元格内容包含查找内容。勾选单元格匹配，精确查找。查找对象和查找内容完全一致；不勾选，模糊查找，查找对像包含查找内容即可。

13.5 小结

十四. 表格打印

14.1 区域打印

选中工作表中要打印的区域后点击文件再点击打印，将打印活动区域改为打印指定区域就可以仅打印选中的区域了。（设置打印区域：页面布局-打印区域-设置打印区域）

14.2 整表打印

在打印时如果最后一页多出一列可以点击打印的自定义边距中的调窄就可以将多出的一列打印在一页上，但是如果多出的列数过多则需要点击缩放中的将所有列数调整到一页上

选择需要excel中需要打印区域，ctrl+P进行打印，打开 “页面设置” 对话框。在 “页面” 标签下，“缩放” 区域选择 “调整为”，将 “页宽” 和 “页高” 都设为 “1 页”。Excel 会自动调整表格缩放比例，使选定区域尽量在一页 A4 纸上显示。

14.3 居中打印

如果要将选中的区域居中打印则可以点击打印中的页面设置中的页边距将水平与垂直都勾选上就可以进行居中打印了。

14.4 行号打印

如果想要打印出工作表中的行号与列号则需要点击页面设置中的工作表勾选上行列与标题就可以打印出行号与列号了。

14.5 标题打印

在页面布局的打印标题中添加标题行，这样每页打印纸中就都有标题了。
打印标题行：页面布局-打印标题-顶端标题行选第一行，选择打印区域-打印。

14.6 工作簿打印

一个工作簿中有多个工作表，如何对工作簿中的多个工作表进行双面打印。

选择工作簿中的任意一个工作表—打印—打印整个工作簿—双面打印（短边翻转）—打印。

注意事项：

打印设置中选择打印整个工作簿。
双面打印中短边和长边翻转的选择。
横向、纸张选择A4纸。
居中打印的选择、工作表调整为一页。
选择打印整个工作簿中的多个工作表时，注意每一个工作表都需要单独调整设置，前后设置不是保持不变的；同时每一个工作表都选择双面打印，最总输出的才会是双面打印。

14.7 小结

区域打印：设置打印区域，页面布局-页面设置-打印区域（或者先框选区域-文件-打印-打印选定区域）
整表打印，灵活设置自定义边距、缩放（整表缩到一页、所有列缩到一页、所有行缩到一页）
居中打印：文件-打印-页面设置（最下）-页边距-居中方式（可勾选水平、垂直居中）
标题行重复：页面设置-选项卡（右下小标）-工作表-打印标题或者页面布局-打印标题-顶端标题行，选择打印区域-打印。
工作簿打印：选择打印整个工作簿中的多个工作表时，注意每一个工作表都需要单独调整设置，前后设置不是保持不变的；同时每一个工作表都选择双面打印，最总输出的才会是双面打印。

十五. 函数基础入门

15.1 基本函数

基础

开始选项卡下有常用函数。

使用tab键或者鼠标双击都可以进入到这个函数当中。
当然函数的种类是非常众多的，对不熟悉公式的函数，开始选项卡下有常用函数也可以使用fx参数面板。
如何显示fx参数面板：视图—显示—编辑栏（勾选编辑栏）即可显示。

求和：=SUM()，拖动框选所需范围。
- 使用sum函数时要在单元格中输入=sum再按tab键，再选中数据即可求和(英文输入法下）。
- 某一个单元格求和后对单元格右下方标识双击，整列都可以直接得到求和数值。
- Alt + = :快速大面积求和。
- 自动求和工具。
求平均值：=AVERAGE()
自定义求值：公式-插入函数（可选择在原公式中选择计算单独单元格）
判断：=IF(条件,为T的结果，为F的结果)

可以在参数面板下选
如：=IF(A1>5,"男","女")
使用If函数时在输入函数后点击ctrl + a就可以调出函数的参数面板。

在Excel的函数面板中可以详细的查看函数信息。
函数中间需要是一个英文状态的逗号。

15.2 使用函数的好处

避免拼写错误，结果会自动修正
使用文本时不会报错，会自动跳过文本

❗️但是如果数字是文本形式，那就不会被计算

新插入或删除行列时，函数会自动更新

15.3 绝对引用/相对引用

**相对引用：**在公式中包含单元格时，拖拽公式至其他格，公式中所引用的单元格都会相对变化；如拖拽公式=K6至下一行，则下一格公式变为=K7
绝对引用：可以单独固定引用的行/列
在需要锁定的行/列序号前键入 $，如K$ 7
行列都锁定时，可在K7前键入快捷键F4
一般选择区域为整行或者整列时，不需要使用绝对应用；选择部分区域时，需要使用绝对应用，因为伴随着函数单元格的改变，选择区域也会发生改变。
举例：

=B$1*$A2

说明：

单个单元格可以看成某行与某列构成。
单元格B1向右移动时，行序号变化，列序号1保持不变，所以使用B$1。
单元格A2向下移动时，行序号A保持不变，列序号变化，所以使用$A2。

15.4 小结

十六. Count系列统计函数

16.1 COUNT

返回包含数字以及包含参数列表中的数字的单元格的个数（数字个数）。利用COUNT函数可以计算单元格区域或数字数组中数字字段的输入项个数。
仅限数字，不计算文本型数值；文本是左对齐，数值是右对齐。
函数：=COUNT()

16.2 COUNTA

返回参数列表中非空值的单元格个数（非空个数）。利用函数COUNTA可以计算单元格区域或数组中包含数据的单元格个数。
函数：=CUONTA()

16.3 COUNTBLANK

返回参数列表中空值的单元格个数（空单元格数）。利用函数COUNTBLANK可以计算单元格区域或数组中包含空格的单元格个数。
函数：=COUNTBLANK()

16.4 条件统计（条件计数）

16.4.1 COUNTIF

条件统计(Excel里的函数，用于计算区域内满足条件的个数)。

COUNTIF：函数名称，专门用于对区域中满足单个指定条件的单元格进行计数。
返回参数列表中满足条件的单元格个数
条件可以设置数值或性质
函数：=COUNTIF(range, "criteria")
- 如=COUNTIF(F2:F4,"及格") 或 (F2:F4,"<>60")
- 注意使用英文标点符号，加"" ；使用fx参数面板直接输入范围和条件即可，不用考虑这些符号。
- ❗️如果在criteria中包含单元格，不能直接"<A1"，需要使用&文本连接符，如"<"&A1
函数格式：

=COUNTIF(总表!P:P,"某个关键词")

举例说明

=COUNTIF('大区提报(每日更新）'!J:J,"*产值*")

大区提报(每日更新）表格中的J列的包含产值的数量和。

=COUNTIF('大区提报(每日更新）'!J:J,"产值")

大区提报(每日更新）表格中的J列的问题类型为“产值”这两个字的区别的数量和。

某一个区域的关键词必须加“”，如果含有某个关键词"某个关键词",全部是英文形式的逗号。"产值"：表示精确匹配，只有单元格内容完全为 “产值” 时，才会满足条件。"*产值*"：其中*是通配符，代表任意数量的任意字符（包括 0 个字符）。

=COUNTIF('大区提报(每日更新）'!J3:J199,"*交付*")

大区提报(每日更新）表格中的J列中的J3到J199的包含交付的数量和。

16.4.2 COUNTIFS

返回参数列表中满足多个条件的单元格个数
函数：=COUNTIFS(range1, "criteria1", range2, "criteria2",...)
- Or条件：可以使用+将两个函数结果相加
- 如：=COUNTIF(range1, "criteria1") + COUNTIF(range2, "criteria2")
举例说明：

=COUNTIFS(区域1,"关键词1",区域2,"关键词2",区域3,"关键词3", ...)

区域 1、区域 2、区域 3 等：条件区域，分别代表你要筛选的各列单元格区域。
关键词 1、关键词 2、关键词 3 等：条件区域的关键词。对应各区域要匹配的关键词。

=COUNTIFS('大区提报(每日更新）'!J:J,"*交付内部*",'大区提报(每日更新）'!O:O,"是")

大区提报(每日更新）表格中的J列的单元格包含交付内部、O列中的单元格为“是”的数量和。

16.5 通配符

16.6 区别

Count函数：返回选中的单元格中的数据个数；Counta函数：返回选中的单元格中的非空值的单元格的个数；Countblank函数：返回参数列表中的空值的单元格个数。
Countif函数：筛选只满足一个条件的单元格个数；Countifs 函数：筛选出满足多个条件的单元格个数。
& ：文本连接数，可以用于点击单元格来获得单元格内容；* ：代表若干个字符的通配符；? : 代表一个字符的通配符； ~ ：转义符号。

16.7 小结

Count函数：返回选中的单元格中的数据个数
Counta函数：返回选中的单元格中的非空值的单元格的个数
Countblank函数：返回参数列表中的空值的单元格个数
Countif函数：筛选只满足一个条件的单元格个数
Countifs 函数：筛选出满足多个条件的单元格个数

十七. 常用求和公式

17.1 对连续单元格区域求和

比如要计算 A1 到 A10 单元格区域的和，在其他单元格中输入

=SUM (A1:A10)

17.2 对不连续单元格区域求和

可以求和单个值/单元格引用/区域
对整个表格可以选中需要sum的任意一格后，选中整个表格；或者选择需要求和的特定区域，使用快捷键alt+=；一行或者一列的求和就自动出来了。

若要计算 A1、A3、A5 这几个不相邻单元格的和，可输入

=SUM (A1,A3,A5)

按回车键得出结果。

17.3 混合求和

要是想把 A1 到 A5 区域与 B1、B3 单元格的数值相加，公式可写成

=SUM (A1:A5,B1,B3)

回车后即可得到总和。

17.4 忽略隐藏行/列求和

17.4.1 SUBTOTAL 函数

隐藏行（即筛选后不显示的行），仅对可见行的数据进行求和。

语法：SUBTOTAL(function_num, ref1, [ref2,...])
参数说明：
- function_num：指定使用的函数，对于求和，使用9。
- ref1, [ref2,...]：要进行计算的一个或多个区域。
示例：若要对 A 列的数据进行忽略隐藏行的求和，可在其他单元格输入公式
```
=SUBTOTAL(9,A:A)
```
此公式会自动忽略筛选后隐藏的行，仅对可见行的数据进行求和。

17.4.2 AGGREGATE 函数

语法：AGGREGATE(function_num, options, ref1, [k,...])
参数说明：
- function_num：同样，求和时使用9。
- options：这个参数决定函数在计算时如何处理隐藏值和错误值等情况。如果要忽略隐藏行，使用7。
- ref1：需要计算的单元格区域。
示例：对 A 列数据忽略隐藏行求和，公式为
```
=AGGREGATE(9,7,A:A)
```

17.4.3 区别

AGGREGATE函数比SUBTOTAL函数功能更强大，它不仅能忽略隐藏行，还能根据options参数的不同设置，灵活处理错误值等情况。

17.5 条件求和

17.5.1 SUMIF 函数

=SUMIF(range, criteria, sum_range)

函数语法：SUMIF(range, criteria, [sum_range])
参数解释：
- range：必需参数，用于条件判断的单元格区域。
- criteria：必需参数，指定的条件，形式可以为数字、表达式、文本等。
- [sum_range]：可选参数，要进行求和的实际单元格区域。若省略，就对range区域内满足条件的单元格自身求和。

举例说明：

=SUMIF(总表!P:P,"北部",总表!U:U)

条件区域：P列，条件为：P列中的北部，求和数值在U列。

表格为总表的P列中的的单元格为北部，对应U列数值的求和。

=SUMIF(总表!P9:P497,"北部",总表!U9:U497)

17.5.2 SUMIFS 函数

语法：SUMIFS(sum_range, criteria_range1, criteria1, [criteria_range2, criteria2, ...])
参数解释：
- sum_range：必需参数，这是要进行求和的实际单元格区域。
- criteria_range1：必需参数，第 1 个用于条件判断的单元格区域。
- criteria1：必需参数，第 1 个指定的条件。
- [criteria_range2, criteria2, ...]：可选参数，可添加多个条件判断区域及对应的条件，最多可包含 127 组条件区域和条件。

举例说明：

=SUMIFS(总表!BK:BK,总表!P:P,"中部",总表!Q:Q,"已签")

条件区域：总表!P:P和总表!Q:Q；条件为："中部" 和"已签"；求和数值在总表!BK:BK。

表格为总表的P列中的的单元格为中部、总表的Q列中的的单元格为已签；对应BK列数值的求和。

注意：使用上面的公式直接复制过来时，如果将公式直接复制到其他位置时，区域因为是相对位置，复制过去后公式中的区域会发生变化。

举例说明：

将已签已经执行的一列含公式的数据向右边拖动时，因为位置是相对位置，公式中的区域发生变化；由=SUMIFS(总表!BK:BK,总表!P:P,"北部",总表!Q:Q,"已签")变成=SUMIFS(总表!BL:BL,总表!Q:Q,"北部",总表!R:R,"已签")。

要改成绝对引用，需在列标和行号前加$

=SUMIFS(总表!$U:$U,总表!$P:$P,"北部",总表!$Q:$Q,"中标未签")

在行和列前面添加$，求和区域和条件区域中的位置都是绝对位置；直接复制后只需要更改判断条件即可。

此时再复制公式，不管是用选择性粘贴 - 公式，还是拖动填充柄（选仅复制公式），公式引用区域都不会因位置改变而变化，因为用定义的名称代替了原来的相对引用区域。

=SUMIFS(总表!$U:$U,总表!$P:$P,"北部",总表!$Q:$Q,"中标未签")-SUMIFS(总表!$BK:$BK,总表!$P:$P,"北部",总表!$Q:$Q,"中标未签")

SUMIFS函数加减。

17.5.3 区别

SUMIF 函数和SUMIFS 函数公式中的条件区域和求和区域是相反的。SUMIF 函数中的要进行求和的实际单元格区域在最后方；SUMIFS 函数的求和区域在最前方。
可以在fx参数面板中可视化操作。在Excel的函数面板中可以详细的查看函数信息。
注意区域中的行、列中相对位置和绝对位置的区分。
SUMIF 函数和SUMIFS 函数公式中的条件可以直接选择单元格，在考虑两个条件单元格的绝对引用/相对引用的情况下，直接下拉即可得到相应数值。单元格是有行与列组合而成，举例：单元格A6，在使用条件求和公式右拉或者下拉拖拽时，注意A列和第六行的行/列使用绝对引用。

17.6 数组求和

17.6.1 Sumproduct 函数

Sumproduct 函数：能进行数组求和的函数，SUMPRODUCT函数是在给定的几组数组中，将数组间对应的元素相乘，并返回乘积之和。

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

使数组里相应元素进行相乘并求和
array：包含对应元素的数组，可以是数组也可以是区域。
如SUMPRODUCT(产品单价列，各产品销量列)
公式内可以包含其他函数

举例：

上述函数中，数组一是产品单价区域，数组二是产品数量区域。

17.6.2 Sumproduct 函数与sumif函数的混合使用

常规计算：如上实例，计算对应产品的销售额时，使用sumif函数计算每一种产品的总数量，单价*数量=销售额。
Sumproduct 函数：选择右边的数组一产品单价区域，数组二产品数量对应区域。回车得到各产品的销售总额。
Sumproduct 函数与sumif函数的混合使用：

数组一是产品单价区域，数组二是产品数量（使用sumif函数计算每种产品的总数量）。

十八. AVERAGE平均值函数

18.1 常用函数公式

函数公式：

=AVERAGE(A1:A10)

对连续单元格区域、不连续单元格区域等全部参考上方的常用求和公式。

18.2 AVERAGEIF 函数

公式语法
- AVERAGEIF(range,criteria,[average_range])
- range：必需参数，要计算平均值的数据区域。
- criteria：必需参数，用于定义要计算平均值的数据需满足的条件。条件可以是数字、表达式、单元格引用或文本。例如，条件可以表示为 32、">32"、B4、"苹果" 或 "32"。
- average_range：可选参数。如果省略，则使用 range 区域内满足条件的单元格计算平均值；若指定，就对该区域内满足 range 区域条件的对应单元格计算平均值。
举例说明

=AVERAGEIF(A1:A10,"一班",B1:B10)

这里 A1:A10 是班级所在的数据区域（range），“一班” 是条件（criteria），B1:B10 是要计算平均值的成绩区域（average_range）。公式会先判断 A1 到 A10 单元格中哪些是 “一班”，然后计算这些对应行在 B1 到 B10 中的成绩平均值。

18.3 AVERAGEIFS函数

公式语法
- AVERAGEIFS(average_range,criteria_range1,criteria1,[criteria_range2,criteria2,...])
- average_range：这是必需参数，指的是要计算平均值的实际单元格区域。
- criteria_range1：必需参数，为条件判断的第一个单元格区域。
- criteria1：必需参数，是应用于criteria_range1区域的条件。
- [criteria_range2,criteria2,...]：可选参数，最多可包含 127 个区域 / 条件对。每个criteria_range是对应的条件判断区域，criteria是相应的条件。所有条件之间是 “与” 的关系，即只有同时满足所有条件的单元格才会被纳入平均值计算。
举例说明

=AVERAGEIFS(C1:C50,A1:A50,"一班",B1:B50,"男生")

这里C1:C50是average_range，即要计算平均成绩的区域；A1:A50是criteria_range1，条件criteria1为 “一班”；B1:B50是criteria_range2，条件criteria2为 “男生”。该公式会筛选出同时满足班级为 “一班” 且性别为 “男生” 的学生成绩，并计算这些成绩的平均值。

18.4 AVERAGE和SUM

AVERAGEIF、AVERAGEIFS与SUMIF、SUMIFS的函数使用方式，完全一致；只需要将进行求和的实际单元格区域更改成为要计算平均值的实际单元格区域即可。

十九. VLOOKUP函数

19.1 函数公式

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

可以理解为

=VLOOKUP（查找值，目标范围，从查找值开始算1第几列，0/FALSE）

用来寻找某单元格行列内其他对应值

lookup_value 需要在数据表首列进行搜索的值，可以是数值，引用或字符串（如：某姓名）
table_array 要在其中搜索数据的文字、数字或逻辑值表，可以是对区域或区域名称的引用，查找值对应列为目标范围的第一列。
col_index_num 返回匹配的序列号，表中首个值列的序号为1 （如需要此行第二个数据：2）
range_lookup 逻辑值：大致匹配用True(1)或省略，精确匹配用False(0)

模糊查找可以按顺序查找某一范围内对应属性，如60-80分：良好

19.2 举例说明

以5 月经营计划及重大专项工作的通知中的经营指标表格为实例。

筛选原二季度计划中的指标数据，生成新表。

计算陆总的各指标数据。

=VLOOKUP(C2,$O$3:$V$37,2,0)

查找值：C2表示所需查找的指标“销售订单”在C2单元格。

查找目标区域范围： $O$ 3: $V$ 37表示查找值数据的绝对区域。查找值必须是目标区域的第一列。

在查找目标区域内从查找值开始算1第几列：2表示陆总在查找值数据的绝对区域的第二列。

0/FALSE：0是精确匹配

其他指标、区域：使用绝对引用，直接下拉拖拽，得到陆总所有指标；类比得到其他各区域的指标数据。

19.3 注意事项

单元格格式一定要选常规！！！选择文本出不来。
如果你要vloookup所有数据，已经用过筛选的，把筛选取消掉。如果你只需要vlookup筛选过的数据，把筛选过的数据复制到另一张空白表上再做vlookup，这样更明了。
VLOOKUP函数 第1参数所在列必须为第2参数（数据框选）首列。
笔记本电脑点Fn+F4,（台式电脑直接F4），就是给你的范围加上绝对引用的指令，这样引用的范围不会变。

19.3 反向查找

19.4 小结

二十. 其他常用函数

20.1 函数公式汇总

序号	名称	语法		EXCEL版本要求
001	求和	SUM
002	求平均	AVERAGE
003	最大最小值	MAX和MIN
004	条件求最大值	MAXIFS（求最大值的范围，条件范围1，条件1，条件范围2，条件2……）		EXCEL2019
	条件求最小值	MINIFS（求最小值的范围，条件范围1，条件1，条件范围2，条件2……）		EXCEL2019
005	排名	RANK（要排位的数字，比较的范围，[升序1/降序0]）
006	逻辑判断	IF（如果你来北京，我请你吃饭，否则你就发个红包）
	逻辑判断嵌套	IF（如果是猪，就吃掉，（如果是狗，就养着，否则就放掉）)
007	逻辑判断	IFS（条件1，返回值1，条件2，返回值2，条件3，返回值3……)		EXCEL2019
008	回避错误值	IF（iserror（运算），0，运算)
009	且（关系）	AND(条件1，条件2，条件3……)
010	或（关系）	OR(条件1，条件2，条件3……)
011	计算参数中包含数字的个数	Count	不能统计文本
012	计算参数中包含非空值的个数	Counta
013	计算区域中空白单元格的个数	Countblank
014	单条件统计	COUNTIF（范围，条件）	超过15位条件要写A2&"*"
015	多条件统计	COUNTIFS(范围1，条件1，范围2，条件2……)
016	连接多个区域的文本字符串	CONCAT（区域范围）
017	单条件求和	Sumif（条件区域，求和条件，需要求和的区域）	超过15位条件要写A2&"*" 如果第3参与第1参相同，可省略
018	多条件求和	Sumifs（求和区域，第1个条件区域，条件1，第2个条件区域，条件2……）
019	查找引用	Vlookup（找什么，在哪找，返回第几列，T/F）	T=1近似匹配，F=0精确匹配。
020	【横向】查找引用	Hlookup（找什么，在哪些行里找这里要绝对引用，返回第几表，T/F）
021	Match查找	Match（要找什么，在哪里找，T/F）	返回值是拿第几个
022	Index引用	Index（在哪里拿，拿第几个）	第二参数就是Match
023	日期函数	Year(日期)=年，Month(日期)=月，Day(日期)=日
024	日期组合函数	DATE(YEAR(日期单元格)，MONTH(日期单元格)+间隔月份单元格，DAY(日期单元格)) DATE(YEAR(开始日期),MONTH(开始日期)+1,1)-1 【day为0就是上月最后一天】 DAY(DATE(YEAR(开始日期),MONTH(开始日期)+1,1)-1)	开始日期2013/6/16 间隔月份4月开始日期2013/6/16 本月最后一天开始日期2008年2月11日本月天数
025	返回两个日期之间的年\月\日间隔数	Datedif(开始日期，结束日期，类型)
026	返回日期在一年中的第几周	Weeknum（日期，2）	1代表星期从星期日开始，2代表星期从星期一开始
027	返回代表一周中第几天的数值	WEEKDAY(日期，2)	2代表星期一是1，星期日是7
028	日期整容	TEXT（把谁整容，怎么整）
029	商品保持期函数	EDATE(生产日期，保质期（个月）)
030	显示单元格使用的公式	FORMULATEXT（单元格）
031	文本截取字符串	Left(文本，取几个)，Right(文本，取几个)，Mid(文本，从第几个截取,取几个)
032	查找函数	Find(要查找的字符串，在哪里查) 例如：邮箱地址，要查的字符@	查邮箱用户名：=LEFT(F2,FIND("@",F2)-1) 先用Find查@在第几位，用LEFT截取Find-1 查邮箱域名：=MID(F2,FIND("@",F2)+1,100) 先用Find查@在第几位，用Mid截取Find+1
033	返回字符串中字符的个数	len(文本)
034	返回字符串中字节的个数	lenb(文本)
035	四舍五入	Round(要四舍五入的数，小数点后保留几位)	例：1.234=1.23，1.235=1.24
036	向上舍入的数字	RoundUp(用来向上舍入的数字，保留几位小数)	例：1.1=2，2.24=3
038	向下舍入的数字	Rounddown（用来向下舍入的数字，保留几位）	列：1.1=1，2.24=2，-1.4=-1
035	“取整”函数	Int（数值）	例：1.1=1，2.35=2，-1.4=-2
036	求余数	Mod（被除数，除数）	例：mod(1.232,1)=0.232，mod(23,7)=2
038	返回行号	Row（）
035	返回列号	Column（）
036	万能函数	SUMPRODUCT(数组或区域1，[数组或区域2]……[数组或区域3])	函数是在给定的几组数组中，将数组间对应的元素相乘，并返回乘积之和
038	LOOKUP函数	Lookup（找什么，在哪找，返回哪个列）
035	返回由文本字符串指定的引用	INDIRECT(ref_text,[a1])
036	数据透视表的动态引用	Offset（以某个点为基准，下移N行，右移N列，取N行，N列）

20.2 if和ifs函数

1. 基础

判断：=IF(条件,为T的结果，为F的结果)

可以在参数面板下选
如：=IF(A1>5,"男","女")
使用If函数时在输入函数后点击ctrl + a就可以调出函数的参数面板。

2. `IFERROR`函数

函数公式：IFERROR(value, value_if_error)
参数说明
- value：表示要进行计算或测试的表达式、单元格引用或函数。这是你希望正常执行的部分，如果这部分执行过程中没有错误，就返回这部分的结果。
- value_if_error：当value部分执行出现错误时，要返回的值或表达式。这个值可以是文本、数字、单元格引用等。
举例说明

=IFERROR(AU22/AT22,"/")

说明：这个公式的作用是先尝试计算AU22单元格的值除以AT22单元格的值。如果这个除法运算能够正常进行，没有出现错误，那么公式就返回这个除法的结果。但如果在计算AU22/AT22的过程中出现了错误（比如AT22单元格为 0，导致除法无法进行，出现 #DIV/0! 错误），那么公式就不会返回错误值，而是返回"/"这个斜杠字符。

示例 1：处理可能出现的 #VALUE! 错误
- 场景：假设你有一个表格，A 列是数字，B 列有些单元格是数字，有些是文本。你想在 C 列计算 A 列与 B 列对应单元格的乘积。
- 公式：在 C1 单元格输入=IFERROR(A1*B1,"数据错误")，然后向下填充。
- 解释：如果 A1 和 B1 都是数字，公式会返回它们的乘积。如果 B1 是文本，A1*B1 会出现 #VALUE! 错误，此时公式返回 “数据错误”。
示例 2：处理可能出现的 #REF! 错误
- 场景：在一个数据处理过程中，有时会删除某些列，导致引用的单元格出现 #REF! 错误。假设原来 D 列数据与 C 列数据相关联，在 E 列有公式引用 D 列数据，如=C1 + D1。当删除 D 列后，E 列公式就会出现 #REF! 错误。
- 公式修正：将 E 列公式改为=IFERROR(C1 + D1,"引用错误")。这样，即使 D 列被删除，E 列也不会显示 #REF! 错误，而是显示 “引用错误”。

20.3 数据透视表

数据透视表（字段就是列，行是记录）

重点注意事项：

数据透视表标题行（首行）不能有并合并单元格。

行和列中直接拉动标签即可。字段就是列，行是记录。
值字段设置—计算类型，更换求和、计数、平均值等形式。
插入透视表。将鼠标放在原待透视表格的任意一个单元格上，点击；插入数据透视表即可（范围会自己填充）；不需要插入区域。
插入透视表时出现如下报错

两个原因导致：

原表格（数据透视表）标题行（首行）有合并单元格。
原表格（数据透视表）标题行（首行）有空格单元格。

举例1：数据透视表中列太详细可以分组（例如每天日期可以按季度显示）

日期：右键-组合-季

举例2：数据透视表做区间统计（如购物篮分析）

0-40000 多少钱（多少笔），40000-80000多少钱（多少笔）

方法：将金额做为行，也将金额做为列**。行标签—点右键—创建组**；值—值字段设置—计算类型

起始于0，终止于320000，步长40000。取320000是为了好除，划分段。

举例3：列上有两个条件时，会出现讨厌的汇总，双击表头-选（无）。

行标签单元格右键-数据透视表选项-勾选经典数据透视表布局

双击行标签-分类汇总-无-确定。

举例4：在数据透视表里算毛利率（行标签中没有该标签）

选中透视表任何一个单元格-分析-字段、项目和集-计算字段。名称（毛利率）-在下面双击自己加运算符号。

右键-数据透视表选项-勾选“对于错误值，显示”

二十一. 分列

21.1 基础操作

分列：将一个单元格中的内容拆分到两个或多个单元格中，注意分列功能一次只能拆分一列（可以将文本类型转化为数据类型或者也可以将不规范日期转化为规范日期格式）。

**分列：**数据 - 分列
一次只能拆分一列
**分割方式：**可以选择分隔符或宽度的分割方式（如自定分割符号/文本）
**内容格式：**可能会把未完全分隔的数字数据自动合并，需要手动将常规格式调整为文本格式（可以选择跳过特定列）
**技巧：**选中文本数值后使用分列，直接选择常规格式，即可修正不规范内容格式
- 注意日期格式需要选择对应原格式YMD

21.2 文本转数值

使用分列功能，一直点下一步，一直到最后进行最后的转换文本、数值即可。

21.3 规范不标准日期

选择区域，使用分列功能，一直点击下一步，最后数据格式更换为日期即可。注意日期格式需要选择对应原格式YMD

文本型的日期通过分列直接变成日期格式：选中单元格-数据-分列-固定宽度-日期-完成

21.4 小结

二十二. 排序和筛选

22.1 排序

重新排列数据，方便查找。
升序/降序（数字&文本都可以进行此类排序），选择需要排序的列的某一个单元格/整个排序区域即可选择升降序。
自定义排序，可以选择主要&次要排序条件（排序条件：字母/笔画/自定义值，行/列）。
中文排序

举例：中文排序（例如：一部，二部，三部）：不能按值排序，如果按值排序，它就按照汉语拼音顺序排序了。开始-排序和筛选-排序-次序（自定义序列）-新序列-手工输入（竖着写，写一个就回车）。

22.2 筛选

快捷键：ctrl+shift+L
筛选条件：
数字可以选择前n项/前n%
文本可以选择包含/开头结尾为某文本
可以设置多个筛选条件（and/or）
进行一次筛选后再进行一次筛选点击将当前数据添加到筛选器即可筛选多个内容。
文本筛选：可以选择“等于“、”不等于“、”开头是“、”结尾是”、“包含”、“不包含”。可以使用通配符“*”和“?”。

22.3 排名

**排序/筛选排名：**顺序排序后依次输入排名
可以向下拖拽自动填充时按住ctrl，出现两个➕即可输入数列顺序
或填充后选择填充系列
**RANK函数：**可以进行快速排名
=RANK(number, ref, order)
**num：**需要排名的单元格
**ref：**需要引用的排名区域（如果num只选第一格记得固定引用此范围）
**order：**忽略/0-降序，非0值-升序

22.4 复制粘贴

22.4.1 如何复制粘贴筛选过的表格

筛选数据：首先对表格进行筛选操作，只显示你需要的内容。比如，在员工信息表中，通过筛选功能只显示 “部门” 为 “销售部” 的员工记录。
选中筛选后的表格：点击表格左上角行号与列标交汇处（全选按钮），确保整个表格处于选中状态。此时，虽然部分数据被隐藏，但整个表格区域都被选中。
定位可见单元格：按下快捷键 Ctrl + G，打开 “定位” 对话框，点击 “定位条件” 按钮，在弹出的 “定位条件” 对话框中选择 “可见单元格”，然后点击 “确定”。这样就只选中了筛选后可见的单元格。
复制与粘贴：选中可见单元格后，按下 Ctrl + C 组合键复制，再切换到需要粘贴的位置，按下 Ctrl + V 粘贴，粘贴后的表格就只包含筛选出来的数据。

进行一次筛选后再进行一次筛选点击将当前数据添加到筛选器即可筛选多个内容。

22.4.2 复制含有公式的数字和单元格格式

选择-复制-选择性粘贴-值和源格式（复制数值和单元格、工作表的格式）。
**选择性粘贴-值和数字格式（**数值不变、公式消失、保留单元格、工作表的格式）。
如果新复制粘贴的表格的格式与原表格式不一致，可以对原表格直接格式刷用在新的表格即可。

22.5 小结

二十三、生成可以跳转的目录

23.1 生成目录公式

【公式】-【定义名称】-【新建名称】-名称输入目录，引用位置填写以下内容

=INDEX(GET.WORKBOOK(1),ROW(A1))&T(NOW())

新建一个名为目录的工作表（右键工作表-插入-工作表），在*3单元格输入以下公式

下拉即可生成下图每一个单元格的内容。（如果出现空白报错，一直下拉就会出现）。

=IFERROR(HYPERLINK("#'"&MID(目录,FIND("]",目录)+1,99)&"'!A1",MID(目录,FIND("]",目录)+1,99)),"")

做返回的超链接，在任意工作表任意位置空白单元格输入以下公式，然后复制粘贴到其他工作表中。

=HYPERLINK("#目录!A1","返回目录")

【最重要】工作表保存时需要选择保存类型为“Excel 启用宏的工作簿（*.xlsm）”，否则再次打开时就不能正常使用了。

23.2 注意事项

报错：Microsoft已阻上宏运行，因为出这件的来源不受信任。

解决：

启用宏设置：打开 Excel 2016，点击 “文件” 选项卡，选择 “选项”。在弹出的对话框中点击 “信任中心”，接着点击 “信任中心设置”，选择 “宏设置”，勾选 “启用所有宏（不推荐，可能会运行潜在危险的代码）”，最后点击 “确定” 即可。

更改受信任位置：进入 “文件”>“选项”>“信任中心”>“信任中心设置”，选择 “受信任位置” 选项卡。点击 “添加新位置”，选择包含宏的文件所在文件夹，可勾选 “子文件夹也受信任”，然后点击 “确定”。将文件放置在该受信任位置，Excel 会自动启用其中的宏。

启用特定工作簿中的宏：打开包含宏的工作簿，Excel 顶部会显示安全警告栏，点击 “启用内容” 按钮，即可在当前工作簿中启用宏。此方法可仅对特定文件启用宏，不更改 Excel 全局设置。