Automatisation Calcul
Bonjour,
Je souhaiterais automatiser le calcul d’une de me feuilles Excel.
J’ai une feuille avec colonne:
Référence - Zone Géographique - Quantité - Délai
1 - A - 1000 - 62
2 - A - 500 - 43
3 - B - 5000 - 20
4 - D - 50 - 20
5 - A - 10000 - 88
6 - B - 300 - 15
7 - C - 1500 - 30 etc
Une seconde feuille avec
Zone géographique - Qté Mini - Qté Maxi - Coefficient - Nbre Jours
A - 0 - 999 - 1,3 - 60
A - 1000 - 99999 - 1,1 - 50
B - 0 - 999 - 1,2 - 50
B - 1000 - 99999 - 1,1 - 60
C - 0 - 99999 - 1,8 - 100
D - 0 - 999 - 1,2 - 40
D - 1000 - 99999 - 1,1 - 20
Je voudrais une macro qui dans la colonne 5 de la 1ère feuille calcule:
(Délai * Coefficient + Nbre Jours)/5
Tout ça en prenant en compte la zone géographique et la borne quantité mini et maxi
Par exemple, ligne 1, il faut trouver 23,64
Ligne 2 = 23,18 etc
Merci de votre aide
Bonjour,
Merci de traduire tes données dans un feuil Excel et de nous l'envoyer pour tester et ainsi donner une solution sans
perdre du temps.
Bonjour Roran, Raja,
je te propose ce fichier Excel :
Ctrl e ➯ travail effectué ; Alt F11 pour voir la macro, puis revenir sur Excel
dhany
Bonjour Roran,
suite à ta demande en MP, voici ton nouveau fichier :
je ferai plus tard l'explication du code VBA.
dhany
Merci, c'est parfait
Roran a écrit :Merci, c'est parfait
merci pour ton retour, et pour avoir passé le sujet en résolu !
mon prochain post sur ce sujet sera l'explication du code VBA !
(mais ça va être très long à faire, alors t'étonne pas si ça tarde)
dhany
Bonjour Roran,
voici l'explication du code VBA :
Option Explicit : oblige à déclarer toutes les variables utilisées
Sub Essai() .. End Sub : sub Essai()
déclaration de plusieurs variables :
Dim FX As Worksheet, Z1 As Range, Z2 As Range
Dim Qté%, Coef!, Nbj%, lg1&, lg2&, dv As Byte : Qté% : idem que Qté As Integer ; lg1& : idem que lg1 As Long
Worksheets("Feuil1").Select : sélection de la feuille "Feuil1"
Set FX = Worksheets("Feuil2") : FX fait référence à "Feuil2"
lg1 = 2 : lg1 va être utilisé pour lire "Feuil1" ; 2 car les données commencent en ligne 2
Application.ScreenUpdating = 0 : gel de l'affichage écran ➯ l'écran n'est plus mis à jour ➯ ça va plus vite
Do .. Loop : 1ère boucle infinie (mais plus loin, y'a c'qui faut pour en sortir) ; cette 1ère boucle est pour lire toutes les lignes de "Feuil1"
Set Z1 = Cells(lg1, 2) : Z1 fait référence à la cellule qui est en ligne lg1, colonne B (sur "Feuil1") ; c'est donc la colonne Zone (Z1 = Zone de "Feuil1") ; et juste pour info, le code VBA n'utilise pas la colonne A "Réf" : on peut très bien s'en passer pour faire l'exo.
If Z1 = "" Then Exit Sub : voilà la porte de sortie de la boucle infinie : on s'arrête si on rencontre une cellule vide en colonne B ; donc ici en ligne 9 (et c'est ce test qui te permet d'ajouter d'autres lignes si besoin : adaptation automatique au nombre de lignes)
With Z1 .. End With : avec Z1
Qté = .Offset(, 1) : Quantité (sur la même ligne, 1 colonne à droite)
lg2 = 2 : lg2 va être utilisé pour lire "Feuil2" ; 2 car les données commencent en ligne 2
Do .. Loop : 2ème boucle infinie (mais plus loin, y'a c'qui faut pour en sortir) ; cette 2ème boucle est pour lire toutes les lignes de "Feuil2"
Set Z2 = FX.Cells(lg2, 1) : Z2 fait référence à la cellule qui est en ligne lg2, colonne A (sur FX = "Feuil2")
If Z2 = "" Then Exit Do : voilà la porte de sortie de la boucle infinie : on s'arrête si on rencontre une cellule vide en colonne A ; donc ici en ligne 9 (et c'est ce test qui te permet d'ajouter d'autres lignes si besoin : adaptation automatique au nombre de lignes)
If Z2 = Z1 Then .. End If : si les 2 Zones correspondent
dv = 1 + (Qté <= Z2.Offset(, 2)) : Z2.Offset(, 2) : c'est la cellule qui est sur la même ligne que Z2 (donc sur lg2), 2 colonnes à droite ; c'est donc Qté maxi ; si Qté <= Qté maxi, le test est VRAI ➯ ça vaut -1, et 1 + (-1) = 1 - 1 = 0 ➯ dv = 0 ; si le test est FAUX (Qté > Qté maxi) : FAUX vaut 0, et 1 + (0) = 1 ➯ dv = 1 ; dv (décalage vertical) permettra de lire la bonne ligne : celle en cours ou celle qui suit juste en dessous
Coef = Z2.Offset(dv, 3) : par rapport à Z2, dv lignes dessous et 3 colonnes à droite : on a le bon Coef
Nbj = Z2.Offset(dv, 4) : par rapport à Z2, dv lignes dessous et 4 colonnes à droite : on a le bon Nbj
Exit Do : et comme on a trouvé nos 2 infos, on sort aussitôt de la 2ème boucle
lg2 = lg2 + 1 : sinon on continue ; et on incrémente lg2 pour lire la ligne suivante (sur "Feuil2")
.Offset(, 3) = Application.RoundUp((.Offset(, 2) * Coef + Nbj) / 5, 0) : par rapport à Z1, même ligne lg1 et 3 colonnes à droite (de la colonne B) : c'est la colonne E "Calcul" : là ou on va écrire le résultat de la formule ; .Offset(, 2) est la colonne D "Délai" ➯ (Délai × Coef × Nbj) / 5 ; j'avais utilisé Round(.., 2) pour un arrondi à 2 décimales ; c'est maintenant RoundUp(.., 0) pour un arrondi supérieur, à l'entier (0 décimales).
lg1 = lg1 + 1 : on incrémente lg1, pour lire la ligne suivante (sur "Feuil1")
dhany
Bonsoir Roran,
j'ai bien reçu ton MP où tu m'as écrit un seul mot : « Merci » ; mais ç'aurait été mieux que tu me répondes ici, pour que les lecteurs de cette conversation sachent que tu as lu l'explication de code VBA que tu m'avais demandée en MP :
d'autre part, j'espère que ma longue explication détaillée ne t'as pas ennuyée, et que tu as su en tirer tout le bénéfice.
à te lire pour avoir ton avis (et ici, pas en MP !
dhany
Et juste une dernière question, si je mets une zone F dans une des lignes de la feuille 1 qui n’existe pas dans le tableau de la feuille 2, il se passe quoi au niveau du calcul?
dhany a écrit :à te lire pour avoir ton avis (et ici, pas en MP !
).
j'ai passé beaucoup de temps à écrire ma longue explication de code VBA, alors avant de me poser une autre question, tu devrais avoir la courtoisie de me donner ton avis sur cette longue explication (même si c'est pour me dire que mon explication était trop longue et que tu aurais préféré en avoir une plus courte).
dhany
Roran46 a écrit :si je mets une zone F dans une des lignes de la feuille 1 qui n’existe pas dans le tableau de la feuille 2, il se passe quoi au niveau du calcul ?
ça sert à quoi que je fasse une longue explication détaillée, ligne par ligne et pas à pas ?
si tu la lis attentivement, tu y trouveras la réponse à ta question !
et puis, fais l'essai : tu verras bien ce qu'il se passe !
dhany
L’explication est très claire. Ce que je captais pas dans le code au départ. C’était Z1 Z2 et dv.
Mais du coup, si Z2 n’est jamais égal à Z1, la condition n’est pas remplie donc pas de calcul.
Roran46 a écrit :L’explication est très claire.
ah, enfin un avis sur mon explication de code VBA ! merci pour ton appréciation !
tu a écrit :si Z2 n’est jamais égal à Z1, la condition n’est pas remplie donc pas de calcul.
ah, bravo ! tu as trouvé tout seul la bonne réponse !
dhany