Общая формула
=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
Когда оба критерия возвращают ИСТИНА, дата находится в заданном месяце, а МАКСЕСЛИМН возвращает максимальное значение дат, соответствующих критериям.
Альтернативные формулы
Если ваша версия Excel не включает функцию MAXIFS, есть другие варианты. Один из вариантов - это простая формула массива, основанная на функциях MAX и IF:
(=MAX(IF(TEXT(dates,"my")=TEXT(G4,"my"),sales)))
Примечание. Это формула массива, которую необходимо вводить с помощью Ctrl + Shift + Enter.
Другой вариант - использовать функцию АГРЕГАТ следующим образом:
=AGGREGATE(14,6,sales/(TEXT(dates,"my")=TEXT(G4,"my")),1)
Оба указанных выше варианта используют более простой метод проверки дат, основанный на функции ТЕКСТ, которая использует числовой формат для извлечения месяца и года из даты. Функцию ТЕКСТ нельзя использовать таким образом внутри функции MAXIFS, потому что для MAXIFS требуется диапазон. Эти альтернативные подходы более подробно описаны здесь.
Максимальный клиент
Чтобы получить клиента, связанного с максимальным значением в данном месяце, формула в G6:
(=INDEX(clients,MATCH(1,(sales=G5)*(TEXT(dates,"my")=TEXT(G4,"my")),0)))
Это формула массива, и ее нужно вводить с помощью Ctrl + Shift + Enter.
Идея здесь в том, что мы уже знаем максимальное значение за месяц (G5) и можем использовать это значение в качестве «ключа» для поиска клиента. Обратите внимание, что мы должны повторить логику, используемую для выделения значений в данном месяце, чтобы избежать ложного совпадения в другом месяце.
Как и упомянутые выше альтернативы, в этой формуле также используется упрощенный тест дат на основе функции ТЕКСТ. Узнайте больше об INDEX и MATCH с несколькими критериями здесь.
Для универсальной формулы замените G5 одним из описанных выше вариантов формулы для определения максимального значения в заданном месяце.
