Excel Intermediário: Função INFODADOSTABELADINÂMICA


Se você já utiliza  a tabela dinâmica já deve conhecer as suas funcionalidades e facilidades para agregar, somar, calcular dados rapidamente e com uma grande versatilidade e flexibilidade. Você sabia que é possível, além disso, extrair um valor específico para uma célula qualquer, de acordo com os critérios e filtros?

Aprenda a utilizar a tabela dinâmica no artigo “Desvendando a tabela dinâmica”.

Sim, através da função INFODADOSTABELADINÂMICA podemos retornar este valor para um célula, passando tantos critérios, quanto houver rótulos na sua tabela dinâmica.

Quando você vai utilizar uma fórmula de igualdade, com =C1 por exemplo, onde C1 seja uma célula contendo uma tabela dinâmica, note que a fórmula é automaticamente convertida para =INFODADOSTABELADINÂMICA(…). Essa opção é padrão no Microsoft Excel e pode ser desativada em “Opções do Excel” > “Fórmulas”, desmarque a opção “Usar funções GetPivotData para referências de Tabela Dinâmica.”

infodados1

Como desativar INFODADOSTABELADINÂMICA automaticamente

Onde utilizar?

A função INFODADOSTABELADINÂMICA pode ser utilizada para dar mais flexibilidade à tabela dinâmica, quando você precisa extrair valores específicos dela para células específicas que, não necessariamente, estejam organizadas em formato de tabela.

Além disso, ao trabalhar com uma massa muito grande de dados, esta função tem uma performance um pouco superior às demais, como SOMASE, CONT.SE etc.

Se você trabalha em versões anteriores ao Excel 2007 (não há SOMASES ou CONT.SES, por exemplo, com várias condições), pode usufruir desta fórmula que permite utilizar inúmeras condições de filtro.

A sintaxe da função

INFODADOSTABELADINÂMICA(‘campo_dados’; ‘tab_din’, [‘campo1’; ‘item1’; ‘campo2’; ‘item2’…]), onde:

‘campo_dados’: Deve ser o nome do campos que contém os valores a serem retornados, por exemplo, a soma das vendas realizadas.

‘tab_din’: É qualquer célula que localizada no mesmo local que a tabela dinâmica.

‘campo1’: É o nome do campo que está ativo na tabela dinâmica. Por exemplo, você tem um campo chamado “Frutas” que contém os nomes das frutas em sua base de dados.

‘item1’: É o item dentro do campo informado anteriormente que você deseja filtrar para exibir os valores. No exemplo anterior, o item pode ser “Abacate”.

Exemplo da função

Neste exemplo da função INFODADOSTABELADINÂMICA iremos utilizar uma base de dados contendo as vendas de dois meses, com detalhes de canal de venda, UF e produto. O relatório deverá demonstrar a quantidade das vendas realizadas por produto, selecionando o mês desejado, o canal de venda, a UF e o produto.

Antes de tudo, vamos criar a base de dados contendo estas informações como abaixo:

Base de dados

Criando base para tabela dinâmica

Crie a tabela dinâmica, selecionando os dados da planilha, clique na guia “Inserir” > “Tabela Dinâmica”. Escolha nova planilha para inserir a tabela dinâmica, adicione os campos “Canal”, “UF”, “Produto” em “Rótulos de Linha” e “Jan”, “Fev” em “Valores”.

Ao lado da tabela dinâmica, insira os produtos que serão exibidos no seu relatório nas células F3 até H3, e os meses nas células E5 e E6. Monte, mais para a direita, um formulário com o qual poderemos filtrar os dados desejados para exibir os valores de acordo.

Relatório e filtros de relatório para tabela dinâmica

Todas os valores serão exibidos entre as células F5 até H6, de acordo com o produto na coluna F:H, o mês nas linhas 5 e 6, canal, UF e produto escolhidos nas células da coluna K4 até K6.

Desta forma, insira a fórmula na célula F5: =INFODADOSTABELADINÂMICA(“Soma de “&$E5;$A$3;”Canal”;$K$4;”UF”;$K$5;”Produto”;F$4) e vamos compreendê-la em partes:

  • “Soma de “&$E5;”: É o nome do campo que contém os valores, neste caso, “Soma de ” & $E5 (concatena a célula E5 que contém o nome do mês desejado). Ao calcular ficaria “Soma de Jan” e “Soma de Fev” na célula seguinte.
  • $A$3: É uma célula que contém a tabela dinâmica.
  • “Canal”;$K$4: Filtra o campo “Canal” como o valor preenchido na célula K4, calculando automaticamente conforme mudar o valor na célula.
  • “UF”;”$K$5: O mesmo que o acima, filtrando uma “UF” específica.
  • “Produto”.”F$4: O mesmo que o acima, mas filtra um “Produto” específico, móvel localizado nas células F4 até H4.

Copie e cole a função nas demais células do relatório.

Assim, criamos um relatório dinâmico, customizado, de acordo com os dados existentes na tabela dinâmica e os filtros que criamos acima.

down_icon Download | Modelo INFODADOS

Se gostou compartilhe clicando nos botões abaixo e participe deixando um comentário com críticas, sugestões, complementação etc!

4,635 total views, 1 views today

Deixe um comentário

O seu endereço de e-mail não será publicado. Campos obrigatórios são marcados com *