Retourner la deuxième valeur d’une recherche avec DECALER, INDEX et EQUIV

Un petit challenge intellectuel pour bien démarrer la semaine.

Donc, voici le problème : vous avez une liste de participants à un concours de photographie, le titre de la photographie, et un score. Chaque participant peut soumettre une ou deux photographies.

Et vous devez retourner les deux scores pour chaque participant dans un tableau récapitulatif

Comment faire ?

Voici donc une solution en image :
retourner-deuxieme-valeur-equiv-index-decaler 1

Et en vidéo, en deux parties :

et la deuxième partie :


On peut ajouter quelques optimisations, comme réduire la taille de la plage de recherche ou encore gérer les valeurs d'erreurs avec une fonction SI(ESTERREUR(...);...;...) ou SIERREUR(...;...). Cette dernière fonction n'étant disponible que depuis Excel 2007.

Bien évidemment, vous pouvez adapter cet exemple à de nombreux cas.

A bientôt

Gaëtan

Pour marque-pages : Permaliens.

29 réponses à Retourner la deuxième valeur d’une recherche avec DECALER, INDEX et EQUIV

  1. VIN dit :

    merci super!
    En plus j’ignorais la fonction de F9 sur une formule: c’est génial pour construire une formule complexe….!!! que faites-vous après avoir “vérifié” pour annuler l’effet F9? vous annulez, tout simplement? Ctrl+z?

  2. Gaetan dit :

    Bonjour,
    J’utilise la touche Echap pour éviter de valider la formule.
    Gaetan

  3. Gaetan dit :

    plus précisément, je crée d’abord la formule, je la valide avec entrée. Puis, je reviens sur les parties un peu complexes et j’utilise F9. Si je veux éviter de valider la formule, j’utilise la touche Echap.

  4. VIN dit :

    C’est génial…!!!
    Parfois on se demande comment on fait pour ignorer des trucs si simples! ça m’aurait tellement aidé tellement souvent… F9 puis Échap! Quel bonheur!

  5. Céline Brien dit :

    Salut Gaëtan,
    Enfin je prends le temps de visualiser ce vidéo.
    Excellent d’un bout à l’autre !
    Moins non plus je ne savais pas que la touche F9 permettait de voir le résultat d’une partie d’une formule.
    Merci,
    Céline

  6. Pierre dit :

    Bonjour,

    Merci pour cette astuce.
    J’ai passé l’après midi à essayer de construire une formule.

    Bien à vous,

  7. Gaetan dit :

    Merci Pierre

  8. Jerome dit :

    Bonjour,
    Pourrait-on trouver, grâce à cette formule, une troisième photo d’un des participants? Si oui, quelles modifications doit-on apporter à la formule?
    Merci d’avance.

  9. Gaetan dit :

    Bonjour Jérôme,

    A priori, en ajoutant un troisième équiv, ca devrait permettre de retrouver la troisième photo.

    Cordialement

    Gaetan Mourmant

  10. esteb45 dit :

    Un peu perdu comme Jérôme je n’arrive pas à comprendre où doit se situer le 3ème equiv.

    Pouvez vous me faire part de la formule pour recherche une 3ème valeur (ici 3ème photos des participants)

    Merci
    Stéphane

  11. magiqueol dit :

    Merci pour ces 2 vidéos très explicites !!!
    Je suis très intéressés par la recherche d’une 3ème ET 4ème valeur.
    Comme Jerome et Stephane, j’aimerai comprendre/savoir la formule à mettre en place, notamment le 3ème (voire 4ème…) equiv.
    Merci pour votre réponse
    Cordialement
    Olivier

  12. Gaetan dit :

    Bonjour,

    Désolé pour le délai de la réponse, c’était moins trivial.

    Ca donnerait ceci pour la 3e valeur. J’ai mis des retours à la ligne pour séparer les différentes parties.

    =INDEX(
    DECALER(
    DECALER(Tableau1[Score];EQUIV(E2;Tableau1[Nom];0);0);
    EQUIV(E2;DECALER(Tableau1[Nom];EQUIV(E2;Tableau1[Nom];0);0);0);
    0);
    EQUIV(E2;DECALER(
    DECALER(Tableau1[Nom];EQUIV(E2;Tableau1[Nom];0);0);
    EQUIV(E2;DECALER(Tableau1[Nom];EQUIV(E2;Tableau1[Nom];0);0);0);
    0);0);1)

    Je vais poster une vidéo avec l’explication.

  13. magiqueol dit :

    Merci pour votre réponse !!!
    De mon côté j’ai travaillé dessus, voilà ce que ça donne pour faire des groupes par exemple.
    C’est par là :
    http://tablepstactiles.eklablog.com/faire-des-groupes-p1007400
    Il y a ici 30 valeurs
    Cordialement
    Olivier

  14. Gaetan dit :

    Sympa. Vous utilisez quoi pour la tablette? Excel?

  15. Magiqueol dit :

    J’utilise Numbers sur iPad.
    Ici j’ai partagé une version excel pour que chacun puisse l’utiliser !!!
    Cordialement

  16. Granier dit :

    Bonjour,

    Pouvez-vous me donner la suite des formules pour des applications au-dela de 3 comme vous l’avez fait en nous montrant la formule 3 en commentaire.

    Il me faudrait les 7 autres formules pour aller jusqu’à un décalage de 10 sur la même plage.

    Mais au delà de ça pouvez-nous dire la logique pour faire ces décalage de 1 à chaque fois.
    Merci pour votre retour, le décalage jusqu’à trois était déjà une révolution 😉

    Bien cordialement et merci

  17. Aidy dit :

    Bonjour et merci pour cette super astuce.
    J’ai une question. Dans le cas où certaines personnes ont 5 notes, d’autres 4 et d’autres une seule… Comment utiliser cette combinaison de fonctions sans repéter 5 fois la note de celui qui n’en a qu’une seule.
    Merci d’avance

  18. Gaetan dit :

    Bonjour,
    J’ai l’impression qu’on atteint les limites d’excel au niveau formules.
    Je pense qu’il serait préférable d’écrire une procédure en VBA pour répondre à la question.
    Je vais y réfléchir pour une prochaine newsletter.
    Cordialement
    Gaetan

  19. Joseph dit :

    Bonjour,

    Une proposition sans VBA.
    Insérer à coté du tableau une colonne qui numérote le nb d’occurrence des nom (avec un nb.si ) puis utiliser cette info dans un nb.si.ens.
    Si vous m’indiquer une soluce pour déposer mon fichier “exemple”, cela serait un plaisir de vous le faire parvenir. Merci pour votre site très riche en information.

  20. Joseph HEILI dit :

    Bonjour,

    Si cela intéresse tjrs qq’un il y a une solution flexible, compacte et “étirable” en faisant

    =SOMMEPROD(GRANDE.VALEUR((Tableau1[Nom]=$E2)*Tableau1[Note];COLONNES($F$2:F2)))

    Et hop on sourit 🙂

  21. Kriss dit :

    Bonjour, j’ai un problème de #REF ! dans ma formule :
    =INDEX(DECALER([parcelle.xls]Sheet!$A:$A;EQUIV(I6;[parcelle.xls]Sheet!$E:$E;0);0);EQUIV(I6;DECALER([parcelle.xls]Sheet!$E:$E;EQUIV(I6;[parcelle.xls]Sheet!$E:$E;0);0);0);1)

    L’objectif est de rechercher toutes les id liés à une référence.

    Avez vous une idée ?

    En vous remerciant.

    En vous remerciant.

  22. Jerome dit :

    Bonjour Mourmant,

    Avez vous fait la video pour la troisième et quatrième valeur ?

    J’ai réussis pour la 3ème valeur avec la formule que vous avez indiqué, mais, j’ai pour la 4ème valeur, et là, je ne trouve pas la solution.

    Etant donné que le post date un peu, il y a peut être de nos jours une autre solution !

    Par avance merci pour votre aide.

    Cdt,

  23. Gaetan Mourmant dit :

    Bonjour Jerome,
    Effectivement, on doit faire beaucoup plus simple maintenant, sur excel 365, avec la fonction FILTRE.
    = TRANSPOSE(TRIER(FILTRE(Tableau1[Score];Tableau1[Nom]=G2);;-1))
    Et on recopie vers le bas
    Dans les commentaires, il y a aussi des belles pistes.
    A bientôt.

  24. Jerome dit :

    Bonjour Gaetan,

    Merci, c’est magique 🙂

  25. Giovanni dit :

    Un grand merci !!!

  26. India dit :

    Bonjour Gaëtan,
    Je souhaite pousser le challenge plus loin, voir récupérer jusqu’à la 10 éme valeur du score.
    Pour la première fois, depuis plusieurs mois de recherche, je trouve enfin une solution à mon problème grâce à votre formule.
    Je te remercie pour aise très précieuse, je croise les doigts pour obtenir cette formule.

    Cordialement
    I.

  27. India dit :

    Petite indication complémentaire; je n’ai pas l’office 365.
    Les dernières fonctions : FILTRE, TRIER, TRANSPOSE, n’existent pas chez moi.
    Donc je cherche la solution selon la formule initiale proposée.
    Merci à vous
    Cordialement
    I.

Laisser un commentaire

Votre adresse e-mail ne sera pas publiée. Les champs obligatoires sont indiqués avec *

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