Excel实用公式和技巧.doc

1Excel 实用公式和技巧【身份证信息提取】从身份证号码中提取出生年月日=IF(LEN(A2)=15,"19"&MID(A2,7,2)&"-"&MID(A2,9,2)&"-"&MID(A2,11,2),MID(A2,7,4)&"-"&MID(A2,11,2)&"-"&MID(A2,13,2)) 从身份证号码中提取出性别=IF(MOD(MID(A1,15,3),2),"男","女") 从身份证号码中进行年龄判断以 2006 年 10 月 31 日为基准日,按按身份证计算年龄(周岁)的公式=DATEDIF(TEXT(MID(A1,7,6+(LEN(A1)=18)*2),"#-00-00")*1,"2006-10-31","y")按身份证号分男女年龄段按身份证号分男女年龄段，身份证号在 K 列，年龄段在 J 列（身份证号为 18 位）男性 16 周岁以下为　　　　　　　　　　　　1男性 16 周岁（含 16 周岁）以上至 50 周岁为 2男性 50 周岁（含 50 周岁）以上至 60 周岁为 3男性 60 周岁（含 60 周岁）以上为　　　　　 4女性 16 周岁以下为　　　　　　　　　　　　1女性 16 周岁（含 16 周岁）以上至 45 周岁为 2女性 45 周岁（含 45 周岁）以上至 55 周岁为 3女性 55 周岁（含 55 周岁）以上为　　　　　 4=MATCH(DATEDIF(DATE(MID(K1,7,4),MID(K1,11,2),MID(K1,13,2)),TODAY(),"y"),{0,16,50,60}-{0,0,5,5}*ISEVEN(MID(K1,17,1)))=SUM(--(DATEDIF(MID(K1,7,4)&"/"&MID(K1,11,2)&"/"&MID(K1,13,2),TODAY(),"y")>={0,16,45,55}+{0,0,5,5}*MOD(MID(K1,17,1),2)))㎜㎜㎜㎜㎜㎜㎜㎜㎜㎜㎜㎜㎜㎜㎜㎜㎜㎜㎜㎜㎜㎜㎜㎜㎜【年龄和工龄计算】根据出生年月计算年龄＝DATEDIF(A1,TODAY(),"y")根据出生年月推算生肖中国人有 12 生肖，属什么可以推算出来。

即用诞生年份除以 12，再用除不尽的余数对照如下：0→猴，1→鸡，2→狗，3→猪，4→鼠，5→牛，6→虎，7→兔，8→龙，9→蛇，10→马，11→羊例如：XXX 出生于 1921 年，即用 1921 年除以 12，商得数为 160，余数为21，对照上面得知余数 1 对应生肖是鸡，XXX 就属鸡MID("猴鸡狗猪鼠牛虎兔龙蛇马羊",MOD(YEAR(A2),12)+1,1) （2007）㎜㎜㎜㎜㎜㎜㎜㎜㎜㎜㎜㎜㎜㎜㎜㎜㎜㎜㎜㎜㎜㎜㎜㎜㎜如何统计各年龄段的数量如何计算 20-50 岁的人数?=COUNTIF(C3:C17,">=20")-COUNTIF(C3:C17,">50")如何统计 40-50 岁的人的个数＝countif(a:a,">40")-countif(a:a,">50")=COUNTA(A1:A10,D1:D10)如何让 EXCEL 自动从头统计到当前单元格情况如下： C 列要根据 A 列的内容来统计 B 列的数据，范围从 A1：An，即当 A 列中 An有数据时，Cn 自动根据 An 的值，统计 B1：Bn 的数据｛=SUM(INDIRECT("B1:B" & LARGE((A1:A65535=20")-COUNTIF(C3:C17,">50")如何计算男 20-50 岁的人数?=SUMPRODUCT((B3:B17="男")*(C3:C17>=20)*(C3:C17A1,1,N(C1)+1)自动生成序号比如在第二列中输入内容回车后第一列的下一行自动生成序列号。

IF(B20,"OK","")名次筛选名次=RANK(K5,K$2:K$435)班名次=RANK(K6,OFFSET(K$2,MATCH(A6,A:A,)-2,,COUNTIF(A$1:A$500,A6)))如何实现快速定位（筛选出不重复值）=IF(COUNTIF($A$2:A2,A2)=1,A2,"")9=IF((COUNTIF($A$2:A2,A2)=1)=TRUE,A2,"")=INDEX(A:A,SMALL(IF(MATCH(A$1:A$20,A$1:A$20,)=ROW($1:$20),ROW(A$1:A$20),65536),ROW()))&""（数组公式）如何请在 N 列中列出 A1:L9 中每列都存在的数值｛=IF(ROW()>SUM(--x),"",INDEX(A:A,SMALL(IF(x,ROW($A$1:$A$9)),ROW())))｝自动为性别编号的问题有一个编码,5 位，第 1 位,1 为男,2 为女，后面 4 位,代表他的编号,从 0001-9999，如何达到下表：性别 编码男 10001男 10002女 20001男 10003女 20002男的也是从 0001-9999女的也是从 0001-9999如果你是已经输入了其它信息，仅仅为快速输入编码的话。

用筛选可以实现吧先以“男”为关键字进行排序，然后在第一个男的编码输入 10001，下拉复制到最后一单即可同理再以“女”排序完成目标用公式：=IF(A2="",TEXT(COUNTIF(A$2:A2,A2),"10000"),TEXT(COUNTIF(A$2:A2,A2),"20000"))向下拖㎜㎜㎜㎜㎜㎜㎜㎜㎜㎜㎜㎜㎜㎜㎜㎜㎜㎜㎜㎜㎜㎜㎜㎜㎜【文本与页面设置】EXCEL 中如何删除 *号在录入帐号是录入了*号，如何删除可以用函数 SUBSTITUTE(a1,"*","")查找~*，替换为空将字符串中的星号“*”替换为其它字符在查找栏输入~*替换为“-”即可去空格函数如何删去单元格中的空格，如姓名前，中，后的空格，即单元格中是两个字的人名中间有一个空格，想删去有何方法如：中 国，改为：中国1、用公式：=SUBSTITUTE(A2," ","") 注：第一对双引号中有一空格而第二个“”中是无空格的2、利用查找－替换，一次性全部解决编辑”－“替换” （或 Ctrl+H） ，在“查找”栏内输入一空格， “替换”什么也不输入（空10白） 然后“全部替换”即可3、有一个专门删除空格的函数: TRIM()在 EXCEL 编辑栏里，不管输中文还是英文只能输一个字节的空格，但如果字与字中间是两个字节的空格，那么 TRIM（）就不起作用了，它就不认为是一个空格，而是一个汉字，怎么去“TRIM”也没用。

如：单元格 A1 中有“中　　心　　是”，如果用 TRIM 则变成“ 中　心　是”， 想将空格全去掉，只能用 SUBSTITUDE（）函数，多少空格都能去掉如何去掉字符和单元格里的空格8900079501 8900079501~1900078801 1900078802~=SUBSTITUTE(B2,"~","")怎样快速去除表中不同行和列的空格编辑-定位-定位条件-空值,可选中所有空单元格, 再删除如何禁止输入空格在 Excel 中如何通过编辑 “有效数据”来禁止录入空格？烦请大侠们费心解答解答：有效性公式COUNTIF(A1,"* *")=0(注：COUNTIF(A1,"* *") 在单元格有空格时结果为 1,没有空格时结果为 0如希望第一位不能输入空格:countif(a1," *")=0如希望最后一位不能输入空格:countif(a1,"* ")=0)代替单元格中字符串单元格编号，开始位数，从开始位数算起第几位数，要用于代替的的字符串windows2000 变成 windows2K=REPLACE(B2,8,3,"K")单元格编号，要代替掉的字符，要用作代替的字符，第几个。

代替单元格 B391 中的全部 TT，改为 UUEETTCCTTFF 变成 EEUUCCUUFF=SUBSTITUTE(B394,"TT","UU")只代替单元格 B391 中的第一次出现的 TT，改为 UUEETTCCTTFF 变成 EEUUCCTTFF=SUBSTITUTE(B397,"TT","UU",1)把单元格中的数字转变成为特定的字符格式函数中的第二个参数的双引号一定不能是中文格式的（不能用任意中文输入法输入的双引号 ）实例： 20000 目的： 变成带有美元符号的字符10000 变成带有人民币符号的字符151581 变成带有欧元符号的字符1451451 变成中文繁体的字符15748415 变成中文简体的字符操作步骤： =TEXT(B72,"$0.00") 结果： $20000.00=TEXT(B73,"¥0.00") ￥10000.0011=TEXT(B74,"€0.00") €151581.00=TEXT(B75,"[DBNum2]G/通用格式") 壹佰肆拾伍万壹仟肆佰伍拾壹=TEXT(B76,"[DBNum1]G/通用格式") 一千五百七十四万八千四百一十五把有六百多个单元格的一列，变成一页的多列有一张表，共有 14 页，但每页只有一列，如何把他们整合在一起，变成一页（按每页的顺序） ，如果使用剪切和粘贴的方式，那样太麻烦。

INDIRECT("r"&(COLUMN()-3)*48+ROW()&"C1",0) 复制到其他单元格将 N 列变 M 列公式归纳为=OFFSET($A$1,INT(((ROW(A1)-12)*m+COLUMN(A1)-1)/n),MOD((ROW(A1)-1)*m+COLUMN(A1)-1,n))=OFFSET($A$1,INT(((ROW(A1)-1)*7+COLUMN(A1)-1)/4),MOD((ROW(A1)-1)*7+COLUMN(A1)-1,4)) 四列变七列=OFFSET($A$1,INT(((ROW()-20)*10+COLUMN()-1)/7),MOD((ROW()-20)*10+COLUMN()-1,7)) 七列变十列一列变四列=OFFSET($A$1,ROW($A1)*4-COLUMNS(C:$F),)=OFFSET($A$1,(ROW()-3)*4+MOD(COLUMN()-8,4),)=OFFSET($A$1,ROW(A1)*4-4+MOD(COLUMN()-13,4),)四列变一列=OFFSET($F$1,INT(ROW(1:1)/4+3/4)-1,MOD(ROW()-1,4))=OFFSET($F$1,INT((ROW(1:1)-1)/4),MOD(ROW()-1,4))=OFFSET($F$1,ROUNDUP((ROW(1:1)/4),0)-1,MOD(ROW()-1,4))=OFFSET($F$1,(ROW()-1)/4,MOD(ROW()-1,4))重复四次填充=TEXT(INT(ROW()/4+3/4),"00")=IF(TRUNC((ROW()-1)/4,0)COUNTA($A$1:$I$10),"",INDEX($A$1:$I$10,MOD(SMALL(IF($A$1:$I$10"",ROW($A$1:$I$10)+COLUMN($A$1:$I$10)*100000),ROW())/100000)))｝将单元格一列分为多列如果有一列资料需要分为多列，只要先将此列选中，然后再选择“数据”→“分列”，此时会出现一个对话框，选“固定宽度”或“分隔符号”。

如为前者则下一步后只要用鼠标轻点资料即可以按任意宽度进行分割了，如为后者则只要有明显的分隔符号即可。