10 funzioni essenziali di Microsoft Excel per l'analisi dei dati

1. CONCATENARE

=CONCATENATE è una delle funzioni più cruciali per l'analisi dei dati in quanto consente di combinare testo, numeri, date, ecc. da più celle in una. La funzione è particolarmente utile per combinare i dati di celle diverse in un'unica cella. Ad esempio, è utile per creare i parametri di monitoraggio per le campagne di marketing, creare query API, aggiungere testo a un formato numerico e molte altre cose.

Nell'esempio sopra, volevo il mese e le vendite insieme in un'unica colonna. Per questo, ho usato la formula = CONCATENATE (A2, B2) nella cella C2 per ottenere Jan $ 700 come risultato.

Formula: =CONCATENA(celle che vuoi combinare)

2. LEN

=LEN è un'altra utile funzione per l'analisi dei dati che essenzialmente restituisce il numero di caratteri in una determinata cella. La funzione è prevalentemente utilizzabile durante la creazione di tag del titolo o descrizioni che hanno un limite di caratteri. Può anche essere utile quando stai cercando di scoprire le differenze tra diversi identificatori univoci che sono spesso piuttosto lunghi e non nell'ordine corretto.

Nell'esempio sopra, volevo contare le cifre per il numero di visualizzazioni che ricevevo ogni mese. Per questo, ho utilizzato la formula = LEN (C2) nella cella D2 per ottenere 5 come risultato.

Formula: =LUNGHEZZA(cellula)

3. CERCA.VERT

=VLOOKUP è probabilmente una delle funzioni più riconoscibili per chiunque abbia familiarità con l'analisi dei dati. Puoi usarlo per abbinare i dati di una tabella con un valore di input. La funzione offre due modalità di corrispondenza: esatta e approssimativa, controllata dall'intervallo di ricerca. Se imposti l'intervallo su FALSE, cercherà una corrispondenza esatta, ma se lo imposti su TRUE, cercherà una corrispondenza approssimativa.

Nell'esempio sopra, volevo cercare il numero di visualizzazioni in un determinato mese. Per questo, ho usato la formula = VLOOKUP ("Jun", A2: C13, 3) nella cella G4 e ho ottenuto 74992 come risultato. Qui, "Jun" è il valore di ricerca, A2:C13 è l'array della tabella in cui sto cercando "Jun" e 3 è il numero della colonna in cui la formula troverà le visualizzazioni corrispondenti per giugno.

L'unico aspetto negativo dell'utilizzo di questa funzione è che funziona solo con i dati che sono stati organizzati in colonne, da cui il nome: ricerca verticale. Quindi, se i tuoi dati sono disposti in righe, devi prima farlo trasponi le righe in colonne.

Formula: =CERCA.VERT(lookup_value, array_tabella, col_index_num, [range_lookup])

4. INDICE/MATCH

Proprio come la funzione CERCA.VERT, le funzioni INDICE e CONFRONTA sono utili per la ricerca di dati specifici in base a un valore di input. INDEX e MATCH, se usati insieme, possono superare i limiti di VLOOKUP di fornire risultati errati (se non stai attento). Quindi, quando si combinano queste due funzioni, possono individuare il riferimento ai dati e cercare un valore in una matrice a dimensione singola. Che restituisce le coordinate dei dati come un numero.

Nell'esempio sopra, volevo cercare il numero di visualizzazioni a gennaio. Per questo, ho usato la formula =INDICE (A2:C13, MATCH("Jan", A2:A13,0), 3). Qui, A2: C13 è la colonna di dati che voglio che la formula restituisca, "Jan" è il valore che voglio abbinare, A2: A13 è il colonna in cui la formula troverà "Jan" e lo 0 significa che voglio che la formula trovi una corrispondenza esatta per valore.

Se vuoi trovare una corrispondenza approssimativa dovrai sostituire lo 0 con 1 o -1. In modo che 1 trovi il valore più grande minore o uguale al valore di ricerca e -1 troverà il valore più piccolo minore o uguale al valore di ricerca. Nota che se non usi 0, 1 o -1, la formula utilizzerà 1, by.

Ora, se non vuoi codificare il nome del mese, puoi sostituirlo con il numero di cella. Quindi possiamo sostituire "Jan" nella formula sopra menzionata con F3 o A2 per ottenere lo stesso risultato.

Formula: =INDICE(colonna dei dati che si desidera restituire, MATCH (punto dati comune che si sta tentando di abbinare, colonna dell'altra origine dati che ha il punto dati comune, 0))

5. MINI/MAXIF

=MINIFS e =MAXIFS sono molto simili alle funzioni =MIN e =MAX, tranne per il fatto che consentono di prendere il set di valori minimo/massimo e abbinarli anche a criteri particolari. Quindi, in sostanza, la funzione cerca i valori minimo/massimo e li abbina ai criteri di input.

Nell'esempio sopra, volevo trovare i punteggi minimi in base al sesso dello studente. Per questo, ho usato la formula =MINIFS (C2:C10, B2:B10, "M") e ho ottenuto il risultato 27. Qui C2:C10 è la colonna in cui la formula cercherà i punteggi, B2:B10 è una colonna in cui la formula cercherà i criteri (il genere) e "M" è il criterio.

Allo stesso modo, per i punteggi massimi, ho usato la formula =MAXIFS(C2:C10, B2:B10, "M") e ho ottenuto il risultato 100.

Formula per MINIFS: =MINIFS(intervallo_min, intervallo_criteri1, criteri1,...)

Formula per MAXIFS: =MAXIFS(intervallo_massimo, intervallo_criteri1, criteri1,...)

6. MEDIE

La funzione =MEDIA.SE consente di trovare una media per un particolare set di dati in base a uno o più criteri. Durante l'utilizzo di questa funzione, è necessario tenere presente che ogni criterio e intervallo medio possono essere diversi. Tuttavia, nella funzione =MEDIA.SE, sia l'intervallo di criteri che l'intervallo di somma devono avere lo stesso intervallo di dimensioni. Notare la differenza di singolare e plurale tra queste funzioni? Bene, è lì che devi stare attento.

In questo esempio, volevo trovare il punteggio medio in base al sesso degli studenti. Per questo ho usato la formula =AVERAGEIFS(C2:C10, B2:B10, "M") e ho ottenuto 56,8 come risultato. Qui, C2:C10 è l'intervallo in cui la formula cercherà la media, B2:B10 è l'intervallo dei criteri e "M" è il criterio.

Formula: =MEDIA.SE(intervallo_medio, intervallo_criteri1, criteri1,...)

7. COUNTIFS

Ora, se vuoi contare il numero di istanze in cui un set di dati soddisfa criteri specifici, dovrai utilizzare la funzione =COUNTIFS. Questa funzione ti consente di aggiungere criteri illimitati alla tua query e quindi rende il modo più semplice per trovare il conteggio in base ai criteri di input.

In questo esempio, volevo trovare il numero di studenti maschi o femmine che hanno ottenuto voti sufficienti (cioè >=40). Per questo ho usato la formula =COUNTIFS(B2:B10, "M", C2:C10, ">=40"). Qui, B2:B10 è l'intervallo in cui la formula cercherà i primi criteri (genere), "M" è il primo criterio, C2:C10 è l'intervallo in cui la formula cercherà il secondo criterio (punti) e ">=40" è il secondo criteri.

Formula: =CONTA.SE(intervallo_criteri1, criteri1,...)

8. SUMPRODOTTO

La funzione =SUMPRODUCT ti aiuta a moltiplicare intervalli o array e poi restituisce la somma dei prodotti. È una funzione piuttosto versatile e può essere utilizzata per contare e sommare array come COUNTIFS o SUMIFS, ma con maggiore flessibilità. Puoi anche utilizzare altre funzioni all'interno di SUMPRODUCT per estenderne ulteriormente le funzionalità.

In questo esempio, volevo trovare la somma totale di tutti i prodotti venduti. Per questo, ho usato la formula =SUMPRODUCT(B2:B8, C2:C8). Qui, B2:B8 è il primo array (la quantità di prodotti venduti) e C2:C8 è il secondo array (il prezzo di ciascun prodotto). La formula quindi moltiplica la quantità di ciascun prodotto venduto con il suo prezzo e quindi somma tutto per fornire le vendite totali.

Formula: =SOMMAPRODOTTO(matrice1, [matrice2], [matrice3],...)

9. ORDINARE

La funzione =TRIM è particolarmente utile quando si lavora con un set di dati che contiene diversi spazi o caratteri indesiderati. La funzione consente di rimuovere facilmente questi spazi o caratteri dai dati, consentendo di ottenere risultati accurati durante l'utilizzo di altre funzioni.

In questo esempio, volevo rimuovere tutti gli spazi extra tra le parole Mouse e pad in A7. Per questo ho usato la formula = TRIM (A7).

La formula ha semplicemente rimosso gli spazi extra e ha fornito il risultato Mouse pad con un singolo spazio.

Formula: =TRIM(testo)

10. TROVA/CERCA

A completare le cose ci sono le funzioni TROVA/RICERCA che ti aiuteranno a isolare un testo specifico all'interno di un set di dati. Entrambe le funzioni sono abbastanza simili in quello che fanno, tranne per una grande differenza: la funzione =FIND restituisce solo corrispondenze con distinzione tra maiuscole e minuscole. Nel frattempo, la funzione =SEARCH non ha tali limitazioni. Queste funzioni sono particolarmente utili quando si cercano anomalie o identificatori univoci.

In questo esempio, volevo trovare il numero di volte in cui "Gui" è apparso all'interno di Guiding Tech per il quale ho usato la formula =FIND(A2, B2), che ha prodotto il risultato 1. Ora, se volessi trovare il numero di volte in cui "gui" è apparso all'interno di Guiding Tech, dovrei usare la formula =SEARCH perché non fa distinzione tra maiuscole e minuscole.

Formula per Trova: =TROVA(trova_testo, entro_testo, [num_iniziale])

Formula per la ricerca: =CERCA(trova_testo, entro_testo, [num_iniziale])