Ako používať funkciu SCAN v Microsoft Excel na výpočet priebežných súčtov

Po roky som sa spoliehal na bežiace súčty v Microsoft Exceli, ale tradičné metódy sú krehké a nespoľahlivé. Odvtedy, čo Microsoft predstavil funkciu SCAN, som prešiel na úplne nový prístup, ktorý je flexibilný, samoozdravujúci a nepraská, keď moje údaje rastú.

Prečo je stará škola problémová

Predtým, než bola predstavená funkcia SCAN, ak som chcel priebežný súčet, používal som trik s absolútnym a relatívnym odkazom.

V tomto hárku obsahuje stĺpec B mesačné zisky a ja chcem zobraziť súčet priebežného súčtu v stĺpci C.

Do bunky C2 by som napísal:

=SUM($B$2:B2)

Potom by som dvojitým kliknutím na úchyt výplne použil vzorec na zostávajúce riadky.

Na prvý pohľad to vyzerá, že funguje dobre. Avšak predpokladajme, že neskôr zistím, že som vynechal jeden mesiac. Keď vložím nový riadok, Excel nedokáže vyplniť vzorec do tohto nového prázdneho riadku, takže vznikne medzera, ktorú je potrebné riešiť manuálne.

Tiež, ak predčasne nastavím stĺpec A až na koniec roka a potiahnem vzorec v stĺpci C až na dno, pokiaľ výrazne nezmením vzorec a nezahrniem do neho funkciu IF, dostanem neporiadny, mätúci opakujúci sa súčet.

V príkladoch vyššie sú údaje v bežnom rozsahu. Ak však svoje údaje naformátujem ako tabuľku Excel, veci sa stanú ešte komplikovanejšími. Štruktúrované odkazy nemajú vstavaný spôsob, ako ukotviť začiatok rozsahu, takže by som buď musel prekonať logiku tabuľky pomocou bežných odkazov na bunky, alebo vytvoriť nový vzorec, ktorý využije funkciu INDEX.

=SUM(INDEX([Profit],1):[@Profit])

Riešenie: Funkcia SCAN

Funkcia SCAN sa nachádza v jednej bunke a výsledky rozlieva dole po stĺpci, čím zabezpečuje, že moje súčty sú vždy štrukturálne a matematicky správne.

Syntax SCAN

Funkcia SCAN má tri argumenty:

=SCAN([initial_value],array,lambda)

kde :

• initial_value je východiskovým bodom pre bežiaci súčet.
• pole je pole, ktoré sa má prehľadávať pri vytváraní bežného súčtu.
• lambda je vlastná funkcia, ktorá sa volá na prehľadanie poľa.

Štruktúra LAMBDA

Tretí argument, lambda, prijíma tri parametre:

=LAMBDA(accumulator,value,body)

kde :

• akumulátor je hodnota, ktorá sa sčíta a vráti ako výsledok.
• value je aktuálna hodnota v poli.
• telo je výpočet aplikovaný na každý prvok v poli.

Dôležité body, ktoré si treba všimnúť pred začatím

Na prvý pohľad vyzerá SCAN zložitejšie než jednoduchá súčet SUM. Ale je rozdiel medzi vzorcom, ktorý je ťažké napísať, a takým, ktorý je ťažké spravovať. SCAN si vyžaduje trochu učenia na začiatku, ale keď je raz nastavený, je to nástroj typu nastav a zabudni, ktorý chráni vaše dáta pred manuálnymi chybami, ktoré sužujú tradičné bežné súčty.

Hoci je funkcia SCAN skvelá na spracovanie bežných súčtov, existuje jedno upozornenie: nefunguje v rámci tabuľky Excel. Dôvodom je, že tabuľky vyžadujú, aby každý riadok mal vlastný nezávislý vzorec alebo hodnotu, zatiaľ čo funkcia SCAN rozširuje výsledky do viacerých riadkov. To znamená, že ju treba používať v bežnom rozsahu. Napriek tomu je stále výhodnejšie formátovať zdrojové údaje ako tabuľku Excel z dôvodov, ktoré spomeniem čoskoro.

Príklad 1: Výpočet základného priebežného súčtu

V tomto hárku sú stĺpce Mesiac a Zisk formátované ako tabuľka Excelu (nazvaná T_Profits) a stĺpec Bežný súčet je v bežnom rozsahu v stĺpci D. Prázdny stĺpec C slúži ako medzera, aby tabuľka „nezachytila“ vonkajší stĺpec.

Tu je vzorec, ktorý zadám do bunky D2:

=SCAN(0,T_Profits[Profit],LAMBDA(a,b,a+b))

kde :

• Počiatočná_hodnota je 0, pretože riadok 2 obsahuje prvý záznam v mojom datasete (nehádam žiadne čísla z roku 2023).
• T_Profits[Profit] je zoznam hodnôt zisku v stĺpci Profit tabuľky T_Profits, cez ktoré bude prechádzať.
• LAMBDA(a,b,a+b) považuje a za doterajší súčet, b za hodnotu v stĺpci zisku na aktuálnom riadku a a+b za výpočet, ktorý je potrebné vykonať.

Výhoda použitia tejto metódy namiesto staromódneho prístupu sa ukáže, keď potrebujem vložiť riadok do stredu dátovej množiny—táto metóda zvládne túto štrukturálnu zmenu a je pripravená, aby som do prázdnej bunky v stĺpci B vložil mesačný súčet.

Tiež nemusím predchádzať stĺpec A ani ťahať vzorce nadol, pretože keď zadám nový mesiac do bunky A22, celá štruktúra sa tejto položke automaticky prispôsobí.

Čo viac, ak potrebujem vymazať riadok zo stredu súboru dát, zvládne túto úpravu bez problémov.

Príklad 2: Sledovanie pohyblivých priemerov

Na výpočet kĺzavého priemeru, ktorý sa aktualizuje pri pridávaní dát, kombinujem funkcie SCAN a SEQUENCE. Zatiaľ čo bežný súčet v príklade vyššie pridáva hodnoty, kĺzavý priemer delí celkovú sumu počtom spracovaných záznamov.

Aby som to urobil, do bunky D2 zadám:

Správa How-To Geek

Správa How-To Geek

Tým, že sa prihlásite na odber, súhlasíte s prijímaním newslettera a marketingových e-mailov a akceptujete Podmienky používania a Zásady ochrany osobných údajov spoločnosti Valnet. Odber môžete kedykoľvek zrušiť.

=SCAN(0,T_Profits[Profit],LAMBDA(a,b,a+b))/SEQUENCE(ROWS(T_Profits[Profit]))

kde :

• Časť vzorca SCAN počíta priebežný súčet presne tak, ako som to urobil v Príklade 1.
• Časť SEQUENCE vytvára dynamický zoznam čísel (1, 2, 3...), ktorý zodpovedá indexu riadku. Napríklad v piatom riadku sekvencia vráti číslo 5.
• Vydelením bežiaceho súčtu (/) poradovým číslom riadku Excel vráti priemer všetkých hodnôt od začiatku až po aktuálny riadok.

Príklad 3: Reset od začiatku roka

V doterajších príkladoch sa funkcia SCAN používa s predpokladom, že chcem zachovať priebežný súčet pre roky 2024 a 2025. Predpokladajme však, že chcem, aby sa výpočet hromadil do decembra 2024 a potom sa okamžite po januári 2025 resetoval.

Pomocou staromódnej metódy by som musel manuálne prerušiť svoj súčet každý 12 mesiacov. S SCAN však môžem do LAMBDA priamo zabudovať prepínač resetu.

Tu je vzorec, ktorý potrebujem použiť:

=SCAN(0,SEQUENCE(ROWS(T_Profits[Profit])),LAMBDA(a,i,IF(MONTH(INDEX(T_Profits[Month],i))=1,INDEX(T_Profits[Profit],i),a+INDEX(T_Profits[Profit],i))))

kde :

• SEQUENCE(RIADKY(T_Profits[Profit])) vytvára zoznam čísel (1, 2, 3...) predstavujúcich každý riadok.
• V LAMBDA i predstavuje aktuálne číslo riadku.
• INDEX(T_Profits[Month],i) pozerá na dátum v aktuálnom riadku. Ak je január (1), prepínač resetovania sa aktivuje a vracia iba aktuálny zisk (INDEX(T_Profits[Profit],i)). Ak ide o iný mesiac, vykoná sa bežný výpočet a+profit.

Záverečné kroky: Upratanie výstupu

Hoci vyššie uvedené vzorce fungujú dobre tak, ako sú, finálny doladenie je to, čo robí môj tabuľkový súbor čo najlepším:

• Pravidlo „jednej bunky“: Pamätajte, že pretože ide o rozširujúce sa vzorce, stačí ich zadať iba do hornej bunky. Ak by som niekedy potreboval zmeniť logiku (napríklad prejsť z bežného súčtu na bežný priemer), musím upraviť iba tú jednu jedinú bunku.
• Prekážka rozliatia: Ak niekedy uvidím chybu #SPILL!, keď zadám svoj vzorec a stlačím Enter, často je to preto, že niečo blokuje dynamické pole, aby sa rozlialo. V tomto scenári, hneď ako vymažem staré manuálne vzorce alebo náhodný text v stĺpci, výsledky SCAN sa okamžite rozšíria nadol.

Kým funkcia SCAN je revolučná pri počítaní bežných súčtov, je len jednou zo širšieho súboru prelomových funkcií, ktoré zmenili spôsob, akým Excel funguje - vďaka dynamickým poliam. Funkcie ako FILTER, SORT a UNIQUE v podstate nahradili mnoho starých funkcií, na ktoré som sa kedysi spoliehal. Namiesto toho, aby som musel pracovať s dátovou sadou riadok po riadku, tieto nové nástroje umožňujú jednej bunke zvládnuť ťažkú prácu naprieč celým rozsahom. Prioritizovaním tohto správania splývania vytváram tabuľky, ktoré sú odolné voči klasickým manuálnym chybám, ktoré kedysi narušovali môj pracovný tok.