《Excel数据图》PPT课件.ppt

Excel的高级应用,主讲教师：宁德琼,一、条件格式化的应用,在Excel中对单元格数据的条件设置有两种方法，一种是基于数值条件进行设置，另一种是基于公式计算结果进行设置。 1、设置基于数值的条件格式化。如在学生成绩表中要显示优秀（大于85分），蓝色加粗效果；不及格成绩（小于60分），红色斜体效果；其余中间成绩，仍用默认字体格式。操作如下：,1、设置基于数值的条件格式化。,（1）选择表中需要设置条件格式的单元格区域；（2）运行“格式”“条件格式”命令，弹出 “条件格式”对话框，中用默认的“单元格数值”选项，在其后关系框中选择“大于或等于”等关系运算，在数字输入框中输入“85”等对应数字即可。（3）单击“格式”，在对话框中设置满足条件时单元格的格式。（4）单击“条件格式”对话框中的“添加”按钮，按照与步骤（2）-（3）的方法设置“不及格”的字体格式。,2、设置基于公式的条件格式化,例如：在图中所示的各省销售数据表中，对销售数据区域中超过平均值以上的单元格使用特殊的字体和图案。就要运用公式条件格式。方法是：（1）选择表中需要设置条件格式的单元格区域。（2） 运行“格式”“条件格式”命令，弹出 “条件格式”对话框，在“条件格式”对话框中的“条件(1)”下拉列表框中选择“公式” ，然后输入对应逻辑公式即可。（3）在公式中输入“=B2AVERAGE($B$2:$B$30)”用它来判断单元格的值是否大于所有数据的平均值。（4）就是对格式进行设置。,几个基于条件格式的公式的应用实例,例1：如表所示对周末日期进行条件格式设置。方法同上，在公式输入“=WEEKDAY（A2，2）5”,d在格式设置所需要的格式。 例2：自动显示数据区域中的前三名。 在公式中输入“=B2=LARGE($B$2:$B$100,3)”,其它设置同上。,WEEKDAY(日期序列号，返回值类型数字) 参数return_type的值 1代表星期日、二代表星期一等,LaRGE(array,K)返回数组中第K个最大值。,几个基于条件格式的公式的应用实例,设置数据表格的自动隔行着色 在公式中输入“=MOD(ROW(),2)=1”,其中MOD（）为求余数函数，ROW（）测试当前行号，它的意思是“当行号除以2的余数为1”就执行。若想实现偶数行单元格则“=MOD（ROW（），2）=0“,MOD()返回两个数相除的余数 ROW（）返回一个引用的行号,条件格式化的删除,条件格式化不再需要的时候还可以删除。如果要删除单元格的添加格式化设置，需要先选取设置有条件格式化的全部单元格，然后执行“格式”/“条件格式”命令，打开“条件格式”对话框，单击其中的“删除”按钮，在出现如图2-37所示的“删除条件格式”对话框中选择要删除的条件复选框，然后单击“确定”按钮即可。,条件格式化操作的注意事项,（1）条件格式化操作对应的“单元格格式”对话框中，可以使用的格式主要有字体的字形效果（粗体、斜体）、颜色、下划线、删除线以及边框的颜色和风格、单元格填充颜色和效果；需要注意的是：它不能定义字体或者改变字号。 （2）在Excel 2003及早期版本中，条件格式化最多只能设置3个条件。 （3）条件格式化中定义的公式必须是逻辑公式。 （4）应该将公式直接输入到“条件格式”对话框中，这样其功能才能真正得以体现。 （5）为单元格区域范围输入一个条件格式公式时， 要选择区域范围左上角单元格的引用，并且需要使用相对引用方式，以便条件格式化可以被复制应用到所有选取区域。 （6）将其他单元格复制到包含条件格式的单元格时，将会自动清除条件格式，并不给任何警告或提示。所以，对于设置有条件格式的单元格，要谨慎使用粘贴方法获取数据。 （7）当复制一个包含条件格式的单元格时，该条件格式也被复制。同样，在包含条件格式的范围内，插入行或列，将会使新的行或列也拥有相同的格式。,学生练习,对表格的测试成绩数据区进行条件格式设置：15分以上（包括15分）设置为蓝色加粗，5分以下设置为红色斜体。 2、将表格数据区各行之间的单元格设置为间隔着色效果。3、计算出表格中每个人的合计成绩，并按成绩进行名次排列（用RANK函数）,RANK函数,它是用来返回一个数值中的排位。Rank(number,ref,order)number为需要找到排位的数字；ref为包含一组数字的数组，但一般要用绝对引用；order为一数字，指明排位的方式，0或省略，按降序排列，不为0按升序排列。,次序和顺序,二、函数及其应用,常用函数及其应用 主要数学函数应用 逻辑判断函数应用 日期时间函数应用 文本函数及其应用 排序函数及其应用,函数的使用,函数输入有借助函数向导输入和手工直接输入两种方法。 1借助函数向导输入 2手工直接输入函数 多数情况下，用户对一些不熟悉的函数常常采用插入函数的方法，其实在使用“插入函数”对话框或“函数参数”对话框的时候，用户都可以通过单击对话框下方的“有关该函数的帮助”超链接或按【F1】键，进入Excel的帮助系统，以便得到该函数的帮助。 说明： Excel的帮助系统具有极其强大的功能，它比许多参考书内容更全面，讲述更清楚，实例更丰富。用户利用它能够解决在使用Excel过程中所遇到的各种问题，如Excel的新技术、疑难问题解答、专用名词术语、函数说明、函数应用实例等。一个Excel的真正用户应该会运用帮助系统，从中获取有用的资料，培养自学能力，养成“终身学习”习惯。,常用函数及其应用,1. SUM函数 2. AVERAGE函数 3. COUNT函数 4MAX函数和MIN函数 5.COUNTA函数 COUNTA返回参数列表中非空值单元格的个数。 6COUNTBLANK函数 COUNTBLANK返回指定参数中空白单元格的个数。 7COUNTIF函数 COUNTIF函数的功能是计算指定参数中满足给定条件的单元格的个数，语法格式为： COUNTIF(range,criteria) 其中，参数Range为需要计算其中满足条件的单元格数目的单元格区域；Criteria是指定的条件，其形式可以为数字、表达式或文本。如果是文本或表达式，则Criteria参数必须加上半角状态下的双引号。,常用函数应用实例：学生成绩处理,（1）应考人数的统计； （2）参考人数的统计； （3）缺考人数的统计； （4）最高分和最低分的计算 （5）计算优秀成绩（85分以上）人数及其比例。 （6）计算各门课程的及格和不及格人数及其比例,2、常用数学函数的介绍,ABS 返回数字的绝对值 INT 将数字向下舍入到最接近的整数 TRUNC 将数字的小数部分截去，返回整数。或指定保留小数的位数。 ROUND 按指定位数四舍五入某个数字 SIGN 返回数字的正负号符号，负数为了-1，正数为1，0为0。 PRODUCT 将参数数字相乘，返回乘积值 MOD 返回两数相除的余数，其结果符号与除数相同 SQRT 返回某一正数算术平方根 FACT 返回数的阶乘 POWER 返回给定数字的乘幂（与“”运算符相同） RAND 返回一个大于等于0 小于1 的随机数 RANDBETWEEN 产生位于两个指定数值之间的一个随机数(工具/加载宏/分析工具库）也可以用=RAND（）*（b-a)+a,数学函数应用实例一：利用随机函数快速获取虚拟数据,如表所示：1、平时作业数据的输入。平时作业老师评分都在10分20分之间，可用公式“=ROUND（RAND（）*10，0）+10”，按下Ctrl+Enter组合键。 期中考试成绩输入，仍采用随机数方式输入，若中考成绩全部及格，则“=60+INT（RAND（）*40）” 期末成绩在（45-95）则“=RANDBTWEEN（45，95）” 最后成绩“=SUM（C3：G3）*0.15+H3*0.25+(I3*0.3+J0.7)*0.6”,例二：按工资总额计算每种面额钞票的张数,如表所示：在100元处的单元格D2中输入100：“=INT（B2/100）”， 50：“=INT（B2-C2*100）/50） 20：”=INT(B2-C2*100-D2*50)/20“ 10: ：”=INT(B2-C2*100-D2*50-E2*20)/10 5: =INT(B2-C2*100-D2*50-E2*20-F*10)/5 2: =INT(B2-C2*100-D2*50-E2*20-F*10-G2*5)/2 等,逻辑判断函数,EXCEL 中的逻辑函数共有6个，分别是IF、AND、OR、NOT、TRUE和FALSE，另外，IS类函数作为信息判断的函数，也有逻辑判断的功能。 1IF函数 IF函数也称为条件函数，其功能是对指定的条件判断真假，根据参数的真假值，返回不同结果。数据处理中，经常利用IF函数对公式和数值进行条件检测。其语法格式如下： IF (logical_test,value_if_true,value_if_false) 其中，参数logical_test是一个条件表达式，可以是比较表达式和逻辑判断表达式，其结果返回值为TRUE和FALSE；Value_if_true表示logical_test为TRUE时的显示内容；Value_if_false表示当logical_test为FALSE时的显示内容。,逻辑判断函数,2 AND、OR、 NOT函数 Excel不同于其它一些计算机编程语言，它没有AND、OR、NOT等逻辑运算符，也就是不能进行 “A AND B”、“英语85 ”OR“ 数学85”、 “NOT 超出计划”等运算，其逻辑运算只能由逻辑函数AND、OR、NOT来实现。这三个函数的语法格式分别为： AND (logical1,logical2, ) OR (logical1,logical2,) NOT (logical) 其中，AND和OR函数中的参数Logical1, logical2, 表示待测试的条件值或表达式，其结果可为 TRUE 或 FALSE，最多不超过30个。当使用AND函数时，只有当运算对象都为TRUE的情况下，运算结果才为TRUE，否则为FALSE；当执行OR运算时，只有当运算对象都为FALSE的情况下，运算结果才为FALSE，否则为TRUE。 NOT函数中的Logical为可以计算出 TRUE 或 FALSE 的逻辑值或逻辑表达式。NOT函数的功能是对该表达式运算结果取相反值。,逻辑判断函数,3. TRUE和FALSE函数 TRUE函数和FALSE函数都是无参函数，其结果分别是返回逻辑值“TRUE ”和“FALSE”。其实，可以直接在单元格或公式中键入“TRUE”或“FALSE”值，而不使用此这两个函数。引入这两个函数主要目的是用于与其它电子表格程序兼容。,逻辑函数应用实例,如表所示在此表中，计算总分，三项成绩都合格就输入“YES”，则公式=IF(AND(C3=60,D3=60,E3=60),”YES”,”) 在部分等级中，公式=IF(F3=250,”优秀”，IF（F3=225,”良好”，IF(F3=200,中等，IF（F3=180，“及格”，“不及格”),日期和时间函数,常用日期和时间函数介绍 1. TODAY函数 TODAY函数的功能是返回当前日期的序列号。如果在输入函数前，单元格的格式为“常规”，则结果为日期格式。其语法格式为：TODAY（）。 示例：在任意一个单元格中输入公式“=TODAY（）”，都得到电脑系统的当前日期。 说明：TODAY函数是一个无参函数，但是在应用时候后面的左右括号不能漏掉。 2. YEAR函数 YEAR函数的功能是返回某日期对应的年份，返回值为 1900 到 9999 之间的整数。 其语法格式为：YEAR(serial_number)。 其中的参数Serial_number为一个日期值，其中包含要查找年份的日期。 示例：公式“=YEAR(“2008/5/15”)”返回2008。,日期和时间函数,MONTH函数 MONTH函数的功能是返回指定日期中的月份，返回值是介于 1到 12之间的整数。 其语法格式为：MONTH(serial_number)，参数同YEAR函数中的参数意义一样。 示例：公式“=MONTH(“2008/5/15“)”返回5。 4. DAY函数 DAY函数的功能是返回指定日期的天数，返回值用整数 1 到 31 表示。 其语法格式为：DAY(serial_number)，参数同YEAR函数中的参数意义一样。 示例：公式“=DAY(“2008/5/15“)”返回15。,日期和时间函数,DATE函数 DATE函数返回代表特定日期的序列号。其语法格式为：DATE(year,month,day) 其中的三个参数中： Year为年份，也可以为年份的序列号，用一到四位数字表示。 Month为月份，如果所输入的月份大于12，将从指定年份下一年的一月份开始往上加算。如：“DATE(2008,14,2) ”返回代表 2009年2月2日的序列号。 Day代表在该月份中的天数，如果 day 大于该月份的最大天数，则将从指定月份的下一月的第一天开始往上累加。如：“DATE(2008,5,50)” 将返回2008 年6月19日。 示例：在有些数据库中，日期的年、月、日数字可能分为三个字段来保存，当导入到Excel工作表后，就可以利用DATE函数将年、月、日合并成一个具体的日期数据。假设单元格A1是年份数字，B1是月份数字，C1是日数，则公式“=DATE（A1，B1，C1）”就将A1的年份数字，B1的月份数字和C1的日数，合并到一起组成了一个具体的日期数据。 说明：上面实例中，读者可能会想到利用连接符“&”将三个数字连接起来,利用公式“= A1&-&B1&-&C1”,单是这种方式得到的结果不是一个时间序列号，而是文本。,日期和时间函数应用实例,如表所示，加班超过半小时按一小时计算，低于半小时则不再计算，每小时标准20元/小时，节假日为40元/小时。 （1）、计算加班小时。公式=“INT(D3-C3)*24*60+30)/60” (2)计算星期几，则是“=WEEKDAY（B3，2）”用了“2”则返回为“6”和“7” （3）判断是否为节日，=IF(OR(F3=6,F3=7),”是”，“”）（4）计算总加班时间，则=SUMIF($A$3:$A$14,I3,$E$3:$E$14) (4)计算节日加班时间：=SUM（