第四章 条件格式与函数

2016/3/5
安全信息技术及管理
9
当数据表格记录行数较多时，采用不同颜色间 隔显示的条纹方式可以让数据更容易识别。
例4-3：将某公司员工信息表 中的奇数行用颜色填充。

条 件 公 式 ： (MOD(ROW(),2)=1)*(A1< >“”)。
2016/3/5
安全信息技术及管理
10
条件公式：(MOD(ROW(),2)=1)*(A1<>“”): MOD函数表示取余数，ROW函数表示取行号， 则 MOD(ROW(),2)=1 表示判断行号除以 2 的余 数是否等于1； A1<>“”判断单元格是否非空，非空则返回1， 空单元格则返回0。

#REF! 当单元格引用无效时，会出现此错误。
=offset(A1,-1,2)
2016/3/5
安全信息技术及管理
39
数学函数
SUM，求和 AVERAGE，求平均值 SIN，求正弦值 SQRT，求平方根 EXP，求e的n次方 MOD，求两数相除的余数 RAND，返回0~1之间随机数
某种程度上可实现数据的可视化。
2016/3/5
安全信息技术及管理
2
2016/3/5
安全信息技术及管理
3
例 4-1 ： 在 某 公 司 产 品 销 售 记 录 表 中 ， 找 到 “产品型号”字段中包含“EF30mmUSM”的 记录，并标识出来。
2016/3/5
安全信息技术及管理
4
对于数值型数据，条件规 则包括：大于、小于、介 于、值最大/小的10项、值 最大 / 小的 10% 项、高 / 低 于平均值等。
2016/3/5
安全信息技术及管理
14
条件公式组合1： 1. =$D2-today()<=7;
黄色
灰色 优先级规则：后添加 的规则优先级越高。 灰色
2. =today()>$E2
条件公式组合2： 1. =today()>$E2; 2. =$D2-today()<=7
2016/3/5 安全信息技术及管理
=SUM($A1:$A10)

混合引用快捷方式：在相对引用基础上多次按<F4>；
2016/3/5
安全信息技术及管理
36
课堂练习
练习相对引用、绝对引用、及混合引用在 公式中的应用。
2016/3/5
安全信息技术及管理
37

##### 当列宽不够显示数字，或者运算结果超出日期的允许范围（ 1900 年1月1日至9999年12月31日）时，会出现此错误。
数学公式：=(A1+A2+A3+…+A9+A10)/10 函数调用：=AVERAGE(A1:A10)

函数必要组成部分包括函数名称和一对半角括号，无论括号中是 否有参数； 例：调用圆周率π的数值
函数调用：=PI( )
2016/3/5
安全信息技术及管理
19

具有多个参数的函数，每个参数具有固定位置，参数之间必须使 用逗号隔开，且无法跳过中间某个参数； 例：


公式组成元素包括等号、运算符、常量、单元格引用、函数、名 称等；
公示通常只能从其他单元格中获取数据进行运算，也无法对其他 单元格进行删除、增减或赋值。
2016/3/5
安全信息技术及管理
18

函数的运算方法封装在Excel内部，通过给定参数进行运算并返回 结果； 例：计算A1:A10单元格区域中数值的平均值
例 4-6 ： 计 算 某企业各月份 利润。
利润=收入-成本
2016/3/5
安全信息技术及管理
33

引用对象不随公式所在单元格变化而改变，即引用对象保持固定 的方式称为“绝对引用”； 绝对引用时，单元格地址前使用“$”符号。
例 4-6 ： 计 算 某企业各月份 收入占比。
收入占比= 当月收入/全年收入
安全信息技术及管理 22
2016/3/5

日期与时间是数值的特殊表现形式，日期用整数表示，时间用小 数表示； Excel逻辑值只有TRUE和FALSE两个，返回表达式的真假值； 对于无法返回正确结果的公式，Excel会显示错误提醒； 常量指运算过程中自身不会改变的值，包括数值和文本。
=1+2*3
黄色
15
2016/3/5
安全信息技术及管理
16
色阶：通过不同颜色的渐变过渡实现数据的可 视化。
例4-5
2016/3/5
安全信息技术及管理
17
4.2 函数公式

公式是以“ =”号为引导，进行数据运算处理并返回结果的等式； 函数是按照特定的算法执行计算，产生一个或一组结果的预定义 的特殊公式；
练习：分别标识出例 4-2 中“销售人员”列重 复出现的人员姓名以及只出现一次的姓名。
2016/3/5
安全信息技术及管理
7
自定义功能可设计更加复杂的条件格式。
Βιβλιοθήκη Baidu
例 4-3 ：标识出某公司员工信息表中“年龄” 在40岁以下的所有“工程师”的整行记录。
条件公式：=($E2<40)*($H2=“工程师”)。
=(A1+B1)*C1^2 =(R1C1+R1C2)*R1C3^2

Excel默认引用方式为A1引用，通常情况下比R1C1方便。
2016/3/5
安全信息技术及管理
29

区域运算符：冒号，引用一个矩形区域，冒号两端两个单元格分 别是这个区域的左上角和右下角； 如(A1:D10)，其目标引用区域为
=date(1900,1,1)-20

#VALUE! 当使用的参数或操作数类型错误时，会出现此错误。
=sum(“Technology”)
2016/3/5
安全信息技术及管理
38

#NAME? 当公式中使用的字符串未加双引号，或函数名称拼写错误时，会 出现此错误。
=left(Technology,3) =averge(A1:10)
2016/3/5
安全信息技术及管理
24
符号
说明
实例
% ^ * 和/ + 和=,<>,>,<,>=,<=
算术运算符：负号
算术运算符：百分号 算术运算符：乘幂 算术运算符：乘和除 算术运算符：加和减 比较运算符：等于、不 等于、大于、小于、大 于等于、小于等于 文本运算符：连接文本 区域引用运算符：冒号 交叉引用运算符：单个 空格 联合引用运算符：逗号
2016/3/5
安全信息技术及管理
27

A1引用指的是用英文字母代表列标，用数字代表行号，由行列坐 标构成单元格地址的引用；
=(A1+B1)*C1^2

Excel2010最大列XFD，最大行1048576。
2016/3/5
安全信息技术及管理
28


R：Row（行）；C：Column（列）;
R1C1引用指的是通过行号和列号以及行列标识“R”和“C”一 起来组成单元格地址引用；
=8*-5=-40
=60*5%=3 =3^2=9 =16^(1/2)=4 =3*2/4=1.5 =3+2-5=0 =(A1=A2), 判断A1与A2相等； =(B1<>“ABC”)， 判断B1不等于“ABC”； =(C1>=5), 判断C1大于等于5 =“Excel” & “2010” 返回“Excel2010” =SUM(A1:B10), 引用以A1为左上角、以B10为 右下角的矩形区域 =SUM(A1:B5 A4:D9), 引用交叉区域，即A4:B5 =SUM(A1:A5,C1:C10)
2016/3/5
安全信息技术及管理
8
所 使 用 的 条 件 公 式 “ =($E2<40)*($H2=“ 工 程 师”)”是将两个条件判断值相乘，公式结果为 0， 表示不满足条件；公式结果为 1，表示满足条件； E列和H列是判断条件的固定字段，采取决对引 用；而活动单元格位于第 2 行，因此使用“ 2 ” 作为行号，且采用相对引用方式，表示数据规 则要应用到整个数据区域。
例 4-1 ：在某公司产品销售记录表中，找到并 标识出“销售数量”前3名的记录。
2016/3/5
安全信息技术及管理
5
课堂练习
采用不同“单元格规则”和“项目选取规 则”，对例4-1中的销售记录表中的“销售 数量”进行条件格式标识；与排序和筛选 进行对比
2016/3/5
安全信息技术及管理
6
操作流程：条件格式 → 突出显示单元格规则 → 重复值 → 设置格式。
2016/3/5
安全信息技术及管理
11
对于项目管理或日程管理，将日期函数和条件 格式结合，可设置自动化预警或到期提醒功能；
例4-4：在某公司的项目进度 计划安排表中，根据系统当 前日期，在项目截止日期前 一周开始自动黄色高亮警示， 到验收日期之后显示灰色， 表示项目已结束。
2016/3/5
安全信息技术及管理
Safety Information Technology and Management 安 全 信 息 技 术 及 管 理
Excel数据分析与处理
第四章 条件格式与函数公式
2016/3/5
安全信息技术及管理
1
4.1 条件格式
通过一些特征条件找到特定的数据； 用直观的方法展现数据规律；
对单元格数据进行判别，并用特殊定义的格式来显示；

2016/3/5
引用整行，可以省略列标，如(1:10)；引用整列，可省略行标，如 (A:D)。
安全信息技术及管理 30

交叉运算符：空格，连接两个单元格区域，引用这两个区域的交 集部分；
如(A1:D10 C6:F12)，其目标引用区域为(C6:D10)
2016/3/5
安全信息技术及管理
31

2016/3/5
安全信息技术及管理
21


Excel数据包括文本、数值、日期、逻辑值、错误值等几种类型；
公式中用一对半角双引号 “” 所包含的内容表示文本，数值是由 0~9这些数字以及特定符号所组成的、可以直接比较大小和参与数 学运算的数据；
=IF(A1>0, 2*SUM(B:B), “”)

数字与数值：数字包括文本型数字和数值型数字，而数值是由负 数、零或正数组成的数据。
25
& : _（空格） ,
2016/3/5
安全信息技术及管理
运算符的优先级
=9--2^4; =9-(-2)^4; =(3+2)×[2+(10-4)÷3]+32
2016/3/5
安全信息技术及管理
26
单元格引用：在公示中取用某个单元格或某个 区域的数据； 引用样式： A1引用； R1C1引用
2016/3/5
安全信息技术及管理
34

同时在行号和列标前添加“$”符号，表示彻底的绝对引用，无论 公式复制到哪儿，都不会改变所引用对象的地址；
=SUM($A$1:$A$10)*$C$1

绝对引用快捷方式：在相对引用基础上按一次<F4>；
2016/3/5
安全信息技术及管理
35

只在行号或列标前添加“$”符号，表示混合引用，复制公式时有 “$”符号的行或列保持不变；
12
条件公式： 1. =$D2-today()<=7;
黄色 灰色
2. =today()>$E2
例 4-4：在某公司的项目进度计划安排表中，根据系统 当前日期，在项目截止日期前一周开始自动黄色高亮 警示，到验收日期之后显示灰色，表示项目已结束。
2016/3/5
安全信息技术及管理
13
课堂练习
例4-4中，将系统当前日期在项目截止日期 之后、验收日期之前的记录标识为 红色。

除了某些函数部分参数可省略外，通常情况下不能输入少于或多 于函数自身所必需的参数个数。
2016/3/5
安全信息技术及管理
20

函数参数可以是常量、单元格引用、计算式或其他函数； 例：
=IF(A1>0, SUM(B:B), “”)

当一个函数作为另一个函数的参数使用时，称为函数的嵌套。上 例中SUM函数为IF函数的嵌套函数。
=IF(A1>0, “正数”)

IF函数语法：IF(logical_test, value_if_true, [value_if_false])； 上 例 中 A1>0 为 第 1 个 参 数 logical_test ， “ 正 数 ” 为 第 2 个 参 数 value_if_true，第3个参数省略；
2016/3/5
安全信息技术及管理
23
运算符是构成公式的基本元素， Excel 包括以下几种类 型的运算符:
算术运算符：主要包含了加、减、乘、除、百分比以及乘幂等 各种常规的算术运算；
比较运算符：用于比较数据的大小；
文本运算符：主要用于将文本字符或字符串进行连接和合并；
引用运算符： Excel特有的运算符，用于在工作表中产生单元格 引用。
联合运算符：逗号，连接两个单元格区域，引用这两个区域的并 集；
如(A1:D10,C6:F12)，其目标引用区域为:
2016/3/5
安全信息技术及管理
32

随着公式所在位置不同而改变单元格引用地址的方式称为“相对 引用”； 相对引用时，公式所在的单元格与引用对象之间的行列间距始终 保持一致。