RÉSOLU - Afficher un texte dans une cellule en fonction d'un mot-clé

[kalem]

Bonjour,
A nouveau besoin de vos lumières !
Je tape des appréciations pour des élèves. Je souhaiterais que lorsque j'indique par exemple, "intro à revoir", Excel détecte le mot clé "intro" et indique dans une zone "conseils" un rappel du cours sur la méthode de l'introduction.
J'ai donc ma feuille "Relevé" qui contient les appréciations, et ma feuille "Conseils", avec en A, les mots-clés, et en B, le conseil lui-même.
J'ai essayé ceci : =SI(NB.SI(B5:G19;"*Conseils!A:A*");"B:B";""), mais ça ne fonctionne pas, j'ai dû faire erreur.

De toute façon, je crois par ailleurs qu'il faut passer par une macro pour qu'Excel revienne à la ligne et indique un nouveau conseil dès qu'un mot clé est détecté.

Si vous avez une idée... je suis preneur ! Merci d'avance.

 

[job75]

Bonjour kalem,

Formule matricielle en Relevé!B24 :

=INDEX(Conseils!B$1:B$5;PETITE.VALEUR(SI(NB.SI(B$5:G$19;"*"&Conseils!A$1:A$5&"*");LIGNE(A$1:A$5));LIGNES(B$24:B24)))

A valider Par Ctrl+Maj+Entrée et tirer vers le bas.

A partir d'Excel 2007, pour masquer les valeurs d'erreur :

=SIERREUR(INDEX(Conseils!B$1:B$5;PETITE.VALEUR(SI(NB.SI(B$5:G$19;"*"&Conseils!A$1:A$5&"*");LIGNE(A$1:A$5));LIGNES(B$24:B24)));"")

A+

 

[kalem]

Je n'ai pas compris grand chose mais ça marche ! Je ne connaissais même pas le principe des formules matricielles... Merci.
Seul souci : les conseils n'apparaissent pas les uns sous les autres mais à leur place respective dans la feuille "Conseils", autrement dit si ce sont par exemple les conseils 2 et 4 qui doivent apparaître, ils apparaissent mais avec des cellules vides entre les deux... Je vais réfléchir. Mais s'il n'y a pas possibilité de les mettre à la suite, tant pis, c'est déjà très bien ainsi.

 

[job75]

Re,

Bah dans la feuille "Relevé" les Conseils se suivent (grâce à PETITE.VALEUR)...

 

[kalem]

Je ne suis pas sûr d'avoir bien compris, mais dans la feuille "Relevé", les conseils suivent le rang qu'ils occupent dans la feuille conseils. Il y a donc bien, parfois, des cellules vides. Mais encore une fois, ce n'est qu'un détail purement esthétique. L'objectif est tout à fait atteint, encore bravo.

 

[job75]

Bonjour kalem, le forum,

Il y a donc bien, parfois, des cellules vides.

Pas du tout mais bien sûr les "Conseils" ne doivent pas être vides (c'était le cas pour "Alinéas").

Fichier joint puisque vous avez du mal à tester.

A+

 

[kalem]

Je ne suis pas sûr d'avoir bien compris, mais dans la feuille "Relevé", les conseils suivent le rang qu'ils occupent dans la feuille conseils. Il y a donc bien, parfois, des cellules vides. Mais encore une fois, ce n'est qu'un détail purement esthétique. L'objectif est tout à fait atteint, encore bravo.

 

[kalem]

Bon sang, je suis absolument le roi des boulets.... Merci, vraiment, pour votre patience... ça fonctionne en effet parfaitement. Bravo !!!!

 

[kalem]

Bonsoir,
Me revoici ! Comme j'avance un peu sur mon fichier, je constate qu'excel ne propose que les 5 premiers conseils...
Je me dis que dans cette formule :

Code (Text):
=SIERREUR(INDEX(Conseils!B$1:B$5;PETITE.VALEUR(SI(NB.SI(B$5:G$19;"*"&Conseils!A$1:A$5&"*");LIGNE(A$1:A$5));LIGNES(B$24:B24)));"")

Il faut modifier certaines cellules:
=SIERREUR(INDEX(Conseils!B$1:B$10;PETITE.VALEUR(SI(NB.SI(B$5:G$19;"*"&Conseils!A$1:A$10&"*");LIGNE(A$1:A$5));LIGNES(B$24:B24)));"")

Ceci afin de prendre en compte davantage de conseils, mais visiblement, jouer au petit bricoleur sur excel n'est pas à la portée de tout le monde... Si quelqu'un a une piste.... Merci d'avance.

 

[job75]

Bonjour kalem,

Il faut que les plages concernées par le calcul matriciel aient la même dimension.

=SIERREUR(INDEX(Conseils!B$1:B$10;PETITE.VALEUR(SI(NB.SI(B$5:G$19;"*"&Conseils!A$1:A$10&"*");LIGNE(A$1:A$10));LIGNES(B$24:B24)));"")

Vous comprendrez peut-être mieux en écrivant ainsi :

=SIERREUR(INDEX(Conseils!B:B;PETITE.VALEUR(SI(NB.SI(B$5:G$19;"*"&Conseils!A$1:A$10&"*");LIGNE(Conseils!A$1:A$10));LIGNES(B$24:B24)));"")

A+

 

[kalem]

Merci !! Super !! ça fonctionne parfaitement !
Ultime question (mais c'est vraiment facultatif) : est-ce possible de faire en sorte que plusieurs mots-clés permettent d'afficher le même conseil sans doublon ? Si une appréciation signale le mot "court", une autre "superficiel", je voudrais qu'un conseil pour approfondir s'affiche, mais pas en deux fois.
Encore bravo et merci job75.

 

[job75]

Bonjour kalem, le forum,

Ultime question (mais c'est vraiment facultatif) : est-ce possible de faire en sorte que plusieurs mots-clés permettent d'afficher le même conseil sans doublon ? Si une appréciation signale le mot "court", une autre "superficiel", je voudrais qu'un conseil pour approfondir s'affiche, mais pas en deux fois.

Voyez ce fichier (2) et les formules en A24 (matricielle) B24 (matricielle) et C24 (fusionnée).

Rappel : une cellule fusionnée ne peut pas contenir une formule matricielle.

Ces 3 formules sont tirées vers le bas sur autant de lignes (9) qu'il y en a dans le tableau de la feuille "Conseils".

Les colonnes auxiliaires A et B sont bien sûr à masquer.

Il y a une MFC sur la plage C24:H32 pour la couleur et les bordures.

Bonne journée.

 

[job75]

Re,

Si l'on veut pouvoir agrandir le tableau de la feuille "Conseils" sans avoir à modifier à chaque fois les formules et le tableau des résultats, il faut se donner une marge.

Par exemple jusqu'à la ligne 20 (19 lignes).

Il suffit pour cela d'ajouter ""& dans les formules en A24 et B24 pour éviter les valeurs zéro.

Fichier (3).

A+

 

[kalem]

Ah, c'est un peu complexe, car ce volet "conseils" s'insère dans un fichier plus complexe. Je vais voir comment je peux faire pour intégrer tout ça. Je n'aurai donc pas réussi à vous poser une colle, décidément !
C'est super en tout cas, merci beaucoup.

 

[job75]

Re,

On peut simplifier les choses en utilisant une fonction VBA, à placer dans un module standard (Alt+F11) :

Function Conseils(r1 As Range, r2 As Range)
Dim t, d As Object, i&
t = r1.Resize(Application.CountA(r1.Columns(1))) 'matrice, plus rapide
Set d = CreateObject("Scripting.Dictionary")
For i = 2 To UBound(t)
  If Application.CountIf(r2, "*" & t(i, 1) & "*") Then d(t(i, 2)) = ""
Next
Conseils = d.keys 'vecteur horizontal
End Function

La formule en A24 est simple :

=SIERREUR(INDEX(Conseils(Conseils!A:B;A$5:G$19);LIGNES(A$24:A24));"")

Il faut la tirer vers le bas jusqu'à l'obtention d'une cellule "vide", la MFC suit.

Fichier .xlsm joint.

A+