Generisk formel
(=MAX(FREQUENCY(IF(rng>0,COLUMN(rng)),IF(rng=0,COLUMN(rng)))))
Sammanfattning
För att räkna månatliga beställningar i rad kan du använda en matrisformel baserad på FREKVENS-funktionen med hjälp av COLUMN och MAX.
I exemplet som visas är formeln i I5:
(=MAX(FREQUENCY(IF(C5:H5>0,COLUMN(C5:H5)),IF(C5:H5=0,COLUMN(C5:H5)))))
Obs: detta är en matrisformel och måste anges med Control + Shift + Enter.
Förklaring
Det här är en knepig formel att förstå, så spänn upp!
De är viktiga för formeln är att veta att FREKVENS samlar siffror i "soptunnor" på ett specifikt sätt. Varje fack representerar en övre gräns och genererar ett antal av alla siffror i datamängden som är mindre än eller lika med den övre gränsen och större än föregående facknummer. Tricket är då att skapa data_array med att använda det villkor du vill testa för (orderantal större än noll i det här fallet), och bins_array med motsatt villkor.
För att skapa data_array bin använder vi följande:
IF(C5:H5>0,COLUMN(C5:H5))
Vi testar orderantal i varje månad och, om positivt, returnerar kolumnnumret där orderantal> 0. Den resulterande matrisen ser ut så här:
(3, FALSE, FALSE, 6,7,8)
Observera att endast kolumner där orderantal> 0 gör det till denna matris.
Fackuppsättningen genereras med detta:
IF(C5:H5=0,COLUMN(C5:H5))
Detta sätter kolumnnummer för orderräkningar = 0 i en matris som slutar så här:
(FALSE, 4,5, FALSE, FALSE, FALSE)
Endast kolumner där orderantal = 0 gör det till denna array där de per standard FREQUENCY-beteende blir de funktionella lagerplatserna som stämmer överens med beställningar som inte är noll. Vinster översätts till FALSE och samlar inte in några siffror från dataarray, eftersom FALSE-värden ignoreras.
Med data array och bin arrays ovan returnerar frekvensen en array med räkningar per bin i en array så här:
(1; 0; 3)
Nu slår vi helt enkelt MAX-funktionen runt arrayen som returneras av FREQUENCY. MAX returnerar sedan det högsta numret i matrisen som slutresultat.
Andra värden i följd
För att räkna efterföljande förekomster av andra värden, justerar du bara logiken efter behov enligt samma mönster: det första villkorstestet för det du vill räkna, det andra villkoret testar det motsatta.
