Excel办公室文员使用小技巧PPT.ppt

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