Recherche

Faire une RECHERCHEV décalée

Temps de lecture 3 minutes

Cet article va vous montrer comment retourner des valeurs qui ne soient sur la même ligne que la valeur recherchée.

Problème à résoudre

Dans le cas où vous avez un tableau qui contient des identifiants communs à plusieurs lignes, ou des cellules vides pour indiquer que l'identifiant est répété, il est fortement recommandé de ré-ordonner vos  données.

Comment transférer les données verticales dans un tableau ordonné

Pour y arriver, nous allons nous servir de 3 fonctions, INDEX, EQUIV et DECALER

Nous avons un tableau (colonne A:D) qui donne la liste des ventes de nos articles à la fin de chaque mois. Pour gagner en lisibilité, nous souhaitons réordonner nos valeurs dans les 2 tableaux des colonnes G à J pour y afficher les ventes et le chiffre d'affaire par mois.

Comme vous le constatez, dans la colonne A, beaucoup de dates sont manquantes. Mais là n'est pas le problème. Même si nous recopions les dates dans les cellules vides, nous sommes face au même problème ; nous ne pouvons pas identifier les données pour les articles B, C et D

Construction de la formule

L'idée c'est de trouver la position des dates (qui est notre identifiant) et de lire les données qui sont en dessous en se décalant de 1, 2 ou 3 lignes.

Ne pas d'utiliser la fonction RECHERCHEV

En effet, la fonction RECHERCHEV est parfaite pour récupérer les valeurs qui sont sur la même ligne que l'identifiant.

Mais pour les autres lignes c'est impossible car la fonction RECHERCHEV retourne une valeur et non pas une plage.

Préférer la fonction INDEX

Nous devons alors nous baser sur la fonction INDEX pour construire notre recherche car la fonction INDEX retourne une plage de données.

Pour la formule G2, nous allons écrire la formule suivante pour retourner le nombre d'articles vendu en Janvier 2014 pour le produit A. Pour les explications détaillées sur la fonction INDEX, vous pouvez consulter Fonction INDEX et pour la fonction EQUIV.

Recherche_decalee_2

=INDEX($A$2:$D$49,EQUIV($F2,$A$2:$A$49,0),3)

La fonction peut être comprise de la façon suivante :

  • Nous nous intéressons à la plage de données A2:D49 (nos données sans la ligne d'entête)
  • Nous recherchons la ligne correspondant à la date qui nous intéresse (fonction EQUIV).
  • Après nous indiquons que nous récupérons les données de la 3ème colonne (paramètre 3) pour retourner le nombre d'articles vendus.
Recherche_decalee_3

Si nous voulons retourner le chiffres d'affaires, nous aurions mis 4 pour le dernier paramètre de la fonction INDEX.

=INDEX($A$2:$D$49,EQUIV($F2,$A$2:$A$49,0),4)

Faire une recherche décalée

Comme la fonction INDEX retourne une plage de données (et non pas une valeur comme le fait la fonction RECHERCHEV), nous allons pouvoir inclure les 2 précédentes formules, dans une fonction DECALER.

=DECALER(référence de la cellule pivot, nombre de lignes, nombre de colonnes)

La fonction DECALER retourne une donnée par rapport à une cellule pivot.

Dans notre exemple, pour retourner le nombre d'articles de l'article B, nous devons nous décaler d'une cellule vers le bas par rapport à la recherche précédente. Nous allons donc écrire notre formule finale de la façon suivante pour le produit B

=DECALER(INDEX($A$2:$D$49,EQUIV($F2,$A$2:$A$46,0),3),1,0)

Recherche_decalee_4

Et en recopiant cette formule, le résultat final est

Related posts

Rechercher sur plusieurs colonnes comme critère

Frédéric LE GUEN

Pourquoi RECHERCHEV retourne #N/A

Frédéric LE GUEN

Comment retourner plusieurs valeurs à partir d’une recherche ?

Frédéric LE GUEN

1 commentaire

Molina francis 22/02/2021 at 12:36

Bravo pour la qualité des explications.
Je me permets de vous posez une question.
j'ai deux colonnes A& B concaténées dans une colonne DD, J'effectue dans la recherche suivante dans une cellule indépendante de type: =recherche(9^9;DD15:DD400). Formule qui fonctionne très bien dans d'autres formats. mais je ne parviens pas à afficher la valeurs.
Les lignes de la colonne de recherche contient cette formule: =SI(A17="";"";CONCATENER(ARRONDI(A17/10;0);"/";ARRONDI(B17/10;0))).
Pour exemple colA =117 Col =B68 et col DD/ 12/7. Résultat dans la cellule
#N/A. J'ai varié les formats sans résultats. Etant retraité je fais cela pour le plaisir de me faire travailler l'esprit mais j'aimerai bien comprendre pourquoi cela ne fonctionne pas.
Merci par avance pour l'éclairage que vous pourriez m'apporter.
Amicalement

Francis

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.