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/

Algumas dicas para lidar com arquivos grandes

O Excel é uma ferramenta de análise de dados extremamente poderosa. Porém, quando ele fica grande demais, começa a travar, os cálculos ficam demorados, etc.

Há algumas dicas a respeito, que podem ajudar a melhorar bastante a performance.

1) Restringir o Procv 

Para fazer cruzamento entre bases, usa-se bastante o procv. Um procv irrestrito é quando o critério pega a coluna inteira, como a seguir.

=PROCV(C2;F:F;1)

Embora seja fácil e garanta que uma nova base com mais linhas entre na conta, a performance diminui. Isso porque o Excel vai tentar procurar desde a primeira linha (1) até a última linha (1.058.576).

Deve-se limitar o range de buscas, para um número suficientemente grande para a base. Por exemplo, se 5000 linhas forem suficientes:

=PROCV(C2;F1:F5000;1)

2) Restringir Somase, somases, contses,  e outros do tipo.

A mesma dica de restringir o range vale para fórmulas como somases e outras do tipo, pela mesma razão descrita acima.

3)

Excluir (não é apagar, é excluir) linhas vazias. Às vezes, uma planilha pode estar com 10 linhas com informação, mas estar utilizando 1 milhão de linhas! Isto ocorre, por exemplo, se alguém utilizar as 1 milhão de linhas, e apagar o conteúdo (com a tecla del). Isto vai apagar o valor da célula, mas o Excel vai continuar considerando que o range útil é de 1 milhão de linhas. O mesmo vale para colunas vazias.

Deve-se excluir (não apagar) esse range (selecionar as linhas, clicar com o botão direito e excluir – ou utilizar o atalhao ctrl -).

Uma forma de conferir o range utilizado é através do atalho ctrl end. Isto vai levar o cursor à última célula livre.

RangeNaoVazio.JPG

4)

Vínculos: algumas vezes, a tabela pode ter vínculos, como o da imagem a seguir. Isto pode dificultar muito a manipulação da planilha, se o vínculo for de milhares de células, por exemplo.

Este vínculo é mesmo necessário? Se não for, em Dados -> Editar links (no Excel 2010) há uma opção para excluir o vínculo.

EditarLinks.JPG

5)

Nomes. Atrelado ao tema vínculos. De vez em quando, há um nome vinculando outra fonte de informações, e pesando muito. Pode ser que nem o usuário tenha criado, mas algum outro Addin que manipula o excel.

Uma forma de deletar tudo o quanto é nome é rodando o código a seguir.

1 2 3 4 5 6 7 8 9 10 11 12 13 Sub delNames()   Dim nme As Name   On Error Resume Next   For Each nme In ActiveWorkbook.Names   nme.Delete   Next   End Sub

6)

Deletar planilhas desnecessárias.

Será que todos os dados da planilha são úteis para alguma coisa? Deve-se deletar tudo o que não for importante.

7)

Salvar como xlsb.

O xlsb é um formato binário, compactado de dados. A planilha pode ficar bem menor.

8) Se a planilha for mesmo muito pesada, pode ser necessário ativar o cálculo manual.

Em arquivos -> opções -> fórmulas, há a opção de mudar o cálculo de automático para manual e vice-versa.

CalcAutomatico.JPG

Este tipo de boa prática já transformou cálculos que demoravam meia hora para poucos minutos.

Alinhar objetos

O Excel possui algumas funções úteis para alinhar objetos.

Suponha que eu tenha vários objetos como na figura.

Para alinhar, deve-se selecionar todos e ir em Formatar -> alinhar ao meio.

A seguir, Formatar -> Distribuir na horizontal.

Há diversas outras opções, como alinhar à esquerda, em cima, embaixo, etc.

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

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

Fórmulas como texto

Este erro é comum quando baixamos relatórios de outros sistemas, como BI.

Às vezes, o campo que deveria ser uma fórmula não faz o cálculo, e sim, fica com a fórmula aparecendo, como no exemplo a seguir.

O motivo disto é que a célula está formatada como texto (string). A solução é mudar a formatação, para número.

Clicar com botão direito na célula com o problema e Formatar células (tecla de atalho: CTRL + 1).

No menu, colocar como Geral, ou Número.

É necessário entrar na célula e dar Enter de novo.

Agora, vai aparecer o valor calculado pela fórmula.

A seguir, contribuição de Michael Wilker.

Também pode ser que esteja com opção de exibição para mostra fórmulas ao invés de resultados;


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

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

Função para detectar campos numéricos

Em relatórios, digamos do BI ou SAP, muitas vezes a informação numérica vem com um # ou alguma outra informação não numérica. Isto pode dar um erro de “Tipos incompatíveis” no código subquente.

Para tratar, podemos utilizar a função “IsNumeric”. Ela retorna verdadeiro se o campo for numérico, e falso se não for numérico (string, data).

If Information.IsNumeric( variável ) Then          

                ‘se for numérico, vai em frente

Else

                ‘se não for, faz algum tratamento

endif

Vide exemplo aqui.


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

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

Procv em dois campos

Dúvida de um colega, que pode ser útil.

Tenho dois campos de critérios, como na figura abaixo. Como fazer um procv com dois campos?

Uma resposta simples: concatenar os valores dos dois campos, com o operador &:

Daí, fazer o procv sobre o campo concatenado:

Vide arquivo aqui como exemplo.



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

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

Como escolher cores arbitrárias no Excel

O Excel oferece muitas cores em sua paleta, porém é possível escolher arbitrariamente uma cor.

Em página inicial -> cores do tema, escolher Mais cores…

Vai aparecer o menu de cores. Ir na aba Personalizar.

Vai aparecer para preencher o código RGB da cor. Este número é uma combinação de Vermelho (Red), Verde (Green) e Azul (Blue), numa escala de 0 a 255 para cada cor (não por coincidência, 256 = 2^8).

Digamos que eu goste da cor amarelo ouro. Esta tem código RGB = (249, 166, 2).

E como saber o código RGB das cores? A internet tem várias fontes, mas para ajudar, o arquivo excel aqui é um simuladorzinho de cores RGB.

É necessário ativar macros para rodar.


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

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

Mudar a linguagem do teclado

Em complemento ao post anterior, de utilizar a autocorreção a seu favor, segue uma dica do amigo Maurício Cota.

O atalho ALT + SHIFT alterna a linguagem do teclado (digamos, de português para inglês).

Os idiomas do teclado devem estar configurados no sistema.

Assim, pode-se utilizar a autocorreção em inglês, por exemplo, quando necessário.

Esta dica é válida para o Windows, transcende o MS Office.


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

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