A Revolução das Matrizes Dinâmicas no Excel

Gerson Viergutz

2 anos atrás

O Recurso de Matrizes Dinâmicas no Excel é uma mudança drástica no motor de cálculo no Excel. Mas o que é esse tal de Matrizes Dinâmicas? Quando uma fórmula do Excel retorna um conjunto de valores ao invés de somente um único valor o Excel retornava um erro #VALOR!, ou seja, o resultado precisava retornar somente valores único, exceto as Fórmulas Matriciais que aí é outra coisa.

Lembrando que as funções de Matrizes Dinâmicas são compatíveis apenas nas versões do Office 365 (Microsoft 365) e Excel 2021.
Novas funções estão sendo lançadas e não devem ser compatíveis com o Office 2021.

E não são somente novas fórmulas que são de Matrizes Dinâmicas, algumas fórmulas atuais se tornaram Matrizes Dinâmicas, olha só.

Vamos para um primeiro exemplo de como retornava o erro.

Antes da introdução das Matrizes Dinâmicas no Excel, ao referenciar um intervalo de células o resultado era o erro #VALOR! conforme o erro acima. Agora ele retorna a matriz e despeja este valor. Veja o resultado abaixo.

Veja que agora o Excel despeja o valor ocupando o tamanho da matriz de origem, ou seja, ele retorna estes valores para as células vizinhas também.

O que é DESPEJAR?

Despejar é o resultado dinâmico de uma matriz que pode usar células vizinhas para retornar o valor de uma matriz.

Veja no exemplo abaixo.

O Intervalo referenciado no exemplo acima eram de 7 células, mas o resultado da matriz são 4 linhas, ou seja, ela é dinâmica. Podemos perceber a região da matriz pelo retângulo azul exibido ao clicar em qualquer lugar da matriz.

Perceba que utilizamos somente uma única célula para digitar a fórmula, não precisamos usar a alça de preenchimento para arrastar a fórmula. Se clicar em outra célula dentro da matriz, será exibido a fórmula, mas não poderá ser alterada, somente na célula original.

Célula Selecionada dentro de uma Matriz Dinâmica no Excel

O Erro #DESPEJAR!

O Erro #DESPEJAR! irá ocorrer em algumas situações em que não será possível retornar a matriz que vou mostrar abaixo.

Um dos principais motivos é quando não há espaço disponível para exibir a matriz, geralmente quando há dados em uma célula onde a matriz iria despejar os dados.

No exemplo acima podemos perceber que há um texto na célula D10. No retângulo pontilhado azul é onde a matriz queria despejar os valores, mas como há um valor na célula ocorre o erro #DESPEJAR!.

Outra forma de ocorrer o erro é quando o Excel não consegue determinar o tamanho da matriz. Um exemplo é usar a função =SEQUÊNCIA(ALEATÓRIOENTRE(1;10000)) vai retornar o erro #DESPEJAR!.

Operador de interseção implícita @

O Operador @ antes da função significa que não queremos usar o recurso de Matrizes Dinâmicas o que chamamos de Operador de interseção implícita.

O Operador @ é utilizando quando queremos que a função se comporte diferente de Matrizes Dinâmicas, ou seja, da forma anterior.

Veja no exemplo acima, utilizando o operador @, ao invés de retornar a matriz, ele retorna o primeiro valor a esquerda apenas, forçando retornar sempre um valor único.

A lógica funciona da seguinte maneira:

Se o valor for um único item, retorne o item.
Se o valor for um intervalo, retorne o valor da célula na mesma linha ou coluna da fórmula.
Se o valor for uma matriz, escolha o valor superior esquerdo.

Vou mostrar um segundo exemplo do operador @.

Foram utilizadas as mesmas fórmulas em todas as linhas, e a fórmula retornou o valor da célula da mesma linha, interessante né.

Novas funções de Matrizes Dinâmicas no Excel

Com a implementação das Matrizes Dinâmicas no Excel, a Microsoft inseriu novas funções que aumentam ainda mais o poder do Excel. As Primeiras funções foram.

Função FILTRO
Função MATRIZALEATÓRIA
Função SEQUÊNCIA
Função CLASSIFICAR
Função CLASSIFICARPOR
Função ÚNICO
Função PROCX
Função CORREPX

As funções PROCX e CORRESPX não forma lançados juntos com as primeiras funções, mas já vieram com o comportamento de Matrizes Dinâmicas.

Gravei um vídeo completo sobre a função FILTRO do Excel, veja a aula aqui: Função FILTRO no Excel – Esta função é incrível.

Logo surgiu também a função TRANSPOR com o recurso de Matriz Dinâmica.

Em março de 2022 a Microsoft lançou 14 novas funções de Matrizes Dinâmicas. São funções de Manipulação de Texto e Matrizes e são simplesmente sensacionais

As funções lançadas foram:

TEXTOANTES
TEXTODEPOIS
TEXTODIVISÃO
EMPILHARV
HSTACK – (Deverá ser traduzido para EMPILARH)
PEGAR
DESCARTAR
ESCOLHERLINS
ESCOLHERCOLS
EXPANDIR
PARALIN
PARACOL
QUEBRARLINS
QUEBRARCOLS

Você pode ver o artigo e vídeo completo destas novas funções aqui: 14 Novas Funções do Excel.

Contar valores únicos no Excel

Uma das grandes vantagens das Matrizes Dinâmicas é poder aninhar com funções antigas do Excel. Vou mostrar um exemplo de como podemos contar os valores únicos de uma lista utilizando a função CONT.VALORES e a função ÚNICO.

Contar Valores Distintos com a Função CONT.VALORES e ÚNICO.

No exemplo acima utilizando a fórmula =CONT.VALORES(ÚNICO(B7:B13)) para retornar a quantidade de registros únicos de um intervalo.

Outro exemplo muito bom são as funções antigas com comportamento de Matrizes Dinâmicas, como por exemplo a função ARRUMAR.

Perceba que no exemplo acima, foi digitado a fórmula apenas em uma célula, mas por causa do comportamento de Matrizes Dinâmicas, ele aumentou o tamanho do intervalo dinamicamente.

Funcionalidade do operador # nas Matrizes Dinâmicas

Uma das funcionalidades das Matrizes Dinâmicas é você poder referenciar uma Matriz Dinâmica referenciando apenas uma única célula utilizando o operador #.

No exemplo acima estamos referenciando a célula D6 que é uma Matriz Dinâmica, pois foi utilizado a função ÚNICO. Ao referencial a célula D6# com o operador #, a referência é feita para toda a matriz, deixando o intervalo dinâmico, ou seja, se surgir novas linhas na matriz da D6, será incluído automaticamente na Matriz da célula F6.