Exce2010l提高实验.docx

提高实验一　图表的应用
实验目的：
掌握常用的三种图表——柱表图、饼图、折线图的制作修饰方法。
任务一　创建电子表格
任务说明：在D1单元格中输入表格标题“北疆大学本科毕业生分配表”，在B2:K8区域输入相应表头文字和原始数据。修改工作表单名称为“分配表”，“分配表”显示了某大学10年来本科毕业生分配流向。
步骤1：参照下图创建电子表格，并以文件名“图表.xlsx”保存，保存位置与前面各次实验相同（即仍然保存在以班级和您自己姓名命名的文件夹）。

步骤2：修饰电子表格
参考下图修饰电子表格
（1）工作表标题修饰
①格式：“楷体”、“加粗”、“24像素（磅）”字；
②对齐：在（D1:I1）区域跨列居中。
（2）正文内容修饰
①格式：“宋体”、“12磅”字，行标题（B2:B8）和列标题（C2:K2）设为深蓝色“粗体”；
②对齐方式：行、列标题的对齐方式为水平居中；
③边框和底纹：内容区（B2:K8）添加内外边框和淡紫色的底纹。
修饰后的表格如下图所示。

任务3 重命名工作表
右键单击工作表标签“Sheet1”，快捷菜单中选择重命名，将其命名为“分配表”，然后回车确认。
任务4　应用柱形图
任务说明：单纯通过数字很难形象地看出分配趋势的变化，通过创建“柱形图”图表能够直观地反映毕业分配的趋势。
步骤1：创建柱形图
（1）选择用以绘制“柱形图”的数据：年度、国有企业、外资企业、合资企业、出国留学和国内深造等6项数据（B2:F8及I2:J8）。
生成柱形图表：按下功能键F11，所创建的图表独立出现在一个新的图表工作表（默认工作表名称为chart1）中，如下图所示，同时工具面板中出现了“图表工具”，其下有“设计”、“布局”、“格式”三个选项卡。

l 说明
柱形图用来显示一段时期内数据的变化或者描述各项之间的比较，能有效地显示随时间变化的数量关系。柱形图从左到右的顺序表示时间的变化，柱形的高度表示每个时期内的数值。
（3）调整柱形图表位置
检查工作列列表中保持“Chart1”为选中（编辑）状态
在“图表”工具的“设计”选项卡中，单击 按钮，“标右键单击图表，从弹出的快捷菜单中选择“位置”命令，打开“图表位置”对话框（如右图）。选中单击选按钮“作为其中的对象插入”到“分配表”工作表，单击“确定”按钮，

嵌入的图表四周出现浅灰色边框，将鼠标指针指向图表（浅灰色边框除外），按住鼠标左键拖动，可调整图表整体的位置，将图表放置在（C11:I27）区域位置处，如下图。

步骤2：向图表添加数据系列
l 说明
通过图表的图例信息，可观察到步骤1完成的图表缺少乡镇企业和私人企业两项数据，步骤2将把所缺的数据项添加到图表中。
（1）选取要添加的数据G2:H8（乡镇企业和私人企业两列数据）。
（2）向图表中添加所选数据系列。
①执行“复制”操作，复制所选数据系列。
②选中图表（图表四周浅灰色边框）。
③执行“粘贴”操作，所选数据系列添加以图表中，左、右边框中点处拖动，调整图表的宽度，见下图。

（3）保存文件
步骤3：调整新添加数据的显示位置
l 说明
图表中数据序列的顺序与工作表单中对应数据的顺序相一致看起来会比较方便，但新添加的数据序列一般会放在最后，需要进行位置上的调整。观察步骤2添加新数据后的图表，可以发现新添加的数据系列排列在其他数据项的后面，通过数据序列在图表中显示位置的调整，使它与工作表单的排列顺序一致。
（1）调整“乡镇企业”序列到合适的位置。
①保持图表处于编辑状态，在“图表工具”的“设计”选项卡中单击按钮，打开“选择数据源”对话框。
②在“选择数据源”对话框中，选中“乡镇企业”项，单击“上移”按钮，每单击一次，数据项上移一位，最终使之移到“合资企业”数据项的下方。
（3）参照上述方法，调整“私人企业”数据序列至“乡镇企业”项的后面。
（4）保存文件。

步骤4：改变图例的位置并为图表加上标题
l 说明
图表中所有的元素都可以进行调整（位置的改变、大小的改变），调整图表的元素以达到最佳的视觉效果。
（1）调整图例的显示位置：
保持图表处于编辑状态，单击“图表工具”“布局”选项卡中的按钮，在下拉列表中选择“在底部显示图例”，图例位置改变到图表正下方，如图所示。
说明：右键单击图例，在快捷菜单中选择“设置图例格式”，也可以改变图例位置。

（2）添加图表标题
①保持图表处于编辑状态，单击“图表工具”下“布局”项目中的按钮，下拉列表中选择“图表上方”项，图表上方居中位置出现“图表标题”字样，将其文字修改为“毕业生分配柱形图”。
②选中标题，将工具面板切换到“开始”选项卡，设置图标标题字体为“宋体”，大小“12号”。
（3）保存文件
任务3　应用饼图
任务说明：通过10年中的第一年（1990年）和最后一年（2000年）数据的“饼图”对比可以清楚地了解10年来本科毕业生在分配选择上最大的改变所在。
步骤1：将1990年数据系列制成饼图
（1）选择图表数据源（D2:K3）区域。
（2）单击“插入”项目卡中按钮，列表中选择“三维饼图”中的第一个，｜图表”命令，打开“图表向导”4个对话框中的第1个对话框。选择图表类型为“饼图”，子图表类型选择“三维饼图”，一个新的三维饼图被插入到“分配表”中。
（3）在图表上方居中添加饼图的标题“1990年毕业生分配比例图”。
（4）单击“图表工具”中“布局”选项卡中的 按钮，在下拉列表中选择最下方的“其他数据标签选项”，打开“设置数据标签格式”对话框。
（5）在“设置数据标签格式”对话框中的“标签选项”列表中，取消“值”前的选中状态，选中“百分比”选项，如下图所示，然后单击关闭按钮。

步骤2：编辑和修饰饼图
（1）设置“饼图”标题：“隶书”、“20磅”，并拖动标题到合适的位置。
（2）为图表区加入“水蓝”色底纹
保持饼图图表处于编辑状态，单击“图表工具”中“格式”选项卡中的按钮（图标中颜色可能为其他颜色），在填充颜色列表中选择“水绿色，强调文字颜色5，淡色80%”，设置图表区颜色，效果参见下图。

步骤3：将2000年数据系列制成饼图
选择“分配表”的数据区域（D2:K2）和（D8:K8），制作2000年毕业生分配比例图如下图。标题为“2000年毕业生分配比例图”，标题为“隶书”、“20号”字。为该图表加上“水绿”色底纹，最终效果见下图。

步骤4：将图表移动到“分配表”工作表中合适位置
（1）把 “分配表”工作表“1990年毕业生分配比例图”移动到（B28:F40）区域。
注：移动后应先调整图表的大小，然后再修改标题和图例的字体。
（2）把 “分配表”工作表“2000年毕业生分配比例图”移动到（G28:L40）区域。
注：复制后应先调整图表的大小，然后再修改标题和图例的字体。
步骤5：切割饼图
（1）单击“1990年毕业生分配比例图”饼图，选中整个饼图，再次单击“国有企业”数据标记的扇形图，选中该扇形（四周出现小圆点）。
（2）按住鼠标左键并向外拖动，观察该扇形图从饼图中分离出来，参见下图。

（3）同样方法，将图表“2000年毕业生分配比例图”中代表国有企业毕业生数量的扇形从饼图中分离，如下图所示。

步骤6：添加图形对象
（1）单击“绘图”工具栏的“自选图形”按钮，从中选取“圆角矩形”工具。
（2）将工具面板切换到“插入”选项卡，单击按钮，选择矩形中的“圆角矩形”在区域（F27:G21）绘制圆角矩形，可通过四周句柄调整图形的大小，拖动黄色菱形句柄调整矩形的圆角曲度。
（3）在圆角矩形对象上，单击鼠标右键，选取“添加文字”快捷菜单命令，然后在其中输入文字内容“国有企业分配比例减少46%”，将文字颜色设置为黑色。
（4）使用“开始”选项卡中的 按钮为圆角矩形填充黄色背景，单击“插入”选项卡中 ，选择“箭头”工具绘制箭头，从圆角矩形指向“国有企业”扇形图，双击绘制的箭头，“绘图工具”“格式”选项卡中“形状样式”工具组右下角的箭头，如下图所示。

（5）在打开的“设置”形状样式对话框中，左窗格中选择“线型”，右窗格中将线宽设置为1.5旁，如下图所示。

圆角矩形及简头绘制完成后效果如下图所示。

步骤7：保存文件
l 小结
饼图是将某个数据系列视为一个整体，其中每一项数据标记用扇形图表示该数值占整个系列数值总和的比例，直观地显示出整体与局部的比例关系。它一般只显示一个数据系列，在需要突出某个重要数据项时十分有用。
任务4　应用折线图
任务分析：下图是一个温度控制仪表的检定实验结果，任务4将在此实验数据基础上完成。由于所学专业的不同，读者不必关心如何得到此实验数据，只需使用最后计算得出的不同温度的检定值误差δ，画出折线图。
步骤1：获得折线图的原始数据
（1）打开“Excel”练习文件夹中的“实验数据.xls”文件。
（2）此工作簿只有一个工作表单，选中（A1:L32）区域（包括两个表格，如下图所示），执行“复制”操作。

（3）通过任务切换到另一个编辑的工作薄“图表.xls”，在“图表”工作簿中新建工作表“实验报告”，见下图。

（4）单击工作表“实验报告”中的A1单元格，执行“粘贴”操作，完成原始数据的复制操作。
步骤2：选择绘图所需数据（A5:A16）和（L5:L16）。
步骤3：单击“插入”选项卡中的图标，在拆线图子类型中选择“带数据标志的拆线图”，在当前工作表中插入如下图所示的折线图。

步骤4：完善折线图，进行以下操作，完成折线图的创建（如右图所示）。
① 图表标题为“测定值误差”；
② 删除图表右侧的图例，修改后的拆线图如下图所示。

③ 修饰图表标题：“隶书”、“20磅”。

步骤4：图表的修饰
（1）去掉“数值轴”的小数位数
鼠标右键单击数值轴，选择“坐标轴格式”命令，在“设置坐标轴格式”对话框中“数字”选项卡中的“小数位数”选项修改为“0”，如下图所示。

（2）设置“图表区”的填充效果
选中图表的绘图区，单击“图表工具”“格式”选项卡中的 按钮，下拉列表中选择“渐变”，再在子菜单中选择“其他渐变”，在打开的“设置绘图区格式”对话框中，按下图进行设置。

设置绘图区填充样式后的图表如下图所示。

步骤5：数据系列格式的改变
（1）观察前一图表中，所有数据点被当作一个系列处理，采用一种颜色描述。
（2）选中“数据系列”（单击图表区的拆线），在“图表工具”选项卡中单击按钮，在打开的“设置数据点格式”对话框中，按下图进行设置。

选择“以数据点分色”功能后，单击“关闭”按钮，效果如下所示，即采用不同的颜色代表不同的温度值，以便更清楚地描述不同温度之间的误差。

步骤6：保存文件
l 小结
折线图以等间距显示数据的变化趋势，可用于显示随着时间变化的趋势。
提高实验二　公式与函数
实验目的：
了解Excel利用公式进行更新数据、自动重算的特点，利用Excel自带的函数库，根据实际需要，创建不同用途的公式，打造不同用途的表格。
任务说明：本实验将用到一份“计算机文化基础成绩汇总”表，如图所示，该表格说明了课程的考核要求，并根据各项考核指标，实现每一位同学成绩的自动计算。课程考核指标如下（总分100分）。
（1）三次作业（共计80分）。
①两道必做题
文字处理软件Word（满分25）。
网页制作（满分35）
②一道任选题作业（满分20）。
（2）两次平时测验：凡参加者，每次均记5分。
（3）讨论参与（满分10）：按0、5、10三档记分。
任务分析：
（1）保证参与求和计算的作业成绩符合考核要求，即任选作业“Excel”、“PowerPoint”、“Flash”以及“Photoshop”只有一项记入成绩。
（2）对于提交多个任选作业的同学，首先将找出其中成绩最高的一次作业，反映在“任选作业”一栏中，并参加总成绩计算。
（3）防止“任选作业”一栏的成绩由人工输入。
（4）为醒目标识出某同学未交齐作业，需要将“Word”、“任选作业”以及“网页”三栏的背景色，在没有成绩时，以红色背景显示。
（5）“参与程度”一栏应根据上述评分标准，提供以0、5、10为三档的输入列表，供教师选择输入。
（6）为减少输入错误，为“Word”、“任选作业”以及“网页”的输入设置有效范围，比如，凡在“Word”一栏中，输入大于25或小于0的数值时，均视为无效作业成绩。
（7）最后总评成绩根据分数，自动设置为三档记分：大于等于85分为“优秀”，60～84分为“通过”，60分以下的为“不通过”。
（8）根据“总评”数据，计算该课程的“课程通过率”和“课程优秀率”。
任务1　设置数据的有效范围
步骤1：启动Excel 2010，打开“Table.xls”文件，将其另存为“公式练习.xlsx”，保存位置同前面各实验，打开的工作簿内容如下图。

步骤2：设置有效范围
（1）选取设置对象（C4:C13）区域（“Word”作业栏）。
（2）具体设置见下图（左）。

（3）打开“输入信息”选项卡，参照上图（右）进行填写：在“标题”栏中输入“Word作业成绩”；在“输入信息”框中输入“满分25”。

（4）观察以上操作效果发现：当选择“Word”成绩所在的列相应单元格时（如C4），会出现所输入的提示信息，以引起输入者注意，参见下图。

步骤3：设置输入错误的警告
（1）选取设置对象（C4:C13）区域（“Word”作业栏）。
（2）选择“数据｜有效性”命令，打开“出错警告”选项卡，具体设置见上图右。
①出错提示采用“停止”样式，观察其下方的停止图标。
②“标题”项填写“无效”。
④ 出错信息”项填写“数据错误”。

（3）观察以上操作效果：当在“Word”成绩列相应单元格输入的数据大于25、或小于0、或非数值数据时，将出现所设置的出错警告，见下图。

l 练习
仿照上述操作完成其他区域数据有效范围的设置，其中：
（1）（D4:D13）区域（“任选作业”成绩），有效条件设为“介于0～20”。提示信息为“任选作业成绩满分20”，出错提示“数据错误”。
（2）（I4:I13）区域（“网页”作业成绩），有效条件设为“介于0～35”。提示信息为“网页作业成绩满分35”，出错提示“数据错误”。
（3）（J4:J13）区域（“平时讨论参与程序”成绩），有效条件设为“介于0～10”。提示信息为“平时讨论参与程度成绩满分10”，出错提示“数据错误”。
步骤4：在“平时测试”区域设置“数据输入帮手”
（1）首先在F15、G15、H15三个单元格中分别输入0、5、10，这三个数据是“平时测试”成绩来源所在，也可以把它放在工作表的任何单元格中。
（2）选取“平时测试”区域（K4:K13）。
（3）选择“数据｜有效性”命令，打开“设置”选项卡。
①将工具面板切换到“数据”选项卡，在“数据”选项卡中单击按钮，选择“数据有效性…”命令，打开“数据有效性”对话框的“设置”选项卡，单击“允许”项右边的下拉箭头按钮，在打开的列表中选择“序列”。

②通过“来源”编辑框右侧选择按钮，选择“序列”来源（F15:H15）区域，见下图。

③打开“输入信息”选项卡，参见下图填写提示信息。

（4）观察以上操作效果：当选择平时测试区域（K4:K13）的单元格时，系统将出现所设置的提示信息，见下图（左），同时在单元格的右下角出现下拉按钮，通过下拉按钮可以在打开的序列中选择0，5,10，见下图（右）。

l 小结
利用数据输入帮手，有利于减少人工输入错误。但如果数据不固定通常不能使用。
任务2　使用函数创建公式
步骤1：创建公式
（1）选中单元格D4，单击“编辑栏”中的“编辑公式”按钮，打开“插入函数”对话框，见下图。

（2）在“选择函数”列表框中，选取具体函数——求最大值函数“MAX”，选定具体函数后，屏幕上弹出“函数参数”对话框，见下图。

（3）在“Number1”编辑框中选定参加计算的一组数据（E4:H4）（“任选作业”的成绩）。

（4）单击“确定”按钮，关闭“函数参数”对话框，按下Enter键完成公式输入，观察上述操作的结果，参见下图。单元格D4中显示公式的运算结果，编辑栏中显示具体公式。

步骤2：填充复制公式，用填充功能，将公式从D4单元格复制填充至D13单元格。选择被复制了公式的单元格，观察编辑栏的变化。
l 注意
在输入公式以及函数参数时，一定要注意输入半角的符号，如“()”=+-”等均应为半角符号（可关闭中文输入法，以确保输入的函数为半角符号）。
l 思考
为什么（D4:D13）区域中每个单元格中均出现数据0？
l 练习
参照以上步骤，在“分数”列（L4:L13区域）中创建求和公式，计算计算机文化基础课的几部分分数求和。
任务3　创建嵌套公式
任务说明：总评成绩是根据每一位同学的成绩决定的，所以计算公式中需要利用逻辑函数“IF”进行条件的判断。
分数在60分以下的为“不及格”；
分数在60～80分之间的为“及格”；
分数大于等于85分为“优秀”。
步骤1：创建逻辑判断公式
（1）选择单元格M4，单击“编辑公式”按钮，创建同学“甲”总成绩的计算公式。
（2）在“插入函数”对话框的函数列表中选取具体函数“IF”，打开“函数参数对话框。
（3）在“Logical_test”编辑框中输入判断条件：“L4>=85”（L4单元格存放学生“甲”的分数）。
（4）在“Value_if_true”编辑框中输入“优秀”，注意编辑栏的变化，见下图。

（5）至于在“Value_if_false”编辑框中的结果值是“及格”或“不及格”还需要进一步的判断，具体实现，请继续步骤2的操作。
步骤2：函数嵌套，实现多重判断
（1）单击“Value_if_false”编辑框（不进行任何文字输入），鼠标单击“编辑栏”中的“IF”函数按钮，打开一个空白“函数参数”对话框，注意观察“公式编辑栏”中内容。
（2）在“Logical_test”编辑框中输入判断条件：“L4>==60”。
（3）在“Value_if_true”编辑框中输入“及格”，注意编辑栏的变化。

（4）单击“Value_if_false编辑框（不进行任何文字输入），用鼠标单击函数“IF”按钮，再次打开一个空白“函数参数”对话框，继续填入分数少于60分情况的判断。
（5）“Logical_test”编辑框中输入判断条件：“L4>0=”。
（6）在“Value_if_true”编辑框中输入“不及格”，在“Value_if_false”编辑框中输入“没有成绩”，单击确定按钮。

（7）回到开始的“函数参数”对话框，注意编辑栏内容的变化，见下图，在M4单元格中出现“不及格”字样。

（7）在（M5:M13）区域进行公式复制完成“总评”成绩公式的设置。
l 说明
也可以通过键盘输入，直接在“Value_if_false”编辑框中创建函数嵌套公式：
=IF(L4>=60,”及格”,IF(L4>=0,”不及格”,”没有成绩”))
单击“确认”按钮，即在M4单元格中出现“不及格”字样。
l 练习
请读者完成及格率（F17）和优秀率（F18）的计算。可以采用直接在“公式编辑栏”中输入的方式，也可以通过“公式选项板”创建公式。
及格率公式“=COUNTIF($L$4:$L$13,”>=60”)/COUNT($L$4:$L$13)”。
优秀率公式“=COUNTIF($L$4:$L$13,”>=85”)/COUNT($L$4:$L$13)”。
公式设置完毕后，将F17和F18单元格设置为百分比样式。
任务4　使用条件格式，醒目显示重要数据
任务说明：醒目标识未交齐作业者。将“Word”、“任选作业”以及“网页”3栏的背景色，在没有成绩时，以红色显示。
（1）选择区域(C4:C13、D4:D13、I3:I13)。
（2）在工具面板的“开始”选项卡中，单击按钮，选择“突出显示单元格规则”下面的“等于…”命令，在打开的“等于”对话框，按下图进行设置，然后单击“确定”按钮。

（3）设置完成后，工作表的“Word”、“任选作业”、“网页”3列为浅红色背景显示，如下图所示。

任务5　使用数据保护功能，防止人为输入
步骤1：取消工作表的数据保护
（1）通过“全选”按钮或菜单命令，选择整个工作表。
（2）选择“格式｜单元格”选项，打开“单元格格式”对话框。选择“保护”选项卡，取消“锁定”复选框的选中状态，如下图。

步骤2：防止“任选作业”一栏的成绩由人工输入
（1）选择区域（D4:D13）(“任选作业”栏)。
（2）打开“单元格格式”对话框。选中“保护”选项卡上的“锁定”复选框。
（3）执行“工具｜保护｜保护工作表”命令，打开“保护工作表”对话框上图（右）。在“密码”编辑栏中输入密码（如000）。注意：重新输入密码时一定要与第一次输入的相同。

（4）观察以上操作效果：比如，选择D4单元格，尝试输入时，系统会弹出消息框，提示此单元格被保护，见下图。

l 注意
数据保护一般需要在最后进行，否则，会影响其他一些编辑操作。
l 思考
如果需要修改被保护的单元格的内容，如何撤消相应保护？
l 练习
参照下图，在表格中添入数据测试功能。
提高实验三　数据管理
实验目的：
了解Excel提供的数据管理功能；
掌握创建数据清单的方法；
掌握数据筛选的方法；
掌握数据排序的方法；
掌握分类汇总的方法。
任务说明：
本实验将用到一份4人结伴出游的流水账单，出发前每人预交了150元。旅途中的主要开销由“出纳员”李明负责支付，凡其他成员支付的公共费用均需向李明申明并备案。出游归来，李明需要使用Excel的相关功能计算出此次旅游的总开销、每个成员的实际支出，并给出成员之间如何转账的建议，同时，该账单数据的排列要便于每个成员查询自己的支出，以及核对每一笔开销。
任务分析
（1）首先根据费用支出的时间顺序制作一份“旅游流水账单”，工作表单命名为“日期”，该表格的创建要符合数据清单的制作准则，以便使用Excel提供的数据管理功能。
（2）编辑修饰“日期”工作表单以便于浏览。
（3）将所以“现金”方式支付，且“没有收据”的消费项目筛选出来，方便核对。
（4）对“日期”工作表单中的数据按日期进行费用汇总，并计算出旅行总支出。
（5）将“日期”工作表单中的原始数据复制到同一工作簿的新工作表“个人支出”中。
（6）对“个人支出”工作表单按个人信息进行费用汇总，计算出每个人实际支出的金额。
（7）在“个人支出”汇总表下方中制作统计信息表，包括姓名、预付款、追加款、收款总额、平均支出、增补费用6项，填入或计算相关数据。
任务1　创建“日期”工作表单
步骤1：启动Excel 2010，程序自动创建“工作簿1.xls”工作簿文件。
步骤2：保存文件
将工作簿1.xlsx保存成“自助旅游费用清单.xlsx”，保存位置与前面各实验相同。
步骤2：复制原始数据
（1）在A1单元格，输入表格标题“Wales旅游流水账单”。
（2）打开“原始数据.xls”文件，选中（A5:I47），执行“复制”操作，切换到“自助旅游费用清单.xlsx”的Sheet1工作表中，单击A5单元格，执行“粘贴”操作，完成原始数据的复制操作。
l 注意
观察该表格数据是否符合数据清单的创建规则。该数据清单由9个字段组成，共有41条记录。
（3）在G3单元格位置处输入文本“费用总计：”。
（4）双击“Sheet1”标签名，把工作表单名称改为“日期”。
l 小结
在Excle中，数据清单是包含相似数据组的带标题的一组工作表数据行，可以将“数据清单”看作“数据库”，其中行作为数据库的记录，列对应数据库中的字段，列标题作为数据库中的字段名称。
任务2　编辑修饰数据清单
步骤1：修饰表格框架
（1）表格标题在（A1:I1）区域跨列居中。
（2）设置表格字段对齐方式。
①将工作表标题设置为“宋体”、“18磅”粗体字，并加上淡灰色底纹；
②将“费用总计：”和列标题设为“宋体”、“12磅”粗体字，并为列标题填加淡灰色底纹，效果见下图。

步骤2：修饰表格内容
（1）对工作表单中每一列内容，使用常规的对齐方式对齐。
①“日期”、“姓名”、“付账方式”、“用途”、“地点”“注释”列内容左对齐。
②“费用”列内容右对齐。
③逻辑值采用中央对齐方式。
④“时间”和“有收据否”两列内容设为居中对齐。
（2）设置数据的显示格式：“费用”列（C6:C47）区域，将“小数位数”设为2，即保留两位有效数据的格式。
（3）此次旅游历时7天，为了清楚显示每一天的消费项目，可使用不同的背景色修饰日期和费用两列（如：黄、红、绿、紫、淡蓝、橘黄、深蓝），参见下图。

l 小结
对于数据清单的创建和修饰，它不像一般的工作表那样随意，有许多需要注意的问题，比如：在同一个数据清单中列标题内容必须惟一；列标题与纯数据之间不能用虚线或空行隔开，同一列数据的数据类型、格式等必须相同；纯数据区域中不允许出现非法数据，如空记录等。在建立数据清单时要予以注意。
任务3　筛选所有以“现金”方式支付且“没有收据”的消费项目。
步骤1：筛选以“现金”支付的项目
（1）用鼠标单击任意列标题（如：姓名），然后在“数据”选项卡中单击按钮，此时，“自动筛选”箭头会出现在数据清单中列标题的右侧，即每个列标题都变成了下拉式列表，参见下图。

（2）单击“付款方式”列标题旁的“自动筛选”箭头按钮，打开对应的下拉列表，从中选择“现金”选项（取消其它选项的选中状态），参见下图（1），然后单击“确定”按钮，即可将所有用“现金”付账的项目筛选出来，见下图（2）。

（1）

（2）
（3）注意观察：数据清单的行号为7、10、11、13、14……，表明筛选结果只显示满足筛选条件的数据，不满足条件的数据暂时被隐藏起来了。
步骤3：筛选“没有收据”的消费项目
单击“有收据否”列标题的“自动筛选”箭头按钮，打开对应的下拉列表，从中选择“N”选项，将所“没有收据”的项目筛选出来，见下图。

l 说明
步骤3是在步骤2的筛选结果的基础上再进行筛选，因此是“逻辑与”的关系，所以步骤2与步骤3综合筛选的数据是“没有收据”并且以“现金”支付的项目。
步骤4：将筛选结果复制到数据清单下方
（1）为了便于核对，把筛选出来的结果复制到数据清单的下方。在A50单元格中输入“请仔细核对以下项目”，并使其在（A50:I50）区域合并居中。
（2）修饰字体为“16磅”字、“字体”，并加上浅灰色底纹。
（3）全部选中步骤4的筛选结果，单击“复制”按钮，选中A51单元格，单击“粘贴”按钮，使没有收据并以现金付账的数据在原始数据清单的下方建立一个新的数据清单，参见下图。

l 思考
这样的复制操作是否破坏了原来的数据清单结构？
回答：一个工作表上可允许多个数据清单，但位置上有要求，在工作表的数据清单与其他数据之间至少留出1空白行或1空白列进行区分，以便在执行数据管理操作时，Excle正确选定数据清单。
步骤5：取消数据清单的筛选状态
在“数据”选项卡中，单击，取消对数据的筛选功能，数据清单将重新释放全部记录，同时，列标题右侧的下拉箭头按钮消失。
也可以在“开始”选项卡中，单击按钮，在下拉列表中取消“筛选”前的选中状态，显示所有数据。
步骤6：保存文件
l 问题
如何筛选出每笔超过50镑并有收据的消费项目？
方法：单击“费用”列标题的“自动筛选”箭头按钮，选择“自定义”选项……。
l 小结
这一部分我们练习了“筛选”操作，在数据清单中进行筛选操作，是要从中提炼出满足筛选条件的数据，不满足条件的数据只是暂时被隐藏起来（并未被真正删除），一旦筛选条件被撤走，这些数据又重新出现。
任务4：按日期顺序汇总每一天的支出
步骤1：使用“排序”功能将数据分类
要计算每一天的花费总额，需依据“日期”列对数据清单进行排序，这里流水账是按照消费的顺序记载的，本身已经是按日期排序，所以该步骤的操作结果没有改变数据。（这是教材中原话，实际上重新升序排序后，1日的数据会列在数据清单中的前几行）
l 技巧
通常在对某一列进行排序，也就是排序的约束条件只有一个时，最常用的方法是选择排序的列的任意单元格，单击“常用”工具栏上的“升序”按钮或“降序”按钮，完成选择列的顺序排列。
步骤2：按“日期”汇总“费用”列数据
（1）在“数据”选项卡中单击按钮，打开“分类汇总”对话框。
（2）在“分类字段”下拉式列表中，选定“日期”。该下拉列表用以设定数据是按哪一列标题进行排序分类的。
（3）在“汇总方式”下拉式列表框中，选定“求和”方式，计算每一天的支出总和。
（4）在“选定汇总项”列表框中选择需要汇总的字段“费用”，该选项可以设置多个（如下图）。

步骤4：观察分类汇总结果
分类汇总操作将为每个分类“日期”插入汇总行（比如第14行等），并在选定列“费用”上执行设定的“求和”计算，同时，还在该数据清单尾部加入总计求和值，见下图。

查看不同层次的汇总数据
（1）观察分类汇总清单，在其最左边出现了一些控制分级显示数据清单的符号，单击第一级符号，仅可查看汇总总和与列标题，参见下图（左）。

（2）单击第二级符号，查看该项分类汇总与汇总总和，见上图（右）。

（3）单击“30日汇总”对应的“显示明细数据”符号，查看30日的具体支出项目，参见下图，此时符号按钮变为“隐藏明细数据”符号，单击此符号，即可隐藏30日的具体支出信息。

步骤6：保存文件
l 小结
这一部分练习了数据汇总的操作方法。利用此功能，我们对可以统计旅游时每一天的消费总额。
任务5　计算每个人实际支出的金额
步骤1：复制“日期”工作表单中的原始数据
（1）取消分类汇总：选定数据清单中任意单元格，“数据”选项卡中单击，在打开的 “分类汇总”对话框中，单击下部的“全部删除”按钮，即可删除全部汇总项，恢复原始数据清单的形式。
（2）选择“日期”工作表中的（A1:I47）区域，选择“复制”操作。
（3）选择“自助旅游费用清单.xlsx”的“Sheet2”，在打开的新的空白工作表中选择A1单元格，进行“粘贴”完成工作表的复制，为新的工作表重命名“个人支出”，见下图。

步骤2：单击数据清单中任意一个单元格。
步骤3：自定义排序顺序，实现以振国、金奇、黎民、李明这样的名字排序（不符合通常的排序顺序）。
打开“文件”菜单，单击“选项”命令，在“Excel选项”对话框中左侧窗中找到“高级”，右侧窗格找到“Web选项…”列表，单击其中的按钮，如下图所示。

按自定义的排序顺序键入列表的项目，每个项目用Enter键分隔，见下图，然后单击确定按钮。

步骤4：按自定义的序列进行排序
（1）单击数据清单中任意一个单元格。
（2）在“数据”选项卡中，单击按钮，在打开的“排序”对话框的“主要关键字”的下拉列表中选择分类项“姓名”，“次序”下拉列表中选择“自定义序列”，见下图。

（3）在打开的“自定义序列”对话框中，选中前一步骤新建的姓名序列，见下图，然后单击确定按钮。

（4）返回“排序”对话框，单击确定按钮，排序结果见下图。

步骤5：计算出每个人实际支出的金额
（1）选择数据清单中任意单元格。
（2）在“数据”选项卡中单击按钮，按字段“姓名”进行分类，汇总方式为“求和”，汇总项为“费用”。

（3）完成汇总操作，单击第二级符号，汇总结果见下图。

l 小结
计算每个人所要担负的费用是制作这个Excel工作表单的初衷，通过这一步已经计算出每个人在旅游途中的总支出。
任务6　制作统计信息表
步骤1：填写原始数据
（1）在工作表清单下方的（B57:G61）区域内，按照下图所示，输入原始数据。

（2）设定“预付款”项保留0位小数，其他列保留2位小数。
（3）在“增补费用”项中有可能出现负数，设置该列的数据格式为下图（左）所示。

步骤2：填写“追加款”
在“追加款”项（D58:D61）中分别填入数据清单中所求出的每个人的汇总钱数（来自C13、C18、C23、C51）。
设置方法是：单击D58单元格，然后在“编辑栏”中输入“=C13”即可（也可以输入等号后单击C13单元格）。D59～D61单元格中数据设置方法相同，设置后结果见上图（右）。

步骤3：计算“收款总额”
在“收款总额”列（E58:E61）中，将相应项的“预付款”和“追加款”的总和放入其中，如“E58=SUM(C58:D58)”，采用复制填充方式，计算出所有人的收款总额，见下图。

l 注意
李明处已有4人的预付款总额600元，在计算他的收款总额时要使用“预付款 + 追加款 -600”。
步骤4：计算平均支出
在“平均支出”列（F58:F61），将计算收款总额并除以4，即公式为“=SUM(E$58:E$61)/4”，计算平均支出，采用复制填充的方法，应用到F59到F61单元格，结果见下图。

l 思考
在此计算平均支出时使用的是混合地址，如果使用相对地址会出现什么情况？
步骤5：计算增补费用
在“增补费用”列中（F58:F61）区域用“收款总额 – 平均支出”计算出“增补费用”，结果见上图。“增补费用”为负数的需要补交费用给“增补费用”为正数的人。
步骤6：保存文件
l 小结
在计算出需要的数据后，在工作表单的下方制作统计信息表，可以使每个人对相应的账目非常清楚，一目了然。
38 / 38