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.

Menu a tendina dinamico (Convalida dati, Funzione INDIRETTO)

Nel post precedente abbiamo visto come inserire in un file un Menu a tendina.

Ora vedremo come far dipendere i dati visualizzati in un Menu a tendina in base alla scelta operata in un altro Menu a tendina. 

Prima di procedere, però, dobbiamo nominare l'elenco delle categorie merceologiche presenti nei diversi negozi.

• Attribuzione categorie merceologiche ai negozi

Vi mostrerò come sia possibile nominare le zone tutte insieme, risparmiando tempo e noia.

    Figura 1

Dopo aver inserito in fianco ai vari negozi la classe merceologica dei prodotti venduti, selezioniamo tutta l'area e scegliamo Crea da selezione. Quindi lasciamo spuntato solo Colonna sinistra. Premendo Ok avremo nominato tutte le zone in un colpo solo ed il nome sarà quello inserito nella colonna di sinistra e cioè il nome del negozio.
Ho anche un'altra chicca da mostrarvi sul Nominare una zona, ma ci torneremo dopo, quando vedremo che quello che abbiamo fatto finora ha ancora ampi margini di miglioramento.

Ora saremmo in grado di inserire il menu a tendina dinamico se non dovessimo prima aprire una breve parentesi e illustrare la funzione INDIRETTO che utilizzeremo in seguito.

La Funzione INDIRETTO permette di costruire una formula, prendendo le varie parti che la compongono da punti diversi del file.

Un esempio semplice è il seguente:

    Figura 2

Nella cella A1 ho scritto ecco! e nella cella E1 ho inserito A1.
Se nella cella E2 inserisco la formula =INDIRETTO (E1), il risultato di tale formula è ecco!
La formula INDIRETTO interpreta ciò che è inserito nella cella E1 come un riferimento ad una cella  (A1) e restituisce quindi il risultato della formula che si viene a creare (corrisponde cioè alla formula diretta =A1).

Un altro esempio di utilizzo di questa funzione è quello di costruire una funzione un pezzo alla volta, come nell'esempio:

    Figura 3

Nella cella E4 ho inserito la parola Tabella. Nella cella sottostante ho costruito la formula puntando alla cella E4 e così ottengo il foglio (Tabella), aggiungo, compreso tra due &, il punto esclamativo e quindi completo la formula indicando il riferimento di cella (A2 tra apici, altrimenti lo interpreta come riferimento di cella diretto).
In questo modo ottengo lo stesso risultato della formula diretta =Tabella!A2. Come si può vedere nella Figura 1, nella cella A2 del foglio Tabelle c'è la scritta Neguno.

Ora siamo pronti a inserire il nostro Menu a tendina dinamico e a vedere un altro utilizzo della suddetta formula.

• Inserimento Menu a tendina dinamico

Dove eravamo rimasti? Nel post precedente abbiamo inserito una Convalida dati nella colonna A e nella casella Origine avevamo inserito =Negozi, ottenendo così la lista dei negozi.

Ora ci mettiamo nella colonna C, dal menu Dati, selezioniamo l'icona Convalida dati, quindi la voce Convalida dati, nei criteri selezioniamo Elenco e nella casella Origine digitiamo la formula
 =Indiretto (A1) e diamo l'OK.

    Figura 4

Appare una maschera di errore: "L'origine restituisce attualmente un errore. Continuare?" Confermare con l'OK (non so a cosa sia dovuta questa segnalazione ma non influisce sul buon esito della convalida)

Attraverso la formula Indiretto abbiamo collegato il secondo menu al primo e quindi selezionando una voce dal primo limitiamo la visualizzazione delle voci del secondo menu.

Ora possiamo provare a selezionare una voce dal primo menu a tendina (per esempio Negtre o Negotto) e possiamo immediatamente verificare che in base a questa selezione, nella seconda casella potremo visualizzare soltanto le categorie di prodotti che fanno riferimento a quel negozio, come si può vedere nella figura seguente e per controprova nella figura 1

    Figura 5

Resta un'ultima cosa da sistemare. Come si può vedere in figura 5, il menu mostra sempre lo stesso numero di righe. Se vogliamo adattare la casella al numero di righe utilizzate per ogni negozio, possiamo intervenire così:

Selezioniamo tutta la zona delle tabelle, come abbiamo fatto quando le abbiamo create.
Quindi premiamo F5 (o dal Menu Vai a), quindi Speciale e poi selezioniamo Celle vuote, come nella figura seguente:

    Figura 6

A questo punto posizionarsi sulle celle evidenziate in grigio e dopo aver premuto il tasto destro del mouse, selezionare Elimina e quindi selezionare Sposta le celle a sinistra.

    Figura 7

In questo modo tutte le zone sono state adattate alle voci che contengono. Ecco la chicca di cui vi parlavo all'inizio del post.

Ma non è finita qui.
Nel prossimo post vedremo come impostare un ulteriore menu a tendina dipendente dalle selezioni fatte negli altri due menu.

Post precedente:

Menu a tendina o Convalida dati

Menu a tendina o Convalida dati

Eccomi qua!

Sono stata molto impegnata, ma non mi sono scordata di questo spazio, ed ora è venuto il momento di condividere le ultime cose che ho imparato su Excel.

La molla scatenante è stata la richiesta di un utente di un sito al quale sono iscritta: come al solito è un esempio pratico che mi stimola a cercare la soluzione e a memorizzare i passaggi. E devo dire che non è stato facile, perché più le cose si complicano e meno informazioni si trovano in rete. O meglio, le informazioni più dettagliate si trovano in inglese, a dimostrazione che all'estero Excel viene valorizzato maggiormente in tutte le sue potenzialità.

La richiesta era la seguente: è possibile collegare un menu a tendina ad altri menu a tendina?

La risposta è SI.

Ma non precipitiamo le cose e vediamo come fare un passo alla volta.

Innanzitutto cos'è un Menu a tendina, propriamente detto Convalida dati?

Database Struttura Contabile n. 7 (Genera InfoDatiTabPivot)

Nei post precedenti abbiamo completato il nostro prospetto.

Adesso vediamo come verificare che tutte le righe della tabella Pivot siano agganciate alle righe del prospetto e che il totale del bilancino corrisponda al totale del prospetto.

A questo proposito, possiamo confrontare il Risultato netto del Prospetto con il totale della Tabella Pivot.

E' semplice: basta inserire la sottrazione tra i valori delle due celle.

Ci sono due modi per farlo:

Fig. 1

Database Struttura Contabile n. 6 (Funzione Val.Errore, Incolla Speciale, Funzione F4)

Nel post precedente abbiamo inserito le formule che puntano alla tabella Pivot nelle prime tre righe del prospetto.

Fig. 1

Introduciamo ora una formula che spesso si accompagna alla funzione Cerca.vert e cioè la formula VAL.ERRORE