Excel在学习中的应用.ppt

第六讲：信息技术基础能力系列3Excel在学习中的应用,图书馆参考咨询部2008年10月7日,本讲提纲,问题导入,在你的学习中，你使用Excel做过什么？调查问卷录入分析、成绩管理、考勤、通讯录、统计图表等。,问题列表,你使用过Excel提供的模板吗？你能说出常用的统计函数吗？你知道“自动筛选”和“高级筛选”的区别吗？你会对数据进行“排序”与“筛选”操作么？你知道“相对引用”、“绝对引用”和“混合引用”的区别么？你知道“xy函数图表”与“统计图表”的主要区别么？你能生成相应的图表么？你知道数据透视吗？,基本知识,Excel能够干些什么？Excel是目前使用最广泛的专业电子表格软件，可以对数据进行处理、统计分析与计算，简单的数据库管理，能绘制图表，并能与Internet网络共享资源。,简介,界面,工作区,与其他Office软件（例如Word、PowerPoint）的区别？工具区的特点：多了与表格制作编辑、修饰相关的工具栏，如“编辑”栏、“名称”栏、行标与列标等制表工作区的特点：只提供制作网格，表格的排版应通过预览查看视图特点：“普通”视图，用于制作表格“分页浏览”视图，用于整体查看表格内容与页面的关系,窗口环境的特点,窗口环境的特点,窗口界面的主要部分工作簿工作表单元格工作簿操作：新建、打开、保存工作表操作：插入、删除、重命名、移动或复制、选定全部工作表、工作表标签颜色单元格功能：存储器、计算器和显示器名称：根据他们所在的行和列命名，例如A1，A2，B1，B2个数：每层工作表Sheet含25665536个单元格格式：右键，设置单元格格式。坐标行坐标：行标记（165536），查看（Ctrl+，Ctrl+）列坐标：列标记（AIZ），查看（Ctrl+，Ctrl+）,3,案例制作与分析,案例制作与分析,案例目标：建立一张某一班学生档案和考试成绩的统计表，并对成绩进行各种统计，进行名次的排定，绘制相应的图表进行成绩分析。,创建规范的数据表格,3.1.1设计表格3.1.2输入数据3.1.3修饰表格3.1.4保存并打印数据,实例：学生档案,设计表格设计表格流程,准确描述管理目标确定管理项目名称建立表格结构,例如建立一个学生档案，目标中应包含如下关键词：学号、姓名、性别、出生日期、邮政编码、家庭住址、联系电话等内容。,简明清晰目标中应含有一些关键词用关键词确定管理项目名称,原则：待管理的项目应独立成列,必须先建立表头框架表头内容必须为文本值表头内容避免空项,设计表格表体结构组成,表体结构,制表人等信息,演示：设计表格结构,输入数据,3.1.2.1数据类型数值型数据包括数字和结果为数字的公式，其在单元格中默认的显示位置为“右对齐”；数值型数据可以有“常规”、“数值”、“货币”、“会计专用”、“科学记数”、“百分比”“分数”等多种显示格式；对于位数教多的身份证号，有两种表示方法，其一是采用由字符串引导符“”引导的数字，其二是采用“文本”显示格式的“数字”。字符串型数据包括汉字、英文字母、空格等，其在单元格中默认的显示位置为“左对齐”。例如学号。日期型数据包括日期（时间）和结果为日期（时间）的公式，其在单元格中默认的显示位置为“右对齐”；输入日期时需要用“-”号或“/”号隔开，如“2008-3-21”；输入时间时，时、分、秒之间用冒号隔开。逻辑型数据包括TRUE和FALSE和结果为逻辑值的公式，其在单元格中默认的显示位置为“居中”。,输入数据,3.1.2.2数据的准确控制原始数据的准确性控制数据管理过程中最基础的数据，或者必须用手工输入的数据，称为“原始数据”。控制方法：调用法。打开相关文件，平铺两个窗口，用“=”号调用。数据有效性。例如，用序列选择；在输入成绩时超过100分时提示错误信息（示范+练习）。加工数据的准确性控制凡可以通过计算获得的数据，则称为“加工数据”。此类数据的监控重点在于公式。控制方法：公式。确定计算结果位置，再按下“=”号，用格地址建立公式。,输入数据,3.1.2.3输入时常见的操作：自定义格式、自动填充、内容编辑1.自定义格式类型：常规、数值、货币、会计专用、日期、时间、文本等。例如，学号为0710023011，输入时就变成了710023011，应将格式设置为“文本”；或者由字符串引导符“”引导的数字。,输入数据,输入时常见的操作：自定义格式、自动填充2.自动填充可自动填充的序列时间：包括指定增量的日、星期和月，或诸如星期、月份和季度的重复序列。等差：通过步长值决定数值的升序或降序等比：数值乘以常数因子,输入数据,输入时常见的操作：自定义格式、自动填充2.自动填充自动填充方式鼠标拖曳方式：最简单方便，可完成大部分自动填充的工作。鼠标左键拖动方式：先键入预填充的前两、三个辞条，并选中，当鼠标指针变成黑十字形时，按下鼠标左键拖曳。鼠标右键拖动方式：先键入预填充的前两、三个辞条，并选中，当鼠标指针变成黑十字形时，按下鼠标右键拖曳，松开右键时弹出快捷菜单（见右图），进行选择即可。菜单方式：较复杂的自动填充一般采用菜单方式。在起始单元格键入数据，并选中该单元格；执行“编辑填充序列”命令，在弹出的对话框中，再进行设置。,输入数据,输入时常见的操作：自定义格式、自动填充3.内容编辑操作对象常规编辑特殊编辑,对象如何选择选中状态,单元格用光标单击单元格光标,列用光标单击整列反白行用光标单击整行反白,工作表用光标单击标签反白,表格区用光标拖拉区域反白,表文件用光标单击标题栏反白,移动复制清除,插入行高/列宽等高/等宽隐藏/显示,插入/删除；移动/复制；命名,小技巧：输入文本时需要换段落时怎么办？,修饰表格,1.对齐方式作用：保证数据的可视性。类型：水平对齐、垂直对齐、文本控制、从右到左。例如，表格标题使用“合并居中”；表头文字加粗“居中”；文字居左、数字居右等。2.格线和填充格式作业：用表头、数据和统计区分割方式显示表格数据，增加可读性。个性化修饰：用颜色、线型等。套用格式：可快速规范表格结构修饰。3.保护表格作用：控制表格数据使用过程的安全性。类型：锁定和隐藏锁定：作用（避免手工输入导致计算错误）隐藏：作用（保护重要的算法）方法：第一步，执行“单元格格式”命令，在“保护”页面中设置，第二步，执行“工具保护保护工作表”。,通过新建/本机上的模板/BalanceSheet介绍如何修饰,保存并打印数据,保存数据保存（常规）另存为保存工作区其他保存打印数据预览表格、简单调整版面位置、打印输出,新文档，命名保存（注意名称规则）旧文档，覆盖保存,定位保存更名保存改变文档类型加密保存,用于更换名称的保存,制作模板选择“模板”类型传递障碍时存“文本”类型,用于安全性较强的保存用“另存为”对话框的“工具”按钮处理,举例：创建学生档案,建立步骤打开Excel软件；重命名工作表名：将鼠标移到sheet1工作表标签上，单击鼠标右键，在弹出的快捷菜单上单击“重命名”命令，输入“学生档案”；输入标题、制表日期、制表人、上表头、左表头；自定义格式：学号、家庭电话、手机号码格式为“文本”。输入数据：“编号”用自动填充，“性别”用数据有效性，其他均需准确输入；数据表格美化：对齐方式（左对齐、居中）合并单元格、填充、边框线。保存。,练习,作业名称：请使用Excel软件设计一份调查问卷（问卷内容见Word文档“大学生心理健康状况调查问卷.doc”）。设计要求利用数据有效性，设计下拉式问卷选项。只允许用户在选择题项处可操作，其他均受保护。问卷形式美观。,分析数据表格,3.2.1运用公式和函数处理数据分析3.2.2运用图表处理数据分析3.2.3运用数据库统计功能,运用公式和函数处理数据分析,公式结构组成：由“=”引导的由数据、含有数据的单元格或区域坐标、运算符组成的表达式，Excel的单元格中保存用户输入的公式，通过即时计算，按用户给定的显示格式显示其结果。例如，=x+1；=A1+B1等。公式复制过程中的3种引用关系：相对引用、绝对引用、混合引用。相对引用：公式拖拉复制过程引用原始单元格取值位置的相对关系。针对有规律的公式使用此方法。例如，如果将单元格B2中的相对引用复制到单元格B3，将自动从=A1调整到=A2。绝对引用：某算术式中存在多个算术项。但是在拖拉复制该公式时，其中某一算术项必须取值于固定的单元格位置。该位置也称“绝对引用”点。绝对引用符“$”，例如例如$F$6。例如，例如，如果将单元格B2中的绝对引用复制到单元格B3，则在两个单元格中一样，都是$F$6。混合引用：某算术式中存在多个算术项。但是在拖拉复制该公式时，绝对引用位置分别在不同的算术项中。针对公式取值固定位置设置$符号。,举例：以“练习材料”作为素材,运用公式和函数处理数据分析,函数类型,运用公式和函数处理数据分析,函数常用函数数学与三角函数取整函数：INT()余数函数：MOD(被除数,除数)：求余数随机数函数（无自变量参数）：ROUND()圆周率函数（无自变量参数）：PI()正弦、余弦、正切函数：SIN()，COS()，TAN()统计函数求和、平均值函数：SUM，AVERAGE最大值、最小值函数：MAX，MIN数值数据计数函数：COUNT数值和字符串数据计数函数：COUNTA条件计数函数：COUNTIF(区域坐标,条件)：求指定区域中满足条件的数据个数条件函数If函数,举例：对某班学生成绩用函数进行分析,素材：学生档案和成绩.xls，“期末成绩”工作表分析目标及所用的函数：每个学生的总成绩（SUM）某学科的最高分、最低分（Max、Min）班级最高总分、最低总分（先求总分，再用Max、Min）班级平均分（Average）,运用图表处理数据分析,Excel提供的图表从数轴的类别可以分成两大类：XY函数图和统计图表。XY函数图：其X和Y轴都是数值型数据（其数据点位置随其X和Y的坐标值大小变换）。统计图表：其X和Y轴只有一个是数值型数据（其数据点位置随其坐标值大小变换），另一个是字符串型数据（其数据点位置是等间距的）。图表类型柱形图、条形图、折线图、饼图、XY散点图、面积图、圆环图、雷达图等。建立基本方法其一是单击常用工具栏中的“图表向导”工具按钮；其二是执行“插入图表”命令，两种方法都打开“图表向导”对话框，跟随向导操作即可。编辑修饰图表,举例：对某班学生成绩用图表进行分析,素材：学生档案和成绩.xls，“成绩分布”工作表分析目标及所用的函数：各科成绩分布图统计各成绩分数段人数（方法1：Countif函数；方法2：用Frequency函数）绘制圆饼图、柱状图修饰图表,举例：对某班学生成绩用图表进行分析,素材：学生档案和成绩.xls，“成绩分布”工作表分析目标及所用的函数：查看某三个学生之间三科成绩的比较情况查看某三个学生自身三科成绩的比较情况,运用数据库统计功能,3.2.3.1数据排序3.2.3.2数据筛选3.2.3.3数据分类汇总3.2.3.4数据透视（本讲略）,运用数据库统计功能,3.2.3.1数据排序作用：将数据组按某字段内容，设置递增或递减的重新排列方式方法：选择数据表（可以带标题行，也可以不带标题行）后，执行“数据排序”命令，然后在“排序”对话框中根据需要进行选择操作。注意：在排序时要建立序列号，以便恢复原来序列。,运用数据库统计功能,3.2.3.2数据筛选作用：显示数据组中满足某条件的记录，隐藏其他类型：自动筛选和高级筛选方法自动筛选的基本方法：选择数据表（必须带标题行）后，执行“数据筛选自动筛选”命令，然后单击标题行各单元格右侧的向下箭头给出筛选条件的基本方法；要显示所有数据，执行“数据筛选全部显示”命令。高级筛选的基本方法：在已有数据表基础上，建立条件区（第一行含需要的数据段名，以下一行或多行输入条件表达式；然后执行“数据筛选高级筛选”命令，然后在弹出的“高级筛选”对话框中设置数据区、条件区和输出区坐标。,举例：对某班学生成绩用排序和筛选进行分析,素材：学生档案和成绩.xls分析目标及所用的函数：给学生排名次（rank函数）查看语文成绩超过85分的都有哪些同学（数据筛选自动筛选，自定义）查看语文、数学两门成绩超过85分的同学（数据筛选高级筛选）先设置筛选条件再执行高级筛选,运用数据库统计功能,3.2.3.3数据分类汇总类型：结构式分类汇总和筛选式分类汇总结构式分类汇总作用：使汇总数据可按宏观或微观形式查看设置：先排序，后汇总；选择汇总项及算法恢复：在“分类汇总”对话框选择“全部删除”项筛选式分类汇总作用：使汇总数据只按筛选条件查看设置：先筛选，后统计恢复：从“筛选”命令中选择“全部显示”项,举例：对高一年级学生成绩进行分类汇总,素材：学生档案和成绩.xls，“高一年级成绩”工作表。目的：对各班每门学科求平均分方法：用Average函数（前面已讲）用分类汇总,更多技巧,数值转置,目的：把Excel表中某一列的内容转置成某一列的内容方法：选中待转置的内容，执行“复制”功能，再在转置的目标位置处，执行“编辑选择性粘贴”，在弹出的对话框中选择“转置”即可。,多页打印标题,目的：由于表格过于大，希望在下一页中仍然有表头。方法：执行“文件页面设置”命令，在弹出的对话框中选择“工作表”选项，打印标题处选择即可，可通过打印预览查看打印效果。,利用高级筛选删除重复记录,目的：删除表中重复记录方法：执行“数据筛选高级筛选”命令，在弹出的对话框