上机1用EXCEL对原始数据进行分组汇总范文.docx

上机用Excel对原始数据进行分组汇总［实验目的］掌握用 EXCEL 进行数据的整理［实验内容］编制变量数列对原始数据进行简单与交叉（复合）分组汇总 ［操作指导］ 例1：对品质型原始数据进行简单分组汇总资料见上机训练数据“性别与年龄构成工 作表” 方法之一：利用 countif 函数方法之二：利用数据透视表例 2：用对数值型型原始数据进行简单分组汇总资料见上机训练数据“能力测试分工 作表”编制变量数列）方法一：利用countif函数等距或异距均可） 方法二：利用直方图分析工具一般是等距，异距也可但不宜直接作图）步骤：第一步一一打开Excel，在某一空白列中输入需要编制变量数列的所有原始数据，每一 单元格输入一个变量值；第二步一一利用函数找出原始数据中的最大值（用函数max）与最小值（用函数min） 第三步——利用公式求出极差（即最大值与最小值之差），依据数据类型、多少与分布 特点确定组数与组距以及组限第四步——选择一空列，定组限名如“按能力分分组”，输入除最后一组以外的各组的 上限值（注意， Excel 默认的是上限在本组内）；第五步——单击“工具”下拉式菜单，选定“数据分析”选项，在弹出的对话框中选定输出选项r输出区域（D： 怦新工作表组（D: r新工作薄址） 厂柏拉图（A）厂累积百分率他；第六步——在对话框中“输入区域”框内键入原始数据所在单元格，绝对、相对引用均直方團1厂输入 直方图”，再单击“确定”，得到如下图所示的对话框：输入区域（1）:取消接收区域⑹:帮助⑪|厂标志©可。

在“接收区域”框内键入组限所在单元格）选中“标志” （当引用单元格未包括标志所在单元格时，不选） 可在“输出区域”框内键入一空列的某一单元格 可选择“累积百分率”选择“图表输出” 单击“确定”第七步——为了把变量数列转化为平常表达习惯，需将表中的“频率”改成次数名称“人 数”，将各组上限值依次改为区间，在与最后一组相邻的下一行单元格写上合计，选定“合 计”单元格右邻的单元格并在工具栏上双击“工”符号；还可在与次数相邻的列计算“比重” ［计算时注意公式中单元格的相对引用（分子）与绝对引用（分母）］第八步——默认直方图形式为柱形图，可以调整为标准的直方图形式具体方法是：在 图中“柱”上右击弹出菜单，选“数据系列格式”命令，再选“选项，”将其对话框中的“间输出结果如下：距宽度”调整为“0”即可^1 尸一•I特别提醒：由于每一组对数据值小于或等于上限的数据进行计数，所以在确定组限时，如果 上组限不包含在本组内，一定要选择小于上组限的数方法三：利用频数分布函数一般是等距，异距也可但不宜直接作图）步骤：第一步一一打开Excel，在某一空白列中输入需要编制变量数列的所有原始数据，每一 单元格输入一个变量值；第二步一一利用函数找出原始数据中的最大值（用函数max）与最小值（用函数min）。

第三步——利用公式求出极差（即最大值与最小值之差），依据数据类型、多少与分布 特点确定组数与组距以及组限第四步一一选择一空列，定组限名同前注意，Excel默认的是上限在本组内）； 第五步——方式一：选定一列多行单元格区域（注意行数与组数对应） ,单击“插入” 下拉式菜单，选定“函数”选项，在弹出的对话框中选定“统计”类别下的frequency （如 下图1）,再单击“确定”，得到对话框在date_array旁边的方框内输入原始数据所在单 元格；在bins_array旁边的方框内输入组限所在单元格（也可以直接输入各组组限，注意 要用大括号括住且各组限之间用分号隔开）（如下图2），然后同时按Ctrl/shift/enter键， 则得到各组频数方式二：也可以选定一列多行单元格区域（注意行数与组数对应）后，直 接输入“ =frequency （原始数据所在单元格，组限所在单元格）”，然后同时按 Ct rl/shif t/en ter键，则得到各组频数图1图2方法之四：利用数据透视表详见课堂上机操作讲解宜等距)例 3：对原始数据进行交叉分组汇总方法之一：利用数据透视表详见课堂上机操作讲解方法之二：利用数组求和函数sum。

A 不定义名称如例子中，在性别为“男”，年龄为<25 岁的单元格中输入：=SUM(($B$3:$B$52=$I4)*($C$3:$C$52〈25))，然后同时按 ctrl、shift、enter 键，则得到25 岁以下男性人数同理，依次在 25-35 岁、 35-45 岁、 45-55 岁及>=55 岁的单元格中以下相应公式：=SUM(($B$3:$B$52=$I4)*($C$3:$C$52>=25)*($C$2:$C$52〈35))=SUM(($B$3:$B$52=$I4)*($C$3:$C$52>=35)*($C$2:$C$52〈45))=SUM(($B$3:$B$52=$I4)*($C$3:$C$52>=45)*($C$2:$C$52〈55))=SUM(($B$3:$B$52=$I4)*($C$3:$C$52>=55)) 而各年龄段女性人数，只要选定上述单元格用填充柄即可B 先定义名称定义名称的便利之处是不用输入上述公式中复杂的单元格符号详见课堂上机操作讲解例 4：用 Excel 对调查问卷原始数据进行简单分组与交叉分组汇总一、原始数据录入的注意事项问卷中问题的输入：按列输入对于只能从备选项中择一的问题，一个问题占用一个单 元格；其它情况（如可任选多项或可选限定的项数并排序）最好是一个备选项占用一个 单元格即一个备选项作为一个变量，也可以只占用一个单元格。

问卷中对问题回答的输入：按行输入即一份问卷占用一行对于非数值型数据，最好用 数字序号代替二、简单分组汇总方法：利用 countif 函数操作说明：假设某问卷问题占有从B列（问卷中第一个问题）至AA列共26列，第4-203行为200 份问卷对各问题回答的录入，且录入多为数字序号；对每个问题的回答序号有1-7不等 即有些备选项只有 2 项，最多的备选项是7 项可以在 A205 单元格内输入：“选 1”，表示对每个问题选第一个备选项的人数然后利 用填充柄拖至A211，会依次出现选2、选3至选7在A212单元格中输入“总计”在B205单元格内输入：“ =countif（$B$4:$B$203,l） ”，然后回车，即可得到对第一个问 题选第一个备选项的总人数；选定B205单元格，利用填充柄拖到B211单元格，依次 选 B206、 B207 至 B211 单元格，将相应单元格公式中的“1”改为“2”、“3”至“7”， 这样则得到对第一个问题选择各个备选项的总人数当然，如果本题只有两个备选项 则从 B207 单元格开始显示为“0”；选定B212单元格，对B205至B211单元格求和可用工具栏上的求和按钮或sum函数。

可以选定A214单元格并输入“各选项所占比重”，将A205： A212单元格复制到A215： A222 单元格；在B215单元格内输入：“=B205/B$212*100”，然后回车，即可得到对第一个问题选第 一个备选项的人数占回答总人数的比重；选定B215单元格，利用填充柄拖到B222单 元格，即得到该问题选每一个选项的人数占全部回答人数的比重；选定B205： B222，利用填充柄拖至问卷的最后一列即AA222对于所有只能从备选项中 择一的问题，既得到了选每一项的人数，也得到了其所占比重对于可以选择多项的情 况则要对比重另行计算三、 交叉分组汇总（介绍最方便的两种） 方法一：利用数据透视表具体操作参照前面项目） 方法二：利用数组求和函数具体操作参照前面项目）四、 多项选择问题汇总方法简介方法一：原始数据输入办法——备选答案的每一个选项作为一个变量输入，每一份问卷选择项用 同一符号（数字或字母）记入相应变量列；使用 countif 函数汇总每一选项总频数，构造公式对汇总后的这一行所有选项计算相对 频数即比率方法二： 原始数据输入办法——将每一份问卷选择的选项的代号（字母或数字，最好是字母）输 入一个单元格选定某一列若干空白单元格分别输入各备选项的代号，如选定P501-506单元格依次输 入 abcdef；在 Q501 单元格输入函数：=SUMPRODUCT(ISNUMBER(FIND($P501,Q$3:Q$491))*($AJ$3:$AJ$491=0))回车，即得 该多项选择问题中选a的总人数，然后使用填充柄即可汇总出选bcdef各项的总人数。

注：sumproduct函数：在给定的几组数组中，将数组间对应的元素相乘，并返回乘积之和 语法SUMPRODUCT(array1,array2,array3, ...)Array1, array2, array3, ... 为 2 到 30 个数组，其相应元素需要进行相乘并求和 说明数组参数必须具有相同的维数，否则，函数 SUMPRODUCT 将返回错误值 #VALUE! 函数 SUMPRODUCT 将非数值型的数组元素作为 0 处理FIND函数：用于查找其他文本字符串(within_text)内的文本字符串(find_text)，并从 within_text 的首字符开始返回 find_text 的起始位置编号也可使用 SEARCH 查找其他文 本字符串中的某个文本字符串，但是， FIND 和 SEARCH 不同， FIND 区分大小写并且不 允许使用通配符语法FIND(find_text,within_text,start_num)Find_text 是要查找的文本Within_text 是包含要查找文本的文本Start_num 指定开始进行查找的字符 within_text 中的首字符是编号为 1 的字符。

如果忽 略start_num,则假设其为1Isnumber函数：检验数值的类型并根据参数取值返回TRUE或FALSE 语法ISNUMBER(value)Value 为需要进行检验的数值为数字引用表达式中，$AJ$3:$AJ$491=0返回一个由TRUE和FALSE组成的数组，参与运算时，TRUE 当1计算，FALSE当0计算，FIND($P501,Q$3:Q$491)返回一个由数字和错误值组成的数组, ISNUMBER将数字转化为TRUE，将错误值转化为FALSE其实这和数学的1+1或1*1之 类的四则运算道理相近。