excel公式（用）

Excel公式的应用一、通过身份证号码套出年龄的公式：1、IF(D2=,DATEDIF(TEXT(LEN(D2)=15)*19&MID(D2,7,6+(LEN(D2)=18)*2),#-00-00),TODAY(),y)（注明：公式在输出年龄的单元格，D2为身份证号码所在单元格，然后拖动单元格即可）2、=INT(TODAY()-L2)/365) 通过当前日期计算其年龄（注明：公式在输出年龄的单元格，L2为出生年月日，然后拖动单元格即可）3、 2009-year（） 通过指定年份计算其年龄（注明：公式在输出年龄的单元格，“2009”为现在的年份，括号内为出生年月所在单元格，然后拖动单元格即可） 二、通过身份证号码套出出生日期的公式： 1、=TEXT(TEXT(MID(A1,7,LEN(A1)/2-1),#-00-00),ee-mm-dd) 输出如：1982-09-17（注明：公式在输出出生日期的单元格，A1为身份证号码所在单元格，然后拖动单元格即可） 2、=MID(G2,7,4)&/&MID(G2,11,2)&/&MID(G2,13,2) 输出如：1982/09/17 (注明：公式在输出出生日期的单元格，G2为身份证号码所在的单元格，7、11、13均为提取第一个字符的位置，4、2、2均为所提取字符的个数) 3、=DATE(MID(G2,7,4),MID(G2,11,2),MID(G2,13,2) 输出如：1982-09-17 （注明：公式在输出出生日期的单元格，G2为身份证号码所在的单元格，7、11、13均为提取第一个字符的位置，4、2、2均为所提取字符的个数）三、从身份证号码中辨别性别并输出 =IF(VALUE(MID(H2,15,3)/2=INT(VALUE(MID(H2,15,3)/2),女,男) （注明：公式在输出性别的单元格，H2未身份证号码所在的单元格，MID(H2,15,3)是将身份证中提取15-17位，VALUE(MID(H2,15,3)是将提取出的文本转换为能计算的数值，VALUE(MID(H2,15,3)/2=INT(VALUE(MID(H2,15,3)/2)是判断奇偶，=IF(VALUE(MID(H2,15,3)/2=INT(VALUE(MID(H2,15,3)/2),女,男)是偶数输出“女”，奇数输出“男”)四、将两个单元格的数据合并到一个单元格中 =A1&B1 （注明：公式在合并的单元格，A1为第一个数据单元格，B1为第二个数据单元格，然后拖动单元格即可）五、提取一组数据中的某个数字到另一个单元格内 =mid（a1,2,3） （注明：公式在输出单元格内，a1为存放数据的单元格，2为从第2个字符开始，3为共提取3个字符，然后拖动单元格即可）六、在某一列或行的单元格前统一添加上相同的字符 1、 =ABC&A1（注明：公式在输出单元格内，ABC为统一添加的字符，A1为原始数据单元格，然后拖动单元格即可）2、 右键全选有数据的列设置单元格格式自定义输入123文本或者”123”#数字确定 （注明：选中有数据的这列或行，进行设置单元格格式，选择“自定义”，如原数据为文本则输入123，如原数据为数字则输入”123”#数字后点击确定。123为统一添加的字符。）七、统计某一列或行，某特定数据的个数。（特定数据可以是”36”，”=60”，”男”，”陈”） =COUNTIF(D3:D87,陈) （注明：公式在输出统计总数的单元格内，D3:D87为存放数据的D列，”陈”为某特定数据）八、将分数替换为不同的等级=IF(A1=80,优秀,IF(A1=70,良好,IF(A1=60,及格,不及格)（注明：公式在输出等级的单元格内，A1为存放数据的单元格）九、从身份证号码中提取出出生年月日=DATE(MID(A1,7,4),MID(A1,11,2),MID(A1,13,2)（注明：公式在输出出生年月日的单元格内，a1为身份证号码的单元格，7、11、13为从第7、11、13个字符开始，4、2、2为共提取4、2、2位字符）十、及格平均分统计 =SUMIF(A2:A10,=60,A2:A10)/COUNTIF(A2:A10,=60)（注明：公式在输出统计平均分的单元格内，A2：A10为存放成绩的单元格，=60为条件，sumif为满足条件的分数求和，countif为满足条件的个数）十一、排名次函数=RANK(C2,$C$2:$C$12)（注明：公式在输出名次的单元格内，c2为需要排名的数据单元格，$c$2:$c$12为c2到c12单元格的绝对数据）十二、四舍五入函数 例如：a1和b1的和乘以0.1取整后再乘以0.36，所得的结果。（如果用单元格设置和的小数位数则所加的和乘以0.36时，和的值仍是没有四舍五入的值与0.36相乘，结果会出错）=(ROUND(A1+B1)*0.1,0)*0.36)（注明：公式在输出结果的单元格内，（(A1+B1)*0.1,0）为A1和B1求和和乘以0.1的值，0为所求和的小数位数，round函数为指定位数对“(A1+B1)*0.1”进行四舍五入。）十三、错误的除法公式和无意义的零（数值探测函数ISNUMBER）1、 无错误除法公式例如：a1数据为5，b1数据为空，在c1内输入公式“=a1/b1”显示结果为“#DIV/0!”，因为b1无数据认为是0，不符合除法规律。则正确公式为：=IF(ISNUMBER(B1),A1/B1, )（注明：公式在输出结果的单元格内，ISNUMBER(B1)为检测b1是否为空，if为判断语句，如果为空输出“ ”，否则正常计算）2、 消除无意义的零 在使用sum求和公式时，如果引用的单元格中没有数据，结果显示为0，这不符合报表要求。=IF(ISNUMBER(a1：B1),sum（A1：B1）, )（注明：公式在输出求和结果的单元格内，ISNUMBER(a1：B1)为检测a1：b1单元格内数据是否为空，if为判断语句，如果为空，计算结果显示为“ ”，否则正常计算）十四、分别去掉一个最高分和最低分求其平均分=TRIMMEAN(A1:A10,0.2)（注明：公式在输出结果的单元格内，a1：a10为存放数据的单元格，公式中的0.2表示10个数据中去掉2个（100.2），即一个最高分和一个最低分。十五、“虚工龄”的计算: 所谓“虚工龄”就是从参加工作算起，每过一年就增加一年工龄。=YEAR(A1)-YEAR(B1)（注明：公式在输出结果的单元格内，A1和B1分别存放工龄的起止日期，YEAR（A1）和YEAR（B1）分别计算出两个日期对应的年份，相减后得出虚工龄。）十六、“实工龄”计算：所谓“实工龄”就是实际工作满一年算一年的工龄。= TRUNC（DAYS360（1998/6/1，2001/12/31）/360，0）（注明：公式中的DAYS360（1998/6/1，2001/12/31）计算两个日期相差的天数，除以360后算出日期相差的年份（小数）。最后TRUNC函数将（DAYS360（A1，B1）/360的计算结果截去小数部分，从而得出“实工龄”。） 如果计算结果需要保留一位小数，只须将公式修改为“= TRUNC（DAYS360（1998/6/1，2001/12/31）/360，1）”即可。 如果你要计算参加工作到系统当前时间的实工龄，可以将公式修改为“= TRUNC（DAYS360（1998/6/1，NOW（）/360，0）”。其中NOW（）函数返回当前的系统日期和时间。十七、将某一单元格添加数据并进行链接=HYPERLINK(F:电影文件连续剧大冬瓜大冬瓜 国语_01.rmvb,大冬瓜第一集)（注明：公式在需要添加数据的单元格内，“F:电影文件连续剧大冬瓜大冬瓜 国语_01.rmvb”为链接完整路径，“大冬瓜第一集”为显示的文字）十八、在excel中有两个工作表,在sheet2中调用sheet1相同列的数据 =VLOOKUP(A2,Sheet1!$A$2:$E$964,4,0)（注明：vlookup是函数的一种，叫查找函数，A2是你要查找的值，Sheet1!$A$2:$E$964是你要查找的区域，找到后，返回该区域第4列（即d列）的同行的值。最后的参数0表示精确查找。A2即为两个工作表相同列的数据，Sheet1为相同列数据多的工作表，$a$2:$e$964为sheet1里面a2到e964的所有数据，4为取sheet1的第4列）例：假设sheet1中A列名称,B列数量.sheet2中A列名称,B列数量，C列是要从sheet1中插入的B列数量. 则C2 =vlookup(a2,sheet1!$a$2:$B$100,2,0) 2代表sheet1中B列的数据。十九、excel中知道A列是出生年月如何自动计算现在的年龄求年龄问题,要看你的数据是哪种格式(日期、文本），举例说明：法1、 如果A1出生年月“1984年1月”，在其他单元格输入公式“=DATEDIF(A1,TODAY(),Y)”，回车，即返回27；A1改为大于今天月份如“1984年4月”，该公式则返回26。（可用）法2、 假定你的A1单元格为日期格式，内容为“1986-2-20”，在B1单元格可输入如下公式得到年龄：=INT(TODAY()-A1)/365) （取整）法3、 假定你的A1单元格为文本格式，内容为“86.02”，在B1单元格可输入如下公式得到年龄： =INT(TODAY()-LEFT(A1,2)*365-RIGHT(A1,2)*30)/365)二十、Excel中引用其他工作表/工作簿中的单元格法1、引用其他工作表中的数据如要引用同一个工作簿中的其他工作表单元格中的数据，一般格式为：工作表名称！单元格地址。图所示的L3单元格中包含公式“=J3+Sheet2!J3”，表示将当前工作表J3单元格中的数据与Sheet2工作表J3单元格中的数据相加。法2、引用其他工作簿的单元格若要引用其他工作簿的单元格数据，一般格式为：工作簿存储地址工作簿名称工作表名称！单元格地址。图所示表格中，L4单元格中包含公式“=J4+E:学生成绩单.xlsxSheet1!J4”，表示将当前工作表J4单元格中的数据与E盘下的“学生成绩单”工作簿中的Sheet1工作表J4单元格中的数据相加。二十一、excel中删除单元格最后2位字符=SUBSTITUTE(A2,RIGHTB(A2,2),1)（注明：原数据在A2单元格，公式填充到B2单元格，即可删除最后两位字符。向下复制公式，即可快速删除整列单元格最后两位字符。）二十二、excel中取某单元格前三个字符=left(A1,3)（注明：原数据在A1单元格，在B1单元格填充其公式）3