1. #1
    Membre du Club Avatar de sibama
    Profil pro
    Inscrit en
    août 2008
    Messages
    113
    Détails du profil
    Informations personnelles :
    Âge : 46
    Localisation : France

    Informations forums :
    Inscription : août 2008
    Messages : 113
    Points : 44
    Points
    44

    Par défaut 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 : Sélectionner tout - Visualiser dans une fenêtre à part
    =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 : Sélectionner tout - Visualiser dans une fenêtre à part
    =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.
    Ce n'est pas parce que l'on allume la bougie de quelqu'un avec sa lanterne que l'on se prive de lumière.

  2. #2
    Membre habitué
    Homme Profil pro
    utilisateur
    Inscrit en
    janvier 2017
    Messages
    105
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Âge : 56
    Localisation : France

    Informations professionnelles :
    Activité : utilisateur
    Secteur : Service public

    Informations forums :
    Inscription : janvier 2017
    Messages : 105
    Points : 182
    Points
    182

    Par défaut numérotage des lignes contenant une partie de chaine

    Bonjour, essayez:

    $D$3 valeur cherchée

    en C5
    Code : Sélectionner tout - Visualiser dans une fenêtre à part
    =SI(ESTERREUR(CHERCHE($D$3;B5));"";LIGNE())
    en D5
    Code : Sélectionner tout - Visualiser dans une fenêtre à part
    =SI(ESTERREUR(PETITE.VALEUR($C$5:$C$11;1));"";PETITE.VALEUR($C$5:$C$11;1))

  3. #3
    Membre expert
    Homme Profil pro
    Retraité
    Inscrit en
    juin 2012
    Messages
    1 357
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Localisation : France, Alpes Maritimes (Provence Alpes Côte d'Azur)

    Informations professionnelles :
    Activité : Retraité
    Secteur : Enseignement

    Informations forums :
    Inscription : juin 2012
    Messages : 1 357
    Points : 3 029
    Points
    3 029

    Par défaut

    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 : Sélectionner tout - Visualiser dans une fenêtre à part
    =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

  4. #4
    Membre du Club Avatar de sibama
    Profil pro
    Inscrit en
    août 2008
    Messages
    113
    Détails du profil
    Informations personnelles :
    Âge : 46
    Localisation : France

    Informations forums :
    Inscription : août 2008
    Messages : 113
    Points : 44
    Points
    44

    Par défaut

    Super,

    ça marche nickel, pour une formule en E5 et pour rechercher " | COMBUSTIBLE |) j'ai mis:
    Code : Sélectionner tout - Visualiser dans une fenêtre à part
    =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.
    Ce n'est pas parce que l'on allume la bougie de quelqu'un avec sa lanterne que l'on se prive de lumière.

  5. #5
    Membre du Club Avatar de sibama
    Profil pro
    Inscrit en
    août 2008
    Messages
    113
    Détails du profil
    Informations personnelles :
    Âge : 46
    Localisation : France

    Informations forums :
    Inscription : août 2008
    Messages : 113
    Points : 44
    Points
    44

    Par défaut

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

    Donc ma formule définitive est :
    Code : Sélectionner tout - Visualiser dans une fenêtre à part
    =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.
    Ce n'est pas parce que l'on allume la bougie de quelqu'un avec sa lanterne que l'on se prive de lumière.

+ Répondre à la discussion
Cette discussion est résolue.

Discussions similaires

  1. Réponses: 6
    Dernier message: 21/06/2017, 16h57
  2. Réponses: 0
    Dernier message: 10/10/2013, 12h29
  3. Réponses: 4
    Dernier message: 24/01/2013, 14h57
  4. Réponses: 22
    Dernier message: 28/10/2010, 17h26
  5. Réponses: 4
    Dernier message: 18/10/2007, 15h09

Partager

Partager
  • Envoyer la discussion sur Viadeo
  • Envoyer la discussion sur Twitter
  • Envoyer la discussion sur Google
  • Envoyer la discussion sur Facebook
  • Envoyer la discussion sur Digg
  • Envoyer la discussion sur Delicious
  • Envoyer la discussion sur MySpace
  • Envoyer la discussion sur Yahoo