WindWorld > Tips&Tricks > Formule calcolo tariffe con Excel

Gerneralizzazione delle formule per il calcolo delle tariffe mediante foglio elettronico

1. CONCETTI BASILARI RIGUARDANTI UN FOGLIO ELETTRONICO

Premessa necessaria a quanto sarà detto in seguito è che si dà per scontata la conoscenza di che cosa sia e come funzioni un foglio elettronico.
Qui verranno richiamati assai rapidamente solo alcuni concetti basilari.

Un foglio elettronico ricorda quei fogli a quadretti che sui utilizzano per giocare alla "battaglia navale". E' formato da una serie di CELLE individuate dall'incrocio di una riga a di una colonna. Le righe sono individuate da numeri (1, 2, 3...); le colonne, al contrario, sono individuate da lettere (A, B, C, D...). Pertanto, per esempio, la prima cella in alto a sinistra sarà denominata A1. Una cella definita dall'incrocio della riga 7 e con la colonna D sarà denominata D4 ecc...

Per creare un foglio elettronico, IMHO, vi sono solo pochi importanti concetti da comprendere.

Il primo di essi è che ciascuna cella può contenere:

1. un NUMERO (1, 5, 234, ecc...)
2. una LABEL (etichetta, in italiano) che è un qualsiasi stringa di caratteri o valore alfanumerico sul quale non possono essere eseguite operazioni ma che serve soltanto a descrivere e/o a chiarire che cosa rappresentano i valori numerici e/o le formule presenti in altre celle (per esempio il nome dei mesi, il nome delle tariffe, la descrizione di una voce in entrata o in uscita di in un bilancio ecc...)
3. una FORMULA (per esempio A1+A2+A3) che può contenere una o più FUNZIONI (per esempio la funzione SOMMA, la funzione ARROTONDA ecc...) e che fa riferimento a dei valori numerici, a delle funzioni e o formule presenti in altre celle.
   Per esempio nella cella A5 potrebbe esserci una formula del tipo: =SOMMA(A1:A4)/$C$4 il che sta ad indicare che nella cella A5 viene calcolato automaticamente il totale dei valori numerici inseriti nelle celle A1, A2, A3 e A4 e che il tutto viene diviso per il valore contenuto in C4
   Come si può notare, per far capire al foglio elettronico che con il termine SOMMA ci si intende riferire ad una funzione e non ad una LABEL, si prefissa il tutto col segno '=' (uguale).
   N.B.: Ciò avviene nel caso di fogli elettronici del tipo Excel e compatibili. Nel caso di fogli Lotus 123 e compatibili, si utilizza invece il segno '+' (più).
   Si noti anche la presenza del simbolo $ (che prefissa la cella definita dall'incrocio della colonna C e della riga 4) che sta ad indicare un riferimento assoluto sul quale ritorneremo qui sotto.

Una volta compreso che cosa si può inserire in una cella di un foglio elettronico, il secondo concetto fondamentale concetto per iniziare a lavorare è comprendere il concetto di RIFERIMENTO ASSOLUTO e di RIFERIMENTO RELATIVO che abbiamo poco sopra introdotto quando abbiamo accennato al simbolo $.
Una formula inserita in una certa cella contiene infatti il riferimento a valori numerici, funzioni, formule, presenti in altre celle del foglio elettronico e, ovviamente, può essere copiata in altre celle. Ma come dovranno essere copiati i riferimenti in questione?

1. Vi potrà essere un riferimento ASSOLUTO quando ci si intende riferire ad una cella ben definita (per es. A3) nel qual caso essa compare nella formula prefissata come detto sopra dal segno '$' (es. $A$3). Vi possono essere anche dei casi in cui il riferimento è assoluto alla riga ma non alla colonna (es.: A$3) oppure è assoluto per la colonna e non per la riga (es. $A3).
2. Alternativamente si parla di riferimento RELATIVO quando copiando la formula si intende farlo riferendosi non tanto al contenuto di una specifica cella, bensì alla cella che è situata, per esempio, due colonne a destra rispetto alla cella in cui ci si trova.
   In tal caso nella formula la cella compare nela formula senza essere prefissata dal segno '$' (per esempio: A3) e si modificherà copiando in altre celle.

In sostanza un riferimento ASSOLUTO è come dire: "Vai in Via Mazzini, 13". In questo caso cioé il riferimento è ad un luogo definito, indipendentemente dal luogo in cui ci si trovi in un certo momento.
Un riferimento RELATIVO è invece del tipo: "Vai nella seconda strada a destra". Ovviamente la seconda strada a destra sarà.diversa a seconda di dove ci troviamo in un certo momento.

IMPORTANTE: se si inseriscono o se si cancellano delle righe e/o delle colonne, i riferimenti assoluti e relativi si modificano opportunamente per tener conto di questi inserimenti o cancellazioni!

Compresi questi concetti, l'uso di un foglio elettronico, almeno per un utilizzo di basilare, diventa molto semplice perché tutto il resto riguarda solo l'aspetto estetico: formattazione dei numeri, colori, font ecc...

2. APPLICAZIONE AL CALCOLO DELLE TARIFFE TELEFONICHE

Si presupponga che:

1. La colonna A dello spreadsheet sia riempita con la durata in secondi della chiamata (da zero a 'n' secondi - per esempio fino a 600 se si desidera effettuare i calcoli fino a 10 minuti);
2. La colonna B dello spreadsheet sia destinata a contenere una tariffa a secondi;
3. La colonna C dello spreadsheet sia destinata a contenere una tariffa a scatti.

a. TARIFFE A SECONDI

E’ necessario conoscere 4 parametri:

1. Costo (in Eurocent) dello scatto alla risposta.
2. Costo al secondo (in Eurocent) della prima tariffazione.*
3. Durata (in secondi) della prima tariffazione.**
4. Costo al secondo (in Eurocent) della seconda tariffazione.

Se, per esempio:

• Il costo (in Eurocent) dello scatto alla risposta è contenuto in B2
• Il costo al secondo (in Eurocent) della prima tariffazione. è contenuto in B6
• La durata (in secondi) della prima tariffazione. è contenuta in B7
• Il costo al secondo (in Eurocent) della seconda tariffazione è contenuto in B8

e la durata (in secondi) della chiamata è presente in colonna A (per esempio da A11 in poi), la formula sarà:

=IF((B$7>0);IF($An<(B$7+1);B$2+($An*B$6);(B$2+(B$7*B$6)+($An-B$7)*B$8));B$2+($An*B$6))

ove 'n' varia di riga in riga e assume valori rispettivamente di 11...12...13... ecc...(se si parte da A11) consentendo così di copiare la formula.
Egualmente il fatto che la colonna B non sia un riferimento assoluto, ma lo sia la riga, consente di copiare la formula su altre colonne per calcolare altre tariffe.

N.B.: In italiano la funzione IF è tradotto con SE

b. TARIFFE A SCATTI

Anche in questo caso è necessario conoscere 4 parametri:

1. Costo (in Eurocent) dello scatto alla risposta.
2. Eventuale numero di secondi compresi nello scatto alla risposta (es. la tariffa Vodafone Personal Sera in Euro ha i primi 3 secondi che vengono tariffati con lo scatto alla risposta iniziale). ***
3. Costo (in Eurocent) del singolo scatto.
4. Durata (in secondi) di uno scatto.

Se, per esempio:

• Il costo (in Eurocent) dello scatto alla risposta è contenuto in C2
• Il numero di secondi compresi nello scatto alla risposta è contenuto in C3
• Il costo (in Eurocent) del singolo scatto è contenuto in C4
• La durata (in secondi) di uno scatto è contenuto in C5

e la durata (in secondi della chiamata è presente nella colonna A (da A11 in poi), la formula sarà:

=IF($An<(C$3+1);C$2;C$2+ROUND(((($An-C$3)/ROUND(C$5;0))+0,4999);0)*C$4)

ove 'n' varia di riga in riga e assume valori rispettivamente di 11...12...13... ecc...(se si parte da A11) consentendo di copiare la formula.
Egualmente il fatto che la colonna C non sia un riferimento assoluto, ma lo sia la riga, consente di copiare la formula in altre colonne per calcolare altre tariffe.

N.B.: In italiano la funzione IF è tradotto con SE e la funzione ROUND con ARROTONDA.

3.CONCLUSIONI

Fin qui ci si è limitati a fornire gli elementi essenziali per potersi calcolare il costo di una telefonata con una certa tariffa telefonica mediante foglio elettronico.
Ovviamente Il tutto poi potrà essere oggetto di miglioramenti quali:

• il ricalcolo dei costi con un coefficiente che esprima l'incidenza dei costi di ricarica (per es. se i costi di ricarica della tarifa presente in colonna B sono il 20%, tale colonna potrà essere moltiplicata per 1,2).
• la creazione di un grafico per visualizzare l'andamento dei costi delle tariffe che ci interessano in funzione della durata delle chiamate in modo tale da consentirne una più immediata comparazione.
• variazioni dei colori dei caratteri utilizzati, delle loro dimensioni, del colore di sfondo in funzione di quale fra le tariffe paragonate risulta, per esempio, la più economica in un certo range di durata ecc...

Sperando di essere stato utile, auguro un buon lavoro a tutti!

4. APPENDICE

Il file esempio_tariffe.xls rappresenta la messa in pratica di quanto fin qui affermato sopra e può essere utilizzato come modello da cui partire per aggiungere le tariffe di proprio interesse semplicemente effettuando un "copia e incolla" del contenuto di una colonna che contiene già dei dati in una nuova colonna. Ovviamente, per ogni nuova tariffa, è anche necessario inserire nelle righe iniziali i valori richiesti, seguendo i criteri spiegati sopra.

Il file esempio_tariffe.xls riporta 5 tariffe che sono rappresentative di molte delle tipologie di tariffe possibili:

1. Tariffa a secondi con doppia tariffazione e senza scatto alla risposta (Wind SempreLight)
2. Tariffa a scatti con scatto alla risposta e ufficialmente senza secondi di conversazione compresi nello scatto alla risposta, in realtà con scatto alla risposta comprensivo del primo secondo di conversazione (Wind10)
3. Tariffa a secondi con scatto alla risposta e tariffazione unica (Tre ua Semplice)
4. Tariffa a scatti con scatto alla risposta senza secondi di conversazione compresi nello scatto alla risposta(Tre SempreTua+)
5. Tariffa a scatti con scatto alla riposta e con 5 secondi di conversazione compresi nello scatto alla risposta (LongTim)

N.B.: Il file esempio_tariffe.xls NON tiene conto dell'incidenza dei costi di ricarica per i quali si rimanda a quanto detto nelle "Conclusioni".