Note: The other languages of the website are Google-translated. Back to English

Лічильники з логікою АБО для кількох критеріїв у Excel

Зазвичай ви можете використовувати функцію COUNTIFS для підрахунку клітин на основі однієї або кількох умов за допомогою логіки AND у Excel. Ви коли -небудь стикалися з ситуацією, коли вам потрібно порахувати більше одного значення з одного стовпця або діапазону клітинок? Це означає рахувати з кількома умовами та логікою АБО. У цьому випадку ви можете застосувати функції SUM та COUNTIFS разом або скористатися функцією SUMPRODUCT.


Підрахувати клітинки з умовами OR в Excel

Наприклад, у мене є цілий ряд даних, як показано на скріншоті нижче, тепер я хочу підрахувати кількість продукту, який є "Олівець" або "Лінійка", тут я обговорю дві формули для вирішення цього завдання в Excel.

Підраховуйте клітинки з умовами OR за допомогою функцій SUM та COUNTIFS

У Excel для підрахунку з кількома умовами АБО можна використовувати функції SUM і COUNTIFS з константою масиву, загальний синтаксис:

=SUM(COUNTIF(range, {criterion1, criterion2, criterion3, …}))
  • range: Діапазон даних містить критерії, з яких проводиться підрахунок клітинок;
  • criterion1, criterion2, criterion3…: Умови, на основі яких потрібно підрахувати клітинки.

Щоб підрахувати кількість продуктів, які мають «Олівець» або «Лінійка», скопіюйте або введіть формулу нижче у порожню клітинку, а потім натисніть вводити ключ для отримання результату:

=SUM(COUNTIFS(B2:B13,{"Pencil","Ruler"}))


Пояснення формули:

= SUM (COUNTIFS (B2: B13, {"Олівець", "Лінійка"}))

  • {"Олівець", "Лінійка"}: По -перше, ви повинні упакувати всі умови в константу масиву, як це: {"Олівець", "Лінійка"}, розділити елементи комами.
  • СЧІТКИ (В2: В13, {"Олівець", "Лінійка"}): Ця функція COUNTIFS отримає індивідуальні підрахунки для "Олівця" та "Лінійки", і ви отримаєте результат таким чином: {2,3}.
  • SUM (COUNTIFS (B2: B13, {"Олівець", "Лінійка"})) = SUM ({2,3}): Нарешті, ця функція SUM підсумовує всі елементи в масиві та повертає результат: 5.

чайові: Ви також можете використовувати посилання на клітинки для критеріїв, застосуйте формулу масиву нижче, а потім натисніть Ctrl + Shift + Enter клавіші разом, щоб отримати правильний результат:

=SUM(COUNTIF(B2:B13,D2:D3))


Підраховуйте клітини з умовами АБО за допомогою функції SUMPRODUCT

Ось ще одна формула, створена функцією SUMPRODUCT, яка також може допомогти підрахувати клітинки з логікою АБО. Загальний синтаксис такий:

=SUMPRODUCT(1*(range ={criterion1, criterion2, criterion3, …}))
  • range: Діапазон даних містить критерії, з яких проводиться підрахунок клітинок;
  • criterion1, criterion2, criterion3…: Умови, на основі яких потрібно підрахувати клітинки.

Скопіюйте або введіть таку формулу у порожню клітинку та натисніть вводити ключ для повернення результату:

=SUMPRODUCT(1*(B2:B13={"Pencil","Ruler"}))


Пояснення формули:

= 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.

Будь ласка, введіть або скопіюйте таку формулу в клітинку, а потім натисніть вводити ключ, щоб отримати результат, див. знімок екрана:

=SUM(COUNTIFS(B2:B13,{"Pencil","Ruler"},C2:C13,{"<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)))


Пояснення формули:

=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.
  • Порахуйте кількість рядків з кількома критеріями АБО
  • Щоб підрахувати кількість рядків з кількома критеріями в різних стовпцях з логікою АБО, вам може допомогти функція SUMPRODUCT. Наприклад, у мене є звіт про продукт, як показано на скріншоті нижче, тепер я хочу порахувати рядки, де товар-"Футболка" або колір-"Чорний". Як впоратися з цим завданням в Excel?

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

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

Бажаєте виконувати щоденну роботу швидко та якісно? Kutools для Excel надає 300 потужних розширених функцій (об’єднання робочих книг, підсумовування за кольором, розділення вмісту клітинок, перетворення дати тощо) і заощаджує 80% вашого часу.

  • Розроблений для 1500 сценаріїв роботи, допомагає вирішити 80% проблем Excel.
  • Щодня зменшуйте тисячі клацань клавіатури та миші, знімайте втомлені очі та руки.
  • Станьте експертом Excel за 3 хвилини. Більше не потрібно згадувати будь-які болісні формули та коди VBA.
  • Безкоштовна 30-денна безкоштовна пробна версія. 60-денна гарантія повернення грошей. Безкоштовне оновлення та підтримка протягом 2 років.
Стрічка Excel (із встановленим Kutools для Excel)

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

  • Одна секунда для перемикання між десятками відкритих документів!
  • Щодня зменшуйте сотні клацань мишею, прощайте руку миші.
  • Збільшує вашу продуктивність на 50% під час перегляду та редагування декількох документів.
  • Приносить ефективні вкладки в Office (включаючи Excel), як і Chrome, Firefox та New Internet Explorer.
Знімок екрана Excel (із встановленою вкладкою Office)
Коментарі (0)
Поки немає оцінок. Оцініть першим!
There are no comments posted here yet
Залишайте свої коментарі
Публікація як гість
×
Оцініть цю публікацію:
0   Персонажі
Рекомендовані місця

Слідуй за нами

Copyright © 2009 - WWW.extendoffice.com. | Всі права захищені. На основі ExtendOffice. | Карта сайту
Microsoft та логотип Office є товарними знаками або зареєстрованими товарними знаками Microsoft Corporation у США та / або інших країнах.
Захищений Sectigo SSL