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

SafetyInformationTechnologyandManagement,Excel数据分析与处理第四章条件格式与函数公式,2019/11/26,安全信息技术及管理,1,安全信息技术及管理,4.1条件格式,2019/11/26,安全信息技术及管理,2,4.1.1条件格式的作用,通过一些特征条件找到特定的数据；用直观的方法展现数据规律；对单元格数据进行判别，并用特殊定义的格式来显示；某种程度上可实现数据的可视化。,2019/11/26,安全信息技术及管理,3,条件格式菜单,2019/11/26,安全信息技术及管理,4,根据文本特征标识单元格,例4-1：在某公司产品销售记录表中，找到“产品型号”字段中包含“EF30mmUSM”的记录，并标识出来。,4.1.2条件格式应用,2019/11/26,安全信息技术及管理,5,根据数值大小标识特定范围,例4-1：在某公司产品销售记录表中，找到并标识出“销售数量”前3名的记录。,对于数值型数据，条件规则包括：大于、小于、介于、值最大/小的10项、值最大/小的10%项、高/低于平均值等。,2019/11/26,安全信息技术及管理,6,课堂练习采用不同“单元格规则”和“项目选取规则”，对例4-1中的销售记录表中的“销售数量”进行条件格式标识；与排序和筛选进行对比,2019/11/26,安全信息技术及管理,7,标识重复值或唯一值,练习：分别标识出例4-2中“销售人员”列重复出现的人员姓名以及只出现一次的姓名。,操作流程：条件格式突出显示单元格规则重复值设置格式。,2019/11/26,安全信息技术及管理,8,自定义条件规则,例4-3：标识出某公司员工信息表中“年龄”在40岁以下的所有“工程师”的整行记录。,自定义功能可设计更加复杂的条件格式。,条件公式：=($E20为第1个参数logical_test，“正数”为第2个参数value_if_true，第3个参数省略；除了某些函数部分参数可省略外，通常情况下不能输入少于或多于函数自身所必需的参数个数。,2019/11/26,安全信息技术及管理,21,函数参数可以是常量、单元格引用、计算式或其他函数；例：,Excel函数特征,=IF(A10,SUM(B:B),“”),当一个函数作为另一个函数的参数使用时，称为函数的嵌套。上例中SUM函数为IF函数的嵌套函数。,2019/11/26,安全信息技术及管理,22,Excel数据包括文本、数值、日期、逻辑值、错误值等几种类型；公式中用一对半角双引号“”所包含的内容表示文本，数值是由09这些数字以及特定符号所组成的、可以直接比较大小和参与数学运算的数据；,数据类型,=IF(A10,2*SUM(B:B),“”),数字与数值：数字包括文本型数字和数值型数字，而数值是由负数、零或正数组成的数据。,4.2.2公式,2019/11/26,安全信息技术及管理,23,日期与时间是数值的特殊表现形式，日期用整数表示，时间用小数表示；Excel逻辑值只有TRUE和FALSE两个，返回表达式的真假值；对于无法返回正确结果的公式，Excel会显示错误提醒；常量指运算过程中自身不会改变的值，包括数值和文本。,数据类型,=1+2*3,2019/11/26,安全信息技术及管理,24,运算符,运算符是构成公式的基本元素，Excel包括以下几种类型的运算符:算术运算符：主要包含了加、减、乘、除、百分比以及乘幂等各种常规的算术运算；比较运算符：用于比较数据的大小；文本运算符：主要用于将文本字符或字符串进行连接和合并；引用运算符：Excel特有的运算符，用于在工作表中产生单元格引用。,2019/11/26,安全信息技术及管理,25,运算符,2019/11/26,安全信息技术及管理,26,运算符,运算符的优先级,=9-24;=9-(-2)4;=(3+2)2+(10-4)3+32,2019/11/26,安全信息技术及管理,27,4.2.3单元格引用,单元格引用：在公示中取用某个单元格或某个区域的数据；引用样式：A1引用；R1C1引用,2019/11/26,安全信息技术及管理,28,A1引用指的是用英文字母代表列标，用数字代表行号，由行列坐标构成单元格地址的引用；,A1引用,Excel2010最大列XFD，最大行1048576。,4.2.3单元格引用,=(A1+B1)*C12,2019/11/26,安全信息技术及管理,29,R：Row（行）；C：Column（列）;R1C1引用指的是通过行号和列号以及行列标识“R”和“C”一起来组成单元格地址引用；,R1C1引用,Excel默认引用方式为A1引用，通常情况下比R1C1方便。,4.2.3单元格引用,=(A1+B1)*C12=(R1C1+R1C2)*R1C32,2019/11/26,安全信息技术及管理,30,区域运算符：冒号，引用一个矩形区域，冒号两端两个单元格分别是这个区域的左上角和右下角；如(A1:D10)，其目标引用区域为,引用运算符,引用整行，可以省略列标，如(1:10)；引用整列，可省略行标，如(A:D)。,2019/11/26,安全信息技术及管理,31,交叉运算符：空格，连接两个单元格区域，引用这两个区域的交集部分；如(A1:D10C6:F12)，其目标引用区域为(C6:D10),引用运算符,2019/11/26,安全信息技术及管理,32,联合运算符：逗号，连接两个单元格区域，引用这两个区域的并集；如(A1:D10,C6:F12)，其目标引用区域为:,引用运算符,2019/11/26,安全信息技术及管理,33,随着公式所在位置不同而改变单元格引用地址的方式称为“相对引用”；相对引用时，公式所在的单元格与引用对象之间的行列间距始终保持一致。,相对引用,例4-6：计算某企业各月份利润。,利润=收入-成本,2019/11/26,安全信息技术及管理,34,引用对象不随公式所在单元格变化而改变，即引用对象保持固定的方式称为“绝对引用”；绝对引用时，单元格地址前使用“$”符号。,绝对引用,例4-6：计算某企业各月份收入占比。,收入占比=当月收入/全年收入,2019/11/26,安全信息技术及管理,35,同时在行号和列标前添加“$”符号，表示彻底的绝对引用，无论公式复制到哪儿，都不会改变所引用对象的地址；,绝对引用,=SUM($A$1:$A$10)*$C$1,绝对引用快捷方式：在相对引用基础上按一次；,2019/11/26,安全信息技术及管理,36,只在行号或列标前添加“$”符号，表示混合引用，复制公式时有“$”符号的行或列保持不变；,混合引用,=SUM($A1:$A10),混合引用快捷方式：在相对引用基础上多次按；,2019/11/26,安全信息技术及管理,37,课堂练习练习相对引用、绝对引用、及混合引用在公式中的应用。,2019/11/26,安全信息技术及管理,38,4.2.4公式常见错误,#当列宽不够显示数字，或者运算结果超出日期的允许范围（1900年1月1日至9999年12月31日）时，会出现此错误。,=date(1900,1,1)-20,#VALUE!当使用的参数或操作数类型错误时，会出现此错误。,=sum(“Technology”),2019/11/26,安全信息技术及管理,39,#NAME?当公式中使用的字符串未加双引号，或函数名称拼写错误时，会出现此错误。,=left(Technology,3)=averge(A1:10),#REF!当单元格引用无效时，会出现此错误。,=offset(A1,-1,2),2019/11/26,安全信息技术及管理,40,4.2.5常用函数公式,2019/11/26,安全信息技术及管理,41,4.2.6函数应用,常规数学统计,常规数学统计主要包括求和、计数、求平均值、求最大最小值等；常用函数：SUM、COUNT、AVERAGE、MAX、MIN等。,例4-7：对某公司产品销售订单进行统计。,2019/11/26,安全信息技术及管理,42,描述统计,描述统计是指通过数学方法，对数据的分布状态、数字特征和随机变量之间的关系进行估计和描述，包括集中趋势分析、离散趋势分析和相关分析；常用函数：平均值AVERAGE、标准差STDEV、标准误差STDEV/SQRT(n)、中位数MEDIAN、众数MODE、方差VAR等。,例4-8：对两组不同型号的相机快门使用寿命数据进行描述统计分析。,2019/11/26,安全信息技术及管理,43,线性回归预测,若两个变量之间形成的X/Y散点图呈线性关系，可进行线性回归预测；函数：TREND(known_ys,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时的能耗。,2019/11/26,安全信息技术及管理,44,指数回归预测,若两个变量之间变化趋势符合指数增长模型，可进行指数回归预测；函数：GROWTH(known_ys,known_xs,new_xs,const)、LOGEST(known_ys,known_xs,const,status),例4-9：对某国家人口增长进行指数回归，并预测2020年人口数。,2019/11/26,安全信息技术及管理,45,多项式拟合和预测,对于变量之间