Faire une recherche décalée

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.

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

Problème à résoudre

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.

Recherche_decalee_1

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

Choix de la fonction

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.

L'erreur à ne pas commettre, c'est 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 les dates mais pour les autres c'est impossible car la fonction RECHERCHEV retourne une valeur et non pas une plage.

C'est pourquoi, nous devons 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.

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

Recherche_decalee_2

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.

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)
Recherche_decalee_3

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ées 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 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 le résultat final est

Articles complémentaires


Voici une liste d'articles qui pourrait également vous intéresser sur le même thème.

Lien Permanent pour cet article : https://www.excel-exercice.com/faire-une-recherche-decalee/


(4 commentaires)

Passer au formulaire de commentaire

  1. 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

  2. et la fonction MATCH sert à quoi ?

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

  3. 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

Laisser un commentaire

Your email address will not be published.