条件格式与函数.ppt

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