6 tichých zabijáků Excel tabulek (a jak je můžete zastavit)

Váš Excelový sešit vám možná lže. Ne, není to žádná konspirace - je to jen sbírka běžných, neviditelných problémů, které obcházejí kontroly chyb a kazí vaše reporty. Pojďme tedy tyto tiché pasti najít a napravit jednou provždy.

Neviditelné výpočty: Skryté a filtrováné řádky

Svou matematiku jste zkontrolovali třikrát, ale celkový součet stále nesedí. To se často stává, protože funkce SUM v Excelu sčítá všechny buňky v rozsahu, i když jste některé řádky ručně skryli nebo je odfiltrovali.

V tomto příkladu, pokud po skrytí řádků 5 až 10 napíšete:

=SUM(A1:A15)

vzorec stále vrací 120, i když součet viditelných buněk je 75.

Tato duchovní data jsou častým viníkem, kterého je téměř nemožné na první pohled odhalit. Místo toho použijte funkci SUBTOTAL:

=SUBTOTAL(109,A1:A15)

První argument určuje Excelu, který výpočet provést a zda zahrnout skryté řádky. Pro sčítání použijte kód 109, aby se zajistilo, že jakýkoli řádek skrytý - ať už filtrem nebo ručně - zůstane mimo vaše výpočty.

Neviditelný filtr: Když ikony trychtýře selhávají

Ve výchozím nastavení, když ve tabulce Excel filtrujete data, u příslušného záhlaví sloupce se zobrazí užitečná ikona trychtýře.

Tato ikona však někdy může zmizet—obvykle když používáte nástroj Pokročilý filtr. Excel si myslí, že vám dělá laskavost tím, že udržuje záhlaví přehledná, ale ve skutečnosti vás to vede k domněnce, že vám chybí data.

V tomto scénáři se podívejte na čísla řádků úplně vlevo. Pokud je posloupnost přerušena a čísla jsou modrá, je aktivní filtr.

Chcete-li to opravit, na kartě Data klikněte na „Vymazat“. Tím se odstraní všechny filtry v listu, viditelné i neviditelné, a vaše ztracená data se vrátí z prázdna.

Smíšené datové typy: Čísla uložená jako text

Buňka, která vypadá jako číslo, ale ve skutečnosti se chová jako text, je v Excelu tichý zabiják. Pokud se pokusíte sčítat sloupec, kde jsou čísla uložena jako text, Excel je jednoduše bere jako nuly, což má za následek nesprávný součet.

Ve výchozím nastavení Excel zarovnává čísla doprava a text doleva. Pokud jsou vaše hodnoty natěsno u levého okraje buňky, Excel si nemyslí, že se jedná o čísla. Můžete vidět malý zelený trojúhelníkový indikátor chyby v rohu buňky, ale pokud je tato funkce vypnutá, pracujete naslepo.

Další způsob, jak to zkontrolovat, je vybrat podezřelé buňky a podívat se na stavový řádek. Pokud vidíte „Počet“, ale ne „Součet“ nebo „Průměr“, jedná se o text.

Chcete-li to opravit, zatímco jsou buňky stále vybrané, klikněte na „Obecné“ v rozbalovací nabídce Formát čísla na kartě Domů.

Pokud zůstanou zarovnané vlevo, klikněte na „Text do sloupců“ na kartě Data a potom klikněte na „Dokončit“.

Excel znovu vyhodnotí buňky, převede textové hodnoty na skutečná čísla a dokončí výpočet. Nyní stavový řádek také zobrazuje průměr a součet, protože pracuje s čísly, ne s textem.

Fantomový prostor v počtech a vyhledáváních

Jedním z nejfrustrujících scénářů v Excelu je, když počet nebo vyhledávací funkce vrátí nesprávný výsledek, aniž byste o tom věděli.

Předpokládejme, že chcete spočítat, kolikrát se ve sloupci Produkt vaší tabulky T_Sales objeví „Cap“. K tomu použijete následující vzorec COUNTIF:

=COUNTIF(T_Sales[Product],M1)

Nicméně, i když jsou ve sloupci ve skutečnosti čtyři položky „Cap“, vzorec vrací 3. Pravděpodobně je to způsobeno neviditelnou mezerou na konci slova „Cap“ v jedné z buněk. Skutečně, buňka obsahující slovo „Cap “ (s mezerou na konci) je technicky jiný řetězec než „Cap“ (bez mezery na konci).

Chcete-li vyčistit data, aniž byste poškodili mezery mezi slovy v jiných položkách (například v "Rubikově kostce"), použijte funkci TRIM ve vedlejším sloupci:

=TRIM([@Product])

Tímto způsobem se odstraní všechny mezery na začátku a na konci, ale zachovají se jednotlivé mezery mezi slovy. Poté můžete tyto nové, oříznuté textové řetězce vybrat, vložit je jako hodnoty (Ctrl+Shift+V) do původního sloupce a smazat pomocný sloupec. Když to uděláte, vzorec správně spočítá počet velkých písmen.

Nestabilní funkce a ruční výpočet

Excel může být nesnesitelně pomalý, pokud je nucen příliš často přepočítávat příliš mnoho dat. Obvykle se to stává kvůli volatilním funkcím, jako jsou OFFSET, INDIRECT a TODAY. Na rozdíl od normálních funkcí se tyto přepočítávají pokaždé, když provedete změnu kdekoli v sešitu.

Abychom se vyrovnali se zpožděním, můžete v rozbalovacím menu Možnosti výpočtu na kartě Vzorce zvolit „Ruční“.

Nicméně i když to program činí rychlejším, můžete zapomenout stisknout F9 k aktualizaci svých dat, což znamená, že byste mohli dělat zásadní rozhodnutí na základě zastaralých čísel.

Lepší řešení je nahradit volatilní funkce stabilními alternativami, například použitím INDEX místo OFFSET nebo XLOOKUP místo INDIRECT. Dokonce i Microsoft doporučuje snížit počet použitých volatilních funkcí, aby se urychlilo fungování tabulky.

Pevně zakódovaná čísla: Skryté miny

Formule v Excelu by měly být dynamické, ale všichni jsme byli v pokušení vložit daňovou sazbu přímo do buňky, když jsme spěchali. Tomu se říká tvrdé kódování a je to ekvivalent uložení miny na vašem dvorku ve světě tabulek.

Nebezpečí není v matematice - je to ve viditelnosti. Za šest měsíců, až se sazba změní, budete muset aktualizovat všechny své vzorce, a je snadné nějaký omylem přehlédnout. Problém je v tom, že nemusíte nutně vědět, že jste nějaký přehlédli, takže se můžete spolehnout na nepřesná data.

Každá proměnná by měla být umístěna ve vlastní označené buňce a vzorce by na ni měly odkazovat. Pokud pak potřebujete proměnnou změnit, stačí to udělat jen na jednom místě.

Ještě lepší je, že můžete buňce přiřadit název, aby byly vaše vzorce ještě snazší na čtení—stačí ji vybrat a do pole názvu v levém horním rohu zadat popisný název pro vytvoření pojmenovaného rozsahu. Poté tento název použijte ve vzorci.

Kromě těchto neviditelných zabijáků existuje několik dalších běžných chyb v Excelu — jako je slučování buněk, nepoužívání Excel tabulek a používání nesprávných typů odkazů — které mohou ohrozit integritu vašich dat. Pokud tyto chyby opravíte nyní, zajistíte, že vaše tabulky budou stejně spolehlivé, jako jsou funkční.