项目4 电子表格制作与数据处理 教学课件 - 中职《信息技术基础与应用（Windows 10+ Office 2016）》（北京理工大学出版社）

任务4.1 制作学生党员志愿者情况登记表 1 1 目录 2 知识指导 3 4 任务实施 知识扩展 任务扩展 2 1 知识指导 3 知识指导 1.1.1 新建工作簿 新建Excel工作簿常用以下方法。 （1）启动Excel 2016，在打开的界面中选择“空白工作簿”选项，系统自动新建名为“工作簿1”的工作簿。 （2）在桌面空白处单击鼠标右键，在弹出菜单中选择“Microsoft Excel 工作表”命令，即可新建一个工作簿文件。 （3）Excel 2016已经启动，单击“文件”→“新建”选项，在“新建”窗口中，用户可以选择不同的工作簿模板，也可以选择“空白工作簿”，来新建一个空白的工作簿。 1.1.2 Excel 2016的工作界面 启动Excel 2016，进入Excel 2016的工作环境。Excel 2016窗口中的快速访问工具栏、文件选项、功能区等类似于Word 2016，这里不再赘述。下面主要介绍Excel特有的基本概念及主要窗口组成。 4 知识指导 工作簿：是指Excel 2016中用来存储和处理数据的文件，其扩展名为.xlsx。Excel为新建的工作簿自动命名“工作簿1”、“工作簿2”、……，存盘时用户可重新赋予工作簿有意义的名字。 工作表：是用于输入数据、编辑、显示和分析数据的表格，由行和列构成。窗口中间最大的区域就是工作表编辑区，是放置表格内容的地方。一个工作簿文件最多可以含有255张工作表，而每个工作表有1048576行和16384列。一个新建的工作簿默认有1个工作表，即Sheet1。 列号：以字母或字母组合命名，如A、B、C…、AA、AB、AC…XFD。 行号：以数字1、2、3…命名。 单元格：用于输入数据的区域，每个单元格用列号和行号来表示，如A1单元格。带有黑色粗框的单元格，是当前活动单元格，可对该单元格进行编辑操作。 名称框：显示当前单元格的名称。 编辑栏：用于显示和编辑活动单元格中的数据公式和函数的区域。 工作表标签：Sheet1是默认的工作表标签。每个工作表具有一个标签，工作表标签是工作表的名称，单击工作表标签，该工作表即为当前工作表 5 知识指导 1.2.1 文本型数据的输入 文本型数据包括汉字、英文字符、数字以及其它可显示的符号，文本通常不参与计算。输入时，先选中活动单元格，然后输入即可，默认情况下，文本在单元格是左对齐的。 操作技巧 对于全部由数字组成的字符串，如身份证号、邮政编码、电话号码等的输入，为了避免Excel将其认为是数值型数据，可采用两种方法，一是在数字串前加单引号“’”（英文状态下的单引号）；二是先将单元格的数字格式设置为“文本”，然后再输入。 例如：输入身份证号610125199601051234时，输入形式为：’6101251199601051234 输入编号001时，输入形式为：’001 6 知识指导 1.2.2 数值型数据的输入 单元格能接受的数值有整数、小数、分数和科学计数数值。默认情况下，数值在单元格中右对齐。 操作技巧 输入分数时，如1/8，应先输入0和一个空格，再输入分数1/8，若直接在单元格中输入1/8，Excel会将其当作日期处理，显示为1月8日。 1.2.3 日期和时间的输入 Excel工作表中日期和时间均按数值处理。日期输入时中间可用“-”或“/”分隔，如2020/5/1或2020-5-1。 输入时间时，小时和分秒间用冒号分隔，如9:30。 操作技巧 输入数据时，按Ctrl+；（分号）键，可快速获取当前日期；按Ctrl+：（冒号）键，可快速获取当前时间。 7 知识指导 1.2.4 自动填充数据 当输入的数据序列具有一定规律时，比如相同或等差序列，可以使用Excel的自动填充功能快速完成输入。 （1）填充相同数据 ①在第一个单元格中输入数据。如“1”。 ②将鼠标指向单元格右下角的填充柄上（黑色小方块），此时鼠标变成黑色十字形，按住鼠标左键向下拖动，到需要填充数据的最后单元格时释放鼠标左键。 （2）填充等差序列数值 如果要在单元格输入类似“1、2、3、……”的数列，操作方法为： ①在单元格输入第一个数据“1”。 ②将鼠标指到填充柄上，按住Ctrl键不放，同时拖动鼠标左键，这时鼠标右上方会出现一个小加号，到需要填充数据的最后单元格时释放鼠标左键。 数据输入的效果。 8 知识指导 （3）使用“填充”按钮 工作表中有规律数据的输入还可以使用“填充”按钮完成。 ①填充相同数据： 在第一个单元格D1中输入数据，如“党员”。 选中要填充的单元格区域（如D1：D7），单击“开始”→“编辑”组中的“填充”按钮，在拉下列表中选择“向下”命令，就会在选中的单元格中均填入“党员”。 ②也可以输入等比序列 选中填充的区域，在“填充”下拉列表中选择“序列”命令，打开“序列”对话框，在其中可以设置序列产生的方向、选择填充类型以及步长值和终止值。 9 知识指导 1.3.1 选定单元格及区域 编辑单元格数据时，首先要选定操作的对象。 选定单个单元格：单击要选中的单元格，被选中的单元格四周出现黑框，并且单元格的地址出现在名称框中，内容则显示在编辑栏中。 选定连续单元格区域:点击第一个单元格，然后按住鼠标左键并拖动到单元格区域的最后一个单元格后释放鼠标左键即可。 选定不连续单元格区域:先选中一个单元格区域，然后按住Ctrl键不放，再选择其它单元格区域。 1.3.2 编辑单元格内容 （1）修改单元格内容 修改单元格内容时，双击单元格，将光标定位在单元格中，然后移动光标到所需修改的位置。 10 知识指导 （2）删除单元格内容 方法一：选定要清除的单元格或单元格区域，按Delete键，或者在右键菜单中选择“清除内容”命令。此方法只能清除所选区域内的数据，而不能清除格式。 方法二：选定要清除的单元格或单元格区域，在“开始”→“编辑”组中单击“清除”按钮，在弹出菜单中选择“全部清除”命令，则会把选定区域中的格式、内容、批注及超链接全部清除。当然，如选择“全部清除”以外的命令，则只是删除选定的那项内容。 （3）复制或移动单元格内容 可以通过剪贴板或直接拖动方式，完成对选定内容的复制或移动操作。 复制：选中要复制内容的单元格区域，在右键菜单中选择“复制”命令，然后选中目标单元格，在右键菜单中选择“粘贴”命令。 移动：选中要移动内容的区域，将鼠标移到选区边上，指针会变成四角十字形状，按住左键拖动，这时会看到一个虚框，到目标位置后释放鼠标即可。当然也可通过“剪贴”和“粘贴”命令完成。 11 知识指导 1.3.3 单元格合并后居中 将相邻的多个单元格可以合并成一个较大的单元格，方法是，选择需要合并的单元格，然后在“开始”→“对齐方式”组中单击“合并后居中”按钮 ，将使所选区域合并成一个单元格，在新单元格中数据居中对齐。单击“合并后居中”右侧的下拉按钮，在这里可以设置多种单元格合并方式。 1.4.1 选定整行或整列 将鼠标指针移到要选行的行号处，待鼠标指针变为形状时，单击鼠标左键即可选定整行，此时按住左键向上或者向下拖动，可连续选定多行。 将鼠标指针移到要选列的列号处，待鼠标指针变为形状时，单击鼠标左键即可选定整列，此时按住左键向左或者向右拖动，可连续选定多列。 12 知识指导 1.4.2 插入行或列 选定一行，在“开始”→“单元格”组中选择“插入工作表行”，即可在选定行前插入一行，或者右击行号，在快捷菜单中选择“插入”命令，也可以插入一行。如果要插入多行，只要在选中时多选几行就可以，所选的行数与要插入的行数相同。 插入列的操作与插入行类似。 1.4.3 删除行或列 选中要删除的行或者列，在行号或列标上单击鼠标右键，在弹出的快捷菜单中选择“删除”即可。 1.4.4 隐藏列或行 当工作表行列数据太多时，可以把暂时不用的行或列隐藏起来，以方便查看。隐藏行列的操作方法是，选定要隐藏的列或者行，在右键菜单中执行“隐藏”命令即可。 13 知识指导 1.5.1 工作表标签操作 一个新建的工作簿默认有1个工作表，即Sheet1，用户可根据需要增加或删除工作表，也可对现有工作表进行重命名、复制、移动等操作。 选择单个工作表：直接单击工作表标签可选择单个工作表。 插入工作表：单击工作表标签右侧的“新工作表”按钮 ，即可插入一张新工作表。 删除工作表：右键单击要删除的工作表标签，弹出快捷菜单，执行“删除”命令即可。 复制工作表：单击要复制的工作表标签，按住Ctrl键，同时拖动工作表标签到目标位置，即可复制工作表。 移动工作表：单击要移动的工作表标签，然后按下鼠标左键，将其拖动到目标位置后释放。 工作表重命名：双击工作表的标签栏，然后输入工作表的名称，或者右键单击要更改名称的工作表，在弹出的菜单中选择“重命名”后即可输入新的工作表名称。 工作表标签颜色：右键单击工作表标签，在弹出菜单中单击“工作表标签颜色”选项，然后颜色框中选择所需颜色。 14 知识指导 任务实施 创建学生党员志愿者情况登记表时，可按以下操作步骤完成。 步骤1：新建Excel工作簿。 在桌面空白处单击鼠标右键，在弹出的快捷菜单中选择“新建”→“ Microsoft Excel工作表”命令，即可新建一个工作簿文件，把默认的文件名更名为“学生党员志愿者情况登记表.xlsx。 步骤2：输入工作表数据。 双击打开“学生党员志愿者情况登记表.xlsx”,在Sheet1工作表中录入学生的各项数据。“序号”采用自动填充录入，“学号”和“身份证号”及“电话号码”以文本型数据录入，“政治面貌”和“籍贯”中相同数据也采用自动填充录入。结果如。 步骤3：编辑单元格内容。 在表格第2行上插入一个空行，合并A2：B2单元格并录入“填表日期：”，在C2单元格按Ctrl+；键获取当前日期。选择A1：L1单元格区域，单击“合并后居中”按钮。 15 知识指导 步骤4：备份工作表。 单击Sheet1的全选按钮或按Ctrl+A选中工作表，右键复制工作表数据，再插入Sheet2工作表，Sheet3工作表。在Sheet2工作表选中A1单元格，右键粘贴工作表数据完成备份操作。 步骤5：工作表更名。 右键单击Sheet1工作表标签，将其重命名为“登记表”，设置标签颜色为红色。右键单击Sheet2工作表标签，将其重命名为“登记表备份”，设置标签颜色为蓝色。 步骤6：删除工作表。 右键单击Sheet3工作表标签，执行“删除”命令，删除Sheet3工作表。 步骤7：保存和关闭文档。 至此，学生党员志愿者情况登记表完成，单击“文件”→“保存”保存工作表数据，然后单击窗口右上角的“关闭”按钮，退出Excel 2016。 16 知识指导 知识拓展 活动1 设置数据验证 数据验证用于定义在单元格中输入或应该在单元格中输入的数据，从而能够有效防止无效数据的输入。 1. 创建下拉列表 利用数据验证可以创建下拉列表，在Excel 单元格中可直接在下拉列表中选择数据来填充单元格，比如[样张]中“性别”一列。 创建下拉列表的具体操作步骤如下。 （1）选择需要输入数据的单元格区域，如选择B2:B11单元格区域。 （2）切换至“数据”选项卡，在“数据工具”组中单击“数据验证”按钮，打开“数据验证”对话框，单击“允许”右侧的下拉按钮，在弹出的下拉列表中选择“序列”选项，在“来源”文本框中输入要在数据验证列表中显示的数据，如输入“女,男” 单击“确定”按钮完成下拉列表的创建。 17 知识指导 （3）选定B2单元格，可看到B2单元格右侧有一个下拉按钮，单击该下拉按钮，在弹出的下拉列表中选择“男”，完成B2单元格的性别输入。此时，如果输入“男”、“女”以外的其它内容时，则会弹出警告提示对话框。 2. 设置数据验证检查 利用数据验证可以检查输入数据是否正确，如果输入非法数据，则显示警告信息，以帮助用户及时修改错误。比如设置[样张]中“工龄”一列的有效性检查，输入值在1~42之间为合法，否则为非法，并给出警告提示信息。具体操作步骤如下。 （1）选择需要做有效性检查的单元格区域C2:C11。 （2）切换至“数据”选项卡，在“数据工具”组中单击“数据验证”按钮，打开“数据验证”对话框，在“设置”选项卡中进行设置，然后在“出错警告”选项卡中进行设置，确定后完成数据验证检查设置。 （3）如果在C2单元格输入45，根据设定的有效性检查条件，输入的数据已经超出范围，系统自动弹出对话框提示输入的数值非法。 18 知识指导 活动2 插入批注 1. 添加批注 （1）选定要加批注的单元格。 （2）在“审阅”→“批注”组中单击“新建批注”按钮 ，在弹出的批注框中输入批注文字。 （3）单击批注框外部的工作区域即可退出。 2. 编辑或删除批注 （1）选定有批注的单元格。 （2）在“审阅”→“批注”组中单击“编辑批注”按钮或者“删除”按钮，即可对批注信息进行编辑或删除 19 知识指导 任务拓展 实训任务1 制作物资捐赠统计表 任务描述：制作物资捐赠统计表，利用数据验证功能为“省（市）”一列创建下拉列表。 任务要求： 1.新建Excel工作簿，命名为“物资捐赠统计表.xlsx”。 2.参照样张，输入工作表数据，标题文字“物资捐赠统计表”在A1：F1合并后居中。 3.为“省（市）”一列创建下拉列表，并完成该列数据的输入。 4.保存工作簿文件。 20 知识指导 实训任务2扶贫数据统计表 任务描述：扶贫数据统计表 任务要求： 1.新建Excel工作簿，命名为“扶贫数据统计表.xlsx”。 2.参照样张，输入工作表数据，标题文字“扶贫数据统计”在A1：E1合并后居中,注意工作表中的空行 3.为标题“扶贫数据”设置批注，批注为“资料来源：《四川省扶贫建档立卡数据汇总资料2013年度》”。 4.保存工作簿文件。 21 任务4.2 修饰学生党员志愿者情况登记表 22 1 目录 2 知识指导 3 4 任务实施 知识扩展 任务扩展 23 1 知识指导 24 知识指导 1.1.1 设置单元格内数据格式 单元格内的数据可以根据需要设置成不同的数据格式，其中包括日期格式、小数位数、货币样式、百分比样式等。 方法1：选定需要设置格式的单元格或单元格区域，在“开始”→“数字”组中单击相应的按钮进行设置。 方法2：选定需要设置格式的单元格或单元格区域，单击鼠标右键，在弹出菜单中选择“设置单元格格式”命令，打开“设置单元格格式”对话框，单击“数字”选项卡，在此可以完成多种数字格式设置。 1.1.2 设置单元格对齐方式 方法1：选定需要设置对齐方式的单元格或单元格区域，在“开始”→“对齐方式”组中单击相应的按钮进行设置。单击“方向”按钮，弹出下拉菜单，在其中可以设置文字的方向。 方法2：利用“设置单元格格式”对话框中的“对齐”选项卡，设置所需的对齐方式。 25 知识指导 1.1.3 设置单元格边框 方法1：选定需要设置边框的单元格或单元格区域，在“开始”→“字体”组中单击“边框”按钮，在弹出菜单中可以选择预设的边框类型，或者手动绘制需要的边框。 方法2：利用“设置单元格格式”对话框的“边框”选项卡，在此可以为选定的单元格区域设置各种边框线，还可以设置边框的线条颜色和样式。 1.1.4 设置单元格的填充 方法1：选定需要填充的单元格或单元格区域，在“开始”→“字体”组中单击“填充颜色”按钮，在弹出颜色框中选择需要的颜色即可。 方法2：利用“设置单元格格式”对话框的“填充”选项卡，在此可以为选定的单元格添加背景色、填充效果和填充图案。 26 知识指导 1.2.1 调整列宽或行高 将鼠标指针移到列号之间的分割线上，当鼠标指针呈双向箭头形状时，按住鼠标拖动向左或向右拖动，可调整列宽。 鼠标指到行号之间的分割线上，当鼠标指针呈双向箭头形状时，按住鼠标向上或向下拖动，可调整行高。 1.2.2 设置列宽和行高 单击列号选定要调整的列，在右键菜单中选择“列宽”命令，打开对话框，输入列宽后确定，可精确设置列宽。 单击行号选定要调整的行，在右键菜单中选择“行高”命令，打开对话框，输入行高值后确定，可精确设置行高。 1.2.3 自动调整列宽或行高 将鼠标指针移到某列右侧的分割线上，当鼠标指针呈双向箭头形状时，双击该分割线，Excel会依据该列内容最多的单元格的宽度自动调整列宽。双击行号下面的分割线，可自动调整行高。 27 知识指导 1.3.1 应用单元格样式 应用单元格样式，就是快速将选择的单元格区域填充为所需的样式，以美化和突出单元格中的数据。 （1）选定标题所在的A1单元格区域，在“开始”→“样式”组中单击“单元格样式”按钮，打开列表框。 （2）单击标题栏中的“标题”样式，即可为A1单元格应用该样式。 （3）参照上述方法，为A2:E2单元格区域应用主题单元格样式中的“着色1”样式。 1.3.2 套用表格格式 把Excel提供的样式自动套用到指定的单元格区域，可以快速设置表格格式。 （1）选定A3:E9单元格区域，在“开始”→“样式”组中单击“套用表格格式”按钮，打开列表框。 （2）选择“表样式浅色18”，弹出“套用表格式”对话框，单击“确定”按钮，完成套用表格格式操作。 28 知识指导 （3）为了显示效果更佳，需要对各列的列宽进行微调，方法是双击列号之间的分割线。 1.3.3 设置条件格式 条件格式可以对含有数值和其它内容的单元格，根据设定的条件来决定数值的显示格式。Excel 2016提供了多种条件格式规则，有突出显示、数据条、色阶和图标集等。 例如，对工作表“销售数量统计”设置条件格式，规则为将销售额在200元以上的单元格突出显示。操作过程是：选定D3：D8单元格区域，在“开始”→“样式”组中单击“条件格式”按钮，将鼠标指针移到“突出显示单元格规则”选项，然后单击“大于”选项，打开“大于”对话框，进行设置。 29 知识指导 任务实施 修饰学生党员志愿者情况登记表时，可按以下操作步骤完成。 步骤1：打开Excel工作表。 打开“项目4\任务2\素材\学生党员志愿者情况登记表.xlsx”，单击“登记表”标签，使之成为当前活动工作表。 步骤2：设置行高和列宽。 通过右键菜单，设置第1行（标题行）的行高为30，其余各行的行高为18。选中各列，在“开始”→“单元格”组中单击“格式”按钮，在弹出菜单中选择“自动调整列宽”选项。 步骤3：设置单元格数据格式。 单击工作表左上角的“全选”按钮 ，或者按[Ctrl +A]组合键，选中整张工作表，设置字体字号为宋体，字号为10；设置单元格对齐方式为水平和垂直居中；设置标题字体为黑体，字号为16号，利用“设置单元格格式”对话框，设置“填充日期”的格式为“*2012/3/14”类型。 30 知识指导 步骤4：为建档立卡设置批注，批注为“符合建档立卡条件的学生每年补助6000元” （1）选定要加批注的单元格L3。 （2）在“审阅”→“批注”组中单击“新建批注”按钮 ，在弹出的批注框中输入批注文字，“符合建档立卡条件的学生每年补助6000元”。 （3）单击批注框外部的工作区域即可退出。 步骤4：设置单元格边框和填充色。 选中A3：L13区域，利用“设置单元格格式”对话框的“边框”选项卡设置单元格边框，线条颜色为橙色，外框线型为2列6行样式，内框线型为1列6行样式；设置标题单元格和表头行填充颜色为浅绿。 31 知识指导 步骤5：为“登记表备份”工作表应用样式。 单击“登记表备份”标签，使之成为当前活动工作表，选定表格标题所在的A1：L1单元格区域，在“开始”→“样式”组中单击“单元格样式”按钮，在打开的列表框中单击标题栏中的“标题”样式；用同样的方法，为A2：L2单元格区域应用主题单元格样式中的“20%-着色5”样式；选定A3:L13单元格区域，在“开始”→“样式”组中单击“套用表格格式”按钮，在打开的列表框中选择“表样式中等深浅3”。 步骤6保存工作簿，退出Excel 2016。 32 知识指导 知识拓展 活动1设置工作表格式 1. 取消单元格网格线 在“视图”→“显示”组中，取消选中“网格线”复选框，即可隐藏网格线。 2. 设置单元格网格线的颜色 选择要更改其网格线颜色的工作表，单击“文件”→“选项”，打开“Excel选项”对话框，单击“高级”选项，在右侧找到“此工作表的显示选项”一栏，勾选“显示网格线”复选框，然后在“网格线颜色”框中，单击所需的颜色，确定后即可看到设置的单元格网格线颜色。在此，取消选中“显示网格线”复选框，也可以隐藏网格线。 3. 设置工作表背景 选择“页面布局”→“页面设置”组，单击“背景”按钮 ，打开“插入图片”对话框，单击“从文件”选项的“浏览”按钮，在打开的“工作表背景”对话框中选择所需要背景图片，单击“插入”按钮，即可完成背景图片的设置。 33 知识指导 4. 隐藏工作表 在需要隐藏的工作表标签上单击鼠标右键，在弹出的快捷菜单中选择“隐藏”，即可隐藏该工作表。在显示的工作表标签上单击鼠标右键，在弹出的快捷菜单中选择“取消隐藏”，打开“取消隐藏”对话框，选择需要恢复的工作表后确定，即可看到该工作表标签。 活动2 工作表保护 对于重要的工作表，用户可以对其进行保护设置，以限制其它人员的使用权限。 1. 设置工作表保护 在“计算机基础试卷”工作表中，为了保护试题不被修改，除“答题区”一列可填写答案外，其余各列均不允许操作，具体操作过程如下。 （1）取消“答题区”一列的锁定。选中A列，在右键菜单中单击“设置单元格格式”，在打开的“单元格格式”对话框中，单击“保护”选项卡，取消勾选“锁定”复选框，单击“确定”按钮。 34 知识指导 （2）在“审阅”→“更改”组中单击“保护工作表”按钮 ，打开“保护工作表”对话框，仅勾选“选定未锁定的单元格”，并输入密码，单击“确定”按钮，在弹出的“确认密码”对话框中，重新输入密码，单击“确定”按钮，完成工作表的保护设置。 2. 设置工作簿保护 为工作簿文件“计算机基础试卷.xlsx”设置密码保护，用户在打开工作簿时必须输入密码，其操作步骤如下。 （1）打开“计算机基础试卷.xlsx”工作簿，在“文件”→“信息”选项中单击“保护工作簿”按钮 ，在弹出的下拉列表中选择“用密码进行加密”选项。 （2）在打开的“加密文档”对话框中输入密码，单击“确定”按钮，接着在“确认密码”对话框中重新输入密码。 （3）保存工作簿，关闭退出Excel窗口，完成密码设置。 （4）打开工作簿文件“计算机基础试卷.xlsx”时，将弹出“密码”对话框，此时需要输入正确的密码，工作簿才能正常打开。 35 知识指导 任务拓展 实训任务1 修饰公司年度销售额表格 任务描述：对“公司年度销售额”工作表进行修饰，使之更加清晰和美观，便于浏览。 任务要求： 1.打开“项目4\任务2\素材\公司年度销售额表格.xlsx”。 2.设置3到8行行高为15，B到G列的列宽为10。 3.设置B3:G8各单元格居中对齐。 4.设置B3:G8单元格数据楷体、12号。 5.设置标题行合并后居中，黑体16号。 6.设置单元格的所有边框为红色细实线。 7.套用表格样式，样式可自行选择，样张选择的是“表样式浅色17”，标题选择的是“标题1”样式。 8.应用主题，样式可自行选择，样张选择的是“回顾”效果。 9.保存工作簿文件。 36 知识指导 实训任务2 修饰饮料零售情况统计表 任务描述：对“饮料零售情况统计”工作表进行格式设置。 任务要求： 1.打开“项目4\任务2\素材\饮料零售情况统计.xlsx”。 2.删除标题行下方的一行空行，添加“合计”一行。 3.设置各行行高为15，各列的列宽为9。 4.设置各单元格的对齐方式，其中“名称”下方单元格对齐方式为“分散对齐（缩进）”。 5.设置“零售单价”一列的小数位为1位小数，并添加货币符号。 6.设置单元格的边框和填充色。 7.保存工作簿文件。 37 知识指导 实训任务3 制作捐赠物资统计表 任务描述：完成捐赠物资统计表制作和设置。 任务要求： 1.新建Excel工作簿，命名为“捐赠物资统计表.xlsx”。 2.打开“捐赠物资统计表.xlsx”，在Sheet1中完成表格的输入和设置。 3.制作完成后，保存工作簿文件。 38 任务4.3 学生党员志愿者成绩计算与统计 39 1 目录 2 知识指导 3 4 任务实施 知识扩展 任务扩展 40 1 知识指导 41 知识指导 1.1.1直接输入公式 选定要输入公式的单元格，如D3单元格，在其中输入公式“=B3*C3”，然后按Enter键确认输入，即可计算出结果。输入完成后，在单元格中显示的是公式计算的结果，公式则显示在编辑栏中。 1.1.2 选择单元格输入公式 输入公式的具体操作过程如下。 （1）选定要输入公式的单元格，键入“=”表示开始输入公式。 （2）单击B3单元格，此时单元格周围出现虚框，同时B3出现在等于号后面。 （3）输入运算符“*”。 （4）单击C3单元格，此时单元格周围出现虚框，同时C3出现在公式中。 （5）按Enter键确认，完成公式输入。 42 知识指导 1.1.3 复制公式 Excel中的公式可以进行复制。公式复制就是将一个单元格内的公式应用到其它需要相似公式的单元格中。复制公式时可以使用拖动填充柄的方式，也可以使用“复制→粘贴”的方式。 使用填充柄复制公式的操作方法如下。 （1）选定含有公式的单元格，比如，D3单元格，鼠标指针移动至其填充柄处。 （2）按住鼠标左键向下拖动，至D8单元格时释放鼠标，即可完成公式的复制。 如果需要在单元格中直接显示公式，单击“公式”→“显示公式”按钮，表格中所有使用公式的单元格将以公式的形式显示。 1.1.4 修改公式 公式和普通数据一样可以进行修改。方法为：选定要修改公式的单元格，用鼠标在编辑栏上定位光标，或者直接双击单元格，进入编辑状态即可进行修改，按回车键完成修改。 43 知识指导 1.2.1 相对引用 单元格相对引用形式为：B3、C3等。相对引用是默认的单元格引用形式，当复制相对引用的单元格时，公式中的单元格地址会随着位置发生变化。 1.2.2 绝对引用 单元格绝对引用形式为：$B$3、$C$3等，是在行号和列号前加“$”符号。绝对引用是指引用单元格和被引用单元格的位置关系是固定的，公式中的绝对引用不会随着单元格位置的变化而变化。 以 “销售数量统计”表为例，计算“百分比”一列数据时，总销售额所在的D9单元格需要绝对引用，否则在复制公式时就会出错，出现错误信息“#DIV/0!”的原因是，公式中的除数引用了空白单元格，比如D10单元格，Excel中空白单元格是按零值处理的。 1.2.3 混合引用 单元格混合引用形式为：B$3或$B3等。混合引用就是在引用一个单元格时，既有绝对引用，同时也包含相对引用，在公式复制过程中，只有相对单元格地址改变，绝对单元格地址则不会改变。 44 知识指导 1.3.1 常用函数的使用 常用函数包括求和、平均值、最大值、最小值和计数函数。以“销售数量统计”表为例，计算销售额“总计”时，函数的使用方法如下。 ①选定存放结果的单元格，本例为D9单元格。 ②在“公式”选项卡下，“函数库”中单击“自动求和”按钮，在D9单元格会自动出现求和函数以及求和的数据区域，按回车键确认即可。如果Excel推荐的数据区域不是需要计算的区域，则需要选择新的数据区域。 单击“自动求和”按钮下方的下拉按钮，在弹出的列表框中可以选择平均值、最大值、最小值和计数的快速计算。 1.3.2其它函数的使用 使用其它函数时，一般采用直接输入函数或插入函数的方式。IF函数为例进行介绍。 45 知识指导 方法1：直接输入函数式。 选定存放结果的单元格C3，直接在该单元格中输入IF函数，按回车键确认，即可看到结果。输入完成后，在单元格中显示的是函数计算的结果，函数则显示在编辑栏中。 IF函数是条件函数，格式为：IF（条件，值1，值2），函数功能是用来判断给定的条件是否满足，如果满足，就返回值1，否则返回值2。本例中，B3单元格的值为81时，条件满足，因此C3单元格的值为“及格”；B4单元格的值为55时，条件不满足，因此C4单元格的值为“不及格”。 方法2：插入函数。 选定存放结果的单元格C3，在“公式”→“函数库”组中单击“逻辑”按钮，在弹出的列表框中选择“IF”，打开“函数参数”对话框，在参数文本框中分别输入参数，单击“确定”按钮，即可在单元格中看到计算结果。 插入IF函数时，也可以在“公式”→“函数库”组中单击“插入函数”按钮，打开“插入函数”对话框，在“选择函数”列表中选中函数IF。 46 知识指导 任务实施 对学生党员志愿者成绩统计时，可按以下操作步骤完成。 步骤1：打开Excel工作表。 打开“项目4\任务3\素材\学生党员志愿者成绩统计.xlsx”。 步骤2：使用IF函数转换实训成绩。 将光标移至单元格G4中，并输入公式“=IF(F4="优秀",95,IF(F4="良好",85, IF(F4="及格",75,55)))”，回车确认，将学号为“09201”的学生的实训成绩转换成百分制。将鼠标指针移动G4单元格的填充柄处，按住鼠标左键向下拖动，至G13单元格时释放鼠标，完成函数的复制。 步骤3：计算平均成绩。 方法1：使用公式计算。选定H4单元格，输入公式“=(C4+D4+E4+G4)/4”，回车确认；通过复制公式完成其下单元格的平均值计算。 47 知识指导 方法2：使用AVERAGE函数计算。选定H4单元格，单击“自动求和”按钮右侧的下拉按钮，在弹出的列表框中选择“平均值”，此处，AVERAGE函数的参数需要重新选定为(C4:E4,G4)回车确认；通过复制函数完成其下单元格的平均值计算。 步骤4：计算综合分。 选定I4单元格，输入公式“=C4*$C$17+D4*$D$17+E4*$E$17+G4*$G$17”，回车确认；通过复制公式完成其下单元格的综合分计算。 步骤5：使用RANK函数计算名次。 方法1：直接输入函数。选定J4单元格，输入函数“=RANK(I4,$I$4:$I$13)”，回车确认；通过复制函数完成其下单元格的名次计算。 方法2：插入RANK函数。选定J4单元格，在“开始”→“编辑”组中单击“自动求和”按钮右侧的下拉按钮，在弹出的下拉列表框选择“其他函数”，打开“插入函数”对话框，在其中选中RANK函数，确定后打开“函数参数”对话框，在其中进行参数设置，单击“确定”按钮完成函数的插入；通过复制函数完成其下单元格的名次计算。 48 知识指导 步骤6：使用MAX函数计算最大值。 使用MAX函数计算。选定C14单元格，单击“自动求和”按钮右侧的下拉按钮，在弹出的下拉列表中选择“最大值”，回车确认；通过向右复制函数到文体列完成单元格的最大值计算。 步骤7：使用LARGE函数计算第2名和第三名。 使用LARGE函数计算。选定C15单元格，输入函数“=LARGE(C4:C13,2)”，回车确认；通过向右复制函数到文体列完成其单元格的名次计算。再次，选定C16单元格，输入函数“=LARGE(C4:C13,3)”，回车确认；通过向右复制函数到文体列完成单元格的名次计算。 至此，学生党员志愿者成绩计算和统计工作完毕。 步骤8：保存工作簿，退出Excel 2016。 49 知识指导 知识拓展 活动1跨工作表的单元格引用 公式或函数计算中，单元格可以跨工作表进行引用。一般引用形式为：工作表名!单元格名。 例如，在“第一季度销量统计”表中，销售量等于一月、二月和三月销售量之和，此处的合计销售量需要跨工作表引用，使用公式计算，可以采用以下两种方法完成。 方法1：直接输入公式。 选中“合计”工作表的C3单元格，输入“= 一月!C3+二月!C3+三月!C3”， 按回车键确认输入；复制该公式完成其下单元格的计算。 方法2：选择单元格输入公式。 （1）选中“合计”工作表的C3单元格，输入“=”， 表示开始输入公式。 （2）用鼠标单击“一月”工作表标签，切换到一月工作表中，在其中单击C3单元格。 （3）输入运算符“+”。 50 知识指导 （4）用鼠标单击“二月”工作表标签，切换到二月工作表中，在其中单击C3单元格。 （5）输入运算符“+”。 （6）用鼠标单击“三月”工作表标签，切换到三月工作表中，在其中单击C3单元格，此时，按回车键，完成公式输入。 （7）复制该公式完成其下单元格的计算。 51 知识指导 活动2 Excel常用函数 Excel函数种类丰富，涉及数据计算的各个方面，表4-1是一些常用函数的用法介绍。 52 知识指导 任务拓展 实训任务1 全球各国C02排放量统计 任务描述：根据提供的原始数据表，利用Excel函数计算的方法，对全球各国C02排放量进行统计和分析。 任务要求： 1.打开“项目4\任务3\素材\C02排放量统计.xlsx”。 2.利用函数完成表格中指定的各种数据计算。 3.对前三名CO2排放总量应用“绿-黄-红色阶”格式。 4.计算完成后，保存工作簿文件。 53 知识指导 实训任务2 饮料零售情况统计 任务描述：根据提供的原始数据表，利用Excel函数和公式，对“饮料零售情况统计”工作表中的数据进行计算。 任务要求： 1.打开“项目4\任务3\素材\饮料零售情况统计.xlsx”。 2.计算销售量和销售额合计。 3.计算各种饮料的利润。 4.计算完成后，保存工作簿文件。 54 知识指导 实训任务3 计算学生课程成绩 任务描述：根据提供的学生成绩数据表，利用公式计算学生的课程总评成绩。 任务要求： 1.打开“项目4\任务3\素材\学生成绩表.xlsx”。 2.“总评成绩”工作表中的“平时”和“期末”两列数据分别来自“平时成绩”和“期末成绩”工作表。 3.总评成绩计算办法为：总评=平时×0.3+期末×0.7。 4.算出总评中优秀的级别（使用IF函数），要求： 总分大于等于90分的在“级别”列显示“优秀”，否则显示空格； 5.计算完成后，保存工作簿文件。 55 任务4.4 商品销售数据整理与分析 56 1 目录 2 知识指导 3 4 任务实施 知识扩展 任务扩展 57 1 知识指导 58 知识指导 1.1.1 快速排序 （1）选中要排序列的任意单元格。 （2）在“数据”→“排序和筛选”组中单击“升序”按钮 或“降序”按钮 即可。 1.1.2 自定义排序 （1）选中要排序列的任意单元格。 （2）在“数据”→“排序和筛选”组中单击“排序”按钮 ，弹出“排序”对话框， 在对话框中选取需要排序的主关键字及升序或降序，单击“确定”按钮。 （3）当排序关键字不止一个时，单击“添加条件”，即可添加“次要关键字”。 如果需要按文字笔画多少排序，单击对话框中的“选项”按钮，在打开的“排序选项”对话框中设置。 1.2.1 数据的筛选 （1）单击工作表中数据区域内的任意单元格，在“数据”→“排序和筛选”组中单击“筛选”按钮，启用筛选，在每个列标题右侧都出现一个下拉按钮。 59 知识指导 （2）单击列标题“性别”旁的下拉按钮，弹出筛选器选择列表。 （3）在列表中只勾选“男”前的复选框，单击“确定”按钮，即可自动筛选出所有男性员工的记录，女性员工的记录已经被隐藏起来。 （4）单击列标题“电脑操作”旁的下拉按钮，又弹出筛选器选择列表，在其中选择“数字筛选”→“大于或等于”选项，弹出“自定义筛选方式”对话框。 （5）在“自定义筛选方式”对话框中设置筛选条件，单击“确定”按钮，筛选操作完成后，可以看到使用筛选的列标题“性别”和“电脑操作”右侧的下拉按钮变成了 ，而且行号显现为蓝色。 1.3.1 创建分类汇总 （1）按分类字段“科室”进行升序排序，把同一科室的记录组织在一起。 （2）在“数据”→“分级显示”组中单击“分类汇总”按钮 ，弹出“分类汇总”对话框。 （3）在对话框中勾选需要的选项，分类字段为“科室”，汇总方式为“平均值”、汇总项为“奖金”。 （4）单击“确定”按钮，完成分类汇总操作。 60 知识指导 1.3.2 分级显示 对数据分类汇总后，工作表左上方出现分级显示的级别符号 ，单击其中的级别数字，可以分级显示汇总的结果。 1.3.3 删除分类汇总 对于不需要或错误的分类汇总，可以将其删除。方法是：在分类汇总数据清单中选中任意单元格，在“数据”→“分级显示”组中单击“分类汇总”按钮，打开“分类汇总”对话框，单击对话框左下角的“全部删除”按钮，即可删除分类汇总结果。 61 知识指导 1.4.1 数据的合并计算 （1）在工作簿中新建“合计”工作表，工作表的行、列标题要与源数据清单相同，然后选中用于存放合并计算结果的单元格区域。 （2）在“数据”→“数据工具”组中单击“合并计算”按钮 ，弹出“合并计算”对话框，在“函数”对话框中选择“求和”，单击“引用位置”框右侧的拾取按钮，用鼠标选取“1分店”工作表的B3:D8单元格区域，返回“合并计算”对话框后，单击“添加”按钮，再选取“2分店”工作表的B3:D8单元格区域，单击“添加”按钮，最后选中“创建指向源数据的链接”复选框，单击“确定”按钮，完成合并计算操作。 （3）计算结果，以分类汇总的方式显示，单击工作表左侧的“+”号，可以显示源数据清单。 62 知识指导 1.5.1 图表的构成 图表标题：描述图表的名称，默认位置在图表的顶端。 图表区：图表区就是整个图表的背景区域，在其中显示整个图表及其全部元素。 图例：用色块表示图表中各种颜色所代表的含义。 绘图区：以坐标轴为界的区域。 数据系列：一个数据系列对应工作表中选定区域的一个或一列数据。 坐标轴：用于表示数值大小及分类的水平线和垂直线，上面标有数据值的刻度。一般情况下，X轴表示数据的分类，Y轴表示数据值的大小。 网格线：从坐标轴刻度线延伸出来并贯穿整个绘图区的线条系列，可有可无。 背景墙与基底：三维图表中会出现背景墙与基底，是包围在许多三维图表周围的区域，用于显示图表的维度和边界。 63 知识指导 1.5.2 制作图表 （1）嵌入式图表与新工作表图表 嵌入式图表：是指图表作为一个对象与其相关的工作表数据存放在同一工作表中。默认情况下，图表是作为嵌入图表放在工作表中。 新工作表图表：是以一个工作表的形式插在工作簿中，在打印输出时，工作表图表占一个页面。 （2）图标类型选择 Excel 2016提供了14种图表类型及组合图表类型，每种图表类型又分为多个子类型，比如柱形图、饼图、条形图、折线图等是较为常用的图表类型。 （3）创建图表 方法一：使用功能区创建图表 选定要为其绘制图表的数据区域，选择“插入”选项卡，即可看到包含的多个创建图表按钮。 单击“饼图”下拉按钮 ，在弹出的下拉列表中选择“二维饼图” 选项，即可在该工作表中插入一个饼图，它清晰的表现了每个数值占总值的比例。 64 知识指导 方法二：使用【插入图表】对话框创建图表 选定要为其绘制图表的数据区域。选择“插入”选项卡，在“图表”组中单击“推荐的图表” 按钮，即可打开“插入图表”对话框，在“推荐的图表”或“所有图表”选项卡中选择所需的图表类型，单击“确定”按钮，即可绘制一个簇状柱形图。主要用于各个项目之间数值多少的比较。 1.5.3 编辑图表 图表建立之后，如果有不合适之处，可以进行修改。 （1）调整图表大小和位置 单击图表区，然后拖动图表四周的尺寸控制点，可调整图表的大小。若要移动图表，只需单击图表区，鼠标拖动图表至所需位置即可。 （2）移动图表 默认情况下，图表是作为嵌入图表放在数据源所在的工作表中，如果要改变图表位置，可对图表进行移动操作。 65 知识指导 ①单击嵌入图表的任意位置将其激活。 ②选择“设计”→“位置”组，单击“移动图表”按钮，弹出移动图表对话框。若要将图表显示在图表工作表中，单击“新工作表”单选框；若要将图表显示为工作表中的嵌入图表，单击“对象位于”单选框，然后在“对象位于”框中单击工作表名称。 （3）更改图表的布局或样式 在“图表工具”→ “设计”选项卡：Excel在此预定义了多种图表布局和图表样式，单击所需的布局和样式即可应用。 （4）美化图表区和绘图区 在“图表工具”→“格式”选项卡：完成对图表的进一步美化设置。 （5）更改图表类型 ①单击图表，激活“图表工具”窗口。 ②在“设计”选项卡→“类型”组中，单击“更改图表类型”按钮，在列表中选择所需图形。 66 知识指导 （6）向图表添加或删除数据源： ①单击图表，激活“图表工具”窗口。 ②选择“设计”→“数据”，单击“选择数据”按钮，在弹出的“选择数据源”对话框中单击数据拾取按钮，在数据表中重新选择数据区域。 4.添加图表元素 方法一：选中图表，单击“图表设计”选项卡下的“添加图表元素”下拉按钮 ，选择需要的各选项进行添加及设置。 方法二：单击选中图表右边的小“+”，弹出“图表元素”下拉列表，勾选需要的各选项对其进行添加及设置。 操作技巧 在Excel 2016中，图表的编辑功能丰富而灵活，除以上介绍的方法外，还有下述方法可以使用。 （1）双击图表的任意一个组成部分，均能打开该部分的格式设置任务窗格。 （2）右键单击图表的任意一个组成部分，在弹出菜单中选择需要的修改命令，可快速执行编辑操作。 67 知识指导 任务实施 按以下操作步骤，从不同方面对四方公司的商品销售数据进行整理和分析。 步骤1：打开Excel工作表。 打开“项目4\任务4\素材\商品销售登记表.xlsx”。 步骤2：对数据进行排序。 对Sheet1中的销售数据，按“品牌”升序排序，对品牌相同的记录，按“商品类型”升序排序。 （1）选中“品牌”一列的任意单元格。 （2）在“数据”→“排序和筛选”组中单击“排序”按钮，弹出“排序”对话框，在“主关键字”下拉列表框中选择“品牌”字段。 （3）单击“添加条件”按钮，在“次要关键字”下拉列表框中选择“商品类型”字段，单击“确定”按钮。 68 知识指导 步骤3：对数据进行筛选。 对Sheet2中的销售数据，筛选出商品类型为“笔记本”的所有记录。 （1）单击Sheet2工作表中数据区域内的任意单元格，在“数据”→“排序和筛选”组中单击“筛选”按钮，启用筛选。 （2）单击列标题“商品类型”旁的下拉按钮，弹出筛选器选择列表。 （3）在列表中只勾选“笔记本”前的复选框，单击“确定”按钮，即可自动筛选出所有关于笔记本的销售记录。 步骤4：对数据进行分类汇总。 对Sheet3中的销售数据，计算出每种品牌商品的销售金额的总计。 （1）按“品牌”升序排序，把同一品牌的记录组织在一起。 （2）在“数据”→“分级显示”组中单击“分类汇总”按钮，弹出“分类汇总”对话框。 （3）在对话框中勾选需要的选项，分类字段为“品牌”，汇总方式为“求和”、汇总项为“销售金额”。 （4）单击“确定”按钮，即可完成分类汇总操作。 69 知识指导 步骤5：对数据进行合并计算。 对Sheet4中的销售数据，利用合并计算功能，计算出每种品牌商品的销售金额的总计。 （1）在Sheet4中制作 “分析表”，用于单独存放计算的结果，选中样张所示的单元格。 （2）在“数据”→“数据工具”组中单击“合并计算”按钮，弹出“合并计算”对话框，在“函数”对话框中选择“求和”，单击“引用位置”框右侧的拾取按钮，用鼠标选取B3:C23单元格区域，返回“合并计算”对话框后，在“标签位置”栏中选择“最左列”复选框，单击“确定”按钮即可。 70 知识指导 步骤6：制作和美化图表。 在Sheet5中制作饼图，对IBM服务器的各个型号的销售金额占比进行比较。 （1）选定D3:D6和G3:G6单元格区域。 （2）在“插入”→“图表”组中单击“饼图”按钮，在列表中选择图表类型为“二维饼图”，即可创建一个基本饼图，在图表工具“设计”选项卡下，→“图表布局”组中，单击“快速布局”按钮→选择“布局1”选项，然后在“图表标题”文本框中输入标题文字“IBM服务器销售分析”，并适当调整图表的大小和位置。 （3）在图表区空白处双击，打开“设置图表区格式”任务窗格。在“填充”选项中单击“图片或纹理填充”单选按钮，选择“纸莎草纸”纹理效果，单击“确定”按钮。 （4）鼠标移到图表标题处，单击右键，在弹出菜单中选择“字体”命令，打开“字体”对话框，设置字体为宋体，大小为16，颜色红。至此，图表修饰完成。 步骤7：保存工作簿文件，退出Excel 2016。 71 知识指导 知识拓展 活动1 建立数据透视表 1. 数据透视表 数据透视表实际上是一种交互式表格，能够方便地对大量数据进行快速汇总，并建立交叉列表。使用数据透视表，不仅能够通过转换行和列显示源数据的不同汇总结果，也能显示不同页面以筛选数据，同时还能根据用户的需要显示区域中的细节数据。 2. 创建数据透视表 以销售数量登记表为例，建立数据透视表，显示各分店各型号产品销售量的总和，具体操作步骤如下。 （1）选定工作表中的一个单元格。 （2）在“插入”选项卡→“表格”组中单击“数据透视表” 按钮 ，打开“创建数据透视表”对话框确认“表/区域”文本框为所需的数据区域，“选择放置数据透视表的位置”选中“现有工作表”单选钮，单击“位置”文本框右侧的拾取按钮，在现有工作表中指定放置数据透视表的第一个单元格，单击“确定”。 72 知识指导 （3）激活“数据透视表工具”窗口，同时工作表中出现空白数据透视表和透视表的字段列表。接下来是制作透视表的关键环节，就是将正确的字段放置到正确的位置。 （4）将字段放置到透视表布局特定的区域中，方法是：在“选择要添加到报表的字段”列表框中，右键单击字段名，在弹出菜单中选择所需的选项。例如，右键单击“经销店”字段，弹出菜单，从中选择“添加到报表筛选”即可完成该字段的放置。用相同方法完成其它字段的放置。设置完成后,“数据透视表字段列表” 。 （5）在数据透视表区域外单击，“数据透视表字段列表”即可自动隐藏。 （6）使用数据透视表。单击透视表中的下拉按钮，在弹出的列表中设置，可以使数据透视表动态地显示汇总结果。例如，单击“经销店”右侧下拉按钮，在其中选择“1分店”，然后单击“行标签”右侧下拉按钮，在其中选择“矿泉水”和“啤酒”。 73 知识指导 活动2建立超链接 1. 超链接 超链接可以从一个工作簿或文件快速跳转到其它工作簿或文件。超链接可以建立在单元格或图形上。 2. 建立超链接 （1）选定要链接的单元格或单元格区域。 （2）在“插入”→“链接”组单击“超链接”按钮 ，打开“插入超链接”对话框。 （3）单击“查找范围”下拉列表框，选择要链接的文件所在位置。 （4）单击“确定”按钮，即可完成超链接的建立。此时，选定的作为超链接显示的单元格内容变为蓝色，将鼠标移到链接处，鼠标指针变为手形时，单击即可打开相关文件。 74 知识指导 3. 编辑超链接 （1）单击超链接单元格旁边的单元格，利用方向键移动到包含超链接的单元格上。 （2）在“插入”→“链接”组单击“超链接”按钮 ，打开“编辑超链接”对话框。 （3）输入新的需要链接的位置，单击“确定”按钮即可。 4. 取消超链接 鼠标指向超链接单元格，单击鼠标右键，在弹出菜单中选择“取消超链接”命令，即可删除超链接。 75 知识指导 任务拓展 实训任务 超市销售数据统计与分析 任务描述：根据提供的原始销售数据，利用Excel的数据统计、分析操作，对超市销售数据进行所需的处理。 任务要求： 1.打开“项目4\任务4\素材\超市销售统计.xlsx” 2.对Sheet1中的销售数据，按“合计”降序排序。 3.对Sheet2中的销售数据，筛选出六月份销售额大于80000元的所有记录。 4.对Sheet3中的销售数据，计算汇总每个销售区间在每个月的销售总额。 5.对Sheet4中的销售数据，利用合并计算功能，计算全年4个连锁店的每种类别商品的销售金额。 6.在Sheet5中制作柱形图及饼图，并对图表进行适当的美化。 7.计算完成后，保存工作簿文件。 76 任务4.5 打印员工信息表 77 1 目录 2 知识指导 3 4 任务实施 知识扩展 任务扩展 78 1 知识指导 79 知识指导 1.1.1 设置纸张大小 Excel默认的纸张大小为A4纸，如果需要其它的纸张，可单击“页面设置”组的“纸张大小”按钮，在弹出的下拉列表中选择即可。 1.1.2 设置纸张方向 Excel默认的纸张方向为纵向，当表格较宽时，可设置为横向，单击“纸张方向”按钮选择即可。 1.1.3 设置页边距 页边距是指打印内容的边界与纸张边缘之间的距离，有上、下、左、右四个页边距。单击“页面设置”组的“页边距”按钮，在弹出的下拉列表中选择，或者单击“自定义边距”，打开页面设置的“页边距”选项卡，在此可精确设置四个边距的距离，还可以在“居中方式”栏中设置工作表在打印纸中的位置。 80 知识指导 1.1.4 设置打印区域 Excel默认打印的是整个工作表，通过设置打印区域，可在打印时只打印工作表的局部。操作方法是，选择要打印的局部区域，单击“页面设置”组的“打印区域”按钮，在弹出菜单中选择“设置打印区域”即可。 1.1.5 设置页眉/页脚 在“页眉/页脚”选项卡中可以对页眉/页脚进行简单的设置。若需设置较为复杂的页眉，可单击“自定义页眉”按钮，打开“页眉”对话框，页眉被分为左、中、右三个区，输入相应的文字，也可通过使用相应的工具栏按钮完成文本格式设置或插入页码等操作。自定义页脚的设置方法与此类似。 1.1.6 设置分页符 如果工作表比较大，Excel会根据纸张的大小自动进行分页，用户也可以根据自己的实际需要，插入分页符强行进行分页。 插入分页符的操作方法为：单击行号或列号，选定需要换页的行或列，在“页面布局”→“页面设置”组中单击“分隔符”按钮 ，在弹出的下拉列表中选择“插入分页符”即可，这时在工作表中可看到表示分页的一条虚线。 81 知识指导 如果要删除分页符，先选定分页符下方一行或右侧一列，在“页面布局”→“页面设置”组中单击“分隔符”按钮，在弹出的下拉列表中选择“删除分页符”选项即可。 1.1.7 设置打印标题 打印较长表格时，由于行数或列数较多，工作表会分很多页，而只能在第一页有标题行，其它打印页均为“光头”表格。如果需要在每一页都打印出标题行，就需要设置打印标题，具体操作方法如下。 ① 在“页面布局”→“页面设置”组中单击“打印标题”按钮，打开页面设置对话框的“工作表”选项卡。 ② 如果标题行在工作表顶部，则单击“顶端标题行”文本框右侧的拾取按钮，到工作表中选定需要的行；如果标题行在工作表左侧，则单击“左端标题列”文本框右侧的拾取按钮，在工作表中选定需要的列。 ③单击“确定”按钮，完成打印标题的设置。此后打印输出的每页工作表，都会有相同的标题行或标题列。 82 知识指导 1.2.1 打印预览 进入打印预览窗口的方法有以下三种。 （1）单击“文件”→“打印”。 （2）在“自定义快速访问工具栏”中，单击“打印预览和打印”命令。 （3）按Ctrl+F2快捷键。 1.2.2 分页预览 在“视图”→“工作簿视图”组中单击“分页预览”按钮，或者单击状态栏中的“分页预览”按钮，即可分页预览工作表。 1.打印工作表 （1）单击“文件”→“打印”选项卡，进入打印预览和打印视图。 （2）在此需要设置打印机属性、打印的份数及打印的页码等，当各项参数均符合要求时，单击“打印”按钮，即可进行打印。 83 知识指导 任务实施 打印员工信息表时，可按以下操作步骤完成。 步骤1：打开Excel工作表。 打开“项目4\任务5\素材\职工信息表.xlsx”。 步骤2：页面设置。 为了得到理想的打印效果，需要对员工信息表进行以下设置。 （1）设置纸张的方向为横向。 由于本工作表较宽时，默认的A4纸纵向打印不完，需要设置为纸张方向为横向，在“页面布局”→“页面设置”组中单击“纸张方向”按钮选择即可。 （2）设置工作表在页面的位置为水平居中。 在“页面布局”→“页面设置”组中单击“页边距”按钮，在弹出的下拉列表中选择“自定义边距”，打开页面设置“页边距”选项卡，在“居中方式”栏中选择“水平”复选框，即可将工作表的位置调整为水平居中。 84 知识指导 （3）设置页眉/页脚。 打开“页面设置”对话框，在“页眉/页脚”选项卡中单击“自定义页眉”按钮，在打开的“页眉”对话框中选择页眉位置为“右”，单击“插入日期”按钮，插入当前日期作为为页眉内容，单击“确定”按钮，返回“页面设置”对话框，然后设置页脚格式为“第1页 共？页”，单击“确定”按钮，完成页眉和页脚设置。 （4）设置打印标题。 在“页面布局”→“页面设置”组中单击“打印标题”按钮，打开页面设置对话框的“工作表”选项卡，在“顶端标题行”文本框中直接输入“$1:$2”，单击“确定”按钮。 由于本工作表不能自动分页，为了能够观察打印标题的效果，可在工作表中插入一个分页符人工换页，比如，在行号为10的一行后面插入一个分页符。 步骤3：分页预览。 单击状态栏中的“分页预览”按钮，进入分页预览视图窗口，此时工作表的显示在此可以整体观察各个页面的数据情况。 85 知识指导 步骤4：打印预览。 单击“文件”→“打印”选项卡，在右侧窗口即可预览工作表的打印效果，在窗口下方选择页码，可以预览下一张工作表。 步骤5：打印工作表。 准备好打印机，在“文件”→“打印”选项卡中单击“打印”按钮，即可将电子表格打印输出。 步骤6：保存工作簿，退出Excel 2016。 86 知识指导 知识拓展 活动1拆分工作表窗口 如果工作表的行数和列数比较多，在查看对照数据时非常不方便，这时可以使用拆分窗口的功能，对表格进行横向或纵向分割，这样就可以方便地查看表格的不同部分。 1. 拆分工作表窗口 （1）选中一行或一列，在“视图”→“窗口”组中单击“拆分”按钮，即可将窗口拆分为不同的部分。 （2）在工作表选中任意单元格，在“视图”→“窗口”组中单击“拆分”按钮，系统将把选中单元格左上角当作交点，将窗口拆分成4个窗口。 2. 取消拆分窗口 双击分割条即可删除该分割条，从而取消窗口的拆分。 87 知识指导 活动2 冻结工作表窗口 使用冻结窗格功能可以把部分窗口冻结，然后在活动窗口中查看和编辑数据。 1. 冻结工作表窗口 冻结工作表窗口实质上也是将当前工作表活动窗口拆分成窗格，不过在冻结工作表窗口时，活动工作表的上窗格、左窗格将被冻结，通常是冻结行标题和列标题，被冻结的区域保持不动，通过滚动条来查看工作表内容。 冻结工作表窗口有以下三种方式。 （1）冻结拆分窗口：在工作表中选中一个单元格，单击“视图”→“窗口”组中单击“冻结窗口”按钮，系统将以选中单元格左上角为分界线，滚动工作表其余内容，窗口分割条为细实线。 （2）冻结首行：只冻结工作表中的第一行，其余行可滚动。 （3）冻结首列：只冻结工作表中的第一列，其余列可滚动。 2. 取消冻结工作表窗口 如果要取消窗口的冻结，单击“视图”→“窗口”组中单击“取消冻结窗口”按钮，冻结的窗口被取消。 88 知识指导 任务拓展 实训任务1 打印员工考核表 任务描述：对员工考核表按要求进行页面设置，然后预览工作表的设置效果。 任务要求： 1.打开“项目4\任务5\素材\员工培训考核表.xlsx”。 2.设置纸张大小及方向为A4纸、横向。 3.设置工作表位置为水平及垂直均居中。 4.设置页眉为文件名，页脚为“第1页，共？页”。 5.在第10行前插入分页符，设置表头和标题行为打印标题。 6.分页预览和打印预览工作表。 7.设置完成后，保存工作簿文件。 89 知识指导 实训任务2 打印班级通讯录 任务描述：对通讯录工作表按要求进行页面设置，完成后打印预览。 任务要求： 1.打开“项目4\任务5\素材\班级通讯录.xlsx”。 2.设置上、下、左、右页边距均为2.5，页眉/ 页脚距边距为1.0/1.5。 3.设置工作表的位置为水平居中。 4.设置页眉文字为“班级通讯录”，格式为居中、红色、方正舒体、28号字、双下划线；设置页脚为“第1页，共？页”，居中对齐。 5.设置标题行为打印标题。 6.打印预览，查看打印的模拟显示效果。 7.设置完成后，保存工作簿文件。 90 $$