Wat om te weet
- Skep eers 'n INDEX-funksie, en begin dan die geneste MATCH-funksie deur die Lookup_value-argument in te voer.
- Volgende, voeg die Lookup_array-argument by, gevolg deur die Match_type-argument, en spesifiseer dan die kolomreeks.
- Verander dan die geneste funksie in 'n skikkingsformule deur Ctrl+ Shift+ Enter te druk. Laastens, voeg die soekterme by die werkblad.
Hierdie artikel verduidelik hoe om 'n opsoekformule te skep wat veelvuldige kriteria in Excel gebruik om inligting in 'n databasis of tabel met data te vind deur 'n skikkingsformule te gebruik. Die skikkingsformule behels die nes van die MATCH-funksie binne die INDEX-funksie. Inligting dek Excel vir Microsoft 365, Excel 2019, Excel 2016, Excel 2013, Excel 2010 en Excel vir Mac.
Volg saam met die tutoriaal
Om die stappe in hierdie tutoriaal te volg, voer die voorbeelddata in die volgende selle in, soos in die prent hieronder getoon. Rye 3 en 4 word leeg gelaat om die skikkingsformule wat tydens hierdie tutoriaal geskep is, te akkommodeer. (Let daarop dat hierdie tutoriaal nie die formatering insluit wat in die prent gesien word nie.)
- Voer die boonste reeks data in selle D1 tot F2 in.
- Voer die tweede reeks in selle D5 tot F11 in.
Skep 'n INDEX-funksie in Excel
Die INDEX-funksie is een van die min funksies in Excel wat verskeie vorms het. Die funksie het 'n Skikkingsvorm en 'n Verwysingsvorm. Die Array Form gee die data terug vanaf 'n databasis of tabel van data. Die verwysingsvorm gee die selverwysing of ligging van die data in die tabel.
In hierdie tutoriaal word die Skikkingsvorm gebruik om die naam van die verskaffer vir titanium-legstukke te vind, eerder as die selverwysing na hierdie verskaffer in die databasis.
Volg hierdie stappe om die INDEX-funksie te skep:
- Kies sel F3 om dit die aktiewe sel te maak. Hierdie sel is waar die geneste funksie ingevoer sal word.
-
Gaan na Formules.
Image - Kies Opsoek en verwysing om die funksie-aftreklys oop te maak.
- Kies INDEX om die Select Arguments dialoogkassie oop te maak.
- Kies array, row_num, column_num.
- Kies OK om die Function Arguments dialoogkassie oop te maak. In Excel vir Mac maak die Formulebouer oop.
- Plaas die wyser in die Array tekskassie.
-
Highlight selle D6 tot F11 in die werkblad om die reeks in die dialoogkassie in te voer.
Laat die dialoogkassie Funksie-argumente oop. Die formule is nie klaar nie. Jy sal die formule in die instruksies hieronder voltooi.
Image
Begin die Nested MATCH-funksie
Wanneer een funksie binne-in 'n ander genes word, is dit nie moontlik om die tweede, of geneste, funksie se formulebouer oop te maak om die nodige argumente in te voer nie. Die geneste funksie moet ingevoer word as een van die argumente van die eerste funksie.
Wanneer funksies met die hand ingevoer word, word die funksie se argumente van mekaar geskei deur 'n komma.
Die eerste stap om die geneste MATCH-funksie in te voer, is om die Lookup_value-argument in te voer. Die Lookup_value is die ligging of selverwysing vir die soekterm wat in die databasis pas.
The Lookup_value aanvaar slegs een soekkriteria of term. Om vir veelvuldige kriteria te soek, brei die Lookup_value uit deur twee of meer selverwysings aaneen te koppel of saam te voeg deur die ampersand-simbool (&) te gebruik.
-
In die Function Arguments dialoogkassie, plaas die wyser in die Row_num tekskassie.
- Voer MATCH(. in
- Kies sel D3 om daardie selverwysing in die dialoogkassie in te voer.
- Voer & (die ampersand) na die selverwysing D3 in om 'n tweede selverwysing by te voeg.
- Kies sel E3 om die tweede selverwysing in te voer.
-
Voer in,('n komma) na die selverwysing E3 om die inskrywing van die MATCH-funksie se Lookup_value-argument te voltooi.
Image In die laaste stap van die tutoriaal sal die Lookup_values in selle D3 en E3 van die werkblad ingevoer word.
Voltooi die geneste MATCH-funksie
Hierdie stap dek die byvoeging van die Lookup_array-argument vir die geneste MATCH-funksie. Die Lookup_array is die reeks selle wat die MATCH-funksie deursoek om die Lookup_value-argument te vind wat in die vorige stap van die tutoriaal bygevoeg is.
Omdat twee soekvelde in die Lookup_array-argument geïdentifiseer is, moet dieselfde gedoen word vir die Lookup_array. Die MATCH-funksie soek slegs een skikking vir elke term gespesifiseer. Om veelvuldige skikkings in te voer, gebruik die ampersand om die skikkings saam te voeg.
- Plaas die wyser aan die einde van die data in die Row_num tekskassie. Die wyser verskyn na die komma aan die einde van die huidige inskrywing.
- Highlight selle D6 tot D11 in die werkblad om die reeks te betree. Hierdie reeks is die eerste skikking wat die funksie soek.
- Voer & ('n ampersand) in na die selverwysings D6:D11. Hierdie simbool veroorsaak dat die funksie twee skikkings soek.
- Highlight selle E6 tot E11 in die werkblad om die reeks te betree. Hierdie reeks is die tweede skikking wat die funksie soek.
-
Voer , ('n komma) na die selverwysing E3 in om die inskrywing van die MATCH-funksie se Lookup_array-argument te voltooi.
Image - Laat die dialoogkassie oop vir die volgende stap in die tutoriaal.
Voeg die MATCH Tipe-argument by
Die derde en laaste argument van die MATCH-funksie is die Match_type-argument. Hierdie argument vertel Excel hoe om die Lookup_value met waardes in die Lookup_array te pas. Die beskikbare keuses is 1, 0 of -1.
Hierdie argument is opsioneel. As dit weggelaat word, gebruik die funksie die verstekwaarde van 1.
- As Match_type=1 of weggelaat word, vind MATCH die grootste waarde wat minder as of gelyk is aan die Lookup_value. Die Lookup_array-data moet in stygende volgorde gesorteer word.
- As Match_type=0, MATCH vind die eerste waarde wat gelyk is aan die Lookup_value. Die Lookup_array-data kan in enige volgorde gesorteer word.
- As Match_type=-1, MATCH vind die kleinste waarde wat groter as of gelyk aan die Lookup_value is. Die Lookup_array-data moet in dalende volgorde gesorteer word.
Voer hierdie stappe in na die komma wat in die vorige stap ingevoer is op die Ry_num-lyn in die INDEX-funksie:
- Voer 0 ('n nul) na die komma in die Row_num tekskassie in. Hierdie nommer veroorsaak dat die geneste funksie presiese passings terugstuur na die terme wat in selle D3 en E3 ingevoer is.
-
Voer ) ('n sluitingsronde hakie) in om die MATCH-funksie te voltooi.
Image - Laat die dialoogkassie oop vir die volgende stap in die tutoriaal.
Voltooi die INDEX-funksie
Die MATCH-funksie is klaar. Dit is tyd om na die Column_num-tekskassie van die dialoogkassie te beweeg en die laaste argument vir die INDEX-funksie in te voer. Hierdie argument vertel Excel dat die kolomnommer in die reeks D6 tot F11 is. Hierdie reeks is waar dit die inligting vind wat deur die funksie teruggestuur word. In hierdie geval, 'n verskaffer vir titanium-legstukke.
- Plaas die wyser in die Column_num tekskassie.
-
Voer 3 in (die nommer drie). Hierdie getal vertel die formule om vir data in die derde kolom van die reeks D6 tot F11 te soek.
Image - Laat die dialoogkassie oop vir die volgende stap in die tutoriaal.
Skep die Skikkingsformule
Voordat die dialoogkassie toegemaak word, verander die geneste funksie in 'n skikkingsformule. Hierdie skikking laat die funksie toe om vir veelvuldige terme in die datatabel te soek. In hierdie tutoriaal word twee terme ooreenstem: Legstukke van kolom 1 en Titanium vanaf kolom 2.
Om 'n skikkingsformule in Excel te skep, druk die CTRL, SHIFT, en ENTERsleutels gelyktydig. Sodra dit gedruk word, word die funksie omring deur krulhakies, wat aandui dat die funksie nou 'n skikking is.
- Kies OK om die dialoogkassie toe te maak. In Excel vir Mac, kies Klaar.
- Kies sel F3 om die formule te sien, en plaas dan die wyser aan die einde van die formule in die Formulebalk.
- Om die formule na 'n skikking om te skakel, druk CTRL+ SHIFT+ ENTER.
- A N/A-fout verskyn in sel F3. Dit is die sel waar die funksie ingevoer is.
-
Die N/A-fout verskyn in sel F3 omdat selle D3 en E3 leeg is. D3 en E3 is die selle waar die funksie soek om die Lookup_value te vind. Nadat data by hierdie twee selle gevoeg is, word die fout vervang deur inligting vanaf die databasis.
Image
Voeg die soekkriteria by
Die laaste stap is om die soekterme by die werkblad te voeg. Hierdie stap pas by die terme Widgets van kolom 1 en Titanium uit kolom 2.
As die formule 'n passing vind vir beide terme in die toepaslike kolomme in die databasis, gee dit die waarde van die derde kolom terug.
- Kies sel D3.
- Enter Widgets.
- Kies sel E3.
- Tik Titanium, en druk Enter.
- Die verskaffer se naam, Widgets Inc., verskyn in sel F3. Dit is die enigste verskaffer wat gelys is wat Titanium Widgets verkoop.
-
Kies sel F3. Die funksie verskyn in die formulebalk bokant die werkblad.
{=INDEX(D6:F11, MATCH(D3&E3, D6:D11&E6:E11, 0), 3)}
In hierdie voorbeeld is daar net een verskaffer vir titanium-legstukke. As daar meer as een verskaffer was, word die verskaffer wat eerste in die databasis gelys is, deur die funksie teruggestuur.
Image
