Contact: 514-605-7112 / info@lecfomasque.com

Excel: Recherchev (vlookup) avec résultats multiples

Publié le : 26 octobre 2015

Un lecteur a posé une question sur notre forum, à savoir comment dresser une liste de tous les résultats possibles d’un recherchev (vlookup), sans utiliser de tableau croisé dynamique. Nous pouvons résoudre cette problématique de différentes façons. Cet article de blogue vise à en présenter une, que nous jugeons efficace. Si vous avez d’autres solutions à proposer, nous vous invitons à utiliser l’espace commentaires, à la suite de cet article.

 

Mettre les données sous forme de tableau

D’abord, nous recommandons de mettre les données de départ sous forme de tableau. Pour savoir comment faire et pour comprendre tous les avantages de la mise sous forme de tableau, vous pouvez relire l’article Découvrez la magie des tableaux Excel. Dans notre exemple ci-bas, nous avons donc mis nos produits et nos ventes sous forme de tableau. Il sera facile de mettre à jour ce tableau, qui pourra comprendre plus ou moins de lignes au fil du temps et qui s’ajustera automatiquement.

Recherchev multiple

 

Ajouter une colonne calculée avec la fonction Si (ou If)

Ensuite, nous recommandons d’ajouter une colonne à votre tableau dans le but d’identifier clairement les lignes du tableau qui doivent être retenues. Dans notre exemple ci-bas, l’usager peut faire un choix dans la cellule H5. Le choix actuel est le produit A et c’est pourquoi la fonction Si (ou If) retourne un 1 pour tous les produits A, dans notre tableau.

Recherchev multiple

 

Ajouter une colonne calculée avec les fonctions Si (ou If) et Somme (ou Sum)

Nous proposons d’ajouter ensuite une seconde colonne calculée pour indiquer la séquence de l’apparition des produits A dans notre tableau. Dans notre exemple ci-bas, nous avons 3 produits A et chacun est numéroté de 1 à 3 selon son rang d’apparition dans le tableau. À noter que, puisque les données ont été mises sous forme de tableau, ces 2 colonnes calculées s’ajusteront si le tableau évolue au fil du temps.

Recherchev multiple

 

Calculer le nombre d’occurences

Dans la cellule H6, ci-bas, nous avons utilisé la fonction Somme (ou Sum) sur la colonne Occurence pour indiquer combien de fois le produit A apparaît dans notre tableau. C’est aussi le nombre de lignes que devra comprendre notre table de résultats.

Recherchev multiple

 

Construire la table de résultats

Nous suggérons de créer une table de résultats avec 3 colonnes. La première étant simplement un index pour indiquer le rang du résultat. Pour cela, nous utilisons la fonction Ligne (ou Row), imbriquée dans une fonction Si (ou If), tel qu’illustré ci-dessous. Ainsi, la fonction retourne le rang du résultat dans le tableau, de 1 à 3 dans notre exemple, et retourne du vide pour les autres lignes. Ce faisant, nous serons assurés qu’un produit qui est présent plus de 3 fois dans la table, verra sa table de résultats s’allonger.

Recherchev multiple

 

Ensuite, nous ajoutons une colonne, à la l’aide de la fonction Si (ou If) pour indiquer le nom du produit.

Recherchev multiple

 

Et finalement, nous utilisons une fonction Index/Equiv (ou Index/Match) pour retourner les ventes correspondantes de chaque produit.

Recherchev multiple

 

Résultat final

Lorsque l’usager choisit un autre produit, par exemple, le produit C, les résultats s’ajustent automatiquement.

Recherchev multiple

 

Vous avez des questions Excel vous aussi?

Utilisez notre forum pour poser vos questions ou encore, utilisez les zones de commentaires dans le bas de chacun de nos articles.

 


NOTRE OFFRE DE FORMATIONS


Microsoft Most Valuable Professional

Le CFO masqué vous offre un vaste choix de formations Excel et Power BI, réparties dans 8 catégories: Excel – Tableaux de bord, Excel – Modélisation financière, Excel – VBA, Excel – Power Tools, Excel – Ninja, Power BI, Finance corporative et Compléments avancés. Ces formations sont offertes en classe, en entreprise et en ligne. Nos formateurs sont des experts dans leur domaine et ils sont accrédités par Emploi-Québec et vous remettent un certificat, à la fin de chaque formation, que vous pouvez notamment utiliser pour faire reconnaître des heures de formation continue auprès de votre ordre professionnel.

 

Découvrez quelles formations vous conviennent

Pour info: 514-605-7112 ou info@lecfomasque.com

 

Sophie Marchand

Instigatrice du CFO masqué, Sophie Marchand est détentrice d’une M.Sc. en finance corporative, d’un titre comptable CPA, CGA, d’un titre MVP (Most valuable professional) Excel et d'un titre MVP Data Platform de Microsoft, et cumule de nombreuses années d’expérience dans le milieu des affaires. Elle se spécialise particulièrement en modélisation financière et en intelligence d’affaires. À ce titre, elle développe des modèles financiers rigoureux, des tableaux de bord sophistiqués et des outils de gestion performants. Elle offre ses services en tant que consultante, formatrice et conférencière.

  • réponse emanuele ,

    bravo!

    • réponse Moti ,

      Tres clair et parfait

      • réponse Hubert ,

        Simple & efficace comme tjs ! Merci…

        • réponse Stéphane ,

          Bonjour,
          Il existe une possibilité avec 2 formules, que j’avais glané sur un forum,(je ne me souviens plus de l’auteur) et que j’utilise régulièrement pour des extractions sans TCD. A juger si pratique ou non….

          En D6 : =SI(ESTERREUR(CHERCHE($H$5;[@Produit])); » »;LIGNE())
          En K6 : =SIERREUR(INDEX(C:C;PETITE.VALEUR(D:D;LIGNE()-LIGNE($D$5))); » »)

          Pour l’index
          En I6 : =SI(NBCAR(k6)>1;I6; » »)
          En I7 : =SI(NBCAR(k7)>1;I6+1; » ») puis étendre la formule

          Très cordialement
          Stéphane

          • réponse Hugo ,

            Bonjour,
            Une autre façon avec une formule plus complexe mais moins d’opérations serait (Désolé je n’ai pas les formules en Français mais Sophie pourra traduire):
            Vous créer une liste de chiffre dans colonne i (1 à 16 disons dans cet exemple) et vous appliquez la formule suivante dans la colonne J.
            ={IF(SMALL(IF(Table1[Produit]=$H$1,Table1[Ventes],MAX(Table1[Ventes])+1),$I2)=MAX(Table1[Ventes])+1, » »,SMALL(IF(Table1[Produit]=$H$1,Table1[Ventes],MAX(Table1[Ventes])+1),$I2))}
            Ne pas oublier Ctrl-Shift-Enter car c’est une formule « Array » 🙂

            La seule variante, la liste sort en ordre de chiffre de vente et non en ordre selon la liste.

            Bonne journée,
            Hugo

            • réponse Hugues Perron ,

              Très intéressant comme technique. J’aime beaucoup ce genre de technique.

              Mais pour obtenir pratiquement le même résultat, il y a aussi le filtre avancé sous l’onglet « Données ». À la différence que la mise à jour du tableau ne se fait pas automatiquement. Toutefois, le filtre peut avoir des « wildcard », ce qui peut être très pratique aussi.

              Hugues

              • réponse Guy Marty ,

                Bonjour,
                Le problème avec cette solution est qu’il y a une rétroaction entre la base de donnée et la recherche sur celle-ci qu’il me semble souhaitable d’éviter.
                Pour cela, je propose de supprimer la colonne « Occurence » et dans la colonne « Séquence » de remplacer la formule par :
                =[Produit] & »-« & NB.SI($B$2:B2; »= »&[Produit])
                Ainsi, chaque cellule contient une valeur de type ‘NomProduit-NumSequence’ unique.
                En H6 mettre la formule
                =NB.SI([Produit];G6)
                Dans la colonne I, pour retrouver dans chaque cellule ‘NomProduit-NumSequence’, étendre la formule suivante à partir de I6 :
                =SI(LIGNE()-5<$H$6;$G$6&"-"&LIGNE()-5;"")
                En espérant que cela sera utile.
                A bientôt
                Guy

                • réponse Laurie ,

                  Bonjour,
                  Merci pour ce tutoriel j’ai réussi à tout faire sauf la dernière étape avec index équiv.
                  En fait au lieu de mes produits il s’agit de codes postaux et je veux que les ventes (pour moi le nom des clients) s’affiche mais il y a zéro à la place…. une idée ?
                  dans l’attente de votre retour,
                  Bien cordialement

                  • réponse Sophie Marchand ,

                    Bonjour,

                    Pourriez-vous svp poser votre question dans notre forum, en y insérant votre fichier Excel? Il sera plus facile de vous aider ainsi.

                    Merci.

                    Sophie

                  • réponse tarik ,

                    Super. Ça demande un peu de réflexion mais c’est très bien expliqué. Merci

                    Laisser un commentaire