办公自动化之excel应用
办公自动化之Excel应用,函数简介,Microsoft Excel函数是一些预定义的公式,它们使用一些特定数值作为参数,按照特定的顺序或结构进行计算,用户可以直接用它们对数值进行一系列运算,快速得到计算结果。 函数是可以嵌套的,也就是说一个函数可以作为另外一个函数的参数,比如:=IF(A1B1, SUM(A1:A4), 0)。 函数结构是以函数名称开始,后面是左圆括号,以逗号分隔的参数和右圆括号。如果函数以公式的形式出现,在函数名称前面加等号,比如:=SUM(A1,B1:B5,C1)。也可以通过菜单或工具栏上的公式工具来编辑使用公式。,2,函数应用-逻辑函数,用来判断真假值,或者进行复核检验的Excel函数 AND, OR, NOT 函数:用来返回参数逻辑值 IF 函数:用于执行真假值判断后,根据逻辑测试的真假值返回不同的结果 IFERROR 函数:用于执行真假值判断后,根据公式的计算结果是否错误,返回您指定的值或公式的结果,3,函数应用-逻辑函数,AND 函数:如果其所有参数均为 TRUE,则返回 TRUE 语法 AND(logical1, logical2, .) AND 函数语法具有以下参数: logical1 必需。 要测试的第一个条件,其计算结果可以为 TRUE 或 FALSE。 logical2, . 可选。 要测试的其他条件,其计算结果可以为 TRUE 或 FALSE,最多可包含 255 个条件。,4,函数应用-逻辑函数,说明 参数的计算结果必须是逻辑值(如 TRUE 或 FALSE),或者参数必须是包含逻辑值的数组或引用。 如果数组或引用参数中包含文本或空白单元格,则这些值将被忽略。 如果指定的单元格区域未包含逻辑值,则 AND 函数将返回 #VALUE! 错误值。,5,函数应用-逻辑函数,OR 函数:如果任一参数为 TRUE,则返回 TRUE 语法 OR(logical1, logical2, .) OR 函数语法具有下列参数: Logical1, logical2, . Logical1 是必需的,后续逻辑值是可选的。 1 到 255 个需要进行测试的条件,测试结果可以为 TRUE 或 FALSE。,6,函数应用-逻辑函数,说明 参数必须能计算为逻辑值,如 TRUE 或 FALSE,或者为包含逻辑值的数组或引用。 如果数组或引用参数中包含文本或空白单元格,则这些值将被忽略。 如果指定的区域中不包含逻辑值,则 OR 返回 错误值 #VALUE!。 可以使用 OR 数组公式以查看数组中是否出现了某个值。 若要输入数组公式,请按 Ctrl+Shift+Enter。,7,函数应用-逻辑函数,NOT 函数:对其参数的逻辑求反 语法 NOT(logical) NOT 函数语法具有下列参数: Logical 必需。 计算结果为 TRUE 或 FALSE 的任何值或表达式。,8,函数应用-逻辑函数,说明 如果逻辑值为 FALSE,函数 NOT 返回 TRUE;如果逻辑值为 TRUE,函数 NOT 返回 FALSE。,9,函数应用-逻辑函数,IF 函数:指定要执行的逻辑检测 语法 IF(logical_test, value_if_true, value_if_false) IF 函数语法具有下列参数。 logical_test 必需。 计算结果为 TRUE 或 FALSE 的任何值或表达式。此参数可以使用任何比较计算运算符。 value_if_true 可选。 logical_test 参数的计算结果为 TRUE 时所要返回的值。 value_if_false 可选。 logical_test 参数的计算结果为 FALSE 时所要返回的值。,10,函数应用-逻辑函数,说明 最多可以使用 64 个 IF 函数作为 value_if_true 和 value_if_false 参数进行嵌套以构造更详尽的测试。 (请参阅示例 3,这是嵌套 IF 函数的一个示例。)此外,若要检测多个条件,请考虑使用 LOOKUP、VLOOKUP、HLOOKUP 或 CHOOSE 函数。 (请参阅示例 4,这是 LOOKUP 函数的一个示例。) 如果 IF 的任意参数为数组,则在执行 IF 语句时,将计算数组的每一个元素。 Excel 还提供了其他一些函数,可使用这些函数根据条件来分析数据。 例如,若要计算某单元格区域内某个文本字符串或数字出现的次数,可使用 COUNTIF 或 COUNTIFS 工作表函数。 若要计算基于某区域内一个文本字符串或一个数值的总和,可使用 SUMIF 或 SUMIFS 工作表函数。,11,函数应用-逻辑函数,IFERROR 函数:如果公式的计算结果错误,则返回指定值;否则返回公式的结果 语法 IFERROR(value, value_if_error) IFERROR 函数语法具有下列参数: value 必需。 检查是否存在错误的参数。 value_if_error 必需。 公式的计算结果错误时返回的值。 计算以下错误类型:#N/A、#VALUE!、#REF!、#DIV/0!、#NUM!、 #NAME? 或 #NULL!。,12,函数应用-逻辑函数,说明 如果 Value 或 Value_if_error 是空单元格,则 IFERROR 将其视为空字符串值 (“)。 如果 Value 是数组公式,则 IFERROR 为 value 中指定区域的每个单元格返回一个结果数组。 请参阅下面的第二个示例。,13,函数应用-查找和引用函数,当需要在数据清单或表格中查找特定数值,或者需要查找某一单元格的引用时,可以使用查询和引用工作表函数。 COLUMN, ROW 函数:用于返回给定引用的列标和行标 COLUMNS, ROWS 函数:用于返回数组或引用的列数和行数 VLOOKUP, HLOOKUP 函数:用于在表格或数值数组的首列/首行查找指定的数值,并由此返回表格或数组当前列/行中指定列/行的数值,14,函数应用-查找和引用函数,COLUMN 函数:返回引用的列号 语法 COLUMN(reference) COLUMN 函数语法具有以下参数: 引用 可选。 要返回其列号的单元格或单元格区域。 如果省略参数 reference 或该参数为一个单元格区域,并且 COLUMN 函数是以水平数组公式的形式输入的,则 COLUMN 函数将以水平数组的形式返回参数 reference 的列号。,15,函数应用-查找和引用函数,说明 将公式作为数组公式输入 从公式单元格开始,选择要包含数组公式的区域。 按 F2,再按 Ctrl+Shift+Enter。 注释 在 Excel Web App 中,不能创建数组公式。 如果参数 reference 为一个单元格区域,并且 COLUMN 函数不是以水平数组公式的形式输入的,则 COLUMN 函数将返回最左侧列的列号。 如果省略参数 reference,则假定该参数为对 COLUMN 函数所在单元格的引用。 参数 reference 不能引用多个区域。,16,函数应用-查找和引用函数,ROW 函数:返回引用的行号 语法 ROW(reference) ROW 函数语法具有下列参数: Reference 可选。 需要得到其行号的单元格或单元格区域。,17,函数应用-查找和引用函数,说明 如果省略 reference,则假定是对函数 ROW 所在单元格的引用。 如果 reference 为一个单元格区域,并且 ROW 作为垂直数组输入,则 ROW 将以垂直数组的形式返回 reference 的行号。 Reference 不能引用多个区域。,18,函数应用-查找和引用函数,COLUMNS 函数:返回引用中包含的列数 语法 COLUMNS(array) COLUMNS 函数语法具有以下参数: Array 必需。 要计算列数的数组、数组公式或是对单元格区域的引用。,19,函数应用-查找和引用函数,ROWS 函数 :返回引用中的行数 语法 ROWS(array) ROWS 函数语法具有以下参数: Array 必需。 需要得到其行数的数组、数组公式或对单元格区域的引用。,20,函数应用-查找和引用函数,VLOOKUP 函数:在数组第一列中查找,然后在行之间移动以返回单元格的值 语法 VLOOKUP(lookup_value, table_array, col_index_num, range_lookup) VLOOKUP 函数语法具有下列参数: lookup_value 必需。 要在表格或区域的第一列中搜索的值。 lookup_value 参数可以是值或引用。 如果为 lookup_value 参数提供的值小于 table_array 参数第一列中的最小值,则 VLOOKUP 将返回错误值 #N/A。,21,函数应用-查找和引用函数,table_array 必需。 包含数据的单元格区域。 可以使用对区域(例如,A2:D8)或区域名称的引用。 table_array 第一列中的值是由 lookup_value 搜索的值。 这些值可以是文本、数字或逻辑值。 文本不区分大小写。 col_index_num 必需。 table_array 参数中必须返回的匹配值的列号。 col_index_num 参数为 1 时,返回 table_array 第一列中的值;col_index_num 为 2 时,返回 table_array 第二列中的值,依此类推。 如果 col_index_num 参数: 小于 1,则 VLOOKUP 返回 错误值 #REF!。 大于 table_array 的列数,则 VLOOKUP 返回。 错误值 #REF!。,22,函数应用-查找和引用函数,range_lookup 可选。 一个逻辑值,指定希望 VLOOKUP 查找精确匹配值还是近似匹配值: 如果 range_lookup 为 TRUE 或被省略,则返回精确匹配值或近似匹配值。 如果找不到精确匹配值,则返回小于 lookup_value 的最大值。 要点 如果 range_lookup 为 TRUE 或被省略,则必须按升序排列 table_array 第一列中的值;否则,VLOOKUP 可能无法返回正确的值。 如果 range_lookup 为 FALSE,则不需要对 table_array 第一列中的值进行排序。 如果 range_lookup 参数为 FALSE,VLOOKUP 将只查找精确匹配值。 如果 table_array 的第一列中有两个或更多值与 lookup_value 匹配,则使用第一个找到的值。 如果找不到精确匹配值,则返回错误值 #N/A。,23,函数应用-查找和引用函数,说明 在 table_array 第一列中搜索文本值时,请确保 table_array 第一列中的数据没有前导空格、尾部空格、直引号( 或 “)与弯引号(或“)不一致或非打印字符。 否则,VLOOKUP 可能返回不正确或意外的值。 有关详细信息,请参阅 CLEAN 函数和 TRIM 函数。 在搜索数字或日期值时,请确保 table_array 第一列中的数据未存储为文本值。 否则,VLOOKUP 可能返回不正确或意外的值。 如果 range_lookup 为 FALSE 且 lookup_value 为文本,则可以在 lookup_value 中使用通配符 (问号 (?) 和星号 (*)) 。 问号匹配任意单个字符;星号匹配任意一串字符。 如果要查找实际的问号或星号,请在该字符前键入波形符 ()。,24,函数应用-查找和引用函数,HLOOKUP 函数:查找数组的首行,并返回指定单元格的值 语法 HLOOKUP(lookup_value, table_array, row_index_num, range_lookup) HLOOKUP 函数语法具有下列参数