Analyse des données Excel - Fonctions de recherche
Vous pouvez utiliser les fonctions Excel pour -
- Rechercher des valeurs dans une plage de données - RECHERCHEV et RECHERCHEH
- Obtenir une valeur ou la référence à une valeur à partir d'une table ou d'une plage - INDEX
- Obtenir la position relative d'un élément spécifié dans une plage de cellules - MATCH
Vous pouvez également combiner ces fonctions pour obtenir les résultats requis en fonction des entrées dont vous disposez.
Utilisation de la fonction RECHERCHEV
La syntaxe de la fonction RECHERCHEV est
VLOOKUP (lookup_value, table_array, col_index_num, [range_lookup])Où
lookup_value- est la valeur que vous souhaitez rechercher. Lookup_value peut être une valeur ou une référence à une cellule. Lookup_value doit être dans la première colonne de la plage de cellules que vous spécifiez dans table_array
table_array- est la plage de cellules dans laquelle VLOOKUP recherchera la valeur_recherche et la valeur de retour. table_array doit contenir
la valeur lookup_value dans la première colonne, et
la valeur de retour que vous souhaitez trouver
Note- La première colonne contenant la valeur lookup_value peut être triée par ordre croissant ou non. Cependant, le résultat sera basé sur l'ordre de cette colonne.
col_index_num- est le numéro de colonne dans le tableau_table qui contient la valeur de retour. Les nombres commencent par 1 pour la colonne la plus à gauche de table-array
range_lookup- est une valeur logique facultative qui spécifie si vous souhaitez que VLOOKUP trouve une correspondance exacte ou une correspondance approximative. range_lookup peut être
omis, auquel cas il est supposé être TRUE et RECHERCHEV essaie de trouver une correspondance approximative
TRUE, auquel cas VLOOKUP essaie de trouver une correspondance approximative. En d'autres termes, si aucune correspondance exacte n'est trouvée, la valeur suivante la plus grande inférieure à lookup_value est renvoyée
FALSE, auquel cas RECHERCHEV essaie de trouver une correspondance exacte
1, auquel cas il est supposé être TRUE et RECHERCHEV essaie de trouver une correspondance approximative
0, auquel cas il est supposé être FALSE et VLOOKUP tente de trouver une correspondance exacte
Note- Si range_lookup est omis ou TRUE ou 1, VLOOKUP ne fonctionne correctement que lorsque la première colonne de table_array est triée par ordre croissant. Sinon, cela peut entraîner des valeurs incorrectes. Dans un tel cas, utilisez FALSE pour range_lookup.
Utilisation de la fonction VLOOKUP avec range_lookup TRUE
Considérez une liste des notes des élèves. Vous pouvez obtenir les notes correspondantes avec RECHERCHEV à partir d'un tableau contenant les intervalles de notes et la catégorie de réussite.
tableau_table -
Notez que les notes de la première colonne en fonction desquelles les notes sont obtenues sont triées par ordre croissant. Par conséquent, en utilisant TRUE pour l'argument range_lookup, vous pouvez obtenir une correspondance approximative qui est requise.
Nommez ce tableau comme Grades.
Il est recommandé de nommer les tableaux de cette manière afin que vous n'ayez pas à vous souvenir des plages de cellules. Maintenant, vous êtes prêt à rechercher la note de la liste des notes que vous avez comme suit -
Comme vous pouvez le constater,
col_index_num - indique que la colonne de la valeur de retour dans table_array est 2
la range_lookup est vrai
La première colonne contenant la valeur de recherche dans les notes table_array est dans l'ordre croissant. Par conséquent, les résultats seront corrects.
Vous pouvez également obtenir la valeur de retour pour les correspondances approximatives. ie RECHERCHEV se calcule comme suit -
| Des marques | Catégorie de passe |
|---|---|
| <35 | Échouer |
| > = 35 et <50 | Troisième classe |
| > = 50 et <60 | Seconde classe |
| > = 60 et <75 | Première classe |
| > = 75 | Première classe avec distinction |
Vous obtiendrez les résultats suivants -
Utilisation de la fonction VLOOKUP avec range_lookup FALSE
Considérez une liste de produits contenant l'ID de produit et le prix de chacun des produits. L'ID de produit et le prix seront ajoutés à la fin de la liste chaque fois qu'un nouveau produit est lancé. Cela signifierait que les ID de produit n'ont pas besoin d'être dans l'ordre croissant. La liste des produits peut être comme indiqué ci-dessous -
tableau_table -
Nommez ce tableau comme ProductInfo.
Vous pouvez obtenir le prix d'un produit avec l'ID produit avec la fonction RECHERCHEV car l'ID produit se trouve dans la première colonne. Le prix est dans la colonne 3 et donc col_index_ num devrait être 3.
- Utiliser la fonction VLOOKUP avec range_lookup comme TRUE
- Utiliser la fonction VLOOKUP avec range_lookup comme FALSE
La réponse correcte provient du tableau ProductInfo est 171,65. Vous pouvez vérifier les résultats.
Vous observez que vous avez -
- Le résultat correct lorsque range_lookup est FALSE, et
- Un résultat erroné lorsque range_lookup est TRUE.
En effet, la première colonne du tableau ProductInfo n'est pas triée par ordre croissant. Par conséquent, n'oubliez pas d'utiliser FALSE chaque fois que les données ne sont pas triées.
Utilisation de la fonction HLOOKUP
Vous pouvez utiliser HLOOKUP fonction si les données sont en lignes plutôt qu'en colonnes.
Exemple
Prenons l'exemple de l'information produit. Supposons que le tableau ressemble à ceci -
Nommez cette Array ProductRange. Vous pouvez trouver le prix d'un produit avec l'ID de produit avec la fonction RECHERCHEH
La syntaxe de la fonction HLOOKUP est
HLOOKUP (lookup_value, table_array, row_index_num, [range_lookup])Où
lookup_value - est la valeur à trouver dans la première ligne du tableau
table_array - est un tableau d'informations dans lequel les données sont recherchées
row_index_num - est le numéro de ligne dans table_array à partir de laquelle la valeur correspondante sera renvoyée
range_lookup - est une valeur logique qui spécifie si vous souhaitez que HLOOKUP trouve une correspondance exacte ou une correspondance approximative
range_lookup peut être
omis, auquel cas il est supposé être TRUE et HLOOKUP essaie de trouver une correspondance approximative
TRUE, auquel cas HLOOKUP essaie de trouver une correspondance approximative. En d'autres termes, si aucune correspondance exacte n'est trouvée, la valeur suivante la plus grande inférieure à lookup_value est renvoyée
FALSE, auquel cas HLOOKUP essaie de trouver une correspondance exacte
1, auquel cas il est supposé être TRUE et HLOOKUP essaie de trouver une correspondance approximative
0, auquel cas il est supposé être FALSE et HLOOKUP essaie de trouver une correspondance exacte
Note- Si range_lookup est Omitted ou TRUE ou 1, HLOOKUP fonctionne correctement uniquement lorsque la première colonne de table_array est triée par ordre croissant. Sinon, cela peut entraîner des valeurs incorrectes. Dans un tel cas, utilisez FALSE pour range_lookup.
Utilisation de la fonction HLOOKUP avec range_lookup FALSE
Vous pouvez obtenir le prix d'un produit à partir de l'ID de produit avec la fonction RECHERCHEH car l'ID de produit se trouve dans la première ligne. Le prix est à la ligne 3 et donc row_index_ num doit être 3.
- Utilisez la fonction HLOOKUP avec range_lookup comme TRUE.
- Utilisez la fonction HLOOKUP avec range_lookup comme FALSE.
La réponse correcte du tableau ProductRange est 171,65. Vous pouvez vérifier les résultats.
Vous observez que, comme dans le cas de RECHERCHEV, vous avez
Le résultat correct lorsque range_lookup est FALSE, et
Un résultat erroné lorsque range_lookup est TRUE.
Cela est dû au fait que la première ligne du tableau ProductRange n'est pas triée par ordre croissant. Par conséquent, n'oubliez pas d'utiliser FALSE chaque fois que les données ne sont pas triées.
Utilisation de la fonction HLOOKUP avec range_lookup TRUE
Prenons l'exemple des notes des élèves utilisées dans RECHERCHEV. Supposons que vous ayez les données en lignes au lieu de colonnes comme indiqué dans le tableau ci-dessous -
tableau_table -
Nommez ce tableau comme GradesRange.
Notez que les notes de la première ligne sur la base desquelles les notes sont obtenues sont triées par ordre croissant. Par conséquent, en utilisant HLOOKUP avec TRUE pour l'argument range_lookup, vous pouvez obtenir les notes avec une correspondance approximative et c'est ce qui est requis.
Comme vous pouvez le constater,
row_index_num - indique que la colonne de la valeur de retour dans table_array est 2
la range_lookup est vrai
La première colonne contenant la valeur de recherche dans le tableau Table_array Grades est dans l'ordre croissant. Par conséquent, les résultats seront corrects.
Vous pouvez également obtenir la valeur de retour pour les correspondances approximatives. ie HLOOKUP calcule comme suit -
| Des marques | <35 | > = 35 et <50 | > = 50 et <60 | > = 60 et <75 | > = 75 |
|---|---|---|---|---|---|
| Catégorie de passe | Échouer | Troisième classe | Seconde classe | Première classe | Première classe avec distinction |
Vous obtiendrez les résultats suivants -
Utilisation de la fonction INDEX
Lorsque vous disposez d'un tableau de données, vous pouvez récupérer une valeur dans le tableau en spécifiant le numéro de ligne et le numéro de colonne de cette valeur dans le tableau.
Considérez les données de ventes suivantes, dans lesquelles vous trouvez les ventes dans chacune des régions du Nord, du Sud, de l'Est et de l'Ouest par les vendeurs qui sont répertoriés.
- Nommez le tableau SalesData.
En utilisant la fonction INDEX, vous pouvez trouver -
- Les ventes de l'un des vendeurs dans une certaine région.
- Ventes totales dans une région par tous les vendeurs.
- Ventes totales par un vendeur dans toutes les régions.
Vous obtiendrez les résultats suivants -
Supposons que vous ne connaissiez pas les numéros de ligne des vendeurs et les numéros de colonne des régions. Ensuite, vous devez d'abord rechercher le numéro de ligne et le numéro de colonne avant de récupérer la valeur avec la fonction d'index.
Vous pouvez le faire avec la fonction MATCH comme expliqué dans la section suivante.
Utilisation de la fonction MATCH
Si vous avez besoin de la position d'un élément dans une plage, vous pouvez utiliser la fonction MATCH. Vous pouvez combiner les fonctions MATCH et INDEX comme suit -
Vous obtiendrez les résultats suivants -
