Voltar para: Excel avançado

Motorização dos estados brasileiros

Funcionalidades usadas nesta atividade:

• Consulta a dados em texto delimitado.
• Consulta a dados na Web.
• Função VALOR.
• Função SUBSTITUIR.
• Função PROCV.

Nesta atividade vamos encontrar qual é a proporção entre veículos em circulação e população de cada estado brasileiro. Para isso, teremos que buscar os dados em fontes distintas e externas. O número de veículos registrados em cada estado virá de um relatório da Anfavea e os dados de população por estado virão da Wikipedia.

Consulta a dados de texto delimitado

Os dados da Anfavea estão em formato PDF. Inicialmente, copiamos esses dados e colamos em um arquivo de texto. Dessa forma, será possível criar uma consulta no Excel.

Vamos criar uma consulta baseada em um arquivo de texto. Faça assim:

1. Na aba Dados, clique em Obter dados externos/De texto.
2. Encontre o arquivo de texto que contém as informações desejadas e importe.
3. Selecione o delimitador adequado. Em nosso caso é o espaço.
4. Conclua a importação de dados.

O Excel vai criar uma tabela com as informações sobre a frota de veículos em cadas estado brasileiro.

Observe que nem tudo aconteceu como desejado, pois nos estados que têm nomes compostos as colunas ficaram desorganizadas. Isso ocorreu porque usamos o espaço como delimitador, mas não havia alternativa melhor. Como são poucos registros com problema faça a exclusão manual das células indesejadas.

Com a tabela importada, vamos calcular quantos veículos estão registrados em cada estado.

1. Crie uma coluna com o nome Veículos total.
2. Na primeira célula, faça o cálculo de cada célula multiplicando o total de veículos no país pela porcentagem do estado.
3. Copie a fórmula para as demais células. Não esqueça da referência absoluta.

Consulta a dados da web

A população de cada estado brasileiro está disponível na Wikipedia no endereço:

https://pt.wikipedia.org/wiki/Lista_de_unidades_federativas_do_Brasil_por_popula%C3%A7%C3%A3o

Para trazer os dados para a planilha faça assim:

1. Na aba Dados, clique em Nova consulta/De outras fontes/Da Web.
2. Informe a URL da página em que serão pesquisados os dados.
3. Aguarde o Excel localizar as tabelas disponíveis na página.
4. Selecione a tabela com as informações desejadas.
5. Clique em Editar para acessar o editor de consultas.
6. Selecione apenas duas colunas com as informações de nome do estado e sua população.
7. Feche o editor e carregue os dados.

Observe que os dados foram carregados como texto. É preciso tratar a coluna da população.

Primeiro vamos remover os caracteres não numéricos com a função SUBSTITUIR.

1. Crie uma coluna à direita das demais e nomeie como Remoção de espaços.
2. Aplique a função SUBSTITUIR na primeira célula e remova os espaços do texto à esquerda.

Agora, vem a conversão de texto para número.

1. Crie uma coluna à direita das demais e nomeie como Conversão para número.
2. Na primeira célula, aplique a função VALOR que converte texto em número.

Motorização dos estados

Os dados estão preparados para o que precisamos fazer.

1. Crie uma planilha nova e nomeie como Motorização.
2. Crie uma tabela com as seguintes colunas: Estado, População, Veículos e Motorização.
3. Na coluna Estado, copie e cole os nomes dos estados brasileiros que estão nas outras tabelas.
4. Na coluna da população insira uma função PROCV.
5. O valor procurado é o nome do estado.
6. A matriz onde procurar é a coluna de estados da planilha População.
7. A coluna de resultados é a da população.
8. Na coluna dos Veículos insira uma função PROCV.
9. O valor procurado é o nome do estado.
10. A matriz onde procurar é a coluna de estados da planilha Frota.
11. A coluna de resultados é a de veículos.
12. A motorização é calculada dividindo a população pela frota do estado.

Lembre que para a função PROCV funcionar corretamente é preciso classificar a tabela pela primeira coluna.

O resultado deve ficar assim: