Quando si lavora con i dati in Excel, alcune attività possono sembrare inutilmente noiose. Forse è necessario suddividere una colonna di nomi completi in colonne separate per nome e cognome, o combinare il testo di più celle con virgole specifiche. Non si tratta di sfide analitiche complesse, ma di semplici attività di elaborazione dati che si presentano regolarmente.
La buona notizia è che Excel ha funzioni integrate progettate specificamente per queste situazioni. Tuttavia, vengono spesso trascurate perché non fanno parte di Il set di strumenti standard di Excel che la maggior parte delle persone impara, me compreso. Le funzioni che tratterò qui non riguardano calcoli avanzati, ma se vi trovate a dover eseguire operazioni ripetitive sui dati, queste funzioni possono farvi risparmiare tempo.
Link veloci
5. DIVISO DI TESTO
Separa i testi incollati insieme
Se vi è mai capitato di ricevere un foglio di calcolo in cui qualcuno ha stipato il proprio nome e cognome, e magari anche le iniziali del secondo nome, in un'unica cella, sapete quanto sia complicato separare quei dati. TextSplit risolve proprio questo problema: preleva il testo da una singola cella e lo suddivide su più colonne in base a un separatore specificato.
Proviamo a lavorare con un foglio di calcolo di esempio per le vendite. Vedrai i nomi dei venditori elencati come "Sarah Chen", "Mike Johnson" e "Lisa Park", tutti in un'unica colonna. Invece di dover digitare manualmente ogni nome in colonne separate, TextSplit può farlo automaticamente.
La formula è la seguente:
=TEXTSPLIT(testo, delimitatore_colonna, [delimitatore_riga], [ignora_vuoto], [modalità_corrispondenza], [riempimento_con])
Ecco cosa fa ogni insegnante:
- il testo: La cella che contiene il testo che vuoi dividere.
- col_delimitatore: Il carattere che separa i dati (ad esempio uno spazio, una virgola o un punto e virgola).
- row_delimiter (facoltativo): Utilizzato per la suddivisione in righe e colonne.
- ignore_empty (facoltativo): TRUE ignora i valori vuoti, FALSE li mantiene (il valore predefinito è FALSE).
- match_mode (facoltativo): Controlla la distinzione tra maiuscole e minuscole (0 per distinzione tra maiuscole e minuscole, 1 per non distinzione tra maiuscole e minuscole).
- pad_with (facoltativo): Con cosa si riempiono le celle vuote quando i risultati hanno lunghezze diverse?
Ad esempio, per i nomi dei rappresentanti di vendita, utilizzerei la seguente formula per suddividere i nomi in colonne separate:
=TESTO.DIVISO(A2; " ")
La funzione crea automaticamente il numero necessario di colonne in base ai dati. Sebbene questo approccio di base funzioni nella maggior parte dei casi, sono disponibili parametri aggiuntivi che consentono un controllo più dettagliato. Funzione DIVIDETESTO in Excel.
4. COLLEGAMENTO TESTO
Unisci più celle in una cella
TEXTJOIN fa l'opposto di TEXTSPLIT. Prende il testo da più celle e lo combina in un'unica cella utilizzando il separatore scelto. Questo è utile quando è necessario creare valori sequenziali come indirizzi completi, descrizioni di prodotti o elenchi di indirizzi email.
La formula è la seguente:
=TEXTJOIN(delimitatore, ignora_vuoto, testo1, [testo2], ...)
Ecco cosa controlla ciascun parametro:
- delimitatore: Il carattere o il testo che separa i valori incorporati (virgola, spazio, trattino, ecc.).
- ignora_vuoto: TRUE per ignorare le celle vuote, FALSE per includerle nel risultato.
- testo1, testo2, ecc.: Le celle o gli intervalli che si desidera unire (è possibile specificare singole celle o interi intervalli).
Osservando il foglio di calcolo delle vendite, se ho colonne separate per nome e regione, ma ho bisogno di inserirle in un'unica colonna che le combini, userei TEXTJOIN. ignora_vuoto Impostato su TRUE significa che tutte le celle vuote verranno automaticamente saltate.
=TESTOUNISCI(" - ", VERO, B2, D2)Quando si sceglie tra diversi metodi di integrazione del testo, la comprensione Differenze tra le funzioni CONCAT e TEXTJOIN Può aiutarti a scegliere lo strumento più adatto alle tue specifiche esigenze di integrazione dei dati.
3. SCEGLI COL
Specifica colonne specifiche dei tuoi dati.
CHOOSECOLS consente di estrarre colonne specifiche da un intervallo senza dover copiare e incollare o creare riferimenti. Se si dispone di un set di dati di grandi dimensioni ma per l'analisi sono necessarie solo le colonne 2, 5 e 8, questa funzione prenderà solo le colonne necessarie e scarterà il resto.
In base ai dati di vendita, potrei voler estrarre solo il venditore e i suoi nomi, ignorando le date degli ordini, le categorie di prodotto e altri dettagli. Invece di selezionare e copiare manualmente le colonne, la funzione CHOOSECOLS crea un riferimento dinamico che si aggiorna automaticamente quando i dati di origine cambiano.
La funzione segue la seguente formula:
=SCEGLICOLL(array, col_num1, [col_num2], ...)
Ecco come funziona ciascun parametro:
- Vettore: L'intervallo o la tabella che contiene i dati di origine (può essere un intervallo di celle come A1:F100 o un riferimento di tabella).
- col_num1: Il numero della prima colonna che si desidera estrarre (1 per la prima colonna, 2 per la seconda colonna, ecc.).
- col_num2, ecc.: Numeri di colonna aggiuntivi che desideri includere (facoltativo: puoi specificarne quanti ne vuoi).
Ad esempio, se volessi estrarre i nomi dei rappresentanti di vendita dalla colonna 2 e il loro stato dalla colonna 9, userei:
=SCEGLI.COLL(A1:I23, 2, 9)
La funzione restituisce entrambe le colonne come array in streaming, ridimensionato automaticamente per adattarsi ai dati. Ecco perché CHOOSECOLS è una delle Funzioni di Excel che possono farti risparmiare un sacco di tempoElimina la necessità di più formule CERCA.VERT o di copiare manualmente le colonne quando si lavora con grandi set di dati.
Excel dispone anche della funzione CHOSEROWS, che funziona in modo simile, ma seleziona righe specifiche anziché colonne, utilizzando la stessa struttura della formula con i numeri di riga.
2. Prendi e lascia
Estrai parti dei tuoi dati
TAKE e DROP lavorano in coppia per catturare parti specifiche dell'intervallo di dati. TAKE estrae un numero specifico di righe o colonne dall'inizio o dalla fine del set di dati, mentre DROP rimuove righe o colonne dall'inizio o dalla fine, lasciando ciò che rimane.
Queste funzioni fungono da strumenti precisi per il campionamento dei dati. Che si necessiti solo delle prime dieci righe di dati per un'analisi rapida o che si desideri rimuovere le righe di intestazione che intasano i calcoli, queste funzioni gestiscono il compito in modo impeccabile.
TAKE utilizza questa formula:
=TAKE(array, righe, [colonne])
DROP segue uno schema simile:
=DROP(array, righe, [colonne])
Ecco come funzionano i parametri per entrambe le funzioni:
- Vettore: L'intervallo di dati di origine che si desidera estrarre o modificare.
- righe: Numero di righe da prendere/eliminare (i numeri positivi vanno dall'alto, quelli negativi dal basso).
- colonne (facoltativo):
Numero di colonne che si desidera eliminare o rimuovere (positivo da sinistra, negativo da destra).
Per ottenere le prime cinque righe di dati sulle vendite, utilizzare la seguente formula:
=PRENDI(A1:C100, 5)
Per rimuovere le prime 20 righe e lavorare con dati puliti, prova:
=ESCI(A1:C23; 20)
È possibile combinare operazioni su righe e colonne. Ad esempio, la seguente formula restituisce le prime dieci righe e le prime tre colonne:
=PRENDI(A1:F23, 10, 3)
Queste funzioni diventano molto utili, soprattutto quando hai bisogno di sottoinsiemi dinamici di dati che si adattino automaticamente. Scopri Come utilizzare le funzioni TAKE e DROP in Excel Offre la possibilità di creare report flessibili che si adattano alle dimensioni variabili dei set di dati.
1. AGGREGATO
Calcoli potenti che gestiscono dati disordinati
AGGREGATE combina le funzionalità di 19 diverse funzioni statistiche in un'unica formula flessibile. Ciò che la distingue è la sua capacità di ignorare errori, righe nascoste o dati filtrati, cosa che funzioni standard come SOMMA o MEDIA non sono in grado di fare in modo affidabile.
Se i dati contengono errori #N/D o se si filtra per mostrare solo determinate regioni, AGGREGATE può calcolare somme, medie o altre statistiche senza che questi problemi interferiscano con i risultati. Lo trovo utile quando si lavora con set di dati dinamici in cui la visibilità e la qualità dei dati cambiano frequentemente.
La struttura della frase comprende diversi componenti:
=AGGREGATE(numero_funzione; opzioni; array; [k])
Ogni criterio controlla diversi aspetti del calcolo:
- numero_funzione: Un numero da 1 a 19 che specifica la funzione da utilizzare (1=MEDIA, 4=MAX, 9=SOMMA, 12=MEDIANA, ecc.).
- opzioni: Controlla cosa ignorare durante il calcolo (0=nessuno, 1=righe nascoste, 2=valori di errore, 3=righe nascoste ed errori, 5=solo valori di errore, 6=righe nascoste e valori di errore).
- Vettore: L'intervallo di celle da calcolare.
- k (facoltativo):
- Utilizzato solo con determinate funzioni, ad esempio GRANDE, PICCOLO o PERCENTILE.
Per riassumere gli importi delle vendite mostrati ignorando eventuali errori, posso usare:
=AGGREGA(9; 6; D2:D23)
Il numero 9 specifica SUM, mentre il numero 6 indica alla funzione di ignorare sia le righe nascoste sia i valori di errore.
Questa potente capacità di eseguire calcoli è esattamente il motivo per cui è incluso AGGREGATE. Elenco delle funzioni di Excel che ogni impiegato dovrebbe conoscere—Si occupa del caos dei dati del mondo reale che le funzioni più semplici non riescono a gestire in modo efficace.
Strumenti integrati che vale la pena utilizzare
Le funzioni di Excel più importanti spesso non sono quelle che si imparano per prime. Tuttavia, risolvono i problemi più sottili che si presentano nel lavoro effettivo sui fogli di calcolo, tra cui la gestione di dati di testo disordinati, l'estrazione di parti specifiche da grandi set di dati e l'esecuzione di calcoli su dati incompleti. Nessuna delle funzioni che abbiamo esaminato richiede competenze avanzate di Excel. Tuttavia, TEXTSPLIT, CHOOSECOLS, TAKE e DROP sono disponibili solo in Microsoft 365 ed Excel per il Web.
La prossima volta che ti ritrovi a dover ripulire ripetutamente i dati o a copiare manualmente le colonne, ricorda che queste funzioni esistono. Sono già integrate in Excel per gestire le operazioni più noiose, così puoi concentrarti su ciò che i dati ti dicono effettivamente.
