[VBA] Formule (beaucoup) trop longue

Le 22/10/2020 à 12:43

Bonjour à tous,

J'ai un tableur Excel où j'ai en gros 42 hauteurs pour lesquels je calcule à chaque fois les coefficients linéaires pour 3 variables (sous la forme ax+b).

A partir de ces 42 hauteurs de référence où je connais toutes les variables, je calcule une phase de montée où on atteint une hauteur maximale. Selon cette hauteur maximale, on a une phase de descente qui utilise les même coefficients que la phase de montée, ou d'autres coefficients en fonction de la hauteur maximale atteinte (10 coefficients de descente en tout + celui de montée).

Ces 42 intervalles de hauteurs impliquent d'avoir une formule extrêmement longue pour les traiter. En voici un exemple :

=SI(C4<0;ERR;SI(C4<Synthèse!Y$26;C4*Synthèse!AJ$25-Synthèse!AK$25;SI(C4<Synthèse!Y$27;C4*Synthèse!AJ$26-Synthèse!AK$26;SI(C4<Synthèse!Y$28;C4*Synthèse!AJ$27-Synthèse!AK$27;SI(C4<Synthèse!Y$29;C4*Synthèse!AJ$28-Synthèse!AK$28;SI(C4<Synthèse!Y$30;C4*Synthèse!AJ$29-Synthèse!AK$29;SI(C4<Synthèse!Y$31;C4*Synthèse!AJ$30-Synthèse!AK$30;SI(C4<Synthèse!Y$32;C4*Synthèse!AJ$31-Synthèse!AK$31;SI(C4<Synthèse!Y$33;C4*Synthèse!AJ$32-Synthèse!AK$32;SI(C4<Synthèse!Y$34;C4*Synthèse!AJ$33-Synthèse!AK$33;SI(C4<Synthèse!Y$35;C4*Synthèse!AJ$34-Synthèse!AK$34;SI(C4<Synthèse!Y$36;C4*Synthèse!AJ$35-Synthèse!AK$35;SI(C4<Synthèse!Y$37;C4*Synthèse!AJ$36-Synthèse!AK$36;SI(C4<Synthèse!Y$38;C4*Synthèse!AJ$37-Synthèse!AK$37;SI(C4<Synthèse!Y$39;C4*Synthèse!AJ$38-Synthèse!AK$38;SI(C4<Synthèse!Y$40;C4*Synthèse!AJ$39-Synthèse!AK$39;SI(C4<Synthèse!Y$41;C4*Synthèse!AJ$40-Synthèse!AK$40;SI(C4<Synthèse!Y$42;C4*Synthèse!AJ$41-Synthèse!AK$41;SI(C4<Synthèse!Y$43;C4*Synthèse!AJ$42-Synthèse!AK$42;SI(C4<Synthèse!Y$44;C4*Synthèse!AJ$43-Synthèse!AK$43;SI(C4<Synthèse!Y$45;C4*Synthèse!AJ$44-Synthèse!AK$44;SI(C4<Synthèse!Y$46;C4*Synthèse!AJ$45-Synthèse!AK$45;SI(C4<Synthèse!Y$47;C4*Synthèse!AJ$46-Synthèse!AK$46;SI(C4<Synthèse!Y$48;C4*Synthèse!AJ$47-Synthèse!AK$47;SI(C4<Synthèse!Y$49;C4*Synthèse!AJ$48-Synthèse!AK$48;SI(C4<Synthèse!Y$50;C4*Synthèse!AJ$49-Synthèse!AK$49;SI(C4<Synthèse!Y$51;C4*Synthèse!AJ$50-Synthèse!AK$50;SI(C4<Synthèse!Y$52;C4*Synthèse!AJ$51-Synthèse!AK$51;SI(C4<Synthèse!Y$53;C4*Synthèse!AJ$52-Synthèse!AK$52;SI(C4<Synthèse!Y$54;C4*Synthèse!AJ$53-Synthèse!AK$53;SI(C4<Synthèse!Y$55;C4*Synthèse!AJ$54-Synthèse!AK$54;SI(C4<Synthèse!Y$56;C4*Synthèse!AJ$55-Synthèse!AK$55;SI(C4<Synthèse!Y$57;C4*Synthèse!AJ$56-Synthèse!AK$56;SI(C4<Synthèse!Y$58;C4*Synthèse!AJ$57-Synthèse!AK$57;SI(C4<Synthèse!Y$59;C4*Synthèse!AJ$58-Synthèse!AK$58;SI(C4<Synthèse!Y$60;C4*Synthèse!AJ$59-Synthèse!AK$59;SI(C4<Synthèse!Y$61;C4*Synthèse!AJ$60-Synthèse!AK$60;SI(C4<Synthèse!Y$62;C4*Synthèse!AJ$61-Synthèse!AK$61;SI(C4<Synthèse!Y$63;C4*Synthèse!AJ$62-Synthèse!AK$62;SI(C4<Synthèse!Y$64;C4*Synthèse!AJ$63-Synthèse!AK$63;SI(C4<Synthèse!Y$65;C4*Synthèse!AJ$64-Synthèse!AK$64;SI(C4<Synthèse!Y$66;C4*Synthèse!AJ$65-Synthèse!AK$65;ERR))))))))))))))))))))))))))))))))))))))))))

Pour automatiser la recherche de la bonne phase de descente, j'ai créé un programme VBA. Son but est de repérer la cellule où le maximum est atteint, puis de modifier la formule dans la cellule immédiatement en dessous (j'utilise un .Offset) avec les bons coefficients de descente calculés dans une feuille du tableur, puis utiliser un .FillDown pour finir la phase de descente. J'arrive à repérer quelle descente choisir, mais je n'arrive pas à mettre la bonne formule dans mon programme VBA. Je me suis renseigné sur ce problème et la formule dépasse en fait largement la capacité de caractères d'une formule dans VBA. J'ai essayé plusieurs solutions (scinder en plusieurs lignes, etc.) mais aucune n'a fonctionnée jusqu'à présent.

Avez-vous une idée de ce qui pourrait marcher ? Egalement, je suis preneur d'idées pour simplifier ces très longues formules si jamais vous avez :)

Merci à tous et bonne journée !

Le 22/10/2020 à 12:53

Bonjour ULaval et

Pour commencer, une petite présentation ICI aurait été la bienvenue

Sinon pour ton problème, il faut créer une table de correspondance dans laquelle tu viendras piocher,
mais sans fichier, difficile de te dire si c'est réalisable

@+

Le 22/10/2020 à 14:54

Ca marche, je vais faire une présentation !

Par table de correspondance tu entends un tableau connexe dans lequel la formule est déjà présente et qu'il me suffit de copier dans le tableau principal c'est bien ça ?

Le 22/10/2020 à 15:53

Bonjour,

Oulala , c'est vrai que c'est long.

C'est difficile sans fichier mais on dirait que quelque chose comme ça pourrait aller :

=index($AJ$25:$AJ$66*$C$4-$AK$25:$AK$66;equiv($C$4;$Y$25:$Y$66;1))

Cdlt,

Le 22/10/2020 à 17:43

Oui c'est malheureusement très long...

J'ai essayé les différentes solutions que vous avez amené mais aucune d'entre elles n'a marché.

Voici le fichier anonymisé. En fait, mon but est d'automatiser le changement de "Descente" des colonnes D et E de la feuille "Calculs". Pour ce faire, j'ai créé la macro "Descente" qui détermine dans quelle intervalle on se trouve, puis entre la nouvelle formule dans la cellule immédiatement en dessous de celle où le maximum de la variable x a été détecté. Pour le calcul de la variable x (qui dépend de la colonne C (variable z) de la feuille calcul) les coefficients sont compris entre les colonnes AJ et BE, et pour le calcul de la variable y (qui dépend de x) les coefficients sont compris entre les colonnes BF et CA.

8table-anon.zip (475.16 Ko)
Le 22/10/2020 à 21:59

Si ma solution était bonne mais il fallait juste rajouter le nom de la feuille synthèse (que j'ai oublié sur ma formule).

Par contre, c'est un peu le foutoir sur la feuille synthèse. J'imagine que c'est normal mais Il vaudrait mieux, si possible, que vous ayez des colonnes de même taille et parveniez à trouver une logique vraiment commune. Et si vous mettiez ensuite le tout en tableau structuré, ce serait beaucoup plus simple et clair...

Cdlt,

21table-anon-xlsm.zip (859.31 Ko)
Le 23/10/2020 à 10:20

Alors là j'avoue que je suis bluffé, ça fonctionne parfaitement et ça va régler mon problème de longueur d'expression !

Je ne connaissais pas cette expression Index, je vais me renseigner...

En tout cas merci beaucoup 3GB ! Oui c'est sûr que c'est le foutoir, mais j'ai simplement supprimé beaucoup de données pour l'anonymiser, dans les faits il y a 10 descentes qui comblent les espaces qui restent dans la feuille synthèse ainsi que des graphiques...

Merci encore et bonne journée !

Le 23/10/2020 à 10:29

Bonjour,

Index permet de renvoyer la valeur dans une matrice aux ligne et colonne stipulées en 2è et 3è paramètres.

Equiv permet de renvoyer la position d'une valeur dans une plage, en l'occurrence une ligne dans notre cas. L'imbrication d'index et equiv produit un résultat de recherche mais plus intéressant que le recherchev.

A noter qu'ici, c'est EQUIV qui est bien utile car, au lieu de faire une recherche de la valeur dans la colonne Y avec correspondante exacte (EQUIV(V;plage;0)), il fait une recherche approximative, et renvoie la plus grande position de correspondance inférieure ou égale à la valeur cherchée (EQUIV(V;plage;1)).

Bonne journée à vous aussi et bon courage !

Le 23/10/2020 à 11:51

Merci pour ces explications ! Je m'étais effectivement penché sur le recherchev mais je n'avais pas réussi à trouver mon bonheur... Cette solution a l'air beaucoup plus puissante !

Bon, malheureusement j'ai encore un peu de mal avec l'algo VBA...

Le code (simplifié) est le suivant (tiré du fichier table-anon) :

Sub Descente()
    Dim varx_max
    varx_max = Range("L3")
    For Each cell In Worksheets("Calculs").Range("D4:G12656").Cells
    If cell.Value = varx_max Then
        If elev_max < Worksheets("Synthèse").Range("A54") Then
            Worksheets("Synthèse").Range("I4") = 0
        End If
        If Worksheets("Synthèse").Range("A54") <= elev_max And elev_max < Worksheets("Synthèse").Range("A55") Then
            Worksheets("Synthèse").Range("I4") = 1
        End If
        If Worksheets("Synthèse").Range("A55") <= elev_max And elev_max < Worksheets("Synthèse").Range("A56") Then
            Worksheets("Synthèse").Range("I4") = 2
            cell.Offset(1, 0).Formula = "=@INDEX(Synthèse!$AN$25:$AN$66*" & cell.Offset(1, -1).Adress & "-Synthèse!$AO$25:$AO$66;EQUIV(" & cell.Offset(1, -1).Adress & ";Synthèse!$AA$25:$AA$66;1))"
            cell.Offset(1, 1).Formula = "=@INDEX(Synthèse!$BJ$25:$BJ$66*" & cell.Offset(1, 0).Adress & "-Synthèse!$BK$25:$BK$66;EQUIV(" & cell.Offset(1, 0).Adress & ";Synthèse!$A$25:$A$66;1))"
            Worksheets("Calculs").Range("" & cell.Offset(1, 0).Adress & ":E12656").FillDown
        End If
    End If
    Next
End Sub

Pour le simplifier, je n'ai gardé que les 2 premières descentes, et je n'ai développé l'algorithme que pour la deuxième descente.
Ce que je cherche à faire, c'est repérer la cellule contenant le maximum dans la colonne D de la feuille Calculs (qui contient la variable x) pour intégrer à la cellule immédiatement en dessous la nouvelle formule de 3GB dedans (avec les colonnes modifiées évidemment) à l'aide de cell.Offset(1, 0).Formula .

J'utilise actuellement une boucle For pour repérer le maximum mais ça ne me semble pas être le plus efficace. Je me suis penché du côté de Do Until, mais comme pour recherchev je n'y ai pas trouvé mon bonheur...

Lorsque je rentre l'expression modifiée dans le Formula, ça me ressort également un message d'erreur. Je pense que cela vient du fait que la boucle For ne s'arrête pas une fois que le maximum est trouvé, ce que je souhaiterais faire.

Avez-vous une idée ?

Merci !

Le 23/10/2020 à 12:38

Alors pour ma part, je n'ai pas vraiment d'idée, c'est assez dur à intégrer de l'extérieur.

Je remarque une variable elev_max dont on ne connait pas la valeur. Je me demande si passer par VBA est vraiment nécessaire sachant qu'il pourrait suffire d'incorporer les formules existantes dans d'autres fonctions (SI, MAX, EQUIV par exemple).

Et quant à la boucle, je ne suis pas certain qu'elle soit impérative. Et je ne vois pas vraiment de recherche du max dans le code.

POSDUMAX = application.match(application.max(range("maplage")), range("maplage"), 0) 'attention, position au sein de la plage

qui est l'équivalent sur feuille de

=EQUIV(max(colonne); colonne; 0)

renvoie la position dans la colonne de la valeur maximale (première correspondance lorsque cette valeur n'est pas unique dans la colonne).

Pour avoir la valeur :

VALMAX = application.max(range("maplage"))

'sur feuille
=max(colonne)

pour avoir la valeur maximale.

Mais honnêtement, je ne comprends pas bien...

La colonne D contient des formules et, si je ne me trompe pas, elles sont toutes similaires. Il suffit de saisir la formule qui va en D et d'autofiller manuellement.

Rechercher des sujets similaires à "vba formule beaucoup trop longue"