Innehåll
Denna lilla handledning förklarar hur man gör en funktion VPR (VLOOKUP) skiftlägeskänslig, visar flera andra formler som Excel kan söka på ett skiftlägeskänsligt sätt och pekar ut styrkorna och svagheterna för varje funktion.
Jag antar att varje Excel-användare vet vilken funktion som utför vertikal sökning. Det stämmer, det är en funktion VPR. Det är dock få som vet det VPR är inte skiftlägeskänslig, dvs. gemener och versaler är identiska för den.
Här är ett snabbt exempel som visar oförmågan VPR känna igen register. Antag i en cell A1 innehåller värdet "bill" och cellen A2 – "Bill", formel:
=VLOOKUP("Bill",A1:A10,2)
=ВПР("Bill";A1:A10;2)
… kommer att stoppa sin sökning på "bill" eftersom det värdet kommer först i listan, och extrahera värdet från cellen B1.
Senare i den här artikeln ska jag visa dig hur du gör VPR skiftlägeskänsliga. Dessutom kommer vi att lära oss några fler funktioner som kan utföra skiftlägeskänsliga sökningar i Excel.
Vi börjar med det enklaste – SE (SÖK UPP) och SUMPRODUCT (SUMPRODUKT), som tyvärr har flera betydande begränsningar. Därefter ska vi titta närmare på den lite mer komplexa formeln INDEX+MATCH (INDEX+MATCH), som fungerar felfritt i alla situationer och med vilken datauppsättning som helst.
VLOOKUP-funktionen är skiftlägeskänslig
Som du redan vet, den vanliga funktionen VPR är skiftlägesokänslig. Det finns dock ett sätt att göra det skiftlägeskänsligt. För att göra detta måste du lägga till en extra kolumn i tabellen, som visas i följande exempel.
Antag i en kolumn B det finns produktidentifierare (Artikel) och du vill extrahera produktens pris och motsvarande kommentar från kolumnerna C и D. Problemet är att identifierare innehåller både gemener och versaler. Till exempel cellvärden B4 (001Tvci3u) och B5 (001Tvci3U) skiljer sig endast i fallet med det sista tecknet, u и U respektive.
Som du kan föreställa dig, den vanliga sökformeln
=VLOOKUP("001Tvci3U",$A$2:$C$7,2,FALSE)
=ВПР("001Tvci3U";$A$2:$C$7;2;ЛОЖЬ)
kommer att återvända $ 90, eftersom värdet 001Tvci3u är i sökintervallet tidigare än 001Tvci3U. Men det är väl inte det vi behöver?
För att söka med en funktion VPR i Excel skiftlägeskänslig måste du lägga till en hjälpkolumn och fylla dess celler med följande formel (där B är uppslagskolumnen):
=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));"")
Denna formel delar upp det önskade värdet i separata tecken, ersätter varje tecken med dess kod (till exempel istället för A vid 65 istället a kod 97) och kombinerar sedan dessa koder till en unik nummersträng.
Efter det använder vi en enkel funktion VPR för skiftlägeskänslig sökning:
=VLOOKUP($G$3,$A$2:$C$8,3,FALSE)
=ВПР($G$3;$A$2:$C$8;3;ЛОЖЬ)
Korrekt funktion av funktionen VPR skiftlägeskänslig beror på två faktorer:
- Hjälpkolumnen måste vara kolumnen längst till vänster i det visningsbara området.
- Värdet du letar efter måste innehålla en teckenkod istället för det verkliga värdet.
Hur man använder KOD-funktionen korrekt
Formeln som infogas i cellerna i hjälpkolumnen förutsätter att alla dina sökvärden har samma antal tecken. Om inte, måste du känna till de minsta och största siffrorna och lägga till så många funktioner FEL (IFERROR) hur många tecken är skillnaden mellan det kortaste och längsta sökta värdet.
Till exempel, om det kortaste sökvärdet är 3 tecken och det längsta är 5 tecken, använd den här formeln:
=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));"")
För funktion PSTR (MID) Du ger följande argument:
- 1:a argumentet - text (text) är texten eller cellreferensen som innehåller tecknen som ska extraheras (i vårt fall är det B2)
- 2:a argumentet - startnummer (start_position) är positionen för det första av dessa tecken som ska extraheras. du går in 1 i den första funktionen PSTR, 2 – i den andra funktionen PSTR och så vidare
- 3:a argumentet - num_chars (antal_tecken) – Anger antalet tecken som ska extraheras från texten. Eftersom vi bara behöver 1 tecken hela tiden, skriver vi i alla funktioner 1.
BEGRÄNSNINGAR: Funktion VPR är inte den bästa lösningen för skiftlägeskänsliga sökningar i Excel. Först krävs tillägg av en hjälpkolonn. För det andra gör formeln ett bra jobb bara om uppgifterna är homogena, eller det exakta antalet tecken i de sökta värdena är känt. Om detta inte är ditt fall är det bättre att använda en av lösningarna som vi visar nedan.
LOOKUP-funktion för skiftlägeskänslig sökning
Funktion SE (LOOKUP) relaterad VPR, men dess syntax tillåter skiftlägeskänsliga sökningar utan att lägga till en extra kolumn. För att göra detta, använd SE kombinerat med funktionen EXAKT (EXAKT).
Om vi tar data från föregående exempel (utan en extra kolumn), kommer följande formel att klara uppgiften:
=LOOKUP(TRUE,EXACT($A$2:$A$7,$F$2),$B$2:$B$7)
=ПРОСМОТР(ИСТИНА;СОВПАД($A$2:$A$7;$F$2);$B$2:$B$7)
Formelsökningar inom räckvidd A2: A7 exakt matchning med cellvärdet F2 skiftlägeskänslig och returnerar värdet från kolumn B på samma rad.
Tycka om VPRfungera SE fungerar lika med text och numeriska värden, som du kan se i skärmdumpen nedan:
Viktigt! För funktionen SE fungerade korrekt bör värdena i uppslagskolumnen sorteras i stigande ordning, dvs från minsta till största.
Låt mig kort förklara hur funktionen fungerar EXAKT i formeln som visas ovan, eftersom detta är nyckelpunkten.
Funktion EXAKT jämför de två textvärdena i 1:a och 2:a argumenten och returnerar TRUE om de är exakt likadana, eller FALSE om de inte är det. Det är viktigt för oss att funktionen EXAKT skiftlägeskänsliga.
Låt oss se hur vår formel fungerar VISA+EXAKT:
=LOOKUP(TRUE,EXACT($A$2:$A$7,$F$2),$B$2:$B$7)
=ПРОСМОТР(ИСТИНА;СОВПАД($A$2:$A$7;$F$2);$B$2:$B$7)
- Funktion EXAKT jämför cellvärde F2 med alla element i en kolumn A (A2:A7). Returnerar TRUE om en exakt matchning hittas, annars FALSE.
- Eftersom du ger det första funktionsargumentet SE värde TRUE, extraherar det motsvarande värde från den angivna kolumnen (i vårt fall kolumn B) endast om en exakt matchning hittas, skiftlägeskänslig.
Jag hoppas att denna förklaring var tydlig och att du nu förstår huvudtanken. Om så är fallet kommer du inte att ha några svårigheter med andra funktioner som vi kommer att analysera vidare, eftersom. de arbetar alla på samma princip.
BEGRÄNSNINGAR: Data i uppslagskolumnen måste sorteras i stigande ordning.
SUMPRODUKT – hittar textvärden, skiftlägeskänsliga, men returnerar endast siffror
Som du redan förstått av rubriken, SUMPRODUCT (SUMPRODUKT) är en annan Excel-funktion som hjälper dig att göra en skiftlägeskänslig sökning, men som bara returnerar numeriska värden. Om det här alternativet inte passar dig kan du omedelbart fortsätta till paketet INDEX+MATCH, vilket ger en lösning för alla fall och för alla datatyper.
Låt mig först kort förklara syntaxen för den här funktionen, detta hjälper dig att bättre förstå den skiftlägeskänsliga formeln som följer.
Funktion SUMPRODUCT multiplicerar elementen i de givna arrayerna och returnerar summan av resultaten. Syntaxen ser ut så här:
SUMPRODUCT(array1,[array2],[array3],...)
СУММПРОИЗВ(массив1;[массив2];[массив3];…)
Eftersom vi behöver en skiftlägeskänslig sökning använder vi funktionen EXAKT (EXAKT) från föregående exempel som en av multiplikatorerna:
=SUMPRODUCT((EXACT($A$2:$A$7,$F$2)*($B$2:$B$7)))
=СУММПРОИЗВ((СОВПАД($A$2:$A$7;$F$2)*($B$2:$B$7)))
Som ni kommer ihåg, EXAKT jämför cellvärde F2 med alla element i en kolumn A. Returnerar TRUE om en exakt matchning hittas, annars FALSE. I matematiska operationer tar Excel TRUE som 1, och FALSK för 0Ytterligare SUMPRODUCT multiplicerar dessa siffror och summerar resultaten.
Nollor räknas inte eftersom de alltid ger när de multipliceras 0. Låt oss titta närmare på vad som händer när en exakt matchning i en kolumn A hittade och återlämnade 1… Funktion SUMPRODUCT multiplicerar talet i kolumnen B on 1 och returnerar resultatet – exakt samma siffra! Detta beror på att resultaten för de andra produkterna är noll, och de påverkar inte den resulterande summan.
Tyvärr funktionen SUMPRODUCT kan inte fungera med textvärden och datum då de inte kan multipliceras. I det här fallet kommer du att få ett felmeddelande #VÄRDE! (#VÄRDE!) som i en cell F4 på bilden nedan:
BEGRÄNSNINGAR: Returnerar endast numeriska värden.
INDEX + MATCH – skiftlägeskänslig sökning efter valfri datatyp
Slutligen är vi nära en obegränsad och skiftlägeskänslig sökformel som fungerar med vilken datauppsättning som helst.
Det här exemplet kommer sist, inte för att det bästa lämnas till efterrätt, utan för att kunskapen från de tidigare exemplen hjälper dig att förstå den skiftlägeskänsliga formeln bättre och snabbare. INDEX+MATCH (INDEX+MATCH).
Som du säkert gissat, kombinationen av funktioner MER EXPONERAD и INDEX används i Excel som ett mer flexibelt och kraftfullt alternativ för VPR. Artikeln Använda INDEX och MATCH istället för VLOOKUP kommer perfekt att förklara hur dessa funktioner fungerar tillsammans.
Jag ska bara sammanfatta de viktigaste punkterna:
- Funktion MER EXPONERAD (MATCH) söker efter ett värde i ett givet område och returnerar dess relativa position, det vill säga rad- och/eller kolumnnumret;
- Nästa, funktionen INDEX (INDEX) returnerar ett värde från en angiven kolumn och/eller rad.
Till formel INDEX+MATCH kan söka skiftlägeskänsligt behöver du bara lägga till en funktion till den. Det är inte svårt att gissa vad det är igen EXAKT (EXAKT):
=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 denna formel EXAKT fungerar på samma sätt som i kombination med funktionen SE, och ger samma resultat:
Observera att formeln INDEX+MATCH omsluten av lockiga hängslen är en matrisformel och du måste slutföra den genom att trycka på Ctrl + Skift + Enter.
Varför är INDEX+MATCH den bästa lösningen för skiftlägeskänslig sökning?
De viktigaste fördelarna med bunten INDEX и MER EXPONERAD:
- Kräver inte att lägga till en extra kolumn, till skillnad från VPR.
- Kräver inte att sökkolumnen sorteras, till skillnad från SE.
- Fungerar med alla typer av data – siffror, text och datum.
Den här formeln verkar perfekt, eller hur? Det är det faktiskt inte. Och det är varför.
Antag att cellen i returvärdeskolumnen som är associerad med uppslagsvärdet är tom. Vilket resultat kommer formeln att returnera? Nej? Låt oss se vad formeln faktiskt returnerar:
Hoppsan, formeln ger noll! Detta kanske inte är ett stort problem om du arbetar med rena textvärden. Men om tabellen innehåller siffror, inklusive "riktiga" nollor, blir detta ett problem.
Faktum är att alla andra uppslagsformler (VLOOKUP, LOOKUP och SUMPRODUCT) som vi diskuterade tidigare beter sig på samma sätt. Men du vill ha den perfekta formeln, eller hur?
För att göra en formel skiftlägeskänslig INDEX+MATCH perfekt, sätt den i en funktion IF (IF) som testar en cell med ett returvärde och returnerar ett tomt resultat om den är 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 denna formel:
- B är en kolumn med returvärden
- 1+ är ett tal som vänder den relativa positionen för cellen som returneras av funktionen MER EXPONERAD, till cellens verkliga adress. Till exempel i vår funktion MER EXPONERAD sökarray ges A2: A7dvs cellens relativa position A2 kommer 1, eftersom det är den första i arrayen. Men cellens faktiska position A2 i kolumnen är 2, så lägger vi till 1att kompensera skillnaden och ha funktionen INDIREKT (INDIREKT) hämtade värdet från den önskade cellen.
Bilderna nedan visar den korrigerade skiftlägeskänsliga formeln INDEX+MATCH I aktion. Den returnerar ett tomt resultat om den returnerade cellen är tom.
Jag skrev om formeln till kolumner B:Dför att passa formelfältet på skärmdumpen.
Formel återvänder 0om den returnerade cellen innehåller noll.
Om du vill ha länken INDEX и MER EXPONERAD visas något meddelande när returvärdet är tomt, kan du skriva det i de sista citattecken (“”) i formeln, till exempel så här:
=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.")
