PMC工具箱：采购员必备，精准查询报价单的实用方法

PMC工具箱：采购员必备，精准查询报价单的实用方法

在采购工作中，如何快速准确地从报价单中查询到所需产品的报价信息？本文将详细介绍如何使用Excel函数来实现这一目标，包括数据标准化处理、多条件查询以及动态数组公式的高级应用。

大家好，我是古老师。今天将继续为大家介绍PMC工具箱系列的内容。我们将探讨采购员如何根据客户提供的报价单，迅速查询到相应的报价。例如，在本例中的报价单明细表是一个二维表格。水平方向表示产品的重量区间，如“0-2kg”、“2-5kg”、“5-10kg”等；垂直方向则列出不同产品，如A、B、C等。交叉区域显示的是对应产品的报价详情。若要在查询表中输入两个条件，比如产品B和重量3kg，表格中能返回对应的报价金额，例如9.79元。应该如何设置函数公式呢？

设计思路

根据用户提供的数据案例分析，此案例需求可归类为“查找与引用类”问题。具体来说，就是在一维查询表中，对查询区域内的二维数据进行索引。解决这一问题的关键在于确定查询表中两个条件在二维表中垂直方向和水平方向上的位置。

由于客户提供的数据及查询信息不够标准，因此需先对数据进行标准化处理。例如，报价表中水平方向的重量范围（如“0-2kg”）是以文本形式给出的，需要将其转换为数值形式。同样，查询表中的重量值（如“3kg”）也以文本形式提供，同样需要转换为数值。完成转换后，通过判断查询条件在行和列中的位置，即可快速定位到所需的报价信息。

水平方向

先进行水平方向的标准化，在合适位置录入公式把水平方向的文本转成数字：

--TEXTBEFORE($B$2:$F$2,"-")

公式解释：

• TEXTBEFORE 函数用于从指定的文本串中提取位于特定字符之前的子串。在这个例子中，它用于提取单元格中连字符 - 之前的部分。
• 而公式中的“--”是对提取的结果进行强制类型转换，目的是将文本转换为数字。

垂直方向

在完成水平方向的标准化后，在适当的位置输入以下公式，将垂直方向的文本转换为数字：

=--TEXTBEFORE(I3,"kg")

公式解释：

• TEXTBEFORE 函数用于从指定的文本串中提取位于特定字符（或字符串）之前的子串。在这里，它用于提取单元格中“kg”之前的部分。
• 公式中的“--”用于将提取出的文本结果转换为数字。这样可以确保后续计算或查询操作能够正确执行。

矩阵位置

有了垂直和水平方向的数字后，可以用MATCH函数定位，录入公式：

=MATCH(--TEXTBEFORE(I3,"kg"),--TEXTBEFORE($B$2:$F$2,"-"))+1

函数解释：

• TEXTBEFORE(I3, "kg") 从单元格 I3 中提取“kg”之前的部分。
• --TEXTBEFORE(I3, "kg") 将提取出的文本转换为数字。
• TEXTBEFORE($B$2:$F$2, "-") 从水平方向的单元格区域中提取连字符 - 之前的部分。
• --TEXTBEFORE($B$2:$F$2, "-") 将提取出的文本数组转换为数字数组。
• MATCH 函数用于在指定的数组中查找某个值的位置。第三个参数 0 表示查找完全匹配的值。
• • 1 是因为 MATCH 函数返回的位置是从 1 开始的，但有时可能需要调整位置索引以适应特定的需求。这里加 1 是为了确保最终结果符合预期的索引位置。

这样，你就可以通过这个公式找到垂直方向和水平方向的交点，从而获取对应的报价信息。

查找引用

最后一步是使用查找与引用函数。在适当的位置输入以下公式：

=VLOOKUP(H3,$A$3:$F$8,MATCH(--TEXTBEFORE(I3,"kg"),--TEXTBEFORE($B$2:$F$2,"-"))+1,0)

公式解释：

• VLOOKUP 函数用于在一个表格中查找某一行的值，并返回该行中指定列的值。
• 第一个参数 H3 是要查找的值，即产品名称。
• 第二个参数 $A$3:$F$8 是包含数据的表格区域。
• 第三个参数 MATCH(--TEXTBEFORE(I3, "kg"), --TEXTBEFORE($B$2:$F$2, "-"), 0) + 1 用于确定返回哪一列的值。
• 第四个参数0 表示要求精确匹配。

动态数组

上面的公式是需要用户进行填充公式和锁定单元格才能实现最终的效果，如果不想进行填充公式或锁定单元格，可以用动态数组公式：

=MAP(MATCH(H4:H6,A3:A8,),XMATCH(--TEXTBEFORE(I4:I6,"kg"),--TEXTBEFORE(B2:F2,"-"),-1),LAMBDA(X,Y,INDEX(B3:F8,X,Y)))

公式解释：

• MAP 函数用于对两个数组中的每个元素应用一个函数，并返回一个新的数组。
• 第一个参数MATCH(H4:H6, A3:A8, 0) 用于在垂直方向上查找产品名称的位置。
• H4:H6 是要查找的产品名称列表。
• A3:A8 是包含产品名称的列。
• 0 表示要求精确匹配。
• 第二个参数XMATCH(--TEXTBEFORE(I4:I6, "kg"), --TEXTBEFORE(B2:F2, "-"), 0) 用于在水平方向上查找重量范围的位置。
• I4:I6 是要查找的重量值列表。
• --TEXTBEFORE(I4:I6, "kg") 将重量值中的“kg”前的部分转换为数字。
• B2:F2 是包含重量范围的行。
• --TEXTBEFORE(B2:F2, "-") 将重量范围中的连字符 - 前的部分转换为数字。
• 0 表示要求精确匹配。
• 第三个参数LAMBDA(X, Y, INDEX(B3:F8, X, Y)) 是一个匿名函数，用于根据查找结果返回对应的报价。
• X 和 Y 分别代表 MATCH 和 XMATCH 返回的行号和列号。
• INDEX(B3:F8, X, Y) 从数据区域 B3:F8 中返回指定行和列的值。

通过这个动态数组公式，你可以自动获取多个产品和重量范围对应的报价，而无需手动填充公式或锁定单元格。

最后总结

通过上述步骤，我们详细介绍了如何利用Excel函数来解决采购员在处理客户报价单时遇到的查找与引用问题。首先，通过对水平和垂直方向的数据进行标准化处理，将文本形式的重量范围和产品名称转换为数值，确保了后续查找操作的准确性。接着，使用 MATCH 函数确定了查询条件在二维表中的具体位置，从而实现了对报价信息的精确定位。最后，通过 VLOOKUP 和 INDEX 函数的组合，或者使用动态数组公式 MAP 和 LAMBDA，实现了高效、准确的报价查询功能。

这种方法不仅简化了数据处理流程，提高了工作效率，还避免了手动输入带来的错误。特别是动态数组公式的使用，使得多条件查询更加灵活和便捷，无需逐一填充公式或锁定单元格，极大地提升了用户体验。希望这些方法能够帮助采购员更高效地管理报价单，提高工作质量和客户满意度。