利用VBA工具實現(xiàn)汽車產(chǎn)品數(shù)據(jù)自動化處理

肖凱 郁淑聰 王景景

摘 要：隨著國內(nèi)汽車市場競爭壓力進一步增大，各大主機廠都在加快汽車產(chǎn)品的開發(fā)換代工作。同時，隨著各家產(chǎn)品策略的轉(zhuǎn)變，數(shù)據(jù)體系和分析維度也在不斷變化，如產(chǎn)品名稱的變更，數(shù)據(jù)源的轉(zhuǎn)換，車型分類標(biāo)準擴充等，每次變更都會導(dǎo)致數(shù)據(jù)體系的變化，這也導(dǎo)致汽車產(chǎn)品研究工作面臨大量繁瑣的數(shù)據(jù)分析工作，給相關(guān)業(yè)務(wù)人員帶來了諸多挑戰(zhàn)，也占用了大量不必要的時間，因此，設(shè)計數(shù)據(jù)自動化處理方案對如今的汽車產(chǎn)品規(guī)劃工作十分重要。本文采用了技術(shù)門檻較低的EXCEL及VBA進行自動化數(shù)據(jù)處理，并著重保留其可修改性，以便滿足不斷變化的數(shù)據(jù)分析需求。

關(guān)鍵詞：VBA;乘用車;產(chǎn)品研究;市場研究;數(shù)據(jù)挖掘

1 VBA工具簡述

Visual Basic for Applications（即VBA）是1993年微軟公司基于VB語言開發(fā)的可在應(yīng)用程序內(nèi)共享的通用自動化語言，集成于OFFICE軟件和部分第三方軟件中，如WORD，EXCEL，AUTOCAD等。VBA與其他常見的編程語言不同，需要基于對應(yīng)的應(yīng)用程序運行，不需要通過搭建編譯環(huán)境獨立運行，這給VBA工帶來了門檻低，效率高的優(yōu)勢，部分應(yīng)用程序還可以通過圖形界面生成VBA代碼，實現(xiàn)可視化編程操作，方便程序的流通和修改，這大大拓展了它的應(yīng)用范圍，更適合需要跨專業(yè)知識的非專業(yè)程序員使用，也更適合需要頻繁人工介入的小型項目。

以EXCEL中內(nèi)嵌的VBA工具為例，其開發(fā)工具包基于VB語言，并與軟件高度整合，可借助代碼實現(xiàn)多種對象的操作，包括數(shù)據(jù)的增刪修改，復(fù)制，移位等等，還可通過代碼調(diào)用EXCEL內(nèi)建功能，包括函數(shù)，透視表，排序等。VBA自動化辦公的本質(zhì)就是利用自動化執(zhí)行的代碼代替人工操作，避免重復(fù)性工作，提高數(shù)據(jù)處理效率。

與當(dāng)前流行的Python語言相比，雖然Python可以通過xlwt和xlrd兩個庫進行EXCEL文件的寫入編輯，但其主要應(yīng)用于原始數(shù)據(jù)的清洗整理。如果要對整理好的數(shù)據(jù)進行大量的透視，計算，生成圖表等操作，需要針對不同業(yè)務(wù)場景進行大量的代碼編寫，且過程無法可視化，最終形成的程序可讀性較差，后續(xù)修改困難，不適用于數(shù)據(jù)維度復(fù)雜多變的汽車產(chǎn)品數(shù)據(jù)體系。如最終要生成具有模板的EXCEL文件，還要利用代碼進行文件的統(tǒng)一化，不僅需要額外增加工作量，還對數(shù)據(jù)表的移交和交叉部門協(xié)作帶來困擾。而使用VBA操作不會有類似的問題，生成的圖表和普通EXCEL一致，所有的模板化操作可繼續(xù)沿用，也方便不同部門的交叉協(xié)作。故本文主要采用VBA工具搭配EXCEL自帶功能實現(xiàn)汽車產(chǎn)品數(shù)據(jù)的自動化處理，更適應(yīng)于汽車產(chǎn)品數(shù)據(jù)工作人員。

2 汽車產(chǎn)品數(shù)據(jù)體系

作為制造工業(yè)體系的明珠，整車產(chǎn)品數(shù)據(jù)包羅萬象，在產(chǎn)品規(guī)劃端有整車配置數(shù)據(jù)，研發(fā)端有參數(shù)數(shù)據(jù)，生產(chǎn)端有制造數(shù)據(jù)，銷售端有銷量及價格數(shù)據(jù)。且此類數(shù)據(jù)大多具有工業(yè)數(shù)據(jù)的幾大特點，如標(biāo)準不統(tǒng)一，應(yīng)用范圍窄，數(shù)據(jù)采集成本高等等。不同于互聯(lián)網(wǎng)信息類數(shù)據(jù)的處理，汽車產(chǎn)品數(shù)據(jù)分析更側(cè)重于簡單的交叉分析和專業(yè)人士的介入處理，需要數(shù)據(jù)處理和汽車產(chǎn)品兩方面的專業(yè)知識，為便于理解，本文主要運用以車款型數(shù)據(jù)為基準的銷量及價格數(shù)據(jù)進行分析。

在實際業(yè)務(wù)應(yīng)用中，往往需要針對不同的業(yè)務(wù)場景對產(chǎn)品的多項屬性進行交叉銷量分析，如針對不同車型的交叉分析，不同品牌的交叉分析，或是不同級別的交叉分析。傳統(tǒng)方法是利用EXCEL數(shù)據(jù)透視表或聯(lián)合公式先行計算各個交叉維度的加權(quán)比例，再代入數(shù)據(jù)加和求解，在面臨大量數(shù)據(jù)計算時會造成大量重復(fù)工作。本文以計算產(chǎn)品多維度交叉價格為例，簡要介紹VBA工具的應(yīng)用及數(shù)據(jù)處理思路。

3 VBA數(shù)據(jù)處理范例

3.1 數(shù)據(jù)整合

以表1數(shù)據(jù)為例，現(xiàn)有原始數(shù)據(jù)包含車型，車型級別，價位段，品牌層級等，并包含銷量及價格數(shù)據(jù)，計算交叉維度的月度加權(quán)價格與年度累計加權(quán)價格（如A級轎車10-15萬車型的加權(quán)價格），采用傳統(tǒng)方法計算，需要手動利用銷量計算出各個維度下各車型的銷量占比，再乘以價格算出加權(quán)，最后再以年度和月度分別求和，方法較為繁瑣。借用VBA方法處理，可實現(xiàn)自動化數(shù)據(jù)輸出操作，大大減輕工作量。

若利用VBA進行數(shù)據(jù)處理，首先需要進行數(shù)據(jù)的統(tǒng)一化整合，方便后續(xù)的自動化計算，減少人工操作的時間，本文利用EXCEL的“&”命令，將各個交叉維度整合為單一整體維度，方便后續(xù)計算加權(quán)，如表2，通過構(gòu)建D1&"/"&E1&"/"&F1公式（字母為相關(guān)數(shù)據(jù)所在列），將交叉維度合并為整體。需注意，此類思路可推廣到多種業(yè)務(wù)場景中，如關(guān)聯(lián)條件分析，多條件聯(lián)合匹配等。通過前置的數(shù)據(jù)處理可以減少VBA中復(fù)雜代碼的編寫，便于代碼遷移和修改，對于汽車產(chǎn)品數(shù)據(jù)來說，也給人工條件的介入預(yù)留了條件，一旦后續(xù)需要修改數(shù)據(jù)處理邏輯，只需要通過圖形界面修改前置數(shù)據(jù)處理格式即可，不需要重新進行代碼編寫，適用于業(yè)務(wù)場景多變的汽車產(chǎn)品數(shù)據(jù)人員。

同理，為進行交叉維度的月度及年度的加權(quán)分布計算，還需要在交叉維度上加入年月屬性，同樣利用“&”命令實現(xiàn)，需注意，此處的時間組合維度需與表格中的最小單位一致，以本表為例，最小計算單位為車型月度銷量和月度價格，則交叉維度中的年月屬性及行數(shù)也以車型為最小單位，并允許重復(fù)出現(xiàn)，這樣在最終計算占比時才不會出錯。最終形成如表3所示的分批整合數(shù)據(jù)。以此分類數(shù)據(jù)為基礎(chǔ)，即可開始利用VBA功能進行計算。

3.2 加權(quán)自動化計算

要計算加權(quán)價格，首先要計算各個維度的下各個車型的銷量占比，再將車型價格與銷量占比相乘，最終加和為加權(quán)價格。由于不同維度下的數(shù)據(jù)求和結(jié)果不同，難以利用公式計算，我們可以借助VBA代碼調(diào)用EXCEL內(nèi)置的數(shù)據(jù)透視表功能，利用數(shù)據(jù)透視表的“計算父列加和百分比”模塊，直接計算各個加和維度下的車型銷量占比，相較于利用嵌套公式計算，數(shù)據(jù)透視表可以通過圖形界面演算，過程便于理解，且可以隨時修改調(diào)整透視過程，比較符合非職業(yè)程序員的操作習(xí)慣?？蓪崿F(xiàn)代碼如下：

With ActiveSheet.PivotTables（"數(shù)據(jù)透視表1"）.PivotFields（"整合月份1"）

.Orientation=xlRowField

.Position=1

'設(shè)定計算交叉維度，即“父行”

End With

With ActiveSheet.PivotTables（"數(shù)據(jù)透視表1"）.PivotFields（"車型"）

.Orientation = xlRowField

.Position = 2

End With

'設(shè)定最小計算單位，如本文的車型銷量

With ActiveSheet.PivotTables（"數(shù)據(jù)透視表1"）.PivotFields（"計數(shù)項：銷量"）

.Caption = "求和項：銷量"

.Function=xlSum

.Calculation=xlPercentOfParentRow

'利用父行計算百分比的方式分別計算各個維度下車型銷量的占比

計算執(zhí)行后可得計算結(jié)果類似表4。

可以看出，計算出結(jié)果后，可以得到各個車型在各個交叉維度所占比例，此處的比例即為車型加權(quán)價格的參考，利用VBA執(zhí)行VLOOKUP函數(shù)，可將各車型占比匹配到車型單位中，需注意，這里匹配到的維度必須為計算運用的最小維度，即前文所述的車型月度銷量和月度價格，且此處匹配的MIX值在總體維度上加和需為100%（即任一類交叉維度下所有車型MIX值加和結(jié)果為100%），如加和比例不滿足100%，可能是數(shù)據(jù)結(jié)構(gòu)或計算方式有誤，可嘗試換用“父列匯總百分比”或轉(zhuǎn)置數(shù)據(jù)結(jié)構(gòu)，執(zhí)行代碼后驗證無誤才可進行下一步操作，此處匹配代碼如下：

ActiveCell.FormulaR1C1 = "=VLOOKUP（@C[-10]，Sheet1！C[-30]：C[-29]，2，0）"

Selection.AutoFill Destination：=Range（"AE2：AE291678"）

Range（"AE2：AE291678"）.Select

'利用VLOOKUP函數(shù)匹配數(shù)據(jù)透視表，匹配車型占比值

Dim Sht As Worksheet

For Each Sht In Sheets

Sht.UsedRange = Sht.UsedRange.Value

'利用VBA代碼將VLOOKUP結(jié)果轉(zhuǎn)換為永久數(shù)值，消去公式

計算出各個維度下的車型銷量占比后，本類計算最重要的工作已經(jīng)完成，后續(xù)只需要進行常規(guī)加權(quán)計算操作，可利用VBA將比例與車型價格相乘，計算出車型加權(quán)價格，再借助數(shù)據(jù)透視表或代碼公式將加權(quán)價格以各個維度為單位求和，得出各維度的最終加權(quán)價格。代碼如下：

Application.CutCopyMode=False

ActiveCell.FormulaR1C1 = "=RC[-7]*RC[-26]"

Range（"AK2"）.Select

ActiveCell.FormulaR1C1 = "=RC[-7]*RC11"

Range（"AK2"）.Select

'利用VBA代碼將銷量占比與車型價格相乘，計算出車型加權(quán)價格

Selection.AutoFill Destination：=Range（"AK2：AQ2"），Type：=xlFillDefault

Range（"AK2：AQ2"）.Select

Selection.AutoFill Destination：=Range（"AK2：AQ291678"）

Range（"AK2：AQ291678"）.Select

'將計算公式應(yīng)用到所有單元格中

需注意，此處的單元格應(yīng)用代碼需要根據(jù)實際數(shù)據(jù)情況進行修改，否則會出現(xiàn)重復(fù)的“#N/A”錯誤，如本題所例，單元格范圍由AK2至AQ291678，實際運用時需根據(jù)實際數(shù)據(jù)情況進行代碼調(diào)整，并注意篩選后帶來數(shù)據(jù)行數(shù)減少等問題。最終，通過復(fù)用代碼實現(xiàn)類似數(shù)據(jù)重復(fù)處理，最終輸出結(jié)果如表5所示。

4 總結(jié)

通過以上計算可以了解到，VBA計算的主要優(yōu)勢在于可利用EXCEL的自帶功能，如透視表，公式替換，表格統(tǒng)一等。更便于兼職數(shù)據(jù)處理人員使用。以本文為例，如不擅長編寫代碼，可借助EXCEL的宏錄制功能，利用圖形界面錄制透視表操作過程，自動生成代碼，完成MIX的計算，后續(xù)只需要針對代碼進行微調(diào)，保持計算覆蓋所有單元格，確保格式統(tǒng)一，即可完成自動化數(shù)據(jù)分析。同時，通過調(diào)整數(shù)據(jù)預(yù)處理方式，本文所述方法還可以用于周期性產(chǎn)品數(shù)據(jù)的處理及匹配，如將數(shù)據(jù)維度替換為自動鏈接數(shù)據(jù)庫的新車型銷量數(shù)據(jù)，即可實現(xiàn)自動化計算新上市車型MIX，將數(shù)據(jù)維度替換為車型配置數(shù)據(jù)，可實現(xiàn)產(chǎn)品數(shù)據(jù)智能匹配。如上所述，VBA操作具有良好的兼容性，且操作門檻低，可應(yīng)用于汽車產(chǎn)品研究業(yè)務(wù)的各個環(huán)節(jié)，適合需要頻繁人工介入的汽車產(chǎn)品數(shù)據(jù)處理人員，在實際業(yè)務(wù)中可大大降低工作壓力，提高數(shù)據(jù)處理效率，從而有更多的時間放在數(shù)據(jù)的解讀上，充分發(fā)揮人員的專業(yè)能力，避免無意義的時間消耗。

參考文獻：

[1]王漢雄，喬景順.Excel VBA在測量數(shù)據(jù)處理中的應(yīng)用[J].測繪科學(xué)，2008（2）：210-212.

[2]賀婧.Excel VBA在道路工程測量中的應(yīng)用[J].科技創(chuàng)新與應(yīng)用，2020（08）：185-186.

[3]王靖，王林，周金文.用Excel VBA方法實現(xiàn)報表自動生成[J].電力科學(xué)與工程. 2009（01）.