Automatisation calcul d'heures en fonction des délais et mensualités

Bonjour,

Je souhaiterais automatiser le calcul d'une formule dans un tableur Excel. Seulement, il y a de nombreux paramètres à prendre en compte, et je me demande comment procéder.

A savoir que je n'ai pas de notion de VBA, mais ça ne me dérangerait pas de l'utiliser si cela est nécessaire.

J'explique mon problème.

Le tableur rend compte du déroulé d'un type de tâche à effectuer, avec le nombre de tâches par mois (mensualités), pour chaque phase de cette tâche on a le délais (en mois) et le temps (en heure) que cela prend pour l'effectuer.

La colonne K est un calcul permettant de savoir combien d'heures cela prend au total.

J'ai fais la formule à la main en commençant de la première tâche au premier mois d'une année, étirer la formule ne suffit pas puisque la formule est en cascade. C'est à dire que à la 2eme ligne, il faut continuer le calcul pour les premières tâches qui ont commencé en janvier, et ajouter le temps des tâches de février, ainsi de suite.

Au final, après que cela est été fait pour la première tâche, on obtient la formule complète.

Merci
pour votre aide. Je ne sais pas si j'ai bien expliqué, étant donné que le raisonnement est complexe à intégrer. Le fichier est disponible ci-joint.

Salut Astaroth,

Porte bien ton pseudo, toi : c'est un truc diabolique... dont je ne capte pas encore la logique !
Puisque, à chaque étape, tu additionne le total des étapes précédentes, pourrais-tu me refaire tes formules comme celles-ci, histoire de comprendre le truc.

En [K7] : =C7*D7+K6
En [K8] : =C8*D8+C6*E8+C6*J8+
K7

Par exemple, en [K9] : =C7*D7+C8*D8+C6*J8+C9*D9+C7*E9+C7*J9+C6*F9, je ne retrouve pas C6*D6, C6*E8 ??

Cela dit, une explication française de ta logique de calcul me ferait le plus grand bien !


A+

Salut le forum,

pour ceux qui sont intéressés par le sujet et qui ne s'en sortent pas (comme moi), j'ai concocté une petite boucle qui décortique chaque formule de la colonne [K:K].

Un clic sur une formule décompose la formule opération après opération en colorant les cellules concernées.
Avec ça, je commence à comprendre la logique du bazar.

Si vous avez le temps avant que je m'y mette cette soirée...
Bon travail !

4astaroth.xlsm (25.07 Ko)


A+

Salut le forum,

aux deux courageux qui ont téléchargé le fichier précédent et à ceux qui voudraient se joindre à l'aventure, voici un fichier amélioré pour mieux comprendre le fonctionnement du calcul.

Toujours cliquer sur une formule en colonne [K:K]...

4astaroth-v2.xlsm (28.58 Ko)


A+

Salut à toi Curulius,

Merci d'avoir pris le temps de répondre et de te pencher sur le problème ! (Bien vu la référence au pseudo ahah).

Je vais expliquer un peu plus clairement la démarche de la formule, en détaillant, parce que c'est vrai que ce n'est pas clair du tout dans mon premier message.

Prenons l'exemple présent dans le fichier excel.

Dans la cellule [K6] on a la multiplication du Nombre/mois par le temps que cela prend, donc :

[K6] = [C6]*[D6]

Puis,

[K7] = [C6]*[D6] + [C7]*[D7]

car on ajoute au premier calcul la ligne du dessous.

Seulement, étant donné que le délais de la phase 1 est de 3 mois, le calcul [C6]*[D6] ne sera dans la formule que jusqu'à la ligne 8.

À la ligne 8 on ajoute aussi le temps de la fin de la phase 1 multipliée par le Nombre /mois du mois du premier mois (cellule [C6] et le temps de la colonne J (cellule [J8]) multipliée aussi par [C6]. Ce qui donne :

[K8] = [C6]*[D6] + [C7]*[D7] + [C8]*[D8] + [C6]*[E8] + [C6]*[J8]

Ensuite la phase 2 dure 3 mois, donc la cellule [C6] sera ajoutée à la formule et multipliée par [F9]. Seulement il n'y a plus [C6]*[D6] car cette phase est terminée (pour la première tâche).

Ainsi de suite pour toutes les phases. À noter que la phase 3 dure 4 mois pendant lesquels il n'y a pas d'heures à prendre en compte, donc de [K12] à [K15] il n'y aura pas de multiplication de la cellule [C6]. Pour la phase 5, le délais en de 10 mois mais le temps à prendre en compte n'est comptabilisé qu'au 10ème mois.

Le temps de la Phase 2 à Phase 5 (colonne J) commence à la fin de la phase 1 (3eme mois) et est à ajouter tous les mois jusqu'à la fin de la première tâche.

Comment est-ce que j'ai rempli ça à la main ?

J'ai rempli la cellule [K6], puis étiré la formule sur la cellule [K7] et y ait ajouté [C6]*[D6].

J'ai ensuite étiré la cellule [K7] jusqu'à [K8] et ai ajouté [C6]*[D6]+[C6]*[E8] + [C6]*[J8]

Ainsi de suite, en prenant soin d'ajouter [C6] multiplié par les temps à prendre en compte.

On arrive jusqu'à la fin du délais total à la cellule [K32]. À partir de là on a la formule finale, qu'il suffit d'étirer pour l'avoir sur 1 an complet.

Où est le problème ? Comme je dois effectuer la même démarche pour différents types de tâches, les délais varient, et donc ce ne sera jamais la même formule à la fin du délais total.

En récapitulant tout cela, il m'est venu l'idée de me concentrer sur la formule en [K32] (dans notre cas car le délais total est de 27 mois). Je joins à ce message une capture d'écran avec des cercles de la même couleur pour les cellules à multiplier entre elles.

auto annotee

De cette façon la seule formule à automatiser serait celle de la cellule [K32], sans passer par le raisonnement des cellules précédentes.

Je joins à ce message le détail de la formule pour la cellule [K27] si l'on commence à la ligne 1 :

formule k27 1 tache

Je pense que l'idée serait de reprendre cette formule avec des boucles FOR en fonction des délais.

Merci pour vos réponses

A+

Salut Astaroth,

pour ce tableau, et sans préjuger d'éventuelles exceptions ou particularités non-mentionnées ou d'une incompréhension de ma part, ta demande est rencontrée !

- Pour obtenir les résultats, DOUBLE-CLIQUER sur la cellule "VBA"
* les résultats sous forme de nombre en [L:L] ;
* les formules reconstituées selon le même calcul en [M:M]

- Pour visualiser le déroulé des formules
* clic sur une formule en [K:K] ou leur reconstitution en [M:M]

Ci-dessous, le code pour les phases 2 à 4

                Case 2 To 4
                    For Z = x - iRow To 3 Step -1
                        If iNb < iNbLoop Then _
                            If tTab(Z, 1) <> "" And tTab(Z + iRow, iCol) <> "" Then _
                                tExtract(x, 1) = CDbl(tExtract(x, 1)) + CDbl(tTab(Z, 1)) * CDbl(tTab(Z + iRow, iCol)): _
                                sData = sData & IIf(sData = "", "", "+") & fctCol(3) & CStr(Z + 3) & "*" & fctCol(iCol + 2) & CStr(Z + iRow + 3)
                        If tTab(iNb25 - 1, 8) <> "" And iNb < iIdx25 Then _
                            iNb = iNb + 1: _
                            iNb25 = iNb25 - 1: _
                            tExtract(x, 1) = CDbl(tExtract(x, 1)) + CDbl(tTab(Z, 1)) * CDbl(tTab(iNb25, 8)): _
                            sData = sData & IIf(sData = "", "", "+") & fctCol(3) & CStr(Z + 3) & "*" & fctCol(10) & CStr(iNb25 + 3)
                        If iNb = iNbLoop + iLoop Or iNb = iIdx25 Then Exit For
                    Next

Petite précision : que représente vraiment le nombre "19" en [J4] ?
Je ne vais pas t'expliquer ce que j'en ai fait puisque ça fonctionne mais...

As-tu d'autres tableaux à tester ?
Tu peux coller les valeurs d'autres tableaux : ça devrait aller à partir du moment où la structure est rigoureusement identique !

6astaroth.xlsm (39.16 Ko)

Merci pour ce beau casse-tête !
Diaboliquement vôtre !


A+

Salut !
Merci pour ta réponse. J'ai testé et ça fonctionne très bien !

Pour ta question cellule J4, je me suis effectivement trompé, le délais en mois n'est pas de 19 puisque l'acteur 2 intervient de la fin de la phase 2 à la phase 5.
J'ai donc remplacé 19 par [J4]=D4-(D4-1)+F4+G4+H4+I4

J'ai également testé sur d'autres tableaux de données que j'ai à disposition et cela fonctionne également.
À l'origine mon idée était de les avoir tous dans le même feuillet, mais pour que la macro fonctionne convenablement j'ai créé un feuillet par type de tâche, avec la même macro pour chacun d'entre eux.

(Le fichier est joint à ce message)

Parallèlement, j'ai essayé de comprendre un peu le code, bien que n'ayant aucune connaissance en VBA, de façon à pouvoir le faire évoluer si besoin :

1. Supprimer la colonne K et la macro qui s'y rattache, car je n'aurais alors plus besoin de son résultat.

2. Si jamais j'ajoute une phase (donc un acteur et un délais), autrement dit une colonne, changer le code (de façon à ce qu'il y ait un CASE de plus ?).

Pour le 1. je pense que c'est dans la partie Private Sub Worksheet_SelectionChange(ByVal Target As Range)

Étant donné que la colonne formule sera la seule cliquable et détaillant les cellules qui sont multipliées, celle-ci sera en colonne L et non plus M (la colonne K étant remplacée par le calcul).

Il est également nécessaire de changer les colonnes pour la formule de calcul automatisé.

--> Je pense que ce que j'ai changé fonctionne, mais je voudrais bien une vérification (j'ai laissé le code original commenté)

La ligne sData = Split(Target, "=")(1) doit être à modifier car j'obtiens une erreur lorsque l'on clique après la dernière ligne du tableau.

Pour le 2. étant donné que je ne comprends pas tout le langage j'ai du mal à voir ce qu'il faudrait ajouter ou changer le cas échéant.

N.B. Y a-t-il un moyen de stopper les fenêtres de dialogue qui détaillent le calcul ? (le détail est très utile, mais c'est si jamais on clique sur la cellule sans faire exprès et que l'on ne veut pas cliquer sur OK plein de fois).

--

Merci encore pour le temps passé, cela m'a beaucoup aidé, motivé et intéressé !

4astaroth-test.xlsm (77.61 Ko)

Salut Astaroth,

voilà ton fichier modifié.
L'intérêt d'un code dans le module 'ThisWorkbook' est d'être valable pour toutes les feuilles du classeur : on écrit donc une seule procédure !

Dans ton cas, le souci est donc de déterminer le nombre de phases.
Si je postule que, d'office, il y a une colonne "Fin de phase 1" et une dernière "Phase 2 à Phase 5" (dans 'Type 5', cela ne devrait-il pas être "Phase 2 à Phase 6" ??) et que ton tableau est de structure et placement strictement identiques, il suffit donc de calculer la dernière colonne (iNbCol) et d'en soustraire 5, le nombre de colonnes non concernées.

    iNbCol = .Cells(4, Columns.Count).End(xlToLeft).Column
    iNbPhase = iNbCol - 5

Ensuite, le code est adapté en fonction de ces données.

Petits plus :
- un peu de rigueur : pour différencier les feuilles-Tâches à calculer à d'autres feuilles éventuelles, la cellule en [B5] doit obligatoirement indiquer le nom de la feuille.
Cette cellule se colorera automatiquement d'orange pour signaler la cellule où double-cliquer pour démarrer le calcul.
- la cinématique descriptive des formules est également déclenchée par un double-clic sur la formule désirée.
J'ai abandonné le système de la MsgBox pour un défilement automatique avec SLEEP.

Sleep IIf([B2] = "", 500, .[B2])

En [B2] se trouve une cellule en fonte rouge : c'est le temps de pause de SLEEP dans la cinématique, calculé en 1000e de seconde.
Si [B2] est vide, ce sera 500.
Évidemment, tu n'as plus le détail arithmétique de chaque étape du calcul mais, si cela t'est nécessaire, je trouverai bien le moyen de le remettre.

Tu ne m'as toujours pas expliqué la raison d'être de [J4] si 5 phases ou [K4] si 6 phases !!

Bon, zut, service téléchargement indisponible : j'envoie le fichier dès que possible.


A+

4astaroth-v3.xlsm (49.35 Ko)
Rechercher des sujets similaires à "automatisation calcul heures fonction delais mensualites"