Comment remplir un tableau à partir d'une base de données en répondant à plusieurs critères sans VBA ?

Dans l’article présent de la formation Excel, je vais vous présenter un exercice dont l’objectif est de vous montrer comment créer une formule de recherche personnalisée qui permet d’afficher plusieurs résultats en fonction de plusieurs critères sans utiliser aucun code VBA.

Comment remplir tableau à partir d'une base de données en répondant à plusieurs critères sans VBA


J’ai reçu dernièrement un message d’un de mes contacts qui cherchait une solution pour remplir un tableau à partir d’un autre tableau source en fonction de plusieurs critères de recherche.
L’idée donc consiste à effectuer une recherche pour trouver plusieurs résultats en répondant à plusieurs critères. Une tâche qu’on ne peut pas la faire avec une formule de recherche classique.


Remplir tableau a partir d un autre tableau


Comme vous le voyez dans cet exemple, je veux qu’Excel cherche dans ma base de données les valeurs qui sont liées à mes trois critères de recherche (Nom du vendeur, mois et année) et remplira ensuite mon tableau de Rapport avec les résultats trouvés.
Je souhaite de plus qu’Excel m’affiche dans les entêtes des colonnes Iphone et Samsung la quantité totale des ventes pour chaque smartphone.


Afficher plusieurs résultats en fonction de plusieurs critères


Comment arriver à réaliser ce travail ?

Moi, j’y suis arrivé en utilisant une formule de recherche qui se compose des deux fonctions INDEX et EQUIV conjointement en jouant sur leurs paramètres pour qu’elles me permettent d’afficher plusieurs résultats au lieu d’un seul dans le cas classique.
Et pour vous expliquer comment aboutir à ce résultat souhaité et obtenir la formule finale, suivez avec moi, très attentivement, ces étapes :

Extraire les mois et les années

Dans la feuille Rapport, nous avons deux critères à rechercher à part le nom du vendeur : Mois et Année. Et si nous voulons extraire de la base de données les valeurs correspondant à ces deux critères, nous n’y trouverons aucune colonne les contenant. Ajoutez donc deux colonnes à cette base de données et remplissez-les en extrayant les mois et les années à partir de la colonne Date.

  • Dans la cellule E2 saisissez la formule =TEXTE((A2);"mmmm") puis copiez-la vers le bas.
  • Dans la cellule F2 tapez =ANNEE(A2) puis copiez-la vers le bas également.


Extraire les mois et les années


Contourner les limites de EQUIV

Vous savez que INDEX renvoie la valeur contenue dans la cellule qu’on a précisée ses cordonnées : numéro de ligne et de colonne.

Dans notre cas, et si par exemple nous cherchons les données de ventes (Date, Quantité d’Iphone et de Samsung vendue) associées au vendeur Philippe pendant le mois Février 2018, nous devons passer en premier par EQUIV qui va nous renvoyer les numéros de lignes où se positionnent ces données pour les introduire dans INDEX.

Mais avant d'utiliser la fonction EQUIVnous allons faire quelques manipulations au niveau de ses arguments valeur cherchée et tableau de recherche pour qu’elle nous renvoie les valeurs souhaitées :

Fournir un nouveau critère de recherche

En partant des trois critères de recherche, nous allons nous servir de la fonction CONCAT pour les combiner à fin d’avoir un seul critère de recherche que nous allons introduire après comme valeur de recherche dans la fonction EQUIV.

  • Dans la cellule L2 de la feuille Rapport, entrez la formule suivante :

=CONCAT($E$4;$B$5;$B$3)

  • Ce qui donne : PhilippeFévrier2018

Note : CONCAT est une nouvelle fonction qui vient de remplacer la fonction CONCATENER, cette dernière ne sera plus disponible dans les prochaines versions d’Excel selon Microsoft.

Préparer le tableau de recherche

Nous allons créer tout d’abord une nouvelle colonne qui va afficher les trois valeurs concaténées : Vendeur, Mois et Année,  et dont nous nous servirons après pour créer une seconde colonne qui constituera le tableau de recherche (second argument de EQUIV):
  • Activez la feuille « Base de données » et choisissez la cellule M1 puis saisissez le texte "Valeurs Concaténées" comme entête de cette nouvelle colonne.
  • Dans la cellule M2, tapez la formule =CONCAT(B2;E2;F2)
  • Puis copiez-la vers le bas pour remplir toutes les cellules de cette nouvelle colonne.


Utilisation de la fonction CONCAT


Créer une clé unique pour chaque résultat trouvé

Vous savez que si nous effectuons une recherche avec INDEX+EQUIV classique, Excel va nous renvoyer seulement le premier résultat, alors et pour palier à ce problème et pour que notre formule de recherche nous renvoie tous les résultats trouvés, nous allons définir pour chacun de ces résultats une clé unique.

Compter le nombre d’occurrences d’une valeur cherchée

La première chose à faire avant la création des clés uniques est de créer une formule qui va compter combien de fois se répète une valeur cherchée dans la dernière colonne obtenue:
  • Dans la feuille "Base de données", sélectionnez la cellule L2 et tapez la formule suivante : =NB.SI($M$2:M2;M2) puis étirez vers le bas.
Regardez donc les résultats affichés, par exemple la valeur PhilippeFévrier2018 se répète 5 fois.


Compter le nombre d occurences dans une plage de cellules


Créer la clé unique

Modifiez la formule précédente en lui joignant le nom de chaque élément de la colonne « Valeurs Concaténées » pour créer cette clé.

  • La nouvelle formule que vous allez obtenir est la suivante :

=M2&"_"&NB.SI($M$2:M2;M2)

  • Excel affiche alors la valeur suivie de son nombre d’occurrence.
  • Copiez la formule vers le bas.


Créer une clé unique


Voilà donc, c’est cette colonne qui sera référencée comme tableau de recherche dans EQUIV.

Finaliser la formule EQUIV

Jusqu’à maintenant, les deux arguments valeur cherchée et tableau de recherche sont prêts.

  • Activez donc la feuille Rapport où nous allons utiliser la fonction EQUIV, et numérotez premièrement les lignes du tableau Rapport (31 lignes maximum) à partir de la cellule A9.
  • Dans la cellule B9, tapez donc : =EQUIV($L$2&"_"&A9;'Base de données'!$L$2:$L$72;0)
    • $L$2&"_"&A9 : joint le contenu de L2 (PhilippeFévrier2018) au numéro affiché dans A9 reliés tous les deux par Underscore, ce qui donne PhilippeFévrier2018_1.
    • 'Base de données'!$L$2:$L$72 : est la référence du tableau de recherche obtenu précédemment dans lequel effectuera Excel sa recherche.
    • 0 : c’est le dernier argument de EQUIV qui impose à Excel de renvoyer la valeur exacte.
  • En exécutant la formule, Excel renvoie le numéro 3.


EQUIV personnalisée



  • Copiez la formule vers le bas pour afficher les autres numéros : 5 résultats trouvés et 5 numéros renvoyés.



Plusieurs résultats renvoyés par EQUIV


Supper ! tout ce travail que nous avons mené jusqu’ici c’est pour arriver à obtenir ces numéros de lignes dont INDEX a besoin pour fonctionner d’une façon dynamique.

Afficher la date de vente :

Intégrons à présent la formule EQUIV dans INDEX pour effectuer la première recherche. Copiez la formule suivante dans B9 :

=INDEX('Base de données'!$A$2:$D$72;EQUIV($L$2&"_"&$A9;'Base de données'!$L$2:$L$72;0);1)


  • 'Base de données'!$A$2:$D$72 : est la référence de notre base de données (sans inclure les entêtes de colonnes et sans inclure les colonnes Mois et Année) où sera effectuée la recherche.
  • L'argument numéro de ligne sera renvoyé par EQUIV.
  • 1 est le numéro d’ordre de la colonne Date dans la base de données.
Excel affiche donc la date convenable.
  • Copiez la formule vers le bas pour obtenir les résultats restants.


Plusieurs résultats trouvés en effectuant la recherche avec INDEX et EQUIV


Note : définissez le format Date pour les cellules de la colonne Date, si la date ne s’affiche pas correctement.

Afficher les quantités vendues pour chaque smartphone


  • Copiez la formule index sur la cellule C9 puis sur D10.
  • Sous Iphone, modifiez le numéro de colonne en 3, et sous Samsung, entrez le numéro 4.
  • N’oubliez pas d’appliquer le format standard ou le format Nombre entier aux deux cellules.
  • Sélectionnez les deux cellules et double-cliquez sur la poignée de recopie.
  • Tous les résultats s’affichent bien.


Quantités des smartphones vendus



  • Effectuez d’autres sélections des critères de recherches et vérifiez si tout fonctionne comme souhaité.



Afficher les données de ventes en fonction des critères de recherche


Il nous reste ce message d’erreur #NA !

Pour vous débarrasser de ce message, vous pouvez utiliser la fonction SIERREUR pour le remplacer par une chaîne vide par exemple, ou bien, et ce qui est mieux, est d’utiliser la fonction SI.

Le tour que va jouer SI dans ce cas est d’interdire à Excel de continuer sa recherche quand le nombre de résultat trouvés est atteint.

  • Premièrement, nous allons compter le nombre de résultats trouvés :
    • Sélectionnez M2 dans la feuille Rapport et tapez la formule suivante :
=NB.SI('Base de données'!$M$2:$M$72;L2)
    • Remarquez qu'ici j’ai indiqué à NB.SI d’aller compter le nombre de la valeur affichée dans L2 (PhilippeFévrier2018) dans la colonne « Valeurs concaténées » : 'Base de données'!$M$2:$M$72.
  • Deuxièmement, nous allons créer une formule pour compter le nombre de lignes sélectionnées:
    • Testez par exemple cette formule =LIGNES($B$9:B9) en la tapant dans H1 et étirez vers le bas, que remarquez-vous ?


Compter le nombre de lignes


Revenons à notre formule de recherche

  • Sous Date, modifiez la formule de recherche en l’intégrant dans SI :
=SI(LIGNES($B$9:B9)<=$M$2;INDEX('Base de données'!$A$2:$D$72;EQUIV($L$2&"_"&$A9;'Base de données'!$L$2:$L$72;0);1);"")
    • Si le nombre de lignes renvoyé par LIGNES($B$9:B9) est inférieur ou égal au nombre de résultat affiché dans M2, effectuer la formule de recherche, si non afficher une chaîne vide.
    • Copiez la formule vers le bas.
  • Faites la même chose avec les colonnes Iphone et Samsung.


Se débarraser de l erreur NA


Génial ! n’est-ce pas ?!

Afficher le nombre d’Iphone vendu dans l’entête

  • Sélectionnez l’entête C8 puis tapez ceci :
="Iphone"&" ( "&SOMME($C$9:$C$39)&" )"

Afficher le nombre de Samsung vendu dans l’entête

  • Sélectionnez la cellule D8 puis tapez ceci :
="Samsung"&" ( "&SOMME($D$9:$D$39)&" )"


Afficher le total dans l entête de colonne


Retouche dernière

Cette dernière tâche consiste à faire masquer (et ne pas supprimer !!) tous ce que vous avez ajouté à vos deux feuilles de calcul pour revenir à l’affichage initial de votre classeur :
  • Sélectionnez vos cellules et vos plages de cellules concernées, et définissez une couleur de police blanche ou bien affichez la boite de dialogue Format de cellules et cliquez sur Personnaliser puis tapez ;;; dans la zone Type et validez.
Bravo vous avez pu faire un excellent travail !

Dernier mot, n’hésitez pas à partager cette solution à votre tour !


PARTAGEZ
    Blogger Comment
    Facebook Comment

27 commentaires:

  1. Bonsoir à vous,
    Un grand merci pour vos tutos clairs, précis et accessibles à tous.
    Ils me permettent, en plus des astuces du boulot communiqués tous les jeudis, de pousser encore plus loin l'utilisation d'Excel que je considère comme un jouet.
    Encore un grand merci à vous.
    Au plaisir de vous lire :)

    RépondreSupprimer
    Réponses
    1. Bonsoir,
      Merci pour votre réactivité. Très important votre commentaire.

      Supprimer
  2. Bonjour,
    j'ai un besoin spécifique et je suis sûr que vous devez avoir la solution.
    Ce que j'ai :
    Une base de données qui est une liste avec différentes colonnes qui contiennent les coordonnées d'une liste de personnes (nom, prénom, adresse, tel, mail, ...), la première colonne indique le groupe auquel ils appartiennent sous forme numérique.
    Ce que je voudrais obtenir :
    (Dans un une autre feuille) des tableaux (autant que de groupes) avec toutes les personnes qui se trouvent dans le même groupe et toutes leurs coordonnées (ou celles que j'aurais choisi).
    Ai-je été assez clair ?
    Pouvez-vous me sauver la vie ?
    Merci d'avance.
    Bien cordialement.

    RépondreSupprimer
    Réponses
    1. Je crois qu'un Tableau de croisé dynamique pourrait vous rendre service.
      Si vous voulez vous pouvez m'envoyer un exemple de votre fichier en utilisant le formulaire de contact en haut à droite pour vous donner une réponse convenable.

      Supprimer
  3. Bonsoir, Merci pour ce tuto bien intéressant.
    Cependant le fichier Rapport de Ventes est différent de ce qui est proposé dans le tuto.
    Point de départ 4 colonnes: Date,Vendeur, Smartphone, quantité.
    Puis cela change en Date, Vendeur, Samsung, Iphone. Quelle a été votre procédure pour arriver à ce nouveau tableau qui a mis en place les quantités de ventes des vendeurs par appareils sur deux colonnes.
    Bien merci et à bientôt
    Mexav

    RépondreSupprimer
    Réponses
    1. Bonsoir, merci de votre remarque, pas de procédure spéciale juste que je n'ai pas partagé le bon fichier. mais c'est corrigé maintenant. Salutations

      Supprimer
  4. Bonjour,
    J'ai testé votre fichier cependant je suis bloquée...
    au moment d'écrire la formule : =EQUIV($L$2&"_"&A9;'Base de données'!$L$2:$L$72;0)
    Je n'ai pas le résultat 3 comme vous... mais #N/A
    Pouvez - vous me débloquer?
    Merci infiniement.


    RépondreSupprimer
    Réponses
    1. Bonjour,
      Vérifiez si vous avez tapé un numéro dans la cellule A9.
      Est-ce L2 affiche la bonne valeur

      Supprimer
    2. bonjour merci pour vos tuto! par contre j ai le meme soucis que abdo. pouvez vous m aidé? (concat ne fonctionne pas chez moi j ai du faire concatener c est peu etre pour ca...)

      Supprimer
    3. Si vous ne disposez pas de la dernière version d'excel utilisez la fonction Concatener au lieu de Concat

      Supprimer
  5. Je voulais vous remercier !
    Grace à vos explication (ben oui il suffit de bien lire... ce que je ne fais pas toujours comme il faut) je viens de mettre en place mon tableau avec plus de 2500 lignes de données! et ça marche! Des jours que je faisais du copier coller pour chaque ligne...
    Grace à vous j'ai gagné un temps fou!
    Merci pour ce partage de tuto !

    RépondreSupprimer
    Réponses
    1. Bon effort que vous avez pu effectuer! Je vous félicite ! Bravo!

      Supprimer
  6. Bonsoir,
    Je vous remercie beaucoup pour tout ce que vous faites pour bous aider, et et suis sûre chaque jour que Dieu vous allez aller toujours en avant, ma prière est que Dieu vous accompagne dans chaque vers la victoire et le succes.
    S'il vous plat excusez moi pour ma question hors de sujet; ma question est:
    1- Quels sont les différents niveau de la maitrise d'Excel?
    2- ou se limite chaque niveau? pour un débutant; un intermédiaire etc.... ?
    aider moi. Merci d'avance!!!

    RépondreSupprimer
    Réponses
    1. Bonjour,
      Je vous remercie pour votre prière.
      Pour les niveaux, je vous propose ce lien pour découvrir les trois niveaux et les compétences à acquérir.
      https://www.kelformation.com/editorial/formation-continue/conseils/detail/article/quel-est-votre-vrai-niveau-en-informatique0.html

      Supprimer
  7. Bonjour
    J'ai testé votre fichier cependant Avec la formule =EQUIV($L$2&"_"&A9;'Base de données'!$L$2:$L$72;0)
    Je n'ai pas le résultat 3 comme vous... mais plutôt: "mardi 3 janvier 1900", en B10 j'ai : "samedi 7 janvier 1900", en B11 j'ai :"lundi 9 janvier 1900", en B12, j'ai : "vendredi 13 janvier 1900", en B13, j'ai : "dimanche 15 Janvier 1900".
    J'obtiens donc les chiffres que vous avez trouvé (3, 7, 9, 13, 15) mais sous forme de date. Je ne comprends pas cela. Pouvez vous m'éclairer SVP. J'utilise Excel 2019.
    Merci infiniement

    RépondreSupprimer
    Réponses
    1. Bonjour,
      Modifiez le format de la cellule, il est en date, mettez-le en Standard

      Supprimer
  8. Vraiment super cool votre tutoriel. L'explication est très clair étape par étape. Vraiment merci grandement pour ce geste

    RépondreSupprimer
  9. Super page ! J'ai pu transposer cette méthode à mon cas avec aisance.
    Je souhaiterai cependant comprendre un peu mieux l'utilisation des fonction INDEX et EQUIV que je ne comprends pas bien. EQUIV permet d'isoler une valeur d'une ligne et à partir de cela INDEX permet d'isoler une valeur rattache à cette précédente valeur (ligne) ?
    Merci pour ce partage.
    Zora.

    RépondreSupprimer
    Réponses
    1. Je vous en prie.
      Voici des liens qui vont vous aider à mieux comprendre les deux fonctions INDEX et EQUIV:

      - https://cutt.ly/fonctionindex

      - https://cutt.ly/IndexetEquiv

      - https://cutt.ly/IndexetEquiv

      Supprimer
  10. dans mon cas, j'utilise microsoft forms afin d'alimenter un tableau pour les heures mensuelles d'astreinte.
    J'avais un soucis avec la formule pour la clé dans la base de données.
    j'ai du remplacer la formule :
    =AB2&"_"&NB.SI($AB$2:AB2;AB2)
    par
    =AB2&"_"&NB.SI($AB$2:(AB2);AB2)

    car la cellule AB2 prenait la valeur de dernier ligne du tableau lors d'un ajout d'une réponse du formulaire.

    RépondreSupprimer
  11. Super tous vos cours trop bien et bien explicite

    RépondreSupprimer

Votre commentaire m'intéresse beaucoup :)