Création d'un tableau Excel pour devis
Bonjour à tous,
A la base, je recherché des formules pour trouver le nombre de km entre un point A et un point B.
J'ai trouver un fichier qui correspond en partie à ce que je recherche en le retravaillant un peu.
Néanmoins, impossible de trouver la bonne formule pour continuer mon tableau.
Enfin si, disons que plutôt je n'en suis pas capable car je pense que ça doit faire appel aux fonction SI, ET, OU.
Mon besoin est le suivant exemple =
si le nombre de km = 15 km
et si il est compris entre 0 et 20 km le tarif seras 7,15 €/T
Mais je peux pas savoir à l'avance avant d'avoir entré le nom des deux villes le nombre de km.
J'ai 97 valeurs qui comprenne le tarif transport qui sont de 20 en 20 et qui correspondent à un tarif transport en fonction du nombre de km.
Merci d'avance pour vos réponses.
Cordialement
Cyril.
Bonjour Cyril,
Tu a écrit :si le nombre de km = 15 km
tu a écrit :et s'il est compris entre 0 et 20 km
or il me semble que 15 km est entre 0 et 20 km, non ?
qu'il y ait des frais de transport pour 0 km, n'est-ce-pas ?
> 0 km et <= 20 km : 7,15€/T
> 20 km et <= 40 km : x €/T
> 40 km et <= 60 km : x €/T
etc... et quelle est la dernière tranche ? > 100 km ? autre ?
mais c'est peut-être :
> 0 km et < 20 km : 7,15€/T
>= 20 km et < 40 km : x €/T
>= 40 km et < 60 km : x €/T
etc... et quelle est la dernière tranche ? >= 100 km ? autre ?
⚠ « entre a et b » n'est pas assez précis, car ça peut être :
entre a inclus et b inclus : >= a et <= b
entre a inclus et b exclu : >= a et < b
entre a exclu et b inclus : > a et <= b
entre a exclu et b exclu : > a et < b
le placement du signe « = » fait toute la différence !
Comme il manque trop d'infos, merci de joindre
ton fichier sans données confidentielles.
Cordialement
Bonjour Dhany,
Je te joins le fichier sans ma grille de tarif confidentiel de mon entreprise.
Pour t'expliquer,
J'ai trouver un tableau qui me permet de compter le nombre de km. Que j'ai retravailler à ma manière.
Si tu vois comment l'optimiser je suis preneur car je suis obligé d'aller dans itinéraires pour "lancer le calcul" qui me permet d'avoir un tableau simple dans la feuil 1 et de me noté mes km.
Ensuite j'aurais voulu, comme expliqué précédemment que dans ma feuil 1 le tarif des zone soit noté automatiquement en fonction des km trouvé.
Merci pour ta première réponse et ton implication à mon souci.
Cordialement
Cyril.
Bonjour,
Autant que possible ... dans Excel ... éviter à tout prix ... les cellules fusionnées ...
Ci-joint ton fichier ...
@Chambon
Sur Feuil1, j'ai enlevé les fusions de cellules inutiles qu'il y avait pour les 2 colonnes A et B, puis j'ai supprimé
l'ancienne colonne B ➯ une colonne de moins.
J'ai renommé la feuille "Feuil1" en "TblTR" : Tableau Transport ; à l'ouverture du fichier, tu es sur cette feuille ;
on va supposer qu'au départ le tableau ne contenait aucune donnée, et ensuite tu as saisi les données qui
sont en A4:C8 ; remarque bien que D4:E8 est vide, notamment : il n'y a plus de formules en D4:D8.
Va sur la feuille "Itinéraires" ; tu peux voir que les 3 colonnes E, I, J sont vides ; on va supposer que tu n'as
pas encore saisi les données de la colonne L ; fais Ctrl e ➯ ça a rempli les 3 colonnes E, I, J à partir de ce
que tu avais saisi en feuille "TblTR" ; maintenant, fais comme d'habitude pour remplir la colonne L ; puis
va en feuille "TblTR" ; surprise ! la colonne D est remplie : la macro y a mis les bonnes formules !
Tout ce que je viens de décrire vaut même si tu ajoutes plus de lignes à ce tableau de la feuille "TblTR",
mais tu devras quand même ajouter des bordures et la couleur de remplissage de la colonne A.
Tu avais écrit : « Si tu vois comment l'optimiser je suis preneur » ; c'est ce que j'ai essayé de faire,
à toi de me dire si ça te convient...
Cordialement,
dhany
PS : pour la colonne E "Tarif zone", James007 t'a proposé une très bonne solution !
Bonjour à vous deux,
Je tiens à vous remercier infiniment tout les deux, mais je dois vraiment pas être très bon avec le logiciel excel.
Impossible de coupler vos deux solution dans un seul fichier...
Je comprend pas pourquoi, je prie de croire qu'avant de poster ce message, j'ai essayé pendant un bon moment.
Il faudrait que je prenne vraiment des cours.
Vous êtes formidable.
Cordialement
Cyril
Bonjour Cyril,
J'ai pris mon dernier fichier joint, auquel j'ai ajouté la solution de
James007 pour Tarif zone ; ce qui donne ce nouveau fichier :
Je vais t'indiquer tout ce que j'ai fait, pour que tu puisses le refaire dans ton vrai fichier ;
pour mieux comprendre la procédure à suivre, ouvre mon nouveau fichier joint.
C'est plus simple de commencer par la feuille "Tarif Transport", où il faut ajouter la colonne K :
texte en K2 : Ligne ; formule en K3 (à tirer vers le bas jusqu'en K99) : =LIGNE() ; c'est une fonction
sans argument, comme AUJOURDHUI(), et elle retourne le n° de la ligne où tu mets cette formule,
donc 3 en K3 ; 4 en K4 ; 5 en K5 ... 99 en K99 (reste à faire les bordures et alignements).
Va maintenant en feuille "TblTR" ; note que j'ai déjà exécuté ma macro pour remplir D4:D8
avec les bonnes formules ; exemple en D4 : =Itinéraires!L7 ; formule de James007 en E4 :
=INDEX('Tarif Transport '!B:B;RECHERCHEV(D4;'Tarif Transport '!$F$3:$K$99;6))
je l'ai adaptée, car ayant supprimé la colonne B après avoir enlevé toutes les fusions inutiles,
ça fait une colonne en moins ➯ ce qui doit être recherché n'est plus E4 mais D4 ; reste à
tirer la formule vers le bas jusqu'en E8 (les zéros en E5:E8 sont dus au manque de données
en feuille "Tarif Transport", colonne B ➯ il suffit de compléter tes données € la tonne/Km).
N'oublie pas de m'indiquer si tu as réussi à tout faire dans ton vrai fichier ;
si besoin, n'hésite pas à demander un complément d'info.
Si tout est ok, merci de passer le sujet en résolu : il faut juste cliquer
sur la petite coche verte V qui est à droite du bouton EDIT.
Cordialement,
dhany
Bonsoir Dhany,
Merci énormément, c'est parfait.
Néanmoins, je vais t'expliquer mes derniers petits problèmes en message privé.
A bientôt et encore merci.
Cordialement
Cyril
Bonjour Cyril,
J'ai modifié le code VBA de mon fichier précédent ; comme ce fichier n'est pas confidentiel,
je le mets ci-dessous ; je te laisse le télécharger, le tester, et me donner ton avis.
Cordialement,
dhany
Bonsoir Dhany,
C'est vraiment parfait !
Ça marche, tu es au top !
J'ai rentré de nouveau mes données et ça fonctionne.
A présent je vais chercher une nouvelle solution pour un tableau de distance entre deux villes.
Le mien me plait pas... Je sais, je fais la fine bouche mais il n'ai pas pratique, d'autant plus qu'il me dit souvent que j'ai dépassé le nombre de requête.... ( normalement c'est 250 avant les pauses mais au bout de 3 demandes il me dit que j'ai dépassé le nombre de requêtes)
A bientôt.
Cyril
Bonjour à Tous ... bonjour Dhany (qui m'a pistonné) et Chambon,
Bon ben voilà, plus rien ne fonctionne ! Google a durci les conditions d'accès à ses services. Pour accéder au calcul de distance il faut maintenant utiliser :
https://maps.googleapis.com/maps/api/distancematrix/json?units=imperial&origins=Washington,DC&destinations=New+York+City,NY&key=YOUR_API_KEYet demander une clé YOUR_API_KEY
https://developers.google.com/maps/documentation/distance-matrix/start
Les forum s'agitent partout pour trouver une autre solution, mais rien n'est proposé.
Après une bonne nuit blanche, un mal de tête à retourner les codes html, javascript, vba dans tous les sens, j'ai quand même remarqué que le google.maps.DistanceMatrixService n'était pas verrouillé quand il était inséré dans une page web locale ou distante.
J'ai donc peaufiné ce code
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Strict//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-strict.dtd">
<html xmlns="http://www.w3.org/1999/xhtml" xml:lang="fr" lang="fr">
<head>
<title>Distance Matrix Service</title>
<style>body{font-family: Calibri; font-size: 22px;}</style>
<script src="lieux.js"></script>
</head>
<body><div id="output"></div><script>
function init() {
var service = new google.maps.DistanceMatrixService;
service.getDistanceMatrix({
origins: depuis,
destinations: jusque,
travelMode: 'DRIVING',
unitSystem: google.maps.UnitSystem.METRIC,
avoidHighways: false,
avoidTolls: false
}, function(response, status) {
if (status !== 'OK') {
alert('Error was: ' + status);
} else {
var originList = response.originAddresses;
var destinationList = response.destinationAddresses;
var outputDiv = document.getElementById('output');
outputDiv.innerHTML = 'km|';
for (var i = 0; i < destinationList.length; i++) {
outputDiv.innerHTML += destinationList[i] + '|';
}
outputDiv.innerHTML += '<br>';
for (var i = 0; i < originList.length; i++) {
outputDiv.innerHTML += originList[i] + '|';
var results = response.rows[i].elements;
for (var j = 0; j < results.length; j++) {
outputDiv.innerHTML += results[j].distance.text + '|';
}
outputDiv.innerHTML += '<br>';
}
outputDiv.innerHTML += '';
}
});
}
</script><script async defer src="http://maps.google.com/maps/api/js?sensor=false&callback=init"></script></body>
</html>mis au point la création du fichier .js contenant les paramètres
Sub param()
Dim c As Range
Dim Fichier As String
Fichier = "lieux.js"
On Error Resume Next
Kill [chemin] & "\" & Fichier
On Error GoTo 0
n = FreeFile()
Open [chemin] & "\" & Fichier For Append As #n
ceci = "var depuis = ["
For Each c In [Tdepuis]
ceci = ceci & IIf(Mid(c.Value, 1, 1) = "{", "", "'") & c.Value & IIf(Mid(c.Value, 1, 1) = "{", "", "'") & ","
Next
ceci = Mid(ceci, 1, Len(ceci) - 1) & "];"
Print #n, ceci
ceci = "var jusque = ["
For Each c In [Tjusque]
ceci = ceci & IIf(Mid(c.Value, 1, 1) = "{", "", "'") & c.Value & IIf(Mid(c.Value, 1, 1) = "{", "", "'") & ","
Next
ceci = Mid(ceci, 1, Len(ceci) - 1) & "];"
Print #n, ceci
Close #n
End Subtrouvé une solution qui permette l'activation de la page web locale et la récupération des données issues du javascript (je n'ai pas encore trouvé de solution plus élégante dans le cas où les données à récupérer ne sont pas produites par le serveur (en php) mais par le client via un script local))
Sub DistancesGoole()
Dim Fichier As String
Fichier = "distance_lite.htm"
param True
Sheets("distances").Select
Cells.ClearContents
Range("A1").Select
ShellExecute 0, "", [chemin] & "\" & Fichier, "", "", 0
Application.Wait Time + TimeSerial(0, 0, 4)
SendKeys ("^a")
SendKeys ("^c")
Application.Wait Time + TimeSerial(0, 0, 1)
SendKeys "%{F4}"
ActiveSheet.Paste
Selection.TextToColumns Destination:=Range("A1"), DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
Semicolon:=False, Comma:=False, Space:=False, Other:=True, OtherChar _
:="|", _
TrailingMinusNumbers:=True
Cells.Select
Cells.EntireColumn.AutoFit
End Suble côté le plus fragile est que cela repose sur des tempos, le temps que la page se charge complètement ... j'essaierai d'autres solutions et je posterai sans doute une demande pour essayer d'autres voies
mais bon, cela semble quand même fonctionner ... à vos critiques si vous trouvez des anomalies
le kit complet est joint, il suffit de décompresser et lancer la macro du fichier excel san s'occuper des 2 autres fichiers !
Bonjour Steelson,
tu a écrit :bonjour Dhany (qui m'a pistonné)
??? pistonné ? le piston, c'est par exemple si j'avais fait quelque chose pour te faire obtenir une embauche ou une promotion, et c'est pas le cas ; peut-être as-tu voulu dire que j'avais dit à Chambon que tu es très bon pour tout ce qui est communications ? si c'est ça, alors oui ; tu connais même ce qui est du domaine des serveurs !
dhany
Ah oui c'est vrai, du reste j'avais été pistonné par le ministre de la Défense pour mon affectation au Service National !??? pistonné ? le piston, c'est par exemple si j'avais fait quelque chose pour te faire obtenir une embauche ou une promotion, et c'est pas le cas ; peut-être as-tu voulu dire que j'avais dit à Chambon que tu es très bon pour tout ce qui est communications ? si c'est ça, alors oui ; tu connais même ce qui est du domaine des serveurs !
ah ben pour mon service militaire, j'avais eu droit au même piston !
pour les nouveaux jeunes, je précise qu'à l'époque, c'était obligatoire ; j'm'étais pas engagé volontairement !
dhany
Bonjour,
Merci pour le partage, de ce nouveau code.
Je voulais savoir si il fonctionne avec excel 2013.
Et savoir si il est possible de récupérer les données de temps ?
A+.
Je voulais savoir si il fonctionne avec excel 2013.
oui
Et savoir si il est possible de récupérer les données de temps ?
oui
est-ce que tu veux tous les 2 distance et temps ? je regarde pour mettre dans la même cellule, il sera ensuite facile de distinguer distan,ce et temps.
tout sera regroupé dans le même fichier du reste (excel)
Voici,
à l'issue du chargement de la page html (même si pas encore affichée dans le navigateur) un message précisera qu'il faudra copier la page html dans la page excel.
modif : j'ai mis le temps en heures/minutes, c'est peut-être plus exploitable ensuite
bonsoir,
Merci pour avoir trouvé la solution aussi rapidement. C'est TOP.
Pour cette version il n'y a pas besoin des fichiers lieux.js et distance_lite.htm ?
Serait il possible de faire le copier / coller (comme dans le distancier ) directement dans le fichier Excel et en plus en mettant dans des cellules différentes (la distance au dessus du temps ).
Bonne soirée.
Bonjour,
Désolé, mais dans ce cas la distance est en mètres (j'ai rencontré une difficulté dans la conversion car google donne les points qui sont interprétés ici comme des milliers)
Le temps est à droite de la distance ... sinon le code était à revoir plus en profondeur
nota : si les autres fichiers html et js sont nécessaires mais ils sont maintenant masqués pour l'utilisateur dans le dossier temp et générés directement par la macro