Excel函数高级运用

Excel函数高级运用 MSOffice高级应用——Excel函数部分 1. 条件求和函数SUMIF ? SUMIF(range,criteria,[sum_range]) ? ? range，必需。用于条件计算的单元格区域。 criteria，必需。用于确定对哪些单元格求和的条件，其形式可以为数字、 表 关于同志近三年现实表现材料材料类招标技术评分表图表与交易pdf视力表打印pdf用图表说话 pdf 达式、单元格引用、文本或函数。例如，条件可以表示为32、">32"、B5、32、"32"、"苹果"或TODAY()。 任何文本条件或任何含有逻辑或数学符号的条件都必须使用双引号(")括起来。如果条件为数字，则无需使用双引号。 ? sum_range，可选。要求和的实际单元格(如果要对未在range参数中指定的单元格求和)。如果省略sum_range参数，Excel会对在range参数中指定的单元格(即应用条件的单元格)求和。 a) 使用SUMIF函数可以对区域中符合指定条件的值求和。例如，假设在含有数字的某一列中，需要对大于5的数值求和。请使用以下公式:=SUMIF(B2:B25,">5"); b) 如果需要，可以将条件应用于某个单元格区域，但却对另一个单元格区域中的对应值求和。 c) 例如，使用公式=SUMIF(B2:B5,"俊元",C2:C5)时，该函数仅对单元格区域C2:C5中与单元格区域B2:B5中等于“俊元”的单元格对应的单元格 中的值求和。 ? 套题7的第3问; 2. 多条件求和函数SUMIFS ? SUMIFS(sum_range,criteria_range1,criteria1,[criteria_range2,criteria2],...) ? ? ? sum_range，必需。对一个或多个单元格求和。 criteria_range1，必需。在其中计算关联条件的第一个区域。 criteria1，必需。条件的形式为数字、表达式、单元格引用或文本，可用来定义将对Criteria_range1参数中的哪些单元格求和。例如，条件可以表示为32、">32"、B4、"苹果"或"32"。 ? criteria_range2,criteria2,„，可选。附加的区域及其关联条件。最多允许127个区域/条件对。 ? 对区域中满足多个条件的单元格求和。例如，如果需要对区域A1:A20中的单元格的数值求和，且需符合以下条件:B1:B20中的相应数值大于零(0)且C1:C20中的相应数值小于10，则可以使用公式:=SUMIFS(A1:A20,B1:B20,">0",C1:C20,"<10") SUMIFS和SUMIF函数的参数顺序有所不同。具体而言，sum_range参数在SUMIFS中是第一个参数，而在SUMIF中，却是第三个参数。 3. 绝对值函数ABS ? ABS(number)，返回数值number的绝对值，number为必需的参数。 4. 向下取整函数INT ? INT(number)，Number必需。将数值向下取整为最接近的整数，不进行四舍五入。 ? 对应练习:套题4的第3问; 5. 四舍五入函数Round ? 四舍五入函数ROUND(number,num_digits) ? number，必需。要四舍五入的数字。 ? num_digits，必需。要进行四舍五入运算的小数位数。 ? 如果num_digits大于 0，则将数字四舍五入到指定的小数位数。 ? 如果num_digits等于 0，则将数字四舍五入到最接近的整数。 ? 如果num_digits小于 0，则将数字四舍五入到小数点左边的相应位数。如，ROUND(32.27,-1)=30 ? 例:如果单元格A1包含23.7825，而且您想要将此数值舍入到两个小数位数，可以使用以下公式: ROUND(A1,2)=23.78。 ? 若要始终进行向上舍入(远离 0)，请使用 ROUNDUP 函数。 ? 若要始终进行向下舍入(朝向 0)，请使用 ROUNDDOWN 函数。 ? Roundup:ROUNDUP(number,num_digits),朝着远离0(零)的方向将数字进行向上舍入。 ? Rounddown:ROUNDDOWN(number,num_digits),朝着零的方向将数字进行向下舍入。 6. 取整函数TRUNC ? TRUNC(number,[num_digits]),将数字的小数部分截去，返回整数。注 意，不进行四舍五入～ ? ? Number必需。需要截尾取整的数字。 Num_digits可选。用于指定取整精度的数字。Num_digits的默认值为0(零)。 ? TRUNC和INT类似，都返回整数。 ? TRUNC直接去除数字的小数部分，而INT则是依照给定数的小数部分的值，将其四舍五入到最 接近的整数。 ? INT和TRUNC在处理负数时有所不同:TRUNC(-4.6)返回-4，而INT(-4.6)返回-5，因为-5是较 小的数。 7. 垂直查询函数Vlookup ? VLOOKUP(lookup_value,table_array,col_index_num,[range_lookup])，使用VLOOKUP函数搜 索某个单元格的第一列，然后返回该区域相同行上任何单元格中的值。 ? lookup_value，必需。要在 表格 关于规范使用各类表格的通知入职表格免费下载关于主播时间做一个表格详细英语字母大小写表格下载简历表格模板下载 或区域的第一列中搜索的值。lookup_value参数可以是值或引用。如果为 lookup_value参数提供的值小于table_array参数第一列中的最小值，则VLOOKUP将返回错误值#N/A。 ? table_array，必需。包含数据的单元格区域。可以使用对区域(例如，A2:D8)或区域名称的引用。table_array 第一列中的值是由lookup_value搜索的值。这些值可以是文本、数字或逻辑值，文本不区分大小写。 如果table_array的第一列中有两个或更 多值与lookup_value匹配，则使用第一个找到的值。如果找不到精确匹配值，则返回错误值#N/A。 ? col_index_num，必需。table_array参数中必须返回的匹配值的列号。col_index_num参数为1时，返回 table_array第一列中的值;col_index_num为2时，返回table_array第二列中的值，依此类推。如果 col_index_num参数: ? 小于1，则VLOOKUP返回错误值。 ? 大于table_array的列数，则VLOOKUP返回错误值。 ? range_lookup，可选。一个逻辑值，指定希望VLOOKUP查找精确匹配值还是近似匹配值:如果range_lookup 为TRUE或被省略，则返回精确匹配值或近似匹配值。如果找不到精确匹配值，则返回小于lookup_value的最大值。 如果range_lookup为TRUE或被省略，则必须按升序排列table_array第一列中的值;否则，VLOOKUP可能无法返回正确的值;如果range_lookup为FALSE，则不需要对table_array第一列中的值进行排序，VLOOKUP将只查找精确匹配值。 ? 例如:假设区域A2:C10中包含雇员列表。雇员的ID号存储在该区域的第一列，如下图所示。若要获取38 号雇员的姓名，可以使用公式=VLOOKUP(38,A2:C10,3,FALSE)。此公式将搜索区域A2:C10的第一列中的值38，然后返回该区域同一行中第三列包含的值作为查询值(“黄雅玲”)。 ? 对应练习:套题2的第2、3问;套题3的第5问;套题5的第3 问;套题6的第4问;套题7的第3问; 套题9的第3问。 8. 逻辑判断函数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参数(即logical_test参数后仅跟一个逗号)， IF函数将返回0(零)。若要显示单词TRUE，请对value_if_true参数使用逻辑值TRUE。 ? value_if_false，可选。logical_test参数的计算结果为FALSE时所要返回的值。 ? 如果logical_test的计算结果为FALSE，并且省略value_if_false参数(即value_if_true参数后没有逗号)， 则IF函数返回逻辑值FALSE。如果logical_test的计算结果为FALSE，且value_if_false参数的值为空 (即，value_if_true参数后仅有一个逗号)，则IF函数返回值0(零)。 ? 例，如果A1大于10，公式=IF(A1>10,"大于10","不大于10")将返回“大于10”，如果A1小于等于10，则 返回“不大于10”。 9. 当前日期和时间函数Now() ? 返回当前日期和时间。当单元格为“数值”类型时，now()函数将返回当前日期和时间所对应的序 列号。 10. 年份函数Year(serial_number) ? 返回指定日期对应的年份。Serial_number必需。为一个日期值，其中包含要查找年份的日期。 11. 当前日期函数Today() ? 返回当天的日期。 ? TODAY函数非常有用。它还可用于计算时间间隔。例如，如果您知道某人出生于1963年，您可使用以下 公式计算对方到其今年生日为止的年龄:=YEAR(TODAY())-1963。 ? 对应练习:套题4的第3问; 12. 条件平均值函数Averageif ? AVERAGEIF(range,criteria,[average_range])，返回某个区域内满足给定条件的所有单元格的平 均值(算术平均值)。 ? ? range必需。要计算平均值的一个或多个单元格，其中包括数字或包含数字的名称、数组或引用。 criteria必需。数字、表达式、单元格引用或文本形式的条件，用于定义要对哪些单元格计算平均值。 ? 例如，条件可以表示为32、"32"、">32"、"苹果"或B4。 ? average_range可选。要计算平均值的实际单元格集。如果忽略，则 使用range。 13. 多条件平均值函数Averageifs ? AVERAGEIFS(average_range,criteria_range1,criteria1,[criteria_range2,criteria2 ],...)，返回满足多个条件的所有单元格的平均值(算术平均值). ? ? ? average_range必需。要计算平均值的一个或多个单元格，其中包括数字或包含数字的名称、数组或引用。 criteria_range1,criteria_range2,„:criteria_range1是必需的，随后的criteria_range是可选的。 criteria1,criteria2,...:criteria1是必需的，随后的criteria是可选的。 14. 计数函数COUNT ? COUNT(value1,[value2],...)，COUNT函数计算参数列表中数字的个数。 ? ? value1，必需。要计算其中数字的个数的单元格引用或区域。 value2,...，可选。要计算其中数字的个数的单元格引用或区域，最多可包含255个。 15. 计数函数COUNTA ? 计算参数列表中值的个数，COUNTA(value1,[value2],...) ? ? value1，必需。表示要计数的值的第一个参数。 value2,...可选。表示要计数的值的其他参数，最多可包含255个参数。 16. 条件计数函数COUNTIF ? 计算区域内符合给定条件的单元格的数量,COUNTIF(range,criteria) ? range，必需。要计数的一个或多个单元格，包括数字或包含数字的名称、数组或引用。空值和文本值将被忽略。 ? criteria，必需。定义要进行计数的单元格的数字、表达式、单元格引用或文本字符串。例如，条件可以表示为32、">32"、B4、"apples"或"32"。 17. 多条件计数函数COUNTIFS ? COUNTIFS(criteria_range1,criteria1,[criteria_range2,criteria2]„)，计算区域内符合多个条件的单元格的数量。 ? criteria_range1，必需。在其中计算关联条件的第一个区域。 ? criteria1，必需。条件的形式为数字、表达式、单元格引用或文本，它定义了要计数的单元格范围。例如， 条件可以表示为32、">32"、B4、"apples"或"32"。 ? criteria_range2,criteria2,...，可选。附加的区域及其关联条件。最多允许127个区域/条件对。 每一个附加的区域都必须与参数criteria_range1具有相同的行数和列数。 18. 排位函数RANK.EQ和RANK.AVG ? RANK.EQ(number,ref,[order])和RANK.AVG(number,ref,[order])，返回一个数字在数字列表中 的排位。其大小与列表中的其他值相关。 ? 如果多个值具有相同的排位，使用函数RANK.AVG将返回平均排位;使用函数RANK.EQ则返 回实际排位。 ? ? ? Number，必需。需要找到排位的数字。 Ref，必需。数字列表数组或对数字列表的引用。Ref中的非数值型值将被忽略。 Order，可选。——数字，指明数字排位的方式。 ? ? 如果order为0(零)或省略，MicrosoftExcel对数字的排位是降序的。 如果order不为零，MicrosoftExcel对数字的排位是升序。 19. 文本合并函数CONCATENATE ? 将几个文本项合并为一个文本项，CONCATENATE(text1,[text2],...)。 ? ? Text1，必需。要连接的第一个文本项。 Text2,...，可选。其他文本项，最多为255项。项与项之间必须用逗号隔开。 注释:也可以使用连接符号“&”代替CONCATENATE函数来连接文本项。例如，=A1&B1返回相同的值为=CONCATENATE(A1,B1)。 20. 截取字符串函数MID ? MID返回文本字符串中从指定位置开始的特定数目的字符，该数目由用户指定。 MID(text,start_num,num_chars) ? ? ? Text必需。包含要提取字符的文本字符串。 Start_num必需。文本中要提取的第一个字符的位置。文本中第一个字符的start_num为1，依此类推。 Num_chars必需。指定希望MID从文本中返回字符的个数。 ? 对应练习:套题1的第4问;套题4的第2问。 21. 左侧截取字符串函数LEFT ? LEFT(text,[num_chars])，根据所指定的字符数，返回文本字符串中第一个字符或前几个字符。 ? ? Text必需。包含要提取的字符的文本字符串。 Num_chars可选。指定要由LEFT提取的字符的数量。 ? Num_chars必须大于或等于零，若省略，则假设其值为1。 ? 如果num_chars大于文本长度，则LEFT返回全部文本。 22. 右侧截取字符串函数RIGHT ? RIGHT(text,[num_chars])，根据所指定的字符数，返回文本字符串中右侧一个或多个字符。 ? ? Text必需。包含要提取字符的文本字符串。 Num_chars可选。指定要由RIGHT提取的字符的数量。 23. 删除空格函数TRIM ? TRIM(text)，除了单词之间的单个空格外，清除文本中所有的空格。 ? Text必需。需要删除其中空格的文本。 24. 字符个数函数LEN ? 返回文本字符串中的字符数。LEN(text) ? Text必需。要查找其长度的文本。空格将作为字符进行计数。