Ụzọ 4 iji mee ka ikpe VLOOKUP nwee mmetụta na Excel

Nkuzi nta a na-akọwa otu esi arụ ọrụ VPR (VLOOKUP) nke nwere mmetụta, na-egosi ọtụtụ usoro ndị ọzọ Excel nwere ike ịchọ n'ụzọ nwere mmetụta, ma rụtụ aka ike na adịghị ike nke ọrụ ọ bụla.

Echere m na onye ọrụ Excel ọ bụla maara ọrụ na-eme ọchụchọ kwụ ọtọ. Nke ahụ ziri ezi, ọ bụ ọrụ VPR. Otú ọ dị, mmadụ ole na ole maara nke ahụ VPR adịghị enwe mmetụta nke ikpe, ya bụ, mkpụrụedemede ala na nke elu bụ otu maka ya.

Nke a bụ ọmụmaatụ ngwa ngwa na-egosi enweghị ike VPR mara aha. Were ya na cell A1 nwere uru "ụgwọ" na cell A2 - "Bill", usoro:

=VLOOKUP("Bill",A1:A10,2)

=ВПР("Bill";A1:A10;2)

… ga-akwụsị nchọ ya na “ụgwọ ụgwọ” ebe ọ bụ na uru ahụ na-ebute ụzọ na listi ahụ, wee wepụ uru ya na cell B1.

E mesịa n'isiokwu a, m ga-egosi gị otu esi eme VPR mmetụta nha mkpụrụedemde. Na mgbakwunye, anyị ga-amụta ọrụ ole na ole ndị ọzọ nwere ike ịme nyocha nke nwere mmetụta na Excel.

Anyị ga-amalite na nke kacha mfe - HERE (Lee anya) na OZI (SUMPRODUCT), nke, ọ dị nwute, nwere oke oke dị mkpa. Ọzọ, anyị ga-elebakwu anya na usoro dị mgbagwoju anya dị ntakịrị INDEX+MATCH (INDEX + MATCH), nke na-arụ ọrụ enweghị ntụpọ n'ọnọdụ ọ bụla yana yana dataset ọ bụla.

  • VLOOKUP nke nwere mmetụta nke ikpe - chọrọ kọlụm inyeaka
  • Ọrụ LOOKUP nwere mmetụta nke ikpe - chọrọ ka ahazi data
  • SUMPRODUCT – na-eweghachi naanị ọnụọgụ ọnụọgụgụ
  • INDEX+MATCH – ihe nchọta nwere mmetụta maka ụdị data niile

Ọrụ VLOOKUP nwere mmetụta dị nro

Dị ka ị maralarị, ọrụ a na-emebu VPR bụ ikpe enweghị mmetụta. Agbanyeghị, enwere ụzọ isi mee ka ọ dị nro. Iji mee nke a, ịkwesịrị ịgbakwunye kọlụm inyeaka na tebụl, dị ka egosiri na ihe atụ na-esonụ.

Were ya na kọlụm B enwere njirimara ngwaahịa (Ihe) ma ịchọrọ iwepụ ọnụahịa ngwaahịa na nkọwa kwekọrọ na kọlụm. C и D. Nsogbu bụ na njirimara nwere ma mkpụrụedemede obere na mkpụrụedemede ukwu. Dịka ọmụmaatụ, ụkpụrụ cell B4 (001Tvci3u) na B5 (001Tvci3U) dị iche naanị n'ihe gbasara agwa ikpeazụ, u и U karị.

Dị ka ị nwere ike iche n'echiche, usoro ọchụchọ a na-emebu

=VLOOKUP("001Tvci3U",$A$2:$C$7,2,FALSE)

=ВПР("001Tvci3U";$A$2:$C$7;2;ЛОЖЬ)

ga-alaghachi $ 90, ebe ọ bụ na uru 001Tvci3u dị na nso nso a na-achọ tupu 001Tvci3U. Ma nke ahụ abụghị ihe anyị chọrọ, ọ bụ ya?

Ụzọ 4 iji mee ka ikpe VLOOKUP nwee mmetụta na Excel

Iji ọrụ chọọ ya VPR N'ihe gbasara okwu Excel, ị ga-agbakwunye kọlụm inyeaka wee jupụta mkpụrụ ndụ ya na usoro a (ebe B bụ kọlụm nyocha):

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

Usoro a na-agbaji uru achọrọ n'ime mkpụrụedemede dị iche iche, dochie mkpụrụedemede ọ bụla na koodu ya (dịka ọmụmaatụ, kama A na 65, kama a koodu 97) wee jikọta koodu ndị a n'ime eriri ọnụọgụ pụrụ iche.

Mgbe nke ahụ gasịrị, anyị na-eji ọrụ dị mfe VPR maka nchọta nwere mmetụta:

=VLOOKUP($G$3,$A$2:$C$8,3,FALSE)

=ВПР($G$3;$A$2:$C$8;3;ЛОЖЬ)

Ụzọ 4 iji mee ka ikpe VLOOKUP nwee mmetụta na Excel

Ọrụ kwesịrị ekwesị nke ọrụ ahụ VPR ikpe-sensitive dabere n'ihe abụọ:

  1. Kọlụm inyeaka ga-abụrịrị kọlụm kacha ekpe na oke a na-ahụ anya.
  2. Uru ị na-achọ ga-enwerịrị koodu mkpụrụedemede kama ịbụ ezigbo uru.

Otu esi eji ọrụ CODE nke ọma

Usoro etinyere n'ime sel nke kọlụm inyeaka na-eche na ụkpụrụ ọchụchọ gị niile nwere otu ọnụọgụ mkpụrụedemede. Ọ bụrụ na ọ bụghị, mgbe ahụ ịkwesịrị ịma ọnụọgụ kacha nta na nke kachasị ma gbakwunye ọtụtụ atụmatụ IFEROR (IFERROR) mkpụrụedemede ole bụ ihe dị iche n'etiti uru a na-achọkarị nke kacha dị mkpụmkpụ na kacha ogologo.

Dịka ọmụmaatụ, ọ bụrụ na uru ọchụchọ kacha nso bụ mkpụrụedemede 3 ma nke kachasị ogologo bụ mkpụrụedemede 5, jiri usoro a:

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

Maka ọrụ PSTR (MID) Ị na-enye arụmụka ndị a:

  • arụmụka nke mbụ - ederede (ederede) bụ ntụaka ederede ma ọ bụ cell nwere mkpụrụedemede a ga-ewepụta (n'ọnọdụ anyị ọ bụ B2)
  • arụmụka nke mbụ - mmalite_num (start_position) bụ ọnọdụ nke mbụ n'ime mkpụrụedemede ndị a ga-ewepụta. ị banye 1 na ọrụ mbụ PSTR, 2 - na nke abụọ ọrụ PSTR wdg
  • arụmụka nke mbụ - nọmba_chars (nọmba_nke mkpụrụedemede) - Na-akọwapụta ọnụọgụ mkpụrụedemede iji wepụta na ederede. Ebe ọ bụ na anyị chọrọ naanị agwa 1 mgbe niile, na ọrụ niile anyị na-ede 1.

Oge: ọrụ VPR abụghị ihe ngwọta kachasị mma maka nyocha ndị nwere mmetụta na Excel. Nke mbụ, a chọrọ mgbakwunye nke kọlụm inyeaka. Nke abuo, usoro a na-arụ ọrụ dị mma naanị ma ọ bụrụ na data ahụ dị n'otu, ma ọ bụ mara ọnụ ọgụgụ nke mkpụrụedemede na ụkpụrụ achọrọ. Ọ bụrụ na nke a abụghị ikpe gị, ọ ka mma iji otu n'ime ngwọta nke anyị na-egosi n'okpuru.

Ọrụ LOOKUP maka ọchụchọ nwere mmetụta nke ikpe

ọrụ HERE (LOOKUP) metụtara VPR, Otú ọ dị syntax ya na-enye ohere maka nyocha nke nwere mmetụta na-enweghị itinye kọlụm inyeaka. Iji mee nke a, jiri HERE jikọtara ya na ọrụ EGO (KWUO).

Ọ bụrụ na anyị ewere data site na ihe atụ gara aga (na-enweghị kọlụm inyeaka), mgbe ahụ usoro a ga-anagide ọrụ ahụ:

=LOOKUP(TRUE,EXACT($A$2:$A$7,$F$2),$B$2:$B$7)

=ПРОСМОТР(ИСТИНА;СОВПАД($A$2:$A$7;$F$2);$B$2:$B$7)

Achọ usoro n'ime oke A2:A7 dakọtara na uru cell F2 ikpe nwere mmetụta ma weghachi uru site na kọlụm B nke otu ahịrị.

Dị ka VPRọrụ HERE na-arụ ọrụ nhata na ederede na ụkpụrụ ọnụọgụ, dịka ị nwere ike ịhụ na nseta ihuenyo dị n'okpuru:

Ụzọ 4 iji mee ka ikpe VLOOKUP nwee mmetụta na Excel

Ihe dị mkpa! Maka ịrụ ọrụ HERE na-arụ ọrụ nke ọma, a ga-ahazi ụkpụrụ dị na kọlụm nyocha n'usoro na-arịgo, ya bụ site na nke kacha nta ruo na nke kachasị.

Ka m kọwaa nkenke ka ọrụ ahụ si arụ ọrụ EGO na usoro egosiri n'elu, ebe nke a bụ isi ihe.

ọrụ EGO atụnyere ụkpụrụ ederede abụọ dị na arụmụka 1st na 2nd wee laghachi TRUE ma ọ bụrụ na ha bụ otu, ma ọ bụ Ụgha ma ọ bụrụ na ha abụghị. Ọ dị anyị mkpa na ọrụ ahụ EGO mmetụta nha mkpụrụedemde.

Ka anyị hụ ka usoro anyị si arụ ọrụ HỤRỤ+ KPỤRỤ:

=LOOKUP(TRUE,EXACT($A$2:$A$7,$F$2),$B$2:$B$7)

=ПРОСМОТР(ИСТИНА;СОВПАД($A$2:$A$7;$F$2);$B$2:$B$7)

  • ọrụ EGO atụnyere uru cell F2 na ihe niile dị na kọlụm A (A2: A7). Na-eweghachi EZI Ọ bụrụ na achọtara otu egwuregwu, ma ọ bụghị, Ụgha.
  • Ebe ọ bụ na ị na-enye arụmụka ọrụ mbụ HERE uru TRUE, ọ na-ewepụta uru kwekọrọ na kọlụm akọwapụtara (n'ọnọdụ anyị, kọlụm B) naanị ma ọ bụrụ na achọtara egwuregwu ziri ezi, nke nwere mmetụta.

Enwere m olileanya na nkọwa a doro anya ma ugbu a ị ghọtara isi echiche. Ọ bụrụ otú ahụ, mgbe ahụ, ị ​​​​gaghị enwe nsogbu ọ bụla na ọrụ ndị ọzọ anyị ga-enyocha n'ihu, n'ihi na. ha niile na-arụ ọrụ n'otu ụkpụrụ.

Oge: A ga-ahazirịrị data dị na kọlụm nyocha n'usoro nrịgo.

SUMPRODUCT – na-achọta ụkpụrụ ederede, nke nwere mmetụta, mana ọ na-eweghachi naanị ọnụọgụ

Dịka ị ghọtara na aha ya, OZI (SUMPRODUCT) bụ ọrụ Excel ọzọ nke ga-enyere gị aka ịme ọchụchọ nwere mmetụta, mana ọ ga-eweghachite naanị ọnụọgụ ọnụọgụ. Ọ bụrụ na nhọrọ a adabaghị gị, ị nwere ike ịga n'ihu ozugbo na ngwugwu INDEX+MATCH, nke na-enye ngwọta maka ikpe ọ bụla na maka ụdị data ọ bụla.

Nke mbụ, ka m kọwaa nkenke nkenke syntax nke ọrụ a, nke a ga-enyere gị aka ịghọta nke ọma usoro nke nwere mmetụta na-esote.

ọrụ OZI na-amụba akụkụ nke usoro enyere ma weghachi nchikota nke nsonaazụ ya. Syntax ahụ dị ka nke a:

SUMPRODUCT(array1,[array2],[array3],...)

СУММПРОИЗВ(массив1;[массив2];[массив3];…)

Ebe ọ bụ na anyị chọrọ nyocha nke nwere mmetụta, anyị na-eji ọrụ ahụ EGO (EXACT) site na ihe atụ gara aga dị ka otu n'ime ndị na-ebuwanye ibu:

=SUMPRODUCT((EXACT($A$2:$A$7,$F$2)*($B$2:$B$7)))

=СУММПРОИЗВ((СОВПАД($A$2:$A$7;$F$2)*($B$2:$B$7)))

I chetara, EGO atụnyere uru cell F2 na ihe niile dị na kọlụm A. Na-eweghachi EZI Ọ bụrụ na achọtara otu egwuregwu, ma ọ bụghị, Ụgha. Na arụmọrụ mgbakọ na mwepụ, Excel na-ewere TRUE dị ka 1, na Ụgha maka 0Ọzọkwa OZI na-amụba ọnụọgụgụ ndị a ma na-achịkọta nsonaazụ ya.

A naghị agụ zeros n'ihi na mgbe ọ na-amụba ha na-enye mgbe niile 0. Ka anyị lebakwuo anya n'ihe na-eme mgbe otu egwuregwu dị na kọlụm A hụrụ wee laghachi 1… Ọrụ OZI na-amụba ọnụ ọgụgụ dị na kọlụm B on 1 ma weghachite nsonaazụ - kpọmkwem otu ọnụ ọgụgụ ahụ! Nke a bụ n'ihi na nsonaazụ nke ngwaahịa ndị ọzọ bụ efu, ha anaghị emetụta nchikota nchikota.

Ọ dị nwute na ọrụ ahụ OZI enweghị ike ịrụ ọrụ na ụkpụrụ ederede na ụbọchị dịka enweghị ike ịba ụba. N'okwu a, ị ga-enweta ozi njehie # URU! (#Uru!) dị ka ọ dị na cell F4 na foto dị n'okpuru:

Ụzọ 4 iji mee ka ikpe VLOOKUP nwee mmetụta na Excel

Oge: Na-eweghachi naanị ọnụọgụ ọnụọgụgụ.

INDEX + MATCH – ọchụchọ nwere mmetụta maka ụdị data ọ bụla

N'ikpeazụ, anyị dị nso na usoro ọchụchọ na-enweghị njedebe na nke nwere mmetụta nke na-arụ ọrụ na nhazi data ọ bụla.

Ihe atụ a na-abịa n'ikpeazụ, ọ bụghị n'ihi na a na-ahapụ ihe kachasị mma maka ihe eji megharịa ọnụ, ma n'ihi na ihe ọmụma e nwetara site na ihe atụ ndị gara aga ga-enyere gị aka ịghọta usoro nke nwere mmetụta nke ọma na ngwa ngwa. INDEX+MATCH (INDEX+MATCH).

Dị ka ị nwere ike chepụtara, nchikota nke ọrụ KWURUOkwu и INDEX eji na Excel dị ka ihe mgbanwe na ike ọzọ maka VPR. Edemede Iji INDEX na MATCH kama VLOOKUP ga-akọwa nke ọma ka ọrụ ndị a si arụkọ ọrụ.

Aga m atụgharị isi ihe ndị a:

  • ọrụ KWURUOkwu (MATCH) na-achọ uru dị na mpaghara enyere wee weghachi ọnọdụ ikwu ya, ya bụ, ahịrị na/ma ọ bụ nọmba kọlụm;
  • Na-esote, ọrụ ahụ INDEX (INDEX) weghachi uru site na kọlụm na/ma ọ bụ ahịrị akọwapụtara.

Iji usoro INDEX+MATCH nwere ike ịchọ nke ọma, naanị otu ọrụ ka ị ga-achọ. Ọ naghị esiri ike ịkọ ihe ọ bụ ọzọ EGO (KWUO):

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

Na usoro a EGO na-arụ ọrụ n'otu ụzọ ahụ dị ka njikọ na ọrụ HERE, ma na-enye otu nsonaazụ ahụ:

Ụzọ 4 iji mee ka ikpe VLOOKUP nwee mmetụta na Excel

Rịba ama na usoro INDEX+MATCH agbakwunyere na ihe nkwado gbagọrọ agbagọ bụ usoro n'usoro ma ị ga-emecha ya site na ịpị Ctrl + Shift + Tinye.

Kedu ihe kpatara INDEX+MATCH ji bụrụ ihe ngwọta kachasị mma maka nchọ ihe nwere mmetụta?

Isi uru nke ngwugwu INDEX и KWURUOkwu:

  1. Achọghị ịgbakwunye kọlụm inyeaka, n'adịghị ka VPR.
  2. Achọghị ka ahazi kọlụm ọchụchọ, n'adịghị ka HERE.
  3. Na-arụ ọrụ na ụdị data niile - nọmba, ederede na ụbọchị.

Usoro a yiri ka ọ zuru oke, ọ bụghị ya? N'ezie, ọ bụghị. Ya mere.

Were ya na cell dị na kọlụm uru nloghachi jikọtara ya na uru nchọgharị efu. Kedu ihe si na usoro a ga-alaghachi? Mba? Ka anyị hụ ihe usoro a na-eweghachi n'ezie:

Ụzọ 4 iji mee ka ikpe VLOOKUP nwee mmetụta na Excel

Ee, usoro a na-eweghachi efu! Nke a nwere ike ọ gaghị abụ nnukwu nsogbu ma ọ bụrụ na ị na-eji ụkpụrụ ederede dị ọcha na-arụ ọrụ. Otú ọ dị, ọ bụrụ na tebụl nwere ọnụọgụgụ, gụnyere zeros "ezigbo", nke a na-aghọ nsogbu.

N'ezie, usoro nyocha ndị ọzọ (VLOOKUP, LOOKUP, na SUMPRODUCT) anyị tụlere na mbụ na-akpa otu a. Mana ịchọrọ usoro zuru oke, nri?

Iji mee ka usoro ihe nwee mmetụta INDEX+MATCH zuru oke, tinye ya na ọrụ IF (Ọ BỤRỤ) nke ga-anwale cell nwere uru nloghachi wee weghachi ihe efu ma ọ bụrụ na ọ tọgbọ chakoo:

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

N'ime usoro a:

  • B bụ kọlụm nwere ụkpụrụ nloghachi
  • 1+ bụ nọmba na-atụgharị n'ọnọdụ ikwu nke cell nke ọrụ ahụ weghachiri KWURUOkwu, gaa na ezigbo adreesị cell. Dịka ọmụmaatụ, na ọrụ anyị KWURUOkwu enyere n'usoro ọchụchọ A2:A7, ya bụ, ọnọdụ ikwu nke cell A2 ga- 1, n'ihi na ọ bụ nke mbụ n'usoro. Ma n'ezie ọnọdụ nke cell A2 na kọlụm bụ 2, yabụ anyị na-agbakwunye 1ime ka ihe dị iche na inwe ọrụ ahụ EZIOKWU (INDIRECT) eweghachite uru ahụ n'ime cell achọrọ.

Foto dị n'okpuru ebe a na-egosi usoro nwere mmetụta akparamagwa emeziri INDEX+MATCH Na omume. Ọ na-eweghachite nsonaazụ efu ma ọ bụrụ na cell eweghachiri efu.

Edegharịrị m usoro ahụ ka ọ bụrụ kọlụm B:Diji dabara mmanya usoro na nseta ihuenyo.

Ụzọ 4 iji mee ka ikpe VLOOKUP nwee mmetụta na Excel

Formula laghachi 0ọ bụrụ na cell eweghachiri nwere efu.

Ụzọ 4 iji mee ka ikpe VLOOKUP nwee mmetụta na Excel

Ọ bụrụ na ị chọrọ njikọ INDEX и KWURUOkwu gosipụtara ụfọdụ ozi mgbe uru nloghachi tọgbọrọ chakoo, ị nwere ike dee ya na ngụ ikpeazụ ("") nke usoro ahụ, dịka ọmụmaatụ, dịka nke a:

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

Ụzọ 4 iji mee ka ikpe VLOOKUP nwee mmetụta na Excel

Nkume a-aza