Scarica la cartella di lavoro di esempio
Questo tutorial dimostrerà come concatenare i valori delle celle in base a criteri utilizzando la funzione TEXTJOIN in Excel e Fogli Google.
La funzione TEXTJOIN
Gli utenti di Excel 2022+ hanno accesso alla funzione TEXTJOIN, che unisce più stringhe di testo, separando ogni stringa con un delimitatore specificato.
Nota: Gli utenti di Fogli Google possono utilizzare la funzione TEXTJOIN, ma hanno un modo leggermente diverso di inserire le formule di matrice. Vedere la sezione successiva su questo argomento.
Questo esempio utilizzerà le funzioni TEXTJOIN e IF in una formula di matrice per creare una stringa di testo separata da virgole di Nomi dei giocatori che riguardano a Squadra valore di rosso
| 1 | =TEXTJOIN(", ",VERO,SE(C3:C8="Rosso",B3:B8,"")) |
Gli utenti di Excel 2022 dovranno inserire questa formula come funzione di matrice premendo CTRL + MAIUSC + INVIO. Gli utenti delle versioni successive di Excel non devono seguire questo passaggio.
Per spiegare cosa sta facendo questa formula, suddividiamola in passaggi:
Questa è la nostra formula finale:
| 1 | =TEXTJOIN(", ",VERO,SE(C3:C8="Rosso",B3:B8,"")) |
Innanzitutto, i valori dell'intervallo di celle vengono aggiunti alla formula:
| 1 | =TEXTJOIN(", ",TRUE,IF({"Rosso"; "Blu"; "Blu"; "Rosso"; "Blu"; "Rosso"}="Rosso",{"A"; "B"; "C"; "D"; "E"; "F"},"")) |
Avanti l'elenco di Squadra nomi viene confrontato con il valore rosso:
| 1 | =TEXTJOIN(", ",VERO,SE({VERO; FALSO; FALSO; VERO; FALSO; VERO},{"A"; "B"; "C"; "D"; "E"; "F"} ,"")) |
La funzione IF sostituisce i valori VERO con il Nome del giocatoree valori FALSE con ""
| 1 | =TEXTJOIN(", ",TRUE,{"A"; ""; ""; "D"; ""; "F"}) |
La funzione TEXTJOIN combina quindi tutti i valori dell'array in un'unica stringa di testo. Abbiamo indicato alla funzione di ignorare i valori vuoti e di aggiungere il testo ", " tra ciascun valore. Questo produce il risultato finale:
| 1 | ="A, D, F" |
TextJoin If - in pre-Excel 2022
Poiché la funzione TEXTJOIN non è disponibile prima della versione Excel 2022, è necessario risolvere questo problema in un modo diverso. La funzione CONCATENATE è disponibile ma non accetta intervalli di celle come input né consente operazioni su array, pertanto è necessario utilizzare invece una colonna di supporto con una funzione IF.
Il prossimo esempio mostra come utilizzare una colonna helper per creare una stringa di testo separata da virgole di Nomi dei giocatori che riguardano a Squadra valore di Rosso:
| 1 | =SE(C3="Rosso",B3&", ","")&D4 |
Il primo passo in questo esempio consiste nell'usare una funzione IF per replicare la condizione di Squadra = Rosso. Se la condizione è soddisfatta, il Nome del giocatore valore è scritto e seguito dal delimitatore scelto “, “:
| 1 | =SE(C3="Rosso",B3&", ","") |
Successivamente, possiamo creare una colonna che costruisca un elenco di questi valori in una cella facendo riferimento anche alla cella sottostante:
| 1 | =RE3&MI4 |
Questa formula utilizza il carattere & per unire due valori. Nota che la funzione CONCATENATE potrebbe essere utilizzata per creare esattamente lo stesso risultato, ma il metodo & è spesso preferito in quanto è più breve e rende più chiaro quale azione sta eseguendo la formula.
Queste due colonne di supporto possono quindi essere combinate in un'unica formula:
| 1 | =SE(C3="Rosso",B3&", ","")&D4 |
Una cella di riepilogo può quindi fare riferimento al primo valore nel Elenco giocatori colonna di supporto:
| 1 | =D3 |
Se il valore finale "", non è richiesto, è necessario aggiungere un'ulteriore funzione SE nidificata alla formula:
| 1 | =SE(C3="Rosso",SE(D4="",B3,B3&", "),"")&D4 |
La parte aggiuntiva della formula è la funzione SE di seguito:
| 1 | SE(D4="",B3,B3&", ") |
Questa parte della formula controlla il valore della cella sottostante. Se è vuoto, questa cella deve essere l'ultima Nome del giocatore nell'elenco e il nome viene scritto senza delimitatore. Se la cella sottostante contiene un valore, questa cella non è l'ultima Nome del giocatore nell'elenco e quindi viene aggiunto il delimitatore ", ".
TextUnisciti se in Fogli Google
Queste formule funzionano allo stesso modo in Fogli Google come in Excel, tranne per il fatto che la funzione ARRAYFORMULA deve essere utilizzata in Fogli Google per valutare correttamente i risultati. Questo può essere aggiunto automaticamente premendo i tasti CTRL + MAIUSC + INVIO durante la modifica della formula:
| 1 | =ARRAYFORMULA(TEXTJOIN(", ",TRUE,(IF(C3:C8="Rosso",B3:B8,"")))) |
