Excel

学习资料链接：
B站：https://space.bilibili.com/402780815/channel/seriesdetail?sid=310160

入门

• enter:向下移动，+shift向上移动
• tab：向右移动，+shift向左移动
• 双击间隔线：适配宽度
• 跨栏置中
• 一次性调整宽度：全选再调整
• 有等差数列的全部选中后向下拖拽产生等差数列
• 日期用‘/’后可以更改日期格式，ctrl+‘；’为今日日期
• 选中结果栏-开始-编辑-自动求和/平均值等常用函数-excel自动框定计算条件（不符合可拖动调整）-回车得结果
• 画笔（两种，绘制外框，绘制内框，按Shift切换橡皮功能，按esc退出）

冻结窗格，分割视窗

• 冻结窗格：选定需冻结窗格的下一栏/列-视图-窗口-冻结窗格-冻结窗格（滑动时某一行/列不动）
• 拆分视窗：选定需拆分的大小-选定那一栏-视图-窗口-拆分

排序

• 自订清单（数据有效性——序列那里就是）：依照用户自己设定的顺序来排列资料。排序与筛选--自定排序--选择要排列的栏位--顺序选定为自订清单--清单项目--输入自己想要的顺序删除清单--选择建立的清单-删除

ps：自订清单可以按照自己设定的顺序自动完成内容填充

交叉分析筛选器

• 表格--插入交叉分析筛选器（切片器）--勾选想筛选的栏位--生成面板
  面板可调整大小、栏位，按 delete 可删除，右上角可清除筛选条件
• 同时显示多个项目：按住 control 不放选择
  多重筛选：选取其他面板内的选项

设定格式化的条件

• 设定格式化：选取所在列——功能区“条件格式”——自行设定条件和突出显示样式
• 更改/删除条件
  选取更改列——条件筛选——管理规则——编辑规则——可重新指定条件——删除条件
• 数据条/色阶/图标集
• 清除规则：条件格式——清除规则——清除整个工作表规则

工作表设定

复制工作表：按住Ctrl不放——拖移工作表
工作组：按住Ctrl选择工作表——编辑——右键取消工作组（同时编辑多个工作表）

合并汇算

对多个工作表进行汇总
建立空白工作表——数据——合并计算——选取表格范围——添加范围——挨个添加工作表需要计算的范围——确定

图表制作

框选数据（包括标题）——插入——选择表格样式

枢纽分析表

插入——枢纽分析表（数据透视表）——可选范围和位置
勾选想要显示的栏位标题--拖拽标题至不同位置会在数据透视表发生相应变化

更新数据后——分析——重新整理——才能在枢纽分析表中看到更新的资料（或者在用枢纽分析之前，将表格转化为动态表格）

列印分页设定

调整打印页面范围:
a.通过拖拽分页线调整打印页面范围：视图-分页预览
b.自动缩放：页面布局—设定页面宽度和高度
c.只需打印几行资料：页面布局—打印区域--设定打印范围

每页都有标题:
页面布局—打印标题—顶端标题行：输入标题坐标

手动分割每页：页面布局-分隔符-插入分页符（删除分页符）

页首页尾

视图——页面布局——点击页尾位置——页眉和页脚工具——设计——页码 ——输入可设置“第X页/共X页”

函数

引用函数公式基本结构： =函数名称(引区范围)

例如：=SUM(C3:C6)
一个引数：同样的有AVERAGE()、MAX()、MIN()

两个引数：
=LARGE(引区地址1:引区地址2，顺位数字) 找出第几个大的数
=SMALL(引区地址1:引区地址2，顺位数字) 找出第几个小的数

逻辑函数IF

=IF（条件，“条件成立”，“条件不成立”）

巢状IF函数(EXCEL2016版本后IFS函数)：=IF（条件，成立，IF（条件，成立，不成立））

VLOOKUP函数 & 相对参照

=VLOOKUP(关键字，资料范围，栏数，查询模式)
关键字：点击位置即可，比如按学号查询即点击填写学号的单元格
资料范围：用鼠标框选整个表
栏数：从1开始数（为查询的列位置）
查询模式：精准查询为FALSE/0，模糊查询为TRUE/1（模糊查询需要查询列为递增）

相对参照：
VLOOKUP下拉，其查询表格的位置也相对向下移动，因此会出现错误。

绝对参照：
选中资料范围——按下F4即可锁住表格位置【=VLOOKUP(J3,$M$3:$N$7,2,1)】

IFERROR函数

IFERROR(要检查的公式，公式错误的讯息)
【=IFERROR(VLOOKUP(C3,$E$2:$J$12,2,FALSE),"查无此人")】

验证：选择存储格--数据--验证--设置待验证内容和报错信息

COUNTA() 计数

计算非空个数（即使是文字也可以计算，因为COUNT只能计算数字的）

COUNTIF() 计算符合条件储存格数目

COUNTIF(资料范围,筛选条件)

COUNTIFS(范围1，条件1，范围2，条件2...)
【=COUNTIFS(B2:B14,F2,C2:C14,G2)】

“&”符号的作用是串联文字和储存格的内容
数值类型筛选：【=COUNTIFS(C2:C14,F2,D2:D14,">"&G2)】（日期跟数值一样也可以用大于小于符号）

需要筛选月份资料：
用MONTH()建立辅助列归纳月份——用COUNTIF()筛选范围设定为新建的月份

SUMIF()

SUMIF(加总范围，资料范围1，条件1，资料范围2，条件2...)

储存格定义名称（就是定义范围的名称，以后函数使用该范围就写名称即可）

1、框选范围——公式——定义名称
2、框选范围——左上角写名称
名称管理员可以管理名称

INDIRECT()

INDIRECT(位置)
个人感觉跟VLOOKUP有点像

excel操作小技巧

1、资料剖析（分列）：资料全选——资料（数据）——资料剖析（分列）

2、快速选取资料：
Ctrl+键盘方向键：选取框移动到表格的四个角落
Ctrl+shift+键盘方向键：选取对应的范围

3、插入多个空白列
F4：重复前面的操作
选取想要插入的列数—鼠标右键——插入

4、储存格选取框
选取需要移动的单元格——鼠标移至单元格边框——拖动单元格
移动时按住Ctrl，复制内容
移动时按住shift，插入移动处，而不发生覆盖

5.移除重复资料
框选单元格——数据——删除重复值——勾选

6.表格转置
框选单元——复制——选择性粘贴——勾选转置

7.贴上运算值
输入操作值——框选单元格——选择性粘贴——运算处点选操作

8.显示公式
Ctrl + ~
公式——显示公式

9.目标搜寻
数据——模拟分析（假设分析）——单变量求解——填入对应值
注：目标单元格内一定要有公式

10.表格对角线
开始——边框——绘制边框——手动绘制所需边框
或右键——设置单元格格式——边框——选择所需边框

11.储存格内换行
ALT+enter

12.插入图片注解
选取单元格——右键——插入批注——删除注释框中的名字——在边框处点击右键——设置批注格式——颜色与线条——颜色——填充效果——图片——选择图片。
注：只有在边框处点击右键才会有颜色与线条选项

自订数值格式

1/2可能会误判为日期，所以填写【0 1/2】就不会误判了

在excel通用格式中0是没有用的，所以会自动去掉开头的0，要想解决这个问题，则在输入数值前输入单引号【'00001】标记为文字格式

自订数值格式：开始——数字右边的按钮——数字——自定义——类型下面的输入框（Ctrl+！）

#：一个位数的预留位置,遇到无意义的0将不会显示
？：遇到无意义的0将会空一格作为显示
0：强制显示每一个指定的位数
@：预留文字位置
*：重复*后面的符号
，=千
，，=百万
0.00;(0.00);0.00;@：正值；负值；零值；文字
_：预留一个_后面符号的宽度

>900.00;[>80]青色;0.00)
大于90的分数指定为绿色，大于80的分数指定为青色，其余的数值为预设的黑色（excel这个功能只能用两个条件，三个以上用格式化条件较好）

时间格式

在设定存储格格式中，在开头输入【DBNUM1】，即将阿拉伯数字转换为中文数字

aaa:以星期的方式显示
Ctrl+；：插入现在的日期
Ctrl+shift+；：插入现在的时间
Today（）：实时日期
Now（）：实时时间
ps:按F9或点击单元格时更新

时间计算时，Excel会自动忽略日期的差距，得出错误的结果，因此想要计算正确已经过的时间，自定义格式，【h】:mm：计算已经过的小时数，同理有【m】计算已经过的分钟数

两天之间的天数年数等：Datadif(开始日期，结束日期，计算单位）计算单位用”y”,”m”

自动排除周末跟假日：Networkdays（开始日期，结束日期，假日）

Networkdays进阶版，自己定义周末类型：Networkdays.intl(开始日期，结束日期，自定周末，假日)

计算成绩排名

RANK.EQ(主体，比较范围，排序方式)（2010后RANK的改良版，使用方法一样）排序方式为1时用递增排名

RANK.AVG()与RANK.EQ类似，只是当成绩相同时，RANK.AVG采用排名的平均值

储存格中获取文字资料

LEFT（资料位置，捉取字数）：捉取数据左侧的数据
RIGHT（资料位置，捉取字数）：捉取数据左侧的数据
MID（资料位置，开始位置，捉取字数）：捉取数据中间的数据
FIND（要搜寻的文字，资料来源，搜寻起点）：查找文字所在位置
LEN（资料）：计算储存格中的字数和空格
就是相当于Python里面截取字符串的功能

INDEX & MATCH

VLOOKUP函数有一个很大的缺点就是如果要搜寻的栏位不是在表格的最左侧，或是表格采用了横向排列的话，无用武之地。如果是横向表格，而要查询的资料行也恰巧是第一行的话，可以使用HLOOKUP

HOOKUP（被查询值，查询的范围，要传回的行数）

INDEX(资料范围，列数，栏数)
单行单列＝Index（栏/列范围，顺位）
多栏多列＝Index（资料范围，行数，列数）

MATCH(查找对象，查找范围，比对方式)
MATCH只能单行单列
比对方式：0为完全比对，1为小于，-1为大于

（INDEX是指定一个位置，传回位置的内容，MATCH是给一个内容，传回所在位置）

保护表格

可供修改的单元格：框选单元格——右键——设置单元格格式——保护——取消勾选锁定。

隐藏单元格公式：框选单元格——右键——设置单元格格式——保护——勾选隐藏

隐藏：选定行或列——右键——隐藏

允许编辑范围：审阅——允许编辑范围——新建——设定标题、范围、密码——确认密码——确认

保护工作表：审阅——保护工作表——输入密码——确定密码

允许用户编辑区域，就是分设权限的意思

Excel表设置密码：
1.文件——信息——保护工作簿——用密码进行加密——设定密码——确认密码
2.另存为——工具——常规选项——设定密码

重复资料解决

标记：框选单元格——开始——条件格式——新建规则——使用公式确定要设置格式的单元格——输入公式——修改后一参数值为相对参照——格式——填充——选择颜色——确定

删除：框选单元格——数据——删除重复值——勾选选项——确定

防止：选定行或列——数据——数据验证——允许中选定自定义——填入公式——确定

随机值

Randbetween(最小值，最大值)：随机产生一个整数
CHOOSE(答案，选项A，选项B...)：选择
RAND()：产生介于0到1的数

OFFSET

返回一个单元格：OFFSET(起点单元格，向下移动数量，向右移动数量）

返回多个单元格：OFFSET(起点单元格，向下移动数量，向右移动数量，向下选取的高度，向右选取的宽度）