Všeobecný vzorec
=SUMPRODUCT(--(EXACT(val,lookup_col)),result_col)
Zhrnutie
Vyhľadávania malých a veľkých písmen v programe Excel
Štandardne sa pri vyhľadávaní v Exceli nerozlišujú veľké a malé písmená. VLOOKUP aj INDEX / MATCH jednoducho vrátia prvý zápas a ignorujú veľké a malé písmená.
Priamy spôsob, ako obísť toto obmedzenie, je použiť maticový vzorec založený na INDEX / MATCH s EXACT. Ak však hľadáte iba číselné hodnoty, program SUMPRODUCT + EXACT poskytuje tiež zaujímavý a flexibilný spôsob, ako vyhľadať veľké a malé písmená.
V príklade používame nasledujúci vzorec
=SUMPRODUCT(--(EXACT(E3,B3:B8)),C3:C8)
Aj keď je tento vzorec formátom poľa, nie je potrebné ho zadávať pomocou kombinácie klávesov Control + Shift + Enter, pretože SUMPRODUCT spracováva polia natívne.
Vysvetlenie
SUMPRODUCT je navrhnutý na prácu s poliami, ktoré znásobuje a potom sčítava.
V tomto prípade sme dve polia s SUMPRODUCT: B3: B8 a C3: C8. Trik spočíva v spustení testu na hodnoty v stĺpci B a následnom prevedení výsledných hodnôt TRUE / FALSE na 1 a 0. Test spustíme s PRESNOU tak, že:
EXACT(E3,B3:B8)
Ktoré vytvára toto pole:
(FALSE; FALSE; TRUE; FALSE; FALSE; FALSE)
Upozorňujeme, že skutočná hodnota na pozícii 3 je naša zhoda. Potom použijeme dvojitý zápor (tj. - čo je technicky „dvojitý unár“) na vynútenie týchto hodnôt TRUE / FALSE do 1 a 0. Výsledkom je toto pole:
(0; 0; 1; 0; 0; 0)
V tomto okamihu výpočtu vyzerá vzorec SUMPRODUCT takto:
=SUMPRODUCT((0;0;1;0;0;0),(875;750;775;675;800;825))
SUMPRODUCT potom jednoducho znásobí položky v každom poli a vytvorí konečné pole:
(0; 0; 775; 0; 0; 0)
Ktorý SUMPRODUCT potom sčíta a vráti 775.
Podstata tohto vzorca teda je, že FALSE hodnoty sa používajú na zrušenie všetkých ostatných hodnôt. Jediné hodnoty, ktoré prežijú, sú tie, ktoré boli PRAVDIVÉ.
Pretože používame SUMPRODUCT, prichádza tento vzorec s jedinečným zvratom: ak existuje viac zhôd, SUMPRODUCT vráti súčet týchto zhôd. To môže alebo nemusí byť to, čo chcete, takže buďte opatrní, ak očakávate viac zápasov!
Pamätajte, že tento vzorec funguje iba pre číselné hodnoty, pretože SUMPRODUCT nespracováva text. Ak chcete načítať text, použite INDEX / MATCH + PRESNÝ.
