Лічильники з логікою АБО для кількох критеріїв у Excel
Зазвичай ви можете використовувати функцію COUNTIFS для підрахунку клітин на основі однієї або кількох умов за допомогою логіки AND у Excel. Ви коли -небудь стикалися з ситуацією, коли вам потрібно порахувати більше одного значення з одного стовпця або діапазону клітинок? Це означає рахувати з кількома умовами та логікою АБО. У цьому випадку ви можете застосувати функції SUM та COUNTIFS разом або скористатися функцією SUMPRODUCT.
- Підрахувати клітинки з умовами OR в Excel
- Підрахувати клітинки з декількома наборами умов АБО в Excel
Підрахувати клітинки з умовами OR в Excel
Наприклад, у мене є цілий ряд даних, як показано на скріншоті нижче, тепер я хочу підрахувати кількість продукту, який є "Олівець" або "Лінійка", тут я обговорю дві формули для вирішення цього завдання в Excel.
Підраховуйте клітинки з умовами OR за допомогою функцій SUM та COUNTIFS
У Excel для підрахунку з кількома умовами АБО можна використовувати функції SUM і COUNTIFS з константою масиву, загальний синтаксис:
- range: Діапазон даних містить критерії, з яких проводиться підрахунок клітинок;
- criterion1, criterion2, criterion3…: Умови, на основі яких потрібно підрахувати клітинки.
Щоб підрахувати кількість продуктів, які мають «Олівець» або «Лінійка», скопіюйте або введіть формулу нижче у порожню клітинку, а потім натисніть Що натомість? Створіть віртуальну версію себе у ключ для отримання результату:
Пояснення формули:
= SUM (COUNTIFS (B2: B13, {"Олівець", "Лінійка"}))
- {"Олівець", "Лінійка"}: По -перше, ви повинні упакувати всі умови в константу масиву, як це: {"Олівець", "Лінійка"}, розділити елементи комами.
- СЧІТКИ (В2: В13, {"Олівець", "Лінійка"}): Ця функція COUNTIFS отримає індивідуальні підрахунки для "Олівця" та "Лінійки", і ви отримаєте результат таким чином: {2,3}.
- SUM (COUNTIFS (B2: B13, {"Олівець", "Лінійка"})) = SUM ({2,3}): Нарешті, ця функція SUM підсумовує всі елементи в масиві та повертає результат: 5.
Tips : Ви також можете використовувати посилання на клітинки для критеріїв, застосуйте формулу масиву нижче, а потім натисніть Ctrl + Shift + Enter клавіші разом, щоб отримати правильний результат:
Підраховуйте клітини з умовами АБО за допомогою функції SUMPRODUCT
Ось ще одна формула, створена функцією SUMPRODUCT, яка також може допомогти підрахувати клітинки з логікою АБО. Загальний синтаксис такий:
- range: Діапазон даних містить критерії, з яких проводиться підрахунок клітинок;
- criterion1, criterion2, criterion3…: Умови, на основі яких потрібно підрахувати клітинки.
Скопіюйте або введіть таку формулу у порожню клітинку та натисніть Що натомість? Створіть віртуальну версію себе у ключ для повернення результату:
Пояснення формули:
= SUMPRODUCT (1*(B2: B13 = {"Олівець", "Лінійка"}))
- B2: B13 = {"Олівець", "Лінійка"}: Цей вираз порівнює кожен критерій "Олівець", "Лінійка" з коміркою діапазону B2: B13. Якщо критерій виконано, він повертає ІСТИНУ, інакше відображатиметься НЕВІРНО, ви отримаєте такий результат: {ІСТИНА, ЛОЖЬ; ФАЛЬШ, ФАЛЬШ; ФАЛЬШ, НЕВІРНО; ЛОЖЬ, ІСТИНА; НЕВІРНО, НЕВІРНО; ІСТИНА, НЕВІРНО ; FALSE, FALSE; FALSE, TRUE; FALSE, FALSE; FALSE, FALSE; FALSE, TRUE; FALSE, FALSE}.
- 1*(B2: B13 = {"Олівець", "Лінійка"}): Множення перетворює логічні значення - TRUE і FALSE на 1 та 0, тому воно отримає такий результат: {1,0; 0,0; 0,0; 0,1; 0,0; 1,0; 0,0 , 0,1; 0,0; 0,0; 0,1; 0,0; XNUMX}.
- SUMPRODUCT(1*(B2:B13={"Pencil","Ruler"}))= SUMPRODUCT({1,0;0,0;0,0;0,1;0,0;1,0;0,0;0,1;0,0;0,0;0,1;0,0}): Нарешті, функція SUMPRODUCT додає всі числа в масиві, щоб отримати результат: 5.
Підрахувати клітинки з декількома наборами умов АБО в Excel
Іноді вам може знадобитися підрахувати клітинки з двома або більше наборами умов АБО. У цьому випадку ви можете використовувати або SUM, і COUNTIFS з константою масиву, або SUMPRODUCT з функціями ISNUMBER MATCH.
Підраховуйте клітинки з двома наборами умов АБО за допомогою функцій SUM і COUNTIFS
Щоб мати справу лише з двома наборами критеріїв АБО, вам просто потрібно додати іншу константу масиву до формули COUNTIFS.
Наприклад, у мене є діапазон даних, як показано на скріншоті нижче, тепер я хочу порахувати людей, які замовили “Олівець” чи “Лінійку”, а сума - <100 або> 200.
Будь ласка, введіть або скопіюйте таку формулу в клітинку, а потім натисніть Що натомість? Створіть віртуальну версію себе у ключ, щоб отримати результат, див. знімок екрана:
примітки: У формулі слід використовувати крапку з комою для другої константи масиву, яка створює вертикальний масив.
Підраховуйте клітинки з декількома наборами умов АБО за допомогою функції SUMPRODUCT
Наведена вище формула працює лише для двох наборів критеріїв АБО. Якщо вам потрібно рахувати з більшою кількістю критеріїв, вам може допомогти складна формула SUMPRODUCT разом з функціями ISNUMBER MATCH.
Візьміть, наприклад, наведені нижче дані, щоб порахувати людей, які замовили "Олівець" або "Лінійка", а статус "Доставлено" або "Транзитно" та підписано "Бобом" або "Еко", вам слід застосувати складну формулу .
Скопіюйте або введіть формулу нижче у порожню клітинку, а потім натисніть Що натомість? Створіть віртуальну версію себе у ключ, щоб отримати розрахунок, див. скріншот:
Пояснення формули:
=SUMPRODUCT(ISNUMBER(MATCH(B2:B13,{"Pencil","Ruler"},0))*ISNUMBER(MATCH(C2:C13,{"Delivered","In transit"},0))*ISNUMBER(MATCH(D2:D13,{"Bob","Eko"},0)))
ISNUMBER (МАТЧ (В2: В13, {"Олівець", "Лінійка"}, 0)):
- МАТЧ (В2: В13, {"Олівець", "Лінійка"}, 0): Ця функція MATCH використовується для порівняння кожної комірки в діапазоні B2: B13 з відповідною константою масиву. Якщо збіг знайдено, він повертає відносне положення значення в масиві, інакше відображається значення помилки. Отже, ви отримаєте список масивів таким чином: {1;#N/A;#N/A; 2;#N/A; 1;#N/A; 2; 1;#N/A; 2;# Н/Д}.
- ISNUMBER(MATCH(B2:B13,{"Pencil","Ruler"},0))= ISNUMBER({1;#N/A;#N/A;2;#N/A;1;#N/A;2;1;#N/A;2;#N/A}): Функція ISNUMBER перетворює числа на TRUE та значення помилок у FALSE так: {TRUE; FALSE; FALSE; TRUE; FALSE; TRUE; FALSE; TRUE; TRUE; FALSE; TRUE; FALSE}.
Наведена вище логіка також може бути застосована до другого та третього виразів ISNUMBER.
SUMPRODUCT(ISNUMBER(MATCH(B2:B13,{"Pencil","Ruler"},0))*ISNUMBER(MATCH(C2:C13,{"Delivered","In transit"},0))*ISNUMBER(MATCH(D2:D13,{"Bob","Eko"},0))):
- Потім ці три масиви множаться разом у SUMPRODUCT, який автоматично перетворює значення TRUE та FALSE на 1s та 0s у рамках математичної операції, подібної до такої: SUMPRODUCT ({1; 0; 0; 1; 0; 1; 0; 1; 1; 0; 1; 0} *{1; 1; 0; 0; 1; 1; 0; 1; 1; 0; 1; 1} *{1; 0; 0; 0; 0; 0; 0; 1; 0; 0; 1}) = ПІДВИСОК ({0; 1; 0; 0; 0; 0; 0; 0; 1; 0; 0; 1}).
- Нарешті, функція SUMPRODUCT підсумовує всі числа в масиві, щоб отримати результат: 3.
Використана відносна функція:
- Сума:
- Функція Excel SUM повертає суму поданих значень.
- COUNTIF:
- Функція COUNTIF - це статистична функція в Excel, яка використовується для підрахунку кількості клітинок, які відповідають критерію.
- ПОДГОТОВКА:
- Функцію SUMPRODUCT можна використовувати для множення двох або більше стовпців або масивів разом, а потім отримати суму добутків.
- ISNUMBER:
- Функція Excel ISNUMBER повертає TRUE, якщо клітинка містить число, і FALSE, якщо ні.
- МАТЧ:
- Функція Microsoft Excel MATCH шукає певне значення в діапазоні комірок і повертає відносне положення цього значення.
Більше статей:
- Підраховуйте унікальні числові значення на основі критеріїв
- У робочому аркуші Excel ви можете страждати від проблеми, яка підраховує кількість унікальних числових значень на основі конкретної умови. Наприклад, як я можу підрахувати унікальні значення кількості продукту "футболка" зі звіту, як показано на скріншоті нижче? У цій статті я покажу деякі формули для досягнення цього завдання в Excel.
- Підрахунок клітин, які відповідають двом чи більше критеріям
- У Excel для підрахунку кількості клітинок на основі двох чи кількох критеріїв функція COUNTIFS може допомогти вам швидко та легко вирішити це завдання.
- Порахуйте кількість рядків з кількома критеріями АБО
- Щоб підрахувати кількість рядків з кількома критеріями в різних стовпцях з логікою АБО, вам може допомогти функція SUMPRODUCT. Наприклад, у мене є звіт про продукт, як показано на скріншоті нижче, тепер я хочу порахувати рядки, де товар-"Футболка" або колір-"Чорний". Як впоратися з цим завданням в Excel?
Найкращі інструменти для підвищення продуктивності офісу
Kutools для Excel - допомагає виділитися з натовпу
Kutools для Excel має понад 300 функцій, Переконайтеся, що те, що вам потрібно, знаходиться лише на відстані одного кліка...
Вкладка Office - увімкніть читання та редагування вкладок у Microsoft Office (включаючи Excel)
- Одна секунда для перемикання між десятками відкритих документів!
- Щодня зменшуйте сотні клацань мишею, прощайте руку миші.
- Збільшує вашу продуктивність на 50% під час перегляду та редагування декількох документів.
- Додає ефективні вкладки в Office (включно з Excel), як у Chrome, Edge та Firefox.
