Astuce: Les autres langues sont Google-Traduction. Vous pouvez visiter le English version de ce lien.
Se connecter
x
or
x
x
S'enregistrer
x

or

Comment extraire des valeurs uniques en fonction de critères dans Excel?

Supposons que vous ayez la plage de données suivante que vous souhaitez répertorier uniquement les noms uniques de la colonne B en fonction d'un critère spécifique de la colonne A pour obtenir le résultat comme illustré ci-dessous. Comment pourriez-vous gérer cette tâche dans Excel rapidement et facilement?

Extraire des valeurs uniques basées sur des critères avec une formule matricielle

Extraire des valeurs uniques en fonction des critères Kutools for Excel

doc extrait unique avec critère 1



Pour résoudre ce travail, vous pouvez appliquer une formule de tableau complexe, procédez comme suit:

1. Entrez cette formule: = INDEX ($ B $ 2: $ B $ 17, MATCH (0, SI ($ D $ 2 = $ A $ 2: $ A $ 17, COUNTIF ($ E $ 1: $ E1, $ B $ 2: $ B $ 17), ""), 0)) dans une cellule vide où vous voulez lister le résultat d'extraction, dans cet exemple, je vais le mettre à la cellule E2, puis appuyez sur Maj + Ctrl + Entrée touches pour obtenir la première valeur unique, voir capture d'écran:

doc extrait unique avec critère 2

Veuillez noter que : Dans la formule ci-dessus: B2: B17 est la plage de colonnes contient les valeurs uniques que vous voulez extraire, A2: A17 est la colonne contient le critère sur lequel vous vous êtes basé, D2 indique le critère selon lequel vous souhaitez répertorier les valeurs uniques en fonction de, et E1 est la cellule au-dessus de votre formule entrée.

2. Faites ensuite glisser la poignée de remplissage vers les cellules pour répertorier toutes les valeurs uniques en fonction du critère spécifique, voir capture d'écran:

doc extrait unique avec critère 3


Si vous n'êtes pas qualifié avec la formule compliquée, ici, je vais parler d'un moyen facile de le résoudre sans aucune formule. Vous pouvez d'abord filtrer les données selon un critère spécifique, puis appliquer le Sélectionnez Dupliquer et cellules uniques caractéristique de Kutools for Excel pour sélectionner les valeurs uniques, puis collez-les à d'autres cellules dont vous avez besoin.

Kutools for Excel : avec plus de 300 compléments Excel pratiques, libre d'essayer sans limitation dans 60 jours.

Après l'installation de Kutools for Excel, s'il vous plaît faire comme suit :( Téléchargez Kutools for Excel dès maintenant! )

1. Tout d'abord, sélectionnez la plage de données que vous souhaitez utiliser, puis cliquez sur Data > Filtre, et cliquez sur le bouton fléché à côté de la cellule que vous souhaitez filtrer les données en fonction d'un critère spécifique, dans la zone de liste étendue, sélectionnez le critère que vous souhaitez filtrer, voir capture d'écran:

doc extrait unique avec critère 4

2. Puis clique OK, les données souhaitées ont été filtrées et sélectionnez les valeurs dans la colonne B dont vous souhaitez extraire les noms uniques, puis cliquez sur Kutools > Sélectionner > Sélectionnez les cellules dupliquées et uniques, voir capture d'écran:

doc extrait unique avec critère 5

3. Dans le Sélectionnez les cellules dupliquées et uniques boîte de dialogue, sélectionnez Tous uniques (y compris 1 st duplicates) sous le Règle section, voir capture d'écran:

doc extrait unique avec critère 6

4. Puis clique Ok bouton, toutes les valeurs uniques ont été sélectionnées, puis copiez les valeurs et collez-les dans la cellule dont vous souhaitez lister le résultat, voir capture d'écran:

doc extrait unique avec critère 7

Télécharger et tester gratuitement Kutools for Excel maintenant!


Kutools for Excel: avec plus que 200 compléments Excel pratiques, libre d'essayer sans limitation dans les jours 60. Téléchargez et essai gratuit maintenant!


Kutools for Excel résout la plupart de vos problèmes et augmente votre productivité de 80%

  • Réutilisation: Insérer rapidement formules complexes, graphiques et tout ce que vous avez utilisé auparavant; Crypter les cellules avec mot de passe Créer une liste de diffusion et envoyer des emails ...
  • Super Formula Bar (éditez facilement plusieurs lignes de texte et de formule); Disposition de lecture (facilement lire et éditer un grand nombre de cellules); Coller à la gamme filtrée...
  • Fusionner les cellules / rangées / colonnes sans perdre de données; Contenu des cellules divisées; Combiner les lignes / colonnes en double... Prévenir les cellules en double; Comparer les plages...
  • Sélectionnez Dupliquer ou Unique Des rangées; Sélectionnez les lignes vierges (toutes les cellules sont vides); Super Find et Fuzzy Find dans de nombreux cahiers d'exercices; Sélection aléatoire ...
  • Copie exacte Plusieurs cellules sans changer la référence de la formule; Créer automatiquement des références à plusieurs feuilles; Insérer des balles, Cases à cocher et plus ...
  • Extrait du texte, Ajouter du texte, Supprimer par position, Supprimer l'espace; Créer et imprimer des sous-totaux de pagination; Conversion entre contenu de cellules et commentaires...
  • Super filtre (enregistrer et appliquer des schémas de filtrage à d'autres feuilles); Tri avancé par mois / semaine / jour, fréquence et plus; Filtre spécial en gras, en italique ...
  • Combinaison de classeurs et de feuilles de calcul; Fusionner les tables en fonction des colonnes clés; Fractionner les données en plusieurs feuilles; Conversion par lots xls, xlsx et PDF...
  • Plus que de puissantes fonctionnalités 300. Prend en charge Office / Excel 2007-2019 et 365. Prend en charge toutes les langues. Déploiement facile dans votre entreprise ou organisation. Fonctionnalités complètes Essai gratuit du jour 30.
kte tab 201905

Office Tab apporte une interface à onglets à Office et simplifie grandement votre travail

  • Activer l'édition par onglets et la lecture 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é de 50% et réduit le nombre de clics de souris pour vous chaque jour!
fond officetab
Say something here...
symbols left.
You are guest ( Sign Up? )
or post as a guest, but your post won't be published automatically.
Loading comment... The comment will be refreshed after 00:00.
  • To post as a guest, your comment is unpublished.
    Giancarlo · 5 months ago
    Hi! the formula works really well. I would like to add another criterion, i mean, get the unique answers but using two criteria
    • To post as a guest, your comment is unpublished.
      skyyang · 4 months ago
      Hi, Giancarlo,
      to extract unique values based on multiple criteria, any of the below formula can help you: (after pasting the formula, please press Ctrl + Shift + Enter keys together.)
      =IFERROR(INDEX($C$2:$C$11, MATCH(0, COUNTIF(G1:$G$1, $C$2:$C$11)+IF($A$2:$A$11<>$E$2, 1, 0)+IF($B$2:$B$11<>$F$2, 1, 0), 0)), "")
      =INDEX($C$2:$C$11, MATCH(0, IF(($A$2:$A$11=$E$2)*($B$2:$B$11=$F$2), COUNTIF($G$1:$G1, $C$2:$C$11), ""), 0))
      Please try, hope it can help you!
  • To post as a guest, your comment is unpublished.
    Konstantin · 6 months ago
    Is there a way to make this work while ALLOWING for duplicate values? For instance, I want all instances of Lucy to be listed in the results.
    • To post as a guest, your comment is unpublished.
      skyyang · 5 months ago
      Hello, Konstantin,
      To extract all corresponding values including the duplicates based on a specific cell criteria, the following array formula can help you, see screenshot:
      =IF(ISERROR(INDEX($A$1:$B$17,SMALL(IF($A$1:$A$17=$D$2,ROW($A$1:$A$17)),ROW(1:1)),2)),"",
      INDEX($A$1:$B$17,SMALL(IF($A$1:$A$17=$D$2,ROW($A$1:$A$17)),ROW(1:1)),2))

      After inserting the formula, please press Shift + Ctrl + Enter keys together to get the correct result, and then drag the fill handle down to get all values.
      Hope this can help you, thank you!
  • To post as a guest, your comment is unpublished.
    Ed · 1 years ago
    This has worked great for me with a specific lookup value. However, if I wanted to use a wildcard to look up partial values, how would I do that? For example, if I wanted to lookup all the names associated with KT?

    I am using this function to look up cells that contain multiple text. For example if each product also had a sub-product within the same cell but I was only looking for names associated with the sub-product "elf".

    KTE - elf
    KTE- ball
    KTE - piano
    KTO - elf
    KTO- ball
    KTO - piano
  • To post as a guest, your comment is unpublished.
    ewik · 1 years ago
    For me the formula does not work. I press ctrl shift enter and i still get an error N/A. I would like to add that i prpared exaclty the same data as in tutorial. What is the reason it does not work?
  • To post as a guest, your comment is unpublished.
    Joe Jerz · 1 years ago
    How would I get this formula to return each of the duplicates instead of one of each of the names? For instance, in the example above, how would I get the results column (B:B) to return Lucy, Ruby, Anny, Jose, Lucy, Anny, Tom? I'm using this as a budget tool pulling to specific account summaries from a general ledger. However, several of the amounts and transaction descriptions are duplicates in the general ledger. Once the first of the duplicated values is pulled, no more of them get pulled.
    • To post as a guest, your comment is unpublished.
      skyyang · 1 years ago
      Hi, Joe,
      To extract all corresponding values based on a specific cell criteria, the following array formula can help you, see screenshot:
      =IF(ISERROR(INDEX($A$1:$B$17,SMALL(IF($A$1:$A$17=$D$2,ROW($A$1:$A$17)),ROW(1:1)),2)),"",
      INDEX($A$1:$B$17,SMALL(IF($A$1:$A$17=$D$2,ROW($A$1:$A$17)),ROW(1:1)),2))

      After inserting the formula, please press Shift + Ctrl + Enter keys together to get the correct result, and then drag the fill handle down to get all values.
      Hope this can help you, thank you!
      • To post as a guest, your comment is unpublished.
        Joe Jerz · 1 years ago
        Last Question: If I want the results column to return all values not associated with KTE or KTO (so, D:D would be Tom, Nocol, Lily, Angelina, Genna), how would I do that?
      • To post as a guest, your comment is unpublished.
        Joe Jerz · 1 years ago
        Ok, so it works in the master workbook. There is one exception that I haven't been able to determine the cause of: If the array (in my case, the general ledger that I had beginning in row 3) does not begin in Row 1, the returned values are incorrect. What causes this problem, and which term in the formula fixes it? Thanks again for your help with this!
      • To post as a guest, your comment is unpublished.
        Joe Jerz · 1 years ago
        So far so good. I'm able to duplicate the results in the test sheet, make changes to the array, and then correct the formula to account for the changes I've made. I plan to move this into the master sheet today and see how it works. Thanks for the help!
  • To post as a guest, your comment is unpublished.
    Me · 1 years ago
    Thank You!
  • To post as a guest, your comment is unpublished.
    gon · 1 years ago
    I am getting 0 instead of the expected results, the formula is doing great for data in the same sheet, do you have any solution for data in different sheet ?

    this is my formula

    =IFERROR(INDEX('Switching Data'!$B$7:$B$204,MATCH(0,IF($A$2='Switching Data'!$A$7:$A$204,COUNTIF($A$4:A4,'Switching Data'!$B$7:$B$204),""),0)),0)
    • To post as a guest, your comment is unpublished.
      ANIBAL LUCICHE · 5 months ago
      Hello Gon, I hope you are well. I wonder if you can to resolve this issue. I am getting same error when formula come from different sheet. I will appreciate share the solution if you got it.
    • To post as a guest, your comment is unpublished.
      skyyang · 1 years ago
      Hi, Gon,
      After inserting the formula, you should press Ctrl + Shift + Enter keys together, not just Enter key.
      Please try it, thank you!
  • To post as a guest, your comment is unpublished.
    Mujardin · 1 years ago
    If you get the #N/A error, go to your formula and use Control + Shift + Enter instead of Enter.
  • To post as a guest, your comment is unpublished.
    aditya dhavale · 1 years ago
    Hello, I am getting "#N/A" error at "Match function", can you please guide?
  • To post as a guest, your comment is unpublished.
    aditya · 1 years ago
    I am getting #N/A error at Match function with this formula.Can you please help?
  • To post as a guest, your comment is unpublished.
    Sundari · 1 years ago
    actually I want the cell to reflect "YES" if (AL2="AP" and AK2="AD" and Z2>500000)
  • To post as a guest, your comment is unpublished.
    Sundari · 1 years ago
    =IF(AL2="AP","AP",IF(AK2="AD","AD",IF(Z2>500000,"Yes","No"))) I want "all conditions" to be satisfied to say yes...excel reflecting error in this formula..pls advise
  • To post as a guest, your comment is unpublished.
    Michael · 1 years ago
    this was super helpful, but I keep getting doubles of all the names like this:
    Doe, Jane
    Doe, Jane
    Hoover, Tom
    Hoover, Tom

    How can I stop this?
    • To post as a guest, your comment is unpublished.
      aditya dhavale · 1 years ago
      Hello, I am getting "#N/A" error at "Match function", can you please guide?
  • To post as a guest, your comment is unpublished.
    Andre · 2 years ago
    Hi Ryan. Formulas works great, however when dragging down the first value keeps repeating. I have made sure that COUNTIF references the cell ABOVE the cell with the formula, but still repeats the first value when dragging down? (eg. if the array formula is in C2 then COUNTIF points to cell $C$1:$C$1)
    • To post as a guest, your comment is unpublished.
      Camilla · 1 years ago
      Probably doesn`t work cause you´ve locked the cells - Try to replace $C$1:$C$1 with $C$1:$C1
  • To post as a guest, your comment is unpublished.
    Ryan · 2 years ago
    Hi, to stop the first value repeating as you drag down you must COUNTIF the cell ABOVE the cell you're putting the formula in.

    E.g if the formula is going in E2 you must type countif($E$1:$E1...
    • To post as a guest, your comment is unpublished.
      Andre · 2 years ago
      Hi Ryan. Formulas works great, however when dragging down the first value keeps repeating. I have made sure that COUNTIF references the cell ABOVE the cell with the formula, but still repeats the first value when dragging down? (eg. if the array formula is in C2 then COUNTIF points to cell $C$1:$C$1)
  • To post as a guest, your comment is unpublished.
    Amanda · 2 years ago
    When using this formula it keeps repeating the first value, how do you make that stop and provide the list of values that equals the product in D2?
  • To post as a guest, your comment is unpublished.
    Barrett · 2 years ago
    This works really well, but whenever the value that it is putting in is duplicated, it only places the value once. For example, if your list had two Lucy's in it, it only brings one Lucy over to the new table. Is there a way to fix this?
  • To post as a guest, your comment is unpublished.
    Claire · 2 years ago
    Thanks for this I have tried this and seems to be working fine intermittently. The issue that keeps repeating is that sometimes only the first matched value will return and is then duplicated when I am dragging down to return all matched values. How do I prevent this? Any suggestions?
  • To post as a guest, your comment is unpublished.
    JeteMc · 2 years ago
    Thank You, This is great!
  • To post as a guest, your comment is unpublished.
    Aileen · 2 years ago
    Thank you for this tutorial! I'm also trying to modify the formula, like the above commentator, but with an AND condition so it meets another conditional criteria (e.g. for this example, I'd like to see only things above a certain threshold). Can you please advise? Thank you!
    • To post as a guest, your comment is unpublished.
      Konfis · 2 years ago
      Hey,
      One way to do it:
      Replace the if formula with sumproduct((condition1=rng1)+(condition2=rng2))*countif(...

      It worked for me. Good luck! By replacing the + with an * you can make it an OR condition, but take good care of the brackets!
  • To post as a guest, your comment is unpublished.
    Jake · 2 years ago
    Hi, thanks for this tutorial, it works perfectly.

    I'm trying to modify it to work with an OR condition, but it doesn't seem to be working - is this possible?

    e.g. =INDEX($B$2:$B$17, MATCH(0, IF(OR($D$2=$A$2:$A$17,$D$2=$B$2:$B$17), COUNTIF($E$1:$E1, $B$2:$B$17), ""), 0))