任务二 函数的使用

知识点

1.什么是函数？函数有什么作用？

答：函数是按照特定顺序对一些参数进行预定义的公式。为了减少操作步骤，提高计算速度，可通过函数来简化公式的计算过程。用户在熟知函数计算方法的情况下，只需对数据进行定义就会得到计算结果。函数和公式都是Excel非常重要的功能，了解并熟练运用公式和函数，才能深入认识Excel在数据计算上的强大功能。

2.函数由哪几部分组成？

答：每个函数都包含三个部分：函数名称、参数和小括号，参数之间使用"，"隔开。

3.输入函数有哪几种方法？

答：函数的输入有多种方式： （1）若用户对函数用法比较熟悉，可直接输入。 （2）若用户不能确定函数的名称和参数时，可使用"插入函数"对话框搜索并使用函数。方法是单击"插入函数"按钮或按组合键"Shift+F3"，打开"插入函数"对话框，在"搜索函数"框中输入关键词（如"求平均"），单击"转到"按钮即可列出相关函数；也可以在"或选择类别"下拉列表中选择"统计"，在"选择函数"列表中双击需要的函数。

4.SUM函数的语法格式和功能是什么？

答：语法：SUM（number1，[number2]，…） 功能：计算各参数中所有数值的和。 说明： （1）number1 必需。想要相加的第一个数值参数。 （2）number2，… 可选。想要相加的2到255个数值参数。 （3）每个参数都可以是区域、单元格引用、数组、常量、公式或另一个函数的结果。 （4）如果参数是一个数组或引用，则只计算其中的数字。数组或引用中的空白单元格、逻辑值或文本将被忽略。

5.SUMIF函数的语法格式和功能是什么？

答：语法：SUMIF（range，criteria，[sum_range]） 功能：对满足条件的单元格求和。 说明： （1）range 必需。用于条件判断的单元格区域。每个区域中的单元格都必须是数字或名称、数值或包含数字的引用。空值和文本值将被忽略。 （2）criteria 必需。用于确定对哪些单元格求和的条件，其形式可以为数字、表达式、单元格引用、文本或函数。例如，条件可以表示为 32、">32"、B5、32、"苹果"或 TODAY（）。 （3）sum_range 可选。要求和的实际单元格。如果省略，则 Excel 会对在 range 参数中指定的单元格（即使用条件的单元格）求和。如果 sum_range 参数被省略，Excel 会对在 range 参数中指定的单元格求和。

6.SUMIF函数的条件中可以使用通配符吗？

答：可以在 criteria 参数中使用通配符（包括括号问号"?"和星号"*"）。问号匹配任意单个字符；星号匹配任意一串字符。如果要查找实际的问号或星号，请在该字符前键入波形符"~"。

7.SUMIFS函数的语法格式和功能是什么？

答：语法：SUMIFS（sum_range，criteria_range1，criteria1，[criteria_range2，criteria2]，…） 功能：对区域中满足多个条件的单元格求和。 说明： （1）sum_range 必需。对一个或多个单元格求和，包括数字或包含数字的名称、区域或单元格引用。忽略空白和文本值。 （2）criteria_range1 必需。在其中计算关联条件的第一个区域。 （3）criteria1 必需。条件的形式为数字、表达式、单元格引用或文本，可用来定义将对哪些单元格进行计数。例如，条件可以表示为 32、">32"、B4、"苹果"或"32"。 （4）criteria_range2，criteria2，… 可选。附加的区域及其关联条件。最多允许 127 个区域/条件对。 （5）仅在 sum_range 参数中的单元格满足所有相应的指定条件时，才对该单元格求和。 （6）sum_range 中包含 TRUE 的单元格计算为 1，sum_range 中包含 FALSE 的单元格计算为 0（零）。 （7）与SUMIF函数中的区域和条件参数不同，SUMIFS函数中每个 criteria_range 参数包含的行数和列数必须与 sum_range 参数相同。

8.SUMIFS和SUMIF的参数顺序有什么不同？

答：【注意】SUMIFS 和 SUMIF 函数的参数顺序有所不同。具体而言，sum_range 参数在 SUMIFS 中是第一个参数，而在 SUMIF 中则是第三个参数。如果要复制和编辑这些相似函数，请确保按正确的顺序放置参数。

9.ABS函数的作用是什么？

答：语法：ABS（number） 功能：返回number参数的绝对值。示例应用：在单元格中输入公式"=ABS（-3）"，结果为3。

10.AND函数的语法格式和功能是什么？

答：语法：AND（logical1，[logical2]，…） 功能：所有参数的计算结果为TRUE时，返回TRUE；只要有一个参数的计算结果为FALSE，即返回FALSE。 说明： （1）logical1 必需。要检验的第一个条件，其计算结果可以为 TRUE 或 FALSE。 （2）logical2，… 可选。要检验的其他条件，其计算结果可以为 TRUE 或 FALSE，最多可包含 255 个条件。 （3）参数的计算结果必须是逻辑值（如 TRUE 或 FALSE），或者参数必须是包含逻辑值的数组或引用。 （4）如果数组或引用参数中包含文本或空白单元格，则这些值将被忽略。 （5）如果指定的区域中不包含逻辑值，AND 函数将返回错误值 #VALUE!。 （6）AND 函数一般与其他函数一起使用，用于扩展其判断能力，常用于判断必须满足所有条件，才能返回指定的值。 实例应用：函数AND（3>5，5<3）运算结果为FALSE。

11.OR函数的语法格式和功能是什么？

答：语法：OR（logical1，[logical2]，…） 功能：在其参数组中，只要有一个参数逻辑值为 TRUE，即返回 TRUE；所有参数的逻辑值为 FALSE，才能返回 FALSE。 说明： （1）Logical1 是必需的，后续的逻辑值是可选的。这些是 1 到 255 个需要进行测试的条件，测试结果可以为 TRUE 或 FALSE。 （2）参数必须能计算为逻辑值，如 TRUE 或 FALSE，或者为包含逻辑值的数组或引用。 （3）如果数组或引用参数中包含文本或空白单元格，则这些值将被忽略。 （4）如果指定的区域中不包含逻辑值，函数 OR 返回错误值 #VALUE!。 实例应用：函数OR（3>5，5<3）运算结果为FALSE。

12.NOT函数的作用是什么？

答：语法：NOT（logical） 功能：对参数值求反。当要确保一个值不等于某一特定值时，可以使用 NOT 函数。 说明：如果参数逻辑值为 FALSE，函数 NOT 返回 TRUE；如果参数逻辑值为 TRUE，函数 NOT 返回 FALSE。 实例应用：函数NOT（3>5），运算结果为TRUE。

13.XOR函数的作用是什么？

答：语法：XOR（logical1，[logical2]，…） 功能：对所有参数进行异或运算，运算结果为逻辑值TRUE或FALSE。异或运算规则：两个逻辑值进行异或运算，两值相同结果为FALSE，两值不同结果为TRUE。 实例应用：函数XOR（1，1）运算结果为FALSE。

14.IF函数的语法格式和功能是什么？

答：语法：IF（logical_test，[value_if_true]，[value_if_false]） 功能：如果指定条件logical_test的计算结果为TRUE，IF函数将返回value_if_true；如果该条件的计算结果为FALSE，则返回value_if_false。 说明： （1）logical_test 必需。计算结果可能为 TRUE 或 FALSE 的任意值或表达式。例如，A10=100 就是一个逻辑表达式；如果单元格 A10 中的值等于100，表达式的计算结果为TRUE；否则为FALSE。此参数可使用任何比较运算符。 （2）value_if_true 可选。logical_test 参数的计算结果为TRUE时所返回的值。 （3）value_if_false 可选。logical_test 参数的计算结果为FALSE时所返回的值。 （4）最多可以使用 64 个 IF 函数作为 value_if_true 和 value_if_false 参数进行嵌套。

15.VLOOKUP函数的语法格式和功能是什么？

答：语法：VLOOKUP（lookup_value，table_array，col_index_num，[range_lookup]） 功能：使用VLOOKUP函数搜索某个单元格区域的第一列，然后返回该区域相同行上任何单元格中的值。 说明： （1）lookup_value 必需。需要在表格或区域的第一列中搜索的值。lookup_value 参数可以是值或引用。如果为 lookup_value 参数提供的值小于 table_array 参数第一列中的最小值，则VLOOKUP将返回错误值 #N/A。 （2）table_array 必需。包含数据的单元格区域。可以使用对区域（例如 A2:D8）或区域名称的引用。table_array 第一列中的值是由 lookup_value 搜索的值。这些值可以是文本、数字或逻辑值。文本不区分大小写。 （3）col_index_num 必需。table_array 参数中必须返回的匹配值的列号。col_index_num 参数为 1 时，返回 table_array 第一列中的值；col_index_num 参数为 2 时，返回 table_array 第二列中的值，依此类推。 （4）range_lookup 可选。一个逻辑值，指定希望 VLOOKUP 查找精确匹配还是近似匹配。如果 range_lookup 为 TRUE，则返回近似匹配值。如果 range_lookup 为 FALSE，VLOOKUP 将只查找精确匹配值。如果 table_array 的第一列中有两个或更多值与 lookup_value 匹配，则使用第一个找到的值。如果找不到精确匹配值，则返回错误值 #N/A。

16.使用VLOOKUP函数有哪些注意事项？

答：【注意】 （1）如果 range_lookup 为 TRUE，则必须按升序排列 table_array 第一列中的值；否则，VLOOKUP可能无法返回正确值。如果 range_lookup 为 FALSE，则不需要对 table_array 第一列中的值进行排序。 （2）在 table_array 的第一列中搜索文本时，请确保 table_array 第一列中的数据不包含前导空格、尾部空格、非打印字符或者未使用一致的直引号（' 或 "）与弯引号（' 或 "）。否则，VLOOKUP可能返回不正确或意外的值。 （3）在搜索数字或日期值时，请确保 table_array 第一列中的数据未存储为文本值。否则，VLOOKUP可能返回不正确或意外的值。

17.YEAR函数的作用是什么？

答：语法：YEAR（serial_number） 功能：返回某日期对应的年份。返回值为 1900 到 9999 之间的整数。 说明：Serial_number 必需。为一个日期值，其中包含要查找年份的日期。应使用 DATE 函数输入日期，或者将日期作为其他公式或函数的结果输入。例如，如果使用函数 DATE（2008，5，23）输入2008年5月23日。如果日期以文本形式输入，则会出现问题。 实例应用：在A4单元格中输入"=YEAR（A1）"，运算结果为A1单元格数据的年份值2012。

18.MONTH函数的作用是什么？

答：语法：MONTH（serial_number） 功能：返回以序列号表示的日期中的月份。月份是介于 1（一月）到 12（十二月）之间的整数。 说明：Serial_number 必需。为一个日期值，其中包含要查找月份的日期。 实例应用：在A1单元格中输入"=MONTH（A1）"，运算结果为A1单元格数据的月份值3。

19.DAY函数的作用是什么？

答：语法：DAY（serial_number） 功能：返回以序列号表示的某日期的天数，用整数 1 到 31 表示。 说明：Serial_number 必需。为一个日期值，其中包含要查找日的日期。

20.TODAY函数的作用是什么？

答：语法：TODAY（） 功能：返回当前日期。 实例应用：如果知道某人出生于1963年，可以使用以下公式计算出来对方到目前为止的年龄："=YEAR（TODAY（））-1963"。此公式使用TODAY函数作为YEAR函数的参数，然后减去1963，最终返回对方的年龄。

21.NOW函数的作用是什么？

答：语法：NOW（） 功能：返回当前日期和时间。

22.DATE函数的语法格式和功能是什么？

答：语法：DATE（year，month，day） 功能：DATE函数返回表示特定日期的连续序列号。 说明： （1）Year 必需。year 参数的值可以包含一到四位数字。 （2）Month 必需。一个正整数或负整数，表示一年中从1月至12月的各个月。如果 month 大于 12，则 month 从指定年份的一月份开始累加该月份数。例如，DATE（2008，14，2）返回表示2009年2月2日的序列号。如果 month 小于 1，month 则从指定年份的一月份开始递减该月份数，然后再减上1个。例如，DATE（2008，-3，2）返回表示2007年9月2日的序列号。 （3）Day 必需。一个正整数或负整数，表示一月中从1日到31日的各天。如果 day 大于指定月份的天数，则 day 从指定月份的第一天开始累加该天数。例如，DATE（2008，1，35）返回表示2008年2月4日的序列号。如果 day 小于 1，则 day 从指定月份的上一个月的最后一天开始递减该天数。例如，DATE（2008，1，-15）返回表示2007年12月16日的序列号。

23.LEFT和LEFTB函数的作用是什么？

答：语法：LEFT（text，[num_chars]）／LEFTB（text，[num_bytes]） 功能：根据所指定的字符数，LEFT 返回文本字符串中第一个字符或前几个字符。LEFTB 基于所指定的字节数返回文本字符串中第一个字符或前几个字符。 说明： （1）LEFT和LEFTB函数语法具有下列参数：

• Text 必需。包含要提取的字符的文本字符串。
• Num_chars 可选。指定要由 LEFT 提取的字符的数量。Num_chars 必须大于或等于零。如果 num_chars 大于文本长度，则 LEFT 返回全部文本。如果省略 num_chars，则假设其为1。
• Num_bytes 可选。指定要由 LEFTB 提取的字符的数量。 （2）【注意】函数LEFT面向使用单字节字符集（SBCS）的语言，而函数 LEFTB 面向使用双字节字符集（DBCS）的语言。无论默认语言设置如何，函数LEFT始终将每个字符（不管是单字节还是双字节）按1计数。当启用支持DBCS的语言的编辑并将其设置为默认值时，函数LEFTB会将每个双字节字符按2计数，否则，函数LEFTB会将每个单字节字符按1计数。 实例应用：公式"=LEFT（"我们"，2）"返回值为"我们"，而公式"=LEFTB（"我们"，2）"返回值为"我"。

24.RIGHT和RIGHTB函数的作用是什么？

答：语法：RIGHT（text，[num_chars]）／RIGHTB（text，[num_bytes]） 功能：RIGHT 根据所指定的字符数返回文本字符串中最后一个或多个字符。RIGHTB 根据所指定的字节数返回文本字符串中最后一个或多个字符。 实例应用：公式"=RIGHT（"我们的祖国是花园"，2）"返回值为"花园"，而公式"=RIGHTB（"我们的祖国是花园"，2）"返回值为"园"。

25.MID和MIDB函数的作用是什么？

答：语法：MID（text，start_num，num_chars）／MIDB（text，start_num，num_bytes） 功能：MID 从文本字符串中指定位置开始的特定数目的字符，该数目由用户指定。MIDB 根据指定的字节数，返回文本字符串中从指定位置开始的特定数目的字符。 说明： （1）MID和MIDB函数语法具有下列参数：

• Text 必需。包含要提取字符的文本字符串。
• Start_num 必需。文本中要提取的第一个字符的位置。文本中第一个字符的 start_num 为1，依此类推。
• Num_chars 必需。指定希望MID从文本中返回字符个数。
• Num_bytes 必需。指定希望MIDB从文本中返回字符数个数（字节数）。 （2）如果 start_num 大于文本长度，则 MID 返回空文本（""）。 （3）如果 start_num 小于文本长度，但 start_num 加上 num_chars 超出了文本的长度，则 MID 只返回到文本末尾的字符。 实例应用：公式"=MID（"我们的祖国是花园"，4，2）"返回值为"祖国"。公式"=MIDB（"我们的祖国是花园"，7，4）"返回值为"祖国"。

26.LEN和LENB函数的作用是什么？

答：语法：LEN（text）／LENB（text） 功能：LEN 返回文本字符串中的字符数。LENB 返回文本字符串中用于代表字符的字节数。 说明：Text 必需。要查找其长度的文本。空格将作为字符进行计数。 实例应用：公式"=LEN（"我们ABC"）"结果为5，公式"=LENB（"我们ABC"）"结果为7。

27.TEXT函数的作用是什么？

答：语法：TEXT（value，format_text） 功能：将数值转换为按指定数字格式表示的函数。 说明： （1）value 必需。可以是数值型也可以是文本型数字。 （2）format_text 必需。表示数字格式，常用双引号括起来，例如"m/d/yyyy"或"#,##0.00"。 实例应用：在身份证号码中获取表示出生年月日的8位数据后，需要将其以"××××年××月××日"的形式显示，输入公式"=TEXT（MID（D3，7，8），"0000年00月00日"）"即可完成。

28.REPLACE和REPLACEB函数的作用是什么？

答：语法：REPLACE（old_text，start_num，num_chars，new_text）／REPLACEB（old_text，start_num，num_bytes，new_text） 功能：REPLACE 使用其他文本字符串并根据所指定的字符数替换文本字符串中的部分文本。REPLACEB 使用其他文本字符串并根据所指定的字节数替换文本字符串中的部分文本。 说明： （1）Old_text 必需。要替换其部分字符的文本。 （2）Start_num 必需。用 new_text 替换的 old_text 中字符的位置。 （3）Num_chars 必需。希望 REPLACE 使用 new_text 替换 old_text 中字符的个数。 （4）Num_bytes 必需。希望 REPLACEB 使用 new_text 替换 old_text 中字节的个数。 （5）New_text 必需。用于替换 old_text 中字符的文本。 实例应用：如图2-47所示，要屏蔽手机号码中间四位，形成图中B列所示，应该在B2单元格中输入公式"=REPLACE（A2，4，4，"****"）"，回车确认后，复制公式到其他单元格。

29.SUBSTITUTE函数的作用是什么？

答：语法：SUBSTITUTE（text，old_text，new_text，[instance_num]） 功能：在文本字符串中用 new_text 替代 old_text。 说明： （1）Text 必需。需要替换其中字符的文本，或对含有文本（需要替换其中字符）的单元格的引用。 （2）old_text 必需。需要替换的旧文本。 （3）new_text 必需。用于替换 old_text 的文本。 （4）instance_num 可选。用来指定要以 new_text 替换第几次出现的 old_text。如果指定了 instance_num，则只有满足要求的 old_text 被替换；否则会将Text中出现的每一处 old_text 都更改为 new_text。 实例应用：如图2-48所示工作表中完成如下操作：在"备注"列中完成B列中所有的"男式"替换为"男"，操作步骤：选中D2单元格，输入公式"=SUBSTITUTE（B2，"男式"，"男"）"，回车确认，复制公式到其他单元格。

30.UPPER函数的作用是什么？

答：语法：UPPER（text） 功能：用于将文本字符串中的所有小写字母转换为大写字母。 说明：Text 必需。需要转换为大写形式的文本。Text 可以是引用或文本字符串。 实例应用：在单元格中输入公式"=UPPER（"Hello World"）"，结果为"HELLO WORLD"。

31.LOWER函数的作用是什么？

答：语法：LOWER（text） 功能：将一个文本字符串中的所有大写字母转换为小写字母。 说明：Text 必需。要转换为小写字母的文本。函数LOWER不改变文本中的非字母的字符。 实例应用：在单元格中输入公式"=LOWER（"Hello World"）"，结果为"hello world"。

32.TRIM函数的作用是什么？

答：语法：TRIM（text） 功能：除了单词之间的单个空格外，清除文本中所有的空格。在从其他应用程序中获取带有不规则空格的文本时，可以使用函数TRIM。 说明：Text 必需。需要删除其中空格的文本。 实例应用：在单元格中输入公式"=TRIM（"A B C D"）"，结果为"A BC D"。

33.COUNT函数的作用是什么？

答：语法：COUNT（value1，[value2]，…） 功能：COUNT函数计算包含数字的单元格区域以及参数列表中数字的个数。 说明： （1）value1 必需。要计算其中数字的个数的第一个项、单元格引用或区域。 （2）value2，… 可选。要计算其中数字的个数的其他项、单元格引用或区域，最多可包含 255 个。 （3）这些参数可以包含或引用各种类型的数据，但只有数字类型的数据才被计算在内。 （4）如果参数为数字、日期或者代表数字的文本（例如，用引号引起的数字，如"1"），则将被计算在内。 （5）逻辑值和直接键入到参数列表中的代表数字的文本被计算在内。 （6）如果参数为错误值或不能转换为数字的文本，则不会被计算在内。 （7）如果参数为数组或引用，则只计算数组或引用中数字的个数。不会计算数组或引用中的空白单元格、逻辑值、文本或错误值。 （8）如果要计算逻辑值、文本值或错误值，请使用 COUNTA 函数。 实例应用：在学生成绩单中，计算各科目参考人数，在B22单元格中输入公式"=COUNT（B2:B21）"，回车确认，拖动填充柄复制公式到其他单元格。

34.COUNTIF函数的作用是什么？

答：语法：COUNTIF（range，criteria） 功能：COUNTIF函数对区域中满足单个指定条件的单元格进行计数。 说明： （1）range 必需。要对其进行计数的单元格区域，其中包括数字或名称、数组或包含数字的引用。空值和文本值将被忽略。 （2）criteria 必需。用于定义将对哪些单元格进行计数的数字、表达式、单元格引用或文本字符串。例如，条件可以表示为 32、"32"、B4、"苹果"或"32"。 实例应用：如图2-49中，统计语文成绩大于等于80分的人数，输入公式为"=COUNTIF（B2:B22，">=80"）"。

35.COUNTIFS函数的作用是什么？

答：语法：COUNTIFS（criteria_range1，criteria1，[criteria_range2，criteria2]…） 功能：将条件应用于跨多个区域的单元格，计算多个区域中满足给定条件的单元格的个数。 说明： （1）criteria_range1 必需。在其中计算关联条件的第一个区域。 （2）criteria1 必需。条件的形式为数字、表达式、单元格引用或文本，可用来定义将对哪些单元格进行计数。例如，条件可以表示为 32、">32"、B4、"苹果"或"32"。 （3）criteria_range2，criteria2，… 可选。附加的区域及其关联条件。最多允许 127 个区域/条件对。 （4）每一个附加的区域都必须与参数 criteria_range1 具有相同的行数和列数。这些区域彼此不相邻。 （5）每个区域的条件一次应用于一个单元格。如果所有的第一个单元格都满足其关联条件，则计数增加1。如果所有的第二个单元格都满足其关联条件，则计数再增加1，依次类推，直到计算完所有单元格。 （6）如果条件参数是对空单元格的引用，COUNTIFS 会将该单元格的值视为0。 实例应用：如图2-50所示工作表中，统计产地为广州且单价大于等于3，库存量大于20的品种个数，输入公式为"=COUNTIFS（B2:B10，"广州"，C2:C10，">=3"，D2:D10，">20"）"。

36.RANK和RANK.EQ函数有什么异同？

答：两者用法一致，没有区别。rank是Excel早期版本就有的函数，而rank.eq是Excel 2010才开始出现的，同时增加了rank.avg函数，微软准备用rank.eq替换rank函数，以免与rank.avg混淆。所以保留rank，是为了与低版本Excel兼容。 语法：RANK（number，ref，[order]）／RANK.EQ（number，ref，[order]） 功能：返回一个数字在数字列表中的排位。 说明： （1）Number 必需。需要找到排位的数字。 （2）Ref 必需。数字列表数组或对数字列表的引用。Ref 中的非数值值将被忽略。 （3）Order 可选。一个数字，指示数字排位的方式。如果 order 为 0（零）或省略，降序排列。如果 order 不为零，升序排列。 （4）对重复数的排位相同。但重复数的存在将影响后续数值的排位。 【注意】因为公式需要复制到J3:J20单元格区域，为了保证公式复制后数字列表区域引用正确，此处，数字列表区域引用是绝对引用。若公式不用复制，此处用相对引用也可以。

37.RANK.AVG函数与RANK函数有什么区别？

答：语法：RANK.AVG（number，ref，[order]） 功能：返回一个数字在数字列表中的排位，数字的排位是其大小与列表中其他值的比值，如果多个值具有相同的排位，则将返回平均排位。而不是Rank和Rank.EQ中的首次排位。 说明：参数含义与RANK相同。Order为0（零）或省略，降序排列；如果order不为零，升序排列。

38.AVERAGE函数的语法格式和功能是什么？

答：语法：AVERAGE（number1，[number2]，…） 功能：返回参数的平均值（算术平均值）。 说明： （1）Number1 必需。要计算平均值的第一个数字、单元格引用或单元格区域。 （2）Number2，… 可选。要计算平均值的其他数字、单元格引用或单元格区域，最多可包含 255 个。 （3）参数可以是数字、或者是包含数字的名称、单元格区域或单元格引用。 （4）逻辑值和直接键入到参数列表中代表数字的文本被计算在内。 （5）如果区域或单元格引用参数包含文本、逻辑值或空白单元格，则这些值将被忽略；但包含零值的单元格将被计算在内。 （6）如果参数为错误值或为不能转换为数字的文本，将会导致错误。 （7）若要在计算中包含引用中的逻辑值和代表数字的文本，请使用 AVERAGEA 函数。 （8）若要只对符合某些条件的值计算平均值，请使用 AVERAGEIF 函数或 AVERAGEIFS 函数。 实例应用：在图2-51所示的工作表中完成平均分的计算，选中I2单元格，输入公式"=AVERAGE（C2:C2）"，回车确认，复制公式到其他单元格。

39.AVERAGEIF函数的语法格式和功能是什么？

答：语法：AVERAGEIF（range，criteria，[average_range]） 功能：返回某个区域内满足给定条件的所有单元格的平均值（算术平均值）。 说明： （1）range 必需。要计算平均值的一个或多个单元格，其中包括数字或包含数字的名称、数组或引用。 （2）criteria 必需。数字、表达式、单元格引用或文本形式的条件，用于定义要对哪些单元格计算平均值。例如，条件可以表示为32、">32"、"32"、"苹果"或B4。 （3）average_range 可选。要计算平均值的实际单元格集。如果忽略，则使用 range。 （4）忽略区域中包含 TRUE 或FALSE 的单元格。 （5）如果average_range 中的单元格为空单元格，AVERAGEIF 将忽略它。 （6）可以在条件中使用通配符，即问号（?）和星号（*）。 实例应用：在如图2-52所示的工作表的K2单元格中统计"计算机"专业学生的"语文"平均成绩，选中K2单元格，在单元格中输入公式"=AVERAGEIF（A2:A92，"计算机"，C2:C92）"。

40.AVERAGEIFS函数的语法格式和功能是什么？

答：语法：AVERAGEIFS（average_range，criteria_range1，criteria1，[criteria_range2，criteria2]，…） 功能：返回满足多条条件的所有单元格的平均值（算术平均值）。 说明： （1）average_range 必需。要计算平均值的一个或多个单元格，其包括数字或包含数字的名称、数组或引用。 （2）criteria_range1，criteria_range2，… criteria_range1 是必需的，后续的 criteria_range 是可选的。在其中计算关联条件的 1 至 127 个区域。 （3）criteria1，criteria2，… criteria1 是必需的，后续的 criteria 是可选的。数字、表达式、单元格引用或文本形式的 1 至 127 个条件，用于定义将对其求平均值的单元格。例如，条件可以表示为32、">32"、"32"、"苹果"或B4。 （4）仅当 average_range 中的每个单元格满足为其指定的相应条件时，才对这些单元格进行平均值计算。 （5）与AVERAGEIF函数中的区域和条件参数不同，AVERAGEIFS中每个criteria_range的大小和形状必须与sum_range相同。 （6）如果average_range 中的单元格无法转换为数字，则AVERAGEIFS会返回错误值 #DIV/0!。 （7）如果没有满足所有条件的单元格，AVERAGEIFS 会返回 #DIV/0! 错误值。 实例应用：在图2-43所示工作表的E2单元格中完成"卢宁"销售的"香蕉"的平均数量，输入公式"=AVERAGEIFS（A2:A9，C2:C9，"卢宁"，B2:B9，"香蕉"）"。

41.MAX和MIN函数的作用是什么？

答：语法：MAX（number1，[number2]，…）／MIN（number1，[number2]，…） 功能：MAX函数为返回一组值中的最大值。MIN函数为返回一组值中的最小值。 说明： （1）Number1，number2，… Number1 是必需的，后续数值是可选的。这些是要从中找出最大值（最小值）的1到255个数字参数。 （2）参数可以是数字，或者是包含数字的名称、数组或引用。 （3）逻辑值和直接键入到参数列表中代表数字的文本被计算在内。 （4）如果参数为数组或引用，则只使用该数组或引用中的数字。数组或引用中的空白单元格、逻辑值或文本将被忽略。 （5）如果参数不包含数字，函数MAX（MIN）返回0（零）。 （6）如果参数为错误值或为不能转换为数字的文本，将会导致错误。 实例应用：函数"=MAX（1，2，3）"结果为"3"。函数"=MIN（1，2，3）"结果为"1"。

42.什么是嵌套函数？

答：嵌套函数就是指在某些情况下，需要将某函数作为另一个函数的参数使用。和单一函数相比，嵌套函数就是在设置主函数的参数或条件时，再一次嵌入另一函数。实例应用：在图2-53所示工作表中完成是否满足退休条件的计算操作，退休条件为"男大于等于60岁或女大于等于55岁"，操作方法如下：首先选中D2单元格，输入公式"=IF（OR（AND（B2="男"，C2>=60），AND（B2="女"，C2>=55）），"是"，"否"）"，回车确认，复制公式到其他单元格。在IF函数的条件中嵌套了OR函数，OR函数中嵌套了AND函数，利用嵌套函数，完成了复杂条件的输入。

43.公式可能产生哪些错误值？各是什么原因导致的？

答：公式或函数不符合格式或其他要求时，将无法得到正确结果，而是以错误信息显示，不同类型的错误信息代表不同含义： （1）######：当某列不够宽而无法显示单元格内所有字符时出现。解决办法是增大列宽。 （2）#DIV/0!：当一个数除以零（0）或不包含任何值的单元格时出现。解决办法是将除数（零）更改为非零数即可。 （3）#N/A：当某个值不可用于函数或公式时出现。解决办法是删除公式中不可用的函数或公式。 （4）#NAME?：当Excel无法识别公式中的文本时出现。解决办法是检查公式中的函数或字符名称是否正确。 （5）#NULL!：公式或函数中的区域运算符或单元格引用不正确时出现。解决办法是更改公式中的单元格引用区域。 （6）#NUM!：当公式或函数包含无效数值时出现。解决办法是删除函数或公式中的无效数据即可解决。 （7）#REF!：当单元格引用无效时出现。解决办法是检查是否删除了其他公式所引用的单元格，或将公式粘贴到其他单元格引用上。 （8）#VALUE!：数据类型不匹配时出现。解决办法是启动公式的错误检查，找出公式中所用的错误类型的数值。

44.如何追踪引用单元格或追踪从属单元格？

答：在Excel中可以用箭头标识引用单元格或从属单元格。 （1）追踪引用单元格：如果单元格 D10 包含公式 =B5，那么单元格 B5 就是单元格 D10 的引用单元格。而从属单元格是包含引用其他单元格的公式的单元格。例如，如果单元格 D10 包含公式 =B5，那么单元格 D10 就是单元格 B5 的从属单元格。 （2）操作方法：选中要查看的单元格 D2，在"公式"选项卡的"公式审核"组中单击"追踪引用单元格"按钮，在公式所在的单元格和与该单元格存在引用关系的单元格之间自动添加了蓝色箭头。选中要查看的单元格 B2，在"公式"选项卡的"公式审核"组中单击"追踪从属单元格"按钮，即可显示"追踪从属单元格"的效果。

45.如何使用"公式求值"检查计算公式？

答：要在Excel中进行公式审核，有一个方法就是公式分步求值，即分步求出公式的计算结果（根据优先级求取）。如果公式没有错误，使用该功能可以便于对公式的理解；如果公式有错误，则可以快速地找出导致错误的发生具体是在哪一步。 操作方法：选中要分步求值的单元格，切换到"公式"选项卡，单击"公式审核"组中的"公式求值"按钮，即可打开"公式求值"对话框，单击"求值"按钮，即可对公式逐一步求值，完成后单击"关闭"按钮即可。当使用的公式和函数出现错误时，选中出现错误的单元格，切换到"公式"选项卡，单击"公式审核"组中的"错误检查"按钮，即可打开"错误检查"对话框，在其中可以看到提示信息，指出单元格出现的错误及错误原因。在"错误检查"对话框中单击"下一个"按钮，将根据向导逐一检查错误值，并获取错误值产生的原因。

46.项目二的两个任务实施各是什么？

答：任务一：学生成绩表分析。期中考试完毕，班主任已完成各科成绩的统计，要计算每个同学的总分、平均分和名次，求出各科目的总分、平均分、最高分、最低分。统计各科目参考人数、及格人数、优秀人数、低分人数。计算出及格率、优秀率、低分率和三率利。根据学生平均分完成评价，平均分96分以上为"优秀"，72-96之间为"合格"，72分以下为"不及格"。要求专业各科目总结、平均分。 （1）用SUM、AVERAGE函数计算学生个人（各科目）的总分和平均分。 （2）用RANK或者RANK.EQ函数计算名次。 （3）用COUNT、COUNTIF函数统计各科目参考人数、及格人数、优秀人数、低分人数及各项比率。 （4）用MIN、MAX函数计算各科目最高分、最低保分。 （5）用IF函数完成个人成绩评价。 （6）用SUMIF函数完成"计算机"专业语文总分的计算。 （7）用AVERAGEIF函数完成"计算机"专业语文平均分的计算。

任务二：制作信息查询单。在工作表"信息查询"中建立"学生信息查询单"，在其中完成"学生基本信息表"中学生各项信息的查询工作。 （1）使用数据有效性设置姓名下拉列表（允许序列"来源"为"学生基本信息表!A3:A12"）。 （2）用VLOOKUP函数引用身份证号码、出生年月、引用评价。 （3）用TEXT、MID函数提取出生年月日并以"××××年××月××日"格式显示。 （4）用SUBSTITUTE函数将家庭住址中"北京市"替换为"海北市"。 （5）用REPLACE函数屏蔽手机号码的后四位，用"*"代替。 （6）用AND、OR函数完成奖励规则：建档立卡成绩合格或者非建档立卡成绩优秀给予奖励。

47.通过本项目主要学习了哪些内容？

答：本项目主要学习了输入编辑公式和使用函数，通过"年级学生成绩单"和"信息查询"两个任务的完成，学习了函数公式的使用情境，掌握了公式函数的正确使用方法，合理应用函数嵌套的方法解决单个函数无法解决的复杂问题。