《条件格式与函数》PPT课件

2019/5/7
安全信息技术及管理
9
当数据表格记录行数较多时，采用不同颜色间 隔显示的条纹方式可以让数据更容易识别。
例4-3：将某公司员工信息表 中的奇数行用颜色填充。
条 件 公 式 ：
(MOD(ROW(),2)=1)*(A1< >“”)。
2019/5/7
安全信息技术及管理
10
条件公式：(MOD(ROW(),2)=1)*(A1<>“”):
MOD函数表示取余数，ROW函数表示取行号， 则MOD(ROW(),2)=1表示判断行号除以2的余 数是否等于1；
A1<>“”判断单元格是否非空，非空则返回1， 空单元格则返回0。
2019/5/7
安全信息技术及管理
11
对于项目管理或日程管理，将日期函数和条件 格式结合，可设置自动化预警或到期提醒功能；
Safety Information Technology and Management 安全信息技术及管理
Excel数据分析与处理
第四章 条件格式与函数公式
2019/5/7
安全信息技术及管理
1
4.1 条件格式
通过一些特征条件找到特定的数据； 用直观的方法展现数据规律； 对单元格数据进行判别，并用特殊定义的格式来显示； 某种程度上可实现数据的可视化。
灰色
2. =$D2-today()<=7
黄色
2019/5/7
安全信息技术及管理
优先级规则：后添加 的规则优先级越高。
15
2019/5/7
安全信息技术及管理
16
色阶：通过不同颜色的渐变过渡实现数据的可 视化。
例4-5
2019/5/7
安全信息技术及管理
17
4.2 函数公式
公式是以“=”号为引导，进行数据运算处理并返回结果的等式；
练习：分别标识出例4-2中“销售人员”列重 复出现的人员姓名以及只出现一次的姓名。
2019/5/7
安全信息技术及管理
7
自定义功能可设计更加复杂的条件格式。
例4-3：标识出某公司员工信息表中“年龄” 在40岁以下的所有“工程师”的整行记录。
条件公式：=($E2<40)*($H2=“工程师”)。
2019/5/7
安全信息技术及管理
8
所 使 用 的 条 件 公 式 “ =($E2<40)*($H2=“ 工 程 师”)”是将两个条件判断值相乘，公式结果为0， 表示不满足条件；公式结果为1，表示满足条件；
E列和H列是判断条件的固定字段，采取决对引 用；而活动单元格位于第2行，因此使用“2” 作为行号，且采用相对引用方式，表示数据规 则要应用到整个数据区域。
2019/5/7
安全信息技术及管理
2及管理
3
例 4-1 ： 在 某 公 司 产 品 销 售 记 录 表 中 ， 找 到 “产品型号”字段中包含“EF30mmUSM”的 记录，并标识出来。
2019/5/7
安全信息技术及管理
4
对于数值型数据，条件规 则包括：大于、小于、介 于、值最大/小的10项、值 最 大 / 小 的 10% 项 、 高 / 低 于平均值等。
数学公式：=(A1+A2+A3+…+A9+A10)/10 函数调用：=AVERAGE(A1:A10)
函数必要组成部分包括函数名称和一对半角括号，无论括号中是 否有参数； 例：调用圆周率π的数值
函数调用：=PI( )
2019/5/7
安全信息技术及管理
19
具有多个参数的函数，每个参数具有固定位置，参数之间必须使 用逗号隔开，且无法跳过中间某个参数； 例：
例4-4：在某公司的项目进度 计划安排表中，根据系统当 前日期，在项目截止日期前 一周开始自动黄色高亮警示， 到验收日期之后显示灰色， 表示项目已结束。
2019/5/7
安全信息技术及管理
12
条件公式：
1. =$D2-today()<=7;
黄色
2. =today()>$E2
灰色
例4-4：在某公司的项目进度计划安排表中，根据系统 当前日期，在项目截止日期前一周开始自动黄色高亮 警示，到验收日期之后显示灰色，表示项目已结束。
2019/5/7
安全信息技术及管理
21
Excel数据包括文本、数值、日期、逻辑值、错误值等几种类型； 公式中用一对半角双引号 “” 所包含的内容表示文本，数值是由
例4-1：在某公司产品销售记录表中，找到并 标识出“销售数量”前3名的记录。
2019/5/7
安全信息技术及管理
5
课堂练习
采用不同“单元格规则”和“项目选取规 则”，对例4-1中的销售记录表中的“销售 数量”进行条件格式标识；与排序和筛选
进行对比
2019/5/7
安全信息技术及管理
6
操作流程：条件格式 → 突出显示单元格规则 → 重复值 → 设置格式。
2019/5/7
安全信息技术及管理
13
课堂练习
例4-4中，将系统当前日期在项目截止日期 之后、验收日期之前的记录标识为 红色。
2019/5/7
安全信息技术及管理
14
条件公式组合1：
1. =$D2-today()<=7;
黄色
2. =today()>$E2
灰色
条件公式组合2：
1. =today()>$E2;
函数是按照特定的算法执行计算，产生一个或一组结果的预定义 的特殊公式；
公式组成元素包括等号、运算符、常量、单元格引用、函数、名 称等；
公示通常只能从其他单元格中获取数据进行运算，也无法对其他 单元格进行删除、增减或赋值。
2019/5/7
安全信息技术及管理
18
函数的运算方法封装在Excel内部，通过给定参数进行运算并返回 结果； 例：计算A1:A10单元格区域中数值的平均值
=IF(A1>0, “正数”)
IF函数语法：IF(logical_test, value_if_true, [value_if_false])；
上 例 中 A1>0 为 第 1 个 参 数 logical_test ， “ 正 数 ” 为 第 2 个 参 数 value_if_true，第3个参数省略；
除了某些函数部分参数可省略外，通常情况下不能输入少于或多 于函数自身所必需的参数个数。
2019/5/7
安全信息技术及管理
20
函数参数可以是常量、单元格引用、计算式或其他函数； 例：
=IF(A1>0, SUM(B:B), “”)
当一个函数作为另一个函数的参数使用时，称为函数的嵌套。上 例中SUM函数为IF函数的嵌套函数。