Recherche sur plusieurs critères dans une table, par formule ou en VBA

Comment trouver une cellule répondant à plusieurs conditions dans un tableau ?

Retrouvez les exemples traités dans cette page dans le classeur associé.

Plusieurs fonctions intégrées d'excel permettent de rechercher des cellules répondant à un critère.
Les plus utiles pour cela sont RECHERCHEV RECHERCHEH et la combinaison magique INDEX + EQUIV qui, contrairement à RECHERCHEV n'est pas limitée  par l'emplacement ou l'ordre des colonnes.

Dans le tableau ci-dessous, le montant (8765) de la commande passée chez Qiagen peut être trouvé par les formules suivantes :
=RECHERCHEV("qiagen";tableau;3;0)
=INDEX(montant;EQUIV("qiagen";fournisseur;0))

Mais comment faire, pas formule ou par VBA pour trouver le montant de la commande passée à une date donnée chez JPG ?
Pour comprendre les formules utilisées dans cette page vous devez au préalable connaître quelques fonctions de base d'excel : INDEX, EQUIV, PETITE.VALEUR, et MIN et savoir ce qu'est une fonction matricielle.

Si vous voulez faire ce genre d'interrogation dans une macro, la fonction EVALUATE est la clef.

  A B C
1 FOURNISSEUR DATE COMMANDE MONTANT
2 ABCAM 03/04/2009 320
3 APPLERA 02/03/2009 765
4 APPLERA 21/03/2009 1984
5 APPLERA 23/04/2009 367
6 ATGC 02/05/2009 1257
7 DARTY 21/02/2009 324
8 EPPENDORF 09/01/1900 2876
9 EPPENDORF 21/02/2009 298
9 EUROMEDEX 04/04/2009 974
10 FEDEX 21/01/2009 42
11 FEDEX 03/03/2009 42
12 INVIVOGEN 04/04/2009 320
13 JPG 21/01/2009 832
14 JPG 21/01/2009 143
15 JPG 21/02/2009 231
16 JPG 02/05/2009 256
17 JPG 04/05/2009 143
18 JPG 05/06/2009 143
19 QIAGEN 03/04/2009 8765
20 SIGMA 21/01/2009 234
21 SIGMA 03/04/2009 21
22 STARLAB 22/02/2009 129
23 STARLAB 03/05/2009 378
24 TEBU 02/05/2009 143
25 TRANSCOURRIER 01/02/2009 21

Ce tableau est ici trié en fonction du nom du fournisseur mais il pourrait l'être suivant un autre critère ou même ne pas être trié du tout.
Nous allons voir comment trouver des informations en fonction de plusieurs critères imposés. 

Par formule dans la feuille de calcul

Pour simplifier les formules, les plages sont nommées (insertion/nom/définir ou onglet formules/gestionnaire de noms). Vous pouvez les définir de façon dynamique avec la fonction DECALER pour que les noms s'ajustent si vous ajoutez des données à votre tableau.
 Attention suivant votre résolution d'écran certaines formules peuvent apparaître sur plusieurs lignes. Elles doivent être saisies sur une seule.
Validation matricielle : Pour indiquer à excel qu'il doit travailler avec des plages de données (ou matrices) et non pas avec la valeur d'une seule cellule, il faut utiliser une validation dite matricielle une fois qu'on a entré la formule. Cliquez dans une cellule, saisissez la formule, appuyez simultanément sur les touches ctrl et majuscule et tout en les maintenant enfoncées, appuyez sur enter. Comme on dit sur le MPFE, les matricielles ne sont pas indiquées pour les chirogourdistes qui pourraient attraper des crampes :-)

Le N° d'index dans le tableau de la ligne ou fournisseur= SIGMA et montant = 21

=EQUIV(1;(fournisseur="sigma")*(montant=21);0) Validation matricielle
réponse = 21

Explications :
La saisie matricielle de cette formule renvoie un tableau de valeurs qui évalue ligne par ligne les deux conditions :
(fournisseur = sigma ?) et (montant = 21 ?)
renvoie pour chacune vrai (= 1) ou faux (= 0) et fait le produit de ces deux résultats.
Ainsi JPG/21 renvoie 0 x 0 = 0
Sigma/234 renvoie 1 x 0 = 0
Transcourrier/21 renvoie 0 x 1 = 0
sigma/21 renvoie 1 x 1 = 1

EQUIV(1;test_matriciel;0) renvoie l'index (N° d'ordre dans la plage) de la ligne (sigma/21) qui renvoie 1 (en fait VRAI) dans le test. Le 0 à la fin indique qu'on cherche une valeur exacte (ici 1), pour ce paramètre, voir sur l'aide en ligne ou sur ce site.

Attention, si il y a deux couples Sigma/21, seule la première ligne contenant ce couple est retenu.
Si vous voulez simplement compter le nombre de lignes pour lesquelles les deux critères sont remplis, il vous suffit de faire la somme matricielle suivante :
= somme((fournisseur="sigma")*(montant=21)) Validation matricielle
Que vous pouvez également remplacer par
= sommeprod((fournisseur="sigma")*(montant=21)) qui n'a pas besoin de validation matricielle.
En effet sommeprod est une des fonctions de feuille d'excel qui travaille par défaut sur des plages de cellules.

La date correspondant à fournisseur= SIGMA et montant = 21

=INDEX(date_commande;EQUIV(1;(fournisseur="sigma")*(montant=21);0)) Validation matricielle

réponse = 03/04/2009
On injecte dans la fonction index le numéro de la ligne trouvé par la fonction précédente (voir la page sur la combinaison index+equiv pour plus de détails).

La date de la troisième commande chez JPEG

=INDEX(date_commande;PETITE.VALEUR(SI(fournisseur="jpg";LIGNE(date_commande)-MIN(LIGNE(date_commande))+1);3)) Validation matricielle
réponse = 21/02/2009

Explications :
Le test si(fournisseurs="jpg") saisi en matriciel, renvoie  un tableau de valeurs vrai ou faux.
Si le test est vrai, on renvoie le numéro des lignes où la condition est remplie. L'expression (-min(ligne(plage))+1) sert simplement à ajuster ce n° de ligne pour que le calcul fonctionne quel que soit l'endroit de la feuille où se trouve le tableau de données traitées.
Cette partie renvoie donc un tableau de numéros de ligne répondant à la condition.

Petite valeur(valeurs;n) renvoie la nième valeur d'un tableau de données. On lui passe donc en paramètre le tableau des numéros de ligne répondant à la condition.
0n récupère ensuite avec index le contenu de la cellule située dans le rang ainsi trouvé dans la plage date_commande.

A partir de là vous devez pouvoir décortiquer tout le reste et faire vos propres combinaisons !

Date de la première commande de 143 euros chez JPG

=INDEX(date_commande;EQUIV(1;(fournisseur="jpg")*(montant=143);0)) Validation matricielle
réponse = 21/01/2009

Date de la deuxième commande de 143 euros chez JPG

=INDEX(date_commande;PETITE.VALEUR(SI((fournisseur="jpg")*(montant=143)=1;LIGNE(date_commande)-MIN(LIGNE(date_commande))+1);2))
Validation matricielle
réponse = 04/05/2009

Par macro

Comment écrire une fonction matricielle en VBA ?
Si vous voulez affecter le résultat dans une cellule de calcul, vous pouvez utiliser l'écriture macellule.FormulaArray = ...
Mais vous pouvez avoir simplement besoin d'utiliser le résultat d'un calcul matriciel pour poursuivre votre macro. Dans ce cas, la fonction magique c'est EVALUATE.
Cette fonction crée en quelque sorte une "cellule virtuelle" dans lequel s'effectue le calcul matriciel.
Comme son nom l'indique, elle évalue le résultat d'une expression, qui dans ce cas est écrite comme un simple texte, entre guillemets.
Comme nous sommes dans VBA, il faut bien entendu convertir les fonctions de feuille écrites en français en anglais et penser à remplacer les point-virgules par des virgules pour séparer les arguments des fonctions.
Quelques petites subtilités encore à ne pas oublier par rapport aux guillemets qu'il faut doubler à l'intérieur de la chaine de texte à évaluer et nous y sommes.
Voilà donc la traduction en VBA des formules ci-dessus.

Le N° d'index dans le tableau de la ligne ou fournisseur= SIGMA et montant = 21

Evaluate("MATCH(1,(fournisseur=""sigma"")*(montant=21),0)")

La date correspondant à fournisseur= SIGMA et montant = 21

Evaluate("INDEX(date_commande,match(1,(fournisseur=""sigma"")*(montant=21),0))")

La date de la troisième commande chez JPEG

Evaluate("=INDEX(date_commande,small(if(fournisseur=""jpg"",row(date_commande)-MIN(row(date_commande))+1),3))")

Le nombre de commandes de 143 euros chez JPG

Evaluate("=sum((fournisseur=""jpg"")*(montant=143))")

ou bien encore

Evaluate("SumProduct((fournisseur = ""jpg"") * (montant = 143))")

Date de la première commande de 143 euros chez JPG

Range("K21") = Evaluate("=index(date_commande,match(1,(fournisseur=""jpg"")*(montant=143),0))")

Date de la deuxième commande de 143 euros chez JPG

Range("K22") = Evaluate("=index(date_commande,small(if((fournisseur=""jpg"")*(montant=143)=1, _
row(date_commande)-MIN(row(date_commande))+1),2))")

Utiliser des variables dans les fonctions matricielles en VBA

Au lieu de JPG ou de 143, vous serez probablement amenés dans une macro à remplacer ces valeurs par des variables comme monfournisseur et monmontant.
La ligne

Evaluate("=sum((fournisseur=""jpg"")*(montant=143))")

devient alors

Evaluate("=sum((fournisseur=" & monfournisseur & ")*(montant=" & monmontant & "))")
Auteur(s) : 

Commentaires

Merci bien pour ce sujet, c'est intéressant

Bien fait merci

Cells(2, 15) = FormulaLocal = "=somme(cells(C,8):cells(L,8))"
TextBox3 = Cells(2, 15).Text

pour quoi ca mache pas si je prnds un plages de lignes dans la colonne ca fonctionne j'ia l'impression que les references ne fonctionnent pas??????

Je prends le temps de rédiger les tutoriels, avec un minimum de fautes d'orthographe, prenez celui de dire bonjour et de poser votre question de façon compréhensible.
Par ailleurs, ça ne présente aucun intérêt d'écrire par macro une formule dans une feuille de calcul. Faites le calcul dans la macro (comme dans tous les exemples donnés dans cette page) et écrivez le résultat dans la feuille ou dans le textbox en l'occurence.
textbox3= Application.WorksheetFunction.Sum(Range("A1:A3"))

Merci tout d'bord pour ces exemples qui fonctionnent très bien. malheuresement, je n'arrive pas à faire le traitement avec des variables par macro. J'ai ajouter ceci dans le code du fichier exemple:

monfournisseur = Range("M3")
monmontant = Range("M4")

Et remplacé 'Evaluate("=sum((fournisseur=""jpg"")*(montant=143))")' par 'Evaluate("=sum((fournisseur=" & monfournisseur & ")*(montant=" & monmontant & "))")'
Excel me dit dans la case K18 de l'exemple #NOM?

Comment régler ce soucis?


Ce mois-ci sur Excelabo

- Pas de nouvelle page.
- 2 pages modifiées.