Indhold
Denne lille tutorial forklarer, hvordan man laver en funktion VPR (VLOOKUP) skelner mellem store og små bogstaver, viser adskillige andre formler, som Excel kan søge på på en måde, hvorpå der skelnes mellem store og små bogstaver, og påpeger styrkerne og svaghederne ved hver funktion.
Jeg gætter på, at enhver Excel-bruger ved, hvilken funktion der udfører lodret søgning. Det er rigtigt, det er en funktion VPR. Det er der dog kun få, der ved VPR Der skelnes ikke mellem store og små bogstaver, dvs. små og store bogstaver er identiske for det.
Her er et hurtigt eksempel, der viser manglende evne VPR genkende register. Antag i en celle A1 indeholder værdien "bill" og cellen A2 – "Bill", formel:
=VLOOKUP("Bill",A1:A10,2)
=ВПР("Bill";A1:A10;2)
... vil stoppe sin søgning på "bill", da denne værdi kommer først på listen, og udtrække værdien fra cellen B1.
Senere i denne artikel vil jeg vise dig, hvordan du gør VPR case-sensitive. Derudover vil vi lære nogle flere funktioner, der kan udføre store og små bogstaver i søgninger i Excel.
Vi starter med det enkleste – UDSIGT (OPSLAG) og SUMPRODUCT (SUMPRODUKT), som desværre har flere væsentlige begrænsninger. Dernæst vil vi se nærmere på den lidt mere komplekse formel INDEX+MATCH (INDEX+MATCH), som fungerer upåklageligt i enhver situation og med ethvert datasæt.
VLOOKUP-funktionen skelner mellem store og små bogstaver
Som du allerede ved, den sædvanlige funktion VPR er ufølsom mellem store og små bogstaver. Der er dog en måde at gøre det skelet mellem store og små bogstaver. For at gøre dette skal du tilføje en hjælpekolonne til tabellen, som vist i følgende eksempel.
Antag i en kolonne B der er produktidentifikatorer (vare), og du vil udtrække produktets pris og den tilhørende kommentar fra kolonnerne C и D. Problemet er, at identifikatorer indeholder både små og store bogstaver. For eksempel celleværdier B4 (001Tvci3u) og B5 (001Tvci3U) adskiller sig kun i tilfælde af det sidste tegn, u и U henholdsvis.
Som du kan forestille dig, den sædvanlige søgeformel
=VLOOKUP("001Tvci3U",$A$2:$C$7,2,FALSE)
=ВПР("001Tvci3U";$A$2:$C$7;2;ЛОЖЬ)
kommer tilbage $ 90, da værdien 001Tvci3u er i søgeområdet tidligere end 001Tvci3U. Men det er ikke det, vi har brug for, vel?
For at søge med en funktion VPR i Excel skal du tilføje en hjælpekolonne og udfylde dens celler med følgende formel (hvor B er opslagskolonnen):
=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 formel opdeler den ønskede værdi i separate tegn, erstatter hvert tegn med dets kode (f.eks. i stedet for A på 65 i stedet a kode 97) og kombinerer derefter disse koder til en unik række af tal.
Derefter bruger vi en simpel funktion VPR for store og små bogstaver søgning:
=VLOOKUP($G$3,$A$2:$C$8,3,FALSE)
=ВПР($G$3;$A$2:$C$8;3;ЛОЖЬ)
Korrekt funktion af funktionen VPR Der skelnes mellem store og små bogstaver i to faktorer:
- Hjælperkolonnen skal være kolonnen længst til venstre i det synlige område.
- Den værdi, du leder efter, skal indeholde en tegnkode i stedet for den reelle værdi.
Sådan bruges CODE-funktionen korrekt
Formlen, der er indsat i cellerne i hjælpekolonnen, antager, at alle dine søgeværdier har det samme antal tegn. Hvis ikke, så skal du kende de mindste og største tal og tilføje så mange funktioner FEJL (IFERROR) hvor mange tegn er forskellen mellem den korteste og længste søgte værdi.
Hvis den korteste søgeværdi f.eks. er 3 tegn, og den længste er 5 tegn, skal du bruge denne formel:
=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));"")
Til funktion PSTR (MID) Du giver følgende argumenter:
- 1. argument – tekst (tekst) er teksten eller cellereferencen, der indeholder de tegn, der skal udtrækkes (i vores tilfælde er det B2)
- 2. argument – startnummer (start_position) er positionen for det første af disse tegn, der skal udtrækkes. du indtaster 1 i den første funktion PSTR, 2 – i den anden funktion PSTR etc.
- 3. argument – antal_tegn (antal_tegn) – Angiver antallet af tegn, der skal udtrækkes fra teksten. Da vi kun skal bruge 1 tegn hele tiden, skriver vi i alle funktioner 1.
Begrænsninger: Funktion VPR er ikke den bedste løsning til store og små bogstaver i søgninger i Excel. Først kræves tilføjelsen af en hjælpekolonne. For det andet gør formlen kun et godt stykke arbejde, hvis dataene er homogene, eller det nøjagtige antal tegn i de søgte værdier er kendt. Hvis dette ikke er dit tilfælde, er det bedre at bruge en af de løsninger, som vi viser nedenfor.
LOOKUP-funktion til søgning efter store og små bogstaver
Funktion UDSIGT (OPSLAG) relateret VPR, men dens syntaks tillader søgninger, der skelner mellem store og små bogstaver, uden at tilføje en hjælpekolonne. For at gøre dette, brug UDSIGT kombineret med funktionen EKSAKT (PRÆCIS).
Hvis vi tager dataene fra det foregående eksempel (uden en hjælpekolonne), så vil følgende formel klare opgaven:
=LOOKUP(TRUE,EXACT($A$2:$A$7,$F$2),$B$2:$B$7)
=ПРОСМОТР(ИСТИНА;СОВПАД($A$2:$A$7;$F$2);$B$2:$B$7)
Formelsøgninger inden for rækkevidde A2: A7 nøjagtig match med celleværdi F2 skelner mellem store og små bogstaver og returnerer værdien fra kolonne B i samme række.
lignende VPRfunktion UDSIGT fungerer ligeligt med tekst og numeriske værdier, som du kan se på skærmbilledet nedenfor:
Vigtigt! For funktionen UDSIGT fungerede korrekt, skal værdierne i opslagskolonnen sorteres i stigende rækkefølge, dvs. fra mindste til største.
Lad mig kort forklare, hvordan funktionen fungerer EKSAKT i formlen vist ovenfor, da dette er nøglepunktet.
Funktion EKSAKT sammenligner de to tekstværdier i 1. og 2. argument og returnerer TRUE, hvis de er nøjagtigt ens, eller FALSE, hvis de ikke er det. Det er vigtigt for os, at funktionen EKSAKT case-sensitive.
Lad os se, hvordan vores formel virker SE + NØYAKTIGT:
=LOOKUP(TRUE,EXACT($A$2:$A$7,$F$2),$B$2:$B$7)
=ПРОСМОТР(ИСТИНА;СОВПАД($A$2:$A$7;$F$2);$B$2:$B$7)
- Funktion EKSAKT sammenligner celleværdi F2 med alle elementer i en kolonne A (A2:A7). Returnerer TRUE, hvis der findes et nøjagtigt match, ellers FALSK.
- Siden du giver det første funktionsargument UDSIGT værdi TRUE, udtrækker den den tilsvarende værdi fra den angivne kolonne (i vores tilfælde kolonne B), hvis der findes et nøjagtigt match, der skelnes mellem store og små bogstaver.
Jeg håber, at denne forklaring var klar, og at du nu forstår hovedideen. Hvis det er tilfældet, vil du ikke have problemer med andre funktioner, som vi vil analysere nærmere, fordi. de arbejder alle efter samme princip.
Begrænsninger: Dataene i opslagskolonnen skal sorteres i stigende rækkefølge.
SUMPRODUKT – finder tekstværdier, der skelnes mellem store og små bogstaver, men returnerer kun tal
Som du allerede har forstået ud fra titlen, SUMPRODUCT (SUMPRODUKT) er en anden Excel-funktion, der hjælper dig med at udføre en søgning, der skelner mellem store og små bogstaver, men som kun returnerer numeriske værdier. Hvis denne mulighed ikke passer dig, kan du straks fortsætte til bundtet INDEX+MATCH, som giver en løsning til enhver sag og til enhver datatype.
Først, lad mig kort forklare syntaksen for denne funktion, dette vil hjælpe dig med bedre at forstå den følgende formel, der skelner mellem store og små bogstaver.
Funktion SUMPRODUCT multiplicerer elementerne i de givne arrays og returnerer summen af resultaterne. Syntaksen ser sådan ud:
SUMPRODUCT(array1,[array2],[array3],...)
СУММПРОИЗВ(массив1;[массив2];[массив3];…)
Da vi har brug for en søgning, der skelner mellem store og små bogstaver, bruger vi funktionen EKSAKT (EXAKT) fra det foregående eksempel som en af multiplikatorerne:
=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, EKSAKT sammenligner celleværdi F2 med alle elementer i en kolonne A. Returnerer TRUE, hvis der findes et nøjagtigt match, ellers FALSK. I matematiske operationer tager Excel TRUE som 1, og FALSK for 0Længere SUMPRODUCT gange disse tal og summere resultaterne.
Nuller tælles ikke, for når de ganges, giver de altid 0. Lad os se nærmere på, hvad der sker, når en nøjagtig match i en kolonne A fundet og returneret 1… Funktion SUMPRODUCT gange tallet i kolonnen B on 1 og returnerer resultatet - nøjagtigt det samme tal! Dette skyldes, at resultaterne af de andre produkter er nul, og de påvirker ikke den resulterende sum.
Desværre funktionen SUMPRODUCT kan ikke arbejde med tekstværdier og datoer, da de ikke kan ganges. I dette tilfælde vil du modtage en fejlmeddelelse #VÆRDI! (#VALUE!) som i en celle F4 på billedet nedenfor:
Begrænsninger: Returnerer kun numeriske værdier.
INDEX + MATCH – søgning efter enhver datatype, der skelner mellem store og små bogstaver
Endelig er vi tæt på en ubegrænset og case-sensitive søgeformel, der fungerer med ethvert datasæt.
Dette eksempel kommer til sidst, ikke fordi det bedste er tilbage til dessert, men fordi den viden, der er opnået fra de tidligere eksempler, vil hjælpe dig med at forstå den store og små bogstaver bedre og hurtigere. INDEX+MATCH (INDEX+MATCH).
Som du sikkert har gættet, kombinationen af funktioner MERE UDSAT и INDEX bruges i Excel som et mere fleksibelt og kraftfuldt alternativ til VPR. Artiklen Brug af INDEX og MATCH i stedet for VLOOKUP vil perfekt forklare, hvordan disse funktioner fungerer sammen.
Jeg vil lige opsummere de vigtigste punkter:
- Funktion MERE UDSAT (MATCH) søger efter en værdi i et givet område og returnerer dens relative position, det vil sige række- og/eller kolonnenummeret;
- Dernæst funktionen INDEX (INDEX) returnerer en værdi fra en specificeret kolonne og/eller række.
Til formel INDEX+MATCH kunne søge med store og små bogstaver, behøver du kun at tilføje en funktion til den. Det er ikke svært at gætte, hvad det er igen EKSAKT (PRÆCIS):
=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 formel EKSAKT fungerer på samme måde som i forbindelse med funktionen UDSIGT, og giver samme resultat:
Bemærk, at formlen INDEX+MATCH omgivet af krøllede seler er en matrixformel, og du skal fuldføre den ved at trykke på Ctrl + Skift + Enter.
Hvorfor er INDEX+MATCH den bedste løsning til case-sensitive søgninger?
De vigtigste fordele ved bundtet INDEX и MERE UDSAT:
- Kræver ikke tilføjelse af en hjælpekolonne, i modsætning til VPR.
- Kræver ikke, at søgekolonnen skal sorteres, i modsætning til UDSIGT.
- Fungerer med alle typer data – tal, tekst og datoer.
Denne formel virker perfekt, ikke? Det er det faktisk ikke. Og det er derfor.
Antag, at cellen i kolonnen returværdi, der er knyttet til opslagsværdien, er tom. Hvilket resultat vil formlen returnere? Ingen? Lad os se, hvad formlen faktisk returnerer:
Ups, formlen returnerer nul! Dette er måske ikke et stort problem, hvis du arbejder med rene tekstværdier. Men hvis tabellen indeholder tal, inklusive "rigtige" nuller, bliver dette et problem.
Faktisk opfører alle de andre opslagsformler (VLOOKUP, LOOKUP og SUMPRODUCT), vi diskuterede tidligere, sig på samme måde. Men du vil have den perfekte formel, ikke?
For at gøre en formel følsom over for store og små bogstaver INDEX+MATCH perfekt, sæt det i en funktion IF (IF), der tester en celle med en returværdi og returnerer 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 formel:
- B er en kolonne med returværdier
- 1+ er et tal, der vender den relative position af cellen returneret af funktionen MERE UDSAT, til cellens rigtige adresse. For eksempel i vores funktion MERE UDSAT søgearray givet A2: A7, det vil sige cellens relative position A2 vilje 1, fordi det er den første i arrayet. Men den faktiske position af cellen A2 i kolonnen er 2, så vi tilføjer 1at udligne forskellen og have funktionen INDIREKTE (INDIREKTE) hentede værdien fra den ønskede celle.
Billederne nedenfor viser den korrigerede formel, der skelner mellem store og små bogstaver INDEX+MATCH I aktion. Det returnerer et tomt resultat, hvis den returnerede celle er tom.
Jeg omskrev formlen til kolonner B:Dfor at passe til formellinjen på skærmbilledet.
Formel vender tilbage 0hvis den returnerede celle indeholder nul.
Hvis du vil have linket INDEX и MERE UDSAT vist en besked, når returværdien er tom, kan du skrive den i de sidste anførselstegn ("") af formlen, for eksempel sådan:
=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.")
