Funzioni Foglio di lavoro: SE - CERCA.VERT - SOMMA.SE - CONCATENA - VAL.VUOTO.

per le spiegazioni delle singole funzioni rimando agli articoli relativi.

Spesso nei nostri lavori dobbiamo usare formule condizionali (SE) impostate su più verifiche. La varietà di situazioni da affrontare è talmente varia, che è impensabile gestire esempi per tutte le occorrenze, ma questo esempio che vedremo, può fornire spunti sufficienti per capire come si possono impostare più funzioni in una unica formula.

Questo esercizio, proveniente da una domanda rivoltami, si basa su una tabella dati presente su un foglio, che funge da database, sotto vediamo l'impostazione della tabella:

e da un'altra tabella sul foglio 2 che funge da "riepilogo" impostato sul Campo "Nome". Si vuole cioè, per ogni Nome, avere sulla stessa riga i "Voti" ottenuti per ogni "Giornata" e, sempre per ogni Nome il totale dei "Gol", vediamo sotto l'impostazione della tabella riepilogo:

Come si nota, a partire dalla riga 2, ci troviamo con i campi Nome e a seguire, i numeri delle "Giornate" di cui si vuole il "Voto" . Classico impiego quindi della Funzione CERCA.VERT per ottenere, cercando il Nome, il valore correlato che ci interessa.  Solo che esistono tre problemi:

  • primo: alla funzione CERCA.VERT dovremmo far trovare il Nome, che per la riga 3 è Abruzzese, nella tabella sul foglio1, e come si vede nella prima immagine, il nome Abruzzese figura più volte. Questo rappresenta un ostacolo alla ricerca perchè sappiamo che la funzione CERCA.VERT cerca e trova SEMPRE la prima occorrenza prevista, dall'alto verso il basso, non sarebbe cioè in grado di reperire i valori per tutti gli altri Abruzzese.

  • secondo: se giriamo il problema, e anzichè il Nome, facciamo la ricerca impostata sul numero della Giornata, ci troveremmo sì ad avere una unica giornata presente per un determinato Nome, ma avremmo anche il problema accennato sopra in quanto esistono più Giornate uguali, tante quanti sono i Nomi diversi.

  • terzo: la funzione CERCA.VERT  richiede come argomento per la ricerca una sola "chiave di ricerca". Non è possibile usare due chiavi di ricerca (dovremmo far cercare la corrispondenza ad un "Nome" e contemporaneamente ad un numero di "Giornata").

  • soluzione (parziale): usiamo la Funzione CONCATENA. Per poter usare questa funzione, che unisce in un unico valore più valori separati, abbiamo bisogno di aggiungere una colonna alla prima tabella (che sarà la A), quella sul foglio 1, dove uniremo la Giornata con il Nome, creando quindi un valore univoco, come vediamo nell'immagine sotto: nella colonna A, a partire dalla cella A3 è stata inserita la formula =CONCATENA(B3;C3), che poi per trascinamento abbiamo inserito in tutte le righe sottostanti interessate. Come si nota, abbiamo ottenute chiavi univoche: 1Abruzzese, 1Adriano, 1ecc.ecc  ne esistono uno solo. Saranno questi valori concatenati che useremo come chiave di ricerca.

La nostra preparazione non è ancora finita. Come si nota nell'immagine sopra, il Nome Abruzzese è presente solo nelle Giornate 1 - 3 e 5, questo provocherà un errore #N/D nella cella, sul foglio2, che ospiterà la formula CERCA.VERT di ricerca della Giornata 2 e 4, in quanto saranno Valore Non Disponibile, cioè non esistono. Per questo useremo la Funzione VAL.NON.DISP, che vedremo un poco più avanti. Riepiloghiamo ora la situazione sul foglio 2 : a partire dalla cella B3 e fino alla F3, e poi verso il basso a seguire, inseriremo le formule (cella rossa e seguenti). Come impostiamo la formula?

Iniziamo a vedere la formula di base; all'area del foglio 1 che interesserà la ricerca, viene assegna il nome "zona", cioè da A3 ad E2000 (in totale 5 colonne, fino alla colonna "Voto" compresa:

=CERCA.VERT(CONCATENA($B$2;A3);zona;5;FALSO)

in parole semplici la spiegazione è la seguente: Cerca nella colonna A del foglio1 il valore corrispondente al valore che è nella cella B2 UNITO (Concatena) al valore che è nella cella A3 (quindi = 1Abruzzese),  se lo trovi, mi riporti il valore che troverai nella stessa riga dove si troverà 1Abruzzese, ma nella colonna 5, e vogliamo che la ricerca della chiave sia esatta (FALSO). In questa formula usiamo un riferimento assoluto alla cella B2 in quanto con il trascinamento verso il basso, Excel non aggiornerà questo riferimento ma solo quello della cella A3 che diventerà A4, A5, A6 ecc. ecc. Questa formula andrà poi inserita anche nelle celleC3 e seguenti fini alla F3, con l'unico accorgimento di aggiornare il riferimento all'intestazione di colonna, CONCATENA($C$2;A3), CONCATENA($D$2;A3) ecc, in quanto dovremo ottenere 2Abruzzese, 3Abruzzese, ecc.ecc.

Con la sola formula vista sopra, però avremmo un errore in quelle celle nelle quali la formula va a cercare, esempio, 2Abruzzese, e non lo trova perchè non presente. E' questo il caso in cui dovremo istruire una formula condizionale : SE trovi il valore, mi riporti lo stesso, altrimenti mi lasci la cella vuota. Per questo useremo la funzione VAL.NON.DISP. La sua sintassi è:

 VAL.NON.DISP(val) - dove (val) è il valore da esaminare. Val può essere una cella vuota, un valore logico, numerico, di errore, di testo o di riferimento oppure un nome che si riferisce a uno di questi valori che si desidera esaminare.

Nel nostro caso, (Val) è l'intera formula appena vista, e per impostare una formula con funzione SE, abbiamo bisogno di indicare per prima la condizione da verificare (quindi se il valore NON è disponibile, cosa fare in questo caso, altrimenti cosa fare) e la costruzione dell'intera formula è:

=SE(VAL.NON.DISP(CERCA.VERT(CONCATENA($B$2;A3);zona;5;FALSO));"";CERCA.VERT(CONCATENA($B$2;A3);zona;5;FALSO))

anche qui, in parole semplici si dice: Se eseguendo la ricerca trovi il valore (chiave) non presente, mi lasci la cella (della formula) vuota (i due doppi apici), altrimenti (se il valore cercato è presente) mi esegui la funzione CERCA.VERT.

Finiamo la panoramica con l'esame della formula inserita nella cella H3, dove si vuole la somma dei gol relativi al nome. In questo caso non occorre usare CONCATENA, usiamo infatti una semplice SOMMA.SE , anche in questo caso abbiamo usato un nome per definire le celle con i soli nomi dei giocatori sul foglio 1, quindi con molta fantasia è stata chiamata "giocatori"; questa la formula, che verrà trascinata verso il basso.:

=SOMMA.SE(giocatori;A3;Gol)

 

Buon lavoro

prelevato sul sito www.ennius.altervista.org