Formuła ogólna
(=MAX(IF(TEXT(dates,"ddd")="Mon",values)))
Podsumowanie
Aby znaleźć maksymalną wartość w danym dniu tygodnia (np. W poniedziałek, wtorek, środę itp.), Można użyć prostej formuły tablicowej opartej na funkcjach MAKS., JEŻELI i TEKST. W przedstawionym przykładzie formuła w komórce F5 to:
=MAX(IF(TEXT(dates,"ddd")=F4,values))
Gdzie daty (B5: B15) i wartości (C5: C15) to nazwane zakresy.
Uwaga: to jest formuła tablicowa i należy ją wprowadzić za pomocą kombinacji Control + Shift + Enter.
Wyjaśnienie
Działając od wewnątrz, funkcja TEKST służy do wyodrębnienia wartości dnia tygodnia dla każdej daty:
=TEXT(dates,"ddd")
Daje to tablicę taką jak ta:
("Mon";"Tue";"Wed";"Thu";"Fri";"Mon";"Tue";"Wed";"Thu";"Fri";"Mon")
który jest następnie porównywany z tekstem w F4, „Mon”. Wynikiem jest kolejna tablica, która zawiera tylko wartości PRAWDA i FAŁSZ:
(TRUE;FALSE;FALSE;FALSE;FALSE;TRUE;FALSE;FALSE;FALSE;FALSE;TRUE)
Zauważ, że każda wartość PRAWDA odpowiada poniedziałkowi. Ta tablica jest zwracana wewnątrz funkcji JEŻELI jako test logiczny. Działa jak filtr, aby odfiltrować wartości z innych dni tygodnia. Końcowy wynik funkcji JEŻELI, który jest zwracany bezpośrednio do funkcji MAX, wygląda następująco:
=MAX((85;FALSE;FALSE;FALSE;FALSE;94;FALSE;FALSE;FALSE;FALSE;52))
MAX automatycznie ignoruje wartości FALSE i zwraca najwyższą pozostałą wartość, 94.
Z AGREGATEM
Aby uzyskać nieco bardziej geekową formułę, która nie wymaga Control + Shift + Enter, możesz użyć funkcji AGREGUJ w następujący sposób:
=AGGREGATE(14,6,values/(TEXT(dates,"ddd")=F4),1)
To jest formuła użyta w komórce F6 w pokazanym przykładzie. Tutaj podajemy AGREGATU 14 dla argumentu funkcji (DUŻY) i 6 dla argumentu opcji (ignoruj błędy). Następnie tworzymy logiczne wyrażenie, używając funkcji TEKST, aby sprawdzić wszystkie daty w poniedziałki. Wynikiem tej operacji jest tablica wartości PRAWDA / FAŁSZ, które stają się mianownikiem pierwotnych wartości. W przypadku użycia w operacji matematycznej FALSE przyjmuje wartość zero i generuje # DIV / 0! błąd. PRAWDA ma wartość 1 i zwraca oryginalną wartość. Ostateczna tablica wartości i błędów działa jak filtr. AGREGUJ ignoruje wszystkie błędy i zwraca największą (maksymalną) z pozostałych wartości.
MAXIFS
Funkcja MAXIFS, dostępna w programie Excel Office 365, może zwracać maksymalną wartość przy użyciu jednego lub większej liczby kryteriów bez potrzeby stosowania formuły tablicowej. Jednak MAXIFS jest funkcją opartą na zakresie i nie zezwala innym funkcjom, takim jak TEKST, na przetwarzanie wartości w zakresach kryteriów. Możesz jednak dodać kolumnę pomocniczą do danych, wygenerować wartości dni tygodnia za pomocą TEKST, a następnie użyć wartości MAXIFS z kolumną pomocniczą jako zakresem kryteriów.
