Formula generică
(=MAX(FREQUENCY(IF(rng>0,COLUMN(rng)),IF(rng=0,COLUMN(rng)))))
rezumat
Pentru a număra comenzile lunare consecutive, puteți utiliza o formulă matrice bazată pe funcția FRECVENȚĂ, cu ajutorul de la COLUMN și MAX.
În exemplul prezentat, formula din I5 este:
(=MAX(FREQUENCY(IF(C5:H5>0,COLUMN(C5:H5)),IF(C5:H5=0,COLUMN(C5:H5)))))
Notă: aceasta este o formulă matrice și trebuie introdusă cu Control + Shift + Enter.
Explicaţie
Aceasta este o formulă dificilă de înțeles, deci închideți-vă!
Cheia formulei este faptul că știu că FREQUENCY adună cifrele în „coșuri” într-un mod specific. Fiecare coș reprezintă o limită superioară și generează un număr al tuturor numerelor din setul de date care sunt mai mici sau egale cu limita superioară și mai mari decât numărul de coș anterior. Trucul este apoi să creați data_array folosind condiția pentru care doriți să testați (numărul de comenzi mai mare de zero în acest caz) și bins_array folosind condiția opusă.
Pentru a crea coșul de date_array folosim următoarele:
IF(C5:H5>0,COLUMN(C5:H5))
Testăm numărul de comenzi în fiecare lună și, dacă este pozitiv, returnăm numărul coloanei unde numărul de comenzi> 0. Matricea rezultată arată astfel:
(3, FALS, FALSE, 6,7,8)
Observați că doar coloanele în care numărul de comenzi> 0 intră în această matrice.
Matricea de coșuri este generată cu aceasta:
IF(C5:H5=0,COLUMN(C5:H5))
Aceasta plasează numerele de coloane pentru numărul de comenzi = 0 într-o matrice care se termină astfel:
(FALS, 4,5, FALSE, FALSE, FALSE)
Numai coloanele în care numărul de comenzi = 0 intră în această matrice în care, conform comportamentului standard de FRECVENȚĂ, devin pubele funcționale care corespund ordinelor diferite de zero. Câștigurile sunt traduse în FALS și nu colectează niciun număr din matricea de date, deoarece valorile FALSE sunt ignorate.
Cu matrice de date și matrici de benzi de mai sus, frecvența returnează o matrice de numărări pe bin într-o matrice ca aceasta:
(1; 0; 3)
Acum pur și simplu înfășurăm funcția MAX în jurul matricei returnate de FREQUENCY. MAX returnează apoi cel mai mare număr din matrice ca rezultat final.
Alte valori consecutive
Pentru a număra aparițiile consecutive ale altor valori, trebuie doar să ajustați logica după cum este necesar urmând același tipar: testele pentru prima condiție pentru lucrul pe care doriți să o numărați, testarea pentru a doua condiție pentru opusul.
