Categoria: Excel

Pubblicato il

Excel: anni e mesi tra due date

Aggiornamento:

Su prezioso suggerimento di Federico Giacanelli che qui pubblicamente ringrazio, ho modificato la formula originale in modo che funzioni correttamente anche per periodi superiori ai 24 mesi, e ho corretto il post di conseguenza.

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 primo passo è trovare il numero di mesi

  • del primo anno fino a fine anno se finisce in un anno diverso altrimenti dalla data di inizio alla data di fine:
    E2==SE(ANNO(C2)>ANNO(B2);13-MESE(B2);MESE(C2)-MESE(B2)+1)
  • dell’ultimo anno se maggiore del primo:
    F2=SE(ANNO($C2)>ANNO($B2);MESE($C2);0)
  • degli anni intermedi se maggiori del primo e minori dell’ultimo:
    =SE(ANNO($C2)>ANNO($B2);(ANNO(C2)-ANNO(B2)-1)*12;0)

Questa è la tabella risultante:

situazione_intrmedia_1

A questo punto bisogna aggiungere una colonna per ogni anno in cui calcolare la parte di quota da imputare, controllando se l’anno nella riga 1 è iniziale, finale o intermedio e moltiplicando la quota mensile (Amount / Total months) per il relativo numero di mesi. Se invece l’anno in questione è esterno all’intervallo, la cella viene lasciata vuota. La formula in I2, copiabile a destra con gli opportuni $, è la seguente:

=SE(ANNO($B2)=I$1;$D2/$H2*$E2;
SE(ANNO($C2)=I$1;$D2/$H2*$F2;
SE(E(I$1>ANNO($B2);I$1<ANNO($C2));$D2/$H2*12;””)))

Questa è la tabella risultante:

situazione_intrmedia_2

A questo punto è possibile nascondere le colonne da E a H, oppure eliminarle dopo aver copiato in I2 (con santa pazienza) le loro formule al posto dei relativi riferimenti di cella, ottenendo questa bella lenzuolata:

=SE(ANNO($B2)=E$1;$D2/(13-MESE($B2)+SE(ANNO($C2)>ANNO($B2);
MESE($C2);0)+SE(ANNO($C2)>ANNO($B2);
(ANNO($C2)-ANNO($B2)-1)*12;0))*(13-MESE($B2));
SE(ANNO($C2)=E$1;$D2/(13-MESE($B2)+SE(ANNO($C2)>ANNO($B2);
MESE($C2);0)+SE(ANNO($C2)>ANNO($B2);
(ANNO($C2)-ANNO($B2)-1)*12;0))*(SE(ANNO($C2)>ANNO($B2);
MESE($C2);0));SE(E(E$1>ANNO($B2);E$1<ANNO($C2));
$D2/(13-MESE($B2)+SE(ANNO($C2)>ANNO($B2);
MESE($C2);0)+SE(ANNO($C2)>ANNO($B2);
(ANNO($C2)-ANNO($B2)-1)*12;0))*12;””)))

E questa è la situazione finale:

situazione_finale

Se ti serve, il file è questo: Esempio – mesi e anni tra due date. Se hai qualche altra soluzione più elegante, come ad esempio quella di Federico, fatti sotto.

Pubblicato il

Excel per iPad: cosa c’è che non va

  • Niente macro
  • Niente connessione dati
  • Niente PowerPivot
  • Niente collegamenti ipertestuali
  • Niente filtro interattivo (slicer)
  • Visualizza ma non crea tabelle Pivot
  • Visualizza ma non crea formattazioni condizionali
  • Visualizza ma non crea convalide
  • visualizza ma non crea sparklines, smartart, wordart

Lo potrei vedere in mano agli agenti di commercio: buono per consultare, buono per compilare, no buono per fare. Volendo essere ottimisti, ci sono ampi margini di miglioramento.

Pubblicato il

Corso online: Making Sense of Data

Making Sense of Data

Ti segnalo questa buona iniziativa di Google: Making Sense of Data: si tratta di un corso online attivo dal 18 marzo al 4 aprile in cui vengono spiegati i fondamentali sulla raccolta e analisi di dati con le Fusion Tables.

Il corso è di livello base, si rivolge a chi non ha esperienza di analisi statistica e può essere completato in un pomeriggio. Superando il test finale si ottiene un certificate of completion.

Unica avvertenza: per qualche misterioso motivo, se ti iscrivi con un account di Google Apps for Domains le Fusion Tables non ti funzionano e non puoi completare gli esercizi. Bisogna iscriversi con un account @google.com, come mi hanno spiegato (dopo).

Tra parentesi, cosa aspettano a integrare Fusion con i fogli di calcolo di Google Drive? Ne verrebbe fuori una web app assai interessante.

Pubblicato il

Excel: analizzare una regata

Ovvero di come ho finalmente trovato un utilizzo interessante del grafico a bolle

Per noi velisti amatoriali senza pretese e senza budget, che in regata usiamo l’app gratuita di Android Sailing Tactician, si pone il problema dell’analisi dopo-gara. Ovviamente posso rivedere la regata dentro l’app, ma meglio ancora posso esportare il file .gpx e aprirlo dentro Google Earth.

Questa visualizzazione è molto interessante, ma mi nasconde un elemento chiave: la velocità. Per questo mi viene in aiuto Excel 2013. Il file, se vuoi vedere le formule, è questo (1,4MB). La procedura richiede una buona dimestichezza con l’accesso ai dati e con i grafici ma non è complicatissima:

  1. Cambia l’estensione al file di log da .gpx a .xml (è già nel formato giusto, ma se non cambi l’estensione Excel non lo riconosce).
  2. Apri il file .xml in Excel oppure usa la procedura di importazione dati esterni (ignora i messaggi di errore e procedi comunque).
  3. Elimina tutte le colonne tranne latitudine, longitudine e data/ora e pulisci data/ora in modo che rimanga solo l’ora.
  4. Triangolando dalla coppia di coordinate del secondo precedente alla coppia del secondo seguente ottieni la distanza percorsa ogni secondo.
  5. Dalla distanza percorsa ogni secondo ottieni la velocità in nodi.
  6. Crei un grafico a bolle dove la posizione della bolla è data da latitudine e longitudine, e la larghezza è data dalla velocità.

Il risultato finale è questo. Indovina in quale regata il vento era a a volte assente e a volte a raffiche violente, e in quale era più o meno costante.

Aggiornamento:

Segnalo anche un interessante programma dedicato all’analisi di tracce GPS di regate veliche: GPSActionReplay. E’ gratuito per i primi 2 mesi, poi costa 32€. Adesso lo provo.