Excel 2013数据透视表及数据透视图在农户施肥大数据分析中的应用*

2017-06-15张怀志唐继伟黄绍文

中国农业信息 2017年6期

关键词：字段数据源报表

张怀志，唐继伟，袁硕，黄绍文

（中国农业科学院农业资源与农业区划研究所，北京 100081）

Excel 2013数据透视表及数据透视图在农户施肥大数据分析中的应用*

张怀志，唐继伟，袁硕，黄绍文⋆

（中国农业科学院农业资源与农业区划研究所，北京 100081）

农户调查是掌握农户肥料使用情况的重要手段，是制订化肥减施对策的科学基础，而对农户调查数据的统计分析是研究关键。文章在简介Excel 2013数据透视表的数据源要求基础上，介绍了使用Excel 2013数据透视表、数据透视图对农户调查海量施肥数据进行快捷的统计分析过程。

Excel2013 数据透视表数据透视图施肥调查大数据分析

数据分析是指用适当的统计分析方法对收集来的大量数据进行分析，提取有用信息和形成结论而对数据加以详细研究和概括总结的过程；实践中，数据分析可帮助人们作出判断，以便采取适当行动。Excel因为提供了大量的统计函数，成为众多研究者选择的数据分析工具[1-3]。但是对庞大的数据库进行多条件统计、将得到的统计数据用图形的方式表现出来等特殊要求的时候，Excel统计函数就显得力不从心，这种情况下就适于应用Excel数据透视表和数据透视图，Excel数据透视表的数据分析功能在高等学校学生成绩管理、商品销售管理、问卷调查等工作中已得到应用[4-7]。随着互联网技术飞速发展，大数据时代的来临，需要处理的数据量越来越大，大数据的快速分析工作显得尤为重要。该文以农户施肥调查数据为例，介绍Excel 2013数据透视表及数据透视图在大数据处理分析中的应用。

1 数据透视表和数据透视图

数据透视表是用来从Excel数据列表、关系数据库文件或OLAP多维数据集等数据源的特定字段中总结信息的强大的数据处理分析工具，它是一种交互式的报表，可以快速分类汇总、比较大量数据，并可以根据用户要求，快速变化统计分析维度来查看统计结果，同时还可以随意显示和打印出用户所感兴趣区域的明细数据。数据透视图则是数据透视表的图形化。

若要创建数据透视表或者数据透视图，则要求数据源的数据必须规范，否则需对数据源做技术处理，数据源的数据规范基本要求是：

（1）数据源不能包含多层表头，有且仅有一行标题；

（2）数据源列字段名称不能为空，也不能重复；

（3）一个字段只能包含一个信息；

（4）数据记录中不能有合并单元格，不能包含空白的数据行和数据列；

（5）数据源中的数据格式必须同一规范，工作簿中不能包含非法字符；

（6）能在一个工作表中放置的数据源不要拆分到多个工作表中；

（7）能在一个工作簿中放置的数据源不要拆分到多个工作簿中。

2 数据透视表及数据透视图在农户施肥大数据分析中的应用

科学施肥是实现作物高产稳产的关键，同时也有助于从源头上合理地投入氮磷等养分，有效地控制农业面源污染。判断农户施肥是否科学合理，制订适宜的调控措施，通常需要分析氮磷钾养分用量是否合理、有机肥源和无机肥源中的氮磷钾养分用量，基肥中的有机肥源与化肥源的氮磷钾的比例是否合理，基肥中氮磷钾养分用量占其各自总用量比例是否合理等等，由此需要进行大量而细致的数据分析工作。

图1 数据透视表的规范数据源表——农户施肥调查数据示例

2.1 整理农户调查数据

为应用数据透视表和数据透视图对农户施肥情况进行分析，应该按照前文所述的数据规范要求对施肥调查结果进行规范化处理。农户施肥调查通常涉及作物产量、经济效益、施用的肥料品种及其N、P2O5、K2O含量、肥料价格、是基肥还是追肥等。为此，首先应该将第一手获得的农户施肥调查数据录入到Excel 2013中，录入时候，要求每一个农户的每一种作物茬口作为一个记录行，分类分别记录所用肥料品种及其N、P2O5、K2O含量等所有调查信息（图1 上）。其次在完成调查数据录入的基础上，对农户调查的施肥数据进行计算处理以便于统计分析，计算结果即是数据透视表和数据透视图的数据源（图1 下），计算内容主要是根据分析目的确定的N、P2O5、K2O养分总用量，基肥N、P2O5、K2O养分用量，追肥N、P2O5、K2O养分用量，基肥中有机肥源N、P2O5、K2O养分用量、肥料成本等等。图1中的空列表示中间有些字段因显示界面有限而省略了，不是真实存在的空列。

2.2 应用数据透视表进行农户施肥调查大数据分析

完成农户施肥调查数据的计算处理，并确定符合数据透视表的数据源要求后，就可以应用数据透视表了。打开Excel 2013，点击“插入”菜单，就可以在左上角见到“数据透视表”，单击“数据透视表”即可见到“创建数据透视表”界面（图2）。

通常情况下保持图2对话框内默认的设置不变，单击 “确定”按钮后，即可在新工作表中创建一张空的数据透视表，如图2所示。可见数据透视表分为2个区域，左边为数据透视表的报表生成区域，会随着右侧选择不同字段而更新，右边为数据透视表的字段列表。

图2 创建数据透视

数据透视表字段列表区域表现形式可以调整，但习惯采用上下两个部分格式，其中上部给出数据源所有的列字段名称以供选择，下部再细分为4个区域：筛选器区域，标志区域中的字段将作为数据透视表的报表筛选字段；行区域，此标志区域中的字段将作为数据透视表的行标签显示；列区域，此标志区域中的字段将作为数据透视表的列标签显示；∑数值区域，此标志区域中的字段将作为数据透视表显示统计的数据项。当某一个字段拖入∑数值区域后，单击左键后，在弹出的窗体中再点击“值字段设置”，即可以选择统计函数，数据透视表提供有求和、计数、平均值、最大值、最小值、乘积、数值计数、标准偏差、总体标准偏差、方差、总体方差等11个统计函数供选择。应该指出的是，某个字段拖入“∑数值” 区域仅能获得一项统计值，故若拟获得某个字段的多项统计值，就应该相应次数的拖入该字段，如获得有机肥N用量的平均值、标准误差、参与统计个数（计数项）等3项，则“有机肥N用量（kg/667 m2）”字段应该拖入3次。若认为拖入某个区域的字段不符合目标要求，则将鼠标放在该字段上，单击左键，在弹出的界面中可删除、移动该字段，报表区域也同步发生变化。

图3中相应区域已根据分析目的，完成了农户施肥调查数据字段的设置。首先将基地名称拖入报表筛选区域，以便于分基地掌握作物施肥情况；将蔬菜名称拖入列标签，以便于分作物进行施肥统计；将栽培方式拖入行区域，以便于分日光温室、大棚进行作物施肥统计；将∑数值拖入行标签区域，为的是分行展示不同区域不同作物不同栽培方式下的施肥分类统计结果；该文“∑数值”区域仅列出了统计化肥源N、P2O5、K2O用量和有机肥源N、P2O5、K2O用量的平均值。

图3 数据透视表基本结构与示例

在将字段拖入报表筛选、列标签、行标签、∑数值区域以及完成统计项选择的同时，Excel 2013自动完成相应的工作，其结果显示在报表区域，表现出用数据透视表比用Excel 2013统计函数操作更方便，计算速度也更快的优势。图3中报表区域显示了不同区域不同作物不同栽培模式下的化肥源N、P2O5、K2O用量和有机肥源N、P2O5、K2O用量的平均值。不仅如此，在报表的下部还显示出不同蔬菜的化肥源N、P2O5、K2O用量和有机肥源N、P2O5、K2O用量的平均值（不分区域不分栽培模式）；在报表的右侧则显示出不同区域不同栽培模式下的化肥源N、P2O5、K2O用量和有机肥源N、P2O5、K2O用量的平均值（不分作物），这为评价某一区域某一作物的施肥情况奠定基础。

图3中仅设置了化肥源N、P2O5、K2O用量和有机肥源N、P2O5、K2O用量的平均值，还有很多需要进行统计分析的内容没有拖入，当然，可以继续拖入字段，但也可以重复上述步骤构建新的数据透视表，完成对所关心的数据项进行统计，如基肥化肥N、P2O5、K2O用量分别占总化肥源N、P2O5、K2O用量的比例，基肥N、P2O5、K2O用量分别占N、P2O5、K2O总用量的比例等等。直到获取所需要的所有统计信息。

在农户施肥海量数据统计分析时候，可能有些计算项比如有机肥源N、P2O5、K2O用量与无机肥源N、 P2O5、K2O用量比例等在开始分析时候没有考虑到，或者需要进行更细的区域划分等，那么只要按照数据透视表数据源规则进行计算处理、添加相应的数据列等，即可完成相应的统计分析。

目前已完成农户施肥数据分析的数据透视表构建工作，但随着工作的不断开展，农户调查数据还会不断增加，但数据透视表并不会同步更新，此时原有的数据透视表已不能如实反映原始数据了，为此必须对数据透视表进行手动更新。Excel 2013手动更新有2种方法，（1）选中数据透视表中的任一单元格，点击鼠标右键，在弹出的快捷菜单中选择刷新命令（图4左）；（2）是选中数据透视表中的任一单元格，在“数据透视表工具”的“选项”选项卡中单击“刷新”按钮就可以完成刷新工作（图4 右）。

2.3 应用数据透视图以图形形式展示数据透视表结果

数据透视表提供了灵活、快捷的数据统计工具，同时Excel 2013 也提供了以图形形式直观、动态地展现数据透视表数据的工具，这就是数据透视图。可通过3种方法创建数据透视图。特别提出的是，如果将数据透视表另存为一个表，则可以按照Excel 普通图进行制作、美化，这已不在该文研究之列。

图4 数据透视表手工刷新方法

图5 河北永清不同肥源养分用量

图6 数据透视图基本结构与示例

图7 根据数据透视表创建向导创建数据透视图

（1）利用创建好的数据透视表创建数据透视图。选择数据透视表中任意单元格，在 “数据透视表工具”的“分析” 选项卡中，单击“数据透视图”弹出“插入图表”对话框，在“插入图表”对话框中根据需要选择图表类型。图5是利用数据透视表的数据在非当前工作表上，选择“柱形图-簇状柱形图”展现的一个河北省永清县设施蔬菜施肥调查统计结果并进行了美化。若在当前工作表上展现数据透视图，可参见图6。

（2）由数据源表直接创建数据透视图。当没有创建数据透视表时，也可以根据数据源表直接生成数据透视图。打开Excel 2013，单击数据源表中任一单元格，在“插入”选项卡中，单击“数据透视图-数据透视图”按钮，打开数据透视图对话框（图1）。在确定所用数据源及数据透视图存放位置后，单击“确定”按钮，进入数据透视图设置状态，可见左侧为数据透视表区域，中间是数据透视图区域，右侧是数据透视图字段对话框；拖动字段到相应区域，即可创建数据透视表，同时生成和数据透视表相对应的默认类型数据透视图（图6）。若没有字段拖入“报表区域”，即该区域为空，则生成一张图，但若把基地名称、蔬菜名称、栽培方式（温室/大棚）中的任一字段拖入“报表筛选”区域，则可以或分基地、或分蔬菜、或分栽培方式（温室/大棚）进行作图，图6中间的数据透视图是将基地名称字段拖入“报表区域”，并选择永清基地而作的，且经过一定美化处理。美化前的数据透视图带有报表区域、图例区域、轴类型区域和∑值区域的所有按钮，这些可通过点击“分析”选项卡中的“字段按钮”来显示或者隐藏。

（3）是根据数据透视表创建向导创建数据透视图。在规范的数据源表中单击任一单元格，依次按下＜alt＞、＜D＞、＜P＞键，则弹出“数据透视表和数据透视图向导-步骤1（共3步）对话框”（图7），在“所需创建的报表类型”中选中“数据透视图（及数据透视表）”后，依次点击“下一步”，直至第三步的“完成”按钮，则可生成图6界面，根据需要完成相关操作及美化，即可生成预期的图件。

Excel 2013中数据透视图与普通图完全融合，但鉴于数据透视图是在数据透视表基础之上创建的，对数据透视表高度依存，因此其布局受到数据透视表的制约，当数据透视表布局改变，数据透视图的布局也将发生变化；虽然可以对数据透视图进行美化，也有可能达不到Excel普通图的效果，如不能实现有机肥N用量柱和化肥N用量柱叠加。应该指出的是数据透视图与Excel普通图比较，具有以下限制：（1）无法创建图表类型为XY（散点）图、气泡图和股价图的数据透视图；（2）无法调整图形系列的位置顺序。

3 结论

相对于Excel统计函数，数据透视表综合了求和、计数、平均值、最大值、最小值、乘积、数值计数、标准偏差、总体标准偏差、方差、总体方差等11个统计函数，但其在对庞大的数据库进行多条件统计分析时的速度明显快于使用Excel函数公式统计。因此在海量数据分析时候数据透视表具有强大优势，如对某地的多年逐日气象观测资料进行统计。

若统计指标较多，建议采用同一数据源创建多个数据透视表，利用数据源和数据透视表的耦合性，修订数据源后则可对所有数据透视表进行同步更新。

虽然数据透视图和普通图是融合的，能展示数据分析结果，但由于数据透视图是基于数据透视表创建的，故尽管可以编辑美化数据透视图，也有可能也达不到预期的效果。

[1] 李克农，王翰霖，郭爱芬，等．2011～2014年银川市农户施肥调查与评价．现代农业科技，2015，15：222～223

[2] 赵护兵，王朝晖，高亚军，等.西北典型区域旱地冬小麦农户施肥调查分析[J].植物营养与肥料学报，2013，19（4）：840～848

[3] 黎青慧．陕西省黄瓜西红柿施肥调查．西北农林科技大学学报（自然科学版），2003，31（增刊）：73～78

[4] Excel Home编著．Excel2013数据透视表应用大全．北京：北京大学出版社．2016

[5] 张丽．Excel2007数据透视表和数据透视图的使用．科技信息，2009，（5）：442～444

[6] 孙凤杰．使用Excel数据透视表进行问卷统计分析，无线互联科技，2013，（3）：67

[7] 吴丹．Excel2007数据透视表在高校学生成绩管理中的应用研究．物联网技术，2013，（8）：73～74，78