Samplaí Casta VLOOKUP: Cuardach Ilchritéir

Sa dara cuid dár rang teagaisc ar an bhfeidhm VPR (VLOOKUP) in Excel, déanfaimid anailís ar roinnt samplaí a chabhróidh leat an chumhacht go léir a threorú VPR chun na tascanna Excel is uaillmhianaí a réiteach. Glacann na samplaí leis go bhfuil eolas bunúsach agat cheana féin ar conas a oibríonn an ghné seo. Mura bhfuil, b'fhéidir go mbeadh suim agat tosú leis an gcéad chuid den rang teagaisc seo, a mhíníonn an chomhréir agus an úsáid bhunúsach. VPR. Bhuel, a ligean ar tús a chur leis.

• Cuardaigh in Excel de réir critéar iolrach
• Bainimid na luachanna 2nd, 3rd, etc. ag baint úsáide as VLOOKUP
• Aisghabh gach athrá den luach inmhianaithe
• Cuardach XNUMXD de réir ró agus colún aitheanta
• Ag baint úsáide as VLOOKUPanna iolracha in aon fhoirmle amháin
• Ionadú dinimiciúil sonraí ó tháblaí éagsúla

Cuardaigh in Excel de réir critéar iolrach

Feidhm VPR Is uirlis fíorchumhachtach é in Excel chun cuardach a dhéanamh ar luach ar leith i mbunachar sonraí. Mar sin féin, tá teorannú suntasach ann - ceadaíonn a chomhréir duit cuardach a dhéanamh ar luach amháin. Cad a tharlóidh má theastaíonn uait cuardach a dhéanamh de réir ilchoinníollacha? Gheobhaidh tú an réiteach thíos.

Sampla 1: Cuardaigh de réir 2 chritéar dhifriúla

Cuir i gcás go bhfuil liosta orduithe againn agus ba mhaith linn a fháil Cainníocht earraí (Qty.), bunaithe ar dhá chritéar – Ainm an chliaint (Custaiméir) и Название продукта (Táirge). Tá an t-ábhar casta ag an bhfíric gur ordaigh gach ceann de na ceannaitheoirí roinnt cineálacha earraí, mar atá le feiceáil sa tábla thíos:

feidhm rialta VPR Ní oibreoidh sé sa chás seo mar go dtabharfaidh sé ar ais an chéad luach a aimsíonn sé a mheaitseálann an luach cuardaigh tugtha. Mar shampla, más mian leat eolas a fháil ar chainníocht earra Milseáin'arna ordú ag an gceannaitheoir Jeremy Hill, scríobh an fhoirmle seo a leanas:

=VLOOKUP(B1,$A$5:$C$14,3,FALSE)

=ВПР(B1;$A$5:$C$14;3;ЛОЖЬ)

– tabharfaidh an fhoirmle seo an toradh ar ais 15a fhreagraíonn don táirge Apples, toisc gurb é an chéad luach a mheaitseálann.

Tá réiteach simplí ann - cruthaigh colún breise inar féidir na critéir inmhianaithe go léir a chomhcheangal. In ár sampla, is iad seo na colúin Ainm an chliaint (Custaiméir) и Название продукта (Táirge). Ná déan dearmad go gcaithfidh an colún cumaiscthe a bheith ar an gcolún is faide ar chlé sa raon cuardaigh, mar is é an colún ar chlé a bhfuil an fheidhm VPR Breathnaíonn suas nuair a lorg luach.

Mar sin, cuireann tú colún cúnta leis an tábla agus cóipeáil an fhoirmle seo a leanas thar a chealla go léir: =B2&C2. Más mian leat an teaghrán a bheith níos inléite, is féidir leat na luachanna comhcheangailte a scaradh le spás: =B2&» «&C2. Ina dhiaidh sin, is féidir leat an fhoirmle seo a leanas a úsáid:

=VLOOKUP("Jeremy Hill Sweets",$A$7:$D$18,4,FALSE)

=ВПР("Jeremy Hill Sweets";$A$7:$D$18;4;ЛОЖЬ)

or

=VLOOKUP(B1,$A$7:$D$18,4,FALSE)

=ВПР(B1;$A$7:$D$18;4;ЛОЖЬ)

Cá bhfuil an chill B1 ina bhfuil luach comhcheangailte na hargóna lookup_value (lookup_value) agus 4 – Argóint col_index_num (column_number), ie uimhir an cholúin ina bhfuil na sonraí atá le haisghabháil.

Sampla 2: VLOOKUP de réir dhá chritéar agus an tábla á fheiceáil ar bhileog eile

Más gá duit an príomh-tábla (Príomhthábla) a nuashonrú trí shonraí a chur leis ón dara tábla (Tábla Féach), atá suite ar bhileog eile nó i leabhar oibre Excel eile, ansin is féidir leat an luach atá ag teastáil a bhailiú go díreach san fhoirmle a chuireann tú isteach. isteach sa tábla is mó.

Mar a tharla sa sampla roimhe seo, beidh ort colún cúnta sa tábla Lookup leis na luachanna comhcheangailte. Caithfidh an colún seo a bheith ar an gcolún is faide ar chlé sa raon cuardaigh.

Mar sin an fhoirmle le VPR d'fhéadfadh a bheith mar seo:

=VLOOKUP(B2&" "&C2,Orders!$A&$2:$D$2,4,FALSE)

=ВПР(B2&" "&C2;Orders!$A&$2:$D$2;4;ЛОЖЬ)

Anseo, tá ainmneacha custaiméirí agus ainmneacha táirgí, faoi seach, agus an nasc i gcolúin B agus C Orduithe!$A&$2:$D$2 sainmhíníonn tábla le breathnú suas i mbileog eile.

Chun an fhoirmle a dhéanamh níos inléite, is féidir leat ainm a thabhairt don raon radhairc, agus ansin beidh cuma i bhfad níos simplí ar an bhfoirmle:

=VLOOKUP(B2&" "&C2,Orders,4,FALSE)

=ВПР(B2&" "&C2;Orders;4;ЛОЖЬ)

Le go n-oibreoidh an fhoirmle, ní mór na luachanna sa cholún is faide ar chlé den tábla a bhfuil tú ag féachaint orthu a chomhcheangal ar an mbealach céanna go díreach agus atá sna critéir chuardaigh. San fhigiúr thuas, chuireamar na luachanna u2bu2band le chéile a chuir spás eatarthu, ar an mbealach céanna is gá duit a dhéanamh sa chéad argóint den fheidhm (BXNUMX & “” & CXNUMX).

Cuimhnigh! Feidhm VPR teoranta do 255 carachtar, ní féidir leis luach atá níos mó ná 255 carachtar ar fad a chuardach. Coinnigh seo san áireamh agus déan cinnte nach sáraíonn fad an luach inmhianaithe an teorainn seo.

Aontaím nach é an réiteach is galánta agus nach bhfuil inghlactha i gcónaí é colún cúnta a chur leis. Is féidir leat an rud céanna a dhéanamh gan an colún cúntóir, ach bheadh ​​gá le foirmle i bhfad níos casta le meascán feidhmeanna INNÉACS (INDEX) agus CHLEAMHNAS (NÍOS NÍOS MÓ).

Bainimid na luachanna 2nd, 3rd, etc. ag baint úsáide as VLOOKUP

Tá a fhios agat cheana féin VPR is féidir luach meaitseála amháin a thabhairt ar ais, níos cruinne, an chéad cheann a fuarthas. Ach cad a tharlóidh má dhéantar an luach seo arís agus arís eile san eagar amhairc, agus gur mhaith leat an 2ú nó an 3ú ceann díobh a bhaint? Cad a tharlaíonn má gach luach? Is cosúil go bhfuil an fhadhb casta, ach tá an réiteach ann!

Cuir i gcolún amháin den tábla go bhfuil ainmneacha na gcustaiméirí (Ainm an Chustaiméara), agus go bhfuil na táirgí (Táirge) a cheannaigh siad sa cholún eile. Déanaimis iarracht teacht ar an 2ú, 3ú agus 4ú mír a cheannaigh custaiméir ar leith.

Is é an bealach is éasca ná colún cúnta a chur leis roimh an gcolún ainm custaiméara agus é a líonadh le hainmneacha custaiméirí le huimhir athrá gach ainm, mar shampla, Eoin Dubh1, Eoin Dubh2 srl. Déanfaimid an cleas le huimhriú ag baint úsáide as an bhfeidhm COUNTIF (COUNTIF), ós rud é go bhfuil ainmneacha na gcustaiméirí i gcolún B:

=B2&COUNTIF($B$2:B2,B2)

=B2&СЧЁТЕСЛИ($B$2:B2;B2)

Ina dhiaidh sin is féidir leat an gnáthfheidhm a úsáid VPRchun an t-ordú riachtanach a fháil. Mar shampla:

• Aimsigh 2-ú earra arna ordú ag an gcustaiméir Dan Brown:

  =VLOOKUP("Dan Brown2",$A$2:$C$16,3,FALSE)

  =ВПР("Dan Brown2";$A$2:$C$16;3;ЛОЖЬ)

• Aimsigh 3-ú earra arna ordú ag an gcustaiméir Dan Brown:

  =VLOOKUP("Dan Brown3",$A$2:$C$16,3,FALSE)

  =ВПР("Dan Brown3";$A$2:$C$16;3;ЛОЖЬ)

Go deimhin, is féidir leat tagairt cille a chur isteach mar luach cuardaigh in ionad téacs, mar a thaispeántar san fhigiúr seo a leanas:

Mura bhfuil tú ach á lorg 2-e athrá, is féidir leat é a dhéanamh gan an colún cúnta trí fhoirmle níos casta a chruthú:

=IFERROR(VLOOKUP($F$2,INDIRECT("$B$"&(MATCH($F$2,Table4[Customer Name],0)+2)&":$C16"),2,FALSE),"")

=ЕСЛИОШИБКА(ВПР($F$2;ДВССЫЛ("$B$"&(ПОИСКПОЗ($F$2;Table4[Customer Name];0)+2)&":$C16");2;ИСТИНА);"")

Sa fhoirmle seo:

• $ F $ 2 – cill ina bhfuil ainm an cheannaitheora (níl aon athrú air, tabhair faoi deara - is nasc iomlán é);
• $B $ – colún ainm custaiméara;
• Table4 – Do tábla (is féidir an áit seo a bheith ina raon rialta freisin);
• $ C16 – críochchill do bhoird nó do raon.

Ní fhaigheann an fhoirmle seo ach an dara luach meaitseála. Más gá duit na athrá atá fágtha a bhaint as, bain úsáid as an réiteach roimhe seo.

Más gá duit liosta de na cluichí go léir - an fheidhm VPR ní cúntóir é seo, mar ní thugann sé ach luach amháin ar ais ag an am - tréimhse. Ach tá feidhm ag Excel INNÉACS (INDEX), ar féidir leo dul i ngleic go héasca leis an tasc seo. Conas a bheidh a leithéid de fhoirmle cuma, beidh tú ag foghlaim sa sampla seo a leanas.

Aisghabh gach athrá den luach inmhianaithe

Mar a luadh thuas VPR ní féidir gach luach dúblach a bhaint as an raon scanadh. Chun seo a dhéanamh, beidh foirmle beagán níos casta uait, comhdhéanta de roinnt feidhmeanna Excel, mar shampla INNÉACS (INDEX), BHEAG (BEAG) agus ROW (LÍNE)

Mar shampla, aimsíonn an fhoirmle thíos gach athrá den luach ó chill F2 sa raon B2: B16 agus filleann sí an toradh ó na sraitheanna céanna i gcolún C.

{=IFERROR(INDEX($C$2:$C$16,SMALL(IF($F$2=B2:B16,ROW(C2:C16)-1,""),ROW()-3)),"")}

{=ЕСЛИОШИБКА(ИНДЕКС($C$2:$C$16;НАИМЕНЬШИЙ(ЕСЛИ($F$2=B2:B16;СТРОКА(C2:C16)-1;"");СТРОКА()-3));"")}

Cuir an fhoirmle eagair seo isteach i gcealla iolracha cóngaracha, mar na cealla F4: F8mar a thaispeántar san fhigiúr thíos. Caithfidh líon na gceall a bheith cothrom le nó níos mó ná an t-uaslíon is féidir athrá den luach cuardaigh. Ná déan dearmad cliceáil Ctrl + Shift + Iontráilchun an fhoirmle eagar a chur isteach i gceart.

Má tá suim agat a thuiscint conas a oibríonn sé, déanaimis tumadh isteach i sonraí na foirmle beagán:

1 Cuid:

IF($F$2=B2:B16,ROW(C2:C16)-1,"")

ЕСЛИ($F$2=B2:B16;СТРОКА(C2:C16)-1;"")

$F$2=B2:B16 – cuir an luach i gcill F2 i gcomparáid le gach ceann de na luachanna sa raon B2:B16. Má aimsítear meaitseáil, ansin an slonn STRING(C2:C16)-1 filleann sé uimhir na líne comhfhreagrach (luach -1 is féidir leat gan an ceanntásc a chur san áireamh). Mura bhfuil aon mheaitseáil, an fheidhm IF Tugann (IF) sreang folamh ar ais.

Toradh feidhme IF (IF) beidh eagar cothrománach den sórt sin ann: {1,"",3,"",5,"","","","","","",12,"","",""}

2 Cuid:

ROW()-3

СТРОКА()-3

Seo an fheidhm ROW Feidhmíonn (LÍNE) mar chuntar breise. Ós rud é go ndéantar an fhoirmle a chóipeáil isteach i gcealla F4:F9, déanaimid an uimhir a dhealú 3 ó thoradh feidhm chun luach a fháil 1 sa chill F4 (líne 4, dealaigh 3) a fháil 2 sa chill F5 (líne 5, dealaigh 3) agus mar sin de.

3 Cuid:

SMALL(IF($F$2=B2:B16,ROW(C2:C16)-1,""),ROW()-3))

НАИМЕНЬШИЙ(ЕСЛИ($F$2=B2:B16;СТРОКА(C2:C16)-1;"");СТРОКА()-3))

Feidhm BHEAG Filleann (SMALL). n-ó an luach is lú san eagar sonraí. Is é ár gcás, cén seasamh (ó na cinn is lú) le filleadh a chinnfidh an fheidhm ROW (LÍNE) (féach Cuid 2). Mar sin, le haghaidh cille F4 fheidhm SMALL({eagar},1) Tuairisceáin 1-ú eilimint eagar (is lú) .i 1. Le haghaidh cille F5 Tuairisceáin 2-ú an eilimint is lú san eagar, .i 3, Etc.

4 Cuid:

INDEX($C$2:$C$16,SMALL(IF($F$2=B2:B16,ROW(C2:C16)-1,""),ROW()-3))

ИНДЕКС($C$2:$C$16;НАИМЕНЬШИЙ(ЕСЛИ($F$2=B2:B16;СТРОКА(C2:C16)-1;"");СТРОКА()-3))

Feidhm INNÉACS (INDEX) go simplí ar ais luach cille ar leith in eagar C2:C16. Le haghaidh cille F4 fheidhm INDEX($C$2:$C$16) fillfidh ApplesDo F5 fheidhm INDEX($C$2:$C$16) fillfidh Milseáin' agus mar sin de.

5 Cuid:

IFERROR()

ЕСЛИОШИБКА()

Ar deireadh, cuirimid an fhoirmle taobh istigh den fheidhm IFERROR (IFERROR), mar ní dócha go mbeidh tú sásta leis an teachtaireacht earráide #AT (#N/A) más lú líon na gcealla a ndéantar an fhoirmle a chóipeáil isteach iontu ná líon na luachanna dúblacha sa raon a bhfuiltear ag féachaint air.

Cuardach XNUMXD de réir ró agus colún aitheanta

Is éard atá i gceist le cuardach XNUMXD in Excel ná cuardach a dhéanamh ar luach de réir uimhir aithnid agus as a chéile. I bhfocail eile, tá luach na cille á bhaint agat ag crosbhealach ró agus colún ar leith.

Mar sin, déanaimis dul chuig ár mbord agus scríobh foirmle le feidhm VPR, a gheobhaidh eolas faoi chostas na liomóidí a dhíoltar i mí an Mhárta.

Tá roinnt bealaí ann chun cuardach XNUMXD a dhéanamh. Seiceáil na roghanna agus roghnaigh an ceann is fearr a oireann duit.

Feidhmeanna VLOOKUP agus MATCH

Is féidir leat a lán feidhmeanna a úsáid VPR (VLOOKUP) agus NÍOS NÍOS MÓ (MATCH) chun an luach a fháil ag crosbhealach na réimsí Название продукта (teaghrán) agus Mí (colún) den eagar atá i gceist:

=VLOOKUP("Lemons",$A$2:$I$9,MATCH("Mar",$A$1:$I$1,0),FALSE)

=ВПР("Lemons";$A$2:$I$9;ПОИСКПОЗ("Mar";$A$1:$I$1;0);ЛОЖЬ)

Is feidhm rialta í an fhoirmle thuas VPR, a lorgaíonn meaitseáil bheacht den luach “Lemons” i gcealla A2 trí A9. Ach ós rud é nach bhfuil a fhios agat cén colún ina bhfuil díolacháin mhí an Mhárta, ní bheidh tú in ann uimhir an cholúin a shocrú don argóint tríú feidhme. VPR. Ina áit sin, úsáidtear an fheidhm NÍOS NÍOS MÓchun an colún seo a shainiú.

MATCH("Mar",$A$1:$I$1,0)

ПОИСКПОЗ("Mar";$A$1:$I$1;0)

Aistrithe go teanga dhaonna, ciallaíonn an fhoirmle seo:

• Táimid ag lorg na carachtair “Mar” – argóint lookup_value (lookup_value);
• Breathnú i gcealla ó A1 go I1 – argóint cuardaigh_eagar (lookup_array);
• Meaitseáil bheacht ar ais – argóint cineál_meaitseála (match_type).

Ag baint úsáide as 0 sa tríú argóint, a deir tú feidhmeanna NÍOS NÍOS MÓ lorg an chéad luach a thagann go díreach leis an luach atá á lorg agat. Tá sé seo comhionann leis an luach FALSE (FALSE) don cheathrú argóint VPR.

Seo mar is féidir leat foirmle cuardaigh dhá-bhealach a chruthú in Excel, ar a dtugtar cuardach déthoiseach nó cuardach déthreo freisin.

Feidhm SUMPRODUCT

Feidhm SUMPRODUCT Tugann (SUMPRODUCT) suim tháirgí na n-eagair roghnaithe ar ais:

=SUMPRODUCT(($A$2:$A$9="Lemons")*($A$1:$I$1="Mar"),$A$2:$I$9)

=СУММПРОИЗВ(($A$2:$A$9="Lemons")*($A$1:$I$1="Mar");$A$2:$I$9)

Feidhmeanna INDEX agus MATCH

Sa chéad alt eile míneoidh mé na feidhmeanna seo go mion, mar sin faoi láthair ní féidir leat ach an fhoirmle seo a chóipeáil:

=INDEX($A$2:$I$9,MATCH("Lemons",$A$2:$A$9,0),MATCH("Mar",$A$1:$I$1,0))

=ИНДЕКС($A$2:$I$9;ПОИСКПОЗ("Lemons";$A$2:$A$9;0);ПОИСКПОЗ("Mar";$A$1:$I$1;0))

Raonta ainmnithe agus an t-oibreoir trasnaithe

Mura bhfuil na foirmlí casta Excel sin agat, b'fhéidir gur mhaith leat an bealach amhairc agus i gcuimhne seo:

1. Roghnaigh an tábla, oscail an cluaisín Foirmlí (Foirmlí) agus cliceáil Cruthaigh ón Roghnú (Cruthaigh ó roghnú).
2. Seiceáil na boscaí Sraith barr (ar an líne thuas) agus Colún ar chlé (sa cholún ar chlé). Sannfaidh Microsoft Excel ainmneacha chuig na raonta ó na luachanna sa tsraith uachtarach agus sa cholún ar chlé de do scarbhileog. Anois is féidir leat cuardach a dhéanamh ag baint úsáide as na hainmneacha seo go díreach gan foirmlí a chruthú.
3. In aon chill fholamh, scríobh = row_name column_name, mar shampla mar seo:

   = Lemons Mar

   … nó a mhalairt:

   = Mar Lemons

   Cuimhnigh go gcaithfidh ainmneacha na sraithe agus na gcolún a bheith scartha le spás, a oibríonn sa chás seo cosúil leis an oibreoir trasnaithe.

Nuair a chuireann tú ainm isteach, taispeánfaidh Microsoft Excel leid uirlisí le liosta ainmneacha meaitseála, díreach cosúil le nuair a chuireann tú foirmle isteach.

4. Preas Iontráil agus seiceáil an toradh

Go ginearálta, cibé ceann de na modhanna thuas a roghnaíonn tú, beidh toradh cuardaigh déthoiseach mar an gcéanna:

Ag baint úsáide as VLOOKUPanna iolracha in aon fhoirmle amháin

Tarlaíonn sé nach bhfuil colún amháin i gcoiteann ag an bpríomhthábla agus ag an tábla cuardaigh, agus cuireann sé seo cosc ​​ort an gnáthfheidhm a úsáid VPR. Mar sin féin, tá tábla eile ann nach bhfuil an fhaisnéis a bhfuil suim againn ann, ach a bhfuil colún coitianta ann leis an bpríomhthábla agus an tábla cuardaigh.

A ligean ar ghlacadh le breathnú ar an sampla seo a leanas. Tá Príomhthábla againn le colún SKU (nua), áit ar mhaith leat colún a chur leis na praghsanna comhfhreagracha ó tábla eile. Ina theannta sin, tá 2 tábla cuardaigh againn. Tá uimhreacha nuashonraithe sa chéad cheann (Tábla Amharcála 1). SKU (nua) agus ainmneacha táirgí, agus an dara ceann (Tábla amhairc 2) – ainmneacha táirgí agus seanuimhreacha SKU (sean).

Chun praghsanna ón dara tábla cuardaigh a chur leis an bpríomhthábla, ní mór duit gníomh ar a dtugtar dúbailte a dhéanamh VPR nó neadaithe VPR.

1. Scríobh feidhm VPR, a aimsíonn ainm an táirge sa tábla Tábla cuardaigh 1ag baint úsáide as SKU, mar an luach inmhianaithe:

   =VLOOKUP(A2,New_SKU,2,FALSE)

   =ВПР(A2;New_SKU;2;ЛОЖЬ)

   anseo Nua_SKU – raon ainmnithe $A:$B sa tábla Tábla cuardaigh 1, 2 – seo colún B, ina bhfuil ainmneacha na n-earraí (féach an pictiúr thuas)

2. Scríobh foirmle chun praghsanna a chur isteach ó thábla Tábla cuardaigh 2 bunaithe ar ainmneacha táirgí aitheanta. Chun seo a dhéanamh, greamaigh an fhoirmle a chruthaigh tú níos luaithe mar luach cuardaigh na feidhme nua VPR:

   =VLOOKUP(VLOOKUP(A2,New_SKU,2,FALSE),Price,3,FALSE)

   =ВПР(ВПР(A2;New_SKU;2;ЛОЖЬ);Price;3;ЛОЖЬ)

   anseo Praghas – raon ainmnithe $ A: $ C. sa tábla Tábla cuardaigh 2, 3 is colún C ina bhfuil praghsanna.

Taispeánann an figiúr thíos an toradh a thug an fhoirmle a chruthaigh muid:

Ionadú dinimiciúil sonraí ó tháblaí éagsúla ag baint úsáide as VLOOKUP agus INDIRECT

Ar dtús, soiléirímid cad atá i gceist againn leis an nath “Ionadú dinimiciúil sonraí ó tháblaí éagsúla” chun a chinntiú go dtuigeann muid a chéile i gceart.

Tá cásanna ann nuair a bhíonn roinnt bileoga ann le sonraí den fhormáid chéanna, agus is gá an fhaisnéis riachtanach a bhaint as bileog áirithe, ag brath ar an luach a chuirtear isteach i gcill áirithe. Sílim go bhfuil sé níos éasca é seo a mhíniú le sampla.

Samhlaigh go bhfuil tuarascálacha díolacháin agat do roinnt réigiún leis na táirgí céanna agus san fhormáid chéanna. Ba mhaith leat figiúirí díolacháin a fháil do réigiún ar leith:

Mura bhfuil ach dhá thuarascáil den sórt sin agat, is féidir leat foirmle simplí náireach a úsáid le feidhmeanna VPR и IF (IF) chun an tuairisc atá uait le cuardach a roghnú:

=VLOOKUP($D$2,IF($D3="FL",FL_Sales,CA_Sales),2,FALSE)

=ВПР($D$2;ЕСЛИ($D3="FL";FL_Sales;CA_Sales);2;ЛОЖЬ)

I gcás:

• $D$2 is cill é ina bhfuil ainm an táirge. Tabhair faoi deara go n-úsáidimid dearbhthagairtí anseo chun an luach cuardaigh a sheachaint agus an fhoirmle á chóipeáil go cealla eile.
• $ D3 is cill í a bhfuil ainm an réigiúin uirthi. Táimid ag baint úsáide as tagairt colún absalóideach agus tagairt as a chéile coibhneasta toisc go bhfuil sé beartaithe againn an fhoirmle a chóipeáil go cealla eile sa cholún céanna.
• FL_Sales и CA_Díolacháin – ainmneacha na dtáblaí (nó na raonta ainmnithe) ina bhfuil na tuarascálacha díolacháin comhfhreagracha. Is féidir leat, ar ndóigh, na gnáthainmneacha bileog agus na tagairtí raon cille a úsáid, mar shampla 'Bileog FL'!$A$3:$B$10, ach tá raonta ainmnithe i bhfad níos áisiúla.

Mar sin féin, nuair a bhíonn go leor táblaí den sórt sin, an fheidhm IF nach é an réiteach is fearr. Ina áit sin, is féidir leat an fheidhm a úsáid INDIRECT (INDIRECT) chun an raon cuardaigh atá uait a thabhairt ar ais.

Mar is eol duit is dócha, an fheidhm INDIRECT a úsáidtear chun nasc a thug teaghrán téacs a thabhairt ar ais, agus sin é go díreach atá ag teastáil uainn anois. Mar sin, in ionad go dána sa fhoirmle thuas an abairt leis an bhfeidhm IF chun nasc a dhéanamh le feidhm INDIRECT. Seo meascán VPR и INDIRECT oibríonn go hiontach le:

=VLOOKUP($D$2,INDIRECT($D3&"_Sales"),2,FALSE)

=ВПР($D$2;ДВССЫЛ($D3&"_Sales");2;ЛОЖЬ)

I gcás:

• $D$2 – is cill í seo a bhfuil ainm an táirge uirthi, níl aon athrú uirthi mar gheall ar an nasc iomlán.
• $ D3 an chill ina bhfuil an chéad chuid d'ainm an réigiúin. In ár sampla, seo FL.
• _Díolacháin – an chuid choiteann d'ainm na raonta nó na táblaí ainmnithe go léir. Nuair a chuirtear é in éineacht leis an luach i gcill D3, cruthaíonn sé an t-ainm láncháilithe don raon riachtanach. Seo thíos roinnt sonraí dóibh siúd atá nua san fheidhm INDIRECT.

Conas a oibríonn INDIRECT agus VLOOKUP

Ar dtús, lig dom comhréir na feidhme a chur i gcuimhne duit INDIRECT (INDIRECT):

INDIRECT(ref_text,[a1])

ДВССЫЛ(ссылка_на_текст;[a1])

Is féidir tagairt cille (stíl A1 nó R1C1), ainm raoin, nó teaghrán téacs a bheith sa chéad argóint. Socraíonn an dara argóint cén stíl naisc atá sa chéad argóint:

• A1má tá an argóint CÓD FÍOR (TRUE) nó nach bhfuil sonraithe;
• R1C1, Má FAS E (FALSE).

Is é ár gcás, tá an stíl ag an nasc A1, ionas gur féidir leat an dara argóint a fhágáil amach agus díriú ar an gcéad cheann.

Mar sin déanaimis filleadh ar ár dtuarascálacha díolacháin. Más cuimhin leat, is tábla ar leith é gach tuairisc agus é suite ar bhileog ar leith. Chun go n-oibreoidh an fhoirmle i gceart, ní mór duit do tháblaí (nó raonta) a ainmniú, agus ní mór comhpháirt a bheith ag gach ainm. Mar shampla, mar seo: CA_Díolacháin, FL_ Díolacháin, TX_Díolacháin agus mar sin de. Mar a fheiceann tú, tá “_Sales” i láthair sna hainmneacha go léir.

Feidhm INDIRECT nascann sé an luach i gcolún D agus an teaghrán téacs “_Sales”, rud a insíonn VPR cén tábla le cuardach. Má tá an luach “FL” i gcill D3, déanfaidh an fhoirmle cuardach ar an tábla FL_ Díolacháin, más “CA” – sa tábla CA_Díolacháin agus mar sin de.

Toradh na bhfeidhmeanna VPR и INDIRECT beidh an méid seo a leanas:

Má tá na sonraí suite i leabhair Excel éagsúla, ansin ní mór duit ainm an leabhair a chur leis roimh an raon ainmnithe, mar shampla:

=VLOOKUP($D$2,INDIRECT($D3&"Workbook1!_Sales"),2,FALSE)

=ВПР($D$2;ДВССЫЛ($D3&"Workbook1!_Sales");2;ЛОЖЬ)

Má tá an fheidhm INDIRECT tagairt do leabhar oibre eile, caithfidh an leabhar oibre sin a bheith oscailte. Má tá sé dúnta, tuairisceoidh an fheidhm earráid. #REF! (#SSYL!).