Note: The other languages of the website are Google-translated. Back to English
English English

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, et vous devez toujours obtenir toutes les valeurs uniques de la plage, quelle que soit sa modification. Comment faire une liste dynamique de valeurs uniques? Cet article vous montrera comment y faire face.

Extraire dynamiquement une liste de valeurs uniques d'une plage de colonnes avec une formule
Extraire dynamiquement une liste de valeurs uniques à partir d'une plage de colonnes avec le code VBA


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. Veuillez essayer la formule matricielle suivante.

1. Sélectionnez une cellule vide telle que D2, entrez la formule ci-dessous et appuyez sur la touche Ctrl + Mission + Entrer touches simultanément. (B2: B9 est les données de la colonne dont vous souhaitez extraire les valeurs uniques, D1 est la cellule ci-dessus où se trouve votre formule)

=IFERROR(INDEX($B$2:$B$9, MATCH(0,COUNTIF($D$1:D1, $B$2:$B$9), 0)),"")

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

Désormais, toutes les valeurs uniques de la plage de colonnes B2: B9 sont extraites. Lorsque les valeurs de cette plage ont changé, la liste de valeurs uniques sera immédiatement modifiée dynamiquement.

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

La série Sélectionnez les cellules dupliquées et uniques utilité de Kutools pour Excel peut vous aider à sélectionner et mettre en évidence facilement toutes les valeurs uniques (y compris les premiers doublons) ou les valeurs uniques qui n'apparaissent qu'une seule fois, ainsi que les valeurs en double selon vos besoins, comme illustré ci-dessous.
Téléchargez Kutools for Excel maintenant! (Parcours gratuit de 30 jours)


Extraire dynamiquement une liste de valeurs uniques à partir d'une plage de colonnes avec le code VBA

Vous pouvez également extraire une liste de valeurs uniques de manière dynamique à 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

Notes: Dans le code, D2 est la cellule dans laquelle vous localiserez la liste de valeurs uniques. Vous pouvez le modifier 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 les mots que vous devez afficher dessus. Puis faites un clic droit dessus et sélectionnez Attribuer une macro dans le menu contextuel. dans le Attribuer une macro boîte de dialogue, sélectionnez le CréerListeUnique dans le Nom de la macro , puis cliquez sur le OK bouton. Voir la capture d'écran:

5. Cliquez maintenant sur le bouton rectangle, un Kutools pour Excel s'affiche, sélectionnez la plage contenant les valeurs uniques que vous devez extraire, puis cliquez sur le OK .

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


Articles connexes:


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éeplus
  • 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 gammesplus
  • 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 cellulesplus
  • 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 PDFplus
  • 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
Commentaires (35)
Pas encore de notes. Soyez le premier à évaluer!
Ce commentaire a été minimisé par le modérateur sur le site
Merci pour le tutoriel. En utilisant la méthode de la formule, comment modifieriez-vous la formule si vous vouliez ajouter un qualificatif de catégorie ? Disons que dans la colonne C, vous distinguez si l'article est un fruit ou un légume. Comment modifieriez-vous le code pour ne trier que les fruits uniques et exclure les légumes ? J'ai essayé de remplacer COUNTIF par COUNTIFS, en utilisant le deuxième critère countifs de (LIST RANGE,"CATEGORY") mais il retourne vide. Aurais-je besoin d'étendre mon tableau et d'incorporer VLOOKUP ?
Ce commentaire a été minimisé par le modérateur sur le site
Je suis décent à Excel mais j'essaie vraiment de comprendre comment et pourquoi la formule ci-dessus fonctionne (elle fonctionne pour ce que je l'utilise mais je dois comprendre pourquoi). Je suis parfois un peu confus en utilisant des tableaux, donc toute explication en termes idiots serait extrêmement utile Cordialement
Ce commentaire a été minimisé par le modérateur sur le site
Cette formule est obsolète et ne fonctionne pas. Je viens littéralement de configurer cette feuille Excel exacte pour voir si je pouvais faire fonctionner cette formule et ce n'est pas le cas.
Ce commentaire a été minimisé par le modérateur sur le site
Salut mec,
Quelle version d'Office utilisez-vous ?
Ce commentaire a été minimisé par le modérateur sur le site
{=INDEX($Q$3:$Q$263,MATCH(0,COUNTIF(V$2:V2,$Q$3:$Q$263),0))} - a trouvé que cela fonctionnait à partir d'un autre site...

Utilisez Ctrl+Maj+Entrée pour obtenir la fonction de tableau (accolades). Faites glisser le copier-coller des formules jusqu'à ce que le #NA s'affiche. Mon ensemble de données était dans Column-Q, il a été comparé pour voir s'il existait dans la liste des uniques dans Column-V, qui s'étend continuellement le long de cette même colonne.
Ce commentaire a été minimisé par le modérateur sur le site
Bon jour.
Veuillez répertorier toutes les valeurs uniques de la colonne Q avec la formule abobv, puis utilisez sa formule =IF(D2=V1,"Match","No match") pour comparer si les uniques dans la colonne Q se comparent à la colonne V dans la même ligne .
Ce commentaire a été minimisé par le modérateur sur le site
Bonjour et merci pour votre aide.

J'ai exactement besoin de cette fonctionnalité, mais ma liste de "valeurs uniques" doit s'étendre sur des colonnes plutôt que sur des lignes, de sorte que la liste en expansion vers le bas des lignes ne fonctionnera pas pour moi.

Comment puis-je modifier cette formule afin de développer la liste des "valeurs uniques" lorsque je la fais glisser sur les colonnes ?

Décalage()?
Transposer()?
Indirect() avec une chaîne de références absolues concaténées avec une référence à la colonne au lieu de la ligne ?


Merci encore!
Ce commentaire a été minimisé par le modérateur sur le site
Cher Ryan,
Cette formule =IFERROR(INDEX($B$2:$B$9, MATCH(0,COUNTIF($D$2:D2, $B$2:$B$9), 0)),"") + Ctrl + Shift + Enter peut vous aider à résoudre le problème.
Voir capture d'écran ci-dessous :
Ce commentaire a été minimisé par le modérateur sur le site
De plus, pour une raison quelconque, la formule originale prévoyait :
=IFERROR(INDEX($B$2:$B$9, MATCH(0,COUNTIF($D$1:D1, $B$2:$B$9), 0)),"")

renvoie un avertissement "référence circulaire" et ne calcule pas..
Ce commentaire a été minimisé par le modérateur sur le site
Cher Ryan,
Quelle version d'Office utilisez-vous ? La formule fonctionne bien dans mon Office 2016 et 2013.
Ce commentaire a été minimisé par le modérateur sur le site
Cela m'est déjà arrivé auparavant - ma solution était que je saisissais la formule dans la cellule D1 (équivalente dans la feuille de calcul que j'utilisais). Quelle que soit la cellule à laquelle $D:$1 correspond, vous devez l'entrer dans la cellule ci-dessous - D2. Toutes mes excuses si ce n'est pas la raison pour laquelle vous avez l'erreur
Ce commentaire a été minimisé par le modérateur sur le site
Des conseils pour que l'option VBA fonctionne avec Excel 2016 pour macOS ? J'ai suivi les étapes; Cependant, lorsque j'exécute la macro, rien ne se passe du tout. Merci!
Ce commentaire a été minimisé par le modérateur sur le site
Daer Jones,
Veuillez essayer le code VBA ci-dessous et faites-moi savoir si cela fonctionne pour vous. Merci!

Sous CréerListeUnique()
Dim xRng As Range
Dim xLastRow As Long
Dim xLastRow2 As Long
Dim i comme entier
' En cas d'erreur, reprendre le suivant
Set xRng = Application.InputBox("Veuillez sélectionner la plage :", "Kutools for Excel", Selection.Address, , , , , 8)
Si xRng n'est rien, quittez 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
Pour je = 1 à xLastRow2
Si ActiveSheet.Range("D2:D" & xLastRow2).Cells(I).Value = "" Alors
ActiveSheet.Range("D2:D" & xLastRow2).Cells(I).Delete
Si fin
Suivant
End Sub
Ce commentaire a été minimisé par le modérateur sur le site
Salut Crystal,
J'essaie d'utiliser la version VB de la liste de valeurs uniques et je rencontre un problème.
La plage à partir de laquelle je souhaite créer une colonne de valeurs uniques est constituée de toutes les formules faisant référence à différents onglets.
Comment obtient-on la valeur à transférer au lieu de la formule ?
Ce commentaire a été minimisé par le modérateur sur le site
Cher Mike,
Veuillez convertir vos références de formule en absolu, puis appliquer le script VB.
Ce commentaire a été minimisé par le modérateur sur le site
J'ai le même problème, sauf que ma formule fait référence aux noms de colonnes et ne peut pas être convertie en absolu.
Comment changer le vba pour coller les valeurs et non la formule?
Ce commentaire a été minimisé par le modérateur sur le site
Comment ajouteriez-vous plusieurs critères, par exemple si vous ne vouliez ajouter à la liste dynamique que si la date n'était que le 9 septembre ?

J'essaie "&" dans la formule MATCH, mais cela ne fonctionne pas.

Par exemple, d'après votre exemple :
=SIERREUR(INDEX($B$2:$B$9, MATCH(0 & B4,COUNTIF($D$1:D1, $B$2:$B$9) & $A$2:$A$9, 0)),"" )
Cela génère une erreur ou crée des doublons.

Alternativement, j'ai lu que "+" pourrait fonctionner, bien que je ne puisse pas le faire fonctionner. Ou en utilisant PETIT.

Idées?
Ce commentaire a été minimisé par le modérateur sur le site
Cher Zack,
Désolé, je ne peux pas vous aider, vous pouvez poser votre question sur notre forum : https://www.extendoffice.com/forum.html pour obtenir plus de supports Excel de notre professionnel.
Ce commentaire a été minimisé par le modérateur sur le site
Comment ajouteriez-vous une deuxième variable ? Par exemple, je veux que tous les éléments uniques d'une colonne partagent également une valeur similaire dans une autre colonne. Dans votre exemple, imaginez une 3ème colonne intitulée "Département" qui aurait des valeurs comme produit, viande, etc. Je réalise que ce sont tous des produits, mais j'espère que vous comprenez mon point. Modifiez-vous la formule CountIF en COUNTIFS ou la modifiez-vous d'une autre manière ?
Ce commentaire a été minimisé par le modérateur sur le site
salut Matt
Veuillez essayer cette formule =IF(ISNA(VLOOKUP(A2,$C$2:$C$13,1,FALSE)),"Yes","").
En supposant que les deux listes comparées sont la colonne A et la colonne C, si les valeurs uniques restent uniquement dans la colonne A mais pas dans la colonne C, il sera affiché Oui dans la colonne B ; tandis que si rien ne retourne dans la colonne B, cela signifie que la valeur correspondante reste à la fois dans la colonne A et la colonne C.
Ce commentaire a été minimisé par le modérateur sur le site
Merci pour la réponse .. mais chaud pour extraire cette valeur unique si elle affichait OUI .. pourriez-vous s'il vous plaît me conseiller la formule pour extraire la valeur unique dans une colonne différente.
Ce commentaire a été minimisé par le modérateur sur le site
Si je fais cela pour une feuille Excel de mille lignes sur la dernière version d'Excel sur un Mac, elle ne revient jamais. La première ligne fonctionne, mais lorsque je duplique, Excel passe en mode de calcul qui n'a pas renvoyé de valeurs depuis plus de deux heures maintenant.

Des idées sur la façon de procéder pour les grandes listes (jusqu'à 2 50 lignes) qui renverront 60 ou XNUMX valeurs uniques ?

J'ai simulé cela dans l'application "Numbers", et cela fonctionne parfaitement là-bas, ne prenant que quelques minutes à calculer. Cela prend tellement de temps dans Excel que je me demande si cela se terminera un jour. Je prévois de le laisser "tourner" pendant la nuit pour voir ce qui se passera.
Ce commentaire a été minimisé par le modérateur sur le site
Vérifiez vos options de calcul. Il doit être réglé sur automatique. Fichier > Options > Formules > Options de calcul > Calcul du classeur (sélection automatique)
Ce commentaire a été minimisé par le modérateur sur le site
J'essaie de faire glisser la formule au-delà de mes données réelles afin de pouvoir saisir des ensembles de données de tailles différentes sans avoir à ajuster quoi que ce soit. Cependant, la dernière ligne après la fin de mes données réelles renvoie toujours un "0". J'utilise les valeurs uniques pour autre chose dans une colonne adjacente, et le 0 provoque la répétition de la dernière valeur (lorsque je supprime le 0, la valeur n'est plus répétée). Une idée de comment résoudre ce problème ? J'utilise aussi Office 365 Business
Ce commentaire a été minimisé par le modérateur sur le site
Bonjour, merci pour votre aide.
Maintenant, comment puis-je également trier mes valeurs par ordre alphabétique ? (je ne souhaite pas utiliser le filtre sur ma table master)
Dois-je utiliser un COUNTIFS au lieu de COUNTIF ?
S'il vous plaît AIDER
Ce commentaire a été minimisé par le modérateur sur le site
Salut Alexis,
Désolé, je ne peux pas aider à trier la valeur extraite par ordre alphabétique en même temps que la formule. Merci pour votre commentaire.
Ce commentaire a été minimisé par le modérateur sur le site
J'utilise cette formule =IFERROR(INDEX($B$2:$B$9, MATCH(0,COUNTIF($D$1:D1, $B$2:$B$9), 0)),"") qui est géniale pour une colonne, mais mes données sont réparties sur une plage de colonnes et de lignes. Puis-je modifier la formule pour inclure toute la zone ? Mes données vivent de AC4 à AR60...
Ce commentaire a été minimisé par le modérateur sur le site
J'essaie le code VBA et la formule. Le code VBA fonctionne très bien mais je n'arrive pas à conserver un fichier avec macro. Mais le problème est que je ne peux pas faire fonctionner la formule. Quelqu'un aurait-il une idée ? Merci
Ce commentaire a été minimisé par le modérateur sur le site
Salut charlotte
Merci pour votre commentaire. Vous pouvez conserver le fichier avec la macro pour une utilisation future en enregistrant le classeur en tant que classeur Excel prenant en charge les macros.
Pour le problème de formule, pourriez-vous fournir une capture d'écran de vos données ? Merci pour votre commentaire.
Ce commentaire a été minimisé par le modérateur sur le site
Merci beaucoup
Ce commentaire a été minimisé par le modérateur sur le site
comment faire fonctionner le code vba pour une plage où une autre formule a été utilisée? dans la colonne BI, avoir une formule, faisant référence aux colonnes D et E.
Si j'utilise appliquer le code à la colonne L (disons), (évidemment, en modifiant correctement les cellules dans le code) la macro renvoie la formule appliquée aux colonnes M et N... Ça marche donc, mais pas comme je veux ! Comment conserver les valeurs dans la colonne B ? Merci
Ce commentaire a été minimisé par le modérateur sur le site
Ik heb gemerkt dat de 'formule'-methode erg traag werkt bij gros ensembles de données. Een goed alternatief is het gebruik van een draaitabel. Kies dan alleen de rijlabels, je krijgt dan een lijstje met unieke waarden. Het kan zijn dat je wat extra's krijgt "(leeg)" bijvoorbeeld. Je kunt deze er vervolgens uitfilteren. Helaas kun je maar op 1 criterium filteren. Ook daar zijn wel weer oplossingen voor, maar dat est wat complexer.
Ce commentaire a été minimisé par le modérateur sur le site
J'aimerais pouvoir faire exactement la même chose, sauf qu'en utilisant deux plages de colonnes distinctes (B2: B9) ainsi que (D2: D9), est-ce possible ?
Ce commentaire a été minimisé par le modérateur sur le site
Salut Anthony,
Vous pouvez placer les résultats dans la même colonne que les données d'origine. Comme la colonne B dans ce cas.
Mais vous devez référencer la cellule supérieure de la cellule de résultat dans la formule comme suit.
=SIERREUR(INDEX($B$2:$B$9, MATCH(0,COUNTIF($B$11:B11, $B$2:$B$9), 0)),"") + Ctrl + Maj + Entrée
Ce commentaire a été minimisé par le modérateur sur le site
con este procedimiento de filtro se hace de forma muy rapida

1.EN ESTE EJEMPLO los datos a remover los duplicados estan en la col A de la fila 59 a la 239
2. se définir un critère de filtrado en este caso en la fila d56 el mismo titulo de la lista a remover duplicados y la d57 la dejo en blanco
3. una vez ejecutado se muestran los datos en la fila destin, que en mi caso fue la d59

Range("A59:A239").AdvancedFilter Action :=xlFilterCopy, CriteriaRange :=Range _
("D56:D57"), CopyToRange :=Plage("D59"), Unique :=Vrai
Il n'y a pas encore de commentaires postés ici
Laisser vos commentaires
Publier en tant qu'invité
×
Évaluez cet article:
0   Personnages
Emplacements suggérés