tirotiro
Ko tenei akoranga iti e whakamarama ana me pehea te hanga mahi VPR (VLOOKUP), he whakaatu i etahi atu tauira ka taea e Excel te rapu i roto i te ahua-tairongo, me te tohu i nga kaha me nga ngoikoretanga o ia mahi.
Ki taku mohio kei te mohio nga kaiwhakamahi Excel he aha te mahi e mahi ana i te rapu poutū. Ka tika, he mahi VPR. Engari, he iti noa nga tangata e mohio ana ki tera VPR ehara i te tairongo pū, arā, he ōrite te pūāhua o raro me te pūmatua.
Anei he tauira tere e whakaatu ana i te kore kaha VPR mohio rehita. Whakaarohia i roto i te pūtau A1 kei roto te uara "pire" me te pūtau A2 – “Pire”, tātai:
=VLOOKUP("Bill",A1:A10,2)
=ВПР("Bill";A1:A10;2)
… ka mutu tana rapunga mo te "pire" i te mea ko taua uara te tuatahi ki te rarangi, ka tango i te uara mai i te pūtau B1.
I muri mai i tenei tuhinga, ka whakaatu ahau ki a koe me pehea te mahi VPR take tairongo. I tua atu, ka akohia e matou etahi atu mahi ka taea te mahi i nga rapunga take-tairongo ki Excel.
Ka timata tatou ki te mea ngawari - Tirohia (LOOKUP) me WHAKANUI (SUMPRODUCT), heoi, he maha nga herenga nui. I muri mai, ka ata titiro atu tatou ki te tauira ahua uaua ake INDEX+MATCH (INDEX+MATCH), he pai te mahi i roto i nga ahuatanga me nga huinga raraunga.
Ko te mahi a VLOOKUP he ahua tairongo
Kei te mohio koe, ko te mahi o mua VPR he ahuakore. Heoi ano, he huarahi hei hanga i te ahua o te ahua. Ki te mahi i tenei, me taapiri he pou awhina ki te ripanga, penei i te tauira e whai ake nei.
Whakaarohia i roto i te pou B kei reira nga tohu tohu hua (Tuemi) ka hiahia koe ki te tango i te utu o te hua me nga korero e pa ana mai i nga pou C и D. Ko te raruraru ko nga kaitautuhi kei roto nga tohu iti me nga tohu matua. Hei tauira, uara pūtau B4 (001Tvci3u) me B5 (001Tvci3U) he rereke noa i te ahua o te ahua whakamutunga, u и U aua.
Ka taea e koe te whakaaro, ko te tikanga rapu noa
=VLOOKUP("001Tvci3U",$A$2:$C$7,2,FALSE)
=ВПР("001Tvci3U";$A$2:$C$7;2;ЛОЖЬ)
ka hoki mai $ 90, mai i te uara 001Tvci3u kei roto i te awhe rapu i mua atu 001Tvci3U. Engari ehara i te mea e hiahia ana tatou, he?
Ki te rapu me tetahi mahi VPR i roto i te take tairongo Excel, me taapiri he pou kaiawhina me te whakaki i ona pūtau ki te tauira e whai ake nei (ko B ko te tīwae rapu):
=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));"")
Ka wawahia e tenei tauira te uara e hiahiatia ana ki roto i nga tohu motuhake, ka whakakapi i ia ahuatanga me tana waehere (hei tauira, hei utu mo te A i te 65, hei utu a waehere 97) ka whakakotahi i enei waehere ki tetahi aho tau motuhake.
I muri i tera, ka whakamahia e matou he mahi ngawari VPR mō te rapu pū tairongo:
=VLOOKUP($G$3,$A$2:$C$8,3,FALSE)
=ВПР($G$3;$A$2:$C$8;3;ЛОЖЬ)
Te mahi tika o te mahi VPR E rua nga take e pa ana ki te take-tairongo:
- Me noho te pou kaiawhina ki te taha maui o te awhe ka kitea.
- Ko te uara e rapu ana koe me whakauru he tohu tohu hei utu mo te uara tuturu.
Me pehea te whakamahi tika i te mahi CODE
Ko te tātai i whakauruhia ki roto i nga pūtau o te pou awhina e kii ana he rite te maha o nga tohu o o uara rapu katoa. Ki te kore, me mohio koe ki nga nama iti me te rahi rawa me te taapiri i nga ahuatanga maha IFERROR (IFERROR) e hia ngā pūāhua te rerekētanga i waenga i te uara rapunga poto me te roa rawa atu.
Hei tauira, ki te mea ko te uara rapu poto rawa he 3 pūāhua me te roa rawa he 5 pūāhua, whakamahia tēnei tātai:
=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));"")
Mo te mahi PSTR (MID) Ka whakaratohia e koe nga tohenga e whai ake nei:
- Tohenga tuatahi - kuputuhi (kuputuhi) ko te tuhinga, tohutoro pūtau ranei kei roto nga tohu ka tangohia (ko ta maatau ko B2)
- Tohenga tuatahi - tau_timata (start_position) ko te tuunga o te tuatahi o aua tohu ka tangohia. tomo koe 1 i te mahi tuatahi PSTR, 2 – i te mahi tuarua PSTR etc
- Tohenga tuatahi - num_chars (number_of_characters) – Ka tautuhi i te maha o nga tohu hei tango mai i te tuhinga. I te mea e hiahia ana matou kia 1 te ahua i nga wa katoa, i nga mahi katoa ka tuhia e matou 1.
LIMITATIONS: taumahi VPR ehara i te otinga pai mo nga rapunga tairongo i Excel. Tuatahi, me taapiri he pou awhina. Tuarua, he pai te mahi a te tauira mena he rite tonu nga raraunga, ka mohiotia ranei te maha o nga tohu i roto i nga uara rapu. Mena ehara tenei i a koe, he pai ake te whakamahi i tetahi o nga otinga ka whakaatuhia e matou i raro nei.
Ko te mahi LOOKUP mo te rapu take tairongo
taumahi Tirohia (LOOKUP) e pa ana VPR, heoi ka taea e tana wetereo te rapu i nga keehi-tairongo me te kore e taapirihia he pou awhina. Ki te mahi i tenei, whakamahia Tirohia honoa ki te mahi HE WHAKAMAHI (TIKA).
Mena ka tangohia e tatou nga raraunga mai i te tauira o mua (kaore he pou awhina), ka whai ake te tauira e whai ake nei ki te mahi:
=LOOKUP(TRUE,EXACT($A$2:$A$7,$F$2),$B$2:$B$7)
=ПРОСМОТР(ИСТИНА;СОВПАД($A$2:$A$7;$F$2);$B$2:$B$7)
Rapu tātai i te awhe A2:A7 ōrite tika ki te uara pūtau F2 pū tairongo me te whakahoki i te uara mai i te pou B o te rarangi kotahi.
Ka rite ki VPRmahi Tirohia he rite tonu te mahi me nga tuhinga me nga uara tau, ka kite koe i te Whakaahuamata i raro nei:
Mea nui! Mo te mahi Tirohia i tika te mahi, ko nga uara kei roto i te rarangi rapu me whakariterite i roto i te raupapa aupiki, ara mai i te iti rawa ki te rahi.
Tukuna ahau ki te whakamarama poto pehea te mahi HE WHAKAMAHI i roto i te tauira kua whakaaturia i runga ake nei, i te mea koinei te kaupapa matua.
taumahi HE WHAKAMAHI ka whakatairite i nga uara kuputuhi e rua i roto i nga tohenga tuatahi me te tuarua ka whakahoki i te TRUE mena he rite tonu, he HAPA ranei mena kaore. He mea nui ki a maatau te mahi HE WHAKAMAHI take tairongo.
Kia kite tatou me pehea te mahi o ta maatau tauira tiro+TIKA:
=LOOKUP(TRUE,EXACT($A$2:$A$7,$F$2),$B$2:$B$7)
=ПРОСМОТР(ИСТИНА;СОВПАД($A$2:$A$7;$F$2);$B$2:$B$7)
- taumahi HE WHAKAMAHI whakatairite uara pūtau F2 me nga huānga katoa o te tīwae A (A2:A7). Ka whakahokia TINO ki te kitea he taurite tika, ki te kore he FALSE.
- I te mea ka hoatu e koe te tohenga mahi tuatahi Tirohia uara TRUE, ka tangohia e ia te uara e rite ana mai i te tīwae i tohua (i roto i ta maatau take, tīwae B) mena ka kitea he taurite tika, he take tairongo.
Ko taku tumanako i marama tenei whakamaramatanga inaianei kua marama koe ki te whakaaro matua. Mena ka penei, ka kore koe e raru ki etahi atu mahi ka tirohia e matou, na te mea. mahi ratou katoa i runga i te kaupapa kotahi.
LIMITATIONS: Ko nga raraunga i roto i te tīwae rapu me kōmaka i roto i te raupapa aupiki.
SUMPRODUCT – ka kimi i nga uara kupu, he tairongo tairongo, engari ka whakahoki i nga tau anake
Kua mohio koe mai i te taitara, WHAKANUI Ko (SUMPRODUCT) tetahi atu mahi Excel hei awhina i a koe ki te rapu tairongo, engari ka whakahoki mai i nga uara tau. Mena kaore tenei whiringa e pai ki a koe, ka taea e koe te haere tonu ki te paihere INDEX+MATCH, ka hoatu he otinga mo tetahi keehi me nga momo raraunga.
Tuatahi, me whakamaarama poto ahau i te wetereo o tenei mahi, ma tenei ka awhina koe ki te mohio ake ki te tauira tairongo e whai ake nei.
taumahi WHAKANUI ka whakareatia nga huānga o nga huānga kua hoatu, ka whakahokia te tapeke o nga hua. He penei te ahua o te syntax:
SUMPRODUCT(array1,[array2],[array3],...)
СУММПРОИЗВ(массив1;[массив2];[массив3];…)
I te mea e hiahia ana matou ki te rapu take-tairongo, ka whakamahia e matou te mahi HE WHAKAMAHI (TIKA) mai i te tauira o mua ko tetahi o nga whakarea:
=SUMPRODUCT((EXACT($A$2:$A$7,$F$2)*($B$2:$B$7)))
=СУММПРОИЗВ((СОВПАД($A$2:$A$7;$F$2)*($B$2:$B$7)))
Kei te mahara koe, HE WHAKAMAHI whakatairite uara pūtau F2 me nga huānga katoa o te tīwae A. Ka whakahokia TINO ki te kitea he taurite tika, ki te kore he FALSE. I roto i nga mahi pangarau, ka tangohia e Excel te TRUE hei 1, me te HERE mo 0Ano WHAKANUI ka whakareatia enei tau ka whakahiatohia nga hua.
Kore e tatauhia na te mea ka whakanuia ka hoatu tonu 0. Kia ata titiro tatou ki nga mea ka tupu ina he taurite tika i roto i te pou A kitea, ka hoki mai 1… Mahi WHAKANUI ka whakareatia te tau i te pou B on 1 ka whakahokia mai te hua – he rite tonu te tau! Na te mea ko nga hua o etahi atu hua he kore, a kaore e pa ki te moni ka puta.
Kia aroha mai te mahi WHAKANUI kaore e taea te mahi me nga uara tuhinga me nga ra na te mea kaore e taea te whakarea. I tenei take, ka whiwhi koe i te karere hapa #WARA! (#VALUE!) pera i roto i te pūtau F4 i te pikitia i raro nei:
LIMITATIONS: He whakahoki uara tau anake.
INDEX + MATCH – rapunga take-tairongo mo tetahi momo raraunga
Ka mutu, kua tata matou ki tetahi tauira rapu kore mutunga me te tairongo e mahi ana me tetahi huinga raraunga.
Ko tenei tauira ka puta mai i muri, ehara i te mea ka waiho te mea pai mo te kai reka, engari na te mohiotanga i riro mai i nga tauira o mua ka awhina koe ki te mohio pai ake me te tere ake o te tauira tairongo-take. INDEX+MATCH (INDEX+MATCH).
Ka rite ki to whakaaro, ko te huinga o nga mahi ATU KAUPAPA и Index whakamahia i roto i te Excel hei momo rereke me te kaha ake mo VPR. Ko te tuhinga Ma te whakamahi i te INDEX me te MATCH hei utu mo VLOOKUP ka tino whakamaarama te mahi tahi o enei mahi.
Ka whakahoki ano ahau i nga kaupapa matua:
- taumahi ATU KAUPAPA (MATCH) e rapu ana i tetahi uara i roto i te awhe kua whakaritea, ka whakahoki mai i tona turanga, ara, te rarangi me te tau pou;
- I muri mai, ko te mahi Index (INDEX) he whakahoki uara mai i te tīwae me te haupae/rānei.
Ki te tātai INDEX+MATCH Ka taea e koe te rapu take-tairongo, me whakauru noa e koe tetahi mahi ki reira. Ehara i te mea uaua ki te matapae he aha ano HE WHAKAMAHI (TIKA):
=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))
I roto i tenei tauira HE WHAKAMAHI mahi i roto i te ara rite i roto i te taha ki te mahi Tirohia, ka hoatu he hua rite:
Kia mahara ko te tauira INDEX+MATCH kei roto i nga taiapa mingimingi he tauira huinga me whakaoti e koe ma te pehi Ctrl + Shift + Tomo.
He aha te mea ko INDEX+MATCH te otinga pai mo te rapunga tairongo?
Ko nga painga matua o te paihere Index и ATU KAUPAPA:
- Kaore e hiahiatia te taapiri i tetahi pou awhina, he rereke VPR.
- Karekau e hiahia kia kōmakahia te tīwae rapu, he rereke Tirohia.
- Ka mahi me nga momo raraunga katoa - nga nama, nga tuhinga me nga ra.
He ahua tino pai tenei tauira, kaore? Ko te mea pono, ehara. Na ko te aha.
Me whakaaro kei te putua te pūtau kei te tīwae uara whakahoki e hono ana ki te uara rapu. He aha te hua ka hoki mai te tauira? Kao? Kia kite tatou he aha te tauira e hoki mai ana:
Aue, ka hoki kore te tauira! Kaore pea tenei i te raru nui mena kei te mahi koe me nga uara kuputuhi parakore. Heoi, mena kei te ripanga nga tau, tae atu ki nga "tino" kore, ka raru tenei.
Inaa, ko era atu tauira rapunga katoa (VLOOKUP, LOOKUP, me SUMPRODUCT) i korerohia e matou i mua he rite te ahua. Engari e hiahia ana koe ki te tauira tino tika, tika?
Kia noho tairongo te take tauira INDEX+MATCH tino pai, hoatu ki roto i tetahi mahi IF (IF) ka whakamatau i te pūtau me te uara whakahoki me te whakahoki i te hua putua mena kei te putua:
=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));"")
I roto i tenei tauira:
- B he tīwae whai uara whakahoki
- 1+ he tau ka huri i te tūnga whanaunga o te pūtau i whakahokia mai e te mahi ATU KAUPAPA, ki te wāhi noho tūturu o te pūtau. Hei tauira, i roto i ta maatau mahi ATU KAUPAPA kua hoatu te rarangi rapu A2:A7, arā, te tūnga whanaunga o te pūtau A2 e 1, no te mea koinei te tuatahi i roto i te rarangi. Engari ko te tino waahi o te pūtau A2 kei te pou ko 2, na ka tapiritia e matou 1ki te hanga i te rereketanga me te whai mahi WHAKAHUI (IDIRECT) i tikina te uara mai i te pūtau e hiahiatia ana.
Ko nga pikitia i raro nei e whakaatu ana i te tauira tairongo tairongo kua whakatikahia INDEX+MATCH I roto i te mahi. Ka whakahokia he hua putua mena kei te putua te pūtau i whakahokia.
I tuhi ano ahau i te tauira ki nga pou B:Dkia uru ki te pae tauira i runga i te Whakaahuamata.
Ka hoki mai te tauira 0ki te kore te pūtau kua whakahokia mai.
Ki te hiahia koe ki te hono Index и ATU KAUPAPA ka whakaatu i etahi karere ka putua te uara whakahoki, ka taea e koe te tuhi ki nga korukī whakamutunga (“”) o te tauira, hei tauira, 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.")
