Excel常用功能（持续更新）

1:同时按住电脑键盘上的开始图标和R
2:调出运行框,在输入框中输入“osk”,回车即可直接调出全键盘

• VLOOKUP显示为#N/A（查找不到）修改为空
  1.iferror(vlookup(), “”)
  2.if(iserror(vlookup()), “”, vlookup() )
  3.if(isna(vlookup()), “”, vlookup() )
• VLOOKUP显示为0（查找到空）修改为空
  vlookup()&“”
• VLOOKUP显示为#N/A与0全部修改为空
  if(iserror(vlookup()), “”, vlookup()&“”)
• 删除公式保留计算后的值
  复制 -> 粘贴为数值
• 删除重复项
  数据 -> 删除重复项
• 删除空行
  去除重复项，再删除一行空行即可
• 转换大小写
  转大写：函数upper
  转小写：函数lower
• 返回数据不为空的个数
  counta返回范围内非空单元格个数
• 返回数据为空的个数
  countblank返回范围内空单元格个数
• 返回数据为数字的个数
  count返回范围内空单元格个数
• 固定范围不变
  用$符号，例如A2:A5改为A$2:A$5
• 日期格式vlookup后变为5位数字（数字表示的是从1900年1月1日以来的天数）
  text函数转化为日期，text(a1,“yyyy-mm-dd”)
  text函数转化为日期，text(a1,“yyyymmdd”)
• yyyymmdd字符串转换成yyyy-mm-dd的字符串
  text(a1,“0000-00-00”)
• 返回指定个数字符
  从左返回10个字符，left(a1,10)
  从右返回10个字符，right(a1,10)
• csv格式直接打开变成科学计数法
  记事本打开查看数据是否正常，Excel导入数据，选定分隔符
• 文本合并
  1.用&合并：a1&b1
  2.用concat()函数
• 文本截取
  mid(单元格，起始位置，截取的位数)
• 选中有内容的单元格
  ctrl + shift + ⬆️/⬇️/⬅️/➡️
• 只粘贴可见单元格
  alt + ; 选中可见单元格，然后ctrl + c，ctrl + v
• 判断一个数据是否在一列数据中
  countif(某几列,一个数据)
• 行数/列数
  行数：row()
  列数：column()
• match函数
  match(查找值，某列，0)返回 查找值 在 某列 内的第几行
  match(查找值，某行，0)返回 查找值 在 某行 内的第几列
• 计算年龄
  DATEDIF(开始日期，TODAY(),“Y”)
  “Y”，“M”，"D"分别返回年，月，日
• 满足单一条件的数据求和
  SUMIF(某一列，x，求和区域)
  可将某一列等于x对应求和区域的值相加
  SUMIF(某一列，“*B*”，求和区域)
  可将某一列包含B的对应求和区域的值相加
  SUMIF(某一列，“<>”，求和区域)
  可将某一列非空单元格对应求和区域的值相加
  SUMIF(某一列，“>5”，求和区域)
  可将某一列大于5的单元格对应的求和区域值相加
  SUMIF(某一列，“<>5”，求和区域)
  可将某一列不等于5对应的求和区域相加
  SUMIF(某一列，“>”&B1，求和区域)
  可将某一列大于B1对应的求和区域相加
• 满足多个条件的数据求和
  SUM(SUMIF(某一列，{“A”,“B”,“C”}，求和区域))
  可将某一列等于A或B或C对应的求和区域的值相加
  SUM(SUMIFS(求和区域，列1，条件1，列2，条件2))
  将列1满足条件1且列2满足条件2的求和区域的值相加
  求和区域用offset可以选择多列：offset(参照列，，{1,2})即为参照列右边两列
• 转置操作
  复制-选择性粘贴-转置
• 两列互换位置
  按住shift选中某列的边然后移动（不按shift是直接替换）
• 展示数组
  选中数组相应的单元格数（几行几列），按F2输入公式，Ctrl+Shift+Enter即可
• OFFSET函数
  OFFSET(原始区，偏移几行(正下负上)，偏移几列(正右负左)，扩展为几行，扩展为几列)，返回的是一个数组
  结合其他函数可实现动态单元格，图表的动态变化，参考http://xinzhi.wenda.so.com/m/a/1521424221207903
• 图表只显示最大/最小值等的数据标签
  需要一个辅助列并且加入图表中。例如B列为数据列，则在C列辅助列公式为：C2=if(B2=max(数据列),B2,na())这样辅助列只会在最大值的位置显示数字，其他单元格显示错误值。在图表中加入辅助列后，只会显示最大值的数据点，可以根据需要调整数据点显示样式、数据标志等。
• 判断日期是星期几
  weekday(日期，2)
• 取日期对应的上月末，上月初日期
  上月初：date(year(日期)，month(日期)-1，1)
  上月末：date(year(日期)，month(日期)，0)
  本月初：date(year(日期)，month(日期)，1)
  本月末：date(year(日期)，month(日期)+1，0)
• 公式转换为文本
  ctrl+~可以显示为公式，然后可以进行批量替换公式中某部分的内容
• 选中单元格行列变色
  视图-阅读模式
• vlookup查找不到的原因大部分是因为格式不一样
  用 数值转文本 或 文本转数字 以后再查找
• 数值 转 文本
  单元格&“”
• 文本 转 数字
  value(单元格)
  单元格*1
  单元格+0
• countif函数只会判断前15位，因此会出现countif有vlookup不到的情况
  countif(区域，单元格&“*”)
• 统计某列
  countif(区域，单元格&“*”)
• 四舍五入保留两位小数
  round(单元格，2)
• 直接截取两位小数
  trunc(单元格，2)
• 分组求最大值/最小值
  MAXIFS(最大值/最小值区域，列1，条件1)
• 取商
  QUOTIENT(被除数,除数)
• 取余数
  MOD(被除数,除数)
• 重复字符串
  REPT(字符串,重复次数)
• 数字左补0补齐到6位
  TEXT(单元格,“000000”)
  TEXT(单元格,REPT(0,6))