[excel 2004] ma formule est trop longue

[ccciolll]

Bonjour, j'ai préparé une formule pour excel (j'ai pour cela utilisé xdata et une liste de lettres de l'alphabet sinon ça m'aurait pris des heures&#8230 pour un usage précis et récurrent.

Voici la formule en question (un peu raccourcie néanmoins, c'est pour montrer le principe, considérez que ça continue comme ça de façon «*logique*»).

=CONCATENER(SI(ESTVIDE($C2);"";CONCATENER($C$1;$B$1));
SI(ESTVIDE($D2);"";CONCATENER($D$1;$B$1));
SI(ESTVIDE($E2);"";CONCATENER($E$1;$B$1));
SI(ESTVIDE($F2);"";CONCATENER($F$1;$B$1));
SI(ESTVIDE($G2);"";CONCATENER($G$1;$B$1));
SI(ESTVIDE($H2);"";CONCATENER($H$1;$B$1)); 

 […]  

SI(ESTVIDE($BY2);"";CONCATENER($BY$1;$B$1));
SI(ESTVIDE($BZ2);"";CONCATENER($BZ$1;$B$1)))

Les cracks en excel auront probablement deviné mes intentions en lisant cet extrait de code.

Je vais tout de même tenter de le résumer :
je fais un tableau (ce tableau sera ensuite un outil qui servira de nombreuses fois, avec des données toujours différentes)
En B1 je saisis mon séparateur
De C1 à BZ1 je mets des noms, tous différents, que je changerai à chaque utilisation de l'outil (notez qu'il est tout à fait possible que je n'aille pas jusqu'à BZ1, dans certains cas, je n'aurais peut-être que C1 à H1 utilisés, par exemple)
La formule sera placée en B2 puis copiée-glissée sur la colonne 2 jusqu'à Bnn en fonction de mes besoins.
Ensuite, je tape des x (ou autre chose, mais les X je trouve ça parlant) dans les cellules B2 à BZnn selon ce que je souhaite voir apparaître dans la cellule comportant la formule.

Problème, ma formule complète mesure actuellement 3306 caractères, je n'ai droit qu'à 1024 (je viens de le découvrir).

Alors la question est simple : est-il possible de simplifier cette formule pour réduire son nombre de caractères ?
Ou vais-je absolument devoir passer par des cellules de sous-formule (Genre CA2 rassemble de C2 à V2, CB2 rassemble de W2 à AK2, etc. et ensuite ma formule B2 rassemble CA2+CB2+CC2… ?)

 

[Aliboron]

Je ne comprends pas précisément ce que tu cherches à faire. De toute façon, le nombre de fonctions SI() imbriquées est limité (à 14, je crois). Mais surtout, je ne comprends pas trop pourquoi tu dois dans toutes les cellules de la ligne 2 la totalité des données. Dans quel but ?

Personnellement, il m'arrive d'avoir à traiter un problème plus ou moins approchant (concaténation de matricules) et je me contente de reprendre à chaque fois la cellule précédente, ce qui me donne dans la dernière la chaîne complète. Ce que j'obtiens avec quelque chose comme :
- en cellule C2 =SI(C1="";"";C1&$B$1)
- en cellule D2 =SI(D1="";C2;C2&D1&$B$1)
- en cellule E2 et suivantes, recopier D2

Mais bon, comme je ne suis pas sûr d'avoir compris ce que tu cherches à faire...

 

[ccciolll]

Une image vaut dix mille mots :


Voilà ce que je cherche à faire (enfin, plus précisément, voilà ce que je faisais jusqu'à ce que je m'aperçoive que 19 colonnes (de C à T) ne suffiraient pas et que j'ai voulu étendre la logique de ma formule.

 

[Aliboron]

Ah, oui, alors c'est assez différent de ce que j'avais cru comprendre. Pour faire ça, le mieux, à mon avis, c'est de faire appel à une macro événementielle. En clair, tu ouvres l'éditeur Visual Basic, tu vas sur la feuille de code correspondant à la feuille de travail concernée et tu colles la macro suivante :

Private Sub Worksheet_Change(ByVal Target As Range)
MaLigne = Target.Row
Dim MaChaine As String
MaChaine = ""
For i = 3 To 78
    If ActiveSheet.Cells(MaLigne, i).Value <> "" Then
        MaChaine = MaChaine + ActiveSheet.Cells(1, i).Value + ActiveSheet.Cells(1, 2).Value
    End If
Next i
    Range("B" & MaLigne).Value = Left(MaChaine, Len(MaChaine) - Len(ActiveSheet.Cells(1, 2).Value))
    Exit Sub
End Sub

En principe, ça devrait te renseigner automatiquement la cellule B de chaque ligne sur laquelle tu saisis des valeurs (si tu ajoutes une croix sur une des cellules entre C6 et BZ6 dans ton exemple, ça te reportera les résultat attendu). Au besoin, ça peut s'affiner, évidemment...

 

[ccciolll]

Je ne suis pas du tout un utilisateur de macro aussi ais-je peut-être mal appliqué ton code, mais voici le résultat obtenu

J'ai cliqué sur Outil>Macro>visualBasicEditor
puis là, ma feuille était déjà sélectionnée.
j'ai double-cliqué et il m'a ouvert une fenêtre. J'ai collé ton code puis suis allé sur affichage>microsoftExcel

là j'ai commencé ) mettre un "/" dans B1 comme séparateur (car mon fichier d'origine contient un espace, pas très visuel comme résultat pour des tests). Puis je suis allé modifier les données C2 D2 E2 etc et d'un coup il m'affiche


et me dit qu'il manque de mémoire

Ah bon ?

Je clique débogage et là j'ai ça


Et si je vais sur la feuille, je vois ça :


apparemment il colle des résultats dans B1, et si je regarde de plus près, je vois qu'il les colle à l'infini.

Qu'est-ce qui a loupé ?

EDIT : je viens de refaire un essai.
Tant que je ne touche pas à B1, ça fonctionne plutôt bien.
Peut-être faudrait-il que je place mon séparateur ailleurs qu'en B1 ? Sinon ça fait un effet loop je pense.

RE-EDIT : non, en fait le problème se produit quel que soit la cellule que je touche dans la ligne 1.
Mais effectivement, je pense que le B1 n'est pas un bon choix de cellule pour contenir le séparateur.
Faudrait que je le place ailleurs pour éviter l'effet loop.

C'est dans quel endroit de ton code qu'on spécifie l'emplacement de cette cellule ?

RE RE EDIT :

OK, j'ai trouvé, voilà le code modifié :

Private Sub Worksheet_Change(ByVal Target As Range)
MaLigne = Target.Row
Dim MaChaine As String
MaChaine = ""
For i = 3 To 78
    If ActiveSheet.Cells(MaLigne, i).Value <> "" Then
        MaChaine = MaChaine + ActiveSheet.Cells(1, i).Value + ActiveSheet.Cells(1, 1).Value
    End If
Next i
    Range("B" & MaLigne).Value = Left(MaChaine, Len(MaChaine) - Len(ActiveSheet.Cells(1, 1).Value))
    Exit Sub
End Sub

j'ai juste changé 1,2 par 1,1 et ça roule, ma cellule de séparateur est en A1.

RE RE RE EDIT

Par contre ça n'est pas rétroactif si je modifie le séparateur ou l'un des noms de la ligne 1 (ou tout du moins ça ne modifie que la ligne 1, ce qui n'a pas d'intérêt). Dommage…
Et ça prend un peu de temps à chaque fois, on dirait qu'il a un peu de mal.

N'y aurait-il pas moyen de lui demander d'appliquer la Macro que à la fin du remplissage du tableau (et à l'ensemble du tableau), pour éviter tout ce temps à attendre ?

 

[Aliboron]

Non, c'est moi. C'est vrai que je n'avais pas pensé à une modification dans la première ligne (je n'ai fait que quelques tests). Le fait de modifier la cellule B1 remplaçait son contenu par l'ensemble de la ligne, et en boucle... fatalement ça finissait mal !

Pour réparer ça, il suffit d'exclure la première ligne en ajoutant

If MaLigne = 1 Then Exit Sub

juste après la ligne

MaLigne = Target.Row

 

[ccciolll]

Ah oui, c'est beaucoup mieux, merci.

Avec ça je suis paré pour l'hiver.

Ne reste que ce désagrément du bazar qui met tj des plombes à chaque coche, mais je crois que je vais le contourner en faisant mes coches Macro désactivées, puis enregistrer le fichier, activer les macros et mettre un coup de return dans toutes les lignes. À voir.

 

[Aliboron]

Tu peux aussi tenter d'accélérer l'exécution en ajoutant au début une ligne

    Application.ScreenUpdating = False

et à la fin

    Application.ScreenUpdating = True

. Faut voir (pas en situation de tester "en vrai" pour le moment).

 

[ccciolll]

Finalement, je suis revenu à ma solution avec concateners en cascade, plus réactif (la macro ralentissait le mac (mais je n'ai pas essayé ton complément de code) et surtout ne se mettait pas à jour si je changeais une référence.)

Et j'ai aussi repensé tout le fichier en vertical car je souhaite en outre pouvoir trier mes références et je n'ai pas trouvé comment créer des filtres auto en horizontal de façon simple.