0、excel常用快捷键
- 基础快捷键:
alt+=:快速区域求和;
★ alt+enter:强制换行(因为在excel单元格中没法用enter换行);alt+j:强制换行符的替换删除,这里alt+j就是在替换中输入强制换行符的快捷键;应用场景:有时候有的数据用公式的时候会出现报错,这个时候可以思考是不是这些数据当中存在强制换行符号,就可以用用上述方法把强制换行符删除掉。
★ alt+;:选中可见单元格(也可以通过ctrl+g定位到可见单元格);应用场景:有时候筛选的话会把一些单元格隐藏起来,这时候你复制一个区域内容,粘贴过去的时候,会把隐藏起来的也一块复制过去。
★ ctrl+enter:批量填充,搭配ctrl+g定位到空白单元格实现批量填充;应用场景:对选中的一批单元格填充同一个数据,就可以用该方法。
★ ctrl+G:定位条件很重要,可以通过该操作快速定位一些特殊单元格,比如空值或者可见单元格。
★ ctrl+shift+箭头/shift+箭头/ctrl+箭头:选中单元格区域以及跳转单元格区域;
ctrl+[:跳转公式左边第一个引用的单元格;
★ ctrl+\:定位行内容差异单元格;应用场景:对比两列数据中对应位置数据不一致的情况,把不一致内容定位出来。
★ ctrl+z、ctrl+y:撤销和反撤销;
ctrl+a:全选单元格;
ctrl+n:新建单元格;
★ ctrl+~:显示公式;应用场景:有时候需要大面积复制公式,就可以用这个方法先显示公式,然后再复制公式。
★ F2:进入选中单元格进行编辑;应用场景:如果编辑公式,可以按下F2,避免左右键按下的时候,切换单元格。
★ alt+下:如果输入函数的时候没有提示可以按,在出现提示函数后,可以选你需要的,然后按tab键补全函数。
★ ctrl+1:设置单元格格式;
★ ctrl+shift+1/2/3/4/5:设置单元格内容为数值、时间、日期、货币、百分数;
★ ctrl+shift+L:筛选快捷键
★ ctrl+shift+加号:插入单元格;
win+D:快速返回桌面;
★ win+E:快速调出资源管理器;应用场景:比如找不到我的电脑图标的时候,就可以用这个。
★ ctrl+alt+v:选择性粘贴;
★ ctrl+alt+v:选择性粘贴;
★ F2:编辑单元格;
★ F9:查看单元格当中公式的运算结果; - 小知识1:在excel文件的选项中有保存设置,可以设置自动保存时间,还可以设置自动保存文件的存储位置。
- 小知识2:较低版本的excel在较高版本excel中打开修改后,要保存的话,会弹出提示框,点击继续才能保存,这种情况,可以先将低版本excel保存为高版本的excel文件(后缀是xlsx),在打开编辑。
- 小知识3:通过双击菜单栏标题,比如“视图”,就可以显示菜单栏或者隐藏菜单栏。
- ★ 小知识4:通过excel界面左上角,下箭头可以调出“自定义快速访问工具栏”。(也可以通过文件选项去找这个功能)然后就能往左上角添加功能了,如下我添加了粘贴为值的功能。然后平时用的时候,就可以通过alt箭调用这些功能了。
- ★ 注意:excel是可以全键盘操作的,精髓就是alt键。
- ★★ 魔法填充:ctrl+E。
1、逻辑相关函数:
-
注意:在写函数的时候特殊字符、汉字、字母都是需要双引号包裹的。
-
注意:excel中写函数需要跨表引用的时候就会自动补充表名加感叹号,但是在当前表中就不用表名加感叹号说明了。
-
★ IF() / IFS() / IFERROR():条件判断函数
-
AND():逻辑与
-
OR():逻辑或
-
NOT(): 逻辑非
2、文本相关函数:
-
LEN():文本长度
-
★ MID():文本截取
-
LEFT():从左边截取
-
RIGHT():从右边截取
-
★ TEXT():数字转化为文本,注意TEXT的第一个参数对应单元格中只能是数字,该公式才有效,第二个参数中的0就是占位格。
应用场景:如果提取到身份证上那一串代表出生年月的数字,就要将其通过TEXT函数转换为excel可以识别的日期格式0000-00-00,通过TEXT转换之后还是文本型数据,就要通过给文本数据乘以1的方式转换为数值,然后才可以将数值型数据转换为日期型数据。(注意:只有数值型数据才能转换为日期型数据) -
FIND():查找某个文本在某个查找源的位置
-
REPLACE():从指定单元格中,指定开始替换位置,替换指定长度的内容。
-
★ SUBSTITUTE():从指定单元格,指定替换内容,可以选择默认全部用新内容替换,也可以选择替换第几个查找到的替换内容,如下是替换了第2个替换内容。
3、日期和时间相关函数:
-
YEAR():返回指定日期内容当中的年。
-
MONTH():和上面类似,返回指定日期的月。注意,EOMONTH()函数是获取一个月最后一天的函数。在单元格中输入公式 =EOMONTH(“2021/1/3”, 5),你可以得到 2021/1/30 向后推移 5 个月后,所在月份的最后一天是 2021/06/30。如果不偏移,把5写成0,结果就是该月最后一天日期,这个函数可以理解为end of month。
-
DAY():和上面类似,返回指定日期的天。
-
TODAY():返回当前日期。
-
NOW():返回当前日期,有的版本可以返回当前时间。
-
DATE():把年月日汇总在一起。
-
EDATE():日期的月份偏移,从指定日期增加多少月或者减少多少月。
-
★ DATEDIF():求时间差,注意,这个函数是一个隐藏函数,不会给提示。
4、查找与引用相关函数:
-
★ VLOOKUP():以表格中某一列作为查找对比项,把符合条件的列与查找项对应的值返回。本质说就是把你需要的数据中某行对应的某一列数据拿出来。V查找就是从首列往下找对应值,返回该值的某一列对应的数据。
-
★ HLOOKUP():以表中某一行作为对比项,把符合条件的行与查找项对应的值返回。本质说就是把你需要的数据中某列对应的某一行数据拿出来。H查找就是从首行往右找对应值,返回该值的某一行对应的数据。
-
对比V查找和H查找的区别:
-
★ MATCH():查找指定值所在位置(编号),一般是与INDEX()组合使用的。
应用场景:对一个表格做一个查询定位功能,通过行和列,定位显示对应内容。
- INDIRECT():返回由字符串指定的引用。
- ROW():返回当前单元格行号,配合其他函数使用。
- COLUMN():返回当前单元格在第几列,因为excel中是通过ABC~来标记列号的。
5、数学函数:
- INT():取整
- MOD():求余数
- ROUND():四舍五入
- ABS():求绝对值
- SQRT():求平方根
- RAND():生成随机数
- RANDBETWEEN():生成指定范围的随机数
- ROUNDUP():向上取整,其中第二个参数是要保留的小数位数。
6、统计类函数:
- SUM():求和。
- MAX():求最大值。
- MIN():求最小值。
- ACERAGE():求平均值。
- COUNT():对数字类型单元格计数。
- COUNTA():对非空单元格计数。
- COUNTIF()、COUNTIFS():单条件计数、多条件计数。应用场景:统计某一项在某个区域出现了多少次。
-
SUMIF()、SUMIFS():单条件求和、多条件求和。应用场景:需要统计表中某一项对应的数值之和。
-
AVERAGEIF()、AVERAGEIFS():单条件平均值、多条件平均值。
-
★★★ FREQUENCY():频数统计函数。它是以数组作为参数的公式。通过一个公式执行多个输入操作,可能返回多个结果值。写这个公式时,要选中多个单元格,写完之后,调用的时候要按住ctrl+shift+enter的组合键。
7、★★★ 数组公式:
- 有时候需要把两列单元格对应相乘,这时候就可以用数组公式提高运算效率,数组公式还可以通过一个公式执行多个输入操作。对数组公式的理解就是你始终在拿着一堆数据在做运算,每次运算的结果也是一堆。
- 通过数组公式快速填充:
- 通过数组公式快速多条件运算:
- 在数组公式中用(*)表示且,用(+)表示或。
- 通过数组公式条件求和
总结:
- 函数部分要勤看,因为这里总结的excel的内部函数,全面肯定做不到,因为excel自己会更新,会添加新的函数,同时很多人用WPS比较多,WPS中可能没有一些相关函数。