Creare ricerca interna Excel

Usando Microsoft Excel, è possibile creare una ricerca interna senza utilizzare VBA, filtri o pivot. Possiamo utilizzare una combinazione di funzioni per arrivare allo stesso risultato dei filtri, magari in un altro foglio di lavoro, in modo da avere statistiche stampabili senza dover toccare il database di partenza.

Questa guida spiegherà passo passo come realizzare delle modalità di ricerca interna. Per esigenze sul momento, sicuramente una pivot può fare lo stesso lavoro egregiamente, avendo solo il limite della stampa che risulta spesso difficoltosa. Credo però che imparare queste combinazioni di formule possa essere molto utile anche per lavorare con grossi database, in particolare dato che utilizzeremo degli array. L'esempio è basato sulla ricerca di film di un certo database secondo parametri che andremo a definire assieme. In particolare, vedremo vari casi diversi fra di loro:

RICERCA #1: cercare tutti i film fatti nello stesso anno e RICERCA #1/bis: cercare tutti i film fatti da un certo anno in poi

RICERCA #2: cercare tutti i film che abbiano un rating fra due valori da noi definiti

RICERCA #3: cercare tutti i film che appartengano ad un certo genere (con vari generi inseriti nella stessa cella)

RICERCA #4: cercare tutti i film che appartengano ad un certo genere, siano stati fatti in un certo anno e abbiano un voto fra due valori da noi definiti

RICERCA #5: cercare tutti i film che appartengano ad un certo genere, siano stati fatti in un certo anno e abbiano un voto fra due valori da noi definiti
(come la #4, ma senza dover inserire tutti i campi di ricerca)

Il database di partenza è il seguente, che comprende i 50 film dal voto più alto su IMDB:

Alla fine dell'articolo, troverete anche il file finale con tutti i casi visti nelle varie parti del post.

Ricerca Excel: trovare le righe che presentano un determinato valore numerico in un'altra colonna

In questo caso, vogliamo trovare tutti i film fatti nell'anno 2000.

Fogli utilizzati: DB, ANNO_INDICE

Potremmo essere tentati di utilizzare un semplice INDICE CONFRONTA o CERCA.VERT. Proviamo a realizzarlo per trovare i film fatti nell'anno 2000 che fanno parte della classifica (sono 3: Fight Club, Matrix, Il miglio verde). Per rivedere come è costruita la formula, potete accedere a quest'altro articolo:

Come notiamo, utilizzando solo INDICE e CONFRONTA rileviamo un problema quasi insormontabile. In sostanza, Excel non riesce a riconoscere che ci sono diverse occorrenze del valore 2000 nella colonna B del database, quindi ci restituisce sempre e solo il primo risultato. Quindi questa formula funziona solo quando c'è un solo film per anno e non ci interessa, essendo troppo limitata (le colonne da D in poi non ci interessano, dato che pescano soltanto i valori dal database di partenza).

Vediamo quindi come risolvere, utilizzando INDICE, CONFRONTA e gli array. Gli array si inseriscono con CTRL+SHIFT+ENTER quando si inserisce una determinata formula e permettono di fare alcuni calcoli più complessi in meno tempo.

Per far funzionare la formula, dobbiamo sapere quanti sono i valori della colonna che stiamo considerando. In questo caso, 50 film quindi 50 righe. Ne parlavo anche in questo articolo, ma essendo un po' particolare mi pare giusto spiegarlo nuovamente.

Formula generale:

INDICE([intervallo_valori_da_trovare];
PICCOLO(SE([valore_da_trovare]=[colonna_di_ricerca];
RIF.RIGA([da_1_a_n, con n il totale del campione]));RIF.RIGA([da_1_a_n])))

Esempio: nel nostro caso, sappiamo quali sono le informazioni che stiamo cercando, cioè l'anno. Consideriamo quindi:

- [intervallo_valori_da_trovare] = DB!$A$2:$A$51

- [valore_da_trovare] = $B$2

- [colonna_di_ricerca] = DB!$B$2:$B$51 (come notiamo, da 2 a 51 come nel precedente intervallo)

- [da_1_a_n, con n il totale del campione] = $1:$50 (dato che sono 50 valori, se fossero stati 100 sarebbe stato $1:$100)

- [da_1_a_n] = A1 (per il primo valore, che sarà poi trascinato in modo da dare A2, A3 e via dicendo)

Formula pratica:

INDICE(DB!$A$2:$A$51;
PICCOLO(SE($B$2=DB!$B$2:$B$51;
RIF.RIGA($1:$50));RIF.RIGA(A1)))

C'è un appunto da fare. In questo caso, la formula è tarata perfettamente sul database di partenza, perchè lavorando con gli array si rischia di rallentare i calcoli in maniera importante se abbiamo grossi database. Se il valore "n" del RIF.RIGA fosse superiore a 50 (ovvero superiore al numero di celle incluse da A2 a A51) avremmo un errore.

La formula funziona anche mettendo l'intera colonna A e l'intera colonna B, ma è davvero eccessivo per ciò che stiamo cercando. Se però anzichè avere 50 film ne avessimo un numero variabile (diciamo fino a 3000, anche se non ancora sul database e che inseriremo in futuro) potremmo inserire questa formula per essere sicuri di pescare tutti i valori senza modificare ogni volta i valori della formula. In sostanza, l'accorgimento principale è che dobbiamo essere certi che il valore "n" del RIF.RIGA sia superiore al numero di dati possibili del nostro database di partenza. Il lato negativo, come dicevamo, è che i calcoli saranno parecchio più lenti.

Formula pratica se volete provare, sempre da inserire con gli array (3100 potrebbe essere anche 3001 o 10000, non fa differenza):

INDICE(DB!$A:$A;
PICCOLO(SE($B$2=DB!$B:$B;
RIF.RIGA($1:$3100));RIF.RIGA(A1)))

Per essere veramente precisi, aggiungiamo il SE.ERRORE per pulire il database ed evitare la fila di errori.

Ricerca Excel: trovare le righe che presentano almeno un determinato valore numerico in un'altra colonna

In questo caso, parliamo dei film fatti almeno dall'anno 2000 in poi.

Inserisco solo la formula:

SE.ERRORE(INDICE(DB!$A$2:$A$51;
PICCOLO(SE($B$2<=DB!$B$2:$B$51;
RIF.RIGA($1:$50));RIF.RIGA(A1)));"")

Chiaramente, se fossero i film fino ad un certo anno basterebbe sostituire < con >.

Ricerca Excel: trovare le celle che presentano un valore numerico fra due valori in un'altra colonna

Cerchiamo tutte le celle che presentino la colonna RATING pari almeno ad un certo valore da noi scelto e che non siano maggiori di un altro valore sempre da noi definito.

Fogli utilizzati: DB, RATING, RATING_HELPER

Qui abbiamo due possibili strade che affronteremo. La prima utilizza una colonna di supporto, l'altra lavora direttamente con gli array. Non c'è una versione giusta o sbagliata, la prima è più semplice ma più lenta nel calcolo / la seconda è più complessa da impostare, ma molto più veloce nel calcolo.

Strada 1: colonna di supporto.

Creiamo una colonna di supporto per verificare di considerare solo i film che abbiano un rating fra i valori B1 e B2 del nostro foglio di ricerca (RATING_HELPER). In questo caso, ho inserito  nel foglio RATING_HELPER i valori 8,9 e 9 per definire solo una parte del database complessivo.

Come vediamo, il valore F2 non è corretto (essendo 9,3). Quindi in N2 la formula ci segnala che il primo valore non è in linea con ciò che stiamo cercando. Il ragionamento della formula è semplicemente di capire se le due condizioni si verifichino nello stesso momento: se ciò accade, appare OKAY, altrimenti NO.

La formula è praticamente la stessa che abbiamo visto in precedenza, solo che inseriamo la condizione SE(DB!$N$2:$N$51="OKAY" per chiarire a Excel che stiamo selezionando i film da 8,9 a 9. Come dicevamo, questo caso è più rapido e meno complesso, quindi utile se abbiamo un piccolo database e non ci sono problemi nel tenere il calcolo automatico dell'intero foglio. Con il calcolo manuale, dovremmo infatti prima calcolare il database e poi calcolare il foglio di analisi facendo qualche passaggio in più rispetto al solito.

Strada 2: doppio array.

Il problema è già risolto nella strada 1, ma vogliamo sperimentare un nuovo metodo per arrivare allo stesso risultato. Consideriamo in questo caso di raggiungere lo stesso risultato senza utilizzare alcuna colonna di supporto. Dovremmo quindi inserire una doppia condizione in un array, ponendo di avere i rating da 8,9 a 9.

Potremmo essere tentati di replicare esattamente quanto presente nella colonna di supporto con il SE(E:

Tuttavia, non funziona. La notazione degli array è un po' diversa da quanto ci aspettiamo, quindi ci appare un film con un rating di 9,3 e poi il nulla cosmico negli altri risultati. Non ci siamo.

La soluzione però si trova utilizzando un artificio proprio degli array, da utilizzare quando abbiamo più di una condizione possibile:

Come vediamo, la differenza è inserire SE((DB!$F$2:$F$51>=B$1)*(DB!$F$2:$F$51<=B$2) anzichè SE(E(DB!$F$2:$F$51>=B$1;DB!$F$2:$F$51<=B$2). In tutte le circostanze nelle quali dovremo lavorare con array, dovremo ricordarci di inserire le condizioni che ci interessano e devono corrispondere al vero come seguono: (prima condizione)*(seconda condizione). Possiamo anche inserirne di più e lo vedremo in seguito nell'articolo, ma è da ricordare se non vogliamo perdere troppo tempo e velocizzare il file.

Ricerca Excel: trovare tutte le celle con un certo valore testuale in una colonna definita

Cerchiamo tutti i film di un certo genere.

Fogli utilizzati: DB, GENERE_HELPER, GENERE_TEST

Microsoft Excel è pieno di falsi amici e operazioni che sembrano facili se effettuate con i filtri o le pivot, ma ben più complesse con le formule. Prima della soluzione parliamo infatti di un possibile errore, ovvero di utilizzare le stesse formule della ricerca #1 all'inizio dell'articolo.

Ad una prima occhiata, sembra tutto a posto. Il problema è che la formula prende i film che come genere hanno solo Drama, ignorando quelli che hanno diverse combinazioni (ad esempio Crime, Drama come nel caso di Pulp Fiction). Quindi questa formula va utilizzata solo se c'è un riferimento univoco, come appunto nel caso dell'anno. Per trovare un certo valore all'interno di una cella, dovremo usare un'altra combinazione di funzioni con VAL.NUMERO e TROVA della quale ho parlato qui.

Anche in questo caso, mostrerò due strade per arrivare allo stesso risultato. Utilizzeremo TROVA anzichè RICERCA perchè fingiamo che ci interessi se siano minuscole o maiuscole, se non ci interessasse potremmo tranquillamente sostituire la funzione TROVA con RICERCA.

Strada 1: colonna di supporto.

Le colonne di supporto possono essere davvero utili, in questo caso possiamo semplicemente l'operazione del caso di ricerca #2 ma su un'altra colonna e filtrare poi i risultati secondo le colonne con OKAY. La formula verifica che nella colonna GENERE ci sia effettivamente il genere da noi scelto, in questo caso Drama.

Ripetiamo poi la stessa condizione del caso precedente, inserendo il parametro DB!$P$2:$P$51="OKAY":

Strada 2: array.

Molto simile alla soluzione del caso di ricerca #1.

Ricerca Excel complessa: cercare tutti i film che appartengano ad un certo genere, siano stati fatti in un certo anno e abbiano un voto fra due valori da noi definiti

Fogli utilizzati: DB, MULTIPLA, MULTIPLA_ARRAY

Passiamo ad un caso decisamente interessante, con una ricerca complessa con numerose variabili.

Inseriremo l'anno nella cella B1, il genere nella cella B2, il rating iniziale in B3 e quello finale in B4. Nell'esempio, ho inserito l'anno 1980, genere Crime, rating da 8 a 9.

La prima cella nella quale inserire i valori della formula sarà C6.

Strada 1: colonna di supporto.

Se vogliamo proseguire sulla strada delle colonne di supporto, nessuno ce lo impedisce. Basti vedere come è impostata la colonna R in questo esempio.

Non inserisco altri screenshot, tanto la formula è uguale a quelle precedenti con la condizione ="OKAY". La trovate comunque sul file in coda all'articolo.

Strada 2: array.

In questo caso mostriamo come funzionino gli array dato che è più complessa del solito e sono 4 condizioni concatenate.

Inserisco la formula per una maggiore comprensione:

SE.ERRORE(INDICE(DB!$A$2:$A$51;
PICCOLO((SE(
(DB!$B$2:$B$51>$B$1)*
(DB!$F$2:$F$51>=$B$3)*
(DB!$F$2:$F$51<=$B$4)*
(VAL.NUMERO(TROVA($B$2;DB!$E$2:$E$51)));
RIF.RIGA($1:$50)));RIF.RIGA(A1)));"")

A questo punto, saremmo tentati di chiudere il nostro file. Abbiamo però un ultimo caso da analizzare, perchè il nostro caso per il momento prevede che tutte le celle debbano avere un numero o un valore coerente. Nel nostro foglio, non possiamo cercare solo i film di un certo anno dovendo inserire anche il rating per farlo funzionare. Allo stesso modo, non possiamo cercare solo i film con un certo rating perchè dobbiamo anche inserire le altre condizioni.

Ricerca Excel dinamica e parziale: come cercare tutti i film che appartengano ad un certo genere, siano stati fatti in un certo anno e abbiano un voto fra due valori da noi definiti.

Tutto ciò in maniera dinamica, non dovendo usare tutte le condizioni

Fogli utilizzati: DB, MULTIPLO_F, MULTIPLO_F_ARRAY

In questo caso, utilizzeremo un trucco per arrivare al risultato che desideriamo ottenere. Inseriremo all'interno delle celle il valore <> che significa tutti, ma potrebbe essere qualsiasi altro valore volessimo mettere per segnalare che ci interessano tutti i risultati di una particolare sotto-categoria. Dovremo ricordarci di inserire <> come parametro nelle celle che non ci interessano per la nostra ricerca.

Per intenderci: se inseriamo <> nella cella B1, significherà che non utilizzeremo l'anno come parametro di ricerca e ci interessano tutti i film del database a prescindere da questo valore. Lo stesso succederebbe nel caso lo inserissimo nelle altre celle (tutti i generi, tutti i voti e così via).

Strada 1: colonna di supporto.

Consideriamo i  film Drama dal 1992 in poi (compreso) con un voto da 9 a indefinito .In questo caso, la condizione si complica un po' ma la colonna di supporto continua ad essere un valido aiuto. Dobbiamo mettere in formula le condizioni spiegando che se Excel trova <>, può ignorare quel parametro. Ecco quindi come fare:

Inserisco la formula per una maggiore comprensione:

SE(E(O(MULTIPLO_F!$B$1="<>";MULTIPLO_F!$B$1<=B2);
O(MULTIPLO_F!$B$2="<>";VAL.NUMERO(TROVA(MULTIPLO_F!$B$2;E2)));
O(MULTIPLO_F!$B$3="<>";MULTIPLO_F!$B$3<=F2);
O(MULTIPLO_F!$B$4="<>";MULTIPLO_F!$B$4>=F2));"OKAY";"NO")

Praticamente la formula ci dice che tutte le condizioni devono essere soddisfatte (funzione E) ma per ognuna ci sono varie possibilità e si può ignorare il valore se troviamo <> (funzione O). Se le condizioni sono rispettate, avremo OKAY, altrimenti NO.

La formula è sempre la stessa dei casi precedenti, avendo pescato da una colonna di supporto.

Strada 2: array.

Questa è la formula più complessa dell'intero foglio e richiede una grande attenzione per funzionare. Tuttavia, ha il vantaggio di non usare nessuna colonna di supporto per effettuare i calcoli. Vediamo come funziona.

Prendiamo il caso di film Drama oltre l'anno 1960 con voto massimo pari a 8,5 e voto iniziale indefinito.

Inserisco la formula per una maggiore comprensione e la analizzerò nelle singole parti:

SE(O($B$3="<>";$B$4="<>";$B$4>=$B$3);
SE.ERRORE(INDICE(DB!$A$2:$A$51;
PICCOLO(SE(
(SE($B$1="<>";1;DB!$B$2:$B$51>$B$1))*
(SE($B$3="<>";1;DB!$F$2:$F$51>=$B$3)*(SE($B$4="<>";1;DB!$F$2:$F$51<=$B$4))*
(SE($B$2="<>";1;VAL.NUMERO(TROVA($B$2;DB!$E$2:$E$51)))));
RIF.RIGA($1:$50));RIF.RIGA(A1)));"");
"ERRORE_RATING")

Prima riga: SE(O($B$3="<>";$B$4="<>";$B$4>=$B$3)

Qui sto segnalando di procedere se: O il primo valore è pari a <>, O il secondo valore è pari a <>, O il secondo valore è maggiore uguale al primo. Ciò è necessario perchè se il secondo valore fosse minore, non avrebbe senso (rating dal primo al secondo valore, che deve quindi essere maggiore). Devo inoltre segnalare che va bene se non ci sono entrambi i rating o anche se non ce n'è nessuno (caso nel quale abbiamo <>), altrimenti la formula non funzionerebbe.

Seconda e terza riga sono standard: SE.ERRORE(INDICE(DB!$A$2:$A$51;PICCOLO(SE(

Quarta riga: (SE($B$1="<>";1;DB!$B$2:$B$51>$B$1))

Qui sto segnalando che se B1 fosse uguale a <>, mi vanno bene tutti i risultati (quindi =1, che non influisce sui risultati). Diversamente, si può procedere considerando solo i film fatti da una certa data in poi come nelle altre formule.

Quinta e sesta riga replicano il ragionamento precedente: (SE($B$3="<>";1;DB!$F$2:$F$51>=$B$3)*(SE($B$4="<>";1;DB!$F$2:$F$51<=$B$4))*(SE($B$2="<>";1;VAL.NUMERO(TROVA($B$2;DB!$E$2:$E$51)))));

Settima riga è la parte finale della formula dopo il primo SE: RIF.RIGA($1:$50));RIF.RIGA(A1)));"");

L'ottava e ultima riga mi indica solo che, nel caso avessi inserito dei valori sbagliati nel rating, mi dovrà apparire la scritta ERRORE_RATING: "ERRORE_RATING")

Come promesso, dal link seguente puoi scaricare il file definitivo. Grazie per l'attenzione!