Загальна формула
=SUMPRODUCT(--(A1=things))>0
Резюме
Якщо ви хочете перевірити клітинку, щоб побачити, чи дорівнює вона одній з кількох речей, ви можете зробити це за допомогою формули, яка використовує функцію SUMPRODUCT.
Контекст
Скажімо, у вас є список текстових рядків у діапазоні B5: B11, і ви хочете перевірити кожну клітинку щодо іншого списку речей у діапазоні E5: E9. Іншими словами, для кожної комірки в B5: B11 ви хочете знати: чи дорівнює ця комірка будь-що з речей у E5: E9?
Ви можете почати будувати велику формулу на основі вкладених операторів IF, але формула масиву, заснована на SUMPRODUCT, є більш простим, чистим підходом.
Рішення
Рішення полягає у створенні формули, яка буде перевіряти наявність кількох значень і повертати список значень TRUE / FALSE. Отримавши це, ми можемо обробити цей список (власне масив) за допомогою SUMPRODUCT.
Формула, яку ми використовуємо, виглядає так:
=SUMPRODUCT(--(B5=things))>0
Пояснення
Ключ у цьому фрагменті:
--(B5=things)
який просто порівнює значення в В5 з кожним значенням у названому діапазоні "речі". Оскільки ми порівнюємо B5 з масивом (тобто названий діапазон "речей", E5: E11), результат буде масивом значень TRUE / FALSE, таким як:
(TRUE; FALSE; FALSE; FALSE; FALSE)
Якщо в масиві є хоча б одна ІСТИНА, ми знаємо, що В5 дорівнює принаймні одній справі у списку, тому, щоб примусити значення ІСТИНИ / НЕПРАВДИ до 1s та 0s, ми використовуємо подвійний негатив (-, також званий подвійним одинарний). Після цього примусу ми маємо таке:
(1; 0; 0; 0; 0)
Тепер ми обробляємо результат за допомогою SUMPRODUCT, який складатиме елементи в масиві. Якщо ми отримуємо будь-який ненульовий результат, у нас є принаймні один збіг, тому ми використовуємо> 1, щоб змусити остаточний результат або TRUE, або FALSE.
З жорстко закодованим списком
Немає вимоги, щоб ви використовували діапазон для свого списку речей. Якщо ви шукаєте лише невелику кількість речей, ви можете використовувати список у форматі масиву, який називається константою масиву. Наприклад, якщо ви просто шукаєте кольори червоний, синій та зелений, ви можете використовувати ("червоний", "синій", "зелений") таким чином:
--(B5=("red","blue","green"))
Робота з зайвими пробілами
Якщо комірки, які ви тестуєте, містять зайвий простір, вони не будуть відповідати належним чином. Щоб звільнити весь зайвий простір, ви можете змінити формулу, щоб використовувати функцію TRIM приблизно так:
=SUMPRODUCT(--(TRIM(A1)=things))>0
