✅ Kako narediti Excel VLOOKUP občutljiv na velike in male črke

Kazalo

Naj bo VLOOKUP občutljiv na velike in male črke

Privzeto iskalna vrednost v funkciji VLOOKUP ne razlikuje velikih in malih črk. Če je na primer vrednost iskanja MATT, mat ali Matt, je za funkcijo VLOOKUP vse enako. Vrnila bo prvo ujemajočo se vrednost ne glede na primer.

Naj bo VLOOKUP občutljiv na velike in male črke

Recimo, da imate podatke, kot je prikazano spodaj:

Kot lahko vidite, obstajajo tri celice z istim imenom (A2, A4 in A5), vendar z drugačno črko. Na desni (v E2: F4) imamo tri imena (Matt, MATT in mat) skupaj z njihovimi rezultati v matematiki.

Funkcija Excel VLOOKUP ni opremljena za obravnavanje velikih in malih črk iskalnih vrednosti. V tem zgornjem primeru, ne glede na to, kakšna je vrednost iskalne vrednosti (Matt, MATT ali mat), bo vedno vrnilo 38 (kar je prva ujemajoča se vrednost).

V tej vadnici se boste naučili, kako narediti VLOOKUP občutljivo na velike in male črke:

Uporaba stolpca za pomoč.
Brez uporabe stolpca pomočnika in uporabe formule.

Določanje občutljivosti velikih in malih črk VLOOKUP - uporaba stolpca za pomoč

Pomožni stolpec lahko uporabite za pridobitev edinstvene iskalne vrednosti za vsak element v iskalnem nizu. To pomaga razlikovati med imeni z različnimi črkami.

Tu so naslednji koraki:

Levo od stolpca vstavite pomožni stolpec, od koder želite pridobiti podatke. V spodnjem primeru morate pomožni stolpec vstaviti med stolpca A in C.
V pomožni stolpec vnesite formulo = ROW (). V vsako celico bo vstavil številko vrstice.
Uporabite naslednjo formulo v celici F2, da dobite rezultat iskanja glede na velike in male črke.
```
= VLOOKUP (MAX (TOČNO (E2, $ A $ 2: $ A $ 9)*(ROW ($ A $ 2: $ A $ 9))), $ B $ 2: $ C $ 9,2,0)
```
Kopirajte ga za preostale celice (F3 in F4).

Opomba: Ker je to matrična formula, namesto samo vnesite kombinacijo tipk Control + Shift + Enter.

Kako to deluje?

Razčlenimo formulo, da razumemo, kako deluje:

EXACT (E2, $ A $ 2: $ A $ 9) - Ta del primerja iskalno vrednost v E2 z vsemi vrednostmi v A2: A9. Vrne polje TRUEs/FALSEs, kjer se TRUE vrne, če obstaja natančno ujemanje. V tem primeru, če je vrednost v E2 Matt, bi vrnila naslednjo matriko:
{TRUE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE}.
TOČNO (E2, $ A $ 2: $ A $ 9)*(ROW ($ A $ 2: $ A $ 9) - Ta del pomnoži niz TRUE/FALSE s številko vrstice A2: A9. Kjer koli je TRUE, je poda številko vrstice, drugače pa 0. V tem primeru bi vrnilo {2; 0; 0; 0; 0; 0; 0; 0}.
MAX (TOČNO (E2, $ A $ 2: $ A $ 9)*(ROW ($ A $ 2: $ A $ 9))) - Ta del vrne največjo vrednost iz niza številk. V tem primeru bi vrnil 2 (to je številka vrstice, kjer se natančno ujema).
Zdaj preprosto uporabimo to številko kot iskalno vrednost in uporabimo iskalno polje kot B2: C9.

Opomba: Pomočni stolpec lahko vstavite kjer koli v naboru podatkov. Prepričajte se le, da je levo od stolpca, od koder želite pridobiti podatke. Nato morate ustrezno prilagoditi številko stolpca v funkciji VLOOKUP.

Če niste ljubitelj pomožnega stolpca, lahko poiščete tudi velike in male črke brez stolpca pomočnika.

Naj bo VLOOKUP občutljiv na velike in male črke - brez stolpca za pomoč

Tudi če ne želite uporabljati stolpca za pomoč, morate še vedno imeti navidezni stolpec za pomoč. Ta navidezni stolpec ni del delovnega lista, ampak je sestavljen po formuli (kot je prikazano spodaj).

Tu je formula, ki vam bo dala rezultat brez stolpca za pomoč:

= VLOOKUP (MAX (TOČNO (D2, $ A $ 2: $ A $ 9)*(ROW ($ A $ 2: $ A $ 9))), CHOOSE ({1,2}, ROW ($ A $ 2: $ A $ 9) , $ B $ 2: $ B $ 9), 2,0)

Kako to deluje?

Formula uporablja tudi koncept pomožnega stolpca. Razlika je v tem, da namesto da stolpec pomočnika vnesete na delovni list, ga upoštevajte kot podatke navideznega pomočnika, ki so del formule.

Tu je del, ki deluje kot pomožni podatki (označen z oranžno):

= VLOOKUP (MAX (TOČNO (D2, $ A $ 2: $ A $ 9)*(ROW ($ A $ 2: $ A $ 9))),IZBERITE ({1,2}, ROW ($ A $ 2: $ A $ 9), $ B $ 2: $ B $ 9),2,0)

Naj vam pokažem, kaj mislim s podatki virtualnih pomočnikov.

Na zgornji sliki, ko izberem del formule IZBERI in pritisnem F9, se prikaže rezultat, ki bi ga dala formula IZBERI.

Rezultat je {2,38; 3,88; 4,57; 5,82; 6,55; 7,44; 8,75; 9,38}

To je matrika, kjer vejica predstavlja naslednjo celico v isti vrstici, podpičje pa pomeni, da so naslednji podatki v naslednji vrstici. Ta formula torej ustvarja 2 stolpca podatkov - en stolpec ima številko vrstice, drugi pa matematični rezultat.

Zdaj, ko uporabljate funkcijo VLOOKUP, preprosto poišče iskalno vrednost v prvem stolpcu (teh podatkov navideznega 2 stolpca) in vrne ustrezno oceno. Iskalna vrednost je številka, ki jo dobimo s kombinacijo funkcije MAX in EXACT.

Prenesite datoteko z vzorcem