Deur 'n skikking formule in Excel te gebruik, kan ons 'n opsoek formule skep wat verskeie kriteria gebruik om inligting in 'n databasis of tabel van data te vind.
Die skikking formule behels die naslaan van die MATCH funksie binne die INDEX funksie.
Hierdie handleiding bevat 'n stap vir stap voorbeeld van die skep van 'n opsomming formule wat gebruik maak van verskeie kriteria om 'n verskaffer van titanium Widgets in 'n voorbeeld databasis te vind.
Na aanleiding van die stappe in die tutoriaal-onderwerpe gaan jy deur die skep en gebruik van die formule wat in die bostaande prent gesien word.
01 van 09
Voer die tutoriaal data in
Die eerste stap in die tutoriaal is om die data in 'n Excel- werkblad in te voer .
Om die stappe in die tutoriaal te volg, voer die gegewe data in die bostaande prent in die volgende selle in .
- Voer die boonste reeks data in selle D1 tot F2 in
- Voer die tweede reeks in selle D5 tot F11 in
Rye 3 en 4 word leeg gelaat om die skikkingformule wat tydens hierdie tutoriaal geskep is, te akkommodeer.
Die handleiding bevat nie die formatering wat in die prent gesien word nie, maar dit sal nie beïnvloed hoe die oproepformule werk nie.
Inligting oor formatering opsies soortgelyk aan dié wat hierbo gesien is, is beskikbaar in hierdie Basiese Excel Formatering Tutoriaal.
02 van 09
Begin van die INDEX-funksie
Die INDEX-funksie is een van die min in Excel wat verskeie vorms het. Die funksie het 'n Array-vorm en 'n verwysingsvorm .
Die Array-vorm gee die werklike data van 'n databasis of tabel van data terug, terwyl die verwysingsvorm die selverwysing of ligging van die data in die tabel gee.
In hierdie tutoriaal sal ons die Array-vorm gebruik, aangesien ons die naam van 'n verskaffer vir titanium widgets wil ken, eerder as die selverwysing na hierdie verskaffer in ons databasis.
Elke vorm het 'n ander lys van argumente wat gekies moet word voordat die funksie begin word.
Tutoriaalstappe
- Klik op sel F3 om dit die aktiewe sel te maak . Hier sal ons die geneste funksie betree.
- Klik op die Formules- oortjie van die lintmenu .
- Kies Soek en Verwysing van die lint om die aftreklys vir funksies oop te maak.
- Klik op INDEX in die lys om die dialoog Kies argumente te vertoon .
- Kies die skikking, row_num, col_num opsie in die dialoogkassie.
- Klik OK om die dialoog INDEX-funksie oop te maak.
03 van 09
Voer die INDEX Funksie Array Argument in
Die eerste argument wat benodig word, is die Array-argument. Hierdie argument spesifiseer die reeks selle wat gesoek moet word vir die verlangde data.
Vir hierdie handleiding sal hierdie argument ons steekproef databasis wees .
Tutoriaalstappe
- Kliek in die dialoogkassie INDEX op die reël Array .
- Merk selle D6 tot F11 in die werkblad om die reeks in die dialoogkassie te betree.
04 van 09
Begin die geneste MATCH-funksie
As jy een funksie in 'n ander nes, is dit nie moontlik om die dialoog van die tweede of geneste funksie oop te maak om die nodige argumente in te voer nie .
Die geneste funksie moet getik word as een van die argumente van die eerste funksie.
In hierdie handleiding sal die geneste MATCH-funksie en sy argumente in die tweede reël van die dialoog INDEX-funksie ingevoer word - die ry- lyn.
Dit is belangrik om daarop te let dat wanneer die funksies handmatig ingevoer word, die funksie se argumente van mekaar geskei word deur 'n komma ',' .
Die MATCH-funksie se Lookup_value Argument word ingevoer
Die eerste stap in die invoer van die geneste MATCH-funksie is om die Soekup_value- argument in te voer.
Die Lookup_value is die ligging of selverwysing vir die soekterme wat ons in die databasis wil ooreenstem.
Normaalweg aanvaar die Lookup_value slegs een soekkriteria of term. Om te soek na verskeie kriteria moet ons die Lookup_value uitbrei.
Dit word gedoen deur twee of meer selverwysings saam te voeg of saam te voeg deur die ampersand simbool " & " te gebruik.
Tutoriaalstappe
- In die dialoogkassie INDEX, klik op die Row_numreël .
- Tik die funksie naam match gevolg deur 'n oop ronde bracket " ( "
- Klik op sel D3 om daardie selverwysing in die dialoogkassie in te voer.
- Tik 'n ampersand " & " na die selverwysing D3 om 'n tweede selverwysing by te voeg.
- Klik op sel E3 om hierdie tweede selverwysing in die dialoogkassie in te voer.
- Tik 'n komma ',' na die selverwysing E3 om die inskrywing van die MATCH-funksie se Lookup_value- argument te voltooi.
- Laat die INDEX-funksie dialoog oop vir die volgende stap in die tutoriaal.
In die laaste stap van die tutoriaal sal die Lookup_values ingevoer word in selle D3 en E3 van die werkblad.
05 van 09
Voeg die Lookup_array vir die MATCH-funksie by
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 sal soek om die Lookup_value- argument in die vorige stap van die tutoriaal te vind.
Aangesien ons twee soekvelde in die Lookup_array- argument geïdentifiseer het, moet ons dieselfde doen vir die Lookup_array . Die MATCH funksie soek slegs een skikking vir elke term wat gespesifiseer word.
Om verskeie skikkings in te voer gebruik ons weer die ampersand " & " om die skikkings saam te voeg.
Tutoriaalstappe
Hierdie stappe moet ingevoer word na die komma wat in die vorige stap op die ry nommer in die dialoogkassie INDEX ingevoer is.
- Klik op die Row_numreël na die komma om die invoegpunt te plaas aan die einde van die huidige inskrywing.
- Merk selle D6 tot D11 in die werkblad om die reeks te betree. Dit is die eerste skikking wat die funksie is om te soek.
- Tik 'n ampersand " & " na die selverwysings D6: D11 omdat ons die funksie wil hê om twee skikkings te soek.
- Merk selle E6 tot E11 in die werkblad om die reeks te betree. Dit is die tweede skikking wat die funksie is om te soek.
- Tik 'n komma ',' na die selverwysing E3 om die inskrywing van die MATCH-funksie se Lookup_array- argument te voltooi.
- Laat die INDEX-funksie dialoog oop vir die volgende stap in die tutoriaal.
06 van 09
Voeg die tipe Match by en voltooi die MATCH-funksie
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 keuses is: 1, 0, of -1.
Hierdie argument is opsioneel. As dit weggelaat word, gebruik die funksie die verstekwaarde van 1.
- as die Match_type = 1 of weggelaat word: MATCH vind die grootste waarde wat minder of gelyk is aan die Lookup_value. Die Lookup_array-data moet in stygende volgorde gesorteer word.
- as die match_type = 0: MATCH die eerste waarde vind wat presies gelyk is aan die Lookup_value. Die Lookup_array-data kan in enige volgorde gesorteer word.
- as die Match_type = -1: MATCH die kleinste waarde vind wat groter of gelyk is aan die Lookup_value. Die Lookup_array-data moet in dalende volgorde gesorteer word.
Tutoriaalstappe
Hierdie stappe moet ingevoer word na die komma wat in die vorige stap op die ry nommer in die dialoogkassie INDEX ingevoer is.
- Volg die komma op die Row_numreël , tik 'n nul " 0 " aangesien ons wil hê dat die geneste funksie presiese ooreenkomste moet terugvoer na die terme wat ons in selle D3 en E3 invoer.
- Tik 'n sluitingsrondjie " ) " om die MATCH-funksie te voltooi.
- Laat die INDEX-funksie dialoog oop vir die volgende stap in die tutoriaal.
07 van 09
Terug na die INDEX-funksie
Noudat die MATCH-funksie gedoen is, gaan ons na die derde reël van die oop dialoogkassie en voer die laaste argument vir die INDEX-funksie in.
Hierdie derde en laaste argument is die kolom_num- argument wat Excel die kolomnommer in die reeks D6 tot F11 vertel, waar dit die inligting wat ons wil hê, deur die funksie teruggegee sal word. In hierdie geval, 'n verskaffer vir titanium widgets .
Tutoriaalstappe
- Kliek op die kolom nommer in die dialoogkassie.
- Gee die nommer drie " 3 " (geen aanhalings) op hierdie reël, want ons soek data in die derde kolom van die reeks D6 tot F11.
- Kliek nie op OK of sluit die dialoog INDEX-funksie. Dit moet oop wees vir die volgende stap in die handleiding - die skikking formule skep .
08 van 09
Die skep van die Array Formule
Voordat ons die dialoog sluit , moet ons ons geneste funksie in 'n skikking formule omskep.
'N Skikking formule is wat dit toelaat om te soek vir verskeie terme in die tabel van data. In hierdie tutoriaal wil ons twee terme pas: Widgets van kolom 1 en titanium uit kolom 2.
Om 'n skikking formule in Excel te skep, word gedoen deur die CTRL , SHIFT en ENTER sleutel op die sleutelbord terselfdertyd te druk.
Die effek van die druk van hierdie sleutels bymekaar is om die funksie met krulhakies omring: () wat aandui dat dit nou 'n skikkingformule is.
Tutoriaalstappe
- Met die voltooide dialoogkassie nog oop van die vorige stap van hierdie handleiding, druk en hou die CTRL en SHIFT sleutels op die sleutelbord hou en druk dan die Enter- sleutel los.
- As dit korrek gedoen word, sal die dialoog sluit en 'n # N / A fout sal in sel F3 verskyn - die sel waar ons die funksie ingevoer het.
- Die # N / A fout verskyn in sel F3 omdat selle D3 en E3 leeg is. D3 en E3 is die selle waar ons die funksie vertel het om die Lookup_values in stap 5 van die tutoriaal te vind. Sodra data by hierdie twee selle gevoeg is, sal die fout vervang word deur inligting uit die databasis .
09 van 09
Voeg die soekkriteria by
Die laaste stap in die handleiding is om die soekterme by ons werkkaart by te voeg.
Soos in die vorige stap genoem, soek ons die terme Widgets van kolom 1 en titanium uit kolom 2.
As, en slegs as ons formule 'n ooreenkoms vir beide terme in die toepaslike kolomme in die databasis vind, sal dit die waarde van die derde kolom terugbring.
Tutoriaalstappe
- Klik op sel D3.
- Tik Widgets en druk die Enter- sleutel op die sleutelbord.
- Klik op sel E3.
- Tik Titanium en druk die Enter- sleutel op die sleutelbord.
- Die verskaffer se naam Widgets Inc. moet in sel F3 verskyn - die ligging van die funksie aangesien dit die enigste verskaffer is wat Titanium Widgets verkoop.
- As jy op sel F3 kliek, is die volledige funksie
{= INDEX (D6: F11, MATCH (D3 & E3, D6: D11 & E6: E11, 0), 3)}
verskyn in die formulebalk bokant die werkblad .
Nota: In ons voorbeeld was daar net een verskaffer vir titanium widgets. As daar meer as een verskaffer was, word die verskaffer wat eerste in die databasis gelys word, deur die funksie terugbesorg.