Alapértelmezés szerint a VLOOKUP funkció keresési értéke nem különbözteti meg a kis- és nagybetűket. Például, ha a keresési értéke MATT, matt vagy Matt, akkor a VLOOKUP függvény esetében is ugyanaz. Esettől függetlenül visszaadja az első egyező értéket.
A VLOOKUP kis- és nagybetűk megkülönböztetése
Tegyük fel, hogy megvannak az alábbi adatok:
Amint láthatja, három cella van azonos nevű (A2, A4 és A5), de különböző betűkkel. A jobb oldalon (az E2: F4 -ben) a három név (Matt, MATT és matt) látható a matematikai pontszámokkal együtt.
Az Excel VLOOKUP funkciója nem alkalmas a kis- és nagybetűket megkülönböztető keresési értékek kezelésére. Ebben a fenti példában, függetlenül attól, hogy milyen keresési érték eset (Matt, MATT vagy matt), mindig 38 -at ad vissza (ez az első egyező érték).
Ebben az oktatóanyagban megtudhatja, hogyan teheti a VLOOKUP kis- és nagybetűit érzékenyné:
- Segítő oszlop használata.
- Segítő oszlop és képlet használata nélkül.
A VLOOKUP kis- és nagybetűk megkülönböztetése - Segítő oszlop használata
Segédoszlop segítségével egyedi keresési értéket kaphat a keresési tömb minden eleméhez. Ez segít megkülönböztetni a különböző betűket tartalmazó neveket.
Ehhez tegye a következő lépéseket:
- Helyezzen be egy segédoszlopot az oszlop bal oldalán, ahonnan le szeretné kérni az adatokat. Az alábbi példában be kell illesztenie a segítő oszlopot az A és C oszlop közé.
- A segítő oszlopba írja be a képletet = ROW (). Minden sorba beilleszti a sorszámot.
- Használja a következő képletet az F2 cellában, hogy megkapja a kis- és nagybetűket.
= VLOOKUP (MAX (PONTOS (E2, $ A $ 2: $ A $ 9)*(SOR ($ A $ 2: $ A $ 9)))), $ B $ 2: $ C $ 9,2,0)
- Másolja be a többi cellába (F3 és F4).
Jegyzet: Mivel ez egy tömbképlet, használja a Control + Shift + Enter billentyűkombinációt az enter helyett.
Hogy működik ez?
Bontsuk le a képletet, hogy megértsük, hogyan működik:
- PONTOS (E2, $ A $ 2: $ A $ 9) - Ez a rész összehasonlítja az E2 keresési értékét az A2: A9 összes értékével. Egy IGAZ/HAMIS tömböt ad vissza, ahol az IGAZ értéket adja vissza, ha pontos egyezés van. Ebben az esetben, ha az E2 értéke Matt, a következő tömböt adja vissza:
{IGAZ; HAMIS; HAMIS; HAMIS; HAMIS; HAMIS; HAMIS; HAMIS}. - Pontos megadja a sorszámot, különben 0.
- MAX (PONTOS (E2, $ A $ 2: $ A $ 9)*(SOR ($ A $ 2: $ A $ 9)))) - Ez a rész a számtömb maximális értékét adja vissza. Ebben az esetben 2 -t adna vissza (ez az a sor, ahol pontos egyezés van).
- Most egyszerűen ezt a számot használjuk keresési értékként, a keresési tömböt pedig B2: C9 -ként.
Megjegyzés: A segédoszlopot bárhová beillesztheti az adatkészletbe. Csak győződjön meg arról, hogy az oszlop bal oldalán található, ahonnan le szeretné tölteni az adatokat. Ezután a VLOOKUP funkció oszlopszámát kell ennek megfelelően módosítania.
Ha most nem rajong a segítő oszlopért, akkor a segédoszlop nélkül kis- és nagybetűk között is kereshet.
A VLOOKUP kis- és nagybetűinek érzékenyítése - a Segítő oszlop nélkül
Még ha nem is szeretné használni a segítő oszlopot, akkor is rendelkeznie kell egy virtuális segítő oszloppal. Ez a virtuális oszlop nem része a munkalapnak, hanem a képleten belül van felépítve (ahogy az alább látható).
Itt van a képlet, amely megadja az eredményt a segítő oszlop nélkül:
= VLOOKUP (MAX (PONTOS (D2, $ A $ 2: $ A $ 9)*(ROW ($ A $ 2: $ A $ 9)))), CHOOSE ({1,2}, ROW ($ A $ 2: $ A $ 9) , $ B $ 2: $ B $ 9), 2,0)
Hogy működik ez?
A képlet a segítő oszlop fogalmát is használja. A különbség az, hogy ahelyett, hogy a segítő oszlopot a munkalapra tenné, tekintse azt a képlet részét képező virtuális segítő adatnak.
Itt található az a rész, amely segítő adatként működik (narancssárgával kiemelve):
= VLOOKUP (MAX (PONTOS (D2, $ A $ 2: $ A $ 9)*(SOR ($ A $ 2: $ A $ 9))),VÁLASZT ({1,2}, SOR ($ A $ 2: $ A $ 9), $ B $ 2: $ B $ 9),2,0)
Hadd mutassam meg, mit értek virtuális segítő adatok alatt.
A fenti illusztrációban, amikor kiválasztom a képlet CHOOSE részét, és megnyomom az F9 billentyűt, az azt az eredményt mutatja, amelyet a CHOOSE képlet adna.
Az eredmény {2,38; 3,88; 4,57; 5,82; 6,55; 7,44; 8,75; 9,38}
Ez egy tömb, ahol a vessző ugyanazon sor következő celláját, a pontosvessző pedig azt jelenti, hogy a következő adatok a következő sorban vannak. Ezért ez a képlet 2 adatoszlopot hoz létre - az egyik oszlopban a sorszám, a másikban pedig a matematikai pontszám szerepel.
Most, amikor a VLOOKUP függvényt használja, egyszerűen megkeresi a keresési értéket (ennek a virtuális 2 oszlopos adatnak az első oszlopában), és visszaadja a megfelelő pontszámot. A keresési érték itt egy szám, amelyet a MAX és EXACT funkciók kombinációjából kapunk.
Töltse le a példa fájlt
Van más módszer, amellyel ezt tudja? Ha igen, ossza meg velem a megjegyzések részben.
A következő VLOOKUP oktatóanyagok is tetszhetnek:
- A VLOOKUP függvény használata több kritériummal.
- A VLOOKUP függvény használata a lista utolsó számának lekéréséhez.
- VLOOKUP vs. INDEX/MATCH
- Használja az IFERROR -t a VLOOKUP funkcióval, hogy megszabaduljon a #N/A hibáktól.
