Inhoud
Deze kleine tutorial legt uit hoe je een functie maakt VPR (VERT.ZOEKEN) hoofdlettergevoelig, toont verschillende andere formules die Excel op een hoofdlettergevoelige manier kan doorzoeken en wijst op de sterke en zwakke punten van elke functie.
Ik denk dat elke Excel-gebruiker weet welke functie verticaal zoeken uitvoert. Dat klopt, het is een functie VPR. Maar weinig mensen weten dat VPR is niet hoofdlettergevoelig, dwz de hoofdletters en kleine letters zijn identiek.
Hier is een snel voorbeeld dat het onvermogen aantoont: VPR register herkennen. Stel in een cel A1 bevat de waarde "factuur" en de cel A2 – “Factuur”, formule:
=VLOOKUP("Bill",A1:A10,2)
=ВПР("Bill";A1:A10;2)
... zal het zoeken op "factuur" stoppen omdat die waarde als eerste in de lijst staat, en de waarde uit de cel halen B1.
Verderop in dit artikel laat ik je zien hoe je dat moet doen VPR hoofdlettergevoelig. Daarnaast leren we nog enkele functies die hoofdlettergevoelige zoekopdrachten in Excel kunnen uitvoeren.
We beginnen met de eenvoudigste - BEKIJK (ZOEKEN) en SUMPRODUCT (SOMPRODUCT), die helaas verschillende belangrijke beperkingen hebben. Vervolgens zullen we de iets complexere formule van naderbij bekijken INDEX+WEDSTRIJD (INDEX+MATCH), die in elke situatie en met elke dataset feilloos werkt.
VERT.ZOEKEN-functie is hoofdlettergevoelig
Zoals je al weet, de gebruikelijke functie VPR is hoofdletterongevoelig. Er is echter een manier om het hoofdlettergevoelig te maken. Om dit te doen, moet u een hulpkolom aan de tabel toevoegen, zoals in het volgende voorbeeld.
Stel in een kolom B er zijn product-ID's (Artikel) en u wilt de prijs van het product en de bijbehorende opmerking uit de kolommen halen C и D. Het probleem is dat identifiers zowel kleine letters als hoofdletters bevatten. Bijvoorbeeld celwaarden B4 (001Tvci3u) en B5 (001Tvci3U) verschillen alleen in het geval van het laatste teken, u и U respectievelijk.
Zoals je je kunt voorstellen, de gebruikelijke zoekformule
=VLOOKUP("001Tvci3U",$A$2:$C$7,2,FALSE)
=ВПР("001Tvci3U";$A$2:$C$7;2;ЛОЖЬ)
zal terugkeren € 90, aangezien de waarde 001Tvci3u is eerder in het zoekbereik dan 001Tvci3U. Maar dat is niet wat we nodig hebben, toch?
Zoeken met een functie VPR in Excel hoofdlettergevoelig, moet u een hulpkolom toevoegen en de cellen ervan vullen met de volgende formule (waarbij B de opzoekkolom 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));"")
Deze formule splitst de gewenste waarde op in afzonderlijke tekens, vervangt elk teken door zijn code (bijvoorbeeld in plaats van A op 65, in plaats daarvan a code 97) en combineert deze codes vervolgens tot een unieke reeks cijfers.
Daarna gebruiken we een eenvoudige functie VPR voor hoofdlettergevoelig zoeken:
=VLOOKUP($G$3,$A$2:$C$8,3,FALSE)
=ВПР($G$3;$A$2:$C$8;3;ЛОЖЬ)
Correcte werking van de functie VPR hoofdlettergevoelig hangt af van twee factoren:
- De hulpkolom moet de meest linkse kolom in het zichtbare bereik zijn.
- De waarde die u zoekt, moet een tekencode bevatten in plaats van de echte waarde.
Hoe de CODE-functie correct te gebruiken
De formule die in de cellen van de hulpkolom wordt ingevoegd, gaat ervan uit dat al uw zoekwaarden hetzelfde aantal tekens hebben. Zo niet, dan moet u de kleinste en grootste getallen weten en zoveel mogelijk functies toevoegen IFFOUT (IFERROR) hoeveel tekens is het verschil tussen de kortste en langste gezochte waarde.
Als de kortste zoekwaarde bijvoorbeeld 3 tekens is en de langste 5 tekens, gebruikt u deze 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));"")
voor functie: PSTR (MID) U geeft de volgende argumenten:
- 1e argument - tekst (tekst) is de tekst- of celverwijzing die de tekens bevat die moeten worden geëxtraheerd (in ons geval is dit B2)
- 2e argument - start_getal (start_position) is de positie van de eerste van die tekens die moeten worden geëxtraheerd. jij komt binnen 1 in de eerste functie PSTR, 2 – in de tweede functie PSTR enz.
- 3e argument - aantal_tekens (aantal_tekens) – Specificeert het aantal tekens dat uit de tekst moet worden gehaald. Omdat we altijd maar 1 teken nodig hebben, schrijven we in alle functies 1.
Beperkingen: Functie VPR is niet de beste oplossing voor hoofdlettergevoelige zoekopdrachten in Excel. Ten eerste is de toevoeging van een hulpkolom vereist. Ten tweede werkt de formule alleen goed als de gegevens homogeen zijn, of als het exacte aantal tekens in de gezochte waarden bekend is. Als dit niet uw geval is, kunt u beter een van de oplossingen gebruiken die we hieronder laten zien.
LOOKUP-functie voor hoofdlettergevoelig zoeken
Functie BEKIJK (ZOEKEN) gerelateerd VPR, maar de syntaxis maakt hoofdlettergevoelige zoekopdrachten mogelijk zonder een hulpkolom toe te voegen. Gebruik hiervoor BEKIJK gecombineerd met de functie EXACT (EXACT).
Als we de gegevens uit het vorige voorbeeld nemen (zonder een hulpkolom), dan zal de volgende formule de taak aankunnen:
=LOOKUP(TRUE,EXACT($A$2:$A$7,$F$2),$B$2:$B$7)
=ПРОСМОТР(ИСТИНА;СОВПАД($A$2:$A$7;$F$2);$B$2:$B$7)
Formule zoekt binnen bereik A2: A7 exacte overeenkomst met celwaarde F2 hoofdlettergevoelig en retourneert de waarde uit kolom B van dezelfde rij.
Like VPRfunctie BEKIJK werkt op dezelfde manier met tekst en numerieke waarden, zoals je kunt zien in de onderstaande schermafbeelding:
Belangrijk! Om voor de functie: BEKIJK correct werkte, moeten de waarden in de opzoekkolom in oplopende volgorde worden gesorteerd, dat wil zeggen van klein naar groot.
Laat me kort uitleggen hoe de functie werkt EXACT in de bovenstaande formule, omdat dit het belangrijkste punt is.
Functie EXACT vergelijkt de twee tekstwaarden in de 1e en 2e argumenten en retourneert TRUE als ze precies hetzelfde zijn, of FALSE als ze dat niet zijn. Het is voor ons belangrijk dat de functie EXACT hoofdlettergevoelig.
Laten we eens kijken hoe onze formule werkt BEKIJK+EXACT:
=LOOKUP(TRUE,EXACT($A$2:$A$7,$F$2),$B$2:$B$7)
=ПРОСМОТР(ИСТИНА;СОВПАД($A$2:$A$7;$F$2);$B$2:$B$7)
- Functie EXACT vergelijkt celwaarde F2 met alle elementen in een kolom A (A2:A7). Retourneert TRUE als een exacte overeenkomst wordt gevonden, anders FALSE.
- Aangezien u het eerste functieargument geeft BEKIJK waarde WAAR, het extraheert de overeenkomstige waarde uit de opgegeven kolom (in ons geval kolom B) alleen als een exacte overeenkomst wordt gevonden, hoofdlettergevoelig.
Ik hoop dat deze uitleg duidelijk was en dat je nu de hoofdgedachte begrijpt. Zo ja, dan heb je geen problemen met andere functies die we verder zullen analyseren, want. ze werken allemaal volgens hetzelfde principe.
Beperkingen: De gegevens in de opzoekkolom moeten in oplopende volgorde worden gesorteerd.
SOMPRODUCT – vindt tekstwaarden, hoofdlettergevoelig, maar retourneert alleen getallen
Zoals je al uit de titel hebt begrepen, SUMPRODUCT (SOMPRODUCT) is een andere Excel-functie waarmee u hoofdlettergevoelig kunt zoeken, maar die alleen numerieke waarden retourneert. Mocht deze optie niet bij je passen, dan kun je direct over naar de bundel INDEX+WEDSTRIJD, die voor elk geval en voor elk gegevenstype een oplossing biedt.
Laat me eerst de syntaxis van deze functie kort uitleggen, dit zal u helpen de hoofdlettergevoelige formule die volgt beter te begrijpen.
Functie SUMPRODUCT vermenigvuldigt de elementen van de gegeven arrays en retourneert de som van de resultaten. De syntaxis ziet er als volgt uit:
SUMPRODUCT(array1,[array2],[array3],...)
СУММПРОИЗВ(массив1;[массив2];[массив3];…)
Omdat we hoofdlettergevoelig moeten zoeken, gebruiken we de functie EXACT (EXACT) uit het vorige voorbeeld als een van de vermenigvuldigers:
=SUMPRODUCT((EXACT($A$2:$A$7,$F$2)*($B$2:$B$7)))
=СУММПРОИЗВ((СОВПАД($A$2:$A$7;$F$2)*($B$2:$B$7)))
Zoals je je herinnert, EXACT vergelijkt celwaarde F2 met alle elementen in een kolom A. Retourneert TRUE als een exacte overeenkomst wordt gevonden, anders FALSE. In wiskundige bewerkingen neemt Excel TRUE als 1, en ONWAAR voor 0Verder SUMPRODUCT vermenigvuldigt deze getallen en telt de resultaten op.
Nullen worden niet meegeteld, want vermenigvuldigd geven ze altijd 0. Laten we eens nader bekijken wat er gebeurt als een exacte overeenkomst in een kolom A gevonden en teruggebracht 1... Functie SUMPRODUCT vermenigvuldigt het getal in de kolom B on 1 en retourneert het resultaat - precies hetzelfde aantal! Dit komt omdat de resultaten van de andere producten nul zijn en ze geen invloed hebben op de resulterende som.
Helaas de functie SUMPRODUCT kan niet werken met tekstwaarden en datums omdat ze niet kunnen worden vermenigvuldigd. In dit geval krijgt u een foutmelding #WAARDE! (#VALUE!) als in een cel F4 op de foto hieronder:
Beperkingen: Retourneert alleen numerieke waarden.
INDEX + MATCH - hoofdlettergevoelig zoeken voor elk gegevenstype
Ten slotte zijn we dicht bij een onbeperkte en hoofdlettergevoelige zoekformule die werkt met elke dataset.
Dit voorbeeld komt als laatste, niet omdat het beste overblijft als toetje, maar omdat de kennis die is opgedaan met de vorige voorbeelden u zal helpen de hoofdlettergevoelige formule beter en sneller te begrijpen. INDEX+WEDSTRIJD (INDEX+WEDSTRIJD).
Zoals je waarschijnlijk al geraden had, is de combinatie van functies MEER BLOOTGESTELD и INDEX gebruikt in Excel als een flexibeler en krachtiger alternatief voor VPR. Het artikel INDEX en MATCH gebruiken in plaats van VERT.ZOEKEN zal perfect uitleggen hoe deze functies samenwerken.
Ik zal even de belangrijkste punten samenvatten:
- Functie MEER BLOOTGESTELD (MATCH) zoekt naar een waarde in een bepaald bereik en retourneert de relatieve positie, dat wil zeggen het rij- en/of kolomnummer;
- Vervolgens de functie INDEX (INDEX) retourneert een waarde uit een opgegeven kolom en/of rij.
Naar formule INDEX+WEDSTRIJD hoofdlettergevoelig kan zoeken, hoeft u er maar één functie aan toe te voegen. Het is niet moeilijk om weer te raden wat het is EXACT (EXACT):
=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 deze formule EXACT werkt op dezelfde manier als in combinatie met de functie BEKIJK, en geeft hetzelfde resultaat:
Merk op dat de formule INDEX+WEDSTRIJD tussen accolades is een matrixformule en u moet deze voltooien door op te drukken Ctrl + Shift + Enter.
Waarom is INDEX+MATCH de beste oplossing voor hoofdlettergevoelig zoeken?
De belangrijkste voordelen van de bundel INDEX и MEER BLOOTGESTELD:
- Vereist geen toevoeging van een hulpkolom, in tegenstelling tot VPR.
- Vereist niet dat de zoekkolom wordt gesorteerd, in tegenstelling tot BEKIJK.
- Werkt met alle soorten gegevens - getallen, tekst en datums.
Deze formule lijkt perfect, nietwaar? Eigenlijk is het dat niet. En dat is waarom.
Stel dat de cel in de retourwaardekolom die is gekoppeld aan de opzoekwaarde leeg is. Welk resultaat levert de formule op? Nee? Laten we eens kijken wat de formule daadwerkelijk retourneert:
Oeps, de formule retourneert nul! Dit is misschien geen groot probleem als u met pure tekstwaarden werkt. Als de tabel echter getallen bevat, inclusief "echte" nullen, wordt dit een probleem.
In feite gedragen alle andere opzoekformules (VLOOKUP, LOOKUP en SOMPRODUCT) die we eerder hebben besproken zich op dezelfde manier. Maar je wilt de perfecte formule, toch?
Een formule hoofdlettergevoelig maken INDEX+WEDSTRIJD perfect, zet het in een functie IF (IF) die een cel met een geretourneerde waarde test en een leeg resultaat retourneert als deze 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 deze formule:
- B is een kolom met retourwaarden
- 1+ is een getal dat de relatieve positie van de cel verandert die wordt geretourneerd door de functie MEER BLOOTGESTELD, naar het echte adres van de cel. Bijvoorbeeld, in onze functie MEER BLOOTGESTELD zoekarray gegeven A2: A7, dat wil zeggen, de relatieve positie van de cel A2 wil 1, omdat het de eerste in de array is. Maar de werkelijke positie van de cel A2 in de kolom is 2, dus we voegen toe 1om het verschil te maken en de functie te hebben INDIRECTE (INDIRECT) heeft de waarde uit de gewenste cel opgehaald.
De afbeeldingen hieronder tonen de gecorrigeerde hoofdlettergevoelige formule INDEX+WEDSTRIJD In actie. Het retourneert een leeg resultaat als de geretourneerde cel leeg is.
Ik herschreef de formule in kolommen B: Dom in de formulebalk op de schermafbeelding te passen.
Formule keert terug 0als de geretourneerde cel nul bevat.
Als je de link wilt INDEX и MEER BLOOTGESTELD een bericht heeft weergegeven wanneer de retourwaarde leeg is, kunt u deze tussen de laatste aanhalingstekens ("") van de formule schrijven, bijvoorbeeld als volgt:
=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.")
