Excel a Tabulky s AI: vzorce, chyby, struktura i makra

Pracuju v [Excelu 365 / Excelu 2016 / Google Tabulkách], rozhraní
je [české / anglické].

Data mám v listu [Prodeje], hlavička je na řádku 1, data od řádku 2:
A = pobočka (text), B = datum (formát datum), C = tržba (číslo),
D = kategorie (text)

Ukázka řádků:
[vlož 4 řádky]

Potřebuju vzorec, který spočítá: [sečti tržby pro pobočku uvedenou
v buňce F2, ale jen za letošní rok].

Chci:
1. hotový vzorec k vložení do buňky [G2] přesně v mé jazykové verzi,
2. vysvětlení po částech, co která funkce dělá,
3. co se stane, když do dat přibude sloupec nebo řádek,
4. na jakém řádku si mám výsledek ručně ověřit.

Nepoužívej funkce, které moje verze nemá — když si nejsi jistý
dostupností, nabídni i variantu pro starší Excel.

Potřebuju v [Excelu 365] vyřešit tohle: [popiš úlohu a sloupce].

Nabídni tři různá řešení:
1. co nejjednodušší vzorec, i za cenu menší odolnosti,
2. odolné řešení, které vydrží přidání sloupce a překlepy
   v datech (mezery navíc, velká písmena),
3. řešení s modernějšími funkcemi (XLOOKUP, FILTER, LET),
   pokud dávají smysl.

U každého napiš: kdy ho použít, co ho rozbije, jak se chová při
prázdné buňce a nenalezené hodnotě. Na konci doporuč jedno
a zdůvodni proč — s ohledem na to, že tabulku budu měsíčně
aktualizovat a předám ji kolegovi.

Zdědil jsem tenhle vzorec v [Excelu, česká verze] a nerozumím mu:

[vlož vzorec]

Kontext: je v buňce [G2] na listu [Výkaz], data jsou v [Prodeje!A:D],
hlavička v řádku 1. Ukázka dat:
[vlož 4 řádky]

Vysvětli ho postupně zevnitř ven:
1. rozlož ho na vnořené části a u každé napiš, co vrací
   (na mých ukázkových datech konkrétní hodnotu),
2. co dělá vzorec jako celek, jednou větou běžnou češtinou,
3. jaké předpoklady o datech má — co v nich musí platit,
   aby fungoval,
4. za jakých okolností vrátí špatnou hodnotu, aniž by
   zobrazil chybu,
5. jak bych ho zapsal jednodušeji se stejným výsledkem.

Nic zatím neměň v mém sešitu, jen vysvětluj.

Přepiš tenhle vzorec do čitelnější podoby pro [Excel 365]:

[vlož vzorec]

Požadavky:
- výsledek musí být identický pro všechny případy, včetně
  prázdných buněk, nul a nenalezených hodnot,
- pokud použiješ LET nebo pomocné sloupce, vysvětli, co která
  část dělá,
- napiš postup, jak si ověřím, že nový vzorec dává stejné
  výsledky jako starý (kontrolní sloupec, počet neshod),
- upozorni na případy, kdy se nový a starý vzorec můžou lišit.

Nakonec navrhni komentář do buňky, který kolegovi vysvětlí,
co vzorec počítá.

Můj vzorec vrací chybu. Tady je všechno, co o tom vím:

Vzorec: [vlož vzorec]
Je v buňce: [G2], list [Výkaz]
Chybová hláška: [#N/A]
Objevuje se: [jen na některých řádcích / na všech]
Program: [Excel 365, česká verze]

Ukázka dat, na kterých to selhává (včetně řádku, který funguje):
[vlož 5 řádků]

Udělej tři věci:
1. urči nejpravděpodobnější příčinu a vysvětli, proč zrovna tuhle,
2. dej mi 2 další možné příčiny, které mám vyloučit, a jak je
   rychle otestuju,
3. napiš opravený vzorec.

Neschovávej chybu funkcí IFERROR, dokud nevíme, proč vzniká.

Vím, proč můj vzorec vrací [#N/A]: [důvod, např. položka ještě
není v číselníku]. Chci to ošetřit tak, aby výkaz vypadal
slušně, ale abych o problému věděl.

Vzorec: [vlož vzorec]

Navrhni řešení, které:
- místo chyby zobrazí [prázdno / text „nenalezeno“ / nulu]
  podle toho, co dává smysl pro následné součty,
- nezakryje jiné typy chyb než ten, který čekám,
- umožní mi vedle toho spočítat, kolik řádků do ošetření spadlo
  (kontrolní buňka s počtem).

Vysvětli, jak se ošetřená hodnota chová v součtech a průměrech.

Můj vzorec nevrací chybu, ale výsledek je špatně.

Vzorec: [vlož vzorec]
Co vrací: [412 000]
Co má vyjít: [389 500]
Jak jsem správnou hodnotu zjistil: [ručně sečteno za březen
z těchto řádků]

Ukázka dat: [vlož 8 řádků včetně těch sporných]

Najdi rozdíl: které řádky vzorec započítal navíc nebo vynechal
a proč. Projdi zejména:
- rozsahy, které nesedí velikostí nebo začínají o řádek jinde,
- čísla uložená jako text a mezery v textových hodnotách,
- data porovnávaná jako text místo jako datum,
- absolutní a relativní odkazy při kopírování vzorce dolů,
- skryté nebo filtrované řádky.

Napiš, jak si opravu ověřím na dalších dvou řádcích.

Popíšu ti, co potřebuju v tabulce sledovat, a ty mi navrhni
strukturu sešitu.

Co sleduju: [např. měsíční tržby a náklady pěti poboček,
srovnání s plánem, meziroční srovnání]
Kdo do toho bude psát: [já a vedoucí poboček]
Jak často: [měsíčně, data se vkládají z exportu]
Co z toho musí vypadnout: [jednostránkový přehled pro vedení
a graf vývoje]

Navrhni:
1. jaké listy sešit má mít a co je na kterém,
2. u datového listu přesné sloupce: název, typ, příklad hodnoty,
   jestli se vyplňuje ručně nebo vzorcem,
3. kde použít ověření dat (rozevírací seznamy) a proč,
4. které výpočty řešit vzorci a které kontingenční tabulkou,
5. tři chyby, kterých se s touhle strukturou vyvaruju.

Uveď i to, co v mém zadání chybí a na co se mě musíš zeptat.

Mám tabulku, která vznikala roky a je v ní nepořádek. Struktura
a ukázka:

[vlož hlavičku a 10 řádků]

Problémy, kterých jsem si všiml: [sloučené buňky, mezisoučty
mezi daty, datum jako text, tři varianty názvu téže pobočky]

Napiš mi plán úklidu:
1. co opravit a v jakém pořadí, aby se cestou nic nerozbilo,
2. u každého kroku konkrétní postup v [Excelu] — funkce nebo
   nástroj, který na to použiju,
3. jak před každým krokem ověřím, že se nezmění součty
   (kontrolní součet před a po),
4. co udělat, aby stejný nepořádek nevznikl znovu.

Předpokládej, že pracuju na kopii souboru.

Mám tato data v [Excelu 365, česká verze]:
sloupce [A pobočka, B datum, C kategorie, D tržba, E náklad],
[3 200] řádků, data od řádku 2.

Chci souhrn: [tržby a marže po pobočkách a měsících,
plus podíl každé kategorie].

Napiš mi:
1. návod na kontingenční tabulku krok za krokem — kam kliknout,
   co přetáhnout do řádků, sloupců, hodnot a filtrů,
2. jak nastavit, aby se datum seskupilo po měsících,
3. jak přidat počítané pole pro marži,
4. jak tabulku naformátovat, aby byla čitelná (čísla, procenta,
   pryč se součty, které nedávají smysl),
5. co udělám, když přibudou nová data — jak se to aktualizuje.

Pak napiš, jak bych stejný výsledek udělal vzorci, a řekni,
která cesta je pro měsíčně opakovaný výkaz lepší a proč.

Potřebuju do listu [Výkaz] doplnit ke každému řádku [název
produktu a marži] z listu [Ceník], kde je klíčem [kód produktu].

Data:
Výkaz: A = kód produktu, B = množství, data od řádku 2
Ceník: A = kód, B = název, C = nákupní cena, D = prodejní cena

Napiš vzorec pro [Excel 365, česká verze], který:
- najde hodnotu podle kódu bez ohledu na mezery navíc
  a velikost písmen,
- vrátí čitelnou hlášku, když kód v ceníku není,
- půjde zkopírovat dolů bez posunu odkazů na ceník,
- bude fungovat i po přidání sloupce do ceníku.

Vysvětli, proč jsi zvolil zrovna tuhle funkci, a napiš, jak
si ověřím, že se spárovaly všechny řádky.

Napiš mi makro (VBA) pro Excel, které udělá tohle:
[spojí data ze všech listů v sešitu do jednoho listu „Souhrn“,
přidá sloupec s názvem zdrojového listu a seřadí podle data]

Struktura listů: [hlavička na řádku 1, stejné sloupce A-E,
počet řádků se liší, listy se jmenují podle poboček]

Bezpečnostní požadavky:
- makro nic nemaže a nepřepisuje původní listy,
- výsledek zapisuje do nového listu; když už existuje, ať se
  zeptá, ne přepíše,
- na začátku ať zkontroluje, že listy mají očekávanou hlavičku,
  a když ne, ať skončí s hláškou,
- na konci vypíše, kolik řádků z kolika listů zpracovalo.

Ke každému bloku napiš český komentář. Pak napiš návod, jak
makro vložím a spustím, a jak ho otestuju na kopii souboru.

Napiš Apps Script pro Google Tabulky, který jednou měsíčně
[zkopíruje list „Aktuální“ do archivního listu pojmenovaného
podle měsíce a vyčistí vstupní pole pro nový měsíc].

Požadavky:
- archivní list nikdy nepřepisuj; když už existuje, přidej
  k názvu pořadové číslo,
- vyčisti jen buňky [B2:B20], vzorce a nadpisy nech být,
- před smazáním čehokoli si vyžádej potvrzení v dialogu,
- zapiš do listu „Log“ řádek s datem, kdo skript spustil
  a kolik buněk se vyčistilo,
- nic neodesílej e-mailem, jen připrav text do buňky.

Napiš návod, jak skript vložím, jak ho poprvé spustím ručně
a jak si ho případně naplánuju. Zdůrazni, co si mám před
prvním ostrým během zálohovat.

Chystám se předat tenhle sešit kolegovi a chci, aby v něm
neudělal škodu. Popis sešitu:

Listy: [výčet a co je na kterém]
Klíčové vzorce: [vlož 3-5 vzorců a kde jsou]
Co se každý měsíc mění: [vkládají se nová data do listu Data]

Připrav mi:
1. text na list „Jak to funguje“ — stručně: odkud jsou data,
   co se počítá kde, co se každý měsíc dělá, čeho se nedotýkat,
2. seznam míst, kde se sešit nejspíš rozbije, když do něj
   někdo zasáhne (přidání sloupce, seřazení, vložení řádku),
3. návrh, které buňky zamknout a proč,
4. tři kontroly, které má nový uživatel udělat po každé
   aktualizaci dat, aby poznal, že je něco špatně.

Piš pro člověka, který Excel používá běžně, ale vzorce nepíše.