Загальна формула
=MAXIFS(sales,dates,">="&G4,dates,"<="&EOMONTH(G4,0))
Резюме
Щоб знайти максимальне значення за певний місяць, ви можете скористатися функцією MAXIFS або однією з інших альтернатив, наведених нижче. У наведеному прикладі формула в G5 має вигляд:
=MAXIFS(sales,dates,">="&G4,dates,"<="&EOMONTH(G4,0))
Де продажі (D5: D15), дати (B5: B15) та значення (C5: C15) називаються діапазонами.
Пояснення
Функція MAXIFS може знайти максимальне значення в діапазоні на основі одного або декількох критеріїв. У наведеному прикладі ми використовуємо MAXIFS для знаходження максимальної вартості продажів, яка базується в даному місяці, шляхом "фіксації" дат між першим днем місяця та останнім днем місяця. Перший критерій перевіряє, чи є дати більше або дорівнюють першого числа місяця:
dates,">="&G4,dates // first of month
Примітка: ми припускаємо, що дата в G4 є датою "першого місяця".
Другий критерій перевіряє, чи є дати меншими або рівними останньому місяця, обчисленому за допомогою функції EOMONTH:
dates,"<="&EOMONTH(G4,0) // last of month
Коли обидва критерії повертають значення TRUE, дата вказана в даному місяці, а MAXIFS повертає максимальне значення дат, які відповідають критеріям.
Альтернативні формули
Якщо ваша версія Excel не включає функцію MAXIFS, є інші варіанти. Одним із варіантів є проста формула масиву, заснована на функціях MAX та IF:
(=MAX(IF(TEXT(dates,"my")=TEXT(G4,"my"),sales)))
Примітка: Це формула масиву, і її потрібно вводити за допомогою control + shift + enter.
Інший варіант - використовувати функцію AGGREGATE таким чином:
=AGGREGATE(14,6,sales/(TEXT(dates,"my")=TEXT(G4,"my")),1)
Обидва варіанти, наведені вище, використовують простіший метод тестування дат на основі функції ТЕКСТ, який використовує числовий формат для вилучення місяця та року з дати. Функцію TEXT не можна використовувати таким чином всередині функції MAXIFS, оскільки MAXIFS вимагає діапазону. Ці альтернативні підходи пояснюються тут більш докладно.
Максимальний клієнт
Для отримання клієнта, пов'язаного з максимальним значенням у даному місяці, формула в G6 має такий вигляд:
(=INDEX(clients,MATCH(1,(sales=G5)*(TEXT(dates,"my")=TEXT(G4,"my")),0)))
Це формула масиву, яку потрібно вводити за допомогою control + shift + enter.
Ідея тут полягає в тому, що ми вже знаємо максимальне значення за місяць (G5), і ми можемо використовувати це значення як "ключ" для пошуку клієнта. Зверніть увагу, що ми повинні повторити логіку, яка використовується для ізоляції значень у даному місяці, щоб уникнути помилкового збігу в інший місяць.
Як і згадані вище альтернативи, у цій формулі також використовується спрощений тест для дат на основі функції ТЕКСТ. Детальніше про INDEX та MATCH з кількома критеріями читайте тут.
Для формули «все в одному» замініть G5 одним із описаних вище варіантів формул для визначення максимального значення в даному місяці.
