Finndu marga svið gagna með Excel VLOOKUP

Með því að sameina VLOOKUP virka Excel með COLUMN virka getum við búið til uppflettingarformúla sem gerir þér kleift að skila mörgum gildum frá einum röð af gagnagrunni eða gagnagrunni.

Í dæminu sem sýnd er á myndinni hér fyrir ofan gerir leitarniðurstöðurnar auðvelt að skila öllum gildum - svo sem verð, hlutanúmer og birgir - sem tengjast ýmsum tækjum.

01 af 10

Skila mörgum gildum með Excel VLOOKUP

Eftirfarandi skref sem er að finna hér að neðan býr til upplausnarformúlan sem er að finna í myndinni hér fyrir ofan sem mun skila mörgum gildum úr einu gagnageymslu.

Útlit uppskrift krefst þess að COLUMN virknin sé hreiður inni í VLOOKUP.

Hnitmiðun felur í sér að koma inn í aðra aðgerðina sem eitt af rökunum fyrir fyrsta aðgerðina.

Í þessari einkatími verður COLUMN aðgerðin slegin inn sem dálkvísitala rök fyrir VLOOKUP.

Síðasti skrefið í kennslustundinni felst í því að afrita leitarniðurstöðurnar til viðbótar dálka til þess að sækja viðbótargildi fyrir valinn hluta.

Kennsluefni

• Sláðu inn kennsluupplýsingar
• Búa til nafngreint svið fyrir gagnatafla
• Byrjar VLOOKUP virknina
• Sláðu inn leitargildi Argument using Absolute Cell References
• Innsláttur á töflunni
• Sláðu inn Nested COLUMN virka
• Að ljúka VLOOKUP virkninni
• Afritaðu leitarsamfélagið með fyllihöndluninni
• Að sækja gögn með leitarsamfélaginu

02 af 10

Sláðu inn kennsluupplýsingar

Fyrsta skrefið í kennslu er að slá inn gögnin í Excel verkstæði .

Til að fylgja leiðbeiningunum í kennsluforritinu skaltu slá inn gögnin sem sýnd eru á myndinni hér á eftir í eftirfarandi frumum .

• Sláðu inn efstu gögnin í frumur D1 til G1
• Sláðu inn annað svið í frumur D4 til G10

Leitarviðmiðin og leitarniðurstöðurnar sem búnar voru til í þessari kennsluáætlun verða slegin inn í röð 2 í verkstæði.

Námskeiðið inniheldur ekki formiðið sem sést á myndinni, en það hefur ekki áhrif á hvernig upplausnarsamsetningin virkar.

Upplýsingar um formatting valkosti svipað þeim sem sjást hér að framan er að finna í þessari Basic Excel Formatting Tutorial .

Námskeið

1. Sláðu inn gögnin eins og sést á myndinni hér fyrir ofan í frumur D1 til G10

03 af 10

Búa til nafngreint svið fyrir gagnatafla

Heiti á bilinu er auðveld leið til að vísa til fjölda gagna í formúlu. Frekar en að slá inn reit tilvísana fyrir gögnin, geturðu bara skrifað heiti sviðsins.

Annar kostur við að nota heitið sem heitir, er að klefivísanirnar fyrir þetta svið breytast aldrei, jafnvel þegar formúlan er afrituð í önnur frumur í verkstæði.

Svæðisheiti eru því val til að nota algerar reiti til að koma í veg fyrir villur þegar afritun er formúlan.

Ath .: Heiti sviðsins inniheldur ekki fyrirsagnir eða reitnöfn fyrir gögnin (röð 4) en aðeins gögnin sjálf.

Námskeið

1. Hápunktur frumur D5 til G10 í verkstæði til að velja þau
2. Smelltu á nafnareitinn fyrir ofan dálk A
3. Sláðu inn "Tafla" (engin tilvitnanir) í Nafnakassanum
4. Ýtið á ENTER takkann á lyklaborðinu
5. Frumur D5 til G10 hafa nú heitið "Tafla". Við munum nota nafnið á VLOOKUP töflu array rök seinna í kennslu

04 af 10

Opnaðu VLOOKUP valmyndina

Þó að hægt sé að slá inn lykilformúlu okkar beint í reit í verkstæði, finnst margir erfitt með að halda setningafræði beint - sérstaklega fyrir flókna formúlu eins og sá sem við notum í þessari kennsluefni.

Val, í þessu tilfelli, er að nota VLOOKUP valmyndina . Næstum allar aðgerðir Excel eru með gluggi sem gerir þér kleift að slá inn hvert röksemdir aðgerðarinnar á sérstakri línu.

Námskeið

1. Smelltu á hnappinn E2 á verkstikunni - staðsetningin þar sem niðurstöður tvívíddar upptökunarformúlsins birtast
2. Smelltu á Formulas flipann á borði
3. Smelltu á leitina og tilvísunina í borðið til að opna fallgluggann
4. Smelltu á VLOOKUP á listanum til að opna valmyndina

05 af 10

Sláðu inn leitargildi Argument using Absolute Cell References

Venjulega samsvarar leitarnúmerið gögnum í fyrstu dálknum í gagnatöflunni.

Í dæmi okkar er leitarniðurhæðin átt við nafnið á vélbúnaðarhlutanum sem við viljum finna upplýsingar um.

Leyfilegar gerðir gagna fyrir leitarniðurstöðurnar eru:

• texta gögn
• rökrétt gildi (aðeins SÉR eÐa FALSE)
• tala
• klefi tilvísun í gildi í verkstæði

Í þessu dæmi munum við koma inn í klefi tilvísunina þar sem hlutanafnið verður staðsett - klefi D2.

Algengar tilvísanir í klefi

Í síðari skrefi í kennslustundinni munum við afrita uppskrift uppskrift í klefi E2 í frumur F2 og G2.

Venjulega, þegar formúlur eru afritaðar í Excel, breytist klefivísar til að endurspegla nýja staðsetningu þeirra.

Ef þetta gerist breytist D2 - klefi tilvísunin fyrir leitarniðurstöðurnar - þegar formúlan er afrituð búa til villur í frumum F2 og G2.

Til að koma í veg fyrir villurnar munum við umbreyta klefi tilvísun D2 í algera klefi tilvísun .

Alger endurvísun á flokkum breytist ekki þegar formúlur eru afritaðar.

Algjar klefi tilvísanir eru búnar til með því að ýta á F4 takkann á lyklaborðinu. Að gera þetta bætir dollara merki um klefi tilvísun eins og $ D $ 2

Námskeið

1. Smelltu á línuna útlit í valmyndinni
2. Smelltu á klefi D2 til að bæta við þessari klefi tilvísun í lookup_value línunni. Þetta er klefi þar sem við munum slá inn heiti hlutans sem við leitum að upplýsingum um
3. Án þess að færa innsetningarpunktinn, ýttu á F4 takkann á lyklaborðinu til að umbreyta D2 inn í hreint klefi tilvísun $ D $ 2
4. Skildu VLOOKUP virka valmyndina opna fyrir næsta skref í handbókinni

06 af 10

Innsláttur á töflunni

Tafalagsetningin er töflunni um gögn sem leitarniðurstöður leitast við að finna þær upplýsingar sem við viljum.

Taflauppsetningin verður að innihalda að minnsta kosti tvo dálka gagna .

• Fyrsti dálkurinn inniheldur upplifunargildi (fyrri skref í kennsluefni)
• Annað, og viðbótar dálka, verður leitað með leitarniðurstöðum til að finna þær upplýsingar sem við tilgreinum.

Tafla array röksemdafærslan verður að vera innrituð sem annað hvort bil sem inniheldur klefi tilvísanir fyrir gögn borð eða sem svið nafn .

Í þessu dæmi munum við nota sviðsheiti sem er búið til í skrefi 3 í kennslustundinni.

Námskeið

1. Smelltu á table_array línuna í valmyndinni
2. Sláðu inn "Tafla" (engin tilvitnanir) til að slá inn heiti sviðsins fyrir þetta rök
3. Skildu VLOOKUP virka valmyndina opna fyrir næsta skref í handbókinni

07 af 10

Nesting the COLUMN Virka

Venjulega veitir VLOOKUP aðeins gögn úr einum dálki gagnatafla og þessi dálkur er stilltur með dálkvísitölumagnargrunni.

Í þessu dæmi höfum við hins vegar þrjá dálka sem við óskum eftir að skila gögn frá, þannig að við þurfum leið til að breyta dálkvísitölu númerinu án þess að breyta leitarniðurstöðum okkar.

Þetta er þar sem COLUMN virka kemur inn. Með því að slá það inn sem dálkvísitölu rifrildi mun það breytast þar sem upptökusetningin er afrituð úr klefi D2 í frumur E2 og F2 síðar í kennslu.

Nesting Aðgerðir

COLUMN virka virkar því sem dálkvísitala rökargildi VLOOKUP.

Þetta er gert með því að hneppa COLUMN virka inni í VLOOKUP í Col_index_num línunni í valmyndinni.

Sláðu inn COLUMN virknina handvirkt

Þegar hreiður virkar, leyfir Excel okkur ekki að opna valmyndina í annarri aðgerðinni til að slá inn rökin.

Því verður að færa inn COLUMN-aðgerðina handvirkt í Col_index_num línunni.

COLUMN virknin hefur aðeins eina röskun - tilvísunarargreinin sem er tilvísun í klefi.

Val á tilvísunargildi COLUMN-hlutans

Verkefni COLUMN virka er að skila fjölda dálksins sem gefinn er tilvísunarargrein .

Með öðrum orðum breytir það dálkbréfið í númer með dálki A sem er fyrsta dálkurinn, dálkur B seinni og svo framvegis.

Þar sem fyrsta gögnargagnið sem við viljum skilað er verð vörunnar - sem er í dálki tveimur í gagnatöflunni - við getum valið klefi tilvísun fyrir hvaða klefi í dálki B sem viðmiðunarargumentin til þess að fá númer 2 fyrir Col_index_num rifrildi.

Námskeið

1. Í VLOOKUP valmyndinni skaltu smella á Col_index_num línuna
2. Sláðu inn heiti aðgerðarnámsins og fylgt eftir með opnu umferðinni " ( "
3. Smelltu á reitinn B1 í verkstæði til að slá inn þessa reit tilvísunar sem tilvísunarargrein
4. Sláðu inn lokaklefann " ) " til að ljúka COLUMN-aðgerðinni
5. Skildu VLOOKUP virka valmyndina opna fyrir næsta skref í handbókinni

08 af 10

Sláðu inn VLOOKUP Range Lookup Argument

Lykilorð Range_lookup VLOOKUP er rökrétt gildi (aðeins SUE eða FALSE) sem gefur til kynna hvort þú vilt VLOOKUP til að finna nákvæma eða samræmda samsvörun við Lookup_value.

• Ef TRUE eða ef þetta rök er sleppt, skilar VLOOKUP annaðhvort nákvæmlega samsvörun við Lookup_value, eða, ef nákvæm samsvörun er ekki fundin, skilar VLOOKUP næststærsta gildi. Fyrir formúluna til að gera þetta, verða gögnin í fyrstu dálki Table_array flokkuð í hækkandi röð .
• Ef FALSE, VLOOKUP mun aðeins nota nákvæmlega samsvörun við Lookup_value. Ef tveir eða fleiri gildi eru í fyrstu dálknum í Table_array sem passa við leitarniðurstöðurnar, finnst fyrsta gildi sem er notað. Ef ekki finnst nákvæm samsvörun skilar # N / A villa.

Í þessari einkatími, þar sem við erum að leita að tilteknum upplýsingum um tiltekna vélbúnaðarhlut, munum við setja Range_lookup jafnt sem ósatt .

Námskeið

1. Smelltu á Range_lookup línuna í valmyndinni
2. Sláðu inn orðið False í þessari línu til að gefa til kynna að við viljum VLOOKUP að skila nákvæmu samsvörun fyrir gögnin sem við erum að leita að
3. Smelltu á Í lagi til að ljúka leitarformúlunni og loka glugga
4. Þar sem við höfum ekki enn sett inn leitarniðurstöður í reit D2 verður # N / A villa til staðar í klefi E2
5. Þessi villa verður leiðrétt þegar við munum bæta við útlitsviðmiðunum í síðasta skrefi kennslustundarinnar

09 af 10

Afritaðu leitarsamfélagið með fyllihöndluninni

Uppfyllingarformúlan er ætlað að sækja gögn frá mörgum dálkum gagnagagnanna í einu.

Til að gera þetta verður uppskrift uppskrift að búa á öllum sviðum sem við viljum upplýsingar um.

Í þessari einkatími viljum við að það fái gögn úr dálkum 2, 3 og 4 af gagnatöflunni - það er verð, hlutanúmerið og nafn birgis þegar við slærð inn heiti sem Lookup_value.

Þar sem gögnin eru sett fram í reglulegu mynstri í verkstæði , getum við afritað leitarniðurstöður í klefi E2 í frumur F2 og G2.

Eins og formúlan er afrituð, mun Excel uppfæra hlutfallslega klefi tilvísun í COLUMN virka (B1) til að endurspegla nýja staðsetningu formúlu.

Eins og heilbrigður, breytir Excel ekki hreint klefi tilvísun $ D $ 2 og heitir svið Tafla sem formúlan er afrituð.

Það er fleiri en ein leið til að afrita gögn í Excel, en líklega auðveldasta leiðin er með því að nota fylla handfangið .

Námskeið

1. Smelltu á klefi E2 - þar sem upptökusetningin er staðsett - til að gera það virkt klefi
2. Settu músarbendilinn yfir svarta torgið í neðst hægra horninu. Bendillinn breytist í plúsmerki " + " - þetta er fyllahandfangið
3. Smelltu á vinstri músarhnappinn og dragðu fyllahandfangið yfir í klefi G2
4. Slepptu músarhnappnum og klefi F3 ætti að innihalda tvívídda útlit uppskriftina
5. Ef það er gert rétt, eiga frumur F2 og G2 nú einnig að innihalda # N / A villuna sem er til staðar í klefi E2

10 af 10

Sláðu inn leitarviðmiðin

Þegar leitarsýningin hefur verið afrituð til nauðsynlegra frumna er hægt að nota hana til að sækja upplýsingar úr gagnatöflunni.

Til að gera það skaltu slá inn heiti hlutarins sem þú vilt sækja í Lookup_value klefi (D2) og ýttu á ENTER takkann á lyklaborðinu.

Þegar búið er að framkvæma hverja reit sem inniheldur uppflettingarformúluna skal innihalda mismunandi gögn um vélbúnaðinn sem þú leitar að.

Námskeið

1. Smelltu á klefi D2 í verkstæði
2. Sláðu Búnaður í klefi D2 og ýttu á ENTER takkann á lyklaborðinu
3. Eftirfarandi upplýsingar skulu birtar í frumum E2 til G2:
   • E2 - 14,76 $ - verð á búnaði
   • F2 - PN-98769 - hlutarnúmerið fyrir græju
   • G2 - Widgets Inc. - heiti birgis fyrir búnað
4. Prófaðu VLOOKUP array formúluna frekar með því að slá inn heiti annarra hluta í reit D2 og fylgjast með niðurstöðum í frumum E2 til G2

Ef villuskilaboð eins og #REF! birtist í frumum E2, F2 eða G2, þessi listi yfir VLOOKUP villuskilaboð getur hjálpað þér að ákvarða hvar vandamálið liggur.