XL 2019 Auto compléter une cellule en fonction d'une liste

[Angelzeus]

Bonjour,

Tout d'abord je voulais vous dire un grand merci pour votre site, il est vraiment top.
J'aurais besoin d'aide pour une formule impliquant le NB.SI
Ma formule fonctionne mais est limité à 64 imbrications et j'aimerais pourvoir en faire bien plus.

Je m'explique, j'ai un tableau qui récapitule mon relevé de compte dans lequel il y a 2 colonnes (A "Lib" et B "Catégorie"). Je voudrais que la colonne B se remplisse automatiquement en fonction d'une liste qui se trouve sur une autre feuille nommée "List".

Exemple de la liste:

Colonne A	Colonne B
*8 A HUIT*	Alimentation
*ALIM GENERAL*	Alimentation
*ALIMENT*	Alimentation
*AMAZON*	Divers
*ATELIER DU SOUR*	Divers
*BC 2 PANTIN*	Alimentation
*BIG FERNAND*	Alimentation
*BURGER KING*	Alimentation
*CAMAIEU*	Habits

IMPORTANT: J'ai mis les textes entre astérisques afin qu'il recherche cette chaine de caractère.

Dans le tableau principale, la colonne Lib ressemble à ça:

Colonne A "Lib"	Colonne B "Catégorie"
CB AMAZON PAYMENTS 31/03/21	Divers
CB E.LECLERC 29/03/21	Alimentation
CB LIDL 3393 29/03/21	Alimentation
CB SC.ALIM GENERAL 29/03/21	Alimentation
CB APPLE.COM/BILL 26/03/21	Jeux

Par exemple sur la première ligne, il doit identifier "Amazon" puisque cette chaine de caractère se trouve bien dans la liste et doit donc automatiquement compléter la colonne Catégorie avec "Divers".

Voici la formule que j'utilise et que j'écris dans les cellules de la colonne B "Catégorie" du tableau principale :

# L: La feuille Liste
# @lib: La colonne Lib du tableau principale

=(SI(NB.SI([@Lib];'L'!$A$1);'L'!$B$1;SI(NB.SI([@Lib];'L'!$A$2);'L'!$B$2;SI(NB.SI([@Lib];'L'!$A$3);'L'!$B$3;SI(NB.SI([@Lib];'L'!$A$4);'L'!$B$4;SI(NB.SI([@Lib];'L'!$A$5);'L'!$B$5;SI(NB.SI([@Lib];'L'!$A$6);'L'!$B$6;SI(NB.SI([@Lib];'L'!$A$7);'L'!$B$7;SI(NB.SI([@Lib];'L'!$A$8);'L'!$B$8;SI(NB.SI([@Lib];'L'!$A$9);'L'!$B$9;SI(NB.SI([@Lib];'L'!$A$10);'L'!$B$10;SI(NB.SI([@Lib];'L'!$A$11);'L'!$B$11;SI(NB.SI([@Lib];'L'!$A$12);'L'!$B$12;SI(NB.SI([@Lib];'L'!$A$13);'L'!$B$13;SI(NB.SI([@Lib];'L'!$A$57);'L'!$B$57;SI(NB.SI([@Lib];'L'!#REF!);'L'!#REF!;SI(NB.SI([@Lib];'L'!$A$14);'L'!$B$14;SI(NB.SI([@Lib];'L'!$A$15);'L'!$B$15;SI(NB.SI([@Lib];'L'!$A$16);'L'!$B$16;SI(NB.SI([@Lib];'L'!$A$17);'L'!$B$17;SI(NB.SI([@Lib];'L'!$A$18);'L'!$B$18;SI(NB.SI([@Lib];'L'!$A$19);'L'!$B$19;SI(NB.SI([@Lib];'L'!$A$20);'L'!$B$20;SI(NB.SI([@Lib];'L'!$A$21);'L'!$B$21;SI(NB.SI([@Lib];'L'!$A$22);'L'!$B$22;SI(NB.SI([@Lib];'L'!$A$23);'L'!$B$23;SI(NB.SI([@Lib];'L'!$A$24);'L'!$B$24;SI(NB.SI([@Lib];'L'!$A$25);'L'!$B$25;SI(NB.SI([@Lib];'L'!$A$26);'L'!$B$26;SI(NB.SI([@Lib];'L'!$A$27);'L'!$B$27;SI(NB.SI([@Lib];'L'!$A$28);'L'!$B$28;SI(NB.SI([@Lib];'L'!$A$29);'L'!$B$29;SI(NB.SI([@Lib];'L'!$A$30);'L'!$B$30;SI(NB.SI([@Lib];'L'!$A$31);'L'!$B$31;SI(NB.SI([@Lib];'L'!$A$32);'L'!$B$32;SI(NB.SI([@Lib];'L'!$A$33);'L'!$B$33;SI(NB.SI([@Lib];'L'!$A$34);'L'!$B$34;SI(NB.SI([@Lib];'L'!$A$35);'L'!$B$35;SI(NB.SI([@Lib];'L'!$A$36);'L'!$B$36;SI(NB.SI([@Lib];'L'!$A$37);'L'!$B$37;SI(NB.SI([@Lib];'L'!$A$38);'L'!$B$38;SI(NB.SI([@Lib];'L'!$A$39);'L'!$B$39;SI(NB.SI([@Lib];'L'!$A$40);'L'!$B$40;SI(NB.SI([@Lib];'L'!$A$41);'L'!$B$41;SI(NB.SI([@Lib];'L'!$A$42);'L'!$B$42;SI(NB.SI([@Lib];'L'!$A$43);'L'!$B$43;SI(NB.SI([@Lib];'L'!$A$44);'L'!$B$44;SI(NB.SI([@Lib];'L'!$A$45);'L'!$B$45;SI(NB.SI([@Lib];'L'!$A$46);'L'!$B$46;SI(NB.SI([@Lib];'L'!$A$47);'L'!$B$47;SI(NB.SI([@Lib];'L'!$A$48);'L'!$B$48;SI(NB.SI([@Lib];'L'!$A$49);'L'!$B$49;SI(NB.SI([@Lib];'L'!$A$50);'L'!$B$50;SI(NB.SI([@Lib];'L'!$A$51);'L'!$B$51;SI(NB.SI([@Lib];'L'!$A$52);'L'!$B$52;SI(NB.SI([@Lib];'L'!$A$53);'L'!$B$53;SI(NB.SI([@Lib];'L'!$B$54);'L'!$B$54;SI(NB.SI([@Lib];'L'!$B$55);'L'!$B$55;SI(NB.SI([@Lib];'L'!$A$56);'L'!$B$56;SI(NB.SI([@Lib];'L'!$A$57);'L'!$B$57;SI(NB.SI([@Lib];'L'!$A$58);'L'!$B$58;SI(NB.SI([@Lib];'L'!$A$59);'L'!$B$59;SI(NB.SI([@Lib];'L'!$A$60);'L'!$B$60;SI(NB.SI([@Lib];'L'!$A$61);'L'!$B$61;SI(NB.SI([@Lib];'L'!$A$62);'L'!$B$62;"")))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))

Le problème est que je suis limité à 64 imbrications avec la formule SI alors qu'il m'en faudrait 200 au moins.

Si vous pouvez m'aider, ça serait super sympa. J'ai essayé avec la formule RECHERCHEV, XLOOKUP ou même DECALER mais sans succès. Je ne suis pas un expert du tout.

Merci à vous.

 

[soan]

Bonsoir Angelzeus, job75, le fil,

je propose :

ActiveSheet.ListObjects("Table1").AutoFilter.ShowAllData

soan

 

[Angelzeus]

Bonsoir Angelzeus, le fil,

Il serait quand même sympathique de votre part de nous dire ce que donne la solution par formule de mon post #7 avec votre liste de 580 lignes.

D'autant plus qu'avec cette solution les filtres ne posent pas de problème.

Bonne nuit.

Bonsoir, désolé c'est vrai que je me suis emballé avec le script en VB. Du coup je viens de tester ta solution. Elle fonctionne aussi !! Voici quelques points de comparaison entre la formule et le code en VB :

1- Il faut définir un nombre de ligne qu'il va parcourir. Dans mon test, j'ai mis 1000 lignes puisque j'en ai en réalité 478 pour le moment. Mais à terme ce chiffre augmentera et donc il faudra mettre à jour la formule.

=JOINDRE.TEXTE(" ; ";VRAI;SI(NB.SI([@Libellé];Liste!$A$1:$A$1000);Liste!B$1:B$1000;""))


2- Au niveau perf, c'est plus lent que le code en VB qui lui est quasiment instantané. Comme j'ai un tableau de 3500 lignes pour le moment, Il met bien 10 secondes avant de tout remplir. Après c'est pas bien grave puisqu'une fois qu'il est rempli, ça ne bougera plus. Je rajouterais de nouvelles lignes maximum une fois par mois quand mon relevé de compte est dispo en PDF.

3- J'ai eu des lignes avec des erreurs surement dû au fait qu'il doit y avoir des doublons dans la liste (mais je n'ai pas eu ces erreurs avec le VB).

PRLV SEPA EI TELECOM	CB ; Prélèvement	Téléphone ; Téléphone	NRJ Mobile ; NRJ Mobile
VIREMENT CPAM	Virement ; Virement	Sécurité Sociale ; Santé	0 ; CPAM (Sécurité Sociale)

4- Quand la cellule est vide au niveau de la liste, il affiche des 0 dans le tableau principale. Cela se corrige facilement en ajoutant le ' dans la cellule vide de la liste.

Le gros avantage est que je peux si je veux remplir des cellules dans le tableau principale à la main. Avec le VB, ça efface tout.
C'est un bel avantage parce que j'ai des lignes que je veux absolument remplir à la main puisqu'ils ont le même libellé mais la catégorie doit changer dû à des montants différents.


Du coup, je vais voir pour corriger le problème n°3, mais effectivement cette solution me parait mieux adapter pour mon cas pour le moment.

Merci à toi en tout cas pour cette solution. Je ne connaissais pas du tout la fonction JOINDRE.TEXTE.

Edit 2: Je viens de corriger tous mes doublons, effectivement j'en avais une bonne dizaine. Du coup tout est parfait avec cette solution.

 

[sylvanu]

Bonjour Angelzeus,Job,
En réponse au post #11, pour supprimer les filtres, ajoutez la ligne :

ActiveSheet.ShowAllData

 

[job75]

Bonjour Angelzeus, le forum,

Pour afficher le texte vide "" à la place du zéro il suffit d'ajouter ""&, en B2 :

=JOINDRE.TEXTE(" ; ";VRAI;SI(NB.SI([@Libellé];Liste!$A$1:$A$100);""&Liste!D$1:D$100;""))

L'intérêt d'utiliser JOINDRE.TEXTE c'est que tous les textes trouvés sont concaténés.

Si JOINDRE.TEXTE n'est pas disponible on peut se contenter d'afficher seulement la 1ère occurence, c'est ce que fait la macro de sylvanu.

Voyez alors ce fichier (2) et la formule matricielle en B2 :

=SIERREUR(INDEX(Liste!D$1:D$100;""&PETITE.VALEUR(SI(NB.SI([@Libellé];Liste!$A$1:$A$100);LIGNE(Liste!$A$1:$A$100));1));"")

Je pense que ce n'est guère plus rapide mais testez quand même.

A+

 

[sylvanu]

Bonsoir @Angelzeus, job75, Soan, le fil,

Le gros avantage est que je peux si je veux remplir des cellules dans le tableau principale à la main. Avec le VB, ça efface tout.

Le VB ne fait que ce qu'on lui demande de faire, et ce point n'était pas dans l'hypothèse de départ.
En PJ une solution en VBA, il suffit, quand on insère une donnée de la faire précéder par un espace.
ainsi quand une donnée est manuelle elle n'est pas touchée, la seule règle est de ne pas commencer une "chaine manuelle" par un espace.
Par contre sur un nouveau fichier, il faut initialisé les colonnes en supprimant les données pour que le VBA insère correctement les espaces, sinon les anciennes données n'ayant pas d'espace ne seront pas touchées.

Après c'est pas bien grave puisqu'une fois qu'il est rempli, ça ne bougera plus.

A vérifier, mais même si les données ne bougent pas, les formules sont ré évaluées à chaque modification de cellule sauf si on est en calcul manuel.

 

[patricktoulon]

Bonjour Angelzeus, le forum,


Si JOINDRE.TEXTE n'est pas disponible on peut se contenter d'afficher seulement la 1ère occurence, c'est ce que fait la macro de sylvanu.

Bonjour @job75

Texte - JOINDRE_TEXTE2007

Bonjour à tous comme personne ne pense au pauvres utilisateur qui n'ont pas 365 ou 2016 je me lance une petite fonction similaire a joindre.texte mais qui fonctionne pour tout le monde fonctionne pour vba et en formule '******************************************** 'fonction JOINDRE_TEXTE pour...

www.excel-downloads.com

 

[sylvanu]

Comme quoi l'idée de David s'avère utile.

 

[Angelzeus]

Bonsoir @Angelzeus, job75, Soan, le fil,

Le VB ne fait que ce qu'on lui demande de faire, et ce point n'était pas dans l'hypothèse de départ.
En PJ une solution en VBA, il suffit, quand on insère une donnée de la faire précéder par un espace.
ainsi quand une donnée est manuelle elle n'est pas touchée, la seule règle est de ne pas commencer une "chaine manuelle" par un espace.
Par contre sur un nouveau fichier, il faut initialisé les colonnes en supprimant les données pour que le VBA insère correctement les espaces, sinon les anciennes données n'ayant pas d'espace ne seront pas touchées.

A vérifier, mais même si les données ne bougent pas, les formules sont ré évaluées à chaque modification de cellule sauf si on est en calcul manuel.

Je viens de tester cette version, elle est bien mais comporte un problème que je n'avais pas forcément parlé jusqu'à présent. J'utilise un tableau croisé dynamique pour pouvoir lire facilement ces données à la fin.

Avec cette version, comme il y a un espace qui se rajoute devant chaque cellule pour la catégorisation automatique via le VBA, cela me créer deux lignes sur mon tableau croisé dynamique par rapport aux cellules écrites manuellement.

Exemple pour la sous catégorie internet qui est dans la catégorie Divers, ce qui fait que maintenant j'ai une ligne "Divers" et une autre " Divers", ce qui n'est pas bien pour mon tableau.

Pour corriger cela, en fait il faudrait plutôt faire en sorte qu'il rajoute deux espaces à la fin de la cellule au lieux de commencer par un espace.

Merci,
Matthieu.

 

[Angelzeus]

Bonjour Angelzeus, le forum,

Pour afficher le texte vide "" à la place du zéro il suffit d'ajouter ""&, en B2 :

=JOINDRE.TEXTE(" ; ";VRAI;SI(NB.SI([@Libellé];Liste!$A$1:$A$100);""&Liste!D$1:D$100;""))

L'intérêt d'utiliser JOINDRE.TEXTE c'est que tous les textes trouvés sont concaténés.

Si JOINDRE.TEXTE n'est pas disponible on peut se contenter d'afficher seulement la 1ère occurence, c'est ce que fait la macro de sylvanu.

Voyez alors ce fichier (2) et la formule matricielle en B2 :

=SIERREUR(INDEX(Liste!D$1:D$100;""&PETITE.VALEUR(SI(NB.SI([@Libellé];Liste!$A$1:$A$100);LIGNE(Liste!$A$1:$A$100));1));"")

Je pense que ce n'est guère plus rapide mais testez quand même.

A+

Salut,

Je viens de tester et je préfère rester avec la première fonction, soit JOINDRE.TEXTE, elle est plus simple à lire.
Pour l'instant j'hésite encore avec la solution en VBA qui est aussi pas mal du tout et qui n'a pas ce problème de performance puisqu'il ne recalcule pas en permanence contrairement aux formules.

La fonction JOINDRE.TEXTE ma quand même permit de voir que j'avais quelques doublon à corriger

Après la macro de Sylvain pourrait surement faire la même chose si je l'avais demandé

Merci,
Matthieu.

 

[sylvanu]

Bonjour Angelzeus, Job,
En PJ c'est rectifié. Les chaines se terminent par un double espace.
Evidemment dans le fichier final il faut effacer toutes les données pour ré initialiser correctement le tableau.

 

[Angelzeus]

Bonjour Angelzeus, Job,
En PJ c'est rectifié. Les chaines se terminent par un double espace.
Evidemment dans le fichier final il faut effacer toutes les données pour ré initialiser correctement le tableau.

C'est parfait comme ça.
Merci à toi.

Matthieu.