基于EXCEL函數(shù)實現(xiàn)圖樣明細(xì)的自動分類

段世濤 崔玉娥

摘要：針對設(shè)計過程中頻繁進(jìn)行圖樣明細(xì)分類的工作需要，以EXCEL2003的內(nèi)置函數(shù)VLOOKUP為基礎(chǔ)，配合其他函數(shù)的應(yīng)用，編輯高級函數(shù)公式，在EXCEL中實現(xiàn)圖樣明細(xì)的自動分類，并結(jié)合實際靈活應(yīng)用。

關(guān)鍵詞：EXCEL；函數(shù)；圖樣明細(xì)；自動分類

1引言

在機(jī)械制造行業(yè)，不管是大型企業(yè)還是中小型企業(yè)，圖樣設(shè)計完成后，設(shè)計者都將面對復(fù)雜的圖樣明細(xì)分類。根據(jù)企業(yè)的現(xiàn)實情況，設(shè)計者需要使用EXCEL來完成此項工作。這些繁瑣、低技術(shù)含量的工作，耗費(fèi)了設(shè)計者的大量時間。然而，在實際工作中這些內(nèi)容將不可避免的出現(xiàn)，尤其在設(shè)備備件生產(chǎn)中，基于產(chǎn)品多樣性、小批量生產(chǎn)、生產(chǎn)周期短的特點(diǎn)，快速完成圖樣明細(xì)的分類顯的尤為重要。EXCEL具有強(qiáng)大的數(shù)據(jù)分析與處理功能，基于EXCEL2003的內(nèi)置函數(shù)，編寫函數(shù)公式，無需人工干涉，實現(xiàn)圖樣明細(xì)的自動分類，有效的減少了重復(fù)勞動、提高工作效率。

2公式的編寫

加工明細(xì)表作為總零件明細(xì)表，需要設(shè)計者根據(jù)合同要求逐項填寫或由制圖軟件生成。根據(jù)加工明細(xì)中工藝流程的第一列，確定零件毛坯的工序，將不同工序的零件經(jīng)過篩選，分別填寫到鉚工明細(xì)、鍛造明細(xì)、鑄造明細(xì)、外委明細(xì)、圓料明細(xì)等表項，便完成了圖樣明細(xì)的分類?？偹悸窞椋簭募庸っ骷?xì)工藝流程的第一列中找出所查找的項，再將對應(yīng)項自動輸入到其他明細(xì)即可。

2.1主函數(shù)的應(yīng)用

函數(shù)VLOOKUP：在表格或數(shù)值數(shù)組的首列查找指定的數(shù)值，并由此返回表格或數(shù)組當(dāng)前行中指定列處的數(shù)值。

語法：VLOOKUP（lookup_value，table_array，col_index_num，range_lookup）

Lookup_value為需要在數(shù)組第一列中查找的數(shù)值，即查找目標(biāo)。

Table_array為需要在其中查找數(shù)據(jù)的數(shù)據(jù)表，即查找范圍。

Col_index_num為table_array中待返回的匹配值的列序號，即返回值的列數(shù)。

Range_lookup為一邏輯值，指明函數(shù)VLOOKUP返回時是精確匹配還是近似匹配。

VLOOKUP應(yīng)用如表1所示：

公式說明：

（1）查找目標(biāo)：就是指定的查找的內(nèi)容或單元格引用。本例中“透蓋”就是查找目標(biāo)。

（2）查找范圍：本例中“B2：D4”指定了查找范圍，就是指定從這個范圍內(nèi)進(jìn)行查找。VLOOKUP可以從一個單元格區(qū)域中查找，也可以從一個常量數(shù)組或內(nèi)存數(shù)組中查找。查找范圍要符合以下條件才不會出錯：a.查找目標(biāo)一定要在該范圍的第一列。本例中查找目標(biāo)所對應(yīng)為名稱列，那么B列一定要是查找區(qū)域的第一列，即B2：D4。b.該區(qū)域中一定要包含要返回值所在的列，本例中要返回的值是工序列，一定要包括在這個范圍內(nèi)，即B2：D4。

（3）返回值的列數(shù)：是“返回值”在查找范圍給定的區(qū)域中的列數(shù)。本例中我們要返回的是“工序”，是查找范圍B2：D4的第3列，即為3。

（4）精確查找OR模糊查找：精確查找即完全一樣，模糊查找即包含的意思。如果指定值是0或FALSE就表示精確查找，而指定值為1或TRUE時則表示模糊。

2.2列的互換

函數(shù)VLOOKUP只能從左向右查找，但實際需要根據(jù)工序列查找名稱列，即從右向左查找。只有把區(qū)域列的位置用數(shù)組進(jìn)行互換，才能實現(xiàn)逆向查找。

公式說明：

（1）其實函數(shù)VLOOKUP不可以實現(xiàn)從右至左的查找，而是利用IF函數(shù)的數(shù)組效應(yīng)把兩列換位重新組合后，再按正常的從左至右查找。

（2）IF（{1，0}，D2：D4，B2：B4）完成了列的互換。在EXCEL函數(shù)中使用數(shù)組時，返回的結(jié)果也是一個數(shù)組。這里1和0并不是實際意義上的數(shù)字，而是1相當(dāng)于TRUE，0相當(dāng)于FALSE，當(dāng)為1時，它會返回IF的第一個參數(shù)（D列），為0時返回第二個參數(shù)（B列）。所以使IF（{1，0}，D2：D4，B2：B4）= {D2，B2；D3，B3；D4，B4}={"鑄"，"軸承座"；"鉚"，"透蓋"；"鑄"，"車輪"}。

（3）VLOOKUP支持對數(shù)組的查找，VLOOKUP（“鉚”，{"鑄"，"軸承座"；"鉚"，"透蓋"；"鑄"，"車輪"}，2，0）=透蓋。對于數(shù)組公式，需同時按Ctrl+Shift+Enter結(jié)束。

2.3函數(shù)的多單元格查找

無論查找范圍內(nèi)有多少個查找目標(biāo)，VLOOKUP只能查找到第一個，需要查找到所有目標(biāo)，即要想實現(xiàn)多項查找，就需要對查找目標(biāo)的內(nèi)容進(jìn)行編號，第一個出現(xiàn)的是后面連接1，第二個出現(xiàn)的連接2……實現(xiàn)循環(huán)查找。

公式說明：

（1）函數(shù)ROW：返回引用的行號。語法：ROW（reference）。Reference為需要得到其行號的單元格或單元格區(qū)域。ROW（A1）即為1。如果reference為一個單元格區(qū)域，并且函數(shù)ROW作為垂直數(shù)組輸入，則函數(shù)ROW將reference的行號以垂直數(shù)組的形式返回。ROW（2：4）即為{2；3；4}。

（2）“&”為連接符?！辫T”&ROW（A1）即為”鑄1”。"D2：D"&ROW（2：4）即為{“D2：D2”；”D2：D3”；”D2：D4”}。

（3）函數(shù)INDIRECT：返回由文本字符串指定的引用。此函數(shù)立即對引用進(jìn)行計算，并顯示其內(nèi)容。語法：INDIRECT（ref_text，a1）。Ref_text為對單元格的引用，此單元格可以包含A1-樣式的引用、R1C1-樣式的引用、定義為引用的名稱或?qū)ξ谋咀址畣卧竦囊谩NDIRECT（"D2：D"&ROW（2：4））={D2：D2；D2：D3；D2：D3}。

（4）函數(shù)COUNTIF：計算區(qū)域中滿足給定條件的單元格的個數(shù)。語法：COUNTIF（range，criteria）。Range為需要計算其中滿足條件的單元格數(shù)目的單元格區(qū)域。Criteria為確定哪些單元格將被計算在內(nèi)的條件，其形式可以為數(shù)字、表達(dá)式或文本。COUNTIF（{D2：D2；D2：D3；D2：D3}，“鑄”）= {1；1；2}。

將以上函數(shù)的結(jié)果反饋到主函數(shù)中，則主函數(shù)為：VLOOKUP（“鑄1”，IF（{1，0}，{“鑄1”；”鉚1”；”鑄2”}，B2：B4），2，0）=軸承座。同理當(dāng)查找目標(biāo)為““鑄”&ROW（A2）”時，則返回結(jié)果為“車輪”。

2.4公式的絕對引用和范圍確定

對公式進(jìn)行復(fù)制時，所引用單元格的行和列都會發(fā)生變化，但是查找目標(biāo)不需要改變，就需要進(jìn)行絕對引用。“$”為絕對引用，即在引用的“行號”和“列號”前加上符號（$），就是單元格的絕對引用。

對于不同的圖樣明細(xì)，零件的數(shù)量是不確定的，考慮到一般情況下零件的數(shù)量不會超過一百，且考慮公式的運(yùn)算速度，現(xiàn)將查找范圍設(shè)置為100行。公式確定如下：

=VLOOKUP（“鑄”&ROW（A1）， IF（{1，0}， $D$2：$D$100&COUNTIF（INDIRECT（"D2：D"& ROW（$2：$100）） ，“鑄”），$B$2：$B$100），2，0）。

3公式的應(yīng)用

以鑄造明細(xì)為例，其中A列（序號）、B列（圖號）、C列（零件名稱），D列（單位）、E列（數(shù)量），分別對應(yīng)加工明細(xì)的A、B、C、E、F列。在鑄造明細(xì)A3單元格輸入公式：

=VLOOKUP（"鑄"&ROW（A1），IF（{1，0}，加工明細(xì)表！$G$3：$G$100&COUNTIF（INDIRECT（"加工明細(xì)表！G3：G"&ROW（$3：$100）），"鑄"），加工明細(xì)表！A$3：A$100），2，0）

按Ctrl+Shift+Enter結(jié)束，復(fù)制公式到其他需要自動填寫的單元格內(nèi)，便完成了對鑄造明細(xì)的自動填寫。對沒有查找結(jié)果的單元格，會顯示錯誤#N/A，通過條件格式設(shè)置，使錯誤顯示為白色即可。最終結(jié)果如圖1、圖2所示。

圖1 加工明細(xì)表

圖2 鑄造明細(xì)表

4結(jié)語

本文目的在于總結(jié)日常設(shè)計過程中存在的一些重復(fù)性工作的基礎(chǔ)上，依托軟件的高級應(yīng)用，使其能夠有效地減少重復(fù)勞動、提高效率和準(zhǔn)確性。雖然函數(shù)VLOOKUP存在一定的局限性，但通過與其它函數(shù)的配合并靈活應(yīng)用，就能實現(xiàn)高級的運(yùn)用，使得原本重復(fù)而繁瑣的分類工作高效化，為設(shè)計者節(jié)省時間。

作者簡介：段世濤（1986-），男，助理工程師，主要從事冶金機(jī)械的設(shè)計與研發(fā)工作。