Note: The other languages of the website are Google-translated. Back to English
Mewngofnodi  \/ 
x
or
x
Cofrestru  \/ 
x

or

Swyddogaeth VLOOKUP gyda rhai enghreifftiau sylfaenol ac uwch yn Excel

Yn Excel, mae swyddogaeth VLOOKUP yn swyddogaeth bwerus i'r rhan fwyaf o ddefnyddwyr Excel, a ddefnyddir i chwilio am werth yng ngwaelod chwith yr ystod ddata, a dychwelyd gwerth paru yn yr un rhes o golofn a nodwyd gennych fel isod y llun a ddangosir. . Mae'r tiwtorial hwn yn siarad am sut i ddefnyddio swyddogaeth VLOOKUP gyda rhai enghreifftiau sylfaenol ac uwch yn Excel.

Tabl cynnwys:

1. Cyflwyno swyddogaeth VLOOKUP - Cystrawen a Dadleuon

2. Enghreifftiau sylfaenol VLOOKUP

3. Enghreifftiau VLOOKUP uwch

4. Mae gwerthoedd cyfatebol VLOOKUP yn cadw fformatio celloedd

5. Dadlwythwch ffeiliau sampl VLOOKUP


Cyflwyno swyddogaeth VLOOKUP - Cystrawen a Dadleuon

Cystrawen swyddogaeth VLOOKUP:

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

Dadleuon:

Lookup_value: Y gwerth rydych chi am ei chwilio. Rhaid iddo fod yng ngholofn gyntaf yr ystod table_array.

Table_array: Yr ystod ddata neu'r tabl lle mae'r golofn gwerth edrych a'r golofn gwerth canlyniad yn lleoli.

Col_index_num: Nifer y golofn y dychwelir y gwerth cyfatebol ohoni. Mae'n dechrau gydag 1 o'r golofn chwith yn yr arae bwrdd.

Range_lookup: Gwerth rhesymegol sy'n penderfynu a fydd y swyddogaeth VLOOKUP hon yn dychwelyd cyfatebiaeth union neu gyfatebiaeth fras.

  • Gêm fras - 1 / GWIR: Os na ddarganfyddir cyfatebiaeth union, mae'r fformiwla'n chwilio am yr ornest agosaf - y gwerth mwyaf sy'n llai na'r gwerth edrych. Yn yr achos hwn, dylech ddidoli'r golofn edrych yn nhrefn esgynnol.
    = VLOOKUP (lookup_value, table_array, col_index, GWIR)
    = VLOOKUP (lookup_value, table_array, col_index, 1)
  • Cydweddiad union - 0 / ANWIR: 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.
    = VLOOKUP (lookup_value, table_array, col_index, GAU)
    = VLOOKUP (lookup_value, table_array, col_index, 0)

Nodiadau:

  • 1. Mae'r swyddogaeth Vlookup yn edrych am werth o'r chwith i'r dde yn unig.
  • 2. Os oes sawl gwerth paru yn seiliedig ar y gwerth edrych i fyny, dim ond y cyfateb cyntaf a ddychwelir trwy ddefnyddio'r swyddogaeth Vlookup.
  • 3. Bydd yn dychwelyd y gwerth gwall # Amherthnasol os na ellir dod o hyd i'r gwerth edrych i fyny.

Enghreifftiau sylfaenol VLOOKUP

1. Gwnewch Vlookup sy'n cyfateb yn union a Vlookup sy'n cyfateb yn fras

Gwnewch Vlookup yn cyfateb yn union yn Excel

Fel rheol, os ydych chi'n chwilio am union gyfatebiaeth â swyddogaeth Vlookup, 'ch jyst angen i chi ddefnyddio GAU yn y ddadl ddiwethaf.

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

1. Rhowch y fformiwla isod mewn cell wag lle rydych chi am gael y canlyniad:

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

2. Ac yna, llusgwch y handlen llenwi i lawr i'r celloedd rydych chi am lenwi'r fformiwla hon, a byddwch chi'n cael y canlyniadau yn ôl yr angen. Gweler y screenshot:

Nodiadau:

  • 1. Yn y fformiwla uchod, F2 yw'r gwerth yr ydych am ddychwelyd ei werth paru, A2: D7 yw'r arae bwrdd, y rhif 3 yw'r rhif colofn y dychwelir eich gwerth cyfatebol ohono a'r FALSE yn cyfeirio at yr union gyfatebiaeth.
  • 2. Os na cheir gwerth eich meini prawf yn yr ystod ddata, bydd gwerth gwall # Amherthnasol yn cael ei arddangos.

Gwnewch frasamcan cyfatebol Vlookup yn Excel

Mae'r cyfatebiaeth fras yn ddefnyddiol ar gyfer chwilio gwerthoedd rhwng ystodau data. Os na ddarganfyddir yr union gyfatebiaeth, bydd y Vlookup bras yn dychwelyd y gwerth mwyaf sy'n llai na'r gwerth edrych.

Er enghraifft, os oes gennych y data amrediad canlynol, nid yw'r gorchmynion penodedig yn y golofn Gorchmynion, sut i gael ei Gostyngiad agosaf yng ngholofn B?

1. Rhowch y fformiwla ganlynol mewn cell lle rydych chi am roi'r canlyniad:

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

2. Yna, llusgwch y handlen llenwi i lawr i'r celloedd i gymhwyso'r fformiwla hon, a byddwch yn cael y cyfatebiadau bras yn seiliedig ar y gwerthoedd a roddir, gweler y screenshot:

Nodiadau:

  • 1. Yn y fformiwla uchod, D2 yw'r gwerth yr ydych am ddychwelyd ei wybodaeth gymharol, A2: B9 yw'r ystod ddata, y rhif 2 yn nodi rhif y golofn y dychwelir eich gwerth cyfatebol a'r TRUE yn cyfeirio at yr cyfatebiaeth fras.
  • 2. Bydd y cydweddiad bras yn dychwelyd y gwerth mwyaf sy'n llai na'ch gwerth edrych penodol.
  • 3. Er mwyn defnyddio'r swyddogaeth Vlookup i gael gwerth cyfatebol bras, rhaid i chi ddidoli colofn chwith yr ystod ddata yn nhrefn esgynnol, fel arall bydd yn dychwelyd canlyniad anghywir.

2. Gwnewch Vlookup achos-sensitif yn Excel

Yn ddiofyn, mae swyddogaeth Vlookup yn perfformio chwiliad ansensitif achos sy'n golygu ei fod yn trin nodau llythrennau bach a chymeriadau fel yr un peth. Rywbryd, efallai y bydd angen i chi wneud chwiliad achos-sensitif yn Excel, gall y swyddogaethau Mynegai, Cydweddu ac Uniongyrchol neu swyddogaethau Edrych ac Unioni wneud ffafr i chi.

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.

Fformiwla 1: Defnyddio swyddogaethau EXACT, MYNEGAI, MATCH

1. Rhowch neu copïwch y fformiwla arae isod i mewn i gell wag lle rydych chi am gael y canlyniad:

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

2. Yna, pwyswch Ctrl + Shift + Enter allweddi ar yr un pryd i gael y canlyniad cyntaf, ac yna dewiswch y gell fformiwla, llusgwch y ddolen llenwi i lawr i'r celloedd rydych chi am lenwi'r fformiwla hon, yna fe gewch chi'r canlyniadau cywir sydd eu hangen arnoch chi. Gweler y screenshot:

Nodiadau:

  • 1. Yn y fformiwla uchod, A2: A10 yw'r golofn sy'n cynnwys y gwerthoedd penodol rydych chi am edrych arnyn nhw, F2 yw'r gwerth edrych, C2: C10 yw'r golofn lle dychwelir y canlyniad.
  • 2. Os canfyddir sawl gêm, bydd y fformiwla hon bob amser yn dychwelyd y gêm gyntaf.

Fformiwla 2: Defnyddio swyddogaethau Edrych ac Uniongyrchol

1. Defnyddiwch y fformiwla isod mewn cell wag lle rydych chi am gael y canlyniad:

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

2. Yna, llusgwch y handlen llenwi i lawr i'r celloedd rydych chi am gopïo'r fformiwla hon, a byddwch chi'n cael y gwerthoedd wedi'u cydweddu ag achos-sensitif fel y dangosir isod y screenshot:

Nodiadau:

  • 1. Yn y fformiwla uchod, A2: A10 yw'r golofn sy'n cynnwys y gwerthoedd penodol rydych chi am edrych arnyn nhw, F2 yw'r gwerth edrych, C2: C10 yw'r golofn lle dychwelir y canlyniad.
  • 2. Os canfyddir sawl gêm, bydd y fformiwla hon bob amser yn dychwelyd yr ornest olaf.

3. Gwerthoedd Vlookup o'r dde i'r chwith yn Excel

Mae swyddogaeth Vlookup bob amser yn edrych i fyny gwerth yng ngholofn chwith amrediad data ac yn dychwelyd y gwerth cyfatebol o golofn i'r dde. Os ydych chi am wneud Vlookup gwrthdroi sy'n golygu edrych ar werth penodol yn y dde a dychwelyd ei werth cyfatebol yn y golofn chwith fel y dangosir isod y llun:

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


4. Vlookup yr ail, nfed neu'r gwerth paru olaf yn Excel

Fel rheol, os oes gwerthoedd paru lluosog i'w canfod wrth ddefnyddio'r swyddogaeth Vlookup, dim ond y cofnod cyfatebol cyntaf a ddychwelir. Yn yr adran hon, byddaf yn siarad am sut i gael yr ail, y nawfed neu'r gwerth paru olaf â swyddogaeth Vlookup.

Vlookup a dychwelyd yr ail neu'r nawfed gwerth paru

Tybiwch fod gennych restr o enwau yng ngholofn A, y cwrs hyfforddi a brynwyd ganddynt yng ngholofn B, ac yn awr, rydych yn edrych i ddod o hyd i'r 2il neu'r nawfed cwrs hyfforddi a brynwyd gan y cwsmer penodol. Gweler y screenshot:

1. I gael yr ail neu'r nawfed gwerth paru yn seiliedig ar y meini prawf a roddir, cymhwyswch y fformiwla arae ganlynol mewn cell wag:

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

2. Yna pwyswch Ctrl + Shift + Enter allweddi gyda'i gilydd i gael y canlyniad cyntaf, ac yna dewiswch y gell fformiwla, llusgwch y handlen llenwi i lawr i'r celloedd rydych chi am lenwi'r fformiwla hon, ac mae'r holl werthoedd cyfatebol sy'n seiliedig ar yr enwau a roddwyd wedi'u harddangos ar unwaith, gweler y screenshot:

Nodyn:

  • Yn y fformiwla hon, A2: A14 yw'r ystod gyda'r holl werthoedd ar gyfer edrych, B2: B14 yw ystod y gwerthoedd paru rydych chi am ddychwelyd ohonynt, E2 yw'r gwerth edrych, a'r rhif olaf 2 yn nodi'r ail werth cyfatebol rydych chi am ei gael, os ydych chi am ddychwelyd y trydydd gwerth paru, mae angen i chi ei newid i 3 yn ôl yr angen.

Vlookup a dychwelyd y gwerth paru olaf

Os ydych chi am wylio a dychwelyd y gwerth paru olaf fel y nodir isod, dangosir hyn Vlookup A Dychwelwch y Gwerth Paru Olaf gall tiwtorial eich helpu i gael y gwerth paru olaf mewn manylion.


5. Gwerthoedd paru Vlookup rhwng dau werth neu ddyddiad penodol

Weithiau, efallai yr hoffech edrych ar werthoedd rhwng dau werth neu ddyddiad a dychwelyd y canlyniadau cyfatebol fel isod y llun a ddangosir, yn yr achos hwn, gallwch ddefnyddio'r swyddogaeth LOOKUP a thabl wedi'i ddidoli.

Gwerthoedd paru Vlookup rhwng dau werth neu ddyddiad penodol â fformiwla

1. Yn gyntaf, dylai eich tabl gwreiddiol fod yn ystod ddata wedi'i didoli. Ac yna, copïwch neu nodwch y fformiwla ganlynol mewn cell wag:

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

2. Yna, llusgwch yr handlen llenwi i lenwi'r fformiwla hon i gelloedd eraill sydd eu hangen arnoch, ac yn awr, fe gewch yr holl gofnodion wedi'u paru yn seiliedig ar y gwerth a roddir, gweler y screenshot:

Nodiadau:

  • 1. Yn y fformiwla uchod, A2: A6 yw'r ystod o werthoedd llai a B2: B6 yw'r ystod o rifau mwy yn eich ystod ddata, y E2 yw'r gwerth penodol yr ydych am gael ei werth cyfatebol, C2: C6 yw'r data colofn rydych chi am dynnu ohono.
  • 2. Gellir defnyddio'r fformiwla hon hefyd ar gyfer tynnu gwerthoedd cyfatebol rhwng dau ddyddiad fel y dangosir isod y screenshot:

Gwerthoedd paru Vlookup rhwng dau werth neu ddyddiad penodol gyda nodwedd ddefnyddiol

Os ydych chi'n boenus gyda'r fformiwla uchod, yma, byddaf yn cyflwyno teclyn hawdd - Kutools ar gyfer Excel, Gyda'i LOOKUP rhwng Dau Werth nodwedd, gallwch ddychwelyd yr eitem gyfatebol yn seiliedig ar y gwerth neu'r dyddiad penodol rhwng dau werth neu ddyddiad heb gofio unrhyw fformiwla.   Cliciwch i lawrlwytho Kutools ar gyfer Excel nawr!


6. Defnyddio cardiau gwyllt ar gyfer gemau rhannol yn swyddogaeth Vlookup

Yn Excel, gellir defnyddio'r cardiau gwyllt o fewn swyddogaeth Vlookup, sy'n gwneud paru rhannol ar werth edrych. Er enghraifft, gallwch ddefnyddio Vlookup i ddychwelyd gwerth cyfatebol o dabl yn seiliedig ar ran o werth edrych.

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?

1. Nid yw'r swyddogaeth Vlookup arferol yn gweithio'n gywir, mae angen i chi ymuno â'r testun neu'r cyfeirnod cell â cherdyn gwyllt, copïwch neu nodwch y fformiwla ganlynol mewn cell wag:

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

2. Yna, llusgwch yr handlen llenwi i lenwi'r fformiwla hon i gelloedd eraill sydd eu hangen arnoch, ac mae'r holl sgoriau wedi'u paru wedi'u dychwelyd fel y nodir isod:

Nodiadau:

  • 1. Yn y fformiwla uchod, E2 & ”*” yw'r gwerth edrych, y gwerth i mewn E2 a'r * cerdyn gwyllt (mae “*” yn dynodi unrhyw un cymeriad neu unrhyw gymeriadau), A2: C11 yw'r ystod edrych, y rhif 3 y golofn sy'n cynnwys y gwerth i'w ddychwelyd.
  • 2. Vlookup wrth ddefnyddio cardiau gwyllt, rhaid i chi osod yr union fodd paru â GAU neu 0 ar gyfer y ddadl olaf yn swyddogaeth Vlookup.

Awgrym:

1. Darganfyddwch a dychwelwch y gwerthoedd paru sy'n gorffen gyda gwerth penodol, defnyddiwch y fformiwla hon: =VLOOKUP("*"&E2, $A$2:$C$11, 3, FALSE)

2. Er mwyn edrych a dychwelyd y gwerth cyfatebol yn seiliedig ar ran o'r llinyn testun, p'un a yw'r testun penodedig o flaen, y tu ôl neu yng nghanol y llinyn testun, mae angen i chi ymuno â dau * nod o amgylch cyfeirnod y gell neu'r testun. Gwnewch y fformiwla hon: =VLOOKUP("*"&D2&"*", $A$2:$B$11, 2, FALSE)


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 edrych ar ddata 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:

1. Rhowch neu copïwch y fformiwla isod i mewn i gell wag lle rydych chi am gael yr eitemau sy'n cyfateb:

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

2. Yna, llusgwch y ddolen llenwi i lawr i'r celloedd rydych chi am gymhwyso'r fformiwla hon, a byddwch chi'n cael y canlyniadau cyfatebol yn ôl yr angen, gweler y screenshot:

Nodyn: Yn y fformiwla uchod:

  • A2 yn cynrychioli'r gwerth edrych;
  • Taflen Ddata yw enw'r daflen waith rydych chi am edrych ar ddata ohoni, (Os yw enw'r ddalen yn cynnwys gofod neu bwyntiau atalnodi, dylech amgáu dyfynbrisiau sengl o amgylch enw'r ddalen, fel arall, gallwch chi ddefnyddio'r enw dalen yn uniongyrchol fel = VLOOKUP (A2, Taflen ddata! $ A $ 2: $ C $ 15,3,0));
  • A2: C15 yw'r ystod o ddata yn y Daflen Ddata lle'r ydym yn chwilio am ddata;
  • nifer 3 yw rhif y golofn sy'n cynnwys data cyfatebol rydych chi am ddychwelyd ohono.

8. Gwerthoedd Vlookup o lyfr gwaith arall

Bydd yr adran hon yn siarad am edrych ac yn dychwelyd y gwerthoedd paru o lyfr gwaith gwahanol trwy ddefnyddio'r swyddogaeth Vlookup.

Er enghraifft, mae'r llyfr gwaith cyntaf yn cynnwys y rhestrau cynnyrch a chostau, nawr, rydych chi am echdynnu'r gost gyfatebol yn yr ail lyfr gwaith yn seiliedig ar eitem y cynnyrch fel y dangosir isod y screenshot.

1. I adfer y gost gymharol o lyfr gwaith arall, yn gyntaf, agorwch y ddau lyfr gwaith rydych chi am eu defnyddio, yna cymhwyswch y fformiwla ganlynol mewn cell lle rydych chi am roi'r canlyniad:

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

2. Yna, llusgwch a chopïwch y fformiwla hon i gelloedd eraill sydd eu hangen arnoch, gweler y screenshot:

Nodiadau:

  • 1. Yn y fformiwla uchod:
    B2 yn cynrychioli'r gwerth edrych;
    [Rhestr cynnyrch.xlsx] Taflen1 yw enw'r llyfr gwaith a'r daflen waith yr ydych am edrych ar ddata ohonynt, (Mae'r cyfeiriad at y llyfr gwaith wedi'i amgáu mewn cromfachau sgwâr, ac mae'r ddalen llyfr gwaith + gyfan wedi'i hamgáu mewn dyfyniadau sengl);
    A2: B6 yw'r ystod o ddata yn nhaflen waith llyfr gwaith arall lle'r ydym yn chwilio am ddata;
    nifer 2 yw rhif y golofn sy'n cynnwys data cyfatebol rydych chi am ddychwelyd ohono.
  • 2. Os yw'r llyfr gwaith edrych ar gau, bydd y llwybr ffeil llawn ar gyfer y llyfr gwaith edrych yn cael ei ddangos yn y fformiwla fel y dangosir y llun a ganlyn:

9. Vlookup a dychwelyd testun gwag neu benodol yn lle gwerth gwall 0 neu # Amherthnasol

Fel rheol, pan ddefnyddiwch y swyddogaeth vlookup i ddychwelyd y gwerth cyfatebol, os yw'ch cell sy'n cyfateb yn wag, bydd yn dychwelyd 0, ac os na ddarganfyddir eich gwerth paru, fe gewch wall gwall # Amherthnasol fel y dangosir isod y llun. Yn lle arddangos y gwerth 0 neu # Amherthnasol gyda chell wag neu werth arall rydych chi'n ei hoffi, hwn Vlookup I Ddychwelyd Gwerth Gwag neu Benodol yn lle 0 Neu Amherthnasol efallai y bydd tiwtorial yn ffafrio cam wrth gam i chi.


Enghreifftiau VLOOKUP uwch

1. Edrych dwy ffordd gyda swyddogaeth Vlookup (Vlookup yn rhes a cholofn)

Weithiau, efallai y bydd angen i chi wneud edrychiad dau ddimensiwn, sy'n golygu i Vlookup yn y rhes a'r golofn ar yr un pryd. Gadewch i ni ddweud, os oes gennych yr ystod ddata ganlynol, ac yn awr, efallai y bydd angen i chi gael y gwerth ar gyfer cynnyrch penodol mewn chwarter penodol. Bydd yr adran hon yn cyflwyno rhywfaint o fformiwla ar gyfer delio â'r swydd hon yn Excel.

Fformiwla 1: Defnyddio swyddogaethau VLOOKUP a MATCH

Yn Excel, gallwch ddefnyddio cyfuniad o swyddogaethau VLOOKUP a MATCH i edrych ar ddwy ffordd, cymhwyswch y fformiwla ganlynol mewn cell wag, ac yna pwyswch Rhowch allwedd i gael y canlyniad.

=VLOOKUP(H1, $A$2:$E$6, MATCH(H2, $A$1:$E$1, 0), FALSE)

Nodyn: Yn y fformiwla uchod:

  • H1: y gwerth edrych yn y golofn yr ydych am gael y gwerth cyfatebol yn seiliedig arni;
  • A2: E6: yr ystod ddata gan gynnwys penawdau rhes;
  • H2: y gwerth edrych yn y rhes yr ydych am gael y gwerth cyfatebol yn seiliedig arno;
  • A1: E1: celloedd penawdau colofnau.

Fformiwla 2: Defnyddio swyddogaethau MYNEGAI a MATCH

Dyma fformiwla arall hefyd a all eich helpu i berfformio edrychiad dau ddimensiwn, defnyddiwch y fformiwla isod, ac yna pwyswch Rhowch allwedd i gael y canlyniad sydd ei angen arnoch.

=INDEX($B$2:$E$6, MATCH(H1, $A$2:$A$6, 0), MATCH(H2, $B$1:$E$1, 0))

Nodyn: Yn y fformiwla uchod:

  • B2: E6: yr ystod ddata i ddychwelyd yr eitem wedi'i chydweddu;
  • H1: y gwerth edrych yn y golofn yr ydych am gael y gwerth cyfatebol yn seiliedig arni;
  • A2: A6: mae'r penawdau rhes yn cynnwys y cynnyrch rydych chi am edrych amdano.
  • H2: y gwerth edrych yn y rhes yr ydych am gael y gwerth cyfatebol yn seiliedig arno;
  • B1: E1: mae penawdau'r colofnau'n cynnwys y chwarter rydych chi am edrych amdano.

2. Gwerth paru Vlookup yn seiliedig ar ddau faen prawf neu fwy

Mae'n hawdd ichi edrych ar y gwerth paru yn seiliedig ar un maen prawf, ond os oes gennych ddau faen prawf neu fwy, beth allwch chi ei wneud? Gall y swyddogaethau LOOKUP neu MATCH ac MYNEGAI yn Excel eich helpu chi 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.

Fformiwla 1: Defnyddio swyddogaeth LOOKUP

Defnyddiwch y fformiwla isod mewn cell lle rydych chi am gael y canlyniad, ac yna pwyswch Enter key, gweler y screenshot:

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

Nodiadau:

  • 1. Yn y fformiwla uchod:
    A2: A12 = G1: modd i chwilio meini prawf G1 yn ystod A2: A12;
    B2: B12 = G2: modd i chwilio meini prawf G2 yn ystod B2: B12;
    D2: D12: yr ystod yr ydych am ddychwelyd y gwerth cyfatebol.
  • 2. Os oes gennych fwy na dau faen prawf, dim ond ymuno â'r meini prawf eraill yn y fformiwla, fel: =LOOKUP(2,1/($A$2:$A$12=G1)/($B$2:$B$12=G2)/($C$2:$C$12=G3),($D$2:$D$12))

Fformiwla 2: Defnyddio swyddogaethau MYNEGAI A MATCH

Gellir defnyddio'r cyfuniad o swyddogaeth Mynegai a Match hefyd i ddychwelyd y gwerth cyfatebol yn seiliedig ar feini prawf lluosog. Copïwch neu nodwch y fformiwla ganlynol:

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

Yna, pwyswch Ctrl + Shift + Rhowch allweddi at ei gilydd i gael y gwerth cymharol yn ôl yr angen. Gweler y screenshot:

Nodiadau:

  • 1. Yn y fformiwla uchod:
    A2: A12 = G1: modd i chwilio meini prawf G1 yn ystod A2: A12;
    B2: B12 = G2: modd i chwilio meini prawf G2 yn ystod B2: B12;
    D2: D12: yr ystod yr ydych am ddychwelyd y gwerth cyfatebol.
  • 2. Os oes gennych fwy na dau faen prawf, dim ond ymuno â'r meini prawf newydd yn y fformiwla, fel: =INDEX($D$2:$D$12,MATCH(1,($A$2:$A$12=G1)*($B$2:$B$12=G2)*($C$2:$C$12=G3),0))

3. Vlookup i ddychwelyd gwerthoedd paru lluosog gydag un neu fwy o amodau

Yn Excel, mae'r swyddogaeth Vlookup yn chwilio am werth a dim ond yn dychwelyd os yw'r gwerthoedd cyfatebol cyntaf yn cael eu darganfod. Weithiau, efallai yr hoffech chi ddychwelyd yr holl werthoedd cyfatebol yn olynol, mewn colofn neu mewn un gell. Bydd yr adran hon yn siarad am sut i ddychwelyd y gwerthoedd paru lluosog gydag un neu fwy o amodau mewn llyfr gwaith.

Gwyliwch yr holl werthoedd paru yn seiliedig ar un neu fwy o amodau yn llorweddol

Gwyliwch yr holl werthoedd paru yn seiliedig ar un amod yn llorweddol:

I Vlookup a dychwelyd yr holl werthoedd paru yn seiliedig ar un gwerth penodol yn llorweddol, y fformiwla generig yw:

=IFERROR(INDEX(return_range, SMALL(IF(lookup_value = lookup_range, ROW(return_range) - m, ""), COLUMN() - n)), "")
Nodyn: m yw rhif rhes y gell gyntaf yn yr ystod dychwelyd minws 1.
      n yw rhif colofn y gell fformiwla gyntaf minws 1.

1. Defnyddiwch y fformiwla isod mewn cell wag, yna pwyswch Ctrl + Shift + Enter allweddi gyda'i gilydd i gael y gwerth cyfatebol cyntaf, gweler y screenshot:

=IFERROR(INDEX($C$2:$C$20, SMALL(IF($F1=$A$2:$A$20, ROW($C$2:$C$20)-1,""), COLUMN()-5)),"")

2. Ac yna, dewiswch y gell fformiwla gyntaf, a llusgwch y ddolen llenwi i'r celloedd cywir nes bod cell wag wedi'i harddangos, a bod yr holl eitemau cyfatebol wedi'u tynnu, gweler y screenshot:

Awgrym:

Os oes gwerthoedd cyfatebol dyblyg yn y rhestr a ddychwelwyd, i anwybyddu'r dyblygu, defnyddiwch y fformwlâu hyn, yna pwyswch Rhowch i gael y canlyniad cyntaf: =IFERROR(INDEX($C$2:$C$20,MATCH($F1,$A$2:$A$20,0)),"")

Ewch ymlaen i nodi'r fformiwla hon: =IFERROR(INDEX($C$2:$C$20,MATCH(1,($F1=$A$2:$A$20)*ISNA(MATCH($C$2:$C$20,$F2:F2,0)),0)),"") i mewn i gell wrth ymyl y canlyniad cyntaf, ac yna pwyswch Ctrl + Shift + Enter allweddi gyda'i gilydd i gael yr ail ganlyniad, yna llusgwch y fformiwla hon i gelloedd cywir i gael yr holl werthoedd cyfatebol eraill nes bod celloedd gwag yn arddangos, gweler y screenshot:


Gwyliwch yr holl werthoedd paru yn seiliedig ar ddau neu fwy o amodau yn llorweddol:

I Vlookup a dychwelyd yr holl werthoedd paru yn seiliedig ar werthoedd mwy penodol yn llorweddol, y fformiwla generig yw:

=IFERROR(INDEX(return_range, SMALL(IF(1 = ((--(lookup_value1=lookup_range1)) * (--(lookup_value2 = lookup_range2))), ROW(return_range) - m, ""), COLUMN() - n)),"")
Nodyn: m yw rhif rhes y gell gyntaf yn yr ystod dychwelyd minws 1.
      n yw rhif colofn y gell fformiwla gyntaf minws 1.

1. Rhowch y fformiwla ganlynol mewn cell wag lle rydych chi am allbwn y canlyniad:

=IFERROR(INDEX($C$2:$C$20, SMALL(IF(1=((--($F1=$A$2:$A$20)) * (--($F2=$B$2:$B$20))), ROW($C$2:$C$20)-1,""), COLUMN()-5)),"")

2. Yna, dewiswch y gell fformiwla a llusgwch y ddolen llenwi i'r celloedd cywir nes bod arddangosfeydd celloedd gwag, a bod yr holl werthoedd cyfatebol yn seiliedig ar y meini prawf penodol wedi'u dychwelyd, gweler y screenshot:

Nodyn: I gael mwy o feini prawf, does ond angen i chi ymuno â'r lookup_value ac lookup_range yn y fformiwla, fel: =IFERROR(INDEX(return_range, SMALL(IF(1 = ((--(lookup_value1=lookup_range1)) * (--(lookup_value2 = lookup_range2) * (--(lookup_value3 =lookup_range3)))), ROW(return_range) - m, ""), COLUMN() - n)),"").


Gwyliwch yr holl werthoedd paru yn seiliedig ar un neu fwy o amodau yn fertigol

Gwyliwch yr holl werthoedd paru yn seiliedig ar un cyflwr yn fertigol:

I Vlookup a dychwelyd yr holl werthoedd paru yn seiliedig ar un gwerth penodol yn fertigol, y fformiwla generig yw:

=IFERROR(INDEX(return_range, SMALL(IF(lookup_value = lookup_range, ROW(return_range )- m ,""), ROW() - n )),"")
Nodyn: m yw rhif rhes y gell gyntaf yn yr ystod dychwelyd minws 1.
      n yw rhif rhes y gell fformiwla gyntaf minws 1.

1. Copïwch neu deipiwch y fformiwla ganlynol i mewn i gell lle rydych chi am gael y canlyniad, ac yna pwyswch Ctrl + Shift + Enter allweddi gyda'i gilydd i gael y gwerth cyfatebol cyntaf, gweler y screenshot:

=IFERROR(INDEX($C$2:$C$20, SMALL(IF(E$2=$A$2:$A$20, ROW($C$2:$C$20)-1,""), ROW()-1)),"")

2. Yna, dewiswch y gell fformiwla gyntaf, a llusgwch y ddolen llenwi i lawr i gelloedd eraill nes bod cell wag wedi'i harddangos, a bod yr holl eitemau cyfatebol wedi'u rhestru mewn colofn, gweler y screenshot:

Awgrym:

I anwybyddu'r dyblygu yn y gwerthoedd paru a ddychwelwyd, defnyddiwch y fformwlâu hyn: =IFERROR(INDEX($C$2:$C$20,MATCH(0,COUNTIF($F$1:F1,$C$2:$C$20)+($A$2:$A$20<>$E$2),0)),"")

Yna, pwyswch Ctrl + Shift + Enter allweddi gyda'i gilydd i gael y gwerth cyfatebol cyntaf, ac yna llusgwch y gell fformiwla hon i lawr i gelloedd eraill nes bod celloedd gwag yn arddangos, a byddwch yn cael y canlyniad yn ôl yr angen:


Gwyliwch yr holl werthoedd paru yn seiliedig ar ddau gyflwr neu fwy yn fertigol:

I Vlookup a dychwelyd yr holl werthoedd paru yn seiliedig ar werthoedd mwy penodol yn fertigol, y fformiwla generig yw:

=IFERROR(INDEX(return_range, SMALL(IF(1=((--(lookup_value1=lookup_range1)) * ( --(lookup_value2=lookup_range2))), ROW(return_range)-m,""), ROW()-n)),"")
Nodyn: m yw rhif rhes y gell gyntaf yn yr ystod dychwelyd minws 1.
      n yw rhif rhes y gell fformiwla gyntaf minws 1.

1. Copïwch y fformiwla isod i mewn i gell wag, ac yna pwyswch Ctrl + Shift + Enter allweddi gyda'i gilydd i gael yr eitem gyntaf wedi'i chyfateb.

=IFERROR(INDEX($C$2:$C$20, SMALL(IF(1=((--($E$2=$A$2:$A$20)) * (--($F$2=$B$2:$B$20))), ROW($C$2:$C$20)-1,""), ROW()-1)),"")

2. Yna llusgwch y gell fformiwla i lawr i gelloedd eraill nes bod cell wag yn cael ei harddangos, gweler y screenshot:

Nodyn: I gael mwy o feini prawf, does ond angen i chi ymuno â'r lookup_value ac lookup_range yn y fformiwla, fel: =IFERROR(INDEX(return_range, SMALL(IF(1 = ((--(lookup_value1=lookup_range1)) * (--(lookup_value2 = lookup_range2) * (--(lookup_value3 =lookup_range3)))), ROW(return_range) - m, ""), ROW() - n)),"").


Vlookup yr holl werthoedd paru yn seiliedig ar ddau gyflwr neu fwy i mewn i 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 chi i ddatrys y swydd hon yn gyflym ac yn hawdd.

Vlookup yr holl werthoedd paru yn seiliedig ar un cyflwr yn un gell:

Defnyddiwch y fformiwla isod isod mewn cell wag, ac yna pwyswch Ctrl + Shift + Enter allweddi gyda'i gilydd i gael y canlyniad:

=TEXTJOIN(",",TRUE,IF($A$2:$A$20=F1,$C$2:$C$20,""))

Awgrym:

I anwybyddu'r dyblygu yn y gwerthoedd paru a ddychwelwyd, defnyddiwch y fformwlâu hyn: =TEXTJOIN(",", TRUE, IF(IFERROR(MATCH($C$2:$C$20, IF(F1=$A$2:$A$20, $C$2:$C$20, ""), 0),"")=MATCH(ROW($C$2:$C$20), ROW($C$2:$C$20)), $C$2:$C$20, ""))


Gwyliwch yr holl werthoedd paru yn seiliedig ar ddau gyflwr neu fwy i mewn i un gell:

Ar gyfer delio â chyflyrau lluosog wrth ddychwelyd yr holl werthoedd paru i mewn i un gell, defnyddiwch y fformiwla isod, ac yna pwyswch Ctrl + Shift + Enter allweddi gyda'i gilydd i gael y canlyniad:

=TEXTJOIN(",",TRUE,IF(($A$2:$A$20=F1)*($B$2:$B$20=F2),$C$2:$C$20,""))

Nodiadau:

1. Dim ond yn Excel 2019 ac Office 365 y mae swyddogaeth TEXTJOIN ar gael.

2. Os ydych chi'n defnyddio Excel 2016 a fersiynau cynharach, defnyddiwch Swyddogaeth Diffiniedig Defnyddiwr yr erthyglau isod:


4. Vlookup i ddychwelyd rhes gyfan neu gyfan o gell wedi'i chyfateb

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

1. Copïwch neu deipiwch y fformiwla isod i mewn i gell wag lle rydych chi am allbwn y canlyniad, a gwasgwch Rhowch allwedd i gael y gwerth cyntaf.

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

2. Yna, llusgwch y gell fformiwla i'r ochr dde nes bod data'r rhes gyfan yn cael ei arddangos, gweler y screenshot:

Nodyn: Yn y fformiwla uchod, F2 yw'r gwerth edrych rydych chi am ddychwelyd y rhes gyfan yn seiliedig ar, A1: D12 yw'r ystod ddata rydych chi am ei defnyddio, A1 yn nodi'r rhif colofn cyntaf o fewn eich ystod ddata.

Awgrym:

Os canfyddir rhesi lluosog yn seiliedig ar y gwerth cyfatebol, i ddychwelyd pob un o'r rhesi cyfatebol, defnyddiwch y fformiwla hon: =IFERROR(INDEX(A:A,SMALL(IF(ISNUMBER(SEARCH($F$2,$A$2:$A$12)),ROW($A$2:$A$12),""),ROW()-1)),""), ac yna'r wasg Ctrl + Shift + Enter allweddi gyda'i gilydd i gael y canlyniad cyntaf, yna llusgwch y ddolen llenwi i'r celloedd, gweler y screenshot:

Ac yna llusgwch y handlen llenwi i lawr ar draws y celloedd i gael yr holl resi paru fel y dangosir isod y screenshot:


5. Gwnewch swyddogaeth Vlookup lluosog (Vlookup wedi'i nythu) yn Excel

Weithiau, efallai yr hoffech edrych ar werthoedd mewn tablau lluosog, os yw unrhyw un o'r tablau'n cynnwys y gwerth edrych a roddir fel y nodir isod, a ddangosir, yn yr achos hwn, gallwch gyfuno un neu fwy o swyddogaethau Vlookup ynghyd â swyddogaeth IFERROR i gyflawni edrychiad lluosog.

Y fformiwla generig ar gyfer swyddogaeth Vlookup nythu yw:

=IFERROR(VLOOKUP(lookup_value,table1,col,0),IFERROR(VLOOKUP(lookup_value,table2,col,0),VLOOKUP(lookup_value,table3,col,0)))

Nodyn:

  • lookup_value: y gwerth rydych chi'n edrych amdano;
  • Table1, Table2, Table3, ...: y tablau lle mae'r gwerth edrych a'r gwerth dychwelyd yn bodoli;
  • col: rhif y golofn yn y tabl rydych chi am ddychwelyd y gwerth paru ohono.
  • 0: Defnyddir hwn ar gyfer cyfatebiaeth union.

1. Defnyddiwch y fformiwla ganlynol mewn cell wag lle rydych chi am roi'r canlyniad:

=IFERROR(VLOOKUP(J3,$A$3:$B$7,2,0),IFERROR(VLOOKUP(J3,$D$3:$E$7,2,0),VLOOKUP(J3,$G$3:$H$7,2,0)))

2. Yna, llusgwch y handlen llenwi i lawr i'r celloedd rydych chi am gymhwyso'r fformiwla hon, ac mae'r holl werthoedd cyfatebol wedi'u dychwelyd fel y nodir isod:

Nodiadau:

  • 1. Yn y fformiwla uchod, J3 yw'r gwerth rydych chi'n edrych amdano; A3: B7, D3: E7, G3: H7 yw'r ystodau tabl lle mae'r gwerth edrych a'r gwerth dychwelyd yn bodoli; Y nifer 2 yw'r rhif colofn yn yr ystod i ddychwelyd y gwerth paru ohono.
  • 2. Os na ellir dod o hyd i'r gwerth edrych i fyny, arddangosir gwerth gwall, i ddisodli'r gwall gyda thestun darllenadwy, defnyddiwch y fformiwla hon: =IFERROR(VLOOKUP(J3,$A$3:$B$7,2,0),IFERROR(VLOOKUP(J3,$D$3:$E$7,2,0),IFERROR(VLOOKUP(J3,$G$3:$H$7,2,0),"can't find")))

6. Vlookup i wirio a oes gwerth yn bodoli yn seiliedig ar ddata rhestr mewn colofn arall

Gall swyddogaeth Vlookup hefyd eich helpu i wirio a oes gwerthoedd yn bodoli yn seiliedig ar restr arall, er enghraifft, os ydych chi am chwilio am yr enwau yng ngholofn C a dychwelyd Ie neu Na os yw'r enw i'w gael ai peidio yng ngholofn A fel y nodir isod. dangosir.

1. Defnyddiwch y fformiwla ganlynol mewn cell wag:

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

2. Yna, llusgwch y handlen llenwi i lawr i'r celloedd rydych chi am lenwi'r fformiwla hon, a byddwch chi'n cael y canlyniad yn ôl yr angen, gweler y screenshot:

Nodyn: Yn y fformiwla uchod, C2 yw'r gwerth edrych rydych chi am ei wirio; A2: A10 yw'r rhestr o ystodau lle ceir gwerthoedd edrych; y rhif 1 yw rhif y golofn lle rydych chi am nôl gwerth yn eich ystod.


7. Vlookup a swm yr holl werthoedd cyfatebol mewn rhesi neu golofnau

Os ydych chi'n gweithio gyda data rhifiadol, weithiau, wrth echdynnu'r gwerthoedd cyfatebol o'r tabl, efallai y bydd angen i chi hefyd grynhoi'r rhifau mewn sawl colofn neu res. Bydd yr adran hon yn cyflwyno rhai fformiwlâu i orffen y swydd hon yn Excel.

Vlookup a swm yr holl werthoedd cyfatebol mewn rhes neu resi lluosog

Tybiwch, mae gennych chi restr cynnyrch gyda gwerthiannau am sawl mis fel y dangosir isod y screenshot, nawr, mae angen i chi grynhoi pob archeb ym mhob mis yn seiliedig ar y cynhyrchion a roddir.

Vlookup a swm y gwerthoedd cyfatebol cyntaf yn olynol:

1. 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.

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

2. Yna, llusgwch y ddolen llenwi i lawr i gopïo'r fformiwla hon i gelloedd eraill sydd eu hangen arnoch, ac mae'r holl werthoedd mewn rhes o'r gwerth paru cyntaf wedi'u crynhoi gyda'i gilydd, gweler y screenshot:

Nodyn: Yn y fformiwla uchod: H2 yw'r gell sy'n cynnwys y gwerth rydych chi'n edrych amdano; A2: F9 yw'r ystod ddata (heb benawdau colofnau) sy'n cynnwys y gwerth edrych a'r gwerthoedd cyfatebol; Y nifer {2,3,4,5,6} yw rhifau colofnau a ddefnyddir i gyfrifo cyfanswm yr ystod.


Vlookup a swm yr holl werthoedd cyfatebol mewn sawl rhes:

Dim ond am y gwerth cyfatebol cyntaf y gall y fformiwla uchod gyfri gwerthoedd yn olynol. Os ydych chi am grynhoi'r holl fatsis mewn sawl rhes, defnyddiwch y fformiwla ganlynol, ac yna llusgwch y ddolen llenwi i lawr i'r celloedd rydych chi am gymhwyso'r fformiwla hon, a byddwch chi'n cael y canlyniad a ddymunir sydd ei angen arnoch chi, gweler y screenshot:

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

Nodyn: Yn y fformiwla uchod: H2 yw'r gwerth edrych rydych chi'n edrych amdano; A2: A9 yw'r penawdau rhes sy'n cynnwys y gwerth edrych; B2: F9 ystod ddata'r gwerthoedd rhifol yr ydych am eu crynhoi.


Vlookup a swm yr holl werthoedd cyfatebol mewn colofn neu golofnau lluosog

Vlookup a swm y gwerthoedd cyfatebol cyntaf mewn colofn:

Os ydych chi am grynhoi cyfanswm y gwerth am y misoedd penodol fel y dangosir yn y screenshot isod.

Rhowch 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, nawr, mae'r gwerthoedd cyfatebol cyntaf yn seiliedig ar y mis penodol mewn colofn wedi'u crynhoi gyda'i gilydd, gweler y screenshot:

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

Nodyn: Yn y fformiwla uchod: H2 yw'r gwerth edrych rydych chi'n edrych amdano; B1: F1 yw'r penawdau colofn sy'n cynnwys y gwerth edrych; B2: F9 ystod ddata'r gwerthoedd rhifol yr ydych am eu crynhoi.


Gwyliwch a swm yr holl werthoedd cyfatebol mewn sawl colofn:

I Vlookup a chrynhoi'r holl werthoedd cyfatebol mewn sawl colofn, dylech ddefnyddio'r fformiwla ganlynol:

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

Nodyn: Yn y fformiwla uchod: H2 yw'r gwerth edrych rydych chi'n edrych amdano; B1: F1 yw'r penawdau colofn sy'n cynnwys y gwerth edrych; B2: F9 ystod ddata'r gwerthoedd rhifol yr ydych am eu crynhoi.


Vlookup a swm y gwerthoedd cyfatebol cyntaf neu'r holl werthoedd cyfatebol gyda nodwedd bwerus

Efallai bod y fformwlâu uchod yn anodd i chi eu cofio, yn yr achos hwn, byddaf yn argymell nodwedd ddefnyddiol - Edrych a Swm of Kutools ar gyfer Excel, gyda'r nodwedd hon, gallwch gael y canlyniad mor hawdd â phosibl.    Cliciwch i lawrlwytho Kutools ar gyfer Excel nawr!


Vlookup a swm yr 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.

Defnyddiwch y fformiwla ganlynol mewn cell, ac yna pwyswch Enter key 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 ystod ddata'r gwerthoedd rhifol yr ydych am eu crynhoi; B1: F1 is mae penawdau'r colofnau'n cynnwys y gwerth edrych yr ydych am ei grynhoi yn seiliedig ar; I2 yw'r gwerth edrych o fewn y penawdau colofnau rydych chi'n edrych amdanynt; A2: A9 a yw penawdau'r rhes yn cynnwys y gwerth edrych yr ydych am ei grynhoi yn seiliedig; H2 yw'r gwerth edrych o fewn y penawdau rhes rydych chi'n edrych amdanyn nhw.


8. Vlookup i uno dau dabl yn seiliedig ar un neu fwy o golofnau allweddol

Yn eich gwaith beunyddiol, wrth ddadansoddi'r data, efallai y bydd angen i chi gasglu'r holl wybodaeth angenrheidiol i mewn i un tabl yn seiliedig ar un neu fwy o golofnau allweddol. I ddatrys y swydd hon, gall swyddogaeth Vlookup hefyd ffafrio chi.

Vlookup i uno dau dabl yn seiliedig ar un golofn allweddol

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

Fformiwla 1: Defnyddio swyddogaeth VLOOKUP

I uno'r ddau dabl yn un yn seiliedig ar golofn allweddol, cymhwyswch y fformiwla ganlynol i mewn i gell wag lle rydych chi am gael y canlyniad, ac yna llusgwch y handlen llenwi i lawr i'r celloedd rydych chi am gymhwyso'r fformiwla hon, byddwch chi cael tabl unedig gyda'r golofn archeb yn ymuno â'r data tabl cyntaf yn seiliedig ar ddata allweddol y golofn.

=VLOOKUP($A2,$E$2:$F$8,2,FALSE)

Nodyn: Yn y fformiwla uchod, A2 yw'r gwerth rydych chi'n edrych amdano, E2: F8 yw'r tabl i chwilio, y rhif 2 yw rhif y golofn yn y tabl i adfer y gwerth ohono.

Fformiwla 2: Defnyddio swyddogaethau MYNEGAI a MATCH

Os yw'ch data cyffredin yn yr ochr dde a'r data a ddychwelwyd yn y golofn chwith yn yr ail dabl, i uno'r golofn archeb, ni all swyddogaeth Vlookup wneud y gwaith. I edrych i fyny o'r dde i'r chwith, gallwch ddefnyddio'r swyddogaethau MYNEGAI a MATCH i ddisodli'r swyddogaeth Vlookup.

Copïwch neu nodwch y fformiwla isod mewn cell wag, yna copïwch y fformiwla i lawr y golofn, ac mae'r golofn archebu wedi'i chysylltu â'r tabl cyntaf, gweler y screenshot:

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

Nodyn: Yn y fformiwla uchod, A2 yw'r gwerth edrych rydych chi'n edrych amdano, E2: E8 yw'r ystod o ddata rydych chi am ei ddychwelyd, F2: F8 yw ystod edrych sy'n cynnwys y gwerth edrych.


Vlookup i uno dau dabl yn seiliedig ar sawl colofn allweddol

Os oes gan y ddau dabl rydych chi am ymuno â nhw nifer o golofnau allweddol, i uno'r tablau yn seiliedig ar y colofnau cyffredin hyn, gall y swyddogaethau INDEX a MATCH eich helpu chi.

Y fformiwla generig ar gyfer uno dau dabl yn seiliedig ar sawl colofn allweddol yw:

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

1. Defnyddiwch y fformiwla isod mewn cell 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)

Nodyn: Yn y fformiwla uchod, dangosir yr hyn y mae'r cyfeiriadau celloedd yn ei gynrychioli fel isod:

2. 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 a fersiynau diweddarach, gallwch hefyd ddefnyddio'r Ymholiad Pwer nodwedd i uno dau neu fwy o dablau yn un yn seiliedig ar golofnau allweddol. Cliciwch i wybod y manylion gam wrth gam.

9. Gwerthoedd paru Vlookup ar draws sawl taflen waith

A ydych erioed wedi ceisio Vlookup gwerthoedd ar draws sawl taflen waith? Gan dybio bod gen i'r tair taflen waith ganlynol gydag ystod o ddata, a nawr, rydw i eisiau cael rhan o'r gwerthoedd cyfatebol yn seiliedig ar y meini prawf o'r tair taflen waith hyn i gael y canlyniad fel y dangosir isod y screenshot. Yn yr achos hwn, mae'r Gwerthoedd Vlookup ar draws taflenni gwaith lluosog efallai y bydd tiwtorial yn ffafrio cam wrth gam i chi.


Mae gwerthoedd cyfatebol VLOOKUP yn cadw fformatio celloedd

1. Vlookup i gael fformatio celloedd (lliw celloedd, lliw ffont) ynghyd â gwerth edrych

Fel y gwyddom i gyd, gall y swyddogaeth Vlookup arferol yn unig ein helpu i ddychwelyd y gwerth cyfatebol o ystod ddata arall, ond weithiau, efallai y byddwch am ddychwelyd y gwerth cyfatebol ynghyd â fformatio'r gell, fel y lliw llenwi, lliw ffont, arddull ffont fel y dangosir isod screenshot. Bydd yr adran hon yn siarad am sut i gael y fformatio celloedd gyda'r gwerth a ddychwelwyd yn Excel.

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

1. Yn y daflen waith mae'n cynnwys y data rydych chi am Vlookup, cliciwch ar y dde ar y tab dalen 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 isod y cod VBA i mewn i ffenestr y Cod.

Cod VBA 1: Vlookup i gael fformatio celloedd ynghyd â gwerth edrych

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

3. 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 edrych

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

4. Ar ôl mewnosod y codau uchod, yna cliciwch offer > cyfeiriadau yn y Microsoft Visual Basic ar gyfer Ceisiadau ffenestr. Yna gwiriwch y Microsoft Script Runtime blwch ticio yn y Cyfeiriadau - VBAProject blwch deialog. Gweler sgrinluniau:

5. Yna, cliciwch OK i gau'r blwch deialog, ac yna arbed a chau ffenestr y cod, nawr, ewch yn ôl y daflen waith, ac yna defnyddio'r fformiwla hon: =LookupKeepFormat(E2,$A$1:$C$10,3) i mewn i gell wag lle rydych chi am allbwn y canlyniad, ac yna pwyswch y fysell Enter. Gweler y screenshot:

Nodyn: Yn y fformiwla uchod, E2 yw'r gwerth y byddwch chi'n edrych i fyny arno, A1: C10 yw ystod y tabl, a'r rhif 3 yw rhif colofn y tabl rydych chi am i'r gwerth cyfatebol gael ei ddychwelyd.

6. Yna, dewiswch y gell canlyniad cyntaf, a llusgwch y ddolen llenwi i lawr i gael yr holl ganlyniadau ynghyd â'u fformatio. Gweler y screenshot.


2. Cadwch y fformat dyddiad o werth a ddychwelwyd gan Vlookup

Fel rheol, wrth ddefnyddio'r swyddogaeth Vloook i edrych i fyny a dychwelyd gwerth fformat y dyddiad cyfatebol, bydd rhywfaint o fformat rhif yn cael ei arddangos fel isod y llun a ddangosir. Er mwyn cadw'r fformat dyddiad o ganlyniad a ddychwelwyd, dylech amgáu'r swyddogaeth TEXT i'r swyddogaeth Vlookup.

Defnyddiwch y fformiwla isod i mewn i gell wag, ac yna llusgwch y ddolen llenwi i gopïo'r fformiwla hon i gelloedd eraill, ac mae'r holl ddyddiadau wedi'u paru wedi'u dychwelyd fel y dangosir isod y llun:

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

Nodyn: Yn y fformiwla uchod, E2 yw'r gwerth edrych, A2: C9 yw'r ystod edrych, y rhif 3 yw'r rhif colofn rydych chi am i'r gwerth gael ei ddychwelyd, mm / dd / bbbb yw'r fformat dyddiad rydych chi am ei gadw.


3. Gwerth paru Vlookup a dychwelyd gyda sylw celloedd

A ydych erioed wedi ceisio Vlookup i ddychwelyd nid yn unig y data celloedd sy'n cyfateb, ond hefyd y sylw celloedd hefyd yn Excel fel y screenshot canlynol a ddangosir? I ddatrys y dasg hon, gall y Swyddogaeth Diffiniedig Defnyddiwr isod ffafrio chi.

1. Daliwch 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 ffenestr y cod, nodwch y fformiwla hon: =vlookupcomment(D2,$A$2:$B$9,2,FALSE) i mewn i gell wag i ddod o hyd i'r canlyniad, ac yna llusgwch y ddolen llenwi i gopïo'r fformiwla hon i gelloedd eraill, nawr, mae'r gwerthoedd cyfatebol yn ogystal â'r sylwadau'n cael eu dychwelyd ar unwaith, gweler y screenshot:

Nodyn: Yn y fformiwla uchod, D2 yw'r gwerth edrych rydych chi am ddychwelyd ei werth cyfatebol, A2: B9 yw'r tabl data rydych chi am ei ddefnyddio, y rhif 2 yw rhif y golofn sy'n cynnwys y gwerth cyfatebol rydych chi am ei ddychwelyd.


4. Deliwch â'r testun a'r rhifau real yn Vlookup

Er enghraifft, mae gen i ystod o ddata, y rhif ID yn y tabl gwreiddiol yw fformat rhif, yn y gell edrych sy'n cael ei storio fel testun, wrth gymhwyso'r swyddogaeth Vlookup arferol, mae canlyniad gwall # Amherthnasol yn cael ei arddangos fel islaw'r screenshot dangosir. Yn yr achos hwn, sut allech chi gael y wybodaeth gywir pe bai gan y rhif edrych a'r rhif gwreiddiol yn y tabl y fformat data gwahanol?

Ar gyfer delio â'r testun a'r rhifau real yn swyddogaeth Vlookup, cymhwyswch y fformiwla ganlynol i mewn i gell wag, ac yna llusgwch y ddolen llenwi i lawr i gopïo'r fformiwla hon, a byddwch yn cael y canlyniadau cywir fel y dangosir isod y screenshot:

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

Nodiadau:

  • 1. Yn y fformiwla uchod, D2 yw'r gwerth edrych rydych chi am ddychwelyd ei werth cyfatebol, A2: B8 yw'r tabl data rydych chi am ei ddefnyddio, y rhif 2 yw rhif y golofn sy'n cynnwys y gwerth cyfatebol rydych chi am ei ddychwelyd.
  • 2. Mae'r fformiwla hon hefyd yn gweithio'n dda os nad ydych yn siŵr ble mae gennych rifau a ble mae gennych destun.

Dadlwythwch ffeiliau sampl VLOOKUP

Vlookup_basic_examples.xlsx

Advanced_Vlookup_examples.xlsx

Vlookup_keep_cell_formatting.zip



  • Bar Fformiwla Gwych (golygu llinellau lluosog o destun a fformiwla yn hawdd); Cynllun Darllen (darllen a golygu nifer fawr o gelloedd yn hawdd); Gludo i'r Ystod Hidlo...
  • Uno Celloedd / Rhesi / Colofnau a Cadw Data; Cynnwys Celloedd Hollt; Cyfuno Rhesi Dyblyg a Swm / Cyfartaledd... Atal Celloedd Dyblyg; Cymharwch y Meysydd...
  • Dewiswch Dyblyg neu Unigryw Rhesi; Dewiswch Blank Rows (mae pob cell yn wag); Darganfyddiad Gwych a Darganfyddiad Niwlog mewn Llawer o Lyfrau Gwaith; Dewis ar Hap ...
  • Copi Union Celloedd Lluosog heb newid cyfeirnod fformiwla; Auto Creu Cyfeiriadau i Daflenni Lluosog; Mewnosod Bwledi, Blychau Gwirio a mwy ...
  • Fformiwlâu Hoff a Mewnosod yn Gyflym, Meysydd, Siartiau a Lluniau; Amgryptio Celloedd gyda chyfrinair; Creu Rhestr Bostio ac anfon e-byst ...
  • Testun Detholiad, Ychwanegu Testun, Tynnu yn ôl Swydd, Tynnwch y Gofod; Creu ac Argraffu Subtotals Paging; Trosi rhwng Cynnwys a Sylwadau Celloedd...
  • Hidlo Super (arbed a chymhwyso cynlluniau hidlo i ddalenni eraill); Trefnu Uwch yn ôl mis / wythnos / dydd, amlder a mwy; Hidlo Arbennig gan feiddgar, italig ...
  • Cyfuno Llyfrau Gwaith a Thaflenni Gwaith; Uno Tablau yn seiliedig ar golofnau allweddol; Rhannwch Ddata yn Daflenni Lluosog; Trosi Swp xls, xlsx a PDF...
  • Grwpio Tabl Pivot yn ôl rhif wythnos, diwrnod o'r wythnos a mwy ... Dangos Celloedd Datgloi, wedi'u Cloi yn ôl gwahanol liwiau; Amlygu Celloedd sydd â Fformiwla / Enw...
tab kte 201905
  • 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!
gwaelod officetab
Say something here...
symbols left.
You are guest
or post as a guest, but your post won't be published automatically.
Loading comment... The comment will be refreshed after 00:00.

Be the first to comment.