Обща формула
=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, която използва числов формат за извличане на месеца и годината от датата. Функцията TEXT не може да се използва по този начин във функцията MAXIFS, тъй като MAXIFS изисква диапазон. Тези алтернативни подходи са обяснени по-подробно тук.
Максималният клиент
За да извлечете клиента, свързан с максималната стойност за даден месец, формулата в G6 е:
(=INDEX(clients,MATCH(1,(sales=G5)*(TEXT(dates,"my")=TEXT(G4,"my")),0)))
Това е формула на масив и трябва да се въведе с контрола + shift + enter.
Идеята тук е, че вече знаем максималната стойност за един месец (G5) и можем да използваме тази стойност като "ключ" за търсене на клиента. Забележете, че трябва да повторим логиката, използвана за изолиране на стойности през дадения месец, за да избегнем фалшиво съвпадение в различен месец.
Подобно на алтернативите, споменати по-горе, тази формула също използва опростен тест за дати, базиран на функцията TEXT. Прочетете повече за INDEX и MATCH с множество критерии тук.
За формула „всичко в едно“ заменете G5 с една от обяснените по-горе опции за формула за определяне на максималната стойност за даден месец.
