|
小伙伴们好啊，今天咱们说说函数公式的内容。 单个的函数作用和功能都是比较单一的，在解决实际问题时，往往需要多个函数嵌套使用，今天就和大家分享几个常用的函数嵌套组合。熟悉这些嵌套函数的使用，提高效率一丢丢。 1、判断数据是否重复 如下图，要统计B列的姓名是否为重复出现。 C2使用的公式为： =IF(COUNTIF($B$2:B2,B2)>1,'重复','') COUNTIF函数使用动态扩展区域$B$2:B2作为统计范围，计算B列员工姓名在这个区域中出现的次数，如果出现的次数大于1，就是重复。 以B2为例，北**子首次出现，C3单元格中的公式为： =IF(COUNTIF($B$2:B3,B3)>1,'重复','') 结果就是1，也就是不重复了。 而到了C9单元格，公式为： =IF(COUNTIF($B$2:B9,B9)>1,'重复','') 在$B$2:B9这个区域中，B9单元格的北**子出现了两次。所以$B$2:B3,B3)>1的条件成立，也就是说B9是重复出现的。 2、 查询搭档 如下图所示，要根据D3单元格中的职务，在A~B列查询对应的姓名。 E3单元格公式为： =INDEX(A:A,MATCH(D3,B:B,0)) 先使用MATCH函数，查找D3单元格的“秘书”在B列中所处的位置，得到结果为5。然后使用INDEX函数，在A列中返回第5个元素的内容，结果就是秘书对应的姓名了。 3、指定条件的最小值 如下图所示，要计算生产部的最低分数。 G3单元格使用以下数组公式，按Shift+ctrl+回车： =MIN(IF(A2:A9=F3,D2:D9)) 先用IF函数判断A列的部门是否等于F3指定的部门，如果条件成立，则返回D列对应的分数，否则返回逻辑值FALSE： {FALSE;45;FALSE;FALSE;FALSE;66;FALSE;72} 接下来再使用MIN函数计算出其中的最小值。 MIN函数有一个特性，就是可以自动忽略逻辑值，所以只会对数值部分计算，最终得到指定部门的最低分数。 4、 计算年龄 如下图，要根据员工出生年月，计算年龄。公式为： =DATEDIF(B2,TODAY(),'y') TODAY函数返回系统当前的日期。 DATEDIF函数用于计算两个日期之间的间隔。用法为： DATEDIF(起始日,截止日,间隔的类型) DATEDIF函数以B2的出生年月作为开始日期，以系统日期作为结束日期，第三参数使用“Y”，表示计算两个日期之间的整年数。 如果要计算间隔的月数，第三参数可以使用“m”。 5、筛选后求和 SUBTOTAL函数能够仅对可见单元格汇总计算，能够计算在筛选状态下的求和。 如下图，对B列的部门进行了筛选，使用以下公式可以计算出筛选后的数量之和。 =SUBTOTAL(9,D2:D14) SUBTOTAL第一参数用于指定汇总方式，可以是1~11的数值，通过指定不同的第一参数，可以实现平均值、求和、最大、最小、计数等多种计算方式。 如果第一参数使用101~111，还可以忽略手工隐藏行的数据，小伙伴们有空可以试试。 6、混合内容提取 如下图，要从A列姓名电话中提取出姓名，除了使用高版本的自动填充功能，通常会使用以下公式完成： =LEFT(A2,LENB(A2)-LEN(A2)) LENB函数将每个汉字（双字节字符）的字符数按2计数，LEN函数则对所有的字符都按1计数。因此“LENB(A2)-LEN(A2)”返回的结果就是文本字符串中的汉字个数。 LEFT函数从文本字符串的第一个字符开始，返回指定个数的字符，最终提取出员工姓名。 7、合并带格式的内容 合并带有格式的内容时，Excel默认按常规格式进行合并，但是如果是日期、时间或是其他有格式的数值，结果就会让人大失所望了。如何才能正确连接出需要的字符串呢？其实很简单： 首先使用TEXT函数，把B列的日期变成具有特定样式的字符串，然后再与A列的姓名连接，就变成了最终需要的样式。 Excel中的函数就像是一个万花筒，千变万化的组合有太多太多，今天就和大家分享这些，咱们一起加油吧~~ |
|
|
