主页 / coding / 问题 / 78985538
Accepted
RtMt
Asked: 2024-09-14 23:51:02 +0800 CST

Excel - 使用数组公式（MAP、BYROW、CHOOSECOLS、TEXTJOIN、LAMBDA、FILTER、HSTACK）在单个单元格中动态聚合表中的值

  • 772

我想聚合、合并、压缩一些值，如下所示

输入是一个表，输出是单个单元格上的动态数组公式，目标是根据第一列的唯一值聚合其他列

我的输入是一个表格（名为TAB）

姓名 服务 类型
02HPP002NZ 1：基本 unix
02HPP002NZ 1：基本 unix
02HPP002NZ 3. 相关 赢
02HPP009O4 3. 相关 nt
02HPP001L7 2：标准 unix
02HPP001L7 2：标准 Linux的
02HPP009O4 1：基本 nt

在单个单元格 E2 上使用数组公式实现所需输出

姓名 服务 类型
02HPP002NZ 1：基础 / 3：相关 Unix / WIN
02HPP009O4 3：相关 / 1：基础 nt
02HPP001L7 2：标准 unix / linux

我使用的公式如下：

=LET(
    Names; TAB[Name];
    uNames; UNIQUE(Names);
    aggfunc; LAMBDA(colIdx; MAP(uNames; LAMBDA(x; BYCOL(CHOOSECOLS(TAB;colIdx); LAMBDA(col;TEXTJOIN(" / "; TRUE; UNIQUE(FILTER(col; Names=x))))))));
    HSTACK(
        uNames;
        aggfunc(2);
        aggfunc(3)
    )
)

正如您所注意到的，我硬编码了“aggfunc(x)”（x 是我要返回的表格的列号）。我想使用单个函数aggfunc(numCols)，numCols是列数的数组。公式应该如下所示，但不幸的是它不起作用。它给出错误“ #CALC！ ”

=LET(
    Names; TAB[Name];
    uNames; UNIQUE(Names);
    numCols; SEQUENCE(COLUMNS(TAB[#Data]));
    aggfunc; LAMBDA(colIdx; MAP(uNames; LAMBDA(x; BYCOL(CHOOSECOLS(TAB;colIdx); LAMBDA(col;TEXTJOIN(" / "; TRUE; UNIQUE(FILTER(col; Names=x))))))));
    HSTACK(
        uNames;
        aggfunc(numCols)
    )
)

使用 BYROW 代替 MAP 的替代公式

=LET(
     a; TAB[Name];
     b; TAB[Type];
     c; TAB[Service];
     ua; UNIQUE(a);
     HSTACK(
       ua;
       BYROW(ua; LAMBDA(x; TEXTJOIN(" / ";TRUE;UNIQUE(FILTER(b;a=x)))));
       BYROW(ua; LAMBDA(x; TEXTJOIN(" / ";TRUE;UNIQUE(FILTER(c;a=x)))))
     )
 )

我也尝试过使用 PowerQuery，但使用 group by 然后添加自定义列，但由于我有多个列，因此实现我的目标非常痛苦。如果有人可能感兴趣，请按照以下步骤操作：

  1. 选择表格中的任意单元格
  2. 选择菜单数据，然后从表/范围（在左侧）
  3. 它打开PowerQuery 编辑器，您现在需要选择列Name（第一列）
  4. 在菜单Transform中，选择Group By。打开一个新窗口，我填写了如下所示的字段
  • 新列名：AGGNAME
  • 手术：All Rows
  • 单击“确定”
  1. 现在您有一张包含两列的表格，标题分别为名称和 AGGNAME（表格）
  2. 在菜单“添加列”中，选择“自定义列”。打开一个新窗口，我输入以下值
  • 新列名：Type
  • 自定义列公式：=Text.Combine(List.Transform(List.Distinct([AGGNAME][#"Type"]), Text.From), " / ")
  1. 您现在有 3 列“名称”、“AGGNAME”和“类型”。
  2. 您需要重复步骤 6 并Type根据您的列的名称更改名称（在公式中）。
  3. 插入所有需要的新列后，必须删除AGGNAME列
  4. 现在转到菜单主页并单击“关闭并加载”。它将创建一个具有与我需要相同的输出的新表。

如果输入表的名称发生变化或者表有多个列，使用 PowerQuery 就会变得很痛苦。

我的 Office 365 版本尚不包含功能“GROUPBY” （版本 2406 内部版本 16.0.17726.20206） 64 位。

非常感谢您抽出时间，如果您需要更多信息，请随时询问。问候，T。

excel

6 个回答

  1. 尝试这个公式：

    =LET(
    n,Tab[Name],nUnique,UNIQUE(n),
    agg, LAMBDA(colIdx,n_,TEXTJOIN("/",TRUE,UNIQUE(FILTER(CHOOSECOLS(Tab,colIdx),n=n_)))),
    DROP(
        REDUCE("",nUnique,LAMBDA(r,n_,VSTACK(r,HSTACK(n_,agg(2,n_),agg(3,n_)))))
        ,1)
    )
    • 2

  2. 更新

    =LET(
    aggFunc; LAMBDA(tbl; criteriaSpec; retCols;
        LET(
            header; TAKE(tbl; 1);
            data; DROP(tbl; 1);
            criteriaSpec; TEXTSPLIT(criteriaSpec; "=");
            title; INDEX(criteriaSpec; 1; 1);
            toFind; INDEX(criteriaSpec; 1; 2);
            titleCol; XMATCH(title; header);
            criteria; IF(
                toFind = "";
                SEQUENCE(ROWS(data); ; TRUE; 0);
                CHOOSECOLS(data; titleCol) = toFind
            );
            uFound; UNIQUE(FILTER(CHOOSECOLS(data; titleCol); criteria));
            aggCol; LAMBDA(col; TEXTJOIN(" / "; TRUE; UNIQUE(col)));
            aggForTitle; LAMBDA(acc; titleVal;
                VSTACK(
                    acc;
                    LET(
                        filtered; FILTER(
                            CHOOSECOLS(data; retCols);
                            CHOOSECOLS(data; titleCol) = titleVal
                        );
                        HSTACK(titleVal; BYCOL(filtered; aggCol))
                    )
                )
            );
            REDUCE(
                CHOOSECOLS(header; titleCol; retCols);
                uFound;
                aggForTitle
            )
        )
    );
    aggFunc(TAB_34[#All]; "Name="; {2; 3})
)

    更新结果

    您收到#CALC!错误，因为从 返回了嵌套数组MAP。因此，稍微修改一下，aggFunc使用 累积结果VSTACK，并使用 堆叠结果REDUCE：

    替换,为;，因此如果发现任何错误，请更正。

    =LET(
    Names; TAB[Name];
    uNames; UNIQUE(Names);
    numCols; SEQUENCE(COLUMNS(TAB[#Data]) - 1; ; 2);
    aggfunc; LAMBDA(acc; name;
        VSTACK(
            acc;
            HSTACK(
                BYCOL(
                    CHOOSECOLS(TAB; numCols);
                    LAMBDA(col;
                        TEXTJOIN(
                            " / ";
                            TRUE;
                            UNIQUE(FILTER(col; Names = name))
                        )
                    )
                )
            )
        )
    );
    HSTACK(
        VSTACK(CHOOSECOLS(TAB[#Headers]; 1); uNames);
        REDUCE(CHOOSECOLS(TAB[#Headers]; numCols); uNames; aggfunc)
    )
)

    已更新 2024-09-15

    =LET(
    Names, TAB_5[Name],
    uNames, UNIQUE(Names),
    numCols, SEQUENCE(COLUMNS(TAB_5[#Data]) - 1, , 2),
    aggCol, LAMBDA(col, TEXTJOIN(" / ", TRUE, UNIQUE(col))),
    aggfunc, LAMBDA(acc, name,
        VSTACK(
            acc,
            LET(
                filtered, FILTER(CHOOSECOLS(TAB_5, numCols), Names = name),
                HSTACK(BYCOL(filtered, aggCol))
            )
        )
    ),
    HSTACK(
        VSTACK(CHOOSECOLS(TAB_5[#Headers], 1), uNames),
        REDUCE(CHOOSECOLS(TAB_5[#Headers], numCols), uNames, aggfunc)
    )
)
    • 2

  3. 检索唯一过滤列

    • 结果与 OP 相同，另外Names在左侧增加了一个额外的唯一（）列，即，如果目标是堆叠所有列，则不再需要堆叠唯一的列。
    • 第一行包含参数，然后是两个辅助函数和唯一名称。最后，您的聚合函数接受单个参数，期望返回列的数组。

    掉进兔子洞

    =LET(data,TAB,uniques,TAB[Name],cols,SEQUENCE(COLUMNS(data)),delim," / ",
    GetCell,LAMBDA(cdata,cfilter,delim,BYCOL(cdata,LAMBDA(c,
        TEXTJOIN(delim,,UNIQUE(FILTER(c,cfilter)))))),
    GetCol,LAMBDA(data,udata,cdata,delim,
        MAP(udata,LAMBDA(m,GetCell(cdata,data=m,delim)))),
    u,UNIQUE(uniques),
    aggfunc,LAMBDA(cols,DROP(REDUCE("",cols,LAMBDA(cc,c,
        HSTACK(cc,GetCol(uniques,u,CHOOSECOLS(data,c),delim)))),,1)),
    HSTACK(u,aggfunc(cols)))

    数据和结果截图

    • 2

  4. 看起来您可以使用：

    截屏

    公式E2：

    =GROUPBY(A2:A8,B2:C8,LAMBDA(x,TEXTJOIN(" / ",,UNIQUE(x))),,0)

    注意服务和类型是如何分组的。

    • 2
  5. Best Answer

    已经有很多不错的答案了。另一种方法是使用 MAKERRAY：

    =LET(uNames,UNIQUE(Table1[Name]),
     MAKEARRAY(ROWS(uNames),COLUMNS(Table1),
        LAMBDA(rw,cl,
               ARRAYTOTEXT(UNIQUE(FILTER(INDEX(Table1,,cl),Table1[Name]=INDEX(uNames,rw)))))))

    PS 我使用了表格。请将其更改为 TAB 以供参考。我无法使用 Excel 的应用程序版本重命名表格名称

    此外，如果您希望将分隔符更改为/ARRAYTOTEXT 以 TEXTJOIN。

    • 2

  6. 根据@nkalvi的建议，我改进了公式，使其更加灵活（我添加了更多列

    灵活版本

    K1电池上的公式是：

        =LAMBDA(TAB;Criteria;retCol1;
LET(
    Title;CHOOSECOLS(TEXTSPLIT(Criteria;"=");1);
    iTitle; MATCH(Title; TAB[#Headers];0);
    ToFind;CHOOSECOLS(TEXTSPLIT(Criteria;"=");2);
    Names;CHOOSECOLS(TAB;iTitle);
    uNames; IF(ToFind="";UNIQUE(Names);ToFind);
    numCols; MATCH(retCol1; TAB[#Headers];0);
    aggfunc; LAMBDA(acc;name;
        VSTACK(
            acc;
            HSTACK(
                BYCOL(
                    CHOOSECOLS(TAB; numCols);
                    LAMBDA(col;TEXTJOIN(" / ";TRUE;UNIQUE(SORT(FILTER(col; Names = name))))))))
    );
    HSTACK(
        VSTACK(CHOOSECOLS(TAB[#Headers]; iTitle); uNames);
        REDUCE(CHOOSECOLS(TAB[#Headers]; numCols); uNames; aggfunc)
    )
))(TAB;I2;G2#)

    现在，以下是创建“命名公式”的一些步骤：

    1. 转到菜单“公式”，单击“名称管理器”，然后单击按钮“新建...”
    2. 插入“COMPACT”作为名称
    3. 复制上述除(TAB;I2;G2#)之外的所有代码并将其粘贴到“引用：”字段 + 确定 + 关闭

    现在，如果将公式放在=COMPACT(TAB;I2;G2#)单元格K2上（结果显示在屏幕截图中。

    参数解释：

    • 第一：表的名称
    • 第二个：条件（[Column_Name] = [Value_to_find]，如果省略 [Value_to_find]，则它将返回与 Column_Name 相关的所有唯一值
    • 第三：是要返回的列名称范围

    PS：在公式K9中，我使用了=COMPACT(TAB;I8;G8#)但是我需要包装所有公式以避免重复，所以它变成=UNIQUE(COMPACT(TAB;I8;G8#))

    谢谢大家，如果您有优化公式的想法，我们将欢迎您:)

    • 0

相关问题