《计算机应用基础教程-电子教案(新)-李久仲 第4单元 Excel2003的使用技巧》由会员分享，可在线阅读，更多相关《计算机应用基础教程-电子教案(新)-李久仲 第4单元 Excel2003的使用技巧（151页珍藏版）》请在金锄头文库上搜索。
1、第4单元,Excel2003的使用技巧,2,学习目标 会对数据表进行数据有效性设置； 会利用相关函数对数据表中数据进行统计计算； 会对数据清单中的数据进行排序、筛选及分类汇总； 能够利用数据透视表对数据清单汇总、绘制图表,3,目录,案例1 数据填充与数据有效性设置 案例2 成绩计算与统计 案例3 成绩分析 案例4 成绩表格式化 案例5 投资理财 案例6 产品销售数据管理 案例7 教工信息统计与分析,4,任务一 学生成绩录入与数据有效性控制,在录入含有大量数据记录的过程中，不可避免地出现录入错误的现象。为了尽可能地减少录入错误，可通过对数据表单元格区域设置有效性校验条件，一旦出现录入错误，系统会
2、自动提示，以便修改。,5,案例1 数据填充与数据有效性设置,6,1案例分析 案例中（1）（2）的要求可以通过自动填充或手动填充来实现； （3）（4）中的要求可以通过“数据”菜单下的“有效性”命令来实现。,7,2相关知识点 （1）工作簿、工作表、单元格（单元格区域） Excel文档称为Excel工作簿，其扩展名是“.xls”，每一个工作簿可包含多张工作表。 工作表默认的标签名称是Sheet1、sheet2、Sheet3等。每张工作表有256列、2562=65536行，列号用字母A、B、标识，行号用1、2标识。单元格地址用列号加行号表示，如A1、A65536等。 单元格区域表示：左上角单元格地址：
3、右下角单元格地址表示，如A1:D100。,8,（2）文本型数值数据的输入 在中文Excel中，文本是指当作字符串处理的数据。对于不参与运算的数字型数据有时必须把它们作为文本型数据处理，否则可能显示不正确或造成某些数据位的丢失。如身份证号（若作为数值型数据处理，身份证最后几位数将作0处理）、课程代码06040701（若作为数值型数据左面的0将丢失）等。在输入这些作为文本的数字型数据时，应先输入英文单引号“”，再输入数字串。如06040701,9,（3）数据填充 对于工作表中同行或同列数据有规律时，可以使用填充功能快速输入数据。 单击“编辑填充向下（上）填充、向右（左）填充”命令，实现数据快速复制
4、；,10,11,单击“编辑填充序列”命令，根据需要可在“序列”对话框选择“等差序列”、“等比序列”、“日期”之一进行数据填充。,12,自定义序列 单击“工具选项”命令，在“自定义序列中添加或导入，如下图所示。,13,注意：老师在讲课时，可讲完一个知识点，接着就讲实现方法，不一定严格按照先讲完知识点后，才讲实现方法，这样效果不好。但课件还是按照这个顺序来写。 这里先讲案例（1）、（2）的实现方法。 课堂实践1,14,（4）数据的有效性 就是对某单元格区域预先设置好数据应满足的条件，一旦用户输入数据不符合有效性条件时，系统会自动弹出错误信息对话框，以提示用户修改输入数据，这样可尽可能减少人工输入错
5、误。 方法： 单击“数据有效性”命令，在弹出“数据有效性”对话框中进行各种设置，详见3、4中步骤。,15,16,3实现方法,17,数据格式在“单元格格式”对话框中设置,18,（3）”性别“列数据有效性的实现步骤：,19,（4）“成绩”列的数据有效性设置步骤：,20,注意： 删除标题E1-I1单元格的有效性设置（这里的设置不合情理）：选择E1-I1单元格区域，在如图4-5所示的数据有效性设置中，单击“全部清除”按钮，即可清除这里的有效性设置。 光标移到相关列，在相关单元格中输入任意不符合要求的值，观察有效性设置效果。,21,4课堂实践,22,5评价与总结 教师根据实践情况，或强调、演示或让学生上
6、台操作 老师或学生总结本单元的知识点,23,6课外延伸 在“数据有效性”对话框“设置”选项卡“允许”列表框中选择“自定义”选项，如何实现案例1中的有效性要求？试试看！（提示：用到逻辑函数and及or） 提示：快速输入分数及当前日期及时间数据请看备注栏内容。,24,任务二 学生成绩表的统计与分析,教师在某门课程教学结束时，通常都要对教学进行评价，对学生的平时考查成绩、期末成绩、平时表现分数等进行统计计算，最后给每一位同学一个最终成绩或等级。这些繁琐的工作其实利用Excel函数计算就很容易搞定了。,25,案例2 成绩计算与统计,26,1案例分析 Excel系统提供大量的函数，给人们计算带来了极大方
7、便。利用Sum()及Average()函数可以完成（1）、（2）中的任务；函数Count()与Countif()可以进行计数和有条件的计数；if（）函数可以进行逻辑判断，根据判定不同的结果从而返回不同的值。Sumif()函数可以进行有条件的求和，如（6）中的要求。,27,2相关知识点 Excel提供了大量的系统函数，功能非常丰富。按照其功能来划分主要有统计函数、日期与时间函数、数学与三角函数、财务函数、逻辑函数、文本函数、数据库函数等。 在输入函数时，必须以“=”开头，输入函数的一般格式为： =函数名（参数1,参数2，）,28,(1)常用函数（不一定属于模板中常用函数） SUM(number1
8、,number2,.)，返回参数之和 AVERAGE(number1,number2,.)，返回参数的平均值 COUNT(value1,value2,.)，返回包含数字以及包含参数列表中的数字的单元格的个数 ; COUNTIF(range,criteria),返回区域中满足给定条件的单元格的个数，其中： range是统计的单元格区域；,29,criteria为确定哪些单元格将被计算在内的条件。其形式可以为数字、表达式或文本。例如，条件可以表示为32、“32”、“32”或“apples”等。 SUMIF(range,criteria,sum_range)，返回满足条件的若干单元格的和，其中： R
9、ange为用于条件判断的单元格区域； Criteria为确定哪些单元格将被相加求和的条件； Sum_range是需要求和的实际单元格（区域）。,30,（2）常用数学函数,31,此外，常用数学函数还有取整函数INT、平方根函数SQRT、绝对值函数ABS、幂函数POWER、随机函数Rand等，详细请参考Excel帮助中的相关实例（在Excel中，按F1键后在“搜索”框中输入关键字，回车）。,32,（3）逻辑函数 IF(logical_test,value_if_true,value_if_false)， 执行真假值判断，根据逻辑计算的真假值，返回不同结果。其中： logical_test表示计算结
10、果为TRUE或FALSE的任意值或表达式； Value_if_true是logical_test为TRUE时返回的值， Value_if_false是logical_test为FALSE时返回的值。 如：=if(53,”Y”,”N”)返回”Y”,而=if(530,”Y”,”N”)返回”N”。,33,AND(logical1,logical2, .) 所有参数的逻辑值为真时，返回 TRUE；只要有一个参数的逻辑值为假，即返回 FALSE。 如：=AND（53, 10050）返回TRUE， 而公式：=AND（53, 100500）返回 FALSE。,34,OR(logical1,logical2,
11、 .) 所有参数的计算值均为FALSE时，才返回FALSE；只要有一个参数的逻辑值为真时，即返回 TRUE。如： 公式：=OR（53, 100500）返回TRUE， 公式：=OR（530, 100500）返回 FALSE。,35,（4）文本与数据函数 LEN(text)，返回文本字符串中的字符数。 如：=len(“abcd“)返回4, =LEN(“英语abc“)返回5 MID(text,start_num,num_chars) 返回文本字符串中从指定位置开始的特定数目的字符串。 =MID(“英语abc123“,3,3)返回“abc“。,36,TEXT(value,format_text),将数
12、值转换为按指定数字格式表示的文本。 如：=TEXT(20000,“$0.00”)，返回值“$20000.00”。 VALUE(text)，返回由数字文本字符串转换成的数字 说明：Text可以是MicrosoftExcel中可识别的任意常数、日期或时间格式。如果Text不是这些格式的数据，则函数VALUE返回错误值#VALUE!。 如：=VALUE(“$1,000“)，返回数值1000。,37,常用的文本函数还有LOWER(将文本转换成大写形式)、UPPER（将文本转换成大写形式）、TRIME（除了单词之间的单个空格外，清除文本中所有的空格）等，更多文本函数请参考帮助 “文本与数据函数”中有关函
13、数。,38,3实现方法 （1）将光标定位于J2单元格中，单击工具栏上的“自动求和”按钮，回车。再拖动单元格J2右下角的填充柄直到J22单元格。 （2）将光标定位于E23单元格中 ，单击工具栏上“自动求和”右侧的下拉按钮，单击“平均值”命令， 圈选求平均值的单元格区域后回车 ，将公式 横向填充到F23：I23即可。,39,（3）操作步骤：,40,（4）操作步骤：,41,（5）的操作步骤：,42,（6）的操作步骤：,43,有问题，查帮助,44,4课堂实践,45,5评价与总结（视情况而定） 第一组同学（2位为一组）主动上台演示（1）-（3）。 第二组同学主动上台演示（4）-（6）。 鼓励同学总结本案
14、例解决思路，学生或老师补充。,46,6课外延伸 （1）时间日期函数 Now() 返回当前日期和时间所对应的序列号或日期时间数据（与单元格格式有关）。 如：=NOW(),47,TODAY() 返回当前日期的序列号或日期格式数据。如= TODAY()。 DATE(year,month,day) 返回代表特定日期的序列号。如果在输入函数前，单元格格式为“常规”，则结果将显示为日期数据。如：=date(2010,11,12)。,48,YEAR(serial_number)，返回某日期对应的年份。 =year(now() 与year函数具有相同参数的日期时间函数还有month、day、hour、minu
15、te、time函数。 （2）信息函数 请参见教材,49,（3）课外练习,50,案例3 成绩分析,【场景描述】,51,1案例分析,52,2相关知识点 （1）复制、移动单元格（区域）中的内容,53,选择性粘贴 单击“编辑选择性粘贴”命令在“选择性粘贴”对话框中选择相关选项即可，如图4-13所示。,以案例（1）示范即可,54,（3）单元格的引用 单元格的绝对引用：是指对含有公式的单元格进行复制，不论复制到什么位置，公式中所引用的单元格地址都不发生变化。 如公式：=SUM($E$2:$I$2)，就是对单元格的绝对引用实例。绝对引用单元格地址的列号和行号前都加“$”符号，如$E$2、$A$1等。,55,
16、单元格的相对引用：是指将包含公式的单元格复制到其他单元格时，公式中所引用的单元格地址也随之发生变化。 例如，将工作表中J2单元格中的计算公式SUM(E2:I2)复制到J10单元格中就变成了SUM(E10:I10)。,56,相对引用单元格地址变化规律： 相对引用的公式被复制到其他单元时，公式所在单元格与引用单元格之间仍保持行列相对位置关系不变，相当于对单元格做平移操作。于是相对引用单元格地址变化有如下规律： 原行号+行地址偏移量新行地址 原列号+列地址偏移量新列地址,57,单元格的混合引用：是指在公式引用的单元格地址中，既有绝对地址引用又有相对地址引用。 如本例单元格K2中有公式“=SUM(N$3:R$3)”，当将公式复制到L2中时，公式将变为“=SUM(O$3:S$3)”，对单元格列的引用为相对引用，对行的引用为绝对引用。公式复制时，相当于向右平移一个单元格的位置，绝对引用的地址不变。
