高教社计算机应用基础（创新版）（第三版）教学课件第5章 数据统计分析Excel2016应用

第5章 数据统计分析与Excel2016应用本章主要介绍数据统计分析的概念、Excel2016基本操作、工作表的格式化、公式与函数的使用、图表使用、工作数据库操作、打印工作表等。《计算机应用基础（创新版）（第三版）》配套资源5.1数据统计分析的基本概念5.1.1.数据统计分析的基本概念1.统计学概念统计是处理数据的一门科学，是收集、分析、解释数据并从数据中得出结论的科学。2.简单的数据统计随着社会、经济和科学技术的飞速发展，统计知识已广泛的应用于各个领域。用统计知识来解决实际问题，可以帮助我们更好地管理数据。下面就介绍一下统计的一些基础知识。总体、个体、样本和样本容量平均数众数中位数标准差方差5.1数据统计分析的基本概念5.1.2统计过程数据统计工作一般是由四部分组成：统计设计、统计调查、统计资料整理和统计分析。5.1数据统计分析的基本概念1.统计设计统计设计是统计工作的首要阶段，是根据统计研究的目的和研究对象的特点，明确统计指标和指标体系，以及对应的分组方法，并以分析方法指导实施的统计活动。5.1.2统计过程2.统计调查统计调查是根据调查的目的与要求，运用科学的调查方法，有计划、有组织地收集数据资料的工作活动。常用的方法有:普查、抽样调查、和统计报表等。5.1.2统计过程3.统计资料整理统计资料整理简称统计整理。是指根据统计研究的目的，对统计调查所得的原始资料进行科学的分类和汇总；或对已初步加工的次级资料进行再加工，使其系统化、条理化、科学化，以反映所研究的现象总体特征。5.1.2统计过程4.统计分析统计分析是指运用统计方法及相关的知识，定量与定性相结合对统计资料进行研究活动。5.1.2统计过程5.2.1

Excel

2016的启动与退出启动Excel

2016退出Excel

20165.2

Excel

2016概述5.2.2

Excel

2016的窗口组成Excel

2016应用程序的窗口主要是由标题栏、快速访问工具栏、功能区、编辑栏、工作表编辑区、工作表标签、滚动条、状态栏、视图按钮、显示比例等组成。5.2

Excel

2016概述5.2.3

Excel

2016工作簿、工作表、单元格1．工作簿工作簿是指Excel环境中用来储存并处理工作数据的文件。也就是说Excel文档就是工作簿。它是Excel工作区中一个或多个工作表的集合，其扩展名为.xlsx。每一本工作簿可以拥有许多不同的工作表，工作簿中最多可建立255个工作表。5.2

Excel

2016概述5.2.3

Excel

2016工作簿、工作表、单元格2．工作表工作表是显示在工作簿窗口中的表格。一个工作表可以由

1048576行和16384列构成。5.2

Excel

2016概述5.2.3

Excel

2016工作簿、工作表、单元格3．单元格单元格是表格中行与列的交叉部分，它是组成表格的最小单位，单个数据的输入和修改都是在单元格中进行的。5.2

Excel

2016概述5.3.1工作簿的操作新建工作簿保存工作簿打开工作簿关闭工作簿保护工作簿隐藏/显示工作簿5.3

Excel

2016基本操作5.3.2工作表的操作插入工作表删除工作表重命名工作表移动或复制工作表保护工作表拆分或冻结工作表隐藏或显示工作表5.3

Excel

2016基本操作实例5.1创建“学生基本情况”工作簿任务描述：在Excel中，新建一个工作簿，将“sheet1”工作表重命名为“学生基本情况”，并将工作簿保存为“学生基本情况表.xlsx”。5.3

Excel

2016基本操作拓展训练：新建一个空白工作簿，保存到“My

Documents”文件夹中，文件名为“练习1.xlsx”。在“练习1.xlsx”工作簿中插入三个新工作表，并且将工作表“sheet1”、“sheet2”、“sheet3”、“sheet4”分别重命名为“一季度”、“二季度”、“三季度”、“四季度”。。5.3

Excel

2016基本操作5.3.3单元格的操作1．选取单元格选取一个单元格选取一整行选取一整列选取整个工作表选取单元格区域5.3

Excel

2016基本操作5.3.3单元格的操作插入单元格、行和列删除单元格复制、移动5.3

Excel

2016基本操作5.3.4数据编辑1．清除数据2．查找数据3．替换数据5.3

Excel

2016基本操作5.4.1输入数据元格中可以输入各种类型的数据，例如：文字、数字、时间、时期和公式等，每种数据都有它特定的格式和输入方法。输入文本数据输入数值数字输入时间数据输入日期数据5．自动输入数据5.4

工作表格式化设置实例5.2录入学生基本情况表信息任务描述:为了今后的数据计算、统计分析，需要将学生基本信息情况输入到

Excel中。任务分析：学生基本情况信息来源于学生入学时填写的学生基本情况登记表，

采集每名学生基本情况信息填入Excel工作表，要保证数据的正确性、完整性，例如身份证号数据显示的正确性等。学生基本数据能够支持班主任、教学系、学生处、教务处等部门的使用。实施步骤：①打开“学生基本情况表”表②在“学生基本情况”工作表中录入数据。5.4

工作表格式化设置拓展训练：制作如样章所示“学生登记表”，图所示5.4

工作表格式化设置5.4.2设置单元格格式设置数据格式设置对齐方式设置边框设置填充色5.4

工作表格式化设置5.4.3设置列宽、行高系统默认行高和列宽有时不并能满足需要，这时用户可以调整行高和列宽。通常调整行高和列宽的方法是用鼠标拖动方法和

“格式”列表中的命令来调整行高和列宽。鼠标拖动方法使用“格式”列表中的命令方法5.4

工作表格式化设置实例5.3学生基本情况表的格式设置与美化任务描述：为“学生基本情况表”工作簿中的“学生基本情况”工作表进行设置格式及美化。格式设置后的最终效果如图5.4

工作表格式化设置5.4.4条件格式条件格式是指当单元格中的数据满足某个条件时，数据的格式为指定的格式，否则为原来的格式。设置条件格式是在“开始”选项卡的“样式”组中单击“条件格式”按钮，在展开的列表中选择某一个规则。5.4

工作表格式化设置实例5.4条件格式设置任务描述：为“成绩表”工作表中不及格的成绩设置为浅红填充色深红色文本，使之一目了然。如图所示。5.4

工作表格式化设置拓展训练：通过使用“条件格式”设置，将所有“应发工资”高于2000元的加红色填充，实现突出显示，如图所示。5.4

工作表格式化设置5.4.5自动套用格式及模板的使用套用表格格式Excel

2016的套用表格格式功能可以根据预设的格式，将制作的报表格式化，产生美观的报表。从而节省设置报表格式的时间，同时使表格符合数据库表单的要求。模板使用Excel

2016提供一些预先设计好的常用表格，将其作为模板，方便用户使用。5.4

工作表格式化设置实例5.5建立员工考勤时间表任务描述使用模板，建立一个员工考勤时间表。如图所示5.4

工作表格式化设置5.5.1单元格地址引用单元格地址的引用分为相对地址引用、绝对地址引用和混合地址引用三种。5.5公式与函数的使用相对地址引用相对地址引用：是单元格地址中仅含有单元格的列号与行号，例如：A1、C9绝对地址引用绝对地址引用：是在单元格的列号与行号前各加一个“$”，例如：$A$1、$C$9。混合地址引用混合地址引用：是在单元格的列号与行号中的一个前加一个

“$”，例如：$A1、C$9。单元格区域引用5.5.1单元格地址引用4．单元格区域引用使用逗号逗号可将两个单元格引用联合起来，常用于不相邻的单元格。例如A1，D3，S7表示引用A1、D3和S7单元格。使用冒号冒号表示一个单元格的区域。例如：A1：A4表示A1到A4的所有单元格（A1、A2、A3和A4）。引用同一工作簿中的不同工作表中的单元格，格式如下：<工作表>！<单元格地址>例如：Sheet1！A1。引用不同工作簿工作表中的单元格，格式如下：<[工作簿文件名]><工作表>！<单元格地址>例如：[成绩表]Sheet1！A1。5.5.1单元格地址引用1．公式的概念（1）什么是公式公式是对工作表中的数据进行计算的表达式，表达式是由数据、单元格地址、函数和运算符等组成的。5.5.2公式的使用（2）公式的组成公式必须以等号“＝”开头，等号后面可由如下五种元素组成：运算符：例如“+”或者“*”号。单元格引用：例如：A1:C5数值或文本：例如，“100”或“计算机”。工作表函数：可以是Excel内置的函数，例如SUM或MAX，也可以是自定义的函数。括号：即“(”和“)”。它们用来控制公式中各表达式被处理的优先权。5.5.2公式的使用算术运算符的优先级由高到低的顺序为：％、^、*和/、＋和－，如果优先级相同，则按照从左向右的顺序进行计算5.5.2公式的使用算术运算符含义示例运算结果＋加法1＋12—减法2－11*乘法1*22/除法2/40.5^乘方2^38%求百分比15%0.152.运算符在公式中通常使用的运算符有：算术运算符、比较运算符、文本运算符和引用运算符。（1）算术。运算符算术运算符是用来进行算术运算，其运算结果仍然是数值5.5.2公式的使用（2）比较运算符比较运算符是用来进行比较数值大小，其运算结果是一个逻辑值TRUE（真）。

或FALSE（假）含义示例运算结果比较运算符=FALSE（假）>FALSE<TRUE（真）>=FALSE<=TRUE<>等于3=4大于3>4小于3<4大于等于3>=4小于等于3<=4不等于3<>4TRUE（3）文本运算符文本运算符只有“＆”是用来连接字符串的，其运算结果仍然是文本类型5.5.2公式的使用。文本运算符含义示例运算结果&字符串连接“万事”&“如意”万事如意（4）引用运算符引用运算符有3个，有关引用运算符的说明见表所示5.5.2公式的使用引用运算符含义示例：（冒号）区域运算符，用于引用单元格区域A5:C10，（逗号）联合运算符，用于引用多个单元格区域A5:C10,E5:F10（空格）交叉运算符，用于引用两个单元格区域的交叉部分B3:D3

C1:C5（5）运算符的优先级四种运算符的优先级由高到低的顺序为：引用运算符、算术运算符、文本运算符、比较运算符5.5.2公式的使用优先顺序符号说明1：（空格）,引用运算符：冒号、空格、逗号2—算术运算符：负号3%算术运算符：百分比4^算术运算符：乘方5*和/算术运算符：乘和除6+和－算术运算符：加和减7&文本运算符：连接文本8=,<,>,<=,>=,<>比较运算符：比较两个值3．输入公式输入公式的步骤：①选定要输入公式的单元格。②先在单元格中输入“＝”，然后输入计算式（或在编辑框中输入公式也可）。③按回车键确定（或单击编辑栏中“输入”按钮

）。5.5.2公式的使用任务描述需要计算“成绩表”中每个人的总分，也就是使用公式计算每个人“数学”、“语文”和“计算机”的总成绩。任务分析：各门课程的成绩汇总到“成绩表”后，使用单元格求和公式或SUM函数对各门课程的成绩进行求和计算，得到总分，可以看出每名学生总分高低；还可以排出名次，反映学生学习的总体成效，发现是否存在偏科等现象。实例5.6 计算总成绩按要求制作销售统计表按照样章建立销售统计表，如图所示。拓展训练5.5.3函数什么是函数在Excel中函数是预先定义，执行计算、分析等处理数据任务的特殊公式。函数的组成Excel中函数由函数名和用括号括起来的一系列参数构成。即<函数名>（参数1，参数2，…）5.5公式与函数的使用5.5.3函数3．输入函数输入函数的方法有两种，一种是像输入公式一样直接输入函数，另一种是使用“插入函数”对话框的方法输入函数5.5公式与函数的使用任务描述对学生的成绩进行统计分析，通过使用函数计算每个学生的总分、排名和统计成绩的频率等。学生“成绩表”统计计算结果如图所示实例5.7 学生成绩的分布情况统计SUM：函数函数格式：SUM（Number1,Number2,…）。函数功能：计算单元格区域中所有数据的和。Number1:number1,number2…1到255个待求和和数值。。AVERAGE函数函数格式：AVERAG（Number1,Number2,…）。函数功能：计算参数的算术平均值。COUNT函数函数格式：COUNT（number1,number2,…）。函数功能：计算参数中数字项的个数。知识与技能MAX函数函数格式：MAX（Number1,Number2,…）。函数功能：找出参数中数值的最大值。MIN函数函数格式：MIN（Number1,Number2,…）。函数功能：找出参数中数值的最小值。知识与技能RANK函数函数格式：RANK（Number，ref，order）函数功能：返回某一数值在一列数值中的相对于其他数值的排位。Number

代表需要排序的数值；ref代表排序数值所处的单元格区域；order代表排序方式参数（如果为“0”或者忽略，则按降序排名；如果为非“0”值，则按升序排名。）。FREQUENCY函数函数格式：FREQUENCY(data_array,Bins_array)函数功能：以一列垂直数组返回某个区域中数据的频率分布。Data_array表示用来计算频率的一组数据或单元格区域；Bins_array表示为前面数组进行分隔一列数值。知识与技能任务描述：利用“学生基本情况表”中的数据获取学生的出生日期，如图所示。任务分析：身份证号的第7位至第14位，分别代表出生日期的年、月、日，因此我们可以利用“学生基本情况表”中的“身份证号”的信息提取学生的出生日期。实例5.8 获取出生日期任务描述：某家电卖场规定销售人员促销的售价不低于8折（即售价不得低于定价的80%），否则需要请示店面经理。售价高于8折的部分，提取15%作为销售人员的奖金。由于每位销售人员卖出的产品的折扣各不相同，所以需要对卖出商品折扣进行判断，然后再根据高出的部分计算出每位销售人员的奖金，如图所示。实例5.9 销售人员奖金统计家电卖场的销售数据，可以通过家电卖场的统计报表获得。本例使用“销售人员奖金统计.xlsx”的数据，根据销售折扣核定销售人员的奖金。首先要通过公式求出卖出产品的折扣，然后利用IF函数判断每个人的奖金系数，最后利用公式计算出每个人的奖金。奖金=（售价×数量-8折价格×数量）×15%=（定价×实际折扣×数量-定价×0.8×数量）×15%=定价×数量×（实际折扣-0.8）×15%实例5.9 销售人员奖金统计任务描述小李刚参加工作，每个月有了固定的收入。他选择了某银行的零存整取存款方案，准备每月的固定日子存入银行1500元，为期3年，并享有3.25%的年利率。计算出小李3年后的存款账户总额。任务分析“零存整取”是工薪阶层常用的投资方式，这就需要计算该项投资的未来值，从而决定是否选择某种储蓄方式。零存整取必须是每月存一次，每次存相同金额。零存整取定期储蓄计息公式是：利息＝月存金额×累计月积数×月利率，其中累计月积数＝（存入次数＋1）÷2×存入次数。利用Excel的函数功能可以简化处理工作，选用FV函数（基于固定利率及等额分期付款方式，返回某项投资的未来值）可以方便快捷地求出零存整取的存款账户总额。实例5.10 零存整取的存款总和5.6.1图表的类型在Excel

2016中可以创建各种类型的图表，各种类型图表的作用如下：1.柱形图：是用宽度相同的柱形的高度或长短来表示数据多少的图形。用于显示一段时间内的数据变化或显示各项之间的比较情况。5.6图表的使用2.折线图：可显示随时间而变化的连续数据，适用于显示在相等时间间隔下数据的趋势。5.6.1图表的类型饼图：是用圆形及圆内扇形的角度来表示数值大小的图形。显示一个数据系列中各项的大小与各项总和的比例。圆环图:圆环图是由两个及两个以上大小不一的饼图叠在一起，挖去中间的部分所构成的图形。饼图圆环图5.6.1图表的类型散点图：是用二维坐标展示两个变量之间关系的一种图形。它是用坐标横轴代表变量x，纵轴代表变量y，每组数据（x，y）在坐标系中用一个点表示，n组数据在坐标系中形成的n个点称为散点，由坐标及其散点形成的二维数据图称为散点图。气泡图气泡图是一种特殊的散点图，可显示3个变量的关系。气泡图最适合用于较小的数据集。

7.雷达图雷达图是显示多个变量的常用图示方法。雷达图显示数值相对于中心点的变化情况，显示时可以为每个数据点显示标记。5.6.1图表的类型5.6.2创建图表用快捷键创建图表通过“插入”选项卡的“图表”组来创建图表5.6图表使用任务描述：为电器销售情况表创建一张冰箱、彩电、空调的“销售额”图。任务分析：可从“销售情况图表”工作表中获取所需要的数据信息，通过“插入”选项卡的“图表”组进行创建图表。用图表将数据展示出来会更加形象和直观。实例5.11 创建“销售额”的柱状图和饼图任务描述：根据“销售情况图表”工作表中的数据，绘制一张“销售情况”图表，其中“销售额”用柱形图；“数量”用折线图来展示。如图所示。实例5.12制作“销售情况”双轴柱线复合图1.图表编辑移动图表改变图表大小删除图表5.6.3图表编辑与格式设置2．图表格式设置（1）“设计”选项卡在该选项卡中可以完成更改图表类型，切换行、列数据，快速设置图表布局和图表样式等。如图所示。5.6.3图表编辑与格式设置2．图表格式设置（2）“格式”选项卡在该选项卡中可以设置图形中各组成元素的形状样式、填充、轮廓和效果，以及文本的艺术字样式，图形的大小等。如图所示。5.6.3图表编辑与格式设置任务描述：使用“图表工具”选项卡对创建的图表的大小、文字格式背景等进行必要的调整。实现如图所示的美化效果。实例5.13美化“销售情况图表”5.7.1数据清单的使用数据清单是工作表中包含相关数据的一系列数据行，它可以像数据库一样接受浏览与编辑等操作。5.7工作表的数据库操作任务描述：使用“成绩表”数据记录单，执行查询操作。实例5.14 使用数据清单查询“成绩表”5.7.2数据排序排序是对工作表中的数据进行重新组织安排的一种方式。在Excel

2016中用户可以根据数据区域中的数值对数据进行排序。5.7工作表的数据库操作任务描述：针对“成绩表”工作表，对其按“总分”进行降序排序，如图所示。实例5.15 对“成绩表”进行排序5.7.3数据筛选Excel提供的筛选功能可以把暂时不需要的数据隐藏起来，而只显示那些符合条件的数据记录，系统提供了两种筛选方法：自动筛选和高级筛选。5.7工作表的数据库操作任务描述：在“家电销售情况表”中，筛选出1月份销售量在3台（含3台）以上的产品数据。如图所示。实例5.16销售数量筛选任务描述：将成绩表中数学高于70分、语文高于80分且计算机高于70分的学生成绩筛选出来。实例5.17按条件对成绩进行高级筛选5.7.4分类汇总在实际工作生活中经常要对一些数据进行分类统计。在Excel中，分类汇总的方式有求和、平均值、最大值、最小值等多种。要进行分类汇总的数据表的第一列必须有列标签，在分类汇总之前必须先对数据进行排序，使得数据中拥有同一类关键字的记录