Substitua uma Tabela Dinâmica por 3 Fórmulas de Matriz Dinâmica - Dicas do Excel

Índice

Assistir vídeo

Já se passaram oito dias desde que as fórmulas de matriz dinâmica foram anunciadas na conferência Ignite 2018 em Orlando. Aqui está o que aprendi:

Arrays modernos foram anunciados no Ignite em 24 de setembro de 2018 e oficialmente chamados de Dynamic Arrays.
Escrevi um e-book de 60 páginas com 30 exemplos de como usá-los e estou oferecendo-o gratuitamente até o final de 2018.
O lançamento será muito mais lento do que qualquer um deseja, o que é frustrante. Por que tão devagar? A equipe do Excel fez alterações no código do Calc Engine que se manteve estável por 30 anos. Uma preocupação particular: com suplementos que injetam fórmulas no Excel que inadvertidamente usaram interseção implícita. Esses suplementos serão interrompidos se o Excel agora retornar um intervalo de Spill.
Existe uma nova maneira de se referir ao intervalo retornado por um array: =E3#mas ele ainda não tem um nome. O # é chamado de operador de fórmula derramado . O que você acha de um nome como Spill Ref (sugerido pelo Excel MVP Jon Acampora) ou The Spiller (sugerido pelo MVP Ingeborg Hawighorst)?

Como coautor de Pivot Table Data Crunching, adoro uma boa tabela dinâmica. Mas e se você precisar que suas tabelas dinâmicas sejam atualizadas e não puder confiar que o gerente do seu gerente irá clicar em Atualizar? A técnica descrita hoje oferece uma série de três fórmulas para substituir uma tabela dinâmica.

Para obter uma lista classificada de clientes exclusivos, use =SORT(UNIQUE(E2:E564))em I2.

Uma fórmula de matriz dinâmica para criar clientes na parte lateral do relatório

Para colocar o produto na parte superior, use =TRANSPOSE(SORT(UNIQUE(B2:B564)))em J1.

Para a área de colunas, use TRANSPOSE

Aqui está um problema: você não sabe a altura da lista de clientes. Você não sabe quão ampla será a lista de produtos. Se você se referir a I2 #, o Spiller se referirá automaticamente ao tamanho atual do array retornado.

A fórmula para retornar a área de valores da tabela de pivô é uma única fórmula de matriz em J2: =SUMIFS(G2:G564,E2:E564,I2#,B2:B564,J1#).

Em inglês, isso indica que você deseja adicionar as receitas de G2: G564 onde os clientes em E correspondem ao cliente da linha atual da fórmula de matriz I2 e os produtos em B correspondem à coluna atual da fórmula de matriz em J1.

Esta é uma fórmula doce

E se os dados subjacentes forem alterados? Adicionei um novo cliente e um novo produto, alterando essas duas células na origem.

Alterar algumas células nos dados originais

O relatório é atualizado com novas linhas e novas colunas. A referência Array-Range de I2 # e J1 # trata da linha e coluna extras.

Seu relatório de tabulação cruzada se expande automaticamente com os novos dados

Por que o SUMIFS funciona? Este é um conceito do Excel chamado Broadcasting. Se você tiver uma fórmula que se refere a duas matrizes:

A matriz um é (27 linhas) x (1 coluna)
A matriz dois é (1 linha) x (3 colunas)
O Excel retornará uma matriz resultante que é tão alta e larga quanto a parte mais alta e mais larga das matrizes referenciadas:
O resultado será (27 linhas) x (3 colunas).
Isso é chamado de matrizes de transmissão.