Excel在学习中的应用.pptVIP

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