论文部分内容阅读
摘 要：本文主要介绍了用EXCEL相关函数及其嵌套使用对不同的汽车产品工程零件清单进行汇总比较。通过比较，可以清晰的看到清单的相同与差异处，对产品开发初期工程零件清单的准确性及完整性以及前期财务核算整车成本有重要指导意义。
关键词：EXCEL VLOOKUP
Comparison Method of List Difference of Product Engineering Parts
Lu Hailing Jiang Zhangying
Abstract：This article mainly introduces the use of EXCEL related functions and their nested use to summarize and compare different automotive product engineering parts lists. Through comparison， you can clearly see the similarities and differences in the list， which has important guiding significance for the accuracy and completeness of the engineering parts list at the early stage of product development and the preliminary financial accounting of the vehicle cost.
Key words：EXCEL， VLOOKUP
汽车的工程零件清单是由整车特征的所有沿用件、修改件以及新开发零件构成。在开发一款新产品时，工程零件清单的准确性及完整性对项目阶段的造车与财务成本核算等极为重要。本文主要介绍了在项目开发的初期，通过把新开发车型与已量产车型零件清单的关键信息放同一表格的相同或相近位置进行比较的方法。
1 工程零件清單关键信息的介绍
统一零部件代码（Uniform Parts Classification），它是规定了整车功能模块的代码。功能名称地址代码 （Functional Name Address），由5位字符/字母组成的代码，它表示了一个零件用法的以一个名词和功能修饰词语进行修饰的描述。UPC必须与FNA一起使用，每一个FNA必须与UPC一块使用才会产生零件名称。零部件号则是零部件本身的代码。
2 产品工程零件清单差异比较
首先按需求筛选出需要进行比较的新开发车型与已量产车型的工程零件清单，把需要比较的关键信息列粘贴到新EXCEL表格A与B。对sheet A/B表添加辅助列名称UPC&FNA（F列），用公式F2 =C2&D2并向下拉完成F列内容填充。此时，行号相同的两表格数据是不一样的（图1图2），无法比较两份清单的差异。
接下来，使用“自定义排序”功能对数据进行初始排序。在sheet A/B表格表头的UPC&FNA下三角选择“自定义排序”并以 “主要关键字”为UPC&FNA、“次要关键字”为零部件中文名称进行排序。
2.1 产品工程零件清单差异比较方法一：插入功能
第一步：把sheet A/B两表初步排序后的结果分别复制至汇总比较表格。
第二步：对汇总表格的数据进行逐行的核对比较。若零部件中文名称与UPC&FNA不同，则选择差异的零件并通过右键→活动单元格下移插入单元格，使下一个相同的零部件中文名称与UPC&FNA行在同一行。两零件清单有1000多行，此方法核对数据需要反复插入操作多次以实现两表数据差异化可目视比较。
2.2 产品工程零件清单差异比较方法二：使用EXCEL函数
添加辅助列，自定义sheet A的序号，使用excel函数vlookup，把sheet A的序号VLOOKUP到sheet B序号中，利用数字升序进行排序使得sheet A/B两表格数据排序后，相同或相近内容处于同一行以实现产品工程零件清单差异比较。
第一步：为提高两个表格直接数据的匹配度，先对sheet A/B表添加两个辅助， 分别命名为零部件中文名称&UPC&FNA（G列）、序号（H列）。
第二步：sheet A/B表分别使用合并单元格公式使UPC&FNA&零部件中文名称单元格G2=B2&C2&D2并向下拉完成G列内容填充;sheetA序号列单元格H2填写数字1并向下拉，选择“填充序列”模式，使该列单元格数值为递增模式1、2、3、4、5……
第三步：使用countif公式对G列进行计算，可以看到sheet A 表G列值相同时，H列会有一个或者多个值。VLOOKUP是一个查找函数，给定一个查找的目标，它从指定的查找区域返回想要查找到的第一值。G列值相同多个序号值在sheet B的序号引用时都只引用了第一个值，不利于数据处理。所以再添加两辅助列并分别命名为辅助B表序号1（I列）、辅助B表序号2（J列）。 ①sheet A表格辅助B表序号1运用嵌套函数使I2=H2&IFERROR（"/"&VLOOKUP（G2，G3：I$1149，3，0），""）并下拉完善I列数据，I2单元格值为数据是G2对应H列的序号H2以及H3往后中G列单元格内容与G2相同的序号值并用“/”分隔。其中，函数VLOOKUP=（Lookup_value，Table_array，Col_index_num，Range_lookup），Lookup_value为需要在数据表首列进行搜索的数值即G列，Table_array为需要在其中搜索数据的信息表即G3：I列数据表最底行，Col_index_num为满足条件的单元格在数组区域table_array中的序列号，I列是G列后的第三列，所以数值为3，Range_lookup数值0即表示精确匹配;IFERROR=（Value，Value_if_error），即如果表达式错误，则返回Value_if_error值，否则返回表达式自身的值，这里如果表达式错误，则什么也不返回。②sheet A表格辅助B表序号2运用使用嵌套函数使J2=H2&IFERROR（"/"&VLOOKUP（F2，F3：J$1149，5，0），""）并下拉完善J列数据，函数意义与第一点相同，仅更改搜索条件。结果如图3。 第四步：使用公式VLOOKUP 完善sheet B中序号列内容。首先通过G2零部件中文名称&UPC&FNA去sheet A中匹配I列的辅助B表序号1数值，即使用函数H2=VLOOKUP（G2，A！G：I，3，0）并向下拉完善H列数据。sheet B G列单元格若是在sheet A的G列不存在，会返回值“#N/A”。筛选“#N/A”，对这部分数据通过UPC&FNA去sheet A中匹配I列的辅助B表序号2数值，即使用函数H2=VLOOKUP（F2，A！F：J，5，0） 并向下拉完善H列数据。
第五步：筛选sheet B表H列包含“/”的单元格进行数据处理。表中有六行都是507/508/509（sheet A表序号相应内容只有三个，不够B表分配），就分別填写507、508、509，剩下三个单元格留空白;若有两行都是520/521/522，就分别填写520、521。对其他的三十多条数据反复此步操作使H列不含“/”，结果如图4。
第六步：对sheet B表H列单元格内容为空和“#N/A”的进行筛选，该单元格数值为上一个单元格数值加上0.01添加序号以完善sheet B表H列单元格内容。
第七步：运用countif函数，查看sheet A的H列序号是否存在于sheet B的H列序号，结果为1的是两个表格都有的序号，结果为0的仅是sheet A的序号。把结果为0的序号粘贴到sheet B的H列序号最底下空白处。再次运用countif函数，查看sheet B的H列序号是否存在于sheet A的H列序号，把结果为0的序号粘贴到sheet A的H列序号最底下空白处。分别对sheet A/B的H列序号进行升序排序。
第八步，分别把sheet A/B表内容粘贴到汇总比较表格里，最终结果如下图5。至此，两份不同的产品清单相同与差异处一目了然。
3 结语
Excel的函数涉及面广，种类多，功能强大。在产品开发初期，根据需求，灵活使用VLOOKUP及其嵌套函数，对产品开发初期工程零件清单与已量产的产品零件清单进行差异比较，可以提高产品开发初期工程零件清单的准确性及完整性，更好地指导前期财务核算整车成本，大大提高了数据处理的效率。
参考文献：
[1]魏茂林.办公软件应用（第4版）[M].高等教育出版社，2015.11.
[2]张山风.Excel统计函数应用解析[J].办公自动化，2009.11.
关键词：EXCEL VLOOKUP
Comparison Method of List Difference of Product Engineering Parts
Lu Hailing Jiang Zhangying
Abstract：This article mainly introduces the use of EXCEL related functions and their nested use to summarize and compare different automotive product engineering parts lists. Through comparison， you can clearly see the similarities and differences in the list， which has important guiding significance for the accuracy and completeness of the engineering parts list at the early stage of product development and the preliminary financial accounting of the vehicle cost.
Key words：EXCEL， VLOOKUP
汽车的工程零件清单是由整车特征的所有沿用件、修改件以及新开发零件构成。在开发一款新产品时，工程零件清单的准确性及完整性对项目阶段的造车与财务成本核算等极为重要。本文主要介绍了在项目开发的初期，通过把新开发车型与已量产车型零件清单的关键信息放同一表格的相同或相近位置进行比较的方法。
1 工程零件清單关键信息的介绍
统一零部件代码（Uniform Parts Classification），它是规定了整车功能模块的代码。功能名称地址代码 （Functional Name Address），由5位字符/字母组成的代码，它表示了一个零件用法的以一个名词和功能修饰词语进行修饰的描述。UPC必须与FNA一起使用，每一个FNA必须与UPC一块使用才会产生零件名称。零部件号则是零部件本身的代码。
2 产品工程零件清单差异比较
首先按需求筛选出需要进行比较的新开发车型与已量产车型的工程零件清单，把需要比较的关键信息列粘贴到新EXCEL表格A与B。对sheet A/B表添加辅助列名称UPC&FNA（F列），用公式F2 =C2&D2并向下拉完成F列内容填充。此时，行号相同的两表格数据是不一样的（图1图2），无法比较两份清单的差异。
接下来，使用“自定义排序”功能对数据进行初始排序。在sheet A/B表格表头的UPC&FNA下三角选择“自定义排序”并以 “主要关键字”为UPC&FNA、“次要关键字”为零部件中文名称进行排序。
2.1 产品工程零件清单差异比较方法一：插入功能
第一步：把sheet A/B两表初步排序后的结果分别复制至汇总比较表格。
第二步：对汇总表格的数据进行逐行的核对比较。若零部件中文名称与UPC&FNA不同，则选择差异的零件并通过右键→活动单元格下移插入单元格，使下一个相同的零部件中文名称与UPC&FNA行在同一行。两零件清单有1000多行，此方法核对数据需要反复插入操作多次以实现两表数据差异化可目视比较。
2.2 产品工程零件清单差异比较方法二：使用EXCEL函数
添加辅助列，自定义sheet A的序号，使用excel函数vlookup，把sheet A的序号VLOOKUP到sheet B序号中，利用数字升序进行排序使得sheet A/B两表格数据排序后，相同或相近内容处于同一行以实现产品工程零件清单差异比较。
第一步：为提高两个表格直接数据的匹配度，先对sheet A/B表添加两个辅助， 分别命名为零部件中文名称&UPC&FNA（G列）、序号（H列）。
第二步：sheet A/B表分别使用合并单元格公式使UPC&FNA&零部件中文名称单元格G2=B2&C2&D2并向下拉完成G列内容填充;sheetA序号列单元格H2填写数字1并向下拉，选择“填充序列”模式，使该列单元格数值为递增模式1、2、3、4、5……
第三步：使用countif公式对G列进行计算，可以看到sheet A 表G列值相同时，H列会有一个或者多个值。VLOOKUP是一个查找函数，给定一个查找的目标，它从指定的查找区域返回想要查找到的第一值。G列值相同多个序号值在sheet B的序号引用时都只引用了第一个值，不利于数据处理。所以再添加两辅助列并分别命名为辅助B表序号1（I列）、辅助B表序号2（J列）。 ①sheet A表格辅助B表序号1运用嵌套函数使I2=H2&IFERROR（"/"&VLOOKUP（G2，G3：I$1149，3，0），""）并下拉完善I列数据，I2单元格值为数据是G2对应H列的序号H2以及H3往后中G列单元格内容与G2相同的序号值并用“/”分隔。其中，函数VLOOKUP=（Lookup_value，Table_array，Col_index_num，Range_lookup），Lookup_value为需要在数据表首列进行搜索的数值即G列，Table_array为需要在其中搜索数据的信息表即G3：I列数据表最底行，Col_index_num为满足条件的单元格在数组区域table_array中的序列号，I列是G列后的第三列，所以数值为3，Range_lookup数值0即表示精确匹配;IFERROR=（Value，Value_if_error），即如果表达式错误，则返回Value_if_error值，否则返回表达式自身的值，这里如果表达式错误，则什么也不返回。②sheet A表格辅助B表序号2运用使用嵌套函数使J2=H2&IFERROR（"/"&VLOOKUP（F2，F3：J$1149，5，0），""）并下拉完善J列数据，函数意义与第一点相同，仅更改搜索条件。结果如图3。 第四步：使用公式VLOOKUP 完善sheet B中序号列内容。首先通过G2零部件中文名称&UPC&FNA去sheet A中匹配I列的辅助B表序号1数值，即使用函数H2=VLOOKUP（G2，A！G：I，3，0）并向下拉完善H列数据。sheet B G列单元格若是在sheet A的G列不存在，会返回值“#N/A”。筛选“#N/A”，对这部分数据通过UPC&FNA去sheet A中匹配I列的辅助B表序号2数值，即使用函数H2=VLOOKUP（F2，A！F：J，5，0） 并向下拉完善H列数据。
第五步：筛选sheet B表H列包含“/”的单元格进行数据处理。表中有六行都是507/508/509（sheet A表序号相应内容只有三个，不够B表分配），就分別填写507、508、509，剩下三个单元格留空白;若有两行都是520/521/522，就分别填写520、521。对其他的三十多条数据反复此步操作使H列不含“/”，结果如图4。
第六步：对sheet B表H列单元格内容为空和“#N/A”的进行筛选，该单元格数值为上一个单元格数值加上0.01添加序号以完善sheet B表H列单元格内容。
第七步：运用countif函数，查看sheet A的H列序号是否存在于sheet B的H列序号，结果为1的是两个表格都有的序号，结果为0的仅是sheet A的序号。把结果为0的序号粘贴到sheet B的H列序号最底下空白处。再次运用countif函数，查看sheet B的H列序号是否存在于sheet A的H列序号，把结果为0的序号粘贴到sheet A的H列序号最底下空白处。分别对sheet A/B的H列序号进行升序排序。
第八步，分别把sheet A/B表内容粘贴到汇总比较表格里，最终结果如下图5。至此，两份不同的产品清单相同与差异处一目了然。
3 结语
Excel的函数涉及面广，种类多，功能强大。在产品开发初期，根据需求，灵活使用VLOOKUP及其嵌套函数，对产品开发初期工程零件清单与已量产的产品零件清单进行差异比较，可以提高产品开发初期工程零件清单的准确性及完整性，更好地指导前期财务核算整车成本，大大提高了数据处理的效率。
参考文献：
[1]魏茂林.办公软件应用（第4版）[M].高等教育出版社，2015.11.
[2]张山风.Excel统计函数应用解析[J].办公自动化，2009.11.