Techniques avancées Excel
Faire une recherche avec plusieurs critères !

Apprenez les différentes possibilités pour effectuer des recherches en fonction de plusieurs critères.

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.

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 ».

  • Utilisable uniquement pour ressortir des valeurs numériques.

  • 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 :

  1. La colonne cible (colonne contenant l’information que vous souhaitez faire ressortir)
  2. Le critère 1
  3. La colonne dans laquelle est contenu le critère 1
  4. Le critère 2
  5. La colonne dans laquelle est contenu le critère 2
  6. 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/2017
'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.

Notez nous !
[Moyenne : 4.8]
2020-05-21T18:22:25+00:00