☰

扩展 Excel 中动态数组的结果

tag-icon • tag-icon microsoft-excel
扩展 Excel 中动态数组的结果

我在 Office365 中有一个 Excel 电子表格，我在其中使用 FILTER() 动态数组从外部数据填充父/子表。它为我提供了以下信息（元素和阶段）：

element1    A
element2    ABC
element3    BC

为了展示和进一步分析，我希望将信息扩展为以下格式：

element1    A
element2    A
element2    B
element2    C
element3    B
element3    C

由于数据的不规则性，就父/子对的数量而言，我希望完全坚持使用动态数组（而不是将公式拖到工作表的底部，并在没有匹配项时使用消隐 IF 方案）。

我曾经做过一个工作，需要生成给定点以上相位长度的（非动态）总和，然后为每行赋予第一个最接近的较大元素的值，但 Excel 崩溃后，我无法返回那里提供示例。我想要的可能吗，还是我看错了？

编辑： ={INDEX(dynamicRangeCell#,MATCH(TRUE,ABClengthCumulativeSumRange>ROW(),0))}是非动态方法，但我无法让它与动态方法一起工作

答案1

这对于公式来说不是一件容易的事情：

=LET(
    rng,C1#,
    ch,INDEX(rng,0,2),
    pa,INDEX(rng,0,1),
    HSTACK(
         INDEX(pa,MATCH(SEQUENCE(SUM(LEN(ch)),,0),VSTACK(0,SCAN(0,LEN(ch),LAMBDA(a,b,a+b))))),
         LET(n,TOCOL(TEXTSPLIT(TEXTJOIN(";",,BYROW(ch,LAMBDA(c,IFERROR(REDUCE(c,SEQUENCE(,LEN(c)-1,LEN(c),-1),LAMBDA(a,b,REPLACE(a,b,0,"|"))),c)))),"|",";")),FILTER(n,NOT(ISERROR(n))))))

它基本上创建了两个数组，第一列的列表按第二列的字母数量扩展，第二列按字母扩展。

第一个只是在第二列中创建字符串长度的运行总和并使用匹配的问题。我们需要附加0到该运行总和数组的开头以从匹配中获得正确的返回。

其次，我们|在单元格中的每个字母之间以及;单元格之间创建文本字符串。然后我们使用 TEXTSPLIT 将字符串拆分为其自己的数组，然后使用 TOCOL 将其堆叠到一列中。

然后我们使用 HSTACK 将它们排列得整齐美观。

在此处输入图片描述

答案2

如果您没有一些较新的 O365 功能，也许这会对您有用：

=LET(α,INDEX(C1#,,1),β,INDEX(C1#,,2),γ,SEQUENCE(ROWS(β)),δ,LEN(β),ε,SEQUENCE(SUM(δ)),CHOOSE({1,2},XLOOKUP(ε,MMULT(N(γ>=TRANSPOSE(γ)),δ),α,,1),MID(CONCAT(β),ε,1)))

再次假设您的溢出范围被引用C1#。

相关内容