Análise Regional de Dados – Excel na Prática Aula 1

Nessa aula vamos te mostrar como fazer uma análise regional de dados utilizando segmentação de dados e mapa coroplético para melhor visualização!

A análise regional de dados examina informações separadas por região geográfica — estados, cidades ou zonas — para comparar desempenho e identificar padrões locais. No Excel, você pode fazê-la usando segmentação de dados para filtrar por região e o mapa coroplético (gráfico de mapa) para visualizar os valores com cores por área.

Caso prefira esse conteúdo no formato de vídeo-aula, assista ao vídeo abaixo ou acesse o nosso canal do YouTube!

O que você aprende neste vídeo

Resposta rápida: A aula ensina a analisar vendas por estado no Excel partindo só do DDD do cliente, até o mapa que muda junto com o filtro. Você aprende a trazer a tabela de DDD por estado, preencher a coluna com PROCV, montar a tabela dinâmica, filtrar com segmentação de dados e criar o mapa coroplético a partir de um espelho.

Neste vídeo (23 min):

• 1:00 - A base de vendas com produto, data, DDD e forma de pagamento, e a análise por região
• 3:00 - Consulta da tabela de DDD por estado na Wikipédia para trazer a lista ao Excel
• 4:00 - Colagem numa planilha nova, exclusão da coluna de cidades e remoção dos hiperlinks
• 6:00 - Linha de cabeçalho e nova coluna Estado criadas com o atalho Ctrl+Shift++
• 8:00 - Por que preencher 2.255 linhas à mão não se sustenta e quais fórmulas resolvem o cruzamento
• 9:00 - PROCV com o DDD como valor procurado, matriz tabela na outra planilha e índice de coluna 2
• 11:00 - Duplo clique na alça de preenchimento para replicar a fórmula em toda a coluna
• 13:00 - Seleção da base com Ctrl+T e inserção da tabela dinâmica em uma planilha nova
• 14:00 - Estado nas linhas e nos valores, e a segmentação de dados filtrando um estado por vez
• 15:00 - Espelho com =A4 em células fora da tabela dinâmica e a retirada das linhas de grade
• 17:00 - Gráfico de mapa coroplético apontado para o espelho, com rótulo de dados no visual
• 19:00 - Segunda tabela dinâmica e segunda segmentação para comparar dois estados lado a lado

Trechos do vídeo:

• Um mesmo estado tem vários DDDs, então a coluna de estado precisa sair de uma fórmula, e não de digitação
• No PROCV, o 0 no último argumento exige correspondência exata e evita que um DDD parecido devolva o estado errado
• O Excel não cria gráfico de mapa direto de uma tabela dinâmica: a saída é espelhar as células com = fora dela
• Mapa coroplético e segmentação de dados só existem nas versões mais recentes do Excel

Para receber a planilha que usamos na aula no seu e-mail, preencha:

Como Fazer uma Análise de Dados no Excel

Você sabe os passos que precisa fazer para criar uma análise de dados no excel?

Nessa aula vamos te ensinar como fazer uma análise regional de dados no Excel. Como utilizar os dados para poder fazer essas análises.

Vamos mostrar os passos para fazer essas análises e ainda vamos te mostrar como fazer essa análise com segmentação de dados e um mapa coroplético.

Desta forma sua análise fica mais completa e muito mais visual, o que é uma ótima maneira para melhorar suas apresentações.

Como Fazer uma Análise Regional de Dados no Excel

Nesta aula vamos trabalhar com uma análise regional, então vamos pegar nossa base de dados e criar um relatório para que possamos fazer uma análise comparativa entre duas regiões a escolha do usuário.

O primeiro passo é ter essa base de dados e verificar quais as informações temos nela para que possamos ter uma ideia do que vamos construir.

Aqui temos uma base de dados de alunos (lembrando que são nomes e e-mails fictícios).  Dentro dessa base vamos contabilizar quantos alunos temos por região.

Só que se reparar, nós não temos essas informações logo de cara na tabela, mas temos a informação de DDD que pode nos trazer essa informação de localidade.

Então nosso próximo passo é fazer uma busca rápida na internet de uma tabela que contenha DDD e o estado correspondente aquela informação.

Essa lista em particular foi obtida no site abaixo que já possui uma lista completa e mais fácil de ser copiada: https://pt.wikipedia.org/wiki/Discagem_direta_a_dist%C3%A2ncia

Agora basta copiar esses dados, colar no Excel através da opção de Colar para que fique sem os formatos da página.

Feito isso basta eliminar a coluna de Cidade/Região, pois não vamos utilizá-la nessa análise, vamos levar em consideração apenas o Estado.

Com isso temos uma lista com os prefixos e o estado correspondente a cada um deles, dessa forma podemos acrescentar uma nova coluna na nossa base de dados, ao lado do DDD para buscar o estado referente ao aluno.

Aqui vamos utilizar a famosa fórmula PROCV para fazer essa busca na tabela que acabamos de colar.

Com isso teremos uma coluna somente com os estados, então agora já podemos iniciar nossa análise em relação as regiões, pois pegamos as informações de DDD e limitamos elas a cada um dos seus respectivos estados.

Fizemos isso porque estado pode ter mais de 1 DDD, então era necessário esse procedimento prévio para facilitar a análise.

Agora com essa tabela completa podemos iniciar a construção de duas Tabelas Dinâmicas para fazer uma comparação entre as quantidades de alunos de cada estado.

E para cada tabela vamos fazer uma segmentação de dados utilizando a coluna de estado para facilitar nossa comparação.

Vamos fazer duas dessa mesma maneira, para que possamos dar ao usuário a opção de escolher 2 regiões e fazer a comparação entre elas.

Feito isso vamos ter que copiar os dados que estão aparecendo na tabela para uma célula comum, pois não é possível a utilização do mapa coroplético em tabelas dinâmicas.

Então o que vamos fazer é simplesmente ir até uma célula vazia e igualar ela ao estado e logo ao lado igualar a quantidade de alunos.

É um passo bem simples, mas é necessário para que esse mapa funcione, pois nós vamos criar o mapa através desses dados ao invés dos dados que temos na tabela dinâmica.

Para a criação desse mapa vamos selecionar essas duas células de estado e valor, vamos até a guia Inserir, em seguida em Mapas e por fim em Mapa Coroplético.

OBS: Esse mapa só estará disponível nas versões mais recentes do Excel, então se estiver utilizando uma versão mais antiga é provável que não tenha essa ferramenta.

Feito isso, basta repetir o mesmo procedimento na outra tabela dinâmica para que o usuário possa fazer a comparação entre as duas regiões.

Um ponto importante dos mapas em geral é que eles precisam que os nomes dos estados estejam corretos, então é interessante que faça essa verificação.

Neste exemplo temos um caso que pode vir a gerar um problema, que é quando temos o DDD 61 que ele se refere a duas regiões ao mesmo tempo.

E com isso o Excel não consegue identificar de qual região estamos tratando, então para fins de exemplo você pode alterar para deixar somente Distrito Federal por exemplo. Assim vai evitar erro na hora de visualizar esses dados.

Agora temos uma análise regional dos nossos dados e o usuário pode fazer a comparação entre duas regiões apenas selecionando quais regiões deseja comparar.

Perguntas frequentes

Conclusão

Viu como foi simples a criação de uma análise regional? O único passo a mais que tivemos que fazer foi buscar pela referência dos DDDs para saber qual estado corresponde a cada número.

Feito isso o restante é bem fácil, temos a criação da tabela dinâmica com a segmentação de dados para facilitar a seleção dos dados.

E temos o mapa coroplético que dá uma aparência mais profissional ao nosso relatório, mostrando o mapa de cada uma das regiões selecionadas.

Agora é necessário praticar para fixar ainda mais esse conteúdo, pois quanto mais prática tem no Excel mais fácil e natural vai conseguir criar seus relatórios e análises.

Hashtag Treinamentos

Para acessar outras publicações de Excel Intermediário, clique aqui!

---

Quer aprender tudo de Excel para se tornar o destaque de qualquer empresa?

---