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

Un petit chal­lenge intel­lectuel pour bien démar­rer la semaine.

Donc, voici le prob­lème : vous avez une liste de par­tic­i­pants à un con­cours de pho­togra­phie, le titre de la pho­togra­phie, et un score. Chaque par­tic­i­pant peut soumet­tre une ou deux photographies.

Et vous devez retourn­er les deux scores pour chaque par­tic­i­pant dans un tableau récapitulatif 

Com­ment faire ?

Voici donc une solu­tion en image :
retourner-deuxieme-valeur-equiv-index-decaler 1

Et en vidéo, en deux parties :

et la deux­ième partie : 


On peut ajouter quelques opti­mi­sa­tions, comme réduire la taille de la plage de recherche ou encore gér­er les valeurs d’er­reurs avec une fonc­tion SI(ESTERREUR(…);…;…) ou SIERREUR(…;…). Cette dernière fonc­tion n’é­tant disponible que depuis Excel 2007.

Bien évidem­ment, vous pou­vez adapter cet exem­ple à de nom­breux cas.

A bien­tô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 :

    mer­ci super!
    En plus j’ig­no­rais la fonc­tion de F9 sur une for­mule: c’est génial pour con­stru­ire une for­mule com­plexe.…!!! que faites-vous après avoir “véri­fié” pour annuler l’ef­fet F9? vous annulez, tout sim­ple­ment? Ctrl+z?

  2. Gaetan dit :

    Bon­jour,
    J’u­tilise la touche Echap pour éviter de valid­er la formule.
    Gaetan

  3. Gaetan dit :

    plus pré­cisé­ment, je crée d’abord la for­mule, je la valide avec entrée. Puis, je reviens sur les par­ties un peu com­plex­es et j’u­tilise F9. Si je veux éviter de valid­er la for­mule, j’u­tilise la touche Echap.

  4. VIN dit :

    C’est génial…!!!
    Par­fois on se demande com­ment on fait pour ignor­er des trucs si sim­ples! ça m’au­rait telle­ment aidé telle­ment sou­vent… F9 puis Échap! Quel bonheur!

  5. Céline Brien dit :

    Salut Gaë­tan,
    Enfin je prends le temps de visu­alis­er ce vidéo.
    Excel­lent d’un bout à l’autre !
    Moins non plus je ne savais pas que la touche F9 per­me­t­tait de voir le résul­tat d’une par­tie d’une formule.
    Merci,
    Céline

  6. Pierre dit :

    Bon­jour,

    Mer­ci pour cette astuce.
    J’ai passé l’après midi à essay­er de con­stru­ire une formule.

    Bien à vous,

  7. Gaetan dit :

    Mer­ci Pierre

  8. Jerome dit :

    Bon­jour,
    Pour­rait-on trou­ver, grâce à cette for­mule, une troisième pho­to d’un des par­tic­i­pants? Si oui, quelles mod­i­fi­ca­tions doit-on apporter à la formule?
    Mer­ci d’avance.

  9. Gaetan dit :

    Bon­jour Jérôme,

    A pri­ori, en ajoutant un troisième équiv, ca devrait per­me­t­tre de retrou­ver la troisième photo.

    Cor­diale­ment

    Gae­tan Mourmant

  10. esteb45 dit :

    Un peu per­du comme Jérôme je n’ar­rive pas à com­pren­dre où doit se situer le 3ème equiv.

    Pou­vez vous me faire part de la for­mule pour recherche une 3ème valeur (ici 3ème pho­tos des participants)

    Mer­ci
    Stéphane

  11. magiqueol dit :

    Mer­ci 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 for­mule à met­tre en place, notam­ment le 3ème (voire 4ème…) equiv.
    Mer­ci pour votre réponse
    Cordialement
    Olivier

  12. Gaetan dit :

    Bon­jour,

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

    Ca don­nerait ceci pour la 3e valeur. J’ai mis des retours à la ligne pour sépar­er les dif­fé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 :

    Mer­ci pour votre réponse !!!
    De mon côté j’ai tra­vail­lé 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 :

    Sym­pa. Vous utilisez quoi pour la tablette? Excel?

  15. Magiqueol dit :

    J’u­tilise Num­bers sur iPad.
    Ici j’ai partagé une ver­sion excel pour que cha­cun puisse l’utiliser !!!
    Cordialement

  16. Granier dit :

    Bon­jour,

    Pou­vez-vous me don­ner la suite des for­mules pour des appli­ca­tions au-dela de 3 comme vous l’avez fait en nous mon­trant la for­mule 3 en commentaire.

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

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

    Bien cor­diale­ment et merci

  17. Aidy dit :

    Bon­jour et mer­ci pour cette super astuce.
    J’ai une ques­tion. Dans le cas où cer­taines per­son­nes ont 5 notes, d’autres 4 et d’autres une seule… Com­ment utilis­er cette com­bi­nai­son de fonc­tions sans repéter 5 fois la note de celui qui n’en a qu’une seule.
    Mer­ci d’avance

  18. Gaetan dit :

    Bon­jour,
    J’ai l’im­pres­sion qu’on atteint les lim­ites d’ex­cel au niveau formules.
    Je pense qu’il serait préférable d’écrire une procé­dure en VBA pour répon­dre à la question.
    Je vais y réfléchir pour une prochaine newsletter.
    Cordialement
    Gaetan

  19. Joseph dit :

    Bon­jour,

    Une propo­si­tion sans VBA.
    Insér­er à coté du tableau une colonne qui numérote le nb d’occurrence des nom (avec un nb.si ) puis utilis­er cette info dans un nb.si.ens.
    Si vous m’indi­quer une soluce pour dépos­er mon fichi­er “exem­ple”, cela serait un plaisir de vous le faire par­venir. Mer­ci pour votre site très riche en information.

  20. Joseph HEILI dit :

    Bon­jour,

    Si cela intéresse tjrs qq’un il y a une solu­tion flex­i­ble, com­pacte et “étirable” en faisant 

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

    Et hop on sourit 🙂

  21. Kriss dit :

    Bon­jour, j’ai un prob­lè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’ob­jec­tif 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 :

    Bon­jour Mourmant,

    Avez vous fait la video pour la troisième et qua­trième valeur ?

    J’ai réus­sis pour la 3ème valeur avec la for­mule que vous avez indiqué, mais, j’ai pour la 4ème valeur, et là, je ne trou­ve pas la solution.

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

    Par avance mer­ci pour votre aide.

    Cdt,

  23. Gaetan Mourmant dit :

    Bon­jour Jerome,
    Effec­tive­ment, on doit faire beau­coup plus sim­ple main­tenant, sur excel 365, avec la fonc­tion FILTRE.
    = TRANSPOSE(TRIER(FILTRE(Tableau1[Score];Tableau1[Nom]=G2);;-1))
    Et on recopie vers le bas
    Dans les com­men­taires, il y a aus­si des belles pistes.
    A bientôt.

  24. Jerome dit :

    Bon­jour Gaetan,

    Mer­ci, c’est magique 🙂

  25. Giovanni dit :

    Un grand merci !!!

  26. India dit :

    Bon­jour Gaëtan,
    Je souhaite pouss­er le chal­lenge plus loin, voir récupér­er jusqu’à la 10 éme valeur du score.
    Pour la pre­mière fois, depuis plusieurs mois de recherche, je trou­ve enfin une solu­tion à mon prob­lème grâce à votre formule.
    Je te remer­cie pour aise très pré­cieuse, je croise les doigts pour obtenir cette formule.

    Cor­diale­ment
    I.

  27. India dit :

    Petite indi­ca­tion com­plé­men­taire; je n’ai pas l’office 365.
    Les dernières fonc­tions : FILTRE, TRIER, TRANSPOSE, n’existent pas chez moi.
    Donc je cherche la solu­tion selon la for­mule ini­tiale proposée.
    Mer­ci à 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.