La funzione =SCARTO e la funzione =CONFRONTA.

Ovvero: come individuare una cella che sia all'incrocio di due dati in una tabella. 

Spesso accade che cerchiamo una soluzione ad un problema sfruttando le funzioni del foglio di lavoro che conosciamo di più, senza "ragionare" e trovare soluzioni usando funzioni che normalmente destineremmo ad altre necessità. E' il caso dell'esempio di questo articolo, dove, per trovare un valore in una tabella, attraverso la ricerca di due valori, uno posto in verticale e l'altro in orizzontale, saremmo sicuramente ricorsi a funzioni come il CERCA.VERT insieme al CERCA.ORIZZ.

Vediamo una tabella di esempio:

Classica tabella dove nel Range giallo avremo dei nomi, e nel range arancione avremo delle date, ma la tabella potrà essere impostata in qualsiasi modo a secondo delle nostre esigenze, l'importante comunque sarà avere una colonna e una riga iniziali che formino le intestazioni di riga e di colonna.

L'esercizio comunque sarà quello di ottenere il valore che si trova all'incrocio di un nome (le lettere in questo esempio) con una data. Supponendo che sia una tabella con importi fatture relativi a clienti,

• vorremo sapere l'importo fattura del cliente "d" relativo alla data del 31/12/03.

Intanto useremo una cella che ospiti la nostra formula, e sarà la A1, e poi dovremo usare altre due celle dove scrivere i due criteri di ricerca, quindi in B1 scriveremo "d" e in C1 scriveremo 31/12/03. E' evidente che variando i criteri nelle due celle B1 e C1, renderemo "variabile" la ricerca, ed il risultato in A1.

Non useremo, come sembrerebbe logico, la funzione CERCA.VERT per quanto riguarda la ricerca del nome, nè la funzione CERCA.ORIZZ per la ricerca della data, ma le funzioni SCARTO e CONFRONTA.

La funzione =SCARTO, che vuol dire "spostamento", (in inglese Offset), sappiamo che ci consente, posta una cella a cui riferirsi, di "mirare" ad un'altra cella (e quindi di ottenere il valore contenuto in quest'altra cella) usando dei numeri positivi o negativi per indicare di quante righe e quante colonne, a destra o a sinistra, verso l'alto o verso il basso, ci dobbiamo spostare rispetto alla cella in riferimento. Restituisce quindi un riferimento a un intervallo spostato rispetto a una cella o a un intervallo di celle di un numero specificato di righe e di colonne; esempio:

• =SCARTO(Cella da cui scostarsi ; n. di quante righe scostarsi ; n. di quante colonne scostarsi)

• =SCARTO(C5;2;3) - restituisce il valore che sarà in F7, cioè il valore della cella che si trova 2 righe sotto la C5, e 3 colonne a destra rispetto alla C5. Lo spostamento quindi viene determinato dai numeri che formano il secondo e il terzo argomento della funzione SCARTO. Il primo di questi due argomenti identifica sempre le righe, ed il secondo identifica sempre le colonne, e si dovranno usare SEMPRE dei numeri (oppure qualcosa che restituisca un numero). L'impiego di numeri indica anche la direzione dello "spostamento": i numeri positivi causano uno spostamento verso il basso, per quanto riguarda le righe, e verso destra per le colonne; i numeri negativi causano uno spostamento verso l'alto per quanto riguarda le righe, e verso sinistra per quanto riguarda le colonne. Se il nostro esempio fosse =SCARTO(C5;-2;-2) otterremo il valore che sarà nella cella A3 (due righe sopra la C5 e due colonne a sinistra della C5).

La funzione =CONFRONTA, che parrebbe dal nome che restituisse il termine di un confronto tra due valori, restituisce invece un numero che identifica la posizione del confronto trovato. Ci dice la Guida in linea: "Restituisce la posizione relativa di un elemento, in una matrice che corrisponde a un valore specificato in un ordine particolare. Utilizzare la funzione CONFRONTA invece di una delle sintassi della funzione CERCA quando è necessario determinare la posizione di un elemento all'interno di un intervallo piuttosto che l'elemento stesso". Se noi sostituiamo la parola matrice con un suo sinonimo, cioè "elenco", o meglio "intervallo di celle", forse capiamo meglio il significato delle spiegazioni: "Restituisce la posizione relativa di un elemento, in un' "intervallo di celle" che corrisponde a un valore specificato....". Questa "posizione relativa" altro non è che il numero della posizione che il valore rappresenta all'interno dell'intervallo: in pratica, la lettera "d" della tabella sopra, è al quarto (numero 4 quindi) posto nell'intervallo B5:B9.

Cominciamo a capire ora che se nella funzione SCARTO, anziche scrivere un numero, inseriamo un'istruzione CONFRONTA, è come se si indicasse comunque un numero (quello appunto restituito da CONFRONTA).

Ma vediamo prima la sintassi della funzione CONFRONTA :

• CONFRONTA(valore;matrice;corrisp) - dove valore può essere un valore qualsiasi scritto direttamente nella formula, oppure può essere un riferimento ad una cella che contiene un valore da confrontare. matrice è l'intervallo delle celle dove andare a cercare valore. Corrisp  è il numero -1, 0 o 1. Corrisp specifica il modo in cui Microsoft Excel deve confrontare valore con i valori contenuti in matrice :

• Se corrisp è 1, CONFRONTA troverà il valore più grande che è minore o uguale a valore. È necessario che i valori in matrice siano disposti in ordine crescente: ...-2; -1; 0; 1; 2;...A-Z; FALSO; VERO.

• Se corrisp è 0, CONFRONTA troverà il primo valore che corrisponde esattamente a valore. I valori in matrice possono essere disposti in qualsiasi ordine.

• Se corrisp è -1, CONFRONTA troverà il valore più piccolo che è maggiore o uguale a valore. È necessario che i valori di matrice siano disposti in ordine decrescente: VERO; FALSO; Z-A;...2; 1; 0; -1; -2;... e così via.

• Se corrisp è omesso, verrà considerato uguale a 1.

Traducendo le spiegazioni in una formula, potremo scrivere quindi, inserendo in B1 il valore da confrontare con quelli presenti nell'intervallo verticale B5:B9, e cercando la corrispondenza precisa, questa formula:

• =CONFRONTA(B1;B5:B9;0) e se in B1 avremo scritto "d", otterremmo come restituzione della formula il valore 4. cioè la lettera d scritta in B1 è stata trovata nella quarta posizione dell'intervallo scelto (in B8)

Lo stesso faremo per il valore che scriveremo in C1, che andrà "confrontato" (e quindi "trovato") con l'altra zona della tabella, cioè con l'intervallo C4:H4, ed anche qui avremo in restituzione il numero relativo alla posizione che la data trovata avrà nell'intervallo:

• =CONFRONTA(C1;C4:H4;0) - ed otterremo 4 come risultato restituito.

Senza accorgercene, abbiamo ottenuto due numeri, che possono rappresentare i due famosi argomenti relativi allo spostamento da usarsi con la funzione SCARTO. E se in A1 noi piazzeremo questa formula, otterremo finalmente il risultato voluto, cioè 190, vediamo la formula:

• =SCARTO(B4;CONFRONTA(B1;B5:B9;0);CONFRONTA(C1;C4:H4;0))

Che è come se avessimo scritto:

• =SCARTO(B4;4;4)

Ovviamente variando i valori nelle celle B1 e /o C1, varierà anche il risultato della formula in A1. Abbiamo quindi ottenuto una sorta di DOPPIO CERCA.VERT che potrà tornare particolarmente utile a quanti stanno cercando una soluzione del genere.

L'idea non è mia, io mi sono limitato a tradurla in "pellegrinese" . Potete trovare questa e molte altre funzioni e suggerimenti visitando il sito www.cpearson.com/excel.htm (tutto in inglese). Quindi un sentito grazie all'autore.

Buon lavoro.

prelevato sul sito www.ennius.altervista.org