Note: The other languages of the website are Google-translated. Back to English
Se connecter  \/ 
x
or
x
INSCRIPTION  \/ 
x

or

Comment extraire des valeurs uniques basées sur des critères dans Excel?

Supposons que vous ayez la plage de données de gauche 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 pouvez-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 basées sur plusieurs critères avec une formule matricielle

Extraire des valeurs uniques d'une liste de cellules avec une fonctionnalité utile

 

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

Pour résoudre ce problème, vous pouvez appliquer une formule matricielle complexe, procédez comme suit:

1. Entrez la formule ci-dessous dans une cellule vide où vous souhaitez répertorier le résultat de l'extraction, dans cet exemple, je la placerai dans la cellule E2, puis appuyez sur Maj + Ctrl + Entrée clés pour obtenir la première valeur unique.

=IFERROR(INDEX($B$2:$B$15, MATCH(0, IF($D$2=$A$2:$A$15, COUNTIF($E$1:$E1, $B$2:$B$15), ""), 0)),"")

2. Ensuite, faites glisser la poignée de remplissage vers les cellules jusqu'à ce que les cellules vides soient affichées, et maintenant toutes les valeurs uniques basées sur le critère spécifique ont été répertoriées, voir capture d'écran:

A Noter: Dans la formule ci-dessus: B2: B15 la plage de colonnes contient les valeurs uniques à partir desquelles vous souhaitez extraire, A2: A15 la colonne contient-elle le critère sur lequel vous vous êtes basé, D2 indique le critère sur lequel vous souhaitez répertorier les valeurs uniques, et E1 est la cellule au-dessus de votre formule saisie.

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

Si vous souhaitez extraire les valeurs uniques en fonction de deux conditions, voici une autre formule matricielle qui peut vous rendre service, procédez comme suit:

1. Entrez la formule ci-dessous dans une cellule vide où vous souhaitez répertorier les valeurs uniques, dans cet exemple, je la placerai dans la cellule G2, puis appuyez sur Maj + Ctrl + Entrée clés pour obtenir la première valeur unique.

=IFERROR(INDEX($C$2:$C$15,MATCH(0,COUNTIF(G1:$G$1,$C$2:$C$15)+IF($A$2:$A$15<>$E$2,1,0)+IF($B$2:$B$15<>$F$2,1,0),0)),"")

2. Ensuite, faites glisser la poignée de remplissage vers les cellules jusqu'à ce que les cellules vides soient affichées, et maintenant toutes les valeurs uniques basées sur les deux conditions spécifiques ont été répertoriées, voir capture d'écran:

A Noter: Dans la formule ci-dessus: C2: C15 la plage de colonnes contient les valeurs uniques à partir desquelles vous souhaitez extraire, A2: A15 à ces émotions et de E2 sont la première plage avec les critères sur lesquels vous souhaitez extraire des valeurs uniques, B2: B15 à ces émotions et de F2 sont la deuxième plage avec les critères sur lesquels vous souhaitez extraire des valeurs uniques, et G1 est la cellule au-dessus de votre formule saisie.

Extraire des valeurs uniques d'une liste de cellules avec une fonctionnalité utile

Parfois, vous voulez simplement extraire les valeurs uniques d'une liste de cellules, ici, je recommanderai un outil utile-Kutools pour Excel, Avec son Extraire des cellules avec des valeurs uniques (inclure le premier duplicata) utilitaire, vous pouvez extraire rapidement les valeurs uniques.

A Noter:Appliquer cette Extraire des cellules avec des valeurs uniques (inclure le premier duplicata), tout d'abord, vous devez télécharger le Kutools pour Excel, puis appliquez la fonction rapidement et facilement.

Après l'installation de Kutools pour Excel, veuillez faire comme ceci:

1. Cliquez sur une cellule dans laquelle vous souhaitez afficher le résultat. (Notes: Ne cliquez pas sur une cellule de la première ligne.)

2. Puis clique Kutools > Aide à la formule > Aide à la formule, voir capture d'écran:

3. Dans le Aide aux formules boîte de dialogue, veuillez effectuer les opérations suivantes:

  • Choisir Texte Option de Laits en poudre Type la liste déroulante;
  • Alors choisi Extraire des cellules avec des valeurs uniques (inclure le premier duplicata) du Choisissez une fromule zone de liste;
  • Dans le droit Entrée d'arguments , sélectionnez une liste de cellules dont vous souhaitez extraire des valeurs uniques.

4. Puis clique Ok , le premier résultat est affiché dans la cellule, puis sélectionnez la cellule et faites glisser la poignée de recopie vers les cellules pour lesquelles vous souhaitez répertorier toutes les valeurs uniques jusqu'à ce que les cellules vides soient affichées, voir capture d'écran:

Téléchargez gratuitement Kutools pour Excel maintenant!


Articles plus relatifs:

  • Compter le nombre de valeurs uniques et distinctes dans une liste
  • Supposons que vous ayez une longue liste de valeurs avec des éléments en double, maintenant, vous voulez compter le nombre de valeurs uniques (les valeurs qui n'apparaissent dans la liste qu'une seule fois) ou de valeurs distinctes (toutes les valeurs différentes de la liste, cela signifie unique valeurs + 1ère valeur en double) dans une colonne comme illustré à gauche. Cet article, je vais parler de la façon de gérer ce travail dans Excel.
  • Somme des valeurs uniques basées sur des critères dans Excel
  • Par exemple, j'ai une plage de données qui contient les colonnes Nom et Ordre, maintenant, pour additionner uniquement les valeurs uniques dans la colonne Ordre en fonction de la colonne Nom comme illustré ci-dessous. Comment résoudre cette tâche rapidement et facilement dans Excel?
  • Concaténer des valeurs uniques dans Excel
  • Si j'ai une longue liste de valeurs remplies de données en double, maintenant, je veux trouver uniquement les valeurs uniques, puis les concaténer dans une seule cellule. Comment pourrais-je résoudre ce problème rapidement et facilement dans Excel?

Les meilleurs outils de productivité de bureau

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 e-mails ...
  • Barre Super Formula (modifiez facilement plusieurs lignes de texte et de formule); Disposition de lecture (lire et modifier facilement un grand nombre de cellules); Coller dans la plage filtrée...
  • Fusionner les cellules / lignes / colonnes sans perdre de données; Contenu des cellules divisées; Combiner des lignes / colonnes en double... Empêcher les cellules en double; Comparer les gammes...
  • Sélectionnez Dupliquer ou Unique Lignes; Sélectionnez les lignes vides (toutes les cellules sont vides); Super Find et Fuzzy Find dans de nombreux classeurs; Sélection aléatoire ...
  • Copie exacte Plusieurs cellules sans changer la référence de formule; Créer automatiquement des références à plusieurs feuilles; Insérer des puces, Cases à cocher et plus encore ...
  • Extrait du texte, Ajouter du texte, Supprimer par position, Supprimer l'espace; Créer et imprimer des sous-totaux de pagination; Conversion entre le contenu et les commentaires des cellules...
  • 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 par gras, italique ...
  • Combiner des classeurs et des feuilles de travail; Fusionner les tableaux en fonction des colonnes clés; Diviser les données en plusieurs feuilles; Conversion par lots xls, xlsx et PDF...
  • Plus de 300 fonctionnalités puissantes. Prend en charge Office / Excel 2007-2019 et 365. Prend en charge toutes les langues. Déploiement facile dans votre entreprise ou organisation. Essai gratuit de 30 jours. Garantie de remboursement de 60 jours.
onglet kte 201905

Office Tab apporte une interface à onglets à Office et simplifie considérablement votre travail

  • Activer l'édition 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é de 50% et réduit des centaines de clics de souris chaque jour!
bas de cabine
Say something here...
symbols left.
You are guest
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.
    Ambet23 · 1 months ago
    hi everyone..
    i have problem..
    i got blank result even i press ctrl shift enter together..
  • To post as a guest, your comment is unpublished.
    Saravanan Kumar · 1 years ago
    Hi all, Can some help me to get all unique values on one single cell
  • To post as a guest, your comment is unpublished.
    J Sloth · 1 years ago
    Hi, this worked well! Although it takes Excel sooooo long to calculate. Just dragging down 15 cells in a column takes about 15min to calculate... if not longer. Is this normal? If this becomes dynamic it will take a hell of alot of computing time.
  • To post as a guest, your comment is unpublished.
    K · 1 years ago
    Hello. This is really helpful, however, what If I want a formula that lists the unique values based on multiple criteria. eg. I have a data set which has the following data in a table (after each hyphen is a new column but same row):

    Company A - £200 - £100
    Company A - £300 - £200
    Company B - £300 - £200
    Company C - £600 - £200
    Company B - £100 - £300
    Company D - £0 - £600
    Company A - £700 - £100

    I want a new data table in a new tab which groups the duplicate values without using an array formula. currently I'm grouping using a pivot table and pasting to my new data table. It's a long process but array formulas make my spreadsheet really slow.

    Company A - £1200 - £400
    Company B - £400 - £500
    Company C - £600 - £200
    Company D - £0 - £600

    Thanks,
    K
    • To post as a guest, your comment is unpublished.
      skyyang · 1 years ago
      Hello, K,
      For solving your problem, I can recommend our useful tool- Kutools for Excel, with its Advanced Combine Rows feature, you can deal with this job quickly. Firstly, you should copy and paste your data into a new worksheet, and then apply htis feature as below screenhsot shown.
      You can know more about this feature from: https://www.extendoffice.com/product/kutools-for-excel/excel-combine-duplicate-rows.html
      Please download Kutools for Excel and install it, then apply this feature. Full feature free trial 30-day, please try.
  • To post as a guest, your comment is unpublished.
    Giancarlo · 2 years 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 · 2 years 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.
        Astrid · 1 years ago
        Hi. I am using the two conditions formula =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)), "") to extract a unique list and it works great, but I am struggle to add the SMALL function to get the list sorted as well in ascending order. Are you able to help?
  • To post as a guest, your comment is unpublished.
    Konstantin · 2 years 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 · 2 years 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 · 3 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 · 3 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.
    jjer13 · 3 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 · 3 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.
        jjer13 · 3 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.
        jjer13 · 3 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.
        jjer13 · 3 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 · 3 years ago
    Thank You!
  • To post as a guest, your comment is unpublished.
    gon · 3 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.
      lucianir · 2 years 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 · 3 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 · 3 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.
    aditya047@gmail.com · 3 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 · 3 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 · 3 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 · 3 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 · 3 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.
      aditya047@gmail.com · 3 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 · 4 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 · 3 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 · 4 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 · 4 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 · 4 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 · 4 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 · 4 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 · 4 years ago
    Thank You, This is great!
  • To post as a guest, your comment is unpublished.
    Aileen · 4 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 · 4 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 · 4 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))