盧海玲　蔣樟英

摘 要：本文主要介紹了用EXCEL相關(guān)函數(shù)及其嵌套使用對不同的汽車產(chǎn)品工程零件清單進(jìn)行匯總比較。通過比較，可以清晰的看到清單的相同與差異處，對產(chǎn)品開發(fā)初期工程零件清單的準(zhǔn)確性及完整性以及前期財(cái)務(wù)核算整車成本有重要指導(dǎo)意義。

關(guān)鍵詞：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

汽車的工程零件清單是由整車特征的所有沿用件、修改件以及新開發(fā)零件構(gòu)成。在開發(fā)一款新產(chǎn)品時(shí)，工程零件清單的準(zhǔn)確性及完整性對項(xiàng)目階段的造車與財(cái)務(wù)成本核算等極為重要。本文主要介紹了在項(xiàng)目開發(fā)的初期，通過把新開發(fā)車型與已量產(chǎn)車型零件清單的關(guān)鍵信息放同一表格的相同或相近位置進(jìn)行比較的方法。

1 工程零件清單關(guān)鍵信息的介紹

統(tǒng)一零部件代碼（Uniform Parts Classification），它是規(guī)定了整車功能模塊的代碼。功能名稱地址代碼 （Functional Name Address），由5位字符/字母組成的代碼，它表示了一個(gè)零件用法的以一個(gè)名詞和功能修飾詞語進(jìn)行修飾的描述。UPC必須與FNA一起使用，每一個(gè)FNA必須與UPC一塊使用才會產(chǎn)生零件名稱。零部件號則是零部件本身的代碼。

2 產(chǎn)品工程零件清單差異比較

首先按需求篩選出需要進(jìn)行比較的新開發(fā)車型與已量產(chǎn)車型的工程零件清單，把需要比較的關(guān)鍵信息列粘貼到新EXCEL表格A與B。對sheet A/B表添加輔助列名稱UPC&FNA（F列），用公式F2 =C2&D2并向下拉完成F列內(nèi)容填充。此時(shí)，行號相同的兩表格數(shù)據(jù)是不一樣的（圖1圖2），無法比較兩份清單的差異。

接下來，使用“自定義排序”功能對數(shù)據(jù)進(jìn)行初始排序。在sheet A/B表格表頭的UPC&FNA下三角選擇“自定義排序”并以 “主要關(guān)鍵字”為UPC&FNA、“次要關(guān)鍵字”為零部件中文名稱進(jìn)行排序。

2.1 產(chǎn)品工程零件清單差異比較方法一：插入功能

第一步：把sheet A/B兩表初步排序后的結(jié)果分別復(fù)制至匯總比較表格。

第二步：對匯總表格的數(shù)據(jù)進(jìn)行逐行的核對比較。若零部件中文名稱與UPC&FNA不同，則選擇差異的零件并通過右鍵→活動單元格下移插入單元格，使下一個(gè)相同的零部件中文名稱與UPC&FNA行在同一行。兩零件清單有1000多行，此方法核對數(shù)據(jù)需要反復(fù)插入操作多次以實(shí)現(xiàn)兩表數(shù)據(jù)差異化可目視比較。

2.2 產(chǎn)品工程零件清單差異比較方法二：使用EXCEL函數(shù)

添加輔助列，自定義sheet A的序號，使用excel函數(shù)vlookup，把sheet A的序號VLOOKUP到sheet B序號中，利用數(shù)字升序進(jìn)行排序使得sheet A/B兩表格數(shù)據(jù)排序后，相同或相近內(nèi)容處于同一行以實(shí)現(xiàn)產(chǎn)品工程零件清單差異比較。

第一步：為提高兩個(gè)表格直接數(shù)據(jù)的匹配度，先對sheet A/B表添加兩個(gè)輔助， 分別命名為零部件中文名稱&UPC&FNA（G列）、序號（H列）。

第二步：sheet A/B表分別使用合并單元格公式使UPC&FNA&零部件中文名稱單元格G2=B2&C2&D2并向下拉完成G列內(nèi)容填充;sheetA序號列單元格H2填寫數(shù)字1并向下拉，選擇“填充序列”模式，使該列單元格數(shù)值為遞增模式1、2、3、4、5……

第三步：使用countif公式對G列進(jìn)行計(jì)算，可以看到sheet A 表G列值相同時(shí)，H列會有一個(gè)或者多個(gè)值。VLOOKUP是一個(gè)查找函數(shù)，給定一個(gè)查找的目標(biāo)，它從指定的查找區(qū)域返回想要查找到的第一值。G列值相同多個(gè)序號值在sheet B的序號引用時(shí)都只引用了第一個(gè)值，不利于數(shù)據(jù)處理。所以再添加兩輔助列并分別命名為輔助B表序號1（I列）、輔助B表序號2（J列）。 ①sheet A表格輔助B表序號1運(yùn)用嵌套函數(shù)使I2=H2&IFERROR（"/"&VLOOKUP（G2，G3：I$1149，3，0），""）并下拉完善I列數(shù)據(jù)，I2單元格值為數(shù)據(jù)是G2對應(yīng)H列的序號H2以及H3往后中G列單元格內(nèi)容與G2相同的序號值并用“/”分隔。其中，函數(shù)VLOOKUP=（Lookup_value，Table_array，Col_index_num，Range_lookup），Lookup_value為需要在數(shù)據(jù)表首列進(jìn)行搜索的數(shù)值即G列，Table_array為需要在其中搜索數(shù)據(jù)的信息表即G3：I列數(shù)據(jù)表最底行，Col_index_num為滿足條件的單元格在數(shù)組區(qū)域table_array中的序列號，I列是G列后的第三列，所以數(shù)值為3，Range_lookup數(shù)值0即表示精確匹配;IFERROR=（Value，Value_if_error），即如果表達(dá)式錯(cuò)誤，則返回Value_if_error值，否則返回表達(dá)式自身的值，這里如果表達(dá)式錯(cuò)誤，則什么也不返回。②sheet A表格輔助B表序號2運(yùn)用使用嵌套函數(shù)使J2=H2&IFERROR（"/"&VLOOKUP（F2，F(xiàn)3：J$1149，5，0），""）并下拉完善J列數(shù)據(jù)，函數(shù)意義與第一點(diǎn)相同，僅更改搜索條件。結(jié)果如圖3。

第四步：使用公式VLOOKUP 完善sheet B中序號列內(nèi)容。首先通過G2零部件中文名稱&UPC&FNA去sheet A中匹配I列的輔助B表序號1數(shù)值，即使用函數(shù)H2=VLOOKUP（G2，A！G：I，3，0）并向下拉完善H列數(shù)據(jù)。sheet B G列單元格若是在sheet A的G列不存在，會返回值“#N/A”。篩選“#N/A”，對這部分?jǐn)?shù)據(jù)通過UPC&FNA去sheet A中匹配I列的輔助B表序號2數(shù)值，即使用函數(shù)H2=VLOOKUP（F2，A！F：J，5，0） 并向下拉完善H列數(shù)據(jù)。

第五步：篩選sheet B表H列包含“/”的單元格進(jìn)行數(shù)據(jù)處理。表中有六行都是507/508/509（sheet A表序號相應(yīng)內(nèi)容只有三個(gè)，不夠B表分配），就分別填寫507、508、509，剩下三個(gè)單元格留空白;若有兩行都是520/521/522，就分別填寫520、521。對其他的三十多條數(shù)據(jù)反復(fù)此步操作使H列不含“/”，結(jié)果如圖4。

第六步：對sheet B表H列單元格內(nèi)容為空和“#N/A”的進(jìn)行篩選，該單元格數(shù)值為上一個(gè)單元格數(shù)值加上0.01添加序號以完善sheet B表H列單元格內(nèi)容。

第七步：運(yùn)用countif函數(shù)，查看sheet A的H列序號是否存在于sheet B的H列序號，結(jié)果為1的是兩個(gè)表格都有的序號，結(jié)果為0的僅是sheet A的序號。把結(jié)果為0的序號粘貼到sheet B的H列序號最底下空白處。再次運(yùn)用countif函數(shù)，查看sheet B的H列序號是否存在于sheet A的H列序號，把結(jié)果為0的序號粘貼到sheet A的H列序號最底下空白處。分別對sheet A/B的H列序號進(jìn)行升序排序。

第八步，分別把sheet A/B表內(nèi)容粘貼到匯總比較表格里，最終結(jié)果如下圖5。至此，兩份不同的產(chǎn)品清單相同與差異處一目了然。

3 結(jié)語

Excel的函數(shù)涉及面廣，種類多，功能強(qiáng)大。在產(chǎn)品開發(fā)初期，根據(jù)需求，靈活使用VLOOKUP及其嵌套函數(shù)，對產(chǎn)品開發(fā)初期工程零件清單與已量產(chǎn)的產(chǎn)品零件清單進(jìn)行差異比較，可以提高產(chǎn)品開發(fā)初期工程零件清單的準(zhǔn)確性及完整性，更好地指導(dǎo)前期財(cái)務(wù)核算整車成本，大大提高了數(shù)據(jù)處理的效率。

參考文獻(xiàn)：

[1]魏茂林.辦公軟件應(yīng)用（第4版）[M].高等教育出版社，2015.11.

[2]張山風(fēng).Excel統(tǒng)計(jì)函數(shù)應(yīng)用解析[J].辦公自動化，2009.11.