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!

Pappanoinweb 2014 stasera 4 aprile live streaming Brahms

Il mio radicato amore per la musica classica nonché il mio passato di studente di conservatorio mi hanno fatto accettare con piacere l’invito di Telecom Italia a ospitare lo streaming dei concerti di Pappanoinweb 2014 anche qui su questo piccolo blog.

L’iniziativa va avanti da diversi anni, i video delle edizioni precedenti sono disponibili nella pagina eventi, ed è pronto l’hashtag per la discussione live su twitter #Pappanoinweb.

Il concerto di stasera venerdì 4 aprile inizierà alle 20:30, in programma:

Alexander Lonquich pianista

Solisti di Santa Cecilia: Alessandro Carbonare al clarinetto, Alessio Allegrini al corno, Carlo Maria Parazzoli al violino e Gabriele Geminiani al violoncello.

Johannes Brahms

  • Trio op. 40 per violino, corno e pianoforte;
  • Klavierstücke op. 118;
  • Trio op. 114 per clarinetto, violoncello e pianoforte

Incidentalmente mi fa molto piacere ascoltare dal vivo Alessio Allegrini perché la sua magnifica esecuzione dei concerti per corno di Mozart ha superato quella di Barry Tuckwell che finora era la mia preferita.

Per comodità di consultazione ho predisposto una pagina che rimarrà nel menu principale per tutta la durata dell’evento: Pappanoinweb 2014.

Buon ascolto.

Quella foto per Freak

Beatles by Norman Parkinson

30 anni fa a Bologna c’era un negozio di fotografia in via Volturno. Un gran bel piccolo negozio, figlio di un altro grande negozio che esiste tuttora, che aveva l’unica sfortuna di trovarsi sulla traiettoria casa-scuola di un giovine bolso di sedici anni preso tra due sue grandi passioni (manie, per meglio dire): i Beatles e la fotografia.

Erano gli anni 80, i più giovani faticheranno a capire che si trattava di due passioni ardue da perseguire: quella musicale doveva limitarsi a ellepi e cassette da comprare con la propria paghetta, qualche speciale televisivo e un paio di libri, la seconda era una scalata verticale economica, un vortice risucchia-risparmi alimentato da reflex, pellicole, sviluppo e stampa, ingrandimenti, manuali e la lettura religiosa dei 120 fascicoli settimanali dell’enciclopedia Obiettivo Foto.

Niente Google immagini, niente iPhone, niente iTunes, niente youtube, niente di niente.

Rimediare una foto in più dei propri idoli voleva dire cercare un poster da Ricordi in via Ugo Bassi o spulciare vecchie riviste. Stampare una foto voleva dire fare i negativi, i provini, ordinare l’ingrandimento e aspettare.

Un pomeriggio come tanti passo dal mio negozio di fotografia preferito e rimango ipnotizzato da un cartoncino pubblicitario della Ilford: sfondo nero, i quattro volti dei Beatles e nient’altro. Una foto stupenda, mai vista prima da me, era appoggiata dietro alcuni obiettivi da reflex di seconda mano.

Cerco tutto il coraggio necessario a superare la mia timidezza adolescenziale e chiedo tremante se posso avere in prestito la foto per… rifotografarla. In un colpo solo posso far convergere due manie: riprodurre una foto dei Beatles e mettere in pratica le tecniche di ripresa in luce artificiale studiate in uno dei 120 fascicoli settimanali.

Manuela acconsente, si arrampica su uno sgabello, apre la vetrina e si mette a spostare obiettivi e accesori. Il compito non è agevole. Il cliente ha sempre ragione, specie quello che passa di lì – impietosamente – tutti i pomeriggi, ma alla fine sbuffa:

- Giacanelli, lo faccio solo perché sei tu.

Arrosisco, la ringrazio e chiedo una Ilford FP4 per la riproduzione.

A casa allestisco il mio mini studio fotografico, scansando libri e giochi dal tappeto e appoggio la preziosa reliquia contro l’armadio bianco, lo sfondo più neutro che potessi rimediare. Una lampada da tavolo e un’abat-jour piazzate ai lati mi fanno da illuminazione professionale. Prendo un cavalletto da tavolo e ci piazzo la mia Yashica FX-D Quartz.

Credo di aver fatto almeno una ventina di scatti, forse ho adoperato tutti e 36 i fotogrammi per una foto immobile, variando l’esposizione in più o in meno come da manuale. Uno scatto finale da lontano come ricordo del set-up. Il giorno dopo porto il rullino a sviluppare al negozio. Riporto il prezioso reperto che torna al suo posto.

- I provini sanno pronti venerdì.

- Grazie ancora di avermelo prestato.

Venerdi ripasso, mi guardo i provini di 20 foto identiche per investire la paghetta nell’unico ingrandimento 20×30 che potevo permettermi. Elisa mi dice:

- Senti, ti dispiace se ne faccio fare una in più? Me l’ha chiesta un altro cliente.

Avrei fatto qualsiasi cosa per sdebitarmi.

- Figuratevi… Certo, fate pure!

Passano i giorni di rito in attesa dell’ingrandimento. Quando vado a ritirarlo nel piccolo negozio ci sono quattro persone che nascondono il bancone. Elisa mi vede, mi indica e uno di loro si volta:

- Ciao, grazie per questa foto, sai: siamo ammiratori sfegataaaati dei Beatles!

Ciao Freak, 30 anni dopo quella foto è ancora per te.

Incantatori

Chi ha esaminato a mente fredda quelle promesse ha capito che non sono affatto a portata di mano, ma una buona parte degli italiani ha sempre creduto che i miracoli si fanno, la bacchetta magica esiste e anche l’asino che vola c’è da qualche parte. Se così non fosse, non ci sarebbe un venti per cento di elettori che vota ancora per Silvio. Silvio c’è e se non ha fatto miracoli è perché finora gliel’hanno impedito i suoi nemici toghe rosse e comunisti.

Meno male che Silvio c’è e dunque anche meno male che c’è Renzi. Si somigliano? Sì, si somigliano e anche molto.

***

La vera — e formidabile — bravura di Silvio è sempre stata quella d’incantare la gente, ma è la stessa bravura di Matteo che sa incantare la gente come Silvio e anche di più ora che Silvio è vecchio e fisicamente un po’ provato.

Matteo è un Silvio giovane dal punto di vista dell’incantamento e quindi più efficace.

Eugenio Scalfari – I Campi Elisi di Silvio l’Ispanico

L’ultima notifica di Google Notifier

google-notifier-dead.png

Erano un paio di giorni che stavo pensando di disistallare Google Notifier perché ormai arrivano troppe mail e la notifica tempestiva non è più necessaria o si trovano valide alternative.

Sul computer di lavoro l’ho già spento. Stavo guardando l’icona del Mac di casa… e Google Notifier mi ha notificato l’arrivo della sua ultima mail.

So long, Google Notifier.

Studiare Assurbanipal nel 2013

Invece mia figlia studia Assurbanipal nel 2013; e usa un libro di carta. Punto. Non arriva da me con una raccolta su Pinterest di dieci immagini della Mesopotamia; non ha preparato con i suoi compagni lo storify multimediale della lezione. Non colloca Ninive su Google Maps, non stanno ricostruendo le mappe del 1230 avanti Cristo, cercando di collocare anche il resto del mondo: dove erano gli antenati dei suoi compagni cinesi, senegalesi, peruviani?
Io ascolto mia figlia declamare la lezione, che le frutterà l’ennesimo nove; la guardo sorridermi con la sconfinata bellezza dei suoi nove anni, felice di aver restituito Assurbanipal alla storia e di poter passare ad altro. Ai suoi giochi sul tablet, alla sua maschera da sci hi-tech, al cartone Pixar full HD.

Via Assurbanipal vive e combatte con noi

Lo splendido racconto di Kisbo, da leggere tutto specialmente nel finale, rimarrebbe solo un aneddoto sull’arretratezza della scuola italiana se non avessi anch’io un figlio coetaneo che studia le stesse cose.

Certo, la distanza fra lo studio sulla carta e quello sulla rete è abissale. La scuola sembra ancorata a ritmi e riti di un altro millennio. Tuttavia non me la sentirei, dovessi mai avere la bacchetta magica, di spostare la leva della scuola su avanti tutta verso il digitale: l’esperienza di lavorare sulla lentezza, la resistenza e lo stimolo immaginativo della carta non ha eguali. Fare un disegno, sbagliarlo e doverlo cancellare con la gomma è una fase propedeutica e necessaria allo sfogare la creatività su Paper.

È una mia fissa da vecchio brontolone: prima si impara a nuotare al naturale e poi si mettono le pinne. Se impari ad agire “potenziato” la volta che devi agire al naturale sei perso.

Ciò detto quell’abisso fra libro e multimedialità iperconnessa andrebbe quantomeno ridotto avvicinano i due estremi. Facciamo faticare i nostri figli su libri e quaderni ma poi diamogli uno straccio di filmato, di modello 3D, di galleria di immagini, di approfondimento su wikipedia.

Diamo loro la naturalezza di passare dalla carta al digitale. In entrambe le direzioni.

Perché l’informazione migliora quando

Perché l’informazione migliora quando chi la fa viene sottoposto ogni giorno a un check – pure feroce – su quello che scrive, che fa o che dice: ma concreto, fattuale, puntuale, onesto, autentico. Con una contrapposizione di argomenti, di visioni, di idee, di fatti. Con l’onestà intellettuale. Con la trasparenza. Possibilmente senza riferimenti ai difetti fisici dell’interlocutore. O alla sua appartenenza a una categoria – quale che essa sia. Facendo infine propria, se possibile, anche una ecologia del linguaggio che ormai mi pare irrimandabile e che ha come unica alternativa la barbarie e i forconi.

(Via Oppo, Grillo, noi giornalisti)

Passare oltre, andare alla sintesi fra tesi (il giornalista intoccabile) e antitesi (il giornalista casta) a colpi di factchecking, secondo Alessandro Gilioli che la dice molto meglio di me.

Seminare la civiltà

Spiego: polis, cittadinanza, classe. Sono tre contenitori millenari (la polis: Atene), rivoluzionari (la cittadinanza: Francia 1789), socialisti (le classi, Karl Marx, fine ’800). Tutti e tre implicano un’assunzione di responsabilità, l’ultimo attiene anche alla difesa degli umili. Il piccolo branco che ha spaccato i denti al cittadino solidario, li ha violati tutti. Ha irriso i diritti di ogni bolognese — quei muri sono, appunto, di ognuno: sono nostri — ha deriso il coraggioso moto di ribellione dell’oste indignato (lui sì rivoluzionario, a non girare la faccia dall’altra parte) e ha preso a colpi di nocche sul viso il diritto di tutti a vivere in un luogo minimamente decente.

Luca Bottura – Nettuno d’oro all’oste rivoluzionario

Quando un pezzo ti insegna qualcosa in più del semplice essere d’accordo bisogna superare la viralità del rewteet e del condividi, che suonano come un passivo “eh già”, “anch’io”, “ma guarda tu dove siamo finiti” e si accompagnano ad un’alzata di spalle virtuale o reale.

Ogni tanto bisogna ricordarsi che un blog è una cosa dove le idee vengono seminate e, a volte, germinano e rinascono.

Questo paragrafo di Luca mi è parso un buon seme.

Però voi il pezzo lo leggete tutto, vero?

Dissetarsi

20131028-190832.jpg

Strano come i ricordi si fissino sugli oggetti più impensati.

Ché certi aspetti banali del fine vita rimangono più impressi dei monitor e delle flebo.

Le bottigliette d’acqua col beccuccio, quelle lunghe e strette, incavate al centro, sono state le ultime con cui riusciva a bere.

Il biberon di un moribondo. Un neonato alla meno uno che succhia a fatica.

Poi si è dovuti passare all’acqua solida in gel, una (non) bevanda irreale per non soffocare quando ti abbandona anche il riflesso della deglutizione.

È servita solo per pochi giorni.

Un anno dopo succede che quelle bottigliette finiscono tra le cibarie di una bellissima escursione sull’Appennino.

E non è colpa di nessuno se il ricordo riaffiora silenzioso come un soldato dei corpi speciali che strisciando ti prende alla gola.

E stringe.

Gli effetti collaterali di un’ottima memoria visiva.

Senza la sua risata

Marco Zamperini è scomparso poche ore fa.

FunkyProf in B/W

Il FunkyProf, il dispensatore di Prestigio, il regalatore di idee, compagnia, calore, genialità, ironia e risate improvvisamente non c’è più.

Infarto.

Proprio all’ultimo State of the Net, nel suo keynote Internet everywhere, aveva messo nella slide introduttiva “ho avuto un infarto”.

Credetemi, da queste parti sappiamo cosa vuol dire.

Potevi non sapere chi fosse nel dettaglio ma non potevi fare a meno di incontrarlo in una qualunque adunata dei socialini, convegno serio o faceto, e farti contagiare dalla sua carica geniale e ironica.

Mi dispiace molto, moltissimo.

Un grande abbraccio alla moglie Paola e alle figlie Rebecca e Bianca.