利用VBA工具實現汽車產品數據自動化處理

肖凱 郁淑聰 王景景

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

關鍵詞：VBA;乘用車;產品研究;市場研究;數據挖掘

1 VBA工具簡述

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

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

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

2 汽車產品數據體系

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

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

3 VBA數據處理范例

3.1 數據整合

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

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

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

3.2 加權自動化計算

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

With ActiveSheet.PivotTables（"數據透視表1"）.PivotFields（"整合月份1"）

.Orientation=xlRowField

.Position=1

'設定計算交叉維度，即“父行”

End With

With ActiveSheet.PivotTables（"數據透視表1"）.PivotFields（"車型"）

.Orientation = xlRowField

.Position = 2

End With

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

With ActiveSheet.PivotTables（"數據透視表1"）.PivotFields（"計數項：銷量"）

.Caption = "求和項：銷量"

.Function=xlSum

.Calculation=xlPercentOfParentRow

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

計算執行后可得計算結果類似表4。

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

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

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

Range（"AE2：AE291678"）.Select

'利用VLOOKUP函數匹配數據透視表，匹配車型占比值

Dim Sht As Worksheet

For Each Sht In Sheets

Sht.UsedRange = Sht.UsedRange.Value

'利用VBA代碼將VLOOKUP結果轉換為永久數值，消去公式

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

Application.CutCopyMode=False

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

Range（"AK2"）.Select

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

Range（"AK2"）.Select

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

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

Range（"AK2：AQ2"）.Select

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

Range（"AK2：AQ291678"）.Select

'將計算公式應用到所有單元格中

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

4 總結

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

參考文獻：

[1]王漢雄，喬景順.Excel VBA在測量數據處理中的應用[J].測繪科學，2008（2）：210-212.

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

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