Excel 详细函数宝典

《Excel 详细函数宝典》由会员分享，可在线阅读，更多相关《Excel 详细函数宝典（43页珍藏版）》请在装配图网上搜索。

1、讲得还算详细，不错的，同学们认真研究！Excel函数简介一、什么是函数什么是参数？参数可以是数字、文本、形如 TRUE 或 FALSE 的逻辑值、数组、形如 #N/A 的错误值或单元格引用。给定的参数必须能产生有效的值。参数也可以是常量、公式或其它函数。参数不仅仅是常量、公式或函数，还可以是数组、单元格引用等：1.数组-用于建立可产生多个结果或可对存放在行和列中的一组参数进行运算的单个公式。在 Microsoft Excel有两类数组：区域数组和常量数组。区域数组是一个矩形的单元格区域，该区域中的单元格共用一个公式；常量数组将一组给定的常量用作某个公式中的参数。2.单元格引用-用于表示单元格在

2、工作表所处位置的坐标值。例如，显示在第 B 列和第 3 行交叉处的单元格，其引用形式为B3。3.常量-常量是直接键入到单元格或公式中的数字或文本值，或由名称所代表的数字或文本值。例如，日期 10/9/96、数字 210 和文本Quarterly Earnings都是常量。公式或由公式得出的数值都不是常量。函数是否可以是多重的呢？也就是说一个函数是否可以是另一个函数的参数呢？当然可以，这就是嵌套函数的含义。所谓嵌套函数，就是指在某些情况下，您可能需要将某函数作为另一函数的参数使用。如图所示的公式使用了嵌套的 AVERAGE 函数，并将结果与 50 相比较。这个公式的含义是：如果单元格F2到F5的

3、平均值大于50，则求G2到G5的和，否则显示数值0。 函数的结构以函数名称开始，后面是左圆括号、以逗号分隔的参数和右圆括号。如果函数以公式的形式出现，请在函数名称前面键入等号（=） 公式选项板-帮助创建或编辑公式的工具，还可提供有关函数及其参数的信息。单击编辑栏中的编辑公式按钮，或是单击常用工具栏中的粘贴函数 按钮之后，就会在编辑栏下面出现公式选项板。整个过程如图3所示。 什么是公式？函数与公式既有区别又互相联系。如果说前者是Excel预先定义好的特殊公式，后者就是由用户自行设计对工作表进行计算和处理的计算式。以公式“=SUM(E1:H1)*A1+26”为例，它要以等号“=”开始，其内部可以包

4、括函数、引用、运算符和常量。上式中的“SUM(E1:H1)”是函数，“A1”则是对单元格A1的引用(使用其中存储的数据)，“26”则是常量，“*”和“+”则是算术运算符(另外还有比较运算符、文本运算符和引用运算符) 二、使用函数的步骤1.单击需要输入函数的单元格2.点击fx3.从弹出的菜单中选择所需要的函数三、函数的种类1.数据库函数 - 当需要分析数据清单中的数值是否符合特定条件时，可以使用数据库工作表函数。例如，在一个包含销售信息的数据清单中，可以计算出所有销售数值大于 1,000 且小于 2,500 的行或记录的总数。Microsoft Excel 共有 12 个工作表函数用于对存储在数

5、据清单或数据库中的数据进行分析，这些函数的统一名称为 Dfunctions，也称为 D 函数，每个函数均有三个相同的参数：database、field 和 criteria，这些参数指向数据库函数所使用的工作表区域。其中参数 database 为工作表上包含数据清单的区域；参数 field 为需要汇总的列的标志；参数 criteria 为工作表上包含指定条件的区域。2.日期与时间函数 - 通过日期与时间函数，可以在公式中分析和处理日期值和时间值。3.工程函数 - 工程工作表函数用于工程分析。这类函数中的大多数可分为三种类型：对复数进行处理的函数、在不同的数字系统（如十进制系统、十六进制系统、八

6、进制系统和二进制系统）间进行数值转换的函数、在不同的度量系统中进行数值转换的函数。4.财务函数-财务函数可以进行一般的财务计算，如确定贷款的支付额、投资的未来值或净现值，以及债券或息票的价值5.信息函数 - 可以使用信息工作表函数确定存储在单元格中的数据的类型。信息函数包含一组称为 IS 的工作表函数，在单元格满足条件时返回 TRUE。例如，如果单元格包含一个偶数值，ISEVEN 工作表函数返回 TRUE。如果需要确定某个单元格区域中是否存在空白单元格，可以使用 COUNTBLANK 工作表函数对单元格区域中的空白单元格进行计数，或者使用 ISBLANK 工作表函数确定区域中的某个单元格是否为

7、空。6.逻辑函数 - 使用逻辑函数可以进行真假值判断，或者进行复合检验。例如，可以使用 IF 函数确定条件为真还是假，并由此返回不同的数值。7.查询和引用函数-当需要在数据清单或表格中查找特定数值，或者需要查找某一单元格的引用时，可以使用查询和引用工作表函数。例如，如果需要在表格中查找与第一列中的值相匹配的数值，可以使用 VLOOKUP 工作表函数。如果需要确定数据清单中数值的位置，可以使用 MATCH 工作表函数。8.数学和三角函数-通过数学和三角函数，可以处理简单的计算，例如对数字取整、计算单元格区域中的数值总和或复杂计算。9.统计函数-统计工作表函数用于对数据区域进行统计分析。例如，统计

8、工作表函数可以提供由一组给定值绘制出的直线的相关信息，如直线的斜率和 y 轴截距，或构成直线的实际点数值。10.文本函数-通过文本函数，可以在公式中处理文字串。例如，可以改变大小写或确定文字串的长度。可以将日期插入文字串或连接在文字串上。下面的公式为一个示例，借以说明如何使用函数 TODAY 和函数 TEXT 来创建一条信息，该信息包含着当前日期并将日期以dd-mm-yy的格式表示。11.用户自定义函数-如果要在公式或计算中使用特别复杂的计算，而工作表函数又无法满足需要，则需要创建用户自定义函数。这些函数，称为用户自定义函数，可以通过使用 Visual Basic for Applicatio

9、ns 来创建。函数名 功能 ABS 求出参数的绝对值。 AND “与”运算，返回逻辑值，仅当所有参数的结果均为逻辑“真（TRUE）”时返回逻辑“真（TRUE）”，反之返回逻辑“假（FALSE）”。 AVERAGE 求出所有参数的算术平均值。 COLUMN 显示所引用单元格的列标号值。 CONCATENATE 将多个字符文本或单元格中的数据连接在一起，显示在一个单元格中。 COUNTIF 统计某个单元格区域中符合指定条件的单元格数目。 DATE 给出指定数值的日期。 DATEDIF 计算返回两个日期参数的差值。 DAY 计算参数中指定日期或引用单元格中的日期天数。 DCOUNT 返回数据库或列表

10、的列中满足指定条件并且包含数字的单元格数目。 FREQUENCY 以一列垂直数组返回某个区域中数据的频率分布。 IF 根据对指定条件的逻辑判断的真假结果，返回相对应条件触发的计算结果。 INDEX 返回列表或数组中的元素值，此元素由行序号和列序号的索引值进行确定。 INT 将数值向下取整为最接近的整数。 ISERROR 用于测试函数式返回的数值是否有错。如果有错，该函数返回TRUE，反之返回FALSE。 LEFT 从一个文本字符串的第一个字符开始，截取指定数目的字符。 LEN 统计文本字符串中字符数目。 MATCH 返回在指定方式下与指定数值匹配的数组中元素的相应位置。 MAX 求出一组数中的

11、最大值。 MID 从一个文本字符串的指定位置开始，截取指定数目的字符。 MIN 求出一组数中的最小值。 MOD 求出两数相除的余数。 MONTH 求出指定日期或引用单元格中的日期的月份。 NOW 给出当前系统日期和时间。 OR 仅当所有参数值均为逻辑“假（FALSE）”时返回结果逻辑“假（FALSE）”，否则都返回逻辑“真（TRUE）”。 RANK 返回某一数值在一列数值中的相对于其他数值的排位。 RIGHT 从一个文本字符串的最后一个字符开始，截取指定数目的字符。 SUBTOTAL 返回列表或数据库中的分类汇总。 SUM 求出一组数值的和。 SUMIF 计算符合指定条件的单元格区域内的数值和

12、。 TEXT 根据指定的数值格式将相应的数字转换为文本形式 TODAY 给出系统日期 VALUE 将一个代表数值的文本型字符串转换为数值型。 VLOOKUP 在数据表的首列查找指定的数值，并由此返回数据表当前行中指定列处的数值 WEEKDAY 给出指定日期的对应的星期数。 Excel逻辑函数一、AND、OR、NOT函数（一）AND函数所有参数的逻辑值为真时返回 TRUE；只要一个参数的逻辑值为假即返回 FALSE。简言之，就是当AND的参数全部满足某一条件时，返回结果为TRUE，否则为FALSE。语法为AND(logical1,logical2, .)，其中Logical1, logical2

13、, . 表示待检测的 1 到 30 个条件值，各条件值可能为TRUE，可能为 FALSE。 参数必须是逻辑值，或者包含逻辑值的数组或引用。举例说明：1、 在B2单元格中输入数字50，在C2中写公式=AND(B230,B260)。由于B2等于50的确大于30、小于60。所以两个条件值（logical）均为真，则返回结果为TRUE。 2、 如果 B1-B3 单元格中的值为 TRUE、FALSE、TRUE，显然三个参数并不都为真，所以 在B4单元格中的公式=AND(B1:B3) 等于 FALSE （二）OR函数OR函数指在其参数组中，任何一个参数逻辑值为 TRUE，即返回 TRUE。它与AND函数的

14、区别在于，AND函数要求所有函数逻辑值均为真，结果方为真。而OR函数仅需其中任何一个为真即可为真。比如，上面的示例2，如果在B4单元格中的公式写为=OR(B1:B3)则结果等于TRUE （三）NOT函数NOT函数用于对参数值求反。当要确保一个值不等于某一特定值时，可以使用 NOT 函数。简言之，就是当参数值为TRUE时，NOT函数返回的结果恰与之相反，结果为FALSE.比如NOT(2+2=4)，由于2+2的结果的确为4，该参数结果为TRUE，由于是NOT函数，因此返回函数结果与之相反，为FALSE。二、IF函数（一）IF函数说明IF函数用于执行真假值判断后，根据逻辑测试的真假值返回不同的结果，

15、因此If函数也称之为条件函数。它的应用很广泛，可以使用函数 IF 对数值和公式进行条件检测。它的语法为IF(logical_test,value_if_true,value_if_false)。其中Logical_test表示计算结果为 TRUE 或 FALSE 的任意值或表达式。本参数可使用任何比较运算符。Value_if_true显示logical_test 为 TRUE 时返回的值，Value_if_true 也可以是其他公式。Value_if_false显示 logical_test 为 FALSE 时返回的值。Value_if_false 也可以是其他公式。简言之，如果第一个参数lo

16、gical_test返回的结果为真的话，则执行第二个参数Value_if_true的结果，否则执行第三个参数Value_if_false的结果。IF函数可以嵌套七层，用 value_if_false 及 value_if_true 参数可以构造复杂的检测条件。Excel 还提供了可根据某一条件来分析数据的其他函数。例如，如果要计算单元格区域中某个文本串或数字出现的次数，则可使用 COUNTIF 工作表函数。如果要根据单元格区域中的某一文本串或数字求和，则可使用 SUMIF 工作表函数。（二）IF函数应用1、 输出带有公式的空白表单 以图中所示的人事状况分析表为例，由于各部门关于人员的组成情况的

17、数据尚未填写，在总计栏（以单元格G5为例）公式为：=SUM(C5:F5)我们看到计算为0的结果。如果这样的表格打印出来就页面的美观来看显示是不令人满意的。是否有办法去掉总计栏中的0呢？你可能会说，不写公式不就行了。当然这是一个办法，但是，如果我们利用了IF函数的话，也可以在写公式的情况下，同样不显示这些0。如何实现呢？只需将总计栏中的公式（仅以单元格G5为例）改写成：=IF(SUM(C5:F5),SUM(C5:F5),)通俗的解释就是：如果SUM(C5:F5)不等于零，则在单元格中显示SUM(C5:F5)的结果，否则显示字符串。（1） SUM(C5:F5)不等于零的正规写法是SUM(C5:F5

18、)0，在EXCEL中可以省略0；（2） 表示字符串的内容为空，因此执行的结果是在单元格中不显示任何字符。 2、 不同的条件返回不同的结果我们设定按照平均分判断该学生成绩是否合格的规则。如果各科平均分超过60分则认为是合格的，否则记作不合格：=IF(B1160,合格,不合格)语法解释为，如果单元格B11的值大于60，则执行第二个参数即在单元格B12中显示合格字样，否则执行第三个参数即在单元格B12中显示不合格字样。3、 多层嵌套函数的应用(Excel的IF函数最多允许七重嵌套)我们设定综合评定的规则为当各科平均分超过90时，评定为优秀=IF(F1160,IF(AND(F1190),优秀,合格),

19、不合格)语法解释为，如果单元格F11的值大于60，则执行第二个参数，在这里为嵌套函数，继续判断单元格F11的值是否大于90（为了让大家体会一下AND函数的应用，写成AND(F1190)，实际上可以仅写F1190），如果满足在单元格F12中显示优秀字样，不满足显示合格字样，如果F11的值以上条件都不满足，则执行第三个参数即在单元格F12中显示不合格字样。（三）根据条件计算值COUNTIF可以用来计算给定区域内满足特定条件的单元格的数目。比如在成绩表中计算每位学生取得优秀成绩的课程数。在工资表中求出所有基本工资在2000元以上的员工数。语法形式为COUNTIF(range,criteria)。其中

20、Range为需要计算其中满足条件的单元格数目的单元格区域。Criteria确定哪些单元格将被计算在内的条件，其形式可以为数字、表达式或文本。例如，条件可以表示为 32、32、32、apples。1、成绩表=COUNTIF(B4:B10,90)语法解释为，计算B4到B10这个范围中有多少个数值大于90的单元格2、 销售业绩表销售业绩表可能是综合运用IF、SUMIF、COUNTIF非常典型的示例。比如，可能希望计算销售人员的订单数，然后汇总每个销售人员的销售额，并且根据总发货量决定每次销售应获得的奖金。 按销售人员汇总表 如图10所示的表完全是利用函数计算的方法自动汇总的数据。首先建立一个按照销售

21、人员汇总的表单样式，如图所示。然后分别计算订单数、订单总额、销售奖金。（1） 订单数 -用COUNTIF计算销售人员的订单数。=COUNTIF($C$2:$C$13,A17)语法解释为计算单元格A17(即销售人员ANNIE)在销售人员清单$C$2:$C$13的范围内出现的次数，这个出现的次数即可认为是该销售人员ANNIE的订单数。（2） 订单总额-用SUMIF汇总每个销售人员的销售额。=SUMIF($C$2:$C$13,A17,$B$2:$B$13)此公式在销售人员清单$C$2:$C$13中检查单元格A17 中的文本（即销售人员ANNIE），然后计算订单金额列（$B$2:$B$13）中相应量的

22、和。这个相应量的和就是销售人员ANNIE的订单总额。（3） 销售奖金-用IF根据订单总额决定每次销售应获得的奖金。假定公司的销售奖金规则为当订单总额超过5万元时，奖励幅度为百分之十五，否则为百分之十。根据这一规则仍以销售人员ANNIE为例说明。公式为：=IF(C1710。清单是指包含相关数据的一系列工作表行，例如，发票数据库或一组客户名称和电话号码。清单的第一行具有列标志。2、 建立条件区域的基本要求（1）在可用作条件区域的数据清单上插入至少三个空白行。（2）条件区域必须具有列标志。（3）请确保在条件值与数据清单之间至少留了一个空白行。如在上面的示例中A1:F3就是一个条件区域，其中第一行为列

23、标志，如树种、高度。3、 筛选条件的建立在列标志下面的一行中，键入所要匹配的条件。所有以该文本开始的项都将被筛选。例如，如果您键入文本“Dav”作为条件，Microsoft Excel 将查找“Davolio”、“David”和“Davis”。如果只匹配指定的文本，可键入公式=text，其中“text”是需要查找的文本。如果要查找某些字符相同但其他字符不一定相同的文本值，则可使用通配符。Excel中支持的通配符为： 4、 几种不同条件的建立（1）单列上具有多个条件如果对于某一列具有两个或多个筛选条件，那么可直接在各行中从上到下依次键入各个条件。例如，上面示例的条件区域显示“树种”列中包含“苹果

24、树”或“梨树”的行。（2）多列上具有单个条件若要在两列或多列中查找满足单个条件的数据，请在条件区域的同一行中输入所有条件。例如，下面示例的条件区域显示所有在“高度”列中大于10且“产量”大于10的数据行。 （3）某一列或另一列上具有单个条件若要找到满足一列条件或另一列条件的数据，请在条件区域的不同行中输入条件。例如，上面示例的条件区域显示所有在“高度”列中大于10的数据行。（4）两列上具有两组条件之一若要找到满足两组条件（每一组条件都包含针对多列的条件）之一的数据行，请在各行中键入条件。例如，下面的条件区域将显示所有在“树种”列中包含“苹果树”且“高度”大于10的数据行，同时也显示“樱桃树”的

25、“使用年数”大于10年的行。 （5）一列有两组以上条件若要找到满足两组以上条件的行，请用相同的列标包括多列。例如，上面示例的条件区域显示介于10和16之间的高度。（6）将公式结果用作条件Excel中可以将公式（公式：单元格中的一系列值、单元格引用、名称或运算符的组合，可生成新的值。公式总是以等号 (=) 开始。）的计算结果作为条件使用。用公式创建条件时，不要将列标志作为条件标记使用，应该将条件标记置空，或者使用清单中非列标志的标记。例如，下面的条件区域显示在列 C 中，其值大于单元格区域 C7:C10 平均值的行。=C7AVERAGE($C$7:$C$10) 需要注意的是用作条件的公式必须使用

26、相对引用来引用列标志（例如，“高度”），或者引用第一个记录的对应字段。公式中的所有其他引用都必须是绝对引用并且公式必须计算出结果 TRUE 或 FALSE。在本公式示例中，C7 引用了数据清单中第一个记录（行 7）的字段（列 C）。 当然也可以在公式中使用列标志来代替相对的单元格引用或区域名称。当 Microsoft Excel 在包含条件的单元格中显示错误值 #NAME? 或 #VALUE! 时，您可以忽略这些错误，因为它们不影响列表的筛选。 此外Microsoft Excel 在计算数据时不区分大小写。快速转换学生考试成绩等级有的时候，会遇到要将学生的考试成绩按实际考试分数转换成相应成绩等

27、级的情况，如将考试成绩在90分以上的成绩转换成“A+”形式，85-89分的成绩转换成“A”形式.。一般情况，在excel表格中大家会采用IF()函数来设计公式进行转换，这样所设计的公式会变得很复杂，如果进行转换的成绩等级类型超过IF()函数的最大嵌套(7层)时，IF()函数就无能为力了。这时我们可用如下的方法来简化操作。1、打开学生成绩工作表2、在G2到I12单元格录入考试成绩分数段与考试成绩等级对照表。3、在D3单元格录入公式“=INDEX(I$3:I$12,MATCH(1,(C3=G$3:G$12)*(C3=H$3:H$12),0)”，由于该公式为数组公式，在录入完上述内容后，必须同时按下

28、“Ctrl+Shift+Enter”键，为上述公式内容加上数组公式标志即大括号“”。该公式的作用就是，根据C3单元格中的学生成绩，在D3单元格自动将该成绩转换成相应的成绩等级。4、将光标移到D3单元格，向下拖动填充柄至D12单元格，将公式进行快速复制，这样就可以迅速完成转换学生成绩等级的工作(图2)。5、还可以按照自己的喜好，将G2至I12的单元格区域设置为“隐藏”，以使表格更加美观。Excel的宏管理仓库的事例 一位朋友从事汽车小配件批发经营，在仓库管理方面令他很伤脑筋。他经营有百多种不同规格的产品且各种规格产品每天都有数万至数十万的入库出库量，针对这些庞大的数字想查询各种产品的库存情况却无

29、从下手，导致了经常出现某些产品库存短缺而某些产品库存过剩的情况。为此，他希望建立一个工作表能随时查询各种产品的库存情况而操作要求尽量方便。下面以四种不同规格产品五天的库存情况为例来介绍我的解决方案。 1.数据清单的建立 根据具体情况用Excel建立数据清单(图1)，表中每种不同规格产品的库存量即为该产品所有时间当日结存的总和。 2.宏的应用 点击“工具”“宏”“录制新宏”弹出“录制新宏”对话框，在“宏名”框内输入宏名“汇总”，在快捷键下的框内输入“H”将此宏的快捷操作定为“Ctrl+Shift+H”，单击“确定”后系统会出现“停止录制”的工具条。将光标定在数据清单内任一单元格，点击“数据”菜单

30、“排序”弹出排序对话框，在“主要关键字”的下拉框内选定“品名”、在“次要关键字”的下拉框内选定“规格”后单击“确定”。再次点击“数据”菜单“分类汇总”弹出对话框，在“分类字段”的下拉框内选定“品名”、在“汇总”的下拉框内选定“求和”、在“选定汇总项”框内选定“当日结存” 复选框、将“替换当前分类汇总”和“汇总结果显示在数据下方”两项的复选框打钩并单击“确定”。再次点击“数据”菜单“分类汇总”弹出对话框，在“分类字段”的下拉框内选定“规格”，去掉“替换当前分类汇总”前复选框的钩，其他选项不变，单击“确定”，在分类汇总表的左侧出现的分类层次的选择按钮中选“3”，便可出现如图2的工作表，此表可一目了

31、然显示各种产品的库存情况。单击“停止录制”工具条上“停止录制”命令按钮，便完成了第一个宏的录制工作。 依照第点方法再录制一个名为“一览表”的宏，将快捷操作定为“Ctrl+Shift+Y”，将光标定在图2分类汇总表内任一单元格，点击“数据”菜单“分类汇总”弹出对话框，单击“全部删除”“数据”“排序”弹出排序对话框，在“主要关键字”的下拉框内选定“日期”，单击“确定”后再单击“停止录制”的工具条上“停止录制”命令按钮，便完成了第二个宏的录制工作。 3.建立新菜单 为了更方便操作，我们将建立新菜单来执行“宏”的操作，具体步骤如下： 在工具栏或菜单栏的任一处点击右键“自定义”选定“命令”选项卡，在左侧

32、“类别”框内选定“新菜单”， 在右侧的“命令”框内将“新菜单”用鼠标拖到菜单栏上“数据”菜单的右侧，单击“自定义”对话框内“更改所选内容”下拉箭头，在“命名”框内输入“仓存”后确定，这时菜单栏上多了个名为“仓存”的菜单(图2)。 在“自定义”对话框内“命令”选项左侧“类别”框内选定“宏”，在右侧的“命令”框内将“自定义按钮”用鼠标拖到菜单栏上“仓存”菜单的下方，单击“更改所选内容”下拉箭头，在“命名”框内输入“汇总”，在“更改按钮图”选项选定如图2的图标，在“指定宏”选项指定“汇总”宏，单击“确定”后便完成“仓存”菜单下“汇总”菜单命令设计工作。用同样的方法在“仓存”菜单下再设计名为“一览表”

33、的菜单命令。至此，我们就完成了全部设计工作，只要点击“仓存”菜单下“汇总”菜单命令系统便可自动将各种规格的产品仓存情况显示出来，再点击“一览表”菜单命令便可还原到明细表状态。这样我们就可方便地随时查询各种产品的库存情况了。让Excel录入成绩更智能化功能一：在“成绩”列输入成绩后，在“等第”列就能智能地显示出相应的“等第”，如果“等第”为“不及格”，还会用红色字体提醒。 功能二：在“成绩”列中误输入文字或者输入的成绩数值不符合具体要求时（小学考试多采用100分制，数值大于100或者小于0时都是错误的），在“等第”列就会用醒目的灰底黄字显示提示信息：“分数输入错误”。 功能三：当某位学生因病或因

34、事缺考，“成绩”列中的分数为空时，相应的“等第”也为空，不会出现因为学生缺考而导致“等第”是“不及格”的现象。 制作方法 1. 新建Excel工作簿，在单元格A1、B1、C1中分别输入“姓名”、“成绩”、“等第”。 2. 从A2开始输入学生姓名，从B2开始输入学生成绩，C2显示相应的“等第”，在C2单元格中输入如下内容：“=IF(ISTEXT(B2),分数输入错误,IF(OR(B2100),分数输入错误,IF(B2=85,优秀,IF(B2=75,良好,IF(B2=60,及格,IF(ISNUMBER(B2),不及格,IF(ISBLANK(B2), ,)”（如图2）。 说明：这里应用了IF函数的嵌

35、套，如果第一个逻辑判断表达式“ISTEXT(B2)”为真时，在C2中就显示“分数输入错误”，如果为假，就执行第二个IF语句；如果第二个IF语句中的逻辑表达式“OR(B2100)”为真，在C2中就显示“分数输入错误”，如果为假，就执行第三个IF语句中的逻辑表达式依此类推，直至结束。整个IF语句的意思是：当您在B2单元格输入的内容是文字时，在C2单元格就显示“分数输入错误”；当您输入的数值比0小或者比100大时，也显示“分数输入错误”，当B2的数值大于或等于85时就显示“优秀”，当B2的数值大于或等于75时就显示“良好”，当B2的数值大于或等于60时就显示“及格”，如果是其他数值就显示“不及格”；

36、如果B2单元格内容为空，那么C2也为空。 3. 拖动C2的“填充柄”，利用Excel的自动填充功能将这个公式复制到下面的单元格中。 4. 同时选中列B和列C，点击“格式/条件格式”，出现“条件格式”设置窗口，（点击“添加条件”可以添加更多的条件，但最多是3个条件），对“条件格式”进行如图3所示设置（点击相应条件中的“格式”就能设置文字的格式），并保存文档。 说明：“条件1”限定所有介与0和59之间的数值变为红色字体；“条件2”限定所有等于“不及格”的单元格文字变为红色字体；“条件3”限定所有等于“分数输入错误”的单元格文字为蓝底黄字。用Excel函数快速整理错乱成绩表 (1) “学生基本信息表

37、”的姓名与“成绩表”中的姓名不一样，“学生基本信息表”中的“王一”在“成绩表”中为“ 王一”，出现了全角或半角空格。 (2) “学生基本信息表”中王小平在“成绩表”中无此人，即“学生基本信息表”的人数多于“成绩表”的人数。 (3) “成绩表”中成绩列为文本方式，且出现了全角数字。 (4) 每个表的数据为几千条。如果对“成绩表”中的姓名列进行排序，把成绩列进行复制粘贴到“学生基本信息表”中的成绩列，出现错位。我通过Excel函数SUBSTITUTE和LOOKUP来解决，将“学生基本信息表”和“成绩表”进行了一些修改，实现将“成绩表”中的数据复制到“学生基本信息表”中，并且保持最终表格的清爽和数据

38、的正确。除去“成绩表”中全角或半角空格：让“成绩表”中的学生姓名显示和“学生基本信息表”中的一样。此时我利用替换公式SUBSTITUTE(SUBSTITUTE(A2，半角空格 ，)，全角空格，)。在D2单元格输入公式=SUBSTITUTE(SUBSTITUTE(A2， ，)，)，然后在整个D列复制公式。选择D列数据进行复制，再选择A列所有数据选择性粘贴值和数字格式。转化“成绩表”中成绩列为数字：删除了空格，下面的工作就是将“成绩表”中的数字规范为半角形式。同样利用函数SUBSTITUTE。在E2单元格输入公式=(SUBSTITUTE(C2，。，.)*1，其中SUBSTITUTE(C2，。，.)

39、表示句号“。”转化为点号“.”，“*1”表示转化为数字。然后在E列复制公式。同样进行选择性粘贴。选择E列数据进行复制，再选择C列所有数据选择性粘贴值和数字格式。删除“成绩表”中D列、E列。复制“成绩表”中数据到“学生基本信息表”：最后一步就是复制“成绩表”中的数据到“学生基本信息表”了，但是我们不能简单地利用复制粘贴来实现，因为我们的这个具体案例中包含没有成绩的同学，所以为了数据的正确性，我们知道查询函数LOOKUP有一个特性就是在查询结束后会在指定的区域返回查询结果，我就用它来达到复制“成绩表”中数据的效果。其语法为LOOKUP(lookup_value，lookup_vector，resu

40、lt_vector)。其中Lookup_value为要查找的数值，Lookup_vector为只包含一行或一列的区域，且必须按升序排列，否则要返回错误，Result_vector 返回只包含一行或一列的区域。 如果函数LOOKUP找不到lookup_value，则查找lookup_vector中小于或等于lookup_value的最大数值，如果lookup_value 小于lookup_vector 中的最小值，函数LOOKUP 返回错误值 #N/A，利用这个特性，我们把公式改为=LOOKUP(1，0/(条件)，引用区域)，条件产生的是逻辑值True、False数组，0/True=0，0/fa

41、lse=#DIV0!，即Lookup的第2参数便是由0、#DIV0!组成的数组(都比1小)，如果找到满足条件，就返回对应行引用区域的值;如果没有找到满足条件的记录则返回#N/A错误，从而实行精确查找。 在“学生基本信息表”中D2输入公式=LOOKUP(1，0/(成绩表!A$2:A$5=B2)，成绩表!C$2:C$5)。在没找到数据的一栏出现了#N/A，影响了表格的美观。稍微改进一下，利用ISNA函数判断是否为#N/A，如果是，设置为空。 因此在D2输入公式=IF(ISNA(LOOKUP(1，0/(成绩表!A$2:A$5=B2)，成绩表!C$2:C$5)，LOOKUP(1，0/(成绩表!A$2:

42、A$5=B2)，成绩表!C$2:C$5)，这样#N/A不会出现在单元格中。最后在D列进行公式复制即可。SUBTOTAL函数的用法及应用实例说到SUBTOTAL，日常中最常见到的是在分类汇总中，不过SUBTOTAL还有三个特点，注定在Excel函数里，起着无法代替的作用，在Excel帮助里是这样说明SUBTOTAL的：返回列表或数据库中的分类汇总。通常，使用“数据”菜单中的“分类汇总”命令可以容易地创建带有分类汇总的列表。一旦创建了分类汇总，就可以通过编辑 SUBTOTAL 函数对该列表进行修改。语法: SUBTOTAL(function_num,ref1,ref2, .) Function_n

43、um为 1 到 11（包含隐藏值）或 101 到 111（忽略隐藏值）之间的数字，指定使用何种函数在列表中进行分类汇总计算。Function_num（包含隐藏值） Function_num（忽略隐藏值） 函数 1 101 AVERAGE 2 102 COUNT 3 103 COUNTA 4 104 MAX 5 105 MIN 6 106 PRODUCT 7 107 STDEV 8 108 STDEVP 9 109 SUM 10 110 VAR 11 111 VARP SUBTOTAL的第一个特点是，如果在 ref1, ref2, 中有其他的分类汇总（嵌套分类汇总），将忽略这些嵌套分类汇总，以避

44、免重复计算。也就是在数据区域中有SUBTOTAL获得的结果将被忽略！SUBTOTAL的第二个特点是，当 function_num 为从 1 到 11 的常数时，SUBTOTAL 函数将包括通过“格式”菜单的“行”子菜单下面的“隐藏”命令所隐藏的行中的值。当您要分类汇总列表中的隐藏和非隐藏值时，请使用这些常数。当 function_num 为从 101 到 111 的常数时，SUBTOTAL 函数将忽略通过“格式”菜单的“行”子菜单下面的“隐藏”命令所隐藏的行中的值。当您只分类汇总列表中的非隐藏数字时，使用这些常数。 但不论使用什么 function_num 值，SUBTOTAL 函数都会忽略任何不包括在筛选结果中的行。 而SUBTOTAL 函数不适用于数据行或水平区域。隐藏某一列不影响分类汇总。但是隐藏分类汇总的垂直区域中的某一行就会对其产生影响。SUBTOTAL的第三个特点是，可以代替上面说的11种函数，当有上面说的两种特点情况时，就可以使用SUBTOTAL来完成。有规律的批量求和1、对同一工作簿中多张工作表上的相同单元格或单元格区域求和。公式为：“=SUM(Sheet1:SheetN!A1)”2、在AZ列（不定列）每间隔两列求和。公式为：“=SUM(MOD(COLUMN(A:Z),3)=1)*A1:Z1)”3、从第2行起第1000行（不定行）每间隔5行求和。公