Rechercher toutes les lignes d'une matrice contenant une partie de chaîne

Version imprimable

19/05/2017, 23h42
sibama

Rechercher toutes les lignes d'une matrice contenant une partie de chaîne

Bonjour,

Je cherche à trouver une formule (à priori matricielle) qui me permette de récupérer l'ensemble des lignes qui contienne une partie de chaine.

Je m'explique:
en B5 j'ai "Ceci est le contenu n°1"
en B6 j'ai "La un autre texte n°1"
en B7 j'ai "Et encore un différent n°1"
en B8 j'ai "Ceci est le contenu n°2"
en B9 j'ai "Vraiment plein de textes différent n°1"
en B10 j'ai "Bon on va arrêter là les textes différents n°1"
en B11 j'ai "Ceci est le contenu n°3"

Avec une formule du genre

Code:

=SI(STXT(B5;1;22)="Ceci est le contenu n°";LIGNE(B5);"")

en D5 étendue à D11, j'obtient le n° de ligne qui contient la partie de texte recherchée sur la ligne concernée.

Ensuite je peut créer un filtrage sur la colonne D, ne pas afficher les cellules vides et recopier la plage qui contient des données en D sur une autre fauille pour ce que j'ai à faire sur ces lignes, mais ça sent le bricolage et je ne suis pas super satisfait de cette solution (ça sent la frustration due à un bricolage).

Ce que je voudrais c'est avoir en D5: 5 (parce que B5 contient le texte), en D6: 8 (parce que B8 contient le texte) et en D7: 11 (parce que B11 contient le texte).

J'ai donc essayé (en entrant de manière matricielle Ctrl+Maj+Entrée) la formule suivante en D5

Code:

=SI(STXT(INDIRECT("B5" & ":B"& EQUIV("ZZZ";B:B));1;22)="Ceci est le contenu n°";LIGNE(INDIRECT("B5" & ":B"& EQUIV("ZZZ";B:B)));"")

Ça me trouve bien la première occurrence dans la matrice, mais, malgré de nombreux exemples sur le net, je ne vois pas comment faire pour que la formule en D6 cherche l’occurrence suivante à celle trouvée en D5.....

Si quelqu'un peut me donner un petit coup de main, ce serait sympa.
20/05/2017, 05h53
njhub

numérotage des lignes contenant une partie de chaine

Bonjour, essayez:

$D$3 valeur cherchée

en C5

Code:

=SI(ESTERREUR(CHERCHE($D$3;B5));"";LIGNE())

en D5

Code:

=SI(ESTERREUR(PETITE.VALEUR($C$5:$C$11;1));"";PETITE.VALEUR($C$5:$C$11;1))
20/05/2017, 19h11
papouclo

Bonjour,
En plaçant en D3 la valeur cherchée comme le propose njhub, en utilisant des idées prises dans les formules déjà proposées et à condition qu'il n'y ait pas de donnée numérique dans la plage D1: D4, écrire en D5 la formule matricielle :

Code:

=SIERREUR(PETITE.VALEUR(SI(ESTERREUR(CHERCHE($D$3;INDIRECT("B5" & ":B"& EQUIV("ZZZ";B:B))));"";LIGNE(INDIRECT("B5" & ":B"& EQUIV("ZZZ";B:B))));NB($D$1:D4)+1);"")

et la recopier vers le bas en colonne D.
Cordialement
Claude
21/05/2017, 21h09
sibama

Super,

ça marche nickel, pour une formule en E5 et pour rechercher " | COMBUSTIBLE |) j'ai mis:

Code:

=SIERREUR(PETITE.VALEUR(SI(ESTERREUR(CHERCHE(" | COMBUSTIBLE |";INDIRECT("B5" & ":B"& EQUIV("ZZZ";B:B))));"";LIGNE(INDIRECT("B5" & ":B"& EQUIV("ZZZ";B:B))));NB($E$1:E4)+1);"")

Bon ça rame un peu (même sur ma bête de course), mais c'est du pur bonheur.

Merci à vous deux pour le coup de main.
23/05/2017, 00h02
sibama

Un dernier ajout pour ceux qui liraient un jour cette discussion:

Donc ma formule définitive est :

Code:

=SIERREUR(PETITE.VALEUR(SI(ESTERREUR(CHERCHE("| COMBUSTIBLE |";INDIRECT("B5:B"& EQUIV("zzz";$B:$B))));"";LIGNE(INDIRECT("B5:B"& EQUIV("zzz";$B:$B))));LIGNE(E5)-4);"Y en a plus")

Le fonctionnement en est le suivant:
- Le EQUIV("zzz";$B:$B) permet de rechercher la dernière ligne avec une chaîne de caractères dans la colonne B (si la colonne contenait des valeurs numériques, il faudrait remplacer le "zzz" par un truc qu'on ne trouve pas du genre 9^99). A noter que j'utilise ça car le nombre de lignes dans ma colonne B peut être amené à évoluer (en fonction de mon extraction de données) et que cette formule évite d'avoir à aller rechercher la dernière ligne et à la remplacer dans toutes les formules.

- Le INDIRECT("B5:B"& EQUIV("zzz";$B:$B)) me permet donc de définir la matrice dans laquelle la recherche va s'effectuer à savoir B5:B11 si je prends l'exemple suivant:
en B5 j'ai " | COMBUSTIBLE | n°1"
en B6 j'ai "La un autre texte n°1"
en B7 j'ai "Et encore un différent n°1"
en B8 j'ai " | COMBUSTIBLE | n°2"
en B9 j'ai "Vraiment plein de textes différent n°1"
en B10 j'ai "Bon on va arrêter là les textes différents n°1"
en B11 j'ai " | COMBUSTIBLE | n°3"

- Le fait d'entrer la formule en formule matricielle (Ctrl+Maj+Entrée) fait que la fonction CHERCHE va s'effectuer sur chacune des lignes de la matrice B5:B11. Ainsi, dans mon exemple, le résultat de CHERCHE sera de la forme { 4;#VALEUR;#VALEUR;4;#VALEUR;#VALEUR;4 }:
4 quand la ligne contient la chaîne | COMBUSTIBLE | dans mon exemple toujours précédée de 4 espaces
#VALEUR quand la ligne ne contient pas la chaîne recherchée
B5 à B11 -> 7 ligne, la matrice résultat contient 7 valeurs.

- Ensuite le SI(ESTERREUR(CHERCHE("| COMBUSTIBLE |";INDIRECT("B5:B"& EQUIV("zzz";$B:$B))));"";LIGNE(INDIRECT("B5:B"& EQUIV("zzz";$B:$B)))) que l'on peut traduire par SI(ESTERREUR({ 4;#VALEUR;#VALEUR;4;#VALEUR;#VALEUR;4 });"";LIGNE(B5:B11)) (rentré de façon matricielle) va nous donner un résultat intermédiaire de la forme SI(ESTERREUR({ FAUX;VRAI;VRAI;FAUX;VRAI;VRAI;FAUX });"";LIGNE(B5:B11)): les indices contenant une valeur trouvée (4) ne sont pas une erreur et les indices contenant #VALEUR sont une erreur.
Le SI par dessus tout ça va faire que quand on a FAUX on va afficher le numéro de la ligne concernée "LIGNE(B5:B11)" et "" quand c'est VRAI, ce bloc donne donc un résultat de la forme { 5;"";"";8;"";"";11 }

- Après on passe au PETITE.VALEUR({ 5;"";"";8;"";"";11 };LIGNE(E5)-4):
Le LIGNE(E5)-4 fait que pour la formule en E5 on va avoir 1 puis la LIGNE(E6)-4 fait que pour la formule en E6 on va avoir 2 et ainsi de suite l'indice derrière le E s'incrémentant automatiquement quand on étant la formule en E6/E7/E8/etc..
Le PETITE.VALEUR recherche dans la matrice { 5;"";"";8;"";"";11 } le Nieme plus petit élément définit par la valeur après le ;
Donc en E5 on cherche le premier (LIGNE(E5)-4 = 1) plus petit élément numérique dans { 5;"";"";8;"";"";11 }, on a donc E5 = 5
En E6 on cherche le second (LIGNE(E6)-4 = 2) plus petit élément numérique dans { 5;"";"";8;"";"";11 }, on a donc E6 = 8
En E7 on cherche le troisième (LIGNE(E7)-4 = 3) plus petit élément numérique dans { 5;"";"";8;"";"";11 }, on a donc E7 = 11
En E8 on cherche le quatrième (LIGNE(E8)-4 = 4) plus petit élément numérique dans { 5;"";"";8;"";"";11 }, on a donc E8 = #NOMBRE!, ben oui, il n'y a pas de quatrième valeur numérique dans la matrice, et idem en E9, E10 etc...

- Du coup le dernier SIERREUR( ;"Y en a plus") qui est facultatif fait que pour les Exx où on affiche #NOMBRE! on va avoir un joli "Y en a plus" qui montre qu'on maîtrise parfaitement la formule rentrée.

Voilà j'espère que cette explication permettra à quelqu'un de pouvoir un jour réutiliser la formule donnée par njhub et papouclo voir à l'adapter en fonction de besoins un peu différents.
Moi ça m'a fait un peu réfléchir à ce que je saisissais dans EXCEL et à trouver que le matriciel c'est quand même super puissant si on le comprend à peu prêt.