基于EXCEL函數實現圖樣明細的自動分類

段世濤 崔玉娥

摘要：針對設計過程中頻繁進行圖樣明細分類的工作需要，以EXCEL2003的內置函數VLOOKUP為基礎，配合其他函數的應用，編輯高級函數公式，在EXCEL中實現圖樣明細的自動分類，并結合實際靈活應用。

關鍵詞：EXCEL；函數；圖樣明細；自動分類

1引言

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

2公式的編寫

加工明細表作為總零件明細表，需要設計者根據合同要求逐項填寫或由制圖軟件生成。根據加工明細中工藝流程的第一列，確定零件毛坯的工序，將不同工序的零件經過篩選，分別填寫到鉚工明細、鍛造明細、鑄造明細、外委明細、圓料明細等表項，便完成了圖樣明細的分類。總思路為：從加工明細工藝流程的第一列中找出所查找的項，再將對應項自動輸入到其他明細即可。

2.1主函數的應用

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

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

Lookup_value為需要在數組第一列中查找的數值，即查找目標。

Table_array為需要在其中查找數據的數據表，即查找范圍。

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

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

VLOOKUP應用如表1所示：

公式說明：

（1）查找目標：就是指定的查找的內容或單元格引用。本例中“透蓋”就是查找目標。

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

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

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

2.2列的互換

函數VLOOKUP只能從左向右查找，但實際需要根據工序列查找名稱列，即從右向左查找。只有把區域列的位置用數組進行互換，才能實現逆向查找。

公式說明：

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

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

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

2.3函數的多單元格查找

無論查找范圍內有多少個查找目標，VLOOKUP只能查找到第一個，需要查找到所有目標，即要想實現多項查找，就需要對查找目標的內容進行編號，第一個出現的是后面連接1，第二個出現的連接2……實現循環查找。

公式說明：

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

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

（3）函數INDIRECT：返回由文本字符串指定的引用。此函數立即對引用進行計算，并顯示其內容。語法：INDIRECT（ref_text，a1）。Ref_text為對單元格的引用，此單元格可以包含A1-樣式的引用、R1C1-樣式的引用、定義為引用的名稱或對文本字符串單元格的引用。INDIRECT（"D2：D"&ROW（2：4））={D2：D2；D2：D3；D2：D3}。

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

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

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

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

對于不同的圖樣明細，零件的數量是不確定的，考慮到一般情況下零件的數量不會超過一百，且考慮公式的運算速度，現將查找范圍設置為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公式的應用

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

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

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

圖1 加工明細表

圖2 鑄造明細表

4結語

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

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