Cerca verticale: esempio di cerca vert su fogli diversi

samuele agnolin
  • 26 luglio 2016
  • livello: intermedio   3 minuti
cerca vert su fogli diversi

Esempio pratico sull’utilizzo del cerca verticale su più fogli.
Da un elenco di vini recuperiamo il prezzo unitario basandoci sul codice del vino.

.

Questo articolo tratta una situazione particolare del cerca vert, quella in cui la matrice tabella si trova in un altro foglio. Se hai bisogno delle nozioni di base sulla funzione puoi leggere anche il tutorial sul cerca vert.

Partiamo da due fogli: listino prezzi e ordini.

Listino prezzi: elenco dei vini con varie informazioni, tra cui codice, nome e prezzo unitario.

listino prezzi per cerca vert

Ordini: elenco che nel tempo si popola degli ordini dei vini registrando il codice del vino, la data dell’ordine e il numero delle bottiglie

elenco ordini

Obbiettivo: nel foglio Ordini scrivere una formula che ci permetta di calcolare il prezzo totale sulla base del codice vino ordinato e del relativo prezzo unitario.

Soluzione: con il cerca vert troviamo il prezzo unitario del vino ordinato e moltiplichiamo per il numero di bottiglie.

I passaggi sono i seguenti:

 A  Ci posizioniamo sul foglio Ordini e scriviamo (o richiamiamo dall’elenco delle funzioni) il CERCA.VERT

cerca vert passaggio 1

 B  Popoliamo i parametri del cercavert specificando il valore da cercare (A4), la matrice tabella (l’elenco dei vini del foglio ‘Listino prezzi’), il numero della colonna del prezzo unitario (5) e il fatto che vogliamo una corrispondenza esatta (0)

cerca vert parametri

La formula è quindi:

=CERCA.VERT(A4;’Elenco vini con prezzi’!A$4:E$69;5;0)

Da notare che usare il cerca vert su fogli diversi non è diverso rispetto ad utilizzarlo su un unico foglio. Excel infatti nella matrice tabella scrive il nome del foglio prima dell’intervallo delle celle.

 C  Moltiplichiamo poi il risultato per il numero di bottiglie, posto nella cella accanto a quella dove stiamo inserendo la formula

prezzo per numero bottiglie

Questo quindi il risultato finale:

risultato finale

Veniamo ora ad alcune domande che potrebbero sorgerci rispetto a quanto trovato finora.

Se io mi preparo la formula nella prima cella e poi trascino per qualche decina di righe in modo da poter in futuro scrivere le informazioni del nuovo ordine e veder calcolato in automatico il prezzo ottengo #N/D nelle righe dove non ho ancora inserito ordini. Perché? Come posso evitarlo?
Come mostrato nel tutorial sull’utilizzo standard del cerca vert, l’errore #N/D indica che il cerca vert non riesce a trovare il codice che stiamo cercando. In questo caso è come se stesse cercando un codice inesistente.

errore se codice mancante

Possiamo evitare che scriva #N/D utilizzando la funzione SE.ERRORE ed impostando il valore restituito in caso di errore uguale a stringa vuota

=SE.ERRORE(CERCA.VERT(‘Ordini risultato finale’!A4;’Elenco vini con prezzi’!A$4:E$69;5;0);””)

se errore stringa vuota

Se io scrivessi un codice di vino non presente nel listino prezzi?
In questo caso il cerca verticale non trova il codice e mi restituisce il risultato #N/D.

errore-nd

Potremmo gestire la cosa informando dell’assenza di quel codice:

=SE.ERRORE(CERCA.VERT(‘Ordini risultato finale’!A4;’Elenco vini con prezzi’!A$4:E$69;5;0);”codice non presente nel listino”)

errore nd personalizzato

Se noi avessimo l’esigenza di attribuire un prezzo standard a tutti i vini che non sono presenti nel ‘Listino prezzi’? Questo perché magari ipotizzo che se il codice non c’è nel listino, il vino è di quelli “sfusi” che vendiamo a 5 euro a bottiglia.

Potremmo attribuire il prezzo direttamente nel parametro se_errore, scrivendo:

=SE.ERRORE(CERCA.VERT(‘Ordini risultato finale (2)’!A11;’Elenco vini con prezzi’!A$4:E$69;5;0);5)

prezzo costante se codice non trovato

Se invece che scrivere un codice di vino non presente nel listino prezzi sbagliassimo a scrivere il codice? Potremmo ad esempio scrivere 59ù al posto del 59, invece che pigiare il tasto INVIO pigio il tasto acccanto… Come fare per capire che quello è un errore di digitazione?

Potremmo fare due tipi di controlli.
1. il valore è numerico? se non lo è c’è qualcosa che non va
2. il valore è numerico ma è superiore al numero del codice vino più alto?

1. il valore è numerico?
Con la funzione TIPO verifichiamo se il dato è numerico (avremmo il risulato 1) o meno. Con la funzione SE diciamo che se non è numerico il codice è errato.

=SE(TIPO(A8)>1;”codice errato”;CERCA.VERT(‘Ordini varianti’!A8;’Elenco vini con prezzi’!A$4:E$69;5;0))

codice non numerico

2. il valore è numerico ma è superiore al numero del codice vino più alto?
Ipotizziamo che il valore più alto sia 1000. Con un SE valutiamo se il codice è superiore o meno a 1000.

=SE(A8>1000;”codice errato”;CERCA.VERT(‘Ordini varianti’!A8;’Elenco vini con prezzi’!A$4:E$69;5;0))

codice errato se maggiore mille

Da notare che questa scrittura risolverebbe anche il controllo 1. Excel valuterebbe infatti un codice testuale come superiore a 1000 ed a qualsiasi altro numero.

Se ti è piaciuto questo capitolo del tutorial iscriviti, se ti va, alla newsletter! :-)

Se hai bisogno delle nozioni di base sulla funzione puoi leggere anche il tutorial sul cerca vert

46 Comments

You can post comments in this post.


  • Bravo! Hai trovato il modo di rendere semplice una funzione spiegata in maniera criptica dalla stessa Microsoft. Grazie e bravo (ancora)!

    Giuseppe Lunetta 2 anni ago Reply


    • Grazie per i complimenti Giuseppe! Mi fa molto piacere che tu abbia trovato utile l’articolo e che tu abbia voluto spendere del tempo per farmelo sapere, molto gentile :-)

      samuele 2 anni ago Reply


  • E se uso solo codici alfanumerici come posso cercare ugualmente il relativo prezzo?

    Filippo 2 anni ago Reply


    • Ciao Filippo. Che il codice del vino sia numerico o alfanumerico poco cambia secondo me. Dovrebbe funzionarti seguendo la stessa logica. Fammi sapere se non dovesse essere così. O inviami pure un estratto dei tuoi dati a samuele@amicoexcel.it

      Se vuoi trovi qualche esempio ulteriore di applicazione della funzione nel tutorial sul cerca vert.

      samuele 2 anni ago Reply


  • Grande Filippo! Mi sfuggiva una cosa, ma mentre ti scrivevo l’ho risolta perchè ho capito dove sbagliavo… e cioè, se mi preparo la formula nella prima cella e la trascino, ovviamente la formula delle celle sotto avrà numeri crescenti!! Quindi se partivamo da A4:B69 nelle celle sottostanti automaticamente si avranno A5:B70….A6:B71 ecc. con una perdita di celle da esaminare nella MATRICE_TABELLA! A meno che si “DIGITI” ( invece di selezionare semplicemente le celle col muose) il simbolo “$”….A$4:B$69 in modo tale che l’area MATRICE_TABELLA resti sempre la stessa. E’ corretto?

    PIPPO 2 anni ago Reply


    • Ciao! Corretto. Devi usare il simbolo del dollaro :-) La matrice tabella va “bloccata” altrimenti scendendo i riferimenti vengono incrementati.

      samuele 2 anni ago Reply


  • Ciao e complimenti! finalmente esempi più chiari. io sono autodidatta … insomma mi arrangio, ho dei dati che devo “copiare” tra due file differenti ma non riesco a fargli capire da che file leggere il dato da copiare, il percorso insomma… grazie mille

    SIMO 2 anni ago Reply


    • Ciao Simona. Grazie! Mi stai chiedendo come fare per far capire al cerca vert che la matrice tabella si trova in un altro file? Se è così allora apri il file con i dati a cui vuoi far riferimento, poi quando stai scrivendo il cerca vert e sei nel secondo parametro prova a spostarti sul file con i dati e selezionare l’area che ti interessa… per spostarti clicca semplicemente nella barra delle applicazioni, come faresti per spostarti tra due file…

      samuele 2 anni ago Reply


  • complimenti per il tutorial! a me succede che con listini contenuti funzioni tutto alla perfezione, invece con listini con molte “righe” (ho listini da 15/16 mila righe) i riferimenti vanno a casaccio! immagino sia un problema di excel o sbaglio qualcosa?

    Marco Pagani 2 anni ago Reply


    • Ciao Marco. Non è un problema di dollari vero? Intendo relativi al bloccare la matrice tabella?

      samuele 2 anni ago Reply


  • Ciao e complimenti per il tutorial. Davvero esplicativo!
    Io ho un problema con un cerca.vert che restituisce un valore che non è presente nelle colonna da cui fare la restituzione. Mi spiego meglio. Uno il cerca.vert cercando un codice presente su foglio 1 nella tabella (bloccata con i $) presente nel foglio 2 e chiedo la restituzione del valore contenuto in una certa colonna. Si tratta di uno 0 o un 1. Niente di più ordinario!
    Fino ad un certo punto il cerca.vert mi restituisce i dati corretti…mentre successivamente continua a restituire 0 anche se c’è 1. Da questo deduco che sta trovando il codice cercato, altrimenti mi darebbe #N/D, ma piuttosto che 1, come contenuto nella cella da restituire, mi da 0.
    Ho provato a riformattare le celle, ma nulla.
    A cosa può essere dovuto? :-SSS
    Grazie

    Erika 1 anno ago Reply


    • Ciao Erika, che sia solo un problema di dollari nella matrice tabella? Mandami uno stralcio del file se credi.

      samuele 1 anno ago Reply


  • Ciao, io già utilizzo questo che tu hai molto bene spiegato ma ora mi chiedevo se è possibile trascinare la formula e in ogni cella dove ho trascinato deve andare a cercare in un foglio diverso.. Devo dirgli di effettuare la stessa ricerca in una ventina di fogli senza dover riscrivere la formula e ottimizzarla in ogni foglio. Dello stesso file ovviamente.

    Giuseppe 1 anno ago Reply


    • Potrebbe essere fattibile… secondo me giocando con il concatena, o meglio con il simbolo di & commerciale, e la funzione INDIRETTO.. nel senso che all’interno del CERCA.VERT potresti tentare di “comporti” il riferimento alla matrice tabella… tipo =CERCA.VERT(A2;INDIRETTO(“nome_foglio”&”!”&A1:G100);…

      Se vuoi mandarmi uno stralcio del tuo file vedo la situazione particolare… Ciao!

      samuele 1 anno ago Reply


  • Ciao, complimenti per tutte le tue spiegazioni, premetto che è da molto tempo che uso Excel ma ho iniziato a scoprire le funzioni da poco!
    Ho un problema su un Cerca.Vert che devo creare,ho più fogli ( matrice ) dove cercare la “Descrizione “che mi serve. Ma non riesco a fare funzionare la formula per cercarla nei 2 fogli. Ne trova sempre uno e non l’altro…

    Fabio Costantini Cuoghi 1 anno ago Reply


    • Ciao Fabio, mandami pure un esempio di file così te lo risolvo direttamente lì…. Grazie.

      samuele 1 anno ago Reply


  • Ciao e complimenti per l’articolo! Vorrei utilizzare questa funzione per gestire un file di gestione lavoro dove uno user A scrive sul file A selezionando le risorse, l’alto file B cerca verticale sul suo nome e restituisce alcuni valori. Allo stesso tempo il file A riprendere tramite un altro cerca verticale lo stato dei lavori che l’altro è utente compila. Spero di essere stato abbastanza chiaro. La mia domanda è l’aggiornamento dei dei come avviene? Uso la versione 2013 e non credo sia in tempo reale. Grazie per la risposta!

    Giordano 1 anno ago Reply


    • Intendevo l’aggiornamento dei dati con qualche frequenza avviene e se si può intervenire in qualche maniera.

      Giordano 1 anno ago Reply


      • Grazie Giordano! Ti chiedo cortesemente di inviarmi un file sintetico a samuele@amicoexcel.it. Grazie, velocizziamo così il tentativo di arrivare ad una soluzione :-)

        samuele 1 anno ago Reply


  • Mi aggrego ai complimenti. veramente molto utili i consigli

    Francesca 1 anno ago Reply


    • Grazie mille Francesca!

      samuele 1 anno ago Reply


  • Fantastico! Sei l’Aranzulla di Excel! Finalmente ho risolto l’errore che mi dava la formula cerca.vert. Grazie, grazie, grazie :)

    Lucia 1 anno ago Reply


    • Ottimo Lucia! L'”Aranzulla di Excel” è molto carina :-) che onore!

      samuele 1 anno ago Reply


  • Complimenti in maniera chiara ed esauriente hai illustrato sta funzione in maniera semplice ed intuitiva rispetto alla “munnezza” d’informazioni che si trova sparsa in internet !!!!

    Grazie grazie grazie

    Vincenzo 11 mesi ago Reply


    • Grazie mille Vincenzo. Gentilissimo. Mi fa proprio piacere!!!

      samuele 11 mesi ago Reply


  • Ciao Samuele , io invece ho qs problema…ho convertito un file pdf in excel.. tt ok..Perche se in un altro file excel usando il CERCA.VERT.. dove ricerco un testo scritto uguale anche su quello generato dal pdf , mi da N/D? se vado a riscrivere il nome manualmente nella cella del file generato dal pdf.. funziona. cosa bisogna fare?

    ETTORE STABILINI 11 mesi ago Reply


    • Ciao Ettore. Excel vede i due contenuti diversi. Ci sono spazi? Ci sono caratteri strani?

      Prova con la funzione LUNGHEZZA a farti restituire il numero di caratteri.

      Se ci sono spazi usa la funzione ANNULLA.SPAZI. Se ci sono caratteri strani prova la funzione LIBERA.

      Fammi sapere.

      samuele 11 mesi ago Reply


      • risolto!! ho usato la funziona ANNULLA.SPAZI come suggerito..grazie mille davvero gentilissimo. complimenti. ciaooo

        ETTORE STABILINI 11 mesi ago Reply


      • grazie samuele della tua risposta,come inserisco il dollaro nella formula?
        Qui sotto ti copio la formula con un accenno a cio’ che dovrei fare:
        Dovrei trovare i codici bilancia di alcuni prodotti che stanno in un foglio A ,il codice articolo di questi prodotti e’ il dato che hanno in comune i due fogli.vado su una cella del foglio B clicco ” inserisci funzione”–cerca vert.Nel campo “VALORE” seleziono tutta la colonna dei dati in comune
        (copio questi codici nella prima colonna a sin dopo avere numerato in alto dal 1 in poi le colonne .),poi nel campo” MATRICE” seleziono tutto il foglio A(dove devo ricercare i valori che mi servono) nel campo indice metto il numero della colonna del foglio A che contiene i codici bilancia che cerco ed infine nel campo INTERVALLO Digito FALSO In maiuscolo.
        a questo punto nella prima cella appare il codice bilancia corretto che cerco ma come ti spiegavo se copio nelle celle successive in basso mi da’ #RIF!
        ora ti incollo qui la formula che mi appare nella barra superiore
        .VERT(‘elenco plu gastr. 17-3-2012′!A5:A552;’elenco plu gastr. 17-3-2012’!#RIF!;11;FALSO)
        Scusa ancora se abuso della tua pazienza
        grazie ancora
        Emilio

        EMILIO 11 mesi ago Reply


        • Ciao Emilio. Due cose:

          1) su valore non serve selezionare tutta la colonna ma solo la cella del valore che devi trovare. Quindi invece che ‘elenco plu gastr. 17-3-2012′!A5:A552 basta ‘elenco plu gastr. 17-3-2012′!A5
          Questo cmq non è la causa dell’errore, è più una miglioria tecnica

          2) il problema dipende dal fatto che nel secondo parametro, la matrice tabella, tu selezioni tutto il foglio. Così facendo Excel ti scrive qualcosa come nome_foglio!Foglio1!1:1048576
          Quando tu trascini in basso i riferimenti vengono incrementati ma così è come se finissi fuori del foglio :-) quel 1048576, che è il numero di righe totali del foglio, non può aumentare, Excel ti fa capire con #RIF! che c’è qualche cosa che non va.

          Risolvi così: invece di selezionare tutto il foglio (immagino tu vada a cliccare sul pulsante sopra 1 e a sinistra di A) seleziona le colonne che ti interessano….parti da A (se lì si trova il codice articolo) e seleziona fino all’ultima colonna da cui ti serve estrarre valori.

          Se così non funziona mandami pure uno stralcio del file a samuele@amicoexcel.it

          samuele 11 mesi ago Reply


      • grazie samuele
        provero’ a fare cosi’ appena rientro e ti faccio sapere!!
        Sei un grande e grazie ancora della disponibilita’…a presto!!!!

        EMILIO 10 mesi ago Reply


  • CIAO COMPLIMENTI PER LA TUA PAGINA
    ho un problema:imposto la ricerca verticale in una cella e mi trova il valore giusto per quella cella;se copio nelle altre celle mi da’ l’errore RIF, dove sbaglio’ ?
    grazie in anticipo
    Emilio

    EMILIO 11 mesi ago Reply


    • Grazie Emilio!

      Solitamente l’errore #RIF nel cerca.vert viene dato quando si inserisce nel terzo parametro un numero di colonna superiore al numero di colonne della matrice tabella.

      Però nel tuo caso l’errore ti compare solo quando trascini. Mi vengono in mente due possibilità: nella tua formula il terzo parametro è frutto di una espressione che, trascinata, ti crea problemi oppure trascinando qualche riferimento si aggiorna perdendoti i riferimenti definiti nella prima cella.

      In entrambi i casi potresti forse risolvere bloccando con il dollaro.

      Verifica e casomai riportami la formula che utilizzi così magari vedendola ti posso dare qualche info in più.

      samuele 11 mesi ago Reply


  • Grazie Samuele
    ho seguito la tua spiegazione e il problema sulla ricerca verticale e’ risolto.
    grazie ancora per la tua disponibilta’
    sei stato preziosissimo
    a presto e grazie
    Emilio

    EMILIO 10 mesi ago Reply


    • Mi fa molto piacere Emilio. Grazie a te per il tuo gentile feedback!

      samuele 10 mesi ago Reply


  • È possibile utilizzare il cerca.Bert, utilizzando come prima colonna una lista di prodotti con un menù a tendina? In pratica: scegliendo il prodotto dal menù a tendina nella colonna A, dovrebbe comparire il prezzo nella colonna B.
    Grazie mille.
    Roberto

    Roberto Pagnozzi 9 mesi ago Reply


    • Buonasera Roberto. Direi di si. Se lei ha un elenco di riferimento con prodotti e prezzi può inserire nella colonna B il cerca vert che avrà come valore quel che è stato scelto nella tendina. Quindi =CERCA.VERT(A2;intervallo_tabella_di_riferimento;colonna_prezzo_in_tabella_riferimento;0).

      Se ha modo di provare mi faccia sapere.

      samuele 9 mesi ago Reply


  • Ho scritto questa formula
    CERCA.VERT(C4;’indirizzi Consorziati’!A:H;3;FALSO)
    per estrarre il numero civico da un elenco di indirizzi. Quando il numero non è presente mi estrae 0(zero) invece che lasciare il campo vuoto.
    come posso correggere la formula? anticipatamente ringrazio dell’aiuto oltre a esprimere ammirazione per quanto si legge nel sito.

    Marcello Torbol 9 mesi ago Reply


    • Grazie Marcello :-)

      Si potrebbe risolvere in più modi:

      1) con un SE che verifichi se il CERCA.VERT restituisce zero… tipo =SE(CERCA.VERT(A2;B11:C12;2;0)=0;””;CERCA.VERT(A2;B11:C12;2;0))

      2) modificando il formato della cella: si imposta il formato personalizzato e nella casella del formato si digita 0;-0;;@
      Sai che i quattro valori, divisi da punto e virgola, rappresentano rispettivamente il formato da dare ai numeri positivi, ai numeri negativi, agli zeri ed al testo
      0 significa ripeti i numeri… @ significa ripeti il carattere testuale

      Su questo secondo metodo ti rimando, se hai tempo, ad una pagina ufficiale Microsoft :https://support.office.com/en-us/article/display-or-hide-zero-values-3ec7a433-46b8-4516-8085-a00e9e476b03

      Nella pagina trovi anche altri sistemi… occhio che sul formato personalizzato loro suggeriscono 0;;;@ ma io non sono d’accordo del tutto… si perderebbero i numeri negativi così come lo scrivono.

      Fammi sapere se riesci a risolvere. Ciao!

      samuele 9 mesi ago Reply


  • Geniale. GRAZIEEEEEEEE
    Ho modificato la formula con i riferimenti della mia tabella, e cambiando il parametro =0 con =” ” (segue la formula )
    SE(CERCA.VERT(C4;’indirizzi Consorziati’!A:I;3;0)=” “;””;CERCA.VERT(C4;’indirizzi Consorziati’!A:I;3;0))

    questo non bastava ed allora ho formattato le colonne con il formato personalizzato 0;-0;;@ ed improvvisamente mi ha restituito i dati nel modo corretto in cui volevo fossero estratti.
    mi piace lavorare con excel e sicuramente approfitterò ancora
    di nuovo un immenso grazie.

    Torbol Marcello 9 mesi ago Reply


  • Salve ho digitato la formula cerca vert in questo modo

    =CERCA.VERT(D63;'[90 Italy Serie C – Group A.xlsm]ITA C – A’!$F$13:$EM$260;138;FALSO)

    e fin qui tutto ok.
    Il mio problema sta nel fatto che il nome del file a cui faccio riferimento lo vorrei prendere da una cella di una colonna di appoggio

    e pertanto ho riscritto la formula nel seguente modo

    =CERCA.VERT(D63;'[K63]ITA C – A’!$F$13:$EM$260;138;FALSO)

    giovanni 6 mesi ago Reply


    • Ciao Giovanni. Devi usare la funzione INDIRETTO costruendo al suo interno la stringa con la &. Quindi qualcosa tipo:
      =CERCA.VERT(D63;INDIRETTO(“‘”&K63&”ITA C – A’!$F$13:$EM$260”);138;FALSO)

      samuele 6 mesi ago Reply


  • Grazie per la chiarissima spiegazione!
    Ho bisogno di completare un’analisi urgentemente ma al momento il mio cercaverticale continua a non funzionare come dovrebbe.

    -2 tab dello stesso file in uso. nessuna delle tab dispone di un valore univoco per cui per individuare il “valore da ricercare” ho fatto concatena di tre colonne in entrambe le tab di ricerca – al momento unica apparente differenza sono il “/” invece che “-” nelle date ( che sono parte del concatena)

    -se metto FALSE a fine formula: continuo ad avere #N/A come risultato del cerca verticale e temo il problema sia con il formato dati ( le due tabs da cui sto lavorando sono state scaricate da due software diversi ma sembra che cambio formato nn avvenga anche volendolo applicare)

    -se metto TRUE a fine formula e la trascino, vengono fuori tutti valori uguali ( ma corretti solo per la prima riga)

    La matrice l’avevo bloccata con i dollari quindi nn dovrebbe essere questo il problema. Come si fa in questi casi?
    Grazie mille!
    Sara

    Sara 5 mesi ago Reply


    • Ciao Sara, dovresti lasciare FALSO nel CERCA.VERT e fare in modo che le date siano scritte nello stesso modo, magari modificando il divisore con la funzione SOSTITUISCI, tipo =SOSTITUISCI(A2;”-“;”/”)

      samuele 5 mesi ago Reply


  • Salve avrei bisogno di un aiuto con excel.
    Ho 2 colonne G ed H dove in G ho una lista di giocatori mentre in H ho le squadre,vorrei che in automatico nelle colonne A-B-C-D si generino le squadre.Grazie

    Ivano 2 settimane ago Reply


    • Salve Ivano, con che criterio devono essere generate le squadre?

      samuele 6 giorni ago Reply


Inserisci un commento