Завантажте зразок робочої книги
Цей підручник покаже, як знайти найбільше значення клітинки, яке відповідає певним умовам у Excel та Google Таблицях.
Максимальна функція масиву If
Функція MAX визначає найбільше значення в серії чисел.
Ми можемо використовувати функцію MAX у поєднанні з функцією IF, щоб визначити найбільше значення, яке відповідає певній умові.
Користувачам Google Таблиць та Excel 2022 або пізніших версій рекомендується використовувати простішу функцію MAXIFS. Це пояснюється в наступному розділі.
У цьому прикладі будуть використані функції MAX та IF у формулі масиву для визначення найбільшого Розмір замовлення для кожного Назва магазину
| 1 | {= MAX (IF (B3: B8 = "A", D3: D8))} |
В Office 365 та версіях Excel після 2022 року ви можете просто ввести вищезгадану формулу, як зазвичай (натиснувши ENTER).
Однак для Excel 2022 і раніших версій потрібно ввести формулу, натиснувши CTRL + SHIFT + ENTER. Після цього ви помітите, що навколо формули з’являються фігурні дужки масиву.
Щоб показати, як працює ця формула, давайте розберемо її на етапи.
Це наша остаточна формула (показана без автоматично доданих дужок формул масиву):
| 1 | = MAX (IF (B3: B8 = "A", D3: D8)) |
Спочатку значення діапазону комірок додаються до формули у вигляді масивів:
| 1 | = MAX (IF ({"A"; "B"; "A"; "B"; "A"; "B"} = "A", {500; 400; 300; 700; 600; 200})) |
Далі Назва магазину = "Умова" створює масив зі значеннями TRUE/FALSE:
| 1 | = MAX (IF ({TRUE; FALSE; TRUE; FALSE; TRUE; FALSE}, {500; 400; 300; 700; 600; 200})) |
Потім функція IF змінює всі значення TRUE на відповідні Розмір замовлення:
| 1 | = MAX ({500; FALSE; 300; FALSE; 600; FALSE}) |
Функція MAX визначає найбільше число в масиві, ігноруючи будь -які значення FALSE, щоб показати найбільше Розмір замовлення для Назва магазину = "А":
| 1 | =600 |
Макс. Якщо - кілька критеріїв
Ми також можемо визначити найбільше значення на основі кількох критеріїв, використовуючи булеву логіку.
Цей приклад покаже найбільший Розмір замовлення для кожного Назва магазину, Крім Дати замовлення до 30.04.2021 за допомогою функцій MAX, IF та DATE:
| 1 | {= MAX (IF ((B3: B8 = "A")*(C3: C8 |
Зауважте, тут ми множимо два набори критеріїв TRUE/FALSE разом:
| 1 | (B3: B8 = "A") * (C3: C8 |
Якщо обидва критерії істинні, то загальна умова буде обчислюватися як ІСТИНА, але якщо один (або кілька) критеріїв - ЛОЖЬ, вона обчислюватиметься як ЛОЖЬ.
Використовуючи цю методологію, можна додати до цієї формули багато різних критеріїв.
Макс. Якщо - кілька критеріїв із посиланнями на комірки
Як правило, не рекомендується перетворювати значення у формули на жорсткий код. Натомість більш гнучко використовувати окремі клітинки для визначення критеріїв.
Щоб відповідати Назва магазину до значення, наведеного у стовпці F, ми можемо оновити формулу так:
| 1 | {= MAX (IF ((B3: B8 = F3)*(C3: C8 |
Блокування посилань на клітинки
Щоб полегшити читання наших формул, ми показали формули без заблокованих посилань на клітинки:
| 1 | {= MAX (IF ((B3: B8 = F3)*(C3: C8 |
Але ці формули не працюватимуть належним чином при копіюванні та вставці в інше місце у файлі. Замість цього, ви повинні використовувати заблоковані посилання на клітинки, як це:
| 1 | {= MAX (IF (($ B $ 3: $ B $ 8 = F3)*($ C $ 3: $ C $ 8 |
Прочитайте нашу статтю про блокування посилань на клітинки, щоб дізнатися більше.
Функція MAXIFS
Користувачі Google Таблиць та Excel 2022 або пізніших версій можуть використовувати єдину функцію MAXIFS для відтворення поведінки функцій MAX та IF, показаних у попередніх прикладах.
У цьому наступному прикладі використовуються функції MAXIFS та DATE для відображення найбільших Розмір замовлення для кожного Назва магазину за Дати замовлення до 30.04.2021:
| 1 | = MAXIFS (D3: D8, B3: B8, "A", C3: C8, "<" & DATE (2021,4,30)) |
Функція MAXIFS не вимагає від користувача натискання CTRL + SHIFT + ENTER при введенні формули.
Макс. Якщо (максимальне значення з умовою) у Таблицях Google
Наведені вище приклади працюють точно так само в Google Таблицях, як і в Excel, але оскільки функція MAXIFS доступна, рекомендується використовувати цю єдину функцію замість поєднання функцій MAX та IF.
Якщо потрібно використовувати приклади з функціями MAX та IF, то Google Таблиці вимагають, щоб ви ввели їх як формули масиву. Замість того, щоб відображати формулу за допомогою фігурних дужок масиву Excel {}, натискання CTRL + SHIFT + ENTER автоматично додає функцію ARRAYFORMULA навколо формули:
| 1 | = ARRAYFORMULA (МАКС. (IF ((B3: B8 = "A")*(C3: C8 |
