Všeobecný vzorec
(=MAX(FREQUENCY(IF(rng>0,COLUMN(rng)),IF(rng=0,COLUMN(rng)))))
Zhrnutie
Na počítanie po sebe idúcich mesačných objednávok môžete použiť maticový vzorec založený na funkcii FREQUENCY s pomocou COLUMN a MAX.
V zobrazenom príklade je vzorec v I5:
(=MAX(FREQUENCY(IF(C5:H5>0,COLUMN(C5:H5)),IF(C5:H5=0,COLUMN(C5:H5)))))
Poznámka: toto je vzorec poľa a musí byť zadaný pomocou klávesov Control + Shift + Enter.
Vysvetlenie
Toto je zložitý vzorec na pochopenie, takže sa pripútajte!
Kľúčom k formuli je vedomie, že FREKVENCIA zhromažďuje čísla konkrétnym spôsobom do „košov“. Každá priehradka predstavuje horný limit a generuje počet všetkých čísel v množine údajov, ktoré sú menšie alebo rovnaké ako horný limit a väčšie ako predchádzajúce číslo priehradky. Trik potom spočíva v vytvorení údajového poľa s použitím podmienky, ktorú chcete testovať (v tomto prípade je počet objednávok väčší ako nula), a bins_array s opačnou podmienkou.
Na vytvorenie koša data_array používame nasledovné:
IF(C5:H5>0,COLUMN(C5:H5))
Testujeme počet objednávok v každom mesiaci a ak je pozitívny, vrátime číslo stĺpca, kde počet objednávok> 0. Výsledné pole vyzerá takto:
(3, FALSE, FALSE, 6,7,8)
Všimnite si, že do tohto poľa sa dostanú iba stĺpce, ktorých počet objednávok> 0.
Pole zásobníkov sa generuje takto:
IF(C5:H5=0,COLUMN(C5:H5))
Toto vloží čísla stĺpcov pre počet objednávok = 0 do poľa, ktoré končí takto:
(FALSE, 4,5, FALSE, FALSE, FALSE)
Iba stĺpce, kde počet objednávok = 0, sa dostanú do tohto poľa, kde sa za štandardné FREKVENČNÉ chovanie stanú funkčnými košmi, ktoré porovnávajú nenulové objednávky. Výhry sú preložené do FALSE a nezhromažďujú žiadne čísla z dátového poľa, pretože FALSE hodnoty sú ignorované.
S vyššie uvedeným dátovým poľom a poľami bin vráti frekvencia pole počtov na kôš v takomto poli:
(1; 0; 3)
Teraz jednoducho zabalíme funkciu MAX okolo poľa vráteného pomocou FREQUENCY. MAX potom vráti najvyššie číslo v poli ako konečný výsledok.
Ostatné po sebe nasledujúce hodnoty
Ak chcete počítať po sebe nasledujúce výskyty ďalších hodnôt, stačí podľa potreby upraviť logiku podľa rovnakého vzoru: prvá podmienka testuje vec, ktorú chcete počítať, druhá podmienka testuje opak.
