Come anticipato in copertina, con questa esercitazione imparerai a gestire:
- alcuni calcoli articolati
- la Convalida Dati
- il CERCA.VERT per la compilazione automatica dei campi anagrafici
- il formato celle SPECIALE-CAP per i CAP inizianti per ‘0’
- la formula SE per mantenere pulite le celle vuote
- la Formattazione Condizionale per colorare automaticamente il SALDO in base al segno
Inoltre:
- ripasserai l’utilizzo dei riferimenti Assoluti
- gestirai l’attivazione e la disattivazione della griglia di sfondo, a seconda delle esigenze
Pronti per iniziare con la gestione della fattura? Si parte!
Puoi usare i link per una navigazione più agevole:
INFORMAZIONI PRELIMINARI
ANAGRAFICA CLIENTE – Versione Semplice
ANAGRAFICA CLIENTE – con Convalida Dati
ANAGRAFICA CLIENTE – Completamento Campi Automatico
FORMATO SPECIALE CAP
ANAGRAFICA PRODOTTO
CORPO PRINCIPALE DELLA FATTURA
CALCOLI DEL DOPPIO IMPONIBILE
CALCOLO DELLA DOPPIA IVA
COME APPLICARE LA FORMATTAZIONE CONDIZIONALE
PUNTI DI ATTENZIONE
Informazioni Preliminari
Cominciamo con le indicazioni preliminari:
- Per l’unificazione delle celle e l’inserimento e la manipolazione dei bordi, rimandiamo alla precedente esercitazione.
- Per chi volesse partire da uno schema simile a quello proposto, ma privo di formule, per lavorare solo sull’aspetto logico dell’esercitazione, metto a disposizione il link per scaricare il file: modello fattura
- Per togliere la griglia che Excel che marca visivamente la suddivisione in celle: scheda Visualizza -> togliere la spunta dal quadratino dedicato:
ANAGRAFICA CLIENTE – Versione Semplice
Excel Fattura Doppia Aliquota – Anagrafica Cliente
Per chi volesse scegliere la soluzione più semplice, si potrà procedere alla compilazione manuale della cella Cognome, anche se questa opzione implica la possibilità di commettere errori di battitura indesiderati. L’alternativa che suggeriamo prevede l’utilizzo dello strumento di Convalida Dati che consente all’utente di selezionare esclusivamente dei cognomi già esistenti in un elenco precedentemente fornito.
Anagrafica cliente -Cognomi- Convalida Dati da Elenco
ANAGRAFICA CLIENTE – con Convalida Dati
Applichiamo la Convalida Dati al campo Cognome.
Selezioniamo la scheda Dati e cerchiamo il Gruppo Comandi Strumenti Dati:
clicchiamo sul pulsante Convalida Dati:
A questo punto si aprirà una popup come mostrato in figura. Selezioniamo la voce Elenco dal menù a tendina del campo Consenti e infine, per attingere all’elenco da cui estrarre il Cognome, clicchiamo sulla freccetta nera al fondo del campo Origine:
Ovviamente è necessario predisporre preventivamente l’elenco da cui attingeremo i cognomi. Nel nostro caso, abbiamo creato un foglio Excel dedicato all’anagrafica Clienti nella stessa cartella Excel della fattura, scaricabile attraverso il file modello fattura
Selezioniamo il foglio Clienti come da immagine:
Il foglio Clienti si presenta come segue:
All’interno del foglio, selezioniamo esclusivamente la lista dei cognomi da considerare; la Convalida Dati rileverà in automatico la codifica delle celle selezionate:
A questo punto confermiamo con un INVIO da tastiera e e cliccando sul pulsante OK della popup principale che verrà riproposta. Il risultato è mostrato in figura:
Cliccando sulla frecciolina a destra del campo, verrà esposto l’elenco dei cognomi disponibili per la scelta.
ANAGRAFICA CLIENTE – Completamento Campi Automatico
Una volta effettuata la scelta del Cliente, l’utente dovrà provvedere a compilare i rimanenti campi dell’Anagrafica (ad es. Indirizzo, Città, P.IVA ecc). Le possibilità anche in questo caso sono due:
- completamento manuale
- completamento automatico
Il completamento automatico, oltre a ridurre la possibilità di commettere errori in fase di compilazione, consente di velocizzare l’operazione. Noi ci concentreremo su quest’ultima opzione.
Ovviamente sarà necessario predisporre preventivamente una scheda Clienti con tutti i dati necessari. Per questa esercitazione basterà utilizzare il modello già predisposto per voi modello fattura in cui è stata compilata un’anagrafica Clienti nel foglio omonimo.
A questo punto vogliamo che, inserendo (o variando) il cognome nella fattura, automaticamente tutti i campi relativi al cliente vengano compilati (o modificati) coerentemente, senza l’intervento umano:
Come ottenere la compilazione automatica? Semplice: impostando delle formule nelle celle da compilare.
La formula per la Compilazione Automatica si basa sulla funzione CERCA.VERT Vediamo come utilizzarla adeguatamente.
La formula CERCA.VERT va a ricercare un valore (nel nostro caso, il cognome “Bianchi”) all’interno di una tabella (nel nostro caso l’anagrafica clienti predisposta nel foglio excel clienti) e quando lo trova, restituisce un valore relativo alla stessa riga, per cui potrebbe restituire l’indirizzo oppure il CAP oppure la partita IVA… del cliente specifico (Bianchi).
La formula CERCA.VERT funziona utilizzando 4 parametri separati da punto e virgola, che sono:
- la cella contenente il valore da ricercare in tabella
- la tabella contenente il cognome e gli altri dati di interesse relativi ai clienti
- l’indice della colonna della tabella che vogliamo estrarre
- il quarto valore da inserire sarà sempre 0 o, equivalentemente FALSO
I primi due parametri possono essere inseriti nella formula sia manualmente che automaticamente. Cliccando sulla singola cella o selezionando graficamente la tabella di interesse al momento opportuno, la funzione CERCA.VERT rileverà automaticamente la codifica dei campi selezionati purché l’utente abbia cura di inserire il separatore ‘;‘ tra un parametro e l’altro. Gli ultimi due parametri andranno inseriti manualmente.
Vediamo come impostare la formula per la valorizzazione automatica del campo indirizzo (cella C6):
=CERCA.VERT(C5;clienti!A2:F7;2;0)
I parametri sono valorizzati come segue:
- C5 è la cella della fattura che contiene il cognome da ricercare nella tabella Clienti
- clienti!A2:F7 è la codifica rilevata automaticamente dalla formula a seguito della selezione della tabella dati. La tabella che abbiamo predisposto per contenere i dati Anagrafici del cliente si trova appunto nel foglio Clienti e va dalla cella A2 alla cella F7 (basterà selezionarla come da immagine)
- 2 è l’indice della colonna della tabella che vogliamo estrarre: infatti nel nostro caso l’indirizzo è memorizzato nella seconda colonna della tabella
- 0 oppure FALSO sta ad indicare che se anche trovassimo una riga di un cliente simile a quello cercato, non dovremmo considerarla, ma dovremmo piuttosto procedere a ricercare il cliente di interesse nelle righe successive. Ad esempio, se esistesse un cliente “Bianchini”, che contiene la parola “Bianchi”, dovremmo ignorarlo.
Analogamente la formula da impostare nella cella C7, cioè la cella relativa al CAP, sarà:
=CERCA.VERT(C5;clienti!A2:F7;3;0)
ovvero basterà variare semplicemente l’indice della colonna da estrarre. Questa volta anziché la seconda, relativa all’indirizzo, vogliamo estrarre la terza, quella relativa al CAP.
In maniera analoga agiremo anche per compilare le restanti celle anagrafiche.
FORMATO SPECIALE CAP
Per quanto riguarda l’anagrafica cliente, un’attenzione speciale va dedicata al campo CAP in quanto potrebbe dover contenere un CAP principiante per zero. In questo caso, se il campo non fosse formattato adeguatamente, la cella eliminerebbe di default gli zeri iniziali.
Per questo motivo suggeriamo caldamente di formattare il campo CAP (nel nostro caso cella C7) come segue:
- selezionare la cella
- cliccare col tasto destro
- selezionare la voce Formato Celle dal menù contestuale
- nella popup che si aprirà selezionare:
- Categoria: Speciale
- Tipo: C.A.P.
- confermare col tasto OK, come mostrato in figura
ANAGRAFICA PRODOTTO
Gli accorgimenti descritti per la parte relativa all’anagrafica Cliente, sono applicabili anche all’anagrafica Prodotto.
In particolare, per esempio, la Convalida Dati è auspicabile per il campo Articolo, mentre il completamento dati attraverso la funzione CERCA.VERT è particolarmente adeguata per i campi Prezzo e IVA.
Anche in questo caso, ovviamente, andrà preventivamente disposta una tabella completa di tutti i dati di interesse sui prodotti. Ricordiamo che il file con lo schema e i fogli Clienti e Articoli è disponibile al link modello fattura
CORPO PRINCIPALE DELLA FATTURA
Per il corpo della fattura è stato deciso quanto segue:
- il campo Articolo verrà valorizzato tramite Convalida Dati dall’elenco incluso nel foglio articoli
- il campo Quantità verrà valorizzato manualmente
- il campo Prezzo verrà valorizzato con funzione CERCA.VERT dalla tabella inclusa nel foglio articoli
- il campo Sconto verrà inserito manualmente
- il campo Importo verrà valorizzato con apposita formula che prevede di moltiplicare il prezzo per la quantità e sottrarre lo sconto relativo
- il campo IVA verrà valorizzato con funzione CERCA.VERT dalla tabella inclusa nel foglio articoli
Nel dettaglio, le formule da applicare alla prima riga prodotto (riga 12) saranno le seguenti:
-campi “Prezzo” / “IVA”:
=SE(C12<>””;CERCA.VERT(C12;articoli!A$2:C$12;2;0);””)
=SE(C12<>””;CERCA.VERT(C12;articoli!A$2:C$12;3;0);””)
-campo “Importo”:
=SE(C12<>””;E12*D12*(1-G12);””)
Adesso analizziamo ciascuna formula e capiamo il significato di ogni sua parte.
Il campo Prezzo potrebbe essere semplicemente gestito con la formula CERCA.VERT(C12;articoli!A2:C12;2;0)
analogamente anche il campo IVA, sostituendo semplicemente l’indice 2 col 3.
Al secondo parametro della formula vengono però aggiunti i simboli dollaro:
articoli!A$2:C$12
in modo da evitare che trascinando la formula sulle righe sottostanti, per i prodotti successivi, i riferimenti alla tabella nella quale cercare i dati degli articoli vengano alterati.
Inoltre, la funzione CERCA.VERT viene inglobata nella funzione SE che non altera il valore delle celle valorizzate, ma evita che la formula applicata alle celle vuote, generi dei caratteri spuri come quelli mostrati nell’immagine:
La formula complessiva dunque applica il CERCA.VERT se la cella dell’articolo in oggetto risulta diversa (ovvero <>) dalla cella vuota (ovvero “”), altrimenti lascia vuota la cella (ovvero “”).
Ciò è valido sia per le formule applicate nei campi Prezzo e IVA che per la formula applicata all’Importo.
Per dettagli sul funzionamento della formula SE rimandiamo all’esercitazione precedente.
CALCOLI DEL DOPPIO IMPONIBILE
Al fine di gestire la doppia IVA, dobbiamo distinguere le componenti dell’imponibile al 15% dalle componenti dell’Imponibile al 22%. A questo scopo predisponiamo due differenti colonne, quelle mostrate in rosso nell’immagine precedente. Nella prima delle due faremo confluire le componenti dell’Imponibile al 15% mentre nella seconda faremo confluire le componenti dell’Imponibile al 22%.
Per la prima riga prodotto (riga 32) vale quanto segue.
La colonna relativa all’imponibile al 15% (colonna J) sarà gestita con la seguente formula:
=SE(H12=0,15;F12;0)
cioè la cella J12 verrà valorizza con l’Importo della riga, nel nostro caso 41,4 (cella F12), solo se il campo IVA (H12) è uguale a 0,15 (cioè 15%), altrimenti sarà posta a ‘0’
Analogamente la prima riga della colonna relativa all’imponibile al 22% sarà gestita così:
=SE(H12=0,22;F12;0)
e, conseguentemente, verrà valorizza con l’Importo della riga (F12) solo se il campo IVA del prodotto (H12) è uguale a 0,22, cioè 22%, altrimenti sarà posta a ‘0’
Quindi l’importo di ciascun prodotto finirà esclusivamente in una delle due colonne, esattamente quella relativa all’aliquota IVA dell’articolo.
Entrambe le formule saranno trascinate in basso per predisporre i calcoli automatici anche su tutte le sottostanti righe della fattura in cui potrebbero essere inseriti, pur successivamente, articoli da fatturare.
Infine al fondo delle righe fatturabili predisponiamo i campi per i Totali:
Nella cella F32, in marrone, abbiamo inserito il totale della colonna Importo:
=SOMMA(F12:F31)
Nelle celle J32 e K32, abbiamo inserito rispettivamente la somma degli Imponibili al 15% e la somma degli Imponibili al 22%:
=SOMMA(J12:J31) e =SOMMA(K12:K31)
CALCOLO DELLA DOPPIA IVA
Nelle celle J32 e K32 abbiamo ottenuto rispettivamente l’Imponibile da IVAre al 15% e l’Imponibile da IVAre al 22%.
Adesso utilizziamo questi valori per gestire il calcolo dell’IVA nel riquadro C35:E38.
Riportiamo nella cella C37 il valore del primo Imponibile:
=J32
e riportiamo nella cella C38 il valore del seconda Imponibile:
=K32
Scriviamo manualmente nelle celle D37 e D38 i rispettivi valori di IVA da applicare;
Effettuiamo infine il calcolo dell’IVA nelle celle E37 e E38, come segue:
=C37*D37
e
=C38*D38
Infine, il totale delle quote IVA da pagare sarà dato dalla somma dei valori contenuti in queste ultime due celle e lo scriveremo nella cella E39, colorata di marrone:
=SOMMA(E37:E38)
Adesso abbiamo quasi finito i calcoli, ci resta semplicemente da definire il Totale Fattura; lo inseriremo nelle celle G41-H41, precedentemente unificate e formattate per mostrare uno sfondo e un colore del testo differente in base al segno algebrico del Saldo: nel caso di un netto effettivamente da pagare (valore positivo) i colori saranno rossi, nel caso di un netto a ricevere (segno negativo) i colori saranno verdi.
In ogni caso la formula inserita nella cella in oggetto sarà:
=SOMMA(F32;E39)
in modo da sommare il totale dell’Imponibile al totale IVA appena calcolato.
Come applicare la Formattazione Condizionale
Per formattare lo sfondo e il testo della cella relativa al Totale, sulla base del segno del valore contenuto, dobbiamo procedere come segue:
- selezioniamo la cella (G41-H41)
- dalla scheda Home, raggiungiamo il gruppo Comandi Stili e selezioniamo il comando Formattazione Condizionale, come mostrato in figura
- selezioniamo le voci Regole di evidenziazione celle – Maggiore di
- inseriamo 0 nel campo valore e selezioniamo riempimento rosso e testo rosso scuro dal menù a tendina
- confermiamo con OK
- ripetiamo nuovamente le operazioni con le seguenti variazioni:
- Regole di evidenziazione celle – Minore di
- riempimento verde e testo verde scuro
- Infine verifichiamo la situazione delle Regole inserite (è anche possibile apportare eventuali variazioni) cliccando sulla cella e selezionando la voce Gestisci regole
FASE 2:
Formattazione Condizionale
FASE 3:
FASE 7:
Il pannello di controllo apparirà come segue:
Come evidente dall’immagine, i pulsanti a disposizione permetteranno di effettuare eventuali modifiche, aggiunte o eliminazioni di regole di gestione.
Punti di Attenzione
Ricapitolando, i punti di attenzione per la fattura sono:
-CONVALIDA DATI (campi: cognome cliente e nome prodotto, valorizzati a scelta da un menù a tendina)
-CERCA.VERT (campi degli attributi del cliente e del prodotto da rilevare automaticamente)
-formato celle SPECIALE-CAP: per valori di CAP che cominciano per ‘0’
-RIFERIMENTI ASSOLUTI per evitare trascinamenti indesiderati che causano errori durante i riempimenti automatici
-formula SE per evitare che la fattura sia sporcata nei campi relativi ad oggetti non valorizzati. Mi consente di mantenere attive le formule anche in assenza di inserimenti correnti
Le formule principali applicate, invece sono:
-campi “Prezzo” / “IVA”:
=SE(C13<>””;CERCA.VERT(C13;articoli!A$2:C$12;2;0);””)
-campo “Importo”:
=SE(C13<>””;E13*D13*(1-G13);””)
-colonne importi da IVAre al 15 / 22%:
=SE(H13=0,15;F13;0)
=SE(H13=0,22;F13;0)