Foglio di riferimento per le formule di Excel: guida intermedia

Quindi sei riuscito a padroneggiare le basi di Excel - complimenti a te! Ora, hai deciso di fare un salto di qualità e tuffarti nelle cose più avanzate, giusto? Bene, sei fortunato perché abbiamo messo insieme un pratico cheat sheet intermedio di Excel solo per te!

Questo cheat sheet intermedio di Microsoft Excel copre una vasta gamma di argomenti che miglioreranno le tue abilità nell'analisi numerica, nell'elaborazione del testo e nella complessa logica di data e ora.

Avendo Excel Intermediate Cheat Sheet come riferimento desktop, puoi immergerti in profondità nelle formule e nelle funzioni più potenti di Microsoft Excel.

Ora, assicurati di continuare a leggere, ma anche di stampare e salvare il cheat sheet qui sotto!

Iniziamo!

Excel-formules-cheat-sheet-intermedio-1 Download

Sommario

• Formule matematiche intermedie
  • Logica condizionale
• Formule statistiche intermedie
  • 1. MINA e MAXA
  • 2. COUNTA e COUNTIF
• Formule di Excel per l'analisi finanziaria
  • 1. Funzione PRODOTTO
  • 2. Funzioni QUOZIENTE e LOG
  • Funzioni per scenari di investimento
• Formule intermedie di data e ora di Excel
  • 1. Funzioni del mese intermedio
  • 2. Funzioni settimanali intermedie
  • 3. Funzioni di tempo intermedio
• Funzioni di Excel per l'algebra condizionale e logica
  • 1. SE Funzione
  • 2. AND Funzione
  • 3. Funzione OR
  • 4. NON Funzione
• Celle di riferimento
  • 1. Funzione INDIRETTA
  • 2. Funzione INDICE
  • 3. Funzione OFFSET
• Formule di testo intermedie
  • 1. Funzione TEXTJOIN
  • 2. Funzione SOSTITUIRE
  • 3. Funzione SOSTITUTIVA
• Formule di ricerca intermedie
• Formule per la gestione degli errori di Microsoft Excel
  • 1. Funzione IFERRORE
  • 2. Funzione IFNA
• Scorciatoie per formule e funzioni
• Formattazione intermedia delle celle
• Pensieri finali

Formule matematiche intermedie

Il nostro cheat sheet per principianti copriva le funzioni matematiche per principianti più comuni come la funzione SOMMA. Gli utenti intermedi dovrebbero acquisire familiarità con queste funzioni più complesse:

• ABS() restituisce il valore assoluto di un numero.

• SQRT() restituisce la radice quadrata di un numero

• RAND() fornisce un numero casuale compreso tra 0 e 1

Logica condizionale

Gli utenti intermedi dovrebbero anche sapere come eseguire calcoli matematici basati su più condizioni utilizzando queste funzioni:

• SOMMA.PIÙ.SE(intervallo_somma, intervallo_criteri1, criteri1, [….])

• CONTA.SE(intervallo_criteri1, criteri1, [intervallo_criteri2, criteri2], …)

• MEDIA.SE(intervallo_criteri1, criteri1, [intervallo_criteri2, criteri2], …)

Le funzioni valutano i criteri forniti e producono un valore logico VERO o FALSO.

Supponiamo che tu abbia i seguenti dati di vendita nelle colonne A, B e C:

Per calcolare la somma delle vendite per le magliette rosse, utilizzare la formula SOMMA.PIÙ.SE:

=SOMMA.SE(DO2:DO6, LA2:LA6, “Maglietta”, SI2:SI6, “Rosso”)

Per contare il numero di righe con magliette rosse, usa la formula CONTA.PIÙ.SE:

=CONTA.SE(DO2:DO6, LA2:LA6, “Maglietta”, SI2:SI6, “Rosso”)

Per calcolare le vendite medie per le magliette rosse, utilizzare la formula AVERAGEIFS:

=MEDIA.SE(DO2:DO6, LA2:LA6, “Maglietta”, SI2:SI6, “Rosso”)

Formule statistiche intermedie

Oltre alle funzioni di base come MIN e MAX, Excel ha una gamma più ampia di formule statistiche per utenti intermedi. Ecco alcuni dei più utili:

• MINA

• MAXA

• CONTA

• CONTA.SE

1. MINA e MAXA

Le funzioni MIN e MAX più comunemente utilizzate ignorano i valori di testo.

Le funzioni alternative MINA e MAXA considerano sia il testo che i numeri quando cercano i valori più alti o più bassi. I valori di testo vengono valutati come se fossero zero.

Le differenze sono mostrate nell'immagine qui sotto. La prima riga valuta un valore minimo di 10, mentre la seconda riga valuta 0 a causa della presenza di un valore di testo.

2. COUNTA e COUNTIF

La funzione CONTA.VALORI viene utilizzata per contare il numero di celle non vuote in un intervallo.

La funzione CONTA.SE è meno specifica in quanto viene utilizzata per contare il numero di celle all'interno di un intervallo che soddisfano una condizione o criteri specifici.

È inoltre possibile utilizzare queste funzioni per contare il numero di valori distinti in una colonna. Questo video mostra come farlo.

Formule di Excel per l'analisi finanziaria

Esistono diverse formule che vorrai utilizzare quando esegui analisi finanziarie come la previsione degli investimenti:

• PRODOTTO(numero1, [numero2…])

• QUOZIENTE(numeratore, denominatore)

• LOG(numero; [base])

1. Funzione PRODOTTO

Supponiamo di voler calcolare il valore futuro di un investimento iniziale di $ 1.000 (cella B1) a un tasso di interesse annuo del 4% (cella B2) dopo cinque anni (cella B3).

Utilizzare la funzione PRODOTTO per calcolare il valore futuro con questa formula:

=B1 * PRODOTTO(1 + B2)^B3

Questa formula restituirà il valore futuro del tuo investimento.

2. Funzioni QUOZIENTE e LOG

Il passo successivo è calcolare quanti anni ci vorranno perché un investimento raddoppi di valore al tasso di interesse dato.

Utilizzare la funzione QUOZIENTE in combinazione con la funzione LOG per calcolare quanto segue:

=QUOZIENTE(LOG(2) / LOG(1 + B2), 1)

Questa formula restituirà 17 con i dati di esempio, indicando che ci vorranno 17 anni perché l'investimento raddoppi di valore a un tasso di interesse annuo del 4%.

Questa immagine mostra le formule in un foglio di lavoro di Excel :

Funzioni per scenari di investimento

Queste sono alcune funzioni adatte a specifici scenari finanziari. Uno di questi potrebbe essere esattamente ciò di cui hai bisogno:

• NPV – La funzione NPV (Net Present Value) calcolerà il valore attuale netto di un investimento sulla base di una serie di flussi di cassa futuri basati su un tasso di sconto.

• ACCRINT – La funzione ACCRINT calcola gli interessi maturati di un titolo che paga interessi periodici. Questo è utile per determinare gli interessi maturati su un titolo dall'ultima data di pagamento fino ad una determinata data di regolamento.

• INTRATE – La funzione INTRATE calcola il tasso di interesse per un titolo interamente investito.

• PMT – La funzione PMT calcola il pagamento totale su un titolo di debito.

• IRR – La funzione IRR fornisce il tasso interno di un rendimento.

• RENDIMENTO – La funzione RENDIMENTO fornisce il rendimento di un titolo in base al tasso di interesse, al valore nominale e alla scadenza.

Formule intermedie di data e ora di Excel

Le funzioni base di data e ora in Excel includono le funzioni ADESSO e OGGI per la data corrente. Gli utenti intermedi dovrebbero anche sapere come estrarre i componenti da una determinata data utilizzando:

• GIORNO(data)

• MESE(data)

• ANNO(data)

La formula =MESE(“23 aprile 2023″) restituirà un risultato di 4 per il 4° mese. Allo stesso modo, le funzioni GIORNO e ANNO restituiranno rispettivamente 23 e 2023.

1. Funzioni del mese intermedio

Gli utenti intermedi a volte si occuperanno di aggiungere o sottrarre mesi a una data e trovare la fine di un mese.

• DATA(data_inizio, numero_di_mesi)

• EOMONTH(data_inizio, numero_di_mesi)

Ad esempio, la formula =EDATE(“23 aprile 2023”, 2) calcolerà la data due mesi dopo.

La formula =EOMONTH(23 aprile 2023”, 2) calcola la fine del mese due mesi dopo.

Il risultato è "30 giugno 2023", che tiene conto del fatto che ci sono solo trenta giorni a giugno. Se specifichi 3 mesi, il risultato sarà "31 luglio 2023".

2. Funzioni settimanali intermedie

È possibile utilizzare una combinazione delle funzioni SUM e WEEKDAY per contare il numero di interi giorni lavorativi all'interno di un intervallo di date.

La funzione WEEKDAY restituisce il giorno della settimana per una determinata data. Il secondo parametro determina il sistema di numerazione con il valore predefinito da domenica a sabato.

Se hai una serie di date in un intervallo di celle di B1:B40, utilizza questa formula per calcolare i giorni lavorativi:

=SOMMA(–(GIORNO SETTIMANALE(B1:B40,2)>5))

3. Funzioni di tempo intermedio

La funzione YEARFRAC calcola la frazione di un anno tra due date.

La funzione TIMEVALUE viene utilizzata per convertire un'ora rappresentata come testo in un numero decimale che rappresenta la proporzione di un giorno di 24 ore.

Il risultato è un numero di serie di Excel in cui 1 rappresenta un giorno intero di 24 ore, 0,5 rappresenta 12 ore, 0,25 rappresenta 6 ore e così via.

Supponiamo di avere un orario in formato testo “17:45” nella cella A1 e di volerlo convertire in un numero decimale . Questa è la formula:

=TEMPOVALORE(A1)

Il valore 0,74 rappresenta la proporzione della giornata di 24 ore che è trascorsa all'ora di quindici minuti prima delle sei.

Utilizzare per modificare la cella dell'ora da un formato ora a un formato testo. Potresti voler formattare questo come percentuale per essere più chiaro.

Funzioni di Excel per l'algebra condizionale e logica

Le funzioni condizionali e logiche sono essenziali per il processo decisionale in Excel. Le seguenti funzioni possono essere combinate con le formule di Excel più basilari per una logica potente:

1. IF(condizione, valore_se_vero, valore_se_falso):

2. AND(condizione1, condizione2, …)

3. OR(condizione1, condizione2, …)

4. NOT(condizione1, condizione2, …)

La funzione SE valuta una condizione e restituisce valori diversi a seconda che la condizione sia vera o falsa.

Le funzioni AND, OR e NOT controllano quali condizioni sono vere e consentono di prendere decisioni di conseguenza.

Ecco alcuni esempi basati sui dati di vendita che abbiamo utilizzato in precedenza.

1. SE Funzione

Si supponga di voler visualizzare il testo "Vendite basse" se le vendite degli articoli sono inferiori a 15. Se le vendite sono superiori, si desidera visualizzare "Vendite elevate" in una nuova colonna.

Usa questa formula e copiala nelle altre righe:

=SE(C2<15, “Vendite basse”, “Vendite alte”)

2. AND Funzione

Supponiamo di voler visualizzare VERO o FALSO per le magliette con almeno 15 vendite. Usa questa formula:

=E(LA3=”Maglietta”, DO3>=15)

3. Funzione OR

Supponiamo di voler testare più condizioni e restituire VERO se una delle condizioni è soddisfatta e FALSO in caso contrario, utilizzare la funzione OR.

Ad esempio, se abbiamo i punteggi di un test nelle celle A1 e B1 e vogliamo sapere se uno dei due punteggi è superiore a 80, potremmo usare: =OR( A1>80, B1>80) . Ciò restituirà VERO se uno dei punteggi (o entrambi) è superiore a 80 e FALSO se entrambi sono pari o inferiori a 80.

4. NON Funzione

Supponiamo di voler controllare se l'articolo non è una felpa con cappuccio. Usa questa formula:

=NON(A2=”Felpa con cappuccio”)

Celle di riferimento

Un principiante acquisirà rapidamente familiarità con l'uso di riferimenti assoluti e relativi come $A$1 o B2. Gli utenti intermedi dovrebbero acquisire familiarità con l'utilizzo di riferimenti indiretti, indici e offset:

1. INDIRETTO(ref_text)

2. INDICE(intervallo, numero_riga, numero_colonna)

3. OFFSET(cella_riferimento, righe, colonne):

1. Funzione INDIRETTA

La funzione INDIRETTO restituisce il valore del riferimento di cella specificato immesso come testo. Per fare riferimento alla cella B3, utilizzare questa formula:

=INDIRETTO(“B3”)

Il vantaggio rispetto all'utilizzo di semplici riferimenti di cella è che questa funzione fornisce un riferimento dinamico. Ciò significa che Excel aggiorna automaticamente il riferimento quando la struttura del foglio di calcolo cambia, ad esempio una riga viene eliminata.

2. Funzione INDICE

La funzione INDICE viene utilizzata per fare riferimento alle celle all'interno di un intervallo specificato in base ai numeri di riga e colonna.

Per fare riferimento alla cella B3 in una tabella di sei righe, utilizzare questa formula:

=INDICE(B1:B6; 3)

3. Funzione OFFSET

La funzione OFFSET restituisce una cella o un intervallo che è un numero specificato di righe e colonne lontano da una cella di riferimento.

Se vuoi visualizzare il valore della cella che è due celle in orizzontale e una cella in basso da A1, usa questa formula:

=SCARTO(A1; 2; 1)

Ecco le formule in azione:

Formule di testo intermedie

I principianti dovrebbero conoscere funzioni di testo come LEFT, che estrae uno o più caratteri dal lato sinistro di una stringa. Un utente esperto dovrebbe avere familiarità con funzioni come queste:

1. TEXTJOIN(delimitatore, ignore_empty, testo1, [testo2, …])

2. SOSTITUISCI(vecchio_testo, numero_iniziale, num_caratteri, nuovo_testo)

3. SOSTITUTO(testo, vecchio_testo, nuovo_testo, [numero_istanza])

1. Funzione TEXTJOIN

La funzione TEXTJOIN concatena le celle con il delimitatore fornito. Puoi anche specificare dove ignorare le celle vuote.

Per produrre un elenco delimitato da virgole di alcuni elementi nella colonna A, utilizzare questa formula:

=TEXTJOIN(“, “, TRUE, A2:A4)

2. Funzione SOSTITUIRE

La funzione di sostituzione consente di specificare la posizione iniziale e la lunghezza della stringa che si desidera scambiare nella destinazione.

Per sostituire i primi due caratteri di una stringa con "XX", utilizzare questa formula:

=TESTOUNISCI(A2, 1, 2, “XX”)

3. Funzione SOSTITUTIVA

La funzione di sostituzione sostituisce le occorrenze specificate di una stringa di testo all'interno di un'altra stringa di testo con una nuova stringa di testo.

Per sostituire le occorrenze della parola "Jeans" con "Leggings", utilizzare questa formula:

=SOSTITUTO(A3, “Jeans”, “Leggings”)

Ecco le formule in azione:

Formule di ricerca intermedie

Le funzioni di ricerca comuni come VLOOKUP e MATCH sono trattate nel cheat sheet per principianti.

Gli utenti intermedi dovrebbero familiarizzare con la funzione di ricerca SCEGLI:

• SCEGLI(num_indice, valore1, [valore2, …])

La funzione SCEGLI restituisce un valore da un elenco di valori basato su un numero di indice specificato.

Supponiamo di avere una colonna di taglie di t-shirt etichettate come 1, 2 o 3. Si desidera visualizzare rispettivamente una categoria di piccole, medie e grandi dimensioni nella stessa riga. Usa questa formula:

=SCEGLI(A1, “Piccolo”, “Medio”, “Grande”)

Formule per la gestione degli errori di Microsoft Excel

Il cheatsheet per principianti elenca i tipici messaggi di errore che vedrai quando lavori con Excel. Gli utenti intermedi dovrebbero essere in grado di utilizzare le formule per gestire gli errori con garbo.

1. IFERROR(valore, if_error)

2. IFNA(valore, valore_se_na)

1. Funzione IFERRORE

Gli errori di divisione per zero sono comuni, ma la visualizzazione predefinita di Excel di #DIV/0! non sarà chiaro a tutti gli utenti.

Utilizzare la funzione SE.ERRORE per sostituire l'errore standard con "Impossibile dividere per zero" con questa formula:

=SEERRORE(A1/B1, “Impossibile dividere per zero”)

Questa immagine mostra l'errore non elaborato nella terza riga e la funzione IFERROR che gestisce la quarta riga.

2. Funzione IFNA

La funzione IFNA in Excel viene utilizzata per rilevare e gestire gli errori #N/D.

Un'altra sezione di questo cheat sheet mostrava la funzione SCEGLI. Questa funzione restituirà un errore N/D se il numero di indice non è un numero intero.

Supponiamo che la taglia di una maglietta sia stata inserita come 1.5, con l'utente che pensa erroneamente che questo mostrerà una categoria tra piccola e media. Invece, mostrerà un errore #N/D .

Per fornire un errore più utile, utilizzare questa formula:

=SENA(SCEGLI(A2, “Basso”, “Medio”, “Alto”), “Indice non valido”)

Scorciatoie per formule e funzioni

Esistono diverse scorciatoie da tastiera che possono farti lavorare in modo più efficiente con il tuo foglio di lavoro.

• F2 : modifica la cella attiva e posiziona il punto di inserimento alla fine del contenuto della cella.

• F9 : Calcola e visualizza il risultato della parte selezionata di una formula.

• Ctrl + Maiusc + Invio : inserisci una formula di matrice.

• Maiusc + F3 : apre la finestra di dialogo Inserisci funzione.

• Esc : annulla l'inserimento di una formula e ripristina il contenuto della cella originale.

Alcune di queste scorciatoie di Excel potrebbero non essere disponibili in tutte le lingue o layout di tastiera.

Formattazione intermedia delle celle

Nell'analisi finanziaria, la corretta formattazione delle celle contenenti numeri, date e valute è fondamentale. Per formattare le celle, attenersi alla seguente procedura:

1. Seleziona le celle che desideri formattare.

2. Fai clic con il pulsante destro del mouse sulle celle selezionate e scegli "Formato celle".

3. Selezionare la categoria appropriata (ad es. Numero, Valuta, Data) e applicare il formato desiderato.

Alcuni tipi di formattazione comuni da considerare in ambito finanziario includono:

• Valuta : $ 1.234,56

• Percentuale : 12,34%

• Contabilità : ($ 1.234,00)

• Data : 10 maggio 2023

Pensieri finali

Man mano che diventi più a tuo agio con le varie formule e funzioni evidenziate in questo cheat sheet , ti ritroverai meglio attrezzato per gestire attività e progetti sempre più complessi.

La pratica è la chiave per padroneggiare qualsiasi abilità, quindi non aver paura di sperimentare queste formule nel tuo lavoro quotidiano. Porteranno la tua esperienza dal livello principiante al livello intermedio.

Questo è solo un assaggio di ciò che le funzioni intermedie di Excel possono fare per semplificare il tuo lavoro, ottimizzare le tue analisi e aiutarti a scoprire informazioni dai tuoi dati.

La vera magia accade quando inizi a combinare le funzioni nel nostro cheat sheet e le personalizzi in base alle tue esigenze specifiche. Quindi, non fermarti qui. Continua a esplorare, continua a sperimentare e scoprirai che Excel non è solo uno strumento, è un punto di svolta!

Hai voglia di portare le cose al livello successivo? Controlla il nostro Cheat Sheet delle formule avanzate di Excel.