我有一个公式，用于遍历动态范围，生成累积行（“Tally”中的#/#代表件数和英尺长度）并将它们堆叠到新表中。

正在处理的数据采用以下格式：

订单号 订单行项目 库存单位 容器 相符 角色
12345 54321 PNE16系列 24680 5/8、10/16 2

数据目前通过以下公式处理成以下格式：

收到数量 订单号 po_item 股票代码 容器 ID 录制进程
200 12345 54321 PNE16系列 24680 2
40 12345 54321 PNE16s.8 24680 2
160 12345 54321 PNE16s.16 24680 2

我正在尝试稍微改变 received_qty 的计算方式，如下所示：

收到数量 订单号 po_item 股票代码 容器 ID 录制进程
200 12345 54321 PNE16系列 24680 2
5 12345 54321 PNE16s.8 24680 2
10 12345 54321 PNE16s.16 24680 2

我正在努力思考如何改变累积行生成以适应这种情况。我知道我基本上需要这样做，IF(qty = 0, return qty * len, ELSE return qty但我实际上并不确定如何将其输入。任何提示或帮助都将不胜感激！“po_info”是另一张表的索引，它将容纳原始数据（减去标题行），因为我只是在下面的公式中定义它们。

=LET(
    data, po_info,
    row_indices, SEQUENCE(ROWS(data)),
    result, REDUCE(
        TEXTSPLIT("RECEIVED_QTY,PO_NUMBER,PO_ITEM,STOCK_CODE,CONTAINER_ID,REC_PROCESS", ","),
        row_indices,
        LAMBDA(acc_res,cur_row,
            LET(
                PO_NUMBER, INDEX(data, cur_row, 1),
                PO_ITEM, INDEX(data, cur_row, 2),
                STOCK_CODE, INDEX(data, cur_row, 3),
                CONTAINER_ID, INDEX(data, cur_row, 4),
                REC_PROCESS, INDEX(data, cur_row, 5),
                tally, INDEX(data, cur_row, 6),
                qty_lengths, TEXTSPLIT(TRIM(tally), ","),
                result_0, REDUCE(
                    "cumulative_row",
                    qty_lengths,
                    LAMBDA(acc,cur,
                        LET(
                            qty_len, TEXTSPLIT(cur, "/"),
                            qty, INDEX(qty_len, , 1),
                            len, INDEX(qty_len, , 2),
                            row_, HSTACK(
                                qty * len,
                                PO_NUMBER,
                                PO_ITEM,
                                CONCAT(STOCK_CODE, ".", len),
                                "" & CONTAINER_ID,
                                REC_PROCESS
                            ),
                            VSTACK(acc, row_)
                        )
                    )
                ),
                cumulative_row, HSTACK(
                    SUM(INDEX(result_0, , 1)),
                    PO_NUMBER,
                    PO_ITEM,
                    STOCK_CODE,
                    CONCAT(CONTAINER_ID,),
                    REC_PROCESS
                ),
                result, VSTACK(cumulative_row, DROP(result_0, 1)),
                VSTACK(acc_res, result)
            )
        )
    ),
    result
)

还不确定为什么第一和第二个表格会损坏，因为它们在预览中没有问题..现在正在处理它们..再次提前感谢！

1

  • 表格是正确的，但您需要在文本和表格之间留一个空行才能正确显示。虽然预览可以毫无问题地显示表格，但最终帖子不允许这样做。


    – 


最佳答案
2

如果担心效率，最好避免REDUCE使用和INDEX之类的迭代方法VSTACK，因为当迭代次数超过 1,000 时，它们的性能往往很差。

作为替代方案，请考虑以下方法：

=LET(
    data, po_info,
    rowId, SEQUENCE(ROWS(data)),
    tally, CHOOSECOLS(data, 5),
    total_received, MAP(tally, LAMBDA(v, LET(a, TEXTSPLIT(v, "/", ","), SUM(TAKE(a,, 1) * DROP(a,, 1))))),
    total_rows, HSTACK(total_received, CHOOSECOLS(data, 1, 2, 3, 4, 6)),
    tally_count, LEN(tally) - LEN(SUBSTITUTE(tally, ",", )) + 1,
    tally_cols, SEQUENCE(, MAX(tally_count)),
    tallys, TOCOL(TEXTBEFORE(TEXTAFTER("," & tally & ",", ",", tally_cols), ","), 2),
    rowIds, TOCOL(IFS(tally_count >= tally_cols, rowId), 2),
    data_array, CHOOSEROWS(data, rowIds),
    stock_code, CHOOSECOLS(data_array, 3) & "." & TEXTAFTER(tallys, "/"),
    qty_received, --TEXTBEFORE(tallys, "/"),
    qty_rows, CHOOSECOLS(HSTACK(qty_received, data_array, stock_code), 1, 2, 3, 8, 5, 7),
    VSTACK(
        {"RECIEVED_QTY","PO_NUMBER","PO_ITEM","STOCK_CODE","CONTAINER_ID","REC_PROCESS"},
        SORTBY(VSTACK(total_rows, qty_rows), VSTACK(rowId, rowIds))
    )
)

注意：您分享的示例表似乎表明“计数”是第5列，但您分享的公式将其定义tally为第6列。请根据需要调整列号。

首先生成总行数。具体方法是使用 计算每条记录收到的总和MAP，使用 将结果附加到原始数据集，然后使用HSTACK删除原始计数CHOOSECOLS列。

接下来，使用&将计数列拆分为一个数组。数组的宽度由包含最大分隔符数量的记录决定。分隔符少于最大值的任何记录都将在其超出的列中填充#N/A 。然后使用将结果发送到删除了错误的单个列（通过将可选的[ignore]参数设置为2）。TEXTBEFORETEXTAFTER","TOCOL

然后使用&方法重复原始数据集的行号，然后使用 提取适当的记录。然后使用 、 和操作生成的数据数组，TOCOL为包含各个数量的行生成所需的输出。IFSCHOOSEROWSCHOOSECOLSTEXTAFTERTEXTBEFOREHSTACK

最后，将数量行附加到总行数VSTACK下方，并使用和按行号排序SORTBY。VSTACK还用于将标题添加到最终输出。

比较一下：当使用 5,000 行数据进行测试时，该方法在一瞬间就返回结果，而REDUCE使用INDEX和则VSTACK需要大约 20 秒。

=LET(range,A2:F3,
REDUCE({"recieved_qty","po_number","po_item","stock_code","container_id","rec_process"},
       SEQUENCE(ROWS(range)),
LAMBDA(a,b,
       LET(c,--TEXTSPLIT(INDEX(range,b,5),"/",","), 
       VSTACK(a,
              IFNA(HSTACK(
                          VSTACK(SUM(TAKE(c,,1)*DROP(c,,1)),
                                 TAKE(c,,1)),
                          INDEX(range,b,{1,2}),
                          INDEX(range,b,3)&VSTACK("","."&DROP(c,,1)),
                          INDEX(range,b,{4,6})),
                   INDEX(range,b,{0,1,2,3,4,6})))))))

c会将您的值拆分tally为两列，您可以将它们相乘，并使用第一列 (v) 堆叠在乘法下方。接下来 (h) 堆叠引用行的列值。由于这是一个单行数组，而 vstacked 计数计算是多行，因此这会创建一个溢出范围，包括#N/A。这是我们利用IFNA来提升索引值的地方。