Eisimpleirean VLOOKUP adhartach: Rannsachadh Ioma-shlatan-tomhais

Anns an dàrna pàirt den oideachadh againn air a’ ghnìomh VPR (VLOOKUP) ann an Excel, nì sinn sgrùdadh air beagan eisimpleirean a chuidicheas tu gus a h-uile cumhachd a stiùireadh VPR gus na gnìomhan Excel as adhartaiche fhuasgladh. Tha na h-eisimpleirean a’ gabhail ris gu bheil eòlas bunaiteach agad mu thràth air mar a tha am feart seo ag obair. Mura h-eil, is dòcha gum biodh ùidh agad tòiseachadh leis a’ chiad phàirt den oideachadh seo, a mhìnicheas an co-chòrdadh agus cleachdadh bunaiteach. VPR. Uill, leig dhuinn tòiseachadh.

• Rannsaich ann an Excel a rèir grunn shlatan-tomhais
• Bidh sinn a 'toirt a-mach na luachan 2nd, 3rd, msaa a' cleachdadh VLOOKUP
• Faigh air ais a h-uile ath-aithris den luach a tha thu ag iarraidh
• Rannsachadh XNUMXD a rèir sreath agus colbh aithnichte
• A’ cleachdadh ioma VLOOKUP ann an aon fhoirmle
• Ionadachadh fiùghantach dàta bho chlàran eadar-dhealaichte

Rannsaich ann an Excel a rèir grunn shlatan-tomhais

Dleastanas VPR ann an Excel na inneal fìor chumhachdach airson a bhith a’ dèanamh rannsachaidhean airson luach sònraichte ann an stòr-dàta. Ach, tha cuingealachadh mòr ann - tha a cho-chòrdadh a 'toirt cothrom dhut dìreach aon luach a lorg. Dè ma tha thu airson sgrùdadh a dhèanamh le iomadh suidheachadh? Lorgaidh tu am fuasgladh gu h-ìosal.

Eisimpleir 1: Rannsaich a rèir 2 shlatan-tomhais eadar-dhealaichte

Seach gu bheil liosta òrdughan againn agus tha sinn airson a lorg Meud bathair (Qty.), Stèidhichte air dà shlatan-tomhais - Ainm neach-dèiligidh (Customer) и Ainm toraidh (Bathar). Tha a 'chùis iom-fhillte leis gu bheil gach ceannaiche air grunn sheòrsaichean bathair òrdachadh, mar a chithear bhon chlàr gu h-ìosal:

gnìomh cunbhalach VPR chan obraich e san t-suidheachadh seo oir tillidh e a’ chiad luach a lorgas e a tha a rèir an luach sgrùdaidh a chaidh a thoirt seachad. Mar eisimpleir, ma tha thu airson faighinn a-mach dè an ìre de nì Sweets'air òrdachadh leis a’ cheannaiche Jeremy Hill, sgrìobh am foirmle a leanas:

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

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

- tillidh am foirmle seo an toradh 15a fhreagras air an toradh Ùbhlan, oir is e a 'chiad luach a tha co-ionnan.

Tha dòigh-obrach sìmplidh ann - cruthaich colbh a bharrachd anns am bi thu a ’cothlamadh a h-uile slat-tomhais a tha thu ag iarraidh. Anns an eisimpleir againn, is iad seo na colbhan Ainm neach-dèiligidh (Customer) и Ainm toraidh (Bathar). Na dì-chuimhnich gum feum an colbh co-aonaichte a bhith an-còmhnaidh mar an colbh as fhaide air falbh san raon sgrùdaidh, oir is e an colbh air an taobh chlì a tha an gnìomh VPR coimhead suas nuair a tha thu a’ coimhead airson luach.

Mar sin, cuiridh tu colbh taiceil ris a’ chlàr agus dèan lethbhreac den fhoirmle a leanas thairis air na ceallan gu lèir aige: =B2&C2. Ma tha thu airson gum bi an sreang nas leughaidh, faodaidh tu na luachan còmhla a sgaradh le àite: =B2&» «&C2. Às deidh sin, faodaidh tu am foirmle a leanas a chleachdadh:

=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àit a bheil an cealla B1 anns a bheil luach co-cheangail na h-argamaid lorg_luach (lookup_value) agus 4 — Argumaid col_index_num (column_number), ie àireamh a' cholbh anns a bheil an dàta ri fhaighinn air ais.

Eisimpleir 2: VLOOKUP a rèir dà shlatan-tomhais le clàr ga fhaicinn air duilleag eile

Ma dh'fheumas tu am prìomh chlàr (Prìomh chlàr) ùrachadh le bhith a 'cur dàta bhon dàrna clàr (clàr sgrùdaidh), a tha suidhichte air duilleag eile no ann an leabhar-obrach Excel eile, faodaidh tu an luach a tha thu ag iarraidh a chruinneachadh gu dìreach anns an fhoirmle a chuir thu a-steach. a-steach don phrìomh bhòrd.

Mar a bha san eisimpleir roimhe, bidh feum agad air colbh taiceil sa chlàr Lorg leis na luachan aonaichte. Feumaidh an colbh seo a bhith mar an colbh as fhaide air chlì san raon sgrùdaidh.

Mar sin am foirmle le VPR dh'fhaodadh a bhith mar seo:

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

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

An seo, tha ainmean teachdaiche agus ainmean toraidh ann an colbhan B agus C, fa leth, agus an ceangal Òrdughan!$A&$2:$D$2 a’ mìneachadh clàr airson coimhead suas air duilleag eile.

Gus am foirmle a dhèanamh nas leughaidh, faodaidh tu ainm a thoirt don raon seallaidh, agus an uairsin bidh am foirmle a’ coimhead tòrr nas sìmplidh:

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

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

Gus an obraich am foirmle, feumaidh na luachan anns a’ cholbh as fhaide air chlì den chlàr air a bheil thu a’ coimhead a bhith air an cur còmhla san aon dòigh ’s a tha iad anns na slatan-tomhais sgrùdaidh. Anns an fhigear gu h-àrd, chuir sinn còmhla na luachan chuir u2bu2band àite eatarra, san aon dòigh a dh’ fheumas tu a dhèanamh anns a ’chiad argamaid den ghnìomh (BXNUMX &“” & CXNUMX).

Cuimhnich! Dleastanas VPR cuibhrichte gu 255 caractar, chan urrainn dha luach a lorg a tha nas fhaide na 255 caractar a dh’ fhaid. Cùm seo san amharc agus dèan cinnteach nach bi fad an luach a tha thu ag iarraidh nas àirde na a’ chrìoch seo.

Tha mi ag aontachadh nach e colbh taiceil am fuasgladh as eireachdail agus nach eil an-còmhnaidh iomchaidh a th’ ann. Faodaidh tu an aon rud a dhèanamh às aonais colbh an neach-cuideachaidh, ach dh'fheumadh sin foirmle mòran nas iom-fhillte le measgachadh de ghnìomhan INDEX (INDEX) agus MATCH (TUILLEADH DHUILLEAG).

Bidh sinn a 'toirt a-mach na luachan 2nd, 3rd, msaa a' cleachdadh VLOOKUP

Tha fios agad air sin mu thràth VPR chan urrainn dha ach aon luach maidsidh a thilleadh, nas mionaidiche, a’ chiad fhear a lorgar. Ach dè ma thèid an luach seo ath-aithris grunn thursan anns an raon a chaidh fhaicinn, agus gu bheil thu airson an 2na no an 3mh dhiubh a thoirt a-mach? Dè ma tha a h-uile luach? Tha coltas gu bheil an duilgheadas iom-fhillte, ach tha am fuasgladh ann!

Seach gu bheil aon cholbh den chlàr a 'toirt a-steach ainmean an luchd-ceannach (Ainm Luchd-cleachdaidh), agus anns a' cholbh eile tha na stuthan (Bathar) a cheannaich iad. Feuchaidh sinn ris an 2na, an 3mh agus an 4mh rud a lorg a cheannaich neach-ceannach sònraichte.

Is e an dòigh as fhasa colbh taiceil a chur ris ron cholbh ainm neach-ceannach agus lìon e le ainmean teachdaiche le àireamh ath-aithris gach ainm, mar eisimpleir, Iain Doe1, Iain Doe2 etc. Nì sinn an cleas le àireamhachadh a' cleachdadh a' ghnìomh COUNTIF (COUNTIF), leis gu bheil ainmean an luchd-ceannach ann an colbh B:

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

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

Às deidh sin faodaidh tu an gnìomh àbhaisteach a chleachdadh VPRgus an òrdugh riatanach a lorg. Mar eisimpleir:

• lorg 2-th nì air òrdachadh leis an neach-ceannach Dan Brown:

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

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

• lorg 3-th nì air òrdachadh leis an neach-ceannach Dan Brown:

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

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

Gu dearbh, faodaidh tu iomradh cealla a chuir a-steach mar an luach sgrùdaidh an àite teacsa, mar a chithear san fhigear a leanas:

Ma tha thu dìreach a 'coimhead airson 2an ath-aithris, faodaidh tu a dhèanamh às aonais a’ cholbh taice le bhith a’ cruthachadh foirmle nas iom-fhillte:

=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;ИСТИНА);"")

Anns an fhoirmle seo:

• $F$2 - cealla anns a bheil ainm a 'cheannaiche (chan eil e air atharrachadh, thoir an aire - tha an ceangal iomlan);
• $ B $ - colbh ainm neach-ceannach;
• Table4 - Do bhòrd (faodaidh an t-àite seo a bhith na raon cunbhalach cuideachd);
• $ C16 - cealla crìochnachaidh do bhùird no do raon.

Chan fhaigh am foirmle seo ach an dàrna luach co-ionnan. Ma dh'fheumas tu na h-ath-aithris a tha air fhàgail a thoirt a-mach, cleachd am fuasgladh roimhe.

Ma tha feum agad air liosta de na geamannan uile - an gnìomh VPR chan e neach-cuideachaidh a tha seo, oir chan eil e a 'tilleadh ach aon luach aig aon àm - ùine. Ach tha gnìomh aig Excel INDEX (INDEX), as urrainn dèiligeadh ris a’ ghnìomh seo gu furasta. Cò ris a bhios foirmle mar seo coltach, ionnsaichidh tu san eisimpleir a leanas.

Faigh air ais a h-uile ath-aithris den luach a tha thu ag iarraidh

Mar a chaidh ainmeachadh gu h-àrd VPR chan urrainn dha a h-uile luach dùblaichte a thoirt a-mach às an raon a chaidh a sganadh. Gus seo a dhèanamh, feumaidh tu foirmle beagan nas iom-fhillte, air a dhèanamh suas de ghrunn ghnìomhan Excel, leithid INDEX (INDEX), SMALL (BEAG) agus Row (LINE)

Mar eisimpleir, lorgaidh am foirmle gu h-ìosal a h-uile ath-aithris den luach bho chealla F2 anns an raon B2: B16 agus tillidh e an toradh bho na h-aon sreathan ann an colbh 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 a-steach am foirmle rèite seo a-steach do ghrunn cheallan faisg air làimh, leithid na ceallan F4: F8mar a chithear san dealbh gu h-ìosal. Feumaidh an àireamh de cheallan a bhith co-ionann no nas motha na an àireamh as motha de ath-aithris den luach a chaidh a sgrùdadh. Na dìochuimhnich briogadh Ctrl + Shift + Cuir a-steachgus am foirmle array a chuir a-steach gu ceart.

Ma tha ùidh agad ann a bhith a 'tuigsinn mar a tha e ag obair, leig dhuinn dàibheadh ​​​​a-steach gu mion-fhiosrachadh na foirmle beagan:

Pàirt 1:

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

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

$F$2=B2:B16 - dèan coimeas eadar an luach ann an cealla F2 le gach aon de na luachan anns an raon B2: B16. Ma lorgar maids, an uairsin an abairt STRING(C2:C16)-1 tilleadh àireamh na loidhne fhreagarrach (luach -1 a’ leigeil leat gun a bhith a’ toirt a-steach an loidhne cinn). Mura h-eil maidsean ann, an gnìomh IF (IF) a 'tilleadh sreang falamh.

Toradh gnìomh IF (IF) bidh leithid de raon còmhnard ann: {1,"",3,"",5,"","","","","","",12,"","",""}

Pàirt 2:

ROW()-3

СТРОКА()-3

Seo an gnìomh Row (LINE) ag obair mar chunntair a bharrachd. Leis gu bheil am foirmle air a lethbhreacadh a-steach do cheallan F4: F9, bidh sinn a’ toirt air falbh an àireamh 3 bho thoradh gnìomh gus luach fhaighinn 1 anns a' chill F4 (loidhne 4, thoir air falbh 3) gus faighinn 2 anns a' chill F5 (loidhne 5, thoir air falbh 3) agus mar sin air adhart.

Pàirt 3:

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

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

Dleastanas SMALL (SMALL) a’ tilleadh n-ò an luach as lugha anns an raon dàta. Anns a 'chùis againn, tha an suidheachadh (bhon ìre as lugha) air ais air a dhearbhadh leis a' ghnìomh Row (LINE) (faic Pàirt 2). Mar sin, airson cealla F4 ghnìomh SMALL ({eagrachadh},1) a 'tilleadh 1-th (as lugha) array element, ie 1. Airson cealla F5 a 'tilleadh 2-th an eileamaid as lugha san raon, .i 3Msaa

Pàirt 4:

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))

Dleastanas INDEX (INDEX) dìreach a’ tilleadh luach cealla sònraichte ann an sreath C2:C16. Airson cealla F4 ghnìomh INDEX($C$2:$C$16) tillidh e Ùbhlanairson F5 ghnìomh INDEX($C$2:$C$16) tillidh e Sweets' agus mar sin air adhart.

Pàirt 5:

IFERROR()

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

Mu dheireadh, chuir sinn am foirmle taobh a-staigh an gnìomh IFERROR (IFERROR), oir chan eil coltas gu bheil thu toilichte leis an teachdaireachd mhearachd #AT (#N/A) ma tha an àireamh de cheallan anns a bheil am foirmle air a lethbhreacadh nas lugha na an àireamh de luachan dùblaichte san raon a thathar a’ coimhead.

Rannsachadh XNUMXD a rèir sreath agus colbh aithnichte

Tha a bhith a’ dèanamh sgrùdadh XNUMXD ann an Excel a’ toirt a-steach a bhith a’ lorg luach le sreath aithnichte agus àireamh colbh. Ann am faclan eile, tha thu a’ toirt a-mach luach na cealla aig crois-rathaid sreath is colbh sònraichte.

Mar sin, tionndaidhidh sinn chun bhòrd againn agus sgrìobh foirmle le gnìomh VPR, a gheibh fiosrachadh mu chosgais lemonan a chaidh a reic sa Mhàrt.

Tha grunn dhòighean ann sgrùdadh XNUMXD a dhèanamh. Thoir sùil air na roghainnean agus tagh am fear as freagarraiche dhut.

gnìomhan VLOOKUP agus MATCH

Faodaidh tu dòrlach de ghnìomhan a chleachdadh VPR (VLOOKUP) agus TUILLEADH DHUILLEAG (MATCH) gus an luach a lorg aig crois-rathaid nan raointean Ainm toraidh (sreang) agus Mìos (colbh) den raon a tha fo cheist:

=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);ЛОЖЬ)

Tha am foirmle gu h-àrd na ghnìomh cunbhalach VPR, a tha a’ coimhead airson maids ceart den luach “Lemons” ann an ceallan A2 tro A9. Ach leis nach eil fios agad dè an colbh anns a bheil reic a’ Mhàirt, cha bhith e comasach dhut àireamh a’ cholbh a shuidheachadh airson an treas argamaid gnìomh. VPR. An àite sin, tha an gnìomh air a chleachdadh TUILLEADH DHUILLEAGgus an colbh seo a mhìneachadh.

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

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

Air eadar-theangachadh gu cànan daonna, tha am foirmle seo a’ ciallachadh:

• Tha sinn a 'coimhead airson na caractaran "Mar" - argamaid lorg_luach (lookup_value);
• A’ coimhead ann an ceallan bho A1 gu I1 – argamaid lorg_eagar (lookup_array);
• A 'tilleadh maids cheart - argamaid seòrsa_maids (match_type).

cleachdadh 0 anns an treas argamaid, tha thu ag ràdh gnìomhan TUILLEADH DHUILLEAG coimhead airson a’ chiad luach a tha dìreach a rèir an luach a tha thu a’ sireadh. Tha seo co-ionann ris an luach CEÀRR (FALSE) airson a’ cheathramh argamaid VPR.

Seo mar as urrainn dhut foirmle sgrùdaidh dà-shligheach a chruthachadh ann an Excel, ris an canar cuideachd sgrùdadh dà-mheudach no sgrùdadh dà-thaobhach.

SUMPRODUCT gnìomh

Dleastanas RO-RÀDH (SUMPRODUCT) a 'tilleadh an t-suim de thoraidhean nan arrays taghte:

=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)

gnìomhan INDEX agus MATCH

Anns an ath artaigil mìnichidh mi na gnìomhan sin gu mionaideach, agus mar sin airson a-nis faodaidh tu dìreach am foirmle seo a chopaigeadh:

=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))

Raointean ainmichte agus an gnìomhaiche eadar-ghearradh

Mura h-eil thu anns na foirmlean Excel iom-fhillte sin, is dòcha gum bu toil leat an dòigh lèirsinneach is cuimhneachail seo:

1. Tagh an clàr, fosgail an taba Foirmlean (Foirmlean) agus cliog Cruthaich bho Taghadh (Cruthaich bhon taghadh).
2. Thoir sùil air na bogsaichean Sreath àrd (air an loidhne gu h-àrd) agus Colbh air chlì (sa cholbh air an taobh chlì). Sònraichidh Microsoft Excel ainmean gu na raointean bho na luachan san t-sreath gu h-àrd agus colbh clì na cliath-dhuilleag agad. A-nis faodaidh tu na h-ainmean sin a chleachdadh gu dìreach gun a bhith a’ cruthachadh foirmlean.
3. Ann an cealla falamh sam bith, sgrìobh = row_name column_name, mar eisimpleir mar seo:

   = Lemons Mar

   … no a chaochladh:

   = Mar Lemons

   Cuimhnich gum feum na h-ainmean sreath agus colbh a bhith air an sgaradh le àite, a tha sa chùis seo ag obair mar ghnìomhaiche eadar-ghearraidh.

Nuair a chuireas tu a-steach ainm, seallaidh Microsoft Excel inneal le liosta de dh’ ainmean a tha a’ maidseadh, dìreach mar nuair a chuireas tu a-steach foirmle.

4. Meadhanan Cuir a-steach agus thoir sùil air an toradh

San fharsaingeachd, ge bith dè na dòighean gu h-àrd a thaghas tu, bidh toradh sgrùdadh dà-mheudach mar an ceudna:

A’ cleachdadh ioma VLOOKUP ann an aon fhoirmle

Tha e a’ tachairt nach eil aon cholbh aig a’ phrìomh bhòrd agus an clàr sgrùdaidh mar as trice, agus tha seo a’ cur casg ort bho bhith a’ cleachdadh a’ ghnìomh àbhaisteach VPR. Ach, tha clàr eile ann anns nach eil am fiosrachadh anns a bheil ùidh againn, ach aig a bheil colbh cumanta leis a’ phrìomh chlàr agus an clàr sgrùdaidh.

Bheir sinn sùil air an eisimpleir a leanas. Tha prìomh bhòrd againn le colbh SKU (ùr), far a bheil thu airson colbh a chuir ris leis na prìsean co-fhreagarrach bho bhòrd eile. A bharrachd air an sin, tha 2 chlàr sgrùdaidh againn. Anns a’ chiad fhear (clàr sgrùdaidh 1) tha àireamhan ùraichte SKU (ùr) agus ainmean toraidh, agus an dàrna fear (Clàr Sgrùdaidh 2) – ainmean toraidh agus seann àireamhan SKU (seann).

Gus prìsean a chuir bhon dàrna clàr sgrùdaidh ris a’ phrìomh chlàr, feumaidh tu gnìomh ris an canar dùbailte a dhèanamh VPR no neadachadh VPR.

1. Sgrìobh gnìomh VPR, a lorgas ainm an toraidh sa chlàr Clàr lorg 1cleachdadh SKU, mar an luach a thathar ag iarraidh:

   =VLOOKUP(A2,New_SKU,2,FALSE)

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

   seo Ùr_SKU - raon ainmichte $A:$B anns a' bhòrd Clàr lorg 1, 2 - is e seo colbh B, anns a bheil ainmean a’ bhathair (faic an dealbh gu h-àrd)

2. Sgrìobh foirmle airson prìsean a chuir a-steach bho bhòrd Clàr lorg 2 stèidhichte air ainmean toraidh ainmeil. Gus seo a dhèanamh, cuir a-steach am foirmle a chruthaich thu na bu thràithe mar luach sgrùdaidh airson a’ ghnìomh ùr VPR:

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

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

   seo Price - raon ainmichte $A:$C anns a' bhòrd Clàr lorg 2, 3 tha colbh C anns a bheil prìsean.

Tha an dealbh gu h-ìosal a’ sealltainn an toradh a thàinig air ais leis an fhoirmle a chruthaich sinn:

Ionadachadh fiùghantach dàta bho chlàran eadar-dhealaichte a’ cleachdadh VLOOKUP agus INDIRECT

An toiseach, leig dhuinn soilleireachadh dè tha sinn a’ ciallachadh leis an abairt “Ionadachadh dinamic dàta bho chlàran eadar-dhealaichte” gus dèanamh cinnteach gu bheil sinn a’ tuigsinn a chèile gu ceart.

Tha suidheachaidhean ann nuair a tha grunn dhuilleagan ann le dàta den aon chruth, agus feumar am fiosrachadh riatanach a thoirt a-mach à duilleag sònraichte, a rèir an luach a tha air a chuir a-steach ann an cealla sònraichte. Tha mi a’ smaoineachadh gu bheil e nas fhasa seo a mhìneachadh le eisimpleir.

Smaoinich gu bheil aithisgean reic agad airson grunn roinnean leis na h-aon thoraidhean agus san aon chruth. Tha thu airson figearan reic a lorg airson sgìre shònraichte:

Mura h-eil agad ach dà aithisg mar sin, faodaidh tu foirmle gu math sìmplidh a chleachdadh le gnìomhan VPR и IF (IF) gus an aithisg a tha thu ag iarraidh a thaghadh airson a sgrùdadh:

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

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

Far a bheil:

• $D$2 'S e cealla anns a bheil ainm an toraidh. Thoir an aire gu bheil sinn a’ cleachdadh iomraidhean iomlan an seo gus nach atharraich sinn an luach lorg nuair a bhios sinn a’ dèanamh lethbhreac den fhoirmle gu ceallan eile.
• $D3 'S e cealla le ainm na sgìre a th' ann. Tha sinn a’ cleachdadh iomradh colbh iomlan agus iomradh sreath càirdeach oir tha sinn an dùil an fhoirmle a chopaigeadh gu ceallan eile san aon cholbh.
• FL_Sales и CA_reic - ainmean chlàran (no raointean ainmichte) anns a bheil na h-aithisgean reic co-fhreagarrach. Faodaidh tu, gu dearbh, na h-ainmean duilleag àbhaisteach agus iomraidhean raon cealla a chleachdadh, mar eisimpleir 'Bileag FL'!$A$3:$B$10, ach tha raointean ainmichte mòran nas goireasaiche.

Ach, nuair a tha mòran chlàran mar sin, an gnìomh IF chan e am fuasgladh as fheàrr. An àite sin, faodaidh tu an gnìomh a chleachdadh URNUIGH (INDIRECT) gus an raon sgrùdaidh a tha thu ag iarraidh a thilleadh.

Mar is dòcha gu bheil fios agad, an gnìomh URNUIGH air a chleachdadh gus ceangal a thug sreang teacsa a thilleadh, is e sin dìreach a tha a dhìth oirnn an-dràsta. Mar sin, cuir an gnìomh an gnìomh gu dàna anns an fhoirmle gu h-àrd IF gus ceangal a dhèanamh ri gnìomh URNUIGH. Seo measgachadh VPR и URNUIGH ag obair gu math le:

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

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

Far a bheil:

• $D$2 - is e cealla a tha seo le ainm an toraidh, chan eil e air atharrachadh mar thoradh air a’ cheangal iomlan.
• $D3 an cealla anns a bheil a' chiad phàirt de dh'ainm na roinne. Anns an eisimpleir againn, seo FL.
• _ Reic – am pàirt chumanta de dh’ainm gach raon no clàr ainmichte. Nuair a thèid a chur còmhla ris an luach ann an ceal D3, bidh e a’ cruthachadh ainm làn-theisteanas an raoin a tha a dhìth. Gu h-ìosal tha beagan fiosrachaidh dhaibhsan a tha ùr don ghnìomh URNUIGH.

Mar a tha INDIRECT agus VLOOKUP ag obair

An toiseach, leig dhomh do chuimhneachadh co-chòrdadh na gnìomh URNUIGH (INDIRECT):

INDIRECT(ref_text,[a1])

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

Faodaidh a’ chiad argamaid a bhith na iomradh cealla (stoidhle A1 no R1C1), ainm raon, no sreang teacsa. Tha an dàrna argamaid a’ dearbhadh dè an stoidhle ceangail a tha sa chiad argamaid:

• A1ma tha an argamaid CÒD TRUE (TRUE) no nach eil air a shònrachadh;
• R1C1, ma tha FAS E (FALSE).

Anns a 'chùis againn, tha an stoidhle aig a' cheangal A1, gus an urrainn dhut an dàrna argamaid fhàgail a-mach agus fòcas a chuir air a’ chiad fhear.

Mar sin rachamaid air ais gu na h-aithisgean reic againn. Ma chuimhnicheas tu, tha gach aithisg na bhòrd air leth suidhichte air duilleag air leth. Airson an fhoirmle obrachadh gu ceart, feumaidh tu na clàran (no na raointean) agad ainmeachadh, agus feumaidh pàirt cumanta a bhith aig gach ainm. Mar eisimpleir, mar seo: CA_reic, FL_ Reic, TX_reic agus mar sin air adhart. Mar a chì thu, tha “_Sales” an làthair anns a h-uile ainm.

Dleastanas URNUIGH a’ ceangal an luach ann an colbh D agus an sreang teacsa “_Sales”, mar sin ag innse VPR anns a bhòrd ri rannsachadh. Ma tha an luach “FL” ann an ceal D3, rannsaichidh am foirmle an clàr FL_ Reic, ma tha “CA” - anns a’ chlàr CA_reic agus mar sin air adhart.

Toradh nan gnìomhan VPR и URNUIGH bidh na leanas:

Ma tha an dàta suidhichte ann an diofar leabhraichean Excel, feumaidh tu ainm an leabhair a chur ris ron raon ainmichte, mar eisimpleir:

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

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

Ma tha an gnìomh URNUIGH a’ toirt iomradh air leabhar-obrach eile, feumaidh an leabhar-obrach sin a bhith fosgailte. Ma tha e dùinte, bheir an gnìomh cunntas air mearachd. #REF! (#SSYL!).