身份证的信息提取、字符与字节的区别、时间与日期的公式计算与函数、数学计算函数巧妙应用、数组的基础计算法则、TRUE与FALSE的数组应用

身份证的信息提取：

首先取出前6位地区码，用LEFT函数，=LEFT(A2,6)，如下图：

接下来要把地区码转成地区，用到VLOOKUP函数，在提取信息的工作表里输入=VLOOKUP(LEFT(A2,6),地区编码!A:B,2,0)，地区编码是另一个sheet页，取第2列，精确匹配，回车，如下图：

报错了，因为提取信息工作表中的地区码是文本类型，地区编码工作表里是数字类型，把类型转换一下，把LEFT(A2,6)+0就变成数字类型的了，修改后：=VLOOKUP(LEFT(A2,6)+0,地区编码!A:B,2,0)，回车如下图：

这下ok了，双击填充完全列，接下来看出生年月日，用到MID函数：=MID(A2,7,8)，如下图：

出生年月日有点丑陋，修改一下，修改后：=MID(A2,7,4)&"年"&MID(A2,11,2)&"月"&MID(A2,13,2)&"日"，如下图：

这下ok了，双击填充完全列，接下来看性别，用到MID函数：=MID(A2,17,1)，如下图：

性别是6，这个需要转换一下，用到if函数，借助-1的乘幂来判断，-1的偶数乘幂=1，-1的奇数乘幂=-1，修改后：=IF(-1^MID(A2,17,1)=1,"女","男")，如下图：

这下ok了，双击填充完全列

身份证真伪辨别法:
身份证最后一位=（身份证每一位乘以对应乘数)的和除以11的余数的对比值

字符与字节的区别：
字符：每一个汉字、字母、数字、标点、空格都算一个字符
字节：用于计量存储容量的一种计量单位
占1个字节：数字、字母、空格、英文（半角）标点
占2个字节：汉字、中文（全角）标点
F22里输入有W。A 5.，=LENB(F22)，回车返回9，LENB是获取字节的长度

计算数值和单位，LENB(A2)-LEN(A2)是汉字的个数，公式是：=RIGHT(A2,LENB(A2)-LEN(A2))，单位可以通过如下图提取出来：

现在计算数值，总长度-汉字的数量=数值的个数，LEN(A2)-(LENB(A2)-LEN(A2))就是数值的个数，公式是：=LEFT(A2,LEN(A2)-(LENB(A2)-LEN(A2)))，如下图：

时间与日期的公式计算与函数：
时间与日期的原理：
在任一空白单元格里输入1，点击日期->短日期格式，就变成1900/1/1了，Ctrl+;显示今天的日期，输入两个日期，能算出相差的天数，把两个日期转成常规的格式(显示出数值)，输入=F15-F14，F15和F14是两个日期的位置，回车，就显示出相差的天数，实际就是两个数值的差

时间与日期的公式计算(本质：所有数值单位都是天)

0对应的日期是1900/1/0，

本质：所有数值单位都是天，右侧的数值是1/24天，转成时间就是1个小时，左侧

计算出持续时间(分钟)，把开始时间和结束时间转成常规，都变成小数，按照公式：=(H14-G14)*24*60，先转成小时：*24，再转成分钟：*60，如下图：

根据开始时间和分钟计算结束时间，如下图：

时间与日期的函数计算：
=DAYS(结束日期，开始日期)：计算两个日期之间相差的天数，把上面的间隔天数换成函数=DAYS(H9,G9)计算，结果一样
=DATEDIF(开始日期，结束日期，单位代码)：计算两个日期之间相差的天数或者月数或者年数  Y：年数  M：月数  D：天数  YM：月数，忽略年  YD：天数，忽略年  MD：天数，忽略月

依次传入YM：月数，忽略年       YD：天数，忽略年     MD：天数，忽略月，就会得出对应的结果，如下图：

通过公式计算间隔：=DATEDIF(E9,F9,"y")&"年"&DATEDIF(E9,F9,"ym")&"月"&DATEDIF(E9,F9,"md")&"天"，如下图：

=NETWORKDAYS(开始日期,结束日期,特殊节假日)：计算两个日期之间工作日的天数(默认六/日双休)，公式：=NETWORKDAYS(C9,D9,H9:H11)，如下图：

=NETWORKDAYS.INTL(开始日期,结束日期,休息日,特殊节假日)：计算两个日期之间工作日的天数(自定义休息日)，公式：=NETWORKDAYS.INTL(C4,D4,11,H9:H11)，如下图：

=WORKDAY(开始日期，天数，特殊节假日)：计算出工作的结束日期(默认六、日双休)，公式：=WORKDAY(C14,D14,H11)，法定节假日传H11，如下图：

=WORKDAY.INTL(开始日期，天数，休息日，特殊节假日)：计算出工作的结束日期(自定义休息日)，公式：=WORKDAY.INTL(C19,D19,12,H11)，法定节假日传H11，如下图：

=YEAR(日期)      =MONTH(日期)     =DAY(日期)：提取日期中年、月、日
=HOUR(时间)    =MINUTE(时间)    =SECOND(时间)：提取时间中的小时、分钟、秒

数学计算函数巧妙应用：
数学舍入函数：ROUND函数、ROUNDUP函数、ROUNDDOWN函数、INT函数
先看ROUND函数，四舍五入函数，传入一个值，再传入一个保留小数的位数，公式：=ROUND(C4,2)，如下图：

ROUNDUP函数，向上进位函数，如下图：

ROUNDDOWN函数，向下进位函数，如下图：

INT函数：取整函数，公式：=INT(C4)，如下图：

老板比较苛刻，不到一个小时按0小时计算，就是取整算小时，公式：=INT((D4-C4)*24)*$J$4，往下拖，如下图：

老板比较任性，40/1小时，20/半小时以上1小时以下，工资就是整数小时*40+if函数(小数>=0.5,20,0)，整数部分公式：=INT((D4-C4)*24)*$L$4，如果报错，类型转成数值，如果有小数，要去掉小数，然后求出小数部分：=(D4-C4)*24-INT((D4-C4)*24)，整个的减去整数部分就是小数部分，然后判断，小数部分的公式是：=IF((D4-C4)*24-INT((D4-C4)*24)>=0.5,20,0)，然后求和，公式就是：=INT((D4-C4)*24)*$L$4+IF((D4-C4)*24-INT((D4-C4)*24)>=0.5,20,0)，回车，往下拖，如下图：

MOD函数：算除法的余数，用mod函数求性别，公式：=IF(MOD(MID(A2,17,1),2)=0,"女","男")，除2，=0就是偶数，如下图：

mod函数还有特殊用法，任何数除1，都会得到小数部分，公式：=MOD(D22,1)，与INT函数相反，用这个函数求上面的工资，之前公式是：=INT((D4-C4)*24)*$L$4+IF((D4-C4)*24-INT((D4-C4)*24)>=0.5,20,0)，改成：=INT((D4-C4)*24)*$L$4+IF(MOD((D4-C4)*24,1)>=0.5,20,0)，回车，往下拖，如下图：

数组的基础计算法则：
数组的概念：一组数据与另一组数据分别发生计算，如有一组数据1-9，另一组数据10-18，，同一行的相乘在求和，用笨方法太慢了，用数组的知识，如下图：

回车结果不对，是正常函数的用发，用Ctrl+Shift+Enter结束数组输入，这下结果就对了，690

用数组知识求4个季度的合计，点击进去可以发现上面公式里有大括号，这是数组的标记，但是公式明显不对，单价要绝对引用

公式改成：=SUM(C5:F5*$C$4:$F$4)，用Ctrl+Shift+Enter结束数组输入，这下结果就对了，往下拖也正常

接下来有成本的，要减掉成本，公式：=SUM(C6:F6*($C$5:$F$5-$C$4:$F$4))，如下图：

这里隐藏了一列，取消隐藏，可以看到J列出现了达标标准，400000，因此利润合计这列需要判断一下，公式修改后：=IF(SUM(C6:F6*($C$5:$F$5-$C$4:$F$4))>=$J$4,"达标","不达标")，如下图：

TRUE与FALSE的数组应用：
is函数返回TRUE与FALSE，如

回车，返回TRUE，改成=ISTEXT(F23)，回车就返回FALSE
True：正确(1)          False：错误(0)，true的本质就是1，false的本质是0，可以通过下面验证，如下图：

数组嵌套函数应用：
达标数量计算：产品A、产品B、产品C和产品D，1-12月份的数量，右侧四列是产品A、产品B、产品C和产品D的达标标准，这里用到数组的知识，选中F到I列，公式：=B4:E15>=J4:M4，用Ctrl+Shift+Enter结束数组输入，如下图：

点击Ctrl+Shift+Enter结束数组输入，看到选中区域出现TRUE和FALSE，TRUE是达标的，现在算出达标数量，就是把所有的TRUE加起来，用(B4:E15>=J4:M4)*1，即TRUE*1和FALSE*1，相当于1*1和0*1，然后求和就出来达标数量，公式：=SUM((B4:E15>=J4:M4)*1)，点击Ctrl+Shift+Enter结束数组输入，如下图：

浅析LOOKUP函数：vlookup完全可以取代LOOKUP函数，低版本没有vlookup函数，这个lookup已经被淘汰了，可以不用学习