Come creare una formula di ricerca in Excel con più criteri

Cosa sapere

• Innanzitutto, crea una funzione INDICE, quindi avvia la funzione CONFRONTA nidificata immettendo l’argomento Lookup_value.
• Successivamente, aggiungi l’argomento Lookup_array seguito dall’argomento Match_type, quindi specifica l’intervallo di colonne.
• Quindi, trasforma la funzione nidificata in una formula di matrice premendo Ctrl + Maiusc + Invio. Infine, aggiungi i termini di ricerca al foglio di lavoro.

Questo articolo spiega come creare una formula di ricerca che usa più criteri in Excel per trovare informazioni in un database o una tabella di dati usando una formula di matrice. La formula di matrice implica l’annidamento della funzione CONFRONTA all’interno della funzione INDICE. Le informazioni riguardano Excel per Microsoft 365, Excel 2019, Excel 2016, Excel 2013, Excel 2010 ed Excel per Mac.

Segui il tutorial

Per seguire i passaggi di questo tutorial, inserisci i dati di esempio nelle seguenti celle, come mostrato nell’immagine sottostante. Le righe 3 e 4 vengono lasciate vuote per contenere la formula di matrice creata durante questo tutorial. (Si noti che questo tutorial non include la formattazione vista nell’immagine.)

• Immettere l’intervallo superiore di dati nelle celle da D1 a F2.
• Immettere il secondo intervallo nelle celle da D5 a F11.

Crea una funzione INDICE in Excel

La funzione INDICE è una delle poche funzioni in Excel che ha più moduli. La funzione ha un modulo matrice e un modulo di riferimento. Il modulo matrice restituisce i dati da un database o una tabella di dati. Il modulo di riferimento fornisce il riferimento di cella o la posizione dei dati nella tabella.

In questo tutorial, l’Array Form viene utilizzato per trovare il nome del fornitore per i widget titanium, piuttosto che il riferimento di cella a questo fornitore nel database.

Segui questi passaggi per creare la funzione INDICE:

1. Selezionare la cella F3 per renderla la cella attiva. Questa cella è dove verrà inserita la funzione nidificata.
2. Vai a Formule.

   

3. Scegliere Ricerca e riferimento per aprire l’elenco a discesa delle funzioni.
4. Selezionare INDICE per aprire la finestra di dialogo Seleziona argomenti.
5. Scegli matrice,row_num,column_num.
6. Selezionare OK per aprire la finestra di dialogo Argomenti funzione. In Excel per Mac viene aperto il Generatore di formule.
7. Posizionare il cursore nella casella di testo Array.
8. Evidenzia le celle da D6 a F11 nel foglio di lavoro per inserire l’intervallo nella finestra di dialogo.
   Lasciare aperta la finestra di dialogo Argomenti funzione. La formula non è finita. Completerai la formula nelle istruzioni seguenti.
   

Avvia la funzione MATCH nidificata

Quando si nidifica una funzione all’interno di un’altra, non è possibile aprire il generatore di formule della seconda funzione, o nidificata, per inserire gli argomenti necessari. La funzione nidificata deve essere inserita come uno degli argomenti della prima funzione.

Quando si immettono le funzioni manualmente, gli argomenti della funzione sono separati l’uno dall’altro da una virgola.

Il primo passaggio per accedere alla funzione MATCH nidificata consiste nell’immettere l’argomento Lookup_value. Il Lookup_value è la posizione o il riferimento di cella per il termine di ricerca da trovare nel database.

Il Lookup_value accetta solo un criterio di ricerca o un termine. Per cercare più criteri, estendi il valore Lookup_value concatenando o unendo due o più riferimenti di cella utilizzando il simbolo e commerciale (&).

1. Nella finestra di dialogo Argomenti funzione, posizionare il cursore nella casella di testo Row_num.
2. Digita CONFRONTA(.
3. Seleziona la cella D3 per inserire quel riferimento di cella nella finestra di dialogo.
4. Immettere & (la e commerciale) dopo il riferimento di cella D3 per aggiungere un secondo riferimento di cella.
5. Selezionare la cella E3 per inserire il secondo riferimento di cella.
6. Immettere , (una virgola) dopo il riferimento di cella E3 per completare l’immissione dell’argomento Lookup_value della funzione MATCH.

   
   Nell’ultimo passaggio dell’esercitazione, i valori Lookup_values ​​verranno immessi nelle celle D3 ed E3 del foglio di lavoro.

Completa la funzione CONFRONTA nidificata

Questo passaggio riguarda l’aggiunta dell’argomento Lookup_array per la funzione MATCH nidificata. Lookup_array è l’intervallo di celle che la funzione MATCH cerca per trovare l’argomento Lookup_value aggiunto nel passaggio precedente dell’esercitazione.

Poiché nell’argomento Lookup_array sono stati identificati due campi di ricerca, è necessario eseguire lo stesso per Lookup_array. La funzione MATCH cerca solo un array per ogni termine specificato. Per inserire più array, utilizzare la e commerciale per concatenare insieme gli array.

1. Posizionare il cursore alla fine dei dati nella casella di testo Row_num. Il cursore appare dopo la virgola alla fine della voce corrente.
2. Evidenzia le celle da D6 a D11 nel foglio di lavoro per inserire l’intervallo. Questo intervallo è il primo array che la funzione cerca.
3. Immettere & (una e commerciale) dopo i riferimenti di cella D6:D11. Questo simbolo fa sì che la funzione cerchi due array.
4. Evidenzia le celle da E6 a E11 nel foglio di lavoro per inserire l’intervallo. Questo intervallo è il secondo array che la funzione cerca.
5. Immettere , (una virgola) dopo il riferimento di cella E3 per completare l’immissione dell’argomento Matrice_ricerca della funzione CONFRONTA.

   

6. Lasciare aperta la finestra di dialogo per il passaggio successivo dell’esercitazione.

Aggiungere l’argomento di tipo MATCH

Il terzo e ultimo argomento della funzione MATCH è l’argomento Match_type. Questo argomento indica a Excel come abbinare Lookup_value con i valori in Lookup_array. Le scelte disponibili sono 1, 0 o -1.

Questo argomento è facoltativo. Se viene omesso, la funzione utilizza il valore predefinito 1.

• Se Match_type = 1 o viene omesso, MATCH trova il valore più grande minore o uguale a Lookup_value. I dati Lookup_array devono essere ordinati in ordine crescente.
• Se Match_type = 0, MATCH trova il primo valore uguale a Lookup_value. I dati Lookup_array possono essere ordinati in qualsiasi ordine.
• Se Match_type = -1, MATCH trova il valore più piccolo maggiore o uguale a Lookup_value. I dati Lookup_array devono essere ordinati in ordine decrescente.

Immettere questi passaggi dopo la virgola immessa nel passaggio precedente sulla riga Row_num nella funzione INDICE:

1. Immettere 0 (uno zero) dopo la virgola nella casella di testo Row_num. Questo numero fa sì che la funzione nidificata restituisca corrispondenze esatte ai termini immessi nelle celle D3 ed E3.
2. Immettere ) (una parentesi tonda di chiusura) per completare la funzione CONFRONTA.

   

3. Lasciare aperta la finestra di dialogo per il passaggio successivo dell’esercitazione.

Termina la funzione INDICE

La funzione CONFRONTA è terminata. È ora di passare alla casella di testo Num_colonna della finestra di dialogo e inserire l’ultimo argomento per la funzione INDICE. Questo argomento indica a Excel che il numero di colonna è compreso nell’intervallo da D6 a F11. Questo intervallo è dove trova le informazioni restituite dalla funzione. In questo caso, un fornitore di widget in titanio.

1. Posizionare il cursore nella casella di testo Column_num.
2. Immettere 3 (il numero tre). Questo numero indica alla formula di cercare i dati nella terza colonna dell’intervallo da D6 a F11.

   

3. Lasciare aperta la finestra di dialogo per il passaggio successivo dell’esercitazione.

Crea la formula matrice

Prima di chiudere la finestra di dialogo, trasforma la funzione nidificata in una formula di matrice. Questo array consente alla funzione di cercare più termini nella tabella dei dati. In questo tutorial vengono abbinati due termini: Widget dalla colonna 1 e Titanium dalla colonna 2.

Per creare una formula di matrice in Excel, premere contemporaneamente i tasti CTRL, MAIUSC e INVIO. Una volta premuta, la funzione viene racchiusa tra parentesi graffe, a indicare che la funzione è ora un array.

1. Selezionare OK per chiudere la finestra di dialogo. In Excel per Mac selezionare Fatto.
2. Selezionare la cella F3 per visualizzare la formula, quindi posizionare il cursore alla fine della formula nella barra della formula.
3. Per convertire la formula in una matrice, premi CTRL + MAIUSC + INVIO.
4. Nella cella F3 viene visualizzato un errore #N/D. Questa è la cella in cui è stata inserita la funzione.
5. L’errore #N/D viene visualizzato nella cella F3 perché le celle D3 ed E3 sono vuote. D3 ed E3 sono le celle in cui la funzione cerca per trovare il Lookup_value. Dopo che i dati sono stati aggiunti a queste due celle, l’errore viene sostituito dalle informazioni del database.

   

Aggiungi i criteri di ricerca

L’ultimo passaggio consiste nell’aggiungere i termini di ricerca al foglio di lavoro. Questo passaggio corrisponde ai termini Widget della colonna 1 e Titanium della colonna 2.

Se la formula trova una corrispondenza per entrambi i termini nelle colonne appropriate del database, restituisce il valore della terza colonna.

1. Seleziona la cella D3.
2. Inserisci Widget.
3. Seleziona la cella E3.
4. Digita Titanium e premi Invio.
5. Il nome del fornitore, Widgets Inc., appare nella cella F3. Questo è l’unico fornitore elencato che vende widget in titanio.
6. Seleziona la cella F3. La funzione viene visualizzata nella barra della formula sopra il foglio di lavoro. In questo esempio, esiste un solo fornitore per i widget in titanio. Se c’erano più di un fornitore, la funzione restituisce il fornitore elencato per primo nel database.
   
{=INDEX(D6:F11,MATCH(D3&E3,D6:D11&E6:E11,0),3)}