Troch it kombinearjen fan Excel's VLOOKUP-funksje mei de funksje COLUMN kinne wy in lookup-formulier meitsje dy't jo meardere wearden út in ienige rige fan in database of tafel fan data geane.
Yn it foarbyld sjen litte yn it boppesteande byld, makket it opslaan-formule it maklik om alle wearden werom te jaan - sa as priis, diel nûmer en leveransier - ferbân mei ferskate stikken fan hardware.
01 of 10
Gean meardere wearden werom mei Excel VLOOKUP
Nei de stappen dy't hjirûnder opnommen wurde, skeakelet de opsleine formule yn 'e boppeneamde siden dy't sille meardere wearden weromkomme út in single data-record.
De sykfraachformulier fereasket dat de funksje COLUMN yn binnen de VLOOKUP ynsteld wurdt.
Nesting in funksje befet de ynfier fan 'e twadde funksje as ien fan' e arguminten foar de earste funksje.
Yn dit lesjalearje sil de funksje COLUMN ynfierd wurde as it spultsynformaasje nûmer argumint foar VLOOKUP.
De lêste stap yn 'e praktyk befettet it kopiearjen fan de opsommelformulier oan ekstra kolommen om meardere wearden foar it keazen keazen diel te krijen.
Tutorial Ynhâld
- Opnij de Tutorial Data
- It meitsjen fan in oantsjutte berik foar de datatafel
- Begjin de VLOOKUP-funksje
- Opkomt it sykjen fan wearde op sykjen mei Absolute Cell References
- It Argument fan Tafel Array ynfiere
- Ynfier fan de Nested COLUMN-funksje
- De VLOOKUP-funksje útfiere
- Kopiearje de trochsneed formulier mei de fillinggriff
- Bestannen fan data mei de trochsichtende formule
02 of 10
Fier de tutorials yn
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 'e sellen yn D1 nei G1
- Fier it twadde berik yn de sellen D4 nei G10
De sykresultaten en de troch dizze praktyk makke formulier trochsette formulier wurde yn rige 2 yn it wurkblêd ynfierd.
It ynstruksjetaal befettet gjin formaat te sjen yn it byld, mar dit sil gjin ynfloed op hoe't de oplossing formule wurket.
Ynformaasje oer formaat-opsjes lykas dy hjirboppe te sjen binne beskikber yn dit Basic Excel Formatting Tutorial .
Tutorial Steps
- Fier de gegevens yn as it te sjen yn it byld yn 'e sellen D1 oant G10
03 of 10
It meitsjen fan in oantsjutte berik foar de datatafel
In ferneamd berik is in maklike manier om te ferwizen nei in ferskaat oan gegevens yn in formule. Selektearje jo yn 'e sel foar referinsjes te typen, kinne jo de namme fan it berikke type ynfiere.
In twadde foardiel foar it gebrûk fan in ferneamd berik is dat de seleksferwizen foar dit berik nea feroarje lykas de formule nei oare sellen kopieert yn it wurkblêd.
Range-nammen binne dus in alternatyf om absolute selstraferwizings te brûken om flaters te foarkommen by it kopiearjen fan formules.
Taljochting: De beriknamme docht net de headings of fjildnammen foar de gegevens (rige 4) mar mar de gegevens sels.
Tutorial Steps
- Markearje fan sellen D5 oant G10 yn it wurkblêd om se te selektearjen
- Klik op 'e namme foar boppesteande kolom A
- Typ "Tafel" (gjin quotes) yn 'e Namekast
- Druk de ENTER- kaai op it toetseboerd
- Cells D5 nei G10 hawwe no de beriknamme fan "Tafel". Wy sille de namme brûke foar it tabblêd foar tabellarmaat VLOOKUP letter yn it tutorial
04 of 10
Iepenje it VLOOKUP-dialoochfinster
Hoewol it mooglik is om ús sykfraachformulier direkt yn in sel te typ yn in wurkblêd, sille in soad minsken it dreech fine om de syntaksje rjochtstreeks te hâlden - foaral in komplekse formule lykas de dy't wy brûke yn dit lesboek.
In alternatyf is yn dit gefal it dialooch foar VLOOKUP te brûken. Hast alle funksjes fan Excel hawwe in dialoochfinster dat jo elk fan 'e arguminten fan' e funksje yn in aparte line ynfiere kinne.
Tutorial Steps
- Klik op seleksje E2 fan it wurkblêd - de lokaasje wêr 't de resultaten fan' e twa dimensjeel lookup formule werjûn wurde
- Klikje op de ljepblêd Formulas fan it lint
- Klik op 'e opsjesynformaasje en referinsje yn it lint om de funksje fan dellûkingslist te iepenjen
- Klik op VLOOKUP yn 'e list om it dialoochfinster fan de funksje te iepenjen
05 of 10
Opkomt it sykjen fan wearde op sykjen mei Absolute Cell References
Normaal is it sykkomming wearde mei in fjild fan gegevens yn 'e earste kolom fan' e datatabel.
Yn ús foarbyld ferwizigje de opsetting-wearde nei de namme fan it hardwarepart dêr't wy ynformaasje fine.
De tastienbare datatypen foar de opsleine wearde binne:
- tekstgegevens
- in logyske wearde (allinne TRUE of FALSE)
- in nûmer
- in sel ferwizing nei in wearde yn it wurkblêd
Yn dit foarbyld komme wy yn 'e sel referinsje nei wêr' t de dielnamme werkt - cell D2.
Absolute Cell References
Yn in letter stap yn 'e tutorial sille wy de sykfoarm formulierje yn sel yn E2 nei sellen F2 en G2.
Normaal, as formules binne yn Excel kopieare, selektearje referinsjes nei har nije lokaasje.
As dat bart, sil D2 - de sellenferhanneling foar de sykwearde - feroarje as de formule is kopiearre mei it meitsjen fan fouten yn sellen F2 en G2.
Om de fouten te foarkommen, sille wy de selfer referinsje D2 yn in absolute sel referinsje konvertie.
Absolute seleksferwizings wizigje net as formulas kopiearre wurde.
Absolute cell references are created by pressing the F4 key on the keyboard. Dit soarget dûbelde tekens om 'e selde referinsjes lykas $ D $ 2
Tutorial Steps
- Klik op 'e line lookup_value yn it dialoochfinster
- Klikje op sellen D2 om dizze selferinsje oan te jaan oan de line lookup_value . Dit is de sel foar wêr't wy de dielnammen ynfiere wêrnei't wy ynformaasje sykje
- Skeakelje de ynfierpunt sûnder nei de druk F4- toets op it toetseboerd om D2 konvertearje yn de absolute seleks referinsje $ D $ 2
- Lit it folsleine dialoochfinster fan VLOOKUP iepenje foar de folgjende stap yn 'e tutorial
06 of 10
It Argument fan Tafel Array ynfiere
De tabellen array is de tabel fan gegevens dy't de opsjes formule trochsykje om de ynformaasje te finen dy't wy wolle.
De tabelarray moat op syn minst twa kolommen fan gegevens befetsje .
- De earste kolom befettet it wurdearring-wurdargumint (foarige stap yn 'e tutorial)
- de twadde, en elke oanfoljende kolommen, wurde trochsnien troch de sykfraachformulier om de ynformaasje te finen dy't wy oanpasse.
It argumint fan tabel array moat ynfierd wurde as in berik dat de referinsjes fan 'e sel foar de datatafel of as in beriknamme hat .
Foar dit foarbyld wolle wy gebrûk meitsje fan rige namme yn stap 3 fan it tutorial.
Tutorial Steps
- Klikje op 'e table_array- rigel yn it dialoochfinster
- Typ "Tabel" (gjin quotes) om de beriknamme foar dit argumint te enteren
- Lit it folsleine dialoochfinster fan VLOOKUP iepenje foar de folgjende stap yn 'e tutorial
07 of 10
Nim de COLUMN-funksje
Normalerwjildt VLOOKUP allinich gegevens fan ien kolom fan in datafel, en dizze kolom is ynsteld troch it argument fan kolommen yneks nûmer .
Yn dit foarbyld hawwe wy lykwols trije pylders dat wy wolle dat gegevens weromkomme wolle, sadat wy in manier nedich om it kolumnekennummer maklik te feroarjen sûnder dat ús formulier opnij feroaret.
Dit is wêrtroch de funksje COLUMN ynkomt. As it yndeksearret as it spultsynformaasje nûmer argumint, sil it feroarje as de opsjesfoarm kopieare wurdt fan cell D2 nei sellen E2 en F2 letter yn 'e tutorial.
Nêstfunksjes
De funksje COLUMN docht dêrnei as argumint foar kolommen ynlade fan VLOOKUP.
Dit wurdt befoardere troch de funksje COLUMN yn binnen fan VLOOKUP yn 'e kolomindex fan it dialoochfinster te nestjen .
De KOLUMN-funksjonele manuell ynfierd
Wannear't de funksjes fan Nesting kinne, sille Excel ús net it finster fan it twadde funksje iepenje om har arguminten te ynfoljen.
De funksje COLUMN moat dus dan yndividueel ynfierd wurde yn de line fan Col_index_num .
De funksje COLUMN hat mar ien argumint - it referinsjeroom dat in selfer referinsje is.
Selektearje it KOLLUMNFonksje-Argument
De funksje COLUMN funksjonearret it om it getal werom te jaan as de referinsje argumint.
Mei oare wurden konvertearret de kolombrief in nûmer mei kolom A wêryn de earste kolom, kolom B de twadde en sa is.
Sûnt it earste fjild fan gegevens wolle wy weromgean is de priis fan 'e item - dy't yn kolom twa fan' e datatabel is - wy kinne de selfer referinsje kieze foar elke sel yn kolom B as it referinsjes Argumint om it nûmer 2 foar te krijen it argument Col_index_num .
Tutorial Steps
- Yn it folsleine dialoochfinster fan VLOOKUP klikje jo op 'e line Col_index_num
- Typ de funksje neamd kolom, folge troch in iepen rûne klok " ( "
- Klikje op sellen B1 yn it wurkblêd om dizze selferinsje yn te njen as it referinsjesargumint
- Typ in sluting rûnte " ) " om de funksje COLUMN te foltôgjen
- Lit it folsleine dialoochfinster fan VLOOKUP iepenje foar de folgjende stap yn 'e tutorial
08 of 10
It ynfieren fan it VLOOKUP Range Lookup Argument
VLOOKUP's argument Range_lookup is in logyske wearde (TRUE of FALSE allinich) dat oanjout oft jo wolle dat VLOOKUP in krekte of in oankommende wedstriid te finen is nei de Lookup_value.
- As TRUE of as dizze argumint wegere wurdt, jout VLOOKUP ek in krekte wedstriid foar de Lookup_value, of, as in krekte match net fûn is, komt VLOOKUP de folgjende grutste wearde werom. Foar de formule om dit te dwaan, moatte de gegevens yn 'e earste kolom fan Table_array yn opkommende opdracht sortearre wurde .
- As FALSE sil VLOOKUP allinich in krekte wedstriid brûke oan de Lookup_value. As der twa of mear wearden binne yn 'e earste kolom fan Table_array dy't de opsjeswearde passe, wurdt de earste wearde fûn brûkt. As in krekte wedstriid net fûn wurdt, wurdt in # N / A-flater weromjûn.
Yn dit ynstruksje, om't wy nei spesifike ynformaasje sykje oer in bepaalde hardware item, sette wy Range_lookup lyk oan False .
Tutorial Steps
- Klik op 'e Range_lookup- rigel yn it dialoochfinster
- Typ it wurd False yn dizze line om oan te jaan dat wy VLOOKUP wolle wolle foar in krekte wedstriid foar de gegevens dy't wy sykje
- Klikje OK om de lookupformulier te foltôgje en it dialoochfinster te sluten
- Omdat wy noch net de syk kritearia yn 'e sel ynfierd hawwe, is D2 in # N / A-flater yn' e sel yn E2
- Dizze flater sil korrizjearre wurde as wy de loftskritisten yn 'e lêste stap fan' e tutorial taheakje
09 of 10
Kopiearje de trochsneed formulier mei de fillinggriff
De sykfraachformule is bedoeld om ien fan 'e gegevens fan meardere kolommen fan' e data-tabel werom te heljen.
Om dit te dwaan, moat de opslaan formule yn alle fjilden wenje wêrfan't wy ynformaasje wolle.
Yn dit lesjalearje wolle wy dat de gegevens fan kolommen 2, 3, en 4 fan 'e gegevenstafel weromfiere - dat is de priis, it dielnûmer, en de namme fan de leveransier as wy in dielnamme hawwe as de Lookup_value.
Omdat de gegevens yn in reguliere patroan yn 'e wurkblêd lizze , kinne wy de sykfoarm formulierje yn sel yn E2 nei sellen F2 en G2.
As de formule is kopiearre, sil Excel de relative relate-sel referenje yn 'e funksje COLUMN (B1) om de nije lokaasje fan' e formule te reflektearjen.
Ek Excel feroaret gjin absolute selskipferhelling $ D $ 2 en it neamde berik Tabel as de formule is kopiearre.
Der is mear as ien manier om gegevens te kopiearjen yn Excel, mar wierskynlik de ienfâldige manier is troch it brûken fan de Fillgriff .
Tutorial Steps
- Klik op cell E2 - wêr't de formulier opnij leit - om it aktive sel te meitsjen
- Plak de mûsoanwizer oer it swarte fjild yn 'e ûnderkant rjochte hoeke. De pointer feroare nei in plus teken " + " - dit is it folsleine teken
- Klikje op de lofter mûsknop en ferpleatse it folsleine nei de sel foar G2
- Ferpleats de mûsknop en de seleksje F3 moat de twa dimensjeel lookup-formulier befetsje
- As it goed dien, sille de sellen F2 en G2 no ek de # N / A-flater befetsje dy't yn cell E2 is
10 of 10
It ynskriuwen fan de kritearia
Ien kear de opfangfoarm is kopiearre nei de ferplichte sellen it kin brûkt wurde om ynformaasje te berikken fan 'e gegevenstafel.
Om dizze domein de namme fan it item te typen, dat jo wolle nei it selektearjen fan 'e Lookup_value (D2) ophelje en de ENTER-kaai drukke op' e toetseboerd.
Eartiids kin elke sellen dy 't opnij formule befetsje, in oare stik fan gegevens oer de hardware item dy't jo sykje.
Tutorial Steps
- Klik op cell D2 yn it wurkblêd
- Typ Widget yn selzje D2 en druk op 'e knop ENTER op' e toetseboerd
- De neikommende ynformaasje moatte werjûn wurde yn sellen E2 nei G2:
- E2 - $ 14,76 - de priis fan in widget
- F2 - PN-98769 - it dielnûmer foar in widget
- G2 - Widgets Inc. - de namme fan 'e leveransier foar widgets
- Testje de VLOOKUP-arylförmele formule fierder troch te typen de namme fan oare dielen yn cell D2 en beoardieling de resultaten yn sellen E2 nei G2
As in flater berjocht lykas #REF! ferskynt yn sellen E2, F2, of G2, dizze list fan VLOOKUP-flater berjochten kin jo helpe hoe't it probleem leit.