第4章 电子表格软

MicrosoftExcel2003是Office系列办公软件之一，在Office中，有很多的组件支持向Ｅxcel中导出数据，或将文档存储成Excel数据格式。Excel的主要功能是对数据信息进行管理，即数据输入、数据运算、数据分析和数据共享。Excel功能强大且易于操作，通过本章学习，可以轻松的掌握Ｅxcel的使用方法。第4章电子表格软件Excel4.1案例1—制作学生基本情况表

案例说明

通过制作学生情况统计表，掌握工作薄及工作表的建立，文字输入（包括序列填充），设置表格的格式（包括合并单元格，边框，底纹）、设置文字格式、保存文件（包括密码的设定）以及表格的打印（包括页眉、页脚、打印区域的设定）。表格如图4-1所示。操作步骤1、启动Excel，同时选中A1至G1单元格，按”格式”工具条上的”合并及居中”按钮，将其合并成一个单元格，然后输入统计表的标题”学生成绩统计表”（见图4-2）。图4-1学生情况统计表图4-2标题合并单元格2、根据统计表的格式，将有关列标题及相关内容输入到相应的单元格中（见图4-3）。提示：其中序号、学号的输入可通过”填充柄”快速完成。 序号填充：在A3、A4单元格中分别输入1、2，同时选中A3和A4单元格，将鼠标移至A4单元格右下角的成”细十字”状时（通常称这种状态为”填充柄”状态），向下拖拽至A12。 学号填充：同序号填充。输入学号时需在数字前加”’“，即将学号格式设置为文本类型。图4-3表格输入内容图4-4单元格格式”边框”对话框图4-5加边框后的表格3、给表格添加边框。单击A1单元格，拖拉至G12选中整张表。然后单击【格式】菜单中【单元格】菜单项（如图4-4），选择”边框”标签。选择粗实线线条样式，单击”外边框”按钮，再选择细实线线条样式，单击”内部”按钮，单击确定按钮（如图4-5）。图4-6单元格格式”图案”对话框图4-7表格加入底纹4、给单元格加底纹。单击A1单元格，然后单击【格式】菜单中【单元格】菜单项，选择”图案”标签（如图4-6）。选择粉色，单击确定按钮。单击A2单元格，拖拉至A12，单击【格式】菜单中【单元格】菜单项，选择”图案”标签。选择绿色，单击确定按钮。表格加入底纹后如图4-7。图4-8单元格格式”字体”对话框图4-9字体修改后的表格

5、设置文字格式。单击A1单元格，然后单击【格式】菜单中【单元格】菜单项，选择”字体”标签（如图4-8）。设置字体为”楷体”，字形”加粗”，字号”14”，单击确定按钮。单击A2单元格，拖拉至G2，将其字体设置为字体为”宋体”，字形”加粗”，字号”12”。单击A3单元格，拖拉至A12，将其字体设置为字体为”宋体”，字形”加粗”，字号”12”。如图4-9。图4-10单元格格式”对齐”对话框图4-11对齐后的表格

6、单击A1单元格，拖拉至G12选中整张表。然后单击【格式】菜单中【单元格】菜单项，选择”对齐”标签（如图4-10）。选择对齐方式为水平居中、垂直居中。见图4-11。图4-12“选项”对话框

单击【文件】菜单中【保存】菜单项，选择文件要保存的位置，输入文件名为”学生情况统计表”。单击”确定”按钮。7、保存文件（包括密码的设定）。选【工具】菜单的【选项】命令，出现”选项对话框”。在”选项”对话框中选”安全性”选项卡（如图4-12），设置”打开权限密码”，点”确定”退出。 图4-13页面设置对话框

单击”自定义页眉”按钮，弹出”页眉”对话框（如图4-14），设置页眉中部显示”学生情况统计”后，点”确定”退出。8、打印学生基本情况表。（1）设置页眉页脚：选【文件】菜单的【页面设置】命令，选择”页眉页脚”标签（如图4-13）。图4-14图”页眉”对话框

单击”自定义页脚”，弹出”页脚”对话框（如图4-15），设置页脚右部显示系统日期，点”确定”退出。图4-15“页脚”对话框图4-16页面设置对话框（2）设置打印区域。选择工作标签，设置打印区域为A1：A12（如图4-16）。单击打印预览按钮，对打印页面进行预览。选【文件】菜单的【打印】命令，进行打印。图4-17“样式”对话框图4-18单元格格式”边框”对话框知识扩展1.样式 单击【格式】菜单中【样式】菜单项，弹出”样式”对话框（如图4-17），单击”修改”按钮，弹出”单元格格式”对话框（如图4-18）,可自定义表格样式。2、条件格式对选择区域中那些满足条件的单元格设置格式。单击【格式】菜单中【条件格式】菜单项，弹出条件格式对话框（如图4-19），填写相应的条件后,单击”格式”按钮，弹出单元格格式对话框（如图4-20），定义填充格式。图4-19“条件格式”对话框图4-20单元格格式”字体”对话框3、斜线表头的处理（1）单击工作表中要做斜线表头的单元格，输入斜线表头的内容，如有两个标题”项目名称”和”日期”，先输入”项目名称”，然后按快捷键Alt+回车键，这样可以在单元格内向下换行，再按Alt+回车键一次，输入”日期”。（2）按Ctrl+回车键，这样可以在不离开该单元格的情况下选中该单元格。（3）按快捷键Ctrl+1，打开”单元格格式”对话框。（如果在单元格上单击右键，然后从弹出的快捷菜单中选择命令”设置单元格格式”，也能打开该对话框，但使用快捷键会更快一些。）（4）在“单元格格式”对话框中，单击”边框”选项卡，然后单击右斜线按钮。图4-21表格中插入斜线表头

（5）在“单元格格式”对话框中，单击”对齐”选项卡，将文本的水平和垂直对齐方式都设置为”两端对齐”。设置完毕，单击”确定”按钮。（6）观察表头中的文本，若文字位置不是很合适。双击该单元格，将插入点定位到”项目名称”左边，并按空格键进行位置调整。（如图4-21）图4-22插入艺术字4、插入艺术字、图片、组织结构图单击【插入】菜单中【图片】菜单项，可选择为表格插入图片、艺术字或组织结构图（如图4-22）。4.2案例2—水果销售表案例说明 制作一个水果销售表。要求利用水果的单价和销售量计算出各种水果的货物总价以及计算出所有水果的销售总量和销售总价，并筛选出单价高于￥3.00的水果，创建出水果销售比例的图表。通过本实例的实践学习，读者可掌握如何在Excel中运用自动套用格式、如何对数据进行筛选以及根据数据创建相关图表等知识。本实例的最终效果如图4-23及图4-24所示。图4-23水果销售表最终效果图图4-24水果销售表图表

操作步骤（1）单击“常用”工具栏中的“新建”按钮，新建一个工作簿。（2）在Sheet1工作表中输入以下内容，如图4-25所示。图4-25输入表格图4-26计算货物总价图4-27通过拖拽计算全部货物总价图4-28插入函数对话框（3）在F3单元格中输入公式：＝D3*E3，如图4-26所示。（4）按【Enter】键，即可得到砀山梨的货物总价。选定F3单元格，将鼠标指针移至该单元格右下方的填充柄上，待鼠标指针变为黑色加号（＋）形状时，按住鼠标左键并向下拖动鼠标至F12单元格释放鼠标，相应的数值将会自动填充单元格。如图4-27。图4-29函数参数对话框（6）选中E13单元格，单击“插入函数”按钮，弹出插入函数对话框，如图4-28所示。选择“SUM”,单击”确定”，弹出函数参数对话框（如图4-29），将Number1清空。图4-30选定参数区域单击按钮，在表格上单击E3单元格拖拉至E12，确定函数参数，如图4-30。单击按钮，返回（如图4-31），单击”确定”按钮。图4-31函数参数确定（7）选定E13单元格，将鼠标指针移至该单元格右下方的填充柄上，待鼠标指针变为黑色加号（＋）形状时，按住鼠标左键并向右拖动鼠标至F13单元格释放鼠标，相应的数值将会自动填充单元格。如图4-32。

图4-32拖拽产生货物总价的和图4-33同时选取多个区域数字更改为货币型图4-34【自动套用格式】对话框（8）选定D3:D12单元格区域，按Ctrl键同时选定F3:F13单元格，单击”格式”菜单中的”单元格”命令，弹出”单元格格式”对话框，在”数字”选项卡中的”分类”列表框中选择”货币”，单击确定，如图4-33所示。图4-35自动套用格式后的表格图4-36【图表向导】对话框（9）选定A1:F13单元格区域，单击”格式”菜单中的”自动套用格式”命令，弹出”自动套用格式”对话框，选定”简单”模式，如图4-34所示。（10）单击”确定”按钮，效果如图4-35所示。（11）选定B2:B12单元格区域，按住Ctrl键再选定F2:F12单元格区域，单击”插入”菜单中的”图表”命令，弹出”图表向导”对话框，在”图表类型”列表框中选择的”饼图”，在”子图表类型”列表框中选择”分离型三维饼图”，如图4-36所示。（12）单击”下一步”按钮，进入如图4­-37所示对话框，自动生成”数据区域”。 说明：因为在创建图表前就已经选择过用于创建图表的数据区域B2:B12和F2:F12，因此在步骤2中会自动生成数据区域，不需进行任何设置。如果在创建图表前没有选择数据区域，则在图4­-37所示对话框中需到原工作表中选取用来创建图表的数据区域。图4­-37【图表源数据】对话框图4­-38【图表选项】对话框图4­-39图例位置设置为底部图4­-40【图表位置】对话框（13）单击”下一步”按钮，进入”图表选项”对话框，在”标题”选项卡中将”图表标题”设置为”水果销售比例”，如图4­-38所示。单击”图例”选项卡，将”图例位置”设置为”底部”，如图4­-39所示。图4­-41图表插入后效果图4­-42修改标题格式（14）单击”下一步”按钮，进入”图表位置”对话框，选择”作为其中的对象插入”选项，在其右边的下拉列表中选择”Sheet2”，如图4­-40所示。（15）单击”完成”按钮，则创建好的图表插入到Sheet2工作表中，效果如图4­-41所示。（16）选定图表中的标题”水果销售比例”，单击”格式”菜单中的”图表标题”命令，弹出”图表标题格式”对话框，选择”字体”选项卡，将字体设置为”黑体”，字号设置为16号，效果如图4­-42所示。（17）在Sheet2工作表中拖动图表，将图表定位在A1:F14单元格区域。（18）选定Sheet1工作表中的A2:F12单元格区域，单击”数据”菜单中的”排序”命令，弹出”排序”对话框，在”主要关键字”下拉列表框中选择”销售量”选项，并选中”递减”单选按钮；在”我的数据区域”选项区中选择”有标题行”单选按钮（如图4­-43），单击”确定”按钮，效果如图4­-44所示。图4­-43【排序】对话框图4­-44排序后的图表图4­-45自动筛选图表后的效果图4­-46自定义自动筛选方式（20）选定A2:F12单元格区域，单击”数据”菜单，指向”筛选”，在弹出的级联菜单中选择”自动筛选”命令，效果如图4­-45所示。（21）筛选单价高于￥3.00的数据记录：单击”单价”字段右侧的下拉按钮，在弹出的下拉列表中选择”（自定义…）”选项，将弹出”自定义自动筛选方式”对话框，在”单价”下拉列表框中选择”大于”选项，在其右边的下拉列表框中输入￥3.00，如图4­-46所示。（22）单击”确定”按钮完成，本实例制作完成，最终效果参见图4­-23。（23）保存创建的”水果销售表”：单击”文件”菜单中的”保存”（或”另存为”）命令，弹出”另存为”对话框，选择保存的位置为D盘，输入文件名为”水果销售表”，单击”保存”按钮即可。知识扩展1、单元格的引用EXCEL单元格的引用包括相对引用、绝对引用和混合引用三种。相对引用:公式中的相对单元格引用（例如A1）是基于包含公式和单元格引用的单元格的相对位置。如果公式所在单元格的位置改变，引用也随之改变。如果多行或多列地复制公式，引用会自动调整。默认情况下，新公式使用相对引用。绝对引用:单元格中的绝对单元格引用（例如$A$1）总是在指定位置引用单元格。如果公式所在单元格的位置改变，绝对引用保持不变。如果多行或多列地复制公式，绝对引用将不作调整。混合引用:混合引用具有绝对列和相对行，或是绝对行和相对列。绝对引用列采用$A1、$B1等形式。绝对引用行采用A$1、B$1等形式。如果公式所在单元格的位置改变，则相对引用改变，而绝对引用不变。如果多行或多列地复制公式，相对引用自动调整，而绝对引用不作调整。2、公式的复制与粘贴

选择要复制的单元格复制后，选择”编辑”菜单下的”选择性粘贴”（如图4-47）。选择要粘贴的内容。

图4-47【选择性粘贴】对话框图4-48【保护】选项卡图4-49【保护工作表】命令图4-50保护工作表对话框3、隐藏公式 隐藏公式的单元格，在选定此单元格时，公式不会出现在编辑栏中,隐藏公式的操作步骤如下:（1）选定想隐藏公式的单元格范围，从”格式”菜单中选择”单元格”命令，选定”保护”选项卡，如图4-48所示。（2）选定标记为”隐藏”的选择框,按下”确定”按钮；进入到”工具”菜单中选择”保护”命令中的子命令”保护工作表”命令，如图4-49所示。图4-51【确认口令】对话框图4-52保护后的表格

如果要取消隐藏的公式可以先撤销工作表保护再从”保护”选项卡中清除标记为”隐藏”的复选框。（3）在屏幕上出现图4-50的口令对话框。（4）在其中输入个口令，接着出现图4-51的确认口令对话框，操作后，就会看到公式不在编辑栏中出现，如图4-52，请注意变化。

4、用计算结果替代公式 可以用计算结果替换公式，从而”冻结”公式，使之不再重新计算。如果只想冻结公式的一部分，那么可以只替换不想重新计算的这一部分。如果工作簿中包含许多公式或复杂的公式，而且您希望通过创建静态数据来提高工作簿的性能，那么可以用计算结果替换公式。要点：请确保检查了用计算结果替换公式所产生的影响，尤其是当公式引用了其他包含公式的单元格时。用计算结果替换公式之前，最好保留一份工作簿的副本。

用计算值替换公式的一部分 单击包含公式的单元格，在编辑栏

中，选择要用计算值替换的公式部分。在选择要替换的公式部分时，请确保将完整的操作数

（操作数：操作数是公式中运算符任意一侧的项。在Excel中，操作数可以是值、单元格引用、名称、标签和函数。）包括在内。例如，如果选择一个函数，则必须选择完整的函数名称、左括号、参数

（参数：函数中用来执行操作或计算的值。参数的类型与函数有关。函数中常用的参数类型包括数字、文本、单元格引用和名称。）和右括号。若要计算所选部分，请按F9。若要用计算值替换公式的所选部分，请按Enter。注意：如果用计算值替换公式，则MicrosoftOfficeExcel将永远删除该公式。如果不小心用值替换了公式，并希望还原该公式，请在输入或粘贴值后，立即单击工具栏上的”撤消”按钮。选择包含公式的单元格。5、公式审核在用Excel录入完大量数据后，不可避免地会产生许多错误。有时，我们录入的数据是要符合一定条件的。例如，对于学生成绩表而言，其中的数据通常是0～100之间的某个数值。但是，在录入时由于错误按键或重复按键等原因，我们可能会录入超出此范围的数据。那么，对于这样的无效数据，让我们用公式审核法来解决这类问题。首先，打开一张学生成绩表。然后，选中数据区域（注意:这里的数据区域不包括标题行或列），单击”数据→有效性”命令，进入”数据有效性”对话框。在”设置”选项卡中，将”允许”项设为”小数”，”数据”项设为”介于”，并在”最小值”和”最大值”中分别输入”0”和”100”（见图4-53），最后单击”确定”退出。图4-53【数据→有效性】对话框图4-54圈释无效数据

接着，执行”视图→工具栏→公式审核”命令，打开”公式审核”工具栏。好了，现在直接单击”圈释无效数据”按钮即可。此时，我们就可看到表中的所有无效数据立刻被红圈给圈识出来了（见图4-54）。根据红圈标记的标志，对错误数据进行修正。修正后红圈标记会自动消失。这样，无效数据就轻松地被更正过来了。图4-55取消对锁定图4-56【保护工作表】对话框6、单元格的保护 第一步，用快捷键”Ctrl+A”全选整个工作表，再按”Ctrl+1”调出”单元格格式”设置面板，切换到”保护”选项卡，取消对”锁定”的勾选（图4-55），并确定。图4-57【确认密码】界面

此时，用鼠标点击在第二步中选定的范围内任意单元格，会发现这些单元格已经无法选中了。而这个范围外的其它单元格则可以选中并编辑。第二步，选中要保护的范围，再按”Ctrl+1”调出”单元格格式”设置面板，在”保护”选项卡中勾选”锁定”，确定退出。

第三步，从菜单中依次点选”工具”→”保护”→”保护工作表”，弹出如图4-56右图所示”保护工作表”密码设置界面，在其中输入密码并确定。当然，如果不需要设置密码，也可以直接确定退出。如果输入了密码，确定后会出现”确认密码”界面（图4-57），再次输入同一密码并确认即可。7、数据保护常用方法（1）设置打开及修改密码。操作：文件→另存为→工具→常规选项（2）设置区域保护（适用防范自己对重要数据误操作）操作：1.全选表→格式→单元格→保护→锁定（去掉勾）

2.选保护的区域→格式→单元格→保护→锁定（添加勾）

3.工具→保护→保护工作表4.3案例3—固定资产报表案例说明

制作一个固定资产报表。要求对各种资产设备的数量进行求和汇总并生成一个数据透视表。本实例介绍了一个资产管理实例的制作方法，通过这个实例，读者可以掌握数据分类汇总分方法，数据透视表的创建方法等。本实例最终效果如图4­-58及图4-59所示。图4­-58数据透视表最终效果图4-59通过数据秀视表演查看数据知识点：工作表重命名、分类汇总、单元格数据的复制与粘贴、数据透视表。操作步骤（1）单击”常用”工具栏中的”新建”按钮，新建一个工作簿。（2）在A1单元格中输入”计算机系办公室设备台帐”；选定A1:G1单元格区域，单击”格式”工具栏中的”合并及居中”按钮，并将其”字体”设置为”黑体”，”字号”设置为14。（3）在工作表的其他单元格中输入相关内容，并适当调整各单元格的列宽和行高，如图4­-60所示。图4­-60输入表格（4）双击工作表标签Sheet1，将其重命名为”统计报表”；双击工作表标签Sheet2，将其重命名为”分类汇总表”。（5）单击”统计报表”工作表标签，使其成为当前工作表，按【Ctrl＋A】组合键，选定整个工作表，再按【Ctrl＋C】组合键，将选中的所有数据记录复制到剪贴板上；单击”分类汇总表”工作表标签，使之成为当前工作表，然后按【Ctrl＋V】组合键，将剪贴板上的数据记录复制到该工作表中。（6）选定”分类汇总表”工作区中数据区域内的任意单元格，单击”数据”菜单中的”分类汇总”命令，弹出”分类汇总”对话框，在”分类字段”下拉列表中选择”设备名称”选项，在”汇总方式”下拉列表框中选择”求和”选项，在”选定汇总项”列表框中选中”数量”复选框，然后选中”替换当前分类汇总”复选框和”汇总结果显示在数据下方”复选框，如图4­-61所示。图4­-61“分类汇总”对话框图4­-62对数据进行分类汇总

（7）单击”确定”按钮，返回到工作表，可以看到，每类设备下面显示了该类分类汇总数据，在工作表的左侧出现了分级显示的目录树，如图4­-62所示。图4­-63显示各种设备的汇总情况

（8）单击目录树上方的按钮，隐藏每一类设备的详细数据信息，而只显示各种设备的汇总情况，如图4­-63所示。图4­-64“数据透视表和数据透视