Matricielles Recherche

RECHERCHEV retourne plusieurs lignes

Temps de lecture 2 minutes

Comment retourner plusieurs lignes à partir d'une valeur. C'est possible et très facile mais pas avec la fonction RECHERCHEV.

Fonction RECHERCHEV inadaptée

La fonction RECHERCHEV n'a jamais été conçue pour retourner plusieurs lignes, ni même la fonction INDEX.

Ces fonctions, bien que très utilisées dans les feuilles de calculs, ne peuvent retourner qu'un seul résultat (c'est comme ça).

Le seul moyen de retourner plusieurs lignes à partir d'une valeur c'est d'utiliser la fonction FILTRE.

Fonction FILTRE

La fonction FILTRE est présente seulement dans les versions de Microsoft 365 ou Excel Online

FILTRE fait partie des fonctions matricielles dynamiques. Ce sont de nouvelles fonctions, introduites en 2019, qui permettent de retourner un résultat dans plusieurs cellules.

Exemple sur un cas concret

Dans le document suivant, nous avons le pointage de colis avec comme identifiant le numéro de tracking. Un numéro de tracking peut être composé de plusieurs colis. Nous souhaitons

  1. Trouver les références des produits composant un envoi
  2. Trouver les points de passage avec l'heure, des tracking
Recapitulatif des tracking de colis

Trouver les références composant un envoi

Nous allons utiliser la fonction FILTRE pour ne retourner que la colonne des références produits

=FILTRE($B$2:$B$48;

Selection de la colonne a retourner par la fonction FILTRE

Et ensuite, nous allons indiquer la colonne sur laquelle effectuer le filtrage avec comme critère la cellule G2

=FILTRE($B$2:$B$48;$A$2:$A$48=G2)

Plusieurs lignes retournees sur le numero de tracking

Il est intéressant de noter qu'avec la fonction FILTRE, il n'est pas nécessaire d'inclure dans le premier paramètre, la colonne à filtrer.

Mais ici, nous retournons autant de fois les références des produits qu'ils sont passés sur des points de tracking. Donc, pour ne conserver que les références uniques, nous allons tout simplement utiliser la fonction UNIQUE

=UNIQUE(FILTRE($B$2:$B$48;$A$2:$A$48=G2))

References produits composant le tracking 1

Trouver les points de passage d'un numéro de tracking

Ici, la fonction FILTRE se construit quasiment de la même façon que la précédente fonction. En fait, ce qui change, c'est le premier paramètre. Cette fois-ci, au lieu de sélectionner une seule colonne, nous allons sélection les colonnes Poste ID et Date Heure tracking

=UNIQUE(FILTRE($C$2:$D$48;$A$2:$A$48=G2))

FILTRE retourne 2 colonnes

La conséquence c'est que la fonction retourne maintenant le résultat de 2 colonnes. Il est impératif que vos colonnes soient contigües. Vous ne pouvez pas sélectionner la colonne B et D par exemple.

Vous pouvez mettre les numéros de tracking dans un menu déroulant en créant un menu déroulant dynamique

Filtre retourne plusieurs lignes

Related posts

Menu déroulant sans vide

Frédéric LE GUEN

Pourquoi RECHERCHEV retourne #N/A

Frédéric LE GUEN

Liste de valeurs distinctes sans vide

Frédéric LE GUEN

5 commentaires

Cyril 20/08/2020 at 15:47

Hello
Merci pour ce tuto ... très interessant et bien expliqué ... seulement j'ai un soucis, quand je duplique ma formule : =DECALER(INDEX(Feuil1!$A2:$B391;EQUIV(G$3;Feuil1!$B$2:$B$391;0);1);0;0)

J'obtiens la bonne liste mais il m'affiche la même valeur précédente tant qu'une autre nouvelle valeur n'a pas été trouvée ...
Ex : Avec comme critère de sélection Toto ($G$3)
Feuil1
Val1 - Toto
Val2 - Toto
Val3 - Toto
Val4 - Lulu
Val5 - Lulu
Val6 - Momo
Val7 - Momo
Val8 - Toto
Val9 - Toto

En gros il va m'afficher
Val1
Val2
Val3
Val3
Val3
Val3
Val3
Val8
Val9

J'ai pas besoin de ça moi :) Je veux juste Afficher ligne par ligne les infos retournée par le critère Toto...
Val1
Val2
Val3
Val8
Val9
C'est tout :)

Merci pour ton aide

Répondre
Ismet 25/09/2018 at 00:03

Bonjour,

Je suis en train de faire un planning pour 6 equipe de mon organisation.
Je peux dire que j ai quasi fini mais il y a un point d amélioration que j aimerais apporter.

Il existe 6 equipes reparties sur 6 jours de travail et ont comme service :
01/01/2018 : Equipe 1: jour 8-16
01/01/2018 : Equipe 2: apres midi 16-24
01/01/2018 : Equipe 3: nuit 00-08
01/01/2018 : Equipe 4: repos1
01/01/2018 : Equipe 5: repos 2
01/01/2018 : Equipe 6: repos3

02/01/2018: equipe 1: apres midi 16-24
02/01/2018: equipe 2: nuit 00-08
....

J ai tiré une serie jusqu en 2020 (cela me fait plusieurs milliers de lignes) et je fais une recherche vertical pour retourver le service pour l equipe et le jour concerné, la formule marche mais les recherches prennent du temps.

Y a t il une possibilité de faire une formule qui irait dans ce sens:

J encode l horaire de l'Equipe1:
01/01/2018: 8-16
02/01/2018: 16.24
03/01/2018: 00-08
04/01/2018: Repos1
05/01/2018: Repos 2
06/01/2018 Repos 3

Apd ces donnees, je souhaiterais intégrer une formule.
Imaginons que l on soit le 10/01/2018:
(10/01/2018 -01/01/2018) = 9

Le service de l equipe1 doit etre decalé de "9" et m afficher "00-08". En effet, dès que le "repos3" est atteint, on recommence la serie à "08-16" jusqu'à ce que la difference de date de "9" soit atteint..

J ai essayé avec la fonction DECALE et MOD mais je n y arrive pas.

Votre aide sera la bienvenue :) d avance merci

Répondre
nels gar 22/12/2016 at 09:35

et la fonction MATCH sert à quoi ?

Répondre
Frédéric LE GUEN 26/12/2016 at 22:59

Bonjour,
MATCH c'est la fonction EQUIV. Je ne comprends pas votre question en fait ? C'est par rapport à l'article ?

Répondre
mateo 24/02/2016 at 21:35

Bonjour,

Très intéressant, comme tous les articles de votre site. Toutefois, par exemple, comment feriez-vous pour calculer le nombre d'articles de A et C en admettant que chaque jour se trouve sur une feuille différente et qu'il n'y ait pas de ventes régulières (donc qu'il arrive qu'il n'y ait pas de vente de A, B, C ... au cours d'une journée) ?

Merci

Répondre

Laissez un commentaire

Ce site utilise Akismet pour réduire les indésirables. En savoir plus sur comment les données de vos commentaires sont utilisées.

Ce site utilise des cookies pour améliorer votre expérience et vos recherches. Nous pensons que vous êtes dʼaccord sur ce principe mais vous pouvez refuser cette option. Accepter Continuer

Privacy & Cookies Policy