Obecný vzorec
=SUMPRODUCT(--(EXACT(val,lookup_col)),result_col)
souhrn
Vyhledávání citlivých na malá a velká písmena v aplikaci Excel
Ve výchozím nastavení nerozlišují standardní vyhledávání v aplikaci Excel velká a malá písmena. VLOOKUP i INDEX / MATCH jednoduše vrátí první shodu, ignorují velká a malá písmena.
Přímým způsobem, jak toto omezení obejít, je použít maticový vzorec založený na INDEX / MATCH s EXACT. Pokud však hledáte pouze číselné hodnoty, SUMPRODUCT + EXACT také poskytuje zajímavý a flexibilní způsob, jak vyhledávat malá a velká písmena.
V příkladu používáme následující vzorec
=SUMPRODUCT(--(EXACT(E3,B3:B8)),C3:C8)
Ačkoli tento vzorec je maticový vzorec, není nutné jej zadávat pomocí kláves Control + Shift + Enter, protože SUMPRODUCT zpracovává pole nativně.
Vysvětlení
SUMPRODUCT je navržen pro práci s poli, která násobí a poté sčítá.
V tomto případě jsme dvě pole s SUMPRODUCT: B3: B8 a C3: C8. Trik spočívá v spuštění testu hodnot ve sloupci B a následném převodu výsledných hodnot PRAVDA / NEPRAVDA na 1 a 0. Test provádíme s PŘESNOU tak, že:
EXACT(E3,B3:B8)
Který produkuje toto pole:
(NEPRAVDA; NEPRAVDA; PRAVDA; NEPRAVDA; NEPRAVDA; NEPRAVDA)
Všimněte si, že skutečná hodnota na pozici 3 je naše shoda. Pak použijeme dvojitý zápor (tj. - což je technicky „dvojitý unár“) k vynucení těchto hodnot PRAVDA / NEPRAVDA do 1 a 0. Výsledkem je toto pole:
(0; 0; 1; 0; 0; 0)
V tomto bodě výpočtu vypadá vzorec SUMPRODUCT takto:
=SUMPRODUCT((0;0;1;0;0;0),(875;750;775;675;800;825))
SUMPRODUCT pak jednoduše násobí položky v každém poli dohromady a vytvoří konečné pole:
(0; 0; 775; 0; 0; 0)
Který SUMPRODUCT pak sečte a vrátí 775.
Podstata tohoto vzorce tedy spočívá v tom, že FALSE hodnoty se používají ke zrušení všech ostatních hodnot. Jediné hodnoty, které přežijí, jsou ty, které byly PRAVDIVÉ.
Všimněte si, že protože používáme SUMPRODUCT, tento vzorec přichází s jedinečným twistem: pokud existuje více shod, SUMPRODUCT vrátí součet těchto shod. To může nebo nemusí být to, co chcete, takže buďte opatrní, pokud očekáváte více zápasů!
Nezapomeňte, že tento vzorec funguje pouze pro číselné hodnoty, protože SUMPRODUCT nezpracovává text. Pokud chcete načíst text, použijte INDEX / MATCH + EXACT.
