Planilhas Inteligentes (Distância entre Locais) - Google Maps

Informática Geral Manutenção de Computadores
Planilhas Inteligentes (Distância entre Locais) - Google Maps
Carlos R.
em 23 de Setembro de 2014

Bom Dia, Turma!

É sempre bom viajar e arejar as idéias após longos meses de estudos, trabalhos e o estresse acumulado devido à rotira que vivemos.

Programar uma viagem exige uma série de cuidados, como organizar um roteiro, verificar locais para descanso, dimensionar a bagagem, verificar a manutenção do carro e, é claro, conhecer as rotas e as distâncias entre as cidades. Sei que hoje a tecnologia proporciona uma série de facilidades neste último quesito, tais como o GPS e o famoso WAZE, que indica até se na pista há buracos ou se existe um guarda do CET multando!!hehehe

Este artigo têm como intuito incorporar ao Excel a função:

 

=Km_Distancia(Local de Origem; Local de Destino)

 

Esta função é criada com programação VBA, sendo assim, devemos seguir os seguintes passos:

 

Verificar se a guia Desenvolvedor está habilitada, caso não esteja, siga o seguinte caminho:

Arquivo => Opções => Personalizar Faixa de Opções => Selecionar Desenvolvedor na lista a direita


 

Entrar no Visual Basic na guia Desenvolvedor:

Criar um Módulo para inclusão do código:

Inserir => Selecionar Módulo

 

Incluir o Seguinte Código no Módulo:

Function Km_Distancia(Origin As String, Destination As String) As Double
    'Requer referência ao: 'Microsoft XML, v6.0'

    Dim Solicitacao As XMLHTTP60
    Dim Doc As DOMDocument60
    Dim Distancia_Pontos As IXMLDOMNode

    Let Km_Distancia = 0

    'Checa e limpa as entradas
    On Error GoTo Sair

    Let Origin = Replace(Origin, " ", "%20")
    Let Destination = Replace(Destination, " ", "%20")

    ' Le os dados XML da API do Google Maps.
    Set Solicitacao = New XMLHTTP60

    Solicitacao.Open "GET", "http://maps.googleapis.com/maps/api/directions/xml?origin=" _
        & Origin & "&destination=" & Destination & "&sensor=false", False
    Solicitacao.send

    ' Tornando o XML legível por usar o XPath
    Set Doc = New DOMDocument60

    Doc.LoadXML Solicitacao.responseText

    ' Obtendo o valor da distância entre os nós.
    Set Distancia_Pontos = Doc.SelectSingleNode("//leg/distance/value")
    If Not Distancia_Pontos Is Nothing Then Km_Distancia = Distancia_Pontos.Text / 1000

Sair:
    ' Tidy up
    Set Distancia_Pontos = Nothing
    Set Doc = Nothing
    Set Solicitacao = Nothing
End Function

 

Habilitar a Biblioteca: 

Ferramentas => Referências => Selecionar 'Microsoft XML, v6.0'

 

 

Exemplo da Função Km_Distancia:

- A B
1 DISTÂNCIA ENTRE LOCAIS
2 ORIGEM: Sao Paulo
3 DESTINO: Diamantina
4 DISTÂNCIA: =CONCATENAR(INT(Km_Distancia(B2;B3));" ";"km")

Obs.: Os nomes das cidades devem ser digitados sem acentos ou caracteres especiais.

 

- A B
1 DISTÂNCIA ENTRE LOCAIS
2 ORIGEM: Sao Paulo
3 DESTINO: Diamantina
4 DISTÂNCIA: 858 km

 

Referência:

Consultor e Professor: Ivair Claudio Ferrari.



Um grande abraço a todos, espero que tenham gostado.

Carlos Roberto Roa

São Paulo / SP
Curso Livre: Microsoft Excel Avançado (Acadêmicos do Excel)
Graduado em Engenharia de Produção Mecânica pelo Instituto Mauá de Tecnologia (IMT) atuo com Inteligência de Mercado e administro o site Acadêmicos do Excel, ambiente com um vasto conteúdo de ensino sobre Excel. Já ministrei aulas particulares e suporte em cursinho pré vestibular em Matemática e Fís ...
Oferece aulas presenciais
R$ 70 / aula
Conversar Whatsapp do professor Carlos R. Whatsapp do professor Carlos R. WhatsApp
Cadastre-se ou faça o login para comentar nessa publicação.
Felipe O.
em 13 de Fevereiro de 2019

Bom dia Silmar, poderia compartilhar o código com a correção comigo por favor?
foranges@gmail.com

Cadastre-se ou faça o login para comentar nessa publicação.
Jhonny M.
em 11 de Fevereiro de 2019

Buenos dias soy Jhonny de Peru, tengo el mismo problema, me sale 0 al querer calcular, le agradeceria que me ayude con algun otro procedimiento o en que me podria estar equivocando, muchas gracias de antemano

Cadastre-se ou faça o login para comentar nessa publicação.
Luiz I.
em 08 de Fevereiro de 2019

Bom dia, Silmar.

Qualquer município que escolho para realizar a formula, me retorna "0", como eu insiro a nova Key na Função no VBA, poderia me ajudar? que outros procedimentos você fez?, meu e-mail: Luizishimoto@gmail.com

Cadastre-se ou faça o login para comentar nessa publicação.
Marcus V.
em 07 de Fevereiro de 2019

mago.marcus@hotmail.com

Cadastre-se ou faça o login para comentar nessa publicação.
Marcus V.
em 07 de Fevereiro de 2019

boa noite
fiz todo o procedimento porem a calculo da distancia fica sem 0 km

Cadastre-se ou faça o login para comentar nessa publicação.
Silmar S.
em 09 de Fevereiro de 2019

Marcus, provavelmente o problema está na criação da sua credencial no Google Cloud

Cadastre-se ou faça o login para comentar nessa publicação.
Deoclério L.
em 02 de Janeiro de 2019

Bom dia! Alguém conseguiu incluir a KEY do API no código do VBA do módulo? Já tentei várias alternativas e só retorna zero. Por gentileza, se puder, enviar no meu email deolubefilho@brturbo.com.br. Grato!

Cadastre-se ou faça o login para comentar nessa publicação.
Silmar S.
em 23 de Janeiro de 2019

Boa tarde! estou enviando o código para o seu e-mail.

Cadastre-se ou faça o login para comentar nessa publicação.
Jullia B.
em 31 de Janeiro de 2019

Oi , poderia me ajudar enviado o codigo para mim tbm :)

Jullia.bsouza@gmail.com

Cadastre-se ou faça o login para comentar nessa publicação.
Jullia B.
em 31 de Janeiro de 2019

Oi , poderia me ajudar enviado o codigo para mim tbm :)

Jullia.bsouza@gmail.com

Cadastre-se ou faça o login para comentar nessa publicação.
Francisco B.
em 01 de Fevereiro de 2019

Boa Tarde!!! Poderia me mandar a solução deste erro??
Grato pela atenção e ajuda

Cadastre-se ou faça o login para comentar nessa publicação.
Francisco B.
em 01 de Fevereiro de 2019

Deoclerio, por gentileza, poderia compartilhar a solução deste erro??
Segue e-mail: fbfreithas@gmail.com

Cadastre-se ou faça o login para comentar nessa publicação.
Rodolfo N.
em 04 de Fevereiro de 2019

Bom dia, poderia compartilhar comigo também? estou com o mesmo erro, emila rodolfo-napoleao@hotmail.com

Cadastre-se ou faça o login para comentar nessa publicação.
Silmar S.
em 09 de Fevereiro de 2019

Enviado!

Cadastre-se ou faça o login para comentar nessa publicação.
Silmar S.
em 27 de Novembro de 2018

Boa tarde, consegui ajustar a macro e estou utilizando foi preciso acessar o Google Cloud Platform e ativar as API, criar projeto, criar credencial e etc

Cadastre-se ou faça o login para comentar nessa publicação.
José O.
em 29 de Novembro de 2018

Boa tarde SIlmar, consegui gerar a credencial no Claud, so não estou encontranto o local e a forma como coloco a key no codigo acima, pode me ajudar? mandar um print do seu ou algo do tipo? joseomjunior@gmail.com whatzap 21 9 9583-2537

Cadastre-se ou faça o login para comentar nessa publicação.
Natália L.
em 18 de Janeiro de 2019

Silmar, poderia me ajudar? Eu criei o key no google mas não consegui incluir na função, poderia nos compartilhar como você fez? Obrigada
Meu e-mail: nati-zinhaa@hotmail.com

Cadastre-se ou faça o login para comentar nessa publicação.
Aldo C.
em 23 de Janeiro de 2019

Bom dia, Silmar.

Estou tendo a mesma dificuldade dos demais colegas, como eu insiro a nova Key na Função no VBA, poderia me ajudar? que outros procedimentos você fez?, meu e-mail: aldocsa@hotmail.com

Cadastre-se ou faça o login para comentar nessa publicação.
Silmar S.
em 23 de Janeiro de 2019

Boa tarde! Natália e Aldo estou enviando o código para o e-mail de vocês.

Cadastre-se ou faça o login para comentar nessa publicação.
Francisco B.
em 01 de Fevereiro de 2019

Silmar, boa tarde!

Teria como enviar por e-mail a solução que você fez? fbfreithas@gmail.com

Cadastre-se ou faça o login para comentar nessa publicação.
Silmar S.
em 02 de Fevereiro de 2019

Francisco B., enviado.

Cadastre-se ou faça o login para comentar nessa publicação.
Almir J.
em 07 de Fevereiro de 2019

Silmar, boa noite! será que voce poderia compartilhar no meu email o codigo que voce fez? grato desde já, ALMIR
almirvalverdejr@yahoo.com.br

Cadastre-se ou faça o login para comentar nessa publicação.
Silmar S.
em 09 de Fevereiro de 2019

Enviado!

Cadastre-se ou faça o login para comentar nessa publicação.
Flavia F.
em 26 de Novembro de 2018

Bom dia pessoal, tudo bem?
Estou tendo dificuldades em rodar a macro, alguem poderia compartilhar comigo o excel, por favor?
Ajudaria muito!!!

Cadastre-se ou faça o login para comentar nessa publicação.
Flavia F.
em 26 de Novembro de 2018

meu email é flaviajunqueirafreitas@gmail.com

Cadastre-se ou faça o login para comentar nessa publicação.
Alvaro F.
em 25 de Novembro de 2018

coloquei Curitiba e Recife, retorna 0. Não funcionou.

Cadastre-se ou faça o login para comentar nessa publicação.
Zadir C.
em 22 de Novembro de 2018

Amigos, bom dia. Notei que o código é super completo e permite a pesquisa até por nome de cidade. Entretanto, o que eu quero é algo bem simples: eu tenho uma planilha que uso no trabalho para calcular a distância entre a residência do aluno e a escola onde ele está matriculado. Não consegui adaptar o código fornecido pra isso. Li, em um comentário, também, a respeito de uma API grátis que a pessoa criou e permitiu que fizesse 2.500 consultas ao dia. Era basicamente isso que eu queria: usar o código na planilha que eu já montei e poder fazer, no máximo, essas 2500 consultas (a utilização da minha planilha não chega nem perto dessas 2500 consultas/dia; talvez atinja umas 300, no máximo). Alguém poderia me ajudar? zadir.junior@mpap.mp.br

Cadastre-se ou faça o login para comentar nessa publicação.
Renato T.
em 31 de Outubro de 2018

Bom dia;

já uso está macro a algum tempo e agora atualizei o office para 2019 e não roda mais.

Poderia me auxiliar ?

Cadastre-se ou faça o login para comentar nessa publicação.
Renato T.
em 31 de Outubro de 2018

Bom dia;

já uso está macro a algum tempo e agora atualizei o office para 2019 e não roda mais.

Poderia me auxiliar ?

Cadastre-se ou faça o login para comentar nessa publicação.
Carlos A.
em 19 de Novembro de 2018

Renato!
O problema está na API do Goolgle conforme informado no link: https://github.com/asgunzi/EncontraDistanciasVBA

Cadastre-se ou faça o login para comentar nessa publicação.
Eduardo B.
em 15 de Outubro de 2018

Boa noite, estive usando sua macro o ano todo, mas tive que restaurar meu computador e instalei novamente o office 16 e agora não consigo fazer funcionar de jeito nenhum. Esta habilitado o 6.0 e modulo com exato texto mas fica sempre zerado agora. Pode me ajudar?

Cadastre-se ou faça o login para comentar nessa publicação.
Carlos A.
em 19 de Novembro de 2018

Eduardo!
O problema está na API do Goolgle conforme informado no link: https://github.com/asgunzi/EncontraDistanciasVBA

Cadastre-se ou faça o login para comentar nessa publicação.
Luiz R.
em 16 de Julho de 2018

Sua planilha é muito interessante e útil, parabéns pelo trabalho. O único inconveniente é que ele entende que algumas cidades pertencem a outros estados, então o cálculo fica esquisito.

Cadastre-se ou faça o login para comentar nessa publicação.
Blendo S.
em 04 de Junho de 2018

Boa tarde,

fiz tudo conforme a explicação, porem nao puxa a distancia.

Cadastre-se ou faça o login para comentar nessa publicação.
Iago M.
em 22 de Maio de 2018

Olá boa tarde! Quero programar uma planilha de calculo de frete, onde terá custo para tempo e quilômetro (parecido com a precificação da Uber), mas eu uso o Google Documentos, então é a Planilha do Google, existe essa mesma possibilidade nesse outro programa? Ou apenas em Excel?

Cadastre-se ou faça o login para comentar nessa publicação.
Carlos R.
em 29 de Maio de 2018

Olá sr. Igo,
Esta possibilidade poderá ser implementada via fórmulas no Excel, pois como é possivel calcular a distância somente nos resta as variáveis de custos associados a quilometragem rodada. Abs

Cadastre-se ou faça o login para comentar nessa publicação.
Rogerio P.
em 30 de Janeiro de 2018

Bom dia Professor!
Quando executo o VB aparece a mensagem: "Nome repetido encontrado: Km_Distancia"
Saberia do que se trata esse erro?

Cadastre-se ou faça o login para comentar nessa publicação.
Carlos R.
em 29 de Maio de 2018

Geralmente existe ambiguidade na informação, existem cidades com um mesmo nome em diferentes Estados. Sendo assim, é necessário referenciar na busca mais detalhes. Incluindo a cidade acompanhado com a vírgula o Estado. Abs e desculpe a demora, estou com mtas atividades.

Cadastre-se ou faça o login para comentar nessa publicação.
Wanderley L.
em 22 de Janeiro de 2018

E como passar coordenadas geográficas ao invés do nome do município

Cadastre-se ou faça o login para comentar nessa publicação.
Carlos R.
em 22 de Janeiro de 2018

Olá sr. Wandeley,

Eu tenho um site que se chama Acadêmicos do Excel, postei a uns 2 meses um método de cálculo com coordenadas geograficas...Acredito que irá lhe ajudar.
http://academicosdoexcel.com.br/2017/10/01/distancia-entre-locais-latitude-e-longitude/

Cadastre-se ou faça o login para comentar nessa publicação.
Carlos R.
em 22 de Janeiro de 2018

Olá sr. Wandeley,

Eu tenho um site que se chama Acadêmicos do Excel, postei a uns 2 meses um método de cálculo com coordenadas geograficas...Acredito que irá lhe ajudar.
http://academicosdoexcel.com.br/2017/10/01/distancia-entre-locais-latitude-e-longitude/

Cadastre-se ou faça o login para comentar nessa publicação.
Jhonathan A.
em 19 de Janeiro de 2018

Caro Prof. Carlos, boa tarde.

Segui o passo a passo, porém, a distância sempre aparece zerada na fórmula?
Estou utilizando a versão 171 (Office 365 ProPlus).

Obrigado!

Cadastre-se ou faça o login para comentar nessa publicação.
Carlos R.
em 22 de Janeiro de 2018

Olá Jhonatan,

Geralmente este problema ocorre quando o volume de consultas diárias são excedidas ou quando alguns apis não são habilitados...
Este office 365 por ter suporte a tecnologias na nuvem e manipulação online, esta constantemente tendo atualizações...

Cadastre-se ou faça o login para comentar nessa publicação.
Fernando F.
em 11 de Outubro de 2017

Como fazer a consulta por CEP ao invés de nome da cidade?

Cadastre-se ou faça o login para comentar nessa publicação.
Carlos R.
em 17 de Outubro de 2017

Olá Fernando!!Desculpe a demora, estou em uma rotina com muitas atividades, mas sempre que possível respondo a todos.

Com este sistema é possível localizar via CEP, mas antes você deverá indicar a cidade correspondente ao mesmo, por exemplo: Sao Paulo, 01234-123.

Para entender melhor a dinâmica de trabalho do CEP, existe um esquema bem legal no site dos correios, https://www.correios.com.br/para-voce/precisa-de-ajuda/o-que-e-cep-e-por-que-usa-lo/estrutura-do-cep

O que você precisará na verdade é entender basicamente o 1º número do CEP, que é correspondente a região (Estado), ou no máximo o 2º número que são as sub-regiões (municípios)

Espero que tenha lhe ajudado!

Abs,
Carlos Roa

Cadastre-se ou faça o login para comentar nessa publicação.
Renato S.
em 01 de Agosto de 2017

Boa tarde meu amigo! Locais como Sao Gabriel da Cachoeira, Tabatinga, Barcelos, entre outros, não estão calculando. Sabe o que pode ter ocorrido?

Cadastre-se ou faça o login para comentar nessa publicação.
Carlos R.
em 01 de Agosto de 2017

Ola Renato,

Estou fora agora, mas logo mais vou fazer algun testes...Em primeia mão, não utilize acentos ou caracteres especiais, alguns casos pode ser que o ao invés de "da" é "de". Existem algumas cidades que existem algumas pequenas peculiaridades na grafia.

Cadastre-se ou faça o login para comentar nessa publicação.
Carlos R.
em 01 de Agosto de 2017

Ola Renato,

Estou fora agora, mas logo mais vou fazer algun testes...Em primeia mão, não utilize acentos ou caracteres especiais, alguns casos pode ser que o ao invés de "da" é "de". Existem algumas cidades que existem algumas pequenas peculiaridades na grafia.

Cadastre-se ou faça o login para comentar nessa publicação.
Carlos R.
em 17 de Outubro de 2017

Olá Renato, primeiramente desculpe pela demora, estou em muitas ativiades.

Verifiquei a sua situação e percebi que estas cidades, possuem o mesmo nome em diferentes estados, sendo assim sugiro por exemplo, inserir ao invés de Tabatinga (Tabatinga, SP ou Tabatinga, PR)

Obs.: Perceba que o nome da cidade deve vir entre vírgulas, dividindo a UF.

Um grande abraço,
Carlos Roa

Cadastre-se ou faça o login para comentar nessa publicação.
Walter B.
em 26 de Julho de 2017

Carlos, boa tarde!
Como eu faço para ter o Km entre cidades de países diferentes? Exemplo: distância de São Paulo a Nova York...
No aguardo.

Cadastre-se ou faça o login para comentar nessa publicação.
Carlos R.
em 01 de Agosto de 2017

Ola Walter,

No momento, não consigo investigar o pq, mas assim q voltar de viagem lhe dou um retorno...Vou investigar quanto a destinos internacionais...

Cadastre-se ou faça o login para comentar nessa publicação.
Carlos R.
em 17 de Outubro de 2017

Olá Walter Borges!

Eu verifiquei a questão, e percebi que no modelo acima realmente não é possível isto, visto que o Google para a maioria dos países mostra a distância por rodovias ao invés de percurso aéreo.

O que deve ser feito é criar um código VBA que permita a inserção de um tipo de transporte, como carro e avião, e utilizar esta nova variável no modelo de API que do Google.

Pretendo postar futuramente, esta implementação!!
Obrigado por levantar esta situação, um grande abraço.
Carlos Roa

Um grande abraço,

Cadastre-se ou faça o login para comentar nessa publicação.
Cassio N.
em 05 de Julho de 2017

Muito obrigado por compartilha a ferramenta, me ajudou bastante.
Porém foi preciso fazer uma atualização.... Foi necessário inserir no GET a variável key, gerada no Google APIs. Além disso alterei de HTTP para HTTPS. Depois dessas alterações funcionou muito bem, segue as linhas modificadas:

chave_api = "" 'Alterar caso pare de funcionar - Gerar no Google API
Solicitacao.Open "GET", "https://maps.googleapis.com/maps/api/directions/xml?origin=" _
& Origin & "&destination=" & Destination & "&sensor=false&key=" & chave_api, False

Cadastre-se ou faça o login para comentar nessa publicação.
Carlos R.
em 05 de Julho de 2017

Obrigado pela atualização na fórmula, a sacada de uma key e o https foi mto boa!!!

Cadastre-se ou faça o login para comentar nessa publicação.
Caio H.
em 10 de Agosto de 2017

Obrigado amigo, eu consegui a liberação do google api e consegui fazer mais cidades!!!!!!!!! Vlw pela dica.

Cadastre-se ou faça o login para comentar nessa publicação.
Marcos A.
em 05 de Novembro de 2018

ola, a minha parou de funcionar, vi essa solucao, porem chave_api= " " é uma variavel? se sim como devo declarar ela?

Cadastre-se ou faça o login para comentar nessa publicação.
Edmilson S.
em 29 de Junho de 2017

Carlos, Boa tarde!
Tô tentando calcular e não está retornando nada

Cadastre-se ou faça o login para comentar nessa publicação.
Carlos R.
em 29 de Junho de 2017

Boa Noite Edmilson,

Possiveis causas:
1. Biblioteca Microsoft xlm v6.0 não habilita (verificar passo 5)
2. Regiões com nomes com caracteres especiais (escrever sem acento e ç)
3. A quantidade de consultas de regiões excedeu os limites diário colocado pelo google (este é um ponto crítico, q o google impõe para não sobrecarregar com mtas consultas planilhas vinculadas com o google)

Estou devendo para todos o método que independe do google, é uma formula que trabalha a latitude e longitude das regiões, o ibge possui a base de dados do brasil são mais de 5 mil cidades.

Cadastre-se ou faça o login para comentar nessa publicação.
Carlos R.
em 29 de Junho de 2017

Novamente sr. Edmilson, veja a resposta que dei a Vanessa, nesta deixo o link para download da referida base de dados e o link que explica a metodologia para calcular a latitude e longitude...

Abs!!!

Cadastre-se ou faça o login para comentar nessa publicação.
Luiz C.
em 06 de Maio de 2017

Olá Carlos,

Sou iniciante no Excel.
Atualmente estou com a seguinte situação:
Num mapa preciso selecionar uma área com um determinado raio. Das cidades dentro dessa área preciso saber nome delas, sua população (Masc X Fem), localização (latitude X longitude), a distância dela até oponto de origem.
Até já vi um vídeo em que é gerado um mapa com base na Long X Lat X Pop este último em três cores dependendo da quantidade.
Você pode sugerir alguma solução?

Cadastre-se ou faça o login para comentar nessa publicação.
Carlos R.
em 20 de Junho de 2017

Boa Tarde, Sr. Luiz.
Existe uma função chama Hiperlink, ela pode ser aplicada de uma forma bem interessante a sua necessidade, para você pesquisar a população de uma dada cidade e outras informações, mas para utiliza-la é necessário internet.

Neste caso, fica assim:

=hiperlink("https://pt.wikipedia.org/wiki/"&celula_nome_da_cidade;celula_nome_da_cidade)

Esta função irá criar um link automatizado com o Wikipedia conforme a cidade digitada.

Cadastre-se ou faça o login para comentar nessa publicação.
Matheus D.
em 12 de Abril de 2017

Bom dia, Carlos!
Como faço para ter como output valores com 1 casa decimal? Algumas distâncias que estou pesquisando são menores que 1 kilometro e, por isso, aparecem zeradas. Desde já, obrigado pela atenção.

Cadastre-se ou faça o login para comentar nessa publicação.
Carlos R.
em 12 de Abril de 2017

Oi Matheus, existem muitas formas se se aumentar as casas decimais.

Neste caso utilize somente a função =Km_Distancia(Origem;Destino) e na guia "Pagina Inicial" na categoria "Número" aumente as casas decimais (seta para esquerda acompanhado de zeros).

Infelizmente o Google nos fornecerá até a terceira casa decimal, mas acredito que para distâncias pequenas já é o suficiente.

Espero que tenha ajudado!

Cadastre-se ou faça o login para comentar nessa publicação.
Jr B.
em 28 de Março de 2017

Caso possua uma API_Key do Google qual seria a API a ativar do google nesta consulta e como ficaria o código VBA com a inclusão da API_Key?

Cadastre-se ou faça o login para comentar nessa publicação.
Carlos R.
em 28 de Março de 2017

Este é o grande problema nas consultas do google, o API_Key deles é fechado, mas a porta de acesso é liberada via uma solicitação, no qual restringe o usuário a um limite de consultas diárias, que acredito ser 100, tanto é que vários usuários estão se queixando dos valores zerados após replicar a fórmula para várias células.

Cadastre-se ou faça o login para comentar nessa publicação.
Vanessa F.
em 27 de Março de 2017

Bom dia Carlos! Muito obrigada pelo tutorial, ajudou muito!
Infelizmente tenho um grande número de dados dos quais preciso do resultado da fórmula (são 1012 combinações até agora). Consegui alguns resultados porém muitos foram zerados e quando tentei rodar novamente, zerou tudo (inclusive os que já tinham sido calculados). Vi que em um dos comentários você disse ter um algoritmo aproximado sem depender do Google. Pode compartilhar conosco? Muito obrigada!!!

Cadastre-se ou faça o login para comentar nessa publicação.
Carlos R.
em 27 de Março de 2017

Bom Dia Vanessa! Muito obrigado pela cordialidade, fico muito feliz em ajudar a todos. Aliás, estou com o site Acadêmicos do Excel, com muito conteúdo exclusivo.

Quanto a distância, vou me organizar e colocar um post para explicar passo a passo como contornar esta situação, no entanto, será necessário montar uma base de dados com as coordenadas de latitude e longitude de cada cidade, pois a formula lhe fornecerá uma distância media entre dois pontos. eu tenho uma base com um pouco mais de 5.000 cidades.

Para não lhe deixar na mão, segue alguns links para lhe ajudar.

Base de dados das latitudes e longitudes (abrir em Excel e converter texto para coluna)

Para abrir no Excel você deve colocar em Abrir "Todos os arquivos" e na sequência na guia "Dados" procurar "Converter texto para coluna".

http://download.geonames.org/export/zip/BR.zip

O calculo das distâncias em Excel:
http://pasta1xls.blogspot.com.br/2009/05/como-calcular-distancia-entre.html

Atenciosamente,
Carlos Roa

Cadastre-se ou faça o login para comentar nessa publicação.
Vanessa F.
em 27 de Março de 2017

Bom dia Carlos! Muito obrigada pelo tutorial, ajudou muito!
Infelizmente tenho um grande número de dados dos quais preciso do resultado da fórmula (são 1012 combinações até agora). Consegui alguns resultados porém muitos foram zerados e quando tentei rodar novamente, zerou tudo (inclusive os que já tinham sido calculados). Vi que em um dos comentários você disse ter um algoritmo aproximado sem depender do Google. Pode compartilhar conosco? Muito obrigada!!!

Cadastre-se ou faça o login para comentar nessa publicação.
Cassio N.
em 05 de Julho de 2017

Para resolver esse problema alterei as seguintes linha.
Criei uma chave grátis no Google APIs, e ela permite 2500 consultar por dia, deve resolver seu problema tbm.

chave_api = "" 'Alterar caso pare de funcionar - Gerar no Google API
Solicitacao.Open "GET", "https://maps.googleapis.com/maps/api/directions/xml?origin=" _
& Origin & "&destination=" & Destination & "&sensor=false&key=" & chave_api, False

Cadastre-se ou faça o login para comentar nessa publicação.
Rogério R.
em 01 de Novembro de 2016

Carlos, boa tarde.
Acontece uma falha que não estou conseguindo resolver, algumas cidades não estão retornando o km e mostra como "0" (ex: Brasilia e Salvador), o poder ser, e como podemos resolver isso?
Obrigado.

Cadastre-se ou faça o login para comentar nessa publicação.
Marcelo P.
em 21 de Outubro de 2016

Boa tarde

Carlos muito obrigado pela contribuição, assim como outro seguidor do tópico estou com a planilha exibindo os valores de km zerados já verifiquei em opções e o cálculo automático está habilitado, poderia me ajudar por favor?
Microsoft Excel 2010.

Cadastre-se ou faça o login para comentar nessa publicação.
Carlos R.
em 24 de Outubro de 2016

Boa dia Marcelo, geralmente isto acontece caso o usuário replique a formula muitas vezes (arrastar) pq infelizmente os servidores do Google limitam a quantidade de busca por usuário para não carregar os servidores deles...Provavelmente depois de algumas horas as formulas voltem a funcionar...Estou devendo para meus caros amigos uma solução q não envolva o Google, é um algoritmo q fornece uma distancia aproximada mediante as coordenadas fornecidas...Em breve estarei com um site disponível para dicas, aulas de e excel, curiosidades e um forum para debates de certos assuntos...Obrigado!!!

Cadastre-se ou faça o login para comentar nessa publicação.
Carlos R.
em 24 de Outubro de 2016

Procure inserir os nomes dos locais sem caracteres especiais ou acentos tb...

Cadastre-se ou faça o login para comentar nessa publicação.
Carlos R.
em 24 de Outubro de 2016

Procure inserir os nomes dos locais sem caracteres especiais ou acentos, isto pode influenciar na pesquisa tb...

Cadastre-se ou faça o login para comentar nessa publicação.
Marcelo P.
em 25 de Outubro de 2016

Compreendi, muito obrigado pela explicação, fico no aguardo do algorítmo.

Cadastre-se ou faça o login para comentar nessa publicação.
Mauro A.
em 31 de Janeiro de 2017

Algumas cidades também ficaram com quilometragem zerada pra mim, mas como o Carlos já comentou, tirei os acentos e cedilhas e deu certo. Algumas cidades tinham o mesmo nome de outra cidade em outro lugar, para essas eu acrescentei uma barra e UF (Ex.: California/PR), e funcionou.
Muito obrigado, Carlos, está me ajudando muito.

Cadastre-se ou faça o login para comentar nessa publicação.
Luis C.
em 18 de Outubro de 2016

Prezado Carlos

É possivel, alem da distância entre 2 pontos utilizando o Google Maps no Excel, calcular tambem o tempo medio que leva para percorre-los?

Obrigado

Cadastre-se ou faça o login para comentar nessa publicação.
Carlos R.
em 18 de Outubro de 2016

Boa tarde Luis,

É possivel sim, crie uma celula a velocidade media do carro (km/h) ao longo de todo o percurso e em outra coluna divida a distancia percorrida (d) pela velocidade média...Com isso vc terá uma estimativa de tempo...Lembrando q o resultado será em horas, caso queira em minutos multiplique por 60

Cadastre-se ou faça o login para comentar nessa publicação.
Alexandre S.
em 04 de Outubro de 2016

Carlos, bom dia!
Muito obrigado pelo retorno, porém não consegui o problema com a sua recomendação.
Quando for possível não deixe de disponibilizar o outro método de cálculo pois certamente ajudará mtas pessoas.
Mais uma vez obrigado!

Cadastre-se ou faça o login para comentar nessa publicação.
Alexandre S.
em 03 de Outubro de 2016

Carlos, Boa tarde!
Sou iniciante no assunto e segui o passo a passo obtendo os resultados esperados.
Porém ao abrir o arquivo novamente e efetuar novas consultas de Origem Destino o resultado da função Km_Distancia esta trazendo valores zerados.
Como posso corrigir esse problema?

Cadastre-se ou faça o login para comentar nessa publicação.
Carlos R.
em 03 de Outubro de 2016

Boa Tarde alexandre, será que a sua planilha esta habilitada para calculo automático? Clique F9 para verificar se os resultados são atualizados...Esta formula possui uma pequena restrição imposta pelo google tb, caso o número de repetições das formulas exceda um valor de 100 pesquisas diárias ela para de funcionar, mas após um período retorna normalmente...Tenho um método de cálculo de distâncias q fornece uma aproximação boa para os resultados e independe do google, quando tiver um tempinho eu posto...abçs

Cadastre-se ou faça o login para comentar nessa publicação.
Marcos C.
em 15 de Setembro de 2016

A Fórmula (Função) é excepcional, me ajuda muito. Obrigado

A Unica dúvida e eu montei uma planilha e fixei todas as distancias,(colei valores) para que ao posicionar duas localidades me de a distancia da matriz, porem ta tendo divergências nas distancias que o Google informa, e possível acertar, pondo a distancia mas próxima como padrão?

Cadastre-se ou faça o login para comentar nessa publicação.
Carlos R.
em 15 de Setembro de 2016

Entendi, as divergências ocorrem devido as rotas não serem lineares, o sistema calcula uma distância média de ponto a ponto...O que se pode fazer é criar uma rota no Excel, inserindo cidades proximas e depois somar as distâncias...

Cadastre-se ou faça o login para comentar nessa publicação.
Carlos R.
em 15 de Setembro de 2016

Entendi, as divergências ocorrem devido as rotas não serem lineares, o sistema calcula uma distância média de ponto a ponto...O que se pode fazer é criar uma rota no Excel, inserindo cidades proximas e depois somar as distâncias...

Cadastre-se ou faça o login para comentar nessa publicação.
Carlos R.
em 15 de Setembro de 2016

Entendi, as divergências ocorrem devido as rotas não serem lineares, o sistema calcula uma distância média de ponto a ponto...O que se pode fazer é criar uma rota no Excel, inserindo cidades proximas e depois somar as distâncias...

Cadastre-se ou faça o login para comentar nessa publicação.
André C.
em 02 de Setembro de 2016

Bom dia professor,
É possível pedir que a função retorne a menor distância exibida pelo google maps?

Cadastre-se ou faça o login para comentar nessa publicação.
Carlos R.
em 15 de Setembro de 2016

Entendi, as divergências ocorrem devido as rotas não serem lineares, o sistema calcula uma distância média de ponto a ponto...O que se pode fazer é criar uma rota no Excel, inserindo cidades proximas e depois somar as distâncias.

Cadastre-se ou faça o login para comentar nessa publicação.
Raphael F.
em 10 de Agosto de 2016

Bom dia, professor. é possível aparecer o google maps para o usuário determinar corretamente a rota, e após determinar a rota exportar as informações. Professor gostaria ver a possibilidade de marcarmos algumas aulas online. Abs

Cadastre-se ou faça o login para comentar nessa publicação.
Carlos R.
em 15 de Setembro de 2016

Bom dia Raphael ainda não tenho este sisteminha pronto, mas pretendo nas minhas proximas postagens criar uma rotina em vba para importar informações de uma planilha...Assim q tiver novidades lhe comunico, continue atualizado com os novos pots!abs

Cadastre-se ou faça o login para comentar nessa publicação.
Brunno A.
em 03 de Agosto de 2016

Bom dia Carlos, se eu quiser incluir mais de dois destinos, é possível? Você conseguiria me ajudar nisso?

Cadastre-se ou faça o login para comentar nessa publicação.
Carlos R.
em 05 de Agosto de 2016

Claro, mas acredito que com esta função já atenda, é só vc criar uma planilha com a célula origem e criar vários destinos em diferentes células. Abs

Cadastre-se ou faça o login para comentar nessa publicação.
Carlos R.
em 15 de Setembro de 2016

Para criar dois destinos utilize a formula mais de uma vez no Excel, crie um esquema de rotas!abs, espero ter ajudado!

Cadastre-se ou faça o login para comentar nessa publicação.

Listas de exercícios, Documentos, Revisões de textos, Trabalhos?

Se seu problema for dificuldade em uma lista de exercícios, revisão de teses e dissertações, correção de textos ou outros trabalhos, peça uma ajuda pelo Tarefas Profes.

Enviar Tarefa

Confira artigos similares

Confira mais artigos sobre educação

Ver todos os artigos

Encontre um professor particular

Busque, encontre e converse gratuitamente com professores particulares de todo o Brasil