ARRAYFORMULA – důvod, proč si zamilujete Google Sheets

Mám moc rád MS Excel a jsem přesvědčen, že neexistuje lepší tabulkový kalkulátor. Ale přesto čím dál častěji používám Google Sheets a to ze dvou důvodů – snadné (a bezplatné) sdílení tabulek a funkce ARRAYFORMULA.

Ona to vlastně není až tak úplně funkce, je to jen jakýsi konstrukt, který říká, že daný vzorec se týká nějakého pole buněk, nikoliv jedné. Ano, i MS Excel má pole vzorců, jenže je zde podstatný rozdíl – u MS Excelu musíte předem označit všechny buňky, které se mají vyplnit výsledkem, u Google Sheets napíšete vzorec jen do jedné a on vyplní všechny ostatní.

Skvěle se to totiž hodí i pro řešení problému, který jsem zmínil včera, tedy když potřebujete do nějakého sloupce tabulky dát nějaké hodnoty či výpočet a chcete, aby se to automaticky počítalo u všech řádků a nemuseli řešit problémy, když nějaké řádky přesunete, přidáte, smažete.

Tedy řekněme, že máte

• v buňkách A1 až A1000 nějaké hodnoty
• v buňkách B1 až B1000 nějaké další hodnoty

A potřebujete do buněk C1 až C1000 dá třeba násobek hodnot v předchozích dvou sloupcích, tedy pro buňku C1 byste napsali =A1*B1

Takže vy místo toho napíšete do C1 vzorec

=ARRAYFORMULA(A1:A*B1:B)

a Google automaticky doplní všechny hodnoty. A co víc, když zkusíte nějakou hodnotu z kteréhokoliv řádku ve sloupci C smazat nebo změnti, tak ji Sheets okamžitě dopočítá zpět. Další výhodou je pak rychlost, nemáte to totiž 1000 výpočtů, ale jen jeden (byť nad tisíci řádků).

Nahrazení A1 za A1:A jsme Google Sheets řekli, aby prostě počítal od A1 až do konce sloupce A. Proto jste si asi všimli, že Sheets doplní výpočet do všech řádků, nejen do těch tisíce. Při řešení využijeme druhé úžasné funkce Google Sheets, o které si někdy povíme více a to je funkce Filter. Prostě napíšeme

=ARRAYFORMULA(FILTER(A1:A*B1:B;A1:A<>""))

Tím říkáme „Použij výpočet A1*B1 na všechny pole od A1 resp. B1 po poslední pole ve sloupci A resp. B, ale pouze pakliže příslušná buňka ve sloupci A není prázdná“.

Teď si možná říkáte, že to tak užitečné není, že toho byste dosáhli i třeba pomocí Formátovaných tabulek, které má Excel někdy od verze 2007 a které také umí vyplňovat vzorce do všech řádků. Jenže tyhle funkce v Google Sheets fungují i napříč listy. Takže si dáme další příklad:

Řekněme, že vám nějaký online systém vyjíždí CSV tabulku, kde máte seznam objednávek – ID uživatele, ID objednávky a její hodnotu. Vy si tenhle seznam natáhnete do vaší Google tabulky pomocí další skvělé funkce IMPORTDATA.

No a teď chcete mít na dalším listu seznam objednávek, které byly vyšší než 1000 Kč. Zvládnete to tímhle jedním zápisem na další list:

=ARRAYFORMULA(FILTER('List 1'!A1:C;'List 1'!C1:C>1000))

Tedy vezmi mi všechny hodnoty z A1:C, ale pouze za předpokladu, že hodnota ve sloupci C je vyšší než 1000. Úchvatné, ne? V Excelu byste to museli řešit makry či kontingenčními tabulkami, se všemi jejich nevýhodami.

A to není všechno – výstup pak můžete i seřadit podle nějakého sloupce, třeba sestupně podle posledního sloupce, díky funkci SORT

=ARRAYFORMULA(SORT(FILTER('List 1'!A1:C;'List 1'!C1:C>10);3;FALSE))

Jo a možná vás napadlo: kdybyste chtěli udělat součet všech objednávek pro jednotlivé uživatele, tak aby se jejich ID neopakovalo, můžete použít funkci UNIQUE nad sloupcem s ID uživateli a následně využít funkce SUMIF, a opět přes ARRAYFORMULA si ji natáhnout do všech řádků ke všem uživatelům.