学习资料链接:
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(早餐类型位置)】

微信图片_20230310010706.png


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
微信图片_20230310153248.png
HOOKUP(被查询值,查询的范围,要传回的行数)

INDEX(资料范围,列数,栏数)
单行单列=Index(栏/列范围,顺位)
多栏多列=Index(资料范围,行数,列数)

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

(INDEX是指定一个位置,传回位置的内容,MATCH是给一个内容,传回所在位置)

保护表格

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

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

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

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

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

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

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

重复资料解决

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

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

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

随机值

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

OFFSET

返回一个单元格:OFFSET(起点单元格,向下移动数量,向右移动数量)

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

最后编辑:2023年03月10日 ©著作权归作者所有