Excel实用公式-SAPO-HR-ZL.docx

Excel实用公式*下面公式在EXCEL表格中运用时，请将“”及其他的标点符号都改成英文状态下输入，否则可能造成公式不可用。一、身份证号在Excel中的运用及其它公式设定身份证号所在单元格为A1（1） 用身份证号码转化成年月日=-text(mid(A1,7,8),”0-00-00”)（2） 用身份证号码计算出年龄=datedif(text(19*(len(A1)=15)&mid(A1,7,8-(len(A1)=15)*2),”0-00-00”),today(),”y”)（3）用身份证号计算出性别=if(and(len(A1)=15,mod(mid(A1,15,1)*1,2)=1),”男”,if(and(len(A1)=18,mod(mid(A1,17,1)*1,2)=1),”男”,”女”)（4）用入职日期计算出员工司龄=datedif(A1,today(),”m”)其中m表示月份，得出的结果是多少个月，需要除以12，才能得出年份（5）20090216转变成2009-2-16=date(mid(A1,1,4),mid(A1,5,2),mid(A1,7,2)（6）消除数字中间的小数点，如7.889变成7889，所在7.889单元格为A1=substitute(A1,”.”,”)二、EXCEL比对、索引公式（1）比对Excel同一列中重复的内容（如姓名、身份证号等）,比如在A列中有一万个姓名，在B1中输入公式如下，数字大于1的为重复数据 =countif(A:A，A1)（2）在两个不同的工作表中进行索引,假如有两个表，A是工资，B是出勤，A中B列为姓名列，需要把数据索引到A表中C列；其中B表，C列为姓名共有10000人，G列为员工实际出勤，公式如下：=index（出勤！$1:$10000，MATCH（B1，出勤！$C:$C,0）,6）三、Excel中在所有行前同时插入空白两行（工资表专用）（1）Excel中如何在所有行前插入两行，新健宏，名A，复制下面公式，保存。然后，关闭后，重新打开运行A就行了。For i = 2 To 200 * 3 Step 3 j = i & : & i + 1Rows(j).SelectSelection.Insert Shift:=xlDownNext i说明：其中2、200可以换成指定的行，如果你表中的内容从第3行开始至1400行，如For i = 3 To 1400 * 3 Step 3，或是其他任意指定行。（2）执行了（1）的编制的宏后，就会在表中每一行前同时插入空白的两行，如果想将表头（工资表专用）复制到表中所有空白的两行中，请按下面操作：1、将鼠标移到表头的两行上，进行复制。2、在A列中，从第3行开始往下拉，选中A列所有内容。3、选择编辑，点中定位条件，选择空值，点击确定。4、在A列中第3行第一个单元格中单击鼠标右键，点击粘贴后，会自动将表头的两行复制进表中所有空白的两行中，操作完成。四、排名公式（1）假设在一个工作表中，第H列中是总分数或总成绩，前三行是标题，从第四行开始，才是某某