Home > Dicas

Criando planilha para calcular distâncias rodoviárias

Aprenda um método mais preciso de encontrar informações em uma tabela do Excel

Por Fábio Vianna, especial para a PC WORLD*

27/09/2007 às 18h15

Foto:

Aprenda um método mais preciso de encontrar informações em uma tabela do Excel

Vamos imaginar que você queira, rapidamente, calcular a distância rodoviária entre duas cidades e tenha se deparado com uma tabela como a do arquivo em Excel a seguir:

distrodexcel01

Uma das formas de procurar as distâncias é a de “pescar”, cruzando a cidade e origem com a de destino e ver a distância, mas como são muitas linhas, você pode se confundir.

Mostrarei, a seguir, uma forma simples e rápida de simplificar esta busca, e saiba que este método pode ser aplicado aos mais variados tipos de buscas de dois dados simultâneos em tabelas.

Com este texto, você conhecerá três recursos úteis do Excel: as funções PROCV e CORRESP e o Recurso de Validação de Dados.

Então, a primeira coisa é termos a tabela de distância. Achei uma excelente no site do Departamento Nacional de Infra-Estrutura de Transportes, com a vantagem de já estar em Excel.

Depois de colarmos esta tabela em uma planilha, vamos criar a tabela base de pesquisa, como segue no exemplo abaixo:

distrodexcel02

distrodexcel03Para não haver erros na hora de digitar a cidade de origem e a de destino, vamos criar a chamada Validação de Dados. Para isso, vá até a célula C8 (onde você irá selecionar a cidade de origem) e em seguida clique no menu Dados, Validação:++++

Na tela que aparecerá, vamos configurar na aba Configurações:

distrodexcel04

Em Permitir, selecione a opção Lista. Em Fonte (atenção: dependendo da versão do Excel, aparece origem ao invés de fonte – é a mesma coisa) selecione a lista de cidades de origem (em nosso exemplo, da B15 até a B125).

distrodexcel05Agora, em Mensagem de Entrada, você digita o que quiser que a pessoa leia quando selecionar a célula C8. Colocaremos em nosso exemplo:++++

distrodexcel06Em Alerta de Erro, colocamos uma mensagem que irá aparecer no caso da pessoa digitar um nome de cidade que não existe, ou ainda digitar um nome errado. Deixamos a opção de Alerta como Parar (dessa forma o Excel não permite que seja aceito nome errado) e colocamos nas mensagens.

Agora, fazemos o mesmo procedimento na célula C9 (Cidade de Destino):

distrodexcel08

distrodexcel09Agora, na célula C10, iremos colocar a função que irá buscar a distância com base na cidade de origem e na de destino. Na C10, clique em Inserir, Função:++++

distrodexcel10Na Categoria da Função escolhemos Procura e Referência (dependendo da versão de Excel, a tela é diferente) e em Selecione uma Função, selecionamos o PROCV.

Clique em OK. Em Valor Procurado selecionamos a célula C8, que contém nossa cidade de Origem. O Procv tem como padrão procurar sempre o que aparece na 1ª coluna de nossa base de dados, o que, nesse caso, é a Cidade de Origem.

distrodexcel11Em Matriz_tabela, selecionamos toda a tabela de distâncias, da célula B15 até a AM125. Veja:

O Núm_indice_coluna representa o número da coluna da cidade de destino em relação ao início da tabela.

Se procurássemos a cidade de Belém em nosso exemplo, seria a coluna 3, porque nossa Matriz_tabela começa na B, e Belém está na D – o que significa 3ª coluna em relação à Matriz_tabela (fique atento a isto, porque a maior parte das pessoas que se perde com o PROCV se confunde com isso achando que é a coluna da planilha – não é!).

Não podemos, neste caso, ficar digitando o número da coluna da cidade toda vez que mudarmos a cidade de destino. Para resolver isto, vamos usar a função CORRESP. Esta função funciona como o índice de um livro, que diz em que página está determinado assunto. O que ela vai nos responder é: em que coluna está Belém? E Fortaleza?++++

Para colocarmos esta função no campo Núm_indice_coluna, clique no campo e em seguida clique na seta indicada na imagem abaixo:

distrodexcel12

Clique, a seguir, em Mais funções (veja imagem acima) e aparecerá novamente a tela de função. Selecione na categoria Procura e Referência e em seguida o CORRESP:

distrodexcel13

++++

Na tela do CORRESP, colocamos:

Valor_procurado: é a cidade de destino, que informamos na célula C9
Matriz_procurada: é a tabela com as cidades. Note que precisamos marcar a partir da coluna B (que está vazia) e não da C. Isso porque se escolhermos como cidade de destino Aracaju, no PROCV ela deve ser a coluna 2. Para que seja 2, precisamos começar o intervalo na mesma coluna onde começa o intervalo do PROCV.
 
Tipo_correspondência: para garantir que ele não procure por aproximação, e sim exatamente o nome que informarmos na C9, coloque aí um 0 (zero). Isso fará com que a função procure o que digitamos e não aproximadamente o que digitamos. Em muitos casos não colocar a opção de busca exata (o zero) pode fazer com que ele retorne um dado inválido.

Nossa tela ficará:

distrodexcel14

Não dê OK. Veja a barra de fórmulas:

distrodexcel15

Note que a função CORRESP está em negrito e que no final dela temos dois parênteses. Lembre-se de que inserimos o CORRESP dentro do PROCV (isso se chama aninhamento de função) e agora precisamos retornar ao PROCV. Para fazer isto, clique à direita do último parêntese para voltar ao PROCV.

Agora, para finalizar, coloque em Procurar_intervalo o valor de zero (ou escreva falso). Isso para o PROCF procurar exatamente o que foi digitado (similar ao CORRESP).

Depois disso, dê OK (se aparecer um #N/D! é porque não há cidade de origem nem destino selecionada!).

Selecione uma cidade de origem, uma de destino, e você já verá na célula C10 a distância entre elas.

  • Caso você tenha se perdido no processo e não queira repetir todos os passos, a planilha pronta está disponível para download. Basta clicar aqui.
  • Para conhecer outras dicas bacanas do Excel, clique aqui.

*Fábio Vianna é bacharel em Administração de Empresas pela EAESP/FGV, sócio-diretor do Centro de Excelência em Planilhas e tem mais de 10 anos de experiência em modelagem de planilhas

Tags

Junte-se a nós e receba nossas melhores histórias de tecnologia. Newsletter por e-mail Newsletter por e-mail