在EXCEL中使用VBA處理數據透視表的探索

摘 要：Excel中的數據透視表能對原始數據進行重新組織和動態查詢，但需要進行手工生成，會產生一定的人為失誤。文章通過一個商品銷售工作表為例，編程實現了通過VBA自動生成數據透視表及對其的修改，使Excel的數據處理滿足個性化需要，大大提高了工作效率和質量。

關鍵詞：Excel；VBA；數據處理

1 概述

Excel是Microsoft Office家族成員中一個功能強大、技術先進、使用方便的表格式數據綜合管理和分析系統[1]。Visual Basic for Application（簡稱VBA）是新一代標準宏語言，基于Visual Basic for Windows發展起來，支持面向對象的程序設計語言。由于它具有直接應用Office套裝軟件的各項強大功能，其宏記錄器可以很容易將日常工作轉換為VBA。

Excel數據透視表實質上是一種基于數據源的能對原始數據進行重新組織和動態查詢的表格[2]。創建得到的是一種數據表格，該表格的數據來源于已經創建好的數據源，不僅只是Excel工作表中的數據，也可以是其他數據庫甚至是Internet上的數據。

使用VBA來自動生成數據透視表，既避免了手工生成數據透視表的麻煩，又可以使生成的數據透視表安全穩定，不會出現很多人工生成造成的失誤。

2 實例分析

文章以商品銷售工作表為例，該表列出了在某日期中哪些銷售人員銷售了哪些商品，并附帶有銷售的交易號以備查詢，在將來方便對銷售進行總計，從而對銷售員進行表彰。

商品銷售表如圖1所示：

步驟一、在工作表上新建一個“按鈕”控件，命名為“構造透視表”。并在“設計模式”下雙擊該按鈕控件進入“Visual Basic編輯器”，編輯該“按鈕”控件的單擊響應代碼如下所示：

Option Explicit

Private Sub CommandButton1_Click（）

ActiveWorkbook.PivotCaches.Add（SourceType：=x1Database，Source

Data：=“Sheet1！R2C1：R14C5”）

.CreatePivotTable TableDestination

：=Range（“F1”），TableName

：=“華夏數碼城銷售透視表”

ActiveSheet.PivotTables（“華夏數碼城銷售透視表”） .SmallGrid=False

ActiveSheet.PivotTables（“華夏數碼城銷售透視表”）

.AddFields RowFields：=Array（“銷售日期”，“銷售商品”）， ColumnFields：=“銷售人員”

ActiveSheet.PivotTables（“華夏數碼城銷售透視表”）

.PivotFields（“銷售金額”）.orientation=x1DataField

Range（“F1”）.select

End sub

步驟二、編輯代碼完畢后保存并退出“Visual Basic編輯器”，單擊“構造透視表”按鈕即可自動生成如圖2所示的透視表。

步驟三、利用VBA修改數據透視表。可以得到所選擇日期當天的銷售金額最多的銷售員工。設計代碼如下：

Function GetValue（ByVal TempDate As String） As String

Set PivotFieldVable = ActiveSheet. PivotTables（“華夏數碼城銷

售透視表”）. PivotFields（“銷售日期”）

Set PivotVable= PivotFieldVable.PivotItems（CStr（CDate（Temp

Date）））

GetRow = pivotValue.DataRange.row

For TempInt =7 to 12 Step 1

If （Cells（GetRow， TempInt）.Value=Cells（GetRow， 13）.Value） Then

GetValue=Cells（2，TempInt）.Value

Exit Function

End If

Next TempInt

ENDCHK：

GetValue=“”

End Function

Private Sub CommandButton1_Click（）

Dim Name As String

Name=GetValue（ComboBox1.Text）

If （Name <>“”） Then

MsgBox“當天的銷售狀元是：” Name， vbOKOnly， “銷售狀元”

Else

MsgBox“當天沒有銷售狀元”， vbOKOnly，“銷售狀元”

End If

End sub

如果選擇日期有誤或者當天沒有銷售記錄，會彈出如圖3對話框。

3 結束語

原則是只要數據源有3列或3列以上有數據，都可以使用它來創建數據透視表[4]。但是因為Excel中的數據透視表只是一種分析工具，而創建它的目的是從現有的數據中快速有效地整理出有價值的信息。數據透視表適合于把某列中含有多種數據而每種數據又重復出現多次的列以行的形式列出，使得每種數據在數據透視表中都以獨立的列的形式出現并可以同時對每種數據進行加工處理。通過使用Excel VBA，可以減少人為生成數據透視表產生的失誤問題，提高了辦公軟件的工作效率和質量。

參考文獻

[1]蘇進.探究如何在EXCEL中使用VBA編程處理數據[J].數字技術與應用，2016（1）：250.

[2]陸嬌嬌.基于ExcelVBA的多考場電子成績自動統計[J].電腦知識與技術，2016（12）：218.

[3]孫敬杰.Excel入門與實例演練[M].北京：中國青年電子出版社，2008.

[4]周志軍，張帆，竇志強.基于ExcelVBA實現考試自動編排[J].中國管理信息化，2016，19（3）：181-182.

作者簡介：呂嵐（1982-），女，研究生，研究方向為計算機科學與技術，陜西鐵路工程職業技術學院講師。