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 dans 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 satisfait aux deux conditions : « Vendeur égal à E2 » et « Mois égal à F2 ». Si les deux conditions sont remplies, le résultat est 1 ; sinon, c'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 retourne une cellule vide.
- TEXTEJOINT(", ", VRAI, ...) combine tous les noms de produits non vides en une seule cellule, séparés par ", ".
Méthode 2 : Utilisation de Kutools pour Excel
Kutools pour Excel offre une solution puissante et simple, 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, veuillez procéder comme suit :
- Sélectionnez la plage de données dont 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 colonne contenant vos critères de correspondance (par ex., Vendeur et Mois). Pour chaque colonne sélectionnée, cliquez sur Clé primaire pour les définir comme conditions de recherche.
- Cliquez sur l'en-tête de colonne où vous voulez les résultats combinés (par ex., Produit). Dans la section Combinaison, sélectionnez votre délimiteur préféré (par ex., 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és sur plusieurs conditions, en renvoyant les résultats au format vertical dans une colonne, 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 le montre 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 ET logique (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 (lorsque 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é, clarté et capacité à renverser 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 basés sur les 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 pour le vendeur et le mois.
- "Aucune 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.
- * : ET logique — 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 quelle correspondance renvoyer, selon la distance à laquelle la formule a été tirée vers la droite.
- SIERREUR(...) : Empêche les erreurs une fois les correspondances é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.
- TRANSPOSITION(...) : 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, en fonction de la manière 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 plus anciennes, 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 TEXTEJOINT ou des outils tiers comme Kutools pour Excel peuvent considérablement rationaliser 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 des cellules d'un tableau, 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 le montre la capture d'écran suivante. Comment pouvons-nous obtenir et renvoyer plusieurs valeurs de recherche dans une seule cellule séparée par des virgules dans Excel ?
- RechercheV et renvoyer plusieurs valeurs correspondantes à la fois dans Google Sheet
- La fonction RechercheV normale dans Google Sheets peut vous aider à trouver et renvoyer la première valeur correspondante basée sur des données fournies. Mais, parfois, vous devrez peut-être faire une rechercheV et renvoyer toutes les valeurs correspondantes comme le montre la capture d'écran suivante. Avez-vous des moyens simples et efficaces pour résoudre cette tâche dans Google Sheets ?
- RechercheV et renvoyer plusieurs valeurs à partir d'une liste déroulante
- Dans Excel, comment pourriez-vous faire une rechercheV et renvoyer 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 le montre la capture d'écran suivante. Cet article vous guidera étape par étape pour résoudre ce problème.
- RechercheV et renvoyer plusieurs valeurs verticalement dans Excel
- Normalement, vous pouvez utiliser la fonction RechercheV pour obtenir la première valeur correspondante, mais parfois, vous souhaitez renvoyer tous les enregistrements correspondants basés sur un critère spécifique. Cet article discutera de la façon de faire une rechercheV et de renvoyer toutes les valeurs correspondantes verticalement, horizontalement ou dans une seule cellule.
- RechercheV et renvoyer des 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 renvoyer la valeur correspondante située entre deux valeurs comme le montre la capture d'écran suivante, comment pourriez-vous traiter cette tâche dans Excel ?
Meilleurs outils de productivité pour Office
Améliorez vos compétences Excel avec Kutools pour Excel, et découvrez une efficacité incomparable. Kutools pour Excel propose plus de300 fonctionnalités avancées pour booster votre productivité et gagner du temps. Cliquez ici pour obtenir la fonctionnalité dont vous avez le plus besoin...
Office Tab apporte l’interface par onglets à Office, simplifiant considérablement 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 chaque jour !
Tous les modules complémentaires Kutools. Une seule installation
La suite Kutools for Office regroupe les modules complémentaires pour Excel, Word, Outlook & PowerPoint ainsi qu’Office Tab Pro, idéal pour les équipes travaillant sur plusieurs applications Office.
- Suite tout-en-un — modules complémentaires Excel, Word, Outlook & PowerPoint + Office Tab Pro
- Un installateur, une licence — installation en quelques minutes (compatible MSI)
- Une synergie optimale — productivité accélérée sur l’ensemble des applications Office
- Essai complet30 jours — sans inscription, ni carte bancaire
- Meilleure valeur — économisez par rapport à l’achat d’add-ins individuels