Come estrarre valori univoci da più colonne in Excel?
Se lavori spesso con set di dati distribuiti su diverse colonne in Excel, potresti trovarti in situazioni in cui alcuni valori sono duplicati all'interno della stessa colonna o tra colonne diverse. In molte attività di reporting o analisi dei dati, diventa necessario identificare ed estrarre tutti i valori univoci—quelli che appaiono solo una volta nell'intera selezione, indipendentemente dalla loro posizione. Farlo manualmente può essere dispendioso in termini di tempo e soggetto a errori, specialmente quando si ha a che fare con grandi set di dati o tabelle complesse. Fortunatamente, Excel offre una serie di metodi per estrarre questi valori univoci in modo efficiente.
Questa guida introduce diverse soluzioni che puoi utilizzare in base alla tua versione di Excel e alle tue preferenze—come formule adatte a tutte le versioni, formule di array dinamiche per le versioni recenti, l'uso di Kutools AI Aide per risultati semplici, Tabelle Pivot per consolidamenti visivi e codice VBA per estrazioni automatizzate in scenari complessi.
Estrai valori univoci da più colonne con formule
Ci sono momenti in cui vorresti raggiungere questa estrazione utilizzando le funzioni integrate di Excel. Questa sezione illustra come farlo utilizzando due approcci: una formula di array adatta a tutte le versioni di Excel e una formula di array dinamico disponibile nelle versioni più recenti come Excel 365 e Excel 2021. Questi metodi sono ideali quando desideri una soluzione basata su formule dirette, richiedi aggiornamenti frequenti man mano che cambiano i tuoi dati o hai bisogno di evitare componenti aggiuntivi esterni o codici.
Estrai valori univoci da più colonne con formula di array per tutte le versioni di Excel
Per la compatibilità con tutte le versioni di Excel, utilizzando una formula di array puoi estrarre valori univoci da diverse colonne—anche se il tuo Excel non supporta array dinamici. Questo approccio sfrutta una combinazione di funzioni INDIRECT, TEXT, MIN, IF, COUNTIF, ROW e COLUMN, rendendolo flessibile per varie strutture di dati.
Supponiamo che i tuoi dati si trovino nell'intervallo A2:C9. Per estrarre i valori univoci a partire dalla cella E2, segui la seguente procedura:
1. Clicca sulla cella E2 (o sulla prima cella del tuo intervallo di output) e inserisci la seguente formula di array:
=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"),)&""
- A2:C9 è l'intervallo di dati da cui vuoi estrarre i valori univoci.
- E1:E1 si riferisce alle celle immediatamente sopra la tua prima cella di output ed è necessario per tenere traccia delle voci già estratte.
- $2:$9 sono i riferimenti di riga dei tuoi dati; $A:$C sono i riferimenti di colonna. Modifica questi valori secondo necessità per adattarli al layout del tuo foglio di lavoro.
2. Una volta inserita la formula, invece di premere semplicemente Invio, premi Ctrl + Maiusc + Invio insieme per confermarla come formula di array. Quando fatto correttamente, le parentesi graffe {} appariranno intorno alla tua formula nella barra della formula. Quindi, trascina la maniglia di riempimento da E2 verso il basso lungo la colonna. Continua a trascinare fino a quando non appaiono celle vuote, indicando che non ci sono più valori univoci da estrarre. Questo processo assicura che tutti i valori univoci verranno visualizzati nella colonna di destinazione.
- $A$2:$C$9: Specifica l'intero insieme di celle da esaminare per i valori univoci.
- IF(($A$2:$C$9<>"")*(COUNTIF($E$1:E1,$A$2:$C$9)=0), ROW($2:$9)*100+COLUMN($A:$C),7^8):
- $A$2:$C$9<>"" assicura che le celle vuote vengano ignorate.
- COUNTIF($E$1:E1,$A$2:$C$9)=0 assicura che solo i nuovi valori (non ancora estratti) vengano inclusi.
- Se entrambe le condizioni sono vere, l'output corrispondente è un calcolo basato sulla riga e sulla colonna della cella per generare un numero indice univoco.
- Se una delle condizioni è falsa, la formula restituisce un numero molto grande (7^8) per prevenire una selezione accidentale.
- MIN(...): Identifica il numero indice più basso, individuando efficacemente la posizione del prossimo valore univoco disponibile nei dati.
- TEXT(...,"R0C00"): Converte l'indice in un riferimento di cella valido utilizzando lo stile R1C1.
- INDIRECT(...): Converte il riferimento di cella creato sopra in un valore del tuo intervallo di dati.
- &"": Forza il risultato della formula a essere trattato come testo, garantendo nessuna sorpresa di formattazione.
Estrai valori univoci da più colonne con formula per Excel 365, Excel 2021 e versioni successive
Se utilizzi Excel 365, Excel 2021 o una versione successiva, hai accesso alle funzioni di array dinamico, che forniscono un modo più semplice e intuitivo per estrarre valori univoci da più colonne. Le funzioni UNIQUE e TOCOL rendono più facile e veloce combinare dati da diverse colonne ed eliminare duplicati in un unico passaggio—particolarmente utile per chi lavora con set di dati in costante aggiornamento o di grandi dimensioni.
Per utilizzare questo metodo, seleziona semplicemente una cella vuota (ad esempio, E2, o dove desideri che appaiano i risultati), inserisci questa formula e premi Invio:
=UNIQUE(TOCOL(A2:C9,1))Dopo aver premuto Invio, tutti i valori univoci dall'intervallo A2:C9 verranno riversati automaticamente nelle celle sotto la formula. Questa funzionalità è particolarmente efficiente—l'output si aggiorna dinamicamente man mano che cambiano i dati di origine, risparmiandoti passaggi manuali di aggiornamento.
- TOCOL(A2:C9,1): Converte il tuo intervallo di valori da più colonne in una singola colonna, rimuovendo automaticamente le celle vuote.
- UNIQUE(...): Estrae ogni valore una sola volta, fornendo un elenco pulito e privo di duplicati.
Estrai valori univoci da più colonne con Kutools AI Aide
Se desideri un approccio più semplificato e vuoi ridurre lo sforzo manuale, Kutools AI Aide in Kutools per Excel può aiutarti a estrarre valori univoci da più colonne con facilità. Questo metodo è particolarmente prezioso se non sei familiare con le formule o vuoi evitare il rischio di errori di formula. Kutools AI Aide interpreta le tue istruzioni e elabora i dati automaticamente, ideale sia per principianti che per utenti che cercano una soluzione rapida in pochi clic.
Dopo l'installazione, clicca su Kutools AI > AI Aide per aprire il pannello "Kutools AI Aide":
- Inserisci la tua richiesta nella casella di chat, ad esempio: "Estrai valori univoci dall'intervallo A2:C9, ignorando le celle vuote, e posiziona i risultati a partire da E2:"
- Clicca su "Invia" o premi Invio, quindi dopo che l'IA ha analizzato la richiesta, clicca semplicemente su "Esegui" per avviare. I risultati appariranno istantaneamente nel tuo foglio di lavoro, esattamente nella posizione che hai indicato.
Suggerimento: Questa soluzione è molto utile se il tuo flusso di lavoro di estrazione dei dati varia o se vuoi funzionalità di elaborazione del linguaggio naturale. Ricorda di controllare attentamente l'elenco estratto per celle vuote se i tuoi dati originali non sono perfettamente consistenti, poiché le voci vuote potrebbero essere incluse o filtrate in base ai dettagli della tua richiesta all'IA.
Estrai valori univoci da più colonne con Tabella Pivot
Le Tabelle Pivot sono un altro metodo conveniente per estrarre valori univoci, in particolare se preferisci lavorare con strumenti visivi e vuoi riassumere o analizzare ulteriormente gli elementi univoci, come contare le occorrenze. Questo approccio è semplice e non richiede formule. Tuttavia, richiede alcuni passaggi di configurazione e un leggero riarrangiamento dei dati, soprattutto se le colonne coinvolte hanno intestazioni diverse.
Ecco un processo suggerito per estrarre valori univoci utilizzando una Tabella Pivot:
1. Inserisci una nuova colonna vuota immediatamente a sinistra dei tuoi dati. Ad esempio, inserisci una nuova colonna A se i tuoi dati iniziano dalla colonna B. Questo aggiustamento aiuta a garantire una corretta consolidazione dell'intervallo.
2. Seleziona una qualsiasi cella all'interno del tuo set di dati, premi Alt + D, quindi premi rapidamente P per avviare la "Creazione guidata Tabella Pivot e Grafico Pivot." Nel primo passaggio della procedura guidata, seleziona "Intervalli di consolidamento multipli." Questo ti permette di combinare valori da numerose colonne in un unico campo riepilogativo.
3. Clicca su Avanti, quindi scegli "Crea un singolo campo pagina per me." Questo passaggio organizza tutti i tuoi dati come un unico gruppo per una più facile estrazione di valori univoci.
4. Nel passaggio successivo, seleziona l'intero intervallo di dati (includendo la nuova colonna vuota), clicca sul pulsante Aggiungi per portare la tua selezione nella lista "Tutti gli intervalli", e clicca su Avanti.
5. Nell'ultimo passaggio della procedura guidata, seleziona dove desideri posizionare la Tabella Pivot (nuovo foglio di lavoro o foglio esistente), quindi clicca su Fine per generare il report della Tabella Pivot.
6. Nella nuova Tabella Pivot, deseleziona tutti i campi nella sezione "Scegli campi da aggiungere al report" per cancellare la vista predefinita.
7. Infine, trascina il campo "Valore" nell'area Righe. La Tabella Pivot mostrerà tutti i valori univoci del tuo intervallo originale a più colonne, organizzati ordinatamente in una singola colonna.
Limitazioni: I dati necessitano di un arrangiamento preliminare, e se il tuo set di dati di origine si aggiorna, devi aggiornare la Tabella Pivot per vedere i nuovi valori univoci.
Estrai valori univoci da più colonne con codice VBA
Nei casi in cui devi automatizzare l'estrazione o gestire grandi set di dati irregolari, utilizzare il codice VBA (Visual Basic for Applications) può fornire una soluzione rapida e riutilizzabile. Questo è ideale per utenti con una conoscenza di base dell'editor VBA di Excel, o per attività ricorrenti in cui vuoi minimizzare le operazioni manuali. VBA può anche gestire grandi volumi di dati in modo più efficiente rispetto alle formule di array.
1. Apri l'editor VBA premendo Alt + F11. Nella finestra "Microsoft Visual Basic for Applications" che appare, clicca su Inserisci > Modulo per aggiungere un nuovo modulo.
2. Nel nuovo modulo, incolla il codice qui sotto:
VBA: Estrai 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. Premi F5 per eseguire il codice. Una finestra di dialogo ti chiederà di selezionare l'intervallo di dati. Seleziona tutte le colonne pertinenti (incluse quelle con celle vuote).
4. Dopo aver cliccato OK, un'altra finestra ti chiederà dove inserire i valori univoci. Specifica una cella superiore dove vuoi che i risultati siano elencati (ad esempio, E2).
5. Clicca OK, e la macro verrà eseguita automaticamente. Tutti i valori univoci appariranno, a partire dalla posizione specificata.
- Se ricevi errori come #VALORE! o #SPILL! quando usi le formule, controlla i tuoi intervalli e assicurati che l'area di output sia libera.
- Controlla sempre la presenza di righe nascoste o celle unite nel tuo intervallo di dati, poiché queste possono influenzare la correttezza dell'estrazione dei valori univoci.
- Le formule di array e array dinamico si aggiornano automaticamente con le modifiche, ma le soluzioni con Filtro Avanzato e Tabelle Pivot potrebbero richiedere un aggiornamento manuale o una riesecuzione.
- Per attività ricorrenti, considera di automatizzare l'estrazione utilizzando VBA per coerenza e velocità.
- Effettua un backup dei tuoi dati prima di applicare routine di estrazione massiva o automazione, specialmente in cartelle di lavoro complesse.
Altri articoli correlati:
- Conta Il Numero Di Valori Unici E Distinti Da Un Elenco
- Supponiamo di avere un elenco lungo di valori con alcune voci duplicate, e vuoi contare quanti valori unici (i valori che appaiono solo una volta) o valori distinti totali esistono in una colonna, come mostrato nella schermata a sinistra. Questo articolo spiega metodi efficienti per contare le voci uniche e distinte in Excel.
- Estrai Valori Unici Basati Su Criteri In Excel
- Supponiamo di voler estrarre solo i nomi univoci dalla colonna B in base a una condizione specifica nella colonna A, producendo risultati come mostrato nella schermata. Questa guida dimostra modi per applicare criteri durante l'estrazione di valori univoci.
- Consenti Solo Valori Unici In Excel
- Se vuoi consentire solo voci univoche in una colonna del foglio di lavoro e impedire valori duplicati, questo articolo introduce tecniche pratiche per imporre regole di unicità in Excel.
- Somma Valori Unici Basati Su Criteri In Excel
- Ad esempio, potresti dover sommare solo i valori univoci in una colonna "Ordine" in base ai nomi in una colonna adiacente, come mostrato nella schermata. Questo articolo discute approcci per combinare calcoli univoci e condizionali.
I migliori strumenti per la produttività in Office
Potenzia le tue competenze in Excel con Kutools per Excel e sperimenta un'efficienza mai vista prima. Kutools per Excel offre oltre300 funzionalità avanzate per aumentare la produttività e farti risparmiare tempo. Clicca qui per ottenere la funzione di cui hai più bisogno...
Office Tab porta le schede su Office e rende il tuo lavoro molto più semplice
- Abilita la modifica e lettura a schede in Word, Excel, PowerPoint, Publisher, Access, Visio e Project.
- Apri e crea più documenti in nuove schede della stessa finestra invece che in nuove finestre.
- Aumenta la produttività del50% e riduce centinaia di clic del mouse ogni giorno!
Tutti gli add-in Kutools. Un solo programma di installazione
La suite Kutools for Office include add-in per Excel, Word, Outlook & PowerPoint più Office Tab Pro, ideale per i team che lavorano su più app di Office.
- Suite tutto-in-uno — Add-in per Excel, Word, Outlook & PowerPoint + Office Tab Pro
- Un solo programma di installazione, una sola licenza — configurazione in pochi minuti (pronto per MSI)
- Funzionano meglio insieme — produttività ottimizzata su tutte le app Office
- Prova completa30 giorni — nessuna registrazione, nessuna carta di credito
- Massimo risparmio — costa meno rispetto all’acquisto singolo degli add-in