Troch gebrûk fan in array-formule yn Excel kinne wy in lookup formule oanmeitsje dy't meardere kritearia brûkt om ynformaasje te finen yn in databank of tabel fan gegevens.
De array-formule omfettet de nesting fan 'e MATCH- funksje yn' e INDEX- funksje.
Dit tutorial befettet in stap foar stappe foarbyld fan it meitsjen fan in lookup formule dy't meardere kritearia brûkt om in supplier fan titanium Widgets te finen yn in sample-database.
Folgje de stappen yn 'e tutorials ûnderwerpen ûnderwerpen jo troch troch te meitsjen en te brûken fan de formule sjoen yn it boppeneamde byld.
01 of 09
Opnij de Tutorial Data
De earste stap yn 'e tutorial is om de gegevens te ynfoljen yn in Excel wurkblêd .
Om de stappen yn 'e tutorial te folgjen, kinne jo de gegevens yn' e ôfbylding hjirboppe ynfiere yn 'e folgjende sellen .
- Fier it boppeste oanbod fan gegevens yn sellen D1 nei F2 yn
- Fier it twadde berik yn de sellen D5 nei F11
Rigen 3 en 4 wurde leech litten om de array-formule te ferbetterjen dy't yn dizze praktyk makke is.
It ynstruksjetaal befettet gjin formaat te sjen yn it byld, mar dit sil gjin ynfloed op hoe't de oplossing formule wurket.
Ynformaasje oer formattingopsjes fergelykber mei dy hjirboppe te sjen binne beskikber yn dit Basic Excel Formatting Tutorial.
02 of 09
Start de INDEX-funksje
De funksje INDEX is ien fan 'e pear yn Excel dy't meardere foarmen hat. De funksje hat in Arrayfoarm en in referinsjesfoarm .
It Arrayformulier jout de echte gegevens fan in databank of tabel fan gegevens werom, wylst it referinsjefoarm jo de selde referinsje of lokaasje fan de gegevens yn 'e tabel jout.
Yn dit lesboek sille wy it Arrayfoarm brûke omdat wy de namme fan in leveransier foar Titan Widgets witte dan de cell referinsje oan dizze leveransier yn ús database.
Elke foarm hat in ferskate list fan arguminten dy't selektearre moatte foardat de funksje begjint.
Tutorial Steps
- Klikje op sellen F3 om it aktive sel te meitsjen . Dit is wêr't wy de nêstfunksje ynfiere.
- Klik op 'e ljepblêd Formulas fan it lintsje menu.
- Kies sykjen en referinsje út it lint om de funksje-dellûk-list te iepenjen.
- Klikje op INDEX yn 'e list om it dialooch selektearje te selektearjen .
- Kies de array, row_num, col_num- opsje yn it dialoochfinster.
- Klik op OK om it finster-dialoochfinster INDEX te iepenjen.
03 of 09
It yndeks fan it INDEX-funksje array argumint
It earste argument nedich is it Array argumint. Dit argumint spesifisearret it berik fan sellen om te besjen foar de winske gegevens.
Foar dit tutorial wurdt dit argumint ús probleemdatabase wêze.
Tutorial Steps
- Yn it dialoochfinster fan INDEX kinne jo klikke op de Array- rigel.
- Selektearje de sellen D6 oant F11 yn it wurkblêd om it berik yn te gean yn it dialoochfinster.
04 of 09
Begjin de Nested MATCH-funksje
As jo ien funksje yn it oare nêst ynstelle, dan is it net mooglik om it dialoochs fan ' e twadde of ynstelde funksje te iepenjen om de nedige arguminten te ynfoljen .
De nêstfunksje moat ynfierd wurde as ien fan 'e arguminten fan' e earste funksje.
Yn dit ynstruksje wurde de ynstelde MATCH-funksje en har arguminten ynfierd yn 'e twadde line fan it funksje dialooch INDEX - de line Row_num .
It is belangryk om te notearjen dat de arguminten fan 'e funksje as in komma ' s fan tapassing binne as in manuale ynfierd wurde.
It ynkommens fan 'e Lookup_value Argument fan it MATCH-funksje
De earste stap foar it ynfieren fan de ynstelde MATCH-funksje is it ynfieren fan it argument " Lookup_value" .
De Lookup_value sil de lokaasje of sel referinsje wêze foar de sykterm dat wy oergean wolle yn 'e database.
Normaal is it Lookup_value allinich mar ien sykkriter of term. Om om meardere kritearia te sykjen, moatte wy de Lookup_value útwreidzje .
Dit sil dien wurde troch te ferkeapjen of tegearre twa of mear selferreferinsjes tegearre mei help fan it ampersandsymboal " & ".
Tutorial Steps
- Yn it finster fan it INDEX-funksje klikke jo op 'e line Row_num .
- Typ de oerienkommende namme oantsjutte mei in iepen rûne klokje " ( "
- Klikje op sellen D3 om dizze selferinsje yn te gean yn it dialoochfinster.
- Typ in ampersand " & " nei de sel referinsje D3 om in twadde cell referinsje te foegjen.
- Klik op cell E3 om dizze twadde cell referinsje yn te gean yn it dialoochfinster.
- Typ in komma "," nei de sel foar referinsjes E3 om de ynfoeging fan it ARM- funksje fan 'e Lookup_value- argumint te foltôgjen .
- Lit it finster fan it INDEX-funksje iepenje foar de folgjende stap yn 'e tutorial.
Yn 'e lêste stap fan' e ynstruksje wurdt de Lookup_values yndrukt yn 'e sellen D3 en E3 fan it wurkblêd.
05 of 09
It lookup_array tafoegje foar de MATCH-funksje
Dizze stapke draacht it tafoegjen fan it argument foar Lookup_array foar de ynstelde MATCH-funksje.
De Lookup_array is it berik fan sellen dy 't de MATCH-funksje soart sykje om it Argumint Lookup_value te finen yn it foarige stap fan' e tutorial.
Omdat wy twa sykfjildes identifisearje yn it argument fan Lookup_array moatte wy itselde dwaan foar de Lookup_array . De funksje MATCH fynt allinich ien array foar elk termyn.
Om meardere arrays yn te bringen brûke wy it ampersjen fan ' & ' om 'e arrays te kombinearjen.
Tutorial Steps
Dizze stappen moatte nei de komma ynfierd wurde yn 'e foarige stap op' e line Row_num yn it funksje dialooch INDEX.
- Klik op 'e line Row_num nei de komma om it ynfoegpunt oan' e ein fan 'e aktuele ynfier te pleatsen.
- Markearje de sellen D6 nei D11 yn it wurkblêd om it berik yn te nimmen. Dit is de earste array de funksje is te sykjen.
- Typ in ampersand " & " nei de sel referenzjes D6: D11 om't wy de funksje wolle om twa arrays te sykjen.
- Selektearje de sellen E6 oant E11 yn it wurkblêd om it berik te krijen. Dit is de twadde array de funksje is te sykjen.
- Typ in komma "," nei de sel foar referinsjes E3 om it ynfieren fan it argument fan it MATCH-funksje fan 'e MATCH-funksje te foltôgjen .
- Lit it finster fan it INDEX-funksje iepenje foar de folgjende stap yn 'e tutorial.
06 van 09
It type fan it Match taheakje en it MATCH-funksje útfiere
It tredde en lêste argumint fan 'e MATCH-funksje is it argumint Match_type.
Dit argumint fertelt Ekscel hoe't jo de Lookup_value oanjaan mei wearden yn 'e Lookup_array. De kiezen binne: 1, 0, of -1.
Dit argumint is fakultatyf. As it útlitten wurdt, brûkt de funksje de standertwearde fan 1.
- as de Match_type = 1 of is wegere: MATCH fynt de grutste wearde dy't minder as of lyk is oan de Lookup_value. De gegevens fan Lookup_array moatte yn opkommende opdracht sortearre wurde.
- as de match_type = 0: MATCH fynt de earste wearde dy't krekt is lyk oan de Lookup_value. De gegevens fan Lookup_array kinne sorteare yn in oarder.
- as it Match_type = -1: MATCH fynt it lytste wearde dat grutter dan of lyk is oan de Lookup_value. De data Lookup_array moat sortearre wurde yn ôfnimmende bestel.
Tutorial Steps
Dizze stappen moatte nei de komma ynfierd wurde yn 'e foarige stap op' e line Row_num yn it funksje dialooch INDEX.
- Nei it komma op 'e line Row_num type in nul " 0 ", om't wy de ynstelde funksje exakte wedstriden krije moatte nei de betingsten dy't wy yntelleare yn sellen D3 en E3.
- Typ in slute rûnte " ) " om de MATCH-funksje te foltôgjen.
- Lit it finster fan it INDEX-funksje iepenje foar de folgjende stap yn 'e tutorial.
07 of 09
Werom nei de INDEX-funksje
No dat de funksje MATCH dien wurdt, sille wy nei de tredde line fan it iepen dialoochfakje ferpleatse en it lêste argumint foar de INDEX-funksje ynfiere.
Dit tredde en lêste argumint is it argumint Column_num dat it Excel it kolomnûmer yn 't berik D6 nei F11 fertelt wêr't it de ynformaasje dy't wy wolle troch de funksje weromfine. Yn dit gefal is in leveransier foar titanium widgets .
Tutorial Steps
- Klik op 'e kolom_num rigel yn it dialoochfinster.
- Fier it nûmer trije " 3 " (gjin quotes) yn op dizze line, omdat wy nei gegevens sykje yn 'e tredde kolom fan it berik D6 oant F11.
- Druk net OK of slute it finster-finster INDEX-finster. It moat iepen wêze foar de folgjende stap yn 'e tutorial - it meitsjen fan de arrayformulier .
08 of 09
It formulier fan 'e Arrayfoarm
Foar it sluten fan it dialoochfinster moatte wy ús nêstfunksje yn in array-formule útwreidzje.
In array-formule is wat it mooglik makket om meardere termen te sykjen yn 't tafel fan gegevens. Yn dit lesboek sykje wy nei twa termen: Widgets fan kolom 1 en titium út kolom 2.
It meitsjen fan in array-formule yn Excel wurdt dien troch druk op 'e knop CTRL , SHIFT , en ENTER op it toetseboerd tagelyk.
It effekt fan dizze toetsen te dronken is om de funksje te bewarjen mei heule spuien: {} oantsjutte dat it no in array-formule is.
Tutorial Steps
- Mei it folsleine dialoochfinster iepenje noch fanút de foarige stap fan dit ynstruksje, drukke en hold de CTRL- en SHIFT- kaaien op it toetseboerd en drukke en de ENTER- kaai losse.
- As jo goed wurkje, sil it dialoochfinster ticht en in # N / A- flater ferskynt yn de sel foar F3 - de sel foar wêr't wy de funksje ynfierd hawwe.
- De # N / A- flater ferskynt yn de sel # F3 om't de sellen D3 en E3 leech binne. D3 en E3 binne de sellen wêr't wy de funksje fertelden om de Lookup_values te finen yn stap 5 fan it tutorial. Ienien gegevens wurde oan dizze twa sellen tafoege, de flater wurdt ferfongen troch ynformaasje út de database .
09 of 09
It tafoegjen fan de sykkriteria
De lêste stap yn 'e tutorial is om de syktermen oan ús wurkblêd ta te foegjen.
As oanjûn yn 'e eardere stap, sykje wy oer de termen Widgets út kolom 1 en Titanium út kolom 2.
As en allinich, as ús formule in match foar beide terminen fynt yn 'e passende kolommen yn' e databank, sil it de wearde weromkomme fan 'e tredde kolom.
Tutorial Steps
- Klik op cell D3.
- Typ Widgets en drukke de Enter- toets op 'e toetseboerd.
- Klik op cell E3.
- Typ Titanium en drukke de Enter- toetsje op 'e toetseboerd.
- De namme fan de leveransier Widgets Inc. moat ferskine yn selle F3 - de lokaasje fan 'e funksje, om't it de ienige leveransier is opnij dy't de Titanium Widgets ferkeapet.
- As jo klikke op sellen F3 de folsleine funksje
{= INDEX (D6 & F3, D6 & D6: E11, 0), 3)}
ferskynt yn 'e formulierbalke boppe it wurkblêd .
Taljochting: Yn ús foarbyld wie der mar ien leveransier foar titanium widgets. As der mear as ien leveransier hie, dan is de leveransier opnaam yn 'e databank werjûn troch de funksje.