人力资源常用函数

EXCEL电子表格常用函数使用指南第一部分简单不要说明的函数SUM函数——求和AVERAGE函数——求平均值COUNT函数——计数函数MAX函数一一求最大值MIN函数一一求最小值第二部分较为复杂的函数ROUND函数这是四舍五入函数，用于保留几位小数。语法：ROUND(number,num_digits)Number为要进行四舍五入的数字。num_digits小数点后要保留的数字位数。如：ROUND(3.897677,3)计算3.897677的小数点后保留3位数字的值为3.898。RANK函数这是排位(名)函数,可用于成绩自动排名。语法:RANK(number,ref,order)Number 为需要找到排位的数字。Ref 为数字列表数组或对数字列表的引用。Ref中的非数值型参数将被忽略。Order 为一数字，指明排位的方式。如果order为0(零)或省略，MicrosoftExcel对数字的排位是基于ref为按照降序排列的列表。如果order不为零，MicrosoftExcel对数字的排位是基于ref为按照升序排列的列表。如:RANK(K2,K$2:K$90)计算K2单元格的数值在K2至K90单元格区域中按从大到小排序的位置，也就是第几名。注意，单元格区域的语法是K$2:K$90，如果写成K2:K90，则只能用于K2单元格排位，无法复制到其他单元格。COUNTIF函数计算区域中满足给定条件的单元格的个数。语法:COUNTIF(range,criteria)Range 为需要计算其中满足条件的单元格数目的单元格区域。Criteria 为确定哪些单元格将被计算在内的条件，其形式可以为数字、表达式或文本。如:COUNTIF(A2:A90,〃>=90〃)计算A2至A90单元格区域中大于90分的人数；同理，COUNTIF(A2:A90,〃>=80〃)-COUNTIF(A2:A90,〃>=90〃)为计算80-89分的人数。COUNTIF(A2:A90,〃本科〃)计算学历为本科的人数。SUMIF函数根据指定条件对若干单元格求和。语法：SUMIF(range,criteria,sum_range)Range为用于条件判断的单元格区域。Criteria为确定哪些单元格将被相加求和的条件，其形式可以为数字、表达式或文本。例如，条件可以表示为32、〃32〃、〃>32〃或"apples"oSum_range是需要求和的实际单元格。例如：=SUMIF(D4:D19,〃男〃,N4:N19) 这是计算男性年龄总和的公式。其中，口4:口19存放性别，N4:N19存放年龄。多条件求和的公式如果要统计“东北区”中“辽宁”的A产品业绩汇总，那么可以在C10单元格中输入如下公式：=SUM(IF($A$2:$A$7=〃东北区〃，IF($B$2:$B$7=〃辽宁〃,Sheet1!C$2:C$7)))o然后按下“Ctrl+Shift+Enter”键，则可看到公式最外层加了一对大括号(不可手工输入此括号)，同时，我们所需要的东北区辽宁组的A产品业绩和也在当前单元格得到了。SUMPRODUCT函数该函数在EXCEL定义中描述为在给定的几组数组中，将数组间对应的元素相乘，并返回乘积之和。这种描述给人的感觉似乎是对数组进行计算，对乘积汇总。但实际上它对于多条件求和方面的功能超乎人们的想象，特别是应用于人力资源方面统计更是超强，不仅能完成多条件的统计功能，而且人数统计和工资汇总统计都能实现，灵活应用可以取代COUNTIF()和SUMIF()，因此掌握该这个函数的使用方法，可以说完成任何统计报表的数据统计工作，都能做到游刃有余。该函数进行多条件计数统计时，如条件是“或者”关系。必须用+号连接判断条件，其公式形式如下：SUMPRODUCT(条件1+条件2+条件3…条件2该函数进行多条件计数统计时，如条件是“并列”关系，即同时满足。必须用*号连接判断条件，公式形式如下：SUMPRODUCT(条件1*条件2*条件3…条件N)例如=SUMPRODUCT((D4:D19=〃 男 〃)*(E4:E19二〃 科员〃)*(N4:N19>20))该函数进行多条件求和统计时，如条件同时成立。必须用*号连接判断条件，其公式形式如下：SUMPRODUCT((条件1*条件2*条件3…条件N*计算区域)例如：=SUMPRODUCT((D4:D19=〃男〃)*(E4:E19=〃科员〃)*N4:N19)IF函数执行真假值判断，根据逻辑计算的真假值，返回不同结果。可以使用函数IF对数值和公式进行条件检测。语法IF(logical_test,value_if_true,value_if_false)Logical_test表示计算结果为TRUE或FALSE的任意值或表达式。如，A10=100就是一个逻辑表达式，如果单元格A10中的值等于100，表达式即为TRUE，否则为FALSE。本参数可使用任何比较运算符(一个标记或符号，指定表达式内执行的计算的类型。有数学、比较、逻辑和引用运算符等。)。Value_if_truelogical_test为TRUE时返回的值。如，如果本参数为文本字符串“预算内”而且logical_test参数值为TRUE，则IF函数将显示文本“预算内”。如果logical_test为TRUE而value_if_true为空，则本参数返回0(零)。如果要显示TRUE，则请为本参数使用逻辑值TRUE°value_if_true也可以是其他公式。函数IF可以嵌套七层，用value_if_false及value_if_true参数可以构造复杂的检测条件。如对C2单元格的学生成绩区分A、B、C、D四个档次=IF(C2>=90,〃A〃，IF(C2>=80,〃B〃，IF(C2>=70,〃C〃，IF(C2>=60,〃D〃,〃E〃))))，AND函数所有参数的逻辑值为真时，返回TRUE；只要一个参数的逻辑值为假，即返回FLASEoAND(logical1,logical2,...)Logicall,logical2,...表示待检测的1到30个条件值，各条件值可为TRUE或FALSE。例如：员工生日提前1周提醒：（C2为当年生日）=IF(AND(C2-TODAY()<=7,C2-TODAY()>0),〃还有〃&C2-TODAY()&”到期〃,〃〃)Vlookup函数问题：如下图，已知表sheetl中的数据如下，如何在数据表二sheet2中如下引用：当学号随机出现的时候，如何在B列显示其对应的物理成绩?ABcDEF1专业岳文英语 物理2OD0B1529852320060-254998351ro420DBD356100506D520DBOJ形10179497062DDBD5&01027?4SSO72ODG06S21037547■JJ82DDG07o41047i我100920DfiOa66105T145.1J102006096810669如1201120061070107r431301220DB117210365I-20DE12T41096341Pied]丸-%=VLCCEUP JFIIOO,%,true)ABCDE F1手考t漳22OO601工I I32COB071DCaBO52OOfiCr3ED6jCOfiO*e720060690g2OO5OZ509300抽12DIQ2COGW110112WEI21501220061114013肘口nc首先我们介绍下使用的函数vlookup的几个参数，vlookup是判断引用数据的函数，它总共有四个参数，依次是：1、判断的条件2、跟踪数据的区域3、返回第几列的数据4、是否精确匹配根据问题的需求，这个公式应该是：=vlookup(a2,sheet1!$a$2:$f$100,6,true)详细说明一下在此vlookup函数例子中各个参数的使用说明：1、a2是判断的掉条件，也就是说如果sheet2表中a列对应的数据和sheet1表中的数据相同方能引用；2、sheet1!$a$2:$f$100是数据跟踪的区域，因为需要引用的数据在f列，所以跟踪的区域至少在f列，$是绝对引用；3、6这是返回什么数的列数，如上图的物理是第6列U，所以应该是6,如果要求英语的数值，那么此处应该是54、是否绝对引用，如果是就输入true如果是近似即可满足条件那么输入false(近似值主要用于带小数点的财务、运算等)5、vlookup是垂直方向的判断，如果是水平方向的判断可使用Hlookup函数。Hlookup函数水平方向的判断。Mid、Left、Right提取字符函数可以使用Mid、Left、Right等函数从长字符串内获取一部分字符。具体语法格式为LEFT函数：得到字符串左部指定个数的字符。MID函数：MID(text,start_num,num_chars)其中Text是包含要提取字符的文本串。Start_num是文本中要提取的第一个字符的位置。num_chars提取文本串长度。RIGHT函数：right函数的功能是从字符串右端取指定个数字符。比如，从字符串〃Thisisanapple.〃分别取出字符〃This"、〃apple〃、〃is〃的具体函数写法为。LEFT(〃Thisisanapple〃,4)=ThisRIGHT(〃Thisisanapple〃，5)=appleMID(〃Thisisanapple〃,6,2)=isDATEDIF函数Excel隐藏函数，在帮助和插入公式里面没有。简要说明： 返回两个日期之间的年\月\日间隔数DATEDIF(start_date,end_date,unit)Start_date为一个日期，它代表时间段内的第一个日期或起始日期。End_date为一个日期，它代表时间段内的最后一个日期或结束日期。Unit为所需信息的返回类型。Unit返回〃Y〃时间段中的整年数。“M〃时间段中的整月数。“D〃时间段中的天数。"MD"start_date与end_date日期中天数的差。忽略日期中的月和年。"YM"start_date与end_date日期中月数的差。忽略日期中的日和年。"YD"start_date与end_date日期中天数的差。忽略日期中的年。实例1：题目：计算出生日期为1973-4-1人的年龄公式：=DATEDIF("1973-4-1",TODAY(),"Y")结果：33简要说明当单位代码为"Y"时，计算结果是两个日期间隔的年数.实例2：题目：计算日期为1973-4-1和当前日期的间隔月份数.公式：=DATEDIF("1973-4-1",TODAY(),"M")结果：403简要说明当单位代码为"M"时，计算结果是两个日期间隔的月份数.实例3：题目：计算日期为1973-4-1和当前日期的间隔天数.公式：=DATEDIF(〃1973-4T〃，TODAY(),〃D〃)结果：12273简要说明当单位代码为〃D〃时，计算结果是两个日期间隔的天数.实例4：题目：计算日期为1973-4-1和当前日期的不计年数的间隔天数.公式：=DATEDIF(〃1973-4T〃,TODAY(),〃YD〃)结果：220简要说明当单位代码为"YD〃时，计算结果是两个日期间隔的天数.忽略年数差实例5：题目：计算日期为1973-4-1和当前日期的不计月份和年份的间隔天数.公式：=DATEDIF(〃1973-4T〃,TODAY(),〃MD〃)结果：6简要说明当单位代码为"MD〃时，计算结果是两个日期间隔的天数.忽略年数和月份之差5、实例6：题目：计算日期为1973-4-1和当前日期的不计年份的间隔月份数.公式：=DATEDIF（〃1973-4T〃，T0DAY（）,〃YM〃）结果：7简要说明当单位代码为"YM〃时，计算结果是两个日期间隔的月份数.不计相差年数实际问题解决办法：1、员工当年的生日：62存放出生日期）=DATE（YEAR（T0DAY（））,M0NTH（B2）,DAY（B2））2、员工生日当天提醒：=IF（M0NTH（B2）=M0NTH（N0W（））,IF（DAY（B2）=DAY（N0W（）），〃祝〃&A2&〃生日快乐！！！〃，〃〃），〃〃）A2为员工的姓名！3、员工生日提前1周提醒：（C2为当年生日）=IF（AND（C2-T0DAY（）＜=7,C2-T0DAY（）＞0）,〃还有〃&C2-T0DAY（）&〃到期〃,〃〃）4、试用期计算试用期到期时间：=DATE（YEAR（P3）,M0NTH（P3）+3,DAY（P3）-1）含义：“DATE（YEAR（）,M0NTH（）,DAY（））”显示指定日期；在这里我们假设试用期为3个月，我们需要在Q3单元格中输入上述公式，其中MONTH(P3)+3表示在此人入职时间月的基础上增加三个月。而DAY(P3)-1是根据劳动合同签订为整年正月而设置的。比如2005年11月6日到2006年11月5日为一个劳动合同签订期。5、劳动合同到期时间：=DATE(YEAR(P3)+1,MONTH(P3),DAY(P3)-1)我们同样采用上述函数的设置方法。这里我们假设劳动合同期限为1年，则我们需要设置成丫£人口^3)+1，另外这个数值依然以入职日期为计算根据，所以天数上还要设置成DAY(P3)-1的格式。6、续签合同到期时间：=DATE(YEAR(S3)+1,MONTH(S3),DAY(S3))这里需要注意的是续签合同计算是以前份合同签订到期日期为根据的，所以只在前一份合同到期时间的基础上增加1年即可，无需天数上减1。7、计算退休时间：如果C3是性别，I3是出生年月(1925年6月2日)^3是退休时间，公式为：R3=DATE(YEAR(I3)+IF(C3=〃男〃,60,55),MONTH(I3),DAY(I3))8、从身份证号自动填充性别、出生月日、年龄我们先对“性别”“出生年月”“年龄”进行函数设置。当我们输入某人身份证号码时，系统便会自动生成“性别”“出生年月”及“年龄”，这样就减少了我们录入的工作量。请分别选择性别、出生月日、年龄信息项单元格输入下列公式：(1)性别：=IF(MOD(IF(LEN(E3)=15,MID(E3,15,1),MID(E3,17,1)),2)=1,〃男〃,〃女〃)含义：“LEN(E3)=15"表示看E3中是否有15个字符；“MID(E3,15,1)”表示在E3中从第15位开始提取1位字符;“MOD(MID(),2)=1”表示提取的字符除以2余数为1;“IF(LEN(E3)=15,MID(E3,15,1),MID(E3,17,1)”表示看E3中是否够15个字符，如果够就从第15个字符开始取1个字符，如果不够15个字符就从第17个字符开始取1个字符。我们的身份证号码一般是15位或18位。“IF(MOD(IF(LEN(E3)=15,MID(E3,15,1),MID(E3,17,1)),2)=1,〃男〃,〃女〃”表示所取字符除以2如果余数为1显示男，否则显示女。简略的公式：=IF(MOD(MID(A1,15,3),2),"男"，"女")(2)出生年月：=DATE(MID(E3,7,4),MID(E3,11,2),MID(E3,13,2))含义：DATE(YEAR,MONTH,DAY);“MID(E3,7,4)”表示在E3中从第7个字符开始连续取4个字符表示年，用类似的表示方法一个人的出生年月日便可以通过函数设置表示出来，如果为了看起来方便，我们可以将单元格格式设置成年、月、日的日期格式，这样显示的结果会非常容易理解。如果其中一些身份证是15位的，则用下列公式：=IF(LEN(C6)=15,〃19〃&MID(C6,7,2)&〃-〃&MID(C6,9,2)&〃-〃&MID(C6,11,2),MID(C6,7,4)&〃-〃&MID(C6,11,2)&〃-〃&MID(C6,13,2))(3)年龄:=DATEDIF(G3,TODAY(),"Y")含义：“DATEDIF(date1,date2,“Y")”表示两个