Como fazer uma coluna calculada numa tabela dinâmica?

É possível calcular um valor com alguns números na tabela dinâmica e criar uma coluna auxiliar para fazer isso. Há um problema nesse processo: caso decida inserir uma nova coluna ou valor, esse campo calculado será sobreposto, ou então desconfigurado.

Eis a solução: quando uma tabela dinâmica é criada, podemos criar campos calculados (sem precisar criar campos adicionais na tabela original). Esses campos ficam disponíveis como novas colunas para serem inseridas na tabela dinâmica.

Segue exemplo:

Temos a tabela abaixo e queremos analisar de forma dinâmica:

Para isso, basta criar uma tabela dinâmica e configurar os campos.

Vamos supor que uma dessas configurações seja o valor total (representado pela multiplicação da Quantidade pelo Valor). Para realizar esse processo, basta clicar em Analisar > Campos, Itens e Conjuntos > Campo Calculado:

Automaticamente uma janela abrirá. Nessa nova janela, basta dar um “Nome” ao campo que você deseja realizar o cálculo e criar a fórmula.

Na fórmula podemos utilizar as fórmulas do Excel – no lugar das células que vamos calcular inserimos os “Campos” da tabela dinâmica.

Feito esse processo, basta clicar em adicionar e o campo estará disponível para visualização:

Basta configurar a tabela da forma que atenda a sua necessidade e pronto: agora você tem um campo calculado sem precisar criar colunas adicionais ao lado da tabela dinâmica.

(Dica enviada pelo amigo Guilherme Lessio).

Como inserir cálculos na tabela dinâmica?

Dominar Tabelas Dinâmicas em Excel é uma habilidade extremamente útil.

Além das funcionalidades comuns, é possível fazer cálculos mais elaborados numa tabela dinâmica.

Primeiro, uma parte mais básica.

Dada uma tabela como a seguinte, selecionar a tabela -> Inserir tabela dinâmica. Há algumas opções a preencher, como o local a inserir a tabela dinâmica.

Podemos escolher os campos que irão nas linhas, nas colunas, e por default os valores são somados.

Um primeiro truque: é possível mudar a agregação para média, desvio padrão, máximo, etc. Clicar na seta relativa ao campo desejado e em “Configurações do campo de valor”, para uma lista de alternativas.

Exemplo: fazer a média do primeiro valor, e pegar o máximo do segundo.

Será que é possível fazer cálculos?

Por exemplo, quero fazer uma média ponderada 0,8*valor1 + 0,2*valor2. Uma forma é fazer na planilha, como uma coluna a mais. Outra forma é fazer direto na tabela dinâmica – a vantagem é não poluir ou manipular demais a informação inicial, e assim evitar erros.

Para fazer cálculos direto na tabela dinâmica:

Selecionar a tabela dinâmica e ir em “Analisar” -> “Campos, itens e conjuntos” -> “Campo calculado”

Escrever a fórmula, com apoio dos campos da tabela.

O campo calculado é como se fosse uma coluna nova na tabela original, e vai estar sujeito à mesma agregação e regras de uma tabela dinâmica comum.

Ideias técnicas com uma pitada de filosofia
https://ideiasesquecidas.com/

Média, contagem, mínimo e máximo na Tabela Dinâmica

A Tabela dinâmica é um recurso extremamente útil, para análise de informações no Excel.

Seguem algumas dicas.

1 – Média, contagem, mínimo e máximo na tabela dinâmica

Suponha que eu tenha uma tabela de notas por aluno e por turma, que quero saber a nota média por turma.

Para criar uma tabela dinâmica, selecionar a tabela e Inserir -> Tabela dinâmica.

Vai aparecer uma caixa de diálogo assim.

A seguir, coloco a Turma nas linhas e a Nota em valores. Porém, por default, a tabela dinâmica vai trazer a Soma dos valores, e não a média.

É possível mudar a regra. Clicar na setinha do lado do campo de valor,  e em “Configurações do campo de valor”.        

Na tela de opções que surgir, escolher a média.

De forma análoga, é possível sumarizar os valores com contagem, máximo, soma, mínimo, etc.

É um bom truque para dominar.

2 – Atualizar tabela dinâmica

Quando o conteúdo da tabela muda, a tabela dinâmica não atualiza sozinha.   Manualmente, temos que clicar com o botão direito e mandar atualizar.

Via macro, uma forma de atualizar todas as tabelas dinâmicas é com o comando:
 
ThisWorkbook.RefreshAll
 
 
Aí é só executar a macro para atualizar as tabelas dinâmicas.
 

Como atualizar todas as tabelas dinâmicas do Excel

Segue uma dica escrita pela Mônica Aoki.

Já mostrou dados desatualizados por esquecer de atualizar a tabela dinâmica? Então fique por dentro dessa dica!

Conseguimos atualizar todas as tabelas dinâmicas da planilha apertando apenas um botão:

Como atualizar todas as tabelas dinâmicas do Excel

(Obs. Dados ilustrativos, gerados aleatoriamente)

Mas você quer disponibilizar uma ferramenta para o usuário final e não deseja que ele tenha que fazer nada para atualizar a tabela dinâmica? Conseguimos resolver isso com os “Eventos” do Excel:

1) Clique em Developer > Visual Basic

2) Na janela do Visual Basic, de um duplo clique no nome da sua aba (1). Em seguida selecione a opção “Worksheet” (2) e “Change” (3)

  • Cole o seguinte comando dentro da sua função. 

A base de dados do exemplo vai até a coluna “D”. Estou chamando o nome da minha tabela dinâmica (Faturamento) e falando para o excel dar um “Refresh” nela sempre que houver mudança de valor nessas quatro primeiras colunas.

Aqui vocês podem modificar o código para o número de colunas que tem a sua base de dados.

Uma forma alternativa é utilizar activeworkbook.refreshAll, que funciona, porém vai deixar o Excel lento – porque vai atualizar toda a planilha sempre que houver alguma alteração.

Segue planilha para vocês testarem (https://1drv.ms/x/s!Aumr1P3FaK7jn2Ti9JmfnH_8tgIP)

Fica a dica!

Veja também:

https://ferramentasexcelvba.wordpress.com/

Médias na tabela dinâmica

Tabelas dinâmicas são muito úteis para sumarizar informações.

Há vários truques envolvendo as mesmas, como fazer a média, o mínimo, etc. Vamos ver alguns.

Dada uma tabela de dados, primeiro é necessário criar a tabela dinâmica.

Selecionar a tabela toda e Inserir -> Tabela dinâmica

Na caixa que aparecer, podemos mudar a fonte de dados e o local onde a tabela dinâmica vai ficar.

Colocar o Mês nas linhas, o valor no campo de valores e o Local nas colunas.

Por default, a tabela sumariza os dados somando os valores que atendem os critérios da linha e coluna.

Porém, imagine que eu quero a Média.

Neste caso, é só clicar na setinha ao lado da “Soma de Valor” e escolher “Configurações do Campo de Valor”.

Vai surgir a caixa de diálogo a seguir.

Basta escolher Média e dar Ok.

A tabela agora vai mostrar a média.

Há diversas outras opções, como mínimo, máximo, contagem, e assim por diante.

Outro truque que acho útil.

Coloque a transação na linha. O formato vai ser o seguinte.

O problema deste formato é que o mês e a transação estão em linhas diferentes. Não ajuda, se o objetivo é copiar e colar a planilha para fazer outros cálculos,

A solução é utilizar o layout clássico.

Clicar com o botão direito sobre a tabela dinâmica -> Opções da tabela dinâmica.

Na aba “Exibição”, clicar na opção “Layout clássico de tabela dinâmica”

Vai ficar assim, o mês numa coluna e a transação em outra, ajudando na hora de utilizar em outras contas.

Dá para retirar os subtotais, deixando-a mais clean.

Ideias técnicas com uma pitada de filosofia

https://ideiasesquecidas.com/

Média ses

Exercício na forma de teste. Dada uma tabela, tirar a média dos valores por região, apenas se o campo “Considerar” for igual a 1. Três resoluções abaixo.

1)      O método mais simples é usar a função média.ses.

Conforme o próprio nome indica, a função retorna a média, para vários critérios.

=médiases (B:B; A:A;E4; C:C;1)

               B:B – Porque os valores estão na coluna B

               A:A – primeiro critério, região

               E4 – a região escolhida, digamos Curitiba

               C:C – segundo critério, considerar ou não

               1 – considerar apenas quem é 1

2 – O segundo método é criar uma tabela dinâmica.

A única “pegadinha” é que a tabela dinâmica normalmente faz a soma. Tem que mudar para média.

3 – Nas versões antigas de Excel, a função média.ses não existia. Mas dava para fazer com somases e cont.ses, e depois dividindo um pelo outro. É até mais didático.

Link para download.


Ideias técnicas com uma pitada de filosofia: https://ideiasesquecidas.com

Ferramentas Excel-VBA: https://ferramentasexcelvba.wordpress.com/

Macro para atualizar a base de dados da tabela dinâmica

Como atualizar a fonte de dados de uma tabela dinâmica, para que ela leia certinho o número de linhas da fonte?

Uma forma simples de contornar o problema é colocar um range grande para a entrada da tabela dinâmica, digamos 500 mil linhas, e não ter que se preocupar com isto.  A desvantagem é que isto obriga o Excel a ler tais linhas, mesmo que vazias, aumentando o peso do arquivo.

Outra forma proposta é via macro, download aqui um exemplo.

Primeiro, lê quantas linhas têm na coluna A da Planilha 1 e joga na variável “nl”

Sheets(“Plan1”).Activate

‘Lê quantas linhas tem na coluna a, limitado a 100000 linhas

nl = Application.WorksheetFunction.CountA(Range(“a1:a100000”))

Depois atualiza a fonte de dados.

Há uma série de parâmetros aqui, o nome da tabela dinâmica, o range de dados. Uma forma fácil de obter esses parâmetros é gravando uma macro que atualiza a tabela, e depois apenas modificar o campo “SourceData”.

‘Preencher com o nome da tabela dinamica

‘É necessário também mudar a fonte de dados

‘No caso, “Plan1!R1C1:R” & nl & “C3”, está lendo da linha 1 coluna 1 (R1C1) até a linha nl coluna 3

ActiveSheet.PivotTables(“Tabela dinâmica1″).ChangePivotCache ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:=”Plan1!R1C1:R” & nl & “C3”,   Version:=xlPivotTableVersion15)


Ideias técnicas com uma pitada de filosofia: https://ideiasesquecidas.com

Ferramentas Excel-VBA: https://ferramentasexcelvba.wordpress.com/