庖丁解牛之：人力资源实战应用系列函数对于常用EXCEL的人来说，函数这个字眼也许并不陌生，但至于函数的用途与威力，了解或熟悉的人恐怕是少之又少。我们常见的函数中，有常用函数，统计函数，文本函数，信息函数，财务函数等等。但好像未有听说过人力资源函数这个字眼。我在这里所说的人力资源函数并不是一组专门的函数，而是根据人力资源实践工作中，把经常用到的一些函数集中起，以实现人力资源工作者在实践中用极少数相关的函数在来完成大部分工作。我们在EH论坛中随便看看，不泛好帖，特别是函数，每天数以千计的帖子中，光是精华帖就可以让人目不接霞，如http://club.excelhome.net/viewth...p;extra=&page=1，http://club.excelhome.net/viewth...p;extra=&page=1，http://club.excelhome.net/viewth...p;extra=&page=1，，，http://www.excelhome.net/catalog.asp?cate=4 等等，都即细致，又全面，是函数初学者了良师益友。我也常常拜读。但是，里边好的东西太多了，而我们真正用到得虽然在其中，但要想把他归纳总结出来，为自己所用，恐怕是“冰冻三尺，非一日之寒。”一个人的精力是用限的，人一生要做的事太多，好的东西我们不可能把占完，特别是知识，够用就好，或者说，先解当务之急，只知其然，待抽出时间，自己也有点底子是再知其所以然。闲话少说，接下来就跟着我一起开始我们的EXCEL人力资源函数之旅吧。当然，我们要想学习函数，首先，一点点基础还是要的，至少我们要知道函数是怎么回事吧。那下面我们就来看一看函数的一点点基础知识，对公式和函数有个初步的了解。一、函数应用基础（一）函数和公式1.什么是函数Excel函数即是预先定义，执行计算、分析等处理数据任务的特殊公式。以常用的求和函数SUM为例，它的语法是“SUM(number1,number2,......)”。其中“SUM”maintenancemeasures,thereisabigsecurityrisk,managementhashadagreatimpacttothecity.3.1-8busterminalstationstatusinYibincitylayouts(4)hoursofoperationmostofYibincitybuslinesin5:30-6:20,andbasicallymeettheYibintravelneeds.Bus
称为函数名称，一个函数只有唯一的一个名称，它决定了函数的功能和用途。函数名称后紧跟左括号，接着是用逗号分隔的称为参数的内容，最后用一个右括号表示函数结束。参数是函数中最复杂的组成部分，它规定了函数的运算对象、顺序或结构等。使得用户可以对某个单元格或区域进行处理，如分析存款利息、确定成绩名次、计算三角函数值等。按照函数的来源，Excel函数可以分为内置函数和扩展函数两大类。前者只要启动了Excel，用户就可以使用它们；而后者必须通过单击“工具→加载宏”菜单命令加载，然后才能像内置函数那样使用。2.什么是公式函数与公式既有区别又互相联系。如果说前者是Excel预先定义好的特殊公式，后者就是由用户自行设计对工作表进行计算和处理的公式。以公式“=SUM(E1:H1)*A1+26”为例，它要以等号“=”开始，其内部可以包括函数、引用、运算符和常量。上式中的“SUM(E1:H1)”是函数，“A1”则是对单元格A1的引用(使用其中存储的数据)，“26”则是常量，“*”和“+”则是算术运算符(另外还有比较运算符、文本运算符和引用运算符)。如果函数要以公式的形式出现，它必须有两个组成部分，一个是函数名称前面的等号，另一个则是函数本身。（二）函数的参数函数右边括号中的部分称为参数，假如一个函数可以使用多个参数，那么参数与参数之间使用半角逗号进行分隔。参数可以是常量(数字和文本)、逻辑值(例如TRUE或FALSE)、数组、错误值(例如#N/A)或单元格引用(例如E1:H1)，甚至可以是另一个或几个函数等。参数的类型和位置必须满足函数语法的要求，否则将返回错误信息。1.常量常量是直接输入到单元格或公式中的数字或文本，或由名称所代表的数字或文本值，例如数字“2890.56”、日期“2003-8-19”和文本“黎明”maintenancemeasures,thereisabigsecurityrisk,managementhashadagreatimpacttothecity.3.1-8busterminalstationstatusinYibincitylayouts(4)hoursofoperationmostofYibincitybuslinesin5:30-6:20,andbasicallymeettheYibintravelneeds.Bus
都是常量。但是公式或由公式计算出的结果都不是常量，因为只要公式的参数发生了变化，它自身或计算出来的结果就会发生变化。2.逻辑值逻辑值是比较特殊的一类参数，它只有TRUE(真)或FALSE(假)两种类型。例如在公式“=IF(A3=0,"",A2/A3)”中，“A3=0”就是一个可以返回TRUE(真)或FALSE(假)两种结果的参数。当“A3=0”为TRUE(真)时在公式所在单元格中填入“0”，否则在单元格中填入“A2/A3”的计算结果。3.数组数组用于可产生多个结果，或可以对存放在行和列中的一组参数进行计算的公式。Excel中有常量和区域两类数组。前者放在“{}”(按下Ctrl+Shift+Enter组合键自动生成)内部，而且内部各列的数值要用逗号“,”隔开，各行的数值要用分号“;”隔开。假如你要表示第1行中的56、78、89和第2行中的90、76、80，就应该建立一个2行3列的常量数组“{56,78,89;90,76,80}。区域数组是一个矩形的单元格区域，该区域中的单元格共用一个公式。例如公式“=TREND(B1:B3,A1:A3)”作为数组公式使用时，它所引用的矩形单元格区域“B1:B3,A1:A3”就是一个区域数组。4.错误值使用错误值作为参数的主要是信息函数，例如“ERROR.TYPE”函数就是以错误值作为参数。它的语法为“ERROR.TYPE(error_val)”，如果其中的参数是#NUM!，则返回数值“6”。5.单元格引用单元格引用是函数中最常见的参数，引用的目的在于标识工作表单元格或单元格区域，并指明公式或函数所使用的数据的位置，便于它们使用工作表各处的数据，或者在多个函数中使用同一个单元格的数据。还可以引用同一工作簿不同工作表的单元格，甚至引用其他工作簿中的数据。根据公式所在单元格的位置发生变化时，单元格引用的变化情况，我们可以引用分为相对引用、绝对引用和混合引用三种类型。以存放在F2maintenancemeasures,thereisabigsecurityrisk,managementhashadagreatimpacttothecity.3.1-8busterminalstationstatusinYibincitylayouts(4)hoursofoperationmostofYibincitybuslinesin5:30-6:20,andbasicallymeettheYibintravelneeds.Bus
单元格中的公式“=SUM(A2:E2)”为例，当公式由F2单元格复制到F3单元格以后，公式中的引用也会变化为“=SUM(A3:E3)”。若公式自F列向下继续复制，“行标”每增加1行，公式中的行标也自动加1。如果上述公式改为“=SUM($A$3:$E＄3)”，则无论公式复制到何处，其引用的位置始终是“A3:E3”区域。混合引用有“绝对列和相对行”，或是“绝对行和相对列”两种形式。前者如“=SUM($A3:$E3)”，后者如“=SUM(A$3:E$3)”。上面的几个实例引用的都是同一工作表中的数据，如果要分析同一工作簿中多张工作表上的数据，就要使用三维引用。假如公式放在工作表Sheet1的C6单元格，要引用工作表Sheet2的“A1:A6”和Sheet3的“B2:B9”区域进行求和运算，则公式中的引用形式为“=SUM(Sheet2!A1:A6,Sheet3!B2:B9)”。也就是说三维引用中不仅包含单元格或区域引用，还要在前面加上带“!”的工作表名称。假如你要引用的数据来自另一个工作簿，如工作簿Book1中的SUM函数要绝对引用工作簿Book2中的数据，其公式为“=SUM([Book2]Sheet1!SAS1:SAS8,[Book2]Sheet2!SBS1:SBS9)”，也就是在原来单元格引用的前面加上“[Book2]Sheet1!”。放在中括号里面的是工作簿名称，带“!”的则是其中的工作表名称。即是跨工作簿引用单元格或区域时，引用对象的前面必须用“!”作为工作表分隔符，再用中括号作为工作簿分隔符。不过三维引用的要受到较多的限制，例如不能使用数组公式等。提示：上面介绍的是Excel默认的引用方式，称为“A1引用样式”。如果你要计算处在“宏”内的行和列，必须使用“R1C1引用样式”。在这种引用样式中，Excel使用“R”加“行标”和“C”加“列标”的方法指示单元格位置。启用或关闭R1C1引用样式必须单击“工具→选项”菜单命令，打开对话框的“常规”选项卡，选中或清除“设置”下的“R1C1引用样式”选项。由于这种引用样式很少使用，限于篇幅本文不做进一步介绍。6.嵌套函数maintenancemeasures,thereisabigsecurityrisk,managementhashadagreatimpacttothecity.3.1-8busterminalstationstatusinYibincitylayouts(4)hoursofoperationmostofYibincitybuslinesin5:30-6:20,andbasicallymeettheYibintravelneeds.Bus
除了上面介绍的情况外，函数也可以是嵌套的，即一个函数是另一个函数的参数，例如“=IF(OR(RIGHTB(E2,1)="1"，RIGHTB(E2,1)="3"，RIGHTB(E2,1)="5"，RIGHTB(E2,1)="7"，RIGHTB(E2,1)="9"),"男","女")”。其中公式中的IF函数使用了嵌套的RIGHTB函数，并将后者返回的结果作为IF的逻辑判断依据。7.名称和标志为了更加直观地标识单元格或单元格区域，我们可以给它们赋予一个名称，从而在公式或函数中直接引用。例如“B2:B46”区域存放着学生的物理成绩，求解平均分的公式一般是“=AVERAGE(B2:B46)”。在给B2:B46区域命名为“物理分数”以后，该公式就可以变为“=AVERAGE(物理分数)”，从而使公式变得更加直观。给一个单元格或区域命名的方法是：选中要命名的单元格或单元格区域，鼠标单击编辑栏顶端的“名称框”，在其中输入名称后回车。也可以选中要命名的单元格或单元格区域，单击“插入→名称→定义”菜单命令，在打开的“定义名称”对话框中输入名称后确定即可。如果你要删除已经命名的区域，可以按相同方法打开“定义名称”对话框，选中你要删除的名称删除即可。由于Excel工作表多数带有“列标志”。例如一张成绩统计表的首行通常带有“序号”、“姓名”、“数学”、“物理”等“列标志”(也可以称为字段)，如果单击“工具→选项”菜单命令，在打开的对话框中单击“重新计算”选项卡，选中“工作簿选项”选项组中的“接受公式标志”选项，公式就可以直接引用“列标志”了。例如“B2:B46”区域存放着学生的物理成绩，而B1单元格已经输入了“物理”字样，则求物理平均分的公式可以写成“=AVERAGE(物理)”。需要特别说明的是，创建好的名称可以被所有工作表引用，而且引用时不需要在名称前面添加工作表名(这就是使用名称的主要优点)，因此名称引用实际上是一种绝对引用。但是公式引用“列标志”时的限制较多，它只能在当前数据列的下方引用，不能跨越工作表引用，但是引用“列标志”的公式在一定条件下可以复制。从本质上讲，名称和标志都是单元格引用的一种方式。因为它们不是文本，使用时名称和标志都不能添加引号。maintenancemeasures,thereisabigsecurityrisk,managementhashadagreatimpacttothecity.3.1-8busterminalstationstatusinYibincitylayouts(4)hoursofoperationmostofYibincitybuslinesin5:30-6:20,andbasicallymeettheYibintravelneeds.Bus
（三）函数输入方法对Excel公式而言，函数是其中的主要组成部分，因此公式输入可以归结为函数输入的问题。1.“插入函数”对话框“插入函数”对话框是Excel输入公式的重要工具，以公式“=SUM(Sheet2!A1:A6,Sheet3!B2:B9)”为例，Excel输入该公式的具体过程是：首先选中存放计算结果(即需要应用公式)的单元格，单击编辑栏(或工具栏)中的“fx”按钮，则表示公式开始的“=”出现在单元格和编辑栏，然后在打开的“插入函数”对话框中的“选择函数”列表找到“SUM”函数。如果你需要的函数不在里面，可以打开“或选择类别”下拉列表进行选择。最后单击“确定”按钮，打开“函数参数”对话框。对SUM函数而言，它可以使用从number1开始直到number30共30个参数。对上面的公式来说，首先应当把光标放在对话框的“number1”框中，单击工作簿中的“Sheet2!”工作表标签，“Sheet2!”即可自动进入其中，接着鼠标拖动选中你要引用的区域即可。接着用鼠标单击对话框的“number2”框，单击工作簿中的“Sheet3!”工作表标签，其名称“Sheet3!”即可自动进入其中，再按相同方法选择要引用的单元格区域即可。上述方法的最大优点就是引用的区域很准确，特别是三维引用时不容易发生工作表或工作簿名称输入错误的问题。2.编辑栏输入如果你要套用某个现成公式，或者输入一些嵌套关系复杂的公式，利用编辑栏输入更加快捷。首先选中存放计算结果的单元格；鼠标单击Excel编辑栏，按照公式的组成顺序依次输入各个部分，公式输入完毕后，单击编辑栏中的“输入”(即“√”)按钮(或回车)即可。手工输入时同样可以采取上面介绍的方法引用区域，以公式“=SUM(Sheet2!A1:A6,Sheet3!B2:B9)”为例，你可以先在编辑栏中输入“=SUM()”maintenancemeasures,thereisabigsecurityrisk,managementhashadagreatimpacttothecity.3.1-8busterminalstationstatusinYibincitylayouts(4)hoursofoperationmostofYibincitybuslinesin5:30-6:20,andbasicallymeettheYibintravelneeds.Bus
，然后将光标插入括号中间，再按上面介绍的方法操作就可以引用输入公式了。但是分隔引用之间的逗号必须用手工输入，而不能像“插入函数”对话框那样自动添加。附件档案附件.rar(158.43KB)二、人力资源建库与建表的几个函数一般说来，企业要对人员进行管理，一个简单明了的花名册与人员的电子档案是必有可少的，但企业的人员是在不断的流动的，特别是针对一些低技能的劳动密集型企业，员工的流动量是相当大的，每进出一个员工，人事部门要人事部门都要做若干的资料，一些大型一点的企业，一个月一招就是好几百，上千人。这样的工作量相当大。通常情况下，人事档案主要是根据企业的一些基本事项与员工的身份证信息作为基础来建立，而真正麻烦与肯出错的内容也就是身份证，如姓名，性别，出生日期，籍贯，身份证号，年龄，工龄，等等，都是围绕着身份证来的，特别是身份证号，最简单的18个数字，也是最容易出错的，对果用手工对的话，相当枯燥无味，当然正确率比较低，特别是一些假证，我们不能每一个都是靠肉眼去识别。如果用函数解决，就很多问题都迎刃而解了。（附件）根据附件表来看，这张简单的人事档案表有21项，除去我们要隐藏的辅助列也还有十七八列，如果是一项一项输，就算是100个人，我们够输入一阵子的了，一名人事工作者不可能只做输入档案一项工作。如果把大量的时间花在档案上，其它的工作也就无法展开了。接下来，我看就从第一个涉及函数的单元格开始讲解：=IF(M3="","",IF(MOD(RIGHT(LEFT(M3,17)),2),"男","女"))在E3单元格中，我们看到了有这样一个公式，她是由四个函数构成的，其目的是根据身份证号的第17位数来判断该编号人员的性别。maintenancemeasures,thereisabigsecurityrisk,managementhashadagreatimpacttothecity.3.1-8busterminalstationstatusinYibincitylayouts(4)hoursofoperationmostofYibincitybuslinesin5:30-6:20,andbasicallymeettheYibintravelneeds.Bus
我们先来解析这四个函数中的第一个：IF函数。IF函数是一个条件判断函数，我们来看下她的写法与用途。用途：执行逻辑判断，它可以根据逻辑表达式的真假，返回不同的结果，从而执行数值或公式的条件检测任务。语法：IF(logical_test，value_if_true，value_if_false)。参数：Logical_test计算结果为TRUE或FALSE的任何数值或表达式；Value_if_true是Logical_test为TRUE时函数的返回值，如果logical_test为TRUE并且省略了value_if_true，则返回TRUE。而且Value_if_true可以是一个表达式；Value_if_false是Logical_test为FALSE时函数的返回值。如果logical_test为FALSE并且省略value_if_false，则返回FALSE。Value_if_false也可以是一个表达式。实例：公式“=IF(C2>=85，"A"，IF(C2>=70，"B"，IF(C2>=60，"C"，IF(C2=85成立，则D2单元格被赋值“A”；如果第一个逻辑判断表达式C2>=85不成立，则计算第二个IF语句“IF(C2>=70”；以此类推直至计算结束，该函数广泛用于需要进行逻辑判断的场合。在本例中，IF函数主要起到了两个作用，第一次出现（IF(M3＝””,””……，这是什么意思呢，它是起一个错误控制的作用，意思为如果M3为空值，（即M3这个单元格没有内容的话，就不对它进行任何的操作，其反回结果为空；第二次出现（……IF(MOD(……，在这里又是什么意思呢？在这个地方是前边一个条件的补充，即如果M3单元格不为空呢，就执行后边的操作。接下来，再分析=IF(M3="","",IF(MOD(RIGHT(LEFT(M3,17)),2),"男","女"))中的LEFT函数。用途：根据指定的字符数返回文本串中的第一个或前几个字符。此函数用于双字节字符。语法：LEFT(text，num_chars)或LEFTB(text，num_bytes)。参数：Text是包含要提取字符的文本串；Num_chars指定函数要提取的字符数，它必须大于或等于0。maintenancemeasures,thereisabigsecurityrisk,managementhashadagreatimpacttothecity.3.1-8busterminalstationstatusinYibincitylayouts(4)hoursofoperationmostofYibincitybuslinesin5:30-6:20,andbasicallymeettheYibintravelneeds.Bus
Num_bytes按字节数指定由LEFTB提取的字符数。实例：如果A1=电脑爱好者，则LEFT(A1，2)返回“电脑”，LEFTB(A1，2)返回“电”。该函数在这里的作用是将M3中的18位身份证号的前17号提取出来，以供我们的MOD函数使用。为什么要把它取出来呢，我们都知道，按身份证编码的规则，18位数的身份证前17位是性别位，它所记录的是性别信息。所以，我们先将它提取出来。接下来，要分析的是=IF(M3="","",IF(MOD(RIGHT(LEFT(M3,17)),2),"男","女"))中的RIGHT函数，RIGHT或RIGHTB用途：RIGHT根据所指定的字符数返回文本串中最后一个或多个字符。RIGHTB根据所指定的字节数返回文本串中最后一个或多个字符。语法：RIGHT(text，num_chars)，RIGHTB(text，num_bytes)。参数：Text是包含要提取字符的文本串；Num_chars指定希望RIGHT提取的字符数，它必须大于或等于0。如果num_chars大于文本长度，则RIGHT返回所有文本。如果忽略num_chars，则假定其为1。Num_bytes指定欲提取字符的字节数。实例：如果A1=学习的革命，则公式“=RIGHT(A1，2)”返回“革命”，=RIGHTB(A1，2)返回“命”。 从边来的解释来看，RIGHT函数是把LEFT函数所提取出来的17位数最又边的一位数取出来，当然，这里的两步如果换成MID(M3,17,1)其效果也是一样的。至于MID,安排到另外的实例中讲解。接下来就是MOD出场了。公式中的MOD函数其用途为：返回两数相除的余数，其结果的正负号与除数相同。语法：MOD(number，divisor)参数：Number为被除数，Divisor为除数(divisor不能为零)。实例：如果A1=51，则公式“=MOD(A1，4)”返回3；=MOD(-101，-2)返回–1。看了上边的解释，我们应该可以看得出，MOD在这里就是将前边两位函数所取出来的身份证编码中的第17位数除以2，得出其余数，根据余数来验证第身份证的第17位数是奇数还是偶数。最后把任务交给公式中的第二个IF函数。maintenancemeasures,thereisabigsecurityrisk,managementhashadagreatimpacttothecity.3.1-8busterminalstationstatusinYibincitylayouts(4)hoursofoperationmostofYibincitybuslinesin5:30-6:20,andbasicallymeettheYibintravelneeds.Bus
编码规则中，如果第17位是奇数，则该身份证为男性，为偶数则是女性。这一个IF函数在这里就是根据MOD所求出来的余数确定，如有余数，则是男，反之，则是女，这就是后边那的“男”，与“女”。根据以上的步骤，我们就完成了第一个公式的编写。最后，我也用一句小学老师的语气问一下，大家是否明白了呢。如果明白，请自己动手操作。我知道在EH论坛，这点东西也就是雕虫小技，班门弄斧。如帖子有不妥之处，还敬请各位老师与朋友批评批评。接下来，应该是第二/四个公式了，为什么把第二个和第四个一起分析呢，因为他们比较相似。先看一下公式吧：=DATEDIF(F3,TODAY(),"y") =IF(H3="","",DATEDIF(H3,TODAY(),"y"))在这两个公式当中，都运用了两个日期函数，一个是DATEDIF，一个是TODAY函数，但是他们的功能确有差别。先看一下TODAY函数吧，函数：TODAY用途：返回系统当前日期的序列号。参数：无语法：TODAY()实例：公式“=TODAY()”返回2001-8-28(执行公式时的系统时间)。这是一个比较特殊的函数，它没有参数，或都说它的参数就是一个空括号，从前边的资料当中，我们可以看出，TODAY函数在这两个公式中都是作为DAETDIF函数的参数身份出现的。但可能有朋友要问，为什么是用TODAY呢，难道用在DATEDIF的第二个参数位输入今天的日期不行吗？行，但是是有差距的，因为直接输入日期，它是一个静态数，也就是永远不会改变，但我们的档案名册不可能只用一天两天或是一年半载。如输入（2010-04-19）那么，它就永远是这个日期，而用TODAY函数呢，则会随着你的系统日期变换，永远保持着最新日期。至于用那个，你自己可以选择。DATEDIF函数：这个函数可以计算两个日期间的间隔，它能够显示周、月、年。其语法结构为：=DATEDIF(第一日期,第二日期,"差异")maintenancemeasures,thereisabigsecurityrisk,managementhashadagreatimpacttothecity.3.1-8busterminalstationstatusinYibincitylayouts(4)hoursofoperationmostofYibincitybuslinesin5:30-6:20,andbasicallymeettheYibintravelneeds.Bus
第一日期:两个日期中较早的一个日期第二日期：两日期中距今天最近的日期。"差异":这正是你想要计算的.（表为参数三内容，不同的选择会有不同的结果）"d" 两日期之间的天数. "m" 两日期之间的月数. "y" 两日期之间的年数. "yd" 把两日期年成是同一年的相差月数."ym" 把两日期年成是同一年的相差天数."md" 把两日期年成是同一年、同一月的相差天数.从上面的资料我们可以看出，DATEDIF所执行的就是用“出生日期”（即H3）与“当前日期”（即TODAY求出来的）作比较，然后求出它们之间的整年差异数，即为我们想要求得的年龄。最后一个IF同前一个公式第一次出现一样，都为了屏闭错误值。好的，我们这个单元格的内容也就分析完了。附件附件二.rar(2.96KB)品完茶后，我们继续前边的内容，看一看第三个填有函数的单元格，即为出生日期单元格。=--TEXT(IF(LEN(M3)=15,19,"")&MID(M3,7,6+IF(LEN(M3)=18,2)),"#-00-00")我们将该公式简化为：=--TEXT(MID，M3,7,8),"#-00-00")如果资料里的身份证号全是18号数，这样写是最方便的，但是，这样写的唯一缺点就是，如果资料里边有15位的，就会出现错误值。简化后的公式，只有嵌套两个级别，一个是TEXT函数，另一个就是作为参数出现的MID函数，由于MID函数我们在前讲中已经讲解过，相信朋友们已经掌握，这里只说明一下它的作用是将身份证信息中的第7—14maintenancemeasures,thereisabigsecurityrisk,managementhashadagreatimpacttothecity.3.1-8busterminalstationstatusinYibincitylayouts(4)hoursofoperationmostofYibincitybuslinesin5:30-6:20,andbasicallymeettheYibintravelneeds.Bus
位数提取出来。即出生日期字段提取给TEXT处理，让其设置日期格式。接下来请看TEXT函数的基本定义与写法。.函数：TEXT，将数值转换为按指定数字格式表示的文本。语法：TEXT(value,format_text)Value 为数值、计算结果为数字值的公式，或对包含数字值的单元格的引用。Format_text 为“单元格格式”对话框中“数字”选项卡上“分类”框中的文本形式的数字格式。说明• Format_text不能包含星号(*)。• 通过“格式”菜单调用“单元格”命令，然后在“数字”选项卡上设置单元格的格式，只会更改单元格的格式而不会影响其中的数值。使用函数TEXT可以将数值转换为带格式的文本，而其结果将不再作为数字参与计算。从上边的信息，我们可以了解到，TEXT函数就是将数字或文本转换为指定的文本。什么意思呢？如果不进行转换，MID所提取出来的8位日期值就不会以日期的格式出现，电脑会将其显示为8位连续的数字（如：19790628，就不会显示为：1979-06-28）。这样不是我们想要的结果，所以，外套了一个TEXT文本函数。在这里，MID所提取出来的就是TEXT函数的第一个参数，即，value,(数值或是文本)，而“#-00-00”则是TEXT函数的第二个参数，是我们想要显示的格式，它表为显示为“年-月-日”格式，这样就得到我们想要的日期格式了。前边那个较长的公式，我们也来做个简单的解说：即：用IF判断身份证号所在单元格的位数（用LEN提取出来的）是否＝15位，如是，则加上19，否则就不加，即“”空值。当然，也可以用另一种笨办法：=MID(M3,7,4)&MID(M3,11,2)&MID(M3,13,2)=VLOOKUP(MID(M3,1,2),Sheet1!A:D,2,0)maintenancemeasures,thereisabigsecurityrisk,managementhashadagreatimpacttothecity.3.1-8busterminalstationstatusinYibincitylayouts(4)hoursofoperationmostofYibincitybuslinesin5:30-6:20,andbasicallymeettheYibintravelneeds.Bus
接下来我们共同来品一品VLOOKUP套MID的用法。MID在前面我们已经品过了，在此不再赘述，这里我们主要是来看一下VLOOKUP函数。函数VLOOKUP用途：在表格或数值数组的首列查找指定的数值，并由此返回表格或数组当前行中指定列处的数值。当比较值位于数据表首列时，可以使用函数VLOOKUP代替函数HLOOKUP。语法：VLOOKUP(lookup_value，table_array，col_index_num，range_lookup)参数：Lookup_value为需要在数据表第一列中查找的数值，它可以是数值、引用或文字串。Table_array为需要在其中查找数据的数据表，可以使用对区域或区域名称的引用。Col_index_num为table_array中待返回的匹配值的列序号。Col_index_num为1时，返回table_array第一列中的数值；col_index_num为2，返回table_array第二列中的数值，以此类推。Range_lookup为一逻辑值，指明函数VLOOKUP返回时是精确匹配还是近似匹配。如果为TRUE或省略，则返回近似匹配值，也就是说，如果找不到精确匹配值，则返回小于lookup_value的最大数值；如果range_value为FALSE，函数VLOOKUP将返回精确匹配值。如果找不到，则返回错误值#N/A。实例：如果A1=23、A2=45、A3=50、A4=65，则公式“=VLOOKUP(50，A1:A4，1，TRUE)”返回50。VLOOKUP是一个相当实用而且常用的函数，在论坛上有相当多的帖子，我这里就对精帖做一个借用吧，现有资源利用。这里VLOOKUP的任务是利用MID提取出来的身份证地址编码，去我们建立的身份证地址库中查找相对应的地址，并且引用到档案的籍贯栏中来。另注：此楼VLOOKUP附件为陈泽祥老师作品，因为编写得实在精彩，借来一用。谢谢了。附件maintenancemeasures,thereisabigsecurityrisk,managementhashadagreatimpacttothecity.3.1-8busterminalstationstatusinYibincitylayouts(4)hoursofoperationmostofYibincitybuslinesin5:30-6:20,andbasicallymeettheYibintravelneeds.Bus
VLOOKUP动态图解.rar(26.09KB)·档案附件.rar(158.43KB)=IF(LEN(M3)=15,M3,LEFT(REPLACE(M3,7,2,),15))这一楼我们来谈谈身份证的减位。为什么我们要对18位数身份证号码减位呢，是想展示自己对函数的运用吗？绝对不是。我们都知道早期的身份证号是由15位数字编写而成的。1、第l一6位数为行政区划代码；2、第7—12位数为出生日期代码；3、第13---15位数为分配顺序代码；(1)、行政区划代码，是指公民第一次申领居民身份证时的常住户口所在地的行政地区。(2)、出生日期代码，第7—8位数代表年份(年份前面二位数省略)，第9—10位数代表月份(月份为l位数的前面加零)。第11一12位数代表日期(日期为1位数的前面加零)。(3)、分配顺序代码，是按人口数统一合理分配以固定顺序给予每个人的顺序号.这是就是我们早期15位身份证的编码结构，自1999年10月1日起在全国建立和实行公民身份证号码制度.公民身份证号码按照GB11643—1999《公民身份证号码》国家标准编制，由18位数字组成：前6位为行政区划分代码，第7位至14位为出生日期码，第15位至17位为顺序码，第18位为校验码。第18位号码是校验码，目的在于检测身份证号码的正确性，是由计算机随机产生的，所以不再是男性为单数，女性为双数,而以第十七位决定性别.从上边的内容我们可以看出，18位数的身份证号是由15位升级而来的，升级的原则只是在原出生年份前增加了“19”，这样就不至于1910年和2010年相重复（因为这两年数取后边两位都是10年。）而第18maintenancemeasures,thereisabigsecurityrisk,managementhashadagreatimpacttothecity.3.1-8busterminalstationstatusinYibincitylayouts(4)hoursofoperationmostofYibincitybuslinesin5:30-6:20,andbasicallymeettheYibintravelneeds.Bus
位则是身份证号正确性的校验码。也就是说我们前边的编码输入是否正确，或是该号码本身是否正确，都可以通过校验码来检验出来。清楚了这个，我们就来看下这个函数公式吧：=IF(LEN(M3)=15,M3,LEFT(REPLACE(M3,7,2,),15))这里的IF函数因为前边已经讲过，不再赘述，这里我们主要来品一品LEN函数和REPLACE函数。LEN也应用于：LENB。LEN返回文本字符串中的字符数。LENB返回文本字符串中用于代表字符的字节数。此函数用于双字节字符。语法：LEN(text)：LENB(text)。Text 是要查找其长度的文本。空格将作为字符进行计数。该函数在这里是以IF函数的参数出现的，它的主要功能是将我们原有为15位数的身份证号取15位，也就是将其原记录保存下来。REPLACE也应用于：REPLACEB。REPLACE使用其他文本字符串并根据所指定的字符数替换某文本字符串中的部分文本。REPLACEB使用其他文本字符串并根据所指定的字符数替换某文本字符串中的部分文本。此函数专为双字节字符使用。语法：REPLACE(old_text,start_num,num_chars,new_text)REPLACEB(old_text,start_num,num_bytes,new_text)Old_text 是要替换其部分字符的文本。Start_num 是要用new_text替换的old_text中字符的位置。Num_chars 是希望REPLACE使用new_text替换old_text中字符的个数。Num_bytes 是希望REPLACE使用new_text替换old_text中字节的个数。New_text 是要用于替换old_text中字符的文本。从前边的解释中，我们可以看出，REPLACE函数在这里的主要作用是将18位数编号的第7-8位数去掉，然后交给LEFT函数，从16位中提取左边的15位，（即把验证码去掉）。这样就将原有的15位或是18位身份证号都统一成了15maintenancemeasures,thereisabigsecurityrisk,managementhashadagreatimpacttothecity.3.1-8busterminalstationstatusinYibincitylayouts(4)hoursofoperationmostofYibincitybuslinesin5:30-6:20,andbasicallymeettheYibintravelneeds.Bus
位，作为辅助列为我们后边的验证作准备。接下来，应该看看身份证的主角，即身份证编码的验证码了。在这里，我们先做个前凑，先让大家了解一下身份证编码是怎么回事，特别是它的校验码，有没有什么规则，只有先了解了这个，才能清楚身份证校验码公式的作用。一、身份证号码执行标准：18位身份证标准在国家质量技术监督局于1999年7月1日实施的GB11643-1999《公民身份号码》。 GB11643-1999《公民身份号码》为GB11643-1989《社会保障号码》的修订版，其中指出将原标准名称“社会保障号码”更名为“公民身份号码”，另外GB11643-1999《公民身份号码》从实施之日起代替GB11643-1989。 二、编码规则：公民身份号码是特征组合码，由十七位数字本体码和一位校验码组成。排列顺序从左至右依次为：六位数字地址码，八位数字出生日期码，三位数字顺序码和一位校验码，可以用字母表示如为ABCDEFYYYYMMDDXXXR。其含义如下： 1.地址码（ABCDEF）：表示编码对象常住户口所在县(市、旗、区)的行政区划代码，按GB/T2260的规定执行。 2.出生日期码（YYYYMMDD）：表示编码对象出生的年、月、日，按GB/T7408的规定执行，年、月、日分别用4位、2位（不足两位加0）、2（同上）位数字表示，之间不用分隔符。 3.顺序码（XXX）：表示在同一地址码所标识的区域范围内，对同年、同月、同日出生的人编定的顺序号，顺序码的奇数分配给男性，偶数分配给女性。 4．校验码（R），一位数字，通过前17位数字根据一定计算得出。三、关于地址码含义的详细解释： 身份证前六位是地区代码，我们用ABCDEF表示。代码的解释规则如下： A:国内区域 maintenancemeasures,thereisabigsecurityrisk,managementhashadagreatimpacttothecity.3.1-8busterminalstationstatusinYibincitylayouts(4)hoursofoperationmostofYibincitybuslinesin5:30-6:20,andbasicallymeettheYibintravelneeds.Bus
1华北三省二市 2东北三省 3华东六省一市 4华南六省 5西南四省一市 6西北五省 7台湾 8港澳 B（或者说是AB，就是前2位）:省（直辖市，自治区，特别行政区）代码 按照A划定的分区定义省代码，有直辖市的，直辖市列前，其余按离直辖市的距离排序，没有直辖市的，按离北京的远近排序。 具体省（直辖市，自治区，特别行政区）代码如下： 11-15京津冀晋蒙 21-23辽吉黑 31-37沪苏浙皖闽赣鲁 41-46豫鄂湘粤桂琼 50-54渝川贵云藏 61-65陕甘青宁新 81-82港澳 CD:城市代码 从01开始排，对于直辖市，CD=01表示市辖区，CD=02表示辖县；省的城市代码从省会开始排，比如2101=沈阳2102=大连……只有地级城市有独立的城市代码，县级市没有。 EF:市辖区、郊区、郊县、县级市代码 maintenancemeasures,thereisabigsecurityrisk,managementhashadagreatimpacttothecity.3.1-8busterminalstationstatusinYibincitylayouts(4)hoursofoperationmostofYibincitybuslinesin5:30-6:20,andbasicallymeettheYibintravelneeds.Bus
如果EF=00，指代这个城市，不特定区县；对于非直辖市，如EF=01，指代市辖区（任意一个区），02开始指代特定的区。 其中： E=0代表市辖区， E=1代表郊区， E=2代表郊县， E=8代表县级市。 对于直辖市，从01开始就依次排区，没有市区和郊区的代码区分。：有关更详细的资料见附录。四、生日期码（YYYYMMDD）表示编码对象出生的年、月、日，其中年份用四位数字表示，年、月、日之间不用分隔符。例如：1981年05月11日就用19810511表示。五、顺序码（XXX）（身份证第十五位到十七位）是县、区级政府所辖派出所的分配码，每个派出所分配码为10个连续号码，例如“000-009”或“060-069”，其中单数为男性分配码，双数为女性分配码，如遇同年同月同日有两人以上时顺延第二、第三、第四、第五个分配码。如：007的就是个男生，而且和他同年月日生的男生至少有两个，他们的后四位是001*和003*。分配顺序码中“999、998、997、996”四个顺序号分别为男女性百岁以上老人专用的特定编号。六、关于校验码校验码是通过一系列数学计算得出来的，具体校验的计算方式如下： 1.对前17位数字本体码加权求和 公式为：S=Sum(Ai*Wi),i=0,...,16 其中Ai表示第i位置上的身份证号码数字值，Wi表示第i位置上的加权因子，其各位对应的值依次为：7910584216379105842 maintenancemeasures,thereisabigsecurityrisk,managementhashadagreatimpacttothecity.3.1-8busterminalstationstatusinYibincitylayouts(4)hoursofoperationmostofYibincitybuslinesin5:30-6:20,andbasicallymeettheYibintravelneeds.Bus
通俗解释：身份证第一位数字X7+第二位X9+第三位X10+第四位X5+第五位X5+第六位X8+第七位X4+第八位X1+第九位X6+第十位X3+十一位X7+十二位X9+十三位X10+十四位X5+十五位X8+十六位X4+十七位X2；计算出总和（用S）表示。 2.以11对计算结果取模 Y=mod(S,11) 通俗解释：用S除以11，看最后的余数。如果除尽，为0；余数为1，则计为1；最大余数为10；全部数字为0-10共11个数字。（用Y表示）。 3.根据模的值得到对应的校验码 对应关系为： Y值： 012345678910 校验码：10X98765432 通俗解释：余数为0，则校验码为1；依次类推：余数为1，则校验码对应0；以下：2--X；3--9；4--8；5--7；6--6；7--5；8--4；9-3；10-2。 如果校验码不符合这个规则，则肯定是假号码。 关于18位身份证号码尾数是“X”的一种解释：因为按照上面的规则，校验码有11个，而不是10个，所以不能用0-9表示。所以如果尾号是10，那么就得用X来代替，因为如果用10做尾号，那么此人的身份证就变成了19位，而19位的号码违反了国家标准，并且我国的计算机应用系统也不承认19位的身份证号码。Ⅹ是罗马数字的10，用X来代替10，可以保证公民的身份证符合国家标准。 根据〖中华人民共和国国家标准GB11643-1999〗中有关公民身份号码的规定，公民身份号码是特征组合码，由十七位数字本体码和一位数字校验码组成。排列顺序从左至右依次为：六位数字地址码，八位数字出生日期码，三位数字顺序码和一位数字校验码。顺便传一个俺刚才做的身份证验证系统作为参考，帮助理解maintenancemeasures,thereisabigsecurityrisk,managementhashadagreatimpacttothecity.3.1-8busterminalstationstatusinYibincitylayouts(4)hoursofoperationmostofYibincitybuslinesin5:30-6:20,andbasicallymeettheYibintravelneeds.Bus
[本帖最后由宏业教育于2010-4-2412:47编辑]附件身份证系统.rar(2.76KB)上面的这个疑问我来简单说一下：通常用VLOOKUP()函数时，我们都是从左往右查，但偶尔需要反过来，根据右边某一列的值查找左边的怎么办呢，这就需要借助于数组过渡一下，如附件EXCEL表所示。对于“=VLOOKUP(G2,IF({0,1},A1:A5,B1:B5),2,0)”这个公式的用法，建议大家看一下附件的动画。“公式求值”是一个学习公式运算原理的好窗口，遇到不太好理解的公式，或者运算结果不正确的公式，不妨通过这个先检查一下。附件公式求值.gif(51.52KB)maintenancemeasures,thereisabigsecurityrisk,managementhashadagreatimpacttothecity.3.1-8busterminalstationstatusinYibincitylayouts(4)hoursofoperationmostofYibincitybuslinesin5:30-6:20,andbasicallymeettheYibintravelneeds.Bus
maintenancemeasures,thereisabigsecurityrisk,managementhashadagreatimpacttothecity.3.1-8busterminalstationstatusinYibincitylayouts(4)hoursofoperationmostofYibincitybuslinesin5:30-6:20,andbasicallymeettheYibintravelneeds.Bus
微信/支付宝扫码支付