Unpivot avançado com uma única fórmula [duplicado]

O TEXTSPLIT(TEXTJOIN())método funcionará bem para tabelas menores, onde a sequência resultante não excederá 32.767 caracteres.

Uma abordagem alternativa para tabelas de qualquer tamanho é usar técnicas de manipulação de array baseadas HSTACKem TOCOL-, que variam dependendo da complexidade da tabela.

Exemplo 1: um campo de rótulo de linha única para manter

=LET(
    tbl, Table3[#All],
    vals, DROP(tbl, 1, 1),
    rowLabels, DROP(TAKE(tbl,, 1), 1),
    colLabels, DROP(TAKE(tbl, 1),, 1),
    HSTACK(
        TOCOL(IF(SEQUENCE(, COLUMNS(vals)), rowLabels)),
        TOCOL(IF(SEQUENCE(ROWS(vals)), colLabels)),
        TOCOL(vals)
    )
)

Exemplo 2: vários campos de rótulo de linha para manter

=LET(
    tbl, Table3[#All],
    vals, DROP(tbl, 1, 2),
    rowLabels, DROP(TAKE(tbl,, 2), 1),
    colLabels, DROP(TAKE(tbl, 1),, 2),
    rowId, SEQUENCE(ROWS(vals)),
    HSTACK(
        CHOOSEROWS(rowLabels, TOCOL(IF(SEQUENCE(, COLUMNS(vals)), rowId))),
        TOCOL(IF(rowId, colLabels)),
        TOCOL(vals)
    )
)

Exemplo 3: vários campos de valor para unpivot

=LET(
    tbl, Table3[#All],
    vals, DROP(tbl, 1, 1),
    rowLabels, DROP(TAKE(tbl,, 1), 1),
    colLabels, UNIQUE(LEFT(DROP(TAKE(tbl, 1),, 1), 4), TRUE),
    HSTACK(
        TOCOL(IF(SEQUENCE(, COLUMNS(vals)/2), rowLabels)),
        TOCOL(IF(SEQUENCE(ROWS(vals)), colLabels)),
        WRAPROWS(TOCOL(vals), 2)
    )
)

Estes são apenas 3 exemplos básicos. No entanto, modificações podem ser feitas para lidar com praticamente qualquer cenário.

No passado, eu fiz isso com algumas INDEX/MATCHsoluções bastante complicadas, mas para minha surpresa (não usei muito o Excel nos últimos anos), o Excel 365 tornou isso muito fácil de fazer.

A solução geral é muito simples e legível (especialmente ao usar tabelas e intervalos nomeados). Por exemplo, para uma tabela básica como:

A fórmula é bastante simples e fácil de seguir:

 =TEXTSPLIT(
      TEXTJOIN(",",TRUE,
           TOCOL(Table3[Company]&"|"&Table3[[#Headers],
           [2020]:[2023]]&"|"&Table3[[2020]:[2023]])),
           "|",",")

O que realmente me impressionou foi o quão útil é a nova LETfunção para mim. Por exemplo, ao desdinamizar uma tabela com uma entrada um pouco diferente (com espaços em branco) e uma saída desejada ligeiramente diferente, o uso LETtorna a fórmula muito mais fácil de seguir:

 =LET(
      array,
      TEXTSPLIT(TEXTJOIN(",",TRUE,TOCOL(Table2[COMPANY]&"|"&Table2[[Service 1]:[Service 5]])),"|",","),
      FILTER(array,CHOOSECOLS(array,2)<>""))

Não encontrei muitos exemplos on-line de tais fórmulas não dinâmicas, então espero que alguém também as considere úteis.

Aqui está uma maneira de fazer o unpivoting usando Fórmulas Dinâmicas do Excel aplicáveis ​​aMS365

• Método 1 O método descrito é dinâmico , explode com mudança na expansão de linhas e colunas:

• Fórmula usada na célulaA23

=LET(
     _Data, Table3[#All],
     _Companies, TAKE(_Data,-(ROWS(_Data)-1),1),
     _Years, TAKE(_Data,1,-(COLUMNS(_Data)-1)),
     _Values, DROP(_Data,1,1),
     _Answer, HSTACK(
            TOCOL(IFS(_Values>0,_Companies),2),
            TOCOL(IFS(_Values>0,_Years),2),
            TOCOL(_Values,3)),
     VSTACK({"COMPANY","YEAR","VALUE"},_Answer))

_{NOTA: A fórmula acima pode ser simplesmente encurtada usando o mesmo algoritmo, mas não será dinâmica , ou seja, ela se expande quando as linhas são adicionadas, mas não se expande quando as colunas são aumentadas, pode-se tentar, o acima irá expandir ou recolher imediatamente, se há uma mudança na fonte real. A forma alternativa, descrita abaixo}

• Método 2 :

=HSTACK(TOCOL(IFS(Table3[[2020]:[2023]]>0,Table3[COMPANY]),2),
        TOCOL(IFS(Table3[[2020]:[2023]]>0,Table3[[#Headers],[2020]:[2023]]),2),
        TOCOL(Table3[[2020]:[2023]],3))

Opção de variante 1 : Outra maneira de escrever o primeiro método usando um customizado LAMBDA()para execução para cada um, onde o TOCOL()& IFS()não é usado repetidamente, isso é dinâmico :

=LET(
     _Data, Table3[#All],
     _Companies, TAKE(_Data,-(ROWS(_Data)-1),1),
     _Years, TAKE(_Data,1,-(COLUMNS(_Data)-1)),
     _Values, DROP(_Data,1,1),
     _CustomFx, LAMBDA(α,δ, TOCOL(IFS(α>0,δ),3)),
     _Answer, HSTACK(
            _CustomFx(_Values,_Years),
            _CustomFx(_Values,_Companies),
            _CustomFx(_Values,_Values)),
     VSTACK({"COMPANY","YEAR","VALUE"},_Answer))

Opção de variante 2 : Se você deseja aplicar o mesmo algoritmo com o segundo método, pode tentar desta forma também, isso não é dinâmico :

=LET(
     _Data, Table3[#All],
     _Companies, Table3[COMPANY],
     _Years, Table3[[#Headers],[2020]:[2023]],
     _Values, Table3[[2020]:[2023]],
     _CustomFx, LAMBDA(α,δ, TOCOL(IFS(α>0,δ),3)),
     _Answer, HSTACK(
            _CustomFx(_Values,_Years),
            _CustomFx(_Values,_Companies),
            _CustomFx(_Values,_Values)),
     VSTACK({"COMPANY","YEAR","VALUE"},_Answer))

Para o segundo exemplo de DataSet, você pode tentar o seguinte:

=LET(
     _Data, Table2[#All],
     _Companies, TAKE(_Data,-(ROWS(_Data)-1),1),
     _Values, DROP(_Data,1,1),
     _Answer, HSTACK(
            TOCOL(IFS(_Values>0,_Companies),2),
            TOCOL(_Values,3)),
     VSTACK({"COMPANY","SERVICE"},_Answer))

_{NOTE: Using TEXTJOIN() function when unpivoting data, I don't think is a rock-solid method, because the said function itself has some limitations, yes one can say every functions has their limitations, but all depends on the data one has. Still, I won't be using TEXTJOIN() function or ARRAYTOTEXT() or CONCAT() as per MSFT documentations --> If the resulting string exceeds 32767 characters (cell limit), TEXTJOIN() returns the #VALUE! error, clearly, it is bit risky to use. See the cell limit mentioned here, works with the whole array concept as it is taken into one, it will certainly return an error, as the array formula seats in one cell and se espalha por várias linhas/colunas.}