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.
Tabl cynnwys:
1. Cyflwyno swyddogaeth VLOOKUP - Cystrawen a Dadleuon
2. Enghreifftiau sylfaenol VLOOKUP
- 2.1 Cydweddiad union a chyfateb yn fras VLOOKUP
- 2.2 VLOOKUP sy'n sensitif i achosion
- 2.3 VLOOKUP o'r dde i'r chwith
- 2.4 VLOOKUP yr ail, nfed neu werth cyfatebol olaf
- 2.5 VLOOKUP rhwng dau werth neu ddyddiad penodol
- 2.6 Defnyddio wildcards ar gyfer paru rhannol yn swyddogaeth VLOOKUP
- 2.7 Gwerthoedd VLOOKUP o daflen waith arall
- 2.8 o werthoedd VLOOKUP o lyfr gwaith arall
- 2.9 VLOOKUP a dychwelyd testun gwag neu benodol yn lle gwerth gwall 0 neu #N/A
- 3.1 Chwilio dwy ffordd gyda swyddogaeth VLOOKUP (VLOOKUP mewn rhes a cholofn)
- 3.2 Gwerth cyfatebol VLOOKUP yn seiliedig ar ddau faen prawf neu fwy
- 3.3 VLOOKUP i ddychwelyd gwerthoedd paru lluosog gydag un neu fwy o amodau
- 3.4 VLOOKUP i ddychwelyd rhes gyfan neu res gyfan o gell cyfatebol
- 3.5 Gwnewch swyddogaeth VLOOKUP lluosog (VLOOKUP nythu) yn Excel
- 3.6 VLOOKUP i wirio a yw gwerth yn bodoli yn seiliedig ar ddata rhestr mewn colofn arall
- 3.7 VLOOKUP a chrynhoi'r holl werthoedd cyfatebol mewn rhesi neu golofnau
- 3.8 VLOOKUP i uno dau dabl yn seiliedig ar un neu fwy o golofnau allweddol
- 3.9 VLOOKUP yn cyfateb gwerthoedd ar draws sawl taflen waith
4. Mae gwerthoedd cyfatebol VLOOKUP yn cadw fformatio celloedd
Dadlwythwch ffeiliau sampl VLOOKUP
Enghreifftiau sylfaenol o Vlookup | Enghreifftiau Vlookup Uwch | Vlookup cadw fformatio cell
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:
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.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:
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:
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.
- Cliciwch Kutools > LOOKUP Super > LOOKUP rhwng Dau Werth i alluogi'r nodwedd hon.
- Yna nodwch y gweithrediadau o'r blwch deialog yn seiliedig ar eich data.
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.)
- 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.
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.
- Cliciwch Kutools > LOOKUP Super > Edrych Aml-gyflwr i alluogi'r nodwedd hon.
- Yna nodwch y gweithrediadau o'r blwch deialog yn seiliedig ar eich data.
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:
- Mae swyddogaeth TEXTJOIN ar gael yn Excel 2019, Excel 365 a fersiynau diweddarach yn unig.
- Os ydych chi'n defnyddio Excel 2016 a fersiynau cynharach, defnyddiwch Swyddogaeth Diffiniedig Defnyddiwr yr erthygl isod:
- Vlookup I Dychwelyd Gwerthoedd Lluosog Mewn Un Cell Yn Excel
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:
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)),"")
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.
Y fformiwla generig ar gyfer swyddogaeth VLOOKUP nythu yw:
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.
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.
- Cliciwch Kutools > LOOKUP Super > LOOKUP a Swm i alluogi'r nodwedd hon.
- Yna nodwch y gweithrediadau o'r blwch deialog yn seiliedig ar eich angen.
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:
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:
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
- 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:
- Yn yr agored Microsoft Visual Basic ar gyfer Ceisiadau ffenestr, copïwch y cod VBA isod i mewn i ffenestr y Cod.
- Cod VBA 1: VLOOKUP i gael fformatio celloedd ynghyd â gwerth chwilio
-
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
- 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.
- Cod VBA 2: VLOOKUP i gael fformatio celloedd ynghyd â gwerth chwilio
-
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
- 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:
- 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
- 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
- Dal i lawr y ALT + F11 allweddi i agor y Microsoft Visual Basic ar gyfer Ceisiadau ffenestr.
- 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
- Yna arbed a chau'r ffenestr cod.
Cam 2: Teipiwch y fformiwla i gael y canlyniad
- 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.
Offer Cynhyrchiant Swyddfa Gorau
Supercharge Eich Sgiliau Excel gyda Kutools ar gyfer Excel, a Phrofiad Effeithlonrwydd Fel Erioed Erioed. Kutools ar gyfer Excel Yn Cynnig Dros 300 o Nodweddion Uwch i Hybu Cynhyrchiant ac Arbed Amser. Cliciwch Yma i Gael Y Nodwedd Sydd Ei Angen Y Mwyaf...
Mae Office Tab yn dod â rhyngwyneb Tabbed i Office, ac yn Gwneud Eich Gwaith yn Haws o lawer
- Galluogi golygu a darllen tabbed yn Word, Excel, PowerPoint, Cyhoeddwr, Mynediad, Visio a Phrosiect.
- Agor a chreu dogfennau lluosog mewn tabiau newydd o'r un ffenestr, yn hytrach nag mewn ffenestri newydd.
- Yn cynyddu eich cynhyrchiant 50%, ac yn lleihau cannoedd o gliciau llygoden i chi bob dydd!
Tabl cynnwys
- 1. Cyflwyno swyddogaeth VLOOKUP
- 2. Enghreifftiau sylfaenol VLOOKUP
- 2.1 Vlookup union a bras
- Cydweddiad union
- Gêm fras
- 2.2 Vlookup sy'n sensitif i achosion
- 2.3 Vlookup o'r dde i'r chwith
- 2.4 Vlookup yr ail, yr nfed neu'r olaf gwerth cyfatebol
- Yr ail neu'r nfed gwerth cyfatebol
- Y gwerth cyfatebol olaf
- 2.5 Vlookup rhwng dau werth
- Trwy ddefnyddio fformiwla
- Trwy ddefnyddio nodwedd ddefnyddiol - Kutools
- 2.6 Gêm rhannol Vlookup
- 2.7 Vlookup o daflen waith arall
- 2.8 Vlookup o lyfr gwaith arall
- 2.9 Trwsiwch werth gwall 0 neu # N/A yn Vlookup
- 3. Enghreifftiau VLOOKUP uwch
- 3.1 Chwilio dwy ffordd
- 3.2 Vlookup yn seiliedig ar fwy o feini prawf
- Trwy ddefnyddio fformwlâu
- Trwy ddefnyddio nodwedd smart - Kutools
- 3.3 Vlookup gwerthoedd paru lluosog
- Gwerthoedd dychwelyd yn llorweddol
- Gwerthoedd dychwelyd yn fertigol
- Dychwelyd gwerthoedd i un gell
- 3.4 Vlookup rhes gyfan
- 3.5 Nythog Vlookup
- 3.6 Gwiriwch a oes gwerth yn bodoli
- 3.7 Vlookup a swm
- Mewn rhesi
- Mewn colofnau
- Gyda nodwedd bwerus - Kutools
- Mewn rhesi a cholofnau
- 3.8 Vlookup i uno dau dabl
- Gan un golofn allweddol
- Gan sawl colofn allweddol
- 3.9 Vlookup ar draws taflenni gwaith lluosog
- 4. VLOOKUP a chadw fformatio cell
- 4.1 Cadw lliw a fformatio ffontiau
- 4.2 Cadwch y fformat dyddiad
- 4.3 Cadw sylw cell
- 4.4 Rhifau wedi'u storio fel testun
- Yr Offer Cynhyrchedd Swyddfa Gorau
- sylwadau