Contents
Hōʻike kēia haʻawina liʻiliʻi pehea e hana ai i kahi hana ʻO VPR (VLOOKUP).
Manaʻo wau e ʻike kēlā me kēia mea hoʻohana Excel i ka hana e hana ai i ka huli ʻana. Pololei, he hana ʻO VPR. Eia naʻe, kakaikahi ka poʻe i ʻike i kēlā ʻO VPR ʻAʻole pili i ka helu helu, ʻo ia hoʻi, ua like nā huaʻōlelo haʻahaʻa a me luna.
Eia kahi laʻana wikiwiki e hōʻike ana i ka hiki ʻole ʻO VPR ʻike inoa. Manaʻo i loko o kahi keʻena A1 aia ka waiwai "bill" a me ka cell A2 - "Bila", ka hoʻohālikelike:
=VLOOKUP("Bill",A1:A10,2)
=ВПР("Bill";A1:A10;2)
… e hoʻopau i kāna ʻimi ʻana ma ka "bila" no ka mea e hiki mua ana kēlā waiwai ma ka papa inoa, a e unuhi i ka waiwai mai ke kelepona B1.
Ma hope o kēia ʻatikala, e hōʻike wau iā ʻoe pehea e hana ai ʻO VPR hihia piliki. Eia kekahi, e aʻo mākou i kekahi mau hana hou e hiki ke hana i nā hulina case-sensitive ma Excel.
E hoʻomaka mākou me ka maʻalahi loa - KAʻike (LOOKUP) a KANAWAI (SUMPRODUCT), ka mea pōʻino, he mau palena koʻikoʻi. Ma hope aʻe, e nānā pono mākou i ke ʻano paʻakikī iki INDEX+MAKE (INDEX+MATCH), e hana pono ʻole i kēlā me kēia kūlana a me kekahi ʻikepili.
ʻO ka hana VLOOKUP he hiʻohiʻona
E like me kāu i ʻike mua ai, ka hana maʻamau ʻO VPR ʻaʻohe hihia. Eia nō naʻe, aia kahi ala e hana ai i ka hihia. No ka hana ʻana i kēia, pono ʻoe e hoʻohui i kahi kolamu kōkua i ka papaʻaina, e like me ka mea i hōʻike ʻia ma kēia hiʻohiʻona.
Manaʻo ma kahi kolamu B aia nā mea hōʻike huahana (Item) a makemake ʻoe e unuhi i ke kumukūʻai o ka huahana a me ka ʻōlelo pili mai nā kolamu. C и D. ʻO ka pilikia, loaʻa i nā mea hōʻailona nā huaʻōlelo liʻiliʻi a me nā hua nui. No ka laʻana, nā helu kelepona B4 (001Tvci3u) a B5 (001Tvci3U) ʻokoʻa wale nō i ka hihia o ke ʻano hope, u и U niioaaonoaaiii.
E like me kāu e noʻonoʻo ai, ʻo ke ʻano hulina maʻamau
=VLOOKUP("001Tvci3U",$A$2:$C$7,2,FALSE)
=ВПР("001Tvci3U";$A$2:$C$7;2;ЛОЖЬ)
e hoʻi mai $ 90, mai ka waiwai 001Tvci3u aia ma ka laula huli ma mua 001Tvci3U. Akā ʻaʻole ia ka mea e pono ai mākou, ʻeā?
E huli me kahi hana ʻO VPR i ka hihia koʻikoʻi o Excel, pono ʻoe e hoʻohui i kahi kolamu kōkua a hoʻopiha i kona mau cell me kēia ʻano kumu (ma kahi o B ka kolamu ʻimi):
=CODE(MID(B2,1,1)) & CODE(MID(B2,2,1)) & CODE(MID(B2,3,1)) & CODE(MID(B2,4,1)) & CODE(MID(B2,5,1)) & CODE(MID(B2,6,1)) & CODE(MID(B2,7,1)) & CODE(MID(B2,8,1)) & IFERROR(CODE(MID(B2,9,1)),"")
=КОДСИМВ(ПСТР(B2;1;1)) & КОДСИМВ(ПСТР(B2;2;1)) & КОДСИМВ(ПСТР(B2;3;1)) & КОДСИМВ(ПСТР(B2;4;1)) & КОДСИМВ(ПСТР(B2;5;1)) & КОДСИМВ(ПСТР(B2;6;1)) & КОДСИМВ(ПСТР(B2;7;1)) & КОДСИМВ(ПСТР(B2;8;1)) & ЕСЛИОШИБКА(КОДСИМВ(ПСТР(B2;9;1));"")
Hoʻokaʻawale kēia ʻano kumu i ka waiwai i makemake ʻia i nā huaʻōlelo ʻokoʻa, hoʻololi i kēlā me kēia ʻano me kāna code (no ka laʻana, ma kahi o A ma 65, ma kahi a code 97) a laila hoʻohui i kēia mau code i kahi kaula helu ʻokoʻa.
Ma hope o kēlā, hoʻohana mākou i kahi hana maʻalahi ʻO VPR no ka huli ʻana i ka helu helu:
=VLOOKUP($G$3,$A$2:$C$8,3,FALSE)
=ВПР($G$3;$A$2:$C$8;3;ЛОЖЬ)
ʻO ka hana kūpono o ka hana ʻO VPR pili ka hihia i nā kumu ʻelua:
- Pono ke kolamu kōkua ma ka ʻaoʻao hema loa o ka laulā ʻike.
- Pono e loaʻa i ka waiwai āu e ʻimi nei i kahi code ma kahi o ka waiwai maoli.
Pehea e hoʻohana pono ai i ka hana CODE
ʻO ke kumu i hoʻokomo ʻia i loko o nā keʻena o ke kolamu kōkua i manaʻo ʻia he like nā helu o kāu mau hulina āpau. Inā ʻaʻole, pono ʻoe e ʻike i nā helu liʻiliʻi a nui loa a hoʻohui i nā hiʻohiʻona he nui IFERROR (IFERROR) ehia mau huapalapala ka ʻokoʻa ma waena o ka waiwai hulina pōkole loa a me ka lōʻihi loa.
No ka laʻana, inā he 3 mau huaʻōlelo ka waiwai hulina pōkole a ʻo ka lōʻihi loa he 5 mau huaʻōlelo, e hoʻohana i kēia ʻano:
=CODE(MID(B2,1,1)) & CODE(MID(B2,2,1)) & CODE(MID(B2,3,1)) & IFERROR(CODE(MID(B2,3,1)),"") & IFERROR(CODE(MID(B2,4,1)),"")
=КОДСИМВ(ПСТР(B2;1;1)) & КОДСИМВ(ПСТР(B2;2;1)) & КОДСИМВ(ПСТР(B2;3;1)) & ЕСЛИОШИБКА(КОДСИМВ(ПСТР(B2;3;1));"") & ЕСЛИОШИБКА(КОДСИМВ(ПСТР(B2;4;1));"")
No ka hana PSTR (MID) Hāʻawi ʻoe i kēia mau manaʻo:
- 1st paio - kikokikona (kikokikona) ʻo ia ka kikokikona a i ʻole ka helu kelepona i loaʻa nā huaʻōlelo e unuhi ʻia (ma kā mākou hihia ʻo B2)
- 2st paio - helu_hoʻomaka (start_position) ʻo ia ke kūlana o ka mua o kēlā mau huaʻōlelo e unuhi ʻia. komo ʻoe 1 i ka hana mua PSTR, 2 - ma ka hana lua PSTR etc.
- 3st paio - num_chars (Number_of_characters) – Hōʻike i ka helu o nā huapalapala e unuhi mai ka kikokikona. No ka mea pono mākou i 1 huaʻōlelo i nā manawa a pau, ma nā hana āpau mākou e kākau ai 1.
LIMITATIONS: Launch ʻO VPR ʻaʻole ʻo ia ka hopena maikaʻi loa no ka huli ʻana i ka case-sensitive ma Excel. ʻO ka mea mua, pono e hoʻohui i kahi kolamu kōkua. ʻO ka lua, he hana maikaʻi ke kumu hoʻohālikelike inā he homogeneous ka ʻikepili, a ʻike ʻia ka helu pololei o nā huaʻōlelo i nā waiwai i ʻimi ʻia. Inā ʻaʻole kēia kāu hihia, ʻoi aku ka maikaʻi o ka hoʻohana ʻana i kekahi o nā hoʻonā a mākou e hōʻike nei ma lalo nei.
Hana LOOKUP no ka huli ʻana i ka hihia
Launch KAʻike (LOOKUP) pili ʻO VPR, akā naʻe, hiki i kāna syntax ke huli i ka hihia me ka hoʻohui ʻole i kahi kolamu kōkua. No ka hana ʻana i kēia, hoʻohana KAʻike hui pū me ka hana KA MANAʻI (POLOKOLO).
Inā mākou e lawe i ka ʻikepili mai ka hiʻohiʻona mua (me ka ʻole o ke kolamu kōkua), a laila e hoʻokō kēia ʻano hana i ka hana:
=LOOKUP(TRUE,EXACT($A$2:$A$7,$F$2),$B$2:$B$7)
=ПРОСМОТР(ИСТИНА;СОВПАД($A$2:$A$7;$F$2);$B$2:$B$7)
Huli ʻia ke kumu ma ka laulā A2:A7 kūlike pololei me ka waiwai kelepona F2 hihia a hoʻihoʻi i ka waiwai mai ke kolamu B o ka lālani like.
like me ʻO VPRkuleana pili i ka KAʻike hana like me nā kikokikona a me nā waiwai helu, e like me kāu e ʻike ai ma ke kiʻi ma lalo nei:
Mea nui! No ka hana KAʻike hana pololei, pono e hoʻokaʻawale ʻia nā koina ma ke kolamu ʻimi ma ke ʻano piʻi, ʻo ia hoʻi mai ka liʻiliʻi a i ka nui.
E wehewehe pōkole au i ka hana ʻana o ka hana KA MANAʻI ma ke ʻano i hōʻike ʻia ma luna, ʻoiai ʻo kēia ke kumu nui.
Launch KA MANAʻI hoʻohālikelike i nā waiwai kikokikona ʻelua i ka hoʻopaʻapaʻa 1 a me 2 a hoʻihoʻi i ka TRUE inā like like lākou, a i ʻole FALSE inā ʻaʻole. He mea nui iā mākou ka hana KA MANAʻI hihia piliki.
E ʻike kākou i ka hana ʻana o kā mākou formula NANA+PIKI:
=LOOKUP(TRUE,EXACT($A$2:$A$7,$F$2),$B$2:$B$7)
=ПРОСМОТР(ИСТИНА;СОВПАД($A$2:$A$7;$F$2);$B$2:$B$7)
- Launch KA MANAʻI hoʻohālikelike i ka waiwai kelepona F2 me nā mea a pau i loko o kahi kolamu A (A2:A7). E hoʻihoʻi i ka TRUE inā loaʻa kahi hoʻohālikelike pololei, inā ʻaʻole FALSE.
- No ka mea, hāʻawi ʻoe i ka hoʻopaʻapaʻa hana mua KAʻike waiwai TRUE, unuhi ia i ka waiwai pili mai ke kolamu i kuhikuhi ʻia (ma kā mākou hihia, kolamu B) inā ʻike ʻia kahi pāʻani pololei, ʻike kikoʻī.
Manaʻo wau ua maopopo kēia wehewehe a i kēia manawa ua maopopo iā ʻoe ka manaʻo nui. Inā pēlā, ʻaʻole ʻoe e pilikia me nā hana ʻē aʻe a mākou e loiloi hou ai, no ka mea. hana lākou a pau ma ke kumu hoʻokahi.
LIMITATIONS: Pono e hoʻokaʻawale ʻia ka ʻikepili i ke kolamu ʻimi ma ke ʻano piʻi.
SUMPRODUCT - loaʻa nā waiwai kikokikona, ʻike kikoʻī, akā e hoʻihoʻi i nā helu wale nō
E like me kāu i hoʻomaopopo mua ai ma ke poʻo inoa, KANAWAI (SUMPRODUCT) kekahi hana Excel e kōkua iā ʻoe e hana i ka huli ʻana i ka hihia, akā e hoʻihoʻi wale i nā waiwai helu. Inā ʻaʻole kūpono kēia koho iā ʻoe, a laila hiki iā ʻoe ke hele koke i ka pūpū INDEX+MAKE, e hāʻawi ana i ka hopena no kēlā me kēia hihia a no nā ʻano ʻikepili.
ʻO ka mea mua, e wehewehe pōkole wau i ka syntax o kēia hana, e kōkua kēia iā ʻoe e hoʻomaopopo maikaʻi i ke ʻano hoʻohālikelike case-sensitive e pili ana.
Launch KANAWAI hoʻomāhuahua i nā mea o nā arrays i hāʻawi ʻia a hoʻihoʻi i ka huina o nā hopena. Penei ka syntax:
SUMPRODUCT(array1,[array2],[array3],...)
СУММПРОИЗВ(массив1;[массив2];[массив3];…)
No ka mea makemake mākou i kahi hulina case-sensitive, hoʻohana mākou i ka hana KA MANAʻI (EXACT) mai ka laʻana mua e like me kekahi o nā mea hoʻonui:
=SUMPRODUCT((EXACT($A$2:$A$7,$F$2)*($B$2:$B$7)))
=СУММПРОИЗВ((СОВПАД($A$2:$A$7;$F$2)*($B$2:$B$7)))
E like me ʻoe e hoʻomanaʻo nei, KA MANAʻI hoʻohālikelike i ka waiwai kelepona F2 me nā mea a pau i loko o kahi kolamu A. E hoʻihoʻi i ka TRUE inā loaʻa kahi hoʻohālikelike pololei, inā ʻaʻole FALSE. Ma nā hana makemakika, lawe ʻo Excel i ka TRUE e like me 1, a FALSE no 0Eia hou KANAWAI hoʻonui i kēia mau helu a hōʻuluʻulu i nā hopena.
ʻAʻole helu ʻia nā Zero no ka mea ke hoʻonui ʻia lākou e hāʻawi mau 0. E nānā pono kākou i ka hana ʻana i ka hoʻohālikelike pololei ʻana i ke kolamu A loaa a hoi mai 1… Hana KANAWAI hoonui i ka helu ma ke kolamu B on 1 a hoʻihoʻi i ka hopena - ʻo ka helu like! ʻO kēia no ka mea ʻaʻole pili nā hopena o nā huahana ʻē aʻe, a ʻaʻole pili lākou i ka huina hopena.
Minamina ka hana KANAWAI ʻAʻole hiki ke hana me nā waiwai kikokikona a me nā lā no ka mea ʻaʻole hiki ke hoʻonui ʻia. I kēia hihia, e loaʻa iā ʻoe kahi memo hewa #VALUE! (#VALUE!) e like me loko o kahi keena F4 ma ke kiʻi ma lalo:
LIMITATIONS: Hoʻihoʻi i nā waiwai helu wale nō.
INDEX + MATCH - hulina hihia no kekahi ʻano ʻikepili
ʻO ka mea hope loa, ua kokoke mākou i kahi ʻano hulina palena ʻole a me ka hiʻohiʻona e hana pū me kekahi pūʻulu ʻikepili.
ʻO kēia hiʻohiʻona ka mea hope loa, ʻaʻole no ka mea i waiho ʻia ka mea maikaʻi loa no ka ʻai, akā no ka mea ʻo ka ʻike i loaʻa mai nā hiʻohiʻona mua e kōkua iā ʻoe e hoʻomaopopo maikaʻi a wikiwiki hoʻi i ke ʻano o ka hihia. INDEX+MAKE (INDEX+PAʻO).
E like me kāu i manaʻo ai, ʻo ka hui pū ʻana o nā hana HOIKE HOU и INDEX hoʻohana ʻia ma Excel ma ke ʻano he ʻoi aku ka maʻalahi a me ka ikaika ʻO VPR. ʻO ka ʻatikala ʻO ka hoʻohana ʻana i ka INDEX a me MATCH ma kahi o VLOOKUP e wehewehe pono i ka hana like ʻana o kēia mau hana.
E hoʻopaʻa hou wau i nā mea nui:
- Launch HOIKE HOU (MATCH) ʻimi i ka waiwai i loko o ka laulā i hāʻawi ʻia a hoʻihoʻi i kona kūlana pili, ʻo ia hoʻi, ka helu lālani a/a i ʻole kolamu;
- A laila, ka hana INDEX (INDEX) hoʻihoʻi i kahi waiwai mai kahi kolamu a/a lālani i kuhikuhi ʻia.
To formula INDEX+MAKE Hiki iā ʻoe ke ʻimi i ka case-sensitively, pono ʻoe e hoʻohui i hoʻokahi hana iā ia. ʻAʻole paʻakikī ke koho hou aku KA MANAʻI (POLOKOLO):
=INDEX($B$2:$B$7,MATCH(TRUE,EXACT($A$2:$A$7,$F$2),0))
=ИНДЕКС($B$2:$B$7;ПОИСКПОЗ(ИСТИНА;СОВПАД($A$2:$A$7;$F$2);0))
Ma keia kumu KA MANAʻI hana like me ka hui pū me ka hana KAʻike, a hāʻawi i ka hopena like:
E hoʻomanaʻo i ke ʻano INDEX+MAKE i hoʻopaʻa ʻia i loko o nā pale kuʻi he ʻano hoʻonohonoho a pono ʻoe e hoʻopau iā ia ma ke kaomi ʻana Ctrl + Shift + Kākau.
No ke aha ʻo INDEX+MATCH ka hoʻonā maikaʻi loa no ka huli ʻana i ka hihia?
ʻO nā pōmaikaʻi nui o ka pūpū INDEX и HOIKE HOU:
- ʻAʻole pono e hoʻohui i kahi kolamu kōkua, ʻaʻole like ʻO VPR.
- ʻAʻole pono e hoʻokaʻawale ʻia ke kolamu hulina, ʻaʻole like KAʻike.
- Hana me nā ʻano ʻikepili āpau - helu, kikokikona a me nā lā.
Me he mea lā ua kūpono kēia ʻano, ʻaʻole anei? ʻOiaʻiʻo, ʻaʻole ia. A ʻo ia ke kumu.
E noʻonoʻo ʻoe ua nele ka cell i ke kolamu waiwai hoʻihoʻi i pili me ka waiwai huli. He aha ka hopena e hoʻi mai ai ke kumu? ʻAʻole? E ʻike kākou i ka mea e hoʻihoʻi mai ai ke kumu:
ʻAe, hoʻihoʻi mai ke kumu i ka zero! ʻAʻole paha kēia he pilikia nui inā ʻoe e hana nei me nā waiwai kikokikona maʻemaʻe. Eia naʻe, inā loaʻa i ka papaʻaina nā helu, me nā zeros "ʻoiaʻiʻo", lilo kēia i pilikia.
ʻO ka ʻoiaʻiʻo, ʻo nā ʻano hoʻohālikelike ʻē aʻe a pau (VLOOKUP, LOOKUP, a me SUMPRODUCT) a mākou i kūkākūkā mua ai e hana like. Akā makemake ʻoe i ke ʻano kūpono, ʻeā?
No ka hana ʻana i kahi hiʻohiʻona koʻikoʻi INDEX+MAKE kūpono, hoʻokomo i kahi hana IF (IF) nāna e hoʻāʻo i ke kelepona me ka waiwai hoʻihoʻi a hoʻihoʻi i kahi hopena ʻole inā ʻaʻohe:
=IF(INDIRECT("B"&(1+MATCH(TRUE,EXACT($A$2:$A$7,$G$2),0)))<>"",INDEX($B$2:$B$7, MATCH(TRUE,EXACT($A$2:$A$7,$G$2),0)),"")
=ЕСЛИ(ДВССЫЛ("B"&(1+ПОИСКПОЗ(ИСТИНА;СОВПАД($A$2:$A$7;$G$2);0)))<>"";ИНДЕКС($B$2:$B$7; ПОИСКПОЗ(ИСТИНА;СОВПАД($A$2:$A$7;$G$2);0));"")
Ma kēia ʻano kumu:
- B he kolamu me nā waiwai hoʻihoʻi
- 1+ He helu ia e hoohuli ai i ke kulana pili o ke keena i hoihoiia e ka hana HOIKE HOU, i ka helu wahi maoli o ke keena. No ka laʻana, i kā mākou hana HOIKE HOU hāʻawi ʻia ka ʻimi hulina A2:A7, ʻo ia ke kūlana pili o ke kelepona A2 makemake 1, no ka mea, ʻo ia ka mea mua i ka laha. Akā ʻo ke kūlana maoli o ke kelepona A2 i ke kolamu he 2, no laila ke hoʻohui nei mākou 1e hana i ka ʻokoʻa a loaʻa ka hana AOLE (INDIRECT) kiʻi i ka waiwai mai ke kelepona i makemake ʻia.
Hōʻike nā kiʻi ma lalo nei i ke ʻano hoʻoponopono i hoʻoponopono ʻia INDEX+MAKE Ma ka hana. Hoʻihoʻi ʻo ia i kahi hopena ʻole inā nele ka cell i hoʻihoʻi ʻia.
Ua kākau hou au i ke ʻano i nā kolamu B:De hoʻokomo i ka pahu formula ma ka paʻi kiʻi.
Hoʻihoʻi mai ke kumu 0inā loaʻa ʻole ka cell cell i hoʻihoʻi ʻia.
Inā makemake ʻoe i ka loulou INDEX и HOIKE HOU i hōʻike ʻia i kekahi memo i ka wā ʻole o ka waiwai hoʻihoʻi, hiki iā ʻoe ke kākau iā ia ma nā huaʻōlelo hope ("") o ke ʻano, no ka laʻana, penei:
=IF(INDIRECT("D"&(1+MATCH(TRUE,EXACT($B$2:$B$7,$G$2),0)))<>"",INDEX($D$2:$D$7, MATCH(TRUE,EXACT($B$2:$B$7,$G$2),0)),"There is nothing to return, sorry.")
=ЕСЛИ(ДВССЫЛ("D"&(1+ПОИСКПОЗ(ИСТИНА;СОВПАД($B$2:$B$7;$G$2);0)))<>"";ИНДЕКС($D$2:$D$7; ПОИСКПОЗ(ИСТИНА;СОВПАД($B$2:$B$7;$G$2);0));"There is nothing to return, sorry.")
