在EXCEL中使用VBA處理數(shù)據(jù)透視表的探索

摘 要：Excel中的數(shù)據(jù)透視表能對(duì)原始數(shù)據(jù)進(jìn)行重新組織和動(dòng)態(tài)查詢(xún)，但需要進(jìn)行手工生成，會(huì)產(chǎn)生一定的人為失誤。文章通過(guò)一個(gè)商品銷(xiāo)售工作表為例，編程實(shí)現(xiàn)了通過(guò)VBA自動(dòng)生成數(shù)據(jù)透視表及對(duì)其的修改，使Excel的數(shù)據(jù)處理滿(mǎn)足個(gè)性化需要，大大提高了工作效率和質(zhì)量。

關(guān)鍵詞：Excel；VBA；數(shù)據(jù)處理

1 概述

Excel是Microsoft Office家族成員中一個(gè)功能強(qiáng)大、技術(shù)先進(jìn)、使用方便的表格式數(shù)據(jù)綜合管理和分析系統(tǒng)[1]。Visual Basic for Application（簡(jiǎn)稱(chēng)VBA）是新一代標(biāo)準(zhǔn)宏語(yǔ)言，基于Visual Basic for Windows發(fā)展起來(lái)，支持面向?qū)ο蟮某绦蛟O(shè)計(jì)語(yǔ)言。由于它具有直接應(yīng)用Office套裝軟件的各項(xiàng)強(qiáng)大功能，其宏記錄器可以很容易將日常工作轉(zhuǎn)換為VBA。

Excel數(shù)據(jù)透視表實(shí)質(zhì)上是一種基于數(shù)據(jù)源的能對(duì)原始數(shù)據(jù)進(jìn)行重新組織和動(dòng)態(tài)查詢(xún)的表格[2]。創(chuàng)建得到的是一種數(shù)據(jù)表格，該表格的數(shù)據(jù)來(lái)源于已經(jīng)創(chuàng)建好的數(shù)據(jù)源，不僅只是Excel工作表中的數(shù)據(jù)，也可以是其他數(shù)據(jù)庫(kù)甚至是Internet上的數(shù)據(jù)。

使用VBA來(lái)自動(dòng)生成數(shù)據(jù)透視表，既避免了手工生成數(shù)據(jù)透視表的麻煩，又可以使生成的數(shù)據(jù)透視表安全穩(wěn)定，不會(huì)出現(xiàn)很多人工生成造成的失誤。

2 實(shí)例分析

文章以商品銷(xiāo)售工作表為例，該表列出了在某日期中哪些銷(xiāo)售人員銷(xiāo)售了哪些商品，并附帶有銷(xiāo)售的交易號(hào)以備查詢(xún)，在將來(lái)方便對(duì)銷(xiāo)售進(jìn)行總計(jì)，從而對(duì)銷(xiāo)售員進(jìn)行表彰。

商品銷(xiāo)售表如圖1所示：

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

Option Explicit

Private Sub CommandButton1_Click（）

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

Data：=“Sheet1！R2C1：R14C5”）

.CreatePivotTable TableDestination

：=Range（“F1”），TableName

：=“華夏數(shù)碼城銷(xiāo)售透視表”

ActiveSheet.PivotTables（“華夏數(shù)碼城銷(xiāo)售透視表”） .SmallGrid=False

ActiveSheet.PivotTables（“華夏數(shù)碼城銷(xiāo)售透視表”）

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

ActiveSheet.PivotTables（“華夏數(shù)碼城銷(xiāo)售透視表”）

.PivotFields（“銷(xiāo)售金額”）.orientation=x1DataField

Range（“F1”）.select

End sub

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

步驟三、利用VBA修改數(shù)據(jù)透視表。可以得到所選擇日期當(dāng)天的銷(xiāo)售金額最多的銷(xiāo)售員工。設(shè)計(jì)代碼如下：

Function GetValue（ByVal TempDate As String） As String

Set PivotFieldVable = ActiveSheet. PivotTables（“華夏數(shù)碼城銷(xiāo)

售透視表”）. PivotFields（“銷(xiāo)售日期”）

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“當(dāng)天的銷(xiāo)售狀元是：” Name， vbOKOnly， “銷(xiāo)售狀元”

Else

MsgBox“當(dāng)天沒(méi)有銷(xiāo)售狀元”， vbOKOnly，“銷(xiāo)售狀元”

End If

End sub

如果選擇日期有誤或者當(dāng)天沒(méi)有銷(xiāo)售記錄，會(huì)彈出如圖3對(duì)話(huà)框。

3 結(jié)束語(yǔ)

原則是只要數(shù)據(jù)源有3列或3列以上有數(shù)據(jù)，都可以使用它來(lái)創(chuàng)建數(shù)據(jù)透視表[4]。但是因?yàn)镋xcel中的數(shù)據(jù)透視表只是一種分析工具，而創(chuàng)建它的目的是從現(xiàn)有的數(shù)據(jù)中快速有效地整理出有價(jià)值的信息。數(shù)據(jù)透視表適合于把某列中含有多種數(shù)據(jù)而每種數(shù)據(jù)又重復(fù)出現(xiàn)多次的列以行的形式列出，使得每種數(shù)據(jù)在數(shù)據(jù)透視表中都以獨(dú)立的列的形式出現(xiàn)并可以同時(shí)對(duì)每種數(shù)據(jù)進(jìn)行加工處理。通過(guò)使用Excel VBA，可以減少人為生成數(shù)據(jù)透視表產(chǎn)生的失誤問(wèn)題，提高了辦公軟件的工作效率和質(zhì)量。

參考文獻(xiàn)

[1]蘇進(jìn).探究如何在EXCEL中使用VBA編程處理數(shù)據(jù)[J].數(shù)字技術(shù)與應(yīng)用，2016（1）：250.

[2]陸嬌嬌.基于ExcelVBA的多考場(chǎng)電子成績(jī)自動(dòng)統(tǒng)計(jì)[J].電腦知識(shí)與技術(shù)，2016（12）：218.

[3]孫敬杰.Excel入門(mén)與實(shí)例演練[M].北京：中國(guó)青年電子出版社，2008.

[4]周志軍，張帆，竇志強(qiáng).基于ExcelVBA實(shí)現(xiàn)考試自動(dòng)編排[J].中國(guó)管理信息化，2016，19（3）：181-182.

作者簡(jiǎn)介：呂嵐（1982-），女，研究生，研究方向?yàn)橛?jì)算機(jī)科學(xué)與技術(shù)，陜西鐵路工程職業(yè)技術(shù)學(xué)院講師。