我的书签
添加书签
移除书签18.9 数据透视图的应用技巧
虽然Excel 2010的数据透视图存在一些限制，但用户可以使用一些特殊技巧来突破这些限制，制作出满足需要的数据透视图。
18.9.1 处理多余的图表系列
在图18-66中，左侧是根据销售数据创建的数据透视表，右侧是根据数据透视表创建的数据透视图。在数据透视表中“数量”、“销价”和“金额”3个字段的数值差异很大，在数据透视图中也反映出图形系列之间的反差很大，“销价”系列甚至因数值太小而无法显示出来，如图18-66所示。
图18-66 初步创建的数据透视表和透视图
示例18.11 处理多余的图表系列
为了更好地反映数量和价格之间的关系，需要将“金额”系列从数据透视图中删除，但这样一来数据透视表就不能完整地反映量、价和金额的数据关系。用户可以通过采用隐藏的方法来处理多余的图表系列，请参照以下步骤。
步 骤1
将“销价”系列设置在“次坐标轴”上，并将图表类型改为“带数据标记的折线图”，效果如图18-67所示。
图18-67 设置“销价”图形系列
步 骤2
选中“金额”系列，将【系列重叠】设置为“100%”，并将系列的【填充】设置为“无填充”，【边框颜色】设置为“无线条”，设置后的效果如图18-68所示。
图18-68 隐藏“金额”系列
选中“纵坐标”，在【设置坐标轴格式】对话框中，将【坐标轴选项】中的【最小值】设置为固定“500000”，将【最大值】设置为“3500000”，设置后的效果如图18-69所示。
图18-69 修改“垂直坐标轴”的最小值和最大值
步 骤4
在“图例”中删除“金额”系列的图例，最终得到的量价图形结果，如图18-70所示。
图18-70 “量价”透视图结果图
本例是在不改变透视表布局和不删除数据透视图系列的情况下，采用将多余系列设置为透明的方法，处理掉数据透视图中多余的图表系列。
18.9.2 定义名称摆脱透视图的限制
如果要彻底摆脱透视图的限制，而又能发挥透视表快速灵活的特点，用户可以通过定义名称引用透视表数据序列的方法来实现动态图表的创建。
示例18.12 南欣石油公司销售分析
图18-71所示是南欣石油公司SAP系统生成的2011年1月份销售明细数据表（数据为虚拟数据），要求按部门和品种进行量价分析生成动态图表，并将最高价和最低价圈示出来，请参照以下步骤。
图18-71 南欣石油公司销售数据表
步 骤1
根据销售数据表，在新工作表中创建数据透视表，并将新工作表名改为“透视图”，如图18-72所示。
步 骤2
对数据透视表行标签字段项进行手动分组，将油品分为柴油和汽油两大类，如图18-73所示（有关数据透视表项目分组的方法，请参阅第7章）。
图18-72 创建数据透视表
图18-73 将油品项目手动分组
步 骤3
调整数据透视表布局，修改字段名称，如图18-74所示。
图18-74 调整透视表布局
添加计算字段，将原以“公斤”表示的“数量”改为以“吨”表示的“销量”，“销量”=数量/1000”，另将每公斤的单位的“单价”转为每吨的单位的“销价”，并保留2位小数，“销价=ROUND(含税金额/销量，2)”，如图18-75所示。
图18-75 添加计算字段
步 骤5
对数据透视表进行必要的美化，再选中透视表中C8:C30单元格区域，利用【项目选取规则】设置条件格式，标注出“销价”中的最大值和最小值，如图18-76所示。
图18-76 标注最高价、最低价
步 骤6
在当前窗口的【公式】选项卡中单击【名称管理器】按钮，打开【名称管理器】对话框，分别定义名称date、num、DJ、L_price、S_price，分别动态引用“日期”、“销量”、“销价”、“最高销价”、“最低销价”等相应单元格区域，公式如下。
日期：date=OFFSET（透视图！$A$5，1，，COUNT（透视图!$A$5:$A$100））
销量：num=OFFSET（透视图！$A$5,1,1,COUNT（透视图！$A$5:$A$100））
销价：DJ=OFFSET（透视图！$A$5,1,2,COUNT（透视图！$A$5:$A$100））
最高销价：L_price=IF(MAX(DJ)=DJ，DJ，NA())
最低销价：S_price=IF(MIN(DJ)=DJ，DJ，NA())
步 骤7
单击数据透视表以外的任意单元格，在【插入】选项卡的【图表】命令组中单击【柱形图】按钮，在弹出的快捷菜单中选择【簇状柱形图】，插入一张空白图表。
步 骤8
在空白图表中单击鼠标右键，在弹出的快捷菜单中选择【选择数据】命令，打开【选择数据源】对话框，按图18-77所示方法添加“销量”图表系列。
图18-77 添加“销量”图形系列
步 骤9
按步骤8的方法添加“销价”图表系列，如图18-78所示。
图18-78 添加“销价”图形系列
步 骤10
选中“销价”图表系列，将图表类型改为“带数据标志的折线图”，并将图表系列更改为“绘制在次坐标”上，修改后的效果如图18-79所示。
图18-79 修改“销价”图形系列后的效果
选中“横坐标轴”，单击鼠标右键，在打开的快捷菜单中单击【设置坐标轴格式】命令，打开【设置坐标轴格式】对话框，将【坐标轴类型】设置为【文本坐标轴】，如图18-80所示。
图18-80 修改“水平（类别）坐标轴”类型
步 骤12
对数据透视图做进一步美化，如图18-81所示。
图18-81 美化图形后的效果
步 骤13
向数据透视图中添加“最高价”和“最低价”图形系列，如图18-82所示。
图18-82 添加“最高价”、“最低价”图形系列
步 骤14
选中次要纵坐标轴，单击鼠标右键，在弹出的快捷菜单中选择【设置坐标轴格式】命令，打开【设置坐标轴格式】对话框，将【坐标轴选项】的【最小值】设置为【固定】的“4000”，如图18-83所示。
图18-83 设置“次要纵坐标轴”的最小值
步 骤15
修改“最高价”系列，将【数据标记类型】选为【内置】的“圆形”，【大小】设为“18”，【数据标记填充】设为“无填充”，【线条颜色】设为“无线条”，【标记线颜色】设为“实线”、【颜色】设为“红色”，【标记线样式】的【宽度】设为“1.75”，形成空心的红色大圆环。
步 骤16
修改“最低价”系列，将【数据标记类型】选为【内置】的“圆形”，【大小】设为“14”，【数据标记填充】设为“无填充”，【线条颜色】设为“无线条”，【标记线颜色】设为“实线”、【颜色】设为“红色”，【标记线样式】的【宽度】设为“1.75”，形成空心的红色小圆环。
设置后的效果如图18-84所示。
图18-84 添加“最高价”、“最低价”后的效果
步 骤17
选中“最高价”图表系列，在【图表工具】的【布局】命令组中单击【数据标签】，在弹出的下拉菜单中选择【其他数据标签选项】命令，打开【设置数据标签格式】对话框，如图18-85所示。
步 骤18
在【设置数据标签格式】对话框的【标签选项】中勾选【系列名称】和【值】，在【标签位置】中选择【靠上】，在【分隔符】下拉列表中选择【分行符】，如图18-86所示。
图18-85 打开【设置数据标签格式】对话框
图18-86 设置系列数据标签格式
此外，在【设置数据标签格式】对话框中设置【填充】一【纯色填充】一【白色】，将标签底色设为“白色”；再设置【边框颜色】一【实线】一【颜色】一【黑色】，将标签边框设为“黑色”。
步 骤19
重复操作步骤17，设置“最低价”的系列数据标签格式，并删除图例中的“最高价”、“最低价”图例，如图18-87所示。
图18-87 设置数据标签后的效果
为主要纵坐标轴添加标题“吨”，并为次要纵坐标添加标题“元/吨”，删除图例中的“最高价”和“最低价”，最后添加图表标题，对图表位置进行进一步调整，最后的完成的效果如图18-88所示。
图18-88 最终完成的分析图
用户可以对不同的部门、油品大类和具体的品种进行筛选，如部门选为“南欣本部”、油品大类选为“柴油类”、油品名称选择“-10号柴油”，筛选后的数据透视表和数据透视图如图18-89所示。
图18-89 重新筛选后的图表变化
