Генеричка формула
=SUMPRODUCT(--ISNUMBER(SEARCH(things,A1)))=COUNTA(things)
Резиме
Ако желите да тестирате ћелију да бисте видели да ли садржи све ставке на листи, то можете да урадите помоћу формуле која користи функцију СЕАРЦХ, уз помоћ функција ИСНУМБЕР, СУМПРОДУЦТ и ЦОУНТА.
Контекст
Рецимо да имате листу текстуалних низова у опсегу Б5: Б8 и желите да сазнате да ли ове ћелије садрже све речи из другог опсега, Е5: Е7.
Могли бисте да направите формулу која користи угнежђене ИФ изразе за проверу сваке ставке, али то се неће добро прилагодити ако имате много ствари које требате потражити. Сваки пут када додате реч коју ћете тражити, мораћете да додате још један угнеждени ИФ и прилагодите заграде.
Решење
Решење је креирање формуле која броји сва поклапања одједном. Једном када то добијемо, једноставно упоређујемо тај број са бројем предмета које тражимо. Ако се подударају, знамо да ћелија садржи све ставке.
У приказаном примеру, формула коју користимо је:
=SUMPRODUCT(--ISNUMBER(SEARCH(things,B5)))=COUNTA(things)
Објашњење
Кључ је овог исечка:
ISNUMBER(SEARCH(things,B5)
Ово се заснива на другој формули (овде је детаљно објашњено) која једноставно проверава да ли ћелија има један подниз. Ако ћелија садржи подниз, формула враћа ТРУЕ. Ако није, формула враћа ФАЛСЕ.
Међутим, ако истој формули дамо списак ствари (у овом случају користимо именовани опсег који се назива „ствари“, Е5: Е7), вратиће нам листу ТРУЕ / ФАЛСЕ вредности, по једну за сваку ставку у . Резултат је низ који изгледа овако:
(ИСТИНИТО; ИСТИНИТО; ИСТИНИТО)
Где свака ИСТИНА представља пронађену ставку, а свака ФАЛСЕ представља ставку која није пронађена.
Вредности ТРУЕ / ФАЛСЕ можемо присилити на 1с и 0с са двоструким негативом (-, такође названим двоструким унарним):
--ISNUMBER(SEARCH(things,B5))
који даје низ попут овог:
(1; 1; 1)
Затим обрађујемо овај низ помоћу СУМПРОДУЦТ, што ће нам дати укупан збир. Ако је овај збир једнак броју ставки у именованом опсегу „ствари“, знамо да смо пронашли све ствари и можемо да вратимо ИСТИНУ. Начин на који ово радимо је да директно упоредимо два броја. Добијамо број непразних ћелија у „стварима“ помоћу ЦОУНТА:
=COUNTA(things)
С тврдим кодом
Не постоји обавеза да користите опсег за листу ствари. Ако тражите само мали број ствари, можете да користите листу у формату низа, која се назива константа низа. На пример, ако тражите само црвену, плаву и зелену боју, можете користити („црвена“, „плава“, „зелена“) овако:
=SUMPRODUCT(--ISNUMBER(SEARCH(("yellow","green","dog"),B5)))=COUNTA(things)
