Підрахувати клітинки, які містять x або y у діапазоні в Excel
Щоб підрахувати клітинки, які містять те чи інше значення, деякі користувачі Excel можуть додати дві функції COUNTIF разом, щоб впоратися з цим, однак цей метод підраховує клітинки з логікою AND (містять як x, так і y) замість логічного OR (містять x або y). Щоб уникнути подвійного підрахунку критеріїв, у цьому посібнику буде показано, як детально застосувати формулу на основі функції SUMPRODUCT для підрахунку кількості клітинок у певному діапазоні, які містять x або y у Excel.
Як підрахувати клітинки, які містять x або y у діапазоні в Excel
Як показано на скріншоті нижче, існує діапазон даних B3: B9, щоб підрахувати кількість клітинок у цьому діапазоні, що містить або "KTE", або "KTO", будь ласка, застосуйте формулу нижче, щоб це зробити.
Загальна формула
=SUMPRODUCT(--((ISNUMBER(FIND("criteria1",rng)) + ISNUMBER(FIND("criteria2",rng)))>0))
Аргументи
кол (обов’язково): Діапазон, з якого потрібно підрахувати клітинки, що містять x або y.
Критерії1 (обов’язково): один рядок або символ, на основі якого потрібно підрахувати клітинки.
Критерії2 (обов’язково): Інший рядок або символ, на основі якого потрібно підрахувати клітинки.
Як користуватися цією формулою?
1. Виберіть порожню комірку для виведення результату.
2. Введіть формулу нижче і натисніть на Що натомість? Створіть віртуальну версію себе у ключ, щоб отримати результат.
=SUMPRODUCT(--((ISNUMBER(FIND(D3,B3:B9)) + ISNUMBER(FIND(D4,B3:B9)))>0))
Як працюють ці формули?
=SUMPRODUCT(--((ISNUMBER(FIND(D3,B3:B9)) + ISNUMBER(FIND(D4,B3:B9)))>0))
- 1. FIND(D3,B3:B9): Функція FIND перевіряє, чи існує значення “KTE” у D3 у зазначеному діапазоні (B3: B9), і повертає масив: {1,#VALUE!,#VALUE!,#VALUE!,#VALUE!, 1 ,#ЦІННІСТЬ!}.
У цьому масиві два одиниці 1 означають, що перша та друга до останньої клітинки у списку B3: B9, що містять значення “KTE”, #VALUE! S представляють значення “KTE”, якого немає в інших клітинках. - 2. ISNUMBER{1,#VALUE!, #VALUE!, #VALUE!, #VALUE!,1, #VALUE!}: Функція ISNUMBER повертає TURE, якщо номер зустрічі в масиві, і повертає FALSE, якщо помилки зустрічі, і, нарешті, повертає новий масив як {TURE; FALSE; FALSE; FALSE; FALSE; TURE; FALSE}.
- 3. FIND(D4,B3:B9): Ця функція FIND також перевіряє, чи існує значення “KTO” у D4 у зазначеному діапазоні (B3: B9), і повертає масив: {#VALUE!, 1,#VALUE!,#VALUE !, #VALUE !, #ЦІННІСТЬ!,#ЦІННІСТЬ!}.
- 4. ISNUMBER{#VALUE!,1,#VALUE!,#VALUE!, #VALUE!,#VALUE!,#VALUE!}: Функція ISNUMBER повертає TURE, якщо номер зустрічі в масиві, і повертає FALSE, якщо помилки зустрічі, і, нарешті, повертає новий масив як {FALSE; TURE; FALSE; FALSE; FALSE; FALSE; FALSE}.
- 5. {TURE;FALSE;FALSE;FALSE;FALSE;TURE;FALSE} + {FALSE;TURE;FALSE;FALSE;FALSE;FALSE;FALSE}: Тут два масиви повертають результат як {1; 1; 0; 0; 0; 1; 0}.
- 6. {1;1;0;0;0;1;0}>0: Тут порівнюється, чи кожне число в масиві більше 0, і повертає результат як {TURE; TRUE; FALSE; FALSE; FALSE; TURE; FALSE}.
- 7. --({TURE;TRUE;FALSE;FALSE;FALSE;TURE;FALSE}): Ці два знаки мінус перетворюють “TRUE” на 1 та “FALSE” на 0. Тут ви отримаєте новий масив як {1; 1; 0; 0; 0; 1; 0}.
- 8. =SUMPRODUCT({1;1;0;0;0;1;0}): Функція SUMPRODUCT підсумовує всі числа в масиві і повертає кінцевий результат як 3 у цьому випадку.
Супутні функції
Функція Excel SUMPRODUCT
Функцію Excel COUNTBLANK можна використовувати для множення двох або більше стовпців або масивів разом, а потім отримати суму продуктів.
Функція Excel ISNUMBER
Функція Excel ISNUMBER повертає TRUE, якщо клітинка містить число, і FALSE, якщо ні.
Функція Excel FIND
Функція Excel FIND використовується для пошуку рядка в іншому рядку та повертає початкове положення рядка всередині іншого.
Пов’язані формули
Підрахувати клітинки, які починаються або закінчуються певним текстом
У цій статті буде показано, як підраховувати клітинки, які починаються або закінчуються певним текстом у діапазоні в Excel за допомогою функції COUNTIF.
Підрахувати пусті/непорожні клітинки
У цій статті пояснюються формули для підрахунку кількості порожніх і непорожніх клітинок у діапазоні в Excel.
Підрахувати клітинки, які містять певний текст, з урахуванням регістру
Цей посібник містить детальну формулу для підрахунку кількості клітинок, що містить певний текст з урахуванням регістру в діапазоні в Excel.
Порахуйте, скільки клітинок містить помилки
У цьому посібнику показано, як підрахувати кількість клітинок, які містять помилки (будь -які помилки, наприклад, помилка #N/A, #VALUE! Помилка або #DIV/0! Помилка) у визначеному діапазоні в Excel.
Найкращі інструменти для підвищення продуктивності офісу
Kutools для Excel - допомагає виділитися з натовпу
Kutools для Excel має понад 300 функцій, Переконайтеся, що те, що вам потрібно, знаходиться лише на відстані одного кліка...
Вкладка Office - увімкніть читання та редагування вкладок у Microsoft Office (включаючи Excel)
- Одна секунда для перемикання між десятками відкритих документів!
- Щодня зменшуйте сотні клацань мишею, прощайте руку миші.
- Збільшує вашу продуктивність на 50% під час перегляду та редагування декількох документів.
- Додає ефективні вкладки в Office (включно з Excel), як у Chrome, Edge та Firefox.
