Sut i ddod o hyd i ddata gyda VLOOKUP yn Excel

01 o 03

Dewch o hyd i Gemau Amcangyfrif i Data gyda VLOOKUP Excel

Sut mae'r Swyddogaeth VLOOKUP yn Gweithio

Gellir defnyddio swyddogaeth VLOOKUP Excel, sy'n sefyll ar gyfer edrychiad fertigol , i chwilio am wybodaeth benodol sydd wedi'i lleoli mewn tabl o ddata neu gronfa ddata.

Fel arfer bydd VLOOKUP yn dychwelyd un maes o ddata fel ei allbwn. Sut mae'n gwneud hyn yw:

1. Rydych yn rhoi enw neu lookup_value sy'n dweud VLOOKUP lle rhes neu gofnod o'r tabl data i chwilio am y data a ddymunir
2. Rydych chi'n cyflenwi rhif y golofn - a elwir yn col_index_num - o'r data rydych chi'n ei geisio
3. Mae'r swyddogaeth yn edrych am y lookup_value yng ngholofn gyntaf y tabl data
4. Yna, mae VLOOKUP yn lleoli ac yn dychwelyd y wybodaeth rydych chi'n ei geisio o faes arall o'r un cofnod gan ddefnyddio'r rhif colofn a gyflenwir

Didoli'r Data yn Gyntaf

Er nad yw bob amser yn ofynnol, mae'n well fel arfer i ddosbarthu'r ystod o ddata y mae VLOOKUP yn chwilio mewn gorchymyn esgynnol yn defnyddio'r golofn gyntaf o'r ystod ar gyfer yr allwedd sort.

Os nad yw'r data wedi'i didoli, gallai VLOOKUP ddychwelyd canlyniad anghywir.

Cystrawen a Dadleuon Function VLOOKUP

Mae cystrawen swyddogaeth yn cyfeirio at gynllun y swyddogaeth ac yn cynnwys enw'r swyddogaeth, cromfachau a dadleuon .

Y gystrawen ar gyfer swyddogaeth VLOOKUP yw:

= VLOOKUP (lookup_value, table_array, col_index_num, range_lookup)

edrychwch ar y gwerth - (yn ofynnol) y gwerth i chwilio amdano - fel y swm a werthir yn y ddelwedd uchod

table_array - (gofynnol) dyma'r tabl o ddata y mae VLOOKUP yn chwilio amdano i ddod o hyd i'r wybodaeth yr ydych ar ôl.

• Rhaid i'r table_array gynnwys o leiaf ddau golofn o ddata
• Mae'r golofn gyntaf fel arfer yn cynnwys y lookup_value

col_index_num - (gofynnol) rhif colofn y gwerth yr ydych am ei gael.

• Mae'r rhifo'n dechrau gyda'r golofn search_key fel colofn 1
• Os col_index_num wedi ei osod i rif yn fwy na nifer y colofnau a ddewiswyd yn y ddadl table_array a #REF! mae'r swyddogaeth yn dychwelyd gwall

range_lookup - (dewisol) yn nodi a yw'r amrediad yn cael ei didoli mewn gorchymyn esgynnol ai peidio.

• Defnyddir y data yn y golofn gyntaf fel yr allwedd sort
• Gwerth Boolean - GWIR neu FALSE yw'r unig werthoedd derbyniol
• Os hepgorwyd, mae'r gwerth yn cael ei osod yn DDIR yn ddiofyn
• Os yw'n cael ei osod yn DDIR neu'n cael ei hepgor ac nad yw colofn gyntaf yr amrediad wedi'i drefnu mewn gorchymyn esgynnol, gallai canlyniad anghywir ddigwydd
• Os yw'n cael ei osod yn DIRWCH neu wedi'i hepgor ac nad yw'r gêm gyfatebol ar gyfer yr olwg chwilio _pell wedi'i ganfod, defnyddir y gêm agosaf sy'n llai o ran maint neu werth fel y search_key
• Os yw'n cael ei osod i FALSE, dim ond yn union y mae'r VLOOKUP yn derbyn y gêm chwilio . Os oes gwerthoedd cyfatebol lluosog, dychwelir y gwerth cyfatebol cyntaf
• Os caiff ei osod i FALSE ac ni cheir hyd i unrhyw werth cyfatebol ar gyfer search_key , mae gwall # N / A yn cael ei ddychwelyd gan y swyddogaeth

Enghraifft: Darganfyddwch y Gyfradd Gostyngiad ar gyfer Nifer a Bennir

Mae'r enghraifft yn y ddelwedd uchod yn defnyddio'r swyddogaeth VLOOKUP i ddod o hyd i'r gyfradd ddisgownt sy'n amrywio yn dibynnu ar faint o eitemau a brynwyd.

Mae'r enghraifft yn dangos mai'r gostyngiad ar gyfer prynu 19 eitem yw 2%. Mae hyn oherwydd bod y golofn Quantity yn cynnwys ystod o werthoedd. O ganlyniad, ni all VLOOKUP ddod o hyd i union gyfatebol. Yn lle hynny, mae'n rhaid dod o hyd i gêm fras er mwyn dychwelyd y gyfradd ddisgownt gywir.

I ddod o hyd i gemau bras:

• didoli'r data yn y tabl_array mewn trefn esgynnol;
• gosodwch y ddadl range_lookup i DIR

Yn yr enghraifft, defnyddir y fformiwla ganlynol sy'n cynnwys swyddogaeth VLOOKUP i ddod o hyd i'r gostyngiad ar gyfer nwyddau a brynir.

= VLOOKUP (C2, $ C $ 5: $ D $ 8,2, GWIR)

Er y gall y fformiwla hon gael ei deipio i mewn i gell dalen waith, dewis arall, fel y'i defnyddir gyda'r camau a restrir isod, yw defnyddio blwch deialog y swyddogaeth i nodi ei ddadleuon.

• Mae defnyddio'r blwch deialog yn aml yn ei gwneud yn haws i chi nodi dadleuon swyddogaeth yn gywir.

Agor y Blwch Dialog VLOOKUP

Y camau a ddefnyddir i fynd i mewn i'r swyddogaeth VLOOKUP a ddangosir yn y ddelwedd uchod i mewn i gell B2 yw:

1. Cliciwch ar gell B2 i'w wneud yn y gell weithredol - y lleoliad lle mae canlyniadau'r swyddogaeth VLOOKUP yn cael eu harddangos
2. Cliciwch ar y tab Fformiwlâu .
3. Dewiswch Chwilio a Chyfeirnod o'r rhuban i agor y rhestr ostwng swyddogaeth
4. Cliciwch ar VLOOKUP yn y rhestr i ddod â blwch deialog y swyddogaeth i fyny

02 o 03

Ymuno â Dadleuon Swyddogaeth VLOOKUP Excel

Cyfeirio at Cyfeiriadau Cell

Mae'r dadleuon ar gyfer swyddogaeth VLOOKUP yn cael eu cynnwys mewn llinellau ar wahân o'r blwch deialog fel y dangosir yn y ddelwedd uchod.

Gellir teipio'r cyfeiriadau cell i'w defnyddio fel dadleuon i'r llinell gywir, neu, fel y gwnaed yn y camau isod, gall pwyntio, sy'n cynnwys tynnu sylw at yr ystod ddymunol o gelloedd â phwyntydd y llygoden, gael eu defnyddio i fynd i mewn i'r blwch deialog .

Mae manteision defnyddio pwyntio yn cynnwys:

• Mae'n gyflymach na theipio;
• Gwneir llai o gamgymeriadau gan fynd i mewn i'r cyfeiriadau celloedd cywir.

Defnyddio Cyfeiriadau Cell Cymharol a Absolwt â Dadleuon

Nid yw'n anghyffredin defnyddio copïau lluosog o VLOOKUP i ddychwelyd gwybodaeth wahanol o'r un tabl o ddata. Er mwyn ei gwneud hi'n haws gwneud hyn, yn aml gellir copïo VLOOKUP o un cell i'r llall. Pan fo swyddogaethau'n cael eu copïo i gelloedd eraill, rhaid cymryd gofal i sicrhau bod y cyfeiriadau celloedd canlyniadol yn gywir o gofio lleoliad newydd y swyddogaeth.

Yn y ddelwedd uchod, mae arwyddion doler ( $ ) yn amgylchynu'r cyfeiriadau cell ar gyfer y ddadl table_array sy'n nodi eu bod yn gyfeiriadau cell absoliwt , sy'n golygu na fyddant yn newid os yw'r swyddogaeth yn cael ei gopïo i gell arall. Mae hyn yn ddymunol gan y byddai sawl copi o VLOOKUP yn cyfeirio at yr un tabl o ddata fel ffynhonnell gwybodaeth.

Nid yw'r cyfeirnod cell a ddefnyddir ar gyfer lookup_value, ar y llaw arall , wedi'i amgylchynu gan arwyddion doler, sy'n ei gwneud yn gyfeiriad cell cymharol. Mae cyfeiriadau celloedd cymharol yn newid pan gânt eu copïo i adlewyrchu eu lleoliad newydd yn gymharol â sefyllfa'r data y maent yn cyfeirio ato.

Ymateb i'r Argymhellion Swyddogaeth

1. Cliciwch ar y llinell chwilio _value yn y blwch deialu VLOOKUP
2. Cliciwch ar gell C2 yn y daflen waith i nodi'r cyfeirnod celloedd hwn fel y ddadl search_key
3. Cliciwch ar linell Table_array y blwch deialog
4. Amlygu celloedd C5 i D8 yn y daflen waith i nodi'r amrediad hwn fel y ddadl Table_array - nid yw'r penawdau tabl wedi'u cynnwys
5. Gwasgwch yr allwedd F4 ar y bysellfwrdd i newid yr ystod i gyfeiriadau cell absoliwt
6. Cliciwch ar linell Col_index_num y blwch deialog
7. Teipiwch 2 ar y llinell hon fel y ddadl Col_index_num , gan fod y cyfraddau disgownt wedi'u lleoli yng ngholofn 2 o'r ddadl Table_array
8. Cliciwch ar linell Range_lookup y blwch deialog
9. Teipiwch y gair Gwir fel y ddadl Range_lookup
10. Gwasgwch yr allwedd Enter ar y bysellfwrdd i gau'r blwch deialu a dychwelyd i'r daflen waith
11. Dylai'r ateb 2% (y gyfradd ddisgownt ar gyfer y swm a brynwyd) ymddangos yng ngell D2 y daflen waith
12. Pan fyddwch yn clicio ar gell D2, mae'r swyddogaeth gyflawn = Mae VLOOKUP (C2, $ C $ 5: $ D $ 8,2, TRUE) yn ymddangos yn y bar fformiwla uwchben y daflen waith

Pam VLOOKUP Dychwelwyd 2% fel Canlyniad

• Yn yr enghraifft, nid yw'r golofn Nifer yn cynnwys cydweddiad union ar gyfer y gwerth search_key o 19.
• Gan fod y ddadl is_sorted yn cael ei osod yn TRUE, bydd VLOOKUP yn dod o hyd i gêm fras â'r gwerth search_key .
• Y gwerth mwyaf mewn maint sy'n dal yn llai na'r gwerth search_key o 19 yw 11.
• Felly, mae VLOOKUP yn edrych am y gostyngiad yn y ganran yn y rhes sy'n cynnwys 11, ac, o ganlyniad, yn dychwelyd cyfradd ddisgownt o 2%.

03 o 03

Excel VLOOKUP Ddim yn Gweithio: # N / A a #REF Errors

Negeseuon Gwall VLOOKUP

Mae'r negeseuon gwall canlynol yn gysylltiedig â VLOOKUP.

A # N / A ("gwerth nad yw ar gael") Gwall wedi'i ddangos Os:

• Ni chanfyddir yr olwg _pwl yng ngholofn gyntaf y ddadl amrediad
• Mae'r ddadl Table_array yn anghywir. Er enghraifft, gallai'r ddadl gynnwys colofnau gwag ar ochr chwith yr amrediad
• Mae'r ddadl Range_lookup wedi'i osod yn FALSE ac ni ellir dod o hyd i gêm union ar gyfer y ddadl search_key yng ngholofn gyntaf yr amrediad
• Gosodir y ddadl Range_lookup TRUE ac mae'r holl werthoedd yng ngholofn gyntaf yr amrediad yn fwy na'r search_key

#REF! ("cyfeirio allan o amrediad") Gwall wedi'i ddangos Os:

• Mae'r ddadl Col_index_num yn fwy na nifer y colofnau yn y Tabl_array