Excel: anni e mesi tra due date – metodo alternativo

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:

situazione_iniziale

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:

  1. Che si conosca in anticipo l’anno minimo e massimo di tutta la serie di dati, per preparare correttamente le colonne
  2. Che non vi siano due date all’interno dello stesso anno, ad esempio un abbonamento di soli tre mesi:

Date nello stesso anno

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:

  1. Individuare correttamente i mesi del solo primo anno
  2. Calcolare direttamente i mesi totali
  3. “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:

mesi totali

Quota mensile

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)

Stesso anno

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))

Mesi primo anno

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)

Mesi 2011

Negli anni successivi eseguiamo due controlli:

  1. 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
  2. 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)

Mesi 2012

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:

Mesi 2013

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.

Stesso anno - esempio

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!

La musica si impara da piccoli

1. Si parla spesso delle “scuole medie coi flautini dolci”, come se ci fosse una delega totale all’educazione dell’orecchio alla scuola dell’obbligo. Ma quando mai. La musica si impara, come la parola, come la pipì sul vasino, come la forchettina e il bicchiere con due manine, da mamma e papà.

(via La Flauta – L’educazione alla buona musica (come provare a cambiare il mondo))

La Flauta ha scritto una bellissima lettera al Ministro Bray e questo gli risponde nei commenti trascinandosi anche Gino Paoli come presidente SIAE.

Non contenta di aver conquistato i siti di news musicali e la home page del sito SIAE, si è montata la testa ed è passata alla parte propositiva.

E anche stavolta lo fa da applauso a scena aperta.

Non foss’altro perché ieri sera Piero Angela ci ha presentato un servizio sui neuroscienziati che hanno dimostrato che il cervello di bambini educati precocemente alla musica funziona meglio di quello degli altri bambini.

Non foss’altro perché la musica, vissuta e capita – per non dire studiata – è un piacere della vita che se la gioca ai punti col sesso e il cibo.

Non foss’altro perché, tra le righe, la Flauta si immagina una generazione che si appassiona alla musica e non ai testi delle canzoni, confondendo la litania cacofonica di voci stonate che azzeccano le parole di tutte le strofe di De André o Guccini (erre moscia compresa) in fondo al pullman della gita scolastica con l’amore per la musica di quelle stesse canzoni.

(ho citato solo i Grandi, sorvoliamo sul sapere – e trovare belle – tutte le strofe di Ramazzotti e co.)

Del resto lo aveva(mo) già scritto che la musica viene prima.

La musica è una gran figata e il godimento si nasconde nei passaggi, negli accordi, nell’insieme e nell’assolo ben dosati, nell’armonia e in un sacco di altri anfratti. Talvolta anche nella melodia, dai.

Se la musica italiana fosse cibo, mangereste sempre pasta al pomodoro e basilico?

Conoscendo davvero la musica si può veramente cambiare il mondo cambiando noi stessi. Non è un’esagerazione.

L’internet adolescente

Gaspar è diventato verboso da 11 anni e vede la rete ancora nella sua prima infanzia:

Ci potremo dire nella prima adolescenza quando

  • la rete sarà veloce, simmetrica e pervasiva;
  • tutti gli oggetti connessi saranno server indirizzabili.

Non vedo l’ora.

Tra altri 11 anni ci troveremo a festeggiare il compleanno del Maestro in chat tra Apple iGlasses e Google Googles:

– Auguri, Gaspar! Con cosa brindi?
– Con niente, il frigo è unresponsive
– Perché? CPU piena?
– Disco pieno, non ha ruotato i log.
– non ti ha mandato la mail?
– è finita nello spam.
– ti cucini qualcosa?
– il microonde è andato…
– Bruciato? Formattato?
– L’ho beccato mentre mi stava ordinando certe pastigliette…
– LOL
– Piuttosto, cos’è questa musica, Federico? Ti facevo di gusti più alti…
– No, niente, firewall dimenticato sull’ampli…
– Eh, eh…
– L’hanno attaccato degli hacker di Cesena…

Sento la nostalgia d’un passatooooo….

Buon compleanno, Gaspar!

Giochi di ruolo

Da giovani il ruolo è importante (e si capisce che i vecchi siano così restii a concederlo), perché è il punto d’appoggio su cui fare leva per imporsi sugli altri. Il ruolo agevola la costruzione di una identità sociale, ma con l’età ci si può anche abituare a non averlo e farne a meno, trovando il lato positivo della faccenda: più tempo a disposizione per se stessi e meno recite “a soggetto” davanti a gente davvero poco interessante, che cerca solo conferme di sè.

(Via Il ruolo « …I’ve got a project!.)

Lorenzo riflette sulle acrobazie delle (auto)definizioni sui biglietti da visita per finire a parlare di scuola e precariato.

In ricordo di Nino Loperfido

nino.jpg

Oggi pomeriggio dalle 15 alle 18, nella sala Zodiaco della Provincia di Bologna, si terrà il convegno Le pratiche del Sogno – In ricordo di Nino Loperfido – Bambini e bambine di nuova generazione a Bologna.

Dopo il documentario realizzato da Giuliano e Giacomo, un altra occasione per ricordare un pilastro delle nostre amicizie.

Intervengono: Anna Del Mugnaio, Graziella Giovannini, Mariagrazia Contini, Maria Teresa Tagliaventi, Giancarlo Rigon.

Io ci sarò, almeno per la prima parte.

Scarica l’invito in pdf

Amici a Bologna

Oggi, tra poche ore, sarò a Bologna e lì di amici dubito ce ne saranno (forse qualcuno, a sorpresa), anche perché io Bologna non l’ho mai vista e non conosco nessuno.

Due cuori e una culla, tra Milano e Bologna | Due cuori e…

Ieri sera, complice un colloquio scolastico a metà pomeriggio che ha confinato i bimbi dai nonni, abbiamo approfittato della libera uscita per contraddire Ilaria Mazzarotta e palesarci alla presentazione del libro Due cuori e una culla.

Oltre a riabbracciare Lia Celi e prima perderci in lunghe chiacchiere abbiamo ricevuto l’onoreficienza speciale:

Mamma, questi sono miei amici blogfestari

Ci sono amici in ogni luogo a qualche grado di separazione da te nell’era dei socialcosi. Il Centro di Bologna ha riunito chi non lo conosceva per niente, chi ci ha abitato e poi ha cambiato città e chi ci ha abitato a lungo e ora vive oltre il confine dei viali. Praticamente una passeggiata di ri-scoperta.

Il libro arriverà in ebook, mezzo su cui è impossibile chiedere una dedica. L’impatto con la Feltrinelli, una volta meta abituale settimanale ha inondato di carta tutti i miei sensi. Non ho resistito e ho dato una sniffatina al libro di Ilaria cartaceo. Me la tengo per quando lo sfoglierò sull’iPad.

P.S.: è appena uscito il video della presentazione.

Sui tetti con le parole di Sergio

Fargli un monumento » Sergio Maistrello: Ma io mi ostino a pensare che questa protesta, geniale e coinvolgente, degli studenti che si prendono i luoghi simbolo d’Italia non sia tanto contro questa riforma e contro questo governo. Quelli sono il pretesto, la notizia buona per i titoli del tg. La scintilla che innesca. Mi piace invece pensare che stiano protestando per lo sfascio, per l’arroganza, per il cinismo, per la miopia che gli ultimi venti o trent’anni di storia italiana, con governi di ogni colore, hanno riservato loro. Per lo stato in cui è ridotto l’intero sistema della formazione nazionale, per la precarietà degli edifici, per la prostrazione degli insegnanti, per la tristezza delle ultime riserve di potere, per il tedio dell’ennesima riforma che sai già destinata a impoverire ancora. Per lo spettacolo disonorevole di questi anni. Mi piace pensare che questi esuberanti giovanotti abbiano trovato il coraggio, la motivazione e l’intuizione per fare quello che noi ex-studenti sfuggiti per un soffio al collasso, noi genitori che portiamo a scuola la carta igenica per i nostri figli, noi adulti tramortiti al pensiero dell’eroismo quotidiano che ci sarebbe richiesto, non siamo stati capaci di fare: ritrovare dignità, alzare la voce, riprenderci – almeno simbolicamente – ciò che ci spetta. Per questo trovo quei monumenti occupati un’immagine potente come non se ne vedevano da anni. Per questo auguro a tutti noi che non si stanchino o non siano distratti troppo presto. E per questo, come altri in queste ore, penso che su quei monumenti dovremmo esserci anche noi.

Ho già condiviso le parole di Sergio stamattina ma rileggendole sul post di Massimo ho scelto anch’io di ribloggarle in toto.

Perché le parole sono importanti e nell’essere lette è la loro forza.

Perché la forma è sostanza e il periodare di Sergio in questo post è la perfezione.

Perché quando qualcuno estrae dalla tua testa il tuo stesso pensiero da giorni incapace di uscire gli devi come minimo un caffè, meglio un abbraccio, per ora un post.

La 42esima estate ai tempi di facebook

Confesso che ultimamente ho cliccato con poco entusiasmo sui compleanni che Facebook roboticamente mi segnala. Non tanto perché mi importasse poco della persona cui avrei fatto gli auguri ma perché mi immedesimavo in lei immaginandomi di non gradire auguri sollecitati da un reminder su un social network.

Consideravo come parte del messaggio di auguri la fatica di ricordarsi della giornata e di ritagliare un pensiero speciale per il festeggiato.

Se l’augurio è semiautomatico deve avere lo stesso valore di una newsletter (che peraltro da Vodafone mi è arrivata).

Avevo sottovalutato i 500 milioni di utenti che abitano Facebook o meglio, avevo sottovalutato il radicamento nelle possibili forme comunicative di questo social network anche da parte di chi non è così addentro la rete e la tecnologia come noi friendfeed-maniaci.

Se è vero che quel clic è facile da fare, ci sono stati 42 e più amici che si sono sbattuti per farlo e aggiungere un messaggio, lungo o corto. Sia su FriendFeed che su Facebook che su Twitter.

complebolso_42.png

Sapete una cosa? Non siete stati affatto robotici e mi avete fatto un gran piacere. Così come mi hanno fatto piacere sms e telefonate che hanno completato la multicanalità del complebolso di ieri.

Mi si è confermata la rete come luogo concreto e vitale di risonanza di rapporti e legami e come rete delle possibilità sempre aperte.

Roba che se non stai attento ti arrestano l’Ego per ubriachezza molesta.

Credo che non avrò più dubbi nel cliccare sui compleanni futuri e imminenti.

Grazie a tutti. Grazie, rete.

P.S.: la giornata nel mondo reale si è conclusa con un’altra sorpresa: una bolso-sopportante che mi fa trovare 42 candeline profumate e due aiutanti per spegnerle. Grazie a voi tre. Vi amo.

Due aiutanti per 42 candeline

Le Trame di Spinoza

Non ho mai riso degli aforismi di Spinoza perché sono serissimi.

Ho letto tutto il libro dei fincipt nella più completa impassibilità.

Ho fatto finta di non conoscere quei due quando sono emersi dalla torta come sorpresa del mio quarantesimo compleanno.

Mi deve essere scappato un clic sull’evento facebook di Spinoza alla Libreria Trame per oggi alle 18, perché altrimenti non ci sarei mai andato.

Non andateci. Non fanno ridere. Ci vado io per voi. Staremo fra noi, con la Nico le altre tramesse.

Noi che siamo serissimi. E che ci divertiremo un casino.

spinoza-trame.jpg