Utilizzando una formula di matrice in Excel è possibile creare una formula di ricerca che utilizza più criteri per trovare informazioni in un database o una tabella di dati. La formula dell'array implica l'annidamento della funzione MATCH all'interno della funzione INDICE.
Questo tutorial include un esempio dettagliato di creazione di una formula di ricerca che utilizza più criteri per trovare un fornitoreWidget in titanio in un database di esempio. Segui per scoprire quanto sia facile creare le tue formule di ricerca personalizzate.
Prepara la tua cartella di lavoro di Excel con i dati
Per seguire i passaggi di questo tutorial, è necessario iniziare inserendo i dati di esempio nelle seguenti celle, come mostrato nell'immagine sopra. Le righe 3 e 4 sono lasciate vuote per poter contenere la formula dell'array creata durante questo tutorial.
- Inserisci l'intervallo più alto di dati nelle celle D1 a F2
- Inserisci il secondo intervallo in celle D5 a F11
Il tutorial non include la formattazione vista nell'immagine, ma ciò non influirà sul funzionamento della formula di ricerca. Le informazioni sulle opzioni di formattazione simili a quelle viste sopra sono disponibili in questo tutorial di formattazione Excel di base.
02 di 08Creare una funzione INDICE in Excel
Il INDICE la funzione è uno dei pochi in Excel che ha più forme. La funzione ha un Forma di matrice e a Modulo di riferimento. Il modulo di array restituisce i dati effettivi da un database o una tabella di dati, mentre il modulo di riferimento fornisce il riferimento di cella o la posizione dei dati nella tabella.
In questo tutorial, useremo il modulo Array poiché desideriamo conoscere il nome del fornitore per i widget in titanio piuttosto che il riferimento di cella a questo fornitore nel nostro database. Attenersi alla seguente procedura per creare la funzione INDICE:
- Clicca sulla cella F3 per renderlo la cella attiva - questo è dove entreremo nella funzione annidata.
- Clicca sul formule scheda del menu della barra multifunzione.
- Scegliere Ricerca e riferimento dalla barra multifunzione per aprire la funzione a discesa.
- Clicca su INDICE nella lista per far apparire il Seleziona argomenti la finestra di dialogo.
- Scegli il array, row_num, col_num opzione nella finestra di dialogo.
- Clic ok per aprire la finestra di dialogo della funzione INDICE; questo aprirà il Formula Builder in Excel.
- Nel Formula Builder, clicca sul schieramento linea.
- Evidenzia celle D6 a F11, facendo clic e trascinando, nel foglio di lavoro per inserire l'intervallo nel builder.
Nelle versioni precedenti di Excel Formula Builder sarà sostituito dal Argomenti della funzione finestra. Applica gli stessi passaggi indicati in questo tutorial nella finestra.
03 di 08Avvia la funzione MATCH annidata
Quando si annida una funzione all'interno di un'altra, non è possibile aprire il generatore di formule della seconda o della funzione annidata o inserire gli argomenti necessari. La funzione nidificata deve essere inserita come una delle ordinate nella 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 MATCH annidato è inserire il file Valore di ricerca discussione. Il valore Lookup_value sarà la posizione o il riferimento di cella per il termine di ricerca che vogliamo abbinare nel database.
Normalmente il valore Lookup_ accetta solo un criterio di ricerca o un termine. Per cercare più criteri, dobbiamo estendere il valore Lookup_; til suo è fatto concatenando o unendo due o più riferimenti di cella usando il simbolo e commerciale - &.
- Nel Formula Builder, clicca sul row_num linea.
- Digita il nome della funzione INCONTRO seguito da una parentesi rotonda aperta.
- Clicca sulla cella D3 per inserire quel riferimento di cella nella finestra di dialogo.
- Digitare una e commerciale dopo il riferimento di cella D3 per aggiungere un secondo riferimento di cella.
- Clicca sulla cella E3 per inserire questo riferimento di seconda cella.
- Digitare una virgola dopo il riferimento di cella E3 per completare la voce della funzione MATCH Valore di ricerca discussione.
Nell'ultimo passaggio del tutorial, il nostro Lookup_values verrà inserito nelle celle D3 ed E3 del foglio di lavoro.
04 di 08Completa la funzione MATCH annidato
Questo passaggio copre l'aggiunta del matrice_ricerca argomento per l'annidato INCONTRO funzione. Lookup_array è l'intervallo di celle che la funzione MATCH cercherà per trovare l'argomento Lookup_value aggiunto nel passaggio precedente del tutorial.
Poiché abbiamo identificato due campi di ricerca nell'argomento Lookup_array, dobbiamo fare lo stesso per Lookup_array. La funzione MATCH cerca solo una matrice per ogni termine specificato, quindi per immettere più array si usa ancora la e commerciale per concatenare gli array.
Questi passaggi devono essere inseriti dopo la virgola immessa nel passaggio precedente sul row_num linea nel INDICE funzione.
- Clicca sul row_num riga dopo la virgola per posizionare il punto di inserimento alla fine della voce corrente.
- Evidenzia celle D6 a D11 nel foglio di lavoro per inserire l'intervallo - questo è il primo array che la funzione deve cercare.
- Digitare una e commerciale dopo i riferimenti di cella D6: D11 perché vogliamo che la funzione ricerchi due array.
- Evidenzia celle E6 a E11 nel foglio di lavoro per inserire l'intervallo - questo è il secondo array che la funzione deve cercare.
- Digitare una virgola dopo il riferimento di cella E3 per completare l'entrata del INCONTRO la funzione di matrice_ricerca discussione.
- Lasciare il INDICE finestra di dialogo della funzione aperta per il passaggio successivo nel tutorial.
Aggiungi l'argomento del tipo MATCH
Il terzo ed ultimo argomento della funzione MATCH è il match_type discussione; dice a Excel come abbinare il Valore di ricerca con valori in Lookup_array: le opzioni disponibili sono 1, 0 o -1.
Questo argomento è facoltativo. Se viene omesso, la funzione utilizza il valore predefinito 1.
- Se la Match_type = 1 o è omesso: MATCH trova il valore più grande che è minore o uguale al valore Lookup_. I dati Lookup_array devono essere ordinati in ordine crescente.
- Se la Match_type = 0: MATCH trova il primo valore esattamente uguale al valore Lookup_. I dati Lookup_array possono essere ordinati in qualsiasi ordine.
- Se la Match_type = -1: MATCH trova il valore più piccolo maggiore o uguale al valore Lookup_. I dati Lookup_array devono essere ordinati in ordine decrescente.
Questi passaggi devono essere inseriti dopo la virgola immessa nel passaggio precedente sul row_num linea nel INDICE funzione.
- Seguendo la virgola sul row_num linea, digita uno zero - 0 - dal momento che vogliamo che la funzione nidificata restituisca corrispondenze esatte ai termini inseriti nelle celle D3 e E3.
- Digitare una parentesi rotonda di chiusura - ) - per completare il INCONTRO funzione.
- Lasciare il Formula Builder apri per il prossimo passo nel tutorial.
Finitura della funzione INDICE
Ora che il INCONTRO la funzione è fatta passeremo alla terza riga del Generatore di formule e inseriremo l'ultimo argomento per il INDICE funzione. Questo terzo ed ultimo argomento è il column_num argomento che indica a Excel il numero della colonna nell'intervallo D6 a F11; questo è dove troverà le informazioni che vogliamo essere restituite dalla funzione. In questo caso, un fornitore per widget in titanio.
- Clicca sul column_num linea.
- Inserisci il numero tre - 3 - su questa linea poiché stiamo cercando i dati nella terza colonna dell'intervallo D6 a F11.
- Di nuovo, lascia il Formula Builder apri per il prossimo passo nel tutorial.
Creazione della formula di matrice
Prima di chiudere il Formula Builder, dobbiamo trasformare la nostra funzione annidata in una formula di matrice; questo è ciò che permette alla nostra funzione di cercare più termini nella tabella dei dati. In questo tutorial, stiamo cercando di abbinare due termini: widget dalla colonna 1 e Titanio dalla colonna 2.
La creazione di una formula di matrice in Excel avviene premendo il tasto CTRL, CAMBIO, e ACCEDERE tasti sulla tastiera allo stesso tempo. Una volta premuto, la funzione sarà circondata da parentesi graffe, a indicare che la funzione è ora una matrice.
- Chiudi il Formula Builder cliccando il Fatto pulsante.
- Quindi, seleziona la cella F4 e premere il accedere chiave per visualizzare la formula.
- Per convertire la formula in un array, premere contemporaneamente CTRL + MAIUSC + INVIO sulla tua tastiera.
- Se fatto correttamente a #N / A errore apparirà nella cella F3 - la cella in cui abbiamo inserito la funzione.
- Il #N / A l'errore appare nella cella F3 perché le cellule D3 e E3 sono vuoti D3 ed E3 sono le celle in cui abbiamo detto alla funzione di trovare il Lookup_values. Una volta che i dati vengono aggiunti a queste due celle, l'errore verrà sostituito dalle informazioni del database.
Aggiunta dei criteri di ricerca
Infine, aggiungeremo i termini di ricerca al nostro foglio di lavoro. Come accennato nel passaggio precedente, stiamo cercando di abbinare i termini widget dalla colonna 1 e Titanio dalla colonna 2. Se, e solo se, la nostra formula trova una corrispondenza per entrambi i termini nelle colonne appropriate nel database, restituirà il valore dalla terza colonna.
- Clicca sulla cella D3.
- genere widget e premere il accedere tasto sulla tastiera.
- Clicca sulla cella E3.
- genere Titanio e premere il accedere tasto sulla tastiera.
- Il nome del fornitore Widgets Inc. dovrebbe apparire nella cella F3 - l'ubicazione della funzione poiché è l'unico fornitore elencato che vende Titanium Widgets.
- Quando fai clic sulla cella F3, la funzione completa appare nella barra della formula sopra il foglio di lavoro.
{= INDICE (D6: F11, MATCH (D3 & E3, D6: D11 & E6: E11, 0), 3)}
Nel nostro esempio, c'era un solo fornitore per i widget in titanio. Se esisteva più di un fornitore, il fornitore elencato per primo nel database viene restituito dalla funzione.
