Google Formulář: použití vzorce na nový řádek

Jako spolupořadateli Mistrovství ČR ve fyzické kondici mi připadl úkol provést automatizaci některých procesů v rámci kterých jsem použil dosud nevyzkoušené postupy.

Například jsem řešil, jak vypočítat v Tabulkách Google vzorec nad novou odpovědí z Google Formuláře. V čem je problém? Google Form totiž nové řádky vkládá (nepřepisuje) a tak se automaticky nerozkopíruje vzorec z buňky, která byla pouze roztažena dolů na prázdná pole.

Řešením je konstrukce se vzorcem ARRAYFORMULA (česky maticový vzorec), která funguje tím způsobem, že do ní vložený vzorec aplikuje na všechny následující řádky podle zadání. Například:

=ARRAYFORMULA(A2:A+B2:B25)

Provede sečtení všech hodnot ve sloupci A se všemi v poli B2 až B25, přičemž je tento vzorec umístěn pouze v jedné buňce C3. Toto není těžké pochopit. Problém nastává, pokud začnete vzorec krmit složitějšími daty, protože má různá omezení. Například neumí logickou konstrukci OR, případně provádění agregačních funkcí jako COUNT, SUM, atp. 

Můj problém spočíval v tom, že jsem potřeboval shlukovat soutěžící do kategorií podle pohlaví a věku, přičemž jsem musel minimalizovat použití složitějších logických funkcí, takže jsem nakonec vytvořil příšernou, leč funkční kaskádu podmínek IF.

ARRAYFORMULA(
IF(G2:G="";
        "";
        IF(G2:G = "Muž";
                IF(year(now()) - D2:D<20;
                        "junior";
                        IF(year(now()) - D2:D<50;
                                "hlavní";
                                IF(year(now()) - D2:D<100;
                                        "senior";
                                        "chyba věku")))));
                IF(year(now()) - D2:D<20;
                        "juniorka";
                         IF(year(now()) - D2:D<50;
                                "hlavní";
                                 IF(year(now()) - D2:D<100;
                                        "seniorka";
                                        "chyba věku"))))))

Pro upřesnění v polích "G" jsou textově informace, zda-li je závodník Muž či Žena a v "D" má rok narození. Příklad uvádím proto, že je velmi pravděpodobné, že řešíte něco podobného a inspirace není od věci, proto koukněte třeba sem.

Zajímavost na závěr: Tato funkce není (alespoň doposud) implementována v MS Office, takže po stažení dokumentu v XLS souboru se =Arrayformula() sama přemění na pouhý vzorec nad buňkami. Mějte to na paměti, až se budete snažit uploadovat zpět do Google Docs dříve stažený soubor.