利用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）.