Generisk formel
(=MAX(FREQUENCY(IF(rng>0,COLUMN(rng)),IF(rng=0,COLUMN(rng)))))
Sammendrag
For å telle månedlige påfølgende ordrer, kan du bruke en matriseformel basert på FREKVENS-funksjonen, med hjelp fra KOLONN og MAKS.
I eksemplet vist er formelen i I5:
(=MAX(FREQUENCY(IF(C5:H5>0,COLUMN(C5:H5)),IF(C5:H5=0,COLUMN(C5:H5)))))
Merk: dette er en matriseformel og må angis med Control + Shift + Enter.
Forklaring
Dette er en vanskelig formel å forstå, så spenne opp!
De er nøkkelen til formelen er å vite at FREKVENS samler tall i "søppel" på en bestemt måte. Hver søppel representerer en øvre grense, og genererer et antall antall tall i datasettet som er mindre enn eller lik den øvre grensen, og større enn det forrige søppelnummeret. Trikset er da å lage data_array ved å bruke tilstanden du vil teste for (ordretelling større enn null i dette tilfellet), og bins_array ved å bruke den motsatte tilstanden.
For å lage data_array bin bruker vi følgende:
IF(C5:H5>0,COLUMN(C5:H5))
Vi tester antall ordrer i hver måned, og hvis det er positivt, returnerer vi kolonnetallet der ordretellingen> 0. Den resulterende matrisen ser slik ut:
(3, FALSE, FALSE, 6,7,8)
Legg merke til at bare kolonner der ordretelling> 0 gjør det til denne matrisen.
Søppelbøylene genereres med dette:
IF(C5:H5=0,COLUMN(C5:H5))
Dette setter kolonnetall for ordretellinger = 0 i en matrise som ender slik:
(FALSE, 4,5, FALSE, FALSE, FALSE)
Bare kolonner der rekkefølgen teller = 0 gjør det til denne matrisen der de per standard FREKVENS-oppførsel blir de funksjonelle søppelkassene som stemmer overens med ikke-null ordrer. Gevinster blir oversatt til FALSE og samler ikke inn noen tall fra dataarrayen, siden FALSE-verdier blir ignorert.
Med dataarray og bin arrays ovenfor, returnerer frekvens en array med tellinger per bin i en array som dette:
(1; 0; 3)
Nå vikler vi bare MAX-funksjonen rundt matrisen som returneres av FREQUENCY. MAX returnerer deretter det høyeste tallet i matrisen som det endelige resultatet.
Andre fortløpende verdier
For å telle sammenhengende forekomster av andre verdier, er det bare å justere logikken etter behov etter samme mønster: den første tilstandstesten for det du vil telle, den andre tilstandstesten for det motsatte.
