In questo articolo voglio mostrarti come creare un listino prezzi Excel utilizzando la funzione CERCA.VERT (Cerca verticale).
L’obiettivo è mostrarti come utilizzare le funzioni di Excel per aiutarti a creare un esempio di listino prezzi.
Innanzitutto, vediamo di cosa abbiamo bisogno per realizzare il nostro prodotto finale.
Gli "ingredienti" sono i seguenti:
Dopo avere creato il listino prezzi andremo a realizzare un modulo d'ordine per i prodotti.
Utilizzeremo due fogli di lavoro:
- il foglio Prodotti che conterrà il nome degli articoli e i prezzi
- e il foglio Ordini che utilizzeremo per raccogliere gli ordini.
Bene, sei pronto per iniziare? Allora crea listino prezzi!
Creare listino prezzi Excel
Vediamo come fare un listino prezzi!
Partiamo dal foglio di lavoro Prodotti. Questo foglio conterrà il listino prezzi Excel.
A partire dalla cella A1 inseriamo la voce Prodotto. Nelle successive celle inseriremo tre tipologie di prezzo:
- Vendita (B1)
- Promozione (C1)
- Speciale (D1)
Ipotizziamo di offrire tre prezzi differenti sulla base del tipo di clientela.
Ora selezioniamo l’intervallo di celle da A1 a D1 e applichiamo una formattazione tabella.
Fai un clic sul comando Formatta come tabella presente nel gruppo Stili della scheda Home.
Dopo aver scelto lo stile, verrà mostrata la finestra di dialogo Formatta come tabella. Applichiamo il segno di spunta vicino alla scritta Tabella con intestazioni e facciamo un clic su OK.
Definiamo l’allineamento delle celle dal gruppo Allineamento.
Sarà sufficiente scegliere uno stile tabella ed Excel applicherà la formattazione alle celle.
Andiamo a specificare i formati alle celle:
- Testo (A2)
- Contabilità (B2:D2)
Il risultato sarà simile al seguente.
Ora assegniamo il nome alla tabella. Il nome che daremo alla tabella sarà Prezzi.
Nella casella di testo del gruppo Proprietà della scheda contestuale Progettazione degli Strumenti tabella digita il nome e conferma premendo il tasto Invio.
Nella tabella Prezzi andremo ad inserire i prodotti e i relativi prezzi come mostrato nell'immagine precedente.
Collegare il Listino prezzi Excel al foglio Ordini
Passiamo al foglio Ordini.
Ora andremo a creare un modulo per gli ordini a cui collegheremo il listino prezzi Excel. Faremo in modo di mostrare automaticamente il prezzo quando viene selezionato un prodotto nel modulo d'ordine.
All'interno del foglio di lavoro a partire dalla cella B6 inseriamo la voce Prodotto. Nelle celle successive (rispettivamente C6, D6, E6) le voci Quantità, Prezzo e Totale.
Impostiamo la larghezza delle colonne A e F a 4 e la larghezza delle colonne B:E a 24.
Selezioniamo l’intervallo di celle B6:E6 e applichiamo la formattazione tabella così come abbiamo fatto in precedenza.
Specifichiamo il formato delle celle nel modo seguente:
- Testo (B7)
- Numero (senza decimali) (C7)
- Contabilità (D7)
- Contabilità (E7)
Il passo successivo è creare l’automatismo che ci permetta di visualizzare il prezzo corrispondente al prodotto selezionato.
Selezionare una categoria di prezzo
Nel foglio di lavoro Ordini, aggiungiamo un elenco a discesa in cui è possibile selezionare tra le seguenti categorie di prezzi: Vendita, Promozione, Speciale.
Nella cella E3 applichiamo la convalida dati Excel. L’origine dati sarà l’intervallo di celle da B1 a D1 presente nel foglio Prodotti.
Ora, sempre facendo ricorso al comando Convalida dati, colleghiamo la cella B7 alla colonna Prodotto (della tabella Prezzi) presente nel foglio di lavoro Prodotti.
Utilizzo della funzione CERCA.VERT Excel
Spostiamoci alla cella D7. Questa cella conterrà una funzione che restituirà il prezzo corrispondente al prodotto selezionato.
Attraverso la funzione CERCA.VERT Excel, è possibile estrarre il prezzo del prodotto dalla matrice tabella. Ad esempio, se un cliente ordina una Scrivania, il prezzo mostrato sarà quello presente nella matrice tabella in corrispondenza della tipologia di prezzo (nell'esempio € 560,00 in quanto il prezzo appartiene alla tipologia Vendita).
Il nome della matrice è un riferimento denominato alla tabella Prezzi.
La funzione sarà la seguente.
La funzione appena inserita cerca il prodotto ([@Prodotto]) all'interno matrice tabella (Prezzi[#Tutti]) nella seconda colonna (2) con una corrispondenza esatta (FALSO).
In questo modo però la colonna è statica. Cosa succede se si desidera assegnare prezzi di vendita differenti ad alcuni clienti?
Dovremo apportare delle modifiche alla formula iniziale.
È in questo momento che entra in gioco la funzione CONFRONTA Excel.
Prima di inserire la funzione CONFRONTA andiamo a completare l’inserimento delle formule nella tabella degli ordini.
Inseriamo nella cella E7 la formula pe il calcolo del Totale ovvero la moltiplicazione tra il Prezzo e la Quantità.
Utilizzare la funzione CONFRONTA per ottenere una colonna variabile
Come accennato in precedenza, il prezzo restituito sarà sempre quello presente nella seconda colonna della matrice tabella. A noi serve un prezzo che vari in base alla selezione della tipologia di prezzo presente nella cella E3.
Per utilizzare i prezzi variabili, dobbiamo fare ricorso alla funzione CONFRONTA. Essa trova il numero della colonna corrispondente (intervallo di celle B1:D1 della tabella Prezzi nel foglio di lavoro Prodotti) al prezzo selezionato nella cella E3.
Se, ad esempio nella cella E3, si seleziona la voce Speciale, la funzione CONFRONTA restituirà 3. Poiché la voce Speciale si trova nella quarta colonna dovremo aggiungere il numero 1.
Quindi, 3 + 1 = 4 in quanto i prezzi della tipologia Speciale si trovano nella quarta colonna della tabella Prezzi.
A formula finale sarà la seguente:
Abbiamo nidificato la funzione CONFRONTA all'interno della funzione CERCA.VERT.
Per saperne di più sulle funzioni nidificate, leggi il seguente articolo:
Funzioni nidificate Excel: come usarle
La presenza delle parentesi quadre potrebbe complicare la comprensione della formula. Tuttavia, è solo una questione di lunghezza. Scomponi la formula in passaggi e dopo qualche istante tutto ti sarà più chiaro.
Se desideri approfondire la conoscenza delle tabelle Excel ti suggerisco di leggere i seguenti articoli:
Tabelle Excel: lavorare in modo efficiente
Prima di concludere l’articolo vorrei darti un ultimo suggerimento.
Noterai che quando inserisci una nuova riga alla tabella comparirà una scritta di errore nelle formule Excel. Niente di grave! L’errore svanirà non appena verrà inserito il nome del nuovo prodotto.
Tuttavia, per rendere il lavoro più pulito potrai utilizzare la funzione SE.ERRORE Excel per nascondere le scritte.
La formula diventerà lunga ed apparentemente più complicata ma il risultato sarà professionale!
A questo punto sei in grado di creare un listino prezzi con Excel! Spero abbia trovato quest’articolo utile.
Buon lavoro!
Grazie post utilissimo, mi ha aiutato a risolvere un problema.
Grazie tante!!!
Grazie a te Valter;)
Ciao Damiano, sono in una situazione simile al listino da te creato, anziché avere le tre colonne vendita, promozione e speciale ne ho molte di più (circa 25) quale funzione posso inserire per fare sempre un CERCA.VERT + CONFRONTA?
grazie.
Ciao Domenico,
potresti semplicemente estendere la tabella di origine e utilizzare il CERCA.VERT sui dati.
Inoltre dovresti estendere anche l'intervallo di Convalida dati.
Buongiorno Damiano, ti ringrazio per il post. Ho una domanda, ho creato un foglio con un elenco di vari dati in un altro invece una tabella che di volta in volta inserendo un codice mi si compila in automatico nei vari campi con il cerva vert.
Ti chiedo visto che se per vari motivi dovessi modificare un valore nell'elenco mi si aggiornano, giustamente, i corrispettivi nella tabella se è possibile far si che si aggiornino solo dopo la modifica e non a ritroso.
Grazie
Marco
Ciao Marco,
grazie per aver apprezzato l'articolo;)
Per rispondere alla tua domanda direi che purtroppo l'aggiornamento avviene "a ritroso".
Una soluzione potrebbe essere quella di avere il "vecchio e il nuovo" nella stessa tabella di origine.
Funziona tutto e la formula mi aiuterà moltissimo però riscontro un problema che non so come risolvere. Quando nel menu a tendina E3 seleziono il primo dei 3 prezzi non mi da nessun risultato, sul secondo mi esce il prezzo del primo e sul terzo il prezzo del secondo, come se fosse tutto slittato... Grazie!
Ciao Astrid,
potresti mostrarmi la formula che utilizzi?
In questo modo riesco ad individuare cosa no va per il verso giusto
Questa è la formula applicata alla mia cella. Ho effettuato gli altro passaggi in modo identico alla tua spiegazione ma evidentemente ho commesso qualche errore.
=SE.ERRORE(CERCA.VERT([@PRODOTTO];Prezzi[#Tutti];CONFRONTA($E$3;Prezzi[[#Intestazioni];[Prezzo RIVENDITORE]:[Prezzo VENDITA consigliato]];0)+1;FALSO);"")
Ciao Astrid,
la formula è corretta. L'errore potrebbe risiedere nell'origine della convalida dati.
Senza il file posso fare solo delle ipotesi.
Ti suggerisco di controllare l'origine dati della convalida oppure controllare la tabella con il listino prezzi.
Grazie, ho risolto! il motivo dell'errore è che avevo aggiunto una colonna tra il nome prodotto e il primo prezzo, contenente una immagine, che evidentemente dava problemi.
Benissimo;)
ciao Damiano complimenti per la spiegazione, ho fatto una prova e tutto funziona.
chiedevo soltanto una cosa , magari sbaglio io quando nel foglio ordini inserisco una seconda riga , se ho inserito nella tipologia di prezzo es. vendita( prima riga) posso variare la tipologia per la seconda riga( speciali) , senza che mi si modifichi la prima?
Ciao Vainer,
grazie per aver apprezzato l'articolo.
In realtà stai facendo tutto correttamente, purtroppo al variare della tipologia di prezzo variano tutti i riferiti. Tuttavia potresti aggiungere una colonna in cui puoi selezionare per ciascuna riga la tipologia di prezzo. In questo modo puoi ottenere un risultato differenziato