电子表格相关公式.doc

电子表格相关公式
=LEFT(A1,LEN(A1)-1)
作用：可以批量减少单元格多出的最后一位数
譬如：A1单元格数据为：510121198210013811
在A2单元格建立公式：=LEFT(A1,LEN(A1)-1)
A2单元格得出的结果就是：51012119821001381

公式说明：LEFT（从左向右取数）
          A1（可变）
          ，（分隔号）
          LEN（判断A1单元格的内容一共有多少位）
          -1（减去1位）

=IF(MOD(IF(LEN(C2)=15,MID(C2,15,1),MID(C2,17,1)),2)=1,"男","女")
作用：输入身份证号码，提取性别。
譬如：C2单元格数据为：510121198210013816
在D2单元格建立公式：=IF(MOD(IF(LEN(C2)=15,MID(C2,15,1),MID(C2,17,1)),2)=1,"男","女")
则D2单元格得出的结果就是：男

=IF(LEN(D1)=18,(MID(D1,7,4)&"-"&MID(D1,11,2)&"-"&MID(D1,13,2)),(19&MID(D1,7,2)&"-"&MID(D1,9,2)&"-"&MID(D1,11,2)))
作用：输入身份证号码，提取出生年月日。
譬如：D1单元格已经输入身份证号码：510121198210013816
在E1单元格建立此公式：：=IF(LEN(D1)=18,(MID(D1,7,4)&"-"&MID(D1,11,2)&"-"&MID(D1,13,2)),(19&MID(D1,7,2)&"-"&MID(D1,9,2)&"-"&MID(D1,11,2)))
则E1单元格得出的结果就是：1982年10月1日

=TEXT(((LEN(A3)=15)*19)&MID(A3,7,8-(LEN(A3)=15)*2),"0年00月00日")
此公式也可以得出 出生年月日

A1单元格内容为：张三
自动在B1单元格生成：张三完蛋
在B1单元格建公式为：=A1&"完蛋"

绝对引用
$A$1
如：A1单元格内容为：5
在B1单元格编辑栏里建公式：=$A$1
无论A1单元格内容被剪截到工作薄什么位置 和内容变化 B1单元格内容都能跟踪其内容-_-|||
行绝对引用 A$1
列绝对引用 $A1

LEN
得出单元格占多少字符
如在A1单元格输入数字或文字：我今天很累
在B1单元格输入：=LEN（A1）
则可在B1单元格得到：5
用此方法可以计算出身份证号码位数。

从文本右侧或左侧删除指定数量的字符
=LEFT(A2, LEN(A2)-2)    删除A2单元格后面2个字符
=RIGHT(A2, LEN(A2)-2)  删除A2单元格前面2个字符
如A2单元格内容为：零一二三四五六七八九
在B2单元格建公式：=LEFT(A2, LEN(A2)-2) 
则B2单元格内容为：零一二三四五六七
在B2单元格建公式：=RIGHT(A2, LEN(A2)-2)
则B2单元格内容为：二三四五六七八九

已知A1单元格身份证号码：510121198210013816
要求，算出该身份证号码 2004年3月15日  是多大年纪
在B1单元格建入公式：=DATEDIF(MID(A1,7,4)&"-"&MID(A1,11,2)&"-"&MID(A1,13,2),"2004-03-15","Y")
则可得21
（后面的"Y"改成D  就是天数M 就是月）
参考：=DATEDIF(--TEXT(MID(A1,7,LEN(A1)/2-1),"#-00-00"),"2004-03-15","Y")

IF 、AND
=IF(AND(A2="男",B2>=60),"1类",IF(AND(A2="女",B2>=55),"1类","2类"))
A2为性别、B2为年龄、C3为类别
实现的结果应为：当A2单元格性别为女且B2单元格年龄>=55时，在C2显示1类，否则显示2类。
当A2单元格性别为男且B2单元格年龄>=60时，在C2显示1类，否则显示2类。
用以下公式可实现：
=IF(AND(A2="男",B2>=60),"1类",IF(AND(A2="女",B2>=55),"1类","2类"))

EXCEL中<>为不等于      <=为小于或等于       >=为大于或等于

=IF(ISERROR(FIND("好",A1)),"有好","无好")

如果A1单元格 内容为  大家好
要实现 判断A1单元格是否包含“好”这个字符，如果有则显示“有好”，无则显示“无好”
在B2单元格建此公式：=IF(ISERROR(FIND("好",A1)),"无好","有好")

RAND 函数
随机取数，可指定范围取数
譬如，取2-9之间数字。
可直接编辑公式：=RAND()*(2-9)+9
或引用单元格数字=RAND()*($H$3-$I$3)+$I$3

本例假设身份证号码存放于A2单元格中,实际应用中需针对数据存储位置进行调整；

一、从身份证中提取出生年月日
=TEXT(RIGHT(19&MID(A2,7,LEN(A2)/2-1),8),"#-##-##")
第一种，计算出来的格式为××年××月××日
=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(LEN(A2)=15,"19"&MID(A2,7,2)&"年"&MID(A2,9,2)&"月",MID(A2,7,4)&"年"&MID(A2,11,2)&"月")

二、根据身份证计算居民年龄  
=datedif(text(mid(A2,7,6+LEN(A2)=18)*2),"#-00-00"),TODAY(),"Y")
=INT(DAYS360(TEXT(RIGHT(19&MID(A2,7,LEN(A2)/2-1),8),"#-##-##"),TODAY())/360)
=IF(A2<>"",DATEDIF(TEXT((LEN(A2)=15)*19&MID(A2,7,6+(LEN(A2)=18)*2),"#-00-00"),TODAY(),"y"),)

三、根据身份证判断居民性别   
=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,"男","女")

四、根据身份证判断星座  
=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)

五、根据身份证判断生肖
=CHOOSE(MOD(MID(A2,LEN(A2)/2,2),12)+1,"鼠","牛","虎","兔","龙","蛇","马","羊","猴","鸡","狗","猪")
六、18位身份证判断验证码是否正确
=IF(LEN(A2)=18,MID("10X98765432",MOD(SUM(MID(A2,ROW(INDIRECT("1:17")),1)*2^(18-ROW(INDIRECT("1:17")))),11)+1,1),"")
七、根据身份证判断籍贯
=VLOOKUP(LEFT(A2,2)&"0000",对照表!A:B,2,FALSE) &VLOOKUP(LEFT(A2,6),Sheet2!对照表,2,FALSE)
一 、LEFT,RIGHT,MID
LEFT,RIGHT,MID，这三个函数都是文本的提取的。前两个的格式是一样的，只是提取的方向正好相反。LEFT是从左向右取，RIGHT是从右向左取。
LEFT(text,num_chars)
RIGHT(text,num_chars)
第一个参数text,是文本，是你想要在那提取东西的文本，可以是一个字符串，或是一个单元格。第二个参数chars是想要提取的个数。
比如：=LEFT(A1,2)，也是从A1单元格的文本里，从左边第一位开始，向右提取两位。比如A1的数据是：“找吃的蚂蚁”，得到的结果是：“找吃”。
那么：=RIGHT(A1,2)，也就是从A1单元格的文本里，从右边第一位开，向左提取两位，这样就得到：“蚂蚁”。
当然你也可以这样写，如果你喜欢：=LEFT("找吃的蚂蚁",2)。
MID这个函数也是从左向右提取的，但不一定是从第一个起，可以从中间开始。先看它的格式吧：MID(text,start_num,num_chars)
第一个参数也是text，它的属性与前面两个是一样的。第二个参数，star_num，也就是要提取的开始字符，第三个参数num_chars是要提取的个数。
用前面的例子得到“找吃”的结果的：=MID(A1,1,2),而得到“蚂蚁”的公式是：=MID(A1,4,2)。
第一个公式的意思是：在A1单元格里提取文本，从文本的第1位开始，向右提取2个。
第二个公式的意思是：在A1单元格里提取文本，从文本的第4位开始，向右提取2个。
记住了，MID函数只能从左向右提取的。
还得讲一个函数才能讲例子：LEN
这是返回字符个数的函数。它有一位兄弟：LENB，它则是返回字节数的，用于双字节。
还得举例吧：还是刚才的数据，A1里的是“找吃的蚂蚁”。那么：
=LEN(A1) 就会返回：5
LENB(A1)就会返回：10
就这么简单，这几个函数的用法就这样了。是不是很容易呀？以前也说过了，函数功能的强大是得多个函数套用起来才显现出来的。下面的一个例子是很多人问过的。公式是从罗刚君同志那里拿来的，是个会计用到的功能吧：比如在A列（假设是从A2开始）里输入的是数值（会计算的当然是钱了），在B列到L列分别自动填入相应的数值（也就是从亿位到元角分了），并在前面加个“￥”这样的符号：
=IF(OR($A2=0,LEN("￥"&$A2*100)<13-COLUMN()),"",LEFT(RIGHT("￥"&$A2*100,13-COLUMN())))
这个公式主体分两层，一个是IF判断，里面的前部分是判断的条件，后面的LEFT(RIGHT("￥"&$A2*100,13-COLUMN()))，就是数据的提取了。这里，同时用到了LEFT与RIGHT两个函数，我们看看它们是怎么工作的：以B2里的公式为例吧，我把它简化一下，13-COLUMN()，在B2里COLUMN()返回2，13-12=11，公式就简化为：LEFT(RIGHT("￥"&$A2*100,11))，这样好看多了。再打个比方，A2的数据是：375.25，那么$A2*100=37525，这个*100的作用就在这，把小数点乘没了，这样就可以直接一帆风顺地提取数字了。"￥"&$A2*100也就是在前面再加个“￥”，就成了￥375.25。公式变成了=LEFT(RIGHT(￥37525,11))。
RIGHT(￥37525,11)，就是在“￥375.25”里从右向左提取11个字符，这里只有6个，函数就把它们全都提了出来。
也就是￥37525，把它放回公式里：
LEFT(￥37525),这里，LEFT的第二个参数省略了，使用默认值，从左向右提取1个字符，得到￥。