Excel函数应用

你的成功，是我们最大的收获!柯怩卡美能达商用科技(东莞)有限公司何公超编著2011-8-252第一章：函数简介第二章：逻辑函数第三章：数学和三角函数第四章：文本/日期/时间函数第五章：信息函数第六章：查询与引用函数第七章：统计函数附录：常见的公式错误代码Excel函数应用讲课提纲讲课内容3第一章：函数简介什么是公式：公式是对工作表的数值进行计算或操作的等式，以“=”开始，可以包括运算符、单元格引用、值或常量及及工作表函数。公式的基本元素包括以下几种元素：运算符：运算符对公式的元素进行特定类型的计算，一个运算符就是一个符号，例如“*”、“+”等；单元格引用：用于表示单元格在工作表所处位置的坐标值。例如，显示在第B列和第3行交叉处的单元格，其引用形式为B3“值或常量：直接键入到单元格或公式中的数字或文本值，或由名称所代表的数字或文本值。例如，日期10/9/96、数字210和文本QuarterlyEarnings都是常量。公式或由公式得出的数值都不是常量。工作表函数：包括一些函数或参数，可以返回一定函数值公式及其基本元素4第一章：函数简介运算符及其优先级优先级符号运算符1^幂运算2*乘号2/除号3+加号3-减号4&连接符号5=等于符号5小于符号5大于符号像其它运算一样，在EXCEL中括号(包括各类括号)可以覆盖其内置优先顺序。括号中的表达式，将享有最高优先级从而被最先计算。5第一章：函数简介认识函数什么是函数？Excel中所提的函数其实是一些预定义的公式，它们使用一些称为参数的特定数值按特定的顺序或结构进行计算。用户可以直接用它们对某个区域内的数值进行一系列运算，如分析和处理日期值和时间值、确定贷款的支付额、确定单元格中的数据类型、计算平均值、排序显示和运算文本数据等等。例如，SUM函数对单元格或单元格区域进行加法运算6第一章：函数简介什么是参数？参数可以是哪些数据类型？参数可以是数字、文本、形如TRUE或FALSE的逻辑值、数组、形如#N/A的错误值或单元格引用。(注：当参数为文本时需用英文状态下的双引用)参数也可以是常量、公式或其它函数。还可以是数组、单元格引用等认识参数7第一章：函数简介函数结构图1：函数基本结构图2：嵌套函数示例8第一章：函数简介输入界面或公式选项板--帮助创建或编辑公式的工具，还可提供有关函数及其参数的信息9第一章：函数简介数据引用关于引用需要了解如下几种情况的含义：外部引用--不同工作簿中的单元格的引用称为外部引用。远程引用--引用其它程序中的数据称为远程引用。相对引用--在创建公式时，单元格或单元格区域的引用通常是相对于包含公式的单元格的相对位置。绝对引用--如果在复制公式时不希望Excel调整引用，那么请使用绝对引用。即加入美元符号，如$C$1。10第一章：函数简介相对引用/绝对引用$美元符号在Excel单元格数据区域中,代表的是绝对引用。绝对引用,一般是在函数的参数中使用。在填写其参数的数据区域的时候,可通过F4快捷键来快速自动的输入绝对引用。11第一章：函数简介Excel函数一共有11类，分别是：数据库函数日期与时间函数工程函数财务函数信息函数逻辑函数查询和引用函数数学和三角函数统计函数文本函数以用户自定义函数。函数的种类12第二章：逻辑函数什么是逻辑函数？判断真假值，或者进行复合检验的Excel函数，我们称为逻辑函数。在Excel中提供了六种逻辑函数。即AND、OR、NOT、FALSE、IF、TRUE函数。13第二章：逻辑函数AND、OR、NOT函数语法：AND(logical1,logical2,...)语法：OR(logical1,logical2,...)语法：NOT(logical1,logical2,...)14第二章：逻辑函数IF函数Value_if_falselogical_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也可以是其他公式。Value_if_truelogical_test为TRUE时返回的值。例如，如果本参数为文本字符串“预算内”而且logical_test参数值为TRUE，则IF函数将显示文本“预算内”。如果logical_test为TRUE而value_if_true为空，则本参数返回0（零）。如果要显示TRUE，则请为本参数使用逻辑值TRUE。Value_if_true也可以是其他公式。执行真假值判断，根据逻辑计算的真假值，返回不同结果。可以使用函数IF对数值和公式进行条件检测。语法：IF(logical_test,value_if_true,value_if_false)Logical_test表示计算结果为TRUE或FALSE的任意值或表达式。例如，A10=100就是一个逻辑表达式，如果单元格A10中的值等于100，表达式即为TRUE，否则为FALSE。本参数可使用任何比较运算符。15第二章：逻辑函数实例一：实例二：IF函数实例B10单元公式：IF(B989,优秀,IF(B979,良,IF(B969,中,IF(B959,及格,不及格))))16第二章：逻辑函数求闰年能够被4整除但不能被100整除，或能够被400整除公式1：IF(OR(A2/400=INT(A2/400),AND(A2/4=INT(A2/4),A2/100INT(A2/100))),闰年,平年)公式2：IF(OR(MOD(A2,400)=0,AND(MOD(A2,4)=0,MOD(A2,100)0)),闰年,平年)IF/AND/OR综合应用MOD：返回两数相除的余数。结果的正负号与除数相同。语法MOD(number,divisor)Number为被除数，Divisor为除数17“数学与三角函数”包括正弦函数、余弦函数、取整函数等SUM函数是Excel中使用最多的函数，利用它进行求和运算可以忽略存有文本、空格等数据的单元格，语法简单、使用方便。相信这也是大家最先学会使用的Excel函数之一。但是实际上，Excel所提供的求和函数不仅仅只有SUM一种，还包括SUBTOTAL、SUM、SUMIF、SUMPRODUCT、SUMSQ、SUMX2MY2、SUMX2PY2、SUMXMY2几种函数。第三章：数学和三角函数与求和有关的函数18返回某一单元格区域中所有数字之和。语法：SUM(number1,number2,...)Number1,number2,...为1到30个需要求和的参数。说明：直接键入到参数表中的数字、逻辑值及数字的文本表达式将被计算，如果参数为数组或引用，只有其中的数字将被计算。数组或引用中的空白单元格、逻辑值、文本或错误值将被忽略。请参阅下面的示例三。如果参数为错误值或为不能转换成数字的文本，将会导致错误。示例：SUM函数第三章：数学和三角函数1201015023函数结果说明=SUM(A1:A4)303对A列中的数据求和=SUM(5,A1:A4)308对A列中的数据与5求和19第三章：数学和三角函数SUMIF函数根据指定条件对若干单元格求和。语法：SUMIF(range,criteria,sum_range)Range为用于条件判断的单元格区域。Criteria为确定哪些单元格将被相加求和的条件，其形式可以为数字、表达式或文本。例如，条件可以表示为32、32、32或apples。Sum_range是需要求和的实际单元格。说明：只有在区域中相应的单元格符合条件的情况下，sum_range中的单元格才求和。如果忽略了sum_range，则对区域中的单元格求和。MicrosoftExcel还提供了其他一些函数，它们可根据条件来分析数据。例如，如果要计算单元格区域内某个文本字符串或数字出现的次数，则可使用COUNTIF函数。如果要让公式根据某一条件返回两个数值中的某一值（例如，根据指定销售额返回销售红利），则可使用IF函数。20第三章：数学和三角函数SUM与SUMIF案例21第三章：数学和三角函数利用SUM进行多条件统计注意：数组公式在输入完成后需按组合键Ctrl+Shift+Enter加大括号，而不能手动加入22第三章：数学和三角函数返回列表或数据库中的分类汇总。通常，使用“数据”菜单中的“分类汇总”命令可以容易地创建带有分类汇总的列表。一旦创建了分类汇总，就可以通过编辑SUBTOTAL函数对该列表进行修改。语法:SUBTOTAL(function_num,ref1,ref2,...)说明：SUBTOTAL分类汇总序号Function_num（包含隐藏值）Function_num（忽略隐藏值）函数11AVERAGE101AVERAGE22COUNT102COUNT33COUNTA103COUNTA44MAX104MAX55MIN105MIN66PRODUCT106PRODUCT77STDEV107STDEV88STDEVP108STDEVP99SUM109SUM1010VAR110VAR1111VARP111VARP23第三章：数学和三角函数在给定的几组数组中，将数组间对应的元素相乘，并返回乘积之和。语法SUMPRODUCT(array1,array2,array3,...)Array1,array2,array3,...为2到30个数组，其相应元素需要进行相乘并求和。说明数组参数必须具有相同的维数，否则，函数SUMPRODUCT将返回错误值#VALUE!。•函数SUMPRODUCT将非数值型的数组元素作为0处理。SUMPRODUCT24第三章：数学和三角函数利用SUMPRODUCT跨行求和ROW：返回引用的行号。ROW(reference)COLUMN：返回给定引用的列标。COLUMN(reference)Reference为需要得到其行号的单元格或单元格区域。25第三章：数学和三角函数SUM/SUMIF/SUMPRODUCT/SUBTOTA综合应用SUBTOTAL使用SUM使用SUMIF使用SUMIF使用SUM使用SUMPRODUCT使用26第三章：数学和三角函数ROUND函数返回某个数字按指定位数取整后的数字。语法：ROUND(number,num_digits)Number需要进行四舍五入的数字。Num_digits指定的位数，按此位数进行四舍五入。说明：如果num_digits大于0，则四舍五入到指定的小数位。如果num_digits等于0，则四舍五入到最接近的整数。如果num_digits小于0，则在小数点左侧进行四舍五入。案例：函数结果说明=ROUND(0.3115,0)00.3115四舍五入到整数=ROUND(0.3115,1)0.30.3115四舍五入到1个小数位=ROUND(0.3115,2)0.310.3115四舍五入到2个小数位=ROUND(0.3115,3)0.3120.3115四舍五入到3个小数位27第三章：数学和三角函数INT函数将数字向下舍入到最接近的整数。语法：INT(number)Number需要进行向下舍入取整的实数。示例：28第四章：文本/日期/时间函数1、了解Excel日期与时间1）日期和时间都仅仅是数字而