Les meilleures fonctions de recherche dans Excel
Excel dans ses dernières versions compte près de 500 fonctions. Chacune d’entre elles a sa propre utilité et sa propre syntaxe. Pas évident alors de savoir quelle fonction utiliser dans quel cas.
Toutes ces fonctions sont séparées en différentes catégories en fonction de ce qu’elles permettent de manipuler (textes, dates, valeurs booléennes, etc.) :
Vérifier des conditions et utiliser les valeurs VRAI et FAUX
Rechercher des valeurs en fonctions de différents critères
Etc..
Nous avons, pour pouvoir vous aiguiller, commencé à créer une série d’articles sur chacun de ces domaines en énumérant les différentes fonctions qui les composent. Après les fonctions de date, de texte, mathématiques, voici … les fonctions de recherche !
Les 14 fonctions de recherche réparties dans 4 catégories. Lesquelles garder dans la besace, lesquelles oublier ? La réponse à la suite de cet article !
Les différents types de fonctions de recherche
Effectivement, si l’on s’en tient à la définition stricte d’une fonction de recherche, cette thématique n’inclut que la première catégorie : les fonctions qui permettent de rechercher une valeur selon un ou plusieurs critères. Même dans cette catégorie, si vous disposez d’Excel 365, il n’y a potentiellement que 2 fonctions à réellement retenir : RECHERCHEX et INDEX.
Cela étant, nous choisissons d’étendre la définition, pour inclure notamment des fonctions disponibles uniquement sur Microsoft 365, et qui permettent encore beaucoup plus de libertés.
Ces catégories, arbitrairement créée par nos soins sont :
- Les fonctions qui permettent de retourner une ou plusieurs valeurs selon un ou plusieurs critères de recherche,
- Les fonctions qui permettent de retourner un tableau à part entière,
- Les fonctions qui ne retournent pas une ou plusieurs valeurs directement,
- LIREDONNEESTABCROISDYNAMIQUE : la fonction pour pointer une valeur dans un tableau croisé dynamique, quels que soient les filtres appliqués.
Pour tous les exemples de toutes les fonctions dans la suite de cet article, nous allons utiliser le tableau ci-dessous comme exemple (sauf pour LIREDONNEESTABCROISDYNAMIQUE) :
Nous allons appeler ce tableau « Tableau 1 » dans tous les exemples.
Les 2 meilleures fonctions qui permettent de retrouver une valeur selon un ou plusieurs critères : RECHERCHEX et INDEX
Dans cette partie, nous nous intéressons particulièrement aux fonctions RECHERCHEX et INDEX, mais nous laissons une mention honorable à RECHERCHEV, la star révolue des fonctions de recherche.
La fonction RECHERCHEX
C’est la fonction la plus complète à ce jour pour rechercher une valeur : il est possible de séparer la colonne de recherche de la colonne de résultats, elle a un SIERREUR intégré, il y a plus de méthodes de recherche dans le cas de la correspondance approximative, et on peut choisir si la recherche commence par le bas ou par le haut du tableau.
Utilité : Récupérer une valeur dans une plage de cellules en fonction d’une valeur dans une autre plage de cellules (avec options).
Syntaxe : =RECHERCHEX( valeur_cherchée ; tableau_recherche ; tableau_renvoyé ; [si_non_trouvé] ; [mode_correspondance] ; [mode_recherche] )
Formule : =RECHERCHEX(«Alicia B.» ;Tableau1[Matricule] ;Tableau1[Rému.] ; «Non trouvé» ;0 ;1) >>> Résultat : 64623. Alicia B. a bien été trouvée dans la colonne Matricule du tableau, et la valeur de la colonne Rému. sur la même ligne est 64 623 €.
La fonction INDEX
C’est une fonction de recherche très utile quand la valeur cherchée est déterminée non pas par un seul critère, mais par 2 critères, en colonne et en ligne. C’est donc ultra utile sur des tableaux à double entrée. A noter que ce n’est que couplée à une ou 2 fonctions EQUIV (ou EQUIVX maintenant) que cette fonction atteint son plein potentiel.
Utilité : Récupérer la valeur située à l'intersection d'un numéro de ligne et d'un numéro de colonne dans une plage de cellules.
Syntaxe : =INDEX( matrice ; numéro_ligne ; [numéro_colonne] )
Formule : =INDEX(Tableau1 ; 5 ; 4) >>> Résultat : 40. La valeur de la 5ème ligne et en 4ème colonne du tableau est l’âge de Sam. H : 40.
La fonction RECHERCHEV (mention honorable)
La fonction reste très utilisée, mais d’un point de vue technique, elle a mal vieilli. Les fonctions INDEX (avec EQUIV) et RECHERCHEX font le boulot de RECHERCHEV, mais en mieux.
Utilité : Récupérer une valeur dans une plage de cellules verticale en fonction d’une valeur initiale.
Syntaxe : =RECHERCHEV( valeur ; table_matrice ; no_index ; [valeur_proche] )
Formule : =RECHERCHEV(«Mélodie G.» ; Tableau1 ;2 ;FAUX) >>> Résultat : Logistique. La valeur du tableau 1 dans la 2ème colonne après le matricule, dans la même ligne que Mélodie G. est logistique.
Les 6 fonctions qui permettent de rechercher des plages
Impossible de choisir les meilleures fonctions parmi celles-ci, tant elles ont des utilités différentes les unes des autres. En revanche, nous choisissons de rassembler CHOISIRCOLS et CHOISIRLIGNES dans le même exemple, puisqu’elles fonctionnent exactement pareil, sauf que l’une sélectionne des colonnes et l’autre des lignes. Même traitement pour PRENDRE et EXCLURE, puisqu’elles fonctionnent de la même manière, mais leurs résultats est pile l’opposé l’un de l’autre.
La fonction FILTRE
Nous connaissons tous le fameux bouton de filtre. FILTRE est ce bouton, mais en formule ! En d’autre terme, il extrait toutes les valeurs qui correspondent à un ou plusieurs critères. C’est donc en quelque sorte une fonction de recherche multi-résultats (on peut également la ranger parmi les fonctions de logique, en regardant son fonctionnement de plus près sa structure).
Utilité : Récupérer la liste des lignes d’une plage de cellules en fonction d’un ou plusieurs critères.
Syntaxe : =FILTRE( tableau; inclure ; [si_vide] )
Formule : =FILTRE(Tableau1[Matricule] ; Tableau1[Service] = «RH» ; «Service inexistant») >>> Résultats : Félicie P., Armand P., Alicia B., … Ce sont toutes les personnes dans la colonne Matricule avec RH écrit à côté.
La fonction CHOISIRCOLS (et CHOISIRLIGNES)
Ces fonctions permettent de réarranger les colonnes (ou les lignes) dans l’ordre souhaité et/ou d’en exclure une ou plusieurs du tableau d’origine. Nous sommes donc clairement dans une définition au sens très large des fonctions de recherche (ce qui est le cas pour la plupart des fonctions de cette catégorie).
Utilité : Créer une plage de cellules comprenant uniquement les colonnes choisies d’une plage de cellules initiale, tout en choisissant l’ordre de ces colonnes.
Syntaxe : =CHOISIRCOLS( array ; col_num1; [col_num2]; …)
Formule : =CHOISIRCOLS(Tableau1[#Tout] ;2 ;1) >>> Résultat :
De tout le tableau en exemple, nous avons sélectionné les colonnes 2, puis 1 et donc ignoré toutes les autres.
La fonction PRENDRE (et EXCLURE)
Ces fonctions ont exactement la même structure, mais l’une ne garde que la plage sélectionnée et l’autre garde tout sauf la plage sélectionnée.
Utilité : Créer une plage de cellules comprenant uniquement les X premières (ou dernières) lignes ou colonnes d’une plage de cellules initiale.
Syntaxe : =PRENDRE( array ; rows ; [columns] )
Formule : =PRENDRE(Tableau1 ; 1 ; 2) >>> Résultat :
Les deux premières colonnes de la première ligne du tableau contiennent les valeurs Norma H. et Produit.
La fonction DECALER
La fonction DECALER est un peu spéciale, mais elle est utile pour atteindre la ou les valeurs d’une cellule ou d’une plage en partant d’une cellule de départ puis en décalant d’un certain nombre de lignes et / ou de colonnes.
Utilité : renvoie une référence à une plage qui correspond à un nombre déterminé de lignes et de colonnes d’une cellule ou plage de cellules.
Syntaxe : =DECALER(réf; lignes; colonnes; [hauteur]; [largeur])
Formule : =DECALER(Tableau1[[#En-têtes];[Matricule]];3;5;1;1) >>> Résultat : 37 261 €. La valeur qui se trouve 3 lignes en-dessous et 5 colonnes à droite de l’en-tête Matricule est 37 261 €.
Rechercher une position relative : EQUIV et EQUIVX
La fonction EQUIVX est une version actualisée de la fonction EQUIV, c’est-à-dire qu’elles retrouvent toutes les deux une position dans une ligne ou une colonne, mais EQUIVX intègre en plus un mode de recherche pour changer de sens de numérotation ou pour trier les valeurs virtuellement, ainsi que des types de correspondance approximative qui prend en compte des textes.
Nous choisissons donc de montrer uniquement EQUIVX, puisqu’en termes de structure, EQUIV n’en est qu’une version simplifiée (et compatible avec les anciennes versions d’Excel).
Également, dans les 2 cas, il est important de noter qu’elles ont une grande synergie avec la fonction INDEX, où elles peuvent prendre la place du numéro de ligne et du numéro de colonne, ainsi qu’avec DECALER, où elles peuvent être utilisées dans tous les arguments (d’une manière ou d’une autre) sauf le premier.
La fonction EQUIVX
Sans aucune introduction supplémentaire, voici comment elle fonctionne :
Utilité : Récupérer le numéro correspondant à la première position d’une valeur dans une plage de cellules (avec options).
Syntaxe : =EQUIVX(valeur_cherchée; tableau_recherche; [mode_correspondance]; [mode_recherche])
Formule : =EQUIVX(«Jade F.» ; Tableau1[Matricule] ; 0 ; 1) >>> Résultat : 9. De haut en bas, Jade F. est la 9ème valeur de la colonne Matricule du tableau.
La fonction LIREDONNEESTABCROISDYNAMIQUE, pour rechercher une valeur dans un tableau croisé dynamique
Cette fonction est un peu spéciale, pour 2 raisons : la première c’est qu’elle ne peut rechercher des données que dans un tableau croisé dynamique, et la seconde : c’est la seule fonction qui apparaît automatiquement quand on écrit = dans la barre de formule, puis on sélectionne une cellule d’un tableau croisé dynamique (par défaut dans les options d’Excel).
A noter que cette fonction synergise bien avec l’utilisation des segments de tableaux croisés dynamiques.
Pour cet exemple, nous avons généré un TCD à partir du tableau que nous avons utilisé pour tous les exemples de cet article. Le voici :
Passons aux informations sur la fonction :
Utilité : Renvoyer une valeur d’un tableau croisé dynamique.
Syntaxe : =LIREDONNEESTABCROISDYNAMIQUE(champ_données ; tableau_croisé_dyn ; [champ1; élément1; champ2; élément2]; ...)
Formule : =LIREDONNEESTABCROISDYNAMIQUE(«Rému.» ; K2 ; «Service» ; «Produit») >>> Résultat : 495 314 €. La rémunération totale du service produit du tableau croisé dynamique qui se trouve à partir de la cellule K2 est 495 314 €.
Conclusion
Les fonctions de recherche sont toutes très utiles pour retrouver une ou plusieurs valeurs. Pour chacune d’entre elles, l’approche peut différer, ainsi que le type de résultat qu’elles peuvent donner. Elles sont donc difficilement interchangeables (sauf pour celles qui ont été mentionnées mais pas détaillées).
A partir de maintenant, plus aucune valeur n’échappera à votre vigilance !
