Excel2007培训(3)--高级应用

1、Excel 2007的高级应用Excel 2007 培训第1章 数据统计与分析1.1 数据的排序1.1.1 单条件排序1、单条件排序：就是依据某列的数据规则对数据进行排序操作。可以使用快捷键实现。1.1.1 单条件排序2、默认排序规则：升序排序 / 降序排序：如果排序条件是数值格式，按数值的大小，从小到大进行排序；如果排序条件是文本格式，或者是包含数值的文本，排序规则是：数值格式的数字0-9、文本格式的数字、标点符号、字母a-z，汉字汉语拼音的顺序。如果排序条件是逻辑值，按Fasle、True排序。1.1.1 单条件排序3、对单列数据排序：选中单列数据，点排序按钮1.1.2 多条件排序选中数据

2、区域任意一个单元格；切换到数据菜单选项卡，点击排序按钮；设置排序关键字及升降序；添加排序条件；设置完成后，点击确定。1.1.3 汉字笔画排序默认情况，汉字以拼音的顺序进行排序。1.1.4 自定义排序1.1.5 按颜色和图标排序按单元格中字体和填充的不同颜色进行排序，或者按单元格数值使用的不同图标进行排序。设置颜色设置图标按颜色或图标排序1.1.6 利用函数进行排序有时候，可能希望按某列的数值进行排序，但不希望打乱整个表格的原有顺序，而只希望返回相应数值的顺序序数，这样的排序需求，可以用函数来实现。1.2 数据的筛选1.2.1 自动筛选1、单条件筛选所谓单条件筛选，就是将符合单一条件的数据筛选出

3、来。1.2.1 自动筛选2、多条件筛选所谓多条件筛选，就是将符合多个条件的数据筛选出来。1.2.1 自动筛选3、自定义筛选模糊筛选范围筛选前N名筛选通配符筛选1.2.1 自动筛选4、清除筛选结果方法一：下拉菜单法方法二：快捷按钮法1.2.2 高级筛选高级筛选与自动筛选就本质而言，没有太大的不同，一个最主要的区别是进行高级筛选操作的同同时，可以直接将筛选后的数据负责到其他单元格区域中保存起来。1、单条件高级筛选2、多条件高级筛选1.2.2 高级筛选3、把结果直接复制到其他工作表原表中建立条件区域切换到其他工作表，选择任意单元格切换到数据，单击高级筛选设置筛选条件及复制到的位置完成1.3 数据的分

4、类汇总1.3 数据的分类汇总在Excel中，利用软件的一种内置的统计功能，可以自动对数据进行【分类汇总】。要正确使用【分类汇总】功能，必须对作为汇总条件列的数据进行排序，然后才能依据数据值列中的分类项目，对指定列的数据进行分类汇总。1.3.1 数据分类汇总1、直接分类汇总1.3.1 数据分类汇总2、嵌套分类汇总嵌套分类汇总，就是指多次分类汇总如：先按省份，再按学院分类汇总1.3.1 数据分类汇总3、清除分类汇总1.3.2 数据分类打印和保存1、分类明细数据的打印2、分类汇总数据的保存1.3.3 删除重复的数据行第第2 2章章 EXCEL 2007 EXCEL 2007 图表图表 图表是以图形化

5、方式表示数值数据。它能够清晰直观地表现数据之间的关系，反映事件发展的趋势。 Excel 2007中提供了11种标准图表类型。27第第2 2章章 Excel 2007 Excel 2007 图表图表 Excel 图表综述 图表的创建 更改图表名称和图表位置 更改图表类型 更改图表数据 更改图表布局 设置图表样式 使用趋势线与误差线分析数据 创建图表应用举例第2章 Excel中的图表2.1 Excel 图表综述u 分析数据规律，展示数据间的相互关系及变化分析数据规律，展示数据间的相互关系及变化趋势30u 自动更新功能自动更新功能2.1 Excel 图表综述2.1 Excel 图表综述u 了解图表了

6、解图表数据系列图表类型坐标轴图例网格线绘图区2.1 Excel 图表综述u 柱形图柱形图经常用于表示以行和列排列的数据。对于显示随时间的变化很有用u 折线图折线图与柱形图类似，也可以很好地显示在工作表中以行和列排列的数据。区别在于折线图可以显示一段时间内连续的数据，特别用于显示趋势u 饼图饼图适合于显示个体与整体的比例关系。显示数据系列相对于总量的比例，每个扇区显示其占总体的百分比，所有扇区百分数的总和为100%u 条形图条形图用于比较两个或多个项之间的差异u 面积图面积图面积图图是以阴影或颜色填充折线下方区域的折线图图，适用于要突出部分时间系列时，特别适合适合于显示随时间改变的量2.1 Ex

7、cel 图表综述u XY（散点）图（散点）图适合于表示表格中数值之间的关系，特别适合用于比较两个可能互相关联的变量。虽然散点图可用于大型数据集，但其缺陷是，如果不使用VBA或插件，将很难给点加上标签u 股价图股价图常用于显示股票市场的波动，可使用它显示特定股票的最高价/最低价与收盘价u 曲面图曲面图适合于显示两组数据的最优组合，但难以阅读u 圆环图圆环图与饼图一样，圆环图显示整体中各部分的关系。但与饼图不同的是，它能够绘制超过一列或一行的数据。圆环图不容易阅读2.1 Excel 图表综述u 雷达图雷达图可用于对比表格中多个数据系列的总计，很适合适合用于进行绩效评估，但不太常用。雷达图图可显示4

8、6个变量之间的关系。u 气泡图气泡图与散点图相似，但气泡图不常用且通常不易理解。气泡图是一种特殊的XY散点图，可显示3个变量的关系。气泡图最适合用于较小的数据集2.1 Excel 图表综述u 2007 2007 图表新特性图表新特性外观改善制作步骤简单化 图表工具选项卡制作过程智能化 上下文选项卡 实时预览2.2 图表的创建图表也称为数据图表，是以图形的方式显示图表也称为数据图表，是以图形的方式显示ExcelExcel工作工作表中的数据，可直观体现工作表中各数据间的关系。表中的数据，可直观体现工作表中各数据间的关系。Excel 2007Excel 2007中提供了中提供了1111种标准图表类型

9、，其中最常用种标准图表类型，其中最常用的图表类型包括：柱形图、条形图、饼图、折线图、的图表类型包括：柱形图、条形图、饼图、折线图、XYXY散点图、面积图和圆环图。散点图、面积图和圆环图。常用图常用图表类型表类型图表，是依据工作表中的数据绘制出来的。创建图表必图表，是依据工作表中的数据绘制出来的。创建图表必须先切换至须先切换至“插入插入”选项卡，应用选项卡，应用“图表图表”组中的选项组中的选项创建图表。创建图表。 1 切换至切换至该选项卡该选项卡2 单击此单击此按钮按钮3 单击该单击该图标图标创建的簇状创建的簇状圆柱形图表圆柱形图表2.2 图表的创建创建图表后，认识一下图表中各元素。创建图表后，

10、认识一下图表中各元素。图表区图表区系列图例系列图例水平轴水平轴水平轴标签水平轴标签垂直轴标签垂直轴标签垂直轴垂直轴数据点数据点主要网格线主要网格线默认创建的不含标题的图表默认创建的不含标题的图表2.2 图表的创建 数据点与数据系列：数据点是指图表中用于表示工作表中某数据的图形表现形式；数据系列是指图表中描绘一组数据的相关数据点。 坐标轴与坐标轴标签：坐标轴一般分为垂直坐标轴和水平坐标轴。坐标轴上类似刻度的标记旁显示的即为坐标轴标签。 网格线：是指图表中为了方便查看数据的辅助线条，一般情况下只显示主要水平网格，垂直网格线自动设置为无。 系列图例：是用于显示每个数据系列的标识名称和符号，图例有可能

11、是方块、菱形、小三角形或其他形式。 绘图区：绘图区主要用于承载图表主要信息，如坐标轴、数据点、数据系列、网格线等对象的区域。 图表中的标题：图表中的标题分为两类：图表主标题和坐标轴标题。默认情况下，图表主标题一般位于绘图区顶端中心位置，水平坐标轴标题位于水平坐标轴线下方，垂直坐标轴标题位于垂直坐标轴左侧。2.3 更改图表名称和图表位置2.3.1 更改图表名称如果要更改图表名称，应选择图表，切换至图表工具如果要更改图表名称，应选择图表，切换至图表工具“布局布局”选项卡。选项卡。 2 单击单击1 切换至该切换至该选项卡选项卡3 输入图表名称，输入图表名称，按按Enter键完成图键完成图表名称设置表

12、名称设置2.3.2 更改图表位置如果要更改图表的位置，应选择图表，切换至图表工具如果要更改图表的位置，应选择图表，切换至图表工具“设计设计”选项卡。选项卡。2 单击此单击此按钮按钮1 切换至该切换至该选项卡选项卡打开的打开的“移动移动图表图表”对话框对话框3 选择图表移选择图表移动目标位置动目标位置4 输入名称输入名称5 单击此单击此按钮按钮2.4 更改图表类型创建图表后，在功能区中自动显示图表工具的创建图表后，在功能区中自动显示图表工具的“设设计计”选项卡，应用选项卡，应用“类型类型”组中的组中的“更改图表类型更改图表类型”可将当前图表类型更改为其他图表类型。可将当前图表类型更改为其他图表类

13、型。 “三维柱三维柱形形”图表图表1 切换至切换至该选项卡该选项卡2 单击此单击此按钮按钮3 切换至切换至该选项卡该选项卡4 单击此单击此图标图标5 单击此单击此按钮按钮2.5 更改图表数据默认创建的图表，将当前工作表中的所有数据做为默认创建的图表，将当前工作表中的所有数据做为数据源创建图表，用户可根据需要更改数据。数据源创建图表，用户可根据需要更改数据。 打开的打开的“选择数据选择数据源源”对话框对话框2 单击此单击此按钮按钮3 单击此单击此按钮按钮1 切换至该切换至该选项卡选项卡5 单击此单击此按钮按钮4 重新选择重新选择数据源数据源更改数据源得更改数据源得到的图表效果到的图表效果更改行列

14、数据更改行列数据后的图表效果后的图表效果2 单击此单击此按钮按钮3 单击此单击此按钮按钮1 打开打开“选择数据选择数据源源”对话框对话框如果用户希望可以更改数据行、列间的位置，可如果用户希望可以更改数据行、列间的位置，可切换至图表工具切换至图表工具“设计设计”选项卡，单击选项卡，单击“数据数据”组中的组中的“切换行切换行/ /列列”按钮。按钮。 2.6 更改图表布局默认创建的图表不包含图表标题，用户如果想为图默认创建的图表不包含图表标题，用户如果想为图表添加标题，可使用更改图表布局的方式添加。表添加标题，可使用更改图表布局的方式添加。 更改布局后更改布局后的图表效果的图表效果1 单击此单击此按

15、钮按钮2 单击此单击此图标图标2.7 设置图表样式2.7.1 套用图表样式进行图表类型、数据源和布局设置后，用户还可以进行图表类型、数据源和布局设置后，用户还可以进一步根据需要更改图表格式，如套用图表格式。进一步根据需要更改图表格式，如套用图表格式。 1 单击此单击此按钮按钮2 单击此单击此图标图标套用格式后套用格式后的图表效果的图表效果2.7.2 设置标签文本格式默认创建的图表中除标题文字外，水平轴标签、垂直轴默认创建的图表中除标题文字外，水平轴标签、垂直轴标签、图例等各部分中的文本格式均为标签、图例等各部分中的文本格式均为1010号宋体，用户号宋体，用户可根据需要重新设置标签文本格式。可根

16、据需要重新设置标签文本格式。1 选择垂选择垂直标签直标签2 切换至切换至该选项卡该选项卡3 输入输入7，按按Enter键键更改文本字更改文本字号后的效果号后的效果更改格式后更改格式后的图表效果的图表效果4 切换至切换至该选项卡该选项卡6 单击该单击该图标图标5 单击该单击该图标图标8 单击该单击该 图标图标7 单击该单击该按钮按钮2.7.3 添加标题用户可切换至图表工具的用户可切换至图表工具的“布局布局”选项卡，选择选项卡，选择“标签标签”组中组中“图表标题图表标题”、“坐标轴标题坐标轴标题”为图表添加标题、为图表添加标题、坐标轴标题。坐标轴标题。2 单击此单击此按钮按钮竖排的标竖排的标题效果

17、题效果1 选择要添加选择要添加标题的图表标题的图表4 选择此选择此命令命令3 选择此选择此命令命令2.7.4 设置数据点及数据系列数据点用于表示各数据间的关系，用户可为其设置边框数据点用于表示各数据间的关系，用户可为其设置边框颜色、填充色或立体阴影等效果。颜色、填充色或立体阴影等效果。2 切换至切换至该选项卡该选项卡4 单击该单击该图标图标更改选择某更改选择某数据点填充数据点填充色后的效果色后的效果3 单击该单击该按钮按钮1 选择图表中选择图表中要更改格式的要更改格式的数据系列数据系列从该面板中从该面板中选择要使用选择要使用的样式的样式数据点轮廓数据点轮廓颜色更改后颜色更改后的效果的效果6 单

18、击该单击该按钮按钮5 选择图表中选择图表中要更改格式的要更改格式的数据点数据点“形状轮廓形状轮廓”下拉面板下拉面板7 单击该单击该图标图标2.7.5 设置背景墙及基底用户可以根据需要更改图表背景墙及基底背景颜色。用户可以根据需要更改图表背景墙及基底背景颜色。1 单击该单击该按钮按钮2 选择此选择此命令命令设置背景墙设置背景墙设置的背设置的背景墙效果景墙效果3 单击该单击该按钮按钮4 单击该单击该按钮按钮5 单击该单击该按钮按钮6 单击该单击该按钮按钮设置基底设置基底设置的基设置的基底效果底效果1 单击该单击该按钮按钮2 单击该单击该按钮按钮3 单击该单击该按钮按钮4 单击该单击该按钮按钮5 单

19、击该单击该图标图标6 单击该单击该按钮按钮2.7.6 设置三维效果如果用户创建的是三维图表，可以对其进行立体旋转，如果用户创建的是三维图表，可以对其进行立体旋转，使图表效果更加鲜明。使图表效果更加鲜明。2 切换至切换至该选项卡该选项卡更改旋转值更改旋转值后的效果后的效果3 单击该单击该按钮按钮1 选择更选择更改立体效果改立体效果的图表的图表 4 设置旋设置旋转值转值5 单击该单击该按钮按钮2.8 使用趋势线与误差线分析数据趋势线和误差线是Excel在进行数据分析时的一种重要手段。趋势线能够以图形的方式显示某个系列中数据的变化趋势，而误差线则能以图形的方式表示出数据系列中每个数据标记的可能误差量

20、。2.8 使用趋势线与误差线分析数据2.8.1 添加趋势线与误差线误差线是指在数据图表中的数据与实际值的差值所误差线是指在数据图表中的数据与实际值的差值所绘制的曲线。绘制的曲线。1 选择数选择数据系列据系列创建的标创建的标准误差线准误差线添加误差线添加误差线2 单击此单击此按钮按钮3 选择此选择此命令命令趋势线是用来预测数据的走势，以便用户进行数据趋势线是用来预测数据的走势，以便用户进行数据分析，也被称为回归分析。分析，也被称为回归分析。1 选择数选择数据系列据系列创建的线创建的线性趋势线性趋势线添加趋势线添加趋势线2 单击此单击此按钮按钮3 选择此选择此命令命令2.8.2 设置误差线与趋势线

21、用户可根据需要设置误差线显示方向、误差量、线条颜用户可根据需要设置误差线显示方向、误差量、线条颜色、线型、阴影等效果。色、线型、阴影等效果。4 切换至切换至该选项卡该选项卡1. 设置误差线2 单击此单击此按钮按钮3 选择此选择此命令命令1 选择选择要进行要进行设置的设置的误差线误差线5 选择该选择该选项选项6 选择该选择该选项选项7 切换至切换至该选项卡该选项卡11 切换至切换至该选项卡该选项卡8 选择该选择该选项选项9 单击此单击此按钮按钮10 单击该单击该图标图标12 设置设置线条宽度线条宽度13 单击该单击该按钮按钮更改样式更改样式后的标准后的标准误差线误差线用户也可为趋势线设置线条颜色

22、、线型、阴影等效果，用户也可为趋势线设置线条颜色、线型、阴影等效果，除此之外还设置趋势预测除此之外还设置趋势预测/ /回归分析类型、超势名称、回归分析类型、超势名称、趋势预测等。趋势预测等。2. 设置趋势线2 单击此单击此按钮按钮3 选择此选择此命令命令1 选择选择要进行要进行设置的设置的误差线误差线4 切换至切换至该选项卡该选项卡5 选择该选择该选项选项6 自定义趋自定义趋势线名称势线名称7 设置趋设置趋势周期势周期8 切换至切换至该选项卡该选项卡9 设置线设置线条粗细条粗细10 单击此单击此按钮按钮11 选择该选择该选项选项12 单击该单击该按钮按钮设置设置1周期周期指数趋势线指数趋势线后

23、的效果后的效果2.9 应用举例u 组合图表的制作u 动态图表2.9.1 组合图表的制作 在日常工作中，创建比较不同类型数据的图表是很有用的。例如，你在一个制造公司工作，想要分析前几个月的销售单价和每个月的总销售收入，并且希望知道哪里出现了问题，比如高的销售单价而低的交易额。当然此问题可以制作两个不同的图表，一个绘制每月的销售单价，一个绘制每月的交易额。但是，将要单独分析两个图表。因此，我们更愿意创建像下面所示的图表。$0.00 $1,000.00 $2,000.00 $3,000.00 $4,000.00 $5,000.00 $6,000.00 $7,000.00 $8,000.00 0204

24、06080100120一月二月三月四月五月交易总额销售单价销售单价交易总额($)2.9.1 组合图表的制作步骤步骤1: 在工作表中输入下面的数据月份月份销售单价销售单价交易总额交易总额($)($)一月50900二月1003000三月301200四月1047000五月875050六月15075002.9.1 组合图表的制作步骤步骤2: 选择数据，在功能区“插入”选项卡中插入柱形图2.9.1 组合图表的制作现在，创建了一个带有两个系列的柱状图，均绘制在相同的坐标轴上，但与我们刚才的意图不相符，因为在交易额和销售单价之间的比例不同，我们甚至看不到销售单价系列。2.9.1 组合图表的制作下一步将“交易

25、总额”系列的图表类型改为折线步骤步骤3:选择要改变为不同类型的系列，本例中为“交易总额”系列。2.9.1 组合图表的制作步骤步骤4: 改变所选择系列的图表类型2.9.1 组合图表的制作添加第二个坐标轴上面的图表仍难以分析，因为交易总额的比例是远大于销售单价的比例。结果，难以读取销售单价，并且柱状图也不可分辨。所以，交易总额应该移到第二个坐标轴中，允许坐标轴有不同的比例。步骤步骤5: 选择想要放置在第二个坐标轴上的数据系列。在本例中，是“交易总额”系列。步骤步骤6: 在“格式”或“布局”选项卡中，在左侧“当前所选内容”中，确保下拉框中为“系列交易总额”，然后单击“设置所选内容格式”。步骤步骤7:

26、 在弹出的“设置数据系列格式”对话框中，打开“系列选项”，单击“次坐标轴”，然后单击“关闭”。2.9.1 组合图表的制作下面介绍的内容将对已完成了组合图表进行格式设置。步骤步骤8: 将图例移到底部。单击功能区中的“图表布局”选项卡，然后单击“图例”，选择“在底部显示图例”。步骤步骤9: 改变第二个坐标轴标签来显示货币符号。在坐标轴标签上单击右键，选择“设置坐标轴格式”。步骤步骤10: 添加坐标轴标题。在功能区“布局”选项卡中，选择“坐标轴标题”。在下拉菜单中，选择“主要纵坐标轴标题”，然后选择“旋转过的标题”。输入标题。对“次要纵坐标轴”进行相同的操作。步骤步骤11: 添加图表标题。在功能区“

27、布局”选项卡中，单击“图表标题”，输入标题名。2.9.2 动态图表的制作所谓动态图表就是要实现数据与图表动态交互。所谓动态图表就是要实现数据与图表动态交互。在实际中很有实际意义，就是图表一直能够随着数在实际中很有实际意义，就是图表一直能够随着数据的变化而变化。据的变化而变化。1 1、利用条件筛选制作动态图、利用条件筛选制作动态图表表1 利用条件筛选制作动态图表步骤步骤1 1：首先将工作表变成筛选表，再绘图。：首先将工作表变成筛选表，再绘图。 1 利用条件筛选制作动态图表步骤步骤2 2：进行按条件筛选。：进行按条件筛选。 1 利用条件筛选制作动态图表步骤步骤2 2：随意按条件筛选。：随意按条件筛

28、选。 第3章 创建数据透视表3.1 什么是数据透视表数据透视表是用来从Excel数据列表、关系数据库文件或OLAP多维数据集等数据源的特定字段中总结信息的分析工具。它是一种交互式报表，可以快速分类汇总、比较大量的数据，可以随时选择页、行和列中的不同元素，以快速查看数据源的不同统计结果，同时还可以随意显示和打印出用户所感兴趣区域的明细数据。3.1 什么是数据透视表数据透视表有机的综合了数据排序、筛选、分类汇总等数据分析的方法的优点，可以方便地调整分类汇总的方式，灵活地以多种不同方式展示数据的特征。一张数据透视表仅靠鼠标移动字段位置，即可以变换出各种类型的报表。应用数据透视表也是解决Excel函数公司速度瓶颈的重要手段之一。3.2 数据透视表的数据源1.Excel数据列表：标题行不能有空白单元格或者合并的单元格；2.外部数据源3.多