Passer au contenu principal

Comment extraire des valeurs uniques de plusieurs colonnes dans Excel?

En supposant que vous ayez plusieurs colonnes avec plusieurs valeurs, certaines valeurs sont répétées dans la même colonne ou dans une colonne différente. Et maintenant, vous voulez trouver les valeurs présentes dans l'une ou l'autre colonne une seule fois. Existe-t-il des astuces rapides pour extraire des valeurs uniques de plusieurs colonnes dans Excel?


Extraire des valeurs uniques de plusieurs colonnes avec une formule matricielle

Voici une formule matricielle qui peut également vous aider à extraire les valeurs uniques de plusieurs colonnes.

1. En supposant vos valeurs dans la plage A2: C9, veuillez entrer la formule suivante dans la cellule E2:

=INDIRECT(TEXT(MIN(IF(($A$2:$C$9<>"")*(COUNTIF($E$1:E1,$A$2:$C$9)=0),ROW($2:$9)*100+COLUMN($A:$C),7^8)),"R0C00"),)&""

Notes: Dans la formule ci-dessus, A2: C9 indique la plage de cellules dont vous souhaitez extraire les valeurs uniques, E1: E1 est la première cellule de la colonne dans laquelle vous souhaitez placer le résultat, $ 2: $ 9 se dresse sur les lignes contiennent les cellules que vous souhaitez utiliser, et $ A: $ C indique que les colonnes contiennent les cellules que vous souhaitez utiliser. Veuillez les changer pour les vôtres.

2. Puis appuyez Maj + Ctrl + Entrée clés ensemble, puis faites glisser la poignée de recopie pour extraire les valeurs uniques jusqu'à ce que des cellules vides apparaissent. Voir la capture d'écran:


Extraire des valeurs uniques de plusieurs colonnes avec le tableau croisé dynamique

Si vous connaissez le tableau croisé dynamique, vous pouvez facilement extraire les valeurs uniques de plusieurs colonnes en suivant les étapes suivantes:

1. Dans un premier temps, veuillez insérer une nouvelle colonne vide à gauche de vos données, dans cet exemple, je vais insérer la colonne A à côté des données d'origine.

2. Cliquez sur une cellule de vos données et appuyez sur Alt + D touches, puis appuyez sur P clé immédiatement pour ouvrir le Assistant de tableau croisé dynamique et graphique croisé dynamique, choisissez Plusieurs plages de consolidation dans l'assistant step1, voir capture d'écran:

3. Puis clique Suivant bouton, vérifier Créez un champ de page unique pour moi option dans l'assistant step2, voir capture d'écran:

4. Continuez à cliquer Suivant , cliquez pour sélectionner la plage de données comprenant la nouvelle colonne de cellules de gauche, puis cliquez sur Ajouter pour ajouter la plage de données au Toutes les gammes zone de liste, voir capture d'écran:

5. Après avoir sélectionné la plage de données, continuez à cliquer Suivant, à l'étape 3 de l'assistant, choisissez où vous souhaitez placer le rapport de tableau croisé dynamique comme vous le souhaitez.

6. Enfin, cliquez Finition pour terminer l'assistant, et un tableau croisé dynamique a été créé dans la feuille de calcul actuelle, puis décochez tous les champs de la Choisissez les champs à ajouter au rapport section, voir capture d'écran:

7. Vérifiez ensuite le champ Valeur ou faites glisser la valeur vers le rangées label, vous obtiendrez maintenant les valeurs uniques des multiples colonnes comme suit:


Extraire des valeurs uniques de plusieurs colonnes avec le code VBA

Avec le code VBA suivant, vous pouvez également extraire les valeurs uniques de plusieurs colonnes.

1. Maintenez le ALT + F11 clés, et il ouvre le Fenêtre Microsoft Visual Basic pour Applications.

2Cliquez sur insérer > Moduleet collez le code suivant dans la fenêtre Module.

VBA: extraire des valeurs uniques de plusieurs colonnes

Sub Uniquedata()
'Updateby Extendoffice
Dim rng As Range
Dim InputRng As Range, OutRng As Range
Set dt = CreateObject("Scripting.Dictionary")
xTitleId = "KutoolsforExcel"
Set InputRng = Application.Selection
Set InputRng = Application.InputBox("Range :", xTitleId, InputRng.Address, Type:=8)
Set OutRng = Application.InputBox("Out put to (single cell):", xTitleId, Type:=8)
For Each rng In InputRng
    If rng.Value <> "" Then
        dt(rng.Value) = ""
    End If
Next
OutRng.Range("A1").Resize(dt.Count) = Application.WorksheetFunction.Transpose(dt.Keys)
End Sub

3. Puis appuyez F5 pour exécuter ce code, et une boîte de dialogue apparaîtra pour vous rappeler de sélectionner la plage de données que vous souhaitez utiliser. Voir la capture d'écran:

4. Et puis cliquez OK, une autre boîte de dialogue apparaîtra pour vous permettre de choisir un endroit pour mettre le résultat, voir capture d'écran:

5. Cliquez OK pour fermer cette boîte de dialogue, et toutes les valeurs uniques ont été extraites en même temps.


Extraire des valeurs uniques d'une seule colonne avec une fonctionnalité étonnante

Parfois, vous devez extraire les valeurs uniques d'une seule colonne, les méthodes ci-dessus ne vous aideront pas, ici, je peux recommander un outil utile-Kutools for Excel, Avec son Extraire des cellules avec des valeurs uniques (inclure le premier duplicata) utilitaire, vous pouvez extraire rapidement les valeurs uniques.

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

Après l'installation de Kutools for 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:

  • Sélectionnez 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 et faites glisser la poignée de remplissage vers les cellules pour lesquelles vous souhaitez répertorier toutes les valeurs uniques jusqu'à ce que les cellules vides s'affichent, 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.
  • Extraire des valeurs uniques basées sur des critères dans Excel
  • Supposons que vous ayez la plage de données suivante que vous souhaitez lister 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?
  • Autoriser uniquement les valeurs uniques dans Excel
  • Si vous souhaitez conserver uniquement des valeurs uniques entrant dans une colonne de feuille de calcul et éviter les doublons, cet article présentera quelques astuces rapides pour vous permettre de gérer cette tâche.
  • 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?

Meilleurs outils de productivité bureautique

Caractéristiques populaires: Rechercher, mettre en évidence ou identifier les doublons   |  Supprimer les lignes vides   |  Combinez des colonnes ou des cellules sans perdre de données   |   Tour sans formule 
Super recherche: VSearchup à critères multiples    VSearch à valeurs multiples  |   Recherche virtuelle sur plusieurs feuilles   |   Recherche floue ....
Liste déroulante avancée: Créez rapidement une liste déroulante   |  Liste déroulante dépendante   |  Liste déroulante à sélection multiple ....
Gestionnaire de colonnes: Ajouter un nombre spécifique de colonnes  |  Déplacer les colonnes  |  Basculer l'état de visibilité des colonnes masquées  |  Comparer les plages et les colonnes 
Caractéristiques en vedette: Mise au point de la grille   |  Voir Design   |   Grande barre de formule    Gestionnaire de classeurs et de feuilles   |  Centre de ressources (Texte automatique)   |  Sélecteur de date   |  Combiner des feuilles de travail   |  Crypter/déchiffrer les cellules    Envoyer des e-mails par liste   |  Super filtre   |   Filtre spécial (filtre gras/italique/barré...) ...
Les 15 meilleurs ensembles d'outils12 Texte Outils (Ajouter du texte, Supprimer les caractères, ...)   |   50+ Graphique Types (Diagramme de Gantt, ...)   |   40+ Pratique Formules (Calculer l'âge en fonction de l'anniversaire, ...)   |   19 Insertion Outils (Insérer le code QR, Insérer une image à partir du chemin, ...)   |   12 Conversion Outils (Nombres en mots, Conversion des devises, ...)   |   7 Fusionner et fractionner Outils (Lignes de combinaison avancées, Cellules divisés, ...)   |   ... et plus

Améliorez vos compétences Excel avec Kutools for Excel et faites l'expérience d'une efficacité comme jamais auparavant. Kutools for Excel offre plus de 300 fonctionnalités avancées pour augmenter la productivité et gagner du temps.  Cliquez ici pour obtenir la fonctionnalité dont vous avez le plus besoin...

onglet kte 201905


Office Tab apporte une interface à onglets à Office et facilite grandement 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!
Comments (31)
Rated 5 out of 5 · 1 ratings
This comment was minimized by the moderator on the site
Thank you for this solution, however what if the columns are in separate excel sheet? or is in separate columns instead of a table?
This comment was minimized by the moderator on the site
Hello, Jon,
The methods in this article ar only works well for a range of data, if your data in separate columns, you should copy and paste them into one range first, and then apply the formula or VBA code.
Thank you!
This comment was minimized by the moderator on the site
Thank you for this great article.

For people who are using the array formular in non-English Excel there must be taken special care of the text format string: in your example: "R0C00".
For German this would translate to "Z0S00". However, "S" is a special character refering to seconds for time formating. This character needs to be escaped and therefore the correct format string for German Excel is "Z0\S00".

I hope this helps someone in the future :-)
This comment was minimized by the moderator on the site
Hello, Michael H.
Thanks for your kindly explanation.
Hope this can help others in the future.😄
This comment was minimized by the moderator on the site
Thank you for this great article.

For people who are using the array formular in non-English Excel there must be taken special care of the text format string: in your example: "R0C00".
For German this would translate to "Z0S00". However, "S" is a special character refering to seconds for time formating. This character needs to be escaped and therefore the correct format string for German Excel is "Z0\S00".

I hope this helps someone in the future :-)
This comment was minimized by the moderator on the site
Hello Sir! The VBA worked wonders, thank you very much for that! I was wondering, If I change the original data, is it possible to refresh the column with the unique values automatically?
Rated 5 out of 5
This comment was minimized by the moderator on the site
Hello Ioannis,

Glad to help. After you change the original data, the VBA can not refresh the result automatically. And the easiest way I can think of is to press Ctrl + Alt + F9 to refresh all results in worksheets in all open workbooks. Have a nice day.

Sincerely,
Mandy
This comment was minimized by the moderator on the site
The array formula at the top is working great when used with data in the same sheet, however when I try to use it to reference the same exact data from another sheet the formula returns nothing. I'm unable to figure out why. Is there a limitation with array functions that prevents you from referencing ranges in a different sheet?

Thanks for any insight you can provide.
This comment was minimized by the moderator on the site
Hello Erin,

Glad to help. The INDIRECT function in this formula is more complicated to use when referencing data in other worksheets. It is not recommended to use this feature when referencing ranges in different worksheets.

For example: Now the data is in Sheet1, I want to reference the content of cell C2 of Sheet1 in Sheet2. First, in any two cells in Sheet2, such as D1 and D2, enter Sheet1 and C2, respectively. At this point, enter the formula in the empty cell of Sheet2:
=INDIRECT("'"&D1&"'!"&D2), then the content of cell C2 in Sheet1 can be returned.

As you can see, it make things way more complex. Hope my explanation can help. Have a nice day.

Sincerely,
Mandy
This comment was minimized by the moderator on the site
Czy to żart?
This comment was minimized by the moderator on the site
can we create uniqdata function instead of macro?
This comment was minimized by the moderator on the site
Hi, İlhan,If you like a User Defined Function to create a formula for solving this problem, the below code may help you:After inserting the code, select a list of cells where you want to put the results. Then type this formula:=Uniques(A1:C4)  in the formula bar.Press Ctrl+Shift+Enter keys together. 


Function Uniques(ByVal inputRange As Range)
Dim inputArray As Variant
Dim myColl As New Collection
Dim xVal As Variant
Dim outArray() As Variant
On Error Resume Next
With inputRange
inputArray = Application.Intersect(.Cells, .Parent.UsedRange).Value
End With
On Error GoTo 0
On Error Resume Next
For Each xVal In inputArray
myColl.*** Item:=xVal, Key:=(CStr(xVal) & TypeName(xVal))
Next xVal
myColl.Remove "String"
On Error GoTo 0
ReDim outArray(1 To Application.Max(myColl.Count, Application.Caller.Cells.Count))
For xVal = 1 To UBound(outArray)
outArray(xVal) = vbNullString
Next xVal
For xVal = 1 To Application.Min(myColl.Count, Application.Caller.Cells.Count)
outArray(xVal) = myColl(xVal)
Next xVal
If Application.Caller.Columns.Count = 1 Then
Uniques = Application.Transpose(outArray)
Else
Uniques = outArray
End If
End Function
This comment was minimized by the moderator on the site
Thanks for the code. I'm using the VBA code of this page. Is there a way to add a sorting code after the unique values are extracted so it sorts it automatically?
This comment was minimized by the moderator on the site
Regarding the formula version, could you explain in more detail what this portion is doing? *100+COLUMN($A:$C),7^8)),"R0C00") Specifically, what are the *100, 7^8, and "R0C000" doing? I'm understanding everything else, but I can't figure out what these are for.
This comment was minimized by the moderator on the site
Little late for my response here but...
ROW($2:$9)*100 - this is multiplying the row number *100, so if it's in row 5, now the number is 500
COLUMN($A:$C) - this gets added to the row*100 number, so if it's row 5 col 2, then the number is 502.
7^8)), - this (I think) is to have a max value for the min statement from earlier.
"R0C00") - this formats the text based on the number. In the example, we had 502 so this gives R5C02 (row 5, col 02).

If you have a lot of columns but not many rows, then you could change it to ROW($2:$9)*1000+COLUMN($A:$C),7^8)),"R0C000")
This comment was minimized by the moderator on the site
i've adjusted to my sheet but am only returning the first value in the defined array... what am i missing?
This comment was minimized by the moderator on the site
Hello, Cody,
The above formula works well in my worksheet, could you give a screenshot of your data problem here?
Thank you!
There are no comments posted here yet
Load More
Please leave your comments in English
Posting as Guest
×
Rate this post:
0   Characters
Suggested Locations