人力资源相关EXCEL公式汇总

Excel强大的功能，你知多少？,r'z$C%n1|X'P4C1n1[,C5b-g.m-T0v,R:z2iMicrosoftExcel是微软公司的办公软件Microsoftoffice的组件之一，Excel中大量的公式、函数、工具可以选择应用，可以实现许多的功能，给使用者提供了极大的方便。很难想象如果没有它，我们现在工作会处于什么状态。本人在工作过程中，收集了一些方法和技巧，现分享给大家，欢迎补充！一、合同到期提醒.r,v8q'`+t;r假如A1单元格显示签订合同时间2005-11-051]%A,Z8@$M3M8O;k9LB1单元格显示合同到期时间2007-10-319N,iV2F!_f.K6w3r;v当前时间由系统提取出来假设当前日期为2007-10-15n6a2U9H(J$C+e:I)q想让C1单元格显示:在合同即将到期30天内提醒:合同即将到期,且每日提醒最好弹出提醒对话框,点击确定当日不再提醒,点击取消当日在一定时间内继续提醒/f8S&KAB(N:}8Q6M(I#zR=IF(ISERROR(DATEDIF(TODAY(),B2,D)),已过期,IF(DATEDIF(TODAY(),B2,D)30,未到期,IF(DATEDIF(TODAY(),B2,D)=30,今天到期,差&DATEDIF(TODAY(),B2,D)&天到期)差多少天到期的合同如何让反应的文字以红色字体体现出来,这样好统计*qu3{(c%]$AM$?选中C列,格式条件格式公式=AND(DATEDIF(TODAY(),B2,D)=30,DATEDIF(TODAY(),B2,D)0),再点击格式在单元格格式对话框中选图案选红色.确定。6Y2^v,C/O;S.e*r二、新税法下的个税计算公式;f6o0|'f,?:p0h:I公式=ROUND(MAX((应发工资-3500)*{3,10,20,25,30,35,45}/100-{0,21,111,201,551,1101,2701}*5,0),2)；其中速扣数的公倍数5也可以乘以到里面。三、年龄（岁数）和工龄的计算公式$R+O8]*{0S0p*L7r公式=DATEDIF(I4,TODAY(),y)&年&DATEDIF(I4,TODAY(),ym)&月&DATEDIF(I4,TODAY(),md)&日；其中I4为数据源。$q/K9m1n,B/q+f四、平均年龄计算公式公式=AVERAGE(TODAY()-H3:H24)/365；其中H3:H24为计算范围；注：更新范围时，单回车键不行，需要ctrl+shift+回车键！2011年11月22日更新五、数据查询并定位5u-Q9q/u7v!X1、O35=（输入查找内容）；#s&G5R7X0U*P$c2、O36=IF(ISERROR(MATCH(O35,sheet1!C:C,0)),,MATCH(O35,sheet1!C:C,0))。（意思为显示的位置,其中C为数据列）；3、O37=HYPERLINK(#sheet1！C&O36,IF(ISNUMBER(O36),点击显示,没有找到))。（意思是查询结果）；注：可以应用在从大量数据中查找所需数据，当然你也可以通过Excel自带的查询工具（ctrl+f为快捷键）。3I0WR`R*V7l%v5k/g#p0_9l!MD7O#H&Z2011年11月26日更新N7d+L%w3b-k%U'C*B六、如何在Excel中插入Flash时钟的?!e)d;_6J%ua6i动态时钟不是用函式运算、自动化功能制作出来的,这只是简单的插入Flash文挡的功能而已，而且只要你有Flash文件，任何人都可以轻松自行制作。制作方法：第1步首先打开一个空白Excel文件，点击“视图”→然后点选【控件工具箱】，→点击“其他控件”。第2步然后再点击[ShockwaveFlashObject]项目，表示要插入Flash物件。第3步接下来，鼠标会变成一个小十字，此时可以在Excel编辑区中画一个大小适中的方框，这个方框就是用来显示Flash时钟的内容的。;aI!t-B/A5|!f7R&b1h+W第4步画好方框后，接着点击【属性】，准备设置属性。第5步出现「属性」对话框后，将DeviceFont设置成False；将Eebedmovie设置成True；将Enabled设置成True；将Locked设置成True；将Loop设置成True；将Menu设置成False；并在“Movie”右侧填入时钟的地址与名称。4@5n4e5z(^%b0^*S第6步退出设计模式，全部完成。2011年11月29日更新,g%Y1r*K&j4p0R-I2M$}-X9u七、与身份证相关公式$[%j4G/h;{)c5z1、身份证验证公式=IF(LEN(A2)=18,MID(10X98765432,MOD(SUMPRODUCT(MID(A2,ROW(INDIRECT(1:17)),1)*2^(18-ROW(INDIRECT(1:17)))),11)+1,1)=RIGHT(A2),IF(LEN(C3)=15,ISNUMBER(--TEXT(19&MID(C3,7,6),#-00-00))))；2、提取性别公式=CHOOSE(MOD(MID(A2,LEN(A2)/2+8,1),2)+1,女,男)；或公式=IF(MOD(IF(LEN(A2)=15,MID(A2,15,1),MID(A2,17,1)),2)=1,男,女)。3、判断生肖公式=CHOOSE(MOD(MID(A2,LEN(A2)/2,2),12)+1,鼠,牛,虎,兔,龙,蛇,马,羊,猴,鸡,狗,猪)；以上A2为身份证数据源；1a:K:y&i8nx0S9j'](l:P%H7M5q2011年12月2日更新&s1u/x:k'K1z*c\9c4、提取出生日期公式=TEXT(RIGHT(19&MID(A2,7,LEN(A2)/2-1),8),#-##-##)；5、提取年龄（整岁）公式=INT(DAYS360(TEXT(RIGHT(19&MID(A2,7,LEN(A2)/2-1),8),#-##-##),TODAY())/360)；6、判断星座公式=VLOOKUP(VALUE(1900-&TEXT(MID(A2,LEN(A2)/2+2,4),#-##)),{1,摩羯座;21,水瓶座;50,双鱼座;81,白羊座;112,金牛座;143,双子座;174,巨蟹座;205,狮子座;236,处女座;268,天秤座;298,天蝎座;328,人马座;357,摩羯座},2,TRUE)；7、15位转换为18位公式=IF(LEN(C2)=15,REPLACE(C2,7,,19)&MID(10X98765432,MOD(SUMPRODUCT(MID(REPLACE(C2,7,,19),ROW(INDIRECT(1:17)),1)*2^(18-ROW(INDIRECT(1:17)))),11)+1,1),C2)；#{6^W5z'D2011年12月9日更新八、选中单元格的行与列变颜色1、条件格式里用公式里填，应用于=$1:$65536公式=(ROW()=CELL(row))+(COLUMN()=CELL(col))vba编辑器里填（用ALT+F11调出）：PrivateSubWorksheet_SelectionChange(ByValTargetAsRange)Calculate7D8h/Z'P.n&{#r+p0fEndSub颜色在格式里自己调；2、或者直接在vba编辑器里填：+Z;I7c.~e;aPrivateSubWorksheet_SelectionChange(ByValTargetAsExcel.Range)8|.z&m*S$Q$s7nw-XOnErrorResumeNext(W0l&z9u4R,`5k!G7fCells.FormatConditions.Delete7e3x1O(`.C.p6Q$A6YiColor=Int(50*Rnd()+2)!@U1v*].\*^&R+K;C8WWithTarget.EntireRow.FormatConditions.Delete.AddxlExpression,,TRUE.Item(1).Interior.ColorIndex=iColorEndWithWithTarget.EntireColumn.FormatConditions.Delete3j-t5]c$x8r'X.AddxlExpression,,TRUE5u/qL!f4a.Item(1).Interior.ColorIndex=iColorEndWith7E6d$N5v.k4D/N:z,N(VEndSub2011年12月26日更新在应用“第八”的第二条后，发现文件就不能再编辑，慎用之。4R6B6K&e!Q0J1iS+r&S-Y2011年最后一天更新九、重复数据去除从大量数据中，去除重复记录，保证数据不重复，可以利用excel自带工具高级筛选“，假设数据区域为B列，打开高级筛选，选择列表区域，勾选”选择不重复的记录“。如下图：&B'o5@*a(Q}:j(z#W6a+B$M1l0n'W:{3U,d+?:z*i;c-A5Z!N`)^2l4f2012年第一次更新（1月2日）*n1A0F3h%e十、个税倒推公式(配合第二条使用）公式=ROUND(MAX((A4-3500-{0,105,555,1005,2755,5505,13505})/(1-{0.03,0.1,0.2,0.25,0.3,0.35,0.45})+3500,A4),2)，其中A4为税后数据。)i)n)J9x,r1C%Er-t.@-m(U*G([7[7j2012年1月14日更新/K,q.Y*`'D(_.n5J9`4`十一、用身份证号提取户籍地+j,v/t#e.J,A公式=IF(B2=,/,IF(ISERROR(LOOKUP(VALUE(LEFT(B2,2)),{11,北京;12,天津;13,河北;14,山西;15,内蒙古;21,辽宁;22,吉林;23,黑龙江;31,上海市;32,江苏;33,浙江;34,安徽;35,福建;36,江西;37,山东;41,河南;42,湖北;43,湖南;44,广东;45,广西;46,海南;50,重庆;51,四川;52,贵州;53,云南;54,西藏;61,陕西;62,甘肃;63,青海;64,宁夏;65,新疆;71,台湾;81,香港;82,澳门;,0})0),,LOOKUP(VALUE(LEFT(B2,2)),{11,北京;12,天津;13,河北;14,山西;15,内蒙;21,辽宁;22,吉林;23,黑龙;31,上海;32,江苏;33,浙江;34,安徽;35,福建;36,江西;37,山东;41,河南;42,湖北;43,湖南;44,广东;45,广西;46,海南;50,重庆;51,四川;52,贵州;53,云南;54,西藏;61,陕西;62,甘肃;63,青海;64,宁夏;65,新疆;71,台湾;81,香港;82,澳门;,0})))，其中B2是身份证数据。2012年2月24日更新/G'C6P9U4zG0f*B5[.`)q十二、去除单元格中的空格字符4L#h1c)s%E7~%Y选择整列，点击菜单“数据—分列”，第一步选“分隔符号”，第二步选择“空格”，第三步选择“文本”，完成。5t4W#n*k!s_.g2012年3月2日更新)k%~$S1E0Q2X#g十三、此工作簿包含其他数据源的链接，是否需要更新？有的时候，当我们打开XLS工作表文件时，会看到这样的对话框提示（如下图）：时间长了或者数据公式多的时候，不知道这个需不需要更新，而且每次都这样提示，觉得挺烦的。解决方法：1、编辑”→“链接”→“查看链接源”，之后您就可以对链接源进行编辑了，是想更改链接还是删除链接以断开数据源，都可以操作的。2、查找工作表中已经引用链接的单元格。按CTRL+F键，输入“*.XLS”，然后查找，在结果中全选，就可以看到工作表中哪些单元格引用了链接，再进行相关操作。2012年3月10日更新十四、自定