Renvoyer plusieurs valeurs correspondantes basées sur plusieurs critères dans Excel (Guide complet)
Les utilisateurs d'Excel rencontrent souvent des scénarios où il est nécessaire d'extraire plusieurs valeurs qui satisfont simultanément à plusieurs critères, et de présenter tous les résultats correspondants dans une colonne, une ligne ou consolidés dans une seule cellule. Ce guide explore des méthodes pour toutes les versions d'Excel ainsi que la nouvelle fonction FILTRE disponible dans Excel 365 et 2021.
Renvoyer plusieurs valeurs correspondantes basées sur plusieurs critères dans une seule cellule
- Avec la fonction Textjoin (Excel365 / 2021, 2019)
- Avec Kutools pour Excel (pour toutes les versions)
Renvoyer plusieurs valeurs correspondantes basées sur plusieurs critères dans une colonne
Renvoyer plusieurs valeurs correspondantes basées sur plusieurs critères dans une ligne
Renvoyer plusieurs valeurs correspondantes basées sur plusieurs critères dans une seule cellule
Dans Excel, extraire plusieurs valeurs correspondantes basées sur plusieurs critères au sein d'une seule cellule est un défi courant. Voici deux méthodes efficaces.
Méthode 1 : Utilisation de la fonction Textjoin (Excel365 / 2021, 2019)
Pour obtenir toutes les valeurs correspondantes dans une seule cellule avec des délimiteurs, la fonction TEXTJOIN peut vous aider.
Entrez ou copiez la formule suivante dans une cellule vide, puis appuyez sur la touche Entrée (Excel 2021 et Excel 365) ou Ctrl + Maj + Entrée dans Excel 2019 pour obtenir le résultat :
=TEXTJOIN(", ", TRUE, IF(($A$2:$A$18=E2)*($B$2:$B$18=F2), $C$2:$C$18, ""))
- ($A$2:$A$21=E2)*($B$2:$B$21=F2) vérifie si chaque ligne répond aux deux conditions : « Vendeur égal à E2 » et « Mois égal à F2 ». Si les deux conditions sont remplies, le résultat est 1 ; sinon, il est 0. L'astérisque * signifie que les deux conditions doivent être vraies.
- SI(..., $C$2:$C$21, "") renvoie le nom du produit si la ligne correspond ; sinon, elle renvoie une cellule vide.
- TEXTJOIN(", ", VRAI, ...) combine tous les noms de produits non vides dans une seule cellule, séparés par ", ".
Méthode 2 : Utilisation de Kutools pour Excel
Kutools pour Excel offre une solution puissante et simple, vous permettant de récupérer et combiner rapidement plusieurs correspondances dans une seule cellule basée sur plusieurs critères sans formules complexes.
Après avoir installé Kutools pour Excel, procédez comme suit :
- Sélectionnez la plage de données à partir de laquelle vous souhaitez obtenir toutes les valeurs correspondantes basées sur des critères.
- Ensuite, cliquez sur Kutools > Fusionner et Diviser > Fusion avancée des lignes, voir capture d'écran :
- Dans la boîte de dialogue Fusion avancée des lignes, configurez les options suivantes :
- Choisissez les en-têtes de colonnes contenant vos critères de correspondance (par exemple, Vendeur et Mois). Pour chaque colonne sélectionnée, cliquez sur Clé principale pour les définir comme conditions de recherche.
- Cliquez sur l'en-tête de colonne où vous souhaitez les résultats combinés (par exemple, Produit). Dans la section Combiner, sélectionnez votre délimiteur préféré (par exemple, virgule, espace ou séparateur personnalisé).
- Enfin, cliquez sur le bouton OK.
Résultat: Kutools fusionnera instantanément toutes les valeurs correspondantes dans une seule cellule par combinaison unique de critères.
Renvoyer plusieurs valeurs correspondantes basées sur plusieurs critères dans une colonne
Lorsque vous devez extraire et afficher plusieurs enregistrements correspondants à partir d'un ensemble de données basé sur plusieurs conditions, en renvoyant les résultats dans un format de colonne verticale, Excel propose plusieurs solutions puissantes.
Méthode 1 : Utilisation d'une formule matricielle (pour toutes les versions)
Vous pouvez utiliser la formule matricielle suivante pour renvoyer les résultats verticalement dans une colonne :
1. Copiez ou entrez la formule suivante dans une cellule vide :
=IFERROR(INDEX($C$2:$C$18, SMALL(IF(($A$2:$A$18=$E$2)*($B$2:$B$18=$F$2), ROW($C$2:$C$18)-ROW($C$2)+1), ROW(1:1))), "")2. Appuyez sur Ctrl + Maj + Entrée pour obtenir le premier résultat correspondant, puis sélectionnez la première cellule de formule et faites glisser la poignée de remplissage vers le bas jusqu'à ce qu'une cellule vide soit affichée, maintenant toutes les valeurs correspondantes sont renvoyées comme indiqué dans la capture d'écran ci-dessous :
- $A$2:$A$18=$E$2 : Vérifie si le Vendeur correspond à la valeur dans la cellule E2.
- $B$2:$B$18=$F$2 : Vérifie si le Mois correspond à la valeur dans la cellule F2.
- * est un opérateur logique ET (les deux conditions doivent être vraies).
- LIGNE($C$2:$C$18)-LIGNE($C$2)+1 : Génère un numéro de ligne relatif pour chaque produit.
- PETIT(..., LIGNE(1:1)) : Récupère le n-ième plus petit numéro de ligne correspondant (à mesure que la formule est tirée vers le bas).
- INDEX(...) : Renvoie le produit de la ligne correspondante.
- SIERREUR(..., "") : Renvoie une cellule vide s'il n'y a plus de correspondances.
Méthode 2 : Utilisation de la fonction FILTRE (Excel365 / 2021)
Si vous utilisez Excel 365 ou Excel 2021, la fonction FILTRE est un excellent choix pour renvoyer plusieurs résultats basés sur plusieurs critères, grâce à sa simplicité, sa clarté et sa capacité à répandre dynamiquement les résultats sans formules matricielles complexes.
Copiez ou entrez la formule ci-dessous dans une cellule vide, puis appuyez sur la touche Entrée, tous les enregistrements correspondants sont renvoyés en fonction des critères multiples.
=FILTER(C2:C18, (A2:A18=E2)*(B2:B18=F2), "No match")
- FILTRE(...) renvoie toutes les valeurs de C2:C18 où les deux conditions sont remplies.
- (A2:A18=E2)*(B2:B18=F2) : Tableau logique qui vérifie les correspondances de vendeur et de mois.
- "Pas de correspondance" : Message facultatif si aucune valeur n'est trouvée.
Renvoyer plusieurs valeurs correspondantes basées sur plusieurs critères dans une ligne
Les utilisateurs d'Excel ont souvent besoin d'extraire plusieurs valeurs d'un ensemble de données qui répondent à plusieurs conditions et de les afficher horizontalement (dans une ligne). Cela est utile pour créer des rapports dynamiques, des tableaux de bord ou des tableaux récapitulatifs où l'espace vertical est limité. Dans cette section, nous explorerons deux méthodes puissantes.
Méthode 1 : Utilisation d'une formule matricielle (pour toutes les versions)
Les formules matricielles traditionnelles permettent d'extraire plusieurs valeurs correspondantes en utilisant les fonctions INDEX, PETIT, SI et COLONNE. Contrairement à l'extraction verticale (basée sur les colonnes), nous ajustons la formule pour renvoyer les résultats dans une ligne.
1. Copiez ou entrez la formule ci-dessous dans une cellule vide :
=IFERROR(INDEX($C$2:$C$18, SMALL(IF(($A$2:$A$18=$E$2)*($B$2:$B$18=$F$2), ROW($C$2:$C$18)-ROW($C$2)+1), COLUMN(A1))), "")2. Appuyez sur Ctrl + Maj + Entrée pour obtenir le premier résultat correspondant, puis sélectionnez la première cellule de formule et faites glisser la formule vers la droite à travers les colonnes pour récupérer tous les résultats.
- $A$2:$A$18=$E$2 : Vérifie si le Vendeur correspond.
- $B$2:$B$18=$F$2 : Vérifie si le Mois correspond.
- * : Logique ET — les deux conditions doivent être vraies.
- LIGNE($C$2:$C$18)-LIGNE($C$2)+1 : Crée des numéros de ligne relatifs.
- COLONNE(A1) : Ajuste quel résultat correspondant renvoyer, en fonction de la distance à laquelle la formule a été tirée vers la droite.
- SIERREUR(...) : Empêche les erreurs une fois que les correspondances sont épuisées.
Méthode 2 : Utilisation de la fonction FILTRE (Excel365 / 2021)
Copiez ou entrez la formule ci-dessous dans une cellule vide, puis appuyez sur la touche Entrée, toutes les valeurs correspondantes sont extraites et placées dans une ligne. Voir capture d'écran :
=TRANSPOSE(FILTER(C2:C18, (A2:A18=E2)*(B2:B18=F2), "No match"))
- FILTRE(...) : Récupère les valeurs correspondantes de la colonne C basées sur les deux conditions.
- (A2:A18=E2)*(B2:B18=F2) : Les deux conditions doivent être vraies.
- TRANSPOSER(...) : Convertit le tableau vertical renvoyé par FILTRE en un tableau horizontal.
Conclusion
Récupérer plusieurs valeurs correspondantes basées sur plusieurs critères dans Excel peut être accompli de plusieurs manières, selon la façon dont vous souhaitez afficher les résultats — que ce soit dans une colonne, une ligne ou dans une seule cellule.
- Pour les utilisateurs d'Excel 365 ou Excel 2021, la fonction FILTRE offre une solution moderne, dynamique et élégante qui minimise la complexité.
- Pour ceux qui utilisent des versions antérieures, les formules matricielles restent des outils puissants, bien qu'ils nécessitent un peu plus de configuration et de soin.
- De plus, si vous souhaitez consolider les résultats dans une seule cellule ou préférez une solution sans code, la fonction TEXTJOIN ou des outils tiers comme Kutools pour Excel peuvent considérablement simplifier le processus.
Choisissez la méthode qui convient le mieux à votre version d'Excel et à votre disposition préférée, et vous serez bien équipé pour gérer efficacement et précisément les recherches multicritères. Si vous êtes intéressé par l'exploration de plus d'astuces et conseils Excel, notre site Web propose des milliers de tutoriels pour vous aider à maîtriser Excel.
Plus d'articles relatifs :
- Renvoyer plusieurs valeurs de recherche dans une seule cellule séparée par des virgules
- Dans Excel, nous pouvons appliquer la fonction RECHERCHEV pour renvoyer la première valeur correspondante d'une table de cellules, mais parfois, nous devons extraire toutes les valeurs correspondantes puis les séparer par un délimiteur spécifique, tel qu'une virgule, un tiret, etc., dans une seule cellule comme indiqué dans la capture d'écran suivante. Comment obtenir et renvoyer plusieurs valeurs de recherche dans une seule cellule séparée par des virgules dans Excel ?
- RechercheV et retourner plusieurs valeurs correspondantes à la fois dans Google Sheet
- La fonction RechercheV normale dans Google Sheets peut vous aider à trouver et retourner la première valeur correspondante basée sur des données fournies. Mais, parfois, vous devrez peut-être effectuer une rechercheV et retourner toutes les valeurs correspondantes comme indiqué dans la capture d'écran suivante. Avez-vous des moyens simples et efficaces pour résoudre cette tâche dans Google Sheets ?
- RechercheV et retourner plusieurs valeurs à partir d'une liste déroulante
- Dans Excel, comment effectuer une rechercheV et retourner plusieurs valeurs correspondantes à partir d'une liste déroulante, ce qui signifie que lorsque vous choisissez un élément dans la liste déroulante, toutes ses valeurs relatives sont affichées à la fois comme indiqué dans la capture d'écran suivante. Cet article vous guidera étape par étape pour résoudre ce problème.
- RechercheV et retourner plusieurs valeurs verticalement dans Excel
- Normalement, vous pouvez utiliser la fonction RechercheV pour obtenir la première valeur correspondante, mais parfois, vous souhaitez retourner tous les enregistrements correspondants basés sur un critère spécifique. Cet article discutera de la manière d'effectuer une rechercheV et de retourner toutes les valeurs correspondantes verticalement, horizontalement ou dans une seule cellule.
- RechercheV et retourner les données correspondantes entre deux valeurs dans Excel
- Dans Excel, nous pouvons appliquer la fonction RechercheV normale pour obtenir la valeur correspondante basée sur des données fournies. Mais, parfois, nous souhaitons effectuer une rechercheV et retourner la valeur correspondante entre deux valeurs comme indiqué dans la capture d'écran suivante, comment pourriez-vous traiter cette tâche dans Excel ?
Meilleurs outils de productivité pour Office
Boostez vos compétences Excel avec Kutools pour Excel, et découvrez une efficacité sans précédent. Kutools pour Excel propose plus de300 fonctionnalités avancées pour augmenter la productivité et gagner du temps. Cliquez ici pour obtenir la fonctionnalité dont vous avez le plus besoin...
Office Tab apporte une interface à onglets à Office, et facilite grandement votre travail
- Activez la modification et la lecture par onglets dans Word, Excel, PowerPoint, Publisher, Access, Visio et Project.
- Ouvrez et créez plusieurs documents dans de nouveaux onglets de la même fenêtre, plutôt que dans de nouvelles fenêtres.
- Augmente votre productivité de50 %, et réduit des centaines de clics de souris pour vous chaque jour !