Skip i'r prif gynnwys

20+ o Enghreifftiau VLOOKUP Ar gyfer Dechreuwyr Excel a Defnyddwyr Uwch

Swyddogaeth VLOOKUP yw un o'r swyddogaethau mwyaf poblogaidd yn Excel. Bydd y tiwtorial hwn yn cyflwyno sut i ddefnyddio swyddogaeth VLOOKUP yn Excel gyda dwsinau o enghreifftiau sylfaenol ac uwch gam wrth gam.


Cyflwyno swyddogaeth VLOOKUP - Cystrawen a Dadleuon

Yn Excel, mae'r swyddogaeth VLOOKUP yn swyddogaeth bwerus i'r rhan fwyaf o ddefnyddwyr Excel, mae'n caniatáu ichi chwilio am werth yn y rhan fwyaf o'r ystod data ar y chwith, a dychwelyd gwerth cyfatebol yn yr un rhes o golofn a nodwyd gennych fel y sgrinlun a ddangosir isod .

Cystrawen swyddogaeth VLOOKUP:

=VLOOKUP (lookup_value, table_array, col_index_num, [range_lookup])

Dadleuon:

Edrych_gwerth (gofynnol): Y gwerth yr ydych am ei chwilio. Gall fod yn werth (rhif, dyddiad neu destun) neu gyfeirnod cell. Rhaid iddo fod yng ngholofn gyntaf yr ystod table_array. 

Tabl_array (gofynnol): Yr ystod data neu dabl lle mae'r golofn gwerth chwilio a'r golofn gwerth canlyniad wedi'u lleoli.

Col_index_num (gofynnol): Rhif y golofn sy'n cynnwys y gwerthoedd dychwelyd. Mae'n dechrau gydag 1 o'r golofn ar y chwith yn yr arae tabl.

Ystod_lookup (dewisol): Gwerth rhesymegol sy'n pennu a fydd y swyddogaeth VLOOKUP hon yn dychwelyd union gyfatebiaeth neu gyfatebiaeth fras.

  • Paru bras – 1 / GWIR / hepgor (diofyn): Os na chanfyddir cyfatebiaeth union, mae'r fformiwla'n chwilio am y cyfatebiad agosaf - y gwerth mwyaf sy'n llai na'r gwerth am-edrych.
    Hysbysiad: Yn yr achos hwn, rhaid i chi ddidoli'r golofn chwilio (colofn chwith yr ystod data) yn nhrefn esgynnol, fel arall bydd yn dychwelyd canlyniad gwall anghywir neu #N/A.
  • Cyfatebiaeth union – 0 / ANGHYWIR: Defnyddir hwn i chwilio am werth sy'n union yr un fath â'r gwerth edrych. Os na ddarganfyddir cyfatebiaeth union, dychwelir y gwerth gwall # Amherthnasol.

Nodiadau Swyddogaeth:

  • Mae swyddogaeth Vlookup yn chwilio am werth o'r chwith i'r dde yn unig.
  • Mae swyddogaeth Vlookup yn perfformio chwiliad achos-ansensitif.
  • Os oes gwerthoedd paru lluosog yn seiliedig ar y gwerth edrych i fyny, dim ond yr un cyntaf a barwyd fydd yn cael ei ddychwelyd trwy ddefnyddio'r swyddogaeth Vlookup.

Enghreifftiau sylfaenol VLOOKUP

Yn yr adran hon, byddwn yn siarad am rai fformiwlâu Vlookup a ddefnyddiwyd gennych yn aml.

2.1 Cydweddiad union a chyfateb yn fras VLOOKUP

 2.1.1 Gwnewch VLOOKUP yn cyfateb yn union

Fel arfer, os ydych chi'n chwilio am gydweddiad union â'r swyddogaeth VLOOKUP, does ond angen i chi ddefnyddio FALSE fel y ddadl olaf.

Er enghraifft, i gael y sgorau Mathemateg cyfatebol yn seiliedig ar y rhifau adnabod penodol, gwnewch hyn:

Copïwch a gludwch y fformiwla isod i mewn i gell wag (yma, rwy'n dewis G2), a gwasgwch Rhowch allwedd i gael y canlyniad:

=VLOOKUP(F2,$A$2:$D$7,3,FALSE)

Nodyn: Yn y fformiwla uchod, mae pedair dadl:

  • F2 yw'r gell sy'n cynnwys y gwerth C1005 yr ydych am ei chwilio;
  • A2: D7 yw'r arae tabl yr ydych yn perfformio'r chwilio ynddo;
  • 3 yw rhif y golofn y dychwelir eich gwerth cyfatebol ohono; (Unwaith y bydd y swyddogaeth yn gweld yr ID - C1005, bydd yn mynd i drydedd golofn yr arae tabl, ac yn dychwelyd y gwerthoedd yn yr un rhes â'r ID - C1005. )
  • Anghywir yn cyfeirio at yr union gyfatebiaeth.

Sut mae fformiwla VLOOKUP yn gweithio?

Yn gyntaf, mae'n edrych am yr ID - C1005 yng ngholofn fwyaf chwith y tabl. Mae'n mynd o'r top i'r gwaelod ac yn dod o hyd i'r gwerth yng nghell A6.

Cyn gynted ag y bydd yn dod o hyd i'r gwerth, mae'n mynd i'r dde yn y drydedd golofn ac yn tynnu'r gwerth ynddo.

Felly, fe gewch y canlyniad fel y sgrinlun a ddangosir isod:

Nodyn: Os na cheir y gwerth chwilio yn y golofn ar y chwith, mae'n dychwelyd gwall # N/A.
🤖 Kutools AI Aide: Chwyldro dadansoddi data yn seiliedig ar: Cyflawniad Deallus   |  Cynhyrchu Cod  |  Creu Fformiwlâu Personol  |  Dadansoddi Data a Chynhyrchu Siartiau  |  Invoke Swyddogaethau Kutools...
Nodweddion Poblogaidd: Darganfod, Amlygu neu Adnabod Dyblygiadau   |  Dileu Rhesi Gwag   |  Cyfuno Colofnau neu Gelloedd heb Colli Data   |   Rownd heb Fformiwla ...
Super-edrych: VLookup Meini Prawf Lluosog  |   VLookup Gwerth Lluosog  |   VLookup Ar Draws Taflenni Lluosog   |   Edrych Niwlog ...
Rhestr gwympo Uwch: Creu Rhestr Gollwng yn Gyflym   |  Rhestr Gollwng Dibynnol   |  Rhestr Gollwng Aml-ddewis ...
Rheolwr Colofn: Ychwanegu Nifer Penodol o Golofnau  |  Symud Colofnau   |  Datguddio Colofnau  |  Cymharwch Ystodau a Cholofnau ...
Nodweddion dan Sylw: Ffocws ar y Grid   |  Golwg Dylunio   |   Bar Fformiwla Mawr   |  Rheolwr Llyfr Gwaith a Thaflen  |  Llyfrgell Adnoddau   |  Dewiswr Dyddiad  |  Cyfuno Taflenni Gwaith   |  Amgryptio/Dadgryptio Celloedd    Anfon E-byst trwy Restr   |  Hidlo Super   |   Hidlo Arbennig (mewn print trwm/italig...) ...
Set Offer 15 Uchaf12 Testun offer (Ychwanegu Testun, Dileu Cymeriadau,...)   |   50 + Siart Mathau (Siart Gantt,...)   |   40+ Ymarferol Fformiwlâu (Cyfrifwch oedran yn seiliedig ar ben-blwydd,...)   |   19 mewnosod offer (Mewnosod Cod QR, Mewnosod Llun o'r Llwybr,...)   |   12 Trosi offer (Rhifau i Eiriau, Trosi arian cyfred,...)   |   7 Uno a Hollti offer (Rhesi Cyfuno Uwch, Celloedd Hollt,...)   |   Llawer Mwy...

Kutools ar gyfer Excel Mae ganddo Dros 300 o Nodweddion, Sicrhau mai dim ond clic i ffwrdd yw'r hyn sydd ei angen arnoch chi...

 
 2.1.2 VLOOKUP yn cyfateb yn fras

Mae'r cyfatebiad bras yn ddefnyddiol ar gyfer chwilio gwerthoedd rhwng ystodau data. Os na chanfyddir yr union gyfatebiaeth, bydd y VLOOKUP bras yn dychwelyd y gwerth mwyaf sy'n llai na'r gwerth chwilio.

Er enghraifft, os oes gennych yr ystod ganlynol o ddata, ac nad yw'r gorchmynion penodedig yn y golofn Gorchmynion, sut i gael ei Gostyngiad agosaf yng ngholofn B?

Cam 1: Cymhwyswch y fformiwla VLOOKUP a'i llenwi i gelloedd eraill

Copïwch a gludwch y fformiwla ganlynol i mewn i gell lle rydych chi am roi'r canlyniad, ac yna llusgwch y ddolen llenwi i lawr i gymhwyso'r fformiwla hon i gelloedd eraill.

=VLOOKUP(D2,$A$2:$B$9,2,TRUE)

Canlyniad:

Nawr, fe gewch y cyfatebiaethau bras yn seiliedig ar y gwerthoedd a roddwyd, gweler y sgrinlun:

Nodiadau:

  • Yn y fformiwla uchod:
    • D2 yw'r gwerth yr ydych am ddychwelyd ei wybodaeth gymharol;
    • A2: B9 yw'r ystod data;
    • 2 yn nodi rhif y golofn y dychwelir eich gwerth cyfatebol;
    • TRUE yn cyfeirio at yr cyfatebiaeth fras.
  • Bydd y cyfatebiad bras yn dychwelyd y gwerth mwyaf sy'n llai na'ch gwerth chwilio penodol os na chanfyddir cyfatebiaeth union.
  • I ddefnyddio'r swyddogaeth VLOOKUP i gael gwerth cyfatebol bras, rhaid i chi ddidoli'r golofn fwyaf chwith o'r ystod ddata mewn trefn esgynnol, fel arall bydd yn dychwelyd canlyniad anghywir.

2.2 Gwnewch VLOOKUP achos sensitif yn Excel

Yn ddiofyn, mae'r swyddogaeth VLOOKUP yn perfformio chwiliad cas ansensitif sy'n golygu ei fod yn trin llythrennau bach a phriflythrennau fel yr un fath. Rhywbryd, efallai y bydd angen i chi berfformio chwiliad achos-sensitif yn Excel, efallai na fydd y swyddogaeth VLOOKUP arferol yn ei ddatrys. Yn yr achos hwn, gallwch ddefnyddio swyddogaethau amgen megis INDEX a MATCH gyda'r swyddogaeth EXACT, neu'r swyddogaethau LOOKUP ac EXACT.

Er enghraifft, mae gennyf yr ystod ddata ganlynol pa golofn ID sy'n cynnwys llinyn testun gyda llythrennau bach neu lythrennau bach, nawr, rwyf am ddychwelyd y sgôr Math cyfatebol o'r rhif ID a roddir.

Cam 1: Defnyddiwch unrhyw un fformiwla a'i llenwi i gelloedd eraill

Copïwch a gludwch unrhyw un o'r fformiwlâu isod i mewn i gell wag lle rydych chi am gael y canlyniad. Yna, dewiswch y gell fformiwla, llusgwch y ddolen llenwi i lawr i'r celloedd lle rydych chi am lenwi'r fformiwla hon.

Fformiwla 1: Ar ôl gludo'r fformiwla, pwyswch Ctrl + Shift + Enter allweddi.

=INDEX($C$2:$C$10,MATCH(TRUE,EXACT(F2,$A$2:$A$10),0))

Fformiwla 2: Ar ôl gludo'r fformiwla, pwyswch Rhowch allweddol.

=LOOKUP(2,1/EXACT(F2,$A$2:$A$10),$C$2:$C$10)

Canlyniad:

Yna byddwch yn cael y canlyniadau cywir sydd eu hangen arnoch. Gweler y sgrinlun:

Nodiadau:

  • Yn y fformiwla uchod:
    • A2: A10 yw'r golofn sy'n cynnwys y gwerthoedd penodol yr ydych am edrych i fyny ynddynt;
    • F2 yw'r gwerth edrych;
    • C2: C10 yw'r golofn lle dychwelir y canlyniad.
  • Os canfyddir paru lluosog, bydd y fformiwla hon bob amser yn dychwelyd y gêm olaf.

2.3 Gwerthoedd VLOOKUP o'r dde i'r chwith yn Excel

Mae'r ffwythiant VLOOKUP bob amser yn chwilio gwerth yn y golofn ar y chwith o'r amrediad data ac yn dychwelyd y gwerth cyfatebol o golofn i'r dde. Os ydych chi am berfformio VLOOKUP gwrthdro sy'n golygu chwilio am werth penodol yn y golofn dde a dychwelyd ei werth cyfatebol yn y golofn chwith fel y sgrinlun isod:

Cliciwch i wybod y manylion gam wrth gam am y dasg hon ...


2.4 VLOOKUP yr ail, nfed neu werth cyfatebol olaf yn Excel

Fel arfer, os canfyddir gwerthoedd paru lluosog wrth ddefnyddio'r swyddogaeth Vlookup, dim ond y cofnod cyfatebol cyntaf fydd yn cael ei ddychwelyd. Yn yr adran hon, byddaf yn siarad am sut i gael yr ail, yr nfed neu'r gwerth cyfatebol olaf mewn ystod data.

 2.4.1 VLOOKUP a dychwelyd yr ail neu'r nfed gwerth cyfatebol

Tybiwch fod gennych restr o enwau yng ngholofn A, y cwrs hyfforddi a brynwyd ganddynt yng ngholofn B. Nawr, rydych chi'n edrych i ddod o hyd i'r 2il neu'r nfed cwrs hyfforddi a brynwyd gan y cwsmer penodol. Gweler y sgrinlun:

Yma, efallai na fydd y swyddogaeth VLOOKUP yn datrys y dasg hon yn uniongyrchol. Ond, gallwch ddefnyddio'r swyddogaeth MYNEGAI fel dewis arall.

Cam 1: Cymhwyso a llenwi'r fformiwla i gelloedd eraill

Er enghraifft, i gael yr ail werth cyfatebol yn seiliedig ar y meini prawf a roddwyd, cymhwyswch y fformiwla ganlynol i mewn i gell wag, a gwasgwch Ctrl + Shift + Enter allweddi gyda'i gilydd i gael y canlyniad cyntaf. Ac yna, dewiswch y gell fformiwla, llusgwch y ddolen llenwi i lawr i'r celloedd lle rydych chi am lenwi'r fformiwla hon.

=INDEX($B$2:$B$14,SMALL(IF(E2=$A$2:$A$14,ROW($A$2:$A$14)-ROW($A$2)+1),2))

Canlyniad:

Nawr, mae'r ail werthoedd cyfatebol yn seiliedig ar yr enwau a roddwyd wedi'u harddangos ar unwaith.

Nodyn: Yn y fformiwla uchod:

  • A2: A14 yw'r amrediad gyda'r holl werthoedd ar gyfer chwilio;
  • B2: B14 yw ystod y gwerthoedd paru yr ydych am ddychwelyd ohonynt;
  • E2 yw'r gwerth edrych;
  • 2 yn nodi'r ail werth cyfatebol rydych chi am ei gael, i ddychwelyd y trydydd gwerth cyfatebol, does ond angen i chi ei newid i 3.
 2.4.2 VLOOKUP a dychwelyd y gwerth cyfatebol olaf

Os ydych chi am wylio a dychwelyd y gwerth paru olaf fel y nodir isod, dangosir hyn VLOOKUP A Dychwelyd Y Gwerth Cyfatebol Diwethaf efallai y bydd tiwtorial yn eich helpu i gael y gwerth cyfatebol olaf mewn manylion.


2.5 VLOOKUP yn cyfateb gwerthoedd rhwng dau werth neu ddyddiad penodol

Weithiau, efallai y byddwch am chwilio gwerthoedd rhwng dau werth neu ddyddiad a dychwelyd y canlyniadau cyfatebol fel y dangosir yn y sgrin isod. Mewn achos o'r fath, gallwch ddefnyddio'r swyddogaeth LOOKUP yn lle swyddogaeth VLOOKUP gyda thabl wedi'i ddidoli.

 2.5.1 VLOOKUP yn paru gwerthoedd rhwng dau werth neu ddyddiad penodol â fformiwla

Cam 1: Trefnwch y data a chymhwyso'r fformiwla ganlynol

Dylai eich tabl gwreiddiol fod yn ystod data wedi'i didoli. Ac yna, copïwch neu nodwch y fformiwla ganlynol i mewn i gell wag. Yna, llusgwch yr handlen llenwi i lenwi'r fformiwla hon i gelloedd eraill sydd eu hangen arnoch.

=LOOKUP(2,1/($A$2:$A$6<=E2)/($B$2:$B$6>=E2),$C$2:$C$6)

Canlyniad:

Ac yn awr, fe gewch yr holl gofnodion cyfatebol yn seiliedig ar y gwerth penodol, gweler y sgrinlun:

Nodiadau:

  • Yn y fformiwla uchod:
    • A2: A6 yw'r ystod o werthoedd llai;
    • B2: B6 yw ystod y niferoedd mwy;
    • E2 yw'r gwerth chwilio yr ydych am gael ei werth cyfatebol;
    • C2: C6 yw'r golofn yr ydych am ddychwelyd gwerth cyfatebol ohoni.
  • Gellir defnyddio'r fformiwla hon hefyd ar gyfer echdynnu gwerthoedd cyfatebol rhwng dau ddyddiad fel y dangosir y sgrinlun isod:
 2.5.2 VLOOKUP yn paru gwerthoedd rhwng dau werth neu ddyddiad penodol gyda nodwedd ddefnyddiol

Os ydych chi'n ei chael hi'n anodd cofio a deall y fformiwla uchod, yma, byddaf yn cyflwyno offeryn hawdd - Kutools ar gyfer Excel, Gyda'i LOOKUP rhwng Dau Werth nodwedd, gallwch ddychwelyd yr eitem cyfatebol yn seiliedig ar y gwerth penodol neu ddyddiad rhwng dau werth neu ddyddiad yn rhwydd.

  1. Cliciwch Kutools > LOOKUP Super > LOOKUP rhwng Dau Werth i alluogi'r nodwedd hon.
  2. Yna nodwch y gweithrediadau o'r blwch deialog yn seiliedig ar eich data.
Nodyn: I gymhwyso'r nodwedd hon, dylech lawrlwytho Kutools ar gyfer Excel gyda threial 30 diwrnod am ddim yn gyntaf.


2.6 Defnyddio wildcards ar gyfer paru rhannol yn swyddogaeth VLOOKUP

Yn Excel, gellir defnyddio'r cardiau gwyllt o fewn y swyddogaeth VLOOKUP, sy'n eich galluogi i berfformio cyfatebiad rhannol ar werth chwilio. Er enghraifft, gallwch ddefnyddio VLOOKUP i ddychwelyd gwerth cyfatebol o dabl yn seiliedig ar ran o werth chwilio.

Gan dybio, mae gen i ystod o ddata fel islaw'r screenshot a ddangosir, nawr, rwyf am echdynnu'r sgôr yn seiliedig ar yr enw cyntaf (nid enw llawn). Sut allai ddatrys y dasg hon yn Excel?

Cam 1: Cymhwyso a llenwi'r fformiwla i gelloedd eraill

Copïwch neu rhowch y fformiwla ganlynol i mewn i gell wag, ac yna, llusgwch y ddolen lenwi i lenwi'r fformiwla hon i gelloedd eraill sydd eu hangen arnoch:

=VLOOKUP(E2&"*", $A$2:$C$11, 3, FALSE)

Canlyniad:

Ac mae'r holl sgoriau cyfatebol wedi'u dychwelyd fel y dangosir y sgrinlun isod:

Nodyn: Yn y fformiwla uchod:

  • E2 & ”*” yw'r meini prawf ar gyfer y mathemateg rhannol. Mae hyn yn golygu eich bod yn chwilio am unrhyw werth sy'n dechrau gyda'r gwerth yng nghell E2. (Y cerdyn gwyllt "*” yn dynodi unrhyw un nod neu unrhyw nodau)
  • A2: C11 yw'r ystod o ddata lle rydych am chwilio am y gwerth cyfatebol;
  • 3 yn golygu dychwelyd y gwerth cyfatebol o 3edd golofn yr ystod data;
  • Anghywir yn dynodi'r union fathemateg. (Wrth ddefnyddio wildcards, rhaid i chi osod y ddadl olaf yn y ffwythiant fel GAU neu 0 i alluogi modd paru union yn swyddogaeth VLOOKUP.)
Awgrymiadau:
  • I ddarganfod a dychwelyd y gwerthoedd paru sy'n gorffen gyda gwerth penodol, dylech roi'r cerdyn gwyllt "*" o flaen y gwerth. Cymhwyswch y fformiwla hon os gwelwch yn dda:
  • =VLOOKUP("*"&E2, $A$2:$C$11, 3, FALSE)

  • I chwilio a dychwelyd y gwerth cyfatebol yn seiliedig ar ran o'r llinyn testun, p'un a yw'r testun penodedig ar y bebinning, diwedd neu yng nghanol y llinyn testun, does ond angen i chi amgáu cyfeirnod y gell neu'r testun gyda dwy seren (*) ar y ddwy ochr. Gwnewch gyda'r fformiwla hon
  • =VLOOKUP("*"&D2&"*", $A$2:$B$11, 2, FALSE)


2.7 Gwerthoedd VLOOKUP o daflen waith arall

Fel arfer, efallai y bydd yn rhaid i chi weithio gyda mwy nag un daflen waith, gellir defnyddio'r swyddogaeth VLOOKUP i chwilio data o ddalen arall yr un fath ag ar un daflen waith.

Er enghraifft, mae gennych ddwy daflen waith fel y dangosir isod y llun, er mwyn edrych a dychwelyd y data cyfatebol o'r daflen waith a nodwyd gennych, gwnewch y camau canlynol:

Cam 1: Cymhwyso a llenwi'r fformiwla i gelloedd eraill

Rhowch neu copïwch y fformiwla isod i mewn i gell wag lle rydych chi am gael yr eitemau cyfatebol. Yna, llusgwch yr handlen llenwi i lawr i'r celloedd rydych chi am gymhwyso'r fformiwla hon.

=VLOOKUP(A2,'Data sheet'!$A$2:$C$15,3,0)

Canlyniad:

Fe gewch y canlyniadau cyfatebol yn ôl yr angen, gweler y sgrinlun:

Nodyn: Yn y fformiwla uchod:

  • A2 yn cynrychioli'r gwerth edrych;
  • 'Taflen ddata'!A2:C15 yn nodi i chwilio'r gwerthoedd o'r ystod A2:C15 ar y daflen waith o'r enw Data sheet; (Os yw enw'r ddalen yn cynnwys gofod neu nodau atalnodi, dylech amgáu enw'r ddalen mewn dyfyniadau sengl, fel arall, gallwch ddefnyddio enw'r ddalen yn uniongyrchol fel =VLOOKUP(A2,Datalen!$A$2:$C$15,3,0) ).
  • 3 yw rhif y golofn sy'n cynnwys data cyfatebol rydych am ddychwelyd ohono;
  • 0 yn golygu perfformio cyfatebiaeth union.

2.8 o werthoedd VLOOKUP o lyfr gwaith arall

Bydd yr adran hon yn sôn am chwilio ac yn dychwelyd y gwerthoedd cyfatebol o lyfr gwaith gwahanol trwy ddefnyddio'r swyddogaeth VLOOKUP.

Er enghraifft, gadewch i ni ddweud bod gennych chi ddau lyfr gwaith. Mae'r llyfr gwaith cyntaf yn cynnwys rhestr o gynhyrchion a'u costau priodol. Yn yr ail lyfr gwaith, rydych chi am dynnu'r gost gyfatebol ar gyfer pob eitem cynnyrch fel y sgrinlun a ddangosir isod.

Cam 1: Cymhwyso a llenwi'r fformiwla

Agorwch y ddau lyfr gwaith rydych chi am eu defnyddio, yna cymhwyswch y fformiwla ganlynol i mewn i gell lle rydych chi am roi'r canlyniad yn yr ail lyfr gwaith. Yna, llusgo a chopïo'r fformiwla hon i gelloedd eraill sydd eu hangen arnoch chi

=VLOOKUP(B2,'[Product list.xlsx]Sheet1'!$A$2:$B$6,2,0)

Canlyniad:

Nodiadau:

  • Yn y fformiwla uchod:
    • B2 yn cynrychioli'r gwerth edrych;
    • '[Rhestr cynnyrch.xlsx]Taflen1'!A2:B6 yn dynodi i chwilio o'r ystod A2:B6 ar y ddalen a enwir Dalen1 o'r llyfr gwaith Rhestr cynnyrch; (Mae'r cyfeiriad at y llyfr gwaith wedi'i amgáu mewn cromfachau sgwâr, ac mae'r llyfr gwaith cyfan + taflen wedi'i amgáu mewn dyfyniadau sengl.)
    • 2 yw rhif y golofn sy'n cynnwys data cyfatebol rydych am ddychwelyd ohono;
    • 0 yn dynodi dychwelyd union gyfatebiaeth.
  • Os yw'r llyfr gwaith chwilio ar gau, bydd y llwybr ffeil llawn ar gyfer y llyfr gwaith chwilio yn cael ei ddangos yn y fformiwla fel y dangosir y sgrinlun a ganlyn:

2.9 Dychwelyd testun gwag neu destun penodol yn lle gwall 0 neu #N/A

Fel arfer, pan fyddwch yn defnyddio'r ffwythiant VLOOKUP i ddychwelyd gwerth cyfatebol, os yw'r gell paru yn wag, bydd yn dychwelyd 0. Ac os na chanfyddir y gwerth cyfatebol, fe gewch werth gwall o # N/A fel y dangosir yn y screenshot isod. Os ydych chi am arddangos cell wag neu werth penodol yn lle 0 neu # N/A, hwn VLOOKUP I Ddychwelyd Gwerth Gwag Neu Benodol yn lle 0 Neu Amh gall tiwtorial wneud ffafr i chi.


Enghreifftiau VLOOKUP uwch

3.1 Chwilio dwy ffordd (VLOOKUP mewn rhes a cholofn)

Weithiau, efallai y bydd angen i chi berfformio chwiliad 2 ddimensiwn, sy'n golygu chwilio am werth yn y rhes a'r golofn ar yr un pryd. Er enghraifft, os oes gennych yr ystod ddata ganlynol, ac efallai y bydd angen i chi gael y gwerth ar gyfer cynnyrch penodol mewn chwarter penodol. Bydd yr adran hon yn cyflwyno fformiwla ar gyfer delio â'r swydd hon yn Excel.

Yn Excel, gallwch ddefnyddio cyfuniad o swyddogaethau VLOOKUP a MATCH i wneud chwiliad dwy ffordd.

Cymhwyswch y fformiwla ganlynol i mewn i gell wag, ac yna pwyswch Rhowch allwedd i gael y canlyniad.

=VLOOKUP(G2, $A$2:$E$7, MATCH(H1, $A$2:$E$2, 0), FALSE)

Nodyn: Yn y fformiwla uchod:

  • G2 yw'r gwerth chwilio yn y golofn yr ydych am gael y gwerth cyfatebol yn seiliedig arno;
  • A2: E7 yw'r tabl data y byddwch yn edrych ohono;
  • H1 yw'r gwerth chwilio yn y rhes yr ydych am gael y gwerth cyfatebol yn seiliedig arno;
  • A2: E2 yw celloedd penawdau colofn;
  • Anghywir yn dynodi i gael union gyfatebiaeth.

3.2 Gwerth cyfatebol VLOOKUP yn seiliedig ar ddau faen prawf neu fwy

Mae'n hawdd i chi edrych ar y gwerth cyfatebol yn seiliedig ar un maen prawf, ond os oes gennych ddau faen prawf neu fwy, beth allwch chi ei wneud?

 3.2.1 Gwerth cyfatebol VLOOKUP yn seiliedig ar ddau faen prawf neu fwy gyda fformiwlâu

Yn yr achos hwn, gall y swyddogaethau LOOKUP neu MATCH a MYNEGAI yn Excel eich helpu i ddatrys y swydd hon yn gyflym ac yn hawdd.

Er enghraifft, mae gennyf y tabl data isod, i ddychwelyd y pris cyfatebol yn seiliedig ar y cynnyrch a'r maint penodol, gall y fformwlâu canlynol eich helpu.

Cam 1: Defnyddiwch unrhyw un fformiwla

Fformiwla 1: Ar ôl gludo'r fformiwla, pwyswch Rhowch allweddol.

=LOOKUP(2,1/($A$2:$A$12=G1)/($B$2:$B$12=G2),($D$2:$D$12))

Fformiwla 2: Ar ôl gludo'r fformiwla, pwyswch Ctrl + Shift + Enter allweddi.

=INDEX($D$2:$D$12,MATCH(1,($A$2:$A$12=G1)*($B$2:$B$12=G2),0))

Canlyniad:

Nodiadau:

  • Yn y fformwlâu uchod:
    • A2: A12 = G1 modd chwilio meini prawf G1 yn ystod A2:A12;
    • B2: B12 = G2 yn golygu chwilio meini prawf G2 yn ystod B2:B12;
    • D2: D12 is yr ystod yr ydych am ddychwelyd y gwerth cyfatebol ohoni.
  • Os oes gennych fwy na dau faen prawf, y cyfan sydd angen i chi ei wneud yw ymuno â'r meini prawf eraill yn y fformiwla, megis:
    =LOOKUP(2,1/($A$2:$A$12=G1)/($B$2:$B$12=G2)/($C$2:$C$12=G3),($D$2:$D$12))
    =INDEX($D$2:$D$12,MATCH(1,($A$2:$A$12=G1)*($B$2:$B$12=G2)*($C$2:$C$12=G3),0))
 3.2.2 Gwerth cyfatebol VLOOKUP yn seiliedig ar ddau faen prawf neu fwy gyda nodwedd glyfar

Gall fod yn heriol cofio'r fformiwlâu cymhleth uchod y mae angen eu cymhwyso dro ar ôl tro, a all arafu eich effeithlonrwydd gwaith. Fodd bynnag, Kutools ar gyfer Excel yn cynnig a Edrych Aml-gyflwr nodwedd sy'n eich galluogi i ddychwelyd y canlyniad cyfatebol yn seiliedig ar un neu fwy o amodau gyda dim ond sawl clic.

  1. Cliciwch Kutools > LOOKUP Super > Edrych Aml-gyflwr i alluogi'r nodwedd hon.
  2. Yna nodwch y gweithrediadau o'r blwch deialog yn seiliedig ar eich data.
Nodyn: I gymhwyso'r nodwedd hon, dylech lawrlwytho Kutools ar gyfer Excel gyda threial 30 diwrnod am ddim yn gyntaf.


3.3 VLOOKUP i ddychwelyd gwerthoedd lluosog gydag un neu fwy o feini prawf

Yn Excel, mae swyddogaeth VLOOKUP yn chwilio am werth a dim ond yn dychwelyd y gwerth cyfatebol cyntaf os canfyddir gwerthoedd cyfatebol lluosog. Weithiau, efallai y byddwch am ddychwelyd yr holl werthoedd cyfatebol yn olynol, mewn colofn neu mewn un gell. Bydd yr adran hon yn sôn am sut i ddychwelyd y gwerthoedd paru lluosog ag un neu fwy o amodau mewn llyfr gwaith.

 3.3.1 VLOOKUP pob gwerth cyfatebol yn seiliedig ar un neu fwy o amodau yn llorweddol

Gan dybio bod gennych dabl o ddata sy'n cynnwys gwlad, dinas ac enwau yn yr ystod A1: C14, a nawr, rydych chi am ddychwelyd yr holl enwau yn llorweddol sydd o "UD" fel y sgrinlun a ddangosir isod. I ddatrys y dasg hon, os gwelwch yn dda cliciwch yma i gael y canlyniad gam wrth gam.

 3.3.2 VLOOKUP pob gwerth cyfatebol yn seiliedig ar un neu fwy o amodau yn fertigol

Os oes angen i chi Vlookup a dychwelyd yr holl werthoedd cyfatebol yn fertigol yn seiliedig ar feini prawf penodol fel y dangosir y sgrinlun isod, cliciwch yma i gael yr ateb yn fanwl.

 3.3.3 VLOOKUP yr holl werthoedd sy'n cyfateb yn seiliedig ar un neu fwy o amodau yn un gell

Os ydych chi eisiau Vlookup a dychwelyd gwerthoedd cyfatebol lluosog i mewn i un gell gyda gwahanydd penodedig, gall swyddogaeth newydd TEXTJOIN eich helpu i ddatrys y swydd hon yn gyflym ac yn hawdd.

Nodiadau:


3.4 VLOOKUP i ddychwelyd rhes gyfan o gell cyfatebol

Yn yr adran hon, byddaf yn siarad am sut i adfer y rhes gyfan o werth cyfatebol trwy ddefnyddio'r swyddogaeth VLOOKUP.

Cam 1: Cymhwyso a llenwi'r fformiwla ganlynol

Copïwch neu deipiwch y fformiwla isod i mewn i gell wag lle rydych chi am allbynnu'r canlyniad, a gwasgwch Rhowch allweddol i gael y gwerth cyntaf. Yna, llusgwch y gell fformiwla i'r dde nes bod data'r rhes gyfan yn cael ei arddangos.

=VLOOKUP($F$2,$A$1:$D$12,COLUMN(A1),FALSE)

Canlyniad:

Nawr, gallwch weld y data rhes cyfan yn cael ei ddychwelyd. Gweler y sgrinlun:
swyddogaeth vlookup doc 50 1

Nodyn: yn y fformiwla uchod:

  • F2 yw'r gwerth chwilio rydych chi am ddychwelyd y rhes gyfan yn seiliedig arno;
  • A1: D12 yw'r ystod data rydych chi am chwilio am y gwerth chwilio ohoni;
  • A1 yn nodi rhif y golofn gyntaf o fewn eich ystod data;
  • Anghywir yn dynodi union chwilio.

Awgrym:

  • Os canfyddir rhesi lluosog yn seiliedig ar y gwerth cyfatebol, i ddychwelyd pob un o'r rhesi cyfatebol, cymhwyswch y fformiwla isod, yna pwyswch Ctrl + Shift + Enter allweddi gyda'i gilydd i gael y canlyniad cyntaf. Yna llusgwch yr handlen llenwi i'r dde. Ac yna, ewch ymlaen i lusgo'r handlen llenwi i lawr ar draws y celloedd i gael yr holl resi cyfatebol. Gweler y demo isod:
    =IFERROR(INDEX(A:A,SMALL(IF(ISNUMBER(SEARCH($F$2,$A$2:$A$12)),ROW($A$2:$A$12),""),ROW()-1)),"")
    swyddogaeth vlookup doc 51 2

3.5 Wedi nythu VLOOKUP yn Excel

Weithiau, efallai y bydd angen i chi chwilio am werthoedd sydd wedi'u cydgysylltu ar draws tablau lluosog. Yn yr achos hwn, gallwch chi nythu swyddogaeth VLOOKUP lluosog gyda'i gilydd i gael y gwerth terfynol.

Er enghraifft, mae gen i daflen waith sy'n cynnwys dau dabl ar wahân. Mae'r tabl cyntaf yn rhestru'r holl enwau cynnyrch ynghyd â'u gwerthwr cyfatebol. Mae'r ail dabl yn rhestru cyfanswm gwerthiant pob gwerthwr. Nawr, os ydych chi am ddod o hyd i werthiant pob cynnyrch, fel y dangosir yn y sgrin ganlynol, gallwch chi nythu'r swyddogaeth VLOOKUP i gyflawni'r dasg hon.
swyddogaeth vlookup doc 53 1

Y fformiwla generig ar gyfer swyddogaeth VLOOKUP nythu yw:

=VLOOKUP(VLOOKUP(lookup_value, table_array1, col_index_num1, 0), table_array2, col_index_num2, 0)

Nodyn:

  • lookup_value yw'r gwerth rydych chi'n edrych amdano;
  • Tabl_arae1, Tabl_arae2 yw'r tablau lle mae'r gwerth am-edrych a'r gwerth dychwelyd yn bodoli;
  • col_index_num1 yn nodi rhif y golofn yn y tabl cyntaf ar gyfer canfod y data cyffredin canolradd;
  • col_index_num2 yn nodi rhif y golofn yn yr ail dabl eich bod am ddychwelyd y gwerth cyfatebol;
  • 0 yn cael ei ddefnyddio ar gyfer cyfatebiad union.

Cam 1: Cymhwyso a llenwi'r fformiwla ganlynol

Cymhwyswch y fformiwla ganlynol i mewn i gell wag, ac yna llusgwch y ddolen llenwi i lawr i'r celloedd yr ydych am gymhwyso'r fformiwla hon.

=VLOOKUP(VLOOKUP(G3,$A$3:$B$7,2,0),$D$3:$E$7,2,0)

Canlyniad:

Nawr, fe gewch y canlyniad fel y dangosir yn y sgrinlun canlynol:

Nodyn: yn y fformiwla uchod:

  • G3 yn cynnwys y gwerth yr ydych yn chwilio amdano;
  • A3: B7, D3: E7 yw'r ystodau tabl lle mae'r gwerth am-edrych a'r gwerth dychwelyd yn bodoli;
  • 2 yw'r rhif colofn yn yr ystod i ddychwelyd y gwerth cyfatebol ohono.
  • 0 yn dynodi VLOOKUP mathemateg union.

3.6 Gwiriwch a yw gwerth yn bodoli ar sail data rhestr mewn colofn arall

Gall y swyddogaeth VLOOKUP hefyd eich helpu i wirio a yw gwerthoedd yn bodoli yn seiliedig ar y rhestr ddata mewn colofn arall. Er enghraifft, os ydych chi am chwilio am yr enwau yng ngholofn C a dychwelyd Ie neu Na dim ond os yw'r enw i'w gael ai peidio yng ngholofn A fel y dangosir y sgrinlun isod.
swyddogaeth vlookup doc 56 1

Cam 1: Cymhwyso a llenwi'r fformiwla ganlynol

Cymhwyswch y fformiwla ganlynol i mewn i gell wag, yna, llusgwch y ddolen lenwi i lawr i'r celloedd rydych chi am lenwi'r fformiwla hon.

=IF(ISNA(VLOOKUP(C2,$A$2:$A$10,1,FALSE)), "No", "Yes")

Canlyniad:

A byddwch yn cael y canlyniad yn ôl yr angen, gweler y sgrinlun:

Nodyn: yn y fformiwla uchod:

  • C2 yw'r gwerth edrych rydych chi am ei wirio;
  • A2: A10 yw'r rhestr o ystodau o ble i wirio a fydd y gwerthoedd chwilio i'w cael ai peidio;
  • Anghywir yn dynodi i gael union gyfatebiaeth.

3.7 VLOOKUP a chrynhoi'r holl werthoedd cyfatebol mewn rhesi neu golofnau

Wrth weithio gyda data rhifiadol, efallai y bydd angen i chi dynnu gwerthoedd cyfatebol o dabl a chrynhoi'r rhifau mewn sawl colofn neu res. Bydd yr adran hon yn cyflwyno rhai fformiwlâu a all eich helpu i gyflawni'r dasg hon.

 3.7.1 VLOOKUP a swm yr holl werthoedd cyfatebol mewn rhes neu resi lluosog

Tybiwch fod gennych restr cynnyrch gyda gwerthiant am sawl mis, fel y dangosir yn y sgrin ganlynol. Nawr, mae angen i chi grynhoi pob archeb ym mhob mis yn seiliedig ar y cynhyrchion a roddir.

Cam 1: Cymhwyso a llenwi'r fformiwla ganlynol

Copïwch neu rhowch y fformiwla ganlynol i mewn i gell wag, ac yna pwyswch Ctrl + Shift + Enter allweddi gyda'i gilydd i gael y canlyniad cyntaf. Yna, llusgwch y ddolen llenwi i lawr i gopïo'r fformiwla hon i gelloedd eraill sydd eu hangen arnoch chi.

=SUM(VLOOKUP(H2, $A$2:$F$9, {2,3,4,5,6}, FALSE))

Canlyniad:

Mae'r holl werthoedd mewn rhes o'r gwerth cyfatebol cyntaf wedi'u crynhoi gyda'i gilydd, gweler y sgrinlun:

Nodyn: yn y fformiwla uchod:

  • H2 yw'r gell sy'n cynnwys y gwerth rydych chi'n edrych amdano;
  • A2: F9 yw'r amrediad data (heb benawdau colofn) sy'n cynnwys y gwerth am-edrych a'r gwerthoedd cyfatebol;
  • {2,3,4,5,6} yw rhifau colofn a ddefnyddir i gyfrifo cyfanswm yr amrediad;
  • Anghywir yn dynodi cyfatebiaeth union.

Awgrym: Os ydych chi am grynhoi pob cyfatebiaeth mewn rhesi lluosog, defnyddiwch y fformiwla ganlynol:

  • =SUMPRODUCT(($A$2:$A$9=H2)*$B$2:$F$9)
 3.7.2 VLOOKUP a chrynhoi'r holl werthoedd cyfatebol mewn colofn neu golofnau lluosog

Os ydych chi am grynhoi'r cyfanswm gwerth ar gyfer y misoedd penodol fel y dangosir yn y screenshot isod. Mae'n bosibl na fydd y swyddogaeth VLOOKUP arferol yn eich helpu, yma, dylech gymhwyso'r swyddogaethau SUM, INDEX a MATCH gyda'i gilydd i greu fformiwla.

Cam 1: Defnyddiwch y fformiwla ganlynol

Cymhwyswch y fformiwla isod i mewn i gell wag, ac yna llusgwch y ddolen llenwi i lawr i gopïo'r fformiwla hon i gelloedd eraill.

=SUM(INDEX($B$2:$F$9,0,MATCH(H2,$B$1:$F$1,0)))

Canlyniad:

Nawr, mae'r gwerthoedd cyfatebol cyntaf yn seiliedig ar y mis penodol mewn colofn wedi'u crynhoi at ei gilydd, gweler y sgrinlun:

Nodyn: yn y fformiwla uchod:

  • H2 yw'r gell sy'n cynnwys y gwerth rydych chi'n edrych amdano;
  • B1: F1 yw'r penawdau colofn sy'n cynnwys y gwerth edrych;
  • B2: F9 yw'r ystod data sy'n cynnwys y gwerthoedd rhifol yr ydych am eu crynhoi.

Awgrym: I VLOOKUP a chrynhoi'r holl werthoedd cyfatebol mewn colofnau lluosog, dylech ddefnyddio'r fformiwla ganlynol:

  • =SUMPRODUCT($B$2:$F$9*(($B$1:$F$1)=H2))
 3.7.3 VLOOKUP a chrynhoi'r gwerthoedd cyfatebol cyntaf neu'r holl werthoedd cyfatebol gyda nodwedd bwerus

Efallai bod y fformiwlâu uchod yn anodd i chi eu cofio, yn yr achos hwn, byddaf yn argymell nodwedd bwerus - Edrych a Swm of Kutools ar gyfer Excel, gyda'r nodwedd hon, gallwch chi Vlookup a chrynhoi'r gwerthoedd cyfatebol cyntaf neu'r holl werthoedd cyfatebol mewn rhesi neu golofnau mor hawdd â phosib.

  1. Cliciwch Kutools > LOOKUP Super > LOOKUP a Swm i alluogi'r nodwedd hon.
  2. Yna nodwch y gweithrediadau o'r blwch deialog yn seiliedig ar eich angen.
Nodyn: I gymhwyso'r nodwedd hon, dylech lawrlwytho Kutools ar gyfer Excel gyda threial 30 diwrnod am ddim yn gyntaf.
 3.7.4 VLOOKUP a chrynhoi'r holl werthoedd cyfatebol mewn rhesi a cholofnau

Os ydych chi am grynhoi'r gwerthoedd pan fydd angen i chi gyfateb colofn a rhes, er enghraifft, i gael cyfanswm gwerth y siwmper cynnyrch ym mis Mawrth fel y dangosir y llun isod.

Yma, gallwch ddefnyddio'r swyddogaeth SUMPRODCT i gyflawni'r dasg hon.

Cymhwyswch y fformiwla ganlynol i mewn i gell, ac yna pwyswch Rhowch allwedd i gael y canlyniad, gweler y screenshot:

=SUMPRODUCT(($B$2:$F$9)*($B$1:$F$1=I2)*($A$2:$A$9=H2))

Nodyn: Yn y fformiwla uchod:

  • B2: F9 yw'r ystod data sy'n cynnwys y gwerthoedd rhifol yr ydych am eu crynhoi;
  • B1: F1 yw penawdau'r golofn sy'n cynnwys y gwerth am-edrych yr ydych am ei grynhoi yn seiliedig arno;
  • I2 yw'r gwerth edrych o fewn y penawdau colofnau rydych chi'n edrych amdanynt;
  • A2: A9 yw'r penawdau rhes sy'n cynnwys y gwerth am-edrych yr ydych am ei grynhoi yn seiliedig arno;
  • H2 yw'r gwerth am-edrych o fewn y penawdau rhes yr ydych yn chwilio amdanynt.

3.8 VLOOKUP i uno dau dabl yn seiliedig ar golofnau allweddol

Yn eich gwaith dyddiol, wrth ddadansoddi data, efallai y bydd angen i chi gasglu'r holl wybodaeth angenrheidiol mewn un tabl yn seiliedig ar un neu fwy o golofnau allweddol. I gyflawni'r dasg hon, gallwch ddefnyddio'r swyddogaethau INDEX a MATCH yn lle'r swyddogaeth VLOOKUP.

 3.8.1 VLOOKUP i uno dau dabl yn seiliedig ar un golofn allweddol

Er enghraifft, mae gennych ddau dabl, y tabl cyntaf sy'n cynnwys y data cynhyrchion ac enwau, ac mae'r ail dabl yn cynnwys y data cynhyrchion a gorchmynion, nawr, rydych chi am gyfuno'r ddau dabl hyn trwy gydweddu'r golofn cynnyrch cyffredin yn un tabl.

Cam 1: Cymhwyso a llenwi'r fformiwla ganlynol

Cymhwyswch y fformiwla ganlynol i mewn i gell wag. Yna, llusgwch yr handlen llenwi i lawr i'r celloedd rydych chi am gymhwyso'r fformiwla hon

=INDEX($F$2:$F$8, MATCH($A2, $E$2:$E$8, 0))

Canlyniad:

Nawr, fe gewch dabl cyfun gyda'r golofn drefn yn ymuno â'r tabl cyntaf yn seiliedig ar ddata'r golofn allweddol.

Nodyn: Yn y fformiwla uchod:

  • A2 yw'r gwerth edrych rydych chi'n edrych amdano;
  • F2: F8 yw ystod o ddata yr ydych am ddychwelyd y gwerthoedd paru;
  • E2: E8 yw ystod edrych sy'n cynnwys y gwerth edrych.
 3.8.2 VLOOKUP i uno dau dabl yn seiliedig ar golofnau allwedd lluosog

Os oes gan y ddau dabl rydych chi am ymuno â nhw golofnau allweddol lluosog, i uno'r tablau yn seiliedig ar y colofnau cyffredin hyn, dilynwch y camau isod.

Y fformiwla generig yw:

=INDEX(lookup_table, MATCH(1, (lookup_value1=lookup_range1) * (lookup_value2=lookup_range2), 0), return_column_number)

Nodyn:

  • chwilio_bwrdd a yw'r amrediad data yn cynnwys y data chwilio a'r cofnodion paru;
  • chwilio_gwerth1 yw'r meini prawf cyntaf yr ydych yn chwilio amdanynt;
  • chwilio_ystod1 a yw'r rhestr ddata yn cynnwys y meini prawf cyntaf;
  • chwilio_gwerth2 yw'r ail faen prawf yr ydych yn chwilio amdano;
  • chwilio_ystod2 a yw'r rhestr ddata yn cynnwys yr ail faen prawf;
  • dychwelyd_colofn_rhif yn nodi rhif y golofn yn y tabl_looked yr ydych am ddychwelyd y gwerth cyfatebol.

Cam 1: Defnyddiwch y fformiwla ganlynol

Cymhwyswch y fformiwla isod i mewn i gell wag lle rydych chi am roi'r canlyniad, ac yna pwyswch Ctrl + Shift + Enter allweddi gyda'i gilydd i gael y gwerth cyfatebol cyntaf, gweler y screenshot:

=INDEX($E$2:$G$9, MATCH(1, ($A2=$E$2:$E$9) * ($B2=$F$2:$F$9), 0), 3)

Cam 2: Llenwch y fformiwla i gelloedd eraill

Yna, dewiswch y gell fformiwla gyntaf, a llusgwch y ddolen llenwi i gopïo'r fformiwla hon i gelloedd eraill yn ôl yr angen:

Awgrymiadau: Yn Excel 2016 neu fersiynau diweddarach, gallwch hefyd ddefnyddio'r Power Query nodwedd i uno dau neu fwy o dablau yn un yn seiliedig ar golofnau allweddol. Cliciwch i wybod y manylion gam wrth gam.

3.9 VLOOKUP yn cyfateb gwerthoedd ar draws sawl taflen waith

A oedd angen i chi erioed berfformio VLOOKUP ar draws sawl taflen waith yn Excel? Er enghraifft, os oes gennych dair taflen waith gydag ystodau data, a'ch bod am adfer gwerthoedd penodol yn seiliedig ar feini prawf o'r taflenni hyn, gallwch ddilyn y tiwtorial cam wrth gam Gwerthoedd VLOOKUP Ar Draws Taflenni Gwaith Lluosog i gyflawni'r dasg hon.


Mae gwerthoedd cyfatebol VLOOKUP yn cadw fformatio celloedd

Wrth edrych i fyny gwerthoedd cyfatebol, ni fydd y fformat cell gwreiddiol megis lliw ffont, lliw cefndir, fformat data, ac ati yn cael eu cadw. Er mwyn cadw'r gell neu fformatio data, bydd yr adran hon yn cyflwyno rhai triciau ar gyfer datrys y swyddi.

4.1 VLOOKUP cyfateb gwerth a chadw lliw cell, fformatio ffont

Fel y gwyddom i gyd, ni all y swyddogaeth VLOOKUP arferol ond adfer y gwerth cyfatebol o ystod ddata arall. Fodd bynnag, efallai y bydd yna achosion lle hoffech chi gael y gwerth cyfatebol ynghyd â fformatio'r gell, fel y lliw llenwi, lliw ffont, ac arddull ffont. Yn yr adran hon, byddwn yn trafod sut i adfer gwerthoedd cyfatebol wrth gadw fformatio ffynhonnell yn Excel.

Gwnewch y camau canlynol i edrych a dychwelyd ei werth cyfatebol ynghyd â fformatio celloedd:

Cam 1: Copïwch y cod 1 i'r Modiwl Cod Taflen

  1. Yn y daflen waith yn cynnwys y data yr ydych am i VLOOKUP, de-gliciwch y tab taflen a dewis Gweld y Cod o'r ddewislen cyd-destun. Gweler y screenshot:
  2. Yn yr agored Microsoft Visual Basic ar gyfer Ceisiadau ffenestr, copïwch y cod VBA isod i mewn i ffenestr y Cod.
  3. Cod VBA 1: VLOOKUP i gael fformatio celloedd ynghyd â gwerth chwilio
  4. Sub Worksheet_Change(ByVal Target As Range)
    'Updateby Extendoffice
        Dim I As Long
        Dim xKeys As Long
        Dim xDicStr As String
        On Error Resume Next
        Application.ScreenUpdating = False
        xKeys = UBound(xDic.Keys)
        If xKeys >= 0 Then
            For I = 0 To UBound(xDic.Keys)
                xDicStr = xDic.Items(I)
                If xDicStr <> "" Then
                    Range(xDic.Keys(I)).Interior.Color = _
                    Range(xDic.Items(I)).Interior.Color
                    Range(xDic.Keys(I)).Font.FontStyle = _
                    Range(xDic.Items(I)).Font.FontStyle
                    Range(xDic.Keys(I)).Font.Size = _
                    Range(xDic.Items(I)).Font.Size
                    Range(xDic.Keys(I)).Font.Color = _
                    Range(xDic.Items(I)).Font.Color
                    Range(xDic.Keys(I)).Font.Name = _
                    Range(xDic.Items(I)).Font.Name
                    Range(xDic.Keys(I)).Font.Underline = _
                    Range(xDic.Items(I)).Font.Underline
                Else
                    Range(xDic.Keys(I)).Interior.Color = xlNone
                End If
            Next
            Set xDic = Nothing
        End If
        Application.ScreenUpdating = True
    End Sub
    

Cam 2: Copïwch y cod 2 i ffenestr y Modiwl

  1. Dal yn y Microsoft Visual Basic ar gyfer Ceisiadau ffenestr, cliciwch Mewnosod > Modiwlau, ac yna copïwch y cod 2 VBA isod i mewn i ffenestr y Modiwl.
  2. Cod VBA 2: VLOOKUP i gael fformatio celloedd ynghyd â gwerth chwilio
  3. Public xDic As New Dictionary
    Function LookupKeepFormat (ByRef FndValue, ByRef LookupRng As Range, ByRef xCol As Long)
        Dim xFindCell As Range
        On Error Resume Next
        Set xFindCell = LookupRng.Find(FndValue, , xlValues, xlWhole)
        If xFindCell Is Nothing Then
            LookupKeepFormat = ""
            xDic.Add Application.Caller.Address, ""
        Else
            LookupKeepFormat = xFindCell.Offset(0, xCol - 1).Value
            xDic.Add Application.Caller.Address, xFindCell.Offset(0, xCol - 1).Address
        End If
    End Function
    

Cam 3: Dewiswch yr opsiwn ar gyfer VBAproject

  1. Ar ôl mewnosod y codau uchod, yna cliciwch offer > Cyfeiriadau yn y Microsoft Visual Basic ar gyfer Ceisiadau ffenestr. Yna gwiriwch y Amser Rhedeg Sgriptio Microsoft blwch ticio yn y Cyfeiriadau - VBAProject blwch deialog. Gweler sgrinluniau:
  2. Yna, cliciwch OK i gau'r blwch deialog, ac yna arbed a chau'r ffenestr cod.

Cam 4: Teipiwch y fformiwla ar gyfer cael y canlyniad

  1. Nawr, ewch yn ôl i'r daflen waith, cymhwyso'r fformiwla ganlynol. Ac yna, llusgwch yr handlen llenwi i lawr i gael yr holl ganlyniadau ynghyd â'u fformatio. Gweler y sgrinlun:
    =LookupKeepFormat(E2,$A$1:$C$10,3)

Nodyn: yn y fformiwla uchod:

  • E2 yw'r gwerth y byddwch yn edrych i fyny;
  • A1: C10 yw ystod y tabl;
  • 3 yw rhif colofn y tabl yr ydych am adfer y gwerth cyfatebol ohono.

4.2 Cadwch fformat y dyddiad o werth a ddychwelwyd gan VLOOKUP

Wrth ddefnyddio'r swyddogaeth VLOOKUP i chwilio a dychwelyd gwerth gyda fformat dyddiad, gall y canlyniad a ddychwelwyd arddangos fel rhif. I gadw'r fformat dyddiad yn y canlyniad a ddychwelwyd, dylech amgáu'r swyddogaeth VLOOKUP o fewn y swyddogaeth TEXT.

Cam 1: Cymhwyso a llenwi'r fformiwla ganlynol

Cymhwyswch y fformiwla isod i mewn i gell wag. Yna, llusgwch yr handlen llenwi i gopïo'r fformiwla hon i gelloedd eraill.

=TEXT(VLOOKUP(E2,$A$2:$C$9,3,FALSE),"mm/dd/yyyy")

Canlyniad:

Mae'r holl ddyddiadau cyfatebol wedi'u dychwelyd fel y dangosir y sgrinlun isod:

Nodyn: Yn y fformiwla uchod:

  • E2 yw'r gwerth edrych;
  • A2: C9 yw'r ystod chwilio;
  • 3 yw rhif y golofn yr ydych am i'r gwerth ddychwelyd;
  • Anghywir yn dynodi i gael cyfatebiaeth union;
  • mm / dd / bbbb yw'r fformat dyddiad rydych chi am ei gadw.

4.3 Dychwelyd sylw cell gan VLOOKUP

Ydych chi erioed wedi gorfod adfer y data cell cyfatebol a'i sylw cysylltiedig gan ddefnyddio VLOOKUP yn Excel, fel y dangosir yn y sgrinlun canlynol? Os felly, gall y Swyddogaeth a Ddiffiniwyd gan Ddefnyddiwr a ddarperir isod eich helpu i gyflawni'r dasg hon.

Cam 1: Copïwch y cod i fodiwl

  1. Dal i lawr y ALT + F11 allweddi i agor y Microsoft Visual Basic ar gyfer Ceisiadau ffenestr.
  2. Cliciwch Mewnosod > Modiwlau, yna copïwch a gludwch y cod canlynol yn Ffenestr y Modiwl.
    Cod VBA: Gwerth paru Vlookup a dychwelyd gyda sylw celloedd:
    Function VlookupComment(LookVal As Variant, FTable As Range, FColumn As Long, FType As Long) As Variant
    'Updateby Extendoffice
        Application.Volatile
        Dim xRet As Variant 'could be an error
        Dim xCell As Range
        xRet = Application.Match(LookVal, FTable.Columns(1), FType)
        If IsError(xRet) Then
            VlookupComment = "Not Found"
        Else
            Set xCell = FTable.Columns(FColumn).Cells(1)(xRet)
            VlookupComment = xCell.Value
            With Application.Caller
                If Not .Comment Is Nothing Then
                    .Comment.Delete
                End If
                If Not xCell.Comment Is Nothing Then
                    .AddComment xCell.Comment.Text
                End If
            End With
        End If
    End Function
  3. Yna arbed a chau'r ffenestr cod.

Cam 2: Teipiwch y fformiwla i gael y canlyniad

  1. Nawr, nodwch y fformiwla ganlynol, a llusgwch y ddolen llenwi i gopïo'r fformiwla hon i gelloedd eraill. Bydd yn dychwelyd y gwerthoedd cyfatebol a'r sylwadau ar yr un pryd, gweler y sgrinlun:
    =vlookupcomment(D2,$A$2:$B$9,2,FALSE)

Nodyn: Yn y fformiwla uchod:

  • D2 yw'r gwerth chwilio yr ydych am ddychwelyd ei werth cyfatebol;
  • A2: B9 yw'r tabl data rydych chi am ei ddefnyddio;
  • 2 yw rhif y golofn sy'n cynnwys y gwerth cyfatebol rydych am ei ddychwelyd;
  • Anghywir yn dynodi i gael union gyfatebiaeth.

4.4 Rhifau VLOOKUP wedi'u storio fel testun

Er enghraifft, mae gennyf ystod o ddata lle mae'r rhif adnabod yn y tabl gwreiddiol mewn fformat rhif a'r rhif adnabod yn y celloedd chwilio yn cael ei storio fel testun, efallai y byddwch yn dod ar draws gwall # N/A wrth ddefnyddio'r swyddogaeth VLOOKUP arferol. Yn yr achos hwn, i adalw'r wybodaeth gywir, gallwch lapio'r swyddogaethau TESTUN a GWERTH o fewn y swyddogaeth VLOOKUP.Isod yw'r fformiwla i gyflawni hyn:

Cam 1: Cymhwyso a llenwi'r fformiwla ganlynol

Cymhwyswch y fformiwla ganlynol i mewn i gell wag, ac yna llusgwch y ddolen lenwi i lawr i gopïo'r fformiwla hon.

=IFERROR(VLOOKUP(VALUE(D2),$A$2:$B$8,2,0),VLOOKUP(TEXT(D2,0),$A$2:$B$8,2,0))

Canlyniad:

Nawr, fe gewch y canlyniadau cywir fel y dangosir y sgrinlun isod:

Nodiadau:

  • Yn y fformiwla uchod:
    • D2 yw'r gwerth chwilio yr ydych am ddychwelyd ei werth cyfatebol;
    • A2: B8 yw'r tabl data rydych chi am ei ddefnyddio;
    • 2 yw rhif y golofn sy'n cynnwys y gwerth cyfatebol rydych am ei ddychwelyd;
    • 0 yn dynodi i gael union gyfatebiaeth.
  • Mae'r fformiwla hon hefyd yn gweithio'n dda os nad ydych chi'n siŵr ble mae gennych chi rifau a lle mae gennych chi destun.