Цей підручник демонструє, як користуватися Вимel MAXЯFS і MINIFS Функцціонів в Excel для підрахунку даних, що відповідають певним критеріям.
MAXIFS Огляд функцій
Ви можете використовувати функцію MAXIFS в Excel для підрахунку клітинок, які містять певне значення, підрахунку клітинок, які більші або дорівнюють значенню тощо.
(Зверніть увагу, як виглядають вхідні дані формул)
MAXIFS Синтаксис функції та аргументи:
= MAXIFS (максимальний_діапазон, діапазон1, критерії1, [діапазон2], [критерії2],…)
max_range - Діапазон значень, що використовуються для визначення максимуму.
діапазон1 - Перший діапазон для оцінки.
критерії1 - Критерії використання на діапазоні1.
діапазон2 - [необов’язково] Другий діапазон для оцінки.
діапазон2 - [необов’язково] Другий діапазон для оцінки.
Що таке функції MAXIFS і MINIFS?
Хоча Microsoft змогла дати нам на ранній стадії пошук суми чи підрахунку діапазону клітинок на основі критеріїв, MAXIFS та MINIFS з’явилися пізніше в житті електронних таблиць. На щастя, зараз вони у нас є, і вони мають дуже схожу структуру та використання. Простіше кажучи, дві функції можуть сканувати через діапазон клітинок, перевіряючи певний критерій, а потім даючи максимум або значення в діапазоні, що відповідає цим значенням. Оскільки вони були створені після великого оновлення 2007 року, немає старих "MAXIF/MINIF", про які можна турбуватися.
Якщо ви ще цього не зробили, ви можете переглянути більшість подібної структури та приклади у статті COUNTIFS.
Основний приклад
Розглянемо цю таблицю:
Якщо ми хочемо знайти максимальну кількість зібраних бананів, ми можемо скористатися функцією MAXIFS. Ми завжди дамо діапазон чисел, з яких ми хочемо повернути результат, як перший аргумент. Потім ми перерахуємо діапазони критеріїв та відповідні критерії. У нашому прикладі ми можемо розмістити цю формулу в D2:
= MAXIFS (B2: B7, A2: A7, D1)
Подібним чином, якщо ми хотіли знайти найменшу/мінімальну кількість зібраних бананів, ми можемо просто змінити назву функції, залишивши решту незмінною.
= МІНІФИ (B2: B7, A2: A7, D1)
У решті статті ми будемо схильні наводити приклади, використовуючи або MAXIFS, або MINIFS, а не щоразу перераховуючи обидва. Тільки пам’ятайте, що ви можете легко перемикатися між ними в залежності від того, якого результату ви намагаєтесь досягти.
Робота з датами, кілька критеріїв
Під час роботи з датами в електронній таблиці, хоча є можливість ввести дату безпосередньо у формулу, найкраще використовувати цю дату в комірці, щоб ви могли просто посилатися на клітинку у формулі. Наприклад, це допомагає комп’ютеру зрозуміти, що ви хочете використовувати дату 27.05.2020, а не число 5, поділене на 27, поділене на 2022 рік.
Давайте подивимось на нашу наступну таблицю, де реєструється кількість відвідувачів сайту кожні два тижні.
Ми можемо вказати початкову та кінцеву точки діапазону, який ми хочемо подивитися в D2 та E2. Наша формула, щоб знайти найбільшу кількість відвідувачів у цьому діапазоні, може бути такою:
= MAXIFS (B2: B7, A2: A7, "> =" & D2, A2: A7, "<=" & E2)
Зверніть увагу, як нам вдалося об’єднати порівняння “=” із посиланнями на клітинки для створення критеріїв. Крім того, хоча обидва критерії застосовувалися до одного діапазону клітинок (A2: A7), вам потрібно виписати діапазон двічі, один раз для кожного критерію.
Кілька стовпців
При використанні кількох критеріїв ви можете застосувати їх до того самого діапазону, що й у попередньому прикладі, або застосувати до різних діапазонів. Давайте об’єднаємо наші приклади даних у цю таблицю:
Ми налаштували деякі клітинки, щоб користувач міг вводити те, що він хоче шукати, у клітинках від E2 до G2. Таким чином, нам потрібна формула, яка додасть найменшу кількість яблук, зібраних у лютому. Наша формула виглядає так:
= MINIFS (C2: C7, B2: B7, “> =” & F2, B2: B7, “<=” & G2, A2: A7, E2)
MAXIFS/MINIFS з логікою типу OR
До цього моменту всі приклади, які ми використовували, були порівнянням на основі І, де ми шукаємо рядки, які відповідають усім нашим критеріям. Тепер ми розглянемо випадок, коли ви хочете шукати можливість того, щоб рядок відповідав тому чи іншому критерію.
Давайте подивимося на цей список продажів:
Ми хотіли б знайти максимальні продажі як для Адама, так і для Боба. Найпростіше взяти два MAXIFS, а потім взяти MAX обох цих функцій.
= MAX (MAXIFS (B2: B7, A2: A7, "Adam"), MAXIFS (B2: B7, A2: A7, "Bob"))
Тут комп’ютер обчислив наші індивідуальні бали, а потім додав їх разом.
Наш наступний варіант хороший для тих випадків, коли у вас більше діапазонів критеріїв, таких, що вам не потрібно повторно переписувати всю формулу. У попередній формулі ми вручну сказали комп’ютеру обчислити два різних MAXIFS. Однак ви також можете зробити це, написавши свої критерії всередині масиву, наприклад:
= MAX (MAXIFS (B2: B7, A2: A7, {"Адам", "Боб"}))
Подивіться, як масив побудований у фігурних дужках. Коли комп’ютер оцінить цю формулу, він буде знати, що ми хочемо обчислити функцію MAXIFS для кожного елемента нашого масиву, створюючи таким чином масив цифр. Зовнішня функція MAX візьме цей масив чисел і перетворить його на єдине число. Перейшовши до оцінки формули, це виглядатиме так:
= MAX (MAXIFS (B2: B7, A2: A7, {"Adam", "Bob"})) = MAX (14548, 24956) = 24956
Ми отримуємо той самий результат, але нам вдалося записати формулу трохи коротше.
Робота з заготовками
Іноді у наборі даних будуть порожні клітинки, які потрібно або знайти, або уникати. Встановлення критеріїв для них може бути трохи складним, тому давайте розглянемо інший приклад.
Зауважте, що клітинка А3 справді порожня, тоді як комірка А5 має формулу, що повертає рядок нульової довжини “”. Якщо ми хочемо знайти загальне середнє значення справді порожні клітинки, ми б використовували критерій “=”, і наша формула виглядатиме так:
= MAXIFS (B2: B7, A2: A7, "=")
З іншого боку, якщо ми хочемо отримати середнє значення для всіх клітинок, які візуально виглядають порожніми, ми змінимо критерій на "", а формула виглядає так
= MAXIFS (B2: B7, A2: A7, "")
Давайте поговоримо: що, якщо ви хочете знайти середнє значення непустих клітинок? На жаль, поточний дизайн не дозволяє уникнути рядка нульової довжини. Ви можете використовувати критерій "", але, як ви можете бачити у прикладі, він все ще містить значення з рядка 5.
= MAXIFS (B2: B7, A2: A7, "")
Якщо вам не потрібно рахувати клітинки, що містять рядки нульової довжини, вам слід розглянути можливість використання функції LEN всередині SUMPRODUCT
MAXIFS & MINIIFS у Таблицях Google
Функція MAXIFS & MINIIFS працює точно так само в Google Таблицях, як і в Excel:
