目录

  • 一、前言
  • 二、函数和公式
  • 2.1、判断函数使用
  • 2.2、字母的大小写
  • 2.3、比对对比函数
  • 每列最后非空单元格
  • 单元格内容比对处理
  • Office 2021中新增函数
  • 2.4、数字处理函数
  • 小数随机数
  • Office 2021新函数
  • 2.5、提取指定字符
  • 三、操作和技巧
  • 3.1、条件规则动态底色
  • 3.2、动态改变数据表
  • 四、自定义其他


二、函数和公式

2.1、判断函数使用

  1. 是否为空单元格：
    =IF(ISBLANK(A1),"是空单元格","单元格内有内容")
  2. 判断是否是数字：
    =IF(ISNUMBER(A1),"是数字","不是数字")
  3. 判断是否包含数字【通过Ctrl+Shift+Enter将其转为数组公式方可见效】：
    =IF(COUNT(FIND(CHAR(ROW($48:$57)),A1))>=1,"是","单元格内没有数字")
  4. 判断是否包含大小写字母【通过Ctrl+Shift+Enter将其转为数组公式方可见效】：
    =IF(COUNT(FIND(CHAR(ROW($65:$122)),A1))>=1,"是","单元格内没有大小写字母")

2.2、字母的大小写

  • 转换为大写字母：
    =UPPER(A1)
  • 转换为小写字母：
    =LOWER(A1)
  • 转换首字母大写：
    =PROPER(A1)

2.3、比对对比函数

每列最后非空单元格

  1. 获取A列最后一个非空单元格【数字】：
    =LOOKUP(9E+307,A:A)
  2. 获取A列最后一个非空单元格【字母】：
    =LOOKUP(CHAR(65535),A:A)
  3. 获取A列最后一个非空单元格【全部】：
    =LOOKUP(1,0/(A:A<>""),A:A)

单元格内容比对处理

  1. 将A1单元格值在B列中精确比对：
    =VLOOKUP(A1,B:B,1,FALSE)

Office 2021中新增函数

XLOOKUP函数：Office 2021中新增的逐行查找表或区域中的单元格值。使用这个函数可以在一列中查找搜索词，并从另一列中的同一行返回结果，而不管返回列位于哪一边，可以看作是HLOOKUP和VLOOKUP两个函数的合并升级版。

  • =XLOOKUP(A1, B:B,C:C,"未找到符合条件的值",0) ：从B列中查找A1的值，并获取符合条件的同一行的C列对应行的单元格值，如果找不到有效的匹配项，则显示“未找到符合条件的值”，0则是完全匹配(默认为0可忽略不写)。

2.4、数字处理函数

小数随机数

  1. 指定两个数之间的随机数（保留两位小数）：
    =ROUND(RAND()*(B1-A1)+A1,2)=RANDBETWEEN((A1*100),(B1*100))/100
  2. 累加A列中所有数字（即排除非数字）：
    =SUMIF(A:A,"<9E+307")

Office 2021新函数

Office 2021新增了SUMIFS、COUNTIFS以及AVERAGEIFS等计算同时判断的函数。

2.5、提取指定字符

  1. 提取单元格左侧数字：
    =LEFT(A1,2*LEN(A1)-LENB(A1))
  2. 提取单元格右侧数字：
    =RIGHT(A1,2*LEN(A1)-LENB(A1))
  3. 提取单元格内字母或数字（不支持混合隔开的内容）：
    =MIDB(A1,SEARCHB("?",A1),2*LEN(A1)-LENB(A1))
  4. 提取单元格内所有数字【通过Ctrl+Shift+Enter将其转为数组公式方可见效】（缺陷：会忽略小数点及负号等所有字符）：
    =SUM(MID(0&A1,LARGE(ISNUMBER(--MID(A1,ROW($1:$1024),1))*ROW($1:$1024),ROW($1:$308))+1,1)*10^ROW($1:$308)/10)

从Excel 2013开始，新增了一个快速填充快捷键：Ctrl+E！Excel 2019中更是将其优化完善了下，通过它可以一键快速按照已手动提取的模糊规则提取出单元格内容，因此也并不能100%完美处理所有的内容，具体参见官网：使用Excel中的“快速填充”

三、操作和技巧

3.1、条件规则动态底色

  1. 借助序列下拉选则实现条件规则动态改变底色，需要先将目标单元格设置为下拉序列：选中某列单元格数据，点击数据 → 数据验证 → 数据验证后，在设置中允许选择序列选项，来源可以选择一列非重复数据的单元格，也可以手动使用“,”间隔数据，例如：“选项一,选项二,选项三”，如下图：
  2. 选中已设置了数据验证的单元格，点击开始 → 条件格式 → 新建规则并选择使用公式确定要设置格式的单元格这条规则，如下图：
  3. 如上图输入=IF($A1="选项一",TRUE,FALSE)公式，即当第一步的下拉选择框选择选项一时，需要设置的格式，点击确定后，该规则会默认应用于当前所选单元格；
  4. 需要应用于指定范围单元格，请选择条件格式下的管理规则菜单，如下图：
  5. 默认应用于当前单元格，即$A$1单元格，此时可手动选择指定范围单元格，或者手动输入指定范围单元格，比如输入=$A$1:$G$1范围，即当A1单元格选择选项一时第一行从A到G底色变成红色，如下图：

注意：条件格式规则的底色将不受单元格背景色控制，即当存在条件格式规则设置的背景色底色时，修改此该范围内的单元格背景色将不生效！

3.2、动态改变数据表

##################################################