Загальна формула
=SUMPRODUCT(--ISNUMBER(SEARCH(things,A1)))=COUNTA(things)
Резюме
Якщо ви хочете протестувати комірку, щоб побачити, чи містить вона всі елементи у списку, ви можете зробити це за допомогою формули, яка використовує функцію ПОШУК, за допомогою функцій ISNUMBER, SUMPRODUCT та COUNTA.
Контекст
Скажімо, у вас є список текстових рядків у діапазоні B5: B8, і ви хочете з’ясувати, чи містять ці клітинки всі слова в іншому діапазоні, E5: E7.
Ви можете створити формулу, яка використовує вкладені оператори IF для перевірки кожного елемента, але це не буде добре масштабуватися, якщо у вас є багато речей, які потрібно шукати. Кожного разу, коли ви додаєте слово для пошуку, вам потрібно буде додати ще один вкладений IF і налаштувати дужки.
Рішення
Рішення полягає у створенні формули, що враховує всі збіги одночасно. Отримавши це, ми просто порівнюємо цей рахунок з кількістю предметів, які ми шукаємо. Якщо вони збігаються, ми знаємо, що комірка містить усі елементи.
У наведеному прикладі формула, яку ми використовуємо:
=SUMPRODUCT(--ISNUMBER(SEARCH(things,B5)))=COUNTA(things)
Пояснення
Ключ у цьому фрагменті:
ISNUMBER(SEARCH(things,B5)
Це базується на іншій формулі (докладно поясненій тут), яка просто перевіряє клітинку на наявність одного підрядка. Якщо комірка містить підрядок, формула повертає TRUE. Якщо ні, формула повертає FALSE.
Однак якщо ми дамо одній і тій же формулі список речей (у цьому випадку ми використовуємо іменований діапазон, який називається "речі", E5: E7), це поверне нам список значень TRUE / FALSE, по одному для кожного елемента в . Результат - масив, який виглядає так:
(TRUE; TRUE; TRUE)
Де кожна TRUE представляє знайдений предмет, а кожна FALSE - елемент, який не знайдено.
Ми можемо примусити значення TRUE / FALSE до 1s і 0s з подвійним негативом (-, також званий подвійним унарним):
--ISNUMBER(SEARCH(things,B5))
який видає такий масив:
(1; 1; 1)
Далі ми обробляємо цей масив за допомогою SUMPRODUCT, що дасть нам загальну суму. Якщо ця сума дорівнює кількості елементів у названому діапазоні "речі", ми знаємо, що знайшли всі речі і можемо повернути TRUE. Ми робимо це шляхом прямого порівняння двох чисел. Ми отримуємо кількість непустих клітинок у "речах" за допомогою COUNTA:
=COUNTA(things)
З жорстко закодованим списком
Немає вимоги, щоб ви використовували діапазон для свого списку речей. Якщо ви шукаєте лише невелику кількість речей, ви можете використовувати список у форматі масиву, який називається константою масиву. Наприклад, якщо ви просто шукаєте кольори червоний, синій та зелений, ви можете використовувати ("червоний", "синій", "зелений") таким чином:
=SUMPRODUCT(--ISNUMBER(SEARCH(("yellow","green","dog"),B5)))=COUNTA(things)
