Skrýt nulové hodnoty v aplikaci Excel - Nastavit buňky jako prázdné, pokud je hodnota 0

Podívejte se na video - Jak skrýt nulové hodnoty v aplikaci Excel + Jak odstranit nulové hodnoty

Pokud dáváte přednost čtení před sledováním videa, níže je kompletní písemný návod.

Někdy v Excelu možná budete chtít skrýt nulové hodnoty ve vaší datové sadě a ukázat tyto buňky jako prázdná místa.

Předpokládejme, že máte datovou sadu, jak je uvedeno níže, a chcete ve všech těchto buňkách skrýt hodnotu 0 (nebo ji chcete nahradit něčím, jako je pomlčka nebo text „Není k dispozici“).

I když to můžete udělat ručně s malou datovou sadou, budete při práci s velkými datovými sadami potřebovat lepší způsoby.

V tomto tutoriálu vám ukážu několik způsobů, jak skrýt nulové hodnoty v aplikaci Excel, a jednu metodu pro výběr a odebrání všech nulových hodnot z datové sady.

Důležitá poznámka: Když skryjete 0 v buňce pomocí metod zobrazených v tomto tutoriálu, pouze 0 skryje a neodstraní. Buňky mohou vypadat prázdné, ale tyto buňky stále obsahují 0s. A v případě, že použijete tyto buňky (které mají skryté nuly) v jakémkoli vzorci, budou použity ve vzorci.

Začněme a ponořme se do každé z těchto metod!

Automaticky skrýt nulovou hodnotu v buňkách

Excel má vestavěnou funkci, která vám umožňuje automaticky skrýt všechny nulové hodnoty pro celý list.

Stačí v možnostech aplikace Excel zrušit zaškrtnutí políčka a změna se projeví na celém listu.

Předpokládejme, že máte sadu údajů o prodeji, jak je uvedeno níže, a chcete skrýt všechny nulové hodnoty a místo toho zobrazit prázdné buňky.

Níže jsou uvedeny kroky ke skrytí nul ze všech buněk v sešitu v aplikaci Excel:

1. Otevřete sešit, do kterého chcete skrýt všechny nuly
2. Klikněte na kartu Soubor
3. Klikněte na Možnosti
4. V dialogovém okně Možnosti aplikace Excel, které se otevře, klikněte v levém podokně na možnost „Upřesnit“
5. Přejděte dolů do sekce s názvem „Možnost zobrazení pro tento list“ a vyberte list, do kterého chcete skrýt nuly.
6. Zrušte zaškrtnutí možnosti „Zobrazit nulu v buňkách, které mají nulovou hodnotu“
7. Klikněte na Ok

Výše uvedené kroky by okamžitě skryly nuly ve všech buňkách vybraného listu.

Tato změna se použije také na buňky, kde nula je výsledkem vzorce.

Pamatujte, že tato metoda pouze skryje hodnotu 0 v buňkách a neodstraní je. 0 je stále tam, je jen skrytý.

Toto je nejlepší a nejrychlejší metoda, jak v aplikaci Excel skrýt nulové hodnoty. To je ale užitečné pouze v případě, že chcete v celém listu skrýt nulové hodnoty. V případě, že chcete pouze skrýt nulové hodnoty v určitém rozsahu buněk, je lepší použít další metody popsané dále v tomto tutoriálu.

Skrýt nulovou hodnotu v buňkách pomocí podmíněného formátování

Přestože je výše uvedená metoda nejpohodlnější, neumožňuje vám skrýt nuly pouze v určitém rozsahu (spíše skrývá nulové hodnoty v celém listu).

V případě, že chcete skrýt pouze nuly pro konkrétní datovou sadu (nebo více datových sad), můžete použít podmíněné formátování.

S podmíněným formátováním můžete použít konkrétní formát na základě hodnoty v buňkách. Pokud je tedy hodnota v některých buňkách 0, můžete jednoduše naprogramovat podmíněné formátování tak, aby bylo skryto (nebo dokonce zvýrazněno, pokud chcete).

Předpokládejme, že máte datovou sadu, jak je uvedeno níže, a chcete v buňkách skrýt nuly.

Níže jsou uvedeny kroky ke skrytí nul v aplikaci Excel pomocí podmíněného formátování:

1. Vyberte datovou sadu
2. Klikněte na kartu „Domů“
3. Ve skupině „Styly“ klikněte na „Podmíněné formátování“.
4. Umístěte kurzor na možnosti „Zvýraznit pravidla buněk“ a v zobrazených možnostech klikněte na možnost „Rovná se“.
5. V dialogovém okně „Rovno“ zadejte do levého pole „0“.
6. Vyberte rozevírací nabídku Formát a klikněte na „Vlastní formát“.
7. V dialogovém okně „Formát buněk“ vyberte kartu „Písmo“.
8. V rozevíracím seznamu Barva vyberte bílou barvu.
9. Klikněte na OK.
10. Klikněte na OK.

Výše uvedené kroky mění barvu písma buněk s nulami na bílou, takže tyto buňky vypadají prázdné.

Toto řešení funguje pouze v případě, že máte v listu bílé pozadí. V případě, že je v buňkách jiná barva pozadí, bílá barva může stále udržovat viditelné nuly.

V případě, že chcete skutečně skrýt nuly v buňkách, kde buňky vypadají prázdné bez ohledu na to, jakou barvu pozadí mají buňky, můžete použít níže uvedené kroky.

1. Vyberte datovou sadu
2. Klikněte na kartu „Domů“
3. Ve skupině „Styly“ klikněte na „Podmíněné formátování“.
4. Umístěte kurzor na možnosti „Zvýraznit pravidla buněk“ a v možnostech, které se zobrazí, klikněte na možnost „Rovná se“.
5. V dialogovém okně „Rovno“ zadejte 0 do levého pole.
6. Vyberte rozevírací nabídku Formát a klikněte na Vlastní formát.
7. V dialogovém okně „Formát buněk“ vyberte kartu „Číslo“ a v levém podokně klikněte na možnost „Vlastní“.
8. V možnosti Vlastní formát zadejte do pole Typ následující: ;;; (tři středníky bez mezery)
9. Klikněte na OK.
10. Klikněte na OK.

Výše uvedené kroky mění vlastní formátování buněk, které mají hodnotu 0.

Když jako formát použijete tři středníky, skryje vše v buňce (ať už číselné nebo textové). A protože je tento formát aplikován pouze na ty buňky, které mají v sobě hodnotu 0, skryje pouze nuly a ne zbytek datové sady.

Tuto metodu můžete také použít ke zvýraznění těchto buněk jinou barvou (například červenou nebo žlutou). Chcete -li to provést, stačí přidat další krok (po kroku 8), kde musíte v dialogovém okně „Formátovat buňky“ vybrat kartu Vyplnit a poté vybrat barvu, ve které chcete buňky zvýraznit.

Obě výše uvedené metody také fungují, když 0 je výsledkem vzorce v buňce.

Poznámka: Obě metody popsané v této části skrývají pouze nulové hodnoty. Neodstraní tyto nuly. To znamená, že v případě, že použijete tyto buňky ve výpočtech, budou použity také všechny nuly.

Skrýt nulovou hodnotu pomocí vlastního formátování

Dalším způsobem, jak skrýt nulové hodnoty z datové sady, je vytvoření vlastního formátu, který skryje hodnotu v buňkách, které mají 0 s, zatímco jakákoli jiná hodnota se zobrazí podle očekávání.

Níže jsou uvedeny kroky k použití vlastního formátu ke skrytí nul v buňkách:

1. Vyberte celou datovou sadu, pro kterou chcete použít tento formát. Pokud to chcete pro celý list, vyberte všechny buňky
2. S vybranými buňkami klikněte na kartu Domů
3. Ve skupině Buňky klikněte na možnost Formát
4. V rozevírací nabídce klikněte na Formát buněk. Otevře se dialogové okno Formát buněk
5. V dialogovém okně Formát buněk vyberte kartu Číslo
6. V možnosti v levém podokně klikněte na možnost Vlastní.
7. V možnostech vpravo zadejte do pole Typ následující formát: 0;-0;;@
8. Klikněte na OK

Výše uvedené kroky použijí vlastní formát na vybrané buňky, kde jsou pouze ty buňky, které mají hodnotu 0, formátovány tak, aby skryly hodnotu, zatímco ostatní buňky zobrazují data podle očekávání.

Jak tento vlastní formát funguje?

V aplikaci Excel existují čtyři typy datových formátů, které zadáte:

1. Pozitivní čísla
2. Záporná čísla
3. Nuly
4. Text

A pro každý z těchto datových typů můžete určit formát.

Ve většině případů existuje výchozí formát - Obecné, který udržuje všechny tyto datové typy viditelné. Ale máte flexibilitu při vytváření konkrétního formátu pro každý z těchto datových typů.

Níže je uvedena vlastní struktura formátu, kterou musíte dodržovat v případě, že pro každý z těchto datových typů používáte vlastní formát:

;;;

Ve výše uvedené metodě jsem určil formát:

0;-0;;@

Výše uvedený formát provádí následující:

1. Kladná čísla se zobrazují tak, jak jsou
2. Záporná čísla jsou zobrazena se záporným znaménkem
3. Nuly jsou skryté (protože není zadán žádný formát)
4. Text je zobrazen tak, jak je

Profesionální tip: Pokud chcete, aby se záporná čísla ve vaší datové sadě zobrazovala jako červená, použijte následující vlastní formát: 0; [červená] 0; 0;@

Vyměňte nuly za pomlčku nebo nějaký konkrétní text

V některých případech možná budete chtít nuly nejen skrýt v buňkách, ale nahradit je něčím důležitějším.

Můžete například chtít odstranit nuly a nahradit je pomlčkou nebo textem jako „není k dispozici“ nebo „data teprve přijdou“

To je opět možné pomocí vlastního formátování čísel, kde můžete určit, co má buňka zobrazovat místo nuly.

Níže jsou uvedeny kroky pro převod 0 na pomlčku v aplikaci Excel:

1. Vyberte celou datovou sadu, pro kterou chcete použít tento formát. Pokud to chcete pro celý list, vyberte všechny buňky
2. S vybranými buňkami klikněte na kartu Domů
3. Ve skupině Buňky klikněte na možnost Formát
4. V rozevírací nabídce klikněte na Formát buněk. Otevře se dialogové okno Formát buněk
5. V dialogovém okně Formát buněk vyberte kartu Číslo
6. V možnosti v levém podokně klikněte na možnost Vlastní.
7. V možnostech vpravo zadejte do pole Typ následující formát: 0;-0;-;@
8. Klikněte na OK

Výše uvedené kroky by ponechaly všechny ostatní buňky tak, jak jsou, a změnily buňky s hodnotou 0 tak, aby zobrazovaly pomlčku (-).

Poznámka: V tomto příkladu jsem se rozhodl nahradit nulu pomlčkou, ale můžete použít i libovolný text. Pokud například chcete zobrazit NA v buňkách, můžete použít následující formát: 0; -0; "NA";@Profesionální tip: Můžete také určit několik barev textu ve vlastním formátování čísel. Pokud například chcete, aby buňky s nulami zobrazovaly text NA v červené barvě, můžete použít následující formát: 0; -0; [červená] „NA“;@

Skrýt nulové hodnoty v kontingenčních tabulkách

Mohou existovat dva scénáře, kde kontingenční tabulka zobrazuje hodnotu jako 0:

1. Buňky zdrojových dat, které jsou shrnuty v kontingenční tabulce, mají 0 hodnot
2. Buňky zdrojových dat, které jsou shrnuty v kontingenční tabulce, jsou prázdné a kontingenční tabulka byla naformátována tak, aby zobrazovala prázdné buňky jako nulu

Podívejme se, jak skrýt nuly v kontingenční tabulce v každém scénáři:

Když mají buňky zdrojových dat 0 s

Níže je ukázková datová sada, kterou jsem použil k vytvoření kontingenční tabulky. Čísla množství/výnosů/zisku pro všechny záznamy pro „Západ“ jsou 0.

Kontingenční tabulka vytvořená pomocí této sady dat vypadá takto:

Vzhledem k tomu, že v datové sadě byly pro všechny záznamy pro oblast Západ 0, uvidíte zde 0 v kontingenční tabulce pro západ.

Pokud chcete v této kontingenční tabulce skrýt nulu, můžete k tomu použít podmíněné formátování.

Podmíněné formátování v kontingenční tabulce v Excelu však funguje trochu jinak než běžná data.

Zde jsou kroky k použití podmíněného formátování na kontingenční tabulku ke skrytí nul:

1. Vyberte libovolnou buňku ve sloupci, která má v souhrnu kontingenční tabulky 0 s (Nevybírejte všechny buňky, ale pouze jednu buňku)
2. Klikněte na kartu Domů
3. Ve skupině „Styly“ klikněte na „Podmíněné formátování“.
4. Umístěte kurzor na možnosti „Zvýraznit pravidla buněk“ a v možnostech, které se zobrazí, klikněte na možnost „Rovná se“.
5. V dialogovém okně „Rovno“ zadejte 0 do levého pole.
6. Vyberte rozevírací nabídku Formát a klikněte na Vlastní formát.
7. V dialogovém okně „Formát buněk“ vyberte kartu Číslo a v levém podokně klikněte na možnost „Vlastní“.
8. V možnosti Vlastní formát zadejte do pole Typ následující: ;;; (tři středníky bez mezery)
9. Klikněte na OK. Vpravo od vybrané buňky uvidíte malou ikonu Možnosti formátování.
10. Klikněte na tuto ikonu „Možnosti formátování“
11. V dostupných možnostech zvolte - Všechny buňky zobrazující hodnoty „Součet výnosů“ pro „Region“.

Výše uvedené kroky skrývají nulu v kontingenční tabulce takovým způsobem, že pokud změníte strukturu kontingenční tabulky (přidáním dalších záhlaví řádků/sloupců, nuly budou nadále skryty).

Pokud se chcete dozvědět více o tom, jak to funguje, zde je podrobný návod na správnou cestu použít podmíněné formátování na kontingenční tabulky.

Když mají buňky zdrojových dat prázdné buňky

Níže je příklad, kde mám datovou sadu, která má prázdné buňky pro všechny záznamy pro West.

Pokud pomocí těchto dat vytvořím kontingenční tabulku, získám výsledek, jak je uvedeno níže.

Ve výchozím nastavení vám kontingenční tabulka zobrazuje prázdné buňky, když jsou všechny buňky ve zdrojových datech prázdné. Pokud však v tomto případě stále vidíte 0, skryjte nulu a zobrazte prázdné buňky podle následujících pokynů:

1. Klepněte pravým tlačítkem na libovolnou buňku v kontingenční tabulce
2. Klikněte na „Možnosti kontingenční tabulky“
3. V dialogovém okně „Možnosti kontingenční tabulky“ klikněte na kartu „Rozložení a formát“
4. V možnostech Formát zaškrtněte možnosti a „U prázdných buněk zobrazit:“ a ponechte prázdné.
5. Klikněte na OK.

Výše uvedené kroky by skryly nuly v kontingenční tabulce a místo toho zobrazily prázdnou buňku.

V případě, že chcete, aby kontingenční tabulka zobrazovala něco místo 0, můžete to zadat v kroku 4. Pokud například chcete zobrazit text - „Data nejsou k dispozici“, místo 0 zadejte tento text do pole (jak je uvedeno níže).

Najděte a odstraňte nuly v aplikaci Excel

Ve všech výše uvedených metodách jsem vám ukázal způsoby, jak skrýt hodnotu 0 v buňce, ale hodnota v buňce stále zůstává.

V případě, že chcete z datové sady odebrat nulové hodnoty (což by vám ponechalo prázdné buňky), použijte následující kroky:

1. Vyberte datovou sadu (nebo celý list)
2. Klikněte na kartu Domů
3. Ve skupině „Úpravy“ klikněte na „Najít a vybrat“
4. V možnostech, které jsou zobrazeny v rozevíracím seznamu, klikněte na „Nahradit“
5. V dialogovém okně „Najít a nahradit“ proveďte následující změny:
   • Do pole „Najít co“ zadejte 0
   • Do pole „Nahradit“ zadejte nic (nechte prázdné)
   • Klikněte na tlačítko Možnosti a zaškrtněte možnost „Shodit celý obsah buňky“
6. Klikněte na Nahradit vše

Výše uvedené kroky by okamžitě nahradily všechny buňky, které měly nulovou hodnotu, mezerou.

V případě, že nechcete nuly hned odstranit a nejprve vybrat buňky, použijte následující postup:

1. Vyberte datovou sadu (nebo celý list)
2. Klikněte na kartu Domů
3. Ve skupině „Úpravy“ klikněte na „Najít a vybrat“
4. V možnostech, které jsou zobrazeny v rozevíracím seznamu, klikněte na „Nahradit“
5. V dialogovém okně „Najít a nahradit“ proveďte následující změny:
   • Do pole „Najít co“ zadejte 0
   • Do pole „Nahradit“ zadejte nic (nechte prázdné)
   • Klikněte na tlačítko Možnosti a zaškrtněte možnost „Shodit celý obsah buňky“
6. Klikněte na tlačítko „Najít vše“. Tím získáte seznam všech buněk, které obsahují 0
7. Stiskněte Control-A (podržte ovládací klávesu a stiskněte A). Tím vyberete všechny buňky, které mají 0.

Nyní, když máte vybrány všechny buňky s 0, můžete je odstranit, nahradit je něčím jiným nebo změnit barvu a zvýraznit je.

Skrytí nul vs. Odstranění nul

Při ošetřování buněk s 0 s v něm musíte znát rozdíl mezi jejich skrytím a odstraněním.

Když do buňky skryjete nulovou hodnotu, není pro vás viditelná, ale přesto v buňce zůstane. To znamená, že pokud máte datovou sadu a použijete ji pro výpočet, bude při výpočtu použita také tato hodnota 0.

Naopak, když máte prázdnou buňku, její použití ve vzorcích může znamenat, že Excel tyto buňky automaticky ignoruje (podle toho, jaký vzorec používáte).

Níže je například to, co dostanu, když použiji průměrný vzorec pro získání průměru pro dva sloupce (výsledek je v řádku 14). Sloupec A obsahuje buňky, kde byla skrytá hodnota 0 (ale stále je v buňce) a sloupec B obsahuje buňky, kde byla odebrána hodnota 0.

Rozdíl ve výsledku můžete vidět navzdory skutečnosti, že datová sada pro obě datové sady vypadá stejně.

K tomu dochází, protože vzorec AVERAGE ignoruje prázdné buňky ve sloupci B, ale používá ty ve sloupci A (protože ty ve sloupci A v sobě stále mají hodnotu 0).