PB Formule sur Excel
Résolu
paradox0706
-
paradox0706 -
paradox0706 -
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
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:
- PB Formule sur Excel
- Formule si et excel - Guide
- Formule moyenne excel plusieurs colonnes - Guide
- Formule somme excel colonne - Guide
- Excel mise en forme conditionnelle formule - Guide
- Liste déroulante excel - Guide
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.
ç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.
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.
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.
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
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
Vous n’avez pas trouvé la réponse que vous recherchez ?
Posez votre question
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.
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.
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
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