Scopriamo insieme come usare la funzione CERCA.X per superare i limiti della funzione CERCA.VERT.
Chi fino ad oggi ha utilizzato la funzione CERCA.VERT, troverà immediato vantaggio dalla sua naturale evoluzione, la funzione CERCA.X.
Si tratta di una funzione disponibile soltanto nella versione 365 di Office o nella versione online di Excel, mentre, ad oggi, non è inclusa nelle versioni desktop.
Nel file CERCA_X_Dataset, nel Foglio1, trovate una bizzarra ed immaginaria matrice di conversione, molto breve e generata del tutto casualmente:
Simuliamo adesso di dover convertire una scelta prodotto di un nostro cliente ipotetico nel suo relativo prezzo.
1) COME USARE LA FUNZIONE CERCA.X – ESEMPIO DI BASE
Tra le tante, possiamo immaginare di risolvere il problema con la funzione CERCA.VERT:
=CERCA.VERT(F2;B2:C18;2;0)
Notiamo come lo stesso risultato potrebbe essere determinato con la seguente formula:
=CERCA.X(F27;B2:B18;C2:C18)
La sintassi è la seguente:
=CERCA.X(valore da convertire; in quale array cerco il valore da convertire, in quale array prendo la conversione)
Perché si dovrebbe preferire la funzione CERCA.X rispetto al CERCA.VERT?
2) COME USARE LA FUNZIONE CERCA.X, VANTAGGI RISPETTO AL CERCA.VERT
A) RESISTENZA ALLE MODIFICHE APPORTATE ALLA MATRICE DI CONVERSIONE
Proviamo ad inserire una colonna tra la B e la C
Notiamo subito che il CERCA.VERT va incontro ad uno “stallo”, mentre ciò non avviene per il CERCA.X
Possiamo finalmente modificare la nostra matrice senza preoccuparci di aggiornare il CERCA.VERT.
Vediamo adesso un ulteriore vantaggio della funzione CERCA.X.
B) CAPACITA’ DI GENERARE VETTORI
Ci riferiamo alla sua capacità di generare come risultato non solo un singolo valore, ma un Array. Vediamo il seguente esempio
Supponiamo di scrivere nella cella A20 la seguente formula: =CERCA.X(G2;B1:B18;A1:E18)
Cerchiamo di immaginare quale sia il suo risultato interpretandone la sintassi.
In sostanza sto chiedendo ad Excel quanto segue:
- Prendi il valore in G2
- Cercalo in B1:B18
- Quando e se lo trovi mi restituisci un array composto da tutti il valori presenti nella riga di matching positivo della matrice A1:E18
- Il risultato sarà tutta la riga della matrice A1:E18 nella quale è stato trovato il Righello
3) CERCA.X PER ESTRARRE VALORI DA TABELLE CON PIU’ MATCHING
Possiamo utilizzare questa capacità del CERCA.X di produrre Array per effettuare un incrocio su di una tabella con più possibili matching
Possiamo vedere che a differenza della prima tabella, questa volta si hanno due possibili prezzi per il prodotto scelto, in funzione del modello che può essere o Base o Lusso.
Non possiamo utilizzare il CERCA.X nella sua forma base, poiché non possiamo specificare il vettore che contiene il risultato da restituire: sarà infatti D3:D19 se la scelta è un prodotto base, mentre sarà E3:E19 nel caso di un bene di lusso.
Nel caso della scelta di un Righello di Lusso, il risultato sarà dato dall’intersezione del vettore A7:F7 ed E2:E19
Vediamo come restituire il ragionamento in formula
Da notare che lo sfondo giallo ed il rosso è stato da me aggiunto manualmente per fini esplicativi.
La formula usata in J3 è la seguente:
=CERCA.X(H3;$B$2:$B$19;CERCA.X(I3;$D$2:$E$2;$D$2:$E$19))
Cerchiamo di spiegare la formula PASSO PASSO:
- Prendo il valore presente in H3
- Cerco il valore di H3 nell’array $B$2:$B$19 e quando lo trovo voglio convertirlo associandogli un valore che si trova nella stessa posizione ordinale di un altro array che vado a specificare di seguito.
- Devo adesso specificare l’array di conversione, ma siccome possono essere due, devo passare ad Excel quello corretto per la conversione. In sostanza devo scegliere tra l’array di base e quello di lusso. Per farlo posso usare la caratteristica del CERCA.X di generare array.
Chiedo adesso, infatti, ad Excel di usare un altro CERCA.X per restituire l’array di base o del lusso:- =CERCA.X(I3;$D$2:$E$2;$D$2:$E$19)
- Questa funzione restituisce il vettore D2:D19 se I3=”Base” oppure E2:E19 se I3=”Lusso”
- A questo punto la conversione avviene per intersezione tra i due array generati dalle due funzioni CERCA.X
4) COME USARE LA FUNZIONE CERCA.X PER ESTRAZIONE VALORI MULTIPLI ADIACENTI
Supponiamo adesso che desideri estrarre entrambi i prezzi per il prodotto scelto. Usiamo la caratteristica della funzione CERCA.X di generare array condizionati:
=CERCA.X(H3;$B$3:$B$19;$D$3:$E$19)
In sintesi, per spiegare la formula, basta ragionare così:
- Prendo il valore presente in H3
- Lo cerco in $B$3:$B$19 ed identifico la posizione nell’array
- Restituisco i valori che si trovano nella posizione trovata la passaggio 2 me nel range D3:E19
I risultati della formula precedente non sarebbero stati DIRETTAMENTE possibili con il CERCA.VERT in quanto non è in grado di restituire valori multipli in modo diretto. Il caso esaminato è relativamente semplice in quanto deve restituire valori contigui.
Cosa accadrebbe se desiderassi restituire valori appartenenti a range non contigui?
5) COME USARE LA FUNZIONE CERCA.X PER ESTRAZIONE VALORI MULTIPLI NON ADIACENTI
Al di là del fatto che esistono più strade per risolvere il problema, mi piace continuare con la logica dell’intersezione delle matrici e dei vettori.
Vediamo di restituire per un prodotto scelto i due valori non adiacenti del prezzo Base e dell’IVA.
La funzione è del tutto simile alla precedente, la differenza risiede nella matrice di estrazione che non è composta da colonne adiacenti e che devo quindi costruire con una funzione specifica:
SCEGLI({1\2};$D$3:$D$19;$F$3:$F$19)
La funzione costruisce una matrice da cui restituire i dati composta dalle due colonne: $D$3:$D$19 e $F$3:$F$19
La formula finale sarà:
=CERCA.X(H3;$B$3:$B$19;SCEGLI({1\2};$D$3:$D$19;$F$3:$F$19))
Come si può osservare dalla figura il risultato è quello desiderato.
SE DESIDERI SCOPRIRE DI PIU’!
L’articolo è scritto da Gabriele di Ruvo, docente di analisi dei Dati per Mummu Academy e molte altre realtà aziendali.
Gabriele e la sua azienda sono disponibili per stage e formazione sia on-site che a distanza sia per i privati che per le aziende che desiderano migliorare le proprie competenze nella Data Analysis.
Nella formazione a distanza, sincrona ed asincrona, Mummu Academy mette a disposizione la propria piattaforma di E-Learning.
Se sei interessato puoi contattare direttamente Mummu Academy allo 055 4223105 o scrivere a g.diruvo@mummuacademy.it.
POTRESTI TROVARE INTERESSANTE ANCHE I SEGUENTI ARTICOLI
[EXCEL] Usare Emoji ed Emoticons in Excel con la Formattazione Condizionale
https://www.mummuacademy.it/corsi/corso-excel-livello-avanzato-firenze-2020/
[Excel] Sommare tariffe giornaliere diverse per data usando le matrici in Excel
