Benvenuto ad una nuova lezione di MasterExcel.it. In questa lezione ti propongo un esercizio basato sulle funzioni Indice Confronta.

Nella scorsa lezione abbiamo dimostrato la superiorità schiacciante dell’Indice Confronta sopra la molto più famosa Funzione Cerca Verticale. Oggi continuiamo con un applicazione avanzata dell’Indice e Confronta.

Prima di continuare ti consiglio vivamente di visionare la lezione sull’Indice Confronta. Ricorso che questa è una Funzione presente nel Corso Base di MasterExcel.it

Guarda il Video – Indice e Confronta: cerca in verticale e orizzontale

Iniziando mostrando il nostro file esempio:

Questo file rappresenta semplicemente una vendita di “prodotti ortofrutticoli”, e le relativi venditrici.

Link alternativo: scarica il file 💾

Esercizio

Data la matrice che hai appena visto, noi dobbiamo creare in un nuovo foglio il risultato visibile nell’immagine sottostante:

Questo secondo foglio di lavoro, deve contenere un menu a tendina nel quale possiamo selezionare una delle venditrici e vedere, nella colonna B, quanto ha venduto per ciascuno dei nostri prodotti ortofrutticoli. Il come realizzare un menu del genere, ricordo è argomento del Corso Excel Base.

Ora, però, vediamo come raggiungere questo risultato partendo dal file visibile nell’immagine sottostante.

Questo è solo un elenco dei prodotti in ordine alfabetico, e in cella B1, abbiamo un menu a tendina dal quale possiamo selezionare la nostra venditrice tra quelle disponibili.

Per completare questo esercizio, è necessario compiere una ricerca sia in verticale, sia in orizzontale, in contemporanea. Quindi non possiamo utilizzare né il Cerca Verticale, né il Cerca Orizzontale. La funzione che fa al caso nostro è solo una, l’Cerca Orizzontale.

Ora prova a farlo da solo e poi dai un occhiata alla soluzione qui sottostante.

Soluzione Esercizio

Rechiamoci nella cella B2 e scriviamo la Funzione Indice. Come “matrice” selezioniamo tutte le colonne contenute nel nostro primo foglio, quello mostrato ad inizio lezione. Come “riga” apriamo una Funzione Confronta. Per selezionare il “valore” rechiamoci nel secondo foglio, e selezioniamo la cella A2. Ora, come “matrice” (della Funzione Confronta), selezioniamo la colonna A contenuta nel nostro primo foglio. Come “corrispondenza” scriviamo “0”. Chiudiamo la Funzione Confronta utilizzando una parentesi tonda e scriviamo un punto e virgola. Abbiamo appena detto come specificare la colonna. Ora siamo di nuovo nella Funzione Indice, ed ora dobbiamo specificare la riga.

Ora dobbiamo selezionare l’ultimo campo della funzione Indice, ossia la Colonna. Come lo facciamo? Nidificando ancora una nuova funzione Confronta. Come “valore” selezioniamo la cella B1 contenuta nel nostro secondo foglio, e blocchiamola premendo una volta F4 ($B$1). Impostiamo come “matrice” selezioniamo l’intera riga “1”, contenuta nel nostro primo foglio. Blocchiamola ancora con una pressione su F4 ($1:$1). Come “corrispondenza” scriviamo “0”. Chiudiamo l’intera funzione con due parentesi tonde. Il risultato finale di questa complessa formula, sarebbe questo:

=INDICE(DB!A:G;CONFRONTA(Foglio1!A2;DB!A:A;0);CONFRONTA(Foglio1!$B$1;DB!$1:$1;0))

Se non hai capito cosa ho fatto, con tutta probabilità non hai guardato la lezione precedente sull’Indice e Confronta.

Ora utilizziamo il solito doppio click ed otterremo il nostro risultato finale, già anticipato all’inizio di questa lezione.

Sintassi

Farò un breve riepilogo suddividendo la formula in vari argomenti.

=INDICE(DB!A:G;CONFRONTA(Foglio1!A2;DB!A:A;0);CONFRONTA(Foglio1!$B$1;DB!$1:$1;0))

ROSSO: l’inizio della nostra funzione “indice”.

ARANCIONE: la matrice del nostro “indice”, dalla colonna “A” alla “G”.

BLU: la prima Funzione Confronta che serve a fornire alla funzione Indice la riga. In questo caso, il comando è più o meno questo: “prendi la cella A2 (che contiene la parola “aglio”) e cercala nella colonna “A” del primo foglio, quindi dimmi in che riga la trovi”. Ovviamente, trascinando già la formula, questa si applica in automatico a tutte le altre celle della colonna A e quindi a tutti i prodotti.

VERDE: la seconda Funzione Confronta. Serve a fornire alla Funzione Indice la “colonna”.  In questo caso dice: “prendi la cella B1 del secondo foglio e cercala nella riga “1” del primo foglio (BD), quando trovi il valore quando trovi il valore B1 in riga 1, dimmi in che colonna è”.

Conclusioni

Come matrice dell’Indice, ti consiglio sempre di selezionare tutte le colonne del foglio di lavoro. Oppure tutte le colonne compilate a partire dalla A. In caso contrario potresti avere dei problemi.

Oggi abbiamo visto come l’Indice e Confronta sia in grado di far contemporaneamente quello che fa il Cerca Verticale e il Cerca Orizzontale. Nelle prossime lezioni mostrerò altre applicazioni avanzate dell’Indice e Confronta.