资讯科技实验八数据的综合分析和图表应用

实验八数据的综合分析和图表应用1. 实验目的和要求 熟练掌握数据清单的排序； 熟练掌握数据清单的自动筛选； 熟练掌握数据清单的高级筛选；掌握条件区域的构成并在筛选中熟练应用； 熟练掌握分类汇总的条件，操作及对分级显示的解释； 掌握数据透视表的操作; 掌握利用数据表中的数据制作直方图、饼图、折线图、散点图的方法，理解四种图表对于描述数据统计特征的作用； 掌握图表格式的修改（标题、坐标轴、图例等）以及误差线的添加方式。.2. 实验环境 Office办公组件中的Excel电子表格软件。要求：版本最好是2007之后，机房开机应选择Win7；作业说明也基于2007以后版本（括號中的藍色命令為繁體版本所對應之命令）。不建议使用WPS表格、LibreOffice、iWork等软件编辑，以免作业检查程序处理时出现误判。 资讯科技课程所有的课件、实验要求和相关软件，可在链接/s/1Nssig_Bm8xUycPiR-s54VQ，提取密码: yvbx。Excel作业可参考网盘中的“2019excel范例.xls”。3. 实验内容打开实验七所保存的工作簿（活頁簿）文件，根据实验七批改确认邮件所附的意见进行修改，并在此基础上完成本次的实验内容。切记，不要在其他同学的实验七文件基础上完成本次作业，以免作业检查程序处理时出现误判。注意：所有针对数据清单的数据分析操作，仅需要定位于数据清单中任一单元格即可。在执行分析过程中，Excel会自己判断并选择整个数据清单。如不行，可根据课本P114(5)/125(6)的要求修改以符合数据清单的要求。3.1. 数据排序通过排序可以让数据清单中的数据按某种规则顺序排列，方便数据的浏览和筛选。排序后，数据的顺序将会改变（因此，切记不要直接针对源数据进行操作）。数据排序包括单列（欄）数据排序、多列（欄）数据排序和自定义排序。数据排序可使用“数据（資料）”“排序”完成，具体操作方法可参见课本P114-115(5)/125-126(6)的“多关键字排序”部分。作业部分：1. 将“数据源”工作表中的所有数据，即A1:J51单元格区域，复制到一张新工作表中，并设置“自动调整列宽（自動調整欄寬）”。然后，将该工作表重命名为“排序”。先可尝试利用排序按钮 对数据清单进行排序（注：非作业）。注意，定位到任一单元格，使用排序按钮便会对该单元格所在列（欄）进行相应的排序，且整个数据清单会相应变化。2. 然后，使用“排序”功能对“排序”表中的数据清单按主要关键字“分娩方式”升序（排序方式，A到Z），次要关键字“住院天数”降序（次要排序方式，最大到最小），进行多列（欄）数据排序。3. 绘制散点图以产妇的身高数据作为横坐标轴（水平軸），以体重数据作为纵坐标轴（垂直軸），绘制散点图（XY散佈圖）。图表放置在数据的右侧。图表标题为“部分产妇身高体重分布”；主要纵坐标轴标题（主垂直軸標題）为“体重（KG）”，文字方向为“竖排（垂直）”；主要横坐标轴标题（主水平軸標題）为“身高（CM）”。网格线（格線）设置为无。设置主要纵坐标轴（主垂直軸）“坐标轴选项（座標軸選項）”中的“最小值”为50，“最大值”为90，“主要单位（主要刻度間距）”为10，数值不保留小数位。设置“主要类型（主要刻度）”的“刻度线标记（刻度）”为“外部（外側）”。设置主要横坐标轴（主水平軸）“坐标轴选项（座標軸選項）”中的“最小值”为140，“最大值”为180，“主要单位（主要刻度間距）”为10，数值不保留小数位。设置横坐标轴（水平軸）和纵坐标轴（垂直軸）为1.5磅的黑色实线。找到散点图中的数据（155, 74.5）所对应的点（见下图红色点），将其标记单独改为红色的“”。注意，可先点该点，选中全部数据点，再次点击该点，即单独选中该点，右击“设置数据点格式（資料點格式）” “填充与线条（填滿與線條）” “标记（標記）”，对“标记选项（標記選項）”和“边框（框線）”进行设置。图 1 “排序”工作表样图（注意：出于截图考虑，部分列宽有所压缩）3.2. 数据的高级筛选高级筛选（進階篩選），是比“筛选”功能更强、更灵活的从数据清单中筛选数据的方法。注意：筛选条件处于同一行（列），表示字段之间的关系为“与（AND，即所有条件都要满足）”；筛选条件处于不同行（列），则关系为“或（OR，即满足任一条件即可）”。如果同时存在“与”和“或”的关系，则先算“与”，再算“或”。操作方法：先按筛选要求，自行构建“条件区域（準則範圍）”。然后，定位到数据清单中，点击“数据（資料）”选项卡“筛选”“高级（進階）”，指定列表区域（資料範圍，也就是数据清单所在范围）、条件区域（準則範圍）和输出区域的最左上角单元格（儲存格），便可获得筛选后的数据。具体可参见课本P116(5)/126(6)以及课件。作业部分：1. 新建一张工作表，将其重命名为“高级筛选”。选中“分组”工作表中的A4:O54单元格区域，将其“选择性粘贴（選擇性貼上）”到“高级筛选”表中。选择性粘贴（選擇性貼上）以A1为起始位置，并选择“值和数字格式（值與數字格式）”。然后，自行视情况调整“列宽（欄寬）”。2. 在“高级筛选”工作表中删除“随机数”列（欄）和“值”列（欄）。3. 然后，在“高级筛选”表中利用高级筛选（進階篩選），选出满足“年龄在34周岁及以上的正常A组病人”条件的记录（即分组为A组，且正常标记值为1，年龄大于等于34）。条件区域（準則範圍）放置在以A53为起始位置的单元格区域，并将结果复制到以A56为起始位置的单元格区域中。4. 再利用高级筛选（進階篩選），选出满足“剖宫产且住院天数小于6天的病人，或者，顺产且住院天数小于3天的病人”条件的记录（注意：“分娩方式”数据中的1表示“顺产”，2表示“剖宫产”）。条件区域（準則範圍）放置在以A68为起始位置的单元格区域，并将结果复制到以A72为起始位置的单元格区域中，注意该条件区域可有多种写法，图2提供了两种可能的布局。图2 “高级筛选”工作表部分数据样图（红色部分单元格是被遮挡的具体条件单元格。注意：因各人的随机分组情况不一样，所以第一个高级筛选筛选出的结果会各不相同，样图结果仅供参考。此外，蓝色框部分单元格表现了第二个高级筛选两种不同的写法框架供参考。）3.3. 数据的筛选和分类汇总“筛选”是一种简单快速的数据筛选方法。可通过点击“数据（資料）”“筛选”进入筛选状态。随后，通过设置各个字段的筛选器可完成相应的筛选操作。“筛选”各条件字段之间是一种“与（AND，即所有条件都要满足）”的关系。具体可参见课本P115-116(5)/126(6)。分类汇总（小計）可对数据清单某一关键字段值进行分类，并在此基础上完成其他数据的分类汇总分析。方法：首先对作为分类依据的字段进行排序（切记切记！），升序降序皆可。然后，使用“数据（資料）”“分类汇总（小計）”，在弹出的对话框中设置“分类字段（分組小計欄位）”、“汇总方式（使用函數）”以及“选定汇总项（新增小計位置）”后，便可完成数据的分类汇总分析。汇总结果可通过点击列（欄）名左边的1、2、3按钮显示不同层次的数据（可见图3的圈选位置）。具体可参见课本P117(5)/126-127(6)。作业部分：1. 将“数据源表”中的所有数据，即A1:J51单元格区域，复制到一张新工作表中，并设置“自动调整列宽（自動調整欄寬）”。然后，将该工作表命名为“分类汇总”。2. 在“分类汇总”工作表中利用“筛选”，选出满足“顺产且住院天数小于6天”条件的记录。并将筛选出来的数据（包括标题字段）复制粘贴到以A60为起始位置的单元格区域中。然后，点击“清除”按钮清除A1:J51单元格区域的筛选状态（注意：不是退出“筛选”，此时可进行新的筛选操作）。3. 对筛选出来的数据（即以A60为起始的单元格区域）进行分类汇总（小計），得到不同文化程度产妇的人数分布情况（切记要对分类字段排序，汇总项可以是任意字段。）。然后，将数据折叠到第二层次上。如无分级显示，则通过“数据（資料）”“创建组（組成群組）”“自动建立分级显示（自動建立大綱）”建立。4. 在汇总数据的基础上，绘制饼图（圓形圖）。图表要求：在图外侧显示不同文化程度的产妇占总人数的百分比的数据标签（資料標籤）；图表标题为“部分顺产产妇学历分布图”；图例放置在图表右侧。注意：可利用Ctrl键选择不相邻的单元格区域（注意：直接选中所有数据，可能无法直接出饼图，还要进一步修改数据才行）。分类汇总数据中的标题文字也是可以修改的（如“计数”字样是可以删除的）。样表中的饼图（圓形圖）样式和颜色仅供参考，可自行设置。此处为分级显示的第二层次，如无，则通过第三步的方法建立图 3 “分类汇总”工作表部分数据样图3.4. 基于数据透视表的分析数据透视表（樞紐分析表）是根据数据清单，按照一定的汇总方式生成的一种关于数据关联的交互式报表。实现方法：“插入”“数据透视表（樞紐分析表）”。然后，根据分析目的，设置一个列标签（欄標籤），常用于放置因变量（结局）；多个行标签（列標籤），常放置自变量（影响因素），其中数值变量还可以进行分组。可参见课本P117-118(5)/127-128(6)。作业部分：1. 新建一张工作表，将其重命名为“数据透视表”。选中“公式”工作表中的A4:M54单元格区域，将其“选择性粘贴（選擇性貼上）”到“数据透视表”表中。选择性粘贴（選擇性貼上）以A1为起始位置，并选择“值和数字格式（值與數字格式）”。然后，设置“自动调整列宽（自動調整欄寬）”。2. 利用数据透视表（樞紐分析表）对数据清单进行分析数据透视表（樞紐分析表）放置在当前工作表以A60为起始位置的单元格区域，以“BMI指数”作为行标签（列標籤），并进行分组（可通过设置“组字段（群組欄位，將欄位組成群組）”实现），“起始于（開始點）”设置为21，“终止于（結束點）”设置为33，“步长（間距值）”为3；以“正常”作为列标签（欄標籤），求出不同情况下的平均住院天数。所有数据设置显示格式为显示一位小数。同時，修改数据透视表（樞紐分析表）各标签文字如图4所示。3. 绘制柱形图（直線圖）在此基础上，绘制柱形图（直線圖）。图表放置在数据的右侧。图表标题为“不同BMI指数下产妇平均住院天数比较”；主要纵坐标轴标题（主垂直軸標題）为“平均住院天数”，文字方向为“竖排（垂直）”；主要横坐标轴标题（主水平軸標題）为“BMI指数”。图例放置在图表右侧。网格线（格線）设置为无。设置主要纵坐标轴（主垂直軸）“坐标轴选项（座標軸選項）”中的“最小值”为0，“最大值”为10、，“主要单位（主要刻度間距）”为2，数值不保留小数位。设置“主要类型（主要刻度）”的“刻度线标记（刻度）”为“外部（外側）”。设置横坐标轴（水平軸）和纵坐标轴（垂直軸）为1.5磅的黑色实线。注意：数据系列的颜色可自行设置。图 4 “数据透视表”工作表样图4. 作业提交事项保存工作簿（活頁簿）。工作簿（活頁簿）文件中的工作表应呈现以下或类似状态。然后，将该工作簿文件以邮件附件的形式（注意：不要以QQ邮箱中“