《一些非常实用的函数技巧.docVIP

一些非常实用的函数技巧 一、身份证技巧应用 1、从身份证中提取出生日期 需要使用的函数：IF、LEN、DATE、MID、TEXT。 函数解释： IF：判断一个条件是否满足中，如果满足返回一个值，如果不满足则返回另一个值。 LEN：返回文本字符串中的字符个数。语法：LEN(text)，如LEN（A1）代表A1单元格中第一个字符串的长度。 DATE：返回年、月、日。 MID：从文本串中指定的起始位置起返回指定长度的字符。 TEXT：将数值转换为按指定数字格式表示的文本。 函数示例： 1、=IF(LEN(B10)=15,DATE(MID(B10,7,2),MID(B10,9,2),MID(B10,11,2)),DATE(MID(B10,7,4),MID(B10,11,2),MID(B10,13,2))) 2、=IF(LEN(B9)=15,TEXT(MID(B9,7,6),19#-00-00),TEXT(MID(B9,7,8),#-00-00)) 公式分析：判断身份证的位数，如果是15位，则从单元格中字符串的第7位开始提取第7、8、9、10、11、12位数字；如果是18位，则从单元格中字符串的第7位开始提取第7、8、9、10、11、12、13、14位数字。 2、从身份证中提取性别信息 需要使用的函数：IF、MOD、LEN。 函数解释： IF：判断一个条件是否满足中，如果满足返回一个值，如果不满足则返回另一个值。 LEN：返回文本字符串中的字符个数。语法：LEN(text)，如LEN（A1）代表A1单元格中第一个字符串的长度。 MOD：返回两数相除的余数。结果的正负号与除数相同。 函数示例： =IF(MOD(IF(LEN(C2)=15,MID(C2,15,1),MID(C2,17,1)),2)=1,男,女) 公式分析：其中：LEN(C2)=15：检查身份证号码的长度是否是15位。MID(C2,15,1)：提取第15位的数字。MID(C2,17,1)：提取第17位的数字。 MOD(IF(LEN(C2)=15,MID(C2,15,1),MID(C2,17,1)),2)：用于得到给出数字除以指定数字后的余数，本例表示对提出来的数值除以2以后所得到的余数。 IF(MOD(IF(LEN(C2)=15,MID(C2,15,1),MID(C2,17,1)),2)=1,男,女)：如果除以2以后的余数是1，那么B2单元格显示为“男”，否则显示为“女”。 3、从身份证中提取年龄 需要使用的函数：IF、LEN、YEAE、NOW、VALUE。 IF：判断一个条件是否满足中，如果满足返回一个值，如果不满足则返回另一个值。 LEN：返回文本字符串中的字符个数。语法：LEN(text)，如LEN（A1）代表A1单元格中第一个字符串的长度。 MOD：返回两数相除的余数。结果的正负号与除数相同。 YEAE：返回某日期对应的年份。返回值为 1900 到 9999 之间的整数。 NOW：返回日期时间格式的当前日期和时间。该函数不需要参数。 VALUE：将一个代表数值的文本字符串转换成数值。 =IF(LEN(A1)=15,YEAR(NOW())-1900-VALUE(MID(A1,7,2)),IF(LEN(A1)=18,YEAR(NOW())-VALUE(MID(A1,7,4)),身份证错)) =IF(LEN(A1)=15,YEAR(TODAY())-TEXT(MID(A1,7,2),19#),IF(LEN(A1)=18,YEAR(TODAY())-MID(A1,7,4),身份证错)) 判断身份证的位数，如果是15位或18位，将执行相对应的函数运算，否则返回“身份证错”的信息。通过提取身份证中的出生年份，与当前年份相减得出年龄。 二、清除单元格中的空格 需要使用的函数：LEFT、LEN。 LEFT：从一个文本字符串的第一个字符开始返回指定个数的字符。 =LEFT(A1,LEN(A1)-2) A1单元格字符后面有2个空格，这个公式先用到LEN(A1)-2，得到单元格中非空格字符个数，再结合LEFT反回非空格字符。 三、编制证函数 1、非日期判断 =IF(F2=调出,MID(A2,1,3),IF(F2=退休,MID(A2,1,3),IF(F2=死亡,MID(A2,1,3),))) 2、日期判断 =IF($F$3=调出,DATE(MID(D$3,1,4),MID(D$3,6,2),MID(D$3,9,4)),IF($F$3=退休,DATE(MID(D$3,1,4),MID(D$3,6,2),MID(D$3,9,4)),IF($F$3=死亡,DATE(MID(D$3,1,4),MID(D$3,6,2),MID(D$3,9,4)),))) 四、将例如2005