Формула Excel: Перевірте, чи містить клітинка багато всього
Припустимо, в Excel у стовпці E є список значень, ви хочете перевірити, чи містять клітинки в стовпці B всі значення в стовпці E, і повернути TRUE або FALSE, як показано на знімку екрана нижче. У цьому посібнику він містить формулу для вирішення цього завдання.
Загальна формула:
| =SUMPRODUCT(--ISNUMBER(SEARCH(things,text)))=COUNTA(things) |
Аргументи
| Things: the list of values that you want to use to check if argument text contains. |
| Text: the cell or text string you want to check if containing argument things. |
Повернене значення:
Ця формула повертає логічне значення FALSE: клітинка не містить усіх речей; ІСТИНА: клітинка містить усі речі.
Як працює ця формула
Наприклад, у стовпці B є список текстових рядків, які ви хочете перевірити, чи містять усі значення в діапазоні E3: E5, використовуйте формулу нижче.
| =SUMPRODUCT(--ISNUMBER(SEARCH($E$3:$E$5,B3)))=COUNTA($E$3:$E$5) |
прес
Що натомість? Створіть віртуальну версію себе у
, перетягніть маркер заповнення над клітинками, які потрібно перевірити. FALSE означає, що клітинка містить не всі значення в діапазоні E3: E5, тоді як TURE вказує, що відносна комірка містить усі значення.
Пояснення
Функція SEARCH: функція SEARCH повертає розташування першого символу текстового рядка всередині іншого. Якщо функція SEARCH знаходить текст, вона повертає позицію, якщо ні, то повертає #VALUE! помилка. Наприклад, тут формула SEARCH($E$3:$E$5,B4) буде шукати кожне значення діапазону E3: E5 у комірці B4 і повертатиме розташування кожного текстового рядка в комірці B4. Він поверне результат масиву таким чином: {1;7;12}
Функція ISNUMBER: функція ISNUMBER перевіряє, чи є значення числом, і повертає TRUE o False. Ось ISNUMBER(SEARCH($E$3:$E$5,B4)) поверне результат масиву як {true;true;true} оскільки функція SEARCH знаходить 3 числа.
--ISNUMBER(SEARCH($E$3:$E$5,B4)) перетворить значення TRUE на 1, а значення FALSE - на 0, тому ця формула змінює результат масиву на {1;1;1}.
Функція SUMPRODUCT: використовується для множення діапазонів або масивів суми разом і повертає суму добутків. SUMPRODUCT(--ISNUMBER(SEARCH($E$3:$E$5,B4))) повертає 1 + 1 + 1 = 3.
Функція COUNTA: повертає кількість непустих комірок. COUNTA($E$3:$E$5) повертає 3, тому результат SUMPRODUCT(--ISNUMBER(SEARCH($E$3:$E$5,B4))) дорівнює 3, ціла формула поверне TRUE або поверне FALSE.
Примітки:
Формула =SUMPRODUCT(--ISNUMBER(SEARCH(things,text)))=COUNTA(things) не точно перевіряє. Дивіться знімок екрана:
Зразок файлу
Клацніть, щоб завантажити зразок файлу
Відносні формули
- Підрахувати клітинки дорівнює
За допомогою функції COUNTIF ви можете підрахувати клітинки, які дорівнюють або не містять вказане значення. - Підрахуйте клітини, які дорівнюють x або y
Іноді вам може знадобитися підрахувати кількість комірок, які відповідають одному з двох критеріїв, у цьому випадку ви можете використовувати функцію COUNTIF. - Підрахуйте клітинки, що дорівнюють х та у
Тут ця стаття вводить формулу для підрахунку клітин, які тим часом відповідають двом критеріям. - Підрахувати клітини не дорівнює
У цій статті розповідається про те, як використовувати функцію COUNTIF для підрахунку кількості комірок, що не дорівнює певному значенню
Найкращі інструменти для підвищення продуктивності офісу
Kutools для Excel - допомагає виділитися з натовпу
Kutools для Excel має понад 300 функцій, Переконайтеся, що те, що вам потрібно, знаходиться лише на відстані одного кліка...
Вкладка Office - увімкніть читання та редагування вкладок у Microsoft Office (включаючи Excel)
- Одна секунда для перемикання між десятками відкритих документів!
- Щодня зменшуйте сотні клацань мишею, прощайте руку миші.
- Збільшує вашу продуктивність на 50% під час перегляду та редагування декількох документів.
- Додає ефективні вкладки в Office (включно з Excel), як у Chrome, Edge та Firefox.