Перейти до основного матеріалу

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

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

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

=(SUMPRODUCT(--ISNUMBER(SEARCH(include,text)))>0) *(SUMPRODUCT(--ISNUMBER(SEARCH(exclude,text)))=0)

Аргументи

Text: the text string you want to check.
Include: the values you want to check if argument text contains.
Exclude: the values you want to check if argument text does not contain.

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

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

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

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

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

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

Пояснення

Частина 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.
doc перевірити, чи містить одна з речей, але виключити 3

Частина 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.
doc перевірити, чи містить одна з речей, але виключити 4

Частина 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.

Зразок файлу

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


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


Найкращі інструменти для підвищення продуктивності офісу

Kutools для Excel - допомагає виділитися з натовпу

Популярні функції: Знайдіть, виділіть або визначте дублікати  |  Видалити порожні рядки  |  Об’єднайте стовпці або клітинки без втрати даних  |  Раунд без Формули ...
Супер VLookup: Кілька критеріїв  |  Множинне значення  |  На кількох аркушах  |  Нечіткий пошук...
Adv. Випадаючий список: Легкий спадний список  |  Залежний спадний список  |  Виберіть розкривний список, що вибирається...
Менеджер колонок: Додайте конкретну кількість стовпців  |  Перемістити стовпці  |  Перемкнути статус видимості прихованих стовпців  Порівняти стовпці з Виберіть однакові та різні клітини ...
Особливості: Фокус сітки  |  Перегляд дизайну  |  Велика панель формул  |  Диспетчер робочих книг і аркушів | Бібліотека ресурсів (автотекст)  |  Вибір дати  |  Об’єднайте робочі аркуші  |  Шифрування/розшифрування клітинок  |  Надсилайте листи за списком  |  Супер фільтр  |  Спеціальний фільтр (фільтр жирний/курсив/закреслений...) ...
Топ-15 наборів інструментів12 текст Tools (додати текст, Видалити символи ...)  |  50 + Графік типи (діаграма Ганта ...)  |  40+ Практичний Формули (Розрахуйте вік на основі дня народження ...)  |  19 вставка Tools (Вставте QR-код, Вставити зображення зі шляху ...)  |  12 Перетворення Tools (Числа до слів, Валютна конверсія ...)  |  7 Злиття та розділення Tools (Розширені комбіновані ряди, Розділити клітинки Excel ...)  |  ... і більше

Kutools для Excel має понад 300 функцій, Переконайтеся, що те, що вам потрібно, знаходиться лише на відстані одного кліка...


Вкладка Office - увімкніть читання та редагування вкладок у Microsoft Office (включаючи Excel)

  • Одна секунда для перемикання між десятками відкритих документів!
  • Щодня зменшуйте сотні клацань мишею, прощайте руку миші.
  • Збільшує вашу продуктивність на 50% під час перегляду та редагування декількох документів.
  • Додає ефективні вкладки в Office (включно з Excel), як у Chrome, Edge та Firefox.
Comments (1)
No ratings yet. Be the first to rate!
This comment was minimized by the moderator on the site
<p>avec les fonctions en français ça donne : SOMMEPROD(--ESTNUM(CHERCHE(Liste;B2)))</p>
There are no comments posted here yet
Please leave your comments in English
Posting as Guest
×
Rate this post:
0   Characters
Suggested Locations