Excel重新踩坑4：快捷键；逻辑函数；文本函数；日期相关函数；查找与引用函数；统计类函数；数组公式

0、excel常用快捷键

• 基础快捷键：
  alt+=：快速区域求和；
  ★ alt+enter：强制换行（因为在excel单元格中没法用enter换行）；alt+j：强制换行符的替换删除，这里alt+j就是在替换中输入强制换行符的快捷键；应用场景：有时候有的数据用公式的时候会出现报错，这个时候可以思考是不是这些数据当中存在强制换行符号，就可以用用上述方法把强制换行符删除掉。
  ★ alt+；：选中可见单元格（也可以通过ctrl+g定位到可见单元格）；应用场景：有时候筛选的话会把一些单元格隐藏起来，这时候你复制一个区域内容，粘贴过去的时候，会把隐藏起来的也一块复制过去。
  ★ ctrl+enter：批量填充，搭配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()：返回单元格在第几列。

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中可能没有一些相关函数。