Формула Excel: Перевірте, чи містить комірка одне з кількох значень, але виключає інші значення

Припустимо, що є два списки значень, ви хочете перевірити, чи містить комірка B3 одне зі значень у діапазоні E3: E5, але в той же час вона не містить жодних значень у діапазоні F3: F4, як показано на знімку екрана нижче. Цей підручник надасть формулу для швидкого вирішення цього завдання в Excel та пояснить аргументи формули.

Загальна формула:

Аргументи

Повернене значення:

Формула повертає 1 або 0. Коли комірка містить одне зі значень, які потрібно включити, і не містить значень, які потрібно виключити, вона повертає 1 або повертає 0. Ці формули, 1 і 0 обробляються як логічні значення ІСТИННО і НЕВИЩНО.

Як працює ця формула

Припустимо, ви хочете перевірити, чи містить комірка B3 одне зі значень у діапазоні E3: E5, але одночасно виключає значення в діапазоні F3: F4, використовуйте формулу нижче

прес Що натомість? Створіть віртуальну версію себе у ключ, щоб отримати результат перевірки.

Пояснення

Частина 1: (SUMPRODUCT(--ISNUMBER(SEARCH($E$3:$E$5,B3)))>0) перевіряє, чи містить комірка значення в E3: E5

ПОШУК функція: функція SEARCH повертає позицію першого символу текстового рядка всередині іншого, якщо функція SEARCH знаходить відповідний текст, вона повертає відносну позицію, якщо ні, то повертає #VALUE! помилка. Наприклад, тут формула SEARCH($E$3:$E$5,B3) буде шукати кожне значення діапазону E3: E5 у комірці B3 і повертатиме розташування кожного текстового рядка в комірці B3. Він поверне результат масиву таким чином: {1; 7; 12}.

Функція ISNUMBER: функція ISNUMBER повертає TRUE, коли комірка є числом. Так ISNUMBER(SEARCH($E$3:$E$5,B3)) поверне результат масиву як {true, true, true}, оскільки функція SEARCH знаходить 3 числа.

--ISNUMBER(SEARCH($E$3:$E$5,B3)) перетворює значення TRUE в 1 і перетворює значення FALSE в 0, тому ця формула змінює результат масиву на {1; 1; 1}.

SUMPRODUCT Функція: використовується для множення діапазонів або масивів суми разом і повертає суму добутків. SUMPRODUCT(--ISNUMBER(SEARCH($E$3:$E$5,B3))) повертає 1 + 1 + 1 = 3.

Нарешті, порівняйте ліву формулу SUMPRODUCT(--ISNUMBER(SEARCH($E$3:$E$5,B3))) і 0, якщо результат лівої формули перевищує 0, результат буде TRUE, або він поверне FALSE. Тут він повертає TRUE.

Частина 2: (SUMPRODUCT(--ISNUMBER(SEARCH($F$3:$F$4,B3)))=0) перевіряє, чи не містить комірка значення у F3: F4

Формула ПОШУК ($ F $ 3: $ F $ 4, B3) буде шукати кожне значення в діапазоні E3: E5 у комірці B3 і повертатиме розташування кожного текстового рядка в комірці B3. Він поверне результат масиву таким чином: {#VALUE!; # VALUE!}.

ISNUMBER(SEARCH($F$3:$F$4,B3)) поверне результат масиву як {false; false} оскільки функція SEARCH знаходить 0 число.

--ISNUMBER(SEARCH($F$3:$F$4,B3)) перетворює значення TRUE в 1 і перетворює значення FALSE в 0, тому ця формула змінює результат масиву на {0; 0}.

SUMPRODUCT Функція: використовується для множення діапазонів або масивів суми разом і повертає суму добутків. SUMPRODUCT(--ISNUMBER(SEARCH($F$3:$F$4,B3))) повертає 0 + 0 = 0.

Нарешті, порівняйте ліву формулу SUMPRODUCT(--ISNUMBER(SEARCH($F$3:$F$4,B3))) і 0, поки результат лівої формули дорівнює 0, результат поверне TRUE, або поверне FALSE. Тут він повертає TRUE.

Частина 3: Множинні дві формули

=(SUMPRODUCT(--ISNUMBER(SEARCH($E$3:$E$5,B3)))>0)*(SUMPRODUCT(--ISNUMBER(SEARCH($F$3:$F$4,B3)))=0)

=TRUE*TRUE

=1

Ці формули, 1 і 0 обробляються як логічні значення TRUE і FALSE.

Зразок файлу

Клацніть, щоб завантажити зразок файлу

Відносні формули

• Перевірте, чи містить комірка певний текст
  Щоб перевірити, чи містить комірка деякі тексти в діапазоні A, але не містить тексти в діапазоні B, можна скористатися формулою масиву, яка поєднує в програмі Excel функцію COUNT, SEARCH та AND.
• Перевірте, чи містить комірка одну з багатьох речей
  Цей підручник містить формулу для перевірки, чи містить клітинка одне з кількох значень у Excel, а також пояснює аргументи у формулі та принцип роботи формули.
• Перевірте, чи містить комірка одну з речей
  Припустимо, що в Excel у стовпці E є список значень, ви хочете перевірити, чи містять клітинки в стовпці B всі значення в стовпці E, і повернути TRUE або FALSE.
• Перевірте, чи містить комірка номер
  Іноді вам може знадобитися перевірити, чи містить комірка числові символи. Цей підручник містить формулу, яка поверне TRUE, якщо комірка містить число, FALSE, якщо комірка не містить число.