第4章 公式与函数应用

第4章公式与函数的应用

公式录入与编辑函数基础知识Excel函数的种类1234常用Excel函数介绍4.1公式录入与编辑公式的概念A公式中的运算符B公式的录入与编辑C数组公式D公式审核E4.1.1公式的的概念Excel中的公式就是由用户自行设计的对工作表中的单元格数据进行计算和处理的各种计算式。例如：=5+2*A1即为Excel的合法公式。在Excel中，用户编写的公式必须遵循特定的语法要求。特别注意：必须以等号=开头Excel公式允许使用的参数包括常量、单元格引用、单元格区域引用、单元格名称或Excel函数等4.1.2公式中的运算符公式中的运算符运算符的优先级公式中的数据类型转换算术运算符：＋、－、*、／、%、、（）字符运算符：＆关系运算符：=、>、>=、<=、<、<>引用运算符：冒号：、逗号，、空格Excel中所使用的运算符主要包括四种：算术运算符、比较运算符、文本运算符和引用运算符。算术运算符含义示例+（加号）加法3+3–（减号）减法

负数3–1

–1*（星号）乘法3*3/（正斜杠）除法3/3%（百分号）百分比20%^（脱字号）乘方3^2算术运算符若要进行基本的数学运算（如加法、减法、乘法或除法）、合并数字以及生成数值结果，请使用以下算术运算符。比较运算符含义示例=（等号）等于A1=B1>（大于号）大于A1>B1<（小于号）小于A1<B1>=（大于等于号）大于或等于A1>=B1<=（小于等于号）小于或等于A1<=B1<>（不等号）不等于A1<>B1比较运算符可以使用下列运算符比较两个值。当使用这些运算符比较两个值时，结果为逻辑值TRUE或FALSE。文本运算符含义示例&（与号）将两个值连接（或串联）起来产生一个连续的文本值"North"&"wind"的结果为"Northwind"文本连接运算符

可以使用与号(&)连接（联接）一个或多个文本字符串，以生成一段文本。引用运算符含义示例::（冒号）区域运算符，生成一个对两个引用之间所有单元格的引用（包括这两个引用）。B5:B15,（逗号）联合运算符，将多个引用合并为一个引用SUM(B5:B15,D5:D15)（空格）交集运算符，生成一个对两个引用中共有单元格的引用B7:D7C6:C8引用运算符

可以使用以下运算符对单元格区域进行合并计算。Excel公式可同时包含多个运算符，这就需要按照一定的优先次序进行计算，同优先级的运算符从左至右进行计算。运算符从高到低的优先次序如下：运算符优先级运算符说明：冒号，引用单元格区域

空格，将两个单元格区域相交，逗号，合并多个单元格引用-负号%百分比^求幂*和/乘法和除法+和-加法和减法&联接两个或多个字符串>,<,=,>=,<=,<>比较运算符一般来讲，公式中参与运算的数据类型应该一致，每类运算符有特定类型的参数与之对应。如果输入的参数类型与要求不符，Excel将进行自身允许的类型转换，以完成运算，否则显示错误值。常见的数据类型转换如下：公式公式结果说明=“1”+23将字符串”1”转换成数字做加法="1"&212将数字2转换成字符串”2”做联接运算=“2014/2/23”-“2014/2/13”10将双引号内的数据看作日期，将日期转换成序列号后，再进行计算=TRUE+12TRUE转成数字1，FALSE转成数字0=TRUE&”AA”TRUEAA将逻辑值转成文本做联接运算="12">9TRUE将字符1的ASCII码与数字9做比较4.1.3公式的录入与编辑公式的录入公式的编辑在Excel中输入公式必须先输入等号“=”，这样Excel才知道我们输入的是公式，而不是一般的字符串。步骤如下：步骤1：选定要输入公式的E2单元格，将指针移到编辑栏中输入等号”=”步骤2：接着输入“=”之后的计算式，其中的单元格地址可以从键盘直接输入，也可以鼠标单击选取，运算符则必须用键盘输入步骤3：按下编辑栏左侧的确认按钮或按下键盘上的回车键公式的编辑（1）公式复制技巧拖动复制：输入复制：【Ctrl】+【Enter】选择性粘贴（2）公式与结果切换【Ctrl】+【`】键(位于键盘左上角，与“~”为同一键)数组的基本概念数组公式的输入4.1.4数组公式可以将一维数组简单地看成是一行（水平）或一列（垂直）单元格数据的集合。水平一维数组的各个元素间用英文的逗号“,”隔开，垂直一维数组的各个元素间用英文分号“;”隔开，例如:”={10，20，30，40，50}”，”={100；200；300；400}”。二维数组可以看成是一个多行多列的单元格数据集合，也可以看成是多个水平一维数组的组合。二维数组里同行的元素之间用逗号“,”分隔，不同的行的元素用分号“;”分隔，例如{10,20,30,40;11,21,31,41;12,22,32,42}。要输入数组公式，首先必须选择用来存放结果的单元格区域，在编辑栏输入公式，然后按组合键“Ctrl＋Shift＋Enter”锁定数组公式，Excel将在公式两边自动加上花括号“{}”，注意该括号是数组运算时自动产生的，而不是从键盘上直接输入该括号，否则，Excel会认为输入的是一个正文标签4.1.5公式的审核使用公式或函数时，由于参数错误或数据类型不匹配等原因，公式产生的结果可能是一些错误代码。Excel可以通过内置的规则检查出错误的出处，以指导用户根据这些错误提示进行相应的公式修改。公式审核的操作步骤如下：步骤1：在Excel2010工作界面上单击“公式”选项卡步骤2：单击“公式审核”组中的“错误检查”按钮，弹出“错误检查”对话框，如图4-7所示。在该对话框中指明了出错的单元格位置，错误的类型及相应说明。步骤3：单击对话框中的“继续”按钮可以使对话框中的灰色按钮呈高亮显示，以执行相应的按钮操作4.2函数基础知识函数的概念A函数结构B函数嵌套C函数的输入与编辑D4.2.1函数的概念Excel中所提的函数其实是一些预定义的公式，它们使用一些称为参数的特定数值按特定的顺序或结构进行计算。函数与公式既有区别又相互联系。以公式“=AVERAGE(E1:H1)*A2-1”为例，它以等号“=”开始，其内部可以包括函数、单元格引用、运算符和常量。上式中的“AVERAGE(E1:H1)”是函数，“E1:H1”是函数的参数，“A2”则是对单元格A2的引用，“15”是常量，“*”和“-”则是算术运算符。如果函数要以公式的形式出现，它必须有两个组成部分，一个是函数名称前面的等号，另一个则是函数本身4.2.2函数结构函数的结构以函数名称开始，后面是左圆括号、以逗号分隔的参数和右圆括号。函数右边括号中的部分称为参数，假如一个函数使用多个参数，那么参数与参数之间使用半角逗号进行分隔。Excel中的函数可分为有参函数和无参函数两种。参数是函数中最复杂的组成部分，它规定了函数的运算对象、顺序或结构等。参数类型包括：（1）常量常量是直接输入到单元格或公式中的数字或文本，例如：2890.56、2003-8-19、“中国”都是常量。但是公式或函数计算出的结果都不是常量，因为只要公式或函数的参数发生了变化，计算出来的结果也会发生变化。（2）逻辑值逻辑值是比较特殊的一类参数，它只有TRUE(真)或FALSE(假)两种类型。（3）数组数组用于可产生多个结果的公式，或对存放在行和列中的一组参数进行计算的公式。（4）错误值使用错误值作为参数的主要是信息函数。（5）单元格引用单元格引用是函数中最常见的参数，引用的目的在于标识工作表单元格或单元格区域（6）名称和标志为了更加直观地标识单元格或单元格区域，可以给单元格或单元格区域赋予一个名称，从而在公式或函数中直接引用。4.2.3函数嵌套所谓函数嵌套，就是指在某些情况下，可能需要将某函数作为另一函数的参数使用。4.2.4函数的输入与编辑1、函数输入（1）手工输入函数：需先在输入框中输入一个等号“=”，然后输入函数本身（2）插入函数：使用插入函数对话框是经常用到的输入方法。在该对话框中了提供有关函数及其参数的信息，利用对话框可以避免在输入过程中产生键入错误。2、函数编辑（1）移动函数方法1：将鼠标指针移到单元格四周的黑色边框上，当指针变为四向箭头时，按住鼠标左键拖动到目的单元格，松开，即可实现移动操作。方法2：选定源单元格按下组合键Ctrl+X，执行剪切操作，然后单击目的单元格，按下组合键Ctrl+V，执行粘贴操作，即可实现公式的移动。（2）复制函数复制函数的操作与公式复制操作一致，也可使用组合键。4.3Excel函数的种类Excel提供了许多的函数供用户使用，根据函数的功能可以将函数分为数据库函数、日期与时间函数、工程函数、财务函数、信息函数、逻辑函数、查询和引用函数、数学和三角函数、统计函数、文本函数以及用户自定义函数等。4.4常用Excel函数介绍日期及时间函数逻辑函数文本函数数学与统计函数ABCD查找函数E函数名功能用途DATE给出指定数值的日期。显示日期DAY计算参数中指定日期或引用单元格中的日期天数计算天数TODAY给出系统日期显示日期NOW给出当前系统日期和时间。显示日期时间MONTH求出指定日期或引用单元格中的日期的月份。

日期计算WEEKDAY给出指定日期的对应的星期数。星期计算YEAR求出指定日期或引用单元格中的日期的年份。年份计算一、日期及时间函数（1）DATE函数DATE函数的功能是将给定的年、月、日合成日期。DATE函数的表达式为DATE(year,month,day)。其中参数year表示年份,month表示月份,day代表天。应用举例：在C20单元格中输入公式：=DATE(2003,13,35)，确认后，显示出2004-2-4。特别提醒：由于上述公式中，月份为13，多了一个月，顺延至2004年1月；天数为35，比2004年1月的实际天数又多了4天，故又顺延至2004年2月4日。（2）DAY函数DAY函数的功能是返回日期中的天数。DAY函数的表达式为DAY(serial_number)

。其中参数serial_number代表指定的日期或引用的单元格。应用举例：输入公式：=DAY("2003-12-18")，确认后，显示出18。特别提醒：如果是给定的日期，请包含在英文双引号中。（3）MONTH函数MONTH函数的功能是返回日期中的月份。函数的表达式为MONTH(serial_number)

。其中参数serial_number代表指定的日期或引用的单元格。应用举例：输入公式：=month("2003-12-18")，确认后，显示出12。特别提醒：如果是给定的日期，请包含在英文双引号中。（4）YEAR函数YEAR函数的功能是返回日期中的年份。YEAR函数的表达式为YEAR(serial_number)

。其中参数serial_number代表指定的日期或引用的单元格。应用举例：输入公式：=YEAR("2003-12-18")，确认后，显示出2003。特别提醒：如果是给定的日期，请包含在英文双引号中。（5）TODAY函数TODAY函数的功能是返回系统日期。TODAY函数的表达式为TODAY()

。应用举例：输入公式：=TODAY()，确认后即刻显示出系统日期。如果系统日期发生了改变，只要按一下F9功能键，即可让其随之改变。

（6）NOW函数NOW函数的功能是返回系统日期和时间。NOW函数的表达式为NOW()

。（7）WEEKDAY函数weekday函数的功能是返回指定日期对应的星期数

。函数的表达式为weekday(serial_number,return_type)

。其中参数serial_number代表指定的日期或引用的单元格。return_type代表星期的表示方式[该参数为1时，Sunday（星期日）为1、Saturday（星期六）为7；该参数为2时，Monday（星期一）为1、Sunday（星期日）为7（这种情况符合中国人的习惯）；该参数为3时，Monday（星期一）为0、Sunday（星期日）为6]。应用举例：输入公式：=weekday(“2012-9-10”,2)，确认后，显示出1，表示该日期为星期一。二、逻辑函数函数名

功能

用途AND“与”运算，返回逻辑值，仅当有参数的结果均为逻辑“真（TRUE）”时返回逻辑“真（TRUE）”，反之返回逻辑“假（FALSE）”。逻辑判断OR“或”运算，返回逻辑值，仅当所有参数值均为逻辑“假（FALSE）”时返回结果逻辑“假（FALSE）”，否则都返回逻辑“真（TRUE）”。逻辑判断NOT返回出一个逻辑值或逻辑表达式的相反值。逻辑判断IF根据对指定条件的逻辑判断的真假结果，返回相对应条件触发的计算结果。条件计算（1）AND函数使用格式：AND(logical1,logical2,...)参数说明：其中Logical1,logical2,...表示待检测的1到30个条件值，各条件值可能为TRUE，可能为FALSE。参数必须是逻辑值，或者包含逻辑值的数组或引用。功能：所有参数的逻辑值为真时返回TRUE；只要一个参数的逻辑值为假即返回FALSE。应用举例：在C5单元格输入公式：=AND(A5>=60,B5>=60)，确认。如果C5中返回TRUE，说明A5和B5中的数值均大于等于60，如果返回FALSE，说明A5和B5中的数值至少有一个小于60。（2）OR函数

OR函数使用格式与AND函数相同。但在其参数中，任何一个参数逻辑值为TRUE，即返回TRUE。它与AND函数的区别在于，AND函数要求所有函数逻辑值均为真，结果方为真。而OR函数仅需其中任何一个为真即可为真。（3）NOT函数

NOT函数用于对参数值求反。简言之，就是当参数值为TRUE时，NOT函数返回的结果恰与之相反，结果为FALSE。比如NOT(2+2=4)，由于2+2的结果的确为4，该参数结果为TRUE，由于是NOT函数，因此返回函数结果与之相反，为FALSE。（4）IF函数使用格式：IF(logical_test,value_if_true,value_if_false)参数说明：Logical_test表示计算结果为TRUE或FALSE的任意值或表达式。本参数可使用任何比较运算符。Value_if_true是在logical_test为TRUE时返回的值，Value_if_true也可以是其他公式。Value_if_false是在logical_test为FALSE时返回的值，Value_if_false也可以是其他公式。

函数功能：如果参数logical_test返回的结果为真的话，则执行参数Value_if_true的结果，否则执行参数Value_if_false的结果。IF函数可以嵌套七层，用value_if_false及value_if_true参数可以构造复杂的检测条件。

应用举例：在C29单元格中输入公式：=IF(C26>=18,“符合要求”,“不符合要求”)，回车以后，如果C26单元格中的数值大于或等于18，则C29单元格显示“符合要求”字样，反之显示“不符合要求”字样。三、文本函数函数名

功能

用途LOWER将一个文字串中的所有大写字母转换为小写字母。文本转换UPPER将小写转换成大写形式。文本转换LEFT从一个文本字符串的第一个字符开始，截取指定数目的字符。字符截取RIGHT从一个文本字符串的最后一个字符开始，截取指定数目的字符。字符截取MID从一个文本字符串的指定位置开始，截取指定数目的字符。字符截取LEN统计文本字符串中字符数目。字符统计TEXT根据指定的数值格式将相应的数字转换为文本形式数值文本转换VALUE将一个代表数值的文本型字符串转换为数值型。文本数值转换（1）LOWER函数主要功能：将一个文字串中的所有大写字母转换为小写字母。使用格式：LOWER(text)。参数说明：Text是包含待转换字母的文字串。应用举例：如果A1=Excel，则公式“=LOWER(A1)”返回excel。（2）UPPER函数使用格式与lower函数相同，功能相反。例如：如果A1=Excel，则公式“=UPPER(A1)”返回EXCEL。（3）LEFT函数主要功能：从一个文本字符串的第一个字符开始，截取指定数目的字符。使用格式：LEFT(text,num_chars)参数说明：text代表要截字符的字符串；num_chars代表给定的截取数目。应用举例：假定A38单元格中保存了“我喜欢上网”的字符串，我们在C38单元格中输入公式：=LEFT(A38,3)，确认后即显示出“我喜欢”的字符。（4）RIGHT函数主要功能：从一个文本字符串的最后一个字符开始，截取指定数目的字符。使用格式：RIGHT(text,num_chars)参数说明：text代表要截字符的字符串；num_chars代表给定的截取数目。应用举例：假定A6单元格中保存了“我喜欢上网”的字符串，我们在C65单元格中输入公式：=RIGHT(A6,2)，确认后即显示出“上网”的字符。特别提醒：num_chars参数必须大于或等于0，如果忽略，则默认其为1；如果num_chars参数大于文本长度，则函数返回整个文本。（5）MID函数主要功能：从一个文本字符串的指定位置开始，截取指定数目的字符。使用格式：MID(text,start_num,num_chars)参数说明：text代表一个文本字符串；start_num表示指定的起始位置；num_chars表示要截取的数目。应用举例：假定A4单元格中保存了“我喜欢上网”的字符串，我们在C47单元格中输入公式：=MID(A4,4,2)，确认后即显示出“上网”的字符。（6）LEN函数主要功能：返回文本字符串中的字符数。使用格式：LEN(text)参数说明：text是要计算长度的字符串，空格将作为字符进行计数。应用举例：在B5单元格中输入公式：=len("Thisisanapple")，确认后显示结果为16，表示括号中的字符串长度为16。（7）TEXT函数主要功能：根据指定的数值格式将相应的数字转换为文本形式。使用格式：TEXT(value,format_text)参数说明：value代表需要转换的数值或引用的单元格；format_text为指定文字形式的数字格式。应用举例：如果B6单元格中保存有数值1280.45，我们在C6单元格中输入公式：=TEXT(B6,"$0.00")，确认后显示为“$1280.45”。特别提醒：format_text参数可以根据“单元格格式”对话框“数字”标签中的类型进行确定。（8）VALUE函数主要功能：将一个代表数值的文本型字符串转换为数值型。使用格式：VALUE(text)参数说明：text代表需要转换文本型字符串数值。应用举例：如果B7单元格中是通过LEFT等函数截取的文本型数字串，我们在C7单元格中输入公式：=VALUE(B7)，确认后，即可将其转换为数值型。特别提醒：如果文本型数值不经过上述转换，在用函数处理这些数值时，常常返回错误。四、数学及统计函数函数名功能用途ABS求出参数的绝对值。数据计算INT将数值向下取整为最接近的整数。数据计算MOD求出两数相除的余数。数据计算MAX求出一组数中的最大值。数据统计MIN求出一组数中的最小值。数据统计COUNT统计包含数字的单元格的个数单元格统计COUNTIF统计某个单元格区域中符合指定条件的单元格数目。条件统计COUNTIFS根据多个条件对单元格数目统计。多条件统计SUMIF计算符合指定条件的单元格区域内的数值和。条件求和SUMIFS对区域中满足多个条件的单元格求和多条件求和RANK返回某一数值在一列数值中的相对于其他数值的排位。数据排序FREQUENCY计算数值在某个区域内的出现频率频率分布(1)ABS函数主要功能：求出相应数字的绝对值。使用格式：ABS(number)参数说明：number代表需要求绝对值的数值或引用的单元格。应用举例：如果在B2单元格中输入公式：=ABS(A2)，则在A2单元格中无论输入正数（如100）还是负数（如-100），B2中均显示出正数（如100）。特别提醒：如果number参数不是数值，而是一些字符（如A等），则B2中返回错误值“#VALUE！”。(2)INT函数主要功能：将数值向下取整为最接近的整数。使用格式：INT(number)参数说明：number表示需要取整的数值或包含数值的引用单元格。应用举例：输入公式：=INT(18.89)，确认后显示出18。特别提醒：在取整时，不进行四舍五入；如果输入的公式为=INT(-18.89)，则返回结果为-19。(3)MOD函数主要功能：求出两数相除的余数。使用格式：MOD(number,divisor)参数说明：number代表被除数；divisor代表除数。应用举例：输入公式：=MOD(13,4)，确认后显示出结果“1”。特别提醒：如果divisor参数为零，则显示错误值“#DIV/0!”；MOD函数可以借用函数INT来表示：上述公式可以修改为：=13-4*INT(13/4)。(4)MAX函数主要功能：求出一组数中的最大值。使用格式：MAX(number1,number2……)参数说明：number1,number2……代表需要求最大值的数值或引用单元格（区域），参数不超过30个。应用举例：输入公式：=MAX(E44:J44,7,8,9,10)，确认后即可显示出E44至J44单元和区域和数值7，8，9，10中的最大值。

(5)MIN函数主要功能：求出一组数中的最小值。

（6）COUNT函数主要功能：返回包含数字的单元格的个数。使用格式：COUNT(Value1，Value2……)参数说明：Value1，Value2……为常量或引用的单元格。特别提醒：如果需要统计单元格中的逻辑值、文本值和错误值，必须使用COUNTA函数。统计空白单元格可以使用countblank函数。（7）COUNTIF函数主要功能：统计某个单元格区域中符合指定条件的单元格数目。使用格式：COUNTIF(Range,Criteria)参数说明：Range代表要统计的单元格区域；Criteria表示指定的条件表达式。应用举例：在C17单元格中输入公式：=COUNTIF(B1:B13,">=80")，确认后，即可统计出B1至B13单元格区域中，数值大于等于80的单元格数目。（8）COUNTIFS函数主要功能：将条件应用于跨多个区域的单元格，并计算符合所有条件的单元格数目使用格式：COUNTIFS(criteria_range1,criteria1,[criteria_range2,criteria2]…)参数说明：criteria_range1和criteria1是必需项，表示区域。及条件。criteria_range2,criteria2,...为可选项，表示附加的区域及其关联条件。每个附加的区域都必须与参数criteria_range1具有相同的行数和列数。应用举例：在单元格中输入公式：=COUNTIF(B1:B13,“>=80”,C1:C13,“>=80”)，确认后，即可统计出B1至B13区域中数值大于等于80，且C1至C13区域中数值也大于等于80的单元格数目。（9）SUMIF函数主要功能：计算符合指定条件的单元格区域内的数值和使用格式：SUMIF(Range,Criteria,Sum_Range）参数说明：Range代表条件判断的单元格区域；Criteria为指定的条件；Sum_Range代表需要计算的数值所在的单元格区域。应用举例：在D64单元格中输入公式：

=SUMIF(C2:C63,"男",D2:D63)，确认后即可求出“男”生的语文成绩和。（10）SUMIFS函数主要功能：对区域中满足多个条件的单元格求和使用格式：SUMIFS(sum_range,criteria_range1,criteria1,criteria_range2,criteria2,...)

参数说明：sum_range是需要求和的单元格区域，忽略其中的空白单元格和文本值。参数criteria_range1是条件判断的第一个区域。criteria1表示条件。criteria_range2,criteria2,…是可选项，表示附加的区域及其关联条件。例如：要对A1:A20区域

中符合以下条件的单元格数值求和：B1:B20中的数值大于60且C1:C20中的数值小于100，则可以使用以下公式：=SUMIFS(A1:A20,B1:B20,">60",C1:C20,"<100")(11)RANK函数主要功能：返回某一数值在一列数值中的相对于其他数值的排位。使用格式：RANK（Number,ref,order）参数说明：Number代表需要排序的数值；ref代表排序数值所处的单元格区域；order代表排序方式参数（如果为“0”或者忽略，则按降序排名，即数值越大，排名结果数值越小；如果为非“0”值，则按升序排名，即数值越大，排名结果数值越大；）。应用举例：如在C2单元格中输入公式：=RANK(B2,$B$2:$B$31,0)，确认后即可得出丁1同学的语文成绩在全班成绩中的排名结果。(12)FREQUENCY函数主要功能：计算数值在某个区域内的出现频率，然后返回一个垂直数组。

函数格式：FREQUENCY(data_array,bins_array）参数说明：data_array是一个值数组或者是对一组数值的引用，需要为它计算频率。bins_array是一个区间数组或对区间的引用，该区间用于对data_array中的数值进行分组。

特别说明：FREQUENCY函数应以数组公式的形式输入。返回的数组中的元素个数比bins_array中的元素个数多1个。多出来的元素表示最高区间之上的数值个数。

应用举例：工作表中列出了员工的年龄。在C12~C15单元格中依次输入用于分组的数