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 funções que podem ser úteis no Excel.

A função “maior” retorna o maior número de uma lista. Recebe um range de dados (no caso, “A2:A70”), e retorna o maior valor.

Da mesma forma, a função “menor” retorna o menor valor.

=MENOR(A2:A70;1)

=MAIOR(A2:A70;1)

O segundo parâmetro, preenchido como “1” acima, indica que queremos o maior valor. Se fosse 2, seria o segundo maior, 3 o terceiro maior, etc…

Já a função “percentil” é bastante utilizada em estatística e análise de dados.

O input de dados é um range e um número k, o percentual desejado.

Ela retorna um número maior do que k% do universo de dados.

Exemplo, o número 9192 é maior do que 90% dos dados colocados.

=PERCENTIL.INC($A$2:$A$70; 0.9)

Já 2673,4 equivale ao percentil 20%.

Uma utilização comum. Temos que dimensionar o número de atendentes de um call center. Se dimensionar pela média de chamadas, 50% do tempo o call center vai estar sobrecarregado. Faz sentido dimensionar para 80, 90% do percentil de chamadas, dependendo do nível de serviço desejado.

Planilha para download.

Aproveitando o assunto análise de dados, seguem algumas indicações de cursos on-line:

Bolsa da Amazon e a Udacity, de introdução a machine learning. Vai até Julho.

https://sites.google.com/udacity.com/aws-machine-learning/home

Sobre Power BI, a EDX tem o curso a seguir – gratuito para visualização

https://www.edx.org/course/analyzing-and-visualizing-data-with-power-bi-2

A Kaggle é uma plataforma de desafios de data science. Já participei de alguns, e é num nível bem alto, com equipes do mundo todo e prêmios para os melhores colocados. https://www.kaggle.com/

Há opções mais completas (e pagas). Seguem algumas:

Nanodegree em Python, análise e visualização de dados na Udacity

https://www.udacity.com/course/data-analyst-nanodegree–nd002

Python básico, voltado para análise de dados, na EDX

https://www.edx.org/course/analytics-in-python

Para profissionais de Supply Chain:.

https://www.edx.org/micromasters/mitx-supply-chain-management

Como colocar uma linha diagonal numa célula?

É possível colocar uma linha diagonal numa célula do Excel.

Primeiro, selecionar a célula.

Digitar CTRL+1 para entrar em opções da célula, e ir para a aba “Borda”.

Há várias opções, entre elas, a linha diagonal.

Resultado:

Há várias outras opções possíveis, por exemplo.

Vide arquivo para download.

Ideias técnicas com uma pitada de filosofia

https://ideiasesquecidas.com/

Imagens em gráficos

Os gráficos do Excel podem ser preenchidos com cores, padrões, e também com imagens.

Esta última opção por ser bastante útil.

Primeiro, inserir um gráfico comum.

Clicando com o botão direito no gráfico, Formatar Série de dados -> Preenchimento.

Há várias opções de preenchimento. Escolher Preenchimento com imagem.

Deve-se escolher a imagem a ser colocada.

Resultado abaixo.

Há alguns gráficos, como o de área ou de barra, que são bons para este tipo de técnica.

Outro exemplo:

Vide arquivo no OneDrive

Grafico com imagens.xlsx

Veja também:

https://ideiasesquecidas.com/

Intervalos e desloc – solução

Em relação ao post anterior, https://ferramentasexcelvba.wordpress.com/2020/04/08/exercicio-de-intervalos-e-funcao-desloc/

Planilha para download.

A função “corresp” é uma função de busca, similar ao procv. Porém, ela retorna a posição do valor, o índice.

Corresp(valor, range de busca, busca exata ou maior)

Abaixo, corresp da data (06/01/2020) vai retornar 9

Primeira ocorrência (posição)

=CORRESP($H$4;$B$3:$B$206;0) – a busca exata vai dar a primeira ocorrência.

Abaixo, corresp da data (06/01/2020) vai retornar 12

Última ocorrência (posição)

=CORRESP($H$4;$B$3:$B$206;1) – coloquei o parâmetro “1”, então vai buscar o último valor do dia 06/01/2020

Número de transações: pode ser um cont.se, e também pode ser a diferença entre a última ocorrência e a primeira

Primeira ocorrência:  utilizar a função desloc. É como um mapa do tesouro. A partir de um ponto inicial, ande y casas para baixo e x casas para a direita.

=DESLOC($B$2;H11;2)

A referência é a célula B2. Ando para baixo o número de casas da primeira ocorrência. Ando para a direita 2 casas, porque quero buscar o valor.

Primeira ocorrência:  a mesma coisa, usar desloc. Só que ao invés de andar a primeira ocorrência, uso a posição da última ocorrência

=DESLOC($B$2;H12;2)

Para média, mín e máx, o truque é semelhante.

Uso a função =MÉDIA(intervalo)

A questão é definir o intervalo.

Aí, a função desloc ajuda novamente. Ela pode retornar um número, como acima, mas também pode retornar um intervalo.

São 5 parâmetros para o desloc: 1 – ponto de partida, 2 – casas para baixo a deslocar, 3 – casas para direita, 4 – range de linhas do intervalo, 5 – range de colunas do intervalo

O range de linhas é o cont.se da célula H4. O range de colunas é 1.

A fórmula final:

Média =MÉDIA(DESLOC($B$2;$H$11;2;$H$14;1))

Mínimo do dia =MENOR(DESLOC($B$2;$H$11;2;$H$14;1);1)

Máximo do dia =MAIOR(DESLOC($B$2;$H$11;2;$H$14;1);1)

É possível utilizar a função “INDIRETO” para alguns desses cálculos. É a mesma ideia.

Corresp e Desloc são formas indiretas, um pouco mais abstratas, de referenciar dados. É como um ponteiro em C. São um pouco mais avançadas, porém, bastante poderosas.

Ideias técnicas com uma pitada de filosofia

https://ideiasesquecidas.com/

Exercício de intervalos e função desloc

Segue um exercício de intervalos e função desloc.

Tenho uma base de dados como a seguinte.

Quero digitar uma data.

O exercício é calcular:

– A posição da primeira ocorrência da data (em relação ao começo da lista)

– A posição da última ocorrência da data (em relação ao começo da lista)

Número de transações no dia
Valor da primeira ocorrência do dia
Valor da última ocorrência do dia
Média do valor do dia
Mínimo do dia
Máximo do dia

Deve-se utilizar apenas uma fórmula em cada célula da resposta.

Obs. Considerar que os dados acima estão ordenados em ordem crescente de data.

Exemplo: para a data 05/01/2020, tenho os seguintes resultados:

Dica: utilizar funções corresp e desloc.

Gerenciador de cenários – Excel

O Excel tem uma função interessante para gerenciar cenários.

Digamos que eu tenha um preço inicial, e quero testar três cenários de inflação: pessimista, normal e otimista.

Fica em Dados -> Teste de Hipóteses -> Gerenciador de Cenários…

Clicar em adicionar cenário.

Vai pedir o nome do cenário, as células variáveis e comentários.

A seguir, ele vai pedir o valor da célula variável para este cenário:

Repetir a operação para os demais cenários.

Para mostrar o cenário, escolher o mesmo e clicar em Mostrar.

É comum o usuário se perder, num cálculo longo de um Excel complexo – digamos, fazer orçamento.A técnica mostrada pode ser útil para gerenciar cenários assim

As Linguagens de Analytics

No último fórum da Informs (a mais importante associação americana de Operations Research), em Chicago, citaram Pythons umas 6 vezes, Excel também umas 6 vezes, Java uma vez (de um fornecedor que disse que estava mudando para Python), R nenhuma mênção.

Isto mostra a força do Python como a língua franca do Analytics da atualidade.

O pessoal que citou Excel o fez metade das vezes para falar mal, outra metade para dizer que o usuário final utiliza. Isto mostra a resiliência do Excel, que apesar de todas as críticas, continua firme e forte nas grandes corporações – por seu poder e facilidade de uso. Há até uma piada que diz: “Todo o sistema financeiro mundial é baseado em Excel”.

Um último comentário: no final das contas, não interessa muito a linguagem, e sim ter uma base teórica forte e capacidade de execução. Linguagens e ferramentas vêm e vão. Até hoje tem gente utilizando Fortran muito bem, por exemplo.

https://ideiasesquecidas.com/

Função Arrumar

A função “Arrumar” é bastante simples e útil.

Ela elimina espaços em branco antes e após a palavra.

É comum buscarmos informação em alguma fonte de dados, e ela ficar com espaços no início ou fim da palavra, como na coluna A abaixo.

O ruim de espaços em branco é que atrapalha funções de busca como o procv.

A coluna B aplica a função arrumar.

Segue exemplo para download.