Generisk formel
(=MAX(FREQUENCY(IF(rng>0,COLUMN(rng)),IF(rng=0,COLUMN(rng)))))
Resumé
For at tælle på hinanden følgende månedlige ordrer kan du bruge en matrixformel baseret på FREKVENS-funktionen med hjælp fra COLUMN og MAX.
I det viste eksempel er formlen i I5:
(=MAX(FREQUENCY(IF(C5:H5>0,COLUMN(C5:H5)),IF(C5:H5=0,COLUMN(C5:H5)))))
Bemærk: dette er en matrixformel og skal indtastes med Control + Shift + Enter.
Forklaring
Dette er en vanskelig formel at forstå, så spænd op!
De er nøglen til formlen er at vide, at FREKVENS samler tal i "skraldespande" på en bestemt måde. Hver kasse repræsenterer en øvre grænse og genererer et antal af alle tal i datasættet, der er mindre end eller lig med den øvre grænse og større end det forrige kuffertnummer. Tricket er derefter at oprette data_array ved at bruge den betingelse, du vil teste for (ordretælling større end nul i dette tilfælde), og bins_array ved hjælp af den modsatte betingelse.
For at oprette data_array bin bruger vi følgende:
IF(C5:H5>0,COLUMN(C5:H5))
Vi tester ordretælling i hver måned og returnerer kolonnenummeret, hvor ordretællingen er 0, hvis den er positiv. Den resulterende matrix ser sådan ud:
(3, FALSE, FALSE, 6,7,8)
Bemærk, at kun kolonner, hvor rækkefølgen tæller> 0, gør det til dette array.
Bins-arrayet genereres med dette:
IF(C5:H5=0,COLUMN(C5:H5))
Dette placerer kolonnetal for ordretællinger = 0 i en matrix, der ender sådan:
(FALSK, 4,5, FALSK, FALSK, FALSK)
Kun kolonner, hvor rækkefølgen tæller = 0, gør det til denne matrix, hvor de pr. Standard FREKVENS opførsel bliver de funktionelle skraldespande, der stemmer overens med ikke-nul ordrer. Gevinster oversættes til FALSE og indsamler ikke nogen numre fra dataarrayet, da FALSE-værdier ignoreres.
Med data array og bin arrays ovenfor returnerer frekvens et array af tællinger pr. Bin i en array som denne:
(1; 0; 3)
Nu vikler vi blot MAX-funktionen rundt om det array, der returneres af FREQUENCY. MAX returnerer derefter det højeste tal i arrayet som det endelige resultat.
Andre på hinanden følgende værdier
For at tælle sammenhængende forekomster af andre værdier skal du bare justere logikken efter behov efter det samme mønster: den første betingelsestest for den ting, du vil tælle, den anden betingelse tester det modsatte.
