Techniques avancées Excel
Faire une recherche Excel avec plusieurs critères !
Apprenez les différentes possibilités pour effectuer des recherches en fonction de plusieurs critères avec Excel.
Plusieurs techniques
Certaines analyses nécessitent de ressortir des informations en se basant sur plusieurs critères ou informations.
La formule RECHERCHEV classique ne permet cependant d’utiliser qu’un seul argument rendant la recherche parfois assez contraignante et limitée. Les fonctions de recherche d’Excel ne permettent donc pas de le faire directement.
Nous vous proposons donc quelques méthodes pour effectuer une recherche avec plusieurs critères.
Concaténation avec la fonction RECHERCHEV
L’idée est d’utiliser une RECHERCHEV classique. Cette formule ne fonctionnant que sur 1 seul critère de recherche à la fois, il faut donc ruser en transformant les multiples critères pour n’en avoir qu’un seul.
Pour ce faire nous pouvons utiliser soit la formule « CONCATENER » ou le « & » (=A1&B1&…).
Avantages
Facile et rapide à mettre en place.
Lisible pour une autre personne.
Très fiable si bien construite.
Inconvénients
Ajout d’informations « doublons ».
Une colonne de concaténation doit être ajoutée en première colonne de votre base.
Concaténation avec INDEX et EQUIV
La méthode est la même que la précédente en combinant plusieurs critères de recherche pour n’en faire qu’un seul. À la différence de la première, le INDEXEQUIV permet de placer la colonne contenant les critères assemblés n’importe ou dans votre tableau au lieu d’en première position.
Pour apprendre à utiliser cette combinaison de formules, nous vous invitons à lire l’article correspondant.
Avantages
Facile à mettre en place.
Très fiable si bien construite.
Inconvénients
Peu lisible pour une personne ne connaissant pas les formules INDEX et EQUIV.
Ajout d’informations « doublons ».
Alourdi le fichier à cause des formules.
La formule SOMME.SI.ENS
La formule SOMME.SI.ENS permet de faire une somme en utilisant plusieurs critères. Mais cette formule peut être utilisée pour faire ressortir une information numérique en utilisant plusieurs critères non-numériques.
Avantages
Très rapide à mettre en place.
Pas d’ajout de colonne dans votre tableau de données.
Facilement lisible et compréhensible pour une tiers personne.
Inconvénients
Les critères doivent être suffisamment spécifiques pour éviter que la formule ne fasse la somme sur plusieurs lignes.
Utilisable uniquement pour ressortir des valeurs numériques.
RECHERCHEV avec plusieurs critères
Nous vous proposons ci-dessous une fonction permettant d’utiliser une RECHERCHEV prenant en compte entre 2 et 5 critères différents.
Pour l’utiliser il suffit simplement d’ouvrir l’éditeur VBA de votre fichier Excel, puis de copier/coller le code dans un module.
La fonction s’utilise ensuite comme n’importe quelle autre formule en inscrivant : =RECHERCHEVENS.
L’ordre des arguments à compléter est le suivant :
- La colonne cible (colonne contenant l’information que vous souhaitez faire ressortir),
- Le critère 1,
- La colonne dans laquelle est contenu le critère 1,
- Le critère 2,
- La colonne dans laquelle est contenu le critère 2,
- Etc…
Pensez à enregistrer votre fichier au format prenant en charge les macros afin de conserver la formule.
Function RECHERCHEVENS(ColonneValeur As Range, Critere1 As Variant, PlageRecherche1 As Variant, Critere2 As Variant, PlageRecherche2 As Variant, _ Optional Critere3 As Variant, Optional PlageRecherche3 As Variant, _ Optional Critere4 As Variant, Optional PlageRecherche4 As Variant, _ Optional Critere5 As Variant, Optional PlageRecherche5 As Variant) 'Auteur : www.Indexmatch.fr 'Date : 08/2021 'fonction de rechercheV avec plusieurs critères : minimum 2 critères / maximum 5 critères Dim R1 As Variant 'variable pour le numéro de colonne du critère 1 Dim R2 As Variant 'variable pour le numéro de colonne du critère 2 Dim RF As Variant 'variable pour le numéro de colonne du critère 3 Dim R3 As Variant 'variable pour le numéro de colonne du critère 3 Dim R4 As Variant 'variable pour le numéro de colonne du critère 4 Dim R5 As Variant 'variable pour le numéro de colonne du critère 5 Dim RS1 As String 'variable pour feuille du critère 1 Dim RS2 As String 'variable pour feuille du critère 2 Dim RSF As String 'variable pour feuille du critère 3 Dim RS3 As String 'variable pour feuille du critère 3 Dim RS4 As String 'variable pour feuille du critère 4 Dim RS5 As String 'variable pour feuille du critère 5 Dim counter As Integer 'variable de compteur Dim lastLine As Long 'variable pour définir la dernière ligne à traiter R1 = PlageRecherche1.Column R2 = PlageRecherche2.Column RF = ColonneValeur.Column RS1 = PlageRecherche1.Worksheet.Name RS2 = PlageRecherche2.Worksheet.Name RSF = ColonneValeur.Worksheet.Name counter = 0 lastLine = Sheets(RS1).Cells(PlageRecherche1.Row, R1).End(xlDown).Row ' Avec 5 critères ----------- If IsMissing(Critere5) = False And IsMissing(PlageRecherche5) = False Then R3 = PlageRecherche3.Column R4 = PlageRecherche4.Column R5 = PlageRecherche5.Column RS3 = PlageRecherche3.Worksheet.Name RS4 = PlageRecherche4.Worksheet.Name RS5 = PlageRecherche5.Worksheet.Name If Critere1 = "" Or Critere2 = "" Or Critere3 = "" Or Critere4 = "" Or Critere5 = "" Then RECHERCHEVENS = "#VALEUR" Else Do While counter <= lastLine counter = counter + 1 If Sheets(RS1).Cells(counter, R1).Value = Critere1 And Sheets(RS2).Cells(counter, R2).Value = Critere2 And Sheets(RS3).Cells(counter, R3).Value = Critere3 And _ Sheets(RS4).Cells(counter, R4).Value = Critere4 And Sheets(RS5).Cells(counter, R5).Value = Critere5 Then If IsError(Sheets(RSF).Cells(counter, RF).Value) = True Then RECHERCHEVENS = "#N/A" Else RECHERCHEVENS = Sheets(RSF).Cells(counter, RF).Value End If End If Loop End If End If '4 criteres ----------- If IsMissing(Critere5) = True And IsMissing(PlageRecherche5) = True And IsMissing(Critere4) = False And IsMissing(PlageRecherche4) = False Then R3 = PlageRecherche3.Column 'optional R4 = PlageRecherche4.Column 'optional RS3 = PlageRecherche3.Worksheet.Name 'optional RS4 = PlageRecherche4.Worksheet.Name 'optional If Critere1 = "" Or Critere2 = "" Or Critere3 = "" Or Critere4 = "" Then RECHERCHEVENS = "#VALEUR" Else Do While counter <= lastLine counter = counter + 1 If Sheets(RS1).Cells(counter, R1).Value = Critere1 And Sheets(RS2).Cells(counter, R2).Value = Critere2 And Sheets(RS3).Cells(counter, R3).Value = Critere3 And _ Sheets(RS4).Cells(counter, R4).Value = Critere4 Then If IsError(Sheets(RSF).Cells(counter, RF).Value) = True Then RECHERCHEVENS = "#N/A" Else RECHERCHEVENS = Sheets(RSF).Cells(counter, RF).Value End If End If Loop End If End If ' Avec 3 critères ----------- If IsMissing(Critere5) = True And IsMissing(PlageRecherche5) = True And IsMissing(Critere4) = True And IsMissing(PlageRecherche4) = True And _ IsMissing(Critere3) = False And IsMissing(PlageRecherche3) = False Then R3 = PlageRecherche3.Column RS3 = PlageRecherche3.Worksheet.Name If Critere1 = "" Or Critere2 = "" Or Critere3 = "" Then RECHERCHEVENS = "#VALEUR" Else Do While counter <= lastLine counter = counter + 1 If Sheets(RS1).Cells(counter, R1).Value = Critere1 And Sheets(RS2).Cells(counter, R2).Value = Critere2 And Sheets(RS3).Cells(counter, R3).Value = Critere3 Then If IsError(Sheets(RSF).Cells(counter, RF).Value) = True Then RECHERCHEVENS = "#N/A" Else RECHERCHEVENS = Sheets(RSF).Cells(counter, RF).Value End If End If Loop End If End If ' Avec 2 critères ----------- If IsMissing(Critere5) = True And IsMissing(PlageRecherche5) = True And IsMissing(Critere4) = True And IsMissing(PlageRecherche4) = True And _ IsMissing(Critere3) = True And IsMissing(PlageRecherche3) = True Then If Critere1 = "" Or Critere2 = "" Then RECHERCHEVENS = "#VALEUR" End If Do While counter <= lastLine counter = counter + 1 If Sheets(RS1).Cells(counter, R1).Value = Critere1 And Sheets(RS2).Cells(counter, R2).Value = Critere2 Then If IsError(Sheets(RSF).Cells(counter, RF).Value) = True Then RECHERCHEVENS = "#N/A" Else RECHERCHEVENS = Sheets(RSF).Cells(counter, RF).Value End If End If Loop End If End Function |
Lire le prochain article pour apprendre à utiliser la formule index/equiv.