电子表格相关公式.doc

电子表格相关公式=LEFT(A1,LEN(A1)-1)作用：可以批量减少单元格多出的最后一位数譬如：A1单元格数据为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单元格数据为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单元格已经输入身份证号码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列绝对引用 $A1LEN得出单元格占多少字符如在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单元格身份证号码求，算出该身份证号码 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的作用就在