部分+数据表格处理的综合操作.ppt

第五部分 数据表格的综合操作,数据处理综合应用举例 工作簿间数据的复制及共享 工作簿的链接(合并计算) 模板调用 单变量分析 创建、编辑、总结方案 双变量分析 在图表中添加趋势线、误差线和外部数据,工作簿间数据的复制及共享,工作簿间数据的复制：打开两个要交换数据的工作簿；在一个工作簿中选择要复制的数据，执行复制命令；切换到另一工作簿，选择目标区域的第一个单元格，执行粘贴命令。 工作簿的共享：选择“工具”/“共享工作簿”命令；在对话框中选择“编辑”选项卡，并选中“允许多用户同时编辑，同时允许工作簿合并”复选框，单击确定，确认“是否保存文档”。,工作簿的链接(合并计算),此功能用于相似表结构的多个表进行类似数据的汇总计算。 打开两个需要链接的工作簿（如KS5-7A和KS5-7B）； 选择存放汇总结果的目标区域的第一个单元格，再选择“数据”/“合并计算”，打开对话框，如图； 在“函数”下拉列表框中选择汇总类型（求和、求平均等）,这里选择求平均； 单击“引用位置”右边的折叠框，先在一个工作簿中（如KS5-7A ）选择参加合并运算的数据区域（包括行或列标题），返回对话框，单击“添加”；再单击折叠按钮，在另一个工作簿中（如KS5-7B ）选择参加合并运算的类似的数据区域，返回对话框后单击“添加”按钮。 选择“最左列”或“首行”复选框,单击“确定”。,“合并计算”对话框,模板调用,打开一个需要使用模板的工作簿，如KS5-1.XLS； 选择“文件”/“新建”命令，在“新建工作簿”面板中选择“本机上的模板”，打开“模板”对话框； 在对话框中选择“电子方案表格”选项卡，在模板列表中选择所需的一个模板，单击“确定”； 在模板文件的需调用的工作表标签上单击右键，选择“移动或复制工作表”打开对话框； 在对话框中选择要调用模板的工作表，如KS5-1.XLS，选中“建立副本”复选框，单击“确定”。,单变量分析,单变量求解 运用PMT付款函数 运用FV未来值函数,单变量求解,用于公式已知预期的结果，而不知得到此结果的输入值，即可使用此功能。 一公司贷款，要求最高利息不超过某值，计算月利息是多少，方法如下： 建立数据表，如左上图；“利息”右 边的单元格中输入公式=期限*月利率 *10000； 选择“工具”/“单变量求解” 打开对话栻框，如左下图； 在“目标单元格”中输入最终值； 在“可变单元格”中输入可变单元格， 如左下图。,运用PMT付款函数（一）,通过“年利率”的变化，计算“月偿还额”： 打开一个工作簿，如KS5-3，数据如下图所示： 选择E3单元格（月还款额）， 选择“插入”/“函数”，打开对话框； 在对话框中选择“财务”类型中 的PMT函数，打开PMT对话框， 如右图：,运用PMT付款函数（二）,在Rate框中输入“C5/12”；在Nper框中输入C6（贷款期限）；在Pv框中输入C4（贷款额），单击“确定”，求出结果； 选择D3：E8区域，选择“数据”/“模拟运算表”，打开对话框，如图： 在“输入引用列的单元格”框中输入C5（原始年利率），单击确定,运用FV未来值函数（一）,通过“每月存款额”的变化，计算“最终存款额”。 打开工作簿，如KS5-4，数据如下图： 选择E3单元格（最终存款额）， 选择“插入”/“函数”，打开对话框； 在对话框中选择“财务”类型中 的FV函数，打开FV对话框， 如右图：,运用FV未来值函数（二）,在Rate框中输入“C5/12”；在Nper框中输入C6（贷款期限）；在Pmt框中输入C4（月存款额），单击“确定”，求出结果； 选择D3：E7区域，选择“数据”/“模拟运算表”，打开对话框，如图： 在“输入引用列的单元格”框中输入C4（月存款额），单击确定,创建、编辑、总结方案（一）,通过使用方案分析多个变化因素对结果的影响。 选择“工具”/“方案”命令，打开“方案管理”对话框，如下左图；单击“添加”按钮，打开“添加方案”对话框，如下右图。,创建、编辑、总结方案（二）,在“方案名”框中输入方案名称，如KS5-3；在“可变单元格”框中输入“D4：D8”，单击确定，打开“方案变量值”对话框； 在“请输入每个可变单元格的值”下列各文本框中分别输入6%、7%、8%、9%、10%，单击确定，返回方案管理器对话框，单击“摘要”按钮，打开“方案摘要”对话框；在“结果单元格”框中输入E4：E8，单击确定。,双变量分析,运用PMT进行双变量分析 运用FV进行双变量分析,运用PMT进行双变量分析（一）,功能是分析两个变量的变化对结果的影响。如通过“年利率”和“贷款额”的变化分析“每月应付款”的变化结果，操作如下： 打开一个工作簿，如KS5-3，数据如下图所示： 在数据区外选择一个单元格（至少相隔 一行或一列），如B12单元格（月还款额）， 选择“插入”/“函数”，打开对话框； 在对话框中选择“财务”类型中 的PMT函数，打开PMT对话框， 如右图：,运用PMT进行双变量分析（二）,在Rate框中输入“C5/12”；在Nper框中输入C6（贷款期限）；在Pv框中输入C4（贷款额），单击“确定”，求出结果； 选择B13：B17区域依次输入年利率6%、7%、8%、9%、10%，在C12：E12区域依次输入300000、350000、400000，然后选择B12：E17区域，再选择“数据”/“模拟运算表”，打开对话框，如图： 在“输入引用行的单元格”框中输入 $C$4(贷款额)，在“输入引用列的单元格”框 中输入 $C$5(年利率)，单击“确定”。 结果如图所示。,双变量模拟运算的结果,运用FV进行双变量分析（一）,此功能用于通过两个变量的变化分析未来结果的变化。如通过“每月存款额”和 “年利率”的变化分析“最终存款额”。 打开工作簿，如KS5-4，数据如下图： 在数据选择一个单元格（至少相隔 一行或一列），如B12单元格 （月还款额），选择“插入”/“函数”， 打开对话框；在对话框中选择 “财务”类型中 的FV函数， 打开FV对话框，如右图：,运用FV进行双变量分析（二）,在Rate框中输入“C5/12”；在Nper框中输入C6（贷款期限）；在PMT框中输入C4（月存款额），单击“确定”，求出结果； 选择B13：B17区域依次输入年利率5%、6%、7%、8%、9%，在C12：G12区域依次输入1000、1500、2000、2500、3000，然后选择B12：G17区域，再选择“数据”/“模拟运算表”，打开对话框，如图： 在“输入引用行的单元格”框中输入 $C$4(月存款额)，在“输入引用列的单元格”框 中输入$C$5(年利率)，单击“确定”。 结果如图所示。,运用FV进行双变量分析的结果,在图表中添加趋势线、误差线和外部数据,添加趋势线 添加误差线 添加外部数据,添加趋势线,用图形方式显示数据系列中数据变化趋势和走向。 打开一个工作簿,如KS5-7B； 创建一个簇状柱形图； 单击要添加趋势线的数据系列，如“日常生活用品”系列； 选择“图表”/“添加趋势线”命令，如图。在对话框中单击“类型”选项卡，在六种趋势线中选择一种； 单击“选项”选项卡，对趋势线的名称、趋势预测周期和截距等进行设置。其中，R平方值表示采用的公式与数据的配合程度，越接近于1，趋势线越精确。单击确定。 选择趋势线，按Delete，可删除趋势线。,“添加趋势线”对话框,添加误差线（一）,表示数据的不确定性，代表数据系列中每个数据潜在误差的图形线条。 打开或建立一个数据表（KS5-7B），如图： 创建柱形或折线图表，单击要添加误差线的数据系列；,添加误差线（二）,选择“格式”/“数据系列”，打开“数据系列格式”对话框，单击“误差线Y”选项卡，如图： 在显示方式中选择“正负偏差”；在“误差量”中单击“百分比”单选按钮，输入百分比，如10；单击确定退出。,添加外部数据,在数据表中选择要添加到图表中的数据系列，执行复制命令； 选择图表，执行粘贴命令。,数据处理综合应用举例,1.用Excel数据库进行学生成绩处理 2.用EXCEL的关联表格制作歌唱比赛计分表,1.用Excel数据库进行学生成绩处理,数据库工作表框架的建立 数据库表格的格式设置 数据库中的有效性设置 数据库中的数据计算 数据库中的数据统计 数据库中的排序操作 数据库中的筛选操作,数据库工作表框架的建立,Excel中数据库表格的基本结构 数据库工作表遵循的准则 “学生成绩综合评定”数据库结构的建立,Excel中数据库表格的基本结构,数据库是按一定方式组织起来的数据集合，即是装数据的仓库； 关系型数据库是一个二维表格； 表格的列称为字段，表格的行称为记录,数据库工作表遵循的准则,一个数据库最好独占一张工作表； 每一列中的数据必须类型一致； 数据中间不允许有空白行或空白列； 任意两行的内容不能完全相同； 第一行必须是标题行，且为字符串，即字段名（栏上名称）； 数据库的标题与数据区域至少相隔一行。,“学生成绩综合评定”数据库结构的建立,“学生成绩综合评定”数据库见图。建立的具体步骤如下： 启动Excel，双击Sheet1标签，更名为“评定结果”； 在A1单元格输入标题“2001级公关文秘班2003年上半学期成绩综合评定表”； 在A3：P3单元格区域输入各字段标题（共16个）； 保存工作簿为“学生成绩综合评定”。,数据库表格的格式设置,数据库标题格式设置 字段标题格式设置 边框设置 底纹颜色间隔设置 条件格式化设置,数据库标题格式设置,选择标题所在行上与表同宽的单元格区域； 单击格式工具栏上“合并居中”按钮，或选择“格式单元格”命令，打开“单元格格式”对话框，选择对齐选项卡（如右图）； 使用格式工具栏或单元格格式对话框，设置标题字体。,字段标题格式设置,使用格式工具栏，设置字段标题格式，如字体、字号、字型、颜色、下划线、边框五、底纹等； 使用“单元格格式”对话框中的“字体”选项卡设置字体； 使用“单元格格式”对话框中的“对齐”选项卡设置标题对齐方式；,边框设置,单击格式工具栏上的“边框”按钮（如右上图），单击选择一种边框方式； 在“单元格格式”对话框中选择“边框”选项卡,底纹颜色间隔设置,（1）复制格式法 先将头两条记录的底纹设置为不同的颜色； 选择头两条记录，并单击格式刷或执行复制命令； 选择其余的所有记录，执行选择性粘贴命令 （2）条件格式法 选择整个数据区域； 选择“格式条件格式”； 在条件框中选择“公式”，在右侧输入MOD（ROW（），2）0； 单击格式按钮，设置偶数行的底纹。,条件格式化设置,选择要进行条件格式化的数据区域； 选择“格式条件格式”，打开“条件格式”对话框（如下左图）； 选择并输入条件，若需两个条件，单击添加按钮； 单击“格式”按钮，弹出对话框如下右图； 设置条件格式(这里90分以上设置为蓝色,60分以下设置为红色)。,数据库中的有效性设置,(1)所谓有效性，即限定单元格中输入数据具有的有效范围； (2)可设置各门课程字段的有效范围为0100；性别字段设置为序列男、女等，具体操作步骤如下： 选择要设置有效性的单元格区域； 选择“数据有效性”，打开“数据有效性”对话框； 选择设置选项卡，在允许栏内输入范围； 选择“输入信息”选项卡，输入相应的提示信息； 选择“出错警告”选项卡，选中“输入无效数据时显示出错警告”复选框，并在“出错信息”框中输入相应的出错信息文字； 选择“输入法模式”选项卡，确定选择此单元格后的输入法。,有效性设置对话框,数据库中的数据计算,计算总分 排列名次 计算综合平定成绩 排列综合平定名次 确定奖学金等级,计算总分,方法一：将插入点定位在学生总分所在的单元格(E4)；输入公式：=SUM(F4：L4)；确认之后，拖动E4单元格的填充柄到最后一个学生总分所在的单元格； 方法二：选择所有总分单元格；单击工具栏上的自动求和按钮。,排列名次,选择D4单元格，输入公式=RANK(E4，$E$4：$E$57)； 确认后拖动D4单元格填充柄到D57； RANK专用于排列名次的函数，其中$E$4：$E$57指定排名次的范围，必须是绝对引用;若名次范围后加数字0或省略,降序;为其他数字,升序。,计算综合平定成绩,计算方法：四门课平均占60%；三门考查课平均分占20%；再加上操行分； 操作方法：选择N4，输入公式=AVERAGE(F4：I4) *0.6+ AVERAGE(J4：L4) *0.2+M4； 确认后，拖动N4填充柄到N57,排列综合平定名次,选择O4单元格，并输入公式=RANK(N4，$N$4：$N$57)； 确认后拖动O4填充柄到O57，即可得出综合平定名次。,确定奖学金等级,平定方法：前10%获一等奖学金；之后的20%获二等奖学金；再后的30%获三等奖学金；其余的无奖学金。 选择P4单元格，输入公式=IF(O4=ROUND(COUNT($A$4:$A$57)*0.1,0), “一等”， IF(O4=ROUND(COUNT($A$4:$A$57)*0.3，0), “二等”， IF(O4=ROUND(COUNT($A$4:$A$57)*0.6,0), “三等”，“”)； 确认后选择P4，拖动填充柄至P57即可。,数据库中的数据统计,以单科成绩统计分析为例，如图； 先设置B列的公式如下： B3中公式：=COUNT(平定结果!A4：A57) B4中公式：=COUNT(平定结果!F4：F57) B5中公式：=$B$3-B4 B6中公式：=MAX(平定结果!F4：F57) B7中公式：=MIN(平定结果!F4：F57) B8中公式：= COUNTIF(平定结果!F4：F57， “=90”) B9中公式：=B8/B4 B10中公式：= COUNTIF(平定结果!F4：F57， “=80”)-B8 B11中公式：=B10/B4 B12中公式：= COUNTIF(平定结果!F4：F57， “=70”)-B8-B10 B13中公式：=B12/B4 B14中公式：= COUNTIF(平定结果!F4：F57， “=60”)- B8-B10-B12 B15中公式：=B14/B4 B16中公式 = COUNTIF(平定结果!F4：F57， “60”) B17中公式：=B16/B4 通过拖动复制方法得到其他数据。,单科成绩统计分析,数据库中的排序操作,排序的依据和基准 一级字段排序 多级字段排序 其他排序,排序的依据和基准,数值按数值大小排序； 字符按其ASCII码值的大小排序； 逻辑值按其打头字母ASCII码值的大小排序，即F和T的ASCII值； 汉字可按笔画、字典顺序(即拼音字母的ASCII码值的大小)； 空格总是排在最后。,一级字段排序,单击排序列中的任一单元格； 单击工具栏上的“升序”或“降序”按钮,多级字段排序,单击排序列中的任一单元格； 选择“数据|排序”命令，打开“排序”对话框； 依次选择排序的关键字段(最多三个)和“递增”或“递减”； 选择“有标题行”或“无标题行”； 单击“确定”。,其他排序,在“排序”对话框中单击“选项”按钮； 在“排序选项”对话框中分别设置“自定义排序次序”、排序方向及排序方法； 单击“确定”按钮。,数据库中的筛选操作,自动筛选 高级筛选 高级筛选使用注意事项,自动筛选,(1)将鼠标定位到需要筛选的数据库中任一单元格。 (2)选择“数据”“筛选”“自动筛选”，使“自动筛选”项为选中状态（打上对号），这时在每个字段名旁出现筛选器箭头(如图)。 (3)单击“英语”字段名旁的筛选器箭头，从弹出的菜单中选择“自定义”，然后单击鼠标左键。 (4)在“自定义筛选方式”对话框中设置筛选条件； (5)取消筛选：再次选择“数据”“筛选”“自动筛选”。,自动筛选图,高级筛选,（1）新建一个工作表，并命名为“优秀学生筛选”，然后按照如图所示在A2:H4区域内设置高级筛选需要的筛选条件。 （2）单击“优秀学生筛选”工作表中的任一单元格。 （3）选择“数据”“筛选”“高级筛选”命令，出现“高级筛选”对话框(如图)。 （4）在对话框中选择列表区和条件区，单击确定。,优秀生筛选,高级筛选使用注意事项,必须指定条件区，若与数据表在同一工作表中，必须与工作表相隔一行或一列； 条件区域中的字段名必须与数据库中完全一样，最好用复制方法得到； 执行筛选命令前，将光标置于数据库的某一单元格；若条件与数据库不在同一工作表中，则需将光标置于条件区； 当选择“将筛选结果复制到其他位置”时，光标需置于目标位置的最左上角的单元格上； 不同字段名下面的同一行中各条件是“与”的关系；不同行中的各条件是“或”的关系(如图)。,补考生筛选,2. 利用EXCEL的关联表格 制作歌唱比赛计分表,表格关联及实例说明 比赛计分规则 问题提出 问题分析和操作提示 解决思路 主要操作步骤,表格关联及实例说明,所谓关联表格，是指在一个或者多个工作簿中有一定关联关系的工作表的总称。 本节介绍Excel中关联表格的使用方法和注意事项。操作中使用的实例说明如下： 市电视台、市委宣传部和市文化局联合举办了“市青年歌手大奖赛”，经过初赛、复赛，目前已经进入决赛阶段。在决赛阶段，为了体现公平、公正、科学、合理的比赛原则，组委会拟定了如下的比赛计分规则。,每一个参加决赛选手的得分满分为100分，包括以下三大部分： 1、歌唱得分：每一位参赛选手自行选择一首歌曲演唱，满分90分，12个裁判分别打分，总分减去最高分和最低分之后的平均分为该项分数。 2、素质得分：两个题目，每题0.5分，共 1分，各由一个评委评分。 3、声乐得分：选手自己从指定歌曲中选择一首歌曲进行声乐表演，歌曲有不同的难度系数，分A、B、C三个级别，满分9分，12个裁判打分，方法同上，但是在得到的平均分数的基础上再乘上难度系数（A为1，B为0.8，C为0.6），才能得到该项分数。 以上三项分数之和为该选手的总得分，按照该成绩的名次确定最终的获奖等级。,比赛计分规则,问题提出,假设参加决赛的选手共20人，比赛前通过抽签确定出场顺序，比赛结束需要确定综合成绩一等奖1人，二等奖3人，三等奖5人，其余为优秀奖；同时综合成绩最好的3名将推荐到省里参加全省比赛；另外，为了比较单项成绩，还需要将单项成绩的前6名评出。 现在需要制作一套用来计算选手分数的表格，以便在比赛现场能够很快算出分数,问题分析和操作提示,从上面的规则可以看到，需要进行单项成绩的计算和排名，同时需要进行几项成绩之和作为综合分的计算和排名，所以可以考虑利用Excel制作一套计算分数的表格来解决该比赛分数计算问题。制作时需要注意以下几个问题： 1各个表格中要尽量减少现场数据录入和计算的工作量。 2可以在比赛中间随时查看比赛过的选手的各个单项成绩，确定对应的暂时名次。 3单项成绩和综合成绩的计算，需使用公式和函数来实现，并且公式要具有容错性。 4综合分数排名为最后名次，根据该名次用公式确定获奖等级。 5多个工作表之间进行关联操作时，注意数据要能随时变化。 6为了使各个表格之间切换方便，可以制作一个主界面工作表，在上面利用有关图形建立超级链接，以便可以快速切换到对应的工作表。,解决思路,按照以上的分析，本问题的解决可以通过制作8张关联的工作表来实现，分别用来作为“主界面”工作表、 “计分规则”工作表、“选手情况” 工作表、“歌唱得分” 工作表、“素质得分” 工作表、“声乐得分” 工作表、“综合得分” 工作表以及“评奖结果” 工作表。下面就介绍这些工作表的格式制作和公式设计，操作中使用的是Excel 2003。,主要操作步骤,工作表的添加和更名 “主界面”工作表的制作 “计分规则”工作表的制作 “选手情况”工作表的制作 “歌唱得分”工作表的制作 “素质得分”工作表的制作 “声乐得分”工作表的制作 “综合得分”工作表的制作 “评奖结果”工作表的制作,工作表的添加和更名,插入工作表：选择“插入|工作表”，或在工作表标签上右击，在快捷菜单中选择“插入”； 工作表的重命名：双击工作表标签，或右击标签，在快捷菜单中选择“重命名”； 复制/移动工作表：选择“编辑|复制或移动工作表”，或右击工作表标签，在快捷菜单中选择“复制或移动工作表”；或Ctrl+拖动标签，直接拖动标签； 工作表的删除：选择“编辑|删除工作表”，或右击工作表标签，在快捷菜单中选择“删除”。,“主界面”工作表的制作,选择“工具|选项”，取消网格线； 输入标题，并设置字体； 绘制7个椭圆，填充颜色，并右击选择“添加文字”； 进行字体设置，效果如右图所示。,“计分规则”工作表的制作,取消网格线； 按右图输入文字，并设置字体；