1、常用函数公 式 及 技 巧II目录从身份证号码中提取出生年月日 .- 1 -从身份证号码中提取出性别 .- 1 -从身份证号码中进行年龄判断 .- 2 -按身份证号号码计算至今天年龄 .- 2 -按身份证号分男女年龄段 .- 2 -根据出生年月计算年龄 .- 3 -根据出生年月推算生肖 .- 3 -如何求出一个人到某指定日期的周岁？ .- 3 -计算距离退休年龄的公式 .- 3 -求工齡 .- 3 -计算工龄 .- 4 -年龄及工龄计算 .- 4 -自动显示当前日期公式 .- 5 -如何在单元格中自动填入当前日期 .- 5 -如何判断某日是否星期天 .- 5 -某个日期是星期几 .- 5 -什
2、么函数可以显示当前星期 .- 5 -求本月天数 .- 5 -用公式算出除去当月星期六、星期日以外的天数 .- 6 -显示昨天的日期 .- 6 -关于取日期 .- 6 -如何对日期进行上、中、下旬区分 .- 6 -如何获取一个月的最大天数 .- 6 -日期格式转换公式 .- 6 -把转换成 1984.05.- 7 -象 22 怎样转换成 22 日？转成当年当月的日子 .- 8 -将“2006 年 5 月” 转换成“2006 年 05 月” - 8 -将“1968 年 6 月 12 日” 转换为“1968/6/12”格式 .- 8 -将“1968 年 6 月 12 日” 转换为“1968-6-12
3、”格式 - 8 -将 1993-12-28 的日期格式转换成 1993 年 12 月 - 8 -将“1978-5-2”包含年月日的日期转换成“”只有年月的格式 .- 8 -要将“99.08.15” 格式转换成“1999.08.15”如何做 - 8 -要保持 2005/8/6 格式 .- 8 -将“二三年十二月二十五日”转为“2003-12-25”格式， .- 9 -日期格式转换 .- 9 -日期格式转换问题 .- 9 -常用函数公式及技巧III要想自动取得“编制日期：XXXX 年 X 月 X 日” .- 9 -自动排序 .- 10 -按奇偶数排序 .- 10 -自动生成序号 .- 10 -如何
4、自动标示 A 栏中的数字大小排序？ - 10 -如何设置自动排序 .- 10 -重复数据得到唯一的排位序列 .- 10 -按字符数量排序 .- 11 -排序字母与数字的混合内容 .- 11 -随机排序 .- 11 -排序的问题 .- 11 -怎样才能让数列自动加数 .- 12 -一个排序问题 .- 12 -数字的自动排序,插入后不变? - 12 -根据规律的重复的姓名列产生自动序号 .- 13 -排名的函数 .- 14 -自动排名公式 .- 14 -平均分及总分排名 .- 14 -求名次排名 .- 14 -排名次 .- 14 -根据分数进行普通排名 .- 15 -对于普通排名分数相同时，按顺序
5、进行不重复排名 .- 15 -依分数比高低名次成绩排名 .- 15 -美国式排名 .- 16 -中国式排名 .- 16 -求最精简的自动排名公式 .- 16 -排序后排名 .- 17 -位次排名 .- 17 -根据双列成绩进行共同排名 .- 17 -在双列间排名 .- 17 -等次排名 .- 17 -不等次排名(行小排先） - 18 -不等次排名(行大排先） - 18 -顺次排名 .- 18 -有并列排名 .- 18 -无并列排名 .- 19 -有并列分段排名 .- 19 -无并列分段排名 .- 19 -成绩排名 .- 19 -如何排名 .- 20 -数据排名（隔几行排名） .- 20 -根据
6、分数进行倒排名 .- 21 -倒数排名函数是什么 .- 21 -如何实现每日各车间产量的排名 .- 21 -IV分数相同时按照一科的分数进行排名 .- 21 -筛选后自动产生序列号并汇总 .- 21 -如何筛选奇数行 .- 22 -函数筛选姓名 .- 22 -名次筛选 .- 22 -如何实现快速定位（筛选出不重复值） .- 22 -如何请在 N 列中列出 A1:L9 中每列都存在的数值 - 22 -自动为性别编号的问题 .- 22 -EXCEL 中如何删除*号 .- 23 -去空格函数 .- 23 -如何去掉字符和单元格里的空格 .- 24 -怎样快速去除表中不同行和列的空格 .- 24 -如
7、何禁止输入空格 .- 24 -代替单元格中字符串 .- 24 -把单元格中的数字转变成为特定的字符格式 .- 25 -把有六百多个单元格的一列，变成一页的多列 .- 25 -将 N 列变 M 列公式归纳为 .- 25 -一列变四列 .- 25 -四列变一列 .- 26 -重复四次填充 .- 26 -多行数据排成一列 .- 26 -将单元格一列分为多列 .- 26 -把单元格编号中的小写字母变成大写字母 .- 27 -让姓名左右对齐 .- 27 -数字居中而小数点又对齐 .- 27 -格式单元格数字自定义?.?-确定 - 27 -格式单元格数字自定义?.0?-确定 - 28 -比较两个单元格内容
8、是否一致 .- 28 -怎么样设置才能让这一列的每个单元格只能输入 12 位 .- 28 -如何让工作表奇数行背景是红色偶数行背景是蓝色 .- 28 -计算特定的一组单元格中，满足条件的单元格的个数 .- 28 -把文本格式的数字转换成真正的数字 .- 29 -设置页码 .- 29 -Excel 表格里如何插入页码的 ? .- 29 -如何设置页脚首页为第 5 页 .- 29 -表格的页脚问题 .- 29 -无拘无束的页眉 .- 30 -判断当前行是否大于分页符所在行 .- 30 -取得当前行所在页 .- 30 -取得总页 .- 31 -打印表头 .- 31 -Excel 打印中如何不显示错误
9、值符号 .- 31 -对于一些不可打印的字符的处理 .- 31 -用那个函数可将个位数前面的零值显示出来? .- 32 -常用函数公式及技巧V如果你要在 A3 的前面插入 100 行 - 32 -请问如何每隔 30 行粘贴一新行 .- 32 -在工作表里有连续 10 行数据, 现在要每行间隔 2 行 - 32 -一个大表每一行下面需要加一行空行，怎么加最方便 .- 32 -Excel 中插入空白行 .- 33 -快速删除工作表中的空行 .- 33 -快速删除空行 .- 33 -一次删完 Excel 里面多出很多的空白行 - 34 -每 30 行为一页并加上一个标题如何实现 .- 34 -如何实
10、现隔行都加上标题项 .- 34 -如何把标签页去掉的? .- 34 -去掉默认的表格线(网线) - 35 -表格的框线 .- 35 -列标的标识变了 .- 35 -符号的意义 .- 35 -双击格式刷竟也能 COPY 文本(不是文本格式) - 35 -查找+格式刷的妙用 .- 36 -光标移动 .- 36 -最后一行为文本 .- 36 -最后一行为数字 .- 36 -如何在 EXCEL 中快速定位最后一行数据 - 36 -如何用 SUN 函数快速求和 .- 36 -在 Excel 中快速查看所有工作表公式 - 37 -在 Excel 中设置行间距 - 37 -怎样同时改变多行行高 .- 37
11、-快速换行 .- 37 -让文本换行 .- 37 -在 Excel 中行列快速转换 - 38 -将原有列中的内容倒置过来 .- 38 -快速回到 A1 单元格 - 38 -复制粘贴中回车键的妙用 .- 38 -一次选中批注单元格 .- 38 -一次在所有单元格中插入批注 .- 38 -在公式中插入批注 .- 38 -不连续单元格填充同一数据 .- 39 -空白行的填充 .- 39 -怎样用函数向下实现自动填充 .- 39 -怎么设置自动保存 .- 39 -避免输入网址和电子邮件地址时的超链接 .- 40 -单元格前面自动加了等号 .- 40 -加盖公章 .- 40 -查找+格式刷的妙用 .-
12、41 -Excel 中鼠标双击妙用 .- 41 -Excel 中快速定位的技巧实例 .- 43 -VI在 Excel 中插入 Flash 时钟的步骤 .- 43 -小写数字转换成人民币大写 .- 43 -轻轻松松制作超复杂 Excel 表头 - 46 -截取单元格里某个字符后的字符 .- 48 -截取字符的公式 .- 48 -如何确定*号后的数字 .- 48 -如何提取数字中间的数 .- 48 -三个数中,如何取出中间那个 - 48 -取数值后三位公式 .- 48 -取数函数 .- 48 -如何把单元格中的数字提取出来（字符串中不连续） .- 49 -数字在字符串中不连续如何提取数字 .- 4
13、9 -用如何提取“-”前后的字符 - 49 -怎样删去后的文字 .- 49 -怎样只取“.”之后的文字 - 50 -获取单元格内容中字符串 .- 50 -如何提取一串数字中的几位数字（字符） .- 50 -如何把一个单元格中的数字挑出来 .- 51 -分割文本 .- 51 -按照给定的位数，截断小数点后的数字 .- 51 -单元格数字提取问题 .- 51 -以关键字提取名称 .- 52 -如何把文本中的前几个字符去除 .- 52 -对一列中的文字统一去掉最后一个字 .- 52 -如何去掉单元格中的第一个数字 .- 53 -论一下取最后一个单词的方法 .- 53 -如何去掉单元格最后一位数字 .
14、- 53 -如何在一列已经输入的数据前添加“p” .- 53 -什么函数可以插入字符 .- 54 -如何在数据前添加“*”号 .- 54 -数字前面加上数字 .- 54 -数字如何拆分 .- 55 -单元格中的数据拆分 .- 55 -单元格的拆分 .- 55 -如何拆分字组 .- 55 -用连字符“如何判断某日是否星期天=WEEKDAY(A2,2)=TEXT(A1,“aaaa“)=MOD(A1,7)1)显示昨天的日期每天需要单元格内显示昨天的日期，但双休日除外。例如，今天是 7 月 3 号的话，就显示 7 月 2 号，如果是 7 月 9 号，就显示 7 月 6 号。=IF(TEXT(TODAY
15、(),“AAA“)=“一“,TODAY()-3,IF(TEXT(TODAY(),“AAA“)=“日“,TODAY()-2,TODAY()-1)=IF(TEXT(TODAY(),“AAA“)=“一“,TODAY()-3,TODAY()-1)关于取日期怎么设个公式使 A1 在年月日向后推 5 年,变成 2011-7-15=DATE(YEAR(A1)+5,MONTH(A1),DAY(A1)=EDATE(A1,12*5)如何对日期进行上、中、下旬区分=LOOKUP(DAY(A1),0,11,21,31,“上旬“,“中旬“,“下旬“,“下旬“)如何获取一个月的最大天数“=DAY(DATE(2002,3,
16、1)-1)“或“=DAY(B1-1)“,B1 为“2001-03-01日期格式转换公式将 “01/12/2005” 转换成“”格式RIGHT(A1,4)“)象 22 怎样转换成 22 日？转成当年当月的日子公式为：=date(year(now(),month(now(),22)将“2006 年 5 月”转换成“2006 年 05 月”公式为：=TEXT(A8,“yyyy“年“mm“月“;“)也可以这样处理：选中单元格，设置单元格公式数字自定义，将 yyyy“年”m“月”改为：yyyy“年”mm“月”，即可。但这方法打印出来显示为：2006/5/将“1968 年 6 月 12 日”转换为“196
17、8/6/12”格式=YEAR(A1)其实不是数据排序，应该是数据填充。输入公式=LEFT(E3,4)+3&“-“&RIGHT(E3,4)+3 即可。怎样才能让数列自动加数怎样做才能让数列自动加数A A0001B B0001A A0002C C0001A A0003B B0002C C0002公式为=A1&“000“&COUNTIF(A$1:A1,A1)向下拖=TEXT(COUNTIF(A$1:A1,A1),“!“&A1&“0000“)否则数字超过 9 就错误了。一个排序问题一个电子表格，格式是 101、102. 999,10101、10102. 99901,. ,请问如何将它排列成101,10
18、101,102,10201,. 999,99901, 的形式。我在数字前加了个字母,比如“d“&“数字“,然后用排序就可以把它们按你的需求排列了.最后再把字母“d“去掉。数字的自动排序,插入后不变?1 赵一 总经理2 赵二 副经理3 赵三 副经理4 赵四 技术员5 赵五常用函数公式及技巧- 13 -6 赵六 员工如上的一个表,如何实现当我把赵六这一整行(第 6 行)插入到上面的表中时,A 列的序列号不变?最后的效果如下:1 赵一 总经理2 赵二 副经理3 赵六 员工 4 赵三 副经理5 赵四 技术员6 赵五A1 单元格输入公式 =row(),往下拉,然后再插入。=SUBTOTAL(3,$B$2
19、:$B2)在 A1 中输入公式：“=if(b1=“,“,counta($b$1:b1)”后下拉复制至 A 列各行即可（“”不必输入）根据规律的重复的姓名列产生自动序号姓名 序号张三 1张三 1李四 2李四 2赵五 3赵五 3赵五 3王六 4王六 4=(A11,VLOOKUP(A2,A:B,2,0),SUMPRODUCT(1/COUNTIF(A$2:A2,A$2:A2)排名的函数用排名函数来对成绩进行排名，用起来非常地方便。=IF(ISERR(RANK(M3,M:M),“,RANK(M3,M:M)A 列是成绩,B 列是排名=SUMPRODUCT(A$1:A$9A1)/COUNTIF(A$1:A$
20、9,A$1:A$9)+1自动排名公式=RANK(C3,$C$3:$C$12)=RANK(A2,$A$2:$A$11,0)=RANK(C2,$C$2:$C$65)+COUNTIF($C$2:C2,C2)-1百分比排名的公式写法为：=PERCENTRANK($C$3:$C$12,C3)平均分及总分排名=AVERAGE(B2:E2)=RANK(F2,$F$2:$F$65536)求名次排名统计成绩时遇到一个分别求班级和年级总分名次排名的问题，不晓得应该运用什么公式来实现。班级名次：=SUMPRODUCT(BJ=A2)*(ZFE2)+1年级名次：=RANK(E2,ZF) 公式下拖。排名次根据总分值大小，
21、只将姓名排序后, 降序结果=INDEX(A$2:A$6,RANK(D2,D$2:D$6)常用函数公式及技巧- 15 -根据总分值大小，只将姓名排序后, 升序=INDEX(A$2:A$6,RANK(D2,D$2:D$6,1)根据分数进行普通排名=RANK(A2,$A$2:$A$12)=RANK(A2,A$2:A$12)+COUNTIF(A$2:A2,A2)-1=SUMPRODUCT(1*($E$3:$E$12=E3)=RANK(K3,$K$3:$K$26)=RANK(A2,A$2:A$12)=SUM(A$2:A$12=A2)/COUNTIF(A$2:A$12,A$2:A$12)=COUNTIF(
22、$K$3:$K$26,“&K3)+1=INDEX($A$2:$A$7,MATCH(LARGE($C$2:$C$7,ROW(A1),$C$2:$C$7,0),1)=SUMPRODUCT($A$2:$A$12A2)/COUNTIF($A$2:$A$12,$A$2:$A$12&“)+1=RANK(D2,OFFSET($A$1,MATCH($A2,$A:$A,0)-1,3,COUNTIF($A:$A,$A2),1)对于普通排名分数相同时，按顺序进行不重复排名=RANK(K32,$K$32:$K$55)+COUNTIF($K$32:$K32,K32)-1=COUNTIF($K$32:K32,K32)-1
23、+COUNTIF($K$3:$K$26,“&K32)+1=SUMPRODUCT(1*($E$3:$E$12+ROW($E$3:$E$12)/100=($E3+ROW(E3)/100)=RANK(E3,$E$3:$E$12)+COUNTIF($E$3:E3,E3)-1=SUMPRODUCT(1*($E$3:$E$12+$B$3:$B$12/100)=(E3+B3/100)依分数比高低名次成绩排名=RANK($E3,$E$3:$E$22) 內建方式排名=SUMPRODUCT(1*($E$3:$E$12=E3) 一般方式排名=RANK(E3,$E$3:$E$22)+SUM(IF($E$3:$E$22
24、E3,1/COUNTIF($E$3:$E$22,$E$3:$E$22),0)-COUNTIF($E$3:$E$22,“&E3) 一般方式排名=RANK(E3,$E$3:$E$12)+COUNTIF($E$3:E3,E3)-1 不重复排名=SUMPRODUCT(1*($E$3:$E$12+ROW($E$3:$E$12)/100=($E3+ROW(E3)/100)- 16 -=SUMPRODUCT(1*($E$3:$E$12+$B$3:$B$12/100)=(E3+B3/100) 不重复排名=SUMPRODUCT(1*($E$3:$E$12+$B$3:$B$12/100+$C$3:$C$12/10
25、000)=(E3+B3/100+C3/10000) 不重复排名=RANK($E3,$E$3:$E$22,1) 倒排序美国式排名=RANK(K247,$K$247:$K$270) =RANK(B1,$B1:$H1)中国式排名=RANK(B2,$B$2:$B$21,0)=RANK(B1,$B1:$H1)+COUNTIF($B$1:B1,B1)-1=SUM(IF($A$1:$E$1=A1,1/COUNTIF($A$1:$E$1,$A$1:$E$1),“)=SUMPRODUCT($B$2:$B$21=B2)/COUNTIF($B$2:B$21,B$2:B$21)=SUMPRODUCT(B$3:B$21
26、B3)*(1/COUNTIF($B$3:$B$21,$B$3:$B$21)+1 （升序）=SUMPRODUCT(B$3:B$21B2)+1=SUM(IF($B$3:$B$21B2,1/COUNTIF($B$2:B$21,B$2:B$21)+1=SUM(IF($A$1:$E$1=A1,1/COUNTIF($A$1:$E$1,$A$1:$E$1),“)=SUM($B$2:$B$21B2)*(MATCH($B$2:B$21,B$2:B$21,)=ROW($1:$20)+1=SUM(IF($B$1:$H$1=E2)/COUNTIF($E$2:$E$21,$E$2:$E$21),“第DBNUM1G/通用
27、格式名“)排序后排名=SUM(IF($B$2:$B$15=B2,1/COUNTIF($B$2:$B$15,$B$2:$B$15)=SUMPRODUCT(B$2:B$15=B2)/COUNTIF(B$2:B$15,B$2:B$15)位次排名=IF($B2:$O2=0,RANK($B2:$O2,$B2:$O2,0),)根据双列成绩进行共同排名=RANK(C345,($C$345:$C$356,$H$345:$H$356)在双列间排名=RANK(B2,($B$2:$B$26,$E$2:$E$16)等次排名由大到小排名=RANK(B3,$B$3:$B$12)=SUMPRODUCT($A$16:$A$2
28、5=A16)*($B$16:$B$25B16)+1由小到大排名=RANK(B3,$B$3:$B$12,1)=SUMPRODUCT($A$16:$A$25=A16)*($B$16:$B$25B16-ROW(B16)/10000)+1由小到大=RANK(B3,$B$3:$B$12,1)+COUNTIF($B$3:B3,B3)-1=SUMPRODUCT($A$16:$A$25=A16)*($B$16:$B$25+ROW($B$16:$B$25)/10000B16+ROW(B16)/10000)+1由小到大=COUNT($B$3:$B$12)-RANK(B3,$B$3:$B$12)-COUNTIF($B$3:B3,B3)+2=SUMPRODUCT($A$16:$A$25=A16)*($B$16:$B$25-ROW($B$16:$B$25)/10000B3)*(1/COUNTIF(B$3:B$12,B$3:B$12)+1=SUMPRODUCT($A$16:$A$25=A16)*($B$16:$B$25B16)/COUNTIF($K$16:$K$25,$K$16:$K$25)+1由小到大=SUMPRODUCT(B$3:B$12B3)+1=COUNTIF($B$3:$B$21,“&B3)+1=SUM(IF($B$3:$B$21B3,1,0)+1
