Mike suit les valeurs de latitude et de longitude dans une feuille de calcul Excel. Comme il s’agit essentiellement de points sur une grille, Mike aimerait calculer la distance entre deux points de latitude / longitude donnés.

Si les paires de latitude et de longitude n’étaient en réalité que des points sur une grille, alors calculer la distance entre elles serait facile. Le problème est qu’il s’agit en réalité de points sur une sphère, ce qui signifie que vous ne pouvez pas utiliser de calculs à grille plate pour déterminer la distance. De plus, il existe de nombreuses façons de calculer les distances: distance de surface la plus courte, trajectoire de vol optimale (« à vol d’oiseau »), distance à travers la terre, distance de conduite, etc.

Évidemment, cela pourrait être une question compliquée. Dans l’espace disponible, j’examinerai quelques façons de déterminer la distance du grand cercle (« à vol d’oiseau »), puis je fournirai quelques références pour des informations supplémentaires sur les autres types de calculs.

La première chose que vous devez comprendre est de savoir comment la latitude et la longitude de chaque point seront représentées dans Excel. Il y a plusieurs façons de le représenter. Par exemple, vous pouvez entrer les degrés, les minutes et les secondes dans des cellules individuelles. Ou, vous pouvez les avoir dans une seule cellule en tant que DD: MM: SS. Dans les deux cas, c’est acceptable, mais ils devront être traités différemment de vos formules. Pourquoi? Parce que si vous entrez la latitude et la longitude comme DD: MM: SS, Excel les convertira en interne en une valeur de temps, et il vous suffit de prendre en compte cette conversion.

Quoi qu’il en soit, vous devrez convertir votre latitude et votre longitude en une valeur décimale en radians. Si vous avez une coordonnée dans trois cellules séparées (degrés, minutes et secondes), vous pouvez utiliser la formule suivante pour effectuer la conversion en une valeur décimale en radians:

=RADIANS((Degrees3600+Minutes60+Seconds)/3600)

La formule utilise des plages nommées pour vos degrés, minutes et secondes. Il convertit ces trois valeurs en une valeur unique représentant le nombre total de degrés, puis utilise la fonction RADIANS pour le convertir en radians.

Si vous commencez avec une valeur de 32 degrés, 48 ​​minutes et 0 seconde, la formule finit par ressembler à ceci:

=RADIANS((323600+4860+0)/3600)

=RADIANS((115200+2880+0)/3600)

=RADIANS(118080/3600)

=RADIANS(32.8)

=0.572467995

Si vous stockez vos coordonnées au format DD: MM: SS dans une seule cellule (dans cet exemple, la cellule E12), vous pouvez utiliser la formule suivante pour convertir en une valeur décimale en radians:

=RADIANS((DAY(E12)86400+HOUR(E12)3600+MINUTE(E12)*60+SECOND(E12))/3600)

En supposant que la cellule E12 contient 32:48:00, la formule finit par ressembler à ceci:

=RADIANS((186400+83600+48*60+0)/3600)

=RADIANS((86400+28800+2880+0)/3600)

=RADIANS(118080/3600)

=RADIANS(32.8)

=0.572467995

Avec vos coordonnées en radians, vous pouvez utiliser une formule trigonométrique pour calculer la distance le long de la surface d’une sphère. Il existe de nombreuses formules de ce type qui pourraient être utilisées; la formule suivante suffira à nos fins:

=ACOS(SIN(Lat1)SIN(Lat2)+COS(Lat1)COS(Lat2)COS(Lon2-Lon1))180/PI()*60

Dans cette formule, chacune des coordonnées de latitude (Lat1 et Lat2) et de longitude (Lon1 et Lon2) doit être une valeur décimale, en radians, comme déjà discuté. La formule renvoie une valeur en miles nautiques, à laquelle vous pouvez ensuite appliquer diverses formules afin de la convertir en d’autres unités de mesure, comme vous le souhaitez.

Vous devez comprendre que les valeurs que vous obtenez en utilisant une formule qui calcule la distance à la surface d’une sphère donneront des résultats légèrement erronés. Pourquoi? Parce que la Terre n’est pas une sphère parfaite. Ainsi, les distances ne doivent être considérées que comme approximatives. Si vous voulez être un peu plus précis, vous pouvez utiliser la formule suivante pour déterminer vos miles nautiques:

=ACOS(SIN(Lat1)SIN(Lat2)+COS(Lat1)COS(Lat2)COS(Lon2-Lon1))3443.89849

Cette formule remplace le rayon de la terre (3443,89849 miles nautiques) par le rayon d’une sphère (180 / PI () * 60 ou 3437,746771). Dans tous les cas, la réponse doit toujours être considérée comme approximative.

Comme vous pouvez le constater, la formule pour calculer les distances est assez longue. Vous trouverez peut-être plus facile de développer votre propre fonction définie par l’utilisateur qui fera le calcul à votre place. La fonction suivante prend quatre valeurs (les deux paires de latitudes et de longitudes, en degrés), puis renvoie un résultat en miles nautiques:

Function CrowFlies(dlat1, dlon1, dlat2, dlon2)

Pi = Application.Pi()

earthradius = 3443.89849  'nautical miles

lat1 = dlat1  Pi / 180     lat2 = dlat2  Pi / 180     lon1 = dlon1  Pi / 180     lon2 = dlon2  Pi / 180

cosX = Sin(lat1)  Sin(lat2) + Cos(lat1) _        Cos(lat2) * Cos(lon1 - lon2)

CrowFlies = earthradius * Application.Acos(cosX)

End Function

Si vous souhaitez voir une discussion plus approfondie sur les latitudes et les longitudes, et les mathématiques impliquées, vous pouvez trouver une bonne sélection d’articles sur ce site:

http://mathforum.org/library/drmath/sets/select/dm_lat_long.html

Avec les mathématiques sous votre ceinture, vous pouvez alors commencer à examiner diverses formules que vous pouvez utiliser. Il y en a un intéressant en VBA sur cette page Web:

http://www.freevbcode.com/ShowCode.asp?ID=5532

Une bonne discussion générale peut également être trouvée sur le site de Chip Pearson, ici:

http://www.cpearson.com/excel/LatLong.aspx

_Note: _

Si vous souhaitez savoir comment utiliser les macros décrites sur cette page (ou sur toute autre page des sites ExcelTips), j’ai préparé une page spéciale qui comprend des informations utiles.

lien: / excelribbon-ExcelTipsMacros [Cliquez ici pour ouvrir cette page spéciale dans un nouvel onglet de navigateur].

ExcelTips est votre source pour une formation Microsoft Excel rentable.

Cette astuce (3275) s’applique à Microsoft Excel 97, 2000, 2002 et 2003. Vous pouvez trouver une version de cette astuce pour l’interface ruban d’Excel (Excel 2007 et versions ultérieures) ici:

link: / excelribbon-Calculating_the_Distance_between_Points [Calcul de la distance entre les points].