第4章_Excel函数与公式1

函数名称 CEILING 函数 CEILING.PRECISE 函数
EVEN 函数 FLOOR 函数 FLOOR.PRECISE 函数
INT 函数 MROUND 函数 ODD 函数 ROUND 函数 ROUNDDOWN 函数 ROUNDUP 函数 TRUNC 函数
表 4-1 数值舍入函数
说明 将数字舍入为最接近的整数或最接近的指定基数的倍数 将数字舍入为最接近的整数或最接近的指定基数的倍数。无论该数字的符 号如何，该数字都向上舍入。
4.3.6 IF函数的应用
IF函数是一个条件函数，其完整的的格式为：
IF(logical_test,value_if_true,value_if_false) 其中，第一个参数logical_test是当值函数的逻辑条件，
第二个参数value_if_true是当值为“真”时的返回值， 第三个参数value_if_false是当值为“假”时的返回值。 IF函数的功能为对满足条件的数据进行处理，条件满 足则输出Value_if_true，不满足则输出Value_if_false。 注意，在IF函数中三个参数中可以省略Value_if_true 或Value_if_false，但不能同时省略。另外，在IF函数 的使用中，还可用在IF函数中使用嵌套函数，最多可 用嵌套7层。
5．时间值舍入 有些时候可能需要把一个时间值舍入到特定的分钟数。例如，在输
入公司员工的工时记录时可能需要舍入到最接近的15分钟的倍数。 以下给出了几种舍入时间值的不同方法： 将B1单元格中的时间值舍入为最接近的分钟数，可使用公式“＝R OUND（B1*1440，0）/1440”，该公式将时间值乘以1440（以 得到总分钟数），然后计算结果传递给ROUND函数，再把计算出 的结果除以1440。如果B1单元格中的时间是“13：42：56”，则 使用该公式将会返回“13：43：00”。 将B1单元格中的时间值舍入为最接近的小时数，可使用公式“＝R OUND（B1*24，0）/24”，如果B1单元格中的时间值是“9：21： 45”，公式将返回“9：00：00”。 将B1单元格中的时间值舍入为最接近的15分钟的倍数，可使用公 式“＝ROUND（B1*24/0.25，0）*（0.25/24）”，如果B1单元 格中的时间值为“15：35：12”，公式将返回“15：45”。
4.3.5 AVERAGE函数的应用
AVERAGE函数是返回指定参数所对应数值的算术平均数。 其完整的格式为：
AVERAGE(number1, [number2], ...) 其中，number1，number2等这些是指定所要进行求平均
值的参数。该函数只对参数的数值求平均数，如区域引用 中包含了非数值的数据，则AVERAGE不把它包含在内。例 如：A1：A4中分别存放着数据1～4，如果输入在A5中输 入“＝AVERAGE（A1：A4，10）”，则A5中的值为4， 即为（1＋2＋3＋4＋10）/5。但如果在上例中的A2和A3 单元格分别输入了文本，比如“语文”和“英语”，则A5 的值就变成了5，即为（1＋4＋10）/3，A2和A3虽然包含 在区域引用内，但并没有参与平均值计算。
在Excel中的函数库中，还有一种类似求和函数的 条件求和函数——SUMIF函数。该函数是用于计 算符合指定条件的单元格区域内的数值进行求和。 其完整的格式为：
SUMIF(range, criteria, [sum_range])
其中，range表示的是条件判断的单元格区域；c riteria表示的是指定条件表达式；而sum_range 表示的是需要计算的数值所在的单元格区域。如 果省略 sum_range 参数，Excel 会对在范围参 数中指定的单元格（即应用条件的单元格）求和。
创建图表——使用图标工具能够根据表格的具体数据创建多种类型 的图表，这些既美观又实用的图表，可以让用户清楚地看到数字所 代表的意义。
绘制图形和结构图——使用绘图工具和自选图形能够创建各种图形 及结构图，达到美化工作表和直观显示逻辑关系的目的。
使用外部数据库——Excel2010能够通过访问不同类型的外部数据 库，来增强该软件处理数据这一方面的功能。
4.3 EXCEL中函数与公式
4.3.1 公式的概述
公式就是对工作表中的数值进行计算的式子，由 操作符和运算符两个基本部分组成。操作符可以 是常量、名称。数组、单元格引用和函数等。运 算符用于连接公式中的操作符，是工作表处理数 据的指令。
1．公式元素 2．运算符 在公式中，每个运算符都有一个优先级。对于不
1．舍入到最接近的基数的倍数 这样的函数有5个，其中MROUND函数按四舍五入进行舍入，CEILING函数按绝对值增大的方向
舍入，FLOOR函数按绝对值减小的方向舍入，CEILING.PRECISE函数按算术值增大的方向舍入， FLOOR.PRECISE函数按算术值减小的方向舍入。 另外，EVEN函数和ODD函数都是按绝对值增大的方向舍入。 例如：
4．舍入为n位有效数字 在进行数据处理时，有时候可能需要将一个数值舍入为特定位数的
有效数字。 如果要处理的数值是一个不带小数的正数，可使用公式“＝ROUN
DDOWN（E1，3－LEN（E1））”来进行处理，该公式将E1单元 格中的数字舍入为三位有效数字。如果需要的结果不是两位有效数 字，那么用需要的值替换公式中的3即可。 如果要处理的数值是一个非整数或者一个负数，则可使用公式“＝ ROUND（E1，E2－1－INT（LOG10（ABS（E1））））”来进 行处理，该公式可以将E1单元格中的数字舍入为E2中指定了有效 数字位数的数字；该公式可用于正负整数和非整数。例如，如果单 元格E1的数据是2.546587，那么公式“＝ROUND（E1，E2－1－ INT（LOG10（ABS（E1））））”将返回2.55（ 舍入为三个有 效数字的数值）。
如果TRUNC函数接受一个额外（可选）的参数，可用 于截取小数。例如，公式“＝TRUNC（75.6666， 3）”将返回“75.666”（数值被截取为保留三位小 数）。
3.按小数点位数舍入 按小数点位数进行的舍入的有ROUND函数、ROUNDDO
WN函数和ROUNDUP函数。当小数点位数大于零时，即 保留小数点后几位，当小数点位数小于零时，其实是指在 小数点的左侧进行舍入，当小数点位数等于零时，即舍入 到整数。这三个函数中ROUND函数按四舍五入进行舍入， ROUNDDOWN按绝对值减小的方向舍入，而ROUNDUP 按绝对值增大的方向舍入。 例如，公式：＝ROUND（3.1415926，4），其值为3.14 16；公式：＝ROUND（3.1415926，0），其值为3；公 式：＝ROUND（759.7852，－2），其值为800；公式： =ROUNDDOWN(3.1415926，4)，其值为3.1415；公式： =ROUNDUP(3.1415926,2)，其值为3.15。
将数字向上舍入到最接近的偶数 向绝对值减小的方向舍入数字 将数字向下舍入为最接近的整数或最接近的指定基数的倍数。无论该数字 的符号如何，该数字都向下舍入。
将数字向下舍入到最接近的整数 返回一个舍入到所需倍数的数字 将数字向上舍入为最接近的奇数 将数字按指定位数舍入 向绝对值减小的方向舍入数字 向绝对值增大的方向舍入数字 将数字截尾取整
同优先级的运算，按照从高到低的优先级顺序进 行计算。对于同一优先级的运算，按照从左到右 的书讯进行计算。
4.3.2 单元格的引用
相对引用 绝对引用 混合引用 三维引用 循环引用
4.3.3 创建名称及其使用
在Excel中，可以通过一个名称来代表工作表、 单元格、常量、图表或公式等。如果在Excel 中定义一个名称，就可以在公式中直接使用它。
完成名称的定义之后，就可以再工作表中使用 了
4.3.4 SUM函数的应用
SUM函数是返回指定参数所对应的数值之和。 其完整的结构为：
SUM(number1,[number2],...]) 其中，number1，number2等这些是指定所
要进行求和的参数，参数类型可以是数字、逻 辑值和数字的文字表示等形式。
2．使用INT和TRUNC函数 INT函数和TRUNC函数很相似，都能将一个数值转换
为整数，但TRUNC函数是简单地将一个数值的小数部 分去掉，INT函数则是将一个数值基于其小数部分的值 向下取整。
当参数为非负数时，这两个函数将返回相同的结果； 当参数为负数时，它们将返回不同的结果。例如，公 式“＝TRUNC（-17.3.）”其返回值为“-17”；而公 式“＝INT（-17.3）”其返回值为“-18”。
公式“=MROUND(167,5)”的返回值为“165”； 公式“=MROUND(168,5)”的返回值为“170”； 公式“=CEILING(167,5)”的返回值为“170”； 公式“=CEILING(-2.3,-1)”的返回值为“-3”； 公式“=CEILING.PRECISE(-2.3,-1)”的返回值为“-2”； 公式“=FLOOR(168,5)”的返回值为“165”； 公式“=FLOOR.PRECISE(-2.3,-1)”的返回值为“-3”； 公式“=EVEN(2.9)”的返回值为“4”； 公式“=EVEN(-2.9)”的返回值为“-4”； 公式“=ODD(1.9)”的返回值为“3”； 公式“=ODD(-1.9)”的返回值为“-3”。
4.2.4 数据输入技巧
1．特殊数据输入 输入分数 在输入的分数前加上一个“0”和一个空格 输入负数 除了直接输入负号和数字外，也可以使用括号来完成 输入文本类型的数字 在输入的数据前面加上单引号 输入特殊字符 2．快速输入大写中文数字 3．自动超链接的处理
4.2.5 数据的舍入方法
4.2.3 条件格式
在Excel中提供了一个功能非常独特的数据管理功能——条件 格式。通过设置数据条件格式，可以让单元格中的数据满足 指定条件时就以特殊的标记（如：以红色、数据条、图标等） 显示出来。该功能可以让单元格根据不同来自应用环境所设置 的条件发生变化。