Formula generica
(=MAX(FREQUENCY(IF(rng>0,COLUMN(rng)),IF(rng=0,COLUMN(rng)))))
Sommario
Per contare gli ordini mensili consecutivi, puoi utilizzare una formula di matrice basata sulla funzione FREQUENZA, con l'aiuto di COLONNA e MAX.
Nell'esempio mostrato, la formula in I5 è:
(=MAX(FREQUENCY(IF(C5:H5>0,COLUMN(C5:H5)),IF(C5:H5=0,COLUMN(C5:H5)))))
Nota: questa è una formula di matrice e deve essere inserita con Control + Maiusc + Invio.
Spiegazione
Questa è una formula difficile da capire, quindi allaccia le cinture!
La chiave della formula è sapere che FREQUENZA raccoglie i numeri in "contenitori" in un modo specifico. Ogni bin rappresenta un limite superiore e genera un conteggio di tutti i numeri nel set di dati che sono inferiori o uguali al limite superiore e maggiori del numero di bin precedente. Il trucco quindi è creare il data_array utilizzando la condizione per cui si desidera eseguire il test (conteggio degli ordini maggiore di zero in questo caso) e bins_array utilizzando la condizione opposta.
Per creare il contenitore data_array utilizziamo quanto segue:
IF(C5:H5>0,COLUMN(C5:H5))
Testiamo il conteggio degli ordini in ogni mese e, se positivo, restituiamo il numero della colonna in cui il conteggio degli ordini è> 0. L'array risultante ha questo aspetto:
(3, FALSO, FALSO, 6,7,8)
Si noti che solo le colonne in cui il conteggio degli ordini è> 0 vengono inserite in questo array.
L'array bins viene generato con questo:
IF(C5:H5=0,COLUMN(C5:H5))
Questo inserisce i numeri di colonna per il conteggio degli ordini = 0 in un array che finisce in questo modo:
(FALSE, 4,5, FALSE, FALSE, FALSE)
Solo le colonne in cui order count = 0 entrano in questo array dove, in base al comportamento FREQUENCY standard, diventano i contenitori funzionali che totalizzano ordini diversi da zero. Le vittorie vengono tradotte in FALSE e non raccolgono alcun numero dall'array di dati, poiché i valori FALSE vengono ignorati.
Con array di dati e array bin sopra, frequency restituisce un array di conteggi per bin in un array come questo:
(1; 0; 3)
Ora avvolgiamo semplicemente la funzione MAX attorno all'array restituito da FREQUENCY. MAX restituisce quindi il numero più alto nella matrice come risultato finale.
Altri valori consecutivi
Per contare le occorrenze consecutive di altri valori, è sufficiente regolare la logica secondo necessità seguendo lo stesso schema: la prima condizione verifica la cosa che si desidera contare, la seconda condizione verifica il contrario.
