Formuła ogólna
(=MAX(FREQUENCY(IF(rng>0,COLUMN(rng)),IF(rng=0,COLUMN(rng)))))
Podsumowanie
Aby policzyć kolejne miesięczne zamówienia, możesz użyć formuły tablicowej opartej na funkcji CZĘSTOŚĆ, z pomocą KOLUMNA i MAKS.
W pokazanym przykładzie formuła w I5 to:
(=MAX(FREQUENCY(IF(C5:H5>0,COLUMN(C5:H5)),IF(C5:H5=0,COLUMN(C5:H5)))))
Uwaga: to jest formuła tablicowa i należy ją wprowadzić za pomocą kombinacji Control + Shift + Enter.
Wyjaśnienie
To trudna do zrozumienia formuła, więc zapnij pasy!
Kluczem do wzoru jest wiedza, że FREQUENCY gromadzi liczby w „pojemnikach” w określony sposób. Każdy przedział reprezentuje górną granicę i generuje liczbę wszystkich liczb w zestawie danych, które są mniejsze lub równe górnej granicy i większe niż poprzednia liczba przedziałów. Sztuczka polega więc na utworzeniu tablicy data_array przy użyciu warunku, dla którego chcesz przetestować (w tym przypadku liczba zamówień jest większa niż zero), a bins_array przy użyciu przeciwnego warunku.
Aby utworzyć bin data_array, używamy:
IF(C5:H5>0,COLUMN(C5:H5))
Testujemy liczbę zamówień w każdym miesiącu i, jeśli jest dodatnia, zwracamy numer kolumny, w której liczba zamówień> 0. Otrzymana tablica wygląda następująco:
(3, FAŁSZ, FAŁSZ, 6,7,8)
Zauważ, że tylko kolumny, w których liczba zamówień> 0, trafiają do tej tablicy.
Tablica bins jest generowana za pomocą tego:
IF(C5:H5=0,COLUMN(C5:H5))
Spowoduje to umieszczenie numerów kolumn dla liczby zamówień = 0 w tablicy, która kończy się następująco:
(FALSE, 4,5, FALSE, FALSE, FALSE)
Tylko kolumny, w których liczba zamówień = 0, trafiają do tej tablicy, gdzie zgodnie ze standardowym zachowaniem FREQUENCY stają się funkcjonalnymi pojemnikami, które zliczają niezerowe zamówienia. Wygrane są tłumaczone na FALSE i nie zbierają żadnych liczb z tablicy danych, ponieważ wartości FALSE są ignorowane.
W przypadku tablic danych i tablic bin powyżej, frequency zwraca tablicę zliczeń na bin w tablicy takiej jak ta:
(1; 0; 3)
Teraz po prostu zawijamy funkcję MAX wokół tablicy zwróconej przez FREQUENCY. MAX następnie zwraca najwyższą liczbę w tablicy jako wynik końcowy.
Inne kolejne wartości
Aby policzyć kolejne wystąpienia innych wartości, po prostu dostosuj logikę zgodnie z tym samym wzorcem: pierwszy warunek sprawdza to, co chcesz policzyć, drugi warunek sprawdza odwrotność.
