盧海玲 蔣樟英
摘 要:本文主要介紹了用EXCEL相關(guān)函數(shù)及其嵌套使用對(duì)不同的汽車(chē)產(chǎn)品工程零件清單進(jìn)行匯總比較。通過(guò)比較,可以清晰的看到清單的相同與差異處,對(duì)產(chǎn)品開(kāi)發(fā)初期工程零件清單的準(zhǔn)確性及完整性以及前期財(cái)務(wù)核算整車(chē)成本有重要指導(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
汽車(chē)的工程零件清單是由整車(chē)特征的所有沿用件、修改件以及新開(kāi)發(fā)零件構(gòu)成。在開(kāi)發(fā)一款新產(chǎn)品時(shí),工程零件清單的準(zhǔn)確性及完整性對(duì)項(xiàng)目階段的造車(chē)與財(cái)務(wù)成本核算等極為重要。本文主要介紹了在項(xiàng)目開(kāi)發(fā)的初期,通過(guò)把新開(kāi)發(fā)車(chē)型與已量產(chǎn)車(chē)型零件清單的關(guān)鍵信息放同一表格的相同或相近位置進(jìn)行比較的方法。
1 工程零件清單關(guān)鍵信息的介紹
統(tǒng)一零部件代碼(Uniform Parts Classification),它是規(guī)定了整車(chē)功能模塊的代碼。功能名稱(chēng)地址代碼 (Functional Name Address),由5位字符/字母組成的代碼,它表示了一個(gè)零件用法的以一個(gè)名詞和功能修飾詞語(yǔ)進(jìn)行修飾的描述。UPC必須與FNA一起使用,每一個(gè)FNA必須與UPC一塊使用才會(huì)產(chǎn)生零件名稱(chēng)。零部件號(hào)則是零部件本身的代碼。
2 產(chǎn)品工程零件清單差異比較
首先按需求篩選出需要進(jìn)行比較的新開(kāi)發(fā)車(chē)型與已量產(chǎn)車(chē)型的工程零件清單,把需要比較的關(guān)鍵信息列粘貼到新EXCEL表格A與B。對(duì)sheet A/B表添加輔助列名稱(chēng)UPC&FNA(F列),用公式F2 =C2&D2并向下拉完成F列內(nèi)容填充。此時(shí),行號(hào)相同的兩表格數(shù)據(jù)是不一樣的(圖1圖2),無(wú)法比較兩份清單的差異。
接下來(lái),使用“自定義排序”功能對(duì)數(shù)據(jù)進(jìn)行初始排序。在sheet A/B表格表頭的UPC&FNA下三角選擇“自定義排序”并以 “主要關(guān)鍵字”為UPC&FNA、“次要關(guān)鍵字”為零部件中文名稱(chēng)進(jìn)行排序。
2.1 產(chǎn)品工程零件清單差異比較方法一:插入功能
第一步:把sheet A/B兩表初步排序后的結(jié)果分別復(fù)制至匯總比較表格。
第二步:對(duì)匯總表格的數(shù)據(jù)進(jìn)行逐行的核對(duì)比較。若零部件中文名稱(chēng)與UPC&FNA不同,則選擇差異的零件并通過(guò)右鍵→活動(dòng)單元格下移插入單元格,使下一個(gè)相同的零部件中文名稱(chēng)與UPC&FNA行在同一行。兩零件清單有1000多行,此方法核對(duì)數(shù)據(jù)需要反復(fù)插入操作多次以實(shí)現(xiàn)兩表數(shù)據(jù)差異化可目視比較。
2.2 產(chǎn)品工程零件清單差異比較方法二:使用EXCEL函數(shù)
添加輔助列,自定義sheet A的序號(hào),使用excel函數(shù)vlookup,把sheet A的序號(hào)VLOOKUP到sheet B序號(hào)中,利用數(shù)字升序進(jìn)行排序使得sheet A/B兩表格數(shù)據(jù)排序后,相同或相近內(nèi)容處于同一行以實(shí)現(xiàn)產(chǎn)品工程零件清單差異比較。
第一步:為提高兩個(gè)表格直接數(shù)據(jù)的匹配度,先對(duì)sheet A/B表添加兩個(gè)輔助, 分別命名為零部件中文名稱(chēng)&UPC&FNA(G列)、序號(hào)(H列)。
第二步:sheet A/B表分別使用合并單元格公式使UPC&FNA&零部件中文名稱(chēng)單元格G2=B2&C2&D2并向下拉完成G列內(nèi)容填充;sheetA序號(hào)列單元格H2填寫(xiě)數(shù)字1并向下拉,選擇“填充序列”模式,使該列單元格數(shù)值為遞增模式1、2、3、4、5……
第三步:使用countif公式對(duì)G列進(jìn)行計(jì)算,可以看到sheet A 表G列值相同時(shí),H列會(huì)有一個(gè)或者多個(gè)值。VLOOKUP是一個(gè)查找函數(shù),給定一個(gè)查找的目標(biāo),它從指定的查找區(qū)域返回想要查找到的第一值。G列值相同多個(gè)序號(hào)值在sheet B的序號(hào)引用時(shí)都只引用了第一個(gè)值,不利于數(shù)據(jù)處理。所以再添加兩輔助列并分別命名為輔助B表序號(hào)1(I列)、輔助B表序號(hào)2(J列)。 ①sheet A表格輔助B表序號(hào)1運(yùn)用嵌套函數(shù)使I2=H2&IFERROR("/"&VLOOKUP(G2,G3:I$1149,3,0),"")并下拉完善I列數(shù)據(jù),I2單元格值為數(shù)據(jù)是G2對(duì)應(yīng)H列的序號(hào)H2以及H3往后中G列單元格內(nèi)容與G2相同的序號(hào)值并用“/”分隔。其中,函數(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為滿(mǎn)足條件的單元格在數(shù)組區(qū)域table_array中的序列號(hào),I列是G列后的第三列,所以數(shù)值為3,Range_lookup數(shù)值0即表示精確匹配;IFERROR=(Value,Value_if_error),即如果表達(dá)式錯(cuò)誤,則返回Value_if_error值,否則返回表達(dá)式自身的值,這里如果表達(dá)式錯(cuò)誤,則什么也不返回。②sheet A表格輔助B表序號(hào)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中序號(hào)列內(nèi)容。首先通過(guò)G2零部件中文名稱(chēng)&UPC&FNA去sheet A中匹配I列的輔助B表序號(hào)1數(shù)值,即使用函數(shù)H2=VLOOKUP(G2,A!G:I,3,0)并向下拉完善H列數(shù)據(jù)。sheet B G列單元格若是在sheet A的G列不存在,會(huì)返回值“#N/A”。篩選“#N/A”,對(duì)這部分?jǐn)?shù)據(jù)通過(guò)UPC&FNA去sheet A中匹配I列的輔助B表序號(hào)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表序號(hào)相應(yīng)內(nèi)容只有三個(gè),不夠B表分配),就分別填寫(xiě)507、508、509,剩下三個(gè)單元格留空白;若有兩行都是520/521/522,就分別填寫(xiě)520、521。對(duì)其他的三十多條數(shù)據(jù)反復(fù)此步操作使H列不含“/”,結(jié)果如圖4。
第六步:對(duì)sheet B表H列單元格內(nèi)容為空和“#N/A”的進(jìn)行篩選,該單元格數(shù)值為上一個(gè)單元格數(shù)值加上0.01添加序號(hào)以完善sheet B表H列單元格內(nèi)容。
第七步:運(yùn)用countif函數(shù),查看sheet A的H列序號(hào)是否存在于sheet B的H列序號(hào),結(jié)果為1的是兩個(gè)表格都有的序號(hào),結(jié)果為0的僅是sheet A的序號(hào)。把結(jié)果為0的序號(hào)粘貼到sheet B的H列序號(hào)最底下空白處。再次運(yùn)用countif函數(shù),查看sheet B的H列序號(hào)是否存在于sheet A的H列序號(hào),把結(jié)果為0的序號(hào)粘貼到sheet A的H列序號(hào)最底下空白處。分別對(duì)sheet A/B的H列序號(hào)進(jìn)行升序排序。
第八步,分別把sheet A/B表內(nèi)容粘貼到匯總比較表格里,最終結(jié)果如下圖5。至此,兩份不同的產(chǎn)品清單相同與差異處一目了然。
3 結(jié)語(yǔ)
Excel的函數(shù)涉及面廣,種類(lèi)多,功能強(qiáng)大。在產(chǎn)品開(kāi)發(fā)初期,根據(jù)需求,靈活使用VLOOKUP及其嵌套函數(shù),對(duì)產(chǎn)品開(kāi)發(fā)初期工程零件清單與已量產(chǎn)的產(chǎn)品零件清單進(jìn)行差異比較,可以提高產(chǎn)品開(kāi)發(fā)初期工程零件清單的準(zhǔn)確性及完整性,更好地指導(dǎo)前期財(cái)務(wù)核算整車(chē)成本,大大提高了數(shù)據(jù)處理的效率。
參考文獻(xiàn):
[1]魏茂林.辦公軟件應(yīng)用(第4版)[M].高等教育出版社,2015.11.
[2]張山風(fēng).Excel統(tǒng)計(jì)函數(shù)應(yīng)用解析[J].辦公自動(dòng)化,2009.11.