Generieke formule
(=MAX(FREQUENCY(IF(rng>0,COLUMN(rng)),IF(rng=0,COLUMN(rng)))))
Samenvatting
Om opeenvolgende maandelijkse bestellingen te tellen, kunt u een matrixformule gebruiken op basis van de functie FREQUENCY, met hulp van COLUMN en MAX.
In het getoonde voorbeeld is de formule in I5:
(=MAX(FREQUENCY(IF(C5:H5>0,COLUMN(C5:H5)),IF(C5:H5=0,COLUMN(C5:H5)))))
Opmerking: dit is een matrixformule en moet worden ingevoerd met Control + Shift + Enter.
Uitleg
Dit is een lastige formule om te begrijpen, dus houd je vast!
De sleutel tot de formule is te weten dat FREQUENCY getallen op een specifieke manier in "bakken" verzamelt. Elke bak vertegenwoordigt een bovengrens en genereert een telling van alle getallen in de gegevensset die kleiner zijn dan of gelijk zijn aan de bovengrens en groter zijn dan het vorige baknummer. De truc is dan om de data_array te maken met de conditie die je wilt testen (ordertelling groter dan nul in dit geval), en de bins_array met de tegenovergestelde conditie.
Om de data_array-bak te maken, gebruiken we het volgende:
IF(C5:H5>0,COLUMN(C5:H5))
We testen het aantal bestellingen in elke maand en, indien positief, retourneren we het kolomnummer waar het aantal bestellingen> 0 is. De resulterende array ziet er als volgt uit:
(3, FALSE, FALSE, 6,7,8)
Merk op dat alleen kolommen met een ordertelling> 0 in deze array terechtkomen.
De bins-array wordt hiermee gegenereerd:
IF(C5:H5=0,COLUMN(C5:H5))
Dit plaatst kolomnummers voor orderaantallen = 0 in een array die als volgt eindigt:
(FALSE, 4,5, FALSE, FALSE, FALSE)
Alleen kolommen waar order count = 0 komen in deze array terecht waar ze, per standaard FREQUENCY-gedrag, de functionele bakken worden die niet-nul orders tellen. Winsten worden vertaald naar FALSE en verzamelen geen getallen uit de gegevensmatrix, aangezien FALSE-waarden worden genegeerd.
Met bovenstaande gegevensarray en bin-arrays retourneert frequentie een reeks tellingen per bin in een array als deze:
(1; 0; 3)
Nu wikkelen we de MAX-functie gewoon rond de array die wordt geretourneerd door FREQUENCY. MAX retourneert vervolgens het hoogste getal in de array als het eindresultaat.
Andere opeenvolgende waarden
Om opeenvolgende keren van andere waarden te tellen, past u gewoon de logica aan volgens hetzelfde patroon: de eerste voorwaarde test voor het ding dat u wilt tellen, de tweede voorwaarde test het tegenovergestelde.
