基于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-），男，助理工程师，主要从事冶金机械的设计与研发工作。