Neste artigo você aprenderá como criar uma tabela dinâmica usando para isso funções de matrizes dinâmicas no Excel.
Tabela Dinâmica com Matrizes Dinâmicas no Excel
O objetivo deste artigo é mostrar como podemos criar uma tabela dinâmica usando apenas funções de matrizes dinâmicas.
Tudo criado de forma que ao mudar os dados os mesmos sejam redimensionados dinamicamente, e também ao mudar a base os valores sejam alterados.
Como podemos notar na imagem acima, temos a mudança dos cálculos de forma automatizada ao clicar nos botões e ao mudar o campo de resumo.
Base de Dados
A base de dados da nossa tabela é a abaixo, aonde temos as vendas, vendedores e valores de comissão.
Nosso objetivo é resumir estes dados por data e poder mudar dinamicamente os cálculos e também os campos que serão usados nos cálculos de sumarização.
Controles da Tabela Dinâmica com Fórmulas Matriciais
Agora podemos criar um controle para a tabela dinâmica, cujo objetivo que ao mudar pelo clique, seja alterado o resultado.
Então clicamos na guia Desenvolvedor e clicamos em controles e Inserir->Botões de opção, desenhe 4 e altere o nome deles.
No nosso exemplo, soma, média, mínimo e máximo.
Em seguida com o botão direito sobre um deles, altere o controle para uma célula, no nosso exemplo L4.
Isso fará com que ao clicar no botão seja mudado o número do objeto selecionado nesta célula.
Depois criamos uma lista suspensa com validação de dados para a célula ao lado de Campo.
Nela colocamos Valor e Valor Comissão.
Na célula ao lado de Operação colocamos a função ESCOLHER, para retornar o nome conforme o item selecionado.
=ESCOLHER(M4;”Soma”;”Média”;”MínImo”;”Máximo”)
E agora ao mudar o item selecionado, a célula muda também retornando o nome do cálculo que será realizado.
Coluna Classificada e Única no Excel – Vendedores e Datas
Na primeira coluna incluímos os vendedores.
Para isso usamos a fórmula: =CLASSIFICAR(ÚNICO(Tabela1[Vendedor]))
Ela retorna uma lista única e classificada de todos os vendedores da tabela de vendas.
Para as datas usamos a função BYROW junta com a CLASSIFICAR, ÚNICO, TRANSPOR, FIMMÊS E LAMBDA.
O objetivo é que a cada célula seja realizado o cálculo de final do mês e retornado apenas o final de cada mês na coluna ao topo.
A fórmula utilizada para as datas foi:
=TRANSPOR(CLASSIFICAR(ÚNICO(BYROW(Tabela1[Data];LAMBDA(A;FIMMÊS(A;0))))))
Cálculos com Matrizes Dinâmicas no Excel
Agora que temos os cabeçalhos de linha e coluna, podemos realizar a sumarização dos dados.
Para isso usamos a fórmula SOMASES junta com a INDIRETO, DATA, ANO e MÊS, como vemos abaixo.
Nela temos a soma o campo valor de comissão ou do valor da venda retornados pela função, à partir da função indireto que concatena este nome com o nome da Tabela1.
Com isso temos um campo de cálculo dinâmico.
À partir deste cálculo inicial usamos a fórmula seguinte, aonde temos os cálculos de acordo com o que foi selecionado na operação e campo de valor.
=SEERRO(SES(L4=”SOMA”;
SOMASES(INDIRETO(“Tabela1[“&L5&”]”);
Tabela1[Vendedor];K9#;
Tabela1[Data];”>=”&DATA(ANO(L8#);MÊS(L8#);1);
Tabela1[Data];”<=”&L8#); L4=”Média”; MÉDIASES(INDIRETO(“Tabela1[“&L5&”]”); Tabela1[Vendedor];K9#; Tabela1[Data];”>=”&DATA(ANO(L8#);MÊS(L8#);1);
Tabela1[Data];”<=”&L8#); L4=”Mínimo”; MÍNIMOSES(INDIRETO(“Tabela1[“&L5&”]”); Tabela1[Vendedor];K9#; Tabela1[Data];”>=”&DATA(ANO(L8#);MÊS(L8#);1);
Tabela1[Data];”<=”&L8#); L4=”Máximo”; MÁXIMOSES(INDIRETO(“Tabela1[“&L5&”]”); Tabela1[Vendedor];K9#; Tabela1[Data];”>=”&DATA(ANO(L8#);MÊS(L8#);1);
Tabela1[Data];”<=”&L8#));0)
Então ao mudar o campo ele troca o cálculo retornando soma, média, mínimo ou máximo, conforme a data e coluna, simulando uma tabela dinâmica com funções de matrizes dinâmicas no Excel.
Totalizadores da Tabela Dinâmica com Funções de Matrizes
Ao final iremos totalizar as linhas e colunas.
Para isso usamos as funções BYROW e SES, para sumarizar conforme a operação selecionada.
Para isso usamos a fórmula abaixo:
=BYROW(L9#;LAMBDA(A;SES(L4=”SOMA”;SOMA(A);L4=”MÉDIA”;MÉDIA(A);L4=”MÍNIMO”;MÍNIMO(A);L4=”MÁXIMO”;MÁXIMO(A))))
Perceba que temos então um resumo linha a linha dos valores.
Ao topo incluímos então uma soma das colunas
E para isso usamos uma fórmula muito parecida:
=BYCOL(L9#;LAMBDA(A;SES(L4=”SOMA”;SOMA(A);L4=”MÉDIA”;MÉDIA(A);L4=”MÍNIMO”;MÍNIMO(A);L4=”MÁXIMO”;MÁXIMO(A))))
E por fim temos o resultado abaixo. Podendo ainda fazer formatações condicionais como explicado no vídeo para que sejam exibidas linhas de grade e cores formatadas conforme a linha.
O download da planilha de tabela dinâmica com funções de matrizes dinâmicas é gratuito, basta clicar no botão abaixo.
Download Criar Tabela Dinâmica com Funções de Matrizes no Excel
Clique no botão abaixo para realizar o download do arquivo de exemplo:
