V dnešním videu jsem se pro vás snažila shrnout 15 kroků, které byste měli udělat, než někomu pošlete vás excelový soubor. Jedná se o kontrolní kroky, které odhalí většinu chyb ve vašem Excelu, takže budete mít šanci je opravit ještě předtím, než váš excelový soubor někomu pošlete. Jednotlivé chyby, jak je odhalit a jak je opravit, si ukážeme na praktických příkladech.
Excelový soubor ke stažení
15 triků k odstranění chyb v Excelu
Jednotlivé chyby a jak je odhalit si ukážeme na následujícím excelovém sešitu, kde máme dva listy. Jeden se zdrojovou tabulkou dat a druhý s několika základními výpočty, které vycházejí ze zdrojové tabulky dat.
1. Zapnutí zobrazení vedle sebe nebo duplikovat obrazovku
Pokud máte provádět kontrolu v excelovém sešitu, který má více listů, tak to po určité době může být dost unavující, jelikož to znamená spoustu přepínání se z listu na list, čímž může rovněž dojít k chybě, jelikož se tak velmi snadno překouknete. Jedním z nejlepších triků na takovouto kontrolu je to, že si přepnete zobrazení Excelu. Na kartě Zobrazení vyberete Nové okno, tím se Excel duplikuje. Otevře se nové okno Excelu, které je duplikátem otevřeného souboru. Výhodou je, že si v jednom okně můžete otevřít list Zdrojová data a ve druhém okně Výpočty a vidíte oba listy zároveň. Znovu klikneme na kartu Zobrazení a vybereme Uspořádat vše a vyberete vedle sebe, okna se zarovnají vedle sebe na obrazovce a teď můžeme provádět kontrolu dat.
2. Kontrola součtů
Základní kontrola každého excelového souboru spočívá v tom, si vždy zkontrolovat, že vám sedí celkové součty. Zůstaneme v zobrazení, které jsme si zapnuli, kdy na jednom listu vidíme výpočty a na druhém zdrojová data. Na listu výpočty máme základní výpočty, které vycházejí ze zdrojových dat. Na listu s výpočty máme uvedený celkový součet tržeb ve výši 200 050 Kč. Rychlou kontrolou nad zdrojovou tabulkou zjistíme, zda máme součty správně. Nad zdrojovou tabulkou pro kontrolu provedeme součet tržeb a rychlým pohledem zjistíme, že se součty nerovnají.
Klikneme tedy na sumu na listu Výpočty a zjistíme, že vzorec sčítá pouze do řádku G59 nicméně máme ve zdrojové tabulce hodnoty až po řádek G61. Této chybě můžete předejít, pokud ze své zdrojové tabulky uděláte oficiální excelovou tabulku, která automaticky zahrnuje nově přidané hodnoty do všech souvisejících výpočtů. A nebo zkuste trik s funkcí SUMA, který automaticky zahrnuje nově přidané hodnoty do vzorce, tento trik najdete ve videu Tajné triky s funkcí SUMA. Na chybějící údaje v součtech se vás Excel rovněž snaží většinou upozornit zeleným trojúhelníkem v levém horním rohu buňky. Pokud se vám tento trojúhelník objeví v buňce je na místě překontrolovat správnost výpočtu. Chybu tedy opravíme tím, že vzorec protáhneme a zahrneme do SUMY všechny řádky.
3. Kontrola klíčových hodnot
Kontrolovat byste neměli pouze celkové součty, ale všechny základní klíčové hodnoty vašich dat. Například celkové hodnoty prodeje, počáteční a konečné datum, zkrátka klíčové hodnoty, které jsou důležité pro vaši sadu dat.
Klíčové hodnoty můžete zkontrolovat několika způsoby, ke kontrole můžete využít například základní excelové funkce. Řekněme, že provedeme jednoduchou kontrolu nad sloupcem Datum na listu Zdrojová data, abychom ověřili, kdy byl proveden první a poslední nákup. K tomu můžeme využít funkci MIN a MAX. Vedle zdrojové tabulky si zkusíme spočítat nejprve funkci MIN pro datumové hodnoty a následně funkci MAX pro sloupec s datumy a hned vidíme, že něco u minimální hodnoty není něco správně. Funkce MIN ukazuje, že jsme první nákup provedli v roce 1900, což je evidentně překlep.
Pro vyhledání této chybné hodnoty, ale i pro její odhalení můžeme použít automatický filtr nad tabulkou. Klikneme do tabulky a zmáčkneme klávesovou kombinaci CTRL+SHIFT+L, čímž se do záhlaví tabulky vloží filtr. Pouhým pohledem na filtr ve sloupci Datum bychom viděli, že první datum nevypadá úplně správně, vyfiltrujeme tedy toto datum a datum opravíme.
Vyhledat toto datum byste mohli i pomocí funkce POZVYHLEDAT. Vedle datumu napíšeme funkci POZVYHLEDAT, kde jako co hledáme označíme datum, toto datum hledáme ve sloupci s datumy a hledáme přesnou shodu. Vrátil ose číslo 17, což znamená, že toto chybné datum je na 17. řádků v tabulce. Najdeme řádek a hodnotu opravíme.
Kontingenční tabulka pro kontrolu chyb
Při kontrole klíčových hodnot je velmi nápomocná i kontingenční tabulka. Ta nám totiž dovolí provést rychlé sumární přehledy našich dat a pomůže odhalit chyby. Zkontrolujeme tedy výpočty na listu Výpočty. Klikneme do zdrojové tabulky dat a vytvoříme z naší zdrojové tabulky kontingenční tabulku na novém listu. Do pole hodnoty nejprve vložíme pole tržby a kontingenční tabulka ukazuje, že celkové tržby sedí s celkovými tržbami z našeho vzorce.
Tržby vyhodíme a vložíme do pole hodnot kusy, abychom ověřili, že máme správně sečtené prodané kusy produktů. Akorát, že kontingenční tabulka ukazuje 58 kusů a naše výpočty ukazují 185 kusů. Někde máme tedy chybu. Vzhledem k tomu, že kontingenční tabulka skoro nikdy nelže, tak chyba bude tutově v našich datech. Nejprve zkontrolujeme výpočet, ten se zdá být v pořádku, jelikož sčítá všechny řádky. Chyba bude tedy přímo ve zdrojových datech.
Jak ale poznáme, kde máme chybu?
V tomto případě je to jednoduché. Do pole hodnoty v kontingenční tabulce jsme vložili číselnou hodnotu, kontingenční tabulka by tak měla jako základní výpočet provést součet hodnot. Ona ale provedla počet hodnot, což značí, že máme někde ve sloupci Počet kusů textovou hodnotu. Jakmile máte totiž mezi číselnými hodnotami text, tak kontingenční tabulka v základu provede počet a nikoliv součet. Máme několik způsobů, jak tuto chybu odhalit.
Nejjednodušší je vrátit se ke zdroji a zkusit odhalit chybu pomocí funkce JE.ČISLO. Vedle tabulky do buňky I4 napíšeme funkci JE.ČISLO a v ní označíme počty. Stáhneme funkci dolů a kde se objeví NEPRAVDA, tam máme text. Nebo použijeme kontingenční tabulku a počty vložíme do řádků a vidíme, že problém je v čísle 4.
Nicméně tato chyba je zákeřná v tom, že ji pouhým okem ve zdrojových datech neodhalíte. Funkce SUMA nad sloupcem totiž počítá, i když je mezi číselnými hodnotami textová hodnota. Textovou hodnotu funkce SUMA jednoduše přeskočí, bez toho, aby na tuto chybu upozornila. Stejně tak automatický filtr nad tabulkou se tváří, že 4 ve formátu text je pouze obyčejná čtyřka. Až vizuálně zjistíme, když vyfiltrujeme hodnoty, že tato číslice 4 má v poli i mezeru a proto je z ní text. Nejjednodušší a nejspolehlivější je tak použít funkci JE.ČISLO.
Chybu opravíme a vrátíme se ke kontingenční tabulce a aktualizujeme ji. Vyhodíme počet kusů z tabulky a znovu ho vložíme do pole hodnoty. Teď už kontingenční tabulka ukazuje správný součet hodnot 189.
Podmíněné formátování pro kontrolu chybu
Pro kontrolu klíčových hodnot můžete použít i podmíněné formátování. Můžete si třeba nastavit kontrolní výpočty. Na listu Výpočty klikneme na SUMU tržeb a klikneme na podmíněné formátování a Pravidla zvýraznění buněk a zde vybereme Rovná se. Zde v poli označíme buňku SUMY nad tabulkou a vybereme, že pokud se hodnoty budou rovnat, chceme buňku obarvit na zeleno. Potvrdíme. Jelikož jsme součty opravili, tak je buňka zelená.
4. Získat detail v kontingenční tabulce
Na listu výpočty máme i součet tržeb pro jednotlivé kategorie produktů. Na první pohled se zdá, že vše počítá správně. Pro výpočet jsme použili jednoduchou funkci SUMIF, která nám sečetla tržby ve zdrojové tabulce dle jednotlivých druhů produktů. Jednoduchou kontrolou součtu ale zjistíme, že někde máme chybu. Celkový součet za tyto tři kategorie by měl být stejný jako celkový součet, jelikož všechny produkty spadají pouze do těchto tří kategorií. Jenže není. Letmým pohledem na zdrojová data chybu taky nevidíme.
V odhalení chyb nám opět pomůže kontingenční tabulka. Do sloupce hodnoty vložíme tržby a na řádky druhy produktů. A okamžitě vidíme chybovou hlášku #NENÍ_K_DISPOZICI, což značí, že někde ve zdrojových datech máme chybu.
Jak ale rychle zjistit, kde chybu máme? Samozřejmě se můžeme vrátit ke zdrojové tabulce a vyfiltrovat nad sloupcem Druh produktu chybovou hlášku. Když ale zkusíme automatický filtr, tak tam žádná chybová hláška není, jako na Obrázku č.8.
Můžeme se tedy vrátit ke kontingenční tabulce a vyřešit to s její pomocí. Jednoduše dvojitě poklepejte na chybové hodnoty, čímž se vám rozbalí nový list s detailem s těmito hodnotami. Dá vám to tedy nápovědu, u jakého produktu, který den máme chybu.
Proč se ale chybové hlášky nezobrazili v automatickém filtru nad tabulkou?
Odpověď najdeme, když se přepneme na záložce Soubor na Informace, kde vybereme Zkontrolovat sešit. Zde vybereme možnost Zkontrolovat metadata. Excel nás upozorní, že bychom měli Excel nejprve uložit, takže to uděláme a budeme pokračovat a necháme Excel zkontrolovat soubor. Ve vyskakovacím okně sjedeme dolů a vidíme problém > v Excelovém listu máme skryté řádky.
Tím, že jsou řádky skryté, tak se nezobrazí ani v automatickém filtru. Kontingenční tabulka je ale odhalí. Vrátíme se tedy ke zdrojové tabulce a buď skryté řádky najdeme vizuálně a nebo klikneme do rohu celého sešitu, čímž označíme celý list a na kartě Domů vybereme Formát a zde Zobrazit skryté řádky nebo sloupce. Tím se na listu rozbalí všechny skryté buňky. Vrátíme se k filtru a najednou se chyba ve filtru objevila.
Když jsme s opravou chyby hotovi, tak pomocnou kontingenční tabulku s detailem můžeme klidně smazat.
5. Kontrola, zda vše počítá
S předešlou chybou souvisí i celková kontrola toho, zda nám všechny vzorce na listech počítají správně. Navážeme tedy na minulou chybu, kde jsme kromě skrytých řádků odhalili i chybu ve vzorci. Chyba je ve funkci SVYHLEDAT, která nám přiřazuje druh produktu k produktu ze zdrojové tabulky. Podíváme se blíže na chybové hodnoty. Na první pohled vidíme, že se nám ve zdroji objevil nový produkt Kravata, který ale ve zdrojové tabulce pro funkci SVYHLEDAT nemáme. Můžeme ho tedy přidat do tabulky a tím chybu opravit. Přidáme tedy produkt Kravata do tabulky a k němu oblečení, ještě ale musíme opravit funkci SVYHLEDAT. Vrátíme se k první buňce SVYHLEDAT a aktualizujeme zdrojovou tabulku, abychom do ní zahrnuli i Kravaty. Funkci potvrdíme a pošleme ji dolů a vidíme, že jedna chyba zmizela.
Další chyba je u sukně, tento produkt ale ve zdrojové tabulce máme. Nejčastější chybou při používání vyhledávacích funkcí je to, že někde v textu máme nadbytečné mezery. Buďto můžete buňku prozkoumat ručně, klikneme na buňku a za slovem Sukně vidíme nadbytečnou mezeru a nebo můžete použít funkci STEJNÉ. Vedle napíšeme funkci STEJNÉ, kde jako první slovo označíme toto slovo a jako druhé, slovo sukně ze zdroje. Funkce vrátila NEPRAVDU, což znamená, že slova nejsou stejná a je to kvůli nadbytečné mezeře. Když ji smažeme, funkce vrátí PRAVDU. Pokud chcete předejít této chybě, bylo by nejlepší ve funkci SVYHLEDAT použít i funkci PROČISTIT. Vrátíme se k funkci SVYHLEDAT a buňku s hledaným produktem zabalíme do funkce PROČISTIT, což z buňky odstraní všechny nadbytečné mezery. Funkci potvrdíme a pošleme dolů. Druhá chyba je odstraněná.
6. Použití automatického filtru pro překlepy
Velmi dobrou pomůckou pro odhalení chyb je i použití automatického filtru nad tabulkou. Použijeme ho k odhalení další chyby s funkcí SVYHLEDAT. Automatický filtr nad tabulkou ukáže ve filtru jedinečné hodnoty. Je tak skvělým pomocníkem pro odhalování překlepů, špatné diakritiky a jiných textových chyb. Když teď otevřeme filtr s produktem, tak vidíme, že zde máme dvakrát Kalhoty dámské a je to proto, protože u druhého nám chybí čárka nad slovem a. Vyfiltrujeme toto slovo, opravíme diakritiku, a hned vidíme, že funkce SVYHLEDAT začala fungovat. Odfiltrujeme tabulku a vrátíme se k původní tabulce na listu Výpočty a zkontrolujeme celkové součty.
7. Kontrola správnosti výpočtů
Zelené trojúhelníky v Excelu neznačí pouze to, že vám v celkových součtech chybí hodnoty, ale všeobecně značí nekonzistentnost vzorce. Nikdy byste proto výskyt zeleného trojúhelníku neměli jen tak přejít, někdy se trojúhelník objeví i přesto, že je váš výpočet správně, v takovém případě ho můžete vypnout tím, že na něj kliknete pravým tlačítkem a vyberete Ignorovat. Nám se trojúhelníky nicméně objevili ve sloupci Sídlo. Zkusíme zjistit, kde máme chybu. Klikneme na první buňku s trojúhelníkem a podíváme se na vzorec, funkce hledá Vodafone a hledá ji v tabulce od řádku L20 do řádku M25. Zkontrolujeme zdrojovou tabulku a je to správně. Klikneme na buňku, kde zelený trojúhelník nemáme a porovnáme funkce. A zde máme zdrojovou tabulku jen do řádku M24. Takováto chyba vznikne nejčastěji nepozorností a to tak, že máte například vyfiltrovanou tabulku, všimnete si chyby, opravíte ji, ale už ji neopravíte v celé délce sloupce, ale jen ve vyfiltrovaných hodnotách. Pokud bychom teď vyfiltrovali pouze České Budějovice a opravili vzorec a stáhli ho pro ostatní buňky, tak opět opravíme vzorec pouze ve viditelných buňkách. Abychom provedli opravu správně, tak musíme odfiltrovat tabulku, opravit první buňku a následně ji poslat dolů pro všechny buňky.
8. Kontrola duplicit a jedinečných položek
Hodit se vám může i kontrola duplicitních položek nebo naopak jedinečných položek. Na listu výpočty máme ještě shrnutí tržeb po jednotlivých klientech a městech. Celkové součty sedí, ale když se na data podíváme podrobněji, tak si všimneme něčeho divného. Vidíte to?
Každý klient má jiné sídlo, nemáme dva klienty ve stejném městě, tudíž by se měly součty u měst a klientů rovnat. V Praze máme jen O2, a jejich tržby se nerovnají. Stejně jako v Opavě, kde sídlí pouze Makro.
Provedeme tedy kontrolu pomocí kontingenční tabulky, označíme zdrojová data, vložíme z nich kontingenční tabulku na nový list a na řádky vložíme klienty, do pole hodnoty tržby a do řádků ještě vložíme sídlo. A hned vidíme, že máme ve zdrojových datech opravdu chybu. U dvou klientů máme uvedená dvě sídla. Stejně tak bychom na to přišli, kdybychom označili sloupec klienti a sídlo, zkopírovali, vložili ho někam bokem a na kartě Data vybrali Odstranit duplicity. Zůstaly by pouze unikátní kombinace sídla a klienta a my bychom tak odhalili problém.
9. Kontrola podle Zobrazení vzorců
K odhalení problému z předchozího příkladu můžeme použít třeba pomůcku na kartě Vzorce a zde Zobrazit vzorce. Všechny vzorce se tím změní na vzorce a my tak vizuálně odhalíme, že nám někdo vzorec u dvou klientů přepsal natvrdo. Nefunguje tak vzorec SVYHLEDAT a proto nemáme u klienta správné sídlo. K opravení chyby stačí tedy protáhnout vzorec SVYHLEDAT. Vrátíme se ke kontingenční tabulce, vybereme Obnovit a chyby jsou opravené. Zkontrolujeme výpočty a zdá se, že teď vše sedí.
10. Podmíněné formátování pro extrémní hodnoty
Posledním výpočtem, který na listu Výpočty máme je průměrná tržba. Pro rychlou kontrolu extrémních hodnot ve zdrojových datech můžete použít i podmíněného formátování v podobě barevných škál. Řekněme, že v naší tabulce budeme chtít namátkově zkontrolovat průměrnou cenu produktu. Ve zdrojové tabulce máme jen počet prodaných kusů a tržby. Vedle tabulky provedeme pomocný kontrolní výpočet, kde jednoduše vydělíme tržby počtem kusů. Stáhneme vzorec pro ostatní buňky dolů a obarvíme buňky podmíněným formátováním, kde vybereme barevné škály. Okamžitě v datech vidíme, že máme jednu hodnotu úplně červenou a jednu naopak úplně zelenou, což značí, že máme v datech extrémní hodnoty. Někdy to tak má být, někdy vás to ale může upozornit na překlep nebo chybu. V našem příkladu víme, že by si všechny průměrné ceny produktů měly být více méně dost podobné. Máme tedy šanci ceny nebo počet kusů opravit nebo minimálně zkontrolovat.
11. Kontrola chybových hlášek
Když v Excelu počítáte s funkcemi, je vždy dobré si ověřit funkčnost i v případě, že se hodnoty změní. To, že v Excelu teď nemáte chybové hodnoty neznamená, že po změně vstupních hodnot chybová hláška nenastane. Představte si to třeba s dělením, pokud se vám v datech vyskytne nula, kterou byste měli dělit, tak vám vzorec vrátí chybovou hlášku. Je tedy správné dopředu ověřit, zda neexistuje možnost, že nám Excel vrátí chybovou hlášku, například potom, co změníme vstupní data výpočtu. Velmi časté je to třeba právě u dělení nebo vyhledávacích funkcí. Nejjednodušší je tak ošetřit rovnou ve výpočtu situaci, která by chybovou hlášku způsobila. Dělení můžeme zabalit do funkce KDYŽ, kde podmínkou bude, pokud bude jmenovatel vyšší než nula, tak chceme hodnoty dělit, jinak chceme vrátit nulu. Předejdeme tak chybové hlášce Dělení nulou. Funkci SVYHLEDAT můžeme zase zabalit do funkce IFERROR, kde specifikujeme, že pokud funkce hledanou položku nenajde, že má vrátit třeba slovo Nenalezeno. Atd.
12. Předchůdci a kontrola chyb
Ke kontrole vašeho Excelu můžete použít i dvě pomůcky, kterými jsou na kartě Vzorce Předchůdci a Kontrola chyb. Funkce Předchůdci vám ukáže, odkud se bere zdroj pro váš výpočet. Kliknete na buňku, ve kterém máte vzorec a na kartě Vzorce vyberete Předchůdci. Excel zdroje výpočtu znázorní šipkami, takže máte šanci zkontrolovat, že máte ve vzorcích označené správné buňky. Když budete s kontrolou hotovi, zmáčkněte zase Odebrat šipky a šipky zmizí. Pokud se vám v Excelu objeví chybová hláška, se kterou si nebudete vědět rady, můžete zase vyžít funkce Kontrola chyb. Ta vám umožní otevřít nápovědu k zobrazené chybě nebo zobrazit kroky výpočtu, abyste odhalili chybu ve vzorci.
13. Kontrola skrytých údajů v Excelu
Před odesláním nebo sdílením excelového souboru byste se rovněž měli vždy přesvědčit, že váš Excel neobsahuje nějaké skryté údaje, na které jste zapomněli a neradi byste je s někým sdíleli. Abyste nemuseli procházet Excel list po listu a zkoumat, zda někde nemáte zapomenuté komentáře, skryté informace nebo listy, tak vám poslouží záložka Soubor > Informace > Zkontrolovat sešit a zde vybrat Zkontrolovat metadata. Excel vás upozorní, že před kontrolou doporučuje Excel uložit, takže tu možnost potvrdíme a zde potvrdíme inspekci souboru. Po nějaké chvilce Excel vrátí souhrn kontroly metadat. Jak vidíme, tak v souboru máme nějaké komentáře. Stejně tak máme v sešitu uvedené informace o autorovi Excelu a máme tam i nějaké skryté sloupce.
Informace o autorovi Excelu můžete zkontrolovat na záložce Soubor > Informace > Vlastnosti a Upřesnit vlastnosti. Zde vidíme, že soubor vytvořil uživatel Akademie Excelu, vidíme i kdy jsme dokument vytvořili a jiné informace.
Pokud bychom chtěli zkontrolovat poznámky, na které jsme možná zapomněli, tak kontrolu musíme provést list po listu. Abyste nemuseli hledat buňku po buňce, tak na kartě Domů vyberte Najít a vybrat Poznámky. Označí se poznámka v listu, a můžeme se tak rozhodnout, zda ji ponechat nebo odstranit.
Vždy byste také měli zkontrolovat, že jste nezapomněli na nějaké skryté listy. Kontrola je jednoduchá. Klikněte pravým tlačítkem na jakýkoliv list a vyberte Zobrazit. Rozbalí se vám okno se všemi skrytými listy a můžete se rozhodnout, zda list v Excelu ponechat nebo ne.
Dobré je rovněž zkontrolovat, zda na listech nemáte skryté objekty. Na tomto listu v tomto místě mám schovaný obrázek. Ale není vidět. Možnost jak najít skryté objekty na kartě Domů a Najít a Nahradit a zde vybereme Podokno výběru. V seznamu se nám ukáže, jaké objekty máme na listu. Vidíme komentář, který jsme našli před chvílí a vidíme obrázek. A u komentáře máme očičko, což znamená, že je komentář viditelný. U obrázku máme znak pro skrytý objekt. Pokud na tuto ikonu klikneme obrázek se objeví. Takto bychom třeba nechali v listu omylem graf bonusových mezd.
14. Sledování změn
Pokud víte, že s excelovým souborem, bude někdo ještě pracovat, a chcete vědět, co dotyčný člověk změnil, tak můžete zapnout Sledování změn v dokumentu. Sledování změn označí každou změnu, kterou v excelovém dokumentu někdo provede, přičemž nejen, že změnu označí, ale ještě u ní uvede i kdo změnu provedl, kdy a hlavně jakou změnu provedl. Sledování změn musíte v Excelu nejprve povolit, jelikož většina z nás tuto možnost nemá v základu v Excelu povolenou. O tom jak na sledování změn se podívejte na související video Sledování změn v Excelu.
15. Zamčení Excelového souboru
Pokud máte v plánu zamknout excelový soubor, než ho někomu pošlete, tak hlavně zkontrolujte, že jste zamknuli správné buňky, případně, že jste nechali správné buňky povolené k vyplňování. Pokud chcete, aby uživatel mohl například v zamčené tabulce filtrovat pomocí automatického filtru, neměli byste zapomenout tuto možnost povolit při zamykání dokumentu. Více o tom, jaké formy zabezpečení v Excelu existují najdete ve videu Ochrana Excelu.
