Discussion :

[discus23]

Bonjour,

Je recherche un moyen de retourner toutes les lignes d'un tableau "qui commencent pareil". Je m'explique :
J'ai un tableau de données qui ressemble à :

TOTO 45 mu
TOTO 25 lu
TOTO 14 ki
TITI 25 ki
TITI 35 ra


Dans un autre onglet, j'aimerais, au moyen d'une formule, retourner toutes les lignes qui commencent par TITI, et ainsi obtenir :

TITI 25 ki
TITI 35 ra


Je galère depuis un petit moment avec les différentes formules de RECHERCHE, en m'aidant bien sûr de ce que je trouve sur le net, mais je n'arrive pas à obtenir ce que je souhaite.

Tout d'abord, est-ce faisable? Et si oui, comment puis-je y arriver?

Merci beaucoup par avance !!

[Pierre Fauconnier]

Salut.

Par formule, tu vas devoir passer par une matricielle. La formule doit être validée "en matricielle" (CTRL+SHIFT+ENTER), Excel ajoutera les accolades automatiquement. Le problème, c'est que tu devras tirer la formule vers le bas jusqu'à ce que la formule renvoie une chaine vide.



Tu pourrais également passer par un filtre élaboré, voire par un TCD pour extraire rapidement les données dans une nouvelle plage ou feuille.

[discus23]

Voilà une formule qui n'est pas forcément intuitive ^^
Merci beaucoup en tout cas, ca marche nickel !!

[discus23]

Pierre,

Pourrais-tu m'apporter quelques précisions quant au fonctionnement de cette formule stp?
En effet, je cherche à bien comprendre ce que je fais, et un point m'échappe.
Si mon tableau en entrée, ou la cellule où je tape la formule, n'est pas en première ligne, cela ne fonctionne pas.
Peux-tu me guider là dessus?

Merci.

[Pierre Fauconnier]

Le principe de base est d'extraire une donnée par rapport à sa position dans une matrice.

Dans mon exemple, la première ligne contenant "Martine" est sur la deuxième ligne de la plage A2:A7. On peut récupérer la donnée grâce à la formule

=INDEX($A$2:$A$7;2)

La deuxième occurrence de "Martine" est sur la 5ième ligne de la plage. On la récupérera grâce à

=INDEX($A$2:$A$7;5)

Le problème est donc de créer la liste (= matrice => validation matricielle) des numéros de lignes qui contiennent "Martine".

Pour cela, on va utiliser une propriété intéressante du zéro, absorbant pour la multiplication, et de la constante FAUX, qui vaut 0.

Regarde l'illustration suivante. En D2, on a

=A2="Martine"

qui renvoie VRAI lorsque Martine est en A sur la même ligne. EN E2, on multiplie cela par le numéro de ligne pour obtenir la matrice de valeurs en E2:E7.

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

=D2*LIGNE()

On s'aperçoit que 3 et 6 sont respectivement les 5ième et 6ième petites valeurs par ordre croissant, puisque 4 valeurs sont égales à 0. On va donc utiliser la fonction PETITE.VALEUR(Matrice;k) ou k représente le numéro ordinal de la valeur que l'on veut récupérer par ordre croissant. LIGNE() permet d'incrémenter cette valeur k de 1 lorsque l'on recopie vers le bas. NB.SI(...;"<>Martine") permet de savoir combien il y a de valeurs égales à 0. La première valeur à prendre en compte est celle qui suit.

On s'aperçoit également que les valeurs (3 et 6) renvoyés sont trop grandes d'une unité pour pouvoir être utilisées par INDEX (d'où le -1 de la formule). Si tu places la première formule sur une autre ligne que la ligne 1, tu dois alors diminuer k du numéro de ligne sur laquelle tu places cette formule -1.

De façon générique, LIGNE()-X doit renvoyer 1 pour récupérer la première ligne. Donc, pour démarrer l'extraction en ligne 13, tu auras LIGNE()-12. LIGNE()-X est ajouté à NB.SI(...) pour extraire la première valeur différente de zéro parmi la matrice renvoyée.

Pour le -1 qui suit, tu dois penser que INDEX compte 1 pour la première ligne du tableau. Si ton tableau commence en A2, la ligne 1 pour INDEX est la ligne 2 de la feuille, du dois donc faire 2-1. Si ton tableau commence en A16, la ligne 1 pour INDEX est obtenue grâce à -15.




La validation matricielle permet ici à Excel de "boucler" sur les lignes des vecteurs (= matrices) passés en paramètres pour créer la liste {0,0,0,0,3,6}.

[discus23]

Wouhahou top !!!
Ca c'est de la réponse.
Merci beaucoup d'avoir pris le temps pour m'expliquer les choses en détails comme ça.

[hben1961]

Bonjour à tous,

Le même résultat est obtenue avec la formule matricielle suivante:

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

=SIERREUR(INDEX(Feuil1!A:A;PETITE.VALEUR(SI(Feuil1!$A$1:$A$1000="TITI";LIGNE($1:$1000));LIGNE($A1)));"")

en supposant que toutes les données sont saisies en Feuil1 à partir de la première colonne... La formule sera recopiée vers la droite pour les valeurs des autres colonnes et vers le bas pour toutes les lignes suffisantes...

NB: L'utilisation des formules matricielles en grand nombre ralentirait le bon fonctionnement des classeurs...

Cordialement

[Gigapioche]

Bonjour à tous,

Merci pour ces réponses qui m'ont beaucoup aidé.

Dans la formule de M. Fauconnier, je ne comprend le "<>Martine". Est-il possible de faire une recherche selon une liste déroulante si on voulait que la formule fonctionne pour Pierre, Manon,ect....

Merci d'avance !

[Pierre Fauconnier]

Salut.

<>Martine permet de compter le nombre de lignes qui vont renvoyer 0, puisque la matrice construite va être 0,0,0,0,3,6.

Si tu veux sortir la constante de la formule pour utiliser la valeur d'une cellule (par exemple G1), tu dois remplacer "<>Martine" par "<>" & $G$1 et ="Martine" par =$G$1.

Dans G1, tu places la valeur dont tu veux extraire les lignes, que ce soit par liste de validation, par saisie clavier ou par formule.

Comme je l'ai précisé dans mon premier message, tu devras tirer la formule vers le bas jusqu'à avoir une chaîne vide, ou tirer sur suffisamment de lignes pour récupérer toutes les lignes en fonction de G1. Pense que les formules matricielles sont gourmandes en ressources, puisqu'elles travaillent "en boucle" sur les lignes, donc la solution de tirer "trop de lignes" n'est pas la plus performante.

[Gigapioche]

Merci pour ces explications.

En faites, je suis en train de créer un planning de production avec dans un onglet, les tâches avec les ressources, le projet ect... Dans un autre onglet "planning" un résumé en fonction de la ressource sélectionnné. Dans cet onglet s'affiche sous forme de "tableau de Gant" un résumé par semaine ou mois des tâches de la personne.

Je mettrai le fichier lorsque je l'aurai terminé.

Bonne journée

[Gigapioche]

Re Bonjour,

Une petite question pour changer

Si on voulait faire un tableau récapitulatif avec dans l'ordre les prénoms.

Dans le tableau principal, nous avons "Pierre, Manon, Martine ect..." dans un orde aléatoire.

Dans un tableau annexe, il faudrait tous les prénoms par ordre :

Pierre 12
Pierre 34
Pierre 45
Martine 19
Martine...
Ect..

Si en plus on voulait pouvoir choisir les nom à afficher. C'est possible?

Merci en tout cas.

[Gigapioche]

Bonjour,
J'ai encore une autre question.

J'aimerais faire un trie et affichage selon deux critères. Je pense que le fichier sera plus parlant.

Merci de votre aide.

Gigapioche