Qualche giorno fa Gaspar ha proposto un metodo per calcolare con Excel il numero di mesi divisi per anno solare tra due date arbitrarie. Il problema da risolvere è ripartire le quote annuali di iscrizione ad un’associazione professionale. Cito dal post di Gaspar Excel: anni e mesi tra due date:
Paola lavora per una associazione professionale e si occupa delle iscrizioni, che iniziano in periodi diversi e valgono per periodi diversi. Anche l’ammontare della quota varia a seconda del tipo di socio.
Paola ha bisogno di imputare a ogni anno la parte di quota di iscrizione proporzionale ai mesi di validità di quell’anno. I dati rilevanti che estrae dal gestionale in un foglio di Excel sono la data di inizio, la data di fine e l’ammontare della quota:
Ti faccio un esempio: se un socio in data 01/06/2012 ha pagato $240 per 24 mesi, Paola vuole imputare $70 nel 2012, $120 nel 2013 e $50 nel 2014.
Il metodo di Gaspar, cui avevo già suggerito di tenere conto di intervalli di tempo maggiori di 24 mesi, si basa su individuare dove si trova la “testa”, il “corpo” e la “coda” della sequenza di mesi rispetto alla scansione degli anni solari. Gaspar perciò individua quanti mesi ci sono nel primo anno, quanti nell’ultimo e quanti negli anni intermedi. La somma fornisce il numero totale di mesi e le tre categorie forniscono una classificazione in cui andare a piazzare le colonne di ogni anno, verificando per ogni cella annuale a colpi di funzione logica SE, l’appartenenza al primo anno o all’ultimo anno (assegnando quindi il relativo numero di mesi), ad un anno intermedio (assegnandogli di default 12 mesi) o lasciata vuota (l’ultimo caso della catena di SE). Ogni casella viene infine moltiplicata per la quota mensile per socio.
Il metodo presuppone due assunzioni forti:
- Che si conosca in anticipo l’anno minimo e massimo di tutta la serie di dati, per preparare correttamente le colonne
- Che non vi siano due date all’interno dello stesso anno, ad esempio un abbonamento di soli tre mesi:
In questo caso l’Excel calcola per il primo anno il numero di mesi fra la data iniziale e il mese di dicembre, sbagliando i calcoli (riga 11 dei dati di Gaspar che ho alterato per fare la prova).
Amo poco l’uso di catene IF-ELSIF-ELSE e avevo l’impressione che il metodo si potesse risolvere minimizzando l’uso delle funzioni condizionali e usando un numero minore di informazioni. Inoltre volevo risolvere anche il caso di due date nello stesso anno. La soluzione si è basata su questi tre punti:
- Individuare correttamente i mesi del solo primo anno
- Calcolare direttamente i mesi totali
- “Spalmare” i mesi totali su tutti gli anni successivi
Per fare questo occorre fissare i dati del problema che troveranno posto nelle prime quattro colonne dalla E alla H.
Per prima cosa ho calcolato in colonna E il numero di mesi totale fra le due date (DATA.DIFF) e in colonna F la quota mensile per socio, per non doverla ricalcolare ogni volta nelle celle annuali:
A questo punto ho inserito in colonna G un controllo per verificare se la data di inizio e fine appartengono allo stesso anno, inserendo 1 in caso positivo e 0 in caso contrario:
=SE(ANNO($B3)=ANNO($C3);1;0)
Infine, in colonna H, calcoliamo quanti mesi dobbiamo imputare al primo anno, andando a verificare nella colonna precedente se la data di inizio e fine cadono nello stesso anno solare:
=SE($G3;MESE($C3)-MESE($B3)+1;13-MESE($B3))
Da qui in avanti comincia il calcolo vero e proprio. Ho inserito anche colonne di anni esterni all’intervallo dei dati di Gaspar per verificare che il metodo restituisse zero mesi negli anni fuori dall’intervallo consentito.
Nel mio metodo la prima colonna a sinistra nella serie degli anni è speciale: funge da “seme” iniziale su cui le successive fanno un calcolo per accumulazione. In colonna I controlliamo quindi se il valore dell’intestazione è uguale all’anno iniziale, se lo è inseriamo i mesi del primo anno, altrimenti zero. Questo controllo sarà comune a tutte le celle successive:
=SE((I$2-ANNO($B3)=0);$H3;0)
Negli anni successivi eseguiamo due controlli:
- Se il valore dell’intestazione della colonna è uguale all’anno iniziale (come nel caso precedente), nel qual caso inseriamo il numero di mesi del primo anno
- Se la colonna è maggiore dell’anno iniziale, nel qual caso si tratta di un anno intermedio o finale. In questo caso inseriamo il numero di mesi di quell’anno calcolandolo come il valore minimo (MIN) fra 12 mesi (anno intermedio) e la differenza fra i mesi totali meno la somma dei mesi sottratti in tutte le colonne a sinistra. In formula:
=SE((J$2-ANNO($B3)=0);$H3;SE((J$2-ANNO($B3))>0;MIN(12;$E3-SOMMA($I3:I3));0))
Sembra più complicato a dirsi che a farsi, in realtà è semplice. Avendo noi fissato i mesi del primo anno e i mesi totali, ci accertiamo che una certa colonna sia diversa dal primo anno e andiamo a scriverci i mesi residui dal conto totale. Vediamo come esempio la prima riga dei dati di Gaspar. Si tratta di un abbonamento iniziato nel 2012 e finito nel 2015 per un totale di 36 mesi.
Nel 2012 abbiamo 7 mesi, che vengono copiati correttamente nella colonna J (la colonna I fallisce il controllo fra intestazione della colonna e anno e quindi riporta giustamente 0)
Nel 2013 rimangono 36-7 = 29 mesi, cioè più dei 12 mesi che formano un anno intermedio. La formula con la funzione MIN della colonna K riporta quindi 12, il minimo fra 12 e 29:
Nel 2014 i mesi restanti sono 36-7-12=17 mesi, ancora più grande di 12 quindi la colonna L riporta 12 mesi.
Nel 2015, ultimo mese di abbonamento, i mesi restanti sono solo 5, minore di 12, quindi la colonna M riporta il valore 5.
E le colonne successive? Come fanno a sapere di essere esterne all’intervallo degli anni? Facile: non lo sanno!
Nella colonna N come nella O e nella P, verificata la condizione che il relativo anno di intestazione (2016,2017,2018) sia maggiore dell’anno iniziale (come per tutte le colonne precedenti), la “magia” viene fatta ancora una volta dalla funzione MIN: Il calcolo dei mesi residui dà sempre 0: 36-7-12-12-5-0-0-0-0-… = 0 e il minimo fra 0 e 12 è sempre…0!
I vantaggi di questa formula sono molteplici:
- Non c’è la catena di IF-ELSIF-ELSE (migliore leggibilità )
- Non devo controllare se sono sull’ultimo anno né se sono su anni intermedi con una doppia condizione, basta solo che non sia sul primo anno, il resto viene per differenza
- Usando correttamente gli indici di colonna iniziali con il segno $ la sommatoria può essere spalmata automaticamente facendo copia e incolla delle formule sulle celle successive (utile in caso di macro).
Per concludere il calcolo bisognerebbe aggiungere tante colonne quanti sono gli anni in cui fare la moltiplicazione effettiva della quota mensile. Non l’ho fatto perché ho preferito concentrarmi sul calcolo teorico dei mesi. Personalmente preferirei fare un secondo foglio di calcolo che, usando i riferimenti alle colonne annuali del primo, si occupi solo della moltiplicazione: in questo modo non dobbiamo nascondere le colonne dei mesi e possiamo consultarle facilmente in caso di dubbi.
Bonus: ecco di nuovo la riga 11 con l’abbonamento “corto” nello stesso anno correttamente calcolato.
Trivia: il foglio di calcolo è stato fatto il giorno di Pasqua con Numbers sull’iPad (da cui le screenshot colorate) e poi esportato in Excel (da cui ho tratto le formule testuali). In allegato i due file:
P.S.: per i solutori più abili: anche il mio metodo usa un terzo IF ma è ben nascosto!