Calcolare la competenza annuale con Excel

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.

competenziato

Lascia un commento

Il tuo indirizzo email non sarà pubblicato. I campi obbligatori sono contrassegnati *