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

Come estrarre dinamicamente un elenco di valori univoci da un intervallo di colonne in Excel?

Per un intervallo di colonne in cui i valori cambiano regolarmente e devi sempre ottenere tutti i valori univoci dall'intervallo, indipendentemente da come è cambiato. Come creare un elenco dinamico di valori univoci? Questo articolo ti mostrerà come affrontarlo.

Estrai dinamicamente un elenco di valori univoci da un intervallo di colonne con formula
Estrai dinamicamente un elenco di valori univoci da un intervallo di colonne con codice VBA


Estrai dinamicamente un elenco di valori univoci da un intervallo di colonne con formula

Come mostrato nell'immagine sottostante, è necessario estrarre dinamicamente un elenco di valori univoci dall'intervallo B2: B9. Prova la seguente formula di matrice.

1. Seleziona una cella vuota come D2, inserisci la formula sottostante e premi il tasto Ctrl + spostamento + Entra tasti contemporaneamente. (B2: B9 sono i dati della colonna da cui vuoi estrarre i valori univoci, D1 è la cella sopra in cui si trova la tua formula)

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

2. Continuare a selezionare la cella D2, quindi trascinare la maniglia di riempimento verso il basso per ottenere tutti i valori univoci dall'intervallo specificato.

Ora vengono estratti tutti i valori univoci nell'intervallo di colonne B2: B9. Quando i valori in questo intervallo vengono modificati, l'elenco dei valori univoci verrà modificato immediatamente in modo dinamico.

Seleziona ed evidenzia facilmente tutti i valori univoci in un intervallo in Excel:

L' Seleziona Celle duplicate e uniche utilità di Kutools for Excel può aiutarti a selezionare ed evidenziare facilmente tutti i valori univoci (includi i primi duplicati) oi valori univoci che compaiono solo una volta, nonché i valori duplicati di cui hai bisogno come mostrato nell'immagine sottostante.
Scarica subito Kutools per Excel! (30-giorni di percorso gratuito)


Estrai dinamicamente un elenco di valori univoci da un intervallo di colonne con codice VBA

È inoltre possibile estrarre dinamicamente un elenco di valori univoci da un intervallo di colonne con il seguente codice VBA.

1. Stampa altro + F11 tasti contemporaneamente per aprire il file Microsoft Visual Basic, Applications Edition finestra.

2. Nel Microsoft Visual Basic, Applications Edition finestra, fare clic inserire > Modulo. Quindi copia e incolla il codice VBA sottostante nel file Modulo finestra.

Codice VBA: estrai un elenco di valori univoci da un intervallo

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:: Nel codice, D2 è la cella in cui individuerai l'elenco di valori univoci. Puoi cambiarlo secondo le tue necessità.

3. Tornare al foglio di lavoro, fare clic su inserire > Forme > Rettangolo. Vedi screenshot:

4. Disegna un rettangolo nel foglio di lavoro, quindi inserisci alcune parole che devi visualizzare su di esso. Quindi fai clic con il pulsante destro del mouse e seleziona Assegna macro dal menu di scelta rapida. Nel Assegna macro finestra di dialogo, selezionare il CreaListaUnica nel Nome della macro casella, quindi fare clic su OK pulsante. Vedi screenshot:

5. Ora fare clic sul pulsante rettangolo, a Kutools for Excel si apre la finestra di dialogo, selezionare l'intervallo contiene valori univoci che è necessario estrarre, quindi fare clic su OK pulsante.

D'ora in poi, puoi ripetere il passaggio 5 di cui sopra per aggiornare automaticamente l'elenco dei valori univoci.


Articoli correlati:


I migliori strumenti per la produttività in ufficio

Kutools per Excel risolve la maggior parte dei tuoi problemi e aumenta la tua produttività dell'80%

  • Riutilizzo: Inserisci rapidamente formule complesse, grafici e tutto ciò che hai usato prima; Crittografa celle con password; Crea mailing list e invia email ...
  • Bar Super Formula (modifica facilmente più righe di testo e formula); Layout di lettura (leggi e modifica facilmente un gran numero di celle); Incolla su intervallo filtrato...
  • Unisci celle / righe / colonne senza perdere dati; Contenuto delle celle divise; Combina righe / colonne duplicate... Impedisci celle duplicate; Confronta intervalli...
  • Seleziona Duplica o Unico Righe; Seleziona Righe vuote (tutte le celle sono vuote); Super Find e Fuzzy Find in molte cartelle di lavoro; Selezione casuale ...
  • Copia esatta Più celle senza modificare il riferimento della formula; Riferimenti di creazione automatica a più fogli; Inserisci punti elenco, Caselle di controllo e altro ...
  • Estrai testo, Aggiungi testo, Rimuovi per posizione, Rimuovi spazio; Creare e stampare totali parziali di paging; Converti contenuto e commenti tra celle...
  • Super filtro (salva e applica schemi di filtri ad altri fogli); Ordinamento avanzato per mese / settimana / giorno, frequenza e altro; Filtro speciale in grassetto, corsivo ...
  • Combina cartelle di lavoro e fogli di lavoro; Unisci tabelle in base a colonne chiave; Suddividi i dati in più fogli; Conversione in batch xls, xlsx e PDF...
  • Più di 300 potenti funzionalità. Supporta Office/Excel 2007-2021 e 365. Supporta tutte le lingue. Facile implementazione nella tua azienda o organizzazione. Funzionalità complete Prova gratuita di 30 giorni. Garanzia di rimborso di 60 giorni.
scheda kte 201905

Scheda Office porta l'interfaccia a schede a Office e semplifica notevolmente il 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!
fondo officetab
Commenti (35)
Ancora nessuna valutazione. Puoi essere il primo a votare!
Questo commento è stato fatto dal moderatore sul sito
Grazie per il tutorial. Usando il metodo della formula, come modificheresti la formula se volessi aggiungere un qualificatore di categoria? Dì nella colonna C di distinguere se l'oggetto è un frutto o un ortaggio. Come cambieresti il ​​codice per ordinare solo i frutti univoci ed escludere le verdure? Ho provato a sostituire COUNTIF con COUNTIFS, utilizzando il secondo criterio countifs di (LIST RANGE, "CATEGORY") ma restituisce vuoto. Avrei bisogno di espandere il mio array e incorporare VLOOKUP?
Questo commento è stato fatto dal moderatore sul sito
Sono decente in Excel ma sto davvero cercando di capire come e perché funziona la formula sopra (funziona per quello per cui la sto usando ma devo capire perché). A volte mi confondo usando gli array, quindi qualsiasi spiegazione in termini idioti sarebbe estremamente utile Saluti
Questo commento è stato fatto dal moderatore sul sito
Questa formula è obsoleta e non funziona. Ho letteralmente impostato questo esatto foglio Excel per vedere se riuscivo a far funzionare questa formula e non è così.
Questo commento è stato fatto dal moderatore sul sito
Hey ragazzo,
Quale versione di Office usi?
Questo commento è stato fatto dal moderatore sul sito
{=INDEX($Q$3:$Q$263,MATCH(0,COUNTIF(V$2:V2,$Q$3:$Q$263),0))} - trovato che funziona da un altro sito...

Utilizzare Ctrl+Maiusc+Invio per ottenere la funzione di matrice (parentesi graffe). Trascina copia-incolla le formule finché non viene visualizzato #NA. Il mio set di dati era in Column-Q, è stato confrontato per vedere se esisteva nell'elenco di Unique in Column-V, che si estende continuamente lungo questa stessa colonna.
Questo commento è stato fatto dal moderatore sul sito
Bel giorno.
Elenca tutti i valori univoci della colonna Q con la formula abobv, quindi usa la sua formula =IF(D2=V1,"Match","Nessuna corrispondenza") per confrontare se gli univoci nella colonna Q rispetto alla colonna V nella stessa riga .
Questo commento è stato fatto dal moderatore sul sito
Ciao e grazie per il tuo aiuto.

Ho bisogno esattamente di questa funzionalità, ma il mio elenco di "valori univoci" deve estendersi su colonne anziché righe, quindi l'elenco in espansione lungo le righe non funzionerà per me.

Come posso modificare questa formula per espandere l'elenco dei "valori univoci" mentre lo trascino sulle colonne?

Compensare()?
Trasporre()?
Indirect() con una stringa di riferimenti assoluti concatenati con un riferimento alla colonna anziché alla riga?


Grazie ancora!
Questo commento è stato fatto dal moderatore sul sito
Caro Ryan,
Questa formula =IFERROR(INDEX($B$2:$B$9, MATCH(0,COUNTIF($D$2:D2, $B$2:$B$9), 0)),"") + Ctrl + Maiusc + Invio può aiutarti a risolvere il problema.
Vedi sotto lo screenshot:
Questo commento è stato fatto dal moderatore sul sito
Inoltre, per qualsiasi motivo, la formula originale prevedeva:
=IFERROR(INDEX($B$2:$B$9, MATCH(0,COUNTIF($D$1:D1, $B$2:$B$9), 0)),"")

restituisce un avviso di "riferimento circolare" e non calcola..
Questo commento è stato fatto dal moderatore sul sito
Caro Ryan,
Quale versione di Office usi? La formula funziona bene nel mio Office 2016 e 2013.
Questo commento è stato fatto dal moderatore sul sito
Mi è successo prima: la mia soluzione era che stavo inserendo la formula nella cella D1 (equivalente nel foglio di lavoro che stavo usando). Qualunque cella corrisponda a $D:$1, devi inserirla nella cella sottostante - D2. Mi scuso se non è per questo che hai ricevuto l'errore
Questo commento è stato fatto dal moderatore sul sito
Qualche consiglio su come far funzionare l'opzione VBA con Excel 2016 per macOS? ho seguito i passaggi; tuttavia, quando eseguo la macro, non accade nulla. Grazie!
Questo commento è stato fatto dal moderatore sul sito
Daer Jones,
Prova il codice VBA sottostante e fammi sapere se funziona per te. Grazie!

Sub CreateUniqueList()
Dim xRng come intervallo
Dim xLastRow Fino a quando
Dim xLastRow2 quanto a lungo
Dim I come intero
' In caso di errore Riprendi Avanti
Set xRng = Application.InputBox("Seleziona intervallo:", "Ktools for Excel", Selection.Address, , , , , 8)
Se xRng non è niente, allora esci da Sub
On Error Resume Next
xRng.Copy Range("D2")
xUltimaRiga = xRng.Rows.Count + 1
ActiveSheet.Range("D2:D" & xLastRow).RemoveDuplicates Columns:=1, Header:=xlNo
xUltimaRiga2 = Celle(Righe.Conteggio, "B").End(xlSu).Riga
Per I = 1 A xLastRow2
Se ActiveSheet.Range("D2:D" & xLastRow2).Cells(I).Value = "" Allora
ActiveSheet.Range("D2:D" & xLastRow2).Cells(I).Delete
End If
Successiva
End Sub
Questo commento è stato fatto dal moderatore sul sito
Ciao Cristallo,
Sto cercando di utilizzare la versione VB dell'elenco dei valori univoci e sto riscontrando un problema.
L'intervallo da cui voglio creare una colonna di valori univoci è costituito da tutte le formule che fanno riferimento a schede diverse.
Come si ottiene il valore da trasferire invece della formula?
Questo commento è stato fatto dal moderatore sul sito
Caro Mike,
Converti i riferimenti della tua formula in assoluto, quindi applica lo script VB.
Questo commento è stato fatto dal moderatore sul sito
Ho lo stesso problema, tranne per il fatto che la mia formula si riferisce ai nomi delle colonne e non può essere convertita in assoluto.
Come posso modificare vba per incollare i valori e non la formula?
Questo commento è stato fatto dal moderatore sul sito
Come aggiungeresti più criteri, ad esempio se volessi aggiungerli all'elenco dinamico solo se la data fosse solo il 9/12?

Sto provando "&" nella formula MATCH, ma non funziona.

Ad esempio, in base al tuo esempio:
=SEERRORE(INDEX($B$2:$B$9, MATCH(0 & B4,COUNTIF($D$1:D1, $B$2:$B$9) & $A$2:$A$9, 0)),"" )
Questo genera un errore o crea duplicati.

In alternativa, ho letto che "+" potrebbe funzionare, anche se non riesco a farlo funzionare. O usando SMALL.

Idee?
Questo commento è stato fatto dal moderatore sul sito
Caro Zac,
Scusa se non posso aiutarti con questo, puoi postare la tua domanda nel nostro forum: https://www.extendoffice.com/forum.html per ottenere più supporti Excel dal nostro professionista.
Questo commento è stato fatto dal moderatore sul sito
Come aggiungeresti una seconda variabile? Ad esempio, voglio tutti gli elementi univoci in una colonna che condividano anche un valore simile in un'altra colonna. Nel tuo esempio, immagina una terza colonna intitolata "Dipartimento" che avrebbe valori come prodotto, carne, ecc. Mi rendo conto che sono tutti prodotti, ma spero che tu abbia capito. Modificheresti la formula CountIF in un COUNTIFS o la modificheresti in un altro modo?
Questo commento è stato fatto dal moderatore sul sito
Ciao Matt
Prova questa formula =IF(ISNA(VLOOKUP(A2,$C$2:$C$13,1,FALSE)),"Sì","").
Supponendo che i due elenchi confrontati siano Colonna A e Colonna C, se i valori univoci rimangono solo nella Colonna A ma non nella Colonna C, verrà visualizzato Sì nella colonna B; mentre se non restituisce nulla nella colonna B, significa che il valore corrispondente rimane sia nella colonna A che nella colonna C.
Questo commento è stato fatto dal moderatore sul sito
Grazie per la risposta .. ma caldo per estrarre quel valore univoco se viene visualizzato SÌ .. potresti per favore consigliarmi la formula per estrarre il valore univoco in una colonna diversa.
Questo commento è stato fatto dal moderatore sul sito
Se lo faccio per un foglio excel di mille righe sull'ultima versione di Excel su un Mac, non ritorna mai. La prima riga funziona, ma quando duplico verso il basso, Excel entra in una modalità di calcolo che non ha restituito valori per oltre due ore.

Qualche idea su come farlo per elenchi di grandi dimensioni (fino a 2k righe) che restituiranno 50 o 60 valori univoci?

L'ho preso in giro nell'app "Numeri" e lì funziona perfettamente, impiegando solo un paio di minuti per il calcolo. Ci vuole così tanto tempo in Excel che mi chiedo se verrà mai completato. Ho intenzione di lasciarlo "correre" durante la notte per vedere cosa accadrà.
Questo commento è stato fatto dal moderatore sul sito
Controlla le tue opzioni di calcolo. Deve essere impostato su automatico. File > Opzioni > Formule > Opzioni di calcolo > Calcolo cartella di lavoro (selezione automatica)
Questo commento è stato fatto dal moderatore sul sito
Sto cercando di trascinare la formula oltre i miei dati effettivi in ​​modo da poter inserire set di dati di dimensioni diverse e non dover regolare nulla. Tuttavia, l'ultima riga dopo la fine dei miei dati effettivi restituisce sempre uno "0". Sto usando i valori univoci per qualcos'altro in una colonna adiacente e lo 0 fa ripetere l'ultimo valore (quando elimino lo 0 il valore non viene più ripetuto). Qualche idea su come risolvere questo problema? Inoltre sto usando Office 365 Business
Questo commento è stato fatto dal moderatore sul sito
Ciao, grazie per il tuo aiuto.
Ora, come posso avere anche i miei valori ordinati in ordine alfabetico? (Non voglio usare il filtro sulla mia tabella principale)
Devo usare un COUNTIFS invece di COUNTIF?
Per favore aiuto
Questo commento è stato fatto dal moderatore sul sito
ciao Alessio,
Spiacente, non è possibile ordinare alfabeticamente il valore estratto contemporaneamente alla formula. Grazie per il tuo commento.
Questo commento è stato fatto dal moderatore sul sito
Sto usando quella formula =IFERROR(INDEX($B$2:$B$9, MATCH(0,COUNTIF($D$1:D1, $B$2:$B$9), 0)),"") che è fantastica per una colonna, ma i miei dati sono distribuiti su un intervallo di colonne e righe. Posso modificare la formula per includere l'intera area? I miei dati vivono da AC4 a AR60...
Questo commento è stato fatto dal moderatore sul sito
Provo il codice VBA e la formula. Il codice VBA funziona molto bene ma non riesco a mantenere un file con macro. Ma il problema è che non riesco a far funzionare la formula. Qualcuno ha avuto un'idea? Grazie
Questo commento è stato fatto dal moderatore sul sito
Ciao Charlotte,
Grazie per il tuo commento. È possibile conservare il file con la macro per un uso futuro salvando la cartella di lavoro come cartella di lavoro con attivazione macro di Excel.
Per il problema della formula, potresti fornire uno screenshot dei tuoi dati? Grazie per il tuo commento.
Questo commento è stato fatto dal moderatore sul sito
grazie mille
Questo commento è stato fatto dal moderatore sul sito
come far funzionare il codice vba per un intervallo in cui è stata utilizzata un'altra formula? sulla colonna BI avere una formula, riferita alle colonne D ed E.
Se utilizzo applica il codice alla colonna L (diciamo), (ovviamente modificando opportunamente le celle del codice) la macro restituisce la formula applicata alle colonne M e N... Funziona, quindi, ma non come voglio! Come mantenere i valori nella colonna B? grazie
Questo commento è stato fatto dal moderatore sul sito
Ik heb gemerkt dat de 'formule'-methode erg traag werkt bij grote datasets. Een goed alternatief is het gebruik van een draaitabel. Kies dan alleen de rijlabels, je krijgt dan een lijstje con 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 is wat complexer.
Questo commento è stato fatto dal moderatore sul sito
Mi piacerebbe essere in grado di fare esattamente la stessa cosa, tranne l'utilizzo di due intervalli di colonne separati (B2: B9) e (D2: D9) è possibile?
Questo commento è stato fatto dal moderatore sul sito
Ciao Antonio,
È possibile inserire i risultati nella stessa colonna dei dati originali. Come la colonna B in questo caso.
Ma è necessario fare riferimento alla cella superiore della cella del risultato nella formula come segue.
=IFERROR(INDEX($B$2:$B$9, MATCH(0,COUNTIF($B$11:B11, $B$2:$B$9), 0)),"") + Ctrl + Maiusc + Invio
Questo commento è stato fatto dal moderatore sul sito
con questo procedimento di filtraggio 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 define un criterio 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 volta espulso se mostra os dados en la fila destinação, que en mi caso fue la d59

Intervallo ("A59:A239"). Azione filtro avanzato:=xlFilterCopy, Intervallo criteri:=Intervallo _
("D56:D57"), CopyToRange:=Range("D59"), Unique:=True
Non ci sono ancora commenti pubblicati qui
Lasciate i vostri commenti
Pubblicazione come ospite
×
Valuta questo post:
0   Personaggi
Posizioni suggerite

Seguici

Copyright © 2009 - www.extendoffice.com. | Tutti i diritti riservati. Offerto da ExtendOffice, | Mappa del sito
Microsoft e il logo Office sono marchi o marchi registrati di Microsoft Corporation negli Stati Uniti e / o in altri paesi.
Protetto da Sectigo SSL