将Excel部分知识记录下来,便于后续查阅。
一、基础操作
1.跳转(Ctrl+方向键):快速移动到数据的末端
2.选定(Shift+点击选中):多选数据
常见于选择指定数据进行条件格式渲染,且配合跳转操作(Ctrl+Shift+方向键)选中列或者行
3.定位(Ctrl+G):根据选中条件选择离散单元格
可用于空值或者错误值的替换:复制待替换值,选中替换区域执行定位操作,选出数据后粘贴
4.自动填充:选中需要拓展的区域的右下角,当出现+时进行拖拽
自动化处理,有一定智能性。在函数批量化处理中常见
5.查找替换(Ctrl+F):通配符(*表示多个,?表示一个)
注:在sql中"_"表示一个,"%"表示多个
二、常见标签的使用
1.数据透视(插入-数据透视表)
应用于初次查看数据,结果判断数据类型,或者进行简单表格构建
2.冻结窗口(视图-冻结窗口)
进行跨表编写函数时,不至于晃眼睛
3.迷你图(视图-迷你图)
呈现单元格内部的简单统计图
三.常见函数的使用
通常只用编写一个函数,然后使用自动填充进行批量化处理。这时候就需要使用$符号将需要计算的数据位置固定住,比如A$1表示锁住1行,允许A列移动
1.判断语句:IF(判断条件,为真的结果,为假的结果)
常见于套娃操作,比如:
=IF($E1>=18,IF($E1>=30,"老年","青年"),"未成年")
2.单筛选条件求和:SUMIF(待筛选区域,选择值,求和区域)
#对应人数为20的收入之和
=SUMIF(A2:A6,20,B2:B6)
3.多筛选条件求和:SUMIFS(求和区域,筛选1,条件1,筛选2,条件2…)
#时间在8/1到8/11之间的总和
=SUMIFS(B9:B22,A9:A22,">="&A12,A9:A22,"<="&A22)
4.超实用查询函数:VLOOKUP(待查对象,对象列和值列的区域,值列所在列的序列号,精确查找)
#最后一个参数默认为0,进行精确查找
#查找a对应的值
=VLOOKUP(I100&"*",F96:G103,2,0)
#查找b开头并且是三个字符所对应的数值
=VLOOKUP(I103&"??",F95:G103,2,0)
5.INDEX与MATCH函数的结合
用于EXCEL表的即时改变,常见于报表的制作
#match函数返回待查值的序列。最后一个参数默认为0,进行精确查找
=MATCH(待查值,待查值所在的行/列,0)
#index()返回对应位置的值。当第二个参数为0时,返回整列
=INDEX(待查区域,行序列,列序列)
结合上述两个函数的结合,可以实现根据表格的变化而自动改变值
#第一个match用来寻找行,第二个match用来寻找列
=INDEX(数据区,MATCH(行待查值,值所在列,0),MATCH(列待查值,列所在行,0))
# 第一列(想象为name) 表头
6.编写函数中遇到问题的处理
1.出现"#name?"则说明函数中出现了中文符号,多注意一下引号
2.直接报错输入的不是函数,则看看括号是否对称