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

Angelzeus

XLDnaute Nouveau
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/21Divers
CB E.LECLERC 29/03/21Alimentation
CB LIDL 3393 29/03/21Alimentation
CB SC.ALIM GENERAL 29/03/21Alimentation
CB APPLE.COM/BILL 26/03/21Jeux

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.
 
Dernière édition:
Solution
Bonjour Angelzeus, JHA, sylvanu,

J'ai supprimé mon message initial.

Sur Excel 2019 la fonction JOINDRE.TEXTE existe.

Donc voyez le fichier joint et cette formule matricielle en colonne B de la 2ème feuille :
Code:
=JOINDRE.TEXTE(" ; ";VRAI;SI(NB.SI([@Libellé];Liste!$A$1:$A$100);Liste!D$1:D$100;""))
à valider par Ctrl+Maj+Entrée.

CB est en doublon parce qu'il y a une rubrique CB* en colonne A de la 1ère feuille.

A+

Angelzeus

XLDnaute Nouveau
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 TELECOMCB ; PrélèvementTéléphone ; TéléphoneNRJ Mobile ; NRJ Mobile
VIREMENT CPAMVirement ; VirementSé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.
 
Dernière édition:

job75

XLDnaute Barbatruc
Bonjour Angelzeus, le forum,

Pour afficher le texte vide "" à la place du zéro il suffit d'ajouter ""&, en B2 :
Code:
=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 :
Code:
=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+
 

Pièces jointes

  • Exemple(2).xlsm
    18.8 KB · Affichages: 12

sylvanu

XLDnaute Barbatruc
Supporter XLD
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.
 

Pièces jointes

  • Exemple V5.xlsm
    26.9 KB · Affichages: 7

patricktoulon

XLDnaute Barbatruc
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
 

Angelzeus

XLDnaute Nouveau
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.
 
Dernière édition:

Angelzeus

XLDnaute Nouveau
Bonjour Angelzeus, le forum,

Pour afficher le texte vide "" à la place du zéro il suffit d'ajouter ""&, en B2 :
Code:
=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 :
Code:
=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.
 

Discussions similaires

Réponses
8
Affichages
364