Formuła ogólna
=MAXIFS(sales,dates,">="&G4,dates,"<="&EOMONTH(G4,0))
Podsumowanie
Aby znaleźć maksymalną wartość w danym miesiącu, możesz skorzystać z funkcji MAXIFS lub jednej z poniższych alternatyw. W pokazanym przykładzie formuła w G5 to:
=MAXIFS(sales,dates,">="&G4,dates,"<="&EOMONTH(G4,0))
Gdzie sprzedaż (D5: D15), daty (B5: B15) i wartości (C5: C15) to nazwane zakresy.
Wyjaśnienie
Funkcja MAXIFS może znaleźć maksymalną wartość w zakresie na podstawie co najmniej jednego kryterium. W przedstawionym przykładzie używamy MAXIFS do znalezienia maksymalnej wartości sprzedaży w danym miesiącu na podstawie dat „w nawiasach” między pierwszym a ostatnim dniem miesiąca. Pierwsze kryterium sprawdza, czy daty są większe lub równe pierwszemu dniu miesiąca:
dates,">="&G4,dates // first of month
Uwaga: zakładamy, że data w G4 jest datą „pierwszego dnia miesiąca”.
Drugie kryterium sprawdza, czy daty są mniejsze lub równe ostatniemu dniu miesiąca, obliczone funkcją EOMONTH:
dates,"<="&EOMONTH(G4,0) // last of month
Gdy oba kryteria zwracają wartość PRAWDA, data przypada w danym miesiącu, a funkcja MAXIFS zwraca maksymalną wartość dat spełniających kryteria.
Alternatywne formuły
Jeśli Twoja wersja programu Excel nie zawiera funkcji MAXIFS, istnieją inne opcje. Jedną z opcji jest prosta formuła tablicowa oparta na funkcjach MAX i JEŻELI:
(=MAX(IF(TEXT(dates,"my")=TEXT(G4,"my"),sales)))
Uwaga: to jest formuła tablicowa i należy ją wprowadzić za pomocą kombinacji Ctrl + Shift + Enter.
Inną opcją jest użycie funkcji AGREGACJA w następujący sposób:
=AGGREGATE(14,6,sales/(TEXT(dates,"my")=TEXT(G4,"my")),1)
Obie powyższe opcje wykorzystują prostszą metodę testowania dat w oparciu o funkcję TEKST, która wykorzystuje format liczbowy do wyodrębnienia miesiąca i roku z daty. Funkcja TEKST nie może być używana w ten sposób wewnątrz funkcji MAXIFS, ponieważ MAXIFS wymaga zakresu. Te alternatywne podejścia wyjaśniono bardziej szczegółowo tutaj.
Klient max
Aby pobrać klienta skojarzonego z maksymalną wartością w danym miesiącu, wzór w G6 to:
(=INDEX(clients,MATCH(1,(sales=G5)*(TEXT(dates,"my")=TEXT(G4,"my")),0)))
To jest formuła tablicowa i należy ją wprowadzić za pomocą kombinacji Ctrl + Shift + Enter.
Pomysł jest taki, że znamy już maksymalną wartość w miesiącu (G5) i możemy użyć tej wartości jako „klucza” do wyszukiwania klienta. Zauważ, że musimy powtórzyć logikę użytą do wyodrębnienia wartości w danym miesiącu, aby uniknąć fałszywego dopasowania w innym miesiącu.
Podobnie jak w przypadku alternatyw wspomnianych powyżej, w tych formułach stosowany jest również uproszczony test dat oparty na funkcji TEKST. Przeczytaj więcej o INDEX i MATCH z wieloma kryteriami tutaj.
W przypadku formuły typu „wszystko w jednym” zastąp G5 jedną z opcji formuły wyjaśnionych powyżej w celu określenia maksymalnej wartości w danym miesiącu.
