Excel制表技巧(5)函数应用
　　让你从菜鸟成为Excel高手说起是否会使用Excel，估计只要用过电脑的人都说自己会用。真的是这样吗？就笔者的多年用户技术支援的经验，很多用户所谓的“会用”，真的只是仅仅局限于“会用”而己。输入一些内容，加上一些表格格线，点一下存盘，瞧，文件做好了。如果只是这样，微软公司就不用煞费苦心，开发什么新版本了。早期的Excel&5.0就己经绰绰有余了。其实，Excel是一个功能非常强大的数据处理系统，决不只是一个拿来画个表格的表格绘制工具。如果真正掌握了Excel的所有功能，你会发现，很多重复的，烦闷的数据处理过程，会变得非常美妙，你只需点点或拖拖鼠标，然后靠在椅背上啜口咖啡，本来会忙得你晕天黑地的任务己让系统帮你处理好了。这时，看着仍忙得一塌湖涂的同事，你只有一个感觉：酷……要真正发挥Excel的威力，你必须掌握Excel的灵魂―函数。不会使用Excel的函数，你就不是一个真正的会用Excel的用户。正是一个个功能各异的函数，组成的不同的公式，才让Excel这位大侠，有了绝世武功。每一个函数，便是他的一招绝学，但只单单学会他的招术，也还是不够，只有把所有招术练熟了，并综合运用，融会贯通，做到招中有招，才能真正掌握他的绝世武功，让Excel自动帮我们完成无法完成的任务。下面，我们便来开始学心Excel大侠的绝学。我会先一招一招地介绍，几招之后，再演示怎么综合应用所学的几招，创建新招。在实际对敌中，我们要知道，招是死的，人是活的，招由心生，绵绵不绝。第一招：大海捞针（Vlookup函数）招如其名。此招用来在一个茫茫的数据源中，自动让电脑找出你要的某个数据的相关资料并填在指定的地方。也是就是，可以让电脑在一个表格或指定的一个区域中查找某一指定的值，并由此返回该值相对应当前行中指定列处的数值。此招还有相应的变化，分别为lookup和Hlookup两式。当查找的数据是水平排列时，可以使用函数&HLOOKUP&代替函数&VLOOKUP。但用到的情况比较少，这里不做介绍。例如：你有一个工作表，上面近万项货品名称，每项货品的代码，价格，购买日期等。如你要做另一份报表，且其中部分货品在这份表中己有数据，则可以利用此招，只输入货品的名称或者代码，其余的让电脑自动查找并返回相应的价格，购买日期等。使用语法VLOOKUP(lookup_value,table_array,col_index_num,range_lookup)Lookup_value&为需要查找的值。Lookup_value&可以为数值、引用或文本字符串。Table_array&为需要在其中查找数据的数据表。可以使用对区域或区域名称的引用，例如数据库或列表。如果&range_lookup&为&TRUE，则&table_array&的第一列中的数值必须按升序排列：…、-2、-1、0、1、2、…、-Z、FALSE、TRUE；否则，函数&VLOOKUP&不能返回正确的数值。如果&range_lookup&为&FALSE，table_array&不必进行排序。通过在“数据”菜单中的“排序”中选择“升序”，可将数值按升序排列。Table_array&的第一列中的数值可以为文本、数字或逻辑值。文本不区分大小写。Col_index_num&为&table_array&中待返回的匹配值的列序号。Col_index_num&为&1&时，返回&table_array&第一列中的数值；col_index_num&为&2，返回&table_array&第二列中的数值，以此类推。如果&col_index_num&小于&1，函数&VLOOKUP&返回错误值值&#VALUE!；如果&col_index_num&大于&table_array&的列数，函数&VLOOKUP&返回错误值&#REF!。Range_lookup&为一逻辑值，指明函数&VLOOKUP&返回时是精确匹配还是近似匹配。如果为&TRUE&或省略，则返回近似匹配值，也就是说，如果找不到精确匹配值，则返回小于&lookup_value&的最大数值；如果&range_value&为&FALSE，函数&VLOOKUP&将返回精确匹配值。如果找不到，则返回错误值&#N/A。说明如果函数&VLOOKUP&找不到&lookup_value，且&range_lookup&为&TRUE，则使用小于等于&lookup_value&的最大值。如果&lookup_value&小于&table_array&第一列中的最小数值，函数&VLOOKUP&返回错误值&#N/A。如果函数&VLOOKUP&找不到&lookup_value&且&range_lookup&为&FALSE，函数&VLOOKUP&返回错误值&#N/A。应用示例：上图中，为方便比较，我将原始数据区域放在了同一工作表中（E1:F5）,实际使用时，原始数据可以在不同的工作表，甚至不同的工作簿（即不同的Excel文件）。当被查找的内容与原始内容在不同的工作表，table_array前面需加上工作表的名称，写法为&“表名!&”区域范围，如“Sheet2!$A$1:$B$12”，而若在不同的工作簿，则还得加上文件名，如“[文件名]sheet1!$A$1:$B$12”。详细解释公式“=Vlookup(A2,$E$2:$F$5,2,FALSE)”中A2表示要查找的值为A2单元格的内容，即“Apple”,&“$E$2:$F$5”告诉电脑，应该去$E$2:$F$5这个数据区域中查找，“2”表示找到后，应传回该区域第二列的值，即数量列，最后“FALSE”参数系统，查找区域内容未进行排序，需使用精确查找，找不到就算了，不返回近似匹配值。特别要注意的是，通常我们都是使用鼠标拖动的方法来填充公式，而拖动时，Excel对公式中区域的引用，处理方法是不一样的。如果是相对参照，即栏名列号前没有“$”符号，则Excel会对该区域作相对位移，如上栏是E2:B5,拖到下栏后，即会自动成为E3:B6，这种处理方法在很多公式中是必要的，但在这个公式中却是致命的，因为它更改了查找的原始数据的区域，导致实际上包含有的数据，因己不在查找的区域中而漏网。这也是很多用户在实际应用中犯的错误，引致查找结果不真实。要解决这个问题，我们可以利用Excel对区域引用的第二种方法：绝对参照。即在栏名列号前加上“$”，这样，系统就不会作相对的位移，无论怎样拖，区域范围都不变。（在很多情况下，我们会使用“名称”来代替直接的区域指定方式，使用更为方便。这一内容将在其他章节中介绍）相对参照与绝对参照的写法，可以让电脑作自动转换。方法是，先将当前单元格定位在要修改的单元格上，然后在资料编辑列，用鼠标涂黑（英文的说法叫Highlight）要转换的部分，再按“F4”即可。见下图：通过上图可以看出，能找到的，系统己自动填入了找到的值，如Apple&&&cherry，对于找不到的（Plum&&&Pear），则显示#N/A。第二招：左右逢源（If函数）此招用来对某一条件执行的真假值进行判断，根据逻辑计算的真假值，返回不同结果。如果结果为真，则返回一个真，如果为假，则返回另一值，可谓左右逢源。使用语法IF(logical_test,value_if_true,value_if_false)Logical_test&表示计算结果为&TRUE&或&FALSE&的任意值或表达式。例如，A1&=60&就是一个逻辑表达式，如果单元格&A1&中的值大于或等于&60，表达式即为&TRUE，否则为&FALSE。本参数可使用任何比较运算符。Value_if_true&logical_test&为&TRUE&时返回的值。例如，如果本参数为文本字符串“预算内”而且&logical_test&参数值为&TRUE，则&IF&函数将显示文本“预算内”。如果&logical_test&为&TRUE&而&value_if_true&为空，则本参数返回&0（零）。如果要显示&TRUE，则请为本参数使用逻辑值&TRUE。Value_if_true&也可以是其他公式。Value_if_false&logical_test&为&FALSE&时返回的值。例如，如果本参数为文本字符串“超出预算”而且&logical_test&参数值为&FALSE，则&IF&函数将显示文本“超出预算”。如果&logical_test&为&FALSE&且忽略了&Value_if_false（即&value_if_true&后没有逗号），则会返回逻辑值&FALSE。如果&logical_test&为&FALSE&且&Value_if_false&为空（即&value_if_true&后有逗号，并紧跟着右括号），则本参数返回&0（零）。Value_if_false&也可以是其他公式。说明函数&IF&最多可以嵌套七层，用&value_if_false&及&value_if_true&参数可以构造复杂的检测条件。在计算参数&value_if_true&和&value_if_false&后，函数&IF&返回相应语句执行后的返回值。应用示例：第三招：投石问路（IS函数）此招用来对某个单元格的当前值的类型进行判断，以便知道其类型后，再采取下一部行动，因此称为投石问路。IS函数共有九个工作表函数。概括为&IS&类函数，可以检验数值的类型并根据参数取值返回&TRUE&或&FALSE。例如，如果数值为对空白单元格的引用，函数&ISBLANK&返回逻辑值&TRUE，否则返回&FALSE。使用语法ISBLANK(value)ISERR(value)ISERROR(value)ISLOGICAL(value)ISNA(value)ISNONTEXT(value)ISNUMBER(value)ISREF(value)ISTEXT(value)Value&为需要进行检验的数值。分别为：空白（空白单元格）、错误值、逻辑值、文本、数字、引用值或对于以上任意参数的名称引用。应用示例：详细解释公式“=ISBLANK(A1)”，表示对A1单元格是否为空进行判断。如是是空的，则返回“True”值，如果不为空，则返回&“False)的值。上面示例图中，B1及B2单元格中的函数分别对A1及A2单元格进行是否为空白的判断。结果显示一个为真，一个为假。OK，我们己经学了三招，现在我们要将这三招组合起来，自创一招新招。第四招：瞒天过海（自创组合招数）我们来看学第一招时用的例子，对于找不到的项目，系统显示#N/A，但这样的报告交给上司，未免太难看了些。用什么方法，可以让其不显示出错误值呢？对了，先来一招投石问路，对系统返回的值做一个判断，看看系统到底找到没有。再来一招左右逢源，对于找到的就显示原值，找不到的，就干脆让它显示空白（当然，也可让设置其他的值如No等），岂不妙哉？因此，对于原单一公式：=VLOOKUP(A2,$E$2:$F$5,2,FALSE)，可以结合IF和IS函数来使用。大家刚才看到，对于投石问路，共有九种变化，其中第三式（ISERROR）或第五式（ISNA）均适合这种情况，可以使用。因此，组合后的公式就变成：=IF(ISNA(VLOOKUP(D2,$G$2:$H$5,2,FALSE)),"",VLOOKUP(D2,$G$2:$H$5,2,FALSE))或=IF(ISERROR(VLOOKUP(D3,$G$2:$H$5,2,FALSE)),"",VLOOKUP(D3,$G$2:$H$5,2,FALSE))下图显示了这种情况。红框中用的就是组合的公式，而其中的Plum&&&Pear没有再显示难看的#N/A，报表因此漂亮多了。因为是公式，只要写好第一个单元格的公式，其余的一拖就好了。好了，新创的这招叫什么呢？就叫瞒天过海吧！第五招：留头去尾（Left函数）此招用来对原始数据进行截取。截取的方式是从第一个字符开始，截取用户指定长度的内容。例如：在一个工作表中，某一列的资料是地址，录有省、市、街道等。如果你想插多一列，加入省份的资料，以便进行省份筛选，则可用该函数自动进行截取，而无需人工输入。使用语法LEFT(text,num_chars)Text&是包含要提取字符的文本字符串，可以直接输入含有目标文字的单元格名称。Num_chars&指定要由&LEFT&所提取的字符数。Num_chars&必须大于或等于&0。如果&num_chars&大于文本长度，则&LEFT&返回所有文本。如果省略&num_chars，则假定其为&1。应用示例：上图中，对含有不同地方的数据，利用“Left”函数，非常简单分离出了他们的省份。详细解释公式“=Left(A2,3)”中A2表示要截取的数据为A2单元格的内容“广东省东莞市东城区…”，“3”表示从第一位开始，共截取3个字符，因此系统返回“广东省”。第六招：去头留尾（Right函数）此招与上招刚好相反，截取的方式是从最后一个字符开始，从后往前截取用户指定长度的内容。使用语法RIGHT(text,num_chars)RIGHTB(text,num_bytes)Text&是包含要提取字符的文本字符串，可以直接输入含有目标文字的单元格名称。Num_chars&指定希望&RIGHT&提取的字符数。注意：Num_chars&必须大于或等于&0。如果&num_chars&大于文本长度，则&RIGHT&返回所有文本。如果忽略&num_chars，则假定其为&1。应用示例：详细解释公式“=Right(A2,8)”中A2表示要截取的数据为A2单元格的内容“广东省东莞市…&电话：”，“8”表示从最后一位开始，共截取8个字符，因此系统返回“”。尽管原始数据长短不齐，但我们只关心最后的8位电话号码。第七招：掐头去尾（MID函数）与上面的两招不同，此招既不从第一位开始截取，也不从最后一位开始截取，而是由用户自行指定开始的位置和字符的长度。因此，若用户指定从第一位开始，便和Left函数一样。使用语法MID(text,start_num,num_chars)Text&是包含要提取字符的文本字符串，可以直接输入含有目标文字的单元格名称。Start_num&是文本中要提取的第一个字符的位置。文本中第一个字符的&start_num&为&1，以此类推。Num_chars&指定希望&MID&从文本中返回字符的个数。注意：如果&start_num&大于文本长度，则&MID&返回空文本&("")。如果&start_num&小于文本长度，但&start_num&加上&num_chars&超过了文本的长度，则&MID&只返回至多直到文本末尾的字符。如果&start_num&小于&1，则&MID&返回错误值&#VALUE!。如果&num_chars&是负数，则&MID&返回错误值&#VALUE!。如果&num_bytes&是负数，则&MIDB&返回错误值&#VALUE!。应用示例：详细解释公式“=MID(A2,7,8)”中A2表示要截取的数据为A2单元格的内容“**********”，“7”表示从第7位开始，共截取8个字符，因此系统返回用户想截取的生日时间“”。OK，我们己经学了三招，但读者可能己经发现，实际工作中，原始资料并不会如此整齐地出现，让我们很容易的用上面的三招去截取。比如说，第三招示例中，我用的都是18位的身份证号码，但实际上，很多人仍在使用15位的身份号码，这样一来，因原始数据长度不一致，导致在截取时，便会截错。再如我们的第一个例子，我们截的是3位，但实际中，有的省份名称本身就有3位，因此对这种情况，简单的套用就无法取得正确的内容。如我在第一篇中所说，在实际的工作使用中，单一公式常常都是不够的，而需要使用组合招数。例如刚才的身份证号码不同长度问题，我们可以在招式中，加入对位数的判断，如果长度是18位，则取8位，如果是15位的，则取6位。还记得前面我们学过左右逢源吗？这一招可是相当的实用哦，我们经常会用到。另外，下面我再介绍两招，用来对单元格的内容进行判断。一个是“瞎子摸象”(Find函数)，让用户对单元格内容中指定的字符进行定位，以确认其位置。当位置被确认后，截取就是轻而易举的一件事情了。另一招是“鲁班神尺”&(Len函数)，让用户对单元格内容的长度进行测量，得出其长度后，再做相应的截取处理。第八招：瞎子摸象(Find函数)此招用来对原始数据中某个字符串进行定位，以确定其位置。因为该招进行定位时，总是从指定位置开始，返回找到的第一个匹配字符串的位置，而不管其后是否还有相匹配的字符串，有点像瞎子摸象，摸到哪就说哪，因此取名“瞎子摸象”。使用语法FIND(find_text,within_text,start_num)Find_text&是要查找的文本。Within_text&是包含要查找文本的文本。Start_num&指定开始进行查找的字符。within_text&中的首字符是编号为&1&的字符。如果忽略&start_num，则假设其为&1。注意：使用&start_num&可跳过指定数目的字符。例如，假定使用文本字符串“AYF0093.YoungMensApparel”，如果要查找文本字符串中说明部分的第一个“Y”的编号，则可将&start_num&设置为&8，这样就不会查找文本的序列号部分。FIND&将从第&8&个字符开始查找，而在下一个字符处即可找到&find_text，于是返回编号&9。FIND&总是从&within_text&的起始处返回字符编号，如果&start_num&大于&1，也会对跳过的字符进行计数。如果&find_text&是空文本&("")，则&FIND&则会返回数值1。Find_text&中不能包含通配符。如果&within_text&中没有&find_text，则&FIND返回错误值&#VALUE!。如果&start_num&不大于&0，则&FIND返回错误值&#VALUE!。如果&start_num&大于&within_text&的长度，则&FIND&返回错误值&#VALUE!。应用示例：上图中，对含有不同地方的数据，利用“Find”函数，非常简单地确定了“省”出现的位置。详细解释公式“=FIND("省",A2)”中，“省”表示要查找的文本为“省”，（实际使用中，也可以很长的一串字符）。要找查找的对象是A2单元格的内容“广东省东莞市东城区…”，因为没有指定起始位置，所以系统从第一位开始。返回的“3”，表示“省“字在第三位。而“黑龙江省哈尔滨市…”则返回4。与Find类似，Search函数也有相同的功能。它们的区别是，Find区分大小写，而Search不分大小写（当被查找的文本为英文时）。另外，在Excel中，对文本进行处理的很多函数都提供了一个特别用来处理双字节字符（如中文，日文）的函数，一般是在原函数后加“B”，如FIND,&就有一个FINDB。之前讲过的LEFT,相对应的就是LEFTB等。其实，我们在实际应用中，使用不带“B”的函数就足够了。如果你想使用带“B”的函数，则要特别注意，尤其是在组合运用函数时，其中一个函数使用带“B”的形式，则其它有带“B”形式的函数，全部都要使用其带“B”的形式，否则结果极可能是错的。第九招：鲁班神尺&(Len函数)此招用来对单元格内容的长度进行测量，得出其长度后，再做相应的处理。使用语法LEN(text)Text&是要查找其长度的文本。空格将作为字符进行计数。应用示例：详细解释公式“=Len(A2)”中A2表示要查找长度的数据为A2单元格的内容“**********”，系统测量后，返回长度“18”。好了，我们己经学完了“瞎子摸象”和“鲁班神尺”，再加上上次所学的三招（留头去尾－Left函数，去头留尾－Right函数和掐头去尾－Mid函数），对一些文本的处理，我们就能将其玩弄与股掌之上。下面我们举一个例子，做一次实际操作。假如你有一堆公司员工的身份证号码登记表，但却没有单独的员工出生年月日这一栏，而偏偏员工的生日资料你很需要。一个一个的将其抄出来？？这未免太花时间吧！别急，利用上面的几招，不出一分钟你就能够将其“生产”出来。第一步的思路，就是利用“掐头去尾”函数，把身份证号码的前后内容去掉，留下中间的出生日期。但因为身份证号有两种长度（15位及18位），直接套用，一定会截错。于是，我们得加上“鲁班神尺”，先量出其长度，再加上“左右逢源”这招，对不同的长度号码，我们做不同的截取，问题就解决了。上图中，两种颜色的数据长度是不一致的，但利用公式，我们很简单的把生日数据截出来了。公式“=IF(LEN(A2)=15,"19"&MID(A2,7,6),MID(A2,7,8))”中，我们利用IF函数，用Len函数对A2的长度进行判断，如果等于15，则返回“"19"&MID(A2,7,6)”，表示如果为15位的身份证号码，就在其之前补上“19”（注：“＆”符号在Excel中，用来把两个数据合并在一起），然后对A2单元格中的数据从第7位开始，截6位出来，合在一起刚好8位。如果不是15位，则返回“MID(A2,7,8))”，表示直接在A2单元格的数据中，从第7位开始，截取8位出来。做完第一个公式后，不管下面还有几千或几万个数据，一拖到底即可。对于要求比较简单的用户，得到这个结果己经够用了。但实际上，这个取出来的数据，并不是日期格式的。因此，就无法像对待日期那样处理它，如更改日期格式，或设置条件格式化，让当天为生日的数据显示为红色等。要让其变为日期，其实也是很简单。只是其中一招我们还未介绍－Datevalue，同时，取出的数据，也需额外加上分隔符，让系统识别。我先把公式列在这里，有兴趣的用户可以试试。=DATEVALUE(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)))记住：公式得出的结果，是一个时间序列号，日期格式你们可自行设置。下图中，C3及C5单元格的“3”就是因没设置日期格式而直接显示序列值。下面我们再来看一个使用了“瞎子摸象”函数的例子。假如有这样一串数据，格式类似Bill&Gates&(****)&Bill.或Charles&Peng&(****)&Charles.，我们需要取出其中的邮件地址部分。因为其邮件地址时长时短，因此，无法直接截取，单用Len函数也无法实现。但根据观查发现，邮件地址起始于“）”后，因此我们可以利用“瞎子摸象”Find函数，先定出每个数据中的“）”位置，再用Len量出整个数据长度，相减之后，就是邮件地址的长度，这样，用“去头留尾”函数就可将需要的数据取出。公式为：=RIGHT(A2,LEN(A2)-FIND(")",A2))。利用身份证号码提取个人信息一、分析身份证号码其实，身份证号码与一个人的性别、出生年月、籍贯等信息是紧密相连的，无论是15位还是18位的身份证号码，其中都保存了相关的个人信息。15位身份证号码：第7、8位为出生年份(两位数)，第9、10位为出生月份，第11、12位代表出生日期，第15位代表性别，奇数为男，偶数为女。18位身份证号码：第7、8、9、10位为出生年份(四位数)，第11、第12位为出生月份，第13、14位代表出生日期，第17位代表性别，奇数为男，偶数为女。例如，某员工的身份证号码(15位)是024，那么表示日出生，性别为女。如果能想办法从这些身份证号码中将上述个人信息提取出来，不仅快速简便，而且不容易出错，核对时也只需要对身份证号码进行检查，肯定可以大大提高工作效率。二、提取个人信息这里，我们需要使用IF、LEN、MOD、MID、DATE等函数从身份证号码中提取个人信息。如图1所示，其中员工的身份证号码信息已输入完毕(C列)，出生年月信息填写在D列，性别信息填写在B列。1.&提取出生年月信息由于上交报表时只需要填写出生年月，不需要填写出生日期，因此这里我们只需要关心身份证号码的相应部位即可，即显示为“7208”这样的信息。在D2单元格中输入公式“=IF(LEN(C2)=15,MID(C2,7,4),MID(C2,9,4))”，其中：LEN(C2)=15：检查C2单元格中字符串的字符数目，本例的含义是检查身份证号码的长度是否是15位。MID(C2,7,4)：从C2单元格中字符串的第7位开始提取四位数字，本例中表示提取15位身份证号码的第7、8、9、10位数字。MID(C2,9,4)：从C2单元格中字符串的第9位开始提取四位数字，本例中表示提取18位身份证号码的第9、10、11、12位数字。IF(LEN(C2)=15,MID(C2,7,4),MID(C2,9,4))：IF是一个逻辑判断函数，表示如果C2单元格是15位，则提取第7位开始的四位数字，如果不是15位则提取自第9位开始的四位数字。如果需要显示为“70年12月”这样的格式，请使用DATE格式，并在“单元格格式→日期”中进行设置。2.&提取性别信息由于报表中各位员工的序号编排是按照上级核定的编制进行的，因此不可能按照男、女固定的顺序进行编排，如果一个一个手工输入的话，既麻烦又容易出错例如性别信息统一在B列填写，可以在B2单元格中输入公式“=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位，那么提取第15位的数字。MID(C2,17,1)：如果身份证号码的长度不是15位，即18位身份证号码，那么应该提取第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单元格显示为“男”，否则显示为“女”。回车确认后，即可在B2单元格显示正确的性别信息，接下来就是选中填充柄直接拖曳。如图2所示，现在这份报表无论是提取信息或是核对，都方便多了！图1&输入身份证号码　　图2&提取个人信息在学生的学籍管理中，经常要录入学生的身份证号、出生年月日、年龄等信息。如果能利用Excel的函数功能，便可以使录入的学生身份证号自动生成出生年月日和年龄，既方便，又可减少录入出生年月日、年龄的差错与繁琐。性别的判断选中D2单元格，输入公式：=IF(MOD(IF(LEN(C2)=15,MID(C2,15,1),MID(C2,17,1)),2)=0,"女","男")或=IF(MOD(IF(LEN(C7)=15,MID(C7,15,1),MID(C7,17,1)),2)=1,"男","女")，输入完成后，按下“Enter”键进行确认，第1位持证人的性别则自动显示在D2单元格中（如图1）。上述第一个公式的含义分三层来理解：①如果身份证号码是15位（LEN(C2)=15），则取其15位数字（MID(C2,15,1)），否则取其17位数字（MID(C2,17,1)）。②然后求取出来的数值除2后的余数（MOD(IF(LEN(C2)=15,MID(C2,15,1),MID(C2,17,1)),2)）。③如果（IF）余数为“0”（MOD(IF(LEN(C2)=15,MID(C2,15,1),MID(C2,17,1)),2)=0），则显示“女”，否则显示“男”。再次选中D2单元格，用“填充柄”将上述公式复制到D列下面的单元格中，即可显示出其他持证人的性别。注意：如果将上述公式修改为：=IF(IF(LEN(C2)=15,MID(C2,15,1),IF(LEN(C2)=18,MID(C2,17,1),""))="","号码有错",IF(MOD(IF(LEN(C2)=15,MID(C2,15,1),IF(LEN(C2)=18,MID(C2,17,1),"")),2)=1,"男","女"))，当输入的号码不是15位或18位，系统提示“号码有错”（参见图1），则效果更佳。注意：修改后的公式与上面的公式含义相似。正确录入身份证号由于身份证号有18位，而默认的数字录入只可正确显示前15位，而后3位显示为0，因此可用下述方法之一操作。1.&在英文标点的状态下，先输入引号“，”再输入身份证号或学号（如图1）。图12.&首先选中需录入身份证号的单元格，在“格式\单元格”的命令菜单中选“数字”选项，在分类项里单击“文本”，再录入身份证号。这样，既可解决18位以上身份证的显示问题，又可解决以0打头的学号显示问题。生成出生年月日利用DATE函数，返回代表特定日期的系列数。语法：DATE(Year,Month,Day)。参数Year——可以为一到四位数；&Month——代表每年中月份的数字；Day——代表在该月份中第几天的数字。利用MID函数，返回文本串中从指定位置开始的特定数目的字符，该数目由用户指定。语法：MID(Text,Start_Num,Num_Chars)。参数Text——是包含要提取字符的文本串，文本中第一个字符的Start_Num为&1，以此类推；Start_Num——是文本中要提取的第一个字符的位置；Num_Chars——指定希望MID从文本中返回字符的个数，如果Num_Chars是负数，则MID返回错误值“#VALUE!”。如图2所示，G列为身份证号，F列为出生年月日，在F2单元格中输入公式“=DATE(MID(J2,7,4),MID(J2,11,2),MID(J2,13,2))”，即可根据身份证号生成出生年月日。只要拖动填充柄就可以完成其他学生的数据。图2生成当前所需年龄利用DATEIF函数，计算两个日期之间的天数、月数或年数。语法：DATEDIF(Start_Date,End_Date,Unit)。Start_Date——为一个日期，它代表时间段内的第一个日期或起始日期；End_Date——为一个日期，它代表时间段内的最后一个日期或结束日期；Unit——为所需信息的返回类型。利用TODAY函数，返回当前日期的系列数。语法：TODAY(&)。如图2所示，G列为身份证号，E列为年龄，在E2单元格中输入公式“=DATEDIF(G2,TODAY(),"Y")”，这样就可根据当前计算机中的日期，自动计算出学生的实际年龄，而且每次打开文件时计算机都自动更新其数据。如果需要计算某日的实际年龄，只要修改系统的时钟后，再打开文件也可自动完成。Excel中人民币小写转换大写技巧要将人民币小写金额转换成大写格式，用Excel提供的格式，将自定义格式类型中的“[dbnum2]G/通用格式”改为“[dbnum2]&G/通用格式“元””来实现。但在转换小数时却出现了问题，比如￥6,050.09只能转换为“陆仟零伍拾.零玖圆”。那么用Excel能不能解决这一先天不足呢？其方法有以下两种（以下均对B1转换，假设B1是通过函数ROUND(&)四舍五入取得二位小数的小写金额数，其值为￥6,050.09）。一、在三个连续的同行单元格中转换1.&B2中输入公式&“=IF(B1〈0，"金额为负无效"，INT(B1))”，计算结果为￥6,050.00，然后点击“格式→单元格→数字→特殊→中文大写数字→确定”，B2显示“陆仟零伍拾”，再点击“格式→单元格→数字→自定义”，将“类型”编辑框中的“[dbnum2]G/通用格式”修改为：[dbnum2](“人民币”)G/通用格式“元”，此时B2显示：“(人民币)陆仟零伍拾元”。2.&C2中输入“=IF(B1〈0，""，INT(B1&10)-INT(B1)&10)”，同样C2改为：[DBNum2]G/通用格式“角”，C2显示：“零角”。3.&D2中输入“=IF(B1〈0，""，INT(B1&100)-INT(B1&10)&10)”，同样D2改为：[DBNum2]G/通用格式“分”，&D2显示：“玖分”。这样在连续的三个同行单元格中实现了大写金额格式的转换，如图1所示。为了使转换符合财务格式，B2应右对齐，C2列宽无间隔，且不设左右边框线，D2要左对齐。　　二、用Excel提供的函数转换个位为0，则不显示；不为0，显示“XXX元”；不为0、且角位和分位均为0，显示“XXX元整”；元角分位均为0，则显示“零元整”。小数点后面第一位为0、且元位或分位有一为0，则不显示；为0、且元位和分位均不为0，则显示“零”；不为0，显示“X角”。小数点后面第二位为0，则显示“整”；不为0，显示“X分”。根据以上思路在B2中输入公式：“IF(B1〈0，"金额为负无效"，IF(OR(B1=0，B1="")，"(人民币)零元整"))；IF(B1〈1，"(人民币)"，TEXT(INT(B1)，"[dbnum2](人民币)G/通用格式")&&"元")&&IF(INT(B1&10)-INT(B1)&10=0)；IF(INT(B1)&(INT(B1&100)-INT(B1&10)&10)=0，""，"零")，TEXT(INT(B1&10)-INT(B1)&10，"[dbnum2]")&&"角")&&IF((INT(B1&100)-INT(B1&10)&10)=0，"整"，TEXT((INT(B1&100)-INT(B1*10)*10)，"[dbnum2]")&&"分")))”。确认后B2显示：“(人民币)陆仟零伍拾元零玖分”。以后使用时将公式复制到有关单元格中，若转换的对象B1发生了变化，可点击“编辑→替换”将B1替换掉。两种转换测试结果如图2所示。Excel中如何让表头自动填写当前月份和日期假设我们要做一个标题为“某公司6月份电费统计表”的Excel表格，其中“6”为进行电费统计的当月月份，第二行有制表日期。电费每月公布一次，那么每月打开此表修改编辑时，都要对标题中的月份和制表日期进行修改，万一只改动了表内数据，而忘记以上两个时间的改动，会出现表头时间与表内数据的矛盾。我们采取一个一劳永逸的措施来解决这个问题，任何时候打开此工作表，都让表头自动填写当前月份和日期。这要借助Excel三个函数来实现，在标题单元格输入“="实验中学"&&&MONTH(TODAY())&&"月份电费统计表"”，函数“MONTH(TODAY())”表示当前月份，在第二行制表时间单元格输入“=TODAY()”。函数“TODAY()”表示当前日期，就出现图中结果。完成上述设置，任何时候打开该表，我们大可放心地去做表内数据的处理工作了，不必担心表头时间会出现什么差错，无需对表头做任何改动。用Excel解决经典“鸡兔问题”“鸡兔问题”是一道古典数学问题，源自我国古代四、五世纪的数学著作《孙子算经》。算经卷下第三十一题为：“今有雉、兔同笼，上有三十五头，下有九十四足。问雉、兔各几何?”原著的解法为：“上署头，下置足。半其足，以头除足，以足除头，即得。”具体解法即：分别列出总头数(35)和总足数(94)，总足数除以二，再减去总头数(94÷2-35)，得到兔数为12，总头数减去兔数35-12得到鸡数为23。鸡兔问题本身并不难，使用2元1次方程组的消元算法，可以很快得到答案。我们可以尝试着利用Excel提供的各种计算工具来进行计算，不仅别有趣味，而且还会加深对Excel功能的综合掌握，对于讲授Excel的教师而言，则是典型的一题多解的素材。利用IF函数试探求解如图1，创建一个二维表，假设鸡数B2为要求解的单元格，将鸡兔的总头数和脚数分别写入D2和D3单元格，利用已知条件在其他单元格中写入公式：因兔头数=总头数-鸡头数，故在C2单元格中写入=D2-B2;鸡脚数=鸡头数*2，故B3单元格写入=B2*2;兔脚数=兔头数*4，故C3单元格写入=C2*4。接下来我们在任意其他单元格输入一个判断公式(本例中使用F1单元格)，公式内容为=IF(D3=B3+C3,"正解!",IF(D3&B3+C3,"高了","低了"))。公式的本质是判断鸡脚数+兔脚数与总脚数之间的关系，如果判断表达式D3=B3+C3结果为True，就意味着我们已经得到了正确答案。最后在B2中输入35以内的任意整数进行试探求解。如果输入的数值高于正解，判断单元格F1会提示“高了”，若数值小于正解则提示“低了”，用户根据提示再继续输入其他一个数字，直到输入了正确答案23，F1单元格会显示“正解!”。这种方法比较直观，但是非常笨拙，需要人工干预。即使用户聪明地使用二分法试探，也需要多次输入才能解决问题，对于更庞大的问题，这种解法几乎是不可行的。用Excel函数自动排名次如第一行为表头，A列(例如A2:A101，下同)为姓名，B列数据，在C2单元格输入公式“=IF(A2=0,0,INT(CONCATENATE(INT(B2),200-ROW(A1))))”。公式中ROW(A1)为A1单元格所在的行数即为1，(该公式下拉时依次为2、3、4.....),用200来减是为了CONCATENATE函数中的第2个参数保持3位数，CONCATENATE函数是一个拼合函数这里把B列的数据和它所在的行数拼合成一个数据。这样在对它进行排序后该数据包含了它所在行数的信息。CONCATENATE函数INT函数套用是为了把原来的文本变为数字。在D2单元格输入公式“=LARGE(C:C,ROW(A1))”即对B列数值(包含所在行的信息)按大小排列。在F2单元格(为了与原始清单分开中间空了一列)输入公式“=IF(D2=0,0,200-RIGHT(D2,3))”，函数&RIGHT(D2,3)即为D2单元格数据的后3位数，用200来减即为此数据所在的行数。在G2单元格输入公式“=IF($F2=0,0,INDEX($A$2:$B$15,$F2,COLUMN(A1)))”，并拖到H2单元格。INDEX函数为引用函数，即根据F2单元格所标明的行数在$A$2:$B$15单元格矩阵中引用姓名及得分。在I2单元格输入公式“=IF(H2=0,0,IF(H2=H1,I1,ROW(A1)))”，本来G、H列就是按得分大小排列的，但可能有平列名次，所以选用上述公式。最后把C2到I2单元格的公式下拉，程序就完成了。用Excel函数快速录入26个英文字母大家知道，用“填充柄”可以快速向表格中输入一些内置序列(如序数、日期等)。但是，有一个问题我始终没有搞明白，Excel软件是外国人开发的，居然不能自动按顺序填充26个英文字母！有没有办法快速输入这个序列呢？有，我们用函数转换来实现26个英文字母的自动填充:如果从A2开始向下输入“A、B、C……”，先在A2单元格中输入公式=CHAR(65+ROW()-2)，然后用“填充柄”向下拖拉即可；如果从B2开始向右输入“A、B、C……”，先在B2单元格中输入公式=CHAR(65+COLUMN()-2)，然后用“填充柄”向右拖拉即可。注意:①如果需要输入小写字母序列，只要将上述两个公式分别修改一下就好了:=CHAR(97+ROW()-2)、=CHAR(97+COLUMN()-2)②也可以将字母做成内置序列，同样可以快速输入。
来自：&&&《》
更多精彩，关注微信号：360doc
馆友评论（0）
您好，请&&或者&&后再进行评论
合作登录：
(window.slotbydup = window.slotbydup || []).push({
container: s,
id: 'u1442766',
scale: '20.3',
display: 'inlay-fix'