Excel 2013數據透視表及數據透視圖在農戶施肥大數據分析中的應用*

張懷志，唐繼偉，袁 碩，黃紹文

（中國農業科學院農業資源與農業區劃研究所，北京 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

*資助項目：“十三五”國家重點研發計劃課題（2016YFD0201001，2016YFD0801006）；中國農業科學院科技創新工程協同創新任務（CAASXTCX2016003）

張懷志（1968-），博士，副研究員，研究方向：施肥與環境，農業信息技術

?通信作者：黃紹文（1964-），博士，博導，研究員，研究方向：蔬菜營養與高效施肥