In questo articolo vi proponiamo la realizzazione di un modello per creare le vostre fatture, o più semplicemente, delle note pro forma. Ripasseremo così tante funzionalità utili come la ricerca in file diversi, le aree di stampa e la protezione delle celle.
La fattura
Iniziamo il lavoro di questo articolo dal file Fattura, visibile in figura 1, che potete trovare sul booksite del libro dai cui è tratto questo articolo.
Il file contiene già la struttura della fattura. Inoltre, usando la tecnica degli intervalli dinamici, nella cella G11 abbiamo inserito un elenco con i possibili tipi di pagamento (questi sono stati, a loro volta, inseriti nel foglio TipiPagamento). Anche nelle celle D17:D32 abbiamo creato un elenco per scegliere tre possibili aliquote IVA (4%, 10% e 21%).
In questa prima fase, ci occuperemo essenzialmente di lavorare nelle righe dalla 17 in giù. Le righe precedenti, infatti, dovranno essere compilate dall’utente che realizza la fattura o la nota (per la verità, più sotto, mostreremo come si possono automatizzare alcune di queste operazioni).
Ovviamente ci sono delle parti da compilare anche nelle righe successive alla 17: bisogna inserire il codice del prodotto, la descrizione, il suo prezzo unitario, la quantità, l’aliquota IVA, lo sconto, eventuali costi di imballaggio (D38), eventuali spese documentate (D37) ed eventuali spese non documentate (D39). Ma gli altri valori verranno calcolati in base ad alcune formule che andremo ad approntare.
Come prima, semplice operazione, ci occupiamo di inserire nella colonna H la formula che calcola il prezzo totale di ogni riga. La formula da usare è questa:
=B17*G17-(B17*G17*E17)
Questa formula calcola il prezzo, moltiplicando la quantità di pezzi (B17) richiesti di un dato prodotto per il suo prezzo unitario (G17); a questo si toglie poi lo sconto, che a sua volta si calcola moltiplicando il prezzo totale (B17*G17) per la percentuale di sconto (E17). Una volta inserita la formula, trascinatela fino alla cella H32. A questo punto viene il difficile. Dobbiamo calcolare l’importo totale della merce cui si applica l’IVA del 21%, quello della merce con IVA al 10% e quello della merce con IVA al 4%. Per farlo abbiamo bisogno di colonne di appoggio che poi, naturalmente, nasconderemo. Lavoriamo alla destra della struttura della fattura. A partire dalla cella J16 scriviamo le etichette IMPORTI AL 21%, IMPORTI AL 10%, IMPORTI AL 4%, come visibile in figura 2. Nelle celle al di sotto di ciascuna etichetta riporteremo l’importo totale della riga in base al tipo di aliquota IVA da applicare.
Cominciamo a scrivere le formule che ci servono. In J2 la formula da usare è:
=SE(D17=21%;H17;0)
Se l’IVA memorizzata nella colonna D è pari al 21%, allora riportiamo qui l’importo totale della riga, cioè il valore di H17, altrimenti scriviamo 0. Trascinate la formula fino a J32. Ora passiamo alle celle K17 e L17. Le formule da usare sono, rispettivamente:
=SE(D17=10%;H17;0)
e:
=SE(D17=4%;H17;0)
Trascinate anche queste formule fino alla riga 32. A questo punto la colonna J contiene tutti gli importi cui va applicata l’IVA del 21%, la colonna K quelli a cui va applicata l’IVA del 10% e la colonna L quelli cui va applicata l’IVA del 4%.
Basterà fare la somma dei valori di ciascuna colonna per scoprire qual è l’importo totale cui si deve applicare ciascuna aliquota IVA. Lo facciamo nelle celle J34, K34 e L34. In J34 scriviamo =SOMMA(J17:J32), in K34 =SOMMA(K17:K32) e in L34 =SOMMA(L17:L32).
Adesso dobbiamo calcolare la porzione delle spese di imballo (che l’utente dovrà scrivere in D38) cui va applicata ciascuna aliquota IVA (questa porzione è in rapporto al totale complessivo, per ogni aliquota).
Faremo questo calcolo nella riga 36. Prima, però, in M34 calcoliamo l’importo complessivo indipendentemente dal tipo di aliquota. La formula da usare è SOMMA(J34:L34).
A questo punto, in J36 scriviamo:
=J34/$M$34*$D$38
Ricordiamo che i valori assoluti servono per poter liberamente trascinare la funzione nelle celle K36 e L36.
La prima parte di questa funzione, J34 (totale importo al 21%) /$M$34 (importo complessivo), calcola qual è la percentuale dell’importo totale cui si applica l’IVA al 21%. La seconda parte della formula, *$D$38, moltiplica la percentuale trovata per le spese di imballaggio.
Nota
Non preoccupatevi se ottenete come risultato di queste formule l’errore #DIV/O!. Questo avviene perché non ci sono dati e nella cella M34 c’è il valore O. La divisione per O genera sempre un errore. Più avanti in questo articolo vedremo come tenere nascosti questi errori “temporanei”.
Ora possiamo riportare i valori calcolati nella riga 34 e nella riga 36 nello schema della fattura, in particolare nelle celle A34, C34 e D34. In A34 la formula è =J34+J36 (in pratica, l’importo cui applicare l’IVA del 21% e la porzione di spese di imballaggio cui applicare la stessa IVA). In C34 è =K34+K36 e in D34 è =L34+L36.
Ora che sappiamo qual è il totale cui va applicata ciascuna aliquota IVA possiamo finalmente calcolare l’IVA da pagare per ogni aliquota. Lo facciamo nella riga 36. Ecco le formule per le celle A36, C36 e D36:
A36 ➔ =A34*21%
C36 ➔ =C34*10%
D36 ➔ =D34*4%
Non ci resta che sommare questi valori per scoprire quanta è l’IVA complessiva. Lo facciamo in A38 (=A36+C36+D36).
A questo punto possiamo anche calcolare il totale complessivo della fattura. Lo scriveremo in H36. La formula da usare è:
=A38+M34+D37+D38+D39
La formula somma il totale dell’IVA che abbiamo appena calcolato (A38) con l’importo totale che avevamo calcolato in M34; a questi aggiunge poi le spese di imballo (D38) e le spese documentate (D37) e non (D39).
Stampare la fattura
La nostra fattura è finita (più avanti nell’articolo proporremo alcuni “abbellimenti”, ma la sostanza c’è tutta). Passiamo a stamparla. Naturalmente, in stampa dobbiamo nascondere le colonne di appoggio J, K, L e M. Ci sono diversi sistemi per farlo, ma quello più semplice è quello di definire un’area di stampa.
Nota
Un altro sistema rapido per non stampare le colonne J, K, L e M è quello di nasconderle. Dopo averle selezionate per intero, con Excel 2007-2010, nella scheda Home del gruppo Celle, aprite il menu del pulsante Formato e scegliete Nascondi e scopri ➔ Nascondi colonne. Con Excel 2003 basta scegliere Formato ➔ Colonna ➔ Nascondi. Per scoprire le colonne nascoste, selezionate le colonne ai lati di quelle nascoste, poi ripetete la procedura per scegliere la voce Scopri.
In pratica, un’area di stampa è un intervallo di celle che l’utente sceglie di stampare escludendo tutte le altre celle del foglio di lavoro. Vediamo subito come farlo. Selezionate le celle da stampare (A1:H39) nel nostro esempio. Poi, con Excel 2007-2010, portatevi alla scheda Layout di pagina e, nel gruppo Imposta pagina, aprite il menu del pulsante Area di stampa e scegliete la voce Area di stampa.
Con Excel 2003, invece, scegliete File ➔ Stampa ➔ Imposta area di stampa.
Se provate l’anteprima di stampa, vi accorgerete che verrà stampata solo la porzione di foglio impostata come area di stampa. La struttura della fattura, comunque, non viene stampata su un’unica pagina, perché è troppo larga per essere stampata su un foglio A4 in verticale. In Excel 2007-2010 potete rendervene conto anche lavorando in visualizzazione Layout di pagina: una nuova visualizzazione, introdotta proprio con questa versione di Excel, che permette di vedere, mentre si lavora, il foglio di lavoro nel modo più simile a come apparirà in stampa, come visibile in figura 3.
Per attivare la visualizzazione Layout di pagina occorre portarsi alla scheda Visualizza, quindi nel gruppo Visualizzazioni cartella di lavoro (il primo a sinistra) si deve premere il pulsante Layout di pagina.
Nota
Per tornare alla visualizzazione normale basta premere il pulsante Normale, sempre nel gruppo Visualizzazioni cartella di lavoro della scheda Visualizza.
Da questa visualizzazione, portandosi alla scheda Layout di pagina della barra multifunzione, è molto facile ridurre le proporzioni della pagina in modo da adattarla alla dimensione di un foglio A4. Ci sono diverse possibilità.
Per esempio, è possibile ridurre gradualmente il valore mostrato nella casella Proporzioni del gruppo Adatta alla pagina fino a quando tutta la struttura della fattura non è visibile in un solo foglio (in questo caso occorre arrivare all’85% delle dimensioni originali). Oppure, è possibile lasciare inalterato il valore delle proporzioni e scegliere 1 pagina dagli elenchi a discesa Larghezza e Altezza, sempre nel gruppo Adatta alla pagina.
In questo modo sarà Excel a ridimensionare il contenuto del foglio quel tanto che basta perché si adatti al foglio A4.
Per un probabile baco, Excel 2007 non sempre mostra nella visualizzazione Layout di pagina il contenuto ridimensionato appena si impostano altezza e larghezza pari a una pagina. Bisogna prima attivare l’anteprima di stampa, quindi chiuderla per vedere la pagina aggiornata.
La visualizzazione Layout di pagina permette anche di aggiungere velocemente le intestazioni e i piè di pagina al foglio. Nel caso della fattura, nell’intestazione si potrà mettere il logo della ditta e nel piè di pagina i dati fiscali.
L’intestazione e il piè di pagina, come nelle precedenti versioni di Excel, sono divisi in tre blocchi: basta fare clic su di essi per poter cominciare a lavorare e inserire il contenuto, come mostrato in figura 4.
Excel 2003
Quanto abbiamo detto a proposito della regolazione della pagina con Excel 2007- 2010 è possibile anche con Excel 2003, solo che la procedura è meno immediata, perché non è possibile lavorare mentre si “vede” la pagina.
Innanzitutto, per ridimensionare il contenuto si deve ricorrere alla finestra Imposta pagina visibile in figura 5, che si può aprire scegliendo File ➔ Imposta pagina.
Nel riquadro Proporzioni potete procedere alla regolazione delle proporzioni come abbiamo visto con Excel 2007-2010. Se volete impostare manualmente le proporzioni, dovete inserire il valore, chiudere la finestra e lanciare l’anteprima di stampa per vedere il risultato (ed eventualmente riaprire la finestra per apportare le necessarie regolazioni).
Quanto all’inserimento di un’intestazione e di un piè di pagina, dovete portarvi alla scheda Intestazione/piè di pagina della finestra Imposta pagina.
Dall’elenco a discesa Intestazione potete scegliere di inserire nella vostra intestazione dei campi speciali di Excel. Ci sono molte possibilità, tra cui il numero della pagina, il nome e il percorso del file, il nome dell’autore (o meglio, dell’utente del PC che ha creato il file) e il nome del foglio.
Se preferite inserire nell’intestazione del testo personalizzato, premete il pulsante Personalizza intestazione. Excel vi mostrerà la finestra Intestazione, visibile in figura 6.
Excel divide l’intestazione in tre aree distinte: sinistra, centro e destra. Potete scrivere un testo in ognuna di queste aree. Una volta scritto il testo potete selezionarlo e formattarlo mediante la finestra Tipo di carattere, che potete attivare premendo il pulsante Carattere.
Per concludere, nella scheda Margini, visibile in figura 8, vi è una funzione molto utile: è possibile centrare il contenuto da stampare rispetto al foglio di stampa sia in orizzontale sia in verticale, semplicemente selezionando le opzioni Orizzontalmente e/o Verticalmente nell’opzione Centra nella pagina.
Centrare nella pagina con Excel 2007-2010
Questa funzione non è disponibile in modo immediato dalla barra multifunzione di Excel 2007-2010. Piuttosto, dovete fare clic sull’attivatore di finestra di dialogo, visibile in figura 8, del gruppo Imposta pagina, nella scheda Layout di pagina, per aprire la finestra di dialogo Imposta pagina, in tutto simile a quella vista a proposito di Excel 2003.
A questo punto potete procedere come abbiamo spiegato per Excel 2003 nella figura 7.
Nascondere gli errori “temporanei”
Adesso che abbiamo visto come gestire al meglio le stampe della nostra fattura o nota pro forma, torniamo al foglio Excel per apportare alcune migliorie.
Abbiamo visto che, finché non si inseriscono dei prezzi, il foglio di lavoro mostra degli errori, dovuti a una divisione per zero (li si vede, per esempio nella figura 3).
Abbiamo già spiegato come questi errori non siano un problema. Infatti, appena si comincia a compilare la fattura essi scompaiono. Un utente, però, potrebbe apparire disorientato e qualcuno potrebbe trovarli “brutti”.
Visto che nasconderli è molto rapido, perché non farlo?
Dobbiamo ricorrere alla formattazione condizionale. Per fare più in fretta, conviene selezionare tutto il foglio facendo clic sul quadratino all’incrocio dei righelli. Attivate poi la formattazione condizionale, e scegliete di utilizzare una formula per selezionare le celle da formattare, come visibile nella figura 9.
La formula da usare è:
=VAL.ERRORE(A1)=VERO
Nota
Ricordate sempre che, quando si imposta una formattazione condizionale con una formula per un gruppo di celle, la formula va scritta come se valesse solo per la prima cella dell’intervallo. Per maggiori informazioni sulla formattazione condizionale consultate l’articolo “La formattazione condizionale in Excel 2010“.
Una volta impostata la formula, scegliete il colore bianco per il carattere delle celle da formattare: in questo modo, tutte le celle che contengono un errore useranno un carattere bianco, quindi non visibile. L’errore, dunque, non viene eliminato, ma reso invisibile.
Recuperare i dati da un file esterno
Un’altra miglioria che potremmo apportare al nostro file consiste nell’impostare un sistema per recuperare i dati dei clienti, o quelli relativi ai prodotti, da un file esterno.
Immaginiamo di avere in un file esterno, chiamato Prodotti come quello visibile in figura 10, l’elenco dei nostri prodotti. Potremmo fare in modo che, quando l’utente scrive nella colonna A della fattura il codice di un prodotto, automaticamente si completino le colonne C con la descrizione e G col prezzo. Voi, quindi, potrete procedere allo stesso modo per recuperare i dati del cliente e riportarli nelle prime righe della fattura.
Per fare questo useremo la funzione CERCA.VERT (si trova fra le funzioni di Ricerca e riferimento), che permette di ricercare un valore contenuto nella prima colonna di una tabella e di restituire un valore che si trova nella stessa riga della tabella, ma in un’altra colonna.
In pratica, possiamo chiedere a Excel di ricercare nel file Prodotti il codice prodotto che abbiamo scritto nel file della fattura, quindi di scrivere la descrizione e il prezzo corrispondenti nelle colonne C e G del file della fattura.
La funzione CERCA.VERT richiede tre paramenti obbligatori:
- Valore: richiede il valore noto che stiamo cercando, in questo caso il codice del prodotto che si trova nella cella D2;
- Matrice_tabella: la tabella di ricerca, cioè la tabella dei prodotti. Il valore noto deve sempre essere posto nella colonna più a sinistra della tabella di ricerca. Per evitare problemi di aggiornamento dei riferimenti, se si trascina la formula, conviene sempre assegnare un nome a questa tabella e usare il nome come argomento della funzione;
- Indice: la colonna della tabella di ricerca che contiene il valore da riportare. La colonna va indicata non con la sua lettera, ma col numero corrispondente. Nel nostro caso, la descrizione del prodotto, per esempi, si trova nella colonna B, ossia la seconda (2) colonna della tabella di ricerca.
Il quarto argomento della funzione Intervallo è facoltativo, ma non per questo è meno importante. Questo argomento può assumere valore VERO o FALSO. Nel primo caso, la funzione CERCA.VERT esegue una ricerca approssimativa, ossia restituisce il valore successivo più grande che sia minore di valore a quello ricercato. Nel secondo caso, CERCA.VERT troverà una corrispondenza esatta. Se non specifichiamo nulla, Excel assume che il valore per l’argomento Intervallo sia VERO. Per il nostro tipo di ricerca, invece, ci occorre una ricerca esatta, quindi dovremo indicare il valore FALSO.
Dopo tutte queste spiegazioni teoriche passiamo all’applicazione pratica, che renderà tutto più chiaro.
Per prima cosa, conviene assegnare un nome alla tabella di ricerca, cioè alle celle piene del file Prodotti.
Selezionate dunque le celle piene del file Prodotti (A1:D107) e assegnate loro il nome Prodotti.
Questa volta non possiamo creare un intervallo dinamico, perché altrimenti saremmo obbligati a tenere aperto il file Prodotti quando compiliamo la fattura. Un intervallo dinamico, infatti, viene generato al volo da Excel e non esiste quando il file è chiuso.
Nota
Se volete comunque usare un intervallo dinamico e aprire sempre il file Prodotti, l’intervallo dinamico da creare è un po’ diverso da quelli che abbiamo usato finora, perché è composto da più colonne e non da una sola. Per definire l’intervallo, infatti, bisogna usare la formula:=SCARTO(perline!$A$1;0;0;
CONTA.VALORI
(perline!$A:$A);CONTA. VALORI(perline!$1:$1))
Nota
La novità consiste nel fatto che dobbiamo usare la funzione CONTA. VALORI anche per determinare la larghezza dell’intervallo da creare (CONTA.VALORI(perline!$1:$1)) e non solo la sua altezza. Insomma, si tratta di contare anche le celle piene della riga 1 (o di una riga qualsiasi, visto che sono tutte uguali) e non solo quelle della colonna A.
Una volta definito l’intervallo chiudete pure il file Prodotti e tornate alla vostra fattura. Per vedere il risultato delle funzioni che ora andremo a scrivere, provate a scrivere A15, il codice di uno dei prodotti del file Prodotti, in A17.
Siamo pronti a ricavare automaticamente la descrizione del prodotto con codice A15 in B17.
Fate clic in B17 e avviate l’inserimento della funzione CERCA.VERT. Se volte usare la finestra Argomenti funzione, fatelo come nella figura 11.
Se preferite scrivere la formula, di seguito ve la proponiamo per intero:
=CERCA.VERT(A17;‘C:\Percorso\Prodotti.xlsx’!prodotti;2;FALSO)
dove Percorso è il percorso del file Prodotti e prodotti è il nome dell’intervallo che abbiamo definito.
Naturalmente, 2 indica che dobbiamo riportare il valore della seconda colonna della tabella di ricerca.
A questo punto possiamo riproporre la funzione di ricerca nella colonna G della fattura per recuperare il prezzo del prodotto. Questa volta la formula da scrivere in G17 è:
=CERCA.VERT(A17;‘C:\Percorso\Prodotti.xlsx’!prodotti;3;FALSO)
Trascinate le funzioni verso il basso. Ora, quando scriverete il codice di un prodotto, esso verrà automaticamente recuperato dal file Prodotti anche se questo è chiuso. Quando riaprirete il file Fattura, sia Excel 2007-2010 sia Excel 2003 rileveranno che il file accede ai dati esterni e vi chiederanno come gestirli, come visibile nelle figure 12 e 13.
In entrambi i casi occorre confermare l’uso di dati esterni. Nella finestra di Excel 2003 e 2010, visibile nella figura 13, premete il pulsante Aggiorna, mentre con Excel 2007 dovete premere il pulsante Opzioni, quindi nella nuova finestra selezionate l’opzione Attiva contenuto e premete OK.
Proteggere le celle in cui non si deve scrivere
Dato che abbiamo fatto tanta fatica ad arrivare fin qui, potrebbe essere utile impedire le modifiche, anche accidentali, alle celle che contengono le formule. Di fatto, in questo file, l’utente deve lavorare solo nelle celle A1:H15, A17:B32, D17:F32 e D37:D39. Tutte le altre o sono vuote o vengono calcolate con formule.
Potremmo quindi bloccare le modifiche nelle celle che contengono le formule e permettere all’utente di lavorare solo nelle altre.
Vediamo come farlo. Innanzitutto, è bene ricordare che, perché una cella possa essere resa immodificabile, occorre che la si definisca come Bloccata. Per impostazione predefinita, tutte le celle del foglio di lavoro sono bloccate.
Per verificarlo, selezionate una qualsiasi delle celle (o tutte le celle) del foglio di lavoro, quindi aprite la finestra Formato celle (con Excel 2007-2010 scheda Home, gruppo Celle, menu del pulsante Formato, voce Formato celle; con Excel 2003 Formato ➔ Celle) e portatevi alla scheda Protezione, visibile in figura 14.
Vedrete che per tutte le celle del foglio è selezionata l’opzione Bloccata. Occorre, eventualmente, sbloccare le celle che volete rimangano modificabili anche dopo che è stata impostata la protezione.
Noi abbiamo bisogno di sbloccare tutte le celle e quindi di bloccare solo quelle che contengono delle formule.
Cominciamo sbloccando tutte le celle: fate clic sul pulsante Seleziona tutto, all’incrocio dei righelli, e aprite la finestra Formato celle alla scheda Protezione. Deselezionate l’opzione Bloccata, poi chiudete la finestra. A questo punto ci serve un sistema per selezionare automaticamente tutte le celle che contengono delle formule. Ecco come fare. Per prima cosa occorre selezionare il foglio di lavoro per intero facendo clic sul quadrato all’incrocio tra le intestazioni delle righe e delle colonne. Una volta selezionato il foglio, con Excel 2007-2010 portatevi nella scheda Home della barra multifunzione e, nel gruppo Seleziona e trova, aprite il menu del pulsante Trova e seleziona. Qui scegliete la voce Formule.
Con Excel 2003, invece, una volta selezionato il foglio, scegliete Modifica ➔ Vai. Excel vi mostrerà la finestra Vai a. Qui premete il pulsante Speciale. Visualizzerete la finestra Vai a formato speciale, visibile in figura 15.
Selezionate l’opzione Formule e premete OK.
A questo punto, sia Excel 2007-2010 sia Excel 2003 vi mostreranno tutte le celle che contengono delle formule selezionate, come visibile in figura 16.
Aprite di nuovo la finestra Formato celle alla scheda Protezione, vista in figura 14, e selezionate l’opzione Bloccata.
Nota
Se si seleziona anche l’opzione Nascosta le celle non solo non saranno modificabili, ma la formula non sarà nemmeno visibile. Quando farete clic su di esse la barra della formula rimarrà vuota.
Ora tutte le celle che contengono formule nel foglio di lavoro risulteranno bloccate e nascoste.
Perché il blocco abbia effetto bisogna chiedere a Excel di impostare la protezione per il foglio di lavoro.
Con Excel 2007-2010 portatevi alla scheda Home e, nel gruppo Celle, aprite il menu del pulsante Formato e scegliete la voce Proteggi foglio, mentre con Excel 2003 scegliete Strumenti ➔ Protezione➔ Proteggi foglio. Excel vi mostrerà la finestra Proteggi foglio, visibile in figura 17.
Qui dovete, innanzi tutto, scegliere quale tipo di operazioni permettere sulle celle bloccate. Per impostazione predefinita, è possibile solo selezionarle.
Potete eventualmente selezionare altre opzioni per permettere altre operazioni sulle celle bloccate. Se lo desiderate, potete anche inserire una password.
Una volta che avete definito tutte le impostazioni, premete il pulsante OK. Se avete impostato una password, vi verrà richiesto di digitarla nuovamente.
Se ora cercate di modificare le celle bloccate, Excel vi avviserà con un apposito messaggio. La protezione può essere rimossa in qualsiasi momento. Con Excel 2007-2010 portatevi alla scheda Home e, nel gruppo Celle, aprite il menu del pulsante Formato e scegliete la voce Rimuovi protezione foglio; con Excel 2003, scegliete Strumenti ➔ Protezione ➔ Rimuovi protezione foglio. Se avete impostato una password vi verrà chiesto di fornirla per poter procedere con la rimozione della protezione.
Autore: Alessandra Salvaggio – Tratto da: Problemi e soluzioni con Excel 2010 – Edizioni FAG Milano
