Cosa sapere
- In primo luogo, crea una funzione INDEX, quindi avvia la funzione nidificata MATCH inserendo 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 utilizza più criteri in Excel per trovare informazioni in un database o in una tabella di dati utilizzando una formula di matrice. La formula di matrice prevede 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 celle seguenti, come mostrato nell'immagine seguente. Le righe 3 e 4 vengono lasciate vuote per accogliere la formula di matrice creata durante questo tutorial. (Nota che questo tutorial non include la formattazione vista nell'immagine.)
- Inserisci l'intervallo superiore di dati nelle celle da D1 a F2.
- Inserisci il secondo intervallo nelle celle da D5 a F11.
Crea una funzione INDEX 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 Array restituisce i dati da un database o una tabella di dati. Il modulo di riferimento fornisce il riferimento della cella o la posizione dei dati nella tabella.
In questo tutorial, il modulo Array viene utilizzato per trovare il nome del fornitore per i widget in titanio, piuttosto che il riferimento di cella a questo fornitore nel database.
Segui questi passaggi per creare la funzione INDEX:
- Seleziona la cella F3 per renderla la cella attiva. In questa cella verrà inserita la funzione nidificata.
-
Vai a Formule.
Image - Scegli Ricerca e riferimento per aprire l'elenco a discesa delle funzioni.
- Seleziona INDEX per aprire la finestra di dialogo Seleziona argomenti.
- Scegli array, row_num, column_num.
- Seleziona OK per aprire la finestra di dialogo Argomenti funzione. In Excel per Mac, si apre il Generatore di formule.
- Posiziona il cursore nella casella di testo Array.
-
Evidenzia le celle da D6 a F11 nel foglio di lavoro per inserire l'intervallo nella finestra di dialogo.
Lascia aperta la finestra di dialogo Argomenti funzione. La formula non è finita. Completerai la formula nelle istruzioni seguenti.
Image
Avvia la funzione MATCH nidificata
Quando si annida una funzione dentro un' altra, non è possibile aprire il generatore di formule della seconda funzione, o annidato, per inserire gli argomenti necessari. La funzione annidata deve essere inserita come uno degli argomenti della prima funzione.
Quando si inseriscono le funzioni manualmente, gli argomenti della funzione sono separati l'uno dall' altro da una virgola.
Il primo passo per entrare nella funzione nidificata MATCH è inserire l'argomento Lookup_value. 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 Lookup_value concatenando o unendo due o più riferimenti di cella utilizzando il simbolo e commerciale (&).
- Nella finestra di dialogo Argomenti funzione, posizionare il cursore nella casella di testo Row_num.
- Inserisci MATCH(.
- Seleziona la cella D3 per inserire quel riferimento di cella nella finestra di dialogo.
- Inserisci & (la e commerciale) dopo il riferimento di cella RE3 per aggiungere un secondo riferimento di cella.
- Seleziona la cella E3 per inserire il secondo riferimento di cella.
-
Enter , (una virgola) dopo il riferimento di cella E3 per completare l'immissione dell'argomento Lookup_value della funzione MATCH.
Image Nell'ultimo passaggio del tutorial, i valori di ricerca verranno inseriti nelle celle D3 ed E3 del foglio di lavoro.
Completa la funzione MATCH nidificata
Questo passaggio riguarda l'aggiunta dell'argomento Lookup_array per la funzione nidificata MATCH. Lookup_array è l'intervallo di celle che la funzione CONFRONTA cerca per trovare l'argomento Lookup_value aggiunto nel passaggio precedente del tutorial.
Poiché sono stati identificati due campi di ricerca nell'argomento Lookup_array, lo stesso deve essere fatto per Lookup_array. La funzione CONFRONTA ricerca solo una matrice per ogni termine specificato. Per inserire più array, usa la e commerciale per concatenare gli array insieme.
- Posiziona il cursore alla fine dei dati nella casella di testo Row_num. Il cursore appare dopo la virgola alla fine della voce corrente.
- Evidenzia le celle da D6 a D11 nel foglio di lavoro per entrare nell'intervallo. Questo intervallo è il primo array che la funzione cerca.
- Inserisci & (una e commerciale) dopo i riferimenti di cella D6:D11. Questo simbolo fa sì che la funzione esegua la ricerca in due array.
- Evidenzia le celle da E6 a E11 nel foglio di lavoro per entrare nell'intervallo. Questo intervallo è il secondo array che la funzione cerca.
-
Inserisci , (una virgola) dopo il riferimento di cella E3 per completare l'immissione dell'argomento Lookup_array della funzione MATCH.
Image - Lascia la finestra di dialogo aperta per il passaggio successivo del tutorial.
Aggiungi l'argomento del tipo MATCH
Il terzo e ultimo argomento della funzione MATCH è l'argomento Match_type. Questo argomento indica a Excel come far corrispondere Lookup_value con i valori in Lookup_array. Le scelte disponibili sono 1, 0 o -1.
Questo argomento è opzionale. Se viene omesso, la funzione utilizza il valore predefinito di 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.
Inserisci questi passaggi dopo la virgola immessa nel passaggio precedente sulla riga Row_num nella funzione INDEX:
- Inserisci 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.
-
Inserisci ) (una parentesi tonda di chiusura) per completare la funzione MATCH.
Image - Lascia la finestra di dialogo aperta per il passaggio successivo del tutorial.
Completa la funzione INDEX
La funzione MATCH è terminata. È ora di passare alla casella di testo Column_num della finestra di dialogo e immettere l'ultimo argomento per la funzione INDEX. 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.
- Posiziona il cursore nella casella di testo Column_num.
-
Inserisci 3 (il numero tre). Questo numero indica alla formula di cercare i dati nella terza colonna dell'intervallo da D6 a F11.
Image - Lascia la finestra di dialogo aperta per il passaggio successivo del tutorial.
Crea la formula dell'array
Prima di chiudere la finestra di dialogo, trasforma la funzione nidificata in una formula di matrice. Questa matrice 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, premi CTRL, MAIUSC e INVIOtasti contemporaneamente. Una volta premuta, la funzione è racchiusa tra parentesi graffe, a indicare che la funzione è ora un array.
- Seleziona OK per chiudere la finestra di dialogo. In Excel per Mac, seleziona Fatto.
- Seleziona la cella F3 per visualizzare la formula, quindi posiziona il cursore alla fine della formula nella barra della formula.
- Per convertire la formula in un array, premi CTRL+ SHIFT+ ENTER.
- Un errore N/D appare nella cella F3. Questa è la cella in cui è stata inserita la funzione.
-
L'errore N/D appare nella cella F3 perché le celle D3 ed E3 sono vuote. D3 ed E3 sono le celle in cui la funzione cerca di trovare Lookup_value. Dopo che i dati sono stati aggiunti a queste due celle, l'errore viene sostituito dalle informazioni del database.
Image
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 Titanio della colonna 2.
Se la formula trova una corrispondenza per entrambi i termini nelle colonne appropriate del database, restituisce il valore dalla terza colonna.
- Seleziona cella Re3.
- Inserisci Widget.
- Seleziona cella MI3.
- Digita Titanium e premi Invio.
- Il nome del fornitore, Widgets Inc., appare nella cella F3. Questo è l'unico fornitore elencato che vende Titanium Widgets.
-
Seleziona cella F3. La funzione viene visualizzata nella barra della formula sopra il foglio di lavoro.
{=INDICE(RE6:FA11, ABBINAMENTO(RE3&MI3, RE6:RE11&MI6:MI11, 0), 3)}
In questo esempio, esiste un solo fornitore per i widget in titanio. Se c'era stato più di un fornitore, il fornitore elencato per primo nel database viene restituito dalla funzione.
Image
