Neste artigo você aprenderá sobre matrizes dinâmicas no Excel e verá o que muda no uso do seu Excel com esta grande mudança.

Matrizes dinâmicas Excel - O que muda?

As matrizes dinâmicas vieram para revolucionar o Excel, mas o que muda?

Desde a forma como se referencias as células, até o modo como resolvemos problemas complexos de matriciais podem ser muitas resolvidos agora com funções que contém um único parâmetro.

Neste artigo eu apresento algumas possibilidades na utilização dos recursos e fórmulas de matrizes dinâmicas em soluções profissionais e mesmo complexas de serem realizadas com os recursos antigos.

Material gratuito

Planilha de Controle Financeiro no Excel

Tenha um controle bonito e completo do seu financeiro.

Categorias e subcategorias já configuradas
Planilhas separadas por tabela (configuração, lançamentos, resumo)
Personalize com suas próprias categorias e ícones

Matrizes dinâmicas Excel

As matrizes dinâmicas já estão disponíveis no Office 365 em todas as suas versões. Caso não apareça no seu Office ainda, verifique como está configurada a atualização da sua licença, você pode mudar isto.

E outra forma de liberar este recurso e receber em primeira mão as novidades do Excel é fazer parte do programa Insider, que inclusive é gratuito.

Para isso siga este tutorial: https://www.guiadoexcel.com.br/como-habilitar-o-excel-no-office-insider-em-qualquer-versao-do-office-365/

Abaixo eu listo algumas soluções criadas com os recursos de matrizes dinâmicas e ao final do artigo você pode realizar o download da planilha para estudar.

Filtro dinâmico de dados no Excel

O gerente precisa de um relatório aonde ele irá realizar diversos filtros diferentes para analisar uma massa de dados retornada no Excel.

Esta consulta precisa ser dinâmica e ter os seguintes filtros:

Year: apenas o ano.
Category: ao digitar qualquer letra filtrar automaticamente.
Product: ao digitar qualquer letra filtrar automaticamente.
Sales: intervalo entre valores
Rating: intervalo entre valores

Com o uso da nova função Filtro do Excel você consegue realizar esta tarefa de forma rápida e muito profissional.

Na fórmula que usamos, e foi só uma, nós temos o seguinte:

=FILTRO(Tabela8;(Tabela8[Year]=B8)*(ÉNÚM(LOCALIZAR(C8;Tabela8[Category];1)))*(ÉNÚM(LOCALIZAR(D8;Tabela8[Product];1)))*(Tabela8[Sales]>=E8)*(Tabela8[Sales]<=E9)*(Tabela8[Rating]>=F8)*(Tabela8[Rating]<=F9);"NÃO EXISTEM DADOS")

Tabela8: A matriz de dados que serão retornados.
(Tabela8[Year]=B8): Filtro de ano. Verifica se a coluna Year é igual ao valor digitado no filtro.
(ÉNÚM(LOCALIZAR(C8;Tabela8[Category];1))): Identifica se existe o texto digitado se encontra na coluna Category.
(ÉNÚM(LOCALIZAR(D8;Tabela8[Product];1))): Identifica se existe o texto digitado se encontra na coluna Product.
(Tabela8[Sales]>=E8)*(Tabela8[Sales]<=E9): Filtra somente o período da coluna Sales entre as datas descritas nas células.
(Tabela8[Rating]>=F8)*(Tabela8[Rating]<=F9): Filtra somente o intervalo definido de Rating.
"NÃO EXISTEM DADOS": é como o SEERROS, o texto é apresentado caso não sejam encontrada uma matriz que satisfaça as condições.

Relatório profissional Excel

Com as matrizes dinâmicas e fórmulas de matrizes nós conseguimos dar não só um aspecto profissional ao relatório como também leve e performático.

No exemplo de relatório de cobrança que temos neste exemplo realizamos um filtro de intervalo de datas e de um vendedor de forma dinâmica, ao digitar ou selecionar os dados são atualizados.

Na fórmula nós utilizamos duas funções dinâmicas, CLASSIFICAR e FILTRO. A primeira realiza a classificação dos dados que são filtrados pela coluna 4 da matriz, equivalente a data.

=SEERRO(CLASSIFICAR(FILTRO(Tabela4;(Tabela4[Vencimento]>=Relatório!C3)*(Tabela4[Vencimento]<=Relatório!E3)*(Tabela4[Vendedor]=Relatório!C5);"");4;1);"")

SEERRO: caso retorne erro a fórmula ele irá apresentar vazio.
CLASSIFICAR: classifica os dados que foram filtrados na matriz pela coluna 4, equivalente a data.
FILTRO: assim como no anterior os elementos são multiplicados para filtrar o intervalo de datas e o vendedor e apresentá-los no relatório.

WMS no Excel

Em artigos anteriores eu já havia apresentado esta solução e mais 3 diferentes para resolver o mesmo problema no Excel. O endereçamento de estoque.

A solução mais performática que encontrei foi esta, a utilização de fórmulas de matrizes dinâmicas para realizar o filtro automático de dados, resultando em uma solução muito rápida e elegante.

A explicação é um pouco longa, mas neste artigo eu detalho todas as etapas do desenvolvimento desta solução: https://www.guiadoexcel.com.br/mapa-de-estoque-excel-4-logistica-wms/

E também no vídeo no topo deste artigo você consegue fazer do zero a mesma solução.