《全国计算机等级考试一级教程》课件-5-4

5.4公式与函数

5.4.1自动计算

利用工具栏的自动求和按钮

或在状态栏上单击鼠标右键，无须公式即可自动计算一组数据的累加和、平均值、统计个数、求最大值和最小值等。

【例题5-10】对“销售单”工作表中的数据进行自动计算。计算1：计算A001型号产品三个月的销售总和（置E3单元格）；计算2：计算A001、A002、A003、A004四个型号产品各自三个月的销售总和（置E3:E6单元格区域）以及每个月四个型号产品销售的合计（置B7:E7单元格）；计算3：计算一月份和三月份四种产品销售的平均数量（置F7单元格）。具体操作步骤：

计算1：

（1）选定B3:E3单元格区域。

（2）选择“开始/编辑”，单击“套用表格格式”单击

按钮右侧的向下箭头，单击“求和”，计算结果显示在E3单元格，如图5-27所示，此时，单击E3单元格，编辑栏显示：=SUM（B3:D3）。计算2：

（1）选定B3:E7单元格区域。

（2）选择“开始/编辑”，单击“套用表格格式”单击

按钮右侧的向下箭头，单击“求和”，计算结果显示在E3:E6单元格区域和B7:E7单元格，如图5-28所示，此时，单击E3:E6和B7:E7区域任一单元格，数据编辑区均有求和公式显示。计算3：

（1）选定F7单元格。

（2）选择“开始/编辑”，单击

按钮右侧的向下箭头，选择“平均值”命令，此时，F7单元格有公式出现，选定B3:B6区域，按住Ctrl键，再选定D3:D6单元格区域，如图5-29所示，单击Enter键，结果显示在F7单元格内，结果如图5-30所示。

5.4.2输入公式

Excel可以使用公式对工作表中的数据进行各种计算，如算术运算、关系运算和字符串运算等。1．公式的形式

公式的一般形式为：＝<表达式>表达式可以是算术表达式、关系表达式和字符串表达式等；表达式可由运算符、常量、单元格地址、函数及括号等组成，但不能含有空格；公式中<表达式>前面必须有“=”号。2．运算符

用运算符将常量、单元格地址、函数及括号等连接起来组成了表达式。常用运算符有算术运算符、字符运算符和关系运算符三类。运算符具有优先级，表5-1按运算符优先级从高到低列出各运算符及其功能。3．公式的输入

选定要放置计算结果的单元格后，公式的输入可以在数据编辑区中进行，也可以双击该单元格在单元格中进行。在数据编辑区输入公式时，单元格地址可以通过键盘输入，也可以直接单击该单元格，单元格地址即自动显示在数据编辑区。输入后的公式可以进行编辑和修改，还可以将公式复制到其他单元格。公式计算通常需要引用单元格或单元格区域的内容，这种引用是通过使用单元格的地址来实现的。

【例题

5-11】利用公式计算“销售单”工作表中各型号产品三个月销售的平均数量，置F3:F6单元格区域（数值型，保留小数点位数为“0”）。具体操作步骤：

（1）选定F3单元格，在数据编辑区输入公式：“=(B3+C3+D3)/3”，单击工作表任意位置或按Enter键，结果显示在F3单元格内。

（2）用鼠标拖动F3单元格的自动填充柄至F6单元格，放开鼠标，计算结果显示在F3：F6单元格区域。

（3）选择“开始/数字”，单击“数字格式”下拉箭头，选择“其他数字格式”，在弹出的“设置单元格格式”对话框选择“数值”，“小数位数”输入“0”。单击确定。结果如图5-31所示。

5.4.3复制公式

1．公式复制的方法

方法1：选定含有公式的被复制公式单元格，单击鼠标右键，在弹出的菜单中选择“复制”命令，鼠标移至复制目标单元格，单击鼠标右键，在弹出的菜单中选择“粘贴”或“粘贴公式”（选中复制公式）命令，即可完成公式复制。

方法2：选定含有公式的被复制公式单元格，拖动单元格的自动填充柄，可完成相邻单元格公式的复制。2．单元格地址的引用Excel中单元格的地址分相对地址、绝对地址、混合地址三种。根据计算的要求，在公式中会出现相对地址、绝对地址和混合地址以及它们的混合使用。（1）相对地址

相对地址的形式为：D3、A8等。表示在单元格中当含有相对地址的公式被复制到目标单元格时，公式不是照搬原来单元格的内容，而是根据公式原来位置和复制到的目标位置推算出公式中单元格地址相对原位置的变化，使用变化后的单元格地址的内容进行计算。

例如：在Sheet1工作表D1单元格有公式“=(A1+B1+C1)/3”，如图5-32所示；当将公式复制到D2单元格时，公式变为：“=(A2+B2+C2)/3”，如图5-33所示。而当将公式复制到E3单元格时，公式将变为：“=(B3+C3+D3)/3”，原因是当D1单元格公式“=(A1+B1+C1)/3”复制到D2单元格时，列号不变，行号加1，因此，D2单元格的公式为：“=(A2+B2+C2)/3”；而当D1单元格公式“=(A1+B1+C1)/3”复制到E3单元格时，列号加1，行号加2，因此，E3单元格的公式为：“=(B3+C3+D3)/3”。（2）绝对地址

绝对地址的形式为：$D$3、$A$8等。表示在单元格中当含有绝对地址的公式无论被复制到哪个单元格，公式永远是照搬原来单元格的内容。例如：D1单元格中公式“=($A$1+$B$1+$C$1)/3”，复制到E3单元格公式仍然为“=($A$1+$B$1+$C$1)/3”，公式中单元格引用地址也不变。（3）混合地址

混合地址的形式为：D$3、$A8等，表示在单元格中当含有混合地址的公式被复制到目标单元格时，相对部分会根据公式原来位置和复制到的目标位置推算出公式中单元格地址相对原位置的变化，而绝对部分地址永远不变，之后，使用变化后的单元格地址的内容进行计算。如：D1单元格中公式“=（$A1+B$1+C1）/3”，复制到E3单元格，公式为“=($A3+C$1+D3)/3”。（4）跨工作表的单元格地址引用

单元格地址的一般形式为：[工作簿文件名]工作表名!单元格地址

在引用当前工作簿的各工作表单元格地址时，当前“[工作簿文件名]”可以省略，引用当前工作表单元格的地址时“工作表名!”可以省略。例如，单元格F4中的公式为：“=(C4+D4+E4)*Sheet2!Bl”，其中“Sheet2!Bl”表示当前工作簿Sheet2工作表中的Bl单元格地址，而C4表示当前工作表C4单元格地址。

用户可以引用当前工作簿另一工作表的单元格，也可以引用同一工作簿中多个工作表的单元格。例如“=SUM([Book1.xls]Sheet2:Sheet4!$A$5)”表示Book1工作簿的Sheet2到Sheet4共3个工作表的A5单元格内容求和。这种引用同一工作簿中多个工作表上的相同单元格或单元格区域中数据的方法称为三维引用。

【例题5-12】利用公式计算图5-31“销售单”工作表中各型号产品三个月销售数量的总和以及每个月各型号产品销售数量的合计；计算每种产品销售数量占总销售数量的百分比并放在G3:G6单元格区域（总销售数量为E7单元格的值）。具体操作步骤：

（1）选定E3单元格，在数据编辑区输入公式：“=B3+C3+D3”，单击工具栏的“输入”按钮或按Enter键，计算结果显示在E3单元格。

（2）用鼠标拖动E3单元格的自动填充柄至E6单元格，放开鼠标，“总和”计算结果显示在E3:E6单元格区域。

（3）选定B7单元格，在数据编辑区输入公式：“=B3+B4+B5+B6”，单击工具栏的“输入”按钮或按Enter键，计算结果显示在B7单元格。

（4）用鼠标拖动B7单元格的自动填充柄至E7单元格，放开鼠标，计算结果显示在B7：E7单元格区域，如图5-34所示。

（5）选定G3单元格，在数据编辑区输入公式：“=E3/$E$7”，单击工具栏的“输入”按钮或按Enter键，“百分比”计算结果显示在F3单元格。

（6）用鼠标拖动G3单元格的自动填充柄至G6单元格，放开鼠标，“百分比”计算结果显示在G3:G6单元格区域，如图5-35所示。5.4.4函数应用1．函数形式

函数一般由函数名和参数组成，形式为：

函数名（参数表）

其中：函数名由Excel提供，函数名中的大小写字母等价，参数表由用英文逗号分隔的参数1，参数2，…，参数N（N≤30）构成，参数可以是常数、单元格地址、单元格区域、单元格区域名称或函数等。2．函数引用

若要在某个单元格输入公式：“=AVERAGE(A2:A10)”，可以采用如下方法：

方法1：直接在单元格中输入公式：“=AVERAGE(A2:A10)”。

方法2：用“插入函数”快速输入函数，其方法如下：

（1）选定单元格，单击

按钮右侧的向下箭头，选择“其他函数”，弹出“插入函数”对话框，在“选择函数”列表中选中函数“AVERAGE”，如图5-36所示。单击“确定”按钮，打开“函数参数”对话框，如图5-37所示。

（2）可在“函数参数”对话框第一个参数Value1框内用输入选定A2:A10，单击“确定”按钮；也可以单击“切换”按钮

，然后在工作表上选定A2:A10区域，单击“切换”按钮

，单击“确定”按钮。3．函数嵌套

函数嵌套是指一个函数可以作为另一函数的参数使用。例如公式：ROUND(AVERAGE(A2:C2)，1)

其中，ROUND为一级函数，AVERAGE为二级函数。先执行AVERAGE函数，再执行ROUND函数。一定要注意，AVERAGE作为ROUND的参数，它返回的数值类型必须与ROUND参数使用的数值类型相同。Excel函数嵌套最多可嵌套七级。4.Excel函数1）常用函数

（1）SUM(参数1，参数2，…)：求和函数，求各参数的累加和。

（2）AVERAGE(参数1，参数2，…)：算术平均值函数，求各参数的算术平均值。

（3）MAX(参数1，参数2，…)：最大值函数，求各参数中的最大值。

（4）MIN(参数1，参数2，…)：最小值函数，求各参数中的最小值。2）统计个数函数

（1）COUNT(参数1，参数2，…)：求各参数中数值型数据的个数。

（2）COUNTA(参数1，参数2，…)：求“非空”单元格的个数。

（3）COUNTBLANK(参数1，参数2，…)：求“空”单元格的个数。3）四舍五入函数ROUND(数值型参数，n)

返回时对“数值型参数”进行四舍五入到第n位的近似值。

当n>0时，对数据的小数部分从左到右的第n位四舍五入。

当n=0时，对数据的小数部分最高位四舍五入取数据的整数部分。

当n<0时，对数据的整数部分从右到左的第n位四舍五入。4）条件函数IF(逻辑表达式，表达式1，表达式2)

若“逻辑表达式”值为真，函数值为“表达式1”的值；否则为“表达式2”的值。5）条件计数COUNTIF(条件数据区，"条件")统计“条件数据区”中满足给定“条件”的单元格的个数。COUNTIF函数只能对给定的数据区域中满足一个条件的单元格统计个数，若对一个以上的“条件”统计单元格的个数，用数据库函数DCOUNT或DCOUNTA实现。6）条件求和函数SUMIF(条件数据区，"条件"，［求和数据区］)

在“条件数据区”查找满足“条件”的单元格，计算满足条件的单元格对应于“求和数据区”中数据的累加和。如果“求和数据区”省略，统计“条件数据区”满足条件的单元格中数据的累加和。SUMIF函数中的前两个参数与COUNTIF中的两个参数的含义相同，如果省略SUMIF中的第3个参数，SUMIF是求满足条件的单元格内数据的累加和，COUNTIF是求满足条件的单元格的个数。Excel的其他函数以及详细应用请查看Excel帮助信息。

【例题5-13】对“人力资源情况表”工作表，利用函数计算开发部职工人数置于D4单元格（利用COUNTIF函数），计算开发部职工平均工资置于D6单元格（利用SUMIF函数和已求出的计算开发部职工人数）。具体操作步骤：

（1）选定D4单元格，在数据编辑区输入“=”，单击“名称框”右侧的下拉按钮，选择“COUNTIF”函数，在弹出的“函数参数”对话框中输入“Range”参数和“Criteria”参数（可利用“切换”按钮

），此时，数据编辑区出现公式：“=COUNTIF(B3:B8,"开发部")”,单击“确定”按钮，按Enter键或工具栏的“确认”按钮，此时，D4单元格显示开发部职工人数，如图5-38所示。

（2）选定D6单元格，在数据编辑区输入“=”，单击“名称框”右侧的下拉按钮，选择“SUMIF”函数，在弹出的“函数参数”对话框中输入“Range”参数、“Criteria”参数和Sum_Range参数（可利用“切换”按钮

），单击“确定”按钮，此时，数据编辑区出现公式：“=SUMIF(B3:B8,"开发部"，C3:C8)”，将数据编辑栏的公式编辑为：“=SUMIF(B3:B8,"开发部"，C3:C8)／$D$3”，按Enter键或工具栏的“确认”按钮，此时，D6单元格显示开发部职工平均工资，如图5-39所示。5．关于错误信息

在单元格输入或编辑公式后，有时会出现诸如“####！”或“#VALUE！”的错误信息，错误值一般以“#”符号开头，出现错误值有如表5-2所示几种原因。下面简要说明各错误信息可能产生的原因。1）####！

若单元格中出现“####！”错误信息，可能的原因是：单元格中的计算结果太长，该单元格宽度小，可以通过调整单元格的宽度来消除该错误；或者，日期或时间格式的单元格中出现负值。2）#DIV/0！

若单元格中出现“#DIV/0！”错误信息，可能的原因是：该单元格的公式中出现被零除问题，即输入的公式中包含“0”除数，也可能在公式中的除数引用了零值单元格或空白单元格（空白单元的值被解释为零值）。

解决办法是修改公式中的零除数或零值单元格或空白单元格引用，或者在用除数的单元中输入不为零的值。

当做除数的单元格为空或含的值为零时，如果不希望显示错误，可以使用IF函数。例如,如果单元格B5包含除数，而A5包含被除数，可以使用“=lF（B5=0，""，A5/B5）”（两个连续引号代表空字符串），表示B5值为“0”时，什么也不显示，否则显示A5/B5的商。

3）#N/A

在函数或公式中没有可用数值时，会产生这种错误信息。4）#NAME