Ukryj wartości zerowe w Excelu - Wypełnij puste komórki, jeśli wartość wynosi 0

Obejrzyj wideo - Jak ukryć wartości zerowe w programie Excel + Jak usunąć wartości zerowe

Jeśli wolisz czytać niż oglądać wideo, poniżej znajduje się kompletny pisemny samouczek.

Czasami w programie Excel możesz chcieć ukryj wartości zerowe w zestawie danych i pokaż te komórki jako puste.

Załóżmy, że masz zestaw danych, jak pokazano poniżej i chcesz ukryć wartość 0 we wszystkich tych komórkach (lub zastąpić ją czymś takim jak myślnik lub tekst „Niedostępne”).

Chociaż możesz to zrobić ręcznie w przypadku małego zestawu danych, podczas pracy z dużymi zestawami danych będziesz potrzebować lepszych sposobów.

W tym samouczku pokażę Ci wiele sposobów ukrywania wartości zerowych w programie Excel oraz jedną metodę wybierania i usuwania wszystkich wartości zerowych z zestawu danych.

Ważna uwaga: Gdy ukryjesz 0 w komórce przy użyciu metod przedstawionych w tym samouczku, ukryje tylko 0 i nie usunie go. Chociaż komórki mogą wyglądać na puste, nadal zawierają zera. A jeśli użyjesz tych komórek (które mają ukryte zera) w dowolnej formule, zostaną one użyte w formule.

Zacznijmy i zanurzmy się w każdą z tych metod!

Automatycznie ukryj wartość zerową w komórkach

Excel ma wbudowaną funkcję, która pozwala automatycznie ukryć wszystkie wartości zerowe dla całego arkusza roboczego.

Wystarczy odznaczyć pole w opcjach programu Excel, a zmiana zostanie zastosowana do całego arkusza roboczego.

Załóżmy, że masz zestaw danych sprzedaży, jak pokazano poniżej, i chcesz ukryć wszystkie wartości zerowe i zamiast tego wyświetlić puste komórki.

Poniżej znajdują się kroki, aby ukryć zera we wszystkich komórkach w skoroszycie w programie Excel:

1. Otwórz skoroszyt, w którym chcesz ukryć wszystkie zera
2. Kliknij kartę Plik
3. Kliknij Opcje
4. W otwartym oknie dialogowym Opcje programu Excel kliknij opcję „Zaawansowane” w lewym okienku
5. Przewiń w dół do sekcji „Opcja wyświetlania dla tego arkusza” i wybierz arkusz, w którym chcesz ukryć zera.
6. Odznacz opcję „Pokaż zero w komórkach, które mają zerową wartość”
7. Kliknij OK

Powyższe kroki natychmiast ukryją zera we wszystkich komórkach w wybranym arkuszu.

Ta zmiana dotyczy również komórek, w których zero jest wynikiem formuły.

Pamiętaj, że ta metoda ukrywa tylko wartość 0 w komórkach i ich nie usuwa. 0 nadal tam jest, jest po prostu ukryte.

Jest to najlepsza i najszybsza metoda ukrywania wartości zerowych w programie Excel. Ale jest to przydatne tylko wtedy, gdy chcesz ukryć wartości zerowe w całym arkuszu. Jeśli chcesz tylko ukryć wartości zerowe w określonym zakresie komórek, lepiej użyć innych metod omówionych w dalszej części tego samouczka.

Ukryj wartość zerową w komórkach za pomocą formatowania warunkowego

Chociaż powyższa metoda jest najwygodniejsza, nie pozwala na ukrycie zer tylko w określonym zakresie (raczej ukrywa wartości zerowe w całym arkuszu).

Jeśli chcesz ukryć zera tylko dla określonego zestawu danych (lub wielu zestawów danych), możesz użyć formatowania warunkowego.

Dzięki formatowaniu warunkowemu możesz zastosować określony format na podstawie wartości w komórkach. Jeśli więc wartość w niektórych komórkach wynosi 0, możesz po prostu zaprogramować formatowanie warunkowe, aby je ukryć (lub nawet podświetlić, jeśli chcesz).

Załóżmy, że masz zestaw danych, jak pokazano poniżej i chcesz ukryć zera w komórkach.

Poniżej znajdują się kroki, aby ukryć zera w programie Excel przy użyciu formatowania warunkowego:

1. Wybierz zbiór danych
2. Kliknij kartę „Strona główna”
3. W grupie „Style” kliknij „Formatowanie warunkowe”.
4. Umieść kursor nad opcjami „Wyróżnij reguły komórek” i w wyświetlonych opcjach kliknij opcję „Równe do”.
5. W oknie dialogowym „Równe do” wpisz „0” w lewym polu.
6. Wybierz menu rozwijane Format i kliknij „Format niestandardowy”.
7. W oknie dialogowym „Formatuj komórki” wybierz kartę „Czcionka”.
8. Z listy rozwijanej Kolor wybierz kolor biały.
9. Kliknij OK.
10. Kliknij OK.

Powyższe kroki zmieniają kolor czcionki komórek z zerami na biały, dzięki czemu te komórki wyglądają na puste.

To obejście działa tylko wtedy, gdy arkusz ma białe tło. W przypadku, gdy w komórkach występuje inny kolor tła, biały kolor może nadal sprawiać, że zera są widoczne.

Jeśli chcesz naprawdę ukryć zera w komórkach, w których komórki wyglądają na puste, bez względu na kolor tła nadany komórkom, możesz wykonać poniższe czynności.

1. Wybierz zbiór danych
2. Kliknij kartę „Strona główna”
3. W grupie „Style” kliknij „Formatowanie warunkowe”.
4. Umieść kursor nad opcjami „Wyróżnij reguły komórek” i w wyświetlonych opcjach kliknij opcję „Równe do”.
5. W oknie dialogowym „Równe do” wpisz 0 w lewym polu.
6. Wybierz menu rozwijane Format i kliknij opcję Format niestandardowy.
7. W oknie dialogowym „Formatuj komórki” wybierz kartę „Liczba” i kliknij opcję „Niestandardowe” w lewym okienku.
8. W opcji Format niestandardowy wprowadź następujące dane w polu Typ: ;;; (trzy średniki bez spacji)
9. Kliknij OK.
10. Kliknij OK.

Powyższe kroki zmieniają niestandardowe formatowanie komórek, które mają wartość 0.

Gdy używasz trzech średników jako formatu, ukrywa wszystko w komórce (czy to numeryczne, czy tekstowe). A ponieważ ten format jest stosowany tylko do tych komórek, które mają w sobie wartość 0, ukrywa tylko zera, a nie resztę zestawu danych.

Możesz również użyć tej metody, aby podświetlić te komórki innym kolorem (na przykład czerwonym lub żółtym). Aby to zrobić, po prostu dodaj kolejny krok (po kroku 8), w którym musisz wybrać kartę Wypełnienie w oknie dialogowym „Formatuj komórki”, a następnie wybierz kolor, w którym chcesz podświetlić komórki.

Obie opisane powyżej metody działają również wtedy, gdy 0 jest wynikiem formuły w komórce.

Uwaga: Obie metody opisane w tej sekcji ukrywają tylko wartości zerowe. Nie usuwa tych zer. Oznacza to, że jeśli użyjesz tych komórek w obliczeniach, wszystkie zera również zostaną użyte.

Ukryj wartość zerową za pomocą niestandardowego formatowania

Innym sposobem ukrycia wartości zerowych w zestawie danych jest utworzenie niestandardowego formatu, który ukrywa wartość w komórkach zawierających zera, podczas gdy każda inna wartość jest wyświetlana zgodnie z oczekiwaniami.

Poniżej znajdują się kroki, aby użyć niestandardowego formatu do ukrywania zer w komórkach:

1. Wybierz cały zestaw danych, dla którego chcesz zastosować ten format. Jeśli chcesz to dla całego arkusza roboczego, zaznacz wszystkie komórki
2. Po zaznaczeniu komórek kliknij kartę Strona główna
3. W grupie Komórki kliknij opcję Formatuj
4. Z rozwijanej opcji kliknij Formatuj komórki. Spowoduje to otwarcie okna dialogowego Formatuj komórki
5. W oknie dialogowym Formatowanie komórek wybierz kartę Liczba
6. W opcji w lewym okienku kliknij opcję Niestandardowe.
7. W opcjach po prawej stronie wprowadź następujący format w polu Typ: 0;-0;;@
8. Kliknij OK

Powyższe kroki zastosowałyby niestandardowy format do wybranych komórek, w którym tylko te komórki, które mają wartość 0, są sformatowane w celu ukrycia wartości, podczas gdy pozostałe komórki wyświetlają dane zgodnie z oczekiwaniami.

Jak działa ten niestandardowy format?

W programie Excel istnieją cztery typy formatów danych, które określasz:

1. Liczby dodatnie
2. Liczby ujemne
3. Zera
4. Tekst

Dla każdego z tych typów danych możesz określić format.

W większości przypadków istnieje format domyślny - Ogólne, który zapewnia widoczność wszystkich tych typów danych. Masz jednak swobodę tworzenia określonego formatu dla każdego z tych typów danych.

Poniżej znajduje się struktura formatu niestandardowego, której należy przestrzegać w przypadku stosowania formatu niestandardowego do każdego z tych typów danych:

;;;

W omówionej powyżej metodzie określiłem format na:

0;-0;;@

Powyższy format wykonuje następujące czynności:

1. Liczby dodatnie są pokazane tak, jak jest
2. Liczby ujemne są wyświetlane ze znakiem ujemnym
3. Zera są ukryte (ponieważ nie określono formatu)
4. Tekst jest wyświetlany tak, jak jest

Pro Porada: jeśli chcesz, aby liczby ujemne w zestawie danych były wyświetlane na czerwono, użyj następującego formatu niestandardowego: 0;[Czerwony]0;0;@

Zamień zera na myślnik lub jakiś konkretny tekst

W niektórych przypadkach możesz chcieć nie tylko ukryć zera w komórkach, ale zastąpić je czymś bardziej znaczącym.

Na przykład możesz chcieć usunąć zera i zastąpić je myślnikiem lub tekstem, takim jak „Niedostępne” lub „Dane jeszcze nadejdą”

Jest to ponownie możliwe dzięki niestandardowemu formatowaniu liczb, w którym można określić, co powinna wyświetlać komórka zamiast zera.

Poniżej znajdują się kroki, aby przekonwertować 0 na myślnik w programie Excel:

1. Wybierz cały zestaw danych, dla którego chcesz zastosować ten format. Jeśli chcesz to dla całego arkusza roboczego, zaznacz wszystkie komórki
2. Po zaznaczeniu komórek kliknij kartę Strona główna
3. W grupie Komórki kliknij opcję Formatuj
4. Z rozwijanej opcji kliknij Formatuj komórki. Spowoduje to otwarcie okna dialogowego Formatuj komórki
5. W oknie dialogowym Formatowanie komórek wybierz kartę Liczba
6. W opcji w lewym okienku kliknij opcję Niestandardowe.
7. W opcjach po prawej stronie wprowadź następujący format w polu Typ: 0;-0;-;@
8. Kliknij OK

Powyższe kroki zachowają wszystkie pozostałe komórki bez zmian i zmienią komórki o wartości 0, aby pokazać myślnik (-).

Notatka: W tym przykładzie wybrałem zastąpienie zera myślnikiem, ale możesz również użyć dowolnego tekstu. Na przykład, jeśli chcesz pokazać NA w komórkach, możesz użyć następującego formatu: 0;-0;”Nie dotyczy”;@Pro Porada: Możesz także określić kilka kolorów tekstu w niestandardowym formatowaniu liczb. Na przykład, jeśli chcesz, aby komórki z zerami wyświetlały tekst NA w kolorze czerwonym, możesz użyć następującego formatu: 0;-0;[Czerwony]”Nie dotyczy”;@

Ukryj wartości zerowe w tabelach przestawnych

Mogą istnieć dwa scenariusze, w których tabela przestawna pokazuje wartość 0:

1. Źródłowe komórki danych podsumowane w tabeli przestawnej mają wartości 0
2. Źródłowe komórki danych podsumowane w tabeli przestawnej są puste, a tabela przestawna została sformatowana w taki sposób, aby puste komórki były wyświetlane jako zero

Zobaczmy, jak ukryć zera w tabeli przestawnej w każdym scenariuszu:

Gdy komórki danych źródłowych mają 0s

Poniżej znajduje się przykładowy zestaw danych, którego użyłem do utworzenia tabeli przestawnej. Zwróć uwagę, że numery ilości/przychodu/zysku dla wszystkich rekordów dla „Zachodu” wynoszą 0.

Tabela przestawna utworzona przy użyciu tego zestawu danych wygląda tak, jak pokazano poniżej:

Ponieważ w zestawie danych dla wszystkich rekordów dla regionu Zachodniego były zera, w tabeli przestawnej dla regionu zachodniego jest wyświetlane zero.

Jeśli chcesz ukryć zero w tej tabeli przestawnej, możesz w tym celu użyć formatowania warunkowego.

Jednak formatowanie warunkowe w tabeli przestawnej w programie Excel działa nieco inaczej niż zwykłe dane.

Oto kroki, aby zastosować formatowanie warunkowe do tabeli przestawnej w celu ukrycia zer:

1. Zaznacz dowolną komórkę w kolumnie, która ma zera w podsumowaniu tabeli przestawnej (nie zaznaczaj wszystkich komórek, ale tylko jedną komórkę)
2. Kliknij kartę Strona główna
3. W grupie „Style” kliknij „Formatowanie warunkowe”.
4. Umieść kursor nad opcjami „Wyróżnij reguły komórek” i w wyświetlonych opcjach kliknij opcję „Równe do”.
5. W oknie dialogowym „Równe do” wpisz 0 w lewym polu.
6. Wybierz menu rozwijane Format i kliknij opcję Format niestandardowy.
7. W oknie dialogowym „Formatuj komórki” wybierz kartę Numer i kliknij opcję „Niestandardowe” w lewym okienku.
8. W opcji Format niestandardowy wprowadź następujące dane w polu Typ: ;;; (trzy średniki bez spacji)
9. Kliknij OK. Zobaczysz małą ikonę Opcje formatowania po prawej stronie wybranej komórki.
10. Kliknij ikonę „Opcje formatowania”
11. W dostępnych opcjach wybierz - Wszystkie komórki pokazujące wartości „Suma przychodów” dla „Region”.

Powyższe kroki ukrywają zero w tabeli przestawnej w taki sposób, że jeśli zmienisz strukturę tabeli przestawnej (dodając do niej więcej nagłówków wierszy/kolumn, zera będą nadal ukryte).

Jeśli chcesz dowiedzieć się więcej o tym, jak to działa, oto szczegółowy samouczek na temat właściwej drogi zastosować formatowanie warunkowe do tabel przestawnych.

Gdy komórki danych źródłowych mają puste komórki

Poniżej znajduje się przykład, w którym mam zestaw danych, który ma puste komórki dla wszystkich rekordów dla West.

Jeśli stworzę tabelę przestawną przy użyciu tych danych, otrzymam wynik, jak pokazano poniżej.

Domyślnie tabela przestawna pokazuje puste komórki, gdy wszystkie komórki w danych źródłowych są puste. Ale jeśli nadal widzisz 0 w tym przypadku, wykonaj poniższe kroki, aby ukryć zero i pokazać puste komórki:

1. Kliknij prawym przyciskiem myszy dowolną komórkę w tabeli przestawnej
2. Kliknij „Opcje tabeli przestawnej”
3. W oknie dialogowym „Opcje tabeli przestawnej” kliknij kartę „Układ i format”
4. W opcjach formatu zaznacz opcje i „W przypadku pustych komórek pokaż:” i pozostaw puste.
5. Kliknij OK.

Powyższe kroki ukryją zera w tabeli przestawnej i zamiast tego pokażą pustą komórkę.

Jeśli chcesz, aby tabela przestawna pokazywała coś zamiast 0, możesz to określić w kroku 4. Na przykład, jeśli chcesz wyświetlić tekst „Dane niedostępne”, zamiast 0, wpisz ten tekst w polu pole (jak pokazano poniżej).

Znajdź i usuń zera w programie Excel

We wszystkich powyższych metodach pokazałem ci sposoby ukrycia wartości 0 w komórce, ale wartość nadal pozostaje w komórce.

Jeśli chcesz usunąć wartości zerowe ze zbioru danych (pozostawiając puste komórki), wykonaj poniższe czynności:

1. Wybierz zbiór danych (lub cały arkusz)
2. Kliknij kartę Strona główna
3. W grupie „Edycja” kliknij „Znajdź i wybierz”
4. W opcjach, które są wyświetlane w menu, kliknij „Zamień”
5. W oknie dialogowym „Znajdź i zamień” wprowadź następujące zmiany:
   • W polu „Znajdź co” wpisz 0
   • W polu „Zamień na” nic nie wpisuj (pozostaw puste)
   • Kliknij przycisk Opcje i zaznacz opcję „Dopasuj całą zawartość komórki”
6. Kliknij Zamień wszystko

Powyższe kroki natychmiast zastąpiłyby wszystkie komórki, które miały zerową wartość, puste.

Jeśli nie chcesz od razu usuwać zer i najpierw zaznaczać komórek, wykonaj następujące czynności:

1. Wybierz zbiór danych (lub cały arkusz)
2. Kliknij kartę Strona główna
3. W grupie „Edycja” kliknij „Znajdź i wybierz”
4. W opcjach, które są wyświetlane w menu, kliknij „Zamień”
5. W oknie dialogowym „Znajdź i zamień” wprowadź następujące zmiany:
   • W polu „Znajdź co” wpisz 0
   • W polu „Zamień na” nic nie wpisuj (pozostaw puste)
   • Kliknij przycisk Opcje i zaznacz opcję „Dopasuj całą zawartość komórki”
6. Kliknij przycisk „Znajdź wszystko”. To da ci listę wszystkich komórek, które mają w sobie 0
7. Naciśnij Control-A (przytrzymaj klawisz Control i naciśnij A). Spowoduje to wybranie wszystkich komórek, które mają w sobie 0.

Teraz, gdy masz wszystkie komórki z zaznaczonym 0, możesz je usunąć, zastąpić czymś innym lub zmienić kolor i podświetlić je.

Ukrywanie zer vs. Usuwanie zer

Kiedy traktujesz komórki z zerami w środku, musisz znać różnicę między ich ukrywaniem a usuwaniem.

Kiedy ukrywasz wartość zero w komórce, nie jest ona dla ciebie widoczna, ale nadal pozostaje w komórce. Oznacza to, że jeśli masz zbiór danych i używasz go do obliczeń, ta wartość 0 również zostanie użyta w obliczeniach.

Wręcz przeciwnie, jeśli masz pustą komórkę, użycie jej w formułach może oznaczać, że program Excel automatycznie zignoruje te komórki (w zależności od używanej formuły).

Na przykład poniżej jest to, co otrzymuję, gdy używam formuły ŚREDNIA, aby uzyskać średnią dla dwóch kolumn (wynik znajduje się w wierszu 14). Kolumna A zawiera komórki, w których wartość 0 została ukryta (ale nadal jest w komórce), a kolumna B zawiera komórki, w których wartość 0 została usunięta.

Możesz zobaczyć różnicę w wyniku, mimo że zestaw danych dla obu zestawów danych wygląda tak samo.

Dzieje się tak, ponieważ formuła ŚREDNIA ignoruje puste komórki w kolumnie B, ale używa komórek z kolumny A (ponieważ te w kolumnie A nadal mają w sobie wartość 0).