產品工程零件清單差異比較方法

盧海玲　蔣樟英

摘 要：本文主要介紹了用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.