全文约1600字

大家好，我是古老师。今天我将带领大家创建一个《供应商报价分析》数据表。我们将使用从ERP系统导出的《供应商送货明细表》作为数据源。这份表单是一份标准的二维数据表格，其中A1到G1单元格包含了标题行，列标题分别为：送货日期、送货单号、商品全名、单位、送货数量和单价金额等；而A2到G2000单元格则填充了与这些标题相对应的具体信息。

现在我们需要对商品进行报价分析，找出每种商品最近五次的报价，并按照升序排列显示。为了清晰地展示这些数据，我们将使用二维表格的形式，其中：

纵轴（行）代表商品全名；

横轴（列）代表单价，具体包括最近五次的报价，分别命名为“单价1”、“单价2”……直至“单价5”。

每个单元格将显示对应商品的相应报价信息。这样，我们就可以直观地比较不同商品在最近几次的报价变化。

效果如下图所示：

聚合分析

对于从ERP系统导出的一维数据进行分析时，如果数据是以二维表格形式呈现（即包含行和列），那么最佳的分析方法之一是使用聚合函数来进行汇总分析。这里我们将使用GROUPBY函数来对单价进行聚合汇总：

=GROUPBY(C2:D1570,F2:F1570,ARRAYTOTEXT,,0)

公式解释：
参数1：行字段：C2:D1570,为商品全名列，作为分组依据，确保每个商品只出现一次。；

参数2：值字段：F2:F1570,为单价列，这是我们需要进行聚合分析的数据

参数3：函数：ARRAYTOTEXT，对单价进行文本合并，并用逗号进行分隔。

参数5到6，省略或0，表示不保留原始数据中的标头，也不在结果中添加总计行；

返回前五

聚合分析的数据是返回所有有送货日期对应的单价，这里的分析只保留5个单价，并且单价是需要升序排列后的结果。所以需要对聚合后的单价进行数据分列并升序，然后保留最近5次记录，再边上建立一列辅助列，并录入以下公式进行填充：

=TOROW(TAKE(SORT(--TEXTSPLIT(K2,,",")),5))

公式解释：

TEXTSPLIT(K2, ",")：将单元格K2中的文本字符串根据逗号分隔符拆分成多个元素。

--：双重负号用于将文本形式的数字转换为实际数值，以便可以对其进行数学运算和排序。

SORT(...,)：对上述拆分并转换后的数值列表进行升序排序。

TAKE(..., 5)：从排序后的数组中提取前五个元素。如果数组中的元素少于五个，则返回所有可用元素

TOROW(...)：将垂直数组转换为水平数组（行），以便横向展示这五个价格

一键分析

如果对数据需要建模并且不希望创建太多辅助列的公式，可以使用以下公式进行一键聚合动态扩展分析：

=DROP(LET(A,GROUPBY(C2:D10000,F2:F10000,ARRAYTOTEXT,,0),HSTACK(TAKE(A,,2),IFNA(DROP(REDUCE("",TAKE(A,,-1),LAMBDA(X,Y,VSTACK(X,TOROW(TAKE(SORT(--TEXTSPLIT(Y,,",")),5))))),1),""))),-1)

公式解释：

LET 函数：用于定义变量，使公式更简洁。这里定义了变量 A，其值为 GROUPBY 函数的结果

TAKE(A, , 2)：从 A 中提取前两列，即商品全名和送货日期

REDUCE("", TAKE(A, , -1), ...)：遍历 A 的最后一列（即通过 GROUPBY 聚合后的文本合并列），并对其应用一个操作，该操作将每项转换为升序排列的前五个数值（或所有可用数值，如果少于五个）。

LAMBDA(X, Y, ...)：这是一个匿名函数，用于定义如何处理每一项 Y，即对每个商品的合并单价字符串进行分割、转换为数值、排序并提取前五名。

VSTACK(X, TOROW(...))：将处理过的数值（已排序并转换为行向量）垂直堆叠到累积结果 X 上。

IFNA(..., "")：处理可能产生的错误（如缺少数据的情况），用空字符串代替错误值。

HSTACK(...)：水平组合商品信息列和处理后的价格信息列。

DROP(..., -1)：移除最后多余的行，确保最终结果仅包含有效数据。

最后总结

通过上述步骤，我们完成了从ERP系统导出的《供应商送货明细表》到《供应商报价分析》数据表的转换。这个过程不仅简化了数据分析的复杂度，还提高了效率和准确性。使用GROUPBY函数进行初步的数据聚合，可以有效地将分散的单价信息集中起来，并为后续处理打下基础。进一步地，通过对聚合后的单价数据进行分列、排序和筛选，确保了我们能够精确地捕捉到每种商品最近五次的报价变动，这有助于识别价格趋势和异常情况。

特别是对于那些希望避免创建过多辅助列的用户，一键聚合动态扩展分析公式提供了一种简洁而强大的解决方案。该公式结合了多个高级WPS函数，如LET、REDUCE、LAMBDA等，实现了对数据的一键式处理，大大减少了手动操作的时间成本。同时，它保证了即使在面对大规模数据集时也能保持高效运行，从而使得报价分析更加灵活且易于维护。