主页 / coding / 问题 / 79411860
Accepted
mgmussi
Asked: 2025-02-04 21:25:21 +0800 CST

使用 Excel 公式从预制表创建动态嵌套列表（分为 3 个级别）

  • 772

我正在尝试将三级嵌套列表公式从 Google Sheet改编到 Excel。

由于某种原因，这比预期的要难。

无论我做什么，都会#CALC!出错。我必须适应的一件事是空的TOCOL(;1)，因为它会产生#VALUE!。我还必须为该IFNA()函数包含一条错误消息。

目前，公式如下所示：

=TOCOL(REDUCE(TOCOL("";1);TOCOL(Macroetapas!B2:B1000;1); LAMBDA(a;item;VSTACK(a;item; REDUCE(TOCOL("";1); IFNA(FILTER(Macroatividades!B2:B1000;Macroatividades!C2:C1000=item);"N/A error"); LAMBDA(b;subitem; VSTACK(b;subitem;IFNA(FILTER(SCRUM!B2:B1000;SCRUM!J2:J1000=subitem);"N/A error")))))));1)

就上下文而言，我的电子表格的组织方式如下：

我有一份物品清单

大号！A2:A1000 大号！B2:B1000
1 第 1 项
2 第 2 项
3 第 3 项

子项目列表

宏观活动！B2:B1000 宏观活动！C2:C1000
子项 1 第 1 项
子项 2 第 1 项
子项 3 第 2 项
子项 4 第 3 项
子项 5 第 3 项

以及子项列表

SCRUM！B2:B1000 SCRUM！J2:J1000
子项 a 子项 1
子项 b 子项 1
子项 c 子项 3
子项 d 子项 3
子项 子项 5

我的结果应该是：

结果
第 1 项
子项 1
子项 a
子项 b
子项 2
第 2 项
子项 3
子项 c
子项 d
第 3 项
子项 4
子项 5
子项
excel

2 个回答

  1. Best Answer

    以下是在 Excel 中适用的公式：

    =LET(arr;REDUCE("";TOCOL(B2:B1000;1);LAMBDA(a;item;VSTACK(a;item;
    REDUCE("";FILTER(C2:C1000;D2:D1000=item;"");LAMBDA(b;subitem;
      VSTACK(b;subitem;FILTER(E2:E1000;F2:F1000=subitem;"")))))));FILTER(arr;arr<>""))

    更短的公式

    =LET(lam;LAMBDA(x;y;z;BYROW(x;LAMBDA(w;TEXTJOIN(";";TRUE;w;FILTER(y;z=w;"")))));
TEXTSPLIT(TEXTJOIN(";";TRUE;lam(B2:B100;lam(C2:C100;E2:E100;F2:F100);D2:D100));;";"))
    • 2

  2. 我认为不同的方法可能更有效：

    =LET(c,CHOOSECOLS,
     f,FILTER(Macroatividades!B2:C1000,ISNUMBER(XMATCH(Macroatividades!C2:C1000,TO COL(Macroetapas!B2:B1000,1)))),
UNIQUE(
       TOCOL(HSTACK(c(f,2,1),
             IFS(TOROW(SCRUM!J2:J1000,1)=c(f,1),
                 TOROW(SCRUM!B2:B1000,1))),
             2)))

    首先过滤与Macroatividades中的项目匹配的 B 列和 C 列值Macroetapas，然后将其与与过滤结果中的子项目匹配的子子项目数组堆叠（如果匹配）或（#N/A如果不匹配）则出现错误。

    如果将此堆叠数组包装在 TOCOL 中，则会跳过错误值，您会得到结果，但这些项在每个子项之前重复。因此，这被包装在 unique 中，以仅列出第一次出现。

    需要注意的是，这要求 sub sub 和 sub 项必须是唯一的。否则唯一会过滤掉第 n 次出现的情况。

    • 1

相关问题