函数的基本应用.ppt
EXCEL函数的基本应用-求和(SUMSUM)SUM返回某一单元格区域中所有数字之和。语法语法SUM(number1,number2,.)Number1,number2,.为1到30个需要求和的参数。123456数据-51530,5TRUE A公式公式说明(结果)说明(结果)=SUM(3,2)将3和2相加(5)=SUM(5,15,TRUE)将5、15和1相加,因为文本值被转换为数字,逻辑值TRUE被转换成数字1(21)=SUM(A2:A4)在上列中添加前三个数(40)=SUM(A2:A4,15)在上列中添加前三个数与15(55)=SUM(A5,A6,2)在上面最后两行中添加数值与2。因为引用非数值的值不被转换,故忽略上列中的数值(2)EXCEL函数的基本应用-平均数(平均数(AVERAGEAVERAGE)123456数据1079222公式公式说明(结果)说明(结果)=AVERAGE(A2:A6)上面数字的平均值上面数字的平均值 (11)(11)=AVERAGE(A2:A6,5)上面数字与上面数字与 5 5 的平均值的平均值 (10)(10)AVERAGE返回参数的平均值(算术平均值)。语法语法AVERAGE(number1,number2,.)Number1,number2,.为需要计算平均值的 1 到 30 个参数。说明说明 1、参数可以是数字,或者是包含数字的名称、数组或引用。2、如果数组或引用参数包含文本、逻辑值或空白单元格,则这些值将被忽略;但包含零值的单元格将计算在内。AEXCEL函数的基本应用-最大值(最大值(MAXMAX)MAX返回一组值中的最大值。语法语法MAX(number1,number2,.)Number1,number2,.是要从中找出最大值的是要从中找出最大值的 1 到到 30 个数字参数。个数字参数。123456数据1079272公式公式说明(结果)说明(结果)=MAX(A2:A6)上面一组数字中的最大值上面一组数字中的最大值(27)=MAX(A2:A6,30)上面一组数字和上面一组数字和 30 之中的最大值之中的最大值(30)AMIN返回一组值中的最小值。语法语法MAX(number1,number2,.)Number1,number2,.是要从中找出最小值的是要从中找出最小值的 1 到到 30 个数字参数。个数字参数。123456数据1079272公式公式说明(结果)说明(结果)=MAX(A2:A6)上面一组数字中的最小值上面一组数字中的最小值(2)=MAX(A2:A6,0)上面一组数字和上面一组数字和 30 之中的最小值之中的最小值(0)AEXCEL函数的基本应用-最小值(最小值(MINMIN)EXCEL函数的基本应用-记数(记数(COUNTCOUNT)COUNT返回包含数字以及包含参数列表中的数字的单元格的个数。语法语法COUNT(value1,value2,.)Value1,value2,.为包含或引用各种类型数据的参数(1 到 30个),但只有数字类型的数据才被计算。说明说明 函数 COUNT 在计数时,将把数字、日期、或以文本代表的数字计算在内;但是错误值或其他无法转换成数字的文字将被忽略。如果参数是一个数组或引用,那么只统计数组或引用中的数字;数组或引用中的空白单元格、逻辑值、文字或错误值都将被忽略。如果要统计逻辑值、文字或错误值,请使用函数 COUNTA。1234567数据销售3-161922.24TURE#DIV/0 A公式公式说明(结果)说明(结果)=COUNT(A2:A8)计算上列数据中包含数字的单元格的个数(3)=COUNT(A5:A8)计算上列数据的最后 4 行中包含数字的单元格的个数(2)=COUNT(A2:A8,2)计算上列数据中包含数字的单元格以及包含数值 2 的单元格的个数(4)EXCEL函数的基本应用-条件函数(条件函数(IFIF)1 1IF执行真假值判断,根据逻辑计算的真假值,返回不同结果。可以使用函数 IF 对数值和公式进行条件检测。语法语法IF(logical_test,value_if_true,value_if_false)Logical_test 表示计算结果为 TRUE 或 FALSE 的任意值或表达式。例如,A10=100 就是一个逻辑表达式,如果单元格 A10 中的值等于 100,表达式即为 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 返回相应语句执行后的返回值。如果函数 IF 的参数包含数组,则在执行 IF 语句时,数组中的每一个元素都将计算。Microsoft Excel 还提供了其他一些函数,可依据条件来分析数据。例如,如果要计算单元格区域中某个文本字符串或数字出现的次数,则可使用 COUNTIF 工作表函数。如果要根据单元格区域中的某一文本字符串或数字求和,则可使用 SUMIF 工作表函数。请了解关于根据条件计算值。EXCEL函数的基本应用-条件函数(条件函数(IFIF)2 212数据-5 A公式公式说明(结果)说明(结果)=IF(A2B2,OverBudget,OK)判断第1行是否超出预算(OverBudget)=IF(A3B3,OverBudget,OK)判断第2行是否超出预算(OK)EXCEL函数的基本应用-条件函数(条件函数(IFIF)3 3在上例中,第二个IF语句同时也是第一个IF语句的参数value_if_false。同样,第三个IF语句是第二个IF语句的参数value_if_false。例如,如果第一个logical_test(Average89)为TRUE,则返回“A”;如果第一个logical_test为FALSE,则计算第二个IF语句,以此类推。如果成绩是如果成绩是则返回则返回大于89A80到89B70到79C60到69D小于60F公式公式说明(结果)说明(结果)=IF(A289,A,IF(A279,B,IF(A269,C,IF(A259,D,F)为第一个成绩指定一个字母等级(F)=IF(A389,A,IF(A379,B,IF(A369,C,IF(A359,D,F)为第二个成绩指定一个字母等级(A)=IF(A489,A,IF(A479,B,IF(A469,C,IF(A459,D,F)为第三个成绩指定一个字母等级(C)123成绩459078 AEXCEL函数的基本应用-条件求和(条件求和(SUMIFSUMIF)SUMIF根据指定条件对若干单元格求和根据指定条件对若干单元格求和。语法语法SUMIF(range,criteria,sum_range)Range为用于条件判断的单元格区域。Criteria为确定哪些单元格将被相加求和的条件,其形式可以为数字、表达式或文本。例如,条件可以表示为32、32、32或apples。Sum_range是需要求和的实际单元格。说明说明只有在区域中相应的单元格符合条件的情况下,sum_range中的单元格才求和。如果忽略了sum_range,则对区域中的单元格求和。12345AB属性值属性值佣金佣金100,0007,000200,00014,000300,00021,000400,00028,000公式公式说明(结果)说明(结果)=SUMIF(A2:A5,160000,B2:B5)属性值超过160,000的佣金的和(63,000)2345AB属性值属性值佣金佣金100,0007,000200,00014,000300,00021,000400,00028,000公式公式说明(结果)说明(结果)=SUMIF(A2:A5,160000,B2:B5)属性值超过160,000的佣金的和(63,000)EXCEL函数的基本应用-地址引用(地址引用(ADDRESSADDRESS)ADDRESS按照给定的行号和列标,建立文本类型的单元格地址。语法语法ADDRESS(row_num,column_num,abs_num,)Row_num 在单元格引用中使用的行号。Column_num 在单元格引用中使用的列标。Abs_num 指定返回的引用类型。(1 或省略绝对引用2绝对行号,相对列标3相对行号,绝对列标4相对引用)123456AB公式公式说明(结果)说明(结果)=ADDRESS(2,3)绝对引用($C$2)=ADDRESS(2,3,2)绝对行号,相对列标(C$2)EXCEL函数的基本应用-查找(LOOKUP)LOOKUP向量为只包含一行或一列的区域。函数 LOOKUP 的向量形式是在单行区域或单列区域(向量)中查找数值,然后返回第二个单行区域或单列区域中相同位置的数值。如果需要指定包含待查找数值的区域,则可以使用函数 LOOKUP 的这种形式。函数 LOOKUP 的另一种形式为自动在第一列或第一行中查找数值。语法语法 1 向量形式LOOKUP(lookup_value,lookup_vector,result_vector)Lookup_value 为函数 LOOKUP 在第一个向量中所要查找的数值。Lookup_value 可以为数字、文本、逻辑值或包含数值的名称或引用。Lookup_vector 为只包含一行或一列的区域。Lookup_vector 的数值可以为文本、数字或逻辑值。要点要点 Lookup_vector 的数值必须按升序排序:.、-2、-1、0、1、2、.、A-Z、FALSE、TRUE;否则,函数 LOOKUP 不能返回正确的结果。文本不区分大小写。Result_vector 只包含一行或一列的区域,其大小必须与 lookup_vector 相同。说明说明 如果函数 LOOKUP 找不到 lookup_value,则查找 lookup_vector 中小于或等于 lookup_value 的最大数值。如果 lookup_value 小于 lookup_vector 中的最小值,函数 LOOKUP 返回错误值#N/A。EXCEL函数的基本应用-查找(查找(LOOKUP)123456AB频率频率颜色颜色4.14red4.91orange5.17yellow5.77green6.39blue公式公式说明(结果)说明(结果)=LOOKUP(4.91,A2:A6,B2:B6)在A列中查找4.91,并返回同一行B列的值(orange)=LOOKUP(5.00,A2:A6,B2:B6)在A列中查找5.00,并返回同一行B列的值(orange)=LOOKUP(7.66,A2:A6,B2:B6)在A列中查找7.66(最接近的下一个值为6.39),并返回同一行B列的值(blue)=LOOKUP(0,A2:A6,B2:B6)在A列中查找0,由于0小于查找向量A2:A7中的最小值,所以返回错误值(#N/A)EXCEL函数的基本应用-首行查找(HLOOKUP)HLOOKUP在表格或数值数组的首行查找指定的数值,并由此返回表格或数组当前列中指定行处的数值。当比较值位于数据表的首行,并且要查找下面给定行中的数据时,请使用函数 HLOOKUP。当比较值位于要查找的数据左边的一列时,请使用函数 VLOOKUP。语法语法HLOOKUP(lookup_value,table_array,row_index_num,range_lookup)Lookup_value 为需要在数据表第一行中进行查找的数值。Lookup_value 可以为数值、引用或文本字符串。Table_array 为需要在其中查找数据的数据表。可以使用对区域或区域名称的引用。Table_array 的第一行的数值可以为文本、数字或逻辑值。如果 range_lookup 为 TRUE,则 table_array 的第一行的数值必须按升序排列:.-2、-1、0、1、2、A-Z、FALSE、TRUE;否则,函数 HLOOKUP 将不能给出正确的数值。如果 range_lookup 为 FALSE,则 table_array 不必进行排序。文本不区分大小写。可以用下面的方法实现数值从左到右的升序排列:选定数值,在“数据数据”菜单中单击“排序排序”,再单击“选选项项”,然后单击“按行排序按行排序”选项,最后单击“确定确定”。在“排序依据排序依据”下拉列表框中,选择相应的行选项,然后单击“升序升序”选项。Row_index_num 为 table_array 中待返回的匹配值的行序号。Row_index_num 为 1 时,返回 table_array 第一行的数值,row_index_num 为 2 时,返回 table_array 第二行的数值,以此类推。如果 row_index_num 小于 1,函数 HLOOKUP 返回错误值#VALUE!;如果 row_index_num 大于 table-array 的行数,函数 HLOOKUP 返回错误值#REF!。Range_lookup 为一逻辑值,指明函数 HLOOKUP 查找时是精确匹配,还是近似匹配。如果为 TRUE 或省略,则返回近似匹配值。也就是说,如果找不到精确匹配值,则返回小于 lookup_value 的最大数值EXCEL函数的基本应用-首行查找(首行查找(HLOOKUP)1234ABCAxlesBearingsBolts44957106811公式公式说明(结果)说明(结果)=HLOOKUP(Axles,A1:C4,2,TRUE)在首行查找Axles,并返回同列中第2行的值。(4)=HLOOKUP(Bearings,A1:C4,3,FALSE)在首行查找Bearings,并返回同列中第3行的值。(7)=HLOOKUP(B,A1:C4,3,TRUE)在首行查找B,并返回同列中第3行的值。由于B不是精确匹配,因此将使用小于B的最大值Axles。(5)=HLOOKUP(Bolts,A1:C4,4)在首行查找Bolts,并返回同列中第4行的值。(11)=HLOOKUP(3,1,2,3;a,b,c;d,e,f,2,TRUE)在数组常量的第一行中查找3,并返回同列中第2行的值。(c)EXCEL函数的基本应用-垂直查找垂直查找(VLOOKUP)(VLOOKUP)VLOOKUP在表格或数值数组的首列查找指定的数值,并由此返回表格或数组当前行中指定列处的数值。当比较值位于数据表首列时,可以使用函数 VLOOKUP 代替函数 HLOOKUP。在 VLOOKUP 中的 V 代表垂直。语法语法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。EXCEL函数的基本应用12345678910ABC密度密度粘度粘度温度温度.4573.55500.5253.25400.6162.93300.6752.75250.7462.57200.8352.38150.9462.171001.091.95501.291.710公式公式说明(结果)说明(结果)=VLOOKUP(1,A2:C10,2)在A列中查找1,并从相同行的B列中返回值(2.17)=VLOOKUP(1,A2:C10,3,TRUE)在A列中查找1,并从相同行的C列中返回值(100)=VLOOKUP(.7,A2:C10,3,FALSE)在A列中查找0.746。因为A列中没有精确地匹配,所以返回了一个错误值(#N/A)=VLOOKUP(0.1,A2:C10,2,TRUE)在A列中查找0.1。因为0.1小于A列的最小值,所以返回了一个错误值(#N/A)=VLOOKUP(2,A2:C10,2,TRUE)在A列中查找2,并从相同行的B列中返回值(1.71)