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.
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.
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.
Limitações das Matrizes Dinâmicas
Você não pode utilizar Matrizes Dinâmicas em Tabelas do Excel. Apesar de você poder referenciar uma Tabela, você não pode usar elas na Tabela.
Sabia que você pode aprender Excel de Forma Gratuita? Acesse o minicurso de Excel aqui: Minicurso de Excel Gratuito
Você também pode assistir ao vídeo para saber mais.
Gostou do artigo? Qual das funções você gostaria de se aprofundar?
Não deixe de seguir minhas redes sociais:
Linkedin: https://www.linkedin.com/in/gersonggv
Instagram: https://instagram.com/gerson.mvp
Deixe um comentário