Obecný vzorec
(=MAX(FREQUENCY(IF(rng>0,COLUMN(rng)),IF(rng=0,COLUMN(rng)))))
souhrn
Chcete-li počítat po sobě jdoucí měsíční objednávky, můžete použít maticový vzorec založený na funkci FREQUENCY s pomocí COLUMN a MAX.
V zobrazeném příkladu je vzorec v I5:
(=MAX(FREQUENCY(IF(C5:H5>0,COLUMN(C5:H5)),IF(C5:H5=0,COLUMN(C5:H5)))))
Poznámka: toto je maticový vzorec a musí být zadán pomocí kláves Control + Shift + Enter.
Vysvětlení
Je to složitý vzorec, kterému musíte porozumět, takže připoutejte!
Klíčem k formuli je vědomí, že FREKVENCE shromažďuje čísla do „košů“ konkrétním způsobem. Každá přihrádka představuje horní limit a generuje počet všech čísel v datové sadě, která jsou menší nebo stejná jako horní mez a větší než předchozí číslo přihrádky. Trik je pak vytvořit data_array pomocí podmínky, kterou chcete otestovat (v tomto případě počet objednávek větší než nula), a bins_array pomocí opačné podmínky.
K vytvoření koše datového pole používáme následující:
IF(C5:H5>0,COLUMN(C5:H5))
Testujeme počet objednávek v každém měsíci a pokud je pozitivní, vracíme číslo sloupce, kde počet objednávek> 0. Výsledné pole vypadá takto:
(3, FALSE, FALSE, 6,7,8)
Všimněte si, že do tohoto pole se dostanou pouze sloupce, kde počet objednávek> 0.
Pole zásobníků je generováno takto:
IF(C5:H5=0,COLUMN(C5:H5))
To dá čísla sloupců pro počty objednávek = 0 do pole, které skončí takto:
(FALSE, 4,5, FALSE, FALSE, FALSE)
Pouze sloupce, u nichž je počet objednávek = 0, se dostanou do tohoto pole, kde se na standardní chování FREQUENCY stanou funkčními zásobníky, které sčítají nenulové objednávky. Výhry jsou přeloženy do FALSE a neshromažďují žádná čísla z datového pole, protože FALSE hodnoty jsou ignorovány.
S výše uvedeným datovým polem a binem frekvence vrací pole počtů na bin v takovém poli:
(1; 0; 3)
Nyní jednoduše zabalíme funkci MAX kolem pole vráceného FREQUENCY. MAX poté vrací nejvyšší číslo v poli jako konečný výsledek.
Další po sobě jdoucí hodnoty
Chcete-li počítat po sobě jdoucí výskyty dalších hodnot, stačí upravit logiku podle potřeby podle stejného vzoru: první podmínka testuje věc, kterou chcete počítat, druhá podmínka testuje opak.
