Ajuda Excel - relatório
comentada em 20/10/2019 18:29

Cara,

Não entendi direito como você vai receber os dados, mas eu acho que se vc organizar as informações um pouquinho melhor, você consegue resolver isso tudo através de uma tabela dinâmica...

Pra isso você não colocaria um conjunto separado de colunas (C até G / H até L etc) pra cada período, mas sim uma coluna indicando o período (1° tri, 2° tri etc) e daí juntaria todas as demais.

Outra coisa que eu entendi (posso ter entendido errado) é que você está fazendo médias dos dados da coluna G, que são porcentagens. Isso é matematicamente incorreto.

@FabioGama

Obrigado pela resposta. Cara, nós recebemos esses dados a cada trimestre e inserimos manualmente nessa planilha. Acontece que precisamos tanto da informação mensal (quanto em determinado mês - não trimestre - a pessoa produziu) e uma informação trimestral sobre a qualidade do trabalho (no trimestre, e não no mês), que eu suprimi do post para simplificar.

Seria possível usar sua sugestão da tabela dinâmica sem ter que perder a informação mensal? Se eu entendi bem, teria que transformar as três colunas mensais em uma única trimestral, é isso?

Sobre a média, você entendeu certo porque eu me expressei errado. O que eu quis exemplificar foi o percentual de pessoas que atingiram. Por exemplo, se duas pessoas de quatro atingiram, eu fazia esse procedimento pra chegar aos 50%. No olhômetro

Não tou com muito tempo pra ver o problema direito mas acredito que uma reorganização dos dados já resolva boa parte dos seus problemas. 

E o problema de figuras aqui na área de tech é que o símbolo de emoji fica em cima do das figuras no desktop. Era algo que eu até ia falar pro @gustavo e nunca lembrei rs mas clicando no cantinho dá pra colocar figuras.

@gustavo olha aqui o problema das figuras na área. Acho que o símbolo a mais de código empurra o de emoji pra cima do de figuras.

@guilherme.

"Se eu entendi bem, teria que transformar as três colunas mensais em uma única trimestral, é isso?"

Em princípio sim, basta adicionar uma coluna para indicar a que mês o dado se refere, daí você pode usar a pivot table/ tabela dinâmica para consolidar os dados.

Se você nunca usou pivot table sugiro dar uma estudada nessa parte. Depois que você pega o jeito, é muito mão na roda, você não vai querer fazer de outra maneira. 

Outra dica:

"Cara, nós recebemos esses dados a cada trimestre e inserimos manualmente nessa planilha."

Dá uma pesquisada sobre as funções "obter dados" do menu Dados.
Outro dia eu abri um post aqui no fórum sobre isso.
Basicamente ele "puxa" os dados de diversos arquivos em uma pasta, e você não precisa importar na mão.

@ThiagoDV  
Obrigado pela sugestão. Quando você tiver um tempo, pode dar uma luz sobre como posso organizá-los melhor? Pensei agora em transpor, mas do jeito que imaginei não ajudaria. Não sei como me livrar desse formato "triangular" que a planilha toma, com as primeiras colunas cheias de informação e as últimas com quase nenhuma. Concordo com você que tem espaço pra melhorar a organização, só não estou sabendo por onde começar.
@FabioGama
Valeu, já tou fuçando a tabela dinâmica e vou conferir as outras coisas também. Obrigado.

@guilherme.

A solução para o seu problema é bastante simples porém passa necessariamente pela reorganização dos dados NA ENTRADA, pelo que eu pude entender da forma como os dados são tratados por ti.

O ideal é que a base de dados seja montada no maior nível de detalhamento possível (ex. visão mensal, por vendedor, com todos os detalhes que você quiser) e, a partir desta base bem robusta e detalhada, você consegue montar uma tabela dinâmica contendo os filtros e agrupamentos desejados (campos calculados, agrupamento de período por trimestre, semestre, anual, acumulado do ano ou equipes de trabalho, regionais, etc) e também os indicadores que você utiliza com maior ou menor frequência.

Dica de ouro: Não transformar/calcular nada na base de dados, apenas na tabela dinâmica. Qualquer cálculo realizado na base de dados via fórmulas, por exemplo, vai pesar o arquivo, o que faz com que ele fique demorado para abrir, fechar e salvar.

Partindo pro prático e pegando carona no que o @FabioGama sugeriu, você pode tentar montar a base da seguinte forma:

Colunas
A - Nome
B - Mês da Avaliação
C - Mês da 1a. Avaliação (fixo por vendedor, pode ser um procv para uma tabela auxiliar)
D - Meta
E - Meta Ajustada
F - Realizado

Linhas
Entrada de dados a nível MENSAL

Montada a base, selecionar (Inserir > Tabela Dinâmica), seleciona o range dos dados e, na tabela criada, você pode agrupar os dados do jeito que quiser. Indo além, você pode criar um campo calculado em Opções de Tabela Dinâmica > Campos > Campo Calculado) e daí montar os indicadores que você deseja.

Como material de estudo, sugiro pesquisar sobre estrutura e montagem de Tabelas Dinâmicas (o básico já serve) e, depois, campos calculados e agrupamento.

No ORUTUF, para ficar supimpa e de bem com a vida, quando já estiver com uma base bem estruturada e robusta, sugiro partir para o estudo de PowerBI.

Não me recordo se podemos indicar aqui mas seguem alguns cursos gratuitos que fiz e recomendo (oficiais da Microsoft, em inglês).

Introduction to Data Analysis using Excel | edX
Analyzing and Visualizing Data with Excel | edX
Analyzing and Visualizing Data with Power BI | edX

O campo de aplicação deste conhecimento básico é enorme.

Bons estudos !

@Miano
Sensacional, muito obrigado! Continuo aqui quebrando cabeça, mas já começo a ver como essa tabela dinâmica pode quebrar muito o galho no meu caso.
Ainda hoje começo os cursos com certeza.

@Miano

Legal o roteiro, é por aí mesmo. Obrigado por compartilhar.

Eu costumo usar os campo calculados na dinâmica pras médias e outros indicadores, mas normalmente eu deixo as coisas mais simples (fórmulas básicas como =MÊS(célula), =ANO(célula) etc) todas à direita da minha tabela com os dados.

@FabioGama

Eu também faço assim quando a tabela tem menos que +/- 500 linhas/15 colunas.

Porém, no meu caso, algumas das bases que usamos tem cerca de 50 colunas e 90 mil FUCKING linhas ou mais, daí fica difícil trabalhar sem ser desta forma. E as fórmulas, mesmo com todo o saneamento possível, ainda ficariam bem extensas, o que só piora.

Outra estratégia que também me fez salvar muito tempo foi utilizado a ferramenta de importação de dados de outras bases onde os cálculos são feitos.

ajustei o posicionamento do icone
quem estava com esse problema, verifique se resolveu por favor

@Miano. Hard user hein? Não cheguei nesse nível...

Mas com essa montanha de dados, não é mais adequado migrar pra um banco de dados tipo Access ou SQL?

O Excel é que nem nosso cérebro... a gente usa só 10% da capacidade dele

heheheheh

@gustavo aqui apareceu certinho

O Miano já deu uma resposta mais completa do que eu seria capaz de dar. Mas o caminho é esse, organizar os dados. 

@gustavo resolveu. 

@FabioGama

Pois é, e é só isso porque eu administro apenas uma das regionais.

Para você ter uma idéia, nem o ERP corporativo (SAP R3) e nem o Access nos atendem no nível de complexidade, volatilidade e velocidade que precisamos para operar o nosso processo (IBP, no caso).

Há alguns sistemas no mercado que oferecem mais ou menos as funcionalidades desejadas porém com muitas limitações de configuração e flexibilidade, então, por isso, criamos nós mesmos um SAP (Sistema Avançado de Planilhas ) baseado em banco de dados interligados que opera todo o processo da mesma forma em todos os escritórios regionais espalhados pelos 4 cantos do mundo.

Funciona muito bem, mas tem que entender como é estruturado para não dar mesbla e sempre manter o sistema de dados limpinho e cheiroso para não poluir as macros.

Só para referência futura, caso alguém algum dia tenha o mesmo problema. Depois de 2 dias tirando toda a informação da planilha antiga para colocar na nova, consegui resolver o problema inicial e agora ficou mil vezes mais fácil a vida. A solução foi só organizar os dados, como sugeriram, em particular eliminando o excesso de colunas e repetindo o nome das pessoas tantas vezes quanto foi necessário.
Agora os dados estão em "tabela excel" e quase tudo consigo extrair dela mesmo. Quando preciso de algo a mais só preciso recorrer à tabela dinâmica.
Obrigado a todos que contribuíram, e muito obrigado em particular pelas dicas de cursos, @Miano , tô na metade do primeiro que você recomendou e tô achando excelente. Difícil até entender como consegui ficar até hoje sem saber essas coisas tão úteis.

@guilherme. Feliz em ajudar !