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

（中國農(nóng)業(yè)科學院農(nóng)業(yè)資源與農(nóng)業(yè)區(qū)劃研究所，北京 100081）

Excel 2013數(shù)據(jù)透視表及數(shù)據(jù)透視圖在農(nóng)戶施肥大數(shù)據(jù)分析中的應(yīng)用*

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

（中國農(nóng)業(yè)科學院農(nóng)業(yè)資源與農(nóng)業(yè)區(qū)劃研究所，北京 100081）

農(nóng)戶調(diào)查是掌握農(nóng)戶肥料使用情況的重要手段，是制訂化肥減施對策的科學基礎(chǔ)，而對農(nóng)戶調(diào)查數(shù)據(jù)的統(tǒng)計分析是研究關(guān)鍵。文章在簡介Excel 2013數(shù)據(jù)透視表的數(shù)據(jù)源要求基礎(chǔ)上，介紹了使用Excel 2013數(shù)據(jù)透視表、數(shù)據(jù)透視圖對農(nóng)戶調(diào)查海量施肥數(shù)據(jù)進行快捷的統(tǒng)計分析過程。

Excel2013 數(shù)據(jù)透視表 數(shù)據(jù)透視圖 施肥調(diào)查 大數(shù)據(jù)分析

數(shù)據(jù)分析是指用適當?shù)慕y(tǒng)計分析方法對收集來的大量數(shù)據(jù)進行分析，提取有用信息和形成結(jié)論而對數(shù)據(jù)加以詳細研究和概括總結(jié)的過程；實踐中，數(shù)據(jù)分析可幫助人們作出判斷，以便采取適當行動。Excel因為提供了大量的統(tǒng)計函數(shù)，成為眾多研究者選擇的數(shù)據(jù)分析工具[1-3]。但是對龐大的數(shù)據(jù)庫進行多條件統(tǒng)計、將得到的統(tǒng)計數(shù)據(jù)用圖形的方式表現(xiàn)出來等特殊要求的時候，Excel統(tǒng)計函數(shù)就顯得力不從心，這種情況下就適于應(yīng)用Excel數(shù)據(jù)透視表和數(shù)據(jù)透視圖，Excel數(shù)據(jù)透視表的數(shù)據(jù)分析功能在高等學校學生成績管理、商品銷售管理、問卷調(diào)查等工作中已得到應(yīng)用[4-7]。隨著互聯(lián)網(wǎng)技術(shù)飛速發(fā)展，大數(shù)據(jù)時代的來臨，需要處理的數(shù)據(jù)量越來越大，大數(shù)據(jù)的快速分析工作顯得尤為重要。該文以農(nóng)戶施肥調(diào)查數(shù)據(jù)為例，介紹Excel 2013數(shù)據(jù)透視表及數(shù)據(jù)透視圖在大數(shù)據(jù)處理分析中的應(yīng)用。

1 數(shù)據(jù)透視表和數(shù)據(jù)透視圖

數(shù)據(jù)透視表是用來從Excel數(shù)據(jù)列表、關(guān)系數(shù)據(jù)庫文件或OLAP多維數(shù)據(jù)集等數(shù)據(jù)源的特定字段中總結(jié)信息的強大的數(shù)據(jù)處理分析工具，它是一種交互式的報表，可以快速分類匯總、比較大量數(shù)據(jù)，并可以根據(jù)用戶要求，快速變化統(tǒng)計分析維度來查看統(tǒng)計結(jié)果，同時還可以隨意顯示和打印出用戶所感興趣區(qū)域的明細數(shù)據(jù)。數(shù)據(jù)透視圖則是數(shù)據(jù)透視表的圖形化。

若要創(chuàng)建數(shù)據(jù)透視表或者數(shù)據(jù)透視圖，則要求數(shù)據(jù)源的數(shù)據(jù)必須規(guī)范，否則需對數(shù)據(jù)源做技術(shù)處理，數(shù)據(jù)源的數(shù)據(jù)規(guī)范基本要求是：

（1）數(shù)據(jù)源不能包含多層表頭，有且僅有一行標題；

（2）數(shù)據(jù)源列字段名稱不能為空，也不能重復(fù)；

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

（4）數(shù)據(jù)記錄中不能有合并單元格，不能包含空白的數(shù)據(jù)行和數(shù)據(jù)列；

（5）數(shù)據(jù)源中的數(shù)據(jù)格式必須同一規(guī)范，工作簿中不能包含非法字符；

（6）能在一個工作表中放置的數(shù)據(jù)源不要拆分到多個工作表中；

（7）能在一個工作簿中放置的數(shù)據(jù)源不要拆分到多個工作簿中。

2 數(shù)據(jù)透視表及數(shù)據(jù)透視圖在農(nóng)戶施肥大數(shù)據(jù)分析中的應(yīng)用

科學施肥是實現(xiàn)作物高產(chǎn)穩(wěn)產(chǎn)的關(guān)鍵，同時也有助于從源頭上合理地投入氮磷等養(yǎng)分，有效地控制農(nóng)業(yè)面源污染。判斷農(nóng)戶施肥是否科學合理，制訂適宜的調(diào)控措施，通常需要分析氮磷鉀養(yǎng)分用量是否合理、有機肥源和無機肥源中的氮磷鉀養(yǎng)分用量，基肥中的有機肥源與化肥源的氮磷鉀的比例是否合理，基肥中氮磷鉀養(yǎng)分用量占其各自總用量比例是否合理等等，由此需要進行大量而細致的數(shù)據(jù)分析工作。

圖1 數(shù)據(jù)透視表的規(guī)范數(shù)據(jù)源表——農(nóng)戶施肥調(diào)查數(shù)據(jù)示例

2.1 整理農(nóng)戶調(diào)查數(shù)據(jù)

為應(yīng)用數(shù)據(jù)透視表和數(shù)據(jù)透視圖對農(nóng)戶施肥情況進行分析，應(yīng)該按照前文所述的數(shù)據(jù)規(guī)范要求對施肥調(diào)查結(jié)果進行規(guī)范化處理。農(nóng)戶施肥調(diào)查通常涉及作物產(chǎn)量、經(jīng)濟效益、施用的肥料品種及其N、P2O5、K2O含量、肥料價格、是基肥還是追肥等。為此，首先應(yīng)該將第一手獲得的農(nóng)戶施肥調(diào)查數(shù)據(jù)錄入到Excel 2013中，錄入時候，要求每一個農(nóng)戶的每一種作物茬口作為一個記錄行，分類分別記錄所用肥料品種及其N、P2O5、K2O含量等所有調(diào)查信息（圖1 上）。其次在完成調(diào)查數(shù)據(jù)錄入的基礎(chǔ)上，對農(nóng)戶調(diào)查的施肥數(shù)據(jù)進行計算處理以便于統(tǒng)計分析，計算結(jié)果即是數(shù)據(jù)透視表和數(shù)據(jù)透視圖的數(shù)據(jù)源（圖1 下），計算內(nèi)容主要是根據(jù)分析目的確定的N、P2O5、K2O養(yǎng)分總用量，基肥N、P2O5、K2O養(yǎng)分用量，追肥N、P2O5、K2O養(yǎng)分用量，基肥中有機肥源N、P2O5、K2O養(yǎng)分用量、肥料成本等等。圖1中的空列表示中間有些字段因顯示界面有限而省略了，不是真實存在的空列。

2.2 應(yīng)用數(shù)據(jù)透視表進行農(nóng)戶施肥調(diào)查大數(shù)據(jù)分析

完成農(nóng)戶施肥調(diào)查數(shù)據(jù)的計算處理，并確定符合數(shù)據(jù)透視表的數(shù)據(jù)源要求后，就可以應(yīng)用數(shù)據(jù)透視表了。打開Excel 2013，點擊“插入”菜單，就可以在左上角見到“數(shù)據(jù)透視表”，單擊“數(shù)據(jù)透視表”即可見到“創(chuàng)建數(shù)據(jù)透視表”界面（圖2）。

通常情況下保持圖2對話框內(nèi)默認的設(shè)置不變，單擊 “確定”按鈕后，即可在新工作表中創(chuàng)建一張空的數(shù)據(jù)透視表，如圖2所示?？梢姅?shù)據(jù)透視表分為2個區(qū)域，左邊為數(shù)據(jù)透視表的報表生成區(qū)域，會隨著右側(cè)選擇不同字段而更新，右邊為數(shù)據(jù)透視表的字段列表。

圖2 創(chuàng)建數(shù)據(jù)透視

數(shù)據(jù)透視表字段列表區(qū)域表現(xiàn)形式可以調(diào)整，但習慣采用上下兩個部分格式，其中上部給出數(shù)據(jù)源所有的列字段名稱以供選擇，下部再細分為4個區(qū)域：篩選器區(qū)域，標志區(qū)域中的字段將作為數(shù)據(jù)透視表的報表篩選字段；行區(qū)域，此標志區(qū)域中的字段將作為數(shù)據(jù)透視表的行標簽顯示；列區(qū)域，此標志區(qū)域中的字段將作為數(shù)據(jù)透視表的列標簽顯示；∑數(shù)值區(qū)域，此標志區(qū)域中的字段將作為數(shù)據(jù)透視表顯示統(tǒng)計的數(shù)據(jù)項。當某一個字段拖入∑數(shù)值區(qū)域后，單擊左鍵后，在彈出的窗體中再點擊“值字段設(shè)置”，即可以選擇統(tǒng)計函數(shù)，數(shù)據(jù)透視表提供有求和、計數(shù)、平均值、最大值、最小值、乘積、數(shù)值計數(shù)、標準偏差、總體標準偏差、方差、總體方差等11個統(tǒng)計函數(shù)供選擇。應(yīng)該指出的是，某個字段拖入“∑數(shù)值” 區(qū)域僅能獲得一項統(tǒng)計值，故若擬獲得某個字段的多項統(tǒng)計值，就應(yīng)該相應(yīng)次數(shù)的拖入該字段，如獲得有機肥N用量的平均值、標準誤差、參與統(tǒng)計個數(shù)（計數(shù)項）等3項，則“有機肥N用量（kg/667 m2）”字段應(yīng)該拖入3次。若認為拖入某個區(qū)域的字段不符合目標要求，則將鼠標放在該字段上，單擊左鍵，在彈出的界面中可刪除、移動該字段，報表區(qū)域也同步發(fā)生變化。

圖3中相應(yīng)區(qū)域已根據(jù)分析目的，完成了農(nóng)戶施肥調(diào)查數(shù)據(jù)字段的設(shè)置。首先將基地名稱拖入報表篩選區(qū)域，以便于分基地掌握作物施肥情況；將蔬菜名稱拖入列標簽，以便于分作物進行施肥統(tǒng)計；將栽培方式拖入行區(qū)域，以便于分日光溫室、大棚進行作物施肥統(tǒng)計；將∑數(shù)值拖入行標簽區(qū)域，為的是分行展示不同區(qū)域不同作物不同栽培方式下的施肥分類統(tǒng)計結(jié)果；該文“∑數(shù)值”區(qū)域僅列出了統(tǒng)計化肥源N、P2O5、K2O用量和有機肥源N、P2O5、K2O用量的平均值。

圖3 數(shù)據(jù)透視表基本結(jié)構(gòu)與示例

在將字段拖入報表篩選、列標簽、行標簽、∑數(shù)值區(qū)域以及完成統(tǒng)計項選擇的同時，Excel 2013自動完成相應(yīng)的工作，其結(jié)果顯示在報表區(qū)域，表現(xiàn)出用數(shù)據(jù)透視表比用Excel 2013統(tǒng)計函數(shù)操作更方便，計算速度也更快的優(yōu)勢。圖3中報表區(qū)域顯示了不同區(qū)域不同作物不同栽培模式下的化肥源N、P2O5、K2O用量和有機肥源N、P2O5、K2O用量的平均值。不僅如此，在報表的下部還顯示出不同蔬菜的化肥源N、P2O5、K2O用量和有機肥源N、P2O5、K2O用量的平均值（不分區(qū)域不分栽培模式）；在報表的右側(cè)則顯示出不同區(qū)域不同栽培模式下的化肥源N、P2O5、K2O用量和有機肥源N、P2O5、K2O用量的平均值（不分作物），這為評價某一區(qū)域某一作物的施肥情況奠定基礎(chǔ)。

圖3中僅設(shè)置了化肥源N、P2O5、K2O用量和有機肥源N、P2O5、K2O用量的平均值，還有很多需要進行統(tǒng)計分析的內(nèi)容沒有拖入，當然，可以繼續(xù)拖入字段，但也可以重復(fù)上述步驟構(gòu)建新的數(shù)據(jù)透視表，完成對所關(guān)心的數(shù)據(jù)項進行統(tǒng)計，如基肥化肥N、P2O5、K2O用量分別占總化肥源N、P2O5、K2O用量的比例，基肥N、P2O5、K2O用量分別占N、P2O5、K2O總用量的比例等等。直到獲取所需要的所有統(tǒng)計信息。

在農(nóng)戶施肥海量數(shù)據(jù)統(tǒng)計分析時候，可能有些計算項比如有機肥源N、P2O5、K2O用量與無機肥源N、 P2O5、K2O用量比例等在開始分析時候沒有考慮到，或者需要進行更細的區(qū)域劃分等，那么只要按照數(shù)據(jù)透視表數(shù)據(jù)源規(guī)則進行計算處理、添加相應(yīng)的數(shù)據(jù)列等，即可完成相應(yīng)的統(tǒng)計分析。

目前已完成農(nóng)戶施肥數(shù)據(jù)分析的數(shù)據(jù)透視表構(gòu)建工作，但隨著工作的不斷開展，農(nóng)戶調(diào)查數(shù)據(jù)還會不斷增加，但數(shù)據(jù)透視表并不會同步更新，此時原有的數(shù)據(jù)透視表已不能如實反映原始數(shù)據(jù)了，為此必須對數(shù)據(jù)透視表進行手動更新。Excel 2013手動更新有2種方法，（1）選中數(shù)據(jù)透視表中的任一單元格，點擊鼠標右鍵，在彈出的快捷菜單中選擇刷新命令（圖4左）；（2）是選中數(shù)據(jù)透視表中的任一單元格，在“數(shù)據(jù)透視表工具”的“選項”選項卡中單擊“刷新”按鈕就可以完成刷新工作（圖4 右）。

2.3 應(yīng)用數(shù)據(jù)透視圖以圖形形式展示數(shù)據(jù)透視表結(jié)果

數(shù)據(jù)透視表提供了靈活、快捷的數(shù)據(jù)統(tǒng)計工具，同時Excel 2013 也提供了以圖形形式直觀、動態(tài)地展現(xiàn)數(shù)據(jù)透視表數(shù)據(jù)的工具，這就是數(shù)據(jù)透視圖。可通過3種方法創(chuàng)建數(shù)據(jù)透視圖。特別提出的是，如果將數(shù)據(jù)透視表另存為一個表，則可以按照Excel 普通圖進行制作、美化，這已不在該文研究之列。

圖4 數(shù)據(jù)透視表手工刷新方法

圖5 河北永清不同肥源養(yǎng)分用量

圖6 數(shù)據(jù)透視圖基本結(jié)構(gòu)與示例

圖7 根據(jù)數(shù)據(jù)透視表創(chuàng)建向?qū)?chuàng)建數(shù)據(jù)透視圖

（1）利用創(chuàng)建好的數(shù)據(jù)透視表創(chuàng)建數(shù)據(jù)透視圖。選擇數(shù)據(jù)透視表中任意單元格，在 “數(shù)據(jù)透視表工具”的“分析” 選項卡中，單擊“數(shù)據(jù)透視圖”彈出“插入圖表”對話框，在“插入圖表”對話框中根據(jù)需要選擇圖表類型。圖5是利用數(shù)據(jù)透視表的數(shù)據(jù)在非當前工作表上，選擇“柱形圖-簇狀柱形圖”展現(xiàn)的一個河北省永清縣設(shè)施蔬菜施肥調(diào)查統(tǒng)計結(jié)果并進行了美化。若在當前工作表上展現(xiàn)數(shù)據(jù)透視圖，可參見圖6。

（2）由數(shù)據(jù)源表直接創(chuàng)建數(shù)據(jù)透視圖。當沒有創(chuàng)建數(shù)據(jù)透視表時，也可以根據(jù)數(shù)據(jù)源表直接生成數(shù)據(jù)透視圖。打開Excel 2013，單擊數(shù)據(jù)源表中任一單元格，在“插入”選項卡中，單擊“數(shù)據(jù)透視圖-數(shù)據(jù)透視圖”按鈕，打開數(shù)據(jù)透視圖對話框（圖1）。在確定所用數(shù)據(jù)源及數(shù)據(jù)透視圖存放位置后，單擊“確定”按鈕，進入數(shù)據(jù)透視圖設(shè)置狀態(tài)，可見左側(cè)為數(shù)據(jù)透視表區(qū)域，中間是數(shù)據(jù)透視圖區(qū)域，右側(cè)是數(shù)據(jù)透視圖字段對話框；拖動字段到相應(yīng)區(qū)域，即可創(chuàng)建數(shù)據(jù)透視表，同時生成和數(shù)據(jù)透視表相對應(yīng)的默認類型數(shù)據(jù)透視圖（圖6）。若沒有字段拖入“報表區(qū)域”，即該區(qū)域為空，則生成一張圖，但若把基地名稱、蔬菜名稱、栽培方式（溫室/大棚）中的任一字段拖入“報表篩選”區(qū)域，則可以或分基地、或分蔬菜、或分栽培方式（溫室/大棚）進行作圖，圖6中間的數(shù)據(jù)透視圖是將基地名稱字段拖入“報表區(qū)域”，并選擇永清基地而作的，且經(jīng)過一定美化處理。美化前的數(shù)據(jù)透視圖帶有報表區(qū)域、圖例區(qū)域、軸類型區(qū)域和∑值區(qū)域的所有按鈕，這些可通過點擊“分析”選項卡中的“字段按鈕”來顯示或者隱藏。

（3）是根據(jù)數(shù)據(jù)透視表創(chuàng)建向?qū)?chuàng)建數(shù)據(jù)透視圖。在規(guī)范的數(shù)據(jù)源表中單擊任一單元格，依次按下＜alt＞、＜D＞、＜P＞鍵，則彈出“數(shù)據(jù)透視表和數(shù)據(jù)透視圖向?qū)?步驟1（共3步）對話框”（圖7），在“所需創(chuàng)建的報表類型”中選中“數(shù)據(jù)透視圖（及數(shù)據(jù)透視表）”后，依次點擊“下一步”，直至第三步的“完成”按鈕，則可生成圖6界面，根據(jù)需要完成相關(guān)操作及美化，即可生成預(yù)期的圖件。

Excel 2013中數(shù)據(jù)透視圖與普通圖完全融合，但鑒于數(shù)據(jù)透視圖是在數(shù)據(jù)透視表基礎(chǔ)之上創(chuàng)建的，對數(shù)據(jù)透視表高度依存，因此其布局受到數(shù)據(jù)透視表的制約，當數(shù)據(jù)透視表布局改變，數(shù)據(jù)透視圖的布局也將發(fā)生變化；雖然可以對數(shù)據(jù)透視圖進行美化，也有可能達不到Excel普通圖的效果，如不能實現(xiàn)有機肥N用量柱和化肥N用量柱疊加。應(yīng)該指出的是數(shù)據(jù)透視圖與Excel普通圖比較，具有以下限制：（1）無法創(chuàng)建圖表類型為XY（散點）圖、氣泡圖和股價圖的數(shù)據(jù)透視圖；（2）無法調(diào)整圖形系列的位置順序。

3 結(jié)論

相對于Excel統(tǒng)計函數(shù)，數(shù)據(jù)透視表綜合了求和、計數(shù)、平均值、最大值、最小值、乘積、數(shù)值計數(shù)、標準偏差、總體標準偏差、方差、總體方差等11個統(tǒng)計函數(shù)，但其在對龐大的數(shù)據(jù)庫進行多條件統(tǒng)計分析時的速度明顯快于使用Excel函數(shù)公式統(tǒng)計。因此在海量數(shù)據(jù)分析時候數(shù)據(jù)透視表具有強大優(yōu)勢，如對某地的多年逐日氣象觀測資料進行統(tǒng)計。

若統(tǒng)計指標較多，建議采用同一數(shù)據(jù)源創(chuàng)建多個數(shù)據(jù)透視表，利用數(shù)據(jù)源和數(shù)據(jù)透視表的耦合性，修訂數(shù)據(jù)源后則可對所有數(shù)據(jù)透視表進行同步更新。

雖然數(shù)據(jù)透視圖和普通圖是融合的，能展示數(shù)據(jù)分析結(jié)果，但由于數(shù)據(jù)透視圖是基于數(shù)據(jù)透視表創(chuàng)建的，故盡管可以編輯美化數(shù)據(jù)透視圖，也有可能也達不到預(yù)期的效果。

[1] 李克農(nóng)，王翰霖，郭愛芬，等．2011～2014年銀川市農(nóng)戶施肥調(diào)查與評價．現(xiàn)代農(nóng)業(yè)科技，2015，15：222～223

[2] 趙護兵，王朝暉，高亞軍，等.西北典型區(qū)域旱地冬小麥農(nóng)戶施肥調(diào)查分析[J].植物營養(yǎng)與肥料學報，2013，19（4）：840～848

[3] 黎青慧．陜西省黃瓜西紅柿施肥調(diào)查．西北農(nóng)林科技大學學報（自然科學版），2003，31（增刊）：73～78

[4] Excel Home編著．Excel2013數(shù)據(jù)透視表應(yīng)用大全．北京：北京大學出版社．2016

[5] 張麗．Excel2007數(shù)據(jù)透視表和數(shù)據(jù)透視圖的使用．科技信息，2009，（5）：442～444

[6] 孫鳳杰．使用Excel數(shù)據(jù)透視表進行問卷統(tǒng)計分析，無線互聯(lián)科技，2013，（3）：67

[7] 吳丹．Excel2007數(shù)據(jù)透視表在高校學生成績管理中的應(yīng)用研究．物聯(lián)網(wǎng)技術(shù)，2013，（8）：73～74，78

*資助項目：“十三五”國家重點研發(fā)計劃課題（2016YFD0201001，2016YFD0801006）；中國農(nóng)業(yè)科學院科技創(chuàng)新工程協(xié)同創(chuàng)新任務(wù)（CAASXTCX2016003）

張懷志（1968-），博士，副研究員，研究方向：施肥與環(huán)境，農(nóng)業(yè)信息技術(shù)

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