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.
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.
Download
Faça o download da planilha de simulação de aluguel ou compra de veículos abaixo.
Baixe a planilhaCurso Excel Completo – Do Básico ao VBA
Quer aprender Excel do Básico, passando pela Avançado e chegando no VBA? Clique na imagem abaixo:
