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.

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.

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.

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 :

• Téléchargez tout d’abord le fichier exemple: Rapport de vente.xlsx

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.

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 EQUIV, nous 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.

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.

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.

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.

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

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.

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.

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

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 ?

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.

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)&" )"

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 PARTAGEZ PARTAGEZ