Dato un valore e un intervallo temporale è possibile calcolare le quote annuali di competenza con un semplice foglio di Excel.
Una applicazione tipica di questo calcolo è quella di definire come si ripartisce (competenza) sui singoli anni una spesa o un consumo di energia.
Ad esempio, come si ripartisce annualmente una spesa di 500€ nel periodo tra il 16/05/2015 e il 22/11/2018?
La risposta è:
- 89,36€ nel 2015
- 142,19€ nel 2016
- 141,80€ nel 2017
- 126,65€ nel 2018
Da notare che, nonostante gli anni siano entrambe completi, la quota dell’anno 2016 è di poco superiore a quella dell’anno 2017 perché il 2016 è bisestile e quindi conta un giorno in più rispetto al 2017.
Per effettuare automaticamente il calcolo con un foglio di Excel inseriamo innanzitutto:
- la data di inizio del periodo di competenza nella colonna A che chiameremo START
- la data di fine del periodo di competenza nella colonna B che chiameremo END
- l’importo da competenziare nella colonna C che chiameremo VALUE
Poi, a partire dalla colonna D inseriamo nella prima riga gli anni di interesse su cui competenziare l’importo, nell’esempio 2012, 2013, 2014, …, 2022.
In base alla versione di Excel in uso inseriamo nella cella D2 una delle due formule
- se la versione di Excel in uso è 2016 o successive inseriamo questa formula nella cella D2
=IFS(AND(YEAR($A2)=D$1;YEAR($B2)=D$1);(1+$B2-$A2);YEAR($A2)>D$1;0;YEAR($B2)<D$1;0;YEAR($A2)=D$1;(1+DATE(D$1;12;31)-$A2);YEAR($B2)=D$1;(1+$B2-DATE(YEAR($B2);1;1));YEAR($B2)>D$1;(1+DATE(D$1;12;31)-DATE(D$1;1;1)))/($B2-$A2+1)*$C2
- nelle versioni precedenti di Excel non esiste la funzione IFS , occorre quindi usare degli IF concatenati inserendo questa formula nella cella D2
=IF(AND(YEAR($A2)=D$1;YEAR($B2)=D$1);(1+$B2-$A2);IF(YEAR($A2)>D$1;0;IF(YEAR($B2)<D$1;0;IF(YEAR($A2)=D$1;(1+DATE(D$1;12;31)-$A2);IF(YEAR($B2)=D$1;(1+$B2-DATE(YEAR($B2);1;1));IF(YEAR($B2)>D$1;(1+DATE(D$1;12;31)-DATE(D$1;1;1))))))))/($B2-$A2+1)*$C2
Queste formule sono valide per la versione in lingua inglese di Excel, per adattarle a quella italiana sostituire
- IF con SE
- AND con E
- YEAR con ANNO
- DATE con DATA
Per l’elenco completo di corrispondenza tra le formule di Excel nelle varie versioni linguistiche del programma dai un’occhiata all’articolo Nomi delle funzioni Excel in Italiano, Inglese e Francese
Copiamo quindi in orizzontale la formula contenuta nella cella D2 trascinandola in orizzontale fino alla colonna dell’ultimo anno.
Copiamo adesso in verticale le formule così ottenute nel passaggio precedente trascinandole in verticale fino all’ultima riga.
Il risultato sarà come quello in figura, in cui per ogni anno viene calcolata la rispettiva quota di competenza dell’importo in base alla data di inizio e fine del periodo.
