Vai al contenuto principale

Come estrarre valori univoci da più colonne in Excel?

Supponendo di avere più colonne con più valori, alcuni valori vengono ripetuti nella stessa colonna o in una colonna diversa. E ora vuoi trovare i valori che sono presenti in entrambe le colonne solo una volta. Esistono trucchi rapidi per estrarre valori univoci da più colonne in Excel?


Estrai valori univoci da più colonne con la formula di matrice

Ecco una formula di matrice che può anche aiutarti a estrarre i valori univoci da più colonne.

1. Supponendo che i tuoi valori siano nell'intervallo A2: C9, inserisci la seguente formula nella cella 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"),)&""

Note:: Nella formula sopra, A2: C9 indica l'intervallo di celle da cui si desidera estrarre i valori univoci, E1: E1 è la prima cella della colonna in cui vuoi inserire il risultato, $ 2: $ 9 sta per le righe che contengono le celle che si desidera utilizzare e $ A: $ C indica che le colonne contengono le celle che desideri utilizzare. Si prega di cambiarli con i propri.

2. Quindi premere Maiusc + Ctrl + Invio insieme alle chiavi, quindi trascina il quadratino di riempimento per estrarre i valori univoci finché non vengono visualizzate celle vuote. Vedi screenshot:


Estrai valori univoci da più colonne con la tabella pivot

Se hai familiarità con la tabella pivot, puoi facilmente estrarre i valori univoci da più colonne con i seguenti passaggi:

1. All'inizio, inserisci una nuova colonna vuota a sinistra dei tuoi dati, in questo esempio, inserirò la colonna A accanto ai dati originali.

2. Fare clic su una cella nei dati e premere Alt + D tasti, quindi premere P immediatamente il tasto per aprire il file Creazione guidata tabella pivot e grafico pivotscegli Intervalli di consolidamento multipli nel passaggio 1 della procedura guidata, vedi screenshot:

3. Quindi fare clic Successivo pulsante, controllare Crea un singolo campo pagina per me opzione nel passaggio 2 della procedura guidata, vedi screenshot:

4. Continua a fare clic Successivo fare clic per selezionare l'intervallo di dati che include la nuova colonna di celle sinistra, quindi fare clic Aggiungi pulsante per aggiungere l'intervallo di dati al file Tutte le gamme casella di riepilogo, vedi screenshot:

5. Dopo aver selezionato l'intervallo di dati, continuare a fare clic Successivo, nel passaggio 3 della procedura guidata, scegli dove desideri inserire il rapporto di tabella pivot come preferisci.

6. Finalmente, clicca Fine per completare la procedura guidata e una tabella pivot è stata creata nel foglio di lavoro corrente, quindi deselezionare tutti i campi dal Scegli i campi da aggiungere al rapporto sezione, vedi screenshot:

7. Quindi controlla il campo Valore o trascina il valore sul file Righe etichetta, ora otterrai i valori univoci dalle più colonne come segue:


Estrai valori univoci da più colonne con codice VBA

Con il seguente codice VBA, puoi anche estrarre i valori univoci da più colonne.

1. Tieni premuto il ALT + F11 chiavi e apre il file Finestra di Microsoft Visual Basic, Applications Edition.

2. Clic inserire > Modulie incolla il codice seguente nella finestra del modulo.

VBA: estrae valori univoci da più colonne

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. Quindi premere F5 per eseguire questo codice e verrà visualizzata una finestra di messaggio per ricordarti di selezionare l'intervallo di dati che desideri utilizzare. Vedi screenshot:

4. E poi clicca OK, apparirà un'altra finestra di messaggio per consentirti di scegliere un posto in cui inserire il risultato, vedi screenshot:

5. Clicchi OK per chiudere questa finestra di dialogo e tutti i valori univoci sono stati estratti contemporaneamente.


Estrai valori univoci da una singola colonna con una funzionalità straordinaria

A volte, è necessario estrarre i valori univoci da una singola colonna, i metodi sopra non ti aiuteranno, qui, posso consigliare uno strumento utile-Kutools for Excel, Con la sua Estrai celle con valori univoci (includi il primo duplicato) utility, puoi estrarre rapidamente i valori univoci.

Nota:Per applicare questo Estrai celle con valori univoci (includi il primo duplicato), in primo luogo, dovresti scaricare il file Kutools for Excele quindi applica la funzione in modo rapido e semplice.

Dopo l'installazione Kutools for Excel, per favore fai come questo:

1. Fare clic su una cella in cui si desidera visualizzare il risultato. (Note:: Non fare clic su una cella nella prima riga.)

2. Quindi fare clic Kutools > formula Helper > formula Helper, vedi screenshot:

3. Nel Formule Helper finestra di dialogo, eseguire le seguenti operazioni:

  • Seleziona Testo opzione dal Formula Tipologia menu `A tendina;
  • Quindi scegli Estrai celle con valori univoci (includi il primo duplicato) dal Scegli una fromula casella di riepilogo;
  • In destra Input di argomenti sezione, seleziona un elenco di celle da cui desideri estrarre valori univoci.

4. Quindi fare clic Ok pulsante e trascina il quadratino di riempimento sulle celle in cui desideri elencare tutti i valori univoci finché non vengono visualizzate celle vuote, vedi screenshot:

Scarica subito Kutools per Excel!


Articoli più relativi:

  • Contare il numero di valori univoci e distinti da un elenco
  • Supponendo che tu abbia un lungo elenco di valori con alcuni elementi duplicati, ora, vuoi contare il numero di valori univoci (i valori che compaiono nell'elenco solo una volta) o valori distinti (tutti i diversi valori nell'elenco, significa unico valori + 1 ° valore duplicato) in una colonna come mostrato nell'immagine a sinistra. In questo articolo, parlerò di come affrontare questo lavoro in Excel.
  • Estrai valori univoci in base a criteri in Excel
  • Supponiamo che tu abbia il seguente intervallo di dati che desideri elencare solo i nomi univoci della colonna B in base a un criterio specifico della colonna A per ottenere il risultato come mostrato nell'immagine sottostante. Come puoi gestire questa attività in Excel in modo rapido e semplice?
  • Consenti solo valori univoci in Excel
  • Se desideri mantenere solo i valori univoci immessi in una colonna del foglio di lavoro e impedire i duplicati, questo articolo introdurrà alcuni trucchi rapidi per affrontare questa attività.
  • Sommare i valori univoci in base ai criteri in Excel
  • Ad esempio, ho un intervallo di dati che contiene le colonne Nome e Ordine, ora, per sommare solo valori univoci nella colonna Ordine in base alla colonna Nome come mostrato nella seguente schermata. Come risolvere questa attività rapidamente e facilmente in Excel?

I migliori strumenti per la produttività in ufficio

Funzioni popolari: Trova, evidenzia o identifica i duplicati   |  Elimina righe vuote   |  Combina colonne o celle senza perdere dati   |   Round senza formula ...
Super ricerca: VLookup a criteri multipli    VLookup a valori multipli  |   VLookup su più fogli   |   Ricerca fuzzy ....
Elenco a discesa avanzato: Crea rapidamente un elenco a discesa   |  Elenco a discesa dipendente   |  Elenco a discesa a selezione multipla ....
Gestore di colonna: Aggiungi un numero specifico di colonne  |  Sposta colonne  |  Attiva/disattiva lo stato di visibilità delle colonne nascoste  |  Confronta intervalli e colonne ...
Funzionalità in primo piano: Messa a fuoco della griglia   |  Vista di progettazione   |   Grande barra delle formule    Gestore di cartelle di lavoro e fogli   |  Resource Library (Testo automatico)   |  Date picker   |  Combina fogli di lavoro   |  Crittografa/decrittografa le celle    Invia e-mail per elenco   |  Super filtro   |   Filtro speciale (filtro grassetto/corsivo/barrato...) ...
I 15 migliori set di strumenti12 Testo Strumenti (aggiungi testo, Rimuovi personaggi, ...)   |   Più di 50 Grafico Tipi (Diagramma di Gantt, ...)   |   40+ Pratico Formule (Calcola l'età in base al compleanno, ...)   |   19 Inserimento Strumenti (Inserisci il codice QR, Inserisci immagine dal percorso, ...)   |   12 Conversione Strumenti (Numeri in parole, Conversione di valuta, ...)   |   7 Unisci e dividi Strumenti (Combina righe avanzate, Celle divise, ...)   |   ... e altro ancora

Potenzia le tue competenze di Excel con Kutools per Excel e sperimenta l'efficienza come mai prima d'ora. Kutools per Excel offre oltre 300 funzionalità avanzate per aumentare la produttività e risparmiare tempo.  Fai clic qui per ottenere la funzionalità di cui hai più bisogno...

scheda kte 201905


Office Tab porta l'interfaccia a schede in Office e semplifica notevolmente il tuo lavoro

  • Abilita la modifica e la lettura a schede in Word, Excel, PowerPoint, Publisher, Access, Visio e Project.
  • Apri e crea più documenti in nuove schede della stessa finestra, piuttosto che in nuove finestre.
  • Aumenta la produttività del 50% e riduce ogni giorno centinaia di clic del mouse!
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