Innhold
Denne lille opplæringen forklarer hvordan du lager en funksjon VPR (VLOOKUP) skiller mellom store og små bokstaver, viser flere andre formler som Excel kan søke på på en måte som skiller mellom store og små bokstaver, og påpeker styrker og svakheter ved hver funksjon.
Jeg antar at hver Excel-bruker vet hvilken funksjon som utfører vertikalt søk. Det stemmer, det er en funksjon VPR. Det er imidlertid få mennesker som vet det VPR skiller ikke mellom store og små bokstaver, dvs. små og store bokstaver er identiske for den.
Her er et raskt eksempel som viser manglende evne VPR gjenkjenne registeret. Anta i en celle A1 inneholder verdien "bill" og cellen A2 – «Bill», formel:
=VLOOKUP("Bill",A1:A10,2)
=ВПР("Bill";A1:A10;2)
… vil stoppe søket på "bill" siden den verdien kommer først i listen, og trekke ut verdien fra cellen B1.
Senere i denne artikkelen skal jeg vise deg hvordan du gjør VPR skiller mellom store og små bokstaver. I tillegg skal vi lære noen flere funksjoner som kan utføre store og små bokstaver i Excel.
Vi starter med det enkleste – SE (OPSLAKK) og SUMPRODUCT (SUMPRODUKT), som dessverre har flere betydelige begrensninger. Deretter skal vi se nærmere på den litt mer komplekse formelen INDEKS+SAMLING (INDEX+MATCH), som fungerer feilfritt i enhver situasjon og med ethvert datasett.
VLOOKUP-funksjonen skiller mellom store og små bokstaver
Som du allerede vet, den vanlige funksjonen VPR er ufølsom for store og små bokstaver. Det er imidlertid en måte å gjøre det skille mellom store og små bokstaver. For å gjøre dette må du legge til en hjelpekolonne i tabellen, som vist i følgende eksempel.
Anta i en kolonne B det er produktidentifikatorer (vare) og du vil trekke ut prisen på produktet og den tilhørende kommentaren fra kolonnene C и D. Problemet er at identifikatorer inneholder både små og store bokstaver. For eksempel celleverdier B4 (001Tvci3u) og B5 (001Tvci3U) avviker bare når det gjelder det siste tegnet, u и U henholdsvis.
Som du kan forestille deg, den vanlige søkeformelen
=VLOOKUP("001Tvci3U",$A$2:$C$7,2,FALSE)
=ВПР("001Tvci3U";$A$2:$C$7;2;ЛОЖЬ)
vil returnere $ 90, siden verdien 001Tvci3u er i søkeområdet tidligere enn 001Tvci3U. Men det er vel ikke det vi trenger?
For å søke med en funksjon VPR i Excel må du legge til en hjelpekolonne og fylle cellene med følgende formel (der B er oppslagskolonnen):
=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));"")
Denne formelen deler den ønskede verdien inn i separate tegn, erstatter hvert tegn med sin kode (for eksempel i stedet for A på 65 i stedet a kode 97) og kombinerer deretter disse kodene til en unik tallstreng.
Etter det bruker vi en enkel funksjon VPR for store og små bokstaver:
=VLOOKUP($G$3,$A$2:$C$8,3,FALSE)
=ВПР($G$3;$A$2:$C$8;3;ЛОЖЬ)
Riktig drift av funksjonen VPR skille mellom store og små bokstaver avhenger av to faktorer:
- Hjelperkolonnen må være kolonnen lengst til venstre i det synlige området.
- Verdien du leter etter må inneholde en tegnkode i stedet for den virkelige verdien.
Hvordan bruke KODE-funksjonen riktig
Formelen satt inn i cellene i hjelpekolonnen antar at alle søkeverdiene dine har samme antall tegn. Hvis ikke, må du vite de minste og største tallene og legge til så mange funksjoner FEIL (IFERROR) hvor mange tegn er forskjellen mellom den korteste og lengste søkte verdien.
For eksempel, hvis den korteste søkeverdien er 3 tegn og den lengste er 5 tegn, bruk denne formelen:
=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));"")
For funksjon PSTR (MID) Du angir følgende argumenter:
- 1. argument – tekst (tekst) er teksten eller cellereferansen som inneholder tegnene som skal trekkes ut (i vårt tilfelle er det B2)
- 2. argument – startnummer (start_posisjon) er posisjonen til det første av disse tegnene som skal trekkes ut. du går inn 1 i den første funksjonen PSTR, 2 – i den andre funksjonen PSTR og så videre
- 3. argument – antall_tegn (antall_tegn) – Angir antall tegn som skal trekkes ut fra teksten. Siden vi bare trenger 1 tegn hele tiden, skriver vi i alle funksjoner 1.
BEGRENSNINGER: Funksjon VPR er ikke den beste løsningen for store og små bokstaver i Excel. Først er det nødvendig å legge til en hjelpekolonne. For det andre gjør formelen en god jobb bare hvis dataene er homogene, eller det nøyaktige antallet tegn i de søkte verdiene er kjent. Hvis dette ikke er ditt tilfelle, er det bedre å bruke en av løsningene som vi viser nedenfor.
LOOKUP-funksjon for store og små bokstaver
Funksjon SE (LOOKUP) relatert VPR, men syntaksen tillater søk som skiller mellom store og små bokstaver uten å legge til en hjelpekolonne. For å gjøre dette, bruk SE kombinert med funksjonen NØYAKTIG (NØYAKTIG).
Hvis vi tar dataene fra forrige eksempel (uten en hjelpekolonne), vil følgende formel takle oppgaven:
=LOOKUP(TRUE,EXACT($A$2:$A$7,$F$2),$B$2:$B$7)
=ПРОСМОТР(ИСТИНА;СОВПАД($A$2:$A$7;$F$2);$B$2:$B$7)
Formelsøk innen rekkevidde A2: A7 eksakt samsvar med celleverdi F2 skiller mellom store og små bokstaver og returnerer verdien fra kolonne B i samme rad.
I likhet med VPRfunksjon SE fungerer likt med tekst og numeriske verdier, som du kan se på skjermbildet nedenfor:
Viktig! For funksjonen SE fungerte riktig, bør verdiene i oppslagskolonnen sorteres i stigende rekkefølge, dvs. fra minste til største.
La meg kort forklare hvordan funksjonen fungerer NØYAKTIG i formelen vist ovenfor, da dette er nøkkelpunktet.
Funksjon NØYAKTIG sammenligner de to tekstverdiene i 1. og 2. argument og returnerer TRUE hvis de er nøyaktig like, eller FALSE hvis de ikke er det. Det er viktig for oss at funksjonen NØYAKTIG skiller mellom store og små bokstaver.
La oss se hvordan formelen vår fungerer SE+EKSAKT:
=LOOKUP(TRUE,EXACT($A$2:$A$7,$F$2),$B$2:$B$7)
=ПРОСМОТР(ИСТИНА;СОВПАД($A$2:$A$7;$F$2);$B$2:$B$7)
- Funksjon NØYAKTIG sammenligner celleverdi F2 med alle elementene i en kolonne A (A2:A7). Returnerer TRUE hvis et eksakt samsvar er funnet, ellers FALSE.
- Siden du gir det første funksjonsargumentet SE verdien TRUE, trekker den ut den tilsvarende verdien fra den spesifiserte kolonnen (i vårt tilfelle, kolonne B) bare hvis det finnes et eksakt samsvar, skiller mellom store og små bokstaver.
Jeg håper denne forklaringen var klar og at du nå forstår hovedideen. I så fall vil du ikke ha noen problemer med andre funksjoner som vi vil analysere videre, fordi. de jobber alle etter samme prinsipp.
BEGRENSNINGER: Dataene i oppslagskolonnen må sorteres i stigende rekkefølge.
SUMPRODUKT – finner tekstverdier, skiller mellom store og små bokstaver, men returnerer bare tall
Som du allerede har forstått av tittelen, SUMPRODUCT (SUMPRODUKT) er en annen Excel-funksjon som vil hjelpe deg å gjøre et søk som skiller mellom store og små bokstaver, men som bare vil returnere numeriske verdier. Hvis dette alternativet ikke passer deg, kan du umiddelbart fortsette til pakken INDEKS+SAMLING, som gir en løsning for enhver sak og for alle datatyper.
Først, la meg kort forklare syntaksen til denne funksjonen, dette vil hjelpe deg bedre å forstå den store og små bokstavene som følger.
Funksjon SUMPRODUCT multipliserer elementene i de gitte matrisene og returnerer summen av resultatene. Syntaksen ser slik ut:
SUMPRODUCT(array1,[array2],[array3],...)
СУММПРОИЗВ(массив1;[массив2];[массив3];…)
Siden vi trenger et søk som skiller mellom store og små bokstaver, bruker vi funksjonen NØYAKTIG (EXAKT) fra forrige eksempel som en av multiplikatorene:
=SUMPRODUCT((EXACT($A$2:$A$7,$F$2)*($B$2:$B$7)))
=СУММПРОИЗВ((СОВПАД($A$2:$A$7;$F$2)*($B$2:$B$7)))
Som du husker, NØYAKTIG sammenligner celleverdi F2 med alle elementene i en kolonne A. Returnerer TRUE hvis et eksakt samsvar er funnet, ellers FALSE. I matematiske operasjoner tar Excel TRUE som 1, og FALSE for 0Lengre SUMPRODUCT multipliserer disse tallene og summerer resultatene.
Nuller telles ikke fordi når de multipliseres gir de alltid 0. La oss se nærmere på hva som skjer når en nøyaktig samsvar i en kolonne A funnet og returnert 1… Funksjon SUMPRODUCT multipliserer tallet i kolonnen B on 1 og returnerer resultatet – nøyaktig samme tall! Dette er fordi resultatene for de andre produktene er null, og de påvirker ikke den resulterende summen.
Dessverre funksjonen SUMPRODUCT kan ikke fungere med tekstverdier og datoer da de ikke kan multipliseres. I dette tilfellet vil du motta en feilmelding #VERDI! (#VERDI!) som i en celle F4 på bildet under:
BEGRENSNINGER: Returnerer bare numeriske verdier.
INDEX + MATCH – søk etter alle typer data
Endelig er vi nær en ubegrenset og sensitiv søkeformel som fungerer med ethvert datasett.
Dette eksemplet kommer sist, ikke fordi det beste er igjen til dessert, men fordi kunnskapen du har fått fra de tidligere eksemplene vil hjelpe deg å forstå den store og små bokstaver bedre og raskere. INDEKS+SAMLING (INDEKS+SAMLING).
Som du sikkert har gjettet, kombinasjonen av funksjoner MER UTSATT и INDEX brukes i Excel som et mer fleksibelt og kraftig alternativ for VPR. Artikkelen Using INDEX and MATCH i stedet for VLOOKUP vil perfekt forklare hvordan disse funksjonene fungerer sammen.
Jeg skal bare oppsummere hovedpunktene:
- Funksjon MER UTSATT (MATCH) søker etter en verdi i et gitt område og returnerer dens relative posisjon, det vil si rad- og/eller kolonnenummeret;
- Deretter funksjonen INDEX (INDEX) returnerer en verdi fra en spesifisert kolonne og/eller rad.
Til formel INDEKS+SAMLING kan søke med små og store bokstaver, trenger du bare å legge til én funksjon til den. Det er ikke vanskelig å gjette hva det er igjen NØYAKTIG (NØYAKTIG):
=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 denne formelen NØYAKTIG fungerer på samme måte som i forbindelse med funksjonen SE, og gir samme resultat:
Merk at formelen INDEKS+SAMLING omsluttet av krøllete klammeparenteser er en matriseformel, og du må fullføre den ved å trykke Ctrl + Shift + Enter.
Hvorfor er INDEX+MATCH den beste løsningen for store og små bokstaver?
De viktigste fordelene med bunten INDEX и MER UTSATT:
- Krever ikke å legge til en hjelpekolonne, i motsetning til VPR.
- Krever ikke at søkekolonnen er sortert, i motsetning til SE.
- Fungerer med alle typer data – tall, tekst og datoer.
Denne formelen virker perfekt, ikke sant? Faktisk er det ikke det. Og det er derfor.
Anta at cellen i returverdikolonnen knyttet til oppslagsverdien er tom. Hvilket resultat vil formelen returnere? Nei? La oss se hva formelen faktisk returnerer:
Oops, formelen returnerer null! Dette er kanskje ikke et stort problem hvis du jobber med rene tekstverdier. Men hvis tabellen inneholder tall, inkludert "ekte" nuller, blir dette et problem.
Faktisk oppfører alle de andre oppslagsformlene (VLOOKUP, LOOKUP og SUMPRODUCT) vi diskuterte tidligere på samme måte. Men du vil ha den perfekte formelen, ikke sant?
For å gjøre en formel sensitiv for store og små bokstaver INDEKS+SAMLING perfekt, sett den i en funksjon IF (IF) som vil teste en celle med en returverdi og returnere et tomt resultat hvis den er tom:
=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 denne formelen:
- B er en kolonne med returverdier
- 1+ er et tall som snur den relative posisjonen til cellen som returneres av funksjonen MER UTSATT, til den virkelige adressen til cellen. For eksempel i vår funksjon MER UTSATT søkearray gitt A2: A7, det vil si den relative posisjonen til cellen A2 vil 1, fordi det er den første i matrisen. Men den faktiske plasseringen av cellen A2 i kolonnen er 2, så legger vi til 1å gjøre opp forskjellen og ha funksjonen INDIREKTE (INDIREKTE) hentet verdien fra ønsket celle.
Bildene nedenfor viser den korrigerte formelen som skiller mellom store og små bokstaver INDEKS+SAMLING I aksjon. Den returnerer et tomt resultat hvis den returnerte cellen er tom.
Jeg skrev om formelen til kolonner B:Dfor å passe til formellinjen på skjermbildet.
Formel kommer tilbake 0hvis den returnerte cellen inneholder null.
Hvis du vil ha linken INDEX и MER UTSATT viste en melding når returverdien er tom, kan du skrive den i de siste anførselstegnene (“”) i formelen, for eksempel slik:
=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.")
