我有一个公式,用于遍历动态范围,生成累积行(“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
)
还不确定为什么第一和第二个表格会损坏,因为它们在预览中没有问题..现在正在处理它们..再次提前感谢!
如果担心效率,最好避免
REDUCE
使用和INDEX
之类的迭代方法VSTACK
,因为当迭代次数超过 1,000 时,它们的性能往往很差。作为替代方案,请考虑以下方法:
注意:您分享的示例表似乎表明“计数”是第5列,但您分享的公式将其定义
tally
为第6列。请根据需要调整列号。首先生成总行数。具体方法是使用 计算每条记录收到的总和
MAP
,使用 将结果附加到原始数据集,然后使用HSTACK
删除原始计数CHOOSECOLS
列。接下来,使用&将计数列拆分为一个数组。数组的宽度由包含最大分隔符数量的记录决定。分隔符少于最大值的任何记录都将在其超出的列中填充#N/A 。然后使用将结果发送到删除了错误的单个列(通过将可选的[ignore]参数设置为2)。
TEXTBEFORE
TEXTAFTER
","
TOCOL
然后使用&方法重复原始数据集的行号,然后使用 提取适当的记录。然后使用 、 和操作生成的数据数组,
TOCOL
为包含各个数量的行生成所需的输出。IFS
CHOOSEROWS
CHOOSECOLS
TEXTAFTER
TEXTBEFORE
HSTACK
最后,将数量行附加到总行数
VSTACK
下方,并使用和按行号排序SORTBY
。VSTACK
还用于将标题添加到最终输出。比较一下:当使用 5,000 行数据进行测试时,该方法在一瞬间就返回结果,而
REDUCE
使用INDEX
和则VSTACK
需要大约 20 秒。c
会将您的值拆分tally
为两列,您可以将它们相乘,并使用第一列 (v) 堆叠在乘法下方。接下来 (h) 堆叠引用行的列值。由于这是一个单行数组,而 vstacked 计数计算是多行,因此这会创建一个溢出范围,包括#N/A
。这是我们利用IFNA
来提升索引值的地方。