Foglio di riferimento per le formule di Excel: guida avanzata

Quindi hai esplorato le basi, affrontato le cose intermedie e ora sei pronto per lottare con i pezzi grossi: le formule avanzate di Excel! Excel ha molte funzioni e formule avanzate per calcoli sofisticati, quindi è utile avere una guida che indichi quelle di cui hai bisogno.

Questo cheat sheet delle formule di Excel copre formule di previsione avanzate, analisi statistiche, funzioni di manipolazione dei dati, gestione degli errori e altro ancora.

Questo riferimento ti fornirà la conoscenza di come utilizzare queste funzioni avanzate. Ogni formula è accompagnata da spiegazioni chiare, sintassi ed esempi pratici per aiutare gli utenti intermedi di Excel a diventare utenti esperti avanzati.

Si prega di scaricare e stampare il foglietto illustrativo e tenerlo a portata di mano.

Ok, cominciamo.

Excel-formules-cheat-sheet-advanced-1 Download

Innanzitutto, entriamo nelle formule di matrice.

Sommario

• Formule di matrice
  • 1. Funzione UNICA
  • 2. Funzione ORDINA
  • 3. Funzione FILTRO
• Randomizzare le funzioni di Excel
  • 1. Funzione RANDBETWEEN
  • 2. Funzione RANDARRAY
• Formule di previsione avanzate in Microsoft Excel
  • 1. Funzione PREVISIONE.ETS
  • 2. Funzione TREND
• Formule statistiche avanzate
  • 1. Funzione PERCENTILE
  • 2. Funzione QUARTILE
• Analisi avanzata dei dati e formule di manipolazione
  • 1. Funzione TRASPOSTA
  • 2. Funzione FREQUENZA
  • 3. Funzione GETPIVOTDATA
• Gestione avanzata degli errori
• Formule di ricerca avanzata
  • 1. Funzione CERCA X
  • 2. Funzione XMATCH
• Pensieri finali

Formule di matrice

Il nostro cheat sheet per principianti ti mostra come ordinare e filtrare i tuoi dati manualmente. Gli utenti avanzati lo fanno a livello di programmazione con formule di matrice.

Le formule di matrice consentono di eseguire calcoli su più celle contemporaneamente. Queste sono tre funzioni fondamentali:

1. UNICO

2. ORDINARE

3. FILTRO

Alcune di queste funzioni sono disponibili solo nelle versioni più recenti di Microsoft Excel.

1. Funzione UNICA

La funzione UNIQUE accetta un intervallo e restituisce un elenco di valori univoci.

Supponiamo di disporre di dati di vendita per articoli di abbigliamento. Per trovare gli elementi unici nella colonna A, utilizzare questa formula:

=UNICO(A2:A6)

2. Funzione ORDINA

La funzione SORT ordina il contenuto di un intervallo. La sintassi è:

ORDINA(array, [indice_ordinamento], [ordine_ordinamento], [per_col])

• array : l'intervallo di valori da ordinare.

• sort_index : la colonna da ordinare (1 per impostazione predefinita)

• sort_order : 1 per ascendente (predefinito) o 2 per discendente).

• by_col : TRUE per ordinare per colonna (predefinito) o FALSE per ordinare per riga.

Gli ultimi tre argomenti sono facoltativi e i valori predefiniti sono in genere quelli desiderati.

Per ordinare i dati di esempio in base alla prima colonna, utilizzare questa formula:

=ORDINA(A2:C6)

3. Funzione FILTRO

La funzione FILTRO consente di filtrare un intervallo in base a una condizione specifica. Questa è la sintassi:

=FILTRO(matrice, includi, [se_vuoto])

• array : l'intervallo da filtrare.

• include : la condizione che determina quali valori filtrare.

• if_empty : specifica cosa restituire se nessun valore soddisfa i criteri di filtraggio (il valore predefinito è "").

Supponiamo di voler filtrare le righe nei dati di esempio per mostrare solo dove il valore delle vendite è maggiore di $ 15. Usa questa formula:

=FILTRO(A2:DO6; DO2:DO6>15)

Randomizzare le funzioni di Excel

Il nostro trucco intermedio mostra come utilizzare la funzione RAND che produce un numero casuale compreso tra 0 e 1.

Gli utenti avanzati di Excel sanno come utilizzare le funzioni di randomizzazione per generare rapidamente dati di esempio.

1. Funzione RANDBETWEEN

La funzione RANDBETWEEN è più flessibile di RAND perché puoi specificare i numeri inferiore e superiore come qualcosa di diverso da 0 e 1.

Per generare dati con numeri compresi tra 1 e 100, inserisci questa formula nella cella A1:

=RANDBETWEEN(1; 100)

Quindi, copia la cella in tutte le righe e colonne che desideri. Ci vogliono pochi secondi per produrre una griglia di numeri randomizzati:

2. Funzione RANDARRAY

Potresti pensare che sarebbe carino evitare la copia manuale della funzione RANDBETWEEN. Per diventare super avanzato, puoi utilizzare la nuova funzione RANDARRAY nell'ultima versione di Microsoft Excel.

La sintassi è:

RANDARRAY([righe], [colonne], [min], [max], [numero intero])

• righe : numero di righe

• colonne : numero di colonne

• min : numero più basso

• max : numero più alto

• numero intero : il valore predefinito è VERO, altrimenti utilizza numeri decimali.

Per generare numeri casuali compresi tra 1 e 100 su sei righe e nove colonne, utilizza questa formula:

=RANDARRAY(6, 9, 1, 100, VERO)

Formule di previsione avanzate in Microsoft Excel

Le funzioni di previsione di Excel vengono utilizzate per prevedere i valori futuri in base alle tendenze dei dati esistenti. Queste funzioni aiutano a identificare i modelli e le tendenze del progetto in base ai dati.

1. Funzione PREVISIONE.ETS

La funzione PREVISIONE precedente è stata sostituita con un set di funzioni più recenti in Excel 2016.

Scegli la funzione in base al modello di previsione specifico che desideri. Ad esempio, la funzione FORECAST.ETS utilizza l' algoritmo Exponential Smoothing .

La sintassi è:

PREVISIONE.ETS(data_target, valori, sequenza temporale)

• target_date : la data per cui desideri un valore calcolato.

• valori : i dati storici.

• timeline : un intervallo di date

Supponiamo di avere le date dall'1 al 5 gennaio nella colonna A e gli importi delle vendite nella colonna B. Questa formula prevederà l'importo delle vendite successivo:

=PREVISIONE.ETS(“6 gennaio 2023”, B2:B6, A2:A6)

2. Funzione TREND

La funzione TENDENZA proietta un insieme di valori in base al metodo dei minimi quadrati. Restituisce un array. La sintassi è:

TENDENZA(y_nota, [x_nota], [nuova_x], [const])

• y_noto : intervallo di valori y

• x_nota : intervallo di valori x

• new_x : intervallo di valori calcolati

Spesso, la y_nota sono i punti dati, mentre la x_nota sono le date.

Utilizzando gli stessi dati dell'esempio precedente, puoi inserire la formula seguente nella cella C2. Verrà generato un insieme di valori.

=TENDENZA(B2:B6; A2:A6)

Formule statistiche avanzate

Le funzioni statistiche avanzate includono il calcolo di percentili e quartili. Alcune funzioni matematiche sono disponibili per compatibilità con le versioni precedenti, ma si consiglia di utilizzare le versioni più aggiornate.

1. Funzione PERCENTILE

Questa funzione calcola la percentuale di punti dati al di sotto di un determinato valore. La sintassi è:

INC.PERCENTILE(matrice, k)

• matrice : l'intervallo di celle

• k : il percentile da 0 a 1

Supponiamo di voler calcolare il 70° percentile dei dati nella colonna B. Usa questa formula:

=INC.PERCENTILE(B2:B6; 0,7)

2. Funzione QUARTILE

Questa funzione è una variazione della funzione percentile ma utilizza i quarti per dividere i dati. Questa è la sintassi:

QUARTILE.INC(array, quarto)

• array : l'intervallo di dati

• quart : 1 per il 25° percentile, 2 per il 50°, 3 per il 75° e 4 per il massimo.

La formula seguente calcolerà il primo quartile di dati nella colonna A.

=INC.QUARTILE(A2:A5; 1)

Analisi avanzata dei dati e formule di manipolazione

Diverse funzioni avanzate consentono di cambiare il formato dei dati, analizzare le distribuzioni di frequenza ed estrarre i dati dalle tabelle pivot.

1. TRASPORRE

2. FREQUENZA

3. GETPIVOTDATA

1. Funzione TRASPOSTA

A volte vuoi spostare i dati nelle tue righe in colonne e viceversa. Puoi farlo manualmente o utilizzare invece la funzione TRANSPOSE.

Supponiamo di avere gli elementi "T-Shirt", "Hoodie" e "Jeans" nelle celle A2, A3 e A4. Vuoi trasformarli in intestazioni di colonna. Questa funzione restituisce i valori in una singola riga:

=TRASPOSTA(A2:A4)

2. Funzione FREQUENZA

Questa funzione calcola la distribuzione di frequenza dei valori all'interno di un set di dati. Questa è la sintassi:

FREQUENZA(array_dati, array_bins)

• data_array : intervallo di valori.

• bins_array : gli intervalli da utilizzare.

Supponiamo di avere i dati di vendita nella colonna B e di voler analizzare la distribuzione di frequenza dei valori in base a quanti importi sono:

• inferiore a 20.

• da 20 a 80.

• superiore a 80.

Ciò rappresenta tre contenitori e può essere calcolato con questa formula:

=FREQUENZA(A2:A6; {20,80})

Per ulteriori informazioni sulle distribuzioni di frequenza in Excel, guarda questo video:

3. Funzione GETPIVOTDATA

Questa funzione consente di estrarre informazioni di riepilogo dalle tabelle pivot. Questa è la sintassi:

GETPIVOTDATA(campo_dati, tabella_pivot, [campo1, elemento1], [campo2, elemento2], …)

• data_field : il campo dati o il valore che desideri recuperare dalla tabella pivot.

• pivot_table : un riferimento alla tabella pivot.

• field1, item1, ecc. : le coppie campo/elemento per cui filtrare.

Supponiamo di avere una tabella pivot basata sul colore degli articoli venduti. Per estrarre le vendite per gli articoli rossi, utilizzare questa formula:

=GETPIVOTDATA("Vendite", A1, "Colore", "Rosso")

Gestione avanzata degli errori

Anche le formule di Excel più basilari possono produrre errori. Gli utenti intermedi dovrebbero sapere come utilizzare ISERROR per gestire gli errori. Gli utenti esperti dovrebbero anche avere familiarità con la funzione ERROR.TYPE per l'identificazione degli errori.

La funzione ERROR.TYPE consente di identificare il tipo specifico di errore all'interno di una cella o di una formula.

Restituisce un valore numerico corrispondente a diversi tipi di errore, ad esempio #N/D, #VALUE!, #REF! e altro.

Supponiamo di avere un errore nella cella A1 e di voler identificare il suo tipo di errore. La seguente formula restituirà il numero che corrisponde all'errore specifico:

=ERRORE.TIPO(A1)

È possibile combinare questo con più funzioni per rispondere in modo diverso a seconda del tipo di errore. Questi sono gli errori più comuni e i loro valori:

1. #NULLO! (nessuna cella comune trovata in un intervallo)

2. #DIV/0! ( divisione per zero o una cella vuota)

3. #VALORE! (tipo di dati o argomento inappropriato in una formula)

4. #RIF! (una cella referenziata è stata cancellata o c'è un riferimento circolare )

5. #NOME? (Excel non riconosce la funzione o l'intervallo)

6. #NUM! (valore numerico non valido)

7. #N/D (il valore non può essere trovato)

Supponiamo di voler gestire tre tipi di errore specifici. Utilizza questa formula per visualizzare un messaggio di errore specifico in base al tipo:

=IF(ISERROR(C2), IF(ERROR.TYPE(C2)=2, "Errore divisione per zero", IF(ERROR.TYPE(C2)=3, "Errore valore non valido", IF(ERROR.TYPE(C2) )=7, “Valore non trovato errore”, “Altro errore”))), C2)

Formule di ricerca avanzata

I nostri cheat sheet per principianti e intermedi coprivano una selezione di funzioni di ricerca . Ecco alcune opzioni avanzate:

1. CERCA X

2. XMATCH

1. Funzione CERCA X

Questa funzione di ricerca consente di cercare un valore in un intervallo e restituire un valore corrispondente da un'altra colonna o intervallo.

Offre maggiore versatilità rispetto alle funzioni di ricerca più semplici come VLOOKUP . Questa è la sintassi:

CERCA.X(lookup_value, lookup_array, return_array, [match_mode], [search_mode], [if_not_found])

• lookup_value : il valore che vuoi cercare.

• lookup_array : l'intervallo per la ricerca.

• return_array : l'intervallo che mostrerà il valore corrispondente.

• match_mode : corrispondenza esatta (0), successiva minore (1), successiva maggiore (-1) o corrispondenza con caratteri jolly (2).

• search_mode : -1 per l'alto verso il basso, 1 per il basso verso l'alto o 2 per la ricerca binaria.

• if_not_found : imposta il valore da restituire se non viene trovata alcuna corrispondenza.

Si supponga di voler cercare in un intervallo di dati la prima occorrenza di un articolo di abbigliamento e restituire gli importi delle vendite. Questa formula cercherà il testo "Hoodie" e restituirà il valore nella cella adiacente se trovato:

=CERCA.X("Felpa con cappuccio", A2:A6, B2:B6)

2. Funzione XMATCH

Questa funzione consente di trovare la posizione di un valore specificato all'interno di un intervallo o di un array. Questa è la sintassi:

XMATCH(valore_ricerca, matrice_ricerca, [tipo_corrispondenza], [modalità_ricerca])

• lookup_value : il valore che vuoi trovare.

• lookup_array : l'intervallo in cui vuoi cercare.

• match_type : corrispondenza esatta (0), successivo più piccolo (-1), successivo più grande (1).

• search_mode : ricerca binaria (1) o ricerca lineare (2).

Supponiamo di voler trovare la prima occorrenza di un elemento giallo in un intervallo all'interno della colonna B. Usa questa formula:

=XCONFRONTA(“Giallo”, SI2:SI6, 0)

Pensieri finali

Questo cheat sheet ha coperto un'ampia gamma di funzioni, dall'analisi statistica, alle formule di ricerca, alle tecniche di manipolazione dei dati e alle strategie di gestione degli errori.

Gli esempi e le spiegazioni forniti aiutano a demistificare queste formule avanzate, rendendole accessibili anche a chi ha un'esperienza limitata.

Quando inizi a incorporarli nelle tue attività di Excel, sei sulla buona strada per aumentare le tue abilità di Excel a un livello avanzato.

Ma ricorda, questo cheat sheet è solo la punta dell'iceberg. Le cose davvero sorprendenti accadono quando diventi creativo, mescoli e abbini queste formule e le personalizzi per risolvere le tue sfide uniche. Excel è come una tela e queste formule sono la tua tavolozza, quindi vai avanti, dipingi il tuo capolavoro!