Excel数据图

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）在Excel2003及早期版本中，条件格式化最多只能设置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函数4．MAX函数和MIN函数5.COUNTA函数COUNTA返回参数列表中非空值单元格的个数。6．COUNTBLANK函数COUNTBLANK返回指定参数中空白单元格的个数。7．COUNTIF函数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类函数作为信息判断的函数，也有逻辑判断的功能。1．IF函数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等逻辑运算符，也就是不能进行“AANDB”、“英语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.