Excel高级应用实例教程

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