1要从身份证号码中得到出生日期,这种问题对于从事人资行政岗位的小伙伴一定不陌生,公式也比较简单:=TEXT(MID(A2,7,8),"0-00-00")就能得到所需结果
这个公式中涉及到两个函数,首先来看MID函数,MID函数有三个参数,格式为:=MID(在哪提取,从第几个字开始取,取几个字)。 MID(A2,7,8)表示从A2单元格的第七个数字开始截取八位
出生日期提取出来后却不是我们需要的效果,这时候就该函数魔术师TEXT出马了,TEXT函数只有两个参数,格式为=TEXT(要处理的内容,“以什么格式显示”),本例中要处理的内容就是MID函数这部分,显示格式为"0-00-00",当然你要用"0年00月00日"这个格式显示也没问题,公式改为=TEXT(MID(A2,7,8),"0年00月00日")就可以了
2这里用到了一个Excel的隐藏函数DATEDIF,函数需要三个参数,基本结构为=DATEDIF(起始日期,截止日期,计算方式)。 本例中的起始日期就是出生日期,用B2作为第一参数;截止日期是今天,用TODAY()函数作为第二参数;计算方式为按年计算,用"Y" 作为第三参数。 如果需要直接从身份证号码计算年龄的话,公式可以写为: =DATEDIF(TEXT(MID(A2,7,8),"0-00-00"),TODAY(),"Y")
这类问题多见于绩效考核,例如公司对员工进行了绩效考核,需要按照考核成绩确定奖励级别,定级规则为:50分以下为E,50-65(含)为D,65-75(含)为C,75-90(含)为B,90以上为A。 可以使用公式=LOOKUP(E2,{0;50;65;75;90},{"E";"D";"C";"B";"A"})得到每个员工的奖励级别,要解释这个公式的原理就费劲了,可以参考之前的LOOKUP函数相关教程。 其实要解决这类问题记住套路就够了:LOOKUP按区间返回对应结果的套路为=LOOKUP(成绩,{下限值列表},{奖励级别列表}),下限值之间用分号隔开,奖励级别之间同样用分号隔开。 也可以将成绩下限与奖励级别的对应关系录入在表格里,公式可以修改为=LOOKUP(E2,$I$2:$J$6),结果如图所示。
3VLOOKUP函数的基本结构为=VLOOKUP(找什么,在哪找,第几列,怎么找),例如按照姓名找最高学历,可以使用公式=VLOOKUP(G2,B:E,4,0)得到所需结果,如图所示:使用这个函数有两个要点一定要知道: ①要找的内容必须在查找范围的首列,例如按姓名查找时,查找范围是从B列开始而不是A列。 ②第几列指的是查找范围的列而不是表格中的列,例如要找最高学历,在查找范围的第4列,而不是表格中的列数5。 使用LOOKUP函数进行多条件匹配的套路为:=LOOKUP(1,0/((查找范围1=查找值1)*(查找范围2=查找值2)*……*(查找范围n=查找值n)),结果范围),需要注意的是多个查找条件之间是相乘的关系,同时它们需要放在同一组括号中作为0/的分母。
4条件计数需要用到COUNTIF函数,函数结构为=COUNTIF(统计区域,条件),在本例第一个公式=COUNTIF(B:B,G2)中,B:B就是统计区域,G2是条件,公式结果表示B列中为“女”的数据有14个。
第二参数条件可以不使用单元格引用,直接用具体内容作为条件,当条件为文本时,需要在条件两边添加英文状态的双引号,比如第二个公式=COUNTIF(B:B,"女")就是如此。
首先使用公式=COUNTIF(A:A,A2)计算出每个姓名出现的次数,当结果大于1就表示姓名重复,进而使用IF函数得到最终的结果。
公式为:=IF(COUNTIF(A:A,A2)=1,"","重复")
判断姓名是否重复还有一种情况:第一次出现不算重复,从第二次起才算重复。
遇到这种情况,只需要修改COUNTIF函数的条件区域即可,公式为:=IF(COUNTIF($A$1:A2,A2)=1,"","重复")
SUMIFS函数的结构为=SUMIFS(求和区域,条件区域1,条件1,条件区域2,条件2,……),在本例中,求和区域是D列的销售数量,第一个条件区域是B列的销售人员,第二个条件区域是C列的商品名称,因此最终的公式为:
=SUMIFS(D:D,B:B,"沈伊杰",C:C,"壁挂空调")
需要提醒一点的是SUMIFS求和区域的位置与SUMIF不同,SUMIFS的求和区域在第一参数,而SUMIF的求和区域在第三个参数,千万不要搞混了啊!