Általános képlet
=SUMPRODUCT(--(EXACT(val,lookup_col)),result_col)
Összegzés
Kis- és nagybetűk közötti keresések az Excelben
Alapértelmezés szerint az Excel standard keresései nem tesznek különbséget a kis- és nagybetűk között. A VLOOKUP és az INDEX / MATCH is egyszerűen visszaadja az első mérkőzést, figyelmen kívül hagyva a kis- és nagybetűket.
Közvetlen módja ennek a korlátozásnak a kiküszöbölésére, ha az INDEX / MATCH és az EXACT alapú tömbképletet használjuk. Ha azonban csak numerikus értékeket keres, a SUMPRODUCT + EXACT szintén érdekes és rugalmas módszert kínál a kis- és nagybetűk közötti keresésre.
A példában a következő képletet használjuk
=SUMPRODUCT(--(EXACT(E3,B3:B8)),C3:C8)
Bár ez a képlet tömbképlet, nem kell megadni a Control + Shift + Enter billentyűkombinációval, mivel a SUMPRODUCT natívan kezeli a tömböket.
Magyarázat
A SUMPRODUCT tömbökkel való használatra készült, amelyeket megsokszoroz, majd összegez.
Ebben az esetben két tömböt alkotunk a SUMPRODUCT-tal: B3: B8 és C3: C8. A trükk az, hogy tesztet kell futtatni a B oszlop értékein, majd a kapott IGAZ / HAMIS értékeket 1-re és 0-ra konvertálni. A tesztet PONTOSAN futtatjuk, így:
EXACT(E3,B3:B8)
Ami előállítja ezt a tömböt:
(HAMIS; HAMIS; IGAZ; HAMIS; HAMIS; HAMIS)
Vegye figyelembe, hogy a 3. pozíció valódi értéke a mi egyezésünk. Ezután a kettős negatívot (azaz -, ami technikailag "kettős unária") használjuk, hogy ezeket az IGAZ / HAMIS értékeket 1 és 0 értékre kényszerítsük.
(0; 0; 1; 0; 0; 0)
A számítás ezen a pontján a SUMPRODUCT képlet a következőképpen néz ki:
=SUMPRODUCT((0;0;1;0;0;0),(875;750;775;675;800;825))
A SUMPRODUCT ezután egyszerűen megszorozza az egyes tömbök elemeit, hogy létrehozzon egy végső tömböt:
(0; 0; 775; 0; 0; 0)
Mely SUMPRODUCT ezután összegez, és 775-öt ad vissza.
Ennek a képletnek az a lényege, hogy a FALSE értékeket használjuk az összes többi érték törléséhez. Csak azok az értékek maradnak fenn, amelyek IGAZAK voltak.
Ne feledje, hogy mivel a SUMPRODUCT-ot használjuk, ennek a képletnek egyedülálló csavarja van: ha több egyezés van, akkor a SUMPRODUCT visszaadja ezen mérkőzések összegét. Lehet, hogy nem ez az, amit szeretnél, ezért vigyázz, ha több mérkőzésre számítasz!
Ne feledje, hogy ez a képlet csak numerikus értékeknél működik, mert a SUMPRODUCT nem kezeli a szöveget. Ha szöveget szeretne letölteni, használja az INDEX / MATCH + EXACT billentyűt.
