Velké jazykové modely (LLM) jako ChatGPT mohou být skvělým pomocníkem při práci s Excel vzorci. Mohou vám pomoci nejen vytvořit správný vzorec na základě popisu, ale také jej opravit nebo vyladit tak, aby fungoval správně. V tomto článku se podíváme na tři hlavní oblasti využití:
- Jak pomocí LLM generovat vzorce v Excelu – příklady použití
- Jak ladit chyby ve vzorcích
- Jak se vyhnout běžným chybám v zadání, například nesprávné lokalizaci vzorců
Jak LLM jako ChatGPT využít k tvorbě vzorců v Excelu?
Velké jazykové modely umí skvěle pochopit popis úlohy a převést ho na odpovídající vzorec v Excelu. Stačí zadat srozumitelný dotaz a model vám vrátí odpověď ve správném formátu. Níže si ukážeme tři konkrétní příklady.
Příklad 1: Výpočet provizí podle prodejů
Máme tabulku, kde každý řádek obsahuje jméno obchodníka a jeho měsíční prodeje. Potřebujeme vytvořit vzorec, který vypočítá provizi podle následujících pravidel:
- Pokud obchodník prodal za méně než 10 000 Kč → provize je 5 %
- Pokud prodal mezi 10 000 Kč a 50 000 Kč → provize je 7 %
- Pokud prodal nad 50 000 Kč → provize je 10 %
Zadání pro ChatGPT:
„Potřebuji vzorec v Excelu, který na základě hodnoty v buňce A2 určí výši provize podle těchto pravidel: <vložená pravidla>“
Odpověď ChatGPT:
Vzorec pro výpočet provize (za předpokladu, že prodeje jsou ve sloupci A) může vypadat takto:
=KDYŽ(A2<10000; A2*0.05; KDYŽ(A2<=50000; A2*0.07; A2*0.10))V anglické verzi:
=IF(A2<10000, A2*0.05, IF(A2<=50000, A2*0.07, A2*0.10))
Příklad 2: Spojení jména a příjmení do jednoho sloupce
Máme dva sloupce, ve kterých jsou křestní jména (sloupec A) a příjmení (sloupec B). Chceme je spojit do jednoho sloupce tak, aby mezi nimi byla mezera.
Zadání pro ChatGPT:
„Jaký je vzorec v Excelu pro spojení jména ve sloupci A s příjmením ve sloupci B, oddělené mezerou?“
Odpověď ChatGPT:
Pokud používáte starší verzi Excelu:
=A2&" "&B2
Pokud používáte novější verzi Excelu:
=CONCAT(A2, " ", B2)
Příklad 3: Automatický výpočet stáří na základě data narození
Chceme spočítat věk člověka podle jeho data narození (sloupec A), přičemž věk se má počítat k dnešnímu dni.
Zadání pro ChatGPT:
„Jak spočítám v Excelu věk osoby na základě jejího data narození ve sloupci A?“
Odpověď ChatGPT:
=ROK(DNES()) - ROK(A2) - KDYŽ(DATUM(ROK(DNES());MĚSÍC(A2);DEN(A2))>DNES();1;0)Anglická verze:
=YEAR(TODAY()) - YEAR(A2) - IF(DATE(YEAR(TODAY()), MONTH(A2), DAY(A2)) > TODAY(), 1, 0)
Jak ladit chyby ve vzorcích pomocí ChatGPT?
I když ChatGPT vygeneruje vzorec, může se stát, že v Excelu nebude fungovat správně a zobrazí chybu jako #HODNOTA!, #JMÉNO?, nebo #DĚLENÍ_NULOU!. Namísto ručního hledání chyby můžete ChatGPT požádat o opravu správně formulovaným dotazem.
Aby ChatGPT správně opravil chybný vzorec, vždy ve svém dotazu uveďte:
- Přesný vzorec, který vrací chybu.
- Typ chyby (#HODNOTA!, #JMÉNO?, #N/A…).
- Co chcete, aby vzorec dělal správně (např. „Chci, aby místo chyby zobrazil text“).
- Verzi Excelu nebo jazykové prostředí, pokud to může hrát roli.
Chyba #HODNOTA! – Problém s datovým typem
Příčina: Některý z argumentů ve vzorci je ve špatném formátu. Například když funkce CONCATENATE() nebo CONCAT() očekává text, ale dostane číslo.
Jak položit dotaz ChatGPT pro opravu:
„Můj vzorec =CONCAT(A2, " ", B2) v Excelu vrací chybu #HODNOTA! Pravděpodobně proto, že jedna z buněk obsahuje číslo místo textu. Můžeš upravit vzorec tak, aby správně fungoval pro jakýkoli vstup?“
Opravený vzorec od ChatGPT:
=CONCAT(TEXT(A2, "0"), " ", TEXT(B2, "0"))
Tímto způsobem se čísla převedou na text a chyba zmizí.
Chyba #JMÉNO? – Excel nerozpoznává název funkce
Příčina: Vzorec obsahuje funkci napsanou v nesprávném jazyce (např. anglické funkce v české verzi Excelu) nebo překlep v názvu funkce.
Jak položit dotaz ChatGPT pro opravu:
„Dostal jsem vzorec =IF(A2>10, "Ano", "Ne"), ale používám českou verzi Excelu a zobrazí se chyba #JMÉNO?. Můžeš vzorec přeložit do češtiny?“
Opravený vzorec od ChatGPT:
=KDYŽ(A2>10; "Ano"; "Ne")
Chyba #DĚLENÍ_NULOU! – Dělení nulou
Příčina: Vzorec obsahuje dělení hodnotou, která je nulová nebo prázdná, např. =A2/B2, když B2 obsahuje 0.
Jak položit dotaz ChatGPT pro opravu:
„Mám vzorec =A2/B2, ale pokud je B2 prázdné nebo nulové, zobrazí se chyba #DĚLENÍ_NULOU!. Můžeš vzorec upravit tak, aby se chyba neobjevovala a místo toho zobrazil text ‚N/A‘?“
Opravený vzorec od ChatGPT:
=KDYŽ(B2=0; "N/A"; A2/B2)
Tento vzorec zkontroluje, zda je B2 nulové, a pokud ano, zobrazí text namísto chyby.
Chyba #ODKAZ! – Odkaz na neexistující buňku
Příčina: Vzorec obsahuje odkaz na buňku, která byla odstraněna nebo přesunuta.
Jak položit dotaz ChatGPT pro opravu:
„Můj vzorec =A2+B2+C2 z nějakého důvodu vrací chybu #ODKAZ!. Můžeš mi poradit, jak najít, která buňka způsobuje problém, a jak vzorec opravit?“
Možná odpověď ChatGPT:
- Zkontrolujte, zda některá z buněk A2, B2 nebo C2 neobsahuje chybu #ODKAZ!.
- Pokud ano, zkuste obnovit chybějící hodnoty nebo upravit vzorec tak, aby se na neexistující buňku neodkazoval.
- Použijte vzorec:
=KDYŽ(JE.CHYBHODN(A2); 0; A2) + KDYŽ(JE.CHYBHODN(B2); 0; B2) + KDYŽ(JE.CHYBHODN(C2); 0; C2)
Tímto způsobem se ignorují neplatné odkazy.
Chyba #N/A – Hodnota nebyla nalezena
Příčina: Funkce jako SVYHLEDAT (VLOOKUP) nebo XLOOKUP nemůže najít hledanou hodnotu v daném rozsahu.
Jak položit dotaz ChatGPT pro opravu:
„Používám vzorec =SVYHLEDAT(50; A2:B10; 2; NEPRAVDA), ale některé hodnoty vrací #N/A. Můžeš vzorec upravit tak, aby v případě nenalezení hodnoty zobrazil text ‚Nenalezeno‘ místo chyby?“
Opravený vzorec od ChatGPT:
=KDYŽ(JE.CHYBHODN(SVYHLEDAT(50; A2:B10; 2; NEPRAVDA)); "Nenalezeno"; SVYHLEDAT(50; A2:B10; 2; NEPRAVDA))
Tímto způsobem se vyhneme zobrazování chyby #N/A a místo ní bude uveden srozumitelný text.
Jak se vyhnout běžným chybám při zadávání vzorců?
Překlad vzorců do nesprávného jazyka
Problém: ChatGPT často generuje vzorce v angličtině, ale pokud máte českou verzi Excelu, nebudou fungovat. Například místo:
=IF(A2>10, "Ano", "Ne")musí být:
=KDYŽ(A2>10; "Ano"; "Ne")
Řešení: Při zadání dotazu uveďte, jakou jazykovou verzi Excelu používáte.
2. Nesprávné oddělovače argumentů
Problém: V české verzi Excelu se používají středníky ;, ale v anglické čárky ,.
Řešení: Pokud dostanete vzorec s čárkami, nahraďte je středníky. Správné řešení je ale ChatGPT oznámit v jaké jazykové lokalizaci pracujete a nechat výstup opravit.
3. Zbytečné složitosti ve vzorcích
Problém: ChatGPT někdy vytváří příliš komplikované vzorce, které lze zjednodušit.
Řešení: Požádejte ChatGPT o optimalizovanou verzi nebo zkuste vzorec zjednodušit ručně.