Általános képlet
=SUMPRODUCT(--ISNUMBER(SEARCH(things,A1)))=COUNTA(things)
Összegzés
Ha egy cellát tesztelni szeretne, hogy megnézze, tartalmaz-e egy listában minden elemet, akkor ezt megteheti egy olyan képlettel, amely a KERESÉS funkciót használja, az ISNUMBER, SUMPRODUCT és COUNTA függvények segítségével.
Kontextus
Tegyük fel, hogy rendelkezik a B5: B8 tartományban lévő szöveges karakterláncokkal, és meg szeretné tudni, hogy ezek a cellák tartalmazzák-e az összes szót egy másik tartományban, E5: E7.
Készíthet egy képletet, amely beágyazott IF utasításokat használ az egyes elemek ellenőrzéséhez, de ez nem skálázódik jól, ha sok dologra van szüksége. Valahányszor hozzáad egy szót a kereséshez, hozzá kell adnia egy másik beágyazott IF-t és módosítania kell a zárójeleket.
Megoldás
A megoldás az, hogy létrehozunk egy képletet, amely az összes egyezést megszámolja. Ha ez megvan, egyszerűen összehasonlítjuk ezt a számot a keresett elemek számával. Ha egyeznek, akkor tudjuk, hogy egy cella minden elemet tartalmaz.
A bemutatott példában az általunk használt képlet a következő:
=SUMPRODUCT(--ISNUMBER(SEARCH(things,B5)))=COUNTA(things)
Magyarázat
A kulcs ez a részlet:
ISNUMBER(SEARCH(things,B5)
Ez egy másik képleten alapul (itt részletesen elmagyarázzuk), amely egyszerűen ellenőrzi a cellát egyetlen alstring szempontjából. Ha a cella tartalmazza az alszöveget, a képlet IGAZ értéket ad vissza. Ha nem, akkor a képlet HAMIS.
Ha azonban ugyanannak a képletnek megadunk egy listát a dolgokról (ebben az esetben a "dolgok" nevű tartományt használjuk, E5: E7), akkor visszaadjuk az IGAZ / HAMIS értékek listáját, egyet a . Az eredmény egy tömb, amely így néz ki:
(IGAZ; IGAZ; IGAZ)
Ahol minden TRUE egy talált elemet képvisel, és minden FALSE egy olyan elemet jelent, amely nem található.
Kényszeríthetjük az IGAZ / HAMIS értékeket 1-es és 0-ra kettős negatív (-, más néven kettős unárium):
--ISNUMBER(SEARCH(things,B5))
amely egy ilyen tömböt eredményez:
(1; 1; 1)
Ezután ezt a tömböt a SUMPRODUCT segítségével dolgozzuk fel, amely egy teljes összeget fog kapni. Ha ez az összeg megegyezik a "dolgok" megnevezett tartományban lévő elemek számával, akkor tudjuk, hogy mindent megtaláltunk, és visszaadhatunk IGAZ értéket. Ennek módja a két szám közvetlen összehasonlítása. A „dolgokban” nem üres cellák számát kapjuk a COUNTA használatával:
=COUNTA(things)
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"):
=SUMPRODUCT(--ISNUMBER(SEARCH(("yellow","green","dog"),B5)))=COUNTA(things)
