RECHERCHEV sur deux colonnes ou plus de critères – Université Excel

Si vous avez déjà essayé dutiliser une fonction RECHERCHEV avec deux colonnes ou plus de critères, vous avez rapidement découvert que il na simplement pas été construit dans ce but. Heureusement, il existe une autre fonction qui peut fonctionner comme une alternative à VLOOKUP en fonction de ce que vous voulez retourner.

Objectif de recherche multi-colonnes

Tout dabord, confirmons notre objectif en regardant un exemple de classeur. Nous avons exporté certaines informations de notre système comptable, et il résume essentiellement les totaux des transactions pour le mois par classe et par compte. Un exemple de lexportation est présenté ci-dessous:

À partir de ces données exportées, nous aimerions récupérer les montants sélectionnés en fonction du colonnes de classe et de compte. Nous voulons récupérer les montants et les placer dans notre petit rapport, illustré ci-dessous:

Si vous connaissez la RECHERCHEV , il semble naturel dessayer de créer le rapport avec cette fonction car, après tout, il sagit dune tâche de recherche. Et les tâches de recherche sont mieux résolues avec les fonctions de recherche traditionnelles… nest-ce pas? En fait ça dépend. Cela dépend de ce que vous essayez de récupérer.

Somme conditionnelle pour les recherches

Si vous essayez de récupérer une valeur numérique, telle quun montant, une fonction de recherche traditionnelle peut ne pas soyez votre meilleur pari. Voici pourquoi. À partir dExcel 2007, Microsoft a inclus la fonction de sommation conditionnelle SUMIFS. Cette fonction de sommation à conditions multiples est conçue pour additionner une colonne de nombres et ninclut que les lignes qui remplissent une ou plusieurs conditions. Les points commencent-ils encore à se connecter?

Si nous appliquons cette idée à notre tâche, nous nous rendrions rapidement compte que nous pourrions utiliser cette fonction de sommation conditionnelle pour récupérer les valeurs de notre rapport.

Le premier argument de la fonction SUMIFS est la plage de somme, cest-à-dire la colonne de nombres à ajouter. Dans notre cas, la colonne qui a la valeur que nous souhaitons renvoyer. Les arguments restants viennent par paires: la plage de critères et la valeur des critères.

Il est utile de penser à la fonction en ces termes: additionnez cette colonne (argument 1), nincluez que les lignes où cette colonne (argument 2) est égal à cette valeur (argument 3), et où cette colonne (argument 4) est égale à cette valeur (argument 5), et où… et ainsi de suite, jusquà 127 paires.

Ainsi, pour remplir notre rapport, nous allons récupérer les valeurs de montant de lexportation et faire correspondre les colonnes de classe et de compte, comme indiqué ci-dessous.

Sil y a plusieurs lignes avec la même classe et les mêmes comptes, la fonction SUMIFS renverra la somme de tous les éléments correspondants.

Comme vous pouvez le voir, si la valeur vous essayez de renvoyer un nombre, la fonction SUMIFS simplifie la recherche sur plusieurs colonnes. Mais que se passe-t-il si la valeur que vous essayez de renvoyer nest pas un nombre? Eh bien, vous devrez utiliser une fonction de recherche traditionnelle comme indiqué ci-dessous.

Utilisation de VLOOKUP avec la méthode SUMIFS

Une méthode consiste à utiliser VLOOKUP et SUMIFS dans une seule formule. Essentiellement, vous utilisez SUMIFS comme premier argument de RECHERCHEV. Cette méthode est explorée en détail dans cet article de lUniversité Excel:

Utilisation de VLOOKUP avec la méthode CONCATENATE

Si vous essayez de renvoyer une chaîne de texte plutôt quun nombre, ou utilisez une version de Excel qui na pas SUMIFS, alors vous êtes probablement coincé avec une fonction de recherche traditionnelle telle que RECHERCHEV avec la fonction CONCATENER pour générer une seule colonne de recherche unique. Cette approche est assez bien documentée, mais lidée de base est la suivante: créez dabord une seule colonne de recherche, puis utilisez RECHERCHEV.

Notre exemple sera une liste demployés, comme illustré ci-dessous:

Nous devons récupérer létat de la liste des employés pour notre petit rapport ci-dessous:

Puisque la valeur que nous essayons de renvoyer, létat, est une chaîne de texte et non un nombre, il nous est interdit dutiliser la fonction SUMIFS. Ainsi, nous devrons passer à lancienne avec RECHERCHEV et CONCATENER.

Nous commençons par créer une colonne daide qui crée essentiellement les valeurs de recherche combinées. Cela peut facilement être accompli avec la fonction CONCATENER ou lopérateur de concaténation (&). Cette nouvelle colonne de recherche est illustrée dans la colonne B ci-dessous:

Nous avons maintenant une seule colonne de recherche qui peut être utilisée avec un fonction de recherche traditionnelle telle que RECHERCHEV. Le rapport peut être rempli en recherchant les noms combinés dans la nouvelle plage de recherche, comme indiqué ci-dessous:

Cette même approche peut être utilisé lorsque deux, trois ou plusieurs colonnes de recherche doivent être prises en compte.

Conclusion

En plus de pouvoir effectuer des recherches multi-colonnes lorsque la valeur de retour est numérique, la fonction SUMIFS présente des avantages supplémentaires par rapport aux fonctions de recherche traditionnelles. Par exemple, il renvoie zéro lorsquaucune valeur correspondante nest trouvée, il renvoie la somme de toutes les correspondances, il prend en charge les opérateurs de comparaison et il ne se cassera pas lorsquune nouvelle colonne est insérée entre les colonnes de recherche et de retour.

Ainsi, lorsque vous êtes sur le point de supprimer la fonction RECHERCHEV pour effectuer une tâche de recherche, envisagez dutiliser SUMIFS à la place. Croyez-le ou non, la fonction SUMIFS fait une merveilleuse fonction de recherche.

Si vous avez dautres approches préférées pour les recherches multi-colonnes, nous aimerions en savoir plus… veuillez poster un commentaire ci-dessous.

Exemple de fichier

Si vous souhaitez jouer avec le classeur utilisé pour générer les captures décran ci-dessus, nhésitez pas à télécharger lexemple de fichier:

MultColumnLookup