第4章_Excel函数与公式1

4.2.3 条件格式
在Excel中提供了一个功能非常独特的数据管理功能——条件 格式。通过设置数据条件格式，可以让单Байду номын сангаас格中的数据满足 指定条件时就以特殊的标记（如：以红色、数据条、图标等） 显示出来。该功能可以让单元格根据不同的应用环境所设置 的条件发生变化。
我们还可以在单元格中设置彩色的数据条，以 数据条的长度来表示数值的大小
4.3 EXCEL中函数与公式
4.3.1 公式的概述
公式就是对工作表中的数值进行计算的式子，由 操作符和运算符两个基本部分组成。操作符可以 是常量、名称。数组、单元格引用和函数等。运 算符用于连接公式中的操作符，是工作表处理数 据的指令。
1．公式元素 2．运算符 在公式中，每个运算符都有一个优先级。对于不
第二篇 EXCEL高级应用
Excel2010是美国微软（Microsoft）公司推出的一种电子表格处 理软件，是Microsoft Office 2010套装办公软件的一个重要组件。 它具有以下几个方面的功能：
数据分析处理——Excel2010具有超强的数据分析能力，能够创建 预算、分析调查结果以及进行财务数据分析。
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”。
自动化处理——Excel2010能够通过使用宏功能来进行自动化处理， 实现单击鼠标就可以执行一个复杂任务的功能。
第4章 函数与公式
4.1 EXCEL实例介绍
4.1.1EXCEL表的建立
根据小张店铺的情况，经过相关的分析，整个 Excel工作簿中包含有进货清单、销售清单、 库存清单、销售统计、分类统计报表、分类统 计图表、商品资料、客户资料、其他资料设置 等工作表。
将数字向上舍入到最接近的偶数 向绝对值减小的方向舍入数字 将数字向下舍入为最接近的整数或最接近的指定基数的倍数。无论该数字 的符号如何，该数字都向下舍入。
将数字向下舍入到最接近的整数 返回一个舍入到所需倍数的数字 将数字向上舍入为最接近的奇数 将数字按指定位数舍入 向绝对值减小的方向舍入数字 向绝对值增大的方向舍入数字 将数字截尾取整
4.2.4 数据输入技巧
1．特殊数据输入 输入分数 在输入的分数前加上一个“0”和一个空格 输入负数 除了直接输入负号和数字外，也可以使用括号来完成 输入文本类型的数字 在输入的数据前面加上单引号 输入特殊字符 2．快速输入大写中文数字 3．自动超链接的处理
4.2.5 数据的舍入方法
公式“=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.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层。
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 会对在范围参 数中指定的单元格（即应用条件的单元格）求和。
函数名称 CEILING 函数 CEILING.PRECISE 函数
EVEN 函数 FLOOR 函数 FLOOR.PRECISE 函数
INT 函数 MROUND 函数 ODD 函数 ROUND 函数 ROUNDDOWN 函数 ROUNDUP 函数 TRUNC 函数
表 4-1 数值舍入函数
说明 将数字舍入为最接近的整数或最接近的指定基数的倍数 将数字舍入为最接近的整数或最接近的指定基数的倍数。无论该数字的符 号如何，该数字都向上舍入。
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（ 舍入为三个有 效数字的数值）。
创建图表——使用图标工具能够根据表格的具体数据创建多种类型 的图表，这些既美观又实用的图表，可以让用户清楚地看到数字所 代表的意义。
绘制图形和结构图——使用绘图工具和自选图形能够创建各种图形 及结构图，达到美化工作表和直观显示逻辑关系的目的。
使用外部数据库——Excel2010能够通过访问不同类型的外部数据 库，来增强该软件处理数据这一方面的功能。
4.1.2 EXCEL中数据的管理与分析
数据输入 函数与公式 筛选与排序 分类汇总 数据透视表（图）
4.2 EXCEL中数据的输入
4.2.1 自定义下拉列表输入
4.2.2 自定义序列与填充柄
自定义填充序列是一组数据，可按重复方式填充列。通过工 作表中现有的数据项或以临时输入的方式，可以创建自定义 填充序列。
如果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。
2．使用INT和TRUNC函数 INT函数和TRUNC函数很相似，都能将一个数值转换
为整数，但TRUNC函数是简单地将一个数值的小数部 分去掉，INT函数则是将一个数值基于其小数部分的值 向下取整。
当参数为非负数时，这两个函数将返回相同的结果； 当参数为负数时，它们将返回不同的结果。例如，公 式“＝TRUNC（-17.3.）”其返回值为“-17”；而公 式“＝INT（-17.3）”其返回值为“-18”。
同优先级的运算，按照从高到低的优先级顺序进 行计算。对于同一优先级的运算，按照从左到右 的书讯进行计算。
4.3.2 单元格的引用
相对引用 绝对引用 混合引用 三维引用 循环引用
4.3.3 创建名称及其使用
在Excel中，可以通过一个名称来代表工作表、 单元格、常量、图表或公式等。如果在Excel 中定义一个名称，就可以在公式中直接使用它。
完成名称的定义之后，就可以再工作表中使用 了
4.3.4 SUM函数的应用
SUM函数是返回指定参数所对应的数值之和。 其完整的结构为：
SUM(number1,[number2],...]) 其中，number1，number2等这些是指定所
要进行求和的参数，参数类型可以是数字、逻 辑值和数字的文字表示等形式。
IF函数也能进行嵌套函数的使用，在实例中的许多表 格中都用到了这方面的知识。例如在销售清单中，当 用户选择了一个用户ID之后，为什么后面的一些单元 格（如姓名、发货地址、固定电话、手机、邮编）能 自动的填充上相关的信息？其实这里这些单元格都应 用了IF函数，同时在IF函数中嵌套了一个查询函数— —VLOOKUP函数。例如，在销售清单中的E2单元格 选择“user1”，则F2的单元格输入 “=IF($E2="","",VLOOKUP($E2,客户资料,2,0))”。 该输入的意思就是根据E2的输入，如果E2非空，则查 询客户资料表中的第二列，将其填入到F2单元格中， 如图 4-48所示。