Formule finanziarie di Excel

Una parte significativa del tempo per molti economisti o finanziatori viene impiegata a lavorare su un computer con l’applicazione per ufficio MS Excel. Questo programma ha un numero considerevole di funzioni destinate a creare report, analisi dei dati, piani di informazione, calcoli matematici e molto altro. La conoscenza delle più importanti formule di Excel e una combinazione di varie funzioni facilita notevolmente la soluzione di problemi pratici e riduce il tempo e lo sforzo spesi per esso.
Funzioni INDEX e MATCH
Funzione IF combinata con la funzione AND
Combinazione di funzioni SUM e OFFSET
Funzioni SUMIF e COUNTIF
Funzione MODE.SNGL

Funzioni INDEX e MATCH

Nei calcoli finanziari, viene spesso utilizzata una combinazione delle funzioni INDEX e MATCH. La loro azione congiunta è simile al funzionamento della funzione VLOOKUP, ma presenta molti vantaggi rispetto ad essa. Innanzitutto, considereremo queste funzioni separatamente.

Funzione INDEX

La funzione INDEX trova il valore di un elemento in un blocco dati in base al numero di riga e al numero di colonna specificati. In generale, la sua struttura sarà simile a questa:
INDEX (array; row_number; [column_number]), dove

  • array – questo è un blocco di celle in cui verrà eseguita la ricerca.
  • row_number – questo è un blocco di celle in cui verrà eseguita la ricerca.
  • column_number – questo è il numero seriale della colonna in cui si trova il valore desiderato. Se nella formula non viene specificato alcun numero di riga, questo parametro è obbligatorio.

Se vengono specificati un numero di riga e un numero di colonna, la funzione restituisce il valore della cella situata all’intersezione di questi dati.

Considera un esempio. C’è una tabella con un elenco di numeri di prodotto e le loro dimensioni. Per trovare la lunghezza del numero di prodotto 2, è necessario scrivere una formula del modulo =INDEX(B2:D6;3;2)
Function INDEX
In questo esempio, il risultato della funzione sarà il contenuto della cella all’intersezione della terza riga e della seconda colonna di questo array.

Sfortunatamente, molto spesso nei calcoli, il numero di riga o colonna è sconosciuto. Quindi la funzione MATCH viene in soccorso.

Funzione MATCH

L’azione della funzione MATCH è simile all’azione della funzione INDEX, ma MATCH non restituisce il valore della cella, ma la posizione della cella nell’intervallo specificato. In generale, la formula sarà simile a:

MATCH (search_value; array; [match_type]), dove

  • search_value – questo è quello che devi trovare. Qui può essere non solo un valore testuale o numerico, ma anche un valore logico, nonché un collegamento a una cella.
  • array – questo è l’intervallo di celle visualizzato.
  • match_type – questo è un parametro opzionale che può essere impostato su “1”, “0” o “-1”. Indica alla funzione quale valore trovare: esatto o approssimativo. Se i dati nell’array stanno diminuendo in ordine crescente, il parametro “1” indicherà che è necessario selezionare il valore massimo minore o uguale a quello desiderato. Il parametro “-1” è specificato per un array decrescente. In questo caso, la funzione selezionerà il valore minimo maggiore o uguale a quello desiderato. Il parametro “0” trova il primo valore uguale a quello desiderato. È questo valore del tipo di corrispondenza utilizzato nella combinazione delle funzioni INDEX e MATCH.

Per illustrare il funzionamento di questa funzione, prendere in considerazione un esempio. Nella tabella sopra, troviamo nella colonna “Product Number” quale sarà l’account per il numero prodotto 2. Per questo, scriviamo la formula:=MATCH(2;B2:B6;0)
Function MATCH
Perché devo conoscere la posizione di un elemento in una tabella? Si scopre che è molto conveniente usare questo valore come argomento per la funzione INDEX.

Combinazione di funzioni INDEX e MATCH

Se analizziamo entrambe le funzioni, diventa chiaro che la funzione INDEX cerca i valori di cella per numero di riga e numero di colonna. Allo stesso tempo, la funzione MATCH trova i numeri di riga e di colonna. Pertanto, se si utilizzano queste due funzioni nella stessa formula, MATCH troverà la posizione relativa del valore desiderato e la funzione INDEX utilizzerà questi valori e restituirà il contenuto delle celle calcolate.

Considera un esempio. Nella tabella sopra troviamo la larghezza del prodotto al numero 3. Per questo usiamo le seguenti formule: =INDEX($D$2:$D$6;MATCH(3;$B$2:$B$6;0))
Combination of INDEX and MATCH
Si consiglia di utilizzare collegamenti assoluti per queste formule in modo che gli intervalli di ricerca non vengano confusi durante la copia delle formule.

Perché è meglio usare una combinazione di INDEX e MATCH piuttosto che usare la funzione VLOOKUP? Innanzitutto, una formula basata su INDICE e MATCH consente di cercare i dati desiderati nell’intervallo specificato da sinistra a destra e da destra a sinistra, mentre quando si utilizza VLOOKUP, il valore desiderato dovrebbe essere sempre nella colonna più a sinistra dell’intervallo.

In secondo luogo, quando si utilizza la funzione VLOOKUP, non è possibile eliminare o aggiungere colonne alla tabella. Altrimenti, il risultato della formula sarà errato. Questo perché la sintassi di questa funzione implica la specifica dell’intero intervallo e il numero di colonna specifico da cui verranno presi i dati. Quando si utilizzano le funzioni INDICE e MATCH, è possibile eliminare o aggiungere tutte le colonne desiderate.

In terzo luogo, quando si utilizza una combinazione di funzioni INDICE e MATCH, non vi sono restrizioni sulla dimensione del valore di ricerca, mentre la funzione VLOOKUP limita il numero di caratteri del valore di ricerca a 255 caratteri.

In quarto luogo, quando si eseguono calcoli in grandi matrici di dati, l’uso delle funzioni INDICE e MATCH riduce significativamente il tempo necessario per la ricerca di valori rispetto alla funzione VLOOKUP. Questo perché la funzione VLOOKUP viene chiamata per ciascun valore dall’intervallo di dati specificato. Al contrario, una formula basata sulle funzioni INDICE e MATCH semplicemente esegue una ricerca e restituisce un risultato.

Funzione IF combinata con la funzione AND

La funzione booleana IF verifica se il contenuto delle celle soddisfa determinate condizioni. Se corrisponde, la funzione restituisce uno dei valori definiti dall’utente. In caso di discrepanza, restituisce un altro valore impostato. La sintassi della funzione è la seguente:

=IF(logical_expression; value_if_true; value_if_false), dove

  • logical_expression – questi sono i dati che devono essere controllati e le condizioni per la verifica. Per esempio А2>10.
  • value_if_true – questa è la voce che apparirà se il valore della cella soddisfa la condizione specificata.
  • value_if_false – questo è il record che apparirà se il valore della cella non soddisfa la condizione data.

Molti utenti che hanno eseguito calcoli finanziari complessi sanno quanto sia difficile comprendere le formule che utilizzano loop nidificati utilizzando l’istruzione IF. Si scopre che queste formule possono essere semplificate se si utilizza la funzione IF in combinazione con le funzioni AND/OR. La combinazione delle funzioni AND e IF funziona come segue. Se A = 1 e A = 2, la formula restituisce il valore B, altrimenti restituisce C. Per la funzione OR, la formula non funziona in questo modo. Se A = 1 o A = 2, la formula restituisce il valore B, altrimenti il valore C.

Considera un esempio. Creare una formula che controlla il contenuto della cella C2, uguale a 110. Se il numero è compreso nell’intervallo da 90 a 300, il risultato sarà 1, altrimenti 0. La formula sarà la seguente: =IF(AND(C2>=C4;C2<=C5);C7;C8)
IF function
Come si può vedere dalla figura, un valore pari a 1 verrà inserito nella cella con il risultato della funzione Il numero 110 è in realtà compreso tra 90 e 300.

Il valore totale può essere non solo un numero, ma anche un testo, ad esempio le parole “SÌ” o “No” o qualsiasi altra frase.

Combinazione di funzioni SUM e OFFSET

La stessa funzione OFFSET viene utilizzata raramente, ma la combinazione con altre funzioni può produrre risultati molto buoni. Ad esempio, l’uso combinato delle funzioni SUM e OFFSET consente di creare formule abbastanza complesse durante la creazione di una funzione dinamica che somma un numero variabile di celle. Per risolvere questo problema, viene utilizzata la funzione SUM e, anziché la cella finale, viene specificata la funzione OFFSET, ovvero la formula diventa dinamica.

La formula risultante sarà simile a questa:

= SUM (start_range:OFFSET(riferimento, numero di righe, numero di colonne)), dove

  • start_range – questo è il punto iniziale dell’intervallo di celle utilizzato dalla funzione SUM.
  • riferimento – questo è un riferimento alla cella utilizzata per calcolare l’endpoint dell’intervallo.
  • numero di righe – questo è il numero di righe utilizzate nel calcolo dell’offset della cella. Questo valore può essere positivo, negativo e uguale a zero.
  • numero di colonne – questo è il numero di colonne a destra o a sinistra del riferimento di cella dato. Utilizzato per il calcolo dell’offset. Se spostato a sinistra, questo valore è negativo. Se spostato a destra, il valore è positivo. Se i dati calcolati si trovano nella stessa colonna, questo parametro è zero.

Considera un esempio. C’è una tabella con i numeri di serie dei giorni del mese e le entrate ricevute ogni giorno. Ogni giorno, le informazioni nella tabella vengono aggiornate aggiungendo una riga con le entrate ricevute al giorno. Componiamo la formula nella cella finale dopo il quarto giorno di saldi: =SUM(B2:OFFSET(B6;-1;0))
OFFSET functions
Per aggiungere informazioni sul quinto giorno di vendite, è necessario aggiungere una riga vuota dopo il quarto giorno e inserire le informazioni necessarie in esso. In questo caso, la formula prenderà la forma: =SUM(B2:OFFSET(B7;-1;0)), e il reddito totale aumenterà dell’importo del reddito ricevuto il quinto giorno.

Funzioni SUMIF e COUNTIF

Queste due funzioni sono molto spesso utilizzate nei calcoli finanziari. SUMIF trova la somma in un determinato intervallo di celle in base a una determinata condizione. COUNTIF conta tutte le celle corrispondenti alla condizione specificata.

La funzione SUMIF ha la seguente struttura:

SUMIF(gamma; criterio; sum_range), dove

  • gamma – questo è un array di celle in cui verrà verificata la conformità con i criteri specificati.
  • criterio – questa è una condizione per selezionare le celle. Può essere un numero, un testo, un’espressione o un riferimento di cella.
  • sum_range – Questo è un parametro opzionale. Se non lo si specifica, la somma verrà eseguita tenendo conto dell’argomento “Gamma “.

L’azione di questa funzione è facile da capire con un esempio. C’è una tabella con un elenco di prodotti e la loro quantità. Devi trovare la quantità di “Product 1”. La formula per il calcolo sarà la seguente: =SUMIF(B2:B7;”Product 1″;C2:C7)
SUMIF functions
Nello stesso esempio, puoi calcolare il numero di prodotti escluso “Product 1″ usando questa formula : =SUMIF(B2:B7;”<>Product 1”;C2:C7)

La funzione COUNTIF ha la seguente struttura:

COUNTIF (gamma; criterio), dove

gamma e criterio sono simili a SUMIF.

Nello stesso esempio, contiamo il numero di righe “Product 1”. La formula per il calcolo sarà simile: =COUNTIF(B2:B7;”Product 1″).
COUNTIF functions
Utilizzando questa formula, è possibile calcolare il numero di righe che soddisfano determinate condizioni. Ad esempio, per calcolare in questa tabella il numero di righe con il numero di prodotti maggiore di 10, è necessario creare tale formula: =COUNTIF(C2:C7;”>10″).

Funzione MODE.SNGL

La funzione statistica MODE.SNGL e la sua versione obsoleta MODE trovano il valore più frequente nell’intervallo di dati (array) e restituiscono questo valore. Sintassi della funzione:
= MODE ((numero1; [numero2]; …), dove

  • numero1 – questo è un argomento richiesto, che è un numero, un riferimento a una cella numerica, una matrice o un intervallo di celle.
  • numero2 – argomento facoltativo. Possono esserci da 1 a 255 argomenti del genere.

Gli argomenti che non possono essere convertiti in numeri causano un errore di formula. Se non ci sono numeri identici nell’intervallo specificato, il risultato della funzione sarà il valore di errore #N/A.

Perché posso usare questa funzione nei calcoli finanziari? Ad esempio, al fine di scoprire quali prodotti vengono acquistati più spesso sulla base di dati di riepilogo. Il criterio di selezione può essere il prezzo del prodotto, dimensioni, volume, dimensioni e così via. A titolo di esempio, consideriamo una tabella che riflette le vendite di beni con la data di vendita, le dimensioni del prodotto e il suo prezzo. Per scoprire quali formati di prodotti vengono venduti più spesso, utilizziamo la formula: =MODE(C2:C6)
MODE function
La figura mostra che i prodotti con una dimensione di 36 sono i più richiesti. Pertanto, la funzione MODE determina l’evento che si verifica più frequentemente nella gamma di eventi.

Pertanto, l’uso delle funzioni finanziarie dell’applicazione Excel Office facilita notevolmente la conduzione di liquidazioni finanziarie e commerciali per crediti e prestiti, analisi finanziarie e di investimento e lavoro con i titoli. Un vantaggio significativo dell’applicazione è la possibilità di impostare una password per proteggere i documenti importanti. Ciò impedisce a terzi non autorizzati di accedere ai rapporti finanziari ed economici. Sfortunatamente, a volte le password vengono perse, ma il recuperare password  Excel è possibile utilizzando programmi speciali.