Contents
- Huli ma Excel ma nā pae koho he nui
- Wehe mākou i nā waiwai 2nd, 3rd, etc. me VLOOKUP
- E kiʻi i nā hana hou a pau o ka waiwai i makemake ʻia
- ʻimi ʻo XNUMXD e ka lālani a me ke kolamu i ʻike ʻia
- Ke hoʻohana nei i nā VLOOKUP he nui i hoʻokahi kumu
- Hoʻololi i ka ʻikepili mai nā papa like ʻole me ka hoʻohana ʻana iā VLOOKUP a me INDIRECT
Ma ka ʻāpana ʻelua o kā mākou kumu aʻo i ka hana ʻO VPR (VLOOKUP) ma Excel, e nānā mākou i kekahi mau hiʻohiʻona e kōkua iā ʻoe e kuhikuhi i ka mana āpau ʻO VPR e hoʻoponopono i nā hana Excel nui loa. Manaʻo nā laʻana ua loaʻa iā ʻoe kahi ʻike kumu o ka hana ʻana o kēia hiʻohiʻona. Inā ʻaʻole, makemake paha ʻoe e hoʻomaka me ka hapa mua o kēia kumu aʻo, e wehewehe ana i ka syntax a me ka hoʻohana kumu. ʻO VPR. ʻAe, e hoʻomaka kākou.
Huli ma Excel ma nā pae koho he nui
Launch ʻO VPR i Excel he mea hana ikaika loa no ka hana ʻana i nā ʻimi no kahi waiwai i loko o kahi waihona. Eia naʻe, aia kahi palena nui - hiki i kāna syntax ke ʻimi i hoʻokahi waiwai. He aha inā makemake ʻoe e ʻimi ma nā kūlana he nui? E ʻike ʻoe i ka hoʻonā ma lalo.
Laʻana 1: Huli ma 2 mau koina like ʻole
Inā paha he papa inoa o nā kauoha a makemake mākou e ʻimi Ka nui o ka waiwai (Qty.), e pili ana i ʻelua mau koho - Ka inoa o ka mea kūʻai aku (mea kūʻai aku) и Hua inoa (Huahana). Paʻakikī kēia mea ma ka ʻoiaʻiʻo ua kauoha kēlā me kēia mea kūʻai aku i nā ʻano waiwai, e like me ka ʻike ʻia mai ka papa ma lalo nei.
hana mau ʻO VPR ʻAʻole e hana i kēia hiʻohiʻona no ka mea e hoʻihoʻi ia i ka waiwai mua i ʻike ʻia e pili ana i ka waiwai huli i hāʻawi ʻia. No ka laʻana, inā makemake ʻoe e ʻike i ka nui o kahi mea Nā meaʻono'kauoha ʻia e ka mea kūʻai mai ʻO Jeremy Hill, e kākau i ke ʻano penei:
=VLOOKUP(B1,$A$5:$C$14,3,FALSE)
=ВПР(B1;$A$5:$C$14;3;ЛОЖЬ)
- e hoʻihoʻi kēia kumu i ka hopena 15pili i ka huahana Mele, no ka mea, ʻo ia ka waiwai mua e pili ana.
Aia kahi hana maʻalahi - hana i kahi kolamu hou e hoʻohui ai i nā pae i makemake ʻia. I kā mākou laʻana, ʻo ia nā kolamu Ka inoa o ka mea kūʻai aku (mea kūʻai aku) и Hua inoa (Huahana). Mai poina ʻo ke kolamu i hoʻohui ʻia ʻo ia ke kolamu hema loa i ka pae ʻimi, no ka mea, ʻo ke kolamu hema ka hana. ʻO VPR nānā i ka ʻimi ʻana i kahi waiwai.
No laila, hoʻohui ʻoe i kahi kolamu kōkua i ka papaʻaina a kope i kēia ʻano kumu ma luna o kāna mau cell āpau: =B2&C2. Inā makemake ʻoe e ʻoi aku ka maʻalahi o ke kaula, hiki iā ʻoe ke hoʻokaʻawale i nā waiwai i hui pū ʻia me kahi ākea: =B2&» «&C2. Ma hope o kēlā, hiki iā ʻoe ke hoʻohana i kēia ʻano:
=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;ЛОЖЬ)
Aia i hea ke kelepona B1 loaʻa ka waiwai hoʻohui o ka hoʻopaʻapaʻa huli_waiwai (lookup_value) a 4 – Hoʻopaʻapaʻa helu_index_num (column_number), ʻo ia ka helu o ke kolamu i loaʻa ka ʻikepili e kiʻi ʻia.
Laʻana 2: VLOOKUP ma nā pae hoʻohālikelike ʻelua me ka ʻike ʻia ʻana o ka papa ma kahi pepa ʻē aʻe
Inā pono ʻoe e hoʻohou i ka papa nui (Main table) ma ka hoʻohui ʻana i ka ʻikepili mai ka papa ʻelua (Lookup table), aia ma kahi pepa ʻē aʻe a i ʻole ma kahi papa hana Excel ʻē aʻe, a laila hiki iā ʻoe ke hōʻiliʻili pololei i ka waiwai i makemake ʻia ma ke ʻano āu e hoʻokomo ai. iloko o ka papaaina nui.
E like me ka laʻana ma mua, pono ʻoe i kahi kolamu kōkua ma ka papa ʻo Lookup me nā waiwai i hui pū ʻia. Pono kēia kolamu ma ka ʻaoʻao hema o ka pae huli.
No laila ke kumu me ʻO VPR hiki ke like me kēia:
=VLOOKUP(B2&" "&C2,Orders!$A&$2:$D$2,4,FALSE)
=ВПР(B2&" "&C2;Orders!$A&$2:$D$2;4;ЛОЖЬ)
Eia, aia nā kolamu B a me C nā inoa mea kūʻai aku a me nā inoa huahana, kēlā me kēia, a me ka loulou Kauoha!$A&$2:$D$2 wehewehe i ka papaʻaina e nānā i luna ma kekahi pepa ʻē aʻe.
I mea e maʻalahi ai ka heluhelu ʻana i ke kumu, hiki iā ʻoe ke hāʻawi i ka inoa o ka ʻike, a laila e ʻoi aku ka maʻalahi o ke ʻano:
=VLOOKUP(B2&" "&C2,Orders,4,FALSE)
=ВПР(B2&" "&C2;Orders;4;ЛОЖЬ)
No ka hana ʻana o ke ʻano, pono e hui pū ʻia nā koina ma ka ʻaoʻao hema o ka papaʻaina āu e nānā nei ma ke ʻano like me nā koina hulina. Ma ke kiʻi ma luna, ua hui mākou i nā waiwai u2bu2band e kau i kahi ākea ma waena o lākou, ma ke ʻano like e pono ai ʻoe e hana i ka hoʻopaʻapaʻa mua o ka hana (BXNUMX& "" & CXNUMX).
E hoʻomanaʻo! Launch ʻO VPR i kaupalena 'ia i 255 huapalapala, 'a'ole hiki ke 'imi i ka waiwai i 'oi aku ma mua o 255 huapalapala ka lō'ihi. E hoʻomanaʻo i kēia a e hōʻoia i ka lōʻihi o ka waiwai i makemake ʻia ʻaʻole ma mua o kēia palena.
ʻAe wau ʻo ka hoʻohui ʻana i kahi kolamu kōkua ʻaʻole ia ka hopena nani loa a ʻaʻole ʻae ʻia i nā manawa a pau. Hiki iā ʻoe ke hana like me ka ʻole o ke kolamu kōkua, akā pono ia i kahi ʻano paʻakikī me ka hui pū ʻana o nā hana INDEX (INDEX) a ʻO MATCH (MORE EXPOSED).
Wehe mākou i nā waiwai 2nd, 3rd, etc. me VLOOKUP
Ua ʻike mua ʻoe i kēlā ʻO VPR hiki ke hoʻihoʻi i hoʻokahi waiwai like, ʻoi aku ka pololei, ʻo ka mea mua i loaʻa. Akā, pehea inā e hoʻonui pinepine ʻia kēia waiwai i ka papa i ʻike ʻia, a makemake ʻoe e unuhi i ka 2 a i ʻole 3 o lākou? Pehea inā loaʻa nā waiwai a pau? He paʻakikī ka pilikia, akā aia ka hopena!
Inā paha aia kekahi kolamu o ka papaʻaina i nā inoa o nā mea kūʻai aku (Inoa Mea kūʻai aku), a aia kekahi kolamu i nā huahana (Huahana) a lākou i kūʻai ai. E ho'āʻo kākou e ʻimi i nā mea 2, 3 a me 4 i kūʻai ʻia e kekahi mea kūʻai.
ʻO ke ala maʻalahi ke hoʻohui i kahi kolamu kōkua ma mua o ke kolamu inoa mea kūʻai a hoʻopiha iā ia me nā inoa mea kūʻai aku me ka helu hou ʻana o kēlā me kēia inoa, no ka laʻana, John Doe1, John Doe2 etc. E hana mākou i ka hana me ka helu ʻana me ka hoʻohana ʻana i ka hana KĀNĀWAI (COUNTIF), no ka mea, aia nā inoa mea kūʻai ma ka kolamu B:
=B2&COUNTIF($B$2:B2,B2)
=B2&СЧЁТЕСЛИ($B$2:B2;B2)
Ma hope o kēlā hiki iā ʻoe ke hoʻohana i ka hana maʻamau ʻO VPRe imi i ke kauoha i makemakeia. ʻo kahi laʻana:
- loaa 2-th mea i kauoha ʻia e ka mea kūʻai Dan Brown:
=VLOOKUP("Dan Brown2",$A$2:$C$16,3,FALSE)=ВПР("Dan Brown2";$A$2:$C$16;3;ЛОЖЬ) - loaa 3-th mea i kauoha ʻia e ka mea kūʻai Dan Brown:
=VLOOKUP("Dan Brown3",$A$2:$C$16,3,FALSE)=ВПР("Dan Brown3";$A$2:$C$16;3;ЛОЖЬ)
ʻO ka ʻoiaʻiʻo, hiki iā ʻoe ke hoʻokomo i kahi kuhikuhi kelepona ma ke ʻano he ʻimi hulina ma kahi o ka kikokikona, e like me ka hōʻike ʻana ma kēia kiʻi:
Inā ʻoe e ʻimi wale nei 2-e hana hou, hiki iā ʻoe ke hana me ka ʻole o ke kolamu kōkua ma ka hana ʻana i kahi ʻano paʻakikī:
=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;ИСТИНА);"")
Ma kēia ʻano kumu:
- $F$2 - he kelepona i loaʻa ka inoa o ka mea kūʻai aku (ʻaʻole i loli, e ʻoluʻolu e nānā - paʻa ka loulou);
- $ B $ – kolamu inoa mea kūʻai;
- Table4 - ʻO kāu papaʻaina (hiki i kēia wahi ke hana maʻamau);
- $C16 – ke kelepona hope o kāu papaʻaina a i ʻole ka laulā.
Loaʻa i kēia ʻano kumu ka waiwai like ʻelua. Inā pono ʻoe e unuhi i nā hana hou i koe, e hoʻohana i ka hopena mua.
Inā makemake ʻoe i kahi papa inoa o nā pāʻani āpau - ka hana ʻO VPR ʻAʻole kēia he kōkua, no ka mea, hoʻihoʻi wale ia i hoʻokahi waiwai i ka manawa - manawa. Akā he hana ko Excel INDEX (INDEX), hiki ke hoʻokō maʻalahi i kēia hana. Pehea ke ʻano o ia ʻano, e aʻo ʻoe ma kēia hiʻohiʻona.
E kiʻi i nā hana hou a pau o ka waiwai i makemake ʻia
E like me ka'ōlelo i luna ʻO VPR ʻAʻole hiki ke unuhi i nā waiwai kope a pau mai ka pae i nānā ʻia. No ka hana ʻana i kēia, pono ʻoe i kahi ʻano paʻakikī liʻiliʻi, i hana ʻia me kekahi mau hana Excel, e like me INDEX (INDEX), LIʻILIʻI (LIILI) a lalani (LINE)
No ka laʻana, ʻike ke kumu ma lalo nei i nā hana hou ʻana o ka waiwai mai ke kelepona F2 ma ka laulā B2:B16 a hoʻihoʻi i ka hopena mai nā lālani like ma ke kolamu 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));"")}
E hoʻokomo i kēia ʻano hoʻonohonoho i loko o nā keena pili kokoke, e like me nā cell F4: F8e like me ka mea i hoikeia ma ke kii malalo. Pono ka helu o nā pūnaewele i like a ʻoi aku paha ma mua o ka helu kiʻekiʻe loa o ka hana hou ʻana o ka waiwai i ʻimi ʻia. Mai poina e kaomi Ctrl + Shift + Kākaue hoʻokomo pololei i ke ʻano huila.
Inā makemake ʻoe e hoʻomaopopo i ka hana ʻana, e luʻu liʻiliʻi i nā kikoʻī o ke ʻano:
Mahele 1:
IF($F$2=B2:B16,ROW(C2:C16)-1,"")
ЕСЛИ($F$2=B2:B16;СТРОКА(C2:C16)-1;"")
$F$2=B2:B16 - hoʻohālikelike i ka waiwai i loko o ka cell F2 me kēlā me kēia o nā waiwai i ka pae B2:B16. Inā loaʻa kahi pāʻani, a laila ka ʻōlelo STRING(C2:C16)-1 hoʻihoʻi i ka helu o ka laina pili (waiwai -1 hiki iā ʻoe ke hoʻokomo i ka laina poʻo). Inā ʻaʻohe pāʻani, ʻo ka hana IF (IF) hoʻihoʻi i kahi kaula ʻole.
Ka hopena hana IF (IF) e loaʻa ana kahi ʻano hoʻohālikelike: {1,"",3,"",5,"","","","","","",12,"","",""}
Mahele 2:
ROW()-3
СТРОКА()-3
Eia ka hana lalani (LINE) hana ma ke ʻano he counter hou. No ka mea ua kope ʻia ke kumu i loko o nā keena F4:F9, e unuhi mākou i ka helu 3 mai ka hopena hana e loaʻa ai ka waiwai 1 i loko o ke keena F4 (laina 4, unuhi 3) e loaa 2 i loko o ke keena F5 (laina 5, unuhi 3) a pela aku.
Mahele 3:
SMALL(IF($F$2=B2:B16,ROW(C2:C16)-1,""),ROW()-3))
НАИМЕНЬШИЙ(ЕСЛИ($F$2=B2:B16;СТРОКА(C2:C16)-1;"");СТРОКА()-3))
Launch LIʻILIʻI (Liʻiliʻi) hoʻi n-oh ka waiwai liʻiliʻi loa i loko o ka pūʻulu ʻikepili. I kā mākou hihia, ʻo ke kūlana (mai ka liʻiliʻi loa) e hoʻihoʻi e hoʻoholo ʻia e ka hana lalani (LINE) (e nana i ka Mahele 2). No laila, no ke kelepona F4 kuleana pili i ka LIILII({hui},1) hoike 1-th (ka mea liʻiliʻi loa) ʻano hui, ʻo ia hoʻi. 1. No ke kelepona F5 hoike 2-th ʻo ka mea liʻiliʻi loa i ka laha, ʻo ia hoʻi 3, Etc.
Mahele 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))
Launch INDEX (INDEX) e hoʻihoʻi wale i ka waiwai o kahi kelepona kikoʻī i loko o kahi ʻano C2:C16. No ke kelepona F4 kuleana pili i ka INDEX($C$2:$C$16) e hoʻi mai Meleno ka mea, F5 kuleana pili i ka INDEX($C$2:$C$16) e hoʻi mai Nā meaʻono' a laila.
Mahele 5:
IFERROR()
ЕСЛИОШИБКА()
ʻO ka hope, hoʻokomo mākou i ke ʻano i loko o ka hana IFERROR (IFERROR), no ka mea ʻaʻole paha ʻoe e ʻoluʻolu i ka memo hewa #AT (#N/A) inā ʻoi aku ka liʻiliʻi o ka helu o nā cell kahi i kope ʻia ai ke ʻano ma mua o ka helu o nā waiwai pālua i ka laulā e nānā ʻia nei.
ʻimi ʻo XNUMXD e ka lālani a me ke kolamu i ʻike ʻia
ʻO ka hana ʻana i kahi ʻimi XNUMXD ma Excel e pili ana i ka ʻimi ʻana i kahi waiwai e ka lālani a me ka helu kolamu i ʻike ʻia. I nā huaʻōlelo ʻē aʻe, ke unuhi nei ʻoe i ka waiwai kelepona ma ke kikowaena o kahi lālani a me ke kolamu.
No laila, e huli i kā mākou papaʻaina a kākau i kahi ʻano me kahi hana ʻO VPR, e loaʻa ai ka ʻike e pili ana i ke kumukūʻai o nā lemona i kūʻai ʻia ma Malaki.
Nui nā ala e hana ai i kahi huli XNUMXD. E nānā i nā koho a koho i ka mea kūpono iā ʻoe.
Nā hana VLOOKUP a me MATCH
Hiki iā ʻoe ke hoʻohana i ka nui o nā hana ʻO VPR (VLOOKUP) a HOIKE HOU (MATCH) e huli i ka waiwai ma ka hui o na kahua Hua inoa (kaula) a Mahina (column) o ka laha i ninauia:
=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);ЛОЖЬ)
He hana maʻamau ka formula ma luna ʻO VPR, e ʻimi ana i kahi hoʻohālikelike pololei o ka waiwai "Lemons" ma nā keena A2 a hiki i A9. Akā ʻoiai ʻaʻole ʻoe i ʻike i ke kolamu o ke kūʻai ʻana o Malaki, ʻaʻole hiki iā ʻoe ke hoʻonohonoho i ka helu kolamu no ke kolu o ka hoʻopaʻapaʻa hana. ʻO VPR. Akā, hoʻohana ʻia ka hana HOIKE HOUe wehewehe i keia kolamu.
MATCH("Mar",$A$1:$I$1,0)
ПОИСКПОЗ("Mar";$A$1:$I$1;0)
Unuhi ʻia i ka ʻōlelo kanaka, ʻo ia hoʻi:
- Ke ʻimi nei mākou i nā huaʻōlelo "Mar" - hoʻopaʻapaʻa huli_waiwai (lookup_value);
- Ke nānā nei i nā pūnaewele mai A1 a i I1 - hoʻopaʻapaʻa huli_huʻi (lookup_array);
- Hoʻihoʻi pololei ʻana - hoʻopaʻapaʻa ʻano_like (match_type).
E ho ohana 0 i ka hoʻopaʻapaʻa ʻekolu, ʻōlelo ʻoe i nā hana HOIKE HOU e ʻimi i ka waiwai mua e pili pono ana i ka waiwai āu e ʻimi nei. Ua like kēia me ka waiwai wahahee (FALSE) no ka hoʻopaʻapaʻa ʻehā ʻO VPR.
ʻO kēia ke ʻano e hiki ai iā ʻoe ke hana i kahi ʻano hulina ʻelua ʻaoʻao ma Excel, ʻike ʻia hoʻi ʻo ʻelua-dimensional search a i ʻole ʻimi ʻaoʻao ʻelua.
SUMPRODUCT hana
Launch KANAWAI (SUMPRODUCT) e hoʻihoʻi i ka huina o nā huahana o nā pūʻulu i koho ʻia:
=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)
INDEX a me MATCH hana
Ma ka ʻatikala aʻe e wehewehe au i kēia mau hana i nā kikoʻī, no laila hiki iā ʻoe ke kope wale i kēia ʻano:
=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))
Kapa ʻia nā pae a me ka mea hoʻohana intersection
Inā ʻaʻole ʻoe i loko o kēlā mau palapala Excel paʻakikī, makemake paha ʻoe i kēia ala ʻike a poina ʻole:
- E koho i ka papaʻaina, wehe i ka pā Nā kulekele (Formulas) a kaomi Hana mai ke koho (Hana mai ke koho).
- E hōʻoia i nā pahu Lalani luna (ma ka laina ma luna) a kolamu hema (ma ke kolamu ma ka hema). E hāʻawi ʻo Microsoft Excel i nā inoa i nā pae mai nā koina ma ka lālani luna a me ke kolamu hema o kāu pāpalapala. I kēia manawa hiki iā ʻoe ke ʻimi me ka hoʻohana pololei ʻana i kēia mau inoa me ka hana ʻole ʻana i nā formula.
- Ma kekahi keena hakahaka, e kakau = inoa_lālani column_name, no ka laʻana penei:
= Lemona Mar
… a i ʻole:
= Mar Lemona
E hoʻomanaʻo pono e hoʻokaʻawale ʻia ka inoa o ka lālani a me ke kolamu e kahi ākea, a ma kēia hihia e hana like me ka mea hoʻohana intersection.
Ke hoʻokomo ʻoe i kahi inoa, e hōʻike ana ʻo Microsoft Excel i kahi hāmeʻa me ka papa inoa o nā inoa pili, e like me ke komo ʻana i kahi ʻano.
- Press komo a nānā i ka hopena
Ma keʻano laulā, ʻo kēlā me kēia o nā ʻano hana i luna aʻe āu e koho ai, ʻo ka hopena o kahi hulina ʻelua-dimensional e like nō ia:
Ke hoʻohana nei i nā VLOOKUP he nui i hoʻokahi kumu
ʻAʻohe kolamu maʻamau i ka papaʻaina nui a me ka papa ʻimi, a ke pale nei kēia iā ʻoe mai ka hoʻohana ʻana i ka hana maʻamau. ʻO VPR. Eia naʻe, aia kekahi papa ʻaina ʻaʻole i loaʻa ka ʻike a mākou e makemake ai, akā he kolamu maʻamau me ka papa nui a me ka papa ʻimi.
E nānā kākou i kēia hiʻohiʻona. Loaʻa iā mākou kahi papa nui me kahi kolamu SKU (hou), kahi āu e makemake ai e hoʻohui i kahi kolamu me nā kumukūʻai pili mai kahi papa ʻē aʻe. Eia hou, loaʻa iā mākou 2 mau papa ʻimi. ʻO ka mea mua (Lookup table 1) loaʻa nā helu hou SKU (hou) a me nā inoa huahana, a me ka lua (Lookup table 2) - nā inoa huahana a me nā helu kahiko SKU (kahiko).
No ka hoʻohui ʻana i nā kumukūʻai mai ka papa ʻike ʻelua i ka papa nui, pono ʻoe e hana i kahi hana i kapa ʻia ʻo pālua ʻO VPR a i ʻole ka pūnana ʻO VPR.
- Kākau i kahi hana ʻO VPR, loaʻa ka inoa huahana ma ka pākaukau Papa hulina 1me ka hoʻohana ' SKU, e like me ka waiwai i makemake ʻia:
=VLOOKUP(A2,New_SKU,2,FALSE)=ВПР(A2;New_SKU;2;ЛОЖЬ)Eia New_SKU – kapa inoa $A:$B ma ka papaaina Papa hulina 1, 2 - ʻo ia ke kolamu B, aia nā inoa o nā waiwai (e nānā i ke kiʻi ma luna)
- Kākau i ka papa kuhikuhi no ka hoʻokomo ʻana i nā kumukūʻai mai ka papaʻaina Papa hulina 2 ma muli o nā inoa huahana kaulana. No ka hana ʻana i kēia, hoʻopili i ke ʻano āu i hana ai ma mua e like me ka waiwai huli no ka hana hou ʻO VPR:
=VLOOKUP(VLOOKUP(A2,New_SKU,2,FALSE),Price,3,FALSE)=ВПР(ВПР(A2;New_SKU;2;ЛОЖЬ);Price;3;ЛОЖЬ)Eia Price – kapa inoa $A:$C ma ka papaaina Papa hulina 2, 3 aia ke kolamu C i loko o nā kumukūʻai.
Hōʻike ka kiʻi ma lalo nei i ka hopena i hoʻihoʻi ʻia e ke kumu a mākou i hana ai:
Hoʻololi i ka ʻikepili mai nā papa like ʻole me ka hoʻohana ʻana iā VLOOKUP a me INDIRECT
ʻO ka mea mua, e wehewehe mākou i ka manaʻo o ka huaʻōlelo "Dynamic substitution of data from different tables" i mea e maopopo pono ai mākou kekahi i kekahi.
Aia nā kūlana i ka nui o nā pepa me ka ʻikepili o ka ʻano like, a pono e unuhi i ka ʻike e pono ai mai kekahi pepa, e pili ana i ka waiwai i hoʻokomo ʻia i loko o kahi cell i hāʻawi ʻia. Manaʻo wau he mea maʻalahi ka wehewehe ʻana i kēia me kahi laʻana.
E noʻonoʻo e loaʻa iā ʻoe nā hōʻike kūʻai aku no kekahi mau ʻāina me nā huahana like a ma ka ʻano like. Makemake ʻoe e ʻimi i nā helu kūʻai no kekahi māhele kikoʻī:
Inā loaʻa iā ʻoe ʻelua mau hōʻike like, a laila hiki iā ʻoe ke hoʻohana i kahi ʻano maʻalahi maʻalahi me nā hana ʻO VPR и IF (IF) e koho i ka hōʻike makemake e huli:
=VLOOKUP($D$2,IF($D3="FL",FL_Sales,CA_Sales),2,FALSE)
=ВПР($D$2;ЕСЛИ($D3="FL";FL_Sales;CA_Sales);2;ЛОЖЬ)
Where:
- $D$2 he cell i loaʻa ka inoa o ka huahana. E hoʻomanaʻo, hoʻohana mākou i nā kuhikuhi kikoʻī ma aneʻi e pale aku i ka hoʻololi ʻana i ka waiwai huli i ke kope ʻana i ke ʻano i nā cell ʻē aʻe.
- $D3 he cell me ka inoa o ka ʻāina. Ke hoʻohana nei mākou i kahi kuhikuhi kolamu piha a me kahi kuhikuhi lālani pili no ka mea ke manaʻo nei mākou e kope i ke ʻano i nā cell ʻē aʻe o ke kolamu like.
- FL_Sales и CA_Kuai - nā inoa o nā papa (a i ʻole nā inoa inoa) i loaʻa nā hōʻike kūʻai e pili ana. Hiki iā ʻoe, ʻoiaʻiʻo, ke hoʻohana i nā inoa pepa maʻamau a me nā kuhikuhi ʻana o ke kelepona, no ka laʻana ‘Pepa FL’!$A$3:$B$10, akā ʻoi aku ka maʻalahi o nā pae i kapa ʻia.
Eia naʻe, i ka nui o ia mau papa, ka hana IF ʻaʻole ia ka hopena maikaʻi loa. Akā, hiki iā ʻoe ke hoʻohana i ka hana AOLE (INDIRECT) e hoʻihoʻi i ka laulā hulina i makemake ʻia.
E like me kāu e ʻike ai, ka hana AOLE hoʻohana ʻia e hoʻihoʻi i kahi loulou i hāʻawi ʻia e kahi kaula kikokikona, ʻo ia ka mea e pono ai mākou i kēia manawa. No laila, e hoʻololi wiwo ʻole i ka ʻōlelo ma luna o ka ʻōlelo me ka hana IF e pili me ka hana AOLE. Eia kahi hui ʻO VPR и AOLE hana maikaʻi me:
=VLOOKUP($D$2,INDIRECT($D3&"_Sales"),2,FALSE)
=ВПР($D$2;ДВССЫЛ($D3&"_Sales");2;ЛОЖЬ)
Where:
- $D$2 - he cell kēia me ka inoa o ka huahana, ʻaʻole ia i loli ma muli o ka loulou paʻa.
- $D3 ʻo ia ka cell i loaʻa ka hapa mua o ka inoa ʻāina. I kā mākou laʻana, kēia FL.
- _Kuai – ka ʻāpana maʻamau o ka inoa o nā pae a i ʻole nā papa i kapa ʻia. Ke hui pū ʻia me ka waiwai i loko o ka cell D3, e hoʻokumu i ka inoa kūpono piha o ka pae i makemake ʻia. Ma lalo iho nei kekahi mau kikoʻī no ka poʻe hou i ka hana AOLE.
Pehea ka hana ʻana o INDIRECT a me VLOOKUP
ʻO ka mea mua, e hoʻomanaʻo wau iā ʻoe i ka syntax o ka hana AOLE (KUHIKUHI):
INDIRECT(ref_text,[a1])
ДВССЫЛ(ссылка_на_текст;[a1])
ʻO ka hoʻopaʻapaʻa mua hiki ke lilo i ka helu kelepona (A1 a i ʻole R1C1 style), kahi inoa laulā, a i ʻole kahi kaula kikokikona. Hoʻoholo ka manaʻo ʻelua i ke ʻano o ka loulou i loko o ka manaʻo mua:
- A1ina ka hoopaapaa KANAWAI OIAIO (TRUE) a ʻaʻole i kuhikuhi ʻia;
- R1C1, ina FAS E (FALSE).
I kā mākou hihia, aia ke ʻano o ka loulou A1, no laila hiki iā ʻoe ke haʻalele i ka hoʻopaʻapaʻa ʻelua a nānā i ka mea mua.
No laila, e hoʻi kākou i kā mākou hōʻike kūʻai. Inā hoʻomanaʻo ʻoe, ʻo kēlā me kēia hōʻike he papa ʻokoʻa i loaʻa ma kahi pepa ʻokoʻa. I mea e hana pono ai ke kumu, pono ʻoe e inoa i kāu mau papa (a i ʻole nā pae), a he ʻāpana like nā inoa a pau. No ka laʻana, e like me kēia: CA_Kuai, FL_Kuai, TX_Kuai a laila. E like me kāu e ʻike ai, aia ʻo "_Sales" ma nā inoa āpau.
Launch AOLE hoʻohui i ka waiwai ma ke kolamu D a me ke kaula kikokikona "_Sales", a laila e haʻi ana ʻO VPR ma kahi papa e huli ai. Inā loaʻa i ka cell D3 ka waiwai "FL", e ʻimi ke kumu i ka papaʻaina FL_Kuai, inā "CA" - i ka papaʻaina CA_Kuai a laila.
Ka hopena o nā hana ʻO VPR и AOLE penei:
Inā loaʻa ka ʻikepili i nā puke Excel ʻē aʻe, a laila pono ʻoe e hoʻohui i ka inoa o ka puke ma mua o ka inoa inoa, no ka laʻana:
=VLOOKUP($D$2,INDIRECT($D3&"Workbook1!_Sales"),2,FALSE)
=ВПР($D$2;ДВССЫЛ($D3&"Workbook1!_Sales");2;ЛОЖЬ)
Ina ka hana AOLE pili i kekahi puke hana, pono e wehe ʻia kēlā puke hana. Inā pani ʻia, e hōʻike ka hana i kahi hewa. #REF! (#SSYL!).