Què cal saber
- Primer, creeu una funció INDEX i, a continuació, inicieu la funció MATCH imbricada introduint l'argument Lookup_value.
- A continuació, afegiu l'argument Lookup_array seguit de l'argument Match_type i, a continuació, especifiqueu l'interval de columnes.
- A continuació, converteix la funció imbricada en una fórmula de matriu prement Ctrl+ Maj+ Retorn. Finalment, afegiu els termes de cerca al full de treball.
Aquest article explica com crear una fórmula de cerca que utilitzi diversos criteris a Excel per trobar informació en una base de dades o taula de dades mitjançant una fórmula de matriu. La fórmula de matriu implica niar la funció MATCH dins de la funció INDEX. La informació inclou Excel per a Microsoft 365, Excel 2019, Excel 2016, Excel 2013, Excel 2010 i Excel per a Mac.
Seguir juntament amb el tutorial
Per seguir els passos d'aquest tutorial, introduïu les dades de mostra a les cel·les següents, tal com es mostra a la imatge següent. Les files 3 i 4 es deixen en blanc per adaptar-se a la fórmula de matriu creada durant aquest tutorial. (Tingueu en compte que aquest tutorial no inclou el format que es veu a la imatge.)
- Introduïu l'interval superior de dades a les cel·les D1 a F2.
- Introduïu el segon interval a les cel·les D5 a F11.
Crear una funció INDEX a Excel
La funció INDEX és una de les poques funcions d'Excel que té múltiples formes. La funció té un formulari de matriu i un formulari de referència. El formulari de matriu retorna les dades d'una base de dades o taula de dades. El formulari de referència proporciona la referència de la cel·la o la ubicació de les dades a la taula.
En aquest tutorial, el formulari de matriu s'utilitza per trobar el nom del proveïdor dels ginys de titani, en lloc de la referència de cel·la a aquest proveïdor a la base de dades.
Seguiu aquests passos per crear la funció INDEX:
- Seleccioneu la cel·la F3 per convertir-la en la cel·la activa. Aquesta cel·la és on s'introduirà la funció imbricada.
-
Vés a Fórmules.
Image - Trieu Cerca i referència per obrir la llista desplegable de funcions.
- Seleccioneu INDEX per obrir el quadre de diàleg Seleccioneu els arguments.
- Tria matriu, row_num, column_num.
- Seleccioneu D'acord per obrir el quadre de diàleg Arguments de funció. A Excel per a Mac, s'obre Formula Builder.
- Coloqueu el cursor al quadre de text Matriu.
-
Res alta les cel·les D6 a F11 al full de treball per introduir l'interval al quadre de diàleg.
Deixeu obert el quadre de diàleg Arguments de funció. La fórmula no està acabada. Completaràs la fórmula a les instruccions següents.
Image
Inicia la funció MATCH anidada
Quan nidifiqueu una funció dins d'una altra, no és possible obrir el constructor de fórmules de la segona funció, o imbricada, per introduir els arguments necessaris. La funció imbricada s'ha d'introduir com un dels arguments de la primera funció.
Quan s'introdueixen funcions manualment, els arguments de la funció estan separats entre si per una coma.
El primer pas per introduir la funció MATCH imbricada és introduir l'argument Lookup_value. El valor_de_cerca és la ubicació o la referència de la cel·la del terme de cerca que s'ha de fer coincidir a la base de dades.
El Lookup_value només accepta un criteri o terme de cerca. Per cercar diversos criteris, amplia el Lookup_value concatenant o unint dues o més referències de cel·les utilitzant el símbol et (&).
- Al quadre de diàleg Arguments de funció, col·loqueu el cursor al quadre de text Row_num.
- Introduïu COINCIDENT (.
- Seleccioneu la cel·la D3 per introduir la referència de la cel·la al quadre de diàleg.
- Introduïu & (sandes) després de la referència de la cel·la D3 per afegir una segona referència de cel·la.
- Seleccioneu la cel·la E3 per introduir la referència de la segona cel·la.
-
Introduïu , (una coma) després de la referència de cel·la E3 per completar l'entrada de l'argument Lookup_value de la funció MATCH.
Image A l'últim pas del tutorial, els valors de cerca s'introduiran a les cel·les D3 i E3 del full de treball.
Completa la funció MATCH anidada
Aquest pas inclou afegir l'argument Lookup_array per a la funció MATCH imbricada. La matriu_cerques és l'interval de cel·les que la funció COINCORDA cerca per trobar l'argument valor_cerca afegit al pas anterior del tutorial.
Com que s'han identificat dos camps de cerca a l'argument Lookup_array, s'ha de fer el mateix amb Lookup_array. La funció MATCH només cerca una matriu per a cada terme especificat. Per introduir diverses matrius, utilitzeu el signe et per concatenar les matrius juntes.
- Coloqueu el cursor al final de les dades al quadre de text Row_num. El cursor apareix després de la coma al final de l'entrada actual.
- Res alta les cel·les D6 fins a D11 al full de treball per introduir l'interval. Aquest interval és la primera matriu que cerca la funció.
- Introduïu & (ampersand) després de les referències de cel·la D6:D11. Aquest símbol fa que la funció cerqui dues matrius.
- Res alta les cel·les E6 a E11 al full de treball per introduir l'interval. Aquest interval és la segona matriu que cerca la funció.
-
Introduïu , (una coma) després de la referència de la cel·la E3 per completar l'entrada de l'argument Lookup_array de la funció MATCH.
Image - Deixeu el quadre de diàleg obert per al següent pas del tutorial.
Afegiu l'argument del tipus MATCH
El tercer i últim argument de la funció COINCORDA és l'argument Match_type. Aquest argument indica a Excel com fer coincidir el Lookup_value amb els valors del Lookup_array. Les opcions disponibles són 1, 0 o -1.
Aquest argument és opcional. Si s'omet, la funció utilitza el valor predeterminat d'1.
- Si Match_type=1 o s'omet, MATCH troba el valor més gran que és menor o igual que Lookup_value. Les dades de Lookup_array s'han d'ordenar en ordre ascendent.
- Si Match_type=0, MATCH troba el primer valor que és igual al Lookup_value. Les dades de Lookup_array es poden ordenar en qualsevol ordre.
- Si Match_type=-1, MATCH troba el valor més petit que és més gran o igual que el Lookup_value. Les dades de Lookup_array s'han d'ordenar en ordre descendent.
Introduïu aquests passos després de la coma introduïda al pas anterior a la línia Row_num a la funció INDEX:
- Introduïu 0 (un zero) després de la coma al quadre de text Row_num. Aquest número fa que la funció imbricada retorni coincidències exactes amb els termes introduïts a les cel·les D3 i E3.
-
Introduïu ) (un claudàtor rodó de tancament) per completar la funció COINCIDENT.
Image - Deixeu el quadre de diàleg obert per al següent pas del tutorial.
Acabar la funció INDEX
La funció MATCH està feta. És hora de passar al quadre de text Column_num del quadre de diàleg i introduir l'últim argument per a la funció INDEX. Aquest argument indica a Excel que el número de columna està en l'interval D6 a F11. Aquest interval és on troba la informació que retorna la funció. En aquest cas, un proveïdor de ginys de titani.
- Coloqueu el cursor al quadre de text Column_num.
-
Introduïu 3 (el número tres). Aquest número indica a la fórmula que busqui dades a la tercera columna de l'interval D6 a F11.
Image - Deixeu el quadre de diàleg obert per al següent pas del tutorial.
Crea la fórmula de matriu
Abans de tancar el quadre de diàleg, converteix la funció imbricada en una fórmula de matriu. Aquesta matriu permet que la funció cerqui diversos termes a la taula de dades. En aquest tutorial, es combinen dos termes: widgets de la columna 1 i Titanium de la columna 2.
Per crear una fórmula de matriu a Excel, premeu CTRL, MAJ i ENTERtecles simultàniament. Un cop premuda, la funció està envoltada de claus, cosa que indica que ara la funció és una matriu.
- Seleccioneu D'acord per tancar el quadre de diàleg. A Excel per a Mac, seleccioneu Fet.
- Seleccioneu la cel·la F3 per veure la fórmula i, a continuació, col·loqueu el cursor al final de la fórmula a la barra de fórmules.
- Per convertir la fórmula en una matriu, premeu CTRL+ MAJ+ ENTER.
- A la cel·la F3 apareix un error N/A. Aquesta és la cel·la on s'ha introduït la funció.
-
L'error N/A apareix a la cel·la F3 perquè les cel·les D3 i E3 estan en blanc. D3 i E3 són les cel·les on busca la funció per trobar el valor_de_cerca. Després d'afegir dades a aquestes dues cel·les, l'error se substitueix per informació de la base de dades.
Image
Afegiu els criteris de cerca
L'últim pas és afegir els termes de cerca al full de treball. Aquest pas coincideix amb els termes Widgets de la columna 1 i Titanium de la columna 2.
Si la fórmula troba una coincidència per als dos termes a les columnes adequades de la base de dades, retorna el valor de la tercera columna.
- Selecciona la cel·la D3.
- Introduïu Widgets.
- Selecciona la cel·la E3.
- Escriviu Titani i premeu Retorn.
- El nom del proveïdor, Widgets Inc., apareix a la cel·la F3. Aquest és l'únic proveïdor a la llista que ven widgets de titani.
-
Selecciona la cel·la F3. La funció apareix a la barra de fórmules a sobre del full de treball.
{=ÍNDEX(D6:F11, MATCH(D3&E3, D6:D11&E6:E11, 0), 3)}
En aquest exemple, només hi ha un proveïdor de ginys de titani. Si hi havia hagut més d'un proveïdor, la funció retorna el proveïdor que apareix primer a la base de dades.
Image
