tartalom
Ez a kis oktatóanyag elmagyarázza, hogyan lehet függvényt létrehozni VPR (VLOOKUP) kis- és nagybetűk megkülönböztetése, számos további képletet mutat meg, amelyekben az Excel kis- és nagybetűk megkülönböztetésével tud keresni, és rámutat az egyes funkciók erősségeire és gyengeségeire.
Gondolom, minden Excel-felhasználó tudja, hogy melyik függvény végez függőleges keresést. Így van, ez egy funkció VPR. Ezt azonban kevesen tudják VPR nem különbözteti meg a kis- és nagybetűket, azaz a kis- és nagybetűk azonosak számára.
Íme egy gyors példa a képtelenségre VPR felismeri a regisztert. Tegyük fel, hogy egy cellában A1 tartalmazza a „bill” értéket és a cellát A2 – „Számla”, képlet:
=VLOOKUP("Bill",A1:A10,2)
=ВПР("Bill";A1:A10;2)
… leállítja a keresést a „számlára”, mivel ez az érték az első helyen szerepel a listában, és kivonja az értéket a cellából B1.
A cikk későbbi részében megmutatom, hogyan kell csinálni VPR Kis-nagybetű érzékeny. Ezen kívül megtanulunk még néhány olyan függvényt, amellyel Excelben kis- és nagybetűk közötti keresést is végezhetünk.
Kezdjük a legegyszerűbbvel - Megtekintés (KERESÉS) és SUMPRODUCT (SUMPRODUCT), amelynek sajnos számos jelentős korlátja van. Ezután közelebbről megvizsgáljuk a kissé összetettebb képletet INDEX+MATCH (INDEX+MATCH), amely minden helyzetben és bármilyen adatkészlettel hibátlanul működik.
A VLOOKUP funkció megkülönbözteti a kis- és nagybetűket
Mint már tudod, a szokásos funkció VPR nem érzékeny a kis- és nagybetűkre. Van azonban egy módja annak, hogy a kis- és nagybetűket megkülönböztetővé tegye. Ehhez hozzá kell adni egy segédoszlopot a táblázathoz, ahogy az a következő példában látható.
Tegyük fel, hogy egy oszlopban B vannak termékazonosítók (Tétel) és ki szeretné húzni a termék árát és a hozzá tartozó megjegyzést az oszlopokból C и D. A probléma az, hogy az azonosítók kis- és nagybetűket is tartalmaznak. Például cellaértékek B4 (001Tvci3u) és B5 (001Tvci3U) csak az utolsó karakter esetén tér el, u и U illetőleg.
Ahogy el tudod képzelni, a szokásos keresési képlet
=VLOOKUP("001Tvci3U",$A$2:$C$7,2,FALSE)
=ВПР("001Tvci3U";$A$2:$C$7;2;ЛОЖЬ)
vissza fog térni $ 90, hiszen az érték 001Tvci3u korábban van a keresési tartományban, mint 001Tvci3U. De nem erre van szükségünk, igaz?
Keresés funkcióval VPR az Excelben a kis- és nagybetűk megkülönböztetése esetén hozzá kell adnia egy segédoszlopot, és ki kell töltenie a celláit a következő képlettel (ahol B a keresőoszlop):
=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));"")
Ez a képlet külön karakterekre bontja a kívánt értéket, minden karaktert a kódjával helyettesít (például ahelyett A helyette 65 évesen a kód 97), majd ezeket a kódokat egyedi számsorokká egyesíti.
Ezt követően egy egyszerű függvényt használunk VPR kis- és nagybetűk közötti kereséshez:
=VLOOKUP($G$3,$A$2:$C$8,3,FALSE)
=ВПР($G$3;$A$2:$C$8;3;ЛОЖЬ)
A funkció megfelelő működése VPR A kis- és nagybetűk megkülönböztetése két tényezőtől függ:
- A segítő oszlopnak a látható tartomány bal szélső oszlopának kell lennie.
- A keresett értéknek karakterkódot kell tartalmaznia a valódi érték helyett.
A CODE funkció helyes használata
A segédoszlop celláiba beillesztett képlet feltételezi, hogy az összes keresési érték azonos számú karakterből áll. Ha nem, akkor ismernie kell a legkisebb és legnagyobb számokat, és annyi funkciót kell hozzáadnia IFROROR (IFERROR), hány karakter a különbség a legrövidebb és a leghosszabb keresett érték között.
Például, ha a legrövidebb keresési érték 3 karakter, a leghosszabb pedig 5 karakter, használja ezt a képletet:
=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));"")
A funkcióért PSTR (MID) Ön a következő érveket adja meg:
- 1. érv – szöveg (szöveg) a kivonandó karaktereket tartalmazó szöveg- vagy cellahivatkozás (esetünkben B2)
- 2. érv – kezdő_szám (start_pozíció) az elsőként kibontandó karakterek pozíciója. belépsz 1 az első funkcióban PSTR, 2 – a második funkcióban PSTR elvisszük helyi falvakba ahol megismerkedhet az őslakosok kultúrájával; ...
- 3. érv – szám_száma (karakterek_száma) – Megadja a szövegből kivonandó karakterek számát. Mivel mindig csak 1 karakterre van szükségünk, ezért minden függvényben írunk 1.
KORLÁTOK: Funkció VPR nem a legjobb megoldás a kis- és nagybetűk megkülönböztetésére az Excelben. Először egy segédoszlop hozzáadása szükséges. Másodszor, a képlet csak akkor működik jól, ha az adatok homogének, vagy a keresett értékek pontos karakterszáma ismert. Ha ez nem az Ön esete, akkor jobb, ha az alábbiakban bemutatott megoldások egyikét használja.
LOOKUP funkció a kis- és nagybetűk megkülönböztetéséhez
Funkció Megtekintés (LOOKUP) kapcsolatos VPR, azonban szintaxisa lehetővé teszi a kis- és nagybetűkre érzékeny keresést segédoszlop hozzáadása nélkül. Ehhez használja Megtekintés funkcióval kombinálva PONTOS (PONTOS).
Ha az előző példából vesszük az adatokat (segédoszlop nélkül), akkor a következő képlet fog megbirkózni a feladattal:
=LOOKUP(TRUE,EXACT($A$2:$A$7,$F$2),$B$2:$B$7)
=ПРОСМОТР(ИСТИНА;СОВПАД($A$2:$A$7;$F$2);$B$2:$B$7)
A képlet tartományban keres A2: A7 pontos egyezés a cella értékével F2 megkülönbözteti a kis- és nagybetűket, és ugyanazon sor B oszlopából adja vissza az értéket.
Mint VPRfunkció Megtekintés ugyanúgy működik szöveges és numerikus értékekkel, amint az az alábbi képernyőképen látható:
Fontos! A funkció érdekében Megtekintés megfelelően működött, a kereső oszlopban lévő értékeket növekvő sorrendbe kell rendezni, azaz a legkisebbtől a legnagyobbig.
Hadd magyarázzam el röviden a függvény működését PONTOS a fenti képletben, mivel ez a kulcspont.
Funkció PONTOS összehasonlítja a két szövegértéket az 1. és 2. argumentumban, és IGAZ értéket ad vissza, ha pontosan megegyezik, vagy FALSE-t, ha nem. Számunkra fontos, hogy a funkció PONTOS Kis-nagybetű érzékeny.
Lássuk, hogyan működik a képletünk NÉZET+PONTOS:
=LOOKUP(TRUE,EXACT($A$2:$A$7,$F$2),$B$2:$B$7)
=ПРОСМОТР(ИСТИНА;СОВПАД($A$2:$A$7;$F$2);$B$2:$B$7)
- Funkció PONTOS cellaértéket hasonlít össze F2 minden elemével egy oszlopban A (A2:A7). IGAZ értéket ad vissza, ha pontos egyezést talál, ellenkező esetben HAMIS értéket ad vissza.
- Mivel megadod az első függvény argumentumot Megtekintés érték IGAZ, csak akkor bontja ki a megfelelő értéket a megadott oszlopból (esetünkben a B oszlopból), ha pontos egyezést talál, kis- és nagybetűk megkülönböztetése.
Remélem, ez a magyarázat világos volt, és most már érted a fő gondolatot. Ha igen, akkor nem lesz nehézsége más funkciókkal, amelyeket tovább elemezünk, mert. mindegyik ugyanazon az elven működik.
KORLÁTOK: A keresési oszlopban lévő adatokat növekvő sorrendben kell rendezni.
SUMPRODUCT – szöveges értékeket keres, kis- és nagybetűk megkülönböztetik, de csak számokat ad vissza
Ahogy a címből már értetted, SUMPRODUCT (SUMPRODUCT) egy másik Excel-függvény, amely segít a kis- és nagybetűk megkülönböztetésében, de csak numerikus értékeket ad vissza. Ha ez az opció nem felel meg Önnek, akkor azonnal folytathatja a csomagot INDEX+MATCH, amely minden esetre és bármilyen adattípusra ad megoldást.
Először is engedjék meg, hogy röviden elmagyarázzam ennek a függvénynek a szintaxisát, ez segít jobban megérteni a következő, kis- és nagybetűkre érzékeny képletet.
Funkció SUMPRODUCT megszorozza az adott tömbök elemeit, és visszaadja az eredmények összegét. A szintaxis így néz ki:
SUMPRODUCT(array1,[array2],[array3],...)
СУММПРОИЗВ(массив1;[массив2];[массив3];…)
Mivel szükségünk van a kis- és nagybetűk megkülönböztetésére, a függvényt használjuk PONTOS (PONTOS) az előző példából, mint az egyik szorzó:
=SUMPRODUCT((EXACT($A$2:$A$7,$F$2)*($B$2:$B$7)))
=СУММПРОИЗВ((СОВПАД($A$2:$A$7;$F$2)*($B$2:$B$7)))
Amint emlékszel, PONTOS cellaértéket hasonlít össze F2 minden elemével egy oszlopban A. Ha pontos egyezést talál, IGAZ értéket ad vissza, ellenkező esetben HAMIS értéket ad vissza. A matematikai műveletekben az Excel az IGAZ értéket veszi fel 1, és HAMIS a 0További SUMPRODUCT megszorozza ezeket a számokat, és összeadja az eredményeket.
A nullákat nem számolják, mert szorozva mindig adnak 0. Nézzük meg közelebbről, mi történik, ha egy oszlopban pontos egyezés van A megtalálta és visszavitte 1… Funkció SUMPRODUCT megszorozza az oszlopban lévő számot B on 1 és visszaadja az eredményt – pontosan ugyanannyit! Ennek az az oka, hogy a többi termék eredménye nulla, és nem befolyásolja a kapott összeget.
Sajnos a funkció SUMPRODUCT nem működhet szöveges értékekkel és dátumokkal, mivel nem szorozhatók. Ebben az esetben hibaüzenetet fog kapni #ÉRTÉK! (#ÉRTÉK!), mint egy cellában F4 az alábbi képen:
KORLÁTOK: Csak numerikus értékeket ad vissza.
INDEX + MATCH – kis- és nagybetűk megkülönböztetése bármilyen adattípusra
Végül közel állunk egy korlátlan és kis- és nagybetűkre érzékeny keresési képlethez, amely bármilyen adatkészlettel működik.
Ez a példa az utolsó, nem azért, mert a legjobb a desszertre marad, hanem azért, mert az előző példákból szerzett ismeretek segítségével jobban és gyorsabban megértheti a kis- és nagybetűket érzékeny képletet. INDEX+MATCH (INDEX+MATCH).
Ahogy valószínűleg sejtette, a funkciók kombinációja KIÉPÍTETTebb и INDEX az Excelben rugalmasabb és hatékonyabb alternatívaként használják VPR. Az INDEX és a MATCH használata a VLOOKUP helyett cikk tökéletesen elmagyarázza, hogyan működnek együtt ezek a függvények.
Csak összefoglalom a legfontosabb tudnivalókat:
- Funkció KIÉPÍTETTebb (MATCH) keres egy értéket egy adott tartományban, és visszaadja annak relatív pozícióját, azaz a sor és/vagy oszlop számát;
- Ezután a funkció INDEX (INDEX) értéket ad vissza egy megadott oszlopból és/vagy sorból.
A képlethez INDEX+MATCH kis- és nagybetűk megkülönböztetésével kereshet, csak egy függvényt kell hozzáadnia hozzá. Már megint nem nehéz kitalálni, mi az PONTOS (PONTOS):
=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))
Ebben a képletben PONTOS ugyanúgy működik, mint a funkcióval együtt Megtekintés, és ugyanazt az eredményt adja:
Vegye figyelembe, hogy a képlet INDEX+MATCH kapcsos kapcsos zárójelek közé van zárva egy tömbképlet, amelyet a megnyomásával kell kitöltenie Ctrl + Shift + Enter.
Miért az INDEX+MATCH a legjobb megoldás a kis- és nagybetűk megkülönböztetésére?
A köteg fő előnyei INDEX и KIÉPÍTETTebb:
- Nem szükséges segédoszlop hozzáadása, ellentétben VPR.
- Nem igényli a keresőoszlop rendezését, ellentétben Megtekintés.
- Minden típusú adattal működik – számokkal, szövegekkel és dátumokkal.
Ez a képlet tökéletesnek tűnik, nem igaz? Valójában nem az. És ezért.
Tételezzük fel, hogy a keresési értékhez tartozó visszatérési érték oszlop cellája üres. Milyen eredményt ad vissza a képlet? Nem? Lássuk, mit ad vissza a képlet valójában:
Hoppá, a képlet nullát ad vissza! Ez nem lehet nagy probléma, ha tiszta szöveges értékekkel dolgozik. Ha azonban a táblázat számokat tartalmaz, beleértve a „valódi” nullákat is, ez problémát jelent.
Valójában az összes többi keresési képlet (VLOOKUP, LOOKUP és SUMPRODUCT), amelyeket korábban tárgyaltunk, ugyanúgy viselkedik. De te a tökéletes formulát akarod, igaz?
A képlet kis- és nagybetűvé tétele INDEX+MATCH tökéletes, tedd függvénybe IF (IF), amely egy cellát egy visszatérési értékkel tesztel, és üres eredményt ad vissza, ha az üres:
=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));"")
Ebben a képletben:
- B egy oszlop visszatérési értékekkel
- 1+ egy olyan szám, amely elfordítja a függvény által visszaadott cella relatív helyzetét KIÉPÍTETTebb, a cella valódi címére. Például a mi funkciónkban KIÉPÍTETTebb keresési tömb adott A2: A7, vagyis a cella relatív helyzete A2 lesz 1, mert ez az első a tömbben. De a sejt tényleges helyzete A2 az oszlopban van 2, így hozzátesszük 1hogy kiegyenlítse a különbséget és meglegyen a funkciója KÖZVETETT (INDIRECT) lekérte az értéket a kívánt cellából.
Az alábbi képeken a javított kis- és nagybetűk képlet látható INDEX+MATCH Akcióban. Üres eredményt ad vissza, ha a visszaadott cella üres.
A képletet átírtam oszlopokba B:Dhogy a képletsor illeszkedjen a képernyőképen.
A Formula visszatér 0ha a visszaadott cella nullát tartalmaz.
Ha akarod a linket INDEX и KIÉPÍTETTebb üzenet jelenik meg, amikor a visszatérési érték üres, beírhatja a képlet utolsó idézőjelébe (""), például így:
=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.")
