Mitjançant l'ús d'una fórmula matricial a Excel, podem crear una fórmula de cerca que utilitzi diversos criteris per trobar informació en una base de dades o taula de dades.
La fórmula matricial implica la nidificació de la funció MATCH dins de la funció INDEX .
Aquest tutorial inclou un exemple pas a pas de crear una fórmula de cerca que utilitza diversos criteris per trobar un proveïdor de widgets de titani en una base de dades d'exemple.
Seguint els passos dels temes de tutoria que hi ha a continuació, us explica la creació i l'ús de la fórmula que es mostra a la imatge anterior.
01 de 09
Introduir les dades del tutorial
El primer pas del tutorial és introduir les dades en un full de càlcul d' Excel.
Per seguir els passos del tutorial, introduïu les dades que es mostren a la imatge anterior a les cel·les següents.
- Introduïu l' interval superior de dades a les cel·les D1 a F2
- Introduïu el segon rang a les cel·les D5 a F11
Les files 3 i 4 es deixen en blanc per tal d'acomodar la fórmula matricial creada durant aquest tutorial.
El tutorial no inclou el format vist a la imatge, però això no afectarà la forma en què funciona la fórmula de cerca.
La informació sobre opcions de format similar a les que es veuen a dalt està disponible en aquest Tutorial bàsic de formatació d'Excel.
02 de 09
Començant la funció INDEX
La funció INDEX és una de les poques en Excel que té diverses formes. La funció té un formulari de matriu i un formulari de referència .
El formulari Array retorna les dades reals d'una base de dades o taula de dades, mentre que el Formulari de referència us proporciona la referència o la ubicació de les dades a la taula.
En aquest tutorial utilitzarem el Formulari Array ja que volem saber el nom d'un proveïdor de widgets de titani en lloc de la referència de la cel·la a aquest proveïdor a la nostra base de dades.
Cada formulari té una llista diferent d' arguments que s'han de seleccionar abans de començar la funció.
Tutorial Passos
- Feu clic a la cel·la F3 per fer-la la cel·la activa . Aquí és on entrarem a la funció anidada.
- Feu clic a la pestanya Fórmules del menú de la cinta .
- Trieu la cerca i la referència de la cinta per obrir la llista desplegable de la funció.
- Feu clic a INDEX a la llista per mostrar el quadre de diàleg Seleccionar arguments .
- Trieu l'opció matriu, row_num, col_num al quadre de diàleg.
- Feu clic a D'acord per obrir el quadre de diàleg de la funció INDEX.
03 de 09
Introduir l'argument INDEX Array de funcions
El primer argument necessari és l'argument Array. Aquest argument especifica l' interval de cel·les a buscar a les dades desitjades.
Per a aquest tutorial, aquest argument serà la nostra base de dades d' exemple.
Tutorial Passos
- Al quadre de diàleg de la funció INDEX, feu clic a la línia Array .
- Ressalteu les cel·les D6 a F11 al full de treball per introduir el rang al quadre de diàleg.
04 de 09
S'està iniciant la funció MATCH anida
En nidificar una funció dins d'un altre no és possible obrir el quadre de diàleg de la segona funció o anidar per introduir els arguments necessaris.
La funció anida s'ha d'escriure com un dels arguments de la primera funció.
En aquest tutorial, la funció MATCH anida i els seus arguments s'introdueixen en la segona línia del quadre de diàleg de la funció INDEX - la línia Row_num .
És important tenir en compte que, quan s'introdueixen manualment les funcions, els arguments de la funció estan separats entre si per una coma "," .
Introduir l'argument Lookup_value de la funció MATCH
El primer pas per entrar a la funció MATCH anït és introduir l'argument Lookup_value .
The Lookup_value serà la referència de la ubicació o de la cel·la per al terme de cerca que volem que coincideixi a la base de dades.
Normalment, el Lookup_value accepta només un criteri de cerca o un terme. Per buscar diversos criteris, hem d'ampliar el valor de cerca .
Això es fa concatenant o unint dues o més referències de cel·les juntament amb l'ampersand símbol " & ".
Tutorial Passos
- Al quadre de diàleg de la funció INDEX, feu clic a la línia Row_num .
- Escriviu la coincidència del nom de la funció seguida d'un suport obert " ( "
- Feu clic a la cel·la D3 per introduir la referència de la cel a la caixa de diàleg.
- Escriviu un ampersand " & " després de la referència de cel·la D3 per afegir una segona referència de cel·la.
- Feu clic a la cel·la E3 per introduir aquesta segona referència de cel·la al quadre de diàleg.
- Escriviu 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.
- Deixeu obrir el quadre de diàleg de la funció INDEX per al següent pas del tutorial.
En l'últim pas del tutorial, els Valors de cerca s'introdueixen a les cel·les D3 i E3 del full de treball.
05 de 09
S'està afegint el Lookup_array per a la funció MATCH
Aquest pas inclou l'addició de l' argument Lookup_array per a la funció MATCH anida.
The Lookup_array és el rang de cel·les que la funció MATCH buscarà per trobar l'argument Lookup_value afegit en el pas anterior del tutorial.
Atès que hem identificat dos camps de cerca a l'argument Lookup_array hem de fer el mateix per Lookup_array . La funció MATCH només cerca una matriu per a cada terme especificat.
Per introduir diverses matrius, tornem a utilitzar l'ampersand " & " per concatenar conjuntament les matrius.
Tutorial Passos
Aquests passos s'han d'introduir després de la coma introduïda en el pas anterior a la línia Row_num en el quadre de diàleg de la funció INDEX.
- Feu clic a la línia Row_num després de la coma per col·locar el punt d'inserció al final de l'entrada actual.
- Ressalteu les cel·les D6 a D11 al full de treball per entrar al rang. Aquesta és la primera matriu que la funció és buscar.
- Escrigui un ampersand " & " després de les referències de la cel·la D6: D11 perquè volem que la funció busqui dues matrius.
- Ressalteu les cel·les E6 a E11 al full de treball per entrar al rang. Aquesta és la segona matriu que la funció és buscar.
- Escriviu una coma "," després de la referència de cel·la E3 per completar l'entrada de l'argument Lookup_array de la funció MATCH.
- Deixeu obrir el quadre de diàleg de la funció INDEX per al següent pas del tutorial.
06 de 09
Agregar el tipus de concordança i Completar la funció MATCH
El tercer i últim argument de la funció MATCH és l' argument Match_type.
Aquest argument diu a Excel com combinar el valor de cerca amb valors al Lookup_array. Les opcions són: 1, 0 o -1.
Aquest argument és opcional. Si s'omet, la funció utilitza el valor predeterminat de 1.
- si el Match_type = 1 o s'omet: MATCH troba el valor més gran que és inferior o igual al valor_Verup. Les dades de Lookup_array s'han d' ordenar en ordre ascendent.
- si el match_type = 0: MATCH troba el primer valor que és exactament igual al valor_Verup. Les dades de Lookup_array es poden ordenar en qualsevol ordre.
- si el Match_type = -1: MATCH troba el valor més petit que és major o igual que el valor_Veure. Les dades de Lookup_array s'han d'ordenar en ordre descendent.
Tutorial Passos
Aquests passos s'han d'introduir després de la coma introduïda en el pas anterior a la línia Row_num en el quadre de diàleg de la funció INDEX.
- Seguint la coma a la línia Row_num , escriviu zero " 0 " ja que volem que la funció anida retorni coincidències exactes als termes que introduïm a les cel·les D3 i E3.
- Escriviu un clauer de tancament " ) " per completar la funció MATCH.
- Deixeu obrir el quadre de diàleg de la funció INDEX per al següent pas del tutorial.
07 de 09
Torna a la funció INDEX
Ara que la funció MATCH es fa, passarem a la tercera línia del quadre de diàleg obert i introduirem l'últim argument per a la funció INDEX.
Aquest tercer i últim argument és l'argument Column_num que indica a Excel el número de columna en el rang D6 a F11 on trobarà la informació que volem retornada per la funció. En aquest cas, un proveïdor de widgets de titani .
Tutorial Passos
- Feu clic a la línia Column_num al quadre de diàleg.
- Introduïu el número tres " 3 " (sense cometes) en aquesta línia ja que estem buscant dades a la tercera columna del rang D6 a F11.
- No feu clic a D'acord o tanqueu el quadre de diàleg de la funció INDEX. Ha de romandre obert per al següent pas del tutorial: crea la fórmula matricial .
08 de 09
Creació de la fórmula de matriu
Abans de tancar el quadre de diàleg, hem de convertir la nostra funció anidada en una fórmula de matriu .
Una fórmula matricial és la que li permet cercar diversos termes a la taula de dades. En aquest tutorial busquem coincidir amb dos termes: Ginys de la columna 1 i el titani de la columna 2.
La creació d'una fórmula de matriu en Excel es fa prement les tecles CTRL , SHIFT i ENTER al teclat alhora.
L'efecte de prémer aquestes tecles juntes és envoltar la funció amb claudàtors: {} indicant que ara és una fórmula matricial.
Tutorial Passos
- Amb el quadre de diàleg completat encara obert des del pas anterior d'aquest tutorial, manteniu premut les tecles CTRL i SHIFT al teclat i després premeu i deixeu anar la tecla ENTRAR .
- Si es fa correctament, es tancarà el quadre de diàleg i apareixerà un error # N / A a la cel·la F3: la cel·la on hem 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 hem dit la funció de trobar els Valors de cerca al pas 5 del tutorial. Un cop afegides les dades a aquestes dues cel·les, l'error es reemplaçarà per la informació de la base de dades .
09 de 09
Addició dels criteris de cerca
L'últim pas del tutorial és afegir els termes de cerca al nostre full de càlcul.
Com es va esmentar en el pas anterior, estem buscant que coincideixi amb els termes Widgets de la columna 1 i Titani de la columna 2.
Si, i només si, la nostra fórmula troba una coincidència d'ambdós termes a les columnes adequades de la base de dades, retornarà el valor de la tercera columna.
Tutorial Passos
- Feu clic a la cel·la D3.
- Escriviu Widgets i premeu la tecla Enter al teclat.
- Feu clic a la cel·la E3.
- Escriviu Titanium i premeu la tecla Enter al teclat.
- El nom del proveïdor Widgets Inc. hauria d'aparèixer a la cel·la F3: la ubicació de la funció ja que és l'únic proveïdor que es troba a la venda de Titanium Widgets.
- Quan feu clic a la cel·la F3, la funció completa
{= INDEX (D6: F11, MATCH (D3 & E3, D6: D11 & E6: E11, 0), 3)}
apareix a la barra de fórmules que hi ha a sobre del full de càlcul .
Nota: en el nostre exemple, només hi havia un proveïdor de widgets de titani. Si hi hagués més d'un proveïdor, el proveïdor que figura primer en la base de dades és retornat per la funció.