Általános képlet
=SUMPRODUCT(--(A1=things))>0
Összegzés
Ha egy cellát tesztelni akar, hogy megegyezzen-e a több dolog egyikével, akkor megteheti egy olyan képlettel, amely a SUMPRODUCT függvényt használja.
Kontextus
Tegyük fel, hogy van egy szöveges karaktersorozata a B5: B11 tartományban, és minden cellát tesztelni akar az E5: E9 tartományba tartozó dolgok másik listájával. Más szavakkal, a B5: B11 egyes celláiról tudni szeretné: megegyezik-e ez a cella az E5: E9 bármelyikével?
Elkezdhet egy nagy képletet építeni beágyazott IF utasítások alapján, de a SUMPRODUCT alapú tömbképlet egyszerűbb, tisztább megközelítés.
Megoldás
A megoldás egy olyan képlet létrehozása, amely több értéket tesztel, és visszaadja az IGAZ / HAMIS értékek listáját. Ha ez megvan, a SUMPRODUCT segítségével feldolgozhatjuk ezt a listát (egy tömböt).
Az általunk használt képlet így néz ki:
=SUMPRODUCT(--(B5=things))>0
Magyarázat
A kulcs ez a részlet:
--(B5=things)
amely egyszerűen összehasonlítja a B5 értékét a "dolgok" nevű tartomány minden értékével. Mivel a B5-öt egy tömbhöz hasonlítjuk (azaz a "dolgok" nevű tartományt, E5: E11), az eredmény az IGAZ / HAMIS értékek tömbje lesz, mint ez:
(IGAZ; HAMIS; HAMIS; HAMIS; HAMIS)
Ha csak egy IGAZ van a tömbben, akkor tudjuk, hogy a B5 legalább egy dolognak felel meg a listában, így az IGAZ / HAMIS értékek 1-re és 0-ra kényszerítéséhez kettős negatívot használunk (- unáris). E kényszer után ez áll rendelkezésünkre:
(1; 0; 0; 0; 0)
Most feldolgozzuk az eredményt a SUMPRODUCT segítségével, amely összeadja a tömb elemeit. Ha nem nulla eredményt kapunk, akkor legalább egy meccsünk van, ezért a> 1 értékkel kényszerítjük az IGAZ vagy HAMIS végeredményt.
Keményen kódolt listával
Nincs szükség arra, hogy a dolgok listájához tartományt használjon. Ha csak kevés dologra vágyik, használhat egy tömb formátumú listát, amelyet tömb konstansnak nevezünk. Például, ha csak a vörös, a kék és a zöld színeket keresi, használhatja a következőt ("piros", "kék", "zöld"):
--(B5=("red","blue","green"))
Extra helyek kezelése
Ha a tesztelni kívánt cellákban van további hely, akkor nem fognak megfelelően egyezni. Az összes extra hely leválasztásához módosíthatja a képletet a TRIM függvény használatához, így:
=SUMPRODUCT(--(TRIM(A1)=things))>0
