Transformation VBA vers Google Sheets

Bonjour tout le monde,

Je me permets de venir vers vous avec une demande très particulière, en effet, j'ai réaliser un fichier sur Excel qu'on me demande désormais de passer sur Google, mais sur ce fichier j'avais des fonctions VBA indispensables, que je ne sais pas traduire dans le langage Google

Voici la fonction :

Function Reference(ligne As Long) As String
Premier_cas:
    If Cells(ligne, "B") <> Cells(ligne - 1, "B") Then 'si le fournisseur est différent du précédent, alors
        Reference = Cells(ligne, "G") & "-" & Cells(ligne, "H") & "-" & 1 'on affecte le N° 1

Deuxieme_cas:
    ElseIf Cells(ligne, "B") = Cells(ligne - 1, "B") Then 'si le fournisseur est identique du précédent, alors

        If Cells(ligne, "C") <> Cells(ligne - 1, "C") Then 'si la matière est différente de la précédente,  alors
            Reference = Cells(ligne, "G") & "-" & Cells(ligne, "H") & "-" & 1 'on affecte le N° 1

        ElseIf Cells(ligne, "C") = Cells(ligne - 1, "C") Then 'si la matière est identique à la précédente,  alors
            If Cells(ligne, "E") <> Cells(ligne - 1, "E") Then 'si la finition est différente de la précédente,  alors
                PosTiret = InStrRev(Cells(ligne - 1, "I"), "-", -1) 'on relève la position du dernier tiret de la référence précédente
                N° = Mid(Cells(ligne - 1, "I"), PosTiret + 1, 3) + 1 'on récupère le N° de la référence précédente auquel on ajoute 1
                Reference = Cells(ligne, "G") & "-" & Cells(ligne, "H") & "-" & N°

            ElseIf Cells(ligne, "E") = Cells(ligne - 1, "E") Then 'si la finition est identique à la précédente,  alors
                If Cells(ligne, "F") <> Cells(ligne - 1, "F") Then 'si la teinte est différente de la précédente,  alors
                    PosTiret = InStrRev(Cells(ligne - 1, "I"), "-", -1) 'on relève la position du dernier tiret de la référence précédente
                    N° = Mid(Cells(ligne - 1, "I"), PosTiret + 1, 3) + 1 'on récupère le N° de la référence précédente auquel on ajoute 1
                    Reference = Cells(ligne, "G") & "-" & Cells(ligne, "H") & "-" & N°

                ElseIf Cells(ligne, "F") = Cells(ligne - 1, "F") Then 'si la teinte est identique à la précédente,  alors
                    Reference = Cells(ligne - 1, "I") 'on recopie la référence de la ligne au-dessus
                End If
            End If
        End If
    End If
End Function

Pour faire simple j'ai un tableau qui fonctionne de la manière suivante :

https://docs.google.com/spreadsheets/d/1CasvNzZUayawLhwANwSR09vD-tbMxhGfBjU2xStYhu4/edit?usp=sharing

Toutes les colonnes de A à H sont automatiquement remplies (avec des formules) elles vont rechercher des données d'ailleurs ou pour le cas du Nom descriptif font un concat de deux colonnes.

Mon besoin est sur la colonne I

En effet :

J'aimerais réussir à créer un nom de modèle qui vient reprendre la colonne G et H une sorte de concaténation avec un "-" entre les deux mais en plus de cela, il y a une incrémentation de numéro à la fin et ce numéro vient par rapport aux colonnes : Essence / Epaisseur / Finition / Teinte (pour les colonnes finition et teinte on peut prendre nom descriptif au besoin c'est exactement la même donnée mais en concaténée dans une seule colonne)

1er cas : L'essence / l'épaisseur / la finition et la teinte sont vue pour la 1ère fois alors j'ai le numéro 1 qui vient s'ajouter après ma concaténation de la colonne G et H donnant par exemple : PJ-HA-1

2ème cas : Mon essence ma finition et ma teinte sont déjà présents et sont absolument pareils, mais mon épaisseur est différente, je n'incrémente pas le numéro

Ex :

3ème cas : Mon essence est différente (et n'était pas déjà présent avant) mais tous les autres paramètres sont semblables, alors j'ai un nouveau cas

Ex :

4ème cas : Mon essence est similaire mais ma teinte (et/ou ma finition) est différente alors j'incrémente le numéro à la fin

Ex :

J'espère être plus ou moins clair dans mon besoin, je ne vois pas comment je pourrais faire cela en formule pour moi le seul moyen est d'avoir recours à du Google App Script mais je ne maîtrise pas alors j'aurais grandement besoin de vos lumières :)

Merci d'avance à vous !

Cordialement,

Bonjour,

L'accès au fichier est refusé

Pour partager https://www.sheets-pratique.com/fr/cours/partage

Hello Steelson,

Oui j'ai oublié de passer en éditeur pour tous ceux avec le lien, j'ai réglé le souci en voyant le mail désolé c'est ma faute.

kinders59 a écrit :

1er cas : L'essence / l'épaisseur / la finition et la teinte sont vue pour la 1ère fois alors j'ai le numéro 1 qui vient s'ajouter après ma concaténation de la colonne G et H donnant par exemple : PJ-HA-1

tu mets "vu pour la première fois", pour le même fournisseur ou pour tout le monde ?

edit : en fait ma question n'a pas lieu d'être car il n'y a qu'un fournisseur dans le tableau

un challenge ?

kinders59 a écrit :

je ne vois pas comment je pourrais faire cela en formule

donc voici, notamment en J3

=arrayformula(max(--(iferror(filter(J$2:J2;G$2:G2=G3;H$2:H2=H3);0))))+if(arrayformula(countif(H$2:H2&"~"&D$2:D2&"~"&E$2:E2;H3&"~"&D3&"~"&E3))=0;1;0)

à tirer vers le bas

dans la première partie, on cherche le n° max attribué au couple d'initiales E et F (reportées en G et H)

filter(J$2:J2;G$2:G2=G3;H$2:H2=H3)

dans la seconde partie, on cherche si on a déjà rencontré le trio de valeurs dans les lignes précédentes ou pas encore rencontré avec

countif(H$2:H2&"~"&D$2:D2&"~"&E$2:E2;H3&"~"&D3&"~"&E3)

puis en K1

={"Modèle";arrayformula(if(A2:A="";;G2:G&"-"&H2:H&"-"&J2:J))}

les formules sont sur fond jaune (sauf le 1 d'initialisation en J2)

Bonjour Steelson,

Merci pour cette réponse qui effectivement à l'air de fonctionner du feu de dieu

J'essaie de décomposer de mon côté la formule pour la comprendre, mais du coup pourquoi passer par du "arrayformula" et à quoi sert les "--" après le max ?

Deuxième question est-ce mieux de passer par cette formule ou via du App Script en terme de performance sur plusieurs centaines de lignes ?

Merci d'avance à toi

EDIT : je viens de voir que le arrayformula permet d'éviter de devoir faire descendre toute la formule sur les lignes d'en dessous, est-ce ça ?

kinders59 a écrit :

à quoi sert les "--" après le max ?

-- cela fait +, c'est juste pour passer en numérique (0 ou 1) ce qui est vrai ou faux, pas certain que ce soit utile ici mais je l'ai laissé quand même

kinders59 a écrit :

Deuxième question est-ce mieux de passer par cette formule ou via du App Script en terme de performance sur plusieurs centaines de lignes ?

par principe, tout ce que je peux faire en formule (pas trop complexe !) je le fais, idem en excel, mais avec google sheets cela prend une dimension particulière

1. d'abord parce qu'il existe des fonctions intéressantes comme filter, query, unique, etc.
2. ensuite parce que les scripts de google sont exécutés côté serveur, c'est transparent si tu as une bonne connexion internet, mais il peut y avoir de la latence)
3. et dans des cas extrêmes, ils sont limités à 6mn (j'ai rarement atteint cette limite sauf dans l'écriture de nombreux fichiers ou rendez-vous)

maintenant, ton cas est particulier car il fait appel à des calculs sur toute la plage qui précède, mais ce serait pareil avec une fonction personnalisée dans app script (reflet de ta macro VBA) ... sauf à faire un script qui n'est pas appelé ligne par ligne mais sur la totalité de la colonne en une seule fois

donc si tu as des craintes avérées de lenteur, on pourra voir cela

kinders59 a écrit :

EDIT : je viens de voir que le arrayformula permet d'éviter de devoir faire descendre toute la formule sur les lignes d'en dessous, est-ce ça ?

exact pour K1

mais pour les autres, j'ai utilisé arrayformula pour le calcul lui-même car il permettait de concaténer les données se trouvant dans les lignes précédentes, donc dans les autres cas il ne m'a pas permis de propager la formule sur toute la colonne

Steelson,

Merci de ta réponse,

Du coup il est utile de garder le "arrayformula" pour pouvoir faire glisser la formule dans les colonnes ?

De même le &"~"& la petite ~ sert à quoi ? En faisant : &" "& je vois que ça fonctionne aussi, il y a une utilité particulière au symbole ~ ?

Je suis désolé je pose peut être des questions bêtes mais je préfère comprendre toute la formule que la moitié pour éviter de revenir poser des questions bêtes une prochaine fois

Encore merci à toi pour le temps et l'aide !

toutes les questions sont bienvenues, pas d'inquiétude

kinders59 a écrit :

Du coup il est utile de garder le "arrayformula" pour pouvoir faire glisser la formule dans les colonnes ?

oui sur ce coup un peu particulier car ce arrayformula permet de concaténer sur une plage de valeurs

kinders59 a écrit :

De même le &"~"& la petite ~ sert à quoi ? En faisant : &" "& je vois que ça fonctionne aussi, il y a une utilité particulière au symbole ~ ?

j'évite l'espace qui pourrait aussi être un caractère employé dans l'une des valeurs à concaténer, mais c'est vrai que le résultat ici est le même (dans d'autres cas de formule, la concaténation peut être suivie d'un split, et là s'il y a un espace dans l'une des données concaténées c'est la catastrophe)

donc par habitude, j'utilise soit ♥ ♦ ♣ ♠ qui est assez drôle mais pas facile d'accès, soit #, soit |, mais le plus facile est encore ~ qui se trouve juste à côté de " sur le clavier

Ok super merci pour les nouvelles réponses

Dernière petite question, je vois que tu commences par un 1 et je vois au vue de la formule qu'effectivement sans ce 1 de départ tout serait décalé, penses-tu qu'il aurait été possible de faire sans forcer un premier 1 ? Simple question

Merci à toi encore !

en ligne 3, j'utilise la formule comprenant des plages extensibles comme J$2:J2

du coup, si je la copie en ligne 1 cela devenait erroné .. il fallait donc donner un point de départ à la ligne 2 qui naturellement était 1

Ok top merci beaucoup pour ton aide et cette formule qui fonctionne au poil (et toutes les explications)