统计学基础 课件 任务五 Excel在统计整中的应用

主讲：任务五：Excel在统计整理中应用统计学基础目录01022实训教学内容033实训教学要求044实训操作步骤055上机操作066课后练习1实训教学目的01实训教学目的教学目的Excel软件有强大的数据处理功能。Excel软件在统计整理中的应用,主要是数据排序、数据筛选和数据汇总,编制统计表,绘制统计图。通过本任务实训内容的教学,学生可熟悉Excel软件的功能,初步掌握利用Excel软件的基本应用,提高实践能力。教学内容02实训教学内容1.利用Excel软件进行数据排序、数据筛选、数据汇总。2.利用Excel软件编制变量数列。3.利用Excel软件编制统计表。4.利用Excel软件绘制统计图。03实训教学要求学生先组成学习小组,一般由2-4人组成。实训前需要先熟悉Excel软件,也可自己收集微观经济数据作为原始数据进行整理。Excel界面最上方是标题栏，显示当前工作簿的名称。标题栏状态栏状态栏位于窗口底部，用于显示当前状态，如公式计算进度、选中区域的汇总值等。操作区操作区是用户编辑电子表格的主要区域，用于输入和处理数据。工具栏工具栏集中了常用操作命令，方便用户快速访问。

菜单栏包含文件、开始、插入、页面布局、公式、数据、审阅、视图和帮助等选项，提供各种功能。菜单栏Excel界面介绍04操作步骤快速访问工具栏允许用户自定义常用命令，提高工作效率。用户可以将功能区上选项卡中的一个或几个命令在此区域独立显示，以减少在功能区查找命令的时间，提高工作效率。快速访问工具栏自定义工具栏功能区可以通过多种方式显示或隐藏功能区，如单击折叠功能区按钮、使用功能区显示选项按钮、双击选项卡或使用快捷键Ctrl+F1。显示与隐藏功能区位于标题栏下方，包含9个选项卡，每个选项卡分为多个组，每组包含多个命令。选项卡与命令名称框名称框用于显示当前活动对象的名称信息，如单元格列标和行号、图表名称等。用户可以通过在名称框中输入单元格的列标和行号来快速定位到相应的单元格，例如，输入“C3”时，光标定位到单元格C3。显示活动对象定位单元格编辑栏显示单元格内容编辑栏用于显示当前单元格内容，或者编辑所选单元格。用户可以在编辑栏中直接修改单元格的内容。工作表区域编辑单元格内容工作表区域用于编辑表中各单元格内容，用户可以在此区域输入、修改和格式化数据。01工作表操作一个工作簿可以包含多张工作表，用户可以重命名、移动或复制工作表。双击工作表标签或右击工作表标签，可对工作表进行重命名。左击工作表标签的同时，拖动工作表标签，可更改工作表位置。按【Ctrl+鼠标左键】，拖动选中的工作表标签，可复制工作表。02状态栏状态栏用于显示当前的工作状态，包括公式计算进度、选中区域的汇总值等。显示工作状态01用户可以自定义状态栏显示内容，如需更改状态栏显示内容，可将光标放在状态栏，单击鼠标右键，可自定义状态栏。自定义状态栏02后台视图三栏式设计操作栏功能信息栏功能属性栏功能在Excel2016中，单击功能区左上角“文件”选项卡，可进入后台视图界面。后台视图采用三栏式设计，分别是操作栏、信息栏和属性栏。操作栏可以完成新建、打开、保存、另存、打印、共享和关闭等工作。信息栏可以完成保护工作簿、检查工作簿、管理工作簿等工作。属性栏可以对工作簿的属性、日期、人员信息等进行修改设置。二、数据排序Excel可以对文本、数字、日期类型的数据进行多行或多列排序。多行/列排序Excel支持按单元格颜色、字体颜色、单元格图标等特殊格式进行排序。特殊格式用户可以自定义序列进行排序，以满足特定需求。自定义序列案例：销售业绩排名某公司某月销售人员的销售业绩表包括产品分类、产品编号、计量单位、销售单价、销售数量和销售额等信息。原始资料产品分类产品编号计量单位销售单价销售数量销售额A类A1001罐23030069000A类A1002罐12030036000A类A1003类B1004罐28026072800B类B1005罐31026080600B类B1006罐21026054600B类B1007罐28225070500B类B1008罐28227076140B类B1009罐12028233840C类C1010罐31031196410C类C1011罐29031190190C类C1012罐150311466501.将原始资料复制到Excel工作表中。2.选中标题行中“销售额”所在的单元格,单击【数据】选项卡下【排序和筛选】组中的【排序】命令。3.在弹出的对话框中,单击【排序依据】下拉列表中的【销售额】选项,单击【次序】下拉列表中的【降序】选项后,单击【确定】按钮。案例：销售业绩排名操作步骤三、数据筛选自动筛选允许用户快速查找具有特定条件的记录。01自动筛选高级筛选高级筛选提供更复杂的筛选条件，可以进行多个条件的组合筛选。02案例：学生成绩筛选某班学生部分课程成绩表：原始资料案例：学生成绩筛选操作步骤1.选中标题行,单击【数据】选项卡下【排序和筛选】组中的【筛选】命令。2.单击【地理】标题字段右侧按钮,在弹出的下拉菜单中选择【数字筛选】选项,在弹出的下一级菜单中选择【大于或等于】选项。3.在【自定义自动筛选方式】对话框中,在【大于或等于】文本框中输入“80”,单击【确定】按钮。4.单击【历史】标题字段右侧按钮,在弹出的下拉菜单中选择【数字筛选】选项,在弹出的下一级菜单中选择【大于或等于】选项，在【自定义自动筛选方式】对话框中,在【大于或等于】文本框中输入“85”,单击【确定】按钮。四、变量数列编制010203按日产量分组在空白部分增加日产量分组结果表,可以将日产量分成5、6、7、8、9五组。计算各组人数选中M3单元格,在公式编辑栏输入公式“=COUNTIF(A2:J11,L3)”。计算人数比重选中单元格N3,在公式编辑栏输入公式“=M3/100100”。（一）单项数列（二）组距数列输入数据将原始资料输入单元格并排序，计算全距并确定组数和组距。第一步:,选择单元格E2至E6放置各组上限:150、160、170、180、190。第二步:选择单元格F2至F6作为放置分组结果的区域,单击【公式】选项卡下【函数库】组中的【插入函数】命令,在弹出的对话框中,单击【或选择类别】下拉列表中的【统计】选项后,在【选择函数】列表框中单击【FREQUENCY】函数,单击【确定】按钮,进入函数参数对话框。01使用FREQUENCY函数(1)在Data_array框中输入待分组的原始数据区域“A2:A41”,在Bins_array框中输入数据接收区间“E2:E6”,(2)FREQUENCY函数要求按组的上限分组,且FREQUENCY函数统计结果包括上限,但不包括下限;同时,输入n个分组标志,FREQUENCY函数给出(n+1)个结果。因此,为了与统计中要求的“上限不在内”原则相一致,上限应小半个单位,即将单元格E2至E6中的数值150、160、170、180、190改为149.5、159.5、169.5、179.5、189.5,同时按【Shift+Ctrl+Enter】组合键,可将次数分布记入指定的单元格F2至F6中,最终显示各组次数分布为3、6、15、12、4。02（三）利用Excel表现变量数列第一步:依据分布数列,可重新计算比重及累计次数和累计比重。新建一张工作表,在第一行中输入“分组”“次数”“向上累计”“向下累计”,可使用【开始】选项卡下【对齐方式】组中的【合并后居中】命令。(1)计算比重,选中单元格C3后输入“=B2*50/100”或“=B2*BJ$8/100”,结果为12%,然后利用填充柄功能,可依次计算出单元格C4至C8的值。(2)计算向上累计人数,选中单元格D3后输入“=B3”,计算结果为6,再选中单元格D4后输入“=D3+B4”,计算结果为24,然后利用填充柄功能,计算出单元格D5至D7的值。(3)计算向下累计人数的方法同(2)。第二步:利用图表功能,绘制次数分布直方图或折线图。(1)选定数据区域A3:B7,单击【插入】选项卡下【图表】组右下方的【查看所有图表】按钮。(2)在弹出的【插入图表】对话框中,单击【所有图表】标签,选择【柱形图】后,单击【确定】按钮。(3)单击图形右侧的【图表元素】按钮,添加【坐标轴】【坐标轴标题】【图表标题】【数据标签】等。(4)设置图表标题、坐标轴标题等后即完成柱形图绘制。五、数据汇总Excel可以对数据进行单个字段的分类汇总,汇总方式有求和、计数、平均值、最大值、最小值、乘积、数值计算、标准偏差和方差等。单字段分类汇总Excel可以对数据进行多个字段的分类汇总，按不同的字段进行多层次的汇总分析。多字段分类汇总（一）分类汇总操作步骤第一步:按部门和性别排序。选中所有数据列,单击【数据】选项卡下【排序和筛选】组中的【排序】命令,在弹出的【排序】对话框中,单击【添加条件】按钮,在【主要关键字】下拉列表中选择【部门】,在【次要关键字】下拉列表中选择【性别】,进行升序排序,第二步:按部门进行分类,对基本工资求平均数。选中“基本工资”单元格,单击【数据】选项卡下【分级显示】组中的【分类汇总】命令。在弹出的【分类汇总】对话框中,在【分类字段】下拉列表中选择【部门】,在【汇总方式】下拉列表中选择【平均值】,在【选定汇总项】列表框中选择【基本工资】后,单击【确定】按钮,完成以部门字段进行分类,对基本工资求平均数。第三步:按性别进行嵌套分类,对基本工资求平均数。选中“基本工资”单元格,单击【数据】→【分类汇总】。在弹出的【分类汇总】对话框中,在【分类字段】下拉列表中选择【性别】,在【汇总方式】下拉列表中选择【平均值】,在【选定汇总项】列表框中选择【基本工资】,取消勾选【替换当前分类汇总】复选框后,单击【确定】按钮,完成在以部门字段进行分类的基础上再按性别进行嵌套分类,对基本工资求平均数,（二）数据透视表创建数据透视表第一步:创建数据透视表。在工资表中选择任一单元格,单击【插入】选项卡下【表格】组中的【数据透视表】命令,在弹出的【创建数据透视表】对话框中,在【表/区域】框中选择“A1:K29”区域,在【选择放置数据透视表的位置】中选择【新工作表】后,单击【确定】按钮,。字段设置在右侧数据透视表字段设置中,单击【性别】【基本工资】【部门】字段，汇总出各个部门的基本工资总额（三）函数汇总常用函数Excel中常用的统计汇总函数有SUM、COUNT、COUNTA、SUMIF、COUNTIF、AVERAGE等。01案例：生产订单汇总某公司2024年7月生产订单登记表：02（三）函数汇总1.汇总生产订单总数在单元格A17中输入“生产订单总数”,选中单元格B17,单击【公式】选项卡下【函数库】组中的【插入函数】命令,在弹出的对话框中,单击【或选择类别】下拉列表中的【统计】选项,在【选择函数】列表框中单击【COUNTA】函数后,单击【确定】按钮。在【函数参数】对话框中,将光标定位在【Value1】框中,单击数据选择图标,选择“A2:A15”后,单击【确定】按钮,即可计算出生产订单总数为14。（三）函数汇总2.汇总李梅负责的生产订单数在单元格A18中输入“李梅负责的生产订单数”,选中单元格B18,单击【公式】→【插入函数】,类别选择【统计】,找到【COUNTIF】函数,单击【确定】按钮。在【函数参数】对话框中,在【Range】框中选择“C2:C15”,在【Criteria】框中输入“李梅”,单击【确定】按钮,即可计算出李梅负责的订单数为6。（三）函数汇总3.汇总男性负责人负责的生产订单数在单元格A19中输入“男性负责人负责的生产订单数”,选中单元格B19,使用【COUNTIF】函数,在【Range】框中输入“D2:D15”,在【Criteria】框中输入“男”,单击【确定】按钮,即可计算出男性负责人负责的生产订单数为8。4.汇总未完成的生产订单数在单元格A20中输入“未完成的生产订单数”,选中单元格B20,使用【COUNTIF】函数,在【Range】框中输入“L2:L15”,在【Criteria】框中输入“未完成”或“<>已完成”,单击【确定】按钮,即可计算出未完成的生产订单数为6（三）函数汇总5.汇总订单数量大于35件的订单数在单元格A21中输入“订单数量大于35件的订单数”,选中单元格B21,使890用COUNTIF函数,在【Range】框中输入“G2:G15”,在【Criteria】框中输入“>35”,单击【确定】按钮,即可计算出订单数量大于35件的订单数为5。6.汇总生产数量大于40件且小于或等于80件的订单数在单元格A22中输入“生产数量大于40件且小于或等于80件的订单数”,选中单元格B22,单击【公式】→【插入函数】,类别选择【统计】,找到【COUNTIFS】函数,单击【确定】按钮。在【函数参数】对话框中,在【Criteria_range1】框中输入“H2:H15”,在【Criteria1】框中输入“>40”,在【Criteria_range2】框中输入“H2:H15”,在【Criteria2】框中输入“<=80”,单击【确定】按钮,即可计算出生产数量大于40件且小于或等于80件的订单数为5,（三）函数汇总7.汇总所有订单的交货数量在单元格A23中输入“所有订单的交货数量”,选中单元格B23,单击【公式】→【插入函数】,类别选择【常用函数】,找到【SUM】函数,单击【确定】按钮。在【函数参数】对话框中,在【Number1】框中输入“I2:I15”,单击【确定】按钮,即可计算出所有订单的交货数量为365。8.汇总所有女性负责人的库存数量在单元格A24中输入“所有女性负责人的库存数量”,选中单元格B24,单击【公式】→【插入函数】,类别选择【数学与三角函数】,找到【SUMIF】函数,单击【确定】按钮。在【函数参数】对话框中,在【Range】框中输入“D2:D15”,在【Criteria】框中输入“女”,在【Sum_range】框中输入“K2:K15”,单击【确定】按钮,即可计算出所有女性负责人的库存数量为88,另外,也可尝试使用SUMIFS函数进行计算。（三）函数汇总9.汇总所有男性负责人的未交数量在单元格A25中输入“所有男性负责人的未交数量”,选中单元格B25,单击【公式】→【插入函数】,类别选择【数学与三角函数】,找到【SUMIFS】函数,单击【确定】按钮。在【函数参数】对话框中,在【Sum_range】框中输入“J2:J15”,在【Criteria_range1】框中输入“D2:D15”,在【Criteria1】框中输入“男”,单击【确定】按钮,即可计算出所有男性负责人的未交数量为46。（三）函数汇总10.汇总所有负责人的平均订单数量在单元格A26中输入“所有负责人的平均订单数量”,选中单元格B26,单击【公式】→【插入函数】,类别选择【统计】,找到【AVERAGE】函数,单击【确定】按钮。在【函数参数】对话框中,在【Nu