段世濤 崔玉娥

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

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

1引言

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

2公式的編寫(xiě)

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

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

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

語(yǔ)法：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中待返回的匹配值的列序號(hào)，即返回值的列數(shù)。

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

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

公式說(shuō)明：

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

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

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

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

2.2列的互換

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

公式說(shuō)明：

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

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

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

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

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

公式說(shuō)明：

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

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

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

（4）函數(shù)COUNTIF：計(jì)算區(qū)域中滿(mǎn)足給定條件的單元格的個(gè)數(shù)。語(yǔ)法：COUNTIF（range，criteria）。Range為需要計(jì)算其中滿(mǎn)足條件的單元格數(shù)目的單元格區(qū)域。Criteria為確定哪些單元格將被計(jì)算在內(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）”時(shí)，則返回結(jié)果為“車(chē)輪”。

2.4公式的絕對(duì)引用和范圍確定

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

對(duì)于不同的圖樣明細(xì)，零件的數(shù)量是不確定的，考慮到一般情況下零件的數(shù)量不會(huì)超過(guò)一百，且考慮公式的運(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列（序號(hào)）、B列（圖號(hào)）、C列（零件名稱(chēng)），D列（單位）、E列（數(shù)量），分別對(duì)應(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ù)制公式到其他需要自動(dòng)填寫(xiě)的單元格內(nèi)，便完成了對(duì)鑄造明細(xì)的自動(dòng)填寫(xiě)。對(duì)沒(méi)有查找結(jié)果的單元格，會(huì)顯示錯(cuò)誤#N/A，通過(guò)條件格式設(shè)置，使錯(cuò)誤顯示為白色即可。最終結(jié)果如圖1、圖2所示。

圖1 加工明細(xì)表

圖2 鑄造明細(xì)表

4結(jié)語(yǔ)

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

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