《图书馆资源与服务》系列专题讲座-第二十二讲 信息技术基础能力系列4--Excel在学习中的应用.ppt

第二十二讲：信息技术基础能力系列4 excel在学习中的应用,图书馆参考咨询部 2009年12月8日 杨雪萍 ,1/39,1 问题导入,2 基本知识,4 更多技巧,3 案例制作与分析,本讲提纲,1 问题导入,在你的学习中，你使用excel做过什么？ 调查问卷录入分析、成绩管理、考勤、通讯录、统计图表等。,问题列表,你使用过“空白工作簿”以外的 excel 提供的模板吗？ 你会重复打印标题么？ 你知道“相对引用”、“绝对引用”和“混合引用”的区别么？ 你知道“自动筛选”和“高级筛选”的区别吗？ 你会对数据进行“排序”与“筛选”操作么？ 你会一下子把两长列数据中相同的数据挑出来么？ 你会轻松处理学生成绩么？,返回本讲提纲,2,2 基本知识,excel能够干些什么？ excel是目前使用最广泛的专业电子表格软件，可以对数据进行处理、统计分析与计算，简单的数据库管理，能绘制图表，并能与internet网络共享资源。,2.1 简介,2.2 界面,工作区,与其他office软件（例如word、powerpoint）的区别？ 工具区的特点：多了与表格制作编辑、修饰相关的工具栏，如“编辑”栏、“名称”栏、行标与列标等 制表工作区的特点：只提供制作网格，表格的排版应通过预览查看 视图特点： “普通”视图，用于制作表格 “分页浏览”视图，用于整体查看表格内容与页面的关系,2.3 窗口环境的特点,2.3 窗口环境的特点,窗口界面的主要部分 工作簿工作表单元格 工作簿操作：新建、打开、保存 工作表操作：插入、删除、重命名、移动或复制、选定全部工作表、工作表标签颜色 单元格 功能：存储器、计算器和显示器 名称：根据他们所在的行和列命名，例如a1，a2，b1，b2 个数：每层工作表sheet含25665536个单元格 格式：右键，设置单元格格式。 坐标 行坐标：行标记（165536），查看（ ctrl+ ， ctrl+ ） 列坐标：列标记（aiz），查看（ ctrl+ ， ctrl+ ）,返回本讲提纲,3 案例制作与分析,3 案例制作与分析,案例目标：建立一张某一班学生档案和考试成绩的统计表，并对成绩进行各种统计，进行名次的排定，绘制相应的图表进行成绩分析。,3.1 创建规范的数据表格,3.1.1设计表格 3.1.2输入数据 3.1.3修饰表格 3.1.4保存并打印数据,3.1.1,3.1.1 设计表格设计表格流程,准确描述管理目标 确定管理项目名称 建立表格结构,例如建立一个学生档案，目标中应包含如下关键词：学号、姓名、性别、出生日期、邮政编码、家庭住址、联系电话等内容。,简明清晰 目标中应含有一些关键词 用关键词确定管理项目名称,原则：待管理的项目应独立成列,必须先建立表头框架 表头内容必须为文本值 表头内容避免空项,3.1.1,3.1.1设计表格表体结构组成,表体结构,制表人等信息,实例1：创建“学生档案”,实例1：创建“学生档案”,目标：制作美观的“学生档案” 关键要点 1.设计表格 (1) 新建工作表，重命名工作表名“学生档案”，设置工作表标签颜色。 (2)输入标题、制表日期 、制表人、上表头、左表头 2.输入数据 (1)自定义格式，如“学号”单元格设置，应将格式设置为“文本”；或者由字符串引导符“”引导的数字。 (2)回车符自动跳到右边单元格的设置 (3)数据有效性：性别 (4)自动填充,例如，用序列选择；在输入成绩时超过100分时提示错误信息（示范+练习）。,星期一，星期二，星期三、星期四 第一季，第二季，第三季，第四季,内容编辑的2个技巧 (1)按f2直接进行编辑 (2)用“alt+enter”键换行,实例1：创建“学生档案”（续）,目标：制作美观的“学生档案” 关键要点 3.修饰表格 对齐方式 边框、底纹 添加logo 网格线设置 冻结窗格使用 页眉页脚 页眉设置公司名称“北京师范大学附属中学”c)页脚处添加“第？页，共？页” 4.保存打印数据 打印标题（页面设置/工作表/打印标题） 页面设置/页边距,练习,名称：请使用excel软件设计一份调查问卷（问卷内容见word文档“大学生心理健康状况调查问卷.doc”）。 设计要求 利用数据有效性，设计下拉式问卷选项。 只允许用户在选择题项处可操作，其他均受保护。 问卷形式美观。,保护设置方法（对自由可以操作的单元格设置） 1.选中可自由操作的单元，执行右键，把“锁定”前的勾去掉， 2.执行“工具保护保护工作表”，勾选“选定未锁定的单元格”。,3.2,3.2分析数据表格,3.2.1运用公式和函数处理数据分析 3.2.2运用图表处理数据分析 3.2.3运用数据库统计功能,3.2.1,3.2.1运用公式和函数处理数据分析,公式 结构组成：由“=”引导的由数据、含有数据的单元格或区域坐标、运算符组成的表达式，excel的单元格中保存用户输入的公式，通过即时计算，按用户给定的显示格式显示其结果。例如，=x+1；=a1+b1等。 公式复制过程中的3种引用关系：相对引用、绝对引用、混合引用。 相对引用：公式拖拉复制过程引用原始单元格取值位置的相对关系。针对有规律的公式使用此方法。 绝对引用：某算术式中存在多个算术项。但是在拖拉复制该公式时，其中某一算术项必须取值于固定的单元格位置。该位置也称“绝对引用”点。绝对引用符“$”，例如例如 $f$6。 混合引用：某算术式中存在多个算术项。但是在拖拉复制该公式时，绝对引用位置分别在不同的算术项中。针对公式取值固定位置设置$符号。,相对引用、绝对引用、混合引用,举例： c3单元格公式为：=a1+b1 为相对引用 将公式复制到的d1单元格时变为=b1+c1，将公式复制到c2单元格时变为=a2+b2 c3单元格公式为：=$a$1+$b$1 为绝对引用 将公式复制到的d1单元格时变仍为= =$a$1+$b$1 ，将公式复制到c2单元格时变仍为= =$a$1+$b$1 c3单元格公式为：=$a1+b$1 为相对引用 将公式复制到的d1单元格时变为=$a1+c$1，将公式复制到c2单元格时变为=$a2+b$1 注：相对引用和绝对引用是指将公式向右或向下复制时列标和行号是否跟着变化。,举例：以“练习材料”作为素材,3.2.1,3.2.1运用公式和函数处理数据分析,函数 类型,3.2.1,3.2.1运用公式和函数处理数据分析,函数 常用函数 数学与三角函数 取整函数：int() 余数函数：mod(被除数,除数)：求余数 随机数函数（无自变量参数）：round() 圆周率函数（无自变量参数）：pi() 正弦、余弦、正切函数：sin()，cos()，tan() 统计函数 求和、平均值函数：sum，average 最大值、最小值函数：max，min 数值数据计数函数：count 数值和字符串数据计数函数：counta 条件计数函数：countif(区域坐标,条件)：求指定区域中满足条件的数据个数 条件函数 if函数 ,举例：对某班学生成绩用函数进行分析,素材：学生档案和成绩.xls，“期末成绩”工作表 分析目标及所用的函数： 每个学生的总成绩（sum） 某学科的最高分、最低分（max、min） 班级最高总分、最低总分（先求总分，再用max、min） 班级平均分（average）,3.2.2运用图表处理数据分析,excel提供的图表从数轴的类别可以分成两大类：xy函数图和统计图表。 xy函数图：其x和y轴都是数值型数据（其数据点位置随其x和y的坐标值大小变换）。 统计图表：其x和y轴只有一个是数值型数据（其数据点位置随其坐标值大小变换），另一个是字符串型数据（其数据点位置是等间距的）。 图表类型 柱形图、条形图、折线图、饼图、xy散点图、面积图、圆环图、雷达图等。 建立基本方法 其一是单击常用工具栏中的“图表向导”工具按钮； 其二是执行“插入图表”命令，两种方法都打开“图表向导”对话框，跟随向导操作即可。 编辑修饰图表,举例：对某班学生成绩用图表进行分析,素材：学生档案和成绩.xls， “成绩分布”工作表 分析目标及所用的函数： 各科成绩分布图 统计各成绩分数段人数（方法1：countif函数；方法2：用frequency函数） 绘制圆饼图、柱状图 修饰图表,举例：对某班学生成绩用图表进行分析,素材：学生档案和成绩.xls，“成绩分布”工作表 分析目标及所用的函数： 查看某三个学生之间三科成绩的比较情况 查看某三个学生自身三科成绩的比较情况,3.2.3运用数据库统计功能,数据排序 数据筛选 数据分类汇总 数据透视,3.2.3,3.2.3运用数据库统计功能,数据排序 作用：将数据组按某字段内容，设置递增或递减的重新排列方式 方法：选择数据表（可以带标题行，也可以不带标题行）后，执行“数据排序”命令，然后在“排序”对话框中根据需要进行选择操作。 注意：在排序时要建立序列号，以便恢复原来序列。,3.2.3,3.2.3运用数据库统计功能,数据筛选 作用：显示数据组中满足某条件的记录，隐藏其他 类型：自动筛选和高级筛选 方法 自动筛选的基本方法：选择数据表（必须带标题行）后，执行“数据筛选自动筛选”命令，然后单击标题行各单元格右侧的向下箭头给出筛选条件的基本方法；要显示所有数据，执行“数据筛选全部显示”命令。 高级筛选的基本方法：在已有数据表基础上，建立条件区（第一行含需要的数据段名，以下一行或多行输入条件表达式；然后执行“数据筛选高级筛选”命令，然后在弹出的“高级筛选”对话框中设置数据区、条件区和输出区坐标。,举例：对某班学生成绩用排序和筛选进行分析,素材：学生档案和成绩.xls 分析目标及所用的函数： 给学生排名次（rank函数） 查看语文成绩超过85分的都有哪些同学（数据筛选自动筛选，自定义） 查看语文、数学两门成绩超过85分的同学（数据筛选高级筛选） 先设置筛选条件 再执行高级筛选,3.2.3运用数据库统计功能,数据分类汇总 类型：结构式分类汇总和筛选式分类汇总 结构式分类汇总 作用：使汇总数据可按宏观或微观形式查看 设置：先排序，后汇总；选择汇总项及算法 恢复：在“分类汇总”对话框选择“全部删除”项 筛选式分类汇总 作用：使汇总数据只按筛选条件查看 设置：先筛选，后统计 恢复：从“筛选”命令中选择“全部显示”项,举例：对高一年级学生成绩进行分类汇总,素材：学生档案和成绩.xls， “高一年级成绩”工作表。 目的：对各班每门学科求平均分 方法： 用average函数（前面已讲） 用分类汇总,3.2.3运用数据库统计功能,数据透视 数据透视表是一种交互式的表，可以进行某些计算，如求和与计数等。所进行的计算与数据在数据透视表中的排列有关。 素材：数据透视表-素材.xls,返回本讲提纲,4,4 更多技巧,4.1,4.1数值转置,目的：把excel表中某一列的内容转置成某一列的内容 方法：选中待转置的内容，执行“复制”功能，再在转置的目标位置处，执行“编辑选择性粘贴”，在弹出的对话框中选择“转置”即可。,4.3,4.2利用高级筛选删除重复记录,目的：删除表中重复记录 方法：执行“数据筛选高级筛选”命令，在弹出的对话框中，把“选择不重复的纪录”的框勾选上。,4.4,4.3从两列数据相同挑选相同的,目的：删除两列数据表中挑选相同的 方法：if(countif(a:a,b1),“相同”,“”)，然后用“筛选/自动筛选”。,38/39,end,希望大家积极利用图书馆的各种资源和服务。 为了保护电子资源的知识产权，请合法使用馆藏电子资源，自觉维护我校正常的文献信息服务秩序。,39/39,讲座预告,第二十三讲：资源系列15 中外文报纸文献检索 时间：12 月 11 日 （周五） 14 ： 00 15 ：