Функція макс і мін по умові

Рейтинг: 5/5

Найчастіше в Excel потрібно провести обчислення по деякому умові (-ям). Так, для підсумовування даних за деяким критерієм передбачена функція СУММЕСЛИ і СУММЕСЛІМН, для підрахунку середньої арифметичної - СРЗНАЧЕСЛІ і СРЗНАЧЕСЛІМН, таким же чином можна підрахувати кількість непустих осередків в діапазоні - СЧЁТЕСЛІ і СЧЁТЕСЛІМН. Всі ці функції відмінно себе зарекомендували.

Але буває, що потрібно розрахувати за умовою максимальне або мінімальне значення. Готової функції в Excel немає. Однак провести такий розрахунок можна, якщо скористатися комбінацією функцій.

Наприклад, нехай є дані

Потрібно підрахувати максимальне значення, відповідне групі А. Або мінімальне значення в групі Б.

Одним з варіантів вирішення цього завдання є використання формули масиву. Критерій, за яким відбиратимуться дані, тобто назва групи, введемо в окрему клітинку (D2). Нехай для початку це буде група Б. Поруч введемо наступну формулу:

Це формула масиву, тому ввести її потрібно комбінацією Ctrl + Shift + Enter.

Тепер, змінюючи назву групи, можна без всяких фільтрів і зведених таблиць бачити максимальне значення всередині цієї групи.

Як це працює? Дуже просто. Насамперед потрібно вказати діапазон, який буде використовуватися в якості аргументу функції МАКС, тобто тільки ті осередки, які відповідають зазначеній групі. Так як ми заздалегідь подбали про зручність використання функції, то назва групи вказали не всередині формули, а в окремій клітинці (набагато легше змінювати групу). Тоді формула для потрібного діапазону виглядає так.

Зазначене вираз відбирає тільки ті значення, для яких назва групи збігається з умовою в комірці D2. Ось, як це бачить Excel

На наступному етапі зазначимо функцію МАКС, аргументом якої виступає отриманий вище масив. Excel сприймає приблизно так.

Видно, що максимальне значення всередині масиву одно 31. Його і ми і побачимо в осередку з формулою. Потрібно тільки не забути підсумкову функцію ввести комбінацією клавіш Ctrl + Shift + Enter. інакше нічого не вийде. У рядку формул формула масиву відображається всередині фігурних дужок. Додаються самі, спеціально домальовувати не потрібно.

Для розуміння ще раз про алгоритм розрахунку. Спочатку за допомогою функції ЯКЩО створюється масив з тих даних, які задовольняють умові. Потім вже в отриманому масиві відбувається пошук максимального значення. Власне, все.

Якщо функцію МАКС замінити на МІН, то за вказаною умові (назвою групи) буде видаватися мінімальне значення. Залишимо цю вправу в якості домашнього завдання.

Нижче ви можете подивитися ролик.