Início / coding / Perguntas / 78985538
Accepted
RtMt
Asked: 2024-09-14 23:51:02 +0800 CST

Excel - Agregar valores dinamicamente em uma tabela usando fórmulas de matriz (MAP, BYROW, CHOOSECOLS, TEXTJOIN, LAMBDA, FILTER, HSTACK) em uma única célula

  • 772

Gostaria de agregar, mesclar, compactar alguns valores conforme mostrado abaixo

A entrada é uma tabela e a saída são fórmulas de matriz dinâmicas em uma única célula e tem como objetivo agregar outras colunas com base em valores exclusivos da primeira coluna

Minha entrada é uma tabela (chamada TAB)

Nome Serviço Tipo
02HPP002NZ 1:Básico unix
02HPP002NZ 1:Básico unix
02HPP002NZ 3: Relevante ganhar
02HPP009O4 3: Relevante não
02HPP001L7 2: Padrão unix
02HPP001L7 2: Padrão Linux-Linux
02HPP009O4 1:Básico não

Saída desejada usando fórmulas de matriz em uma única célula E2

Nome Serviço Tipo
02HPP002NZ 1:Básico / 3:Relevante unix / win
02HPP009O4 3: Relevante / 1: Básico não
02HPP001L7 2: Padrão unix/linux

A fórmula que usei está abaixo:

=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)
    )
)

Como você percebeu, eu hardcodei "aggfunc(x)" (x é o número da coluna da minha tabela a ser retornada). Eu gostaria de usar uma única função aggfunc(numCols)e numCols é uma matriz do número de colunas. E a fórmula deveria ser como a seguinte, mas infelizmente não funciona. Ela dá um erro " #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)
    )
)

Uma fórmula alternativa que usa BYROW em vez de 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)))))
     )
 )

Também tentei usar o PowerQuery, mas usando group by e depois adicionar colunas personalizadas, mas como tenho várias colunas, é muito difícil atingir meu objetivo. Se for do interesse de alguém, abaixo estão os passos:

  1. Selecione qualquer célula na tabela
  2. Selecione o menu Dados e depois Da Tabela/Intervalo (no lado esquerdo)
  3. Ele abre o Editor do PowerQuery , agora você precisa selecionar a coluna Name(a primeira coluna)
  4. No menu Transformar , selecione Agrupar por . Uma nova janela é aberta, e preencho os campos conforme mostrado abaixo
  • Novo nome da coluna:AGGNAME
  • Operação:All Rows
  • Clique em OK
  1. Agora você tem uma tabela com duas colunas com títulos Nome e AGGNAME (Tabela)
  2. No menu Adicionar coluna , selecione Coluna personalizada . Uma nova janela é aberta e eu insiro os seguintes valores
  • Novo nome da coluna:Type
  • Fórmula de coluna personalizada:=Text.Combine(List.Transform(List.Distinct([AGGNAME][#"Type"]), Text.From), " / ")
  1. Agora você tem 3 colunas "Nome", "AGGNAME" e "Tipo".
  2. Você precisa repetir o Passo 6 e alterar o nome Type(na fórmula) de acordo com o nome da sua coluna.
  3. Depois de inserir todas as novas colunas necessárias, você deve excluir a coluna AGGNAME
  4. Agora vá ao menu Home e clique em Close & Load . E ele criará uma nova tabela com a mesma saída que eu preciso.

Usar o PowerQuery se torna uma tarefa difícil se os nomes da tabela de entrada mudam ou se a tabela tem várias colunas.

Minha versão do Office 365 ainda não inclui a função "GROUPBY" (Versão 2406 Build 16.0.17726.20206) 64 bits .

Muito obrigado pelo seu tempo e, por favor, não hesite em perguntar se precisar de mais informações. Atenciosamente, T.

excel

6 respostas

  1. Experimente esta fórmula:

    =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. Atualizado

    =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})
)

    Resultado atualizado

    Você está recebendo #CALC!erro porque matrizes aninhadas são retornadas de MAP. Então, modificando levemente o aggFuncpara acumular o resultado com VSTACK, e empilhando os resultados com REDUCE:

    Substituído ,por ;, portanto, corrija se houver algum erro.

    =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)
    )
)

    Atualizado em 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. Recuperar colunas filtradas exclusivas

    • O resultado é o mesmo do OP, mais uma Namescoluna única ( ) à esquerda, ou seja, se o objetivo é empilhar todas as colunas, você não precisa mais empilhar a única.
    • A primeira linha contém os parâmetros, depois seguem duas funções auxiliares e os nomes exclusivos. Finalmente, sua função agregada pega como um único parâmetro esperando que uma matriz das colunas seja retornada.

    Descendo a Toca do Coelho

    =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)))

    Captura de tela dos dados e do resultado

    • 2

  4. Parece que você pode usar:

    captura de tela

    Fórmula em E2:

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

    Observe como tanto o serviço quanto o tipo são agrupados.

    • 2
  5. Best Answer

    Muitas respostas legais já. Outra abordagem poderia ser usar MAKEARRAY:

    =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 Eu usei Tabelas. Altere-as para TAB para sua referência. Não consigo renomear nomes de tabelas usando a versão do aplicativo do Excel

    Além disso, se você quiser que o separador seja alterado, /altere ARRAYTOTEXT para TEXTJOIN.

    • 2

  6. Com base na sugestão de @nkalvi, melhorei a fórmula para torná-la mais flexível (adicionei mais colunas

    Versão flexível

    A fórmula na célula 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#)

    Agora, aqui estão alguns passos para criar uma "Fórmula nomeada":

    1. Vá ao menu "Fórmulas", clique em "Gerenciador de Nomes" e depois clique no botão "Novo..."
    2. Insira "COMPACTO" como nome
    3. Copie todos os códigos acima, exceto (TAB;I2;G2#) e cole-os no campo "Refere-se a:" + OK + Fechar

    Agora, se você colocar a fórmula =COMPACT(TAB;I2;G2#)na célula K2 (o resultado é mostrado na captura de tela.

    Explicação dos parâmetros:

    • 1º: nome da Tabela
    • 2º: Critérios ([Nome_da_coluna]=[Valor_a_encontrar], se [Valor_a_encontrar] for omitido, ele retornará todos os valores exclusivos relacionados ao Nome_da_coluna
    • 3º: é um intervalo de nomes das colunas a serem retornadas

    PS: na fórmula K9 eu uso =COMPACT(TAB;I8;G8#)mas preciso envolver toda a fórmula para evitar duplicatas, então fica=UNIQUE(COMPACT(TAB;I8;G8#))

    Obrigado a todos e se vocês tiverem alguma ideia para otimizar a fórmula, serão bem-vindos :)

    • 0

relate perguntas