Excel - Trimestri uso funzioni DATA, GIORNO e FINE.MESE

google-plus Scritto da . Postato in Excel

Tags: excel funzioni microsoft corso excel

Nell'articolo precedente siamo partiti con l'uso della funzione SOMMA.PIU.SE e proseguito con l'aggiunta di parametri multipli nei criteri sotto forma di matrice:

Excel - SOMMA.PIU.SE con Condizione multipla

Ora, come già annunciato nell'articolo di cui il link qui sopra, vedremo nel foglio di Riepilogo come si può modificare la formula in modo tale che le date corrispondenti all'inizio e alla fine dei trimestri vengano calcolate automaticamente da Excel, partendo dalla cella A1 che conterrà l'anno di esercizio.

Nell'esempio precedente infatti, la formula effettuava il calcolo delle spese/entrate di una ipotetica impresa sulla base di un elenco contenuto nel foglio di lavoro principale, mettendo come date di confronto l'inizio e la fine di ogni trimestre. Le date in questione però erano riportate a mano come criterio della funzione SOMMA.PIU.SE.

Tra qualche mese non saremo più nel 2019, e dunque per rendere funzionale il foglio di lavoro Riepilogo anche nel 2020 saremmo obbligati a cambiare a mano le date relative ai trimestri una per una, con un considerevole dispendio di tempo e con l'innalzarsi della probabilità di commettere un errore nella modifica.

Molto più comodo, è invece istruire Excel in modo tale che, a partire da una cella che detterà l'anno di esercizio, calcoli in automatico le date dei trimestri relative all'anno in corso.

Calcolo data inizio e fine trimestri

Vedremo ora come fare grazie a tre funzioni integrate in Excel: DATA(), GIORNO() e FINE.MESE().

Queste tre funzioni, hanno tutte a che vedere con il calcolo e la manipolazione delle date. Vedremo perché dovremo usarle tutte e tre e come poterlo fare al meglio per ottenere il risultato sperato senza troppo complicarci la vita.

Nel nostro ipotetico prospetto riepilogativo, per ogni conto corrente, può essere utile avere a colpo d'occhio le somme suddivise per trimestre per Dipendenti, Fatture d'acquisto, IVA, F24, Costi bancari etc.

Come già detto, ogni cella del prospetto è calcolata grazie alla funzione SOMMA.PIU.SE. Il punto dove vogliamo inserire la modifica, è quella relativa alla data di inizio/fine trimestri.

Per comodità, useremo un foglio di lavoro separato per vedere meglio il calcolo di queste date e la costruzione della formula corrispondente per poi inserirla nel foglio di lavoro Riepilogo della cartella "Banche".

Calcolo inizio trimestre

excel trimestri

Nell'immagine vediamo la prima formula che ci aiuterà a calcolare la data di inizio del trimestre partendo dalla cella contentente l'anno di esercizio e il numero di trimestre:

=DATA($A$1;((A2*3)+1)-3;1)

Vediamo ora nel dettaglio la formula e come essa è stata concepita per ottenere il risultato desiderato. La funzione DATA, ha come parametri, l'anno (contenuto nella cella A1), il mese ed infine il giorno (il primo giorno del trimestre sarà sempre corrispondente a 1).

Resta dunque da capire come calcolare il mese del trimestre: Partendo dal numero di trimestre, ad esempio 1 è possibile ottenere una formula che poi spostata nel foglio di lavoro sia in grado di calcolare il mese iniziale del trimestre in base appunto al numero trimestre.

Se il trimestre è il 1°, per calcolare il trimestre, la formula ((1*3)+1)-3 darà come risultato 1. Se il trimestre è il 2° la formula  ((2*3)+1)-3 darà come valore 4 e così via.

Il concetto dietro la formula è che si parla di intervalli di 3 mesi e per arrivare al 1° giorno di un trimestre, si parte sempre dal mese successivo al multiplo di 3 eccezzion fatta per gennaio in quanto primo trimestre. Tuttavia, la numerazione parte dallo 0, dunque se moltiplico per 3 il numero di trimestre e aggiungo 1, ottengo il numero del 1° mese del trimestre successivo. Sottraendo 3, si trova il numero del mese del trimestre corrente.

Un po' contorto? Tenuto conto che la formula spostata o incollata altrove deve restituire il valore corretto sulla base di due valori (anno,trimestre), la formula utilizzata ci aiuta ottenere senza troppi ragionamenti cervellotici il risultato desiderato.

La formula ((X*3)+1)-3 dove X sarà il riferimento alla cella contenente il numero del trimestre in oggetto, sarà posta a completamento della funzione DATA come secondo parametro, cioé il mese.

Calcolo fine trimestre

Ora prendiamo in considerazione la formula per il calcolo dell'ultimo giorno del trimestre di cui vediamo un'anteprima nella prossima immagine:

excel ultimo giorno trimestre

=DATA($A$1;A2*3;GIORNO(FINE.MESE(DATA($A$1;(A2*3);1);0)))

In questa formula, l'anno è sempre dettato dalla cella A1 e il mese è facilmente calcolabile in quanto è il terzo mese del trimestre (numero del trimestre per 3). E' il giorno del mese che sarà più complesso in quanto per alcuni mesi cadrà il 31 e per altri il 30.

In questo ci aiuta la funzione FINE.MESE che calcola come dice il suo nome il giorno finale del mese rispetto a una data fornita come parametro.

Per calcolarci la data da dare come parametro alla funzione FINE.MESE, sarà sufficiente utilizzare la funzione DATA: A1 è il parametro anno, per il mese moltiplicheremo il numero del trimestre per tre (il mese finale del trimestre corrispondente) e come giorno, uno qualunque, purché casa nel mese finale del trimestre. Noi per convenzione mettiamo sempre il giorno 1.

Il secondo parametro della funzione FINE.MESE è settato a 0. Il secondo parametri è il numero di mesi precedenti o successivi alla data messa come primo parametro. A noi serve il fine mese della data che mettiamo come primo parametro, per cui non faremo nessun 'salto' di mesi e lasciamo il parametro a 0.

Infine, come si può notare la funzione FINE.MESE è annidata nella funzione GIORNO(). Il perché è molto semplice: in uscita, non ci servirà una data completa, bensì il numero dell'ultimo giorno corrispondente all'ultimo mese del trimestre. La funzione GIORNO() estrapola proprio il solo giorno della data in input.

Anche qui può sembrare abbastanza machiavellico la formula risultante, ma ci consente di ottenere esattamente il risultato sperato, come si vede dalle immagini precedenti.

Annidare le formule inizio/fine trimestre nella funzione SOMMA.PIU.SE

Ora che abbiamo creato le formule necessarie a calcolarci le copie inizio/fine trimestre, possiamo annidarle all'interno della formula che ci consente attraverso la funzione SOMMA.PIU.SE di calcolare i riepiloghi per voce nel nostro foglio di lavoro Riepilogo. Ecco un esempio:

excel sommapiuse trimestri

=SOMMA.PIÙ.SE(Banche!$H$2:$H$43;Banche!$C$2:$C$43;$B$1;Banche!$D$2:$D$43;$B$2;Banche!$A$2:$A$43;">="&DATA($A$1;((E3*3)+1)-3;1);Banche!$A$2:$A$43;"<="&DATA($A$1;E3*3;GIORNO(FINE.MESE(DATA($A$1;(E3*3);1);0)));Banche!$B$2:$B$43;'Riepilogo Banche'!$A4)

La modifica deve essere poi apportata nelle formule che contribuiranno al calcolo dei fogli di lavoro.

E' disponibile al download il file esercizio nella sezione Download > Excel del sito: 


Vuoi imparare a usare Excel dal tuo PC?

Impara a usare professionalmente Microsoft Excel con MasterExcel.it: Aumentereai la tua conoscenza di Excel riuscendo a fare molto di più e in molto meno tempo! Tutto questo senza muoverti da casa o spendere cifre improponibili.
Dai un occhio alle proposte, valuta il tuo livello e scegli il tuo corso Excel!


 

Se hai gradito l'articolo, condividilo, basta un click:

Aggiungi commento


Codice di sicurezza
Aggiorna

  • logo teoca
  • logo arcotraslochi
  • materexcel collabora
  • koilab japan and tropical fish farm
  • logo flash pulizie
  • revolution srl
  • logo planetkoi small
  • logo elevatori sito
  • logo finiture
  • logo prontopro
  • montaggio mobili trento

Realizzato da: Daniele Gagliardi
Viale Monache 3 - 38062 Arco - TN
- Contattami -

Per essere più facile ed intuitivo, il blog fa uso dei cookie, piccole porzioni di dati che consentono di capire come gli utenti navighino e ne visualizzino le pagine. I cookie non registrano alcuna informazione personale sull'utente ed eventuali dati identificabili non verranno memorizzati. La nostra Privacy Policy è cambiata (11/09/2019): Ti invitiamo a prenderne visione. Oppure visiona la nostra Cookie Policy (aggiornata il 17/10/2019):