Início / user-7816778

RtMt's questions

Martin Hope
RtMt
Asked: 2024-09-14 23:51:02 +0800 CST
  • 10

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