excel实例教程

null高级办公应用 第三章 Excel 2000的高级应用 高级办公应用 第三章 Excel 2000的高级应用 3.1 编制工资表 3.1 编制工资表 教学目标： 通过本节的学习能够在日常工作中高效的完成数据的设计、分析、 管理，并能够对数据进行安全性控制。 教学的主要内容： ◎数据有效性设置 ◎数据处理 ◎打印技巧 3.1.1数据的有效性3.1.1数据的有效性1、列表选择性输入 在用Excel进行数据处理是，有大量的原始数据需要用键盘输入。特 别是有一类数据，其值只限于几项之一，例如“性别”、“男女”、“婚否” 仅限于“是”、“否”……对于这类数据为保证快速完成数据的输入同时又 避免输入错误，我们可以采用“列表选择”的方式输入。具体操作步骤 如下： （1）在“工资表.xls”中选择需列表选择性输入的单元格区域D3:D6，单击“数据/有效性”。如右图3-1所示：（1）在“工资表.xls”中选择需列表选择性输入的单元格区域D3:D6，单击“数据/有效性”。如右图3-1所示：图3-1（2）在“数据有效性”对话框中“设置”选项卡，讲“有效性条件”设为“序列”，在“来源”文本框中输入“教授，副教授，讲师，助讲，职员”，即“职称”的所有可选项，注意各项选项之间必须用西文下的逗号“，”隔开，且选中“忽略空格”和“提供下拉箭头”复选框后，单击确定按钮完成设置，如右图3-1所示：（2）在“数据有效性”对话框中“设置”选项卡，讲“有效性条件”设为“序列”，在“来源”文本框中输入“教授，副教授，讲师，助讲，职员”，即“职称”的所有可选项，注意各项选项之间必须用西文下的逗号“，”隔开，且选中“忽略空格”和“提供下拉箭头”复选框后，单击确定按钮完成设置，如右图3-1所示：图3-2（3）列表数据设置完毕之后，单击设置区域中的任意单元格，其右边会出现一个下拉箭头，单击下拉箭头，即可在数据列表中单击选择完成输入，如下图3-3 （3）列表数据设置完毕之后，单击设置区域中的任意单元格，其右边会出现一个下拉箭头，单击下拉箭头，即可在数据列表中单击选择完成输入，如下图3-3 图3-32、快速标注不符合有效性规则的数据2、快速标注不符合有效性规则的数据 向工资表输入数据信息时，有可能会出现输入错误，而一旦数据 有误就会影响后面的计算，如要保证数据输入无误，通常我们在 输入数据前，利用“数据/有效性”命令的方式进行数据输入有效性的炎 症，防止数据输入时不必要的错误发生。 如果已经在工资表中输入了数据，如何快速查找出不符合限定条件 的数据？其具体操作步骤如图-GIF1：null图-GIF13、数据有效性3、数据有效性 列表选择是数据有效性的一种设置，数据有效性的设置方法还 有很多，例如有效性条件为“时间”，则可以设置有效时间段，不再有 效时间段内的时间将无法输入；有效性条件为“文本长度”。则可设置 文本有效长度，超过有效长度的文本将无法输入。 若想取消数据有效性设置，先选定要取消数据有效的区域，如 在图3-2所示“数据有效性”对话框中单击“全部取消”按钮，或者将有效 性条件改为任何值，单击“确定”完成设置。 3.1.2数据处理 1、查找数据 （1）使用记录单查找数据，例如在“职工工资”中查找“公积”>=120的所有讲师的记录”，如图3-4：3.1.2数据处理 1、查找数据 （1）使用记录单查找数据，例如在“职工工资”中查找“公积”>=120的所有讲师的记录”，如图3-4：图3-4(2)查找公式 当工作表输入大量公式后，有时候会找不到某个公式，此时按“ctrl+~”键即可显示出工作表中的所有公式，编译查找。再按“ctrl+~”键切换回原来的计算结果状态。 (3)快速选取包含公式的单元格 如果需要对工作表中包含公式的单元格加以保护，或与其他单元格 加以区分是，可以用以下方法快速选取包含公式的单元格，具体操作如图3-5： (2)查找公式 当工作表输入大量公式后，有时候会找不到某个公式，此时按“ctrl+~”键即可显示出工作表中的所有公式，编译查找。再按“ctrl+~”键切换回原来的计算结果状态。 (3)快速选取包含公式的单元格 如果需要对工作表中包含公式的单元格加以保护，或与其他单元格 加以区分是，可以用以下方法快速选取包含公式的单元格，具体操作如图3-5： 图3-5“编辑\定位”单击“定位条件”选择“公式”选项null2、条件格式 在Excel表格中我们通常对数据进行各种各样的格式设置来美化表格。 但在很多情况下，数据格式设置为有条件的，数据将按不同条件区分显 示。例如将“职工工资”中“基本工资”低于1500元的所有记录用红色显示。（1）选取待条件格式设置的单元格区域“基本工资”所在列，执行菜单 栏上“格式\条件格式”命令，打开“条件格式”对话框。如图3-6所示：图3-6（2）在“条件格式”对话框中，选择、输入条件“单元格小于1500”；单击“格式按钮”，在打开的“单元格格式”对话框中，选择“字体”选项卡，设置字体颜色格式为“红色”，执行“确定”命令后，满足条件的记录全部用红色显示。 提示：如果要添加某种条件格式设置，单击“添加”按钮来进行设置。如果不想显示或打印的数据可将其格式设置为白色。null3、获得最合适的列宽4、单元格中多行显示 方法一：设置单元格，如图3-7所示：图3-7方法二：将光标定位于需要换行的单元格处，使用快捷键“Alt+Enter”，可实现人工换行3.1.3制作工资条3.1.3制作工资条 工资表制作完成后，将打印成工资条。工资条的特点是每个人的记录都 要配有工资栏目，用一般的方法并不容易，我们可以巧用排序的方法来解决。 （1）将“工资表”中的所有职工记录（注意：不包括工资栏目行）复制到里一 张新工作表中（如sheet2），并在第一列前插入一新列，冲第一名职工开始， 利用自动填充的方法输入的差数列2，4，6……，如图3-8所示在第一列前插入一新列自动填充的方法输入最后效果（2）将“工资表”中的工资栏目复制到sheet2中最后一名职工记录之后，注意：有几条职工记录，即复制几条工资栏目，然后再利用自动填充的方法输入等差数列1，3，5……，如图3-9所示（2）将“工资表”中的工资栏目复制到sheet2中最后一名职工记录之后，注意：有几条职工记录，即复制几条工资栏目，然后再利用自动填充的方法输入等差数列1，3，5……，如图3-9所示图3-9（3）选定整个数据区域，按A列的序号数据升序排列。排好后将A列的序号 数据删掉即可得到所需工资条，如图3-10所示：图3-103.1.4表格打印技巧3.1.4表格打印技巧1、表格的缩放 一个表格，一页多一点要缩为一页，除了可以调整也边距外，还可以用 Excel里提供的缩放功能实现。 单击菜单栏上“文件/页面设置”命令，在“页面”选项卡中，选中“调整为”单 选项，输入适当的页数“1”，如图3-11所示： 如果有一个工作表数据较多，需要使用 A3的执行才能在一页全部显示，而当前的 打印机（例如hp LaserJet1010）最多只能 打印A4的纸张。此时可以使用缩放打印的 功能：在“页面”选项卡中单击“选项”按钮， 如图3-11所示，打开“hp LaserJet1010属 性”对话框并选择“纸张”选项卡，在“打印纸 尺寸”下拉列表框中选择A4值上打印A3纸型 的工作表。图3-112、表格栏目的冻结2、表格栏目的冻结 如果一个表格由很多页，当我们上下浏览表格时，第一页能够看到表格栏目 ，后续页看不到，但往往后续各页也需要显示表格栏目，此时我们可以冻结栏目 。其具体操作如下： （1）单击菜单栏上“窗口/拆分”命令，界面上出现拆分条，将拆分条拖到栏目 下。如图3-12所示（2）单击菜单栏上“窗口/冻结窗格”命令，则栏目一行被冻结。当拖动滚动条 浏览时，栏目不会动，各页均能看到栏目。 提示：要“撤销窗口冻结”或“撤销拆分窗口”，点击“窗口”命令，选择相应 选项命令即可。3、表格栏目的重复打印3、表格栏目的重复打印 栏目冻结只适用于屏幕显示，并不能用于打印。若打印时希望每页表格都 有栏目，必须在“页面设置”中进行调整。具体操作如下： 执行菜单栏上“文件/页面设置”命令，在“页面设置”对话框中选择“工作表” 选项卡，在“顶端标题行”编辑框中输入“$1：$1”，单击“确定”完成设置。 如果想打印“行号列号”，在“工作表”选项卡中选中“行号列标”复选框。如 图3-13所示：图3-13页面设置注意： “$1：$1”表示第一行，同理“$A:$A”表示第一列， “$1：$2”表示第一行 和第二行3.2 数据统计与分析3.2 数据统计与分析教学目标： 通过本节学习，掌握数据透视表的基本结构，并能够运用数据透视表 和透视图对Excel工作表中格的数据根据实际需要重新组织与统计分析。 教学主要内容： ◎创建数据透视表 ◎使用数据透视表分析数据 ◎创建数据透视图 ◎发布分析结果3.2.1 创建数据透视表3.2.1 创建数据透视表 1.数据图示表 数据透视表是Excel提供的强大的数据分析处理工具。他能够对数据 进行交叉分析，可以转换行和列以查看眼数据的不同汇果，可以显 示不同页面以筛选数据，还可以根据需要显示所选区域中的明细数据， 非常便于在一个清单中重新组织和统计数据。 数据透视表主要组成： （1）字段：在数据透视表中被指定为行方向的源数据库中的字段。 （2）列字段：在数据透视表中被指定为列方向的源数据库中的字段。 （3）数据字段：含有数据的源数据库中的字段项，提供要汇总的数 据值。 （4）页字段：在数据透视表中被指定为页方向的源数据库中的字段。 允许筛选整个数据透视表，以显示单项或所有项的数据。 2.创建数据透视表2.创建数据透视表 创建数据透视表的操作比较复杂的，为方便用户使用，Excel提供了 数据透视表和图表。通过次向导将数据源进行交叉制表和汇总，并 重新布局、计果，最终可以将平面的工作表数据转换为立体的分析结果 ，具体操作步骤如图3-14所示：null图3-14 3.2.2 使用数据透视表分析数据 3.2.2 使用数据透视表分析数据 原始的数据源清单记录了销售的详细内容，但只是一个流 水账式的工作表虽然可以使用筛选、分类汇总等技术手段 对工作表中的数据进一步分析但很难得到更灵活、更丰富 的分析结果。建立数据透视表后，对当前生成的数据透表 进行操作，可以获得需要的分析数据。1、筛选数据1、筛选数据（1）显示页字段筛选数据，如图3-15所示图3-15（2）显示行字段筛选数据，如图3-16所示（2）显示行字段筛选数据，如图3-16所示（3）显示列字段筛选数据，如图3-17所示图3-16图3-172、更改数据透视表的布局2、更改数据透视表的布局 创建数据透视表后。Excel自动打开“数据透视表”工具栏（或单击“ 视图/工具栏/数据透视表”命令，也可打开“数据透视表”工具栏），如 图3-17所示：图3-18 “数据透视表”工具栏 使用该栏可对数据透视表进行板式布局、数据更新等修改。修改数据透视表版式布局包括更换、增加、移动表结构中的字段等。3、创建销售月报表3、创建销售月报表 在原始的数据表中，销售数据是按照具体日期填写的，而利用数据透 视表还可以制作日报表、月报表、年报表等。现以创建销售月报表为例 ，数据透视表的使用。如图3-19所示：null图3-194、重新设置汇总方式4、重新设置汇总方式 创建的数据透视表默认的汇总方式是“求和”,重新设置汇总方式可以通 过“数据透视表”工具栏中的“字段设置” 按钮。其具体操作如下： 单击汇总方式单元格区域的任意单元格，选择“数据透视表”工具栏中 的“字段设置”按钮，打开“数据透视字段”对话框，重新输入新字段名称， 选择新的汇总方式，例如求最大值。如图3-20所示。单击确定完成设置， 即按新的汇总方式进行汇总。 图3-205、更改数据5、更改数据 数据透视表的数据来源于数据源，不能再透视表中直接 修改数据 。数据清单中的数据改变了，透视表中的数据必 须执行更新数据操作才能刷新。具体操作如下： 数据源清单中的数据改变后，单击“数据透视表”工具栏 中的“更新数据” 按钮；或执行菜单栏上“数据/更新数据” 命令均可自动更新数据。 3.2.3 创建数据透视图 3.2.3 创建数据透视图 数据透视表是用表格的形式来显示和分析数据。而数据 透视图则是通过图表的方式来显示和分析数据。 对于数据工作表，创建数据透视图的过程和创建数据透表相似，不同之处是在“数据透视表和数据透视图向导-3步骤之一”的对话框中选择报表类型为“数据透视图”，如图3-21所示。图3-21null 对于创建好数据透视表的工作表，可以直接单击“数据透视表”工具栏中的“图表向导”按钮，块数创建数据透视图如图3-22所示。 图3-22null 在数据透视图上同样可以像数据透视表一样方便的进行 调整（行字段、列字段、页字段都有相应的下拉箭头）， 调整结果会通过透视图表示出来。并且数据透视图会随着 数据透视表中数据的变化而自动改变，具有自动更新功能。3.2.4发布分析结果3.2.4发布分析结果 数据透视表除了可以在Excel工作表中更实用个，还可以发布成交 忽视的web页，通过IE浏览器对数据透视表尽心个查看和操作以“药 品销售报表”为例将数据透视表发布到网上。其具体操作步骤如下：（1）在“药品销售报表”工作表中，选择菜单栏上“文件/另存为web页” 命令，在“另存为”对话框中，如图3-23所示，选择保存选项区域中的“选择工作表”单选项，并选中“添加交互”复选框，单击“发布”按钮。（1）在“药品销售报表”工作表中，选择菜单栏上“文件/另存为web页” 命令，在“另存为”对话框中，如图3-23所示，选择保存选项区域中的“选择工作表”单选项，并选中“添加交互”复选框，单击“发布”按钮。图3-23（2）在打开的“发布为web页”对话框中个，选择发布内容为“数据透视表”，在“添加加护对象”下拉列表中选择“数据透视表功能”，如图3-24所示，输入、保存文件名后，单击“发布”按钮进行那个网页发布。（2）在打开的“发布为web页”对话框中个，选择发布内容为“数据透视表”，在“添加加护对象”下拉列表中选择“数据透视表功能”，如图3-24所示，输入、保存文件名后，单击“发布”按钮进行那个网页发布。提示：在IE浏览器中用户可以像在Excel工作表中一样对数据透视表进行操作。图3-24