Splošna formula
=SUMPRODUCT(--(EXACT(val,lookup_col)),result_col)
Povzetek
Iskanje z velikimi in malimi črkami v Excelu
Standardna iskanja v Excelu privzeto ne razlikujejo med velikimi in malimi črkami. Tako VLOOKUP kot INDEX / MATCH bosta preprosto vrnila prvo ujemanje, ne bo upoštevala velikih in malih črk.
Neposreden način za rešitev te omejitve je uporaba matrične formule, ki temelji na INDEX / MATCH in EXACT. Če iščete samo številske vrednosti, pa tudi SUMPRODUCT + EXACT ponuja zanimiv in prilagodljiv način iskanja, ki razlikuje med velikimi in malimi črkami.
V primeru uporabljamo naslednjo formulo
=SUMPRODUCT(--(EXACT(E3,B3:B8)),C3:C8)
Čeprav je ta formula matrična formula, je ni treba vnašati s tipkama Control + Shift + Enter, saj SUMPRODUCT matrike ročno obdela.
Pojasnilo
SUMPRODUCT je zasnovan za delo z nizi, ki jih pomnoži, nato sešteje.
V tem primeru smo dva polja s SUMPRODUCT: B3: B8 in C3: C8. Trik je v izvedbi testa na vrednostih v stolpcu B, nato pa nastale vrednosti TRUE / FALSE pretvorite v 1 in 0. Preizkus izvajamo z EXACT tako:
EXACT(E3,B3:B8)
Ki ustvari to matriko:
(FALSE; FALSE; TRUE; FALSE; FALSE; FALSE)
Upoštevajte, da je resnična vrednost na položaju 3 naše ujemanje. Nato z dvojnim negativom (tj. - kar je tehnično "dvojni unar") prisilimo vrednosti TRUE / FALSE v 1 in 0. Rezultat je ta matrika:
(0; 0; 1; 0; 0; 0)
Na tej točki izračuna je formula SUMPRODUCT videti tako:
=SUMPRODUCT((0;0;1;0;0;0),(875;750;775;675;800;825))
Nato SUMPRODUCT preprosto pomnoži elemente v vsaki matriki, da ustvari končno matriko:
(0; 0; 775; 0; 0; 0)
Kateri SUMPRODUCT nato sešteje in vrne 775.
Bistvo te formule je torej, da se vrednosti FALSE uporabljajo za preklic vseh drugih vrednosti. Edine vrednote, ki preživijo, so tiste, ki so bile TRUE.
Ker uporabljamo SUMPRODUCT, ima ta formula edinstven zasuk: če je več zadetkov, bo SUMPRODUCT vrnil vsoto teh ujemanj. To je morda tisto, kar si želite, zato bodite pozorni, če pričakujete več tekem!
Ne pozabite, da ta formula deluje samo za številske vrednosti, ker SUMPRODUCT ne obdeluje besedila. Če želite pridobiti besedilo, uporabite INDEX / MATCH + EXACT.
