PB Formule sur Excel

Résolu/Fermé
paradox0706 - 24 juil. 2008 à 11:05
 paradox0706 - 24 juil. 2008 à 14:25
Bonjour,

Quelqu'un pourrait-il me dire pour quoi cette formule ne marche pas?
Il s'agit d'une fonction qui calcule la distance entre 2 coordonnées géographiques.
L'algorithme que j'ai trouvé sur http://www.movable-type.co.uk/scripts/latlong.html est le sivant :

R = rayon de la terre (= 6371km)
Δlat = lat2− lat1
Δlong = long2− long1
a = sin²(Δlat/2) + cos(lat1).cos(lat2).sin²(Δlong/2)
c = 2.atan2(√a, √(1−a))
distance = R*c

Sous Excel, j'ai implémenté ceci:

=2*ATAN2(RACINE(1-(SIN(((45,18555556-45,09611111)/2)*PI()/180)
*SIN(((45,18555556-45,09611111)/2)*PI()/180))+(COS(45,09611111*PI()/180)
*COS(45,18555556*PI()/180)*(SIN(((3,139166667-2,963611111)/2)*PI()/180))
*SIN(((3,139166667-2,963611111)/2)*PI()/180)));
RACINE(SIN(((45,18555556-45,09611111)/2)*PI()/180)*SIN(((45,18555556-45,09611111)/2)*PI()/180))
+(COS(45,09611111*PI()/180)*COS(45,18555556*PI()/180)*(SIN(((3,139166667-2,963611111)/2)*PI()/180))
*SIN(((3,139166667-2,963611111)/2)*PI()/180)))*6371

NB : J'ai interverti les argument de ATAN2 parce que Excel les interprete dans ce sens contrairement à la convention.
J'ai également converti les latitudes et longitudes des deux coordonnées en système décimal pour pouvoir les soustraires.
J'ai également converti les angles en radians. Sinon, le calcul serait implicitement faux.

Quelqu'un saurait-il pourquoi le résultat que j'obtient, et je le sais avec certitude, n'est pas juste?
je devrais avoir 17 km au lieu des 9,9 que j'obtiens
A voir également:

7 réponses

OUFFF
ça y est. j'ai recontruit la formule
=2*ATAN2(RACINE(1-(SIN(RADIANS(('Coordonnées géographiques'!C5-'Coordonnées géographiques'!C4)/2))*SIN(RADIANS(('Coordonnées géographiques'!C5-'Coordonnées géographiques'!C4)/2)))+(COS(RADIANS('Coordonnées géographiques'!C4))*COS(RADIANS('Coordonnées géographiques'!C5))*(SIN(RADIANS(('Coordonnées géographiques'!C23-2,963611111)/2)))*SIN(RADIANS(('Coordonnées géographiques'!C23-'Coordonnées géographiques'!C22)/2))));RACINE((SIN(RADIANS(('Coordonnées géographiques'!C5-'Coordonnées géographiques'!C4)/2))*SIN(RADIANS(('Coordonnées géographiques'!C5-'Coordonnées géographiques'!C4)/2)))+(COS(RADIANS('Coordonnées géographiques'!C4))*COS(RADIANS('Coordonnées géographiques'!C5))*(SIN(RADIANS(('Coordonnées géographiques'!C23-'Coordonnées géographiques'!C22)/2)))*SIN(RADIANS(('Coordonnées géographiques'!C23-'Coordonnées géographiques'!C22)/2)))))*6371

Marche très bien C4,C22 et C5,C23 étant respectivement les latitudes,longitudes des points 1 et 2

Merci Eric.
3
eriiic Messages postés 24569 Date d'inscription mardi 11 septembre 2007 Statut Contributeur Dernière intervention 28 décembre 2023 7 213
24 juil. 2008 à 12:49
Je pense que l'erreur d'arrondi serait également apparu ici...
Reconstruis ta formule en étant attentif sur les ( ). Mets-en plutot plus que pas assez, quitte à supprimer celles qui sont inutiles ensuite si tu arrives au bon résultat.
2
nvaros Messages postés 141 Date d'inscription mardi 20 mai 2008 Statut Membre Dernière intervention 6 octobre 2009 17
24 juil. 2008 à 11:32
Cette formule te sert réellement ou c'est juste pour comprendre
0
eriiic Messages postés 24569 Date d'inscription mardi 11 septembre 2007 Statut Contributeur Dernière intervention 28 décembre 2023 7 213
24 juil. 2008 à 11:34
cette question te sert réellement ?
0
eriiic Messages postés 24569 Date d'inscription mardi 11 septembre 2007 Statut Contributeur Dernière intervention 28 décembre 2023 7 213
24 juil. 2008 à 11:39
Bonjour,

Tu as bien contrôlé que ce n'était pas dû à une erreur d'arrondi ?
Sinon il faudra tester ta formule morceau par morceau...
Pour tester un bout de ta formule tu sélectionnes ce bout dans la barre dédition et tu fais F9 dessus, cette partie sera évaluée.
eric
0

Vous n’avez pas trouvé la réponse que vous recherchez ?

Posez votre question
Merci Eric. Je l'ai testée morceau par morceau. A priori, il y a rien qui cloche.
0
En fait, lorsque je teste
R = rayon de la terre (= 6371km)
Δlat = lat2− lat1
Δlong = long2− long1
a = sin²(Δlat/2) + cos(lat1).cos(lat2).sin²(Δlong/2)
c = 2.atan2(√a, √(1−a))
distance = R*c

en calculant étape par étape dans des cellules différentes, je trouve 16,9km qui est le bon résultat. La question c'est prkoi ça marche étape par étape et pas d'un seul coup.
Je soupçonne l'erreur d'arrondi dont tu parlais Eriiic.
0
eriiic Messages postés 24569 Date d'inscription mardi 11 septembre 2007 Statut Contributeur Dernière intervention 28 décembre 2023 7 213
24 juil. 2008 à 12:40
Si rien ne cloche sûrement une erreur d'arrondi.
Teste en calculant avec la dernière décimale supprimée puis avec +ou-1 sur la décimale pour avoir les valeurs mini-maxi des 2 points plus éloignés et plus rapprochés.
Si 17 n'est pas inclu dans cette fourchette je pense que tu peux recontrôler ta formule en la découpant dans plusieurs cellules pour faciliter le contrôle.
ex 49.15678 => 49.1567 et 49.1568
-1.35463 => -1.3546 et -1.3547
eric

edit: nos messages se sont croisés
0