Come concatenare valori univoci in Excel?
Quando si lavora con i fogli di calcolo, è comune imbattersi in situazioni in cui è necessario unire (concatenare) solo i valori univoci di una colonna o compilare elenchi che riassumono voci uniche con i relativi record. Gestire i duplicati e presentare informazioni sintetizzate non solo rende i dati più ordinati, ma rende anche i report più chiari e significativi. In Excel, ci sono diversi modi pratici per raggiungere questi obiettivi, dall'uso di funzioni integrate all'utilizzo di componenti aggiuntivi avanzati o codice personalizzato. Questo tutorial offre un'esplorazione dettagliata di vari metodi per concatenare valori univoci e per elencare voci uniche insieme ai loro dati correlati. Le soluzioni trattate soddisfano diverse versioni di Excel e preferenze degli utenti, aiutandoti a selezionare l'approccio migliore per il tuo scenario.
Concatena solo i valori univoci da una colonna
- Utilizzando le funzioni TESTO.UNISCI e UNICI
- Utilizzando Kutools AI Assistant
- Utilizzando una Funzione Definita dall'Utente
- Utilizzando una formula avanzata di Excel (soluzione alternativa)
Elenca i valori univoci e concatena i valori corrispondenti
- Utilizzando le funzioni TESTO.UNISCI e UNICI
- Utilizzando Kutools per Excel
- Utilizzando il codice VBA
- Utilizzando una Tabella Pivot di Excel con formule (soluzione alternativa)
Concatena solo i valori univoci da una colonna
Durante l'analisi dei dati in Excel, un compito frequente è quello di unire solo le voci distinte di una colonna in una singola cella. Questo può essere particolarmente utile per creare report di sintesi, evitare valori duplicati in un elenco o preparare i dati per ulteriori elaborazioni. La scelta del metodo dipende dalla tua versione di Excel, dalle dimensioni del dataset e dalla tua familiarità con le formule o il codice. I seguenti metodi affrontano diverse esigenze, evidenziano considerazioni per ciascuno e offrono suggerimenti pratici per garantire un'esecuzione corretta.
Metodo 1: Utilizzo delle funzioni TESTO.UNISCI e UNICI
Per gli utenti di Excel 365 e Excel 2021, l'introduzione delle funzioni TESTO.UNISCI e UNICI rende la combinazione dei valori univoci da una colonna sia semplice che flessibile.
Questa soluzione è ideale quando la colonna dei dati è continua e desideri consolidare rapidamente tutti gli elementi univoci in una singola cella con un delimitatore scelto. Elimina automaticamente i duplicati, è facilmente verificabile e ti consente di modificare l'intervallo o il delimitatore secondo necessità. Tuttavia, tieni presente che questo approccio è disponibile solo nelle versioni più recenti di Excel; le versioni precedenti non supportano la funzione UNICI.
Nella cella in cui desideri visualizzare il risultato, inserisci la seguente formula (supponendo che i tuoi dati siano nelle celle A2:A18):
=TEXTJOIN(", ", TRUE, UNIQUE(A2:A18))
- UNICI(A2:A18) filtra le voci duplicate e restituisce solo i valori univoci dall'intervallo A2:A18.
- TESTO.UNISCI(", ", VERO, ...) combina (concatena) questi valori univoci in una cella, separati da una virgola e uno spazio. L'argomento VERO assicura che qualsiasi cella vuota venga ignorata nella concatenazione.
Suggerimenti utili e risoluzione dei problemi:
- Assicurati che la tua versione di Excel supporti le funzioni UNICI e TESTO.UNISCI. Se vedi un errore #NOME?, potresti utilizzare una versione precedente.
- Il delimitatore utilizzato in TESTO.UNISCI può essere cambiato con qualsiasi cosa preferisci, come "; " o "|".
- Se aggiungi o rimuovi dati nell'intervallo originale, la formula si aggiorna automaticamente.
- Per evitare spazi o delimitatori indesiderati, verifica attentamente l'argomento del delimitatore nella formula.
Metodo 2: Utilizzando Kutools AI Assistant
Quando hai bisogno di un modo più rapido e completamente automatizzato per concatenare valori univoci—senza scrivere formule—lo strumento "AI Assistant" di Kutools per Excel offre una soluzione pratica che fa risparmiare tempo agli utenti di tutti i livelli di abilità. Questo metodo è particolarmente utile se non sei familiare con le formule avanzate di Excel o se i tuoi dati cambiano frequentemente, richiedendo attività ripetute.
Dopo aver installato Kutools per Excel, accedi a questa funzionalità facendo clic su "Kutools" > "AI Assistant" per aprire il riquadro "Assistente AI di KUTOOLS".
- Seleziona le celle che contengono i valori che desideri combinare in una singola cella, assicurandoti che la selezione corrisponda ai tuoi dati previsti.
- Nella casella di chat, descrivi il tuo requisito. Ad esempio, puoi digitare:
Concatena valori univoci con virgola dall'intervallo selezionato e inserisci il risultato combinato nella cella C2 - Premi il tasto Invio o fai clic sul pulsante "Invia" L'IA analizza la tua richiesta e, dopo l'elaborazione, premi “Esegui” per permettere a Kutools di eseguire l'operazione. Il risultato verrà restituito come descritto.
Note e suggerimenti:
- Controlla di avere l'ultima versione di Kutools per accedere a tutte le funzionalità dell'IA.
- Sii specifico nel tuo comando di testo per ottenere i migliori risultati; menziona il delimitatore e la cella di destinazione.
- Kutools AI è particolarmente efficiente per intervalli di grandi dimensioni o per flussi di lavoro che devono essere ripetuti su vari set di dati.
Metodo 3: Utilizzando una Funzione Definita dall'Utente
Per gli utenti che richiedono flessibilità avanzata, hanno bisogno di delimitatori personalizzati o vogliono uno strumento riutilizzabile per più cartelle di lavoro, codificare una Funzione Definita dall'Utente (UDF) in VBA è un modo efficace per concatenare automaticamente valori univoci. Questa soluzione VBA è compatibile con tutte le versioni di Excel e non è limitata dalla disponibilità di nuove funzioni.
- È necessario abilitare le macro nel tuo foglio di lavoro.
- Salva il file come "abilitato alle macro" (.xlsm) nel caso continui a utilizzare questo codice VBA in futuro.
- Si raccomanda di fare backup regolari del tuo foglio di lavoro prima di eseguire nuovo codice.
1. Tieni premuto ALT + F11 per aprire la finestra Microsoft Visual Basic per Applicazioni.
2. Nella finestra VBA, fai clic su Inserisci > Modulo, quindi copia e incolla il seguente codice:
Codice VBA: Concatena valori univoci in una singola cella:
Function ConcatUniq(xRg As Range, xChar As String) As String
'updateby Extendoffice
Dim xCell As Range
Dim xDic As Object
Set xDic = CreateObject("Scripting.Dictionary")
For Each xCell In xRg
xDic(xCell.Value) = Empty
Next
ConcatUniq = Join$(xDic.Keys, xChar)
Set xDic = Nothing
End Function
3. Torna al tuo foglio di lavoro, e in una cella vuota (ad esempio C2), digita la seguente formula:
=ConcatUniq(A2:A18,",")Premi Invio per confermare. La cella mostrerà tutti i valori univoci dall'intervallo specificato, separati da virgole.
- Se il tuo intervallo è diverso, regola A2:A18 di conseguenza.
- Se è necessario un delimitatore diverso, sostituisci "," nella formula con il simbolo preferito (ad esempio, ";" o |).
- Se incontri un errore #NOME?, verifica che le macro siano abilitate e che il nome della UDF corrisponda esattamente.
Suggerimento: Per riutilizzare questa funzione in altre cartelle di lavoro, copia il codice VBA nei loro moduli.
Metodo 4: Utilizzando una formula avanzata di Excel (soluzione alternativa)
Negli ambienti in cui la funzione UNICI non è disponibile (ad esempio, in Excel 2016 o Excel 2019), puoi comunque concatenare valori univoci utilizzando una combinazione più complessa delle classiche funzioni SE, CONTA.SE e TESTO.UNISCI come formule matrice. Questo approccio funziona ma è più adatto per set di dati più piccoli a causa del suo sovraccarico di calcolo.
1. In una cella di destinazione (ad es., C2), inserisci la seguente formula matrice (dopo aver inserito, premi Ctrl+Shift+Invio invece di semplicemente Invio):
=TEXTJOIN(", ", TRUE, IF(MATCH(A2:A18, A2:A18,0) = ROW(A2:A18) - MIN(ROW(A2:A18)) +1, A2:A18, ""))2. Se vedi parentesi graffe {} apparire intorno alla tua formula, è stata inserita correttamente come formula matrice. La formula restituirà valori univoci concatenati dall'intervallo A2:A18, separati da virgole.
Nota: Questo metodo richiede di regolare gli intervalli per corrispondere ai tuoi dati. Per intervalli molto grandi, il tempo di calcolo potrebbe aumentare. Se non sei a tuo agio con le formule matrice, considera di provare le soluzioni VBA o add-in descritte sopra.
Elenca i valori univoci e concatena i valori corrispondenti
Frequentemente nei rapporti sui dati, potresti voler non solo estrarre i valori univoci da una colonna, ma anche aggregare o unire insieme le loro voci corrispondenti da un'altra colonna. Ad esempio, consolidare tutti i prodotti venduti da ciascun venditore o compilare tutte le voci collegate allo stesso ID. Scegliere il metodo giusto dipende dalla complessità dei tuoi dati e se la priorità è l'automazione, la facilità d'uso o la compatibilità.
Metodo 1: Utilizzo delle funzioni TESTO.UNISCI e UNICI
Quando utilizzi Excel 365 o Excel 2021, puoi combinare le funzioni UNICI e FILTRO con TESTO.UNISCI per un approccio robusto e completamente basato su formule. Questo metodo funziona bene per riassumere i dati in cui un valore può essere relativo a più record e si desidera un elenco separato da delimitatori di quei record correlati.
1. In una colonna vuota, inserisci la seguente formula per elencare tutti i valori univoci dalla Colonna A:
=UNIQUE(A2:A17)
2. Ora, per concatenare i valori corrispondenti dalla Colonna B per ogni voce unica, nella colonna successiva accanto al tuo valore unico (ad es., E2 se i tuoi valori unici iniziano in D2), inserisci questa formula e trascinala verso il basso secondo necessità:
=TEXTJOIN(", ", TRUE, FILTER($B$2:$B$17, $A$2:$A$17 =D2))
- UNICI(A2:A17) crea un array di elementi univoci dalla Colonna A.
- FILTRO(B2:B17, A2:A17 = D2) genera un array contenente tutti i valori corrispondenti dalla Colonna B per ciascun valore unico in D2.
- TESTO.UNISCI(", ", VERO, ...) unisce questi valori corrispondenti, separati da virgole.
- Se hai bisogno di un delimitatore diverso, cambia ", " in TESTO.UNISCI di conseguenza.
- Per evitare errori, assicurati che gli intervalli nelle tue formule abbiano lunghezza uguale e che FILTRO non restituisca errori per mancate corrispondenze.
- Questo approccio aggiorna automaticamente i risultati man mano che i tuoi dati cambiano, rendendolo adatto per tabelle di riepilogo dinamiche.
Metodo 2: Utilizzando Kutools per Excel
Kutools per Excel presenta uno strumento "Unione Avanzata Righe" progettato appositamente per raggruppare i dati in base ai valori univoci e unire i valori corrispondenti con il delimitatore di tua scelta. È adatto per gli utenti che desiderano una soluzione grafica e non sono a proprio agio con la scrittura di formule o codice. È particolarmente prezioso quando si ha a che fare con grandi set di dati o quando è richiesto un raggruppamento frequente, come nei report periodici o nella manutenzione continua dei dati.
Prima di apportare modifiche, è buona norma fare un backup dei tuoi dati copiando i dati originali in un'altra posizione. Quindi segui questi passaggi:
- Seleziona l'intervallo di dati che desideri organizzare.
- Naviga fino a "Kutools" > "Unisci & Dividi" > "Unione Avanzata Righe", come illustrato di seguito:
- Nella finestra di dialogo che si apre:
- Seleziona la colonna con duplicati da unire, impostandola come "Chiave Primaria" nella colonna "Operazione".
- Scegli la colonna che desideri aggregare (i valori da concatenare); specifica il tuo delimitatore preferito nell'elenco a discesa sotto "Operazione".
- Fai clic su OK per eseguire.
Risultato:
Kutools riorganizzerà i tuoi dati, estraendo voci uniche e concatenando tutti i valori associati, in base alla tua configurazione.
- Se commetti un errore, utilizza la funzione Annulla in Excel (Ctrl+Z) per annullare.
- Il processo funziona per set di dati con potenzialmente centinaia o migliaia di record e supporta una varietà di delimitatori.
Metodo 3: Utilizzando il codice VBA
L'utilizzo di uno script VBA fornisce un controllo completo su come estrai e riassumi i dati. Questo approccio è compatibile con tutte le versioni di Excel ed è particolarmente adatto per flussi di lavoro personalizzati, automazione o quando funzioni come UNICI o FILTRO non sono disponibili. Se la struttura dei tuoi dati varia frequentemente, questa soluzione VBA può essere facilmente adattata.
Per utilizzare il codice qui sotto, segui semplicemente questi passaggi:
1. Premi ALT + F11 per aprire l'editor VBA.
2. Vai su Inserisci > Modulo, quindi incolla il seguente codice nella finestra del modulo aperta:
Codice VBA: Elenca i valori univoci e concatena i dati corrispondenti
Sub test()
'updateby Extendoffice
Dim xRg As Range
Dim xArr As Variant
Dim xCell As Range
Dim xTxt As String
Dim I As Long
Dim xDic As Object
Dim xOutputRg As Range
On Error Resume Next
xTxt = ActiveWindow.RangeSelection.Address
Set xRg = Application.InputBox("Please select the data range", "Kutools for Excel", xTxt, , , , , 8)
Set xRg = Application.Intersect(xRg, xRg.Worksheet.UsedRange)
If xRg Is Nothing Then Exit Sub
If xRg.Areas.Count > 1 Then
MsgBox "Does not support multiple selections", , "Kutools for Excel"
Exit Sub
End If
If xRg.Columns.Count <> 2 Then
MsgBox "There must be only two columns in the selected range", , "Kutools for Excel"
Exit Sub
End If
Set xOutputRg = Application.InputBox("Please select the output cell", "Kutools for Excel", Type:=8)
If xOutputRg Is Nothing Then Exit Sub
xArr = xRg
Set xDic = CreateObject("Scripting.Dictionary")
xDic.CompareMode = 1
For I = 1 To UBound(xArr)
If Not xDic.Exists(xArr(I, 1)) Then
xDic.Item(xArr(I, 1)) = xDic.Count + 1
xArr(xDic.Count, 1) = xArr(I, 1)
xArr(xDic.Count, 2) = xArr(I, 2)
Else
xArr(xDic.Item(xArr(I, 1)), 2) = xArr(xDic.Item(xArr(I, 1)), 2) & "," & xArr(I, 2)
End If
Next
xOutputRg.Resize(xDic.Count, 2).Value = xArr
End Sub
3. Premi F5 per eseguire lo script. Un pop-up ti chiederà di selezionare l'intervallo di dati. Assicurati di selezionare esattamente due colonne: la prima per i valori univoci e la seconda per i valori corrispondenti.
4. Fai clic su OK e seleziona la prima cella in cui deve iniziare la tabella dei risultati.
5. Dopo aver fatto clic su OK, il codice genererà una tabella contenente solo valori univoci e i loro dati concatenati associati.
- Se ricevi un errore riguardante il numero di colonne, controlla che la tua selezione includa solo due colonne.
- Se il tuo delimitatore deve cambiare da una virgola a un altro simbolo, regola il codice nella riga
xArr(xDic.Item(xArr(I,1)),2) = xArr(xDic.Item(xArr(I,1)),2) & "," & xArr(I,2)come necessario. - Effettua sempre un backup del tuo file prima di eseguire nuovi script VBA.
In sintesi, Excel offre una varietà di approcci per concatenare valori univoci e consolidare dati correlati. I metodi basati su formule sono veloci e dinamici nelle versioni moderne di Excel, mentre le soluzioni VBA e Kutools offrono una maggiore compatibilità e un controllo più ampio. Scegli sempre il metodo adatto alle dimensioni dei tuoi dati, alla versione di Excel e al flusso di lavoro preferito. Ricorda di testare su dati campione o di fare un backup del tuo lavoro, specialmente quando provi operazioni basate su script o batch. Per ulteriori guide su Excel e suggerimenti avanzati, esplora la nostra collezione completa di tutorial.
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