条件格式与函数.ppt

《条件格式与函数.ppt》由会员分享，可在线阅读，更多相关《条件格式与函数.ppt（47页珍藏版）》请在三一办公上搜索。

1、Safety Information Technology and Management,Excel数据分析与处理第四章 条件格式与函数公式,2023/10/15,安全信息技术及管理,1,安 全 信 息 技 术 及 管 理,4.1 条件格式,2023/10/15,安全信息技术及管理,2,4.1.1 条件格式的作用,通过一些特征条件找到特定的数据；用直观的方法展现数据规律；对单元格数据进行判别，并用特殊定义的格式来显示；某种程度上可实现数据的可视化。,2023/10/15,安全信息技术及管理,3,条件格式菜单,2023/10/15,安全信息技术及管理,4,根据文本特征标识单元格,例4-1：在某公

2、司产品销售记录表中，找到“产品型号”字段中包含“EF30mmUSM”的记录，并标识出来。,4.1.2 条件格式应用,2023/10/15,安全信息技术及管理,5,根据数值大小标识特定范围,例4-1：在某公司产品销售记录表中，找到并标识出“销售数量”前3名的记录。,对于数值型数据，条件规则包括：大于、小于、介于、值最大/小的10项、值最大/小的10%项、高/低于平均值等。,2023/10/15,安全信息技术及管理,6,课堂练习采用不同“单元格规则”和“项目选取规则”，对例4-1中的销售记录表中的“销售数量”进行条件格式标识；与排序和筛选进行对比,2023/10/15,安全信息技术及管理,7,标识

3、重复值或唯一值,练习：分别标识出例4-2中“销售人员”列重复出现的人员姓名以及只出现一次的姓名。,操作流程：条件格式 突出显示单元格规则 重复值 设置格式。,2023/10/15,安全信息技术及管理,8,自定义条件规则,例4-3：标识出某公司员工信息表中“年龄”在40岁以下的所有“工程师”的整行记录。,自定义功能可设计更加复杂的条件格式。,条件公式：=($E240)*($H2=“工程师”)。,2023/10/15,安全信息技术及管理,9,自定义条件规则,所使用的条件公式“=($E240)*($H2=“工程师”)”是将两个条件判断值相乘，公式结果为0，表示不满足条件；公式结果为1，表示满足条件；

4、E列和H列是判断条件的固定字段，采取决对引用；而活动单元格位于第2行，因此使用“2”作为行号，且采用相对引用方式，表示数据规则要应用到整个数据区域。,2023/10/15,安全信息技术及管理,10,自动生成间隔条纹,当数据表格记录行数较多时，采用不同颜色间隔显示的条纹方式可以让数据更容易识别。,例4-3：将某公司员工信息表中的奇数行用颜色填充。,条件公式：(MOD(ROW(),2)=1)*(A1“”)。,2023/10/15,安全信息技术及管理,11,自动生成间隔条纹,条件公式：(MOD(ROW(),2)=1)*(A1“”):MOD函数表示取余数，ROW函数表示取行号，则MOD(ROW(),2

5、)=1表示判断行号除以2的余数是否等于1；A1“”判断单元格是否非空，非空则返回1，空单元格则返回0。,2023/10/15,安全信息技术及管理,12,设计到期提醒和预警,对于项目管理或日程管理，将日期函数和条件格式结合，可设置自动化预警或到期提醒功能；,例4-4：在某公司的项目进度计划安排表中，根据系统当前日期，在项目截止日期前一周开始自动黄色高亮警示，到验收日期之后显示灰色，表示项目已结束。,2023/10/15,安全信息技术及管理,13,设计到期提醒和预警,条件公式：=$D2-today()$E2,例4-4：在某公司的项目进度计划安排表中，根据系统当前日期，在项目截止日期前一周开始自动黄

6、色高亮警示，到验收日期之后显示灰色，表示项目已结束。,黄色,灰色,2023/10/15,安全信息技术及管理,14,设计到期提醒和预警,课堂练习例4-4中，将系统当前日期在项目截止日期之后、验收日期之前的记录标识为红色。,2023/10/15,安全信息技术及管理,15,条件公式的优先级,优先级规则：后添加的规则优先级越高。,2023/10/15,安全信息技术及管理,16,条件公式的优先级,2023/10/15,安全信息技术及管理,17,色阶标识数据,色阶：通过不同颜色的渐变过渡实现数据的可视化。,例4-5,2023/10/15,安全信息技术及管理,18,4.2 函数公式,4.2.1 函数公式的概

7、念与特征,公式是以“=”号为引导，进行数据运算处理并返回结果的等式；函数是按照特定的算法执行计算，产生一个或一组结果的预定义的特殊公式；公式组成元素包括等号、运算符、常量、单元格引用、函数、名称等；公示通常只能从其他单元格中获取数据进行运算，也无法对其他单元格进行删除、增减或赋值。,2023/10/15,安全信息技术及管理,19,函数的运算方法封装在Excel内部，通过给定参数进行运算并返回结果；例：计算A1:A10单元格区域中数值的平均值,Excel函数特征,数学公式：=(A1+A2+A3+A9+A10)/10函数调用：=AVERAGE(A1:A10),函数必要组成部分包括函数名称和一对半角

8、括号，无论括号中是否有参数；例：调用圆周率的数值,函数调用：=PI(),2023/10/15,安全信息技术及管理,20,具有多个参数的函数，每个参数具有固定位置，参数之间必须使用逗号隔开，且无法跳过中间某个参数；例：,Excel函数特征,=IF(A10,“正数”),IF函数语法：IF(logical_test,value_if_true,value_if_false)；上例中A10为第1个参数logical_test，“正数”为第2个参数value_if_true，第3个参数省略；除了某些函数部分参数可省略外，通常情况下不能输入少于或多于函数自身所必需的参数个数。,2023/10/15,安全信

9、息技术及管理,21,函数参数可以是常量、单元格引用、计算式或其他函数；例：,Excel函数特征,=IF(A10,SUM(B:B),“”),当一个函数作为另一个函数的参数使用时，称为函数的嵌套。上例中SUM函数为IF函数的嵌套函数。,2023/10/15,安全信息技术及管理,22,Excel数据包括文本、数值、日期、逻辑值、错误值等几种类型；公式中用一对半角双引号“”所包含的内容表示文本，数值是由09这些数字以及特定符号所组成的、可以直接比较大小和参与数学运算的数据；,数据类型,=IF(A10,2*SUM(B:B),“”),数字与数值：数字包括文本型数字和数值型数字，而数值是由负数、零或正数组成

10、的数据。,4.2.2 公式,2023/10/15,安全信息技术及管理,23,日期与时间是数值的特殊表现形式，日期用整数表示，时间用小数表示；Excel逻辑值只有TRUE和FALSE两个，返回表达式的真假值；对于无法返回正确结果的公式，Excel会显示错误提醒；常量指运算过程中自身不会改变的值，包括数值和文本。,数据类型,=1+2*3,2023/10/15,安全信息技术及管理,24,运算符,运算符是构成公式的基本元素，Excel包括以下几种类型的运算符:算术运算符：主要包含了加、减、乘、除、百分比以及乘幂等各种常规的算术运算；比较运算符：用于比较数据的大小；文本运算符：主要用于将文本字符或字符串

11、进行连接和合并；引用运算符：Excel特有的运算符，用于在工作表中产生单元格引用。,2023/10/15,安全信息技术及管理,25,运算符,2023/10/15,安全信息技术及管理,26,运算符,运算符的优先级,=9-24;=9-(-2)4;=(3+2)2+(10-4)3+32,2023/10/15,安全信息技术及管理,27,4.2.3 单元格引用,单元格引用：在公示中取用某个单元格或某个区域的数据；引用样式：A1引用；R1C1引用,2023/10/15,安全信息技术及管理,28,A1引用指的是用英文字母代表列标，用数字代表行号，由行列坐标构成单元格地址的引用；,A1引用,Excel2010最

12、大列XFD，最大行1048576。,4.2.3 单元格引用,=(A1+B1)*C12,2023/10/15,安全信息技术及管理,29,R：Row（行）；C：Column（列）;R1C1引用指的是通过行号和列号以及行列标识“R”和“C”一起来组成单元格地址引用；,R1C1引用,Excel默认引用方式为A1引用，通常情况下比R1C1方便。,4.2.3 单元格引用,=(A1+B1)*C12=(R1C1+R1C2)*R1C32,2023/10/15,安全信息技术及管理,30,区域运算符：冒号，引用一个矩形区域，冒号两端两个单元格分别是这个区域的左上角和右下角；如(A1:D10)，其目标引用区域为,引用

13、运算符,引用整行，可以省略列标，如(1:10)；引用整列，可省略行标，如(A:D)。,2023/10/15,安全信息技术及管理,31,交叉运算符：空格，连接两个单元格区域，引用这两个区域的交集部分；如(A1:D10 C6:F12)，其目标引用区域为(C6:D10),引用运算符,2023/10/15,安全信息技术及管理,32,联合运算符：逗号，连接两个单元格区域，引用这两个区域的并集；如(A1:D10,C6:F12)，其目标引用区域为:,引用运算符,2023/10/15,安全信息技术及管理,33,随着公式所在位置不同而改变单元格引用地址的方式称为“相对引用”；相对引用时，公式所在的单元格与引用对

14、象之间的行列间距始终保持一致。,相对引用,例4-6：计算某企业各月份利润。,利润=收入-成本,2023/10/15,安全信息技术及管理,34,引用对象不随公式所在单元格变化而改变，即引用对象保持固定的方式称为“绝对引用”；绝对引用时，单元格地址前使用“$”符号。,绝对引用,例4-6：计算某企业各月份收入占比。,收入占比=当月收入/全年收入,2023/10/15,安全信息技术及管理,35,同时在行号和列标前添加“$”符号，表示彻底的绝对引用，无论公式复制到哪儿，都不会改变所引用对象的地址；,绝对引用,=SUM($A$1:$A$10)*$C$1,绝对引用快捷方式：在相对引用基础上按一次；,2023

15、/10/15,安全信息技术及管理,36,只在行号或列标前添加“$”符号，表示混合引用，复制公式时有“$”符号的行或列保持不变；,混合引用,=SUM($A1:$A10),混合引用快捷方式：在相对引用基础上多次按；,2023/10/15,安全信息技术及管理,37,课堂练习练习相对引用、绝对引用、及混合引用在公式中的应用。,2023/10/15,安全信息技术及管理,38,4.2.4 公式常见错误,#当列宽不够显示数字，或者运算结果超出日期的允许范围（1900年1月1日至9999年12月31日）时，会出现此错误。,=date(1900,1,1)-20,#VALUE!当使用的参数或操作数类型错误时，会出

16、现此错误。,=sum(“Technology”),2023/10/15,安全信息技术及管理,39,#NAME?当公式中使用的字符串未加双引号，或函数名称拼写错误时，会出现此错误。,=left(Technology,3)=averge(A1:10),#REF!当单元格引用无效时，会出现此错误。,=offset(A1,-1,2),2023/10/15,安全信息技术及管理,40,4.2.5 常用函数公式,2023/10/15,安全信息技术及管理,41,4.2.6 函数应用,常规数学统计,常规数学统计主要包括求和、计数、求平均值、求最大最小值等；常用函数：SUM、COUNT、AVERAGE、MAX、M

17、IN等。,例4-7：对某公司产品销售订单进行统计。,2023/10/15,安全信息技术及管理,42,描述统计,描述统计是指通过数学方法，对数据的分布状态、数字特征和随机变量之间的关系进行估计和描述，包括集中趋势分析、离散趋势分析和相关分析；常用函数：平均值AVERAGE、标准差STDEV、标准误差STDEV/SQRT(n)、中位数MEDIAN、众数MODE、方差VAR等。,例4-8：对两组不同型号的相机快门使用寿命数据进行描述统计分析。,2023/10/15,安全信息技术及管理,43,线性回归预测,若两个变量之间形成的X/Y散点图呈线性关系，可进行线性回归预测；函数：TREND(known_y

18、s,known_xs,new_xs,const)、FORECAST(x,known_ys,known_xs)、SLOPE(known_ys,known_xs)*x+INTERCEPT(known_ys,known_xs)。,例4-9：采用三种方法对某企业产量和能耗进行线性回归，预测产量为2100时的能耗。,2023/10/15,安全信息技术及管理,44,指数回归预测,若两个变量之间变化趋势符合指数增长模型，可进行指数回归预测；函数：GROWTH(known_ys,known_xs,new_xs,const)、LOGEST(known_ys,known_xs,const,status),例4-9：对某国家人口增长进行指数回归，并预测2020年人口数。,2023/10/15,安全信息技术及管理,45,多项式拟合和预测,对于变量之间变化趋势不太明确的情况，可进行多项式拟合和预测；通项公式：y=m0+m1x+m2x2+m3x3+mnxn;函数：LINEST(known_ys,known_xs,const,status),例4-9：对某药物浓度变化趋势进行二次多项式拟合，预测15时的浓度。,2023/10/15,安全信息技术及管理,46,课堂练习对例4-9进行三次多项式拟合和预测,2023/10/15,安全信息技术及管理,47,本章结束！,