巧用數據透視表處理多行數據轉換成多列數據問題

李雪芹

摘要：利用數據透視表對數據進行分析是Excel辦公軟件強大數據處理功能之一，數據透視表也是《Excel 電子表格制作》教材中的一個重要知識點，是Excel應用的一個核心內容，在歷年來的省市級素質能力大賽中是必考的一項內容，下面文章是從歷年試題中抽取的一道有關運用數據透視表來處理多行數據轉換成多列數據問題的做題過程來分析。

關鍵詞：數據透視表;行標簽;列標簽;值標簽

中圖分類號：G642? 文獻標識碼：A

文章編號：1009-3044（2021）29-0028-03

數據分析功能是Excel強大的功能之一[2]，是《Excel 電子表格制作》教材中的一個重要知識點，是Excel應用的一個核心內容，因此是必須掌握的。用數據透視表分析處理數據知識點在歷年來的省市級素質能力大賽中屢次出現，而在所有的中職《Excel 電子表格制作》教材中都涉及很少一部分內容，且講解非常簡單，甚至有些教材中還沒有這一部分內容，因此大部分學生在學習使用數據透視表對數據進行分析處理時也覺得頗有難度。下面我們就以一道利用數據透視表處理成績表的行列不同顯示模式的報表類競賽試題為例來進行分析其做題步驟。

1問題引入

數據透視表是一種對大量數據進行快速匯總和建立交叉列表的交互式報表，用戶可以旋轉其行或列以查看對源數據的不同匯總，它是Excel強大數據處理能力的具體體現[1]。下面是從歷年Excel競賽試題中抽取的一道有關利用數據透視表處理多行與多列轉換的創意類試題。試題要求：（1）請將“成績素材”表中的原始成績表（如圖1所示）處理成如圖2所示的常規成績表放置在“答題1”工作表中;（2）請簡述自己的解題思路和步驟，將答題思路寫到“答題2”工作表中指定位置。

2問題分析

本試題原成績表中每位學生有5門成績，分5行顯示;處理后的成績表中將5門成績分5列顯示，這是一個典型的利用數據透視表將多行數據轉換為多列數據進行分析查看的一個案例。

3問題處理

3.1插入數據透視表

先選擇“成績素材”工作表，將光標置于數據區域的任意一個單元格內，單擊“插入”-“表格”-“數據透視表”按鈕，打開“創建數據透視表”對話框，在“選擇一個表或區域”欄中選擇“成績素材”工作表中的數據區域（A1：F431）區域，在“選擇放置數據透視表的位置”欄中選擇“新工作表”，如圖3所示。單擊“確定”按鈕，進入一個新的工作表SHEET1中。

在新工作表SHEET1中右側顯示“數據透視表字段”設置窗口，將“院系”、“班級”、“學號”、“姓名”字段拖入“行”標簽下面框中，將“課程”字段拖入“列”標簽下面框中，將“成績”字段拖入“值”標簽下面框中，如圖4所示。生成的數據透視表自動插入到SHEET1工作表的A3單元格開始位置處。

3.2設置數據透視表

1）單擊“數據透視表工具”-“設計”-“布局”-“分類匯總”右側的下拉箭頭，選擇“不顯示分類匯總”，可以將分類匯總行隱藏。如圖5所示。

2）單擊“數據透視表工具”-“設計”-“布局”-“報表布局”右

側的下拉箭頭，分別選擇“表格形式”顯示命令、“重復所有項目標簽”命令，即可將數據透視表以表格形式顯示，并將所有重復的項目標簽都顯示出來，如圖6所示。

3）選擇SHEET1工作表的A4：J90區域，單擊鼠標右鍵，選擇“復制”命令，切換到“答題1”工作表的A1單元格，單擊鼠標右鍵，選擇“粘貼選項”中的“值粘貼”按鈕，即可將數據透視表中的數據分析結果區域復制到“答題1”工作表中的指定位置。

3.3美化處理后的成績表

1）為表格添加邊框。選擇“答題1”工作表的A1：J87區域，單擊“開始”-“單元格”-“格式”右側的下拉列表，選擇“設置單元格格式”命令，打開“設置單元格格式”對話框，選擇“邊框”選項卡，在“直線”樣式欄中選擇合適的線型，在“預置”欄中分別單擊“外邊框”和“內部”按鈕，再單擊“確定”按鈕。

2）調整列寬。選擇A2：J87區域，單擊“格式”下拉列表，選擇“自動調整列寬”命令，將這兩列設置為根據內容自動調整列寬，使這兩列內容能夠完全顯示出來。將“總計”字段名字改為“總分”。

3）設置字段名行字形及填充顏色。選擇A1：J1單元格區域，單擊“開始”-“字體”-“”按鈕，將字段名行文字設置加粗顯示;單擊“開始”-“字體”-“填充”下拉列表，選擇合適的填充顏色。

4）設置字段名行各課程名稱單元格自動換行。選擇E1：J1單元格區域，單擊“開始”-“對齊方式”-“”按鈕，將各門課程字段名單元格設置為自動換行顯示。

4問題小結

本試題是Excel數據處理項目中的創意類題型，它是利用數據透視表創建行列交叉列表的交互式報表的一個典型案例。這道試題只要認真觀察、比較原表與結果樣表的區別，就不難想到使用數據透視表來處理，并分析出數據透視表的“行”標簽字段、“列”標簽字段、“值”標簽字段，難點在于生成數據透視表后的顯示設置，這在所有中職教材中都很少涉及，所以需要教師在講解這一部分內容時將“數據透視表工具”各選項組中的按鈕多演示、并找些相關數據透視表顯示設置的樣題給學生們

多練習、多長見識。

參考文獻：

[1]王小林，郭燕.Excel 2010電子表格制作案例教程[M].北京：航空工業出版社，2017.

[2]劉可，李顯進.Excel 2010從入門到精通[M].北京：清華大學出版社，2014.