In questo articolo creeremo uno scadenzario per la gestione delle fatture scadute che un’immaginaria società deve incassare. Faremo in modo di vedere, a una data specificata, qual è l’ammontare complessivo delle fatture scadute da 30 giorni, da 60, da 90 e così via.
Scadenzario delle fatture scadute
Lavoreremo con il file Scadenzario che trovate nel booksite del libro da cui è tratto questo articolo all’indirizzo. Questo file si compone di due fogli, di cui uno, ElencoFatture, visibile in figura 1, riporta le fatture scadute e non ancora incassate.
Nel secondo foglio, Scadenzario fatture, visibile in figura 2, vogliamo riportare l’elenco di tutti i clienti e fare in modo che, in corrispondenza del cliente, venga riportata la somma degli importi scaduti in vari range di scadenza (0-30 giorni, 31-60 giorni, 61-90 giorni ecc.) rispetto alla data che scriveremo in C3.
In pratica, nella cella C6 dovremo fare la somma di tutte le fatture del cliente, che riporteremo in B6, scadute da meno di 30 giorni.
Creare un elenco di dati univoci
Prima di passare a questa operazione, però, vogliamo compilare in modo veloce il foglio Scadenzario fatture, che abbiamo visto nella figura 2, riportando nella colonna B l’elenco di tutti i clienti presenti nel foglio ElencoFatture. In pratica, a partire da B6 dobbiamo riportare tutti i clienti che hanno almeno una fattura non pagata presi una volta sola. In questo ci vengono incontro i Filtri avanzati.
Selezionate la colonna A del foglio ElencoFatture. Poi, portatevi alla scheda Dati e, nel gruppo Ordina e filtra, premete il pulsante Avanzate.
Apparirà la finestra Filtro avanzato, visibile in figura 3.
Selezionate l’opzione Copia in un’altra posizione, quindi nella casella Intervallo elenco indicate la colonna A (cioè quella che contiene i dati da filtrare).
Lasciate vuota la casella Intervallo criteri (noi vogliamo filtrare tutti i dati, anche se presi una volta sola). Nella casella Copia in specificate una cella del foglio attivo in cui volete che Excel cominci a scrivere i dati filtrati. Purtroppo, non è possibile copiare i dati filtrati in un foglio diverso da quello attivo. Ma non è un grosso problema. Una volta ottenuto l’elenco univoco dei clienti lo copieremo nel foglio Scadenzario fatture. Selezionate l’opzione Copia univoca dei record, infine premete OK per avviare la creazione del filtro.
Il risultato è mostrato nella figura 4.
A questo punto non ci resta che copiare l’elenco filtrato (G2: G11) e copiarlo nel foglio Scadenzario fatture a partire dalla cella B6.
Calcolare da quanto è scaduta una fattura
Ora che abbiamo inserito nel nostro scadenzario l’elenco dei clienti, passiamo a calcolare da quanto sono scadute le nostre fatture.
Scriveremo nella cella C3 del foglio Scadenzario fatture la data da cui vogliamo calcolare da quanto sono scadute le fatture, come mostrato in figura 5.
Per comodità di calcolo, conviene assegnare alla cella che ospita questa data un nome, che poi potremo usare nelle formule. Noi abbiamo scelto il nome DataChiusura.
Nota
Ricordate che, per assegnare un nome a una cella o a un intervallo di celle, dopo averli selezionati, dovete andare alla scheda Formule e, nel gruppo Nomi definiti, premere il pulsante Definisci nome. Nella finestra che appare specificate il nuovo nome. A questo punto ritorniamo nel foglio ElencoFatture: dobbiamo aggiungere una colonna in cui calcoliamo i giorni da cui la fattura è scaduta (sempre rispetto alla data nella cella cui abbiamo dato il nome DataChiusura).
In E2, scriviamo la formula:
= DataChiusura-D2
Il risultato, visibile in figura 6, sarà una data, perché le celle coinvolte nella formula contengono date, ma basta applicare un formato numerico generale per attenere il risultato che ci serve.
Trascinate la formula per tutta la colonna E.
In Excel 2007 sono state introdotte due nuove funzioni utilissime (ovviamente mantenute nella versione 2010): si tratta di SOMMA. PIÙ. SE (funzioni matematiche) e CONTA. PIÙ. SE (funzioni statistiche). Questo due funzioni estendono le funzionalità di SOMMA.SE e CONTA.SE, già presenti nelle vecchie versioni di Excel. SOMMA.SE e CONTA.SE permettono, rispettivamente, di sommare e di contare i valori in un intervallo che rispondono a un determinato criterio (che può anche fare riferimento a un’altra colonna).
Nota
Le due funzioni SOMMA. PIÙ. SE e CONTA. PIÙ. SE permettono di specificare più di un criterio (fino a un massimo di 127).
SOMMA. PIÙ. SE fa proprio al caso nostro, visto che dobbiamo sommare tutti i valori della colonna C del foglio Elenco fatture che rispondono ad almeno due criteri:
- che il cliente sia quello scelto;
- che la data di scadenza rientri in una dato range.
Proviamo subito questa funzione.
Diciamo, prima di tutto, che SOMMA. PIÙ. SE richiede un numero diverso di argomenti in funzione di quanti criteri si vogliono usare per scegliere le celle da sommare. Comunque, la struttura di questi argomenti è sempre uguale:
- Int_somma indica l’intervallo di cella da cui estrarre le celle da sommare;
- Intervallo_criteri1 è il primo intervallo in cui valutare i criteri associati;
- Criteri1 indica i criteri da applicare alle celle dell’intervallo Intervallo_criteri1;
- Intervallo_criteri2, Criteri2,… sono gli intervalli aggiuntivi e i relativi criteri. È consentito un massimo di 127 coppie intervallo/criteri.
È obbligatorio avere, a parte Int_somma, almeno gli argomenti Intervallo_criteri1 e Criteri1, cioè almeno un criterio per selezionare la celle da sommare.
Cominciamo a scrivere la nostra prima funzione SOMMA. PIÙ. SE in C6. Qui dobbiamo sommare le celle della colonna C del foglio Elenco fatture per cui, nella riga corrispondente della colonna A del foglio Elenco fatture, il cliente sia uguale al valore della cella B6 e, nella riga corrispondente della colonna E del foglio Elenco fatture, vi sia un valore minore o uguale a 30. In pratica, tutte le fatture del cliente Fornari scadute da meno di 30 giorni.
Questo significa che gli argomenti vanno specificati come nella tabella che segue:
| Int_somma | ‘Elenco fatture’! C: C |
| Intervallo_criteri1 | ‘Elenco fatture’! A: A |
| Criteri1 | B6 |
| Intervallo_criteri2 | ‘Elenco fatture’! E: E |
| Criteri2 | “>30” |
La finestra Argomenti funzione andrebbe compilata come nella figura 7.
La funzione completa è:
=SOMMA. PIÙ. SE(‘Elenco fatture’! C: C; ‘Elenco fatture’! A: A; B6; ‘Elenco fatture’! E: E; “<=30”)
Trascinate la funzione per tutta la colonna. A questo punto lo scadenzario mostra la somma di tutte le fatture scadute da meno di 30 giorni per tutti i clienti, come visibile in figura 8.
Per le altre colonne occorrono tre coppie Intervallo_criteri/Criteri, in modo da poter verificare che il numero della colonna E del foglio Elenco fatture sia compreso tra gli estremi del range che ci interessa.
La tabella che segue mostra i criteri da usare nella cella D6, ossia per sommare le fatture scadute in un intervallo compreso fra i 31 e i 60 giorni.
| Int_somma | ‘Elenco fatture’! C: C |
| Intervallo_criteri1 | ‘Elenco fatture’! A: A |
| Criteri1 | B6 |
| Intervallo_criteri2 | ‘Elenco fatture’! E: E |
| Criteri2 | “>30” |
| Intervallo_criteri3 | ‘Elenco fatture’! E: E |
| Criteri3 | “<=60” |
La formula da usare è:
=SOMMA. PIÙ. SE(‘Elenco fatture’! C: C; ‘Elenco fatture’! A: A; B6; ‘Elenco fatture’! E: E; “>30”; ‘Elenco fatture’! E: E; “<=60”)
La formula va trascinata per tutta la colonna.
A questo punto, potete proseguire da soli per le altre colonne o usare la tabella che segue come riferimento:
<<=SOMMA. PIÙ. SE(‘Elenco fatture’! C: C; ‘Elenco fatture’! A: A; B6; ‘Elenco fatture’! E: E; “>360”)
| Nella cella | Usare funzione |
| E6 | =SOMMA. PIÙ. SE(‘Elenco fatture’! D: D; ‘Elenco fatture’! A: A; B6; ‘Elenco fatture’! E: E; “>60”; ‘Elenco fatture’! E: E; “<=90”) |
| F6 | =SOMMA. PIÙ. SE(‘Elenco fatture’! C: C; ‘Elenco fatture’! A: A; B6; ‘Elenco fatture’! E: E; “>90”; ‘Elenco fatture’! E: E; “<=180”) |
| G6 | =SOMMA. PIÙ. SE(‘Elenco fatture’! C: C; ‘Elenco fatture’! A: A; B6; ‘Elenco fatture’! E: E; “>180”; ‘Elenco fatture’! E: E; “<=360”) |
| H6 | =SOMMA. PIÙ. SE(‘Elenco fatture’! C: C; ‘Elenco fatture’! A: A; B6; ‘Elenco fatture’! E: E; “>360”) |
Nella cellaUsare la funzioneE6
=SOMMA. PIÙ. SE(‘Elenco fatture’! D: D; ‘Elenco fatture’! A: A; B6; ‘Elenco fatture’! E: E; “>60”; ‘Elenco fatture’! E: E; “<=90”)
F6
=SOMMA. PIÙ. SE(‘Elenco fatture’! C: C; ‘Elenco fatture’! A: A; B6; ‘Elenco fatture’! E: E; “>90”; ‘Elenco fatture’! E: E; “<=180”)
G6
=SOMMA. PIÙ. SE(‘Elenco fatture’! C: C; ‘Elenco fatture’! A: A; B6; ‘Elenco fatture’! E: E; “>180”; ‘Elenco fatture’! E: E; “<=360”)
H6
=SOMMA. PIÙ. SE(‘Elenco fatture’! C: C; ‘Elenco fatture’! A: A; B6; ‘Elenco fatture’! E: E; “>360”)
La figura 9 mostra lo scadenzario completato.
Autore: Alessandra Salvaggio – Tratto da: Problemi e soluzioni con Excel 2010 – Edizioni FAG Milano
