Thomas Zilliox
Intégrateur CSS Freelance à Lyon

Calculer une distance entre deux coordonnées GPS dans Excel

Le tableur (que ce soit Microsoft Excel, Libre Office Calc ou Google Sheets) est l'outil à tout faire. Quand on est dans une chasse au trésor, on s'en sert pour garder une trace de toutes nos découvertes, de nos pistes et de nos idées. C'est un document facile à partager pour qu'un co-équipier puisse vérifier nos calculs. Il me manquait une fonctionnalité importante : pouvoir estimer la distance entre deux coordonnées GPS.

L'objectif est de pouvoir calculer la distance comme dans l'exemple ci-dessous.
Capture d'écran d'un fichier Excel avec le calcul de la distance entre deux coordonnées

Après un peu de recherche, je suis tombé sur la formule de Haversine. Elle permet de déterminer la distance entre deux points d'une sphère à partir de leurs longitudes et latitudes. Bingo !

La formule de Haversine sous forme d'équation algébrique
La formule de Haversine

La formule de Haversine en langage tableur

=2 * 6371 * ASIN(
  SQRT(
    (
      SIN((
        RADIANS(C2) -
        RADIANS(A2)
      )/2)
    )^2
    +
    COS(RADIANS(C2)) *
    COS(RADIANS(A2)) *
    (
      SIN((
        RADIANS(D2) -
        RADIANS(B2)
      )/2)
    )^2
  )
)

C'était presque parfait, mais j'ai deux soucis. Mon premier problème est que j'ai la longitude et la latitude de chaque lieu dans la même cellule. Il me faut donc extraire ces données, pour cela on a besoin des fonctions LEFT(), RIGHT(), FIND(), et LEN().

Récupérer la longitude de la cellule A2

=LEFT(A2; FIND(","; A2) - 1)

Récupérer la latitude de la cellule A2

=RIGHT(A2; LEN(A2) - FIND(","; A2))

Mon deuxième problème est que les longitudes et latitudes utilisent par convention le point pour séparer les décimales. Hors, les formules mathématiques s'attendent à une virgule comme séparateur. C'est la fonction SUBSTITUTE() qui va nous dépanner cette fois-ci :

Récupérer la longitude de la cellule A2, au bon format

=SUBSTITUTE( LEFT(A2; FIND(","; A2) - 1); "."; ",")

La formule complète pour calculer la distance entre les coordonnées GPS contenues dans les cellules A2 et B2

=2 * 6371 * ASIN(
  SQRT(
    (
      SIN((
        RADIANS( SUBSTITUTE( LEFT(A2; FIND(","; A2) - 1); "."; ",")) -
        RADIANS( SUBSTITUTE( LEFT(B2; FIND(","; B2) - 1); "."; ","))
      )/2)
    )^2
    +
    COS( RADIANS( SUBSTITUTE( LEFT(A2; FIND(","; A2) - 1); "."; ","))) *
    COS( RADIANS( SUBSTITUTE( LEFT(B2; FIND(","; B2) - 1); "."; ","))) *
    (
      SIN((
        RADIANS( SUBSTITUTE( RIGHT(A2; LEN(A2) - FIND(","; A2)); "."; ",")) -
        RADIANS( SUBSTITUTE( RIGHT(B2; LEN(B2) - FIND(","; B2)); "."; ","))
      )/2)
    )^2
  )
)

La formule complète est donc complètement illisible, mais l'important c'est que ça marche, non ?

Bonne chasse à tous, Thomas.

That's my face!

Thomas ZILLIOX

L'homme qui murmurait à l'oreille des chevrons.

Je développe, j'intègre, je forme ou je conseille sur les CSS. Besoin d'améliorer la maintenabilité ou les performances de vos projets ?