使用带有动态数组的 BYROW 作为公式输入

Using BYROW with dynamic arrays as formula inputs

提问人：Michael Suchy 提问时间：10/27/2023 最后编辑：Notus_PandaMichael Suchy 更新时间：10/29/2023 访问量：162

问：

我在 excel 表中有一列，其中包含以下公式：=ARRAYTOTEXT(HSTACK(MAKEARRAY(1,[@[Liquidation Lag]],LAMBDA(r,c,0)),TAKE(VALUE(TEXTSPLIT([@[Default Vector]],", ")),,[@[Months to Project]]-[@[Liquidation Lag]])))

在另一个工作表上，我想使用 BYROW 动态溢出一个数组，该数组提取公式的 ARRAYTOTEXT 部分中的嵌入数组并按列溢出它。这是我尝试使用的公式：

=BYROW(lossModel_tbl[Liquidation Vector],LAMBDA(r,VALUE(TEXTSPLIT(r,", "))))

我尝试了各种迭代，包括makearray，sequence，index等，我总是想出任何一个 #calc！或者 #value！哎呀。
任何建议将不胜感激。

excel excel 公式 动态数组

评论

2赞 Ike 10/27/2023
如果您提供一些示例数据和预期的输出，这将对您有所帮助，最好使用 Markdown 表格生成器（阅读此内容以获取解释）
1赞 P.b 10/27/2023
因此，您想要一个公式来溢出前一个的结果，或者您需要前一个公式来返回溢出范围？无论如何，我们需要一些数据来复制。
1赞 Scott Craner 10/27/2023
=DROP(REDUCE("",lossModel_tbl[Liquidation Vector],LAMBDA(s,r,VSTACK(s,VALUE(TEXTSPLIT(r,", ")))),1)
0赞 Michael Suchy 10/28/2023
下面是一个示例： 第一行表行值：“1， 2， 3， 4， 5” 第二行表行值：“0， 0， 0， 0， 0， 0， 0， 10， 0” 我希望我的溢出数组（按行）返回溢出数组（按列），这些数组将 “， ” 分隔的文本字符串转换为数组。因此，对这两个文本行进行 byrow 函数的结果将是一个溢出的数组，如下所示： C1 C2 C3 C4 C5 C6 C7 C8 C9 r1 1 2 3 4 5 0 0 0 0 0 r2 0 0 0 0 0 0 0 10 0
0赞 Michael Suchy 10/28/2023
对不起这里的格式，我是使用这些论坛的菜鸟。

答：

0赞 user22566114 10/28/2023 #1

=NUMBERVALUE（IFNA（DROP（REDUCE（“”，SEQUENCE（ROWS（A1：A4）），LAMBDA（x，y，HSTACK（x，TOCOL（TEXTSPLIT（INDEX（A1：A4，y），“，”)))))，，1），“0”））

如果我正确理解您要做什么，此公式将返回预期结果。 BYROW 无法应用，因为它不能同时水平和垂直溢出。

Using REDUCE with dynamic arrays as formula inputs

评论

0赞 Michael Suchy 10/28/2023
这非常接近！谢谢。我将不得不尝试解开这个！为了获得我所寻求的输出，我所做的唯一更改是转置整个事情，所以我只是将整个事情包装在转置中，因为我必须弄清楚这是如何工作的！
0赞 Michael Suchy 10/28/2023
这个最终成为我一直在寻找的赢家。非常聪明，再次感谢您的帮助。=IFNA（DROP（REDUCE（0，SEQUENCE（loanCount），LAMBDA（x，y，VSTACK（x，VALUE（TEXTSPLIT（INDEX（lossModel_tbl[损失向量]，y），“，”)))))，1），0）
2赞 DjC 10/28/2023 #2

免責聲明：由于 Excel 中递归 lambda 的限制，以下解决方案中概述的方法只能用于小型数据集。

对于任何内置的动态数组函数，如 ，如果对函数的每次迭代执行的计算都生成一个结果数组，它将返回错误，因为不支持“嵌套数组”。BYROW()#CALC!

作为解决方法，您通常会找到的固定解决方案是使用 with 和/或 的某种变体。然而，这种方法可能难以理解，甚至更难适应复杂的场景。REDUCE()VSTACK()HSTACK()

另一种方法是在名称管理器中创建一个自定义函数，其行为与 完全相同，但具有能够“堆叠”嵌套数组结果的额外好处。为此，请打开名称管理器 （Ctrl + F3） 并使用以下公式定义一个名为 STACKBYROW 的新名称：LAMBDA()BYROW()

=LAMBDA(array,function,[initial_value],[start],[pad_with],
   LET(
   n, IF(ISOMITTED(start), 1, start),
   f, function(INDEX(array, n, )),
   v, IF(ISOMITTED(initial_value), f, IFNA(VSTACK(initial_value, f), pad_with)),
   IF(n<ROWS(array), STACKBYROW(array, function, v, n+1, pad_with), v)))

现在可以完全按照您的预期在工作簿中使用该函数：STACKBYROW()BYROW()

=STACKBYROW(lossModel_tbl[Liquidation Vector], LAMBDA(r, VALUE(TEXTSPLIT(r, ", "))))

如果结果生成“交错数组”，则默认情况下，空值将用零 （0） 填充。如果需要，使用可选的 [pad_with] 参数用您选择的值填充它们，例如空字符串 （“”）：

=STACKBYROW(lossModel_tbl[Liquidation Vector], LAMBDA(r, VALUE(TEXTSPLIT(r, ", "))),,, "")

至于上面评论部分中介绍的其他示例，您可以使用：

=STACKBYROW(A1:A2, LAMBDA(r, --TEXTSPLIT(r, ", ")),,, "")

stackbyrow.png

BONUS 示例（复杂性增加）：

为了证明 的灵活性，我们可以用它来解决 Access Analytic 的 Wyn Hopkins 在 2022 年 11 月提出的“票证列表挑战”：STACKBYROW()

ticket_list_challenge.png

溶液：

=STACKBYROW(tblTicket, LAMBDA(r,
LET(
   n, INDEX(r, 1),
   v, --TEXTSPLIT(INDEX(r, 3),"-"),
   m, MIN(v),
   IFERROR(HSTACK(n, SEQUENCE(MAX(v)-m+1,, m)), n))),
{"Name","Ticket Number"})

结果：

ticket_list_solution.png

评论

0赞 Notus_Panda 10/31/2023
"这种方法可能难以理解“，继续创建自定义 Lambda 函数。：'） 不过很棒的解决方案。不知道为什么 OP 提到希望将其分成几行，但随后又像另一个答案 ̄_（ツ）_/ ̄ 一样获得转置结果 ̄_（ツ）_/ ̄
0赞 DjC 10/31/2023
@Notus_Panda哈哈，我听到了。这绝对更像是一个“你知道这是可能的”帖子，而不是一个可行的解决方案，因为它只能处理大约 1000 行数据。我考虑过删除它，但保留了它，因为它的高极客因素和书的吸引力。;-)

上一个：Python 动态数组列表

下一个：如何在Excel中创建一个动态数组，该数组是多个动态数组的串联？