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 dynamiquement une liste de valeurs uniques d'une plage de colonnes dans Excel?

Pour une plage de colonnes dont les valeurs changent régulièrement, vous devez toujours obtenir toutes les valeurs uniques de la plage, quelle que soit la façon dont elles ont changé. Comment faire une liste dynamique de valeurs uniques? Cet article va vous montrer comment y faire face.

Extraire dynamiquement une liste de valeurs uniques d'une plage de colonnes avec une formule

Extraction dynamique d'une liste de valeurs uniques d'une plage de colonnes avec du code VBA


Sélectionnez facilement et mettez en surbrillance toutes les valeurs uniques d'une plage de colonnes dans Excel:

La Sélectionnez les cellules dupliquées et uniques utilité de Kutools for Excel peut vous aider à sélectionner et surligner facilement toutes les valeurs uniques (inclure les premiers doublons) ou les valeurs uniques à l'exception du premier, ainsi que les valeurs en double dont vous avez besoin comme illustré ci-dessous.Téléchargez dès maintenant la totalité des fonctionnalités de Kutools pour Excel pendant la journée 60!

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

Onglet Office Activer l'édition et la navigation par onglets dans Office, et simplifiez grandement votre travail ...
Kutools for Excel résout la plupart de vos problèmes et augmente votre productivité de 80%
  • Réutiliser n'importe quoi: Ajoutez les formules, graphiques et autres éléments les plus utilisés ou les plus complexes à vos favoris et réutilisez-les rapidement.
  • Plus que le texte 20 comprend: Extraire le numéro de la chaîne de texte; Extraire ou supprimer une partie des textes; Convertissez les nombres et les devises en mots anglais.
  • Fusionner les outils: Plusieurs classeurs et feuilles en un; Fusionner plusieurs cellules / lignes / colonnes sans perdre de données; Fusionner les lignes en double et la somme.
  • Outils Split: Fractionner les données en plusieurs feuilles en fonction de la valeur; Un classeur pour plusieurs fichiers Excel, PDF ou CSV; Une colonne à plusieurs colonnes.
  • Coller Sauter Lignes cachées / filtrées; Compte et somme par couleur de fond; Envoyez des e-mails personnalisés à plusieurs destinataires en bloc.
  • Super filtre: Créez des schémas de filtrage avancés et appliquez-les à toutes les feuilles. Trier par semaine, jour, fréquence et plus; Filtre en gras, formules, commentaires ...
  • Plus que de puissantes fonctionnalités 300; Fonctionne avec Office 2007-2019 et 365; Prend en charge toutes les langues; Déploiement facile dans votre entreprise ou organisation.

Extraire dynamiquement une liste de valeurs uniques d'une plage de colonnes avec une formule

Comme illustré ci-dessous, vous devez extraire dynamiquement une liste de valeurs uniques de la plage B2: B9. Essayez la formule matricielle suivante.

1. Sélectionnez une cellule vide telle que D2, entrez la formule =IFERROR(INDEX($B$2:$B$9, MATCH(0,COUNTIF($D$1:D1, $B$2:$B$9), 0)),"") (B2: B9 est la colonne de données dont vous voulez extraire les valeurs uniques, D1 est la cellule ci-dessus où se trouve votre formule) dans la barre de formule, puis appuyez sur la Ctrl + décalage + Entrer touches simultanément. Voir la capture d'écran:

2. Continuez à sélectionner la cellule D2, puis faites glisser le handle de remplissage vers le bas pour obtenir toutes les valeurs uniques de la plage spécifiée.

Maintenant, toutes les valeurs uniques dans la plage de colonnes B2: B9 sont extraites. Lorsque les valeurs de cette plage ont changé, la liste des valeurs uniques sera modifiée de manière dynamique immédiatement.


Extraction dynamique d'une liste de valeurs uniques d'une plage de colonnes avec du code VBA

Vous pouvez également extraire dynamiquement une liste de valeurs uniques à partir d'une plage de colonnes avec le code VBA suivant.

1. presse autre + F11 touches simultanément pour ouvrir le Microsoft Visual Basic pour applications fenêtre.

2. dans le Microsoft Visual Basic pour applications fenêtre, cliquez sur insérer > Module. Ensuite, copiez et collez le code VBA ci-dessous dans le Module fenêtre.

Code VBA: extraire une liste de valeurs uniques d'une plage

Sub CreateUniqueList()
Dim xRng As Range
Dim xLastRow As Long
Dim xLastRow2 As Long
Dim I As Integer
On Error Resume Next
Set xRng = Application.InputBox("Please select range:", "Kutools for Excel", Selection.Address, , , , , 8)
If xRng Is Nothing Then Exit Sub
On Error Resume Next
xRng.Copy Range("D2")
xLastRow = xRng.Rows.Count + 1
ActiveSheet.Range("D2:D" & xLastRow).RemoveDuplicates Columns:=1, Header:=xlNo
xLastRow2 = Cells(Rows.Count, "B").End(xlUp).Row
For I = 1 To xLastRow2
  If ActiveSheet.Range("D2:D" & xLastRow2).Cells(I).Value = "" Then
     ActiveSheet.Range("D2:D" & xLastRow2).Cells(I).Delete
  End If
Next
End Sub

Note: Dans le code, D2 est la cellule que vous localiserez la liste de valeur unique. Vous pouvez le changer selon vos besoins.

3. Revenez à la feuille de calcul, cliquez sur insérer > Formes > Rectangulaire. Voir la capture d'écran:

4. Dessinez un rectangle dans votre feuille de calcul, puis entrez quelques mots que vous devez afficher dessus. Puis faites un clic droit et sélectionnez Assigner une macro dans le menu contextuel. dans le Assigner une macro boîte de dialogue, sélectionnez le CreateUniqueList le Nom de la macro boîte, puis cliquez sur le OK bouton. Voir la capture d'écran:

5. Maintenant, cliquez sur le bouton rectangle, un Kutools for Excel La boîte de dialogue s'affiche, sélectionnez la plage contenant les valeurs uniques à extraire, puis cliquez sur OK .

A partir de maintenant, vous pouvez répéter l'étape 5 ci-dessus pour mettre à jour automatiquement la liste des valeurs uniques.


Office Tab - Navigation par onglets, édition et gestion de classeurs dans Excel:

Office Tab propose une interface à onglets comme celle des navigateurs Web tels que Google Chrome, les nouvelles versions d'Internet Explorer et Firefox pour Microsoft Excel. Ce sera un outil qui vous permettra de gagner du temps et d’être irremplaçable dans votre travail. Voir ci-dessous la démo:

Cliquez pour l'essai gratuit de l'onglet Office!

Onglet Office pour Excel


Articles connexes:


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.
    Charlotte · 4 months ago
    I try the VBA Code and the formula. The code VBA is working very well but I can't keep a file with macro. But th problem is that I can't make the formula working. Did anybody have an idea ? Thank you
    • To post as a guest, your comment is unpublished.
      crystal · 3 months ago
      Hi Charlotte,
      Thank you for your comment. You can keep the file with macro for future use by saving the workbook as an Excel Macro-Enabled Workbook.
      For the formula problem, would you please provide a screenshot of your data? Thank you for your comment.
  • To post as a guest, your comment is unpublished.
    Cameron · 4 months ago
    I'm using that =IFERROR(INDEX($B$2:$B$9, MATCH(0,COUNTIF($D$1:D1, $B$2:$B$9), 0)),"") formula which is great for one column but my data is spread across a range of columns and rows. Can I edit the formula to include the whole area? My data lives from AC4 to AR60...
  • To post as a guest, your comment is unpublished.
    Alexis · 9 months ago
    Hi, thank you for your help.
    Now, how can I have my values also sorted alphabetically? (I do not want to use the filter on my master table)
    Should I use a COUNTIFS instead of COUNTIF?
    Please HELP
    • To post as a guest, your comment is unpublished.
      crystal · 7 months ago
      Hi Alexis,
      Sorry can't hep to sort the extracted value alphabetically at the same time with formula. Thank you for your comment.
  • To post as a guest, your comment is unpublished.
    Charley · 11 months ago
    I am trying to drag the formula down past my actual data so that I can input different sized data sets and not have to adjust anything. However, the last row after my actual data ends always returns a "0". I am using the unique values for something else in an adjacent column, and the 0 causes the last value to repeated (When I delete the 0 the value is no longer repeated). Any idea how to fix this? Also I am using Office 365 Business
  • To post as a guest, your comment is unpublished.
    Steve Parker · 1 years ago
    If I do this for a thousand row excel sheet on the latest version of Excel on a Mac, it never returns. The first row works, but when I duplicate down, excel goes into a compute mode that has not returned values for over two hours now.

    Any thoughts on how to do this for large lists (up to 2k rows) that will return 50 or 60 unique values?

    I mocked this up in the "Numbers" app, and it's working perfectly there, taking only a couple minutes to calculate. It's just taking so long in Excel that I wonder if it will ever complete. I plan on letting it "run" overnight to see what will happen.
    • To post as a guest, your comment is unpublished.
      Josh · 10 months ago
      Check your Calculating options. It needs to be set to automatic. File > Options > Formulas > Calculating options > Workbook Calculation (Automatic selection)
  • To post as a guest, your comment is unpublished.
    Matt · 1 years ago
    How would you add a second variable? For instance, I want all the unique elements in one column that also share a similar value in another column. In your example, imagine a 3rd column titled "Department" that would have values like product, meat, etc. I realize those are all Produce, but hopefully you get my point. Would you modify the CountIF formula to a COUNTIFS or do you modify it in another way?
    • To post as a guest, your comment is unpublished.
      crystal · 1 years ago
      Hi Matt
      Please try this formula =IF(ISNA(VLOOKUP(A2,$C$2:$C$13,1,FALSE)),"Yes","").
      Supposing the two compared lists are Column A and Column C, If the unique values stay only in Column A but not in Column C, it will be displayed Yes in column B; while if return nothing in Column B, it means corresponding value stays in both Column A and Column C.
      • To post as a guest, your comment is unpublished.
        jyotiba mali · 1 years ago
        Thanks for the reply.. but hot to pull out that unique value if it displayed YES.. could you please advice me the formula to pull the unique value in different column.
  • To post as a guest, your comment is unpublished.
    Zac · 1 years ago
    How would you add multiple criteria, such as if you only wanted to add to the dynamic list if the date was just 9/12?

    I'm trying "&" in the MATCH formula, but it's not working.

    For example, based on your example:
    =IFERROR(INDEX($B$2:$B$9, MATCH(0 & B4,COUNTIF($D$1:D1, $B$2:$B$9) & $A$2:$A$9, 0)),"")
    This throws an error or creates duplicates.

    Alternatively, I've read that "+" might work, although I can't get it working. Or using SMALL.

    Ideas?
  • To post as a guest, your comment is unpublished.
    Mike · 2 years ago
    Hi Crystal,
    I am trying to use the VB version of the unique values list and running into an issue.
    The range that I want to create a unique values column from is all formulas that refer to different tabs.
    How does one get the value to transfer over instead of the formula?
    • To post as a guest, your comment is unpublished.
      Odette Meintjes · 1 years ago
      I have the same problem, except that my formula refer to column names and cannot convert to absolute.
      How do I change the vba to paste the values and not the formula?
    • To post as a guest, your comment is unpublished.
      crystal · 1 years ago
      Dear Mike,
      Please convert your formula references to absolute, and then apply the VB script.
  • To post as a guest, your comment is unpublished.
    Jones · 2 years ago
    Any tips on getting the VBA option to work with Excel 2016 for macOS? I have followed the steps; however, when I run the macro, nothing happens at all. Thanks!
    • To post as a guest, your comment is unpublished.
      crystal · 2 years ago
      Daer Jones,
      Please try the below VBA code and let me know whether it works for you. Thank you!

      Sub CreateUniqueList()
      Dim xRng As Range
      Dim xLastRow As Long
      Dim xLastRow2 As Long
      Dim I As Integer
      ' On Error Resume Next
      Set xRng = Application.InputBox("Please select range:", "Kutools for Excel", Selection.Address, , , , , 8)
      If xRng Is Nothing Then Exit Sub
      On Error Resume Next
      xRng.Copy Range("D2")
      xLastRow = xRng.Rows.Count + 1
      ActiveSheet.Range("D2:D" & xLastRow).RemoveDuplicates Columns:=1, Header:=xlNo
      xLastRow2 = Cells(Rows.Count, "B").End(xlUp).Row
      For I = 1 To xLastRow2
      If ActiveSheet.Range("D2:D" & xLastRow2).Cells(I).Value = "" Then
      ActiveSheet.Range("D2:D" & xLastRow2).Cells(I).Delete
      End If
      Next
      End Sub
  • To post as a guest, your comment is unpublished.
    Ryan · 2 years ago
    Also, for whatever reason, the original formula provided:
    =IFERROR(INDEX($B$2:$B$9, MATCH(0,COUNTIF($D$1:D1, $B$2:$B$9), 0)),"")

    returns a "circular reference" warning and will not calculate..
    • To post as a guest, your comment is unpublished.
      Andrew · 1 years ago
      I've had this happen before - my fix was that I was entering the formula into the cell D1 (equivalent in the worksheet I was using). Whichever cell the $D:$1 corresponds to you need to be entering it in the cell below - D2. Apologies if that's not why you got the error
    • To post as a guest, your comment is unpublished.
      crystal · 2 years ago
      Dear Ryan,
      Which Office version do you use? The formula works well in my Office 2016 and 2013.
  • To post as a guest, your comment is unpublished.
    Ryan · 2 years ago
    Hello, and thank you for your help.

    I need exactly this functionality, but my list of "unique values" needs to extend across columns instead of rows, so the expanding list down the rows won't work for me.

    How can I modify this formula in order to make the "unique values" list expand as I drag it across the columns?

    Offset()?
    Transpose()?
    Indirect() with a string of absolute references concatenated with a reference to the column instead of row?


    Thanks again!
    • To post as a guest, your comment is unpublished.
      crystal · 2 years ago
      Dear Ryan,
      This formula =IFERROR(INDEX($B$2:$B$9, MATCH(0,COUNTIF($D$2:D2, $B$2:$B$9), 0)),"") + Ctrl + Shift + Enter can help you to solve the problem.
      See below screenshot:
  • To post as a guest, your comment is unpublished.
    Viswanath Urala · 2 years ago
    {=INDEX($Q$3:$Q$263,MATCH(0,COUNTIF(V$2:V2,$Q$3:$Q$263),0))} - found this to work from another site...

    Use the Ctrl+Shift+Enter to get the array function (curly braces). Drag copy-paste the formulas until the #NA is shown. My data set was in Column-Q, it was compared to see if it existed in the unique's list in Column-V, which continually stretches along this same column.
    • To post as a guest, your comment is unpublished.
      crystal · 2 years ago
      Good Day.
      Please list all unique values of column Q with the abobv formula, and then use his formula =IF(D2=V1,"Match","No match") to compare if the uniques in cilumn Q compareing to column V in the same row.
  • To post as a guest, your comment is unpublished.
    Outdated · 2 years ago
    This formula is outdated and doesn't work. I literally just set this exact excel sheet up to see if I could get this formula to work and it doesnt.
  • To post as a guest, your comment is unpublished.
    PJ · 2 years ago
    I am decent at excel but I am really trying to wrap my head around how and why the above formula works (it works for what I am using it for but I must understand why). I get a little confused using arrays sometimes so any explanation in idiots terms would be extremely helpful

    Regards
  • To post as a guest, your comment is unpublished.
    Eric · 2 years ago
    Thank you for the tutorial. Using the formula method, how would you alter the formula if you wanted to add a category qualifer? Say in column C you distinguish whether the item is a fruit or a vegetable. How would you change the code to only sort the unique fruits and exclude the vegetables? I tried replacing COUNTIF with COUNTIFS, using the second countifs criteria of (LIST RANGE,"CATEGORY") but it returns blank. Would I need to expand my array and incorporate VLOOKUP?