Contents [show]
Hierdie klein handleiding verduidelik hoe om 'n funksie te maak VPR (VLOOKUP) hooflettergevoelig, wys verskeie ander formules wat Excel op 'n hooflettergevoelige wyse kan deursoek, en wys die sterk- en swakpunte van elke funksie uit.
Ek dink elke Excel-gebruiker weet watter funksie vertikale soektog uitvoer. Dit is reg, dit is 'n funksie VPR. Min mense weet dit egter VPR is nie hooflettersensitief nie, maw die kleinletters en hoofletters is identies daarvoor.
Hier is 'n vinnige voorbeeld wat die onvermoë demonstreer VPR register herken. Gestel in 'n sel A1 bevat die waarde "bill" en die sel A2 – “Bill”, formule:
=VLOOKUP("Bill",A1:A10,2)
=ВПР("Bill";A1:A10;2)
… sal sy soektog op "bill" stop aangesien daardie waarde eerste in die lys kom, en die waarde uit die sel onttrek B1.
Later in hierdie artikel sal ek jou wys hoe om te doen VPR hooflettergevoelig. Daarbenewens sal ons nog 'n paar funksies leer wat hooflettergevoelige soektogte in Excel kan uitvoer.
Ons begin met die eenvoudigste – VIEW (OPSOEK) en SUMPRODUCT (SUMPRODUCT), wat ongelukkig verskeie beduidende beperkings het. Vervolgens gaan ons die effens meer komplekse formule van nader bekyk INDEX+MATCH (INDEX+MATCH), wat foutloos in enige situasie en met enige datastel werk.
VLOOKUP-funksie is hooflettersensitief
Soos u reeds weet, die gewone funksie VPR is hoofletteronsensitief. Daar is egter 'n manier om dit hooflettersensitief te maak. Om dit te doen, moet jy 'n hulpkolom by die tabel voeg, soos in die volgende voorbeeld getoon.
Gestel in 'n kolom B daar is produkidentifiseerders (Item) en jy wil die prys van die produk en die ooreenstemmende opmerking uit die kolomme onttrek C и D. Die probleem is dat identifiseerders beide kleinletters en hoofletters bevat. Byvoorbeeld, selwaardes B4 (001Tvci3u) en B5 (001Tvci3U) verskil slegs in die geval van die laaste karakter, u и U onderskeidelik.
Soos jy jou kan voorstel, die gewone soekformule
=VLOOKUP("001Tvci3U",$A$2:$C$7,2,FALSE)
=ВПР("001Tvci3U";$A$2:$C$7;2;ЛОЖЬ)
sal terugkeer $ 90, aangesien die waarde 001Tvci3u is vroeër in die soekreeks as 001Tvci3U. Maar dit is nie wat ons nodig het nie, is dit?
Om met 'n funksie te soek VPR in Excel hooflettersensitief, sal jy 'n helperkolom moet byvoeg en sy selle met die volgende formule moet vul (waar B die opsoekkolom is):
=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));"")
Hierdie formule breek die verlangde waarde in aparte karakters op, vervang elke karakter met sy kode (byvoorbeeld, in plaas van A op 65, in plaas daarvan a kode 97) en kombineer dan hierdie kodes in 'n unieke string nommers.
Daarna gebruik ons 'n eenvoudige funksie VPR vir hooflettergevoelige soektog:
=VLOOKUP($G$3,$A$2:$C$8,3,FALSE)
=ВПР($G$3;$A$2:$C$8;3;ЛОЖЬ)
Behoorlike werking van die funksie VPR hooflettergevoelig hang van twee faktore af:
- Die hulpkolom moet die mees linkse kolom in die sigbare reeks wees.
- Die waarde waarna jy soek moet 'n karakterkode bevat in plaas van die werklike waarde.
Hoe om die KODE-funksie korrek te gebruik
Die formule wat in die selle van die hulpkolom ingevoeg word, aanvaar dat al jou soekwaardes dieselfde aantal karakters het. Indien nie, dan moet jy die kleinste en grootste getalle ken en soveel kenmerke byvoeg IFERROR (IFERROR) hoeveel karakters is die verskil tussen die kortste en langste gesoekte waarde.
Byvoorbeeld, as die kortste soekwaarde 3 karakters is en die langste is 5 karakters, gebruik hierdie formule:
=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));"")
Vir funksie PSTR (MID) Jy verskaf die volgende argumente:
- 1ste argument - teks (teks) is die teks of selverwysing wat die karakters bevat wat onttrek moet word (in ons geval is dit B2)
- 2ste argument - begin_nommer (begin_posisie) is die posisie van die eerste van daardie karakters wat onttrek moet word. jy betree 1 in die eerste funksie PSTR, 2 – in die tweede funksie PSTR ens.
- 3ste argument - aantal_tekens (aantal_karakters) – Spesifiseer die aantal karakters om uit die teks te onttrek. Aangesien ons heeltyd net 1 karakter nodig het, skryf ons in alle funksies 1.
BEPERKINGS: funksie VPR is nie die beste oplossing vir hooflettergevoelige soektogte in Excel nie. Eerstens word die byvoeging van 'n hulpkolom vereis. Tweedens, die formule doen 'n goeie werk slegs as die data homogeen is, of die presiese aantal karakters in die gesoekte waardes bekend is. As dit nie jou geval is nie, is dit beter om een van die oplossings te gebruik wat ons hieronder wys.
OPSOEK-funksie vir hoofletter-sensitiewe soektog
funksie VIEW (OPSOEK) verwant VPR, maar sy sintaksis maak voorsiening vir hooflettersensitiewe soektogte sonder om 'n hulpkolom by te voeg. Om dit te doen, gebruik VIEW gekombineer met die funksie PRESIES (PRESIES).
As ons die data uit die vorige voorbeeld neem (sonder 'n hulpkolom), dan sal die volgende formule die taak hanteer:
=LOOKUP(TRUE,EXACT($A$2:$A$7,$F$2),$B$2:$B$7)
=ПРОСМОТР(ИСТИНА;СОВПАД($A$2:$A$7;$F$2);$B$2:$B$7)
Formulesoektogte binne reeks A2: A7 presiese ooreenstemming met selwaarde F2 hooflettergevoelig en gee die waarde van kolom B van dieselfde ry terug.
soos VPRfunksie VIEW werk gelyk met teks- en numeriese waardes, soos jy in die skermkiekie hieronder kan sien:
Belangrik! Om vir die funksie VIEW korrek gewerk het, moet die waardes in die opsoekkolom in stygende volgorde gesorteer word, dws van die kleinste na die grootste.
Laat ek kortliks verduidelik hoe die funksie werk PRESIES in die formule hierbo getoon, aangesien dit die sleutelpunt is.
funksie PRESIES vergelyk die twee tekswaardes in die 1ste en 2de argumente en gee WAAR as hulle presies dieselfde is, of ONWAAR as hulle nie is nie. Dit is vir ons belangrik dat die funksie PRESIES hooflettergevoelig.
Kom ons kyk hoe ons formule werk KYK + PRESIES:
=LOOKUP(TRUE,EXACT($A$2:$A$7,$F$2),$B$2:$B$7)
=ПРОСМОТР(ИСТИНА;СОВПАД($A$2:$A$7;$F$2);$B$2:$B$7)
- funksie PRESIES vergelyk selwaarde F2 met alle elemente in 'n kolom A (A2:A7). Wys WAAR as 'n presiese passing gevind word, anders ONWAAR.
- Aangesien jy die eerste funksie-argument gee VIEW waarde TRUE, onttrek dit die ooreenstemmende waarde uit die gespesifiseerde kolom (in ons geval, kolom B) slegs as 'n presiese passing gevind word, hooflettersensitief.
Ek hoop hierdie verduideliking was duidelik en nou verstaan jy die hoofgedagte. Indien wel, sal u geen probleme ondervind met ander funksies wat ons verder sal ontleed nie, want. hulle werk almal op dieselfde beginsel.
BEPERKINGS: Die data in die opsoekkolom moet in stygende volgorde gesorteer word.
SUMPRODUK – vind tekswaardes, hooflettersensitief, maar gee slegs syfers terug
Soos jy reeds uit die titel verstaan het, SUMPRODUCT (SUMPRODUCT) is nog 'n Excel-funksie wat jou sal help om 'n hoofletter-sensitiewe soektog te doen, maar sal net numeriese waardes terugstuur. As hierdie opsie jou nie pas nie, kan jy dadelik na die bundel voortgaan INDEX+MATCH, wat 'n oplossing gee vir enige geval en vir enige datatipes.
Laat ek eers die sintaksis van hierdie funksie kortliks verduidelik, dit sal jou help om die hooflettersensitiewe formule wat volg beter te verstaan.
funksie SUMPRODUCT vermenigvuldig die elemente van die gegewe skikkings en gee die som van die resultate terug. Die sintaksis lyk soos volg:
SUMPRODUCT(array1,[array2],[array3],...)
СУММПРОИЗВ(массив1;[массив2];[массив3];…)
Aangesien ons 'n hoofletter-sensitiewe soektog nodig het, gebruik ons die funksie PRESIES (PRESIES) van die vorige voorbeeld as een van die vermenigvuldigers:
=SUMPRODUCT((EXACT($A$2:$A$7,$F$2)*($B$2:$B$7)))
=СУММПРОИЗВ((СОВПАД($A$2:$A$7;$F$2)*($B$2:$B$7)))
Soos u onthou, PRESIES vergelyk selwaarde F2 met alle elemente in 'n kolom A. Wys WAAR as 'n presiese passing gevind word, anders ONWAAR. In wiskundige bewerkings neem Excel WAAR as 1, en ONWAAR vir 0Verdere SUMPRODUCT vermenigvuldig hierdie getalle en som die resultate op.
Nulle word nie getel nie, want wanneer dit vermenigvuldig word, gee hulle altyd 0. Kom ons kyk van nader na wat gebeur wanneer 'n presiese passing in 'n kolom A gevind en terugbesorg 1… Funksie SUMPRODUCT vermenigvuldig die getal in die kolom B on 1 en gee die resultaat terug – presies dieselfde getal! Dit is omdat die resultate van die ander produkte nul is, en dit beïnvloed nie die resulterende som nie.
Ongelukkig die funksie SUMPRODUCT kan nie met tekswaardes en datums werk nie, aangesien dit nie vermenigvuldig kan word nie. In hierdie geval sal jy 'n foutboodskap ontvang Fout # WAARDE! (#WAARDE!) soos in 'n sel F4 in die prentjie hieronder:
BEPERKINGS: Wys slegs numeriese waardes.
INDEX + MATCH – hooflettersensitiewe soektog vir enige datatipe
Ten slotte, ons is naby 'n onbeperkte en hooflettersensitiewe soekformule wat met enige datastel werk.
Hierdie voorbeeld kom laaste, nie omdat die beste vir nagereg gelaat word nie, maar omdat die kennis wat uit die vorige voorbeelde opgedoen is, jou sal help om die hooflettersensitiewe formule beter en vinniger te verstaan. INDEX+MATCH (INDEX+MATCH).
Soos jy waarskynlik geraai het, die kombinasie van funksies MEER BLOOTSTELLING и INDEKS gebruik in Excel as 'n meer buigsame en kragtige alternatief vir VPR. Die artikel Gebruik INDEX en MATCH in plaas van VLOOKUP sal perfek verduidelik hoe hierdie funksies saamwerk.
Ek sal net die sleutelpunte opsom:
- funksie MEER BLOOTSTELLING (MATCH) soek na 'n waarde in 'n gegewe reeks en gee sy relatiewe posisie terug, dit wil sê die ry- en/of kolomnommer;
- Volgende, die funksie INDEKS (INDEKS) gee 'n waarde van 'n gespesifiseerde kolom en/of ry terug.
Om te formuleer INDEX+MATCH hooflettersensitief kan soek, hoef jy net een funksie daarby te voeg. Dit is nie moeilik om weer te raai wat dit is nie PRESIES (PRESIES):
=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))
In hierdie formule PRESIES werk op dieselfde manier as in samewerking met die funksie VIEW, en gee dieselfde resultaat:
Let daarop dat die formule INDEX+MATCH ingesluit in krullerige hakies is 'n skikking formule en jy moet dit voltooi deur te druk Ctrl + Shift + Enter.
Waarom is INDEX+MATCH die beste oplossing vir hooflettergevoelige soektogte?
Die belangrikste voordele van die bundel INDEKS и MEER BLOOTSTELLING:
- Vereis nie die byvoeging van 'n hulpkolom nie, anders as VPR.
- Vereis nie dat die soekkolom gesorteer word nie, anders as VIEW.
- Werk met alle soorte data – getalle, teks en datums.
Hierdie formule lyk perfek, nie waar nie? Eintlik is dit nie. En dit is hoekom.
Aanvaar dat die sel in die terugkeerwaarde-kolom wat met die opsoekwaarde geassosieer word, leeg is. Watter resultaat sal die formule terugkeer? Geen? Kom ons kyk wat die formule eintlik gee:
Oeps, die formule gee nul terug! Dit is dalk nie 'n groot probleem as jy met suiwer tekswaardes werk nie. As die tabel egter getalle bevat, insluitend "regte" nulle, word dit 'n probleem.
Trouens, al die ander opsoekformules (VLOOKUP, LOOKUP en SUMPRODUCT) wat ons vroeër bespreek het, werk dieselfde op. Maar jy wil die perfekte formule hê, reg?
Om 'n formule hooflettergevoelig te maak INDEX+MATCH perfek, plaas dit in 'n funksie IF (IF) wat 'n sel met 'n terugkeerwaarde sal toets en 'n leë resultaat sal gee as dit leeg is:
=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));"")
In hierdie formule:
- B is 'n kolom met terugkeerwaardes
- 1+ is 'n getal wat die relatiewe posisie van die sel wat deur die funksie teruggestuur word, verander MEER BLOOTSTELLING, na die regte adres van die sel. Byvoorbeeld, in ons funksie MEER BLOOTSTELLING soekskikking gegee A2: A7, dit wil sê die relatiewe posisie van die sel A2 sal 1, want dit is die eerste een in die skikking. Maar die werklike posisie van die sel A2 in die kolom is 2, so voeg ons by 1om die verskil te maak en die funksie te hê INDIREKTE (INDIREKTE) het die waarde van die verlangde sel afgehaal.
Die prente hieronder wys die gekorrigeerde hooflettersensitiewe formule INDEX+MATCH In aksie. Dit gee 'n leë resultaat as die teruggestuurde sel leeg is.
Ek het die formule in kolomme herskryf B:Dom die formulebalk op die skermkiekie te pas.
Formule keer terug 0as die teruggekeerde sel nul bevat.
As jy die skakel wil hê INDEKS и MEER BLOOTSTELLING 'n boodskap vertoon het wanneer die terugkeerwaarde leeg is, kan jy dit in die laaste aanhalingstekens ("") van die formule skryf, byvoorbeeld, soos volg:
=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.")
