Generička formula
(=MAX(FREQUENCY(IF(rng>0,COLUMN(rng)),IF(rng=0,COLUMN(rng)))))
Sažetak
Da biste brojali uzastopne mjesečne narudžbe, možete koristiti formulu niza koja se temelji na funkciji FREQUENCY, uz pomoć COLUMN i MAX.
U prikazanom primjeru formula u I5 je:
(=MAX(FREQUENCY(IF(C5:H5>0,COLUMN(C5:H5)),IF(C5:H5=0,COLUMN(C5:H5)))))
Napomena: ovo je formula niza i mora se unijeti Control + Shift + Enter.
Obrazloženje
Ovo je lukava formula za razumijevanje, zato zakopčajte se!
Ključ formule je znanje da FREQUENCY na specifičan način okuplja brojeve u "kante". Svaka kanta predstavlja gornju granicu i generira broj svih brojeva u skupu podataka koji su manji ili jednaki gornjoj granici i veći od prethodnog broja kante. Trik je tada stvoriti data_array koristeći uvjet za koji želite testirati (u ovom slučaju broj narudžbi veći je od nule), a bins_array koristeći suprotan uvjet.
Da bismo kreirali koš za podatke_raza, koristimo sljedeće:
IF(C5:H5>0,COLUMN(C5:H5))
Testiramo broj narudžbi svakog mjeseca i, ako je pozitivan, vraćamo broj stupca gdje je broj narudžbi> 0. Rezultirajući niz izgleda ovako:
(3, NETOČNO, NETOČNO, 6,7,8)
Primijetite da u ovaj niz ulaze samo stupci s brojem narudžbe> 0.
Niz bins generira se s ovim:
IF(C5:H5=0,COLUMN(C5:H5))
To stavlja brojeve stupaca za brojanje naloga = 0 u niz koji završava ovako:
(NETOČNO, 4,5, NETOČNO, NETOČNO, NETOČNO)
U ovaj niz ulaze samo stupci s brojem narudžbe = 0, koji prema standardnom ponašanju FREQUENCY postaju funkcionalne kante koje broje ne-nulte narudžbe. Pobjede se prevode u FALSE i ne prikupljaju brojeve iz polja podataka, jer se FALSE vrijednosti zanemaruju.
S gornjim nizom podataka i nizom bina, frekvencija vraća niz brojanja po binu u polju poput ovog:
(1; 0; 3)
Sada jednostavno omotamo funkciju MAX oko niza koji je vratila FREQUENCY. MAX tada vraća najveći broj u polju kao konačni rezultat.
Ostale uzastopne vrijednosti
Da biste brojali uzastopna pojavljivanja drugih vrijednosti, samo prilagodite logiku prema potrebi slijedeći isti obrazac: prvi uvjeti testiraju stvar koju želite brojati, drugi uvjet testira suprotnu.
