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 utilitzi diversos criteris per trobar un proveïdorestris de titani en una base de dades d'exemple. Seguiu tot seguit per saber el fàcil que és crear les vostres pròpies fórmules de cerca personalitzades.
Prepareu el llibre de treball d'Excel amb dades
Per seguir els passos d'aquest tutorial, haureu de començar introduint les dades d'exemple a les cel·les següents, tal com es mostra a la imatge de dalt. Les files 3 i 4 es deixen en blanc per tal d'acomodar la fórmula matricial creada durant aquest tutorial.
- Introduïu el rang superior de dades a les cel·les D1 a F2
- Introduïu el segon interval a les cel·les D5 a F11
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 08Creeu una funció INDEX a Excel
El ÍNDEX La funció és una de les poques d'Excel que té diverses formes. La funció té una Formulari de matriu i a 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 del proveïdor per a widgets de titani en lloc de la referència de la cel·la a aquest proveïdor a la nostra base de dades. Seguiu aquests passos per crear la vostra funció INDEX:
- Feu clic a la cel·la F3 per convertir-lo en la cel·la activa - aquí és on entrarem a la funció anidada.
- Feu clic al Fórmules pestanya del menú de la cinta.
- Trieu Cerca i referència des de la barra de cinta per obrir la funció desplegable.
- Fer clic a ÍNDEX a la llista per obrir el Seleccioneu arguments Caixa de diàleg.
- Escull el matriu, row_num, col_num opció al quadre de diàleg.
- Feu clic D'acord per obrir el quadre de diàleg de la funció ÍNDEX; això obrirà el Formula Builder a Excel.
- A la Formula Builder, feu clic a la icona Array línia
- Ressalteu les cel·les D6 a F11, fent clic i arrossegant, al full de treball per introduir el rang en el constructor.
En versions anteriors de l'Excel Formula Builder serà reemplaçat per la Arguments de funcions finestra. Apliqueu els mateixos passos que es mencionen en aquest tutorial a la finestra.
03 de 08Comenceu la funció MATCH anida
Quan nidifica una funció dins d'un altre no és possible obrir el constructor de fórmules de la funció secundària o anidada o introduïu els arguments necessaris. La funció anida s'ha d'escriure com una de les ordenades de la primera funció.
Quan s'introdueixen les funcions manualment, els arguments de la funció estan separats entre si per una coma.
El primer pas per entrar a la funció MATCH anida és introduir el Valor_valueu argument 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; tla seva es fa concatenant o unint dues o més referències de cel·les juntament amb el símbol ampersand i - &.
- A la Formula Builder, feu clic a la icona Row_num línia
- Escriviu el nom de la funció MATCH seguit d'un suport obert.
- Feu clic a la cel·la D3 per introduir aquesta referència de cel·la al quadre de diàleg.
- Escriviu un nombre i després de la referència de la cel·la D3 per afegir una segona referència de cel·la.
- Feu clic a la cel·la E3 per entrar en aquesta segona referència de cel·la.
- Escriviu una coma després de la referència de la cel·la E3 per completar l'entrada de la funció MATCH Valor_valueu argument
En l'últim pas del tutorial, el nostre Valors de cerca s'introduirà a les cel·les D3 i E3 del full de treball.
04 de 08Completa la funció MATCH anida
Aquest pas inclou l'addició de Lookup_array argument per a l'anidado MATCH funció 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 busca una matriu per a cada terme especificat, per tant, per introduir diverses matrius, tornem a utilitzar els àlbums i per concatenar conjuntament les matrius.
Aquests passos s'han d'introduir després de la coma introduïda al pas anterior a la finestra Row_num línia al ÍNDEX funció
- Feu clic al Row_num línia 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 en el full de treball per entrar al rang: aquesta és la primera matriu que la funció és buscar.
- Escriviu un nombre i 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 en el full de càlcul per entrar al rang: aquesta és la segona matriu que la funció és buscar.
- Escriviu una coma després de la referència de la cel·la E3 per completar l 'entrada del MATCH funció Lookup_array argument
- Deixeu-ho ÍNDEX quadre de diàleg de funció obert per al següent pas del tutorial.
Afegiu l'argument MATCH Tipus
El tercer i últim argument de la funció MATCH és el Match_type argument; diu a Excel com fer coincidir el Valor_valueu amb valors a Lookup_array: les opcions disponibles 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 bé s'omet: MATCH troba el valor més gran que és inferior o igual al Valor de cerca_. 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 superior o igual al valor_Veure. Les dades de Lookup_array s'han d'ordenar en ordre descendent.
Aquests passos s'han d'introduir després de la coma introduïda al pas anterior a la finestra Row_num línia al ÍNDEX funció
- Seguint la coma a la Row_num línia, 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.
- Escriu un clauer de tancament - ) - per completar la MATCH funció
- Deixeu-ho Formula Builder obriu el següent pas del tutorial.
Acabar la funció INDEX
Ara que el MATCH Per fer-ho, passem a la tercera línia del Formula Builder i introduïu l'últim argument per a la ÍNDEX funció Aquest tercer i últim argument és el Columna_nom argument que indica a Excel el número de columna en el rang D6 a F11; aquí és on trobareu la informació que volem retornar per la funció. En aquest cas, un proveïdor per a estris de titani.
- Feu clic al Columna_nom línia
- Introduïu el número tres: 3 - en aquesta línia ja que estem buscant dades a la tercera columna del rang D6 a F11.
- De nou, deixeu el Formula Builder obriu el següent pas del tutorial.
Creació de la fórmula de matriu
Abans de tancar la Formula Builder, hem de convertir la nostra funció anidada en una fórmula matricial; això és el que permet que la nostra funció busqui diversos termes a la taula de dades. En aquest tutorial, busquem coincidir amb dos termes: Ginys des de la columna 1 i Titani de la columna 2.
La creació d'una fórmula matricial a Excel es fa prement la tecla CTRL, MAYÚS, i ENTRAR tecles al teclat al mateix temps. Un cop premuda, la funció es veurà envoltada de claudàtors, indicant que la funció ara és una matriu.
- Tanqueu el Formula Builder fent clic al botó Fet botó
- A continuació, seleccioneu cel·la F4 i premeu la tecla Entra clau per veure la fórmula.
- Per convertir la fórmula a una matriu, premeu simultàniament CTRL + MAYÚS + ENTRAR al teclat.
- Si es fa correctament a # N / A apareixerà un error a la cel·la F3: la cel·la on hem introduït la funció.
- El # N / A apareix un error a la cel·la F3 perquè les cel·les D3 i E3 estan en blanc. D3 i E3 són les cel·les on us detallem la funció de trobar el Valors de cerca. Un cop afegides les dades a aquestes dues cel·les, l'error es reemplaçarà per la informació de la base de dades.
Addició dels criteris de cerca
Finalment, afegirem 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 Ginys des de la columna 1 i Titani de la columna 2. Si, i només si, la nostra fórmula troba una coincidència per ambdós termes a les columnes adequades de la base de dades, retornarà el valor de la tercera columna.
- Feu clic a la cel·la D3.
- Escriviu Ginys i premeu la tecla Entra tecla al teclat.
- Feu clic a la cel·la E3.
- Escriviu Titani i premeu la tecla Entra tecla 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 apareix a la barra de fórmules que hi ha a sobre del full de càlcul.
{= INDEX (D6: F11, MATCH (D3 & E3, D6: D11 & E6: E11, 0), 3)}
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ó.
