第单元Excel的使用技巧.ppt

《第单元Excel的使用技巧.ppt》由会员分享，可在线阅读，更多相关《第单元Excel的使用技巧.ppt（151页珍藏版）》请在三一文库上搜索。

1、优秀精品课件文档资料,第4单元,Excel2003的使用技巧,3,学习目标 会对数据表进行数据有效性设置； 会利用相关函数对数据表中数据进行统计计算； 会对数据清单中的数据进行排序、筛选及分类汇总； 能够利用数据透视表对数据清单汇总、绘制图表,4,目录,案例1 数据填充与数据有效性设置 案例2 成绩计算与统计 案例3 成绩分析 案例4 成绩表格式化 案例5 投资理财 案例6 产品销售数据管理 案例7 教工信息统计与分析,5,任务一 学生成绩录入与数据有效性控制,在录入含有大量数据记录的过程中，不可避免地出现录入错误的现象。为了尽可能地减少录入错误，可通过对数据表单元格区域设置有效性校验条件，一

2、旦出现录入错误，系统会自动提示，以便修改。,6,案例1 数据填充与数据有效性设置,7,1案例分析 案例中（1）（2）的要求可以通过自动填充或手动填充来实现； （3）（4）中的要求可以通过“数据”菜单下的“有效性”命令来实现。,8,2相关知识点 （1）工作簿、工作表、单元格（单元格区域） Excel文档称为Excel工作簿，其扩展名是“.xls”，每一个工作簿可包含多张工作表。 工作表默认的标签名称是Sheet1、sheet2、Sheet3等。每张工作表有256列、2562=65536行，列号用字母A、B、标识，行号用1、2标识。单元格地址用列号加行号表示，如A1、A65536等。 单元格区域表

3、示：左上角单元格地址：右下角单元格地址表示，如A1:D100。,9,（2）文本型数值数据的输入 在中文Excel中，文本是指当作字符串处理的数据。对于不参与运算的数字型数据有时必须把它们作为文本型数据处理，否则可能显示不正确或造成某些数据位的丢失。如身份证号（若作为数值型数据处理，身份证最后几位数将作0处理）、课程代码06040701（若作为数值型数据左面的0将丢失）等。在输入这些作为文本的数字型数据时，应先输入英文单引号“”，再输入数字串。如06040701,10,（3）数据填充 对于工作表中同行或同列数据有规律时，可以使用填充功能快速输入数据。 单击“编辑填充向下（上）填充、向右（左）填充

4、”命令，实现数据快速复制；,11,12,单击“编辑填充序列”命令，根据需要可在“序列”对话框选择“等差序列”、“等比序列”、“日期”之一进行数据填充。,13,自定义序列 单击“工具选项”命令，在“自定义序列中添加或导入，如下图所示。,14,注意：老师在讲课时，可讲完一个知识点，接着就讲实现方法，不一定严格按照先讲完知识点后，才讲实现方法，这样效果不好。但课件还是按照这个顺序来写。 这里先讲案例（1）、（2）的实现方法。 课堂实践1,15,（4）数据的有效性 就是对某单元格区域预先设置好数据应满足的条件，一旦用户输入数据不符合有效性条件时，系统会自动弹出错误信息对话框，以提示用户修改输入数据，这

5、样可尽可能减少人工输入错误。 方法： 单击“数据有效性”命令，在弹出“数据有效性”对话框中进行各种设置，详见3、4中步骤。,16,17,3实现方法,18,数据格式在“单元格格式”对话框中设置,19,（3）”性别“列数据有效性的实现步骤：,20,（4）“成绩”列的数据有效性设置步骤：,21,注意： 删除标题E1-I1单元格的有效性设置（这里的设置不合情理）：选择E1-I1单元格区域，在如图4-5所示的数据有效性设置中，单击“全部清除”按钮，即可清除这里的有效性设置。 光标移到相关列，在相关单元格中输入任意不符合要求的值，观察有效性设置效果。,22,4课堂实践,23,5评价与总结 教师根据实践情况

6、，或强调、演示或让学生上台操作 老师或学生总结本单元的知识点,24,6课外延伸 在“数据有效性”对话框“设置”选项卡“允许”列表框中选择“自定义”选项，如何实现案例1中的有效性要求？试试看！（提示：用到逻辑函数and及or） 提示：快速输入分数及当前日期及时间数据请看备注栏内容。,25,任务二 学生成绩表的统计与分析,教师在某门课程教学结束时，通常都要对教学进行评价，对学生的平时考查成绩、期末成绩、平时表现分数等进行统计计算，最后给每一位同学一个最终成绩或等级。这些繁琐的工作其实利用Excel函数计算就很容易搞定了。,26,案例2 成绩计算与统计,27,1案例分析 Excel系统提供大量的函数

7、，给人们计算带来了极大方便。利用Sum()及Average()函数可以完成（1）、（2）中的任务；函数Count()与Countif()可以进行计数和有条件的计数；if（）函数可以进行逻辑判断，根据判定不同的结果从而返回不同的值。Sumif()函数可以进行有条件的求和，如（6）中的要求。,28,2相关知识点 Excel提供了大量的系统函数，功能非常丰富。按照其功能来划分主要有统计函数、日期与时间函数、数学与三角函数、财务函数、逻辑函数、文本函数、数据库函数等。 在输入函数时，必须以“=”开头，输入函数的一般格式为： =函数名（参数1,参数2，）,29,(1)常用函数（不一定属于模板中常用函数）

8、 SUM(number1,number2,.)，返回参数之和 AVERAGE(number1,number2,.)，返回参数的平均值 COUNT(value1,value2,.)，返回包含数字以及包含参数列表中的数字的单元格的个数 ; COUNTIF(range,criteria),返回区域中满足给定条件的单元格的个数，其中： range是统计的单元格区域；,30,criteria为确定哪些单元格将被计算在内的条件。其形式可以为数字、表达式或文本。例如，条件可以表示为32、“32”、“32”或“apples”等。 SUMIF(range,criteria,sum_range)，返回满足条件的若

9、干单元格的和，其中： Range为用于条件判断的单元格区域； Criteria为确定哪些单元格将被相加求和的条件； Sum_range是需要求和的实际单元格（区域）。,31,（2）常用数学函数,32,此外，常用数学函数还有取整函数INT、平方根函数SQRT、绝对值函数ABS、幂函数POWER、随机函数Rand等，详细请参考Excel帮助中的相关实例（在Excel中，按F1键后在“搜索”框中输入关键字，回车）。,33,（3）逻辑函数 IF(logical_test,value_if_true,value_if_false)， 执行真假值判断，根据逻辑计算的真假值，返回不同结果。其中： logic

10、al_test表示计算结果为TRUE或FALSE的任意值或表达式； Value_if_true是logical_test为TRUE时返回的值， Value_if_false是logical_test为FALSE时返回的值。 如：=if(53,”Y”,”N”)返回”Y”,而=if(530,”Y”,”N”)返回”N”。,34,AND(logical1,logical2, .) 所有参数的逻辑值为真时，返回 TRUE；只要有一个参数的逻辑值为假，即返回 FALSE。 如：=AND（53, 10050）返回TRUE， 而公式：=AND（53, 100500）返回 FALSE。,35,OR(logica

11、l1,logical2, .) 所有参数的计算值均为FALSE时，才返回FALSE；只要有一个参数的逻辑值为真时，即返回 TRUE。如： 公式：=OR（53, 100500）返回TRUE， 公式：=OR（530, 100500）返回 FALSE。,36,（4）文本与数据函数 LEN(text)，返回文本字符串中的字符数。 如：=len(“abcd“)返回4, =LEN(“英语abc“)返回5 MID(text,start_num,num_chars) 返回文本字符串中从指定位置开始的特定数目的字符串。 =MID(“英语abc123“,3,3)返回“abc“。,37,TEXT(value,for

12、mat_text),将数值转换为按指定数字格式表示的文本。 如：=TEXT(20000,“$0.00”)，返回值“$20000.00”。 VALUE(text)，返回由数字文本字符串转换成的数字 说明：Text可以是MicrosoftExcel中可识别的任意常数、日期或时间格式。如果Text不是这些格式的数据，则函数VALUE返回错误值#VALUE!。 如：=VALUE(“$1,000“)，返回数值1000。,38,常用的文本函数还有LOWER(将文本转换成大写形式)、UPPER（将文本转换成大写形式）、TRIME（除了单词之间的单个空格外，清除文本中所有的空格）等，更多文本函数请参考帮助 “

13、文本与数据函数”中有关函数。,39,3实现方法 （1）将光标定位于J2单元格中，单击工具栏上的“自动求和”按钮，回车。再拖动单元格J2右下角的填充柄直到J22单元格。 （2）将光标定位于E23单元格中 ，单击工具栏上“自动求和”右侧的下拉按钮，单击“平均值”命令， 圈选求平均值的单元格区域后回车 ，将公式 横向填充到F23：I23即可。,40,（3）操作步骤：,41,（4）操作步骤：,42,（5）的操作步骤：,43,（6）的操作步骤：,44,有问题，查帮助,45,4课堂实践,46,5评价与总结（视情况而定） 第一组同学（2位为一组）主动上台演示（1）-（3）。 第二组同学主动上台演示（4）-（

14、6）。 鼓励同学总结本案例解决思路，学生或老师补充。,47,6课外延伸 （1）时间日期函数 Now() 返回当前日期和时间所对应的序列号或日期时间数据（与单元格格式有关）。 如：=NOW(),48,TODAY() 返回当前日期的序列号或日期格式数据。如= TODAY()。 DATE(year,month,day) 返回代表特定日期的序列号。如果在输入函数前，单元格格式为“常规”，则结果将显示为日期数据。如：=date(2010,11,12)。,49,YEAR(serial_number)，返回某日期对应的年份。 =year(now() 与year函数具有相同参数的日期时间函数还有month、d

15、ay、hour、minute、time函数。 （2）信息函数 请参见教材,50,（3）课外练习,51,案例3 成绩分析,【场景描述】,52,1案例分析,53,2相关知识点 （1）复制、移动单元格（区域）中的内容,54,选择性粘贴 单击“编辑选择性粘贴”命令在“选择性粘贴”对话框中选择相关选项即可，如图4-13所示。,以案例（1）示范即可,55,（3）单元格的引用 单元格的绝对引用：是指对含有公式的单元格进行复制，不论复制到什么位置，公式中所引用的单元格地址都不发生变化。 如公式：=SUM($E$2:$I$2)，就是对单元格的绝对引用实例。绝对引用单元格地址的列号和行号前都加“$”符号，如$E$

16、2、$A$1等。,56,单元格的相对引用：是指将包含公式的单元格复制到其他单元格时，公式中所引用的单元格地址也随之发生变化。 例如，将工作表中J2单元格中的计算公式SUM(E2:I2)复制到J10单元格中就变成了SUM(E10:I10)。,57,相对引用单元格地址变化规律： 相对引用的公式被复制到其他单元时，公式所在单元格与引用单元格之间仍保持行列相对位置关系不变，相当于对单元格做平移操作。于是相对引用单元格地址变化有如下规律： 原行号+行地址偏移量新行地址 原列号+列地址偏移量新列地址,58,单元格的混合引用：是指在公式引用的单元格地址中，既有绝对地址引用又有相对地址引用。 如本例单元格K2

17、中有公式“=SUM(N$3:R$3)”，当将公式复制到L2中时，公式将变为“=SUM(O$3:S$3)”，对单元格列的引用为相对引用，对行的引用为绝对引用。公式复制时，相当于向右平移一个单元格的位置，绝对引用的地址不变。 思考：什么时候用绝对、相对及混合引用？,59,3实现方法,60,（3）在J2单元格中插入rank 函数，由于公式在填充时，总分随着学生而变，而每个分数都要与全体分数相比较，所以在Number中使用相对引用，Ref 中行号使用绝对引用 ，设置如下图所示：,61,62,4课堂实践 （1）打开“成绩计算2.xls”文件，按本案例各项要求，完成各步计算。 5评价与总结,63,6课外延

18、伸 请打开工作薄文件CFB.XLS，用公式完成绘制九九乘法表： 请在B2:J10区域制作，要求在B2单元格输入公式，然后复制到其他单元格.,64,案例4 成绩表格式化,【场景描述】 张老师对成绩表的计算、统计等工作都已完成，他还要想对成绩表进行格式化工作，如设置单元格数据格式、字体、对齐方式、边框底纹等，还需要将那些不及格的同学用红颜色标示出来。打开“成绩表3.xls”工作簿文件，按照张老师格式化工作表的要求完成如下设置：,65,66,1案例分析 本案例的前4个问题主要是单元格（区域）的格式化问题，在“单元格格式”对话框中可以完成；（5）中主要是条件格式问题；最后一个问题是对工作表复制、移动等

19、操作,67,2相关知识点 （1）单元格的格式化 单击“格式单元格”命令，弹出“单元格格式”对话框，如图4-16所示。在该对话框中，可以对单元格（区域）数据的数字显示方式、对齐方式、字体、边框、图案等进行设置。,68,数字格式：在“数字”选项卡中，可为单元格数据设置显示格式，如：常规、数值、会计专用、日期、文本、特殊等模式，还要自定义显示格式，如图4-16所示。 对齐方式：在“对齐”选项卡中，可为单元格数据设置对齐方式，如：水平对齐、垂直对齐、自动换行、合并单元格等。,69,（2）条件格式 就是根据本单元格或其它单元格中的数据是否满足一定条件来设置单元格的数据格式，如将单元格区域E2：I23中小

20、于60的数据用红色显示，若单元格内数据值小于60，则字体显示为红色，否则不变。,70,操作步骤：选择要设置的单元格（区域）单击“格式条件格式”命令在“条件格式”对话框中，构造要满足的条件单击“格式”按钮在弹出的对话框中设置字体、边框、图案等,71,3实现方法,72,73,（2）选择字段名区域A2：K2单击右键单击快捷菜单中的“设置单元格格式”命令在“单元格格式”对话框的“字体”选项卡中设置字体：宋体、加粗、字号大小12；在“对齐”选项卡中设置文本对齐方式：“水平对齐”居中、“垂直对齐”居中；选择单元格区域B3：B23，同前面操作一样，在“对齐”选项卡中的“水平对齐”选项中，从下拉列表框中选择“

21、分散对齐”。,74,（3）选择单元格区域E3：J23单击右键，单击“设置单元格格式”命令在“单元格格式”对话框的“数字”选项卡的“分类”列表中选择“数值”，在“小数位数”框中设置为1，如图4-16所示。 选择单元格区域D3：D23，同样在“单元格格式”对话框“数字”选项卡的“分类”列表中选择“日期”，在右边“类型”框中选择“2001-3-14”，“确定”。,75,（4）选择单元格区域A2：K23，在如图4-16所示的“单元格格式”对话框中，选择“边框”选项卡，在“线条”框的“样式”中选粗实线，在“颜色”列表框中单击红色，单击“外边框”按钮，同样，选择蓝色细实线，单击“内部”按钮，单击“确定”按

22、钮。,76,（5）选择单元格区域E2：I23单击“格式条件格式”命令在“条件格式”对话框的第1个列表框中选择“单元格数值”，在第2个列表框中选择运算符“小于”，在第3个框中输入60单击“格式”按钮在“单元格格式”的“字体”选项卡 “颜色”框中选择红色“确定”，如图4-17”所示。,77,78,79,4课堂实践 （1）实践案例（1）-（4）； （2）实践案例（5）-（7）； 5评价与总结 第一组同学（2位为一组）主动上台演示（1）-（4）操作中部分操作； 第二组同学上台演示（5）-（7）全部或部分操作，同学或老师点评； 鼓励同学总结本案例主要知识要点，学生或老师补充。,80,6课外延伸,81,案

23、例5 投资理财与贷款计算,【场景描述】,82,1案例分析 在本案例中，可应用PMT（）函数计算基于固定利率及等额分期付款方式，返回贷款的每期付款额来解决(1)中的问题。FV()函数可用于解决基于固定利率及等额分期付款方式，返回某项投资的未来值，可解决(2)中的问题。函数PV（）函数返回投资的现值。现值为一系列未来付款的当前值的累积和，可解决(3)中的问题。,83,2相关知识点 （1）PV(Rate,Nper,Pmt,Fv,Type)函数 返回投资的现值。现值为一系列未来付款的当前值的累积和。例如，借入方的借入款即为贷出方贷款的现值。 其中各参数意义如下：,84,注意：这里将参数与英文单词联系起

24、来就好记了,85,3实现方法 （1）打开电子工作簿文件“投资理财.xls”，光标定位于B8单元格中，单击数据编辑栏上的插入函数“”按钮，弹出“插入函数”对话框，在“搜索函数”框中输入“PMT”单击“转到”按钮，单击“确定”。在弹出的“函数参考”对话框的Rate中输入A4/12，在Nper框中输入A5*12，在PV框中输入1000或A6，如图4-22所示，单击“确定”按钮。,86,87,（2）光标定位于B19中，单击数据编辑栏上的插入函数“”按钮，弹出“插入函数”对话框，在“搜索函数”框中输入“FV”，单击“转到”按钮，“确定”。在弹出的“函数参考”对话框的Rate中输入A14/12，在Nper

25、框中输入A15*12，在PMT框中输入-2000，在Type框中输入1，如图4-23所示，单击“确定”按钮。在B20中，输入公式“=A16+2000*5*12+(B19-A16-2000*60)*0.8”（即成本+80%的利息），即可算出税后本息总数。,88,89,（3）光标定位于B30中，单击数据编辑栏上的插入函数“”按钮，弹出“插入函数”对话框，在“搜索函数”框中输入“PV”单击“转到”按钮，单击“确定”。在弹出的“函数参考”对话框的Rate中输入A26/12，在Nper框中输入A27*12，在PMT框中输入-A28，如图4-24所示，单击“确定”按钮。,90,91,4课堂实践 实践本案例

26、 5评价与总结 鼓励同学主动报告各小题的计算结果，若不正确错在什么地方？ 鼓励同学总结本案例主要知识要点，学生或老师补充。 6课外延伸 访问某企业财务室，咨询他们平时在实际财务工作还常用到哪些财务函数，试列举一个案例。 研究IPMT（）函数、ISPMT（）及NPV（）的用法及意义，试举例。,92,案例6 产品销售数据管理,【场景描述】 乐佳电器公司主要经营各种家用电器业务，销售业务遍及广东省的东南西北各地区。公司总经理为了及时掌握各个业务员及各种电器的销售情况，不时地要求销售部管理人员进行数据统计汇总等数据管理、分析工作。经常性数据分析管理工作可以归纳为以下几个方面： 电子工作簿文件“电器销售

27、表.xls”是公司3、4季度的电器产品销售数据清单，打开此文件按下面要求完成各项任务：,93,94,95,1案例分析 本案例主要涉及数据清单的排序、自动筛选、高级筛选、分类汇总及透视表的相关功能。利用“数据”菜单下的“排序”、“筛选”、“分类汇总”、“数据透视表和数据透视图”命令可以完成本案例中的任务。,96,1.相关知识点 （1）数据清单 Excel数据清单是一个特殊的表格，是包含列标题的一组连续数据行的工作表。数据清单由两部组成，即表结构和纯数据。表结构就是数据清单中的第一行，即为列标题。数据清单的每一列称为一个字段，列标题称为字段名，从数据清单第二行开始的每一行都称为一条记录。如图4-2

28、5所示。,97,单击“数据记录单”命令，弹出记录编辑对话框，如图4-26所示。,98,（2）数据清单的排序 按一个字段排序（单击工具上”升序“、降序按钮”） 按多个字段排序 单击“数据排序”命令，在“排序”对话框中，选择“主要关键字”、“升序/降序”，还可选择“次要关键字”、“升序/降序”，依此类推，如图4-27所示。,99,以本案 例（1）进行演示。 （3）数据筛选 自动筛选 自动筛选只能将筛选出的记录在原位置上显示，并且一次只能对一个字段设置筛选条件。若筛选涉及到多个字段条件时，必须经过多次自动筛选才完成筛选任务。详细操作步骤见实现方法。 如：筛选销售量在3000以上（包括3000），销售

29、员为姓王的记录。,100,高级筛选 高级筛选可一次完成筛选条件较为复杂的记录筛选。 高级筛选首先要创建筛选条件区域，如果筛选时要求多个条件同时满足，则称这些条件为“与”的关系；如果筛选只要求满足多个条件之一时，则称这些条件为“或”关系 创建条件区域的步骤： 将条件涉及的字段复制到某一行中，若某一字段涉及多次，就复到多列 在复制字段的下方，设置条件,101,同行的条件表示“与”，不同行的条件表示“或” 条件区域设置如图4-28所示，左边条件区域中两个条件是与的关系，右边条件区域中的两个是或的关系。图4-28 条件区域,102,提示： 直接跳转到实现方法部分（4）、（5）部分讲解演示、实践。 跳转

30、：课堂实践（4） 老师可根据学生掌握情况重构练习。,103,（4）分类汇总 分类汇总就是先将记录按某个字段进行分类，然后在每一类进行汇总（如计数、求和、求平均值等），如汇总不同职称教师在某年的发表论文数。分类汇总的操作步骤如下： 先按分类字段排序光标定位于任一字段名上单击“数据分类汇总”命令，直接跳转实现方法（2）。,104,跳转：课堂实践部分 （5）数据透视表 分类汇总只能解决按一个字段分类汇总问题，如要解决按多个字段分类汇总的问题分类汇总已无能为力，如统计不同职称、不同学历、不同性别教师发表论文数。透视表制作步骤如下： 单击“数据数据透视表和数据透视图”命令“数据透视表和数据透视图向导”

31、， 转操作步骤见实现方法。,105,3实现方法 （1）排序操作步骤：,106,107,（2）分类汇总的操作步骤：,108,109,110,（3）自动筛选的操作步骤：,111,112,（4）高级筛选操作步骤： 选择sheet4工作表，在单元格区域I1：K2设置筛选条件如图4-35所示。 然后进行高级筛选。,113,（5）高级筛选操作步骤： 选择sheet5工作表，在单元格区域：I1：K3设置筛选条件如图4-36所示 ，然后进行高级筛选，过程如下图所示：,114,（6）透视表创建与编辑操作步骤：,115,116,117,118,119,（7）页面设置操作步骤：,120,4课堂实践,121,5评价与

32、总结 6课外延伸 根据本案例（6）中的透视表，年销售业绩最好（订货量最大）与销售业绩最差销售员所在行分别用的图案颜色分别设置为浅绿与浅黄色。订货量最大产品与订货量最少产品名称分别用红色和蓝色显示。 用函数方法统计不同销售员不同产品的订货量。,122,案例7 教工信息统计与分析,【场景描述】 志远学院是一所教师众多的高校，人力资源部门经常需要对教师队伍的学历、职称构成、科研论文等数据进行统计和分析，以便为领导引进人才、科学用人提供科学依据。教师人数众多，统计和管理大量数据工作是很辛苦的事情。但如果能熟练地应用Excel中的各种统计函数、数据透视表及图表功能，一定能起到事半功倍的效果。下面就以学院

33、部分教师的科研数据为例，以窥探几分人力资源部门的部分日常管理工作。,123,打开“teacher_inf.xls”文件，完成下列各项工作：,124,125,1案例分析 本案例（1）、（2）等涉及到数据库统计函数知识点，如统计博士或教授发表论文之和等；（3）中的问题可以用前面已学过的条件格式知识来解决；（4）中涉及到数据查询函数vlookup 与lookup的应用问题；（5）可以用前面学习过Countif 、Sumif等进行统计，也可以用本案例学习的数据库函数进行统计。（6）、（7）主要涉及到创建和编辑图表有关知识；最后一个问题涉及到保护工作簿及工作表问题。,126,2相关知识点 （1）数据库函

34、数 数据库函数专门用于数据清单带有附加统计条件的一类统计函数，在实际统计工作中有着广泛的应用。常用的数据库函数有DCOUNTA（DCOUNT）、DSUM、DMAX、DMIN、DAVERAGE等，这些函数的参数都是Database,Field,Criteria,127,DCOUNTA(database,field,criteria) 返回数据库或列表的列中满足指定条件的非空单元格个数。其中： Database构成列表或数据库的单元格区域，如A1：E31； Field指定函数所使用的数据列。Field可以是文本，如“职称”，也可以是代表列表中数据列位置的数字：1表示第一列，2表示第二列，等等； C

35、riteria为一组包含给定条件的单元格区域，如H1:H2。,128,DSUM(database,field,criteria) 返回列表或数据库的列中满足指定条件的数字之和。参数说明同DOUNTA。 DMAX(database,field,criteria)，返回列表或数据库的列中满足指定条件的最大数值。参数说明同DOUNTA。 DMIN(database,field,criteria)，返回列表或数据库的列中满足指定条件的最小数。 DAVERAGE(database,field,criteria) 返回列表或数据库中满足指定条件的列中数值的平均值。,129,跳转实现方法（1） 修改案例中数

36、据库函数的名字，观看统计结果。 （2）查找函数 LOOKUP(lookup_value,lookup_vector,result_vector) 返回第二个单行区域或单列区域中相同位置的数值。其中：,130,Lookup_value为函数LOOKUP在第一个向量中所要查找的数值。Lookup_value可以为数字、文本、逻辑值或包含数值的名称或引用。 Lookup_vector为只包含一行或一列的区域。如，查找发表论文最多是哪位教师：=LOOKUP(MAX(E2:E31),E2:E31,A2:A31)，返回第一个向量中最大值对应行与第二个向量所对应列交叉单元格的值“李文如”，要求E列排序。,1

37、31,提示： Lookup_vector的数值必须按升序排序：.、-2、-1、0、1、2、.、A-Z、FALSE、TRUE；否则，函数LOOKUP不能返回正确的结果。 如果函数LOOKUP找不到lookup_value，则查找lookup_vector中小于或等于lookup_value的最大数值。 如果lookup_value小于lookup_vector中的最小值，函数LOOKUP返回错误值#N/A。,132,跳转实现方法（4） 用“teacher_inf.xls” 进一步练习LOOKUP函数的用法。 （3）图表的创建与编辑 图表的创建 操作步骤：选择数据区域单击工具栏上的“图表向导”按钮

38、在“图表向导4步骤之1图表类型”对话框中选择图表类型及子类型,133,图表的编辑 跳转本案例实现方法（6）、（7）,134,3实现方法 （1）操作步骤：,135,同样，在I8输入公式“=DAVERAGE(A1:E31,E1,I1:J2)”求女副教发表论文的平均值。,136,（2）操作步聚： 单击K2单元格，用DMAX求出“性别”为女发表论文的最大值64； 以“性别”为女且发表论文“篇数”为64作为条件进行高级筛选，将筛选的结果复制到A33开始的单元格即可。,137,（3）操作步聚： 选择单元格区域A2：E31单击“格式条件格式”命令“条件1”左框中选择“公式”，右框中输入“=AND($B2=”

39、女”, $E2=MXA($E$2:$E$31)”单击“格式”按钮设置单元格底纹为“浅绿色”，如图4-46所示“确定”。,138,（4）操作步聚：,139,140,（5） 操作步骤： 单击工作表标签sheet2，在G9：J10中设置条件如（职称：教授等），用DCOUNTA计算教授、副教授、讲师、助教人数，如H2中输入计算教授人数公式：“=DCOUNTA(A1:D31,D1,G9:G10)”。其实用countif函数更方便。 计算不同职称发表论文平均值用Daverage函数.这里用公式“SUMIF/人数”计算教授发表论文的平均值：在单元格J2中输入公式：“=SUMIF(D$1:E$31,G2,E$

40、1:E$31)/H2”，然后将公式向下填充直到J5即可，保留1位小数点。 计算不同职称人数所占百分比：在I2中输入公式“=H2/SUM(H$2:H$5)*100”，回车后将公式向下填充直到I5即可。如图4-49所示。,141,（6）绘制三维饼图的操作步骤：,图4-49 统计,142,143,（7）绘制三维簇状柱形图及编辑操作步骤：,144,145,（8）保护工作簿、工作表的操作步骤：,146,4课堂实践 （1）实践本案例（1）、（2）。 （2）实践本案例（3）、（4）、（5）。 （3）实践本案例（6）、（7）、（8）。 （4）为了方便对后面数据的编辑，需要对数据窗口进行拆分，使得在编辑后面的记

41、录时始终看到Sheet3中的第一行列标题。,147,5评价与总结 6课外延伸 （1）频率分析 在实际统计工作中，常需要分析一批数据在各个区间的分布情况，如老师分析考试成绩在各个分数段的里的人数，销售人员统计销售数据的分布情况等。这时需要用到Excel 中的数据分析工具或频度分析函数。,148,频度分析函数FREQUENCY(data_array,bins_array) 计算一列垂直数组在给定各区间段内数据的分布频率。由于函数 FREQUENCY 返回一个数组，所以必须以数组公式的形式输入。其中： Data_array 为用于计算频率的数组或对一组数值的引用； Bins_array为间隔的数组或对间隔的引用，也就是分隔区间的分段点，由每个数据区间的最大值一组数据构成。 如：在本案例数据表Sheet3中统计发表论文数在09 、1019、2039、4059、60篇以上各区间里的人数。,149,150,数据分析命令,151,（2）课外练习 用FREQUENCY函数和数据分析两种方法统计“6.课外延伸”中频度分析问题。 利用的统计结果，以“论文区间”、“统计人数”两列数据为数据源，创建“簇状柱形图”。 利用透视表的方法统计不同称职、不同学历发表论文的平均数，然后利用统计结果创建“簇状柱形图”。,