excel搜索单元格部分字段_统计学的Excel操作方法（一）

Excel操作方法在《统计学》中非常重要，统计的所有计算都必须通过计算机的运算来完成。具体需要用到Excel操作方法来解决问题的知识点如下表。我们将分两期来介绍统计学的Excel操作方法。今天先介绍《统计学》前三章内容的Excel操作方法。

章节	知识点
第一章统计学及其基本概念	1.1 调用函数
第二章数据的收集与整理	2.1 简单随机抽样
	2.2 变量数列的次数统计
第三章统计表与统计图	3.1制作平面透视表
	3.2制作立体透视表
	3.3完全相同型表格的合并
	3.4不完全相同型表格的合并
	3.5饼图绘制
	3.6条形图绘制
	3.7绘制直方图、折线图和曲线图
第四章数据的描述性分析	4.1描述统计
第五章参数估计	5.1正态总体均值的区间估计
第六章假设检验	6.1单个正态总体均值的检验
	6.2两个非正态总体均值之差的检验
第七章方差分析	7.1单因素方差分析
	7.2无交互作用的双因素方差分析
	7.3有交互作用的双因素方差分析
第九章相关与回归分析	9.1一元线性回归分析
	9.2多元线性回归分析

1.1  调用函数

【例题】利用sum函数对数据1.1中的8个数字求和。

【Excel操作方案】

①调用菜单“公式”—“插入函数”，出现“插入函数”对话框。可以直接搜索函数，也可以根据类别选择函数。

②在“或选择类别”中选择了想使用的函数类别后，下方的“选择函数”栏中列出了该类函数中所有具体的函数。选中“常用函数”中的求和函数“sum”，单击“确定”按钮，进入下一个对话框。在该框内填入各参数所需的数据(可以是常量、单元格或区域引用、名称等多种形式)，每个参数都给予一定的提示。在对话框下方显示出当前填入参数的计算结果。

【注意】当熟悉了函数用法后，可以直接在Excel单元格中输入“=函数名”来调用函数。

2.1  简单随机抽样

【例题】根据数据2.1中的470人名单的序号，抽取一个样本容量为10的随机样本。

【Excel操作】①在进行数据分析前，打开“数据”菜单，查看此菜单下有没有“数据分析”命令。如果没有，表明数据分析工具库尚未安装到正在运行的Excel中。这时需要选择菜单“文件”—“选项”，打开“Excel选项”对话框，选择“加载项”，接着在左下方的“管理A：”项选择“Excel加载项”，并点击“转到”，出现“加载宏”对话框，在“可用加载宏”中选择“分析工具库”后单击“确定”按钮退出。

②调用菜单“数据”—“数据分析”，打开“数据分析”对话框，见下图。

③选择其中的“抽样”，打开对话框。

④正确填写相关信息后，点“确定”按钮退出，结果将显示在C列中，E列是又一次运行的结果。

2.2  变量数列的次数统计

【例题】数据2.2是某苗圃110株树苗的高度(单位：cm)，进行分组并统计各组的次数。

【提示】对于变量数列的次数统计，用Frequency函数，格式为：Frequency(Array,Bins)，其中Array是指原始资料的存放区域，Bins是指统计分组的上组构成的数组。因Frequency函数遵循的是“上组限在内”的原则，与统计学次数统计的原则相违背。因此，在使用Frequency函数统计次数时，要根据实际情况设立虚拟上限。

【Excel操作】①计算基础数据，放在B和C列。具体来说，利用函数count得到数据个数N，利用max和min函数得到最大值和最小值，从而得到全距R。利用斯透奇斯规则得到组数m=1+3.322lg(N)和组距i=R/m。进一步确定分组情况，见D列。

②列出各组的虚拟上限，见H3:H10。在单元格I3中输入函数“＝Frequency(A2:A111,H3:H10)”。用鼠标选定函数返回值存放的区域I3:I10。按下F2键后，同时按下组合键“Ctrl+Shift+Enter”，各组次数将放在I3:I10。结果和公式截图如下。

3.1  制作平面透视表

【例题】数据3.1是王氏公司2011年各季度在各个地区销售9种产品的情况，数据库的四个字段是：地区、季度、商品代号、销售额。要求制作按地区和季度交叉分类的销售额总和的平面透视表。

【Excel操作】①选择数据库表区域：选中当前数据库表中的任一单元格，系统会自动把整个数据库表作为数据来源区域。

②启动数据透视表指南：调用菜单中的“插入”—“数据透视表”，系统弹出“创建数据透视表”对话框。如果数据不是来源于Excel的当前数据库表，可以选择对话框中的“使用外部数据源”项。

③单击“确定”按钮，在工作表上出现透视表雏形。

④按提示将字段名拖拽到相应的位置。本例将“地区”放在行字段，“季度”放在列字段，“销售额”放在值字段，并求和，结果见下图。点击“地区”和“季度”旁边的下拉菜单，可指定显示项目。

3.2  制作立体透视表

【例题】数据3.2是王氏公司2011年各季度在各个地区销售9种产品的情况，数据库的四个字段是：地区、季度、商品代号、销售额。要求制作按商品、地区和季度交叉分类的销售额平均值立体透视表。

【Excel操作】立体表透视表的制作基本与平面表一致，从第四步形成透视表雏形后略有不同。

④按提示将字段名拖拽到相应的位置。将“商品代号”放在报表筛选字段，“地区”放在行字段，“季度”放在列字段，“销售额”放在值字段，此时得到的透视表中的数据默认是求和。

⑤双击左上角的“求和项”，系统会弹出对话框，用以控制数据透视表中的数据生成，如生成平均数、最大值等，还可以修改数字格式。

选择“平均值”，按“确定”按钮，即得到我们需要的立体透视表。

⑥点击“商品代号”、“地区”和“季度”旁边的下拉菜单，可指定显示的项目。如在“商品代号”旁边的下拉菜单中选择“C”，就可以得到仅显示C商品在各地区各季节的销售额平均值。

3.3  完全相同型表格的合并

【例题】数据3.3.1-3.3.3是宏大计算机公司某月上、中、下旬销售利润表，要求汇总各旬情况，完成月度销售利润表。

【Excel操作】①将表格的文字部分复制到另一个工作表3.3中，并适当修改，如将“上旬”改为“月度”，该表将存放汇总结果。选定单元格B3，系统默认从该单元格向右向下存放合并后的数据。

②选取“数据”—“合并计算”，弹出“合并计算”对话框。在“引用位置”文本框内，通过选取或直接键入方式确定源区域。如果源区域位于打开的工作表上，可以通过鼠标单击加拖动方式。点击“添加”按钮，将“引用位置”文本框内的区域加入到“所有引用位置”列表框内。

③注销“标签位置”复选钮内的“首行”、“最左列”选择框，注销“创建指向源数据的链接”选择框，其结果如下。

④如果选中“创建指向源数据的链接”选择框，有两点发生变化。一是，Excel自动创建分级显示符号，它可以查看数据的来源；二是，如果源数据区域中的数据被修改，则汇总表的数据也随之改变，而前者不会发生变化。

【注意】在合并表格时，第一，要留足适当大空间给目标区域，否则合并计算结果要覆盖原有数据，造成数据丢失。第二，只有数值数据参与合并计算，文本和公式不能进行合并计算。

3.4  不完全相同型表格的合并

【例题】数据3.4.1-3.4.3是计算机总公司下属宏大公司、正大公司和光大公司1-4月的销售量统计报表。要求汇总出分品种、分月份的销售量，注意这三个公司销售品种是不同的。

【Excel操作】①新建一张工作表3.4用以存放汇总结果，将光标移至存放结果的左上角。选取“数据”—“合并计算”，弹出“合并计算”对话框。

②在“引用位置”文本框内，通过选取或直接键入确定源区域。如果源区域位于打开的工作表上，可通过鼠标单击加拖动方式。单击“添加”按钮，将“引用位置”文本框内的区域加入到“所有引用位置”列表框内。在“标签位置”复选钮选中“首行”、“最左列”选择框，注销“创建指向源数据的链接”选择框，最后单击“确定”按钮，生成汇总结果，见下图。

③如果选中“创建指向源数据的链接”选择框，其结果下图。

3.5  饼图绘制

【例题】根据数据3.5中2011年三次产业增加值的资料，绘制2011年三次产业增加值的饼图，以反映产业的结构比例。

【Excel操作】①选择数据：选择区域B1:D2。

②绘制大致图形：调用菜单“插入”—“饼图”—“三维饼图”—“分离型三维饼图”，出现饼图雏形。

③添加标题：单击图表区的任意位置，菜单栏出现“图表工具”。依次单击“图表工具”—“布局”—“图表标题”—“图表上方”，在图表上方出现文本框，在该文本框中键入“2011年三次产业增加值比重”。

④修改图例：双击图例区，出现“设置图例格式”对话框，修改图例格式。

⑤添加数据标签：单击饼图的任一部分，选中整个饼图，右击选择“添加数据标签”，显示出各产业的增加值。双击任一数据标签，出现“设置数据标签格式”对话框。在“标签选项”一栏勾选“百分比”、“显示引导线”以及“数据标签外”，其它选项不勾选。也可以在“图表工具”—“布局”—“数据标签”菜单栏中修改。

⑥修改绘图区：双击绘图区，出现“设置数据系列格式”，对整个饼图修饰；再双击任一部分，出现“设置数据点格式”，对饼图的每一部分修饰。

3.6  条形图绘制

【例题】根据数据3.6中汇总的2011年度考核情况，绘制条形图。

【Excel操作】①选择数据：选择区域C1:D6。

②绘制大致图形：调用菜单“插入”—“柱形图”—“二维柱形图”—“簇状柱形图”，出现一个粗糙的竖置条形图。

③添加标题：单击图表区的任意位置，菜单栏出现“图表工具”。单击“图表工具”—“布局”—“坐标轴标题”—“主要纵坐标标题”—“横排标题”，在图表左方出现文本框，在该文本框中键入“人数”，并将其拖到图表左上方。删除右侧的图例“系列1”。

④添加数据标签：单击任意长方条选中整个条形图，右击选择“添加数据标签”。

⑤进一步修饰：双击感觉不满意的地方，例如横坐标轴、纵坐标轴、图表标题、纵轴标题、绘图区、数据系列、数据点进行修饰。

⑥在“图表”中选择“条形图”来绘制横置条形图。

3.7  绘制直方图、折线图和曲线图

【例题】根据数据3.7中对110株树苗的分组情况绘制直方图、折线图和曲线图。

【Excel操作】①选择数据：选择区域A1：B11。

②绘制直方图大致图形：调用菜单“插入”—“柱形图”—“二维柱形图”—“簇状柱形图”，出现一个粗糙的竖置条形图。单击任意长方条选中整个条形图，右击选择“设置数据系列格式”，出现“设置数据系列格式”对话框，在“系列选项”一栏，将“分类间距”调整为“无间距”，单击“关闭”退出，出现直方图的大致形状。

③添加纵轴、横轴以及图表标题，删除图例项：方法同条形图的绘制。

④添加折线图：单击任意长方条选中整个条形图，右击选择“选择数据”，出现“选择数据源”对话框。在该对话框中，单击“添加”，出现“编辑数据系列”对话框，在“系列名称”一栏选择数据区域A2:A11，在“系列值”一栏选择数据区域B2:B11，单击“确定”返回到“选择数据源”对话框，再单击“确定”退出。此时，原来的每个长方条被分成两部分。选择要被更改成折线图的长方条，右击选择“更改系列图表类型”，出现“更改图表类型”对话框，选择“折线图”的第四个类型“带数据标记的折线图”，这时直方图和折线图同时显示。

⑤进一步修饰：双击感觉不满意的地方，例如横坐标轴、纵坐标轴、图表标题、纵轴标题、横轴标题、绘图区、数据系列、数据点进行修饰。

⑥右击折线图，选择“设置数据系列格式”，出现“设置数据系列格式”对话框，在“标记线样式”一栏勾选“平滑线”来绘制曲线图。

(未完待续)