Comment Excel peut-il RECHERCHER plusieurs valeurs dans un tableau ?

Excel : RECHERCHER plusieurs valeurs dans un tableau – Les méthodes efficaces

La recherche de plusieurs valeurs dans un tableau Excel est une compétence essentielle pour l'analyse de données. Elle permet d'extraire rapidement les informations pertinentes correspondant à des critères spécifiques. Plusieurs méthodes peuvent être utilisées, chacune ayant ses avantages et ses inconvénients. Nous allons explorer les plus courantes et vous montrer comment les appliquer concrètement.

1. Utilisation de la fonction RECHERCHEV combinée à INDEX et PETITE.VALEUR

La fonction RECHERCHEV est idéale pour rechercher une valeur unique dans une colonne et renvoyer une valeur correspondante d'une autre colonne. Cependant, pour rechercher plusieurs valeurs, elle devient insuffisante. Nous allons donc la combiner avec les fonctions INDEX et PETITE.VALEUR pour obtenir le résultat souhaité.

Explication des fonctions:

• RECHERCHEV(valeur_recherchée, table_matrice, no_index_col, [valeur_proche]): Recherche une valeur dans la première colonne d'une table et renvoie une valeur de la même ligne dans une colonne spécifiée.
• INDEX(matrice, no_ligne, [no_colonne]): Renvoie la valeur d'une cellule dans une matrice, en spécifiant le numéro de ligne et de colonne.
• PETITE.VALEUR(matrice, k): Renvoie la k-ième plus petite valeur d'une matrice.
• LIGNE(référence): Renvoie le numéro de ligne d'une référence.
• SI(test_logique, [valeur_si_vrai], [valeur_si_faux]): Effectue un test logique et renvoie une valeur si le test est vrai, et une autre si le test est faux.
• ESTNUM(valeur): Vérifie si une valeur est un nombre.

Étapes et formule:

1. Créer une colonne auxiliaire: Cette colonne va nous permettre d'identifier les lignes correspondant à notre critère de recherche. Dans cette colonne, entrez la formule suivante (en adaptant les références aux cellules de votre tableau) : =SI(A2="critère_recherché",LIGNE(A2),""). Remplacez A2 par la première cellule de la colonne où vous recherchez votre critère, et "critère_recherché" par la valeur que vous recherchez. Cette formule renvoie le numéro de ligne si la condition est vraie, et une chaîne vide sinon.
2. Utiliser INDEX et PETITE.VALEUR: Pour extraire les valeurs correspondantes, utilisez la formule suivante : =SIERREUR(INDEX(colonne_à_renvoyer,PETITE.VALEUR(colonne_auxiliaire,LIGNE(A1))),""). Remplacez colonne_à_renvoyer par la plage de cellules contenant les valeurs que vous souhaitez extraire, colonne_auxiliaire par la plage de cellules de votre colonne auxiliaire, et A1 par la première cellule vide où vous entrez la formule.
3. Tirer la formule vers le bas: Tirez la formule vers le bas pour extraire toutes les valeurs correspondantes. La fonction SIERREUR permet de gérer les erreurs lorsque toutes les valeurs correspondantes ont été extraites.

Exemple pratique:

Supposons que vous ayez un tableau avec une colonne "Nom" (A2:A10) et une colonne "Ville" (B2:B10). Vous souhaitez trouver toutes les villes associées au nom "Jean".

1. Dans la colonne C (C2:C10), entrez la formule: =SI(A2="Jean",LIGNE(A2),"").
2. Dans une cellule vide (par exemple, D2), entrez la formule: =SIERREUR(INDEX(B$2:B$10,PETITE.VALEUR(C$2:C$10,LIGNE(A1))),"").
3. Tirez la formule de D2 vers le bas pour obtenir toutes les villes associées à Jean.

Avantages:

• Méthode relativement simple à comprendre.
• Ne nécessite pas de VBA.

Inconvénients:

• Nécessite une colonne auxiliaire.
• Peut être moins performante sur de très grands tableaux.

2. Utilisation de la fonction FILTRE (Excel 365 et versions ultérieures)

La fonction FILTRE est une fonction puissante introduite dans Excel 365 et les versions ultérieures. Elle permet de filtrer une plage de données en fonction d'un ou plusieurs critères, et de renvoyer directement les résultats correspondants.

Explication de la fonction:

• FILTRE(matrice, inclure, [si_vide]): Filtre une plage de données en fonction d'un critère et renvoie les résultats correspondants. matrice est la plage de données à filtrer, inclure est le critère de filtrage, et si_vide est la valeur à renvoyer si aucun résultat ne correspond au critère.

Étapes et formule:

1. Utiliser la fonction FILTRE: Entrez la formule suivante dans une cellule vide: =FILTRE(colonne_à_renvoyer,colonne_de_critères=critère_recherché,"Aucun résultat"). Remplacez colonne_à_renvoyer par la plage de cellules contenant les valeurs que vous souhaitez extraire, colonne_de_critères par la plage de cellules où vous recherchez votre critère, et critère_recherché par la valeur que vous recherchez. "Aucun résultat" est la valeur qui sera affichée si aucun résultat ne correspond au critère.

Exemple pratique:

Reprenons l'exemple précédent avec les colonnes "Nom" (A2:A10) et "Ville" (B2:B10). Vous souhaitez trouver toutes les villes associées au nom "Jean".

1. Dans une cellule vide (par exemple, C2), entrez la formule: =FILTRE(B2:B10,A2:A10="Jean","Aucun résultat").

Avantages:

• Formule simple et concise.
• Ne nécessite pas de colonne auxiliaire.
• Plus performante que la méthode INDEX/PETITE.VALEUR sur de grands tableaux.

Inconvénients:

• Uniquement disponible dans Excel 365 et versions ultérieures.

3. Utilisation de Power Query (Get & Transform Data)

Power Query est un outil puissant intégré à Excel qui permet d'importer, de transformer et de charger des données provenant de différentes sources. Il peut également être utilisé pour rechercher plusieurs valeurs dans un tableau.

Étapes:

1. Importer les données dans Power Query: Sélectionnez votre tableau de données et allez dans l'onglet "Données", puis cliquez sur "From Table/Range".
2. Filtrer les données: Dans l'éditeur Power Query, sélectionnez la colonne contenant le critère de recherche. Cliquez sur la flèche de filtre, puis choisissez "Text Filters" et "Equals". Entrez le critère de recherche.
3. Charger les résultats dans Excel: Cliquez sur "Close & Load" pour charger les résultats filtrés dans une nouvelle feuille de calcul Excel.

Avantages:

• Permet de gérer des données provenant de différentes sources.
• Interface graphique intuitive.
• Possibilité d'automatiser le processus de recherche et de transformation des données.

Inconvénients:

• Courbe d'apprentissage plus importante que les méthodes précédentes.

4. Utilisation de VBA (Visual Basic for Applications)

Le VBA est un langage de programmation intégré à Excel qui permet d'automatiser des tâches complexes. Il peut être utilisé pour créer une macro qui recherche plusieurs valeurs dans un tableau et les affiche dans une autre feuille de calcul.

Exemple de code VBA:

Sub RechercherMultiplesValeurs()
 Dim wsSource As Worksheet, wsDestination As Worksheet
 Dim LastRow As Long, i As Long, j As Long
 Dim Critere As String, Resultat As Range

 'Définir les feuilles de calcul source et destination
 Set wsSource = ThisWorkbook.Sheets("Feuil1") 'Modifier "Feuil1" par le nom de votre feuille source
 Set wsDestination = ThisWorkbook.Sheets("Feuil2") 'Modifier "Feuil2" par le nom de votre feuille destination

 'Définir le critère de recherche
 Critere = InputBox("Entrez le critère de recherche:")

 'Trouver la dernière ligne de la feuille source
 LastRow = wsSource.Cells(Rows.Count, 1).End(xlUp).Row

 'Initialiser le compteur de résultats
 j = 1

 'Parcourir les lignes de la feuille source
 For i = 2 To LastRow 'Commencer à la ligne 2 pour ignorer les en-têtes
 If wsSource.Cells(i, 1).Value = Critere Then 'Modifier 1 par le numéro de la colonne contenant le critère
 'Copier la ligne correspondante vers la feuille destination
 wsSource.Rows(i).Copy wsDestination.Rows(j)
 j = j + 1
 End If
 Next i

 MsgBox "Recherche terminée!"
End Sub

Étapes:

1. Ouvrir l'éditeur VBA: Appuyez sur Alt + F11.
2. Insérer un module: Dans l'éditeur VBA, allez dans "Insert" puis "Module".
3. Coller le code VBA: Collez le code VBA dans le module. Modifiez les noms des feuilles et le numéro de colonne selon vos besoins.
4. Exécuter la macro: Appuyez sur F5 pour exécuter la macro. Une boîte de dialogue vous demandera d'entrer le critère de recherche.

Avantages:

• Flexibilité maximale.
• Possibilité d'automatiser des tâches complexes.

Inconvénients:

• Nécessite des connaissances en VBA.
• Peut être plus lent que les autres méthodes sur de très grands tableaux.

Choisir la méthode appropriée

Le choix de la méthode appropriée dépend de plusieurs facteurs:

• Version d'Excel: Si vous utilisez Excel 365 ou une version ultérieure, la fonction FILTRE est la méthode la plus simple et la plus performante.
• Taille du tableau: Pour les petits tableaux, la méthode INDEX/PETITE.VALEUR peut être suffisante. Pour les grands tableaux, la fonction FILTRE ou Power Query sont préférables.
• Complexité des critères: Si vous avez des critères de recherche complexes, Power Query ou VBA peuvent être nécessaires.
• Niveau de compétence: Si vous n'êtes pas familier avec VBA, il est préférable d'utiliser les méthodes basées sur les fonctions Excel.

Conseils et astuces supplémentaires

• Utiliser des noms de plages: Au lieu d'utiliser des références de cellules, vous pouvez définir des noms de plages pour rendre vos formules plus lisibles et plus faciles à maintenir.
• Utiliser la validation des données: Pour éviter les erreurs de saisie, vous pouvez utiliser la validation des données pour limiter les valeurs autorisées dans les cellules contenant les critères de recherche.
• Combiner plusieurs critères: Vous pouvez combiner plusieurs critères de recherche en utilisant les fonctions ET et OU dans vos formules.
• Gérer les erreurs: Utilisez la fonction SIERREUR pour gérer les erreurs potentielles dans vos formules.

En conclusion, Excel offre de nombreuses possibilités pour rechercher plusieurs valeurs dans un tableau. En choisissant la méthode appropriée et en suivant les conseils présentés dans cet article, vous pourrez extraire rapidement et efficacement les informations dont vous avez besoin pour vos analyses de données.