La funzione CERCA VERTICALE è sicuramente una delle funzioni maggiormente utilizzate, perché cercando un valore nella prima colonna di un intervallo di celle, ci restituisce il valore presente in un’altra colonna ma appartenente alla stessa riga.
In sostanza, nella tabella che vedete a video cercando il valore di un determinato articolo potremo farci restituire il quantitativo.
Tuttavia nel caso in cui avessimo un elenco contenente dei valori duplicati, cioè lo stesso articolo è presente su più righe, usando la funzione CERCA VERTICALE non saremo in grado di determinare la quantità del secondo duplicato, del terzo e così via dicendo.
Oggi vedremo un modo abbastanza semplice per superare questo ostacolo.
Vi ricordo che la funzione CERCA.VERT è già stata trattata.
Partiamo subito scrivendo la funzione nella cella H2
=CERCA.VERT($G$2;B:E;4;FALSO)
Se volete approfondire la differenza fra un riferimento assoluto ed uno relativo in descrizione QUI trovate il link.
Il risultato che abbiamo ottenuto è 30, che come potete vedere è il primo valore relativo all’articolo ART0003, ma se volessimo trovare il secondo? Oppure il terzo? Come possiamo fare con questa funzione?
Tra un attimo lo vedremo, prima facciamo un passaggio intermedio, andiamo ad evidenziare i duplicati. In questo modo avremo una visione più chiara di dove si trovano i valori di nostro interesse.
Qual modo migliore se non utilizzare la formattazione condizionale per evidenziare i duplicati? Anche in questo caso se volete saperne di più sulla formattazione condizionale in descrizione trovate il link.
Selezioniamo le colonne dalla A alla E, portiamoci in Formattazione condizionale, scegliamo Nuova regola, Utilizza una formula per determinare le celle da formattare e come formula scriviamo:
=$B1=$G$2
Abbiamo scritto la formula in questo modo perché andiamo a bloccare la colonna B con il $, ma non la riga che deve scorrere per essere confrontata con la cella G2, che in questo caso dovrà essere un riferimento assoluto.
Selezioniamo come Formato un riempimento di colore giallo diamo OK ancora OK.
Così facendo il risultato sarà quello di evidenziare l’intera riga che contiene il codice da ricercare. Nel caso specifico l’articolo ART0003 compare 4 volte.
Arriviamo ora a scrivere la formula che ci permetterà di determinare il numero del duplicato, quindi prestate attenzione.
Il nostro intento sarà quello di confrontare il contenuto delle varie celle della colonna B con l’articolo presente in G2, ogni volta che troveremo la corrispondenza esatta aumenteremo di una unità il valore, così facendo sapremo con esattezza il numero del duplicato.
portiamoci nella cella A2 e scriviamo:
=SE(B2=$G$2;MAX($A$1:A1)+1;0)
Ora trasciniamo questa formula anche nelle celle sottostanti, e come potete vedere ad ogni duplicato verrà assegnato un valore progressivo.
So che la formula può sembrare difficile da comprendere, per questo motivo portiamoci un attimo nella cella A7 e guardiamo i valori coinvolti per generare il risultato.
Come detto prima, la funzione MAX troverà il valore massimo presente nelle celle dalla A1 alla A6, che stanno sopra, in questo caso corrisponde a 2, e poiché il valore in B7 è identico all’articolo della cella G2 aggiungerà 1, portando questo valore a 3, che corrisponde infatti al 3° duplicato.
In G5 mettiamo il numero del duplicato che vogliamo controllare, per esempio 3.
Per trovare la quantità del 3° duplicato non ci resta che inserire la funzione CERCA VERTICALE nella cella H5 in questo modo:
=CERCA.VERT($G$5;A:E;5;FALSO)
Come potete vedere il risultato è 60 che corrisponde proprio al duplicato n° 3.
Tuttavia per non incappare in errori, consiglio di modificare la formula aggiungendo un controllo, che nel caso in cui il numero del duplicato fosse superiore ai duplicati effettivamente presenti restituisca 0
Ritorniamo in H5 e usando la funzione SE.ERRORE modifichiamo la formula in questo modo:
=SE.ERRORE(CERCA.VERT($G$5;A:E;5;FALSO);0)
Facciamo giusto un paio di prove per verificare che il tutto funzioni correttamente.
Cambiamo il duplicato da 3 a 4, ok il risultato della quantità è 100
Cambiamo l’articolo mettendo ART0001, e vedete che non avendo duplicati nella cella H2 il risultato è 10, mentre nella cella H5 il valore restituito è 0.
Oggi avete visto come poter usare la funzione CERCA VERTICALE quando in un elenco sono presenti dei valori duplicati.
Nel video qui sotto trovate tutti i passaggi appena illustrati, buona visione.
