Posizione di un valore all'interno di un intervallo (Funzione CERCA.VERT, Funzione CONFRONTA)

Nel post precedente, senza entrare nei dettagli, ho anticipato l'uso di un'altra funzione: Confronta

Questa funzione è molto utile per determinare la posizione di una lettera, un numero o una parola intera all'interno di un intervallo..

Vediamo un esempio semplice in  figura 1:

=CONFRONTA("c";A1:A4;0)

Figura 1

La sintassi è la seguente:

=CONFRONTA(valore; matrice; [corrisp])

Nell'esempio cerco la c (valore), che essendo una stringa va indicata tra virgolette, all'interno di un range A1:A4 (matrice) che contiene "a  b  c  d" e restituisce il numero 3 che è la posizione della lettera c all'interno del suddetto range.

corrisp può assumere tre valori:

0  deve esserci la corrispondenza esatta con il valore cercato
1  il valore massimo tra i valori minori o uguali a valore. Nella matrice i valori devono essere in ordine crescente
-1 il valore minimo tra i valori maggiori o uguali a valore. Nella matrice i valori devono essere in ordine decrescente

In figura 1 si può vedere un esempio con Corrisp=1 e si può notare che viene restituita la posizione del numero 46 che è il valore più alto tra i valori minori o uguali a 48 ( e cioè 29, 34 e appunto 46)

Vi chiederete a cosa serva questa funzione, al di là di questi esempi banali.

La funzione Confronta è molto utile se combinata con la funzione Cerca.vert , quando non si conosce la posizione di una colonna per poter definire l'indice.

Per la spiegazione completa della funzione Cerca.vert rimando a questo post:

Ma vediamo un esempio:

Figura 2

=CERCA.VERT(valore; matrice_tabella; indice; [intervallo])

diventa =CERCA.VERT(C7;A1:C4;3;0)

Riassumendo in breve, attraverso il Cerca.vert cerchiamo il valore 2 (nella cella C7) nella prima colonna sinistra di un intervallo (A1:C4) per trovare un valore nella stessa riga in una determinata colonna (3).

L'elemento che determina la colonna che ci interessa è l'indice.

Se sappiamo già la colonna che ci interessa, o comunque l'intervallo non è soggetto a continue variazioni, possiamo indicare direttamente il numero (nel nostro esempio 3, cioè la terza colonna a partire dall'inizio dell'intervallo).
Se invece prevediamo che l'intervallo possa essere soggetto a variazioni (se viene inserita una colonna, tutti gli indici ovviamente restano sfasati), o non siamo sicuri del numero della colonna, possiamo ottenere sempre il risultato corretto utilizzando la formula Confronta.

Vediamo come:

Figura 3

La colonna che ci interessa è quella del Cognome.

Perciò tramite la Funzione Confronta cerchiamo il nome del campo Cognome (D6) nell'intestazione dei campi del database (A1:C1) e indichiamo come corrisp 0 per avere la corrispondenza esatta nella ricerca.

=CONFRONTA(D6;A1:C1;0)

Questa formula restituisce 3 che è esattamente il numero di colonna che cercavamo.

A questo punto è sufficiente sostituire il 3 con questa formula:

=CERCA.VERT(C7;A1:C4;3;0)

diventa

=CERCA.VERT(C7;A1:C4;CONFRONTA(D6;A1:C1;0);0)

La prossima volta vedremo anche la funzione Confronta combinata con la funzione Indice

Menu a tendina dinamico (Formattazione condizionale)

Nel post precedente abbiamo concluso l'inserimento dei tre menu a tendina dinamici, ossia un menu a tendina e due menu dipendenti.

Come dicevo al termine del post, si pone il problema che se cambio la selezione nel primo e nel secondo menu, i menu successivi non si aggiornano e restano delle selezioni incongruenti.

Una soluzione può essere la formattazione condizionale.

Ne avevo già parlato qui, ma oggi vedremo delle opzioni diverse.

Ci posizioniamo quindi nella cella C2 e inseriamo la formattazione, come in  figura 1:

Menu a tendina dinamico (Convalida dati, Funzione INDIRETTO) - terzo menu

Eccomi qua per la terza parte del post sulla convalida dati.

Dove eravamo rimasti? Abbiamo costruito delle tabelle con un elenco di negozi e quindi abbiamo stabilito quali prodotti possiamo trovarci.

Nel primo post abbiamo inserito un menu  a tendina, per poter scegliere da un elenco il negozio che ci interessa e quindi, nel secondo post, abbiamo inserito un secondo menu a tendina, dipendente dal primo, che permette di selezionare solo la categoria merceologica venduta in quel negozio.

Il terzo passaggio consiste nell'attribuire i singoli prodotti ai vari negozi e vedremo come inserire un terzo menu che consenta di selezionare solo i prodotti relativi a uno specifico negozio e ad una particolare categoria merceologica.