Skryť nulové hodnoty v programe Excel - Nechajte bunky prázdne, ak je hodnota 0

Pozrite si video - Ako skryť nulové hodnoty v programe Excel + Ako odstrániť nulové hodnoty

V prípade, že uprednostňujete čítanie pred sledovaním videa, nižšie nájdete kompletný písomný návod.

Niekedy v Exceli možno budete chcieť skryť nulové hodnoty vo vašom súbore údajov a ukázať tieto bunky ako prázdne miesta.

Predpokladajme, že máte množinu údajov, ako je uvedené nižšie, a chcete skryť hodnotu 0 vo všetkých týchto bunkách (alebo ju chcete nahradiť niečím, ako je napríklad pomlčka alebo text „Nie je k dispozícii“).

Aj keď to môžete urobiť ručne s malou množinou údajov, pri práci s veľkými množinami údajov budete potrebovať lepšie spôsoby.

V tomto tutoriále vám ukážem niekoľko spôsobov, ako skryť nulové hodnoty v programe Excel, a jednu metódu na výber a odstránenie všetkých nulových hodnôt z množiny údajov.

Dôležitá poznámka: Keď 0 skryjete 0 v bunke pomocou metód uvedených v tomto návode, 0 iba skryje a neodstráni. Bunky môžu vyzerať prázdne, ale stále obsahujú 0. A v prípade, že použijete tieto bunky (ktoré majú skryté nuly) v ľubovoľnom vzorci, budú použité vo vzorci.

Začnime a ponorme sa do každej z týchto metód!

Automaticky skryť nulovú hodnotu v bunkách

Excel má vstavanú funkciu, ktorá vám umožňuje automaticky skryť všetky nulové hodnoty pre celý pracovný hárok.

Stačí, ak v možnostiach Excelu zrušíte začiarknutie políčka, a zmena sa prejaví na celom pracovnom hárku.

Predpokladajme, že máte množinu údajov o predaji, ako je uvedené nižšie, a chcete skryť všetky nulové hodnoty a namiesto toho zobraziť prázdne bunky.

Nasledujú kroky na skrytie núl zo všetkých buniek v zošite v programe Excel:

1. Otvorte zošit, do ktorého chcete skryť všetky nuly
2. Kliknite na kartu Súbor
3. Kliknite na Možnosti
4. V otvorenom dialógovom okne Možnosti programu Excel kliknite na možnosť „Rozšírené“ na ľavom paneli
5. Posuňte sa nadol na sekciu s názvom „Možnosti zobrazenia pre tento hárok“ a vyberte pracovný hárok, v ktorom chcete skryť nuly.
6. Zrušte začiarknutie možnosti „Zobraziť nulu v bunkách, ktoré majú nulovú hodnotu“
7. Kliknite na Ok

Vyššie uvedené kroky by okamžite skryli nuly vo všetkých bunkách vybratého pracovného hárka.

Táto zmena sa týka aj buniek, kde nula je výsledkom vzorca.

Nezabudnite, že táto metóda iba skryje hodnotu 0 v bunkách a neodstráni ich. 0 je stále tam, je len skrytá.

Toto je najlepšia a najrýchlejšia metóda na skrytie nulových hodnôt v programe Excel. To je však užitočné iba vtedy, ak chcete skryť nulové hodnoty v celom pracovnom hárku. V prípade, že chcete iba skryť nulové hodnoty v konkrétnom rozsahu buniek, je lepšie použiť ďalšie metódy uvedené ďalej v tomto návode.

Skryť nulovú hodnotu v bunkách pomocou podmieneného formátovania

Aj keď je vyššie uvedená metóda najpohodlnejšia, neumožňuje vám skryť nuly iba v určitom rozsahu (skôr skrýva nulové hodnoty v celom pracovnom hárku).

V prípade, že chcete skryť nuly iba pre konkrétnu množinu údajov (alebo viacero množín údajov), môžete použiť podmienené formátovanie.

Pri podmienenom formátovaní môžete použiť konkrétny formát na základe hodnoty v bunkách. Ak je teda hodnota v niektorých bunkách 0, môžete podmienené formátovanie jednoducho naprogramovať tak, aby sa skrylo (alebo ho dokonca chcete zvýrazniť).

Predpokladajme, že máte množinu údajov, ako je uvedené nižšie, a v bunkách chcete skryť nuly.

Nasledujú kroky na skrytie núl v programe Excel pomocou podmieneného formátovania:

1. Vyberte množinu údajov
2. Kliknite na kartu „Domov“
3. V skupine „Štýly“ kliknite na „Podmienené formátovanie“.
4. Umiestnite kurzor na možnosti „Zvýrazniť pravidlá buniek“ a v zobrazených možnostiach kliknite na možnosť „Rovná sa“.
5. V dialógovom okne „Rovná sa“ zadajte do ľavého poľa „0“.
6. Vyberte rozbaľovaciu ponuku Formát a kliknite na položku „Vlastný formát“.
7. V dialógovom okne „Formát buniek“ vyberte kartu „Písmo“.
8. V rozbaľovacej ponuke Farba vyberte bielu farbu.
9. Kliknite na tlačidlo OK.
10. Kliknite na tlačidlo OK.

Vyššie uvedené kroky zmenia farbu písma buniek, ktoré majú nuly, na biele, vďaka čomu budú tieto bunky vyzerať prázdne.

Toto riešenie funguje iba vtedy, ak máte v hárku biele pozadie. V prípade, že je v bunkách iná farba pozadia, biela farba môže stále udržiavať nuly viditeľné.

V prípade, že chcete skutočne skryť nuly v bunkách, kde bunky vyzerajú prázdne bez ohľadu na to, akú farbu pozadia majú bunky, môžete použiť nižšie uvedené kroky.

1. Vyberte množinu údajov
2. Kliknite na kartu „Domov“
3. V skupine „Štýly“ kliknite na „Podmienené formátovanie“.
4. Umiestnite kurzor na možnosti „Zvýrazniť pravidlá buniek“ a v zobrazených možnostiach kliknite na možnosť „Rovná sa“.
5. V dialógovom okne „Rovná sa“ zadajte 0 do ľavého poľa.
6. Vyberte rozbaľovaciu ponuku Formát a kliknite na položku Vlastný formát.
7. V dialógovom okne „Formát buniek“ vyberte kartu „Číslo“ a kliknite na možnosť „Vlastné“ na ľavom paneli.
8. Vo voľbe Vlastný formát zadajte do poľa Typ nasledujúce: ;;; (tri bodkočiarky bez medzery)
9. Kliknite na tlačidlo OK.
10. Kliknite na tlačidlo OK.

Vyššie uvedené kroky menia vlastné formátovanie buniek, ktoré majú hodnotu 0.

Keď ako formát použijete tri bodkočiarky, skryje všetko v bunke (či už číselné alebo textové). A pretože tento formát je aplikovaný len na tie bunky, ktoré majú v ňom hodnotu 0, skryje iba nuly a nie zvyšok množiny dát.

Túto metódu môžete použiť aj na zvýraznenie týchto buniek inou farbou (napríklad červenou alebo žltou). Ak to chcete urobiť, stačí pridať ďalší krok (po kroku 8), kde musíte v dialógovom okne „Formátovať bunky“ vybrať kartu Vyplniť a potom vybrať farbu, v ktorej chcete bunky zvýrazniť.

Obe vyššie uvedené metódy fungujú aj vtedy, keď 0 je výsledkom vzorca v bunke.

Poznámka: Obe metódy zahrnuté v tejto časti iba skrývajú nulové hodnoty. Tieto nuly neodstráni. To znamená, že v prípade, že použijete tieto bunky vo výpočtoch, budú použité aj všetky nuly.

Skryť nulovú hodnotu pomocou vlastného formátovania

Ďalším spôsobom, ako skryť nulové hodnoty z množiny údajov, je vytvoriť vlastný formát, ktorý skryje hodnotu v bunkách s 0 s, pričom akákoľvek iná hodnota sa zobrazí podľa očakávania.

Nasledujú kroky na použitie vlastného formátu na skrytie núl v bunkách:

1. Vyberte celú množinu údajov, pre ktorú chcete použiť tento formát. Ak to chcete pre celý pracovný hárok, vyberte všetky bunky
2. Keď sú vybraté bunky, kliknite na kartu Domov
3. V skupine Bunky kliknite na možnosť Formátovať
4. V rozbaľovacej ponuke kliknite na položku Formátovať bunky. Otvorí sa dialógové okno Formát buniek
5. V dialógovom okne Formát buniek vyberte kartu Číslo
6. V možnosti na ľavom paneli kliknite na možnosť Vlastné.
7. V možnostiach vpravo zadajte do poľa Typ nasledujúci formát: 0;-0;;@
8. Kliknite na tlačidlo OK

Vyššie uvedené kroky by použili vlastný formát na vybraté bunky, kde iba tie bunky, ktoré majú hodnotu 0, sú naformátované tak, aby skryli hodnotu, zatiaľ čo ostatné bunky zobrazujú údaje podľa očakávania.

Ako tento vlastný formát funguje?

V programe Excel existujú štyri typy formátov údajov, ktoré zadáte:

1. Pozitívne čísla
2. Záporné čísla
3. Nuly
4. Text

A pre každý z týchto typov údajov môžete určiť formát.

Vo väčšine prípadov existuje predvolený formát - všeobecný, ktorý udržuje všetky tieto typy údajov viditeľné. Máte však flexibilitu pri vytváraní konkrétneho formátu pre každý z týchto typov údajov.

Nasleduje štruktúra vlastného formátu, ktorú musíte dodržať v prípade, že na každý z týchto typov údajov používate vlastný formát:

;;;

Vo vyššie uvedenej metóde som špecifikoval formát:

0;-0;;@

Vyššie uvedený formát robí nasledujúce:

1. Kladné čísla sa zobrazujú tak, ako sú
2. Záporné čísla sú zobrazené so záporným znamienkom
3. Nuly sú skryté (pretože nie je zadaný žiadny formát)
4. Text je zobrazený tak, ako je

Pro tip:: Ak chcete, aby sa záporné čísla v súbore údajov zobrazovali ako červené, použite nasledujúci vlastný formát: 0; [červená] 0; 0;@

Nahraďte nuly pomlčkou alebo nejakým konkrétnym textom

V niektorých prípadoch možno budete chcieť nuly nielen skryť v bunkách, ale nahradiť ich niečím dôležitejším.

Môžete napríklad chcieť odstrániť nuly a nahradiť ich pomlčkou alebo textom, ako napríklad „Nie je k dispozícii“ alebo „Údaje ešte len prídu“

To je opäť možné pomocou vlastného formátovania čísel, kde môžete určiť, čo má bunka zobrazovať namiesto nuly.

Nasledujú kroky na konverziu 0 na pomlčku v programe Excel:

1. Vyberte celú množinu údajov, pre ktorú chcete použiť tento formát. Ak to chcete pre celý pracovný hárok, vyberte všetky bunky
2. Keď sú vybraté bunky, kliknite na kartu Domov
3. V skupine Bunky kliknite na možnosť Formátovať
4. V rozbaľovacej ponuke kliknite na položku Formátovať bunky. Otvorí sa dialógové okno Formát buniek
5. V dialógovom okne Formát buniek vyberte kartu Číslo
6. V možnosti na ľavom paneli kliknite na možnosť Vlastné.
7. V možnostiach vpravo zadajte do poľa Typ nasledujúci formát: 0;-0;-;@
8. Kliknite na tlačidlo OK

Vyššie uvedené kroky ponechajú všetky ostatné bunky tak, ako sú, a bunky s hodnotou 0 zobrazia pomlčkou (-).

Poznámka: V tomto prípade som sa rozhodol nahradiť nulu pomlčkou, ale môžete použiť aj ľubovoľný text. Ak napríklad chcete zobraziť NA v bunkách, môžete použiť nasledujúci formát: 0; -0; "NA";@Pro tip: Môžete tiež zadať niekoľko farieb textu vo vlastnom formátovaní čísel. Ak napríklad chcete, aby bunky s nulami zobrazovali text NA v červenej farbe, môžete použiť nasledujúci formát: 0; -0; [červená] „NA“;@

Skryť nulové hodnoty v kontingenčných tabuľkách

Môžu existovať dva scenáre, v ktorých kontingenčná tabuľka zobrazuje hodnotu 0:

1. Bunky zdrojových údajov, ktoré sú zhrnuté v kontingenčnej tabuľke, majú 0 hodnôt
2. Bunky zdrojových údajov, ktoré sú zhrnuté v kontingenčnej tabuľke, sú prázdne a kontingenčná tabuľka bola naformátovaná tak, aby zobrazovala prázdne bunky ako nulu

Pozrime sa, ako skryť nuly v kontingenčnej tabuľke v každom scenári:

Keď bunky zdrojových údajov majú 0 s

Nasleduje ukážkový súbor údajov, ktorý som použil na vytvorenie kontingenčnej tabuľky. Čísla množstva/výnosov/zisku pre všetky záznamy pre „západ“ sú 0.

Kontingenčná tabuľka vytvorená pomocou tejto množiny údajov vyzerá nasledovne:

Pretože v množine údajov pre všetky záznamy pre región Západ boli 0 s, v kontingenčnej tabuľke pre západ tu vidíte 0.

Ak chcete v tejto kontingenčnej tabuľke skryť nulu, môžete na to použiť podmienené formátovanie.

Podmienené formátovanie v kontingenčnej tabuľke v programe Excel však funguje trochu inak ako bežné údaje.

Tu sú kroky na použitie podmieneného formátovania v kontingenčnej tabuľke na skrytie núl:

1. Vyberte ľubovoľnú bunku v stĺpci, ktorá má v súhrne kontingenčnej tabuľky 0 s (Nevyberajte všetky bunky, ale iba jednu bunku)
2. Kliknite na kartu Domov
3. V skupine „Štýly“ kliknite na „Podmienené formátovanie“.
4. Umiestnite kurzor na možnosti „Zvýrazniť pravidlá buniek“ a v zobrazených možnostiach kliknite na možnosť „Rovná sa“.
5. V dialógovom okne „Rovná sa“ zadajte 0 do ľavého poľa.
6. Vyberte rozbaľovaciu ponuku Formát a kliknite na položku Vlastný formát.
7. V dialógovom okne „Formátovať bunky“ vyberte kartu Číslo a kliknite na možnosť „Vlastné“ na ľavom paneli.
8. Vo voľbe Vlastný formát zadajte do poľa Typ nasledujúce: ;;; (tri bodkočiarky bez medzery)
9. Kliknite na tlačidlo OK. Vpravo od vybratej bunky sa zobrazí malá ikona Možnosti formátovania.
10. Kliknite na túto ikonu „Možnosti formátovania“
11. V dostupných možnostiach vyberte - Všetky bunky zobrazujúce hodnoty „Suma výnosov“ pre „Región“.

Vyššie uvedené kroky skryjú nulu v kontingenčnej tabuľke takým spôsobom, že ak zmeníte štruktúru kontingenčnej tabuľky (pridaním ďalších hlavičiek/stĺpcov do nej, nuly budú naďalej skryté).

Ak sa chcete dozvedieť viac o tom, ako to funguje, tu je podrobný návod na správnu cestu použiť podmienené formátovanie na kontingenčné tabuľky.

Keď bunky zdrojových údajov majú prázdne bunky

Nasleduje príklad, kde mám množinu údajov, ktorá obsahuje prázdne bunky pre všetky záznamy pre West.

Ak pomocou týchto údajov vytvorím kontingenčnú tabuľku, dostanem výsledok, ako je uvedené nižšie.

V predvolenom nastavení vám kontingenčná tabuľka zobrazuje prázdne bunky, keď sú všetky bunky v zdrojových údajoch prázdne. Ale v prípade, že v tomto prípade stále vidíte 0, postupujte podľa nižšie uvedených krokov, aby ste skryli nulu a zobrazovali prázdne bunky:

1. Kliknite pravým tlačidlom myši na ľubovoľnú bunku v kontingenčnej tabuľke
2. Kliknite na „Možnosti kontingenčnej tabuľky“
3. V dialógovom okne „Možnosti kontingenčnej tabuľky“ kliknite na kartu „Rozloženie a formát“
4. V možnostiach formátu zaškrtnite možnosti a „Pre prázdne bunky sa zobrazí:“ a nechajte ho prázdne.
5. Kliknite na tlačidlo OK.

Vyššie uvedené kroky skryjú nuly v kontingenčnej tabuľke a namiesto toho zobrazia prázdnu bunku.

V prípade, že chcete, aby kontingenčná tabuľka namiesto 0 zobrazovala niečo, môžete to určiť v kroku 4. Ak napríklad chcete zobraziť text - „Údaje nie sú k dispozícii“, namiesto 0 zadajte tento text do pole (ako je uvedené nižšie).

Nájdite a odstráňte nuly v programe Excel

Vo všetkých vyššie uvedených metódach som vám ukázal spôsoby, ako skryť hodnotu 0 v bunke, ale hodnota v bunke stále zostáva.

V prípade, že chcete z množiny údajov odstrániť nulové hodnoty (čo vám ponechá prázdne bunky), použite nasledujúci postup:

1. Vyberte množinu údajov (alebo celý pracovný hárok)
2. Kliknite na kartu Domov
3. V skupine „Úpravy“ kliknite na „Nájsť a vybrať“
4. V možnostiach, ktoré sú zobrazené v rozbaľovacom zozname, kliknite na „Nahradiť“
5. V dialógovom okne „Nájsť a nahradiť“ vykonajte nasledujúce zmeny:
   • Do poľa „Čo nájsť“ zadajte 0
   • Do poľa „Nahradiť“ zadajte nič (nechajte prázdne)
   • Kliknite na tlačidlo Možnosti a začiarknite možnosť „Zodpovedať celému obsahu bunky“
6. Kliknite na položku Nahradiť všetko

Vyššie uvedené kroky by okamžite nahradili všetky bunky, ktoré majú nulovú hodnotu, prázdne.

V prípade, že nechcete hneď odstrániť nuly a najskôr vybrať bunky, postupujte nasledovne:

1. Vyberte množinu údajov (alebo celý pracovný hárok)
2. Kliknite na kartu Domov
3. V skupine „Úpravy“ kliknite na „Nájsť a vybrať“
4. V možnostiach, ktoré sú zobrazené v rozbaľovacom zozname, kliknite na „Nahradiť“
5. V dialógovom okne „Nájsť a nahradiť“ vykonajte nasledujúce zmeny:
   • Do poľa „Čo nájsť“ zadajte 0
   • Do poľa „Nahradiť“ zadajte nič (nechajte prázdne)
   • Kliknite na tlačidlo Možnosti a začiarknite možnosť „Zodpovedať celému obsahu bunky“
6. Kliknite na tlačidlo „Nájsť všetko“. Tým získate zoznam všetkých buniek, v ktorých je 0
7. Stlačte kombináciu klávesov Control-A (podržte ovládací kláves a stlačte kláves A). Tým sa vyberú všetky bunky, v ktorých je 0.

Teraz, keď máte vybraté všetky bunky s 0, môžete ich odstrániť, nahradiť inými alebo zmeniť farbu a zvýrazniť ich.

Skrytie núl vs. Odstránenie núl

Pri ošetrovaní buniek s 0 s v nich musíte vedieť, aký je rozdiel medzi ich skrytím a odstránením.

Keď skryjete nulovú hodnotu v bunke, nie je pre vás viditeľná, ale stále zostane v bunke. To znamená, že ak máte množinu údajov a použijete ju na výpočet, bude pri výpočte použitá aj táto hodnota 0.

Naopak, ak máte prázdnu bunku, jej použitie vo vzorcoch môže znamenať, že Excel tieto bunky automaticky ignoruje (podľa toho, ktorý vzorec používate).

Nižšie je napríklad uvedené to, čo dostanem, keď použijem priemerný priemer na získanie priemeru pre dva stĺpce (výsledok je v riadku 14). Stĺpec A obsahuje bunky, v ktorých bola hodnota 0 skrytá (ale v bunke stále je) a stĺpec B obsahuje bunky, z ktorých bola hodnota 0 odstránená.

Rozdiel vo výsledku môžete vidieť napriek tomu, že množina údajov pre oba súbory údajov vyzerá rovnako.

Stáva sa to tak, že priemerný vzorec ignoruje prázdne bunky v stĺpci B, ale používa bunky v stĺpci A (pretože bunky v stĺpci A majú stále hodnotu 0).