Efnisyfirlit
Þessi litla kennsla útskýrir hvernig á að búa til fall VPR (VLOOKUP) hástafanæm, sýnir nokkrar aðrar formúlur sem Excel getur leitað í á hástöfumnæmum hætti og bendir á styrkleika og veikleika hverrar aðgerðar.
Ég býst við að allir Excel notendur viti hvaða aðgerð framkvæmir lóðrétta leit. Það er rétt, það er aðgerð VPR. Það vita þó fáir VPR er ekki hástafaviðkvæm, þ.e. lágstafir og hástafir eru eins fyrir það.
Hér er fljótlegt dæmi sem sýnir vanhæfni VPR viðurkenna skrá. Segjum sem svo í frumu A1 inniheldur gildið „bill“ og reitinn A2 – „Bill“, formúla:
=VLOOKUP("Bill",A1:A10,2)
=ВПР("Bill";A1:A10;2)
… mun hætta leit sinni á „reikning“ þar sem það gildi kemur fyrst á listanum og draga gildið úr reitnum B1.
Síðar í þessari grein mun ég sýna þér hvernig á að gera VPR hástafaviðkvæmur. Að auki munum við læra nokkrar fleiri aðgerðir sem geta framkvæmt leit sem er há- og lágstöfum í Excel.
Við byrjum á því einfaldasta - SKOÐA (ÚTLEIT) og SUMPRODUCT (SUMPRODUCT), sem, því miður, hafa nokkrar verulegar takmarkanir. Næst munum við skoða aðeins flóknari formúluna nánar INDEX+MATCH (INDEX+MATCH), sem virkar gallalaust við hvaða aðstæður sem er og með hvaða gagnapakka sem er.
VLOOKUP aðgerðin er hástafaviðkvæm
Eins og þú veist nú þegar, venjulega virka VPR er hástafaónæmi. Hins vegar er leið til að gera það hástafaviðkvæmt. Til að gera þetta þarftu að bæta aukadálki við töfluna, eins og sýnt er í eftirfarandi dæmi.
Segjum sem svo í dálki B það eru vöruauðkenni (Item) og þú vilt draga verð vörunnar og samsvarandi athugasemd úr dálkunum C и D. Vandamálið er að auðkenni innihalda bæði lágstafi og hástafi. Til dæmis, frumugildi B4 (001Tvci3u) og B5 (001Tvci3U) eru aðeins frábrugðnar þegar um er að ræða síðasta staf, u и U sig.
Eins og þú getur ímyndað þér, venjulega leitarformúlan
=VLOOKUP("001Tvci3U",$A$2:$C$7,2,FALSE)
=ВПР("001Tvci3U";$A$2:$C$7;2;ЛОЖЬ)
kem aftur $ 90, þar sem verðmæti 001Tvci3u er á leitarsviðinu fyrr en 001Tvci3U. En það er ekki það sem við þurfum, er það?
Til að leita með aðgerð VPR í Excel næm fyrir stórum og hástöfum þarftu að bæta við hjálpardálki og fylla reiti hans með eftirfarandi formúlu (þar sem B er uppflettisdálkur):
=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));"")
Þessi formúla skiptir æskilegu gildi í aðskilda stafi, kemur hverjum staf í stað kóðans (til dæmis í stað A á 65, í staðinn a kóða 97) og sameinar síðan þessa kóða í einstakan talnastreng.
Eftir það notum við einfalda aðgerð VPR fyrir hástafaviðkvæma leit:
=VLOOKUP($G$3,$A$2:$C$8,3,FALSE)
=ВПР($G$3;$A$2:$C$8;3;ЛОЖЬ)
Rétt virkni aðgerðarinnar VPR Hástafaviðkvæmur fer eftir tveimur þáttum:
- Hjálpardálkurinn verður að vera dálkurinn lengst til vinstri á sýnilega sviðinu.
- Gildið sem þú ert að leita að verður að innihalda stafakóða í stað raunverulegs gildis.
Hvernig á að nota CODE aðgerðina rétt
Formúlan sem sett er inn í frumurnar í aukadálknum gerir ráð fyrir að öll leitargildin þín hafi sama fjölda stafa. Ef ekki, þá þarftu að vita minnstu og stærstu tölurnar og bæta við eins mörgum eiginleikum IFERROR (IFERROR) hversu margir stafir er munurinn á stysta og lengsta leitinni.
Til dæmis, ef stysta leitargildið er 3 stafir og það lengsta er 5 stafir, notaðu þessa formúlu:
=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));"")
Fyrir virkni PSTR (MID) Þú færð eftirfarandi rök:
- 1. rök - texta (texti) er texti eða frumuvísun sem inniheldur stafina sem á að draga út (í okkar tilfelli er það B2)
- 2. rök - byrjun_númer (byrjun_staða) er staðsetning fyrsta af þessum stöfum sem á að draga út. þú kemur inn 1 í fyrsta fallinu PSTR, 2 – í seinni fallinu PSTR o.fl.
- 3. rök - fjölda_stafir (fjöldi_stafa) – Tilgreinir fjölda stafa sem á að draga úr textanum. Þar sem við þurfum bara 1 staf allan tímann, í öllum aðgerðum sem við skrifum 1.
Takmarkanir: virka VPR er ekki besta lausnin fyrir leit í Excel. Í fyrsta lagi þarf að bæta við viðbótardálki. Í öðru lagi skilar formúlan sig aðeins ef gögnin eru einsleit eða nákvæmur fjöldi stafa í gildunum sem leitað er að er þekktur. Ef þetta er ekki þitt tilfelli er betra að nota eina af lausnunum sem við sýnum hér að neðan.
LOOKUP virka fyrir stóra og stóra leit
virka SKOÐA (ÚTLIT) tengt VPR, hins vegar gerir setningafræði þess kleift að leita að hástöfum án þess að bæta við aukadálki. Til að gera þetta, notaðu SKOÐA ásamt aðgerðinni NÁKVÆMLEGA (NÁKVÆMLEGA).
Ef við tökum gögnin úr fyrra dæmi (án aukadálks), þá mun eftirfarandi formúla takast á við verkefnið:
=LOOKUP(TRUE,EXACT($A$2:$A$7,$F$2),$B$2:$B$7)
=ПРОСМОТР(ИСТИНА;СОВПАД($A$2:$A$7;$F$2);$B$2:$B$7)
Formúluleit innan sviðs A2: A7 nákvæm samsvörun við frumugildi F2 hástafanæmi og skilar gildinu úr dálki B í sömu röð.
eins VPRvirka SKOÐA virkar jafnt með texta og tölugildum, eins og þú getur séð á skjámyndinni hér að neðan:
Mikilvægt! Til þess að virka SKOÐA virkaði rétt ætti að flokka gildin í uppflettisdálknum í hækkandi röð, þ.e. frá minnstu til stærstu.
Leyfðu mér að útskýra í stuttu máli hvernig aðgerðin virkar NÁKVÆMLEGA í formúlunni sem sýnd er hér að ofan, þar sem þetta er lykilatriðið.
virka NÁKVÆMLEGA ber saman textagildin tvö í 1. og 2. röksemdum og skilar TRUE ef þau eru nákvæmlega eins, eða FALSE ef þau eru það ekki. Það er mikilvægt fyrir okkur að virka NÁKVÆMLEGA hástafaviðkvæmur.
Við skulum sjá hvernig formúlan okkar virkar SKOÐA+NÁKVÆMT:
=LOOKUP(TRUE,EXACT($A$2:$A$7,$F$2),$B$2:$B$7)
=ПРОСМОТР(ИСТИНА;СОВПАД($A$2:$A$7;$F$2);$B$2:$B$7)
- virka NÁKVÆMLEGA ber saman frumugildi F2 með öllum þáttum í dálki A (A2:A7). Skilar TRUE ef nákvæm samsvörun finnst, annars FALSE.
- Þar sem þú gefur upp fyrstu fall rökin SKOÐA gildi TRUE, það dregur út samsvarandi gildi úr tilgreindum dálki (í okkar tilfelli, dálki B) aðeins ef nákvæm samsvörun finnst, hástafaviðkvæm.
Ég vona að þessi skýring hafi verið skýr og nú skilurðu meginhugmyndina. Ef svo er, þá muntu ekki eiga í erfiðleikum með aðrar aðgerðir sem við munum greina frekar, vegna þess að. þeir vinna allir eftir sömu reglu.
Takmarkanir: Gögnin í leitardálknum verða að vera flokkuð í hækkandi röð.
SUMPRODUCT – finnur textagildi, hástafanæm, en skilar aðeins tölum
Eins og þú hefur þegar skilið af titlinum, SUMPRODUCT (SUMPRODUCT) er önnur Excel aðgerð sem hjálpar þér að gera leit sem er há- og hástöfum, en mun aðeins skila tölugildum. Ef þessi valkostur hentar þér ekki, geturðu strax haldið áfram í búntinn INDEX+MATCH, sem gefur lausn fyrir hvaða mál sem er og fyrir hvaða gagnategund sem er.
Í fyrsta lagi, leyfðu mér að útskýra stuttlega setningafræði þessarar aðgerðar, þetta mun hjálpa þér að skilja betur hástafanæmu formúluna sem fylgir.
virka SUMPRODUCT margfaldar þætti tiltekinna fylkinga og skilar summan af niðurstöðunum. Setningafræðin lítur svona út:
SUMPRODUCT(array1,[array2],[array3],...)
СУММПРОИЗВ(массив1;[массив2];[массив3];…)
Þar sem við þurfum á hástöfumnæmri leit að halda notum við aðgerðina NÁKVÆMLEGA (NÁKVÆMT) frá fyrra dæmi sem einn af margfaldarunum:
=SUMPRODUCT((EXACT($A$2:$A$7,$F$2)*($B$2:$B$7)))
=СУММПРОИЗВ((СОВПАД($A$2:$A$7;$F$2)*($B$2:$B$7)))
Eins og þú manst, NÁKVÆMLEGA ber saman frumugildi F2 með öllum þáttum í dálki A. Skilar TRUE ef nákvæm samsvörun finnst, annars FALSE. Í stærðfræðilegum aðgerðum tekur Excel TRUE sem 1, og FALSE fyrir 0Frekari SUMPRODUCT margfaldar þessar tölur og leggur saman niðurstöðurnar.
Núll eru ekki talin vegna þess að þegar þau eru margfölduð gefa þau alltaf 0. Við skulum skoða nánar hvað gerist þegar nákvæm samsvörun er í dálki A fundið og skilað 1… Virka SUMPRODUCT margfaldar töluna í dálknum B on 1 og skilar niðurstöðunni - nákvæmlega sömu tölu! Þetta er vegna þess að niðurstöður annarra vara eru núll og þær hafa ekki áhrif á summan sem myndast.
Því miður aðgerðin SUMPRODUCT getur ekki unnið með textagildi og dagsetningar þar sem ekki er hægt að margfalda þau. Í þessu tilviki færðu villuboð #VALUE! (#VALUE!) eins og í reit F4 á myndinni hér að neðan:
Takmarkanir: Skilar aðeins tölugildum.
INDEX + MATCH – hástafanæm leit að hvaða gagnategund sem er
Að lokum erum við nálægt ótakmarkaðri og hástöfumnæmri leitarformúlu sem virkar með hvaða gagnasett sem er.
Þetta dæmi kemur síðast, ekki vegna þess að það besta er skilið eftir í eftirrétt, heldur vegna þess að þekking sem fengist hefur úr fyrri dæmum mun hjálpa þér að skilja stóra og hástafanæmu formúluna betur og hraðar. INDEX+MATCH (INDEX+MATCH).
Eins og þú hefur líklega giskað á, samsetning aðgerða FYRIR MEIRA и INDEX notað í Excel sem sveigjanlegri og öflugri valkostur fyrir VPR. Greinin Using INDEX og MATCH í stað VLOOKUP mun útskýra fullkomlega hvernig þessar aðgerðir vinna saman.
Ég ætla aðeins að rifja upp lykilatriðin:
- virka FYRIR MEIRA (MATCH) leitar að gildi á tilteknu bili og skilar hlutfallslegri stöðu þess, það er röðinni og/eða dálknum;
- Næst, aðgerðin INDEX (INDEX) skilar gildi úr tilteknum dálki og/eða línu.
Til að formúla INDEX+MATCH gæti leitað með hástöfum, þú þarft aðeins að bæta einni aðgerð við hana. Það er ekki erfitt að giska á hvað það er aftur NÁKVÆMLEGA (NÁKVÆMLEGA):
=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))
Í þessari formúlu NÁKVÆMLEGA virkar á sama hátt og í tengslum við fallið SKOÐA, og gefur sömu niðurstöðu:
Athugið að formúlan INDEX+MATCH með krulluðum axlaböndum er fylkisformúla og þú verður að klára hana með því að ýta á Ctrl + Shift + Sláðu inn.
Af hverju er INDEX+MATCH besta lausnin fyrir leit sem er há- og lágstafanæmir?
Helstu kostir búntsins INDEX и FYRIR MEIRA:
- Þarf ekki að bæta við aukadálki, ólíkt VPR.
- Þarf ekki að leitardálkurinn sé flokkaður, ólíkt því SKOÐA.
- Virkar með allar tegundir gagna - tölur, texta og dagsetningar.
Þessi formúla virðist fullkomin, er það ekki? Reyndar er það ekki. Og þess vegna.
Gerum ráð fyrir að hólfið í skilagildisdálknum sem tengist uppflettigildinu sé tómt. Hvaða niðurstöðu mun formúlan skila? Nei? Við skulum sjá hverju formúlan skilar í raun:
Úbbs, formúlan skilar núlli! Þetta gæti ekki verið mikið vandamál ef þú ert að vinna með hrein textagildi. Hins vegar, ef taflan inniheldur tölur, þar á meðal „raunveruleg“ núll, verður þetta vandamál.
Reyndar haga allar aðrar uppflettingarformúlur (VLOOKUP, LOOKUP og SUMPRODUCT) sem við ræddum áðan á sama hátt. En þú vilt hina fullkomnu formúlu, ekki satt?
Til að gera formúlu hástafaviðkvæma INDEX+MATCH fullkomið, settu það í fall IF (IF) sem mun prófa reit með skilgildi og skila tómri niðurstöðu ef hún er tóm:
=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));"")
Í þessari formúlu:
- B er dálkur með skilgildum
- 1+ er tala sem snýr hlutfallslegri stöðu frumunnar sem fallið skilar FYRIR MEIRA, á raunverulegt heimilisfang frumunnar. Til dæmis, í hlutverki okkar FYRIR MEIRA leitarfylki gefið upp A2: A7, það er hlutfallsleg staða frumunnar A2 mun 1, vegna þess að það er sá fyrsti í fylkinu. En raunveruleg staða frumunnar A2 í dálkinum er 2, svo við bætum við 1að bæta upp mismuninn og hafa hlutverkið ÓBEIN (ÓBEIN) sótti gildið úr reitnum sem óskað er eftir.
Myndirnar hér að neðan sýna leiðrétta formúlu sem er há- og hástöfum INDEX+MATCH Í aðgerð. Það skilar tómri niðurstöðu ef hólfið sem skilað er er tómt.
Ég endurskrifaði formúluna í dálka B:Dtil að passa formúlustikuna á skjámyndinni.
Formúla snýr aftur 0ef hólfið sem skilað er inniheldur núll.
Ef þú vilt hlekkinn INDEX и FYRIR MEIRA birt einhver skilaboð þegar skilagildið er tómt, þú getur skrifað það í síðustu gæsalappir (“”) í formúlunni, til dæmis, svona:
=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.")
