学习资料链接:
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有点像
任务:制作下拉选单
对应单元格——资料——资料验证按钮——资料类型选清单——来源指定中式西式两个标题——框选餐点所在表格——公式——从选取范围建立——餐点单元格——资料——资料类型选清单——来源【INDIRECT(早餐类型位置)】
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(起点单元格,向下移动数量,向右移动数量,向下选取的高度,向右选取的宽度)