Aggiornamento del 24/09/2013: l’utente Aldo ha gentilmente fornito un link ad una risorsa gratuita on-line per la ripartizione della bolletta – link. Mille grazie a Aldo per la segnalazione.

Aggiornamento 26/01/2013: l’utente Lorenzo ha messo a disposizione un file con le formule e uno schema già impostati per il calcolo delle fasce per l’azienda AQP. Scarica il file cliccando su questo testo: Ripartizione_Acqua_Base_per_fasce.

Oggi scrivo questo post facendo riferimento ai commenti lasciati da Gianni in uno dei post dove trattavo la funzione SE di Excel.

La discussione nei commenti non è completa, spostata in privato per questioni di comodità. Al termine, dato l’interesse che poteva avere quel tipo di calcolo, ho chiesto a Gianni se potevo usare quelle informazioni per creare un post. Gentilmente mi ha dato il permesso, augurandosi per primo che la cosa potesse essere utile ad altri.

Approfitto per ringraziarlo per la cortesia.

Riguardo la questione acqua, evito in qualsiasi modo di entrare nel merito di gestione, normative e quant’altro. Non sono competente e non è l’obiettivo di questo mio post.

Mi limito a fare riferimento all’esempio inviatomi e al relativo calcolo, dato che non sono rari i conteggi per scaglioni. E non solo in ambito acqua.

Aggiornamento 08/10/2012: nei commenti potrete trovare maggiori dettagli e precisazioni gentilmente indicate dall’utente Alessandro (cliccate sul link per visualizzare il primo commento inviato). Approfitto per ringraziarlo per questi suoi interventi.

Venendo al succo, la sua intenzione era quella di creare un foglio di calcolo che gli permettesse di conteggiare i consumi, quindi di calcolare l’ammontare della bolletta, allo scopo di verificare i dati riportati in questa.

Per il calcolo sono previste 5 tariffe distinte in base ad un quantitativo prestabilito. E qui il problema nell’impostare il calcolo.

L’esempio è stato sviluppato in Excel 2007, per mia comodità. Non sono state usate funzioni o procedure tali da non permettere il funzionamento su versioni differenti del software. In caso di problemi, inviate un commento oppure usate il form per i contatti.

Anteprima di stampa del file per il calcolo dei consumi d'acqua

Anteprima del file per il calcolo dei consumi (clicca per ingrandire)

Nella figura sopra ho riportato l’anteprima di stampa, così da poter avere un’idea del file risultante.

Calcolo dei giorni e dei consumi

Nel riquadro in alto, sotto il titolo, sono riportate le letture effettuate e le relative date. Quindi il calcolo dei giorni tra le due letture e i metri cubi consumati. Nella figura sottostante potete vedere il dettaglio.

Riquadro delle letture per il file di calcolo dei consumi d'acqua

Riquadro delle letture del file

Nella cella C5 è inserita la formula =B5-A5. Il risultato è una data senza significato.

  • Selezionare la cella C5
  • Nella scheda Home della Ribbon, al gruppo Numeri, impostare Generale come Formato numero (casella di riepilogo)

Per il calcolo dei metri cubi consumati, una semplice sottrazione: in D6 la formula =B6-A6.

I volumi delle diverse tariffe

Prima di arrivare ai calcoli veri e propri, faccio notare la piccola tabella in fondo al documento. Questa riporta i diversi scaglioni e i relativi metri cubi che saranno usate come soglie per le funzioni da adottare.

Tabella degli scaglioni per il calcolo dei costi per l'acqua

Tabella degli scaglioni per il calcolo dei costi per l’acqua

Per spiegare velocemente, ad un consumo fino a 18 mc, si applica la tariffa T1. Per consumi tra 18 e 27 (cioè 18+9=27) la T2 e così via, fino alla tariffa T5 per consumi oltre i 45 metri cubi. Le tariffe sono riportate nella figura successiva.

Questo è il punto. Il consumo calcolato dalla lettura (prima figura) deve essere scomposto nei vari scaglioni, considerando quelli già calcolati.

Proseguendo con il calcolo, la cosa diventerà più chiara. Intanto riporto la tabella principale dove svolgere i calcoli.

Riquadro della bolletta per il calcolo delle tariffe in base al consumo

Riquadro della bolletta per il calcolo delle tariffe in base al consumo

Come da esempio, i metri cubi dalla lettura sono 37 e riportati nella cella D6.

Calcolare il volume per la tariffa T1

Impostando in D10 la funzione =SE(D6<=C42;D6;C42), si verifica se il quantitativo di metri cubi D6 sia inferiore o uguale alla tariffa T1 di C42. Se ciò risponde al vero, allora si inserisce il valore di D6, cioè il risultato della lettura. In caso contrario si riporta il volume massimo della tariffa, scritto in C42, cioè 18 metri cubi.

Nella cella E10, nella figura, è già inserita una formula che calcola il prodotto tra la tariffa e i metri cubi riportati in D10. Lo stesso per le righe sottostanti.

Calcolare il volume per la tariffa T2

In questo caso si deve verificare se i metri cubi conteggiati in D6 sono inferiori alla somma dei volumi previsti per le tariffe T1 e T2. Questa somma è importante perché si deve tenere in considerazione sia il volume della prima tariffa che della seconda, perché il conteggio risulti corretto. Se tale verifica risulta vera, allora si sottrae dal volume di D6, cioè 37, dal risultato trovato per la tariffa T1 scritto nella cella D10. In caso contrario si riporta il volume massimo previsto, cioè 9 e riportato nella cella C43.

La funzione per ottenere questo risultato in D11 è: =SE(D6<(C42+C43);D6-D10;C43).

Grazie a questa funzione, si possono definire tutti i volumi compresi tra 18 e 27 e inserire il valore corretto che va da 0 a 9.

Calcolare il volume per la tariffa T3

Per la T3 si verifica se il volume totale dei metri cubi della lettura è compreso tra 27 (T1+T2) e 36 (T1+T2+T3). Se ciò risulta vero, si sottrae dal volume di D6 i valori trovati per T1+T2 delle celle D10 e D11.

La funzione in D12 è: =SE(D6<(C42+C43+C44);D6-(D10+D11);C44)

Calcolare il volume per la tariffa T4

Anche in questo caso si segue lo stesso principio adottato per le tariffe precedenti. Se i metri cubi sono inferiori alla somma delle 4 tariffe, allora si sottrae il volume consumato dalla somma dei tre precedenti volumi calcolati.

Ed ecco la funzione per la cella D13: =SE(D6<(C42+C43+C44+C45);D6-(D10+D11+D12);C45)

Calcolare il volume per la tariffa T5

L’ultima tariffa contempla tutti i consumi superiori a 45 metri cubi. Quindi si verifica se la lettura supera questo valore, indicato nella cella C46. Se vero si sottrae il valore della lettura dal valore di soglia della tariffa. In caso contrario, cioè se la lettura non supera i 45 mc, allora si riporta uno 0 (zero).

Ecco la funzione per la cella D14: =SE(D6>C46;D6-C46;0)

Verifica dei calcoli

A questo punto non resta che cambiare il valore riportato nella cella B6, relativo all’ultima lettura, per verificare che tutte le scomposizioni del volume di consumo siano corrette.

Impostando un valore che dia un consumo inferiore o pari a 18 mc, nella cella D10 verrà riportato un valore. In tutte le altre verrà inserito uno zero. Mettendo valori più grandi, le restanti celle verranno popolate con numeri tali da ridare sempre il consumo totale. Fate diverse prove per verificare il corretto funzionamento.

Considerazioni

Per le funzioni delle tariffe T2, T3 e T4 ho usato delle somme manuali. Si potrebbe modificare inserendo una funzione SOMMA al posto delle formule tra parentesi, però non cambia il concetto e, data la lunghezza, ho preferito lasciarle così.