Jak usunąć dodatkowe zera w programie Excel. Jak usunąć podział przez błąd zerowy w programie Excel za pomocą formuły

Czy kiedykolwiek musiałeś importować lub wprowadzać dane w programie Excel, które zawierają wiodące zera (na przykład 00123) lub duże liczby (na przykład 1234 5678 9087 6543)? Mogą to być numery ubezpieczenia społecznego, numery telefonów, numery kart kredytowych, kody produktów, numery kont lub kody pocztowe. Excel automatycznie usuwa wiodące zera i konwertuje duże liczby na reprezentację wykładniczą (na przykład 1,23E + 15), dzięki czemu można ich używać w formułach i operacjach matematycznych. W tym artykule wyjaśniono, jak zapisać dane w oryginalnym formacie, który program Excel przetwarza jako tekst.

Konwertowanie liczb na tekst podczas importowania danych tekstowych

Aby sformatować poszczególne kolumny jako tekst podczas importowania danych do programu Excel, użyj funkcji Get and Convert (Power Query)

W programach Excel 2010 i Excel 2013 można importować pliki tekstowe i konwertować liczby na tekst na dwa sposoby. Zalecane jest użycie Power Query (w tym celu należy pobrać dodatek Power Query). Jeśli nie możesz pobrać dodatku Power Query, możesz użyć kreatora importu tekstu. W takim przypadku plik tekstowy jest importowany, ale dane przechodzą takie same kroki konwersji jak podczas importowania z innych źródeł, takich jak XML, Internet, JSON itp.

Użyj formatu niestandardowego, aby zachować początkowe zera

Jeśli ten problem musi zostać rozwiązany w książce, ponieważ nie jest używany przez inne programy jako źródło danych, możesz użyć niestandardowego lub specjalnego formatu do zapisania zer wiodących. Ta metoda jest odpowiednia dla kodów cyfrowych zawierających mniej niż 16 cyfr. Kody numeryczne można również formatować za pomocą łączników lub innych znaków interpunkcyjnych. Na przykład, aby numery telefonów były bardziej czytelne, możesz dodać łączniki między kodem międzynarodowym, numerem kierunkowym, prefiksem i innymi cyframi.

Instrukcje

Korzystanie z funkcji TEKST do formatowania

Aby przekonwertować dane na żądany format, możesz użyć pustej kolumny obok niej i funkcji TEKST.

Zaokrąglanie numerów kart kredytowych

Maksymalna dokładność liczb w programie Excel wynosi 15 cyfr znaczących. Oznacza to, że w dowolnej liczbie składającej się z 16 lub więcej cyfr, na przykład w numerze karty kredytowej, wszystkie cyfry po 15 są zaokrąglane do zera. Jeśli kody cyfrowe składają się z 16 lub więcej cyfr, musisz użyć formatu tekstowego. Aby to zrobić, wykonaj jedną z następujących czynności:

Formatowanie kolumny jako tekstu

Podświetl zakres danych i naciśnij CTRL + 1aby otworzyć okno dialogowe Format > Komórki. Tab Liczba   wybierz format SMS.

Uwaga:   Ta metoda nie pozwala na zmianę wcześniej wprowadzonych liczb. Działa tylko na liczby wprowadzone po zastosowaniu formatu.

Zastosowanie apostrofu

Przed liczbą możesz wprowadzić apostrof ( " ), a program Excel przetworzy go jako tekst.

Dodatkowe informacje

Zawsze możesz zadać pytanie ekspertowi Excel Tech Community, poprosić o pomoc w społeczności Answers, a także zaoferować nową funkcję lub ulepszenie na stronie

W matematyce dzielenie przez zero jest niemożliwe! Jednym ze sposobów wyjaśnienia tej zasady jest analiza procesu, który pokazuje, co dzieje się, gdy jedna liczba jest dzielona na drugą.

Podział programu Excel według błędu zero

W rzeczywistości operacja dzielenia jest zasadniczo taka sama jak odejmowanie. Na przykład podzielenie liczby 10 przez 2 oznacza wielokrotne odjęcie 2 od 10. Powtarzalność powtarza się, aż wynik wyniesie 0. Dlatego konieczne jest odjęcie liczby 2 od dziesięciu dokładnie 5 razy:

1. 10-2=8
2. 8-2=6
3. 6-2=4
4. 4-2=2
5. 2-2=0

Jeśli spróbujemy podzielić liczbę 10 przez 0, nigdy nie otrzymamy wyniku równego 0, ponieważ odejmując 10-0 zawsze będzie to 10. Nieskończona liczba odejmowania zera od dziesięciu nie doprowadzi nas do wyniku \u003d 0. Zawsze będzie jeden i ten sam wynik po operacji odejmowania \u003d 10:

• 10-0=10
• 10-0=10
• 10-0=10
• ∞ nieskończoność.

Na marginesie matematyków mówią, że wynikiem dzielenia dowolnej liczby przez zero jest „nieograniczony”. Każdy program komputerowy, próbując podzielić przez 0, po prostu zwraca błąd. W programie Excel ten błąd jest wyświetlany przez wartość w komórce #DEL / 0!

Ale w razie potrzeby można pominąć występowanie błędu podziału o 0 w programie Excel. Pomiń operację dzielenia, jeśli mianownik zawiera liczbę 0. Rozwiązanie jest realizowane przez umieszczenie argumentów w argumentach funkcji \u003d IF ():

Tak więc formuła Excela pozwala nam „podzielić” liczbę przez 0 bez błędów. Podczas dzielenia dowolnej liczby przez 0 formuła zwróci wartość 0. Oznacza to, że po podzieleniu otrzymujemy następujący wynik: 10/0 \u003d 0.



Jak działa formuła w celu wyeliminowania podziału przez błąd zerowy

Aby działać poprawnie, funkcja JEŻELI wymaga podania 3 argumentów:

1. Warunek logiczny.
2. Działania lub wartości, które zostaną wykonane, jeśli w wyniku tego warunek logiczny zwróci wartość PRAWDA.
3. Działania lub wartości, które zostaną wykonane, gdy warunek logiczny zwróci FAŁSZ.

W takim przypadku argument warunku zawiera sprawdzenie wartości. Czy wartości komórek w kolumnie Sprzedaż wynoszą 0. Pierwszy argument funkcji JEŻELI musi zawsze zawierać operatory porównania między dwiema wartościami, aby uzyskać wynik warunku jako PRAWDA lub FAŁSZ. W większości przypadków znak równości jest używany jako operator porównania, ale można użyć innych, na przykład więcej\u003e lub mniej\u003e. Lub ich kombinacje - większe lub równe\u003e \u003d, nie równe! \u003d.

Jeśli warunek w pierwszym argumencie zwróci wartość PRAWDA, wówczas formuła wypełni komórkę wartością z drugiego argumentu funkcji JEŻELI. W tym przykładzie drugi argument zawiera liczbę 0 jako wartość. Tak więc komórka w kolumnie „Wykonanie” zostanie po prostu wypełniona liczbą 0, jeśli w komórce naprzeciwko kolumny „Sprzedaż” występuje 0 sprzedaży.

Jeśli warunek w pierwszym argumencie zwraca wartość FAŁSZ, wówczas używana jest wartość z trzeciego argumentu funkcji JEŻELI. W takim przypadku wartość ta jest tworzona po podzieleniu wskaźnika z kolumny „Sprzedaż” przez wskaźnik z kolumny „Plan”.

Wzór na dzielenie przez zero lub zero przez liczbę

Komplikujemy naszą formułę funkcją \u003d OR (). Dodajemy kolejnego agenta sprzedaży z zerowym wskaźnikiem sprzedaży. Teraz formuła powinna zostać zmieniona na:

Skopiuj tę formułę do wszystkich komórek w kolumnie Uruchom:

Teraz, bez względu na to, gdzie w mianowniku lub liczniku jest zero, formuła będzie działać zgodnie z potrzebami użytkownika.

Możesz dostosować wyświetlanie wartości pustych w komórce lub użyć zestawu standardów formatowania w tabeli, które wymagają ukrycia wartości pustych. Istnieje kilka sposobów wyświetlania i ukrywania wartości zerowych.

Ukryj i pokaż wszystkie wartości zerowe na arkuszu

Wybierz Plik > Parametry > Opcjonalnie.

W grupie

• Pokaż zera w komórkach zawierających wartości null.

Ukryj wartości zerowe w wybranych komórkach

Te działania ukrywają wartości puste w wybranych komórkach przy użyciu formatu liczb. Ukryte wartości są wyświetlane tylko na pasku formuły i nie są drukowane. Jeśli wartość w jednej z tych komórek zmieni się na niezerową, zostanie wyświetlona w komórce, a format wartości będzie podobny do ogólnego formatu liczb.

Pokaż ukryte wartości.

Wybierz komórki z ukrytymi wartościami null.

Możesz nacisnąć klawisze CTRL + 1   lub tab Strona główna   kliknąć Format\u003e Format komórki.

Aby użyć domyślnego formatu liczb, wybierz Liczba\u003e Razem   i naciśnij przycisk Okej.

Ukryj wartości zerowe zwracane przez formułę

Tab Strona główna Formatowanie warunkowe   i wybierz Reguły wyboru komórki\u003e Równe.

W lewym polu wpisz 0 .

W prawym polu wybierz Format niestandardowy.

W terenie Format komórki   otwórz zakładkę Czcionka.

Na liście Kolor   wybierz biały kolor i naciśnij przycisk Okej.

Wyświetlaj zera jako spacje lub myślniki

Aby rozwiązać ten problem, użyj funkcji JEŻELI.

Jeśli komórka zawiera wartości , użyj formuły, aby zwrócić pustą komórkę, na przykład:

JEŻELI (A2-A3 \u003d 0; ""; A2-A3)

Oto jak czytać formułę. Jeśli wynikiem obliczeń (A2-A3) jest „0”, nic nie jest wyświetlane, w tym „0” (jest to oznaczone podwójnymi cudzysłowami „”). W przeciwnym razie wyświetlany jest wynik obliczeń A2-A3. Jeśli nie musisz pozostawiać pustych komórek, ale nie wyświetla się „0”, ale coś innego, wstaw łącznik „-” lub inny znak między podwójnymi cudzysłowami.

Wybierz raport tabeli przestawnej.

Tab Analiza   w grupie Tabela podsumowująca Parametry   i wybierz Parametry.

Idź do zakładki Układ i format

• Zmień wyświetlanie błędów   W terenie Format   zaznacz pole Wyświetlane są błędy

  Zmień sposób wyświetlania pustych komórek   Zaznacz pole Wyświetl puste komórki

Konieczność wyświetlania zerowych wartości (0) na arkuszach nie zawsze powstaje. Niezależnie od tego, czy standardy lub preferencje formatowania wymagają wyświetlania lub ukrywania wartości zerowych, istnieje kilka sposobów na wdrożenie wszystkich tych wymagań.

Wybierz Plik > Parametry > Opcjonalnie.

W grupie Pokaż opcje dla następnego arkusza   wybierz arkusz, a następnie wykonaj jedną z następujących czynności:

• Aby wyświetlić wartości null (0) w komórkach, wybierz Pokaż zera w komórkach zawierających wartości null.

  Aby wyświetlić wartości null jako puste komórki, wyczyść pole wyboru Pokaż zera w komórkach zawierających wartości null.

Możesz nacisnąć klawisze CTRL + 1   lub tab Strona główna   kliknąć Format\u003e Format komórki.

W terenie Rodzaj   wchodzić 0;-0;;@

Uwagi:

Ukryte wartości są wyświetlane tylko na pasku formuły lub w komórce, jeśli edytujesz jej zawartość. Te wartości nie są drukowane.

Aby ponownie wyświetlić ukryte wartości, wybierz komórki, a następnie naciśnij klawisze CTRL + 1   lub tab Strona główna   w grupie Komórki   najedź kursorem na element Format   i wybierz Format komórki. Aby zastosować domyślny format liczb, lista Kategoria   wybierz Ogólne. Aby ponownie wyświetlić datę i godzinę, wybierz odpowiedni format daty i godziny na karcie Liczba.

Wybierz komórkę zawierającą wartość zero (0).

Tab Strona główna   w grupie Style   kliknij strzałkę obok elementu Formatowanie warunkowenajedź kursorem na element Reguły wyboru komórek   i wybierz opcję W równym stopniu.

W lewym polu wpisz 0 .

W prawym polu wybierz Format niestandardowy.

W oknie dialogowym Format komórki   otwórz zakładkę Czcionka.

W terenie Kolor   wybierz kolor biały.

Aby wykonać to zadanie, użyj funkcji IF.

Przykład

  Funkcja IF.

Ukryj wartości zerowe w raporcie tabeli przestawnej

Tab Parametry   w grupie Opcje tabeli przestawnej   kliknij strzałkę obok polecenia Parametry   i wybierz Parametry.

Idź do zakładki Układ i format, a następnie wykonaj następujące kroki.

W terenie Format   zaznacz pole Wyświetlane są błędy. Wpisz w polu wartość, którą chcesz wyświetlić zamiast błędów. Aby wyświetlić błędy jako puste komórki, usuń cały tekst z pola.

Zaznacz pole Wyświetl puste komórki. Wpisz w polu wartość, którą chcesz wyświetlić w pustych komórkach. Aby pozostawić je puste, usuń cały tekst z pola. Aby wyświetlić wartości zerowe, wyczyść to pole wyboru.

Konieczność wyświetlania zerowych wartości (0) na arkuszach nie zawsze powstaje. Niezależnie od tego, czy standardy lub preferencje formatowania wymagają wyświetlania lub ukrywania wartości zerowych, istnieje kilka sposobów na wdrożenie wszystkich tych wymagań.

Pokaż i ukryj wszystkie wartości zerowe na arkuszu

Ukryj wartości zerowe w wybranych komórkach za pomocą formatu liczb

Te działania ukrywają wartości zerowe w wybranych komórkach. Jeśli wartość w jednej z komórek stanie się niezerowa, jej format będzie podobny do ogólnego formatu liczb.

Wybierz komórki zawierające wartości null (0), które chcesz ukryć.

Możesz nacisnąć klawisze CTRL + 1   lub tab Strona główna   w grupie Komórki   kliknąć Format\u003e Format komórki.

W terenie Rodzaj   wchodzić 0;-0;;@

Uwagi:

Ukryj wartości zerowe zwracane przez formułę przy użyciu formatowania warunkowego

Wybierz komórkę zawierającą wartość zero (0).

Tab Strona główna   w grupie Style   kliknij strzałkę obok przycisku Formatowanie warunkowe   i wybierz Reguły wyboru komórki\u003e Równe.

W lewym polu wpisz 0 .

W prawym polu wybierz Format niestandardowy.

W oknie dialogowym Format komórki   otwórz zakładkę Czcionka.

W terenie Kolor   wybierz kolor biały.

Używanie formuły do \u200b\u200bwyświetlania zer jako spacji lub myślników

Użyj funkcji JEŻELI, aby wykonać to zadanie.

Przykład

Aby ułatwić zrozumienie tego przykładu, skopiuj go na pusty arkusz.

Skopiuj przykład

Podświetl przykład w tym artykule.

Ważne:   Nie wyróżniaj nagłówków wierszy lub kolumn.

Podkreślenie przykładu w Pomocy

Naciśnij CTRL + C.

W programie Excel utwórz pusty skoroszyt lub arkusz.

Wybierz komórkę A1 na arkuszu i naciśnij CTRL + V.

Ważne:   Aby przykład działał poprawnie, musisz wstawić go do komórki A1.

Aby przełączać się między wyświetlaniem wyników i formułami, które zwracają te wyniki, naciśnij CTRL + `(znak akcentu) lub tab Formuły   w grupie „Formuły zależne” kliknij Pokaż formuły.

Kopiując przykład na pusty arkusz, możesz go dostosować według potrzeb.

Aby uzyskać więcej informacji na temat korzystania z tej funkcji, zobacz artykuł o funkcji JEŻELI.

Ukryj wartości zerowe w raporcie tabeli przestawnej

Kliknij raport tabeli przestawnej.

Tab Parametry   w grupie Opcje tabeli przestawnej   kliknij strzałkę obok polecenia Parametry   i wybierz Parametry.

Idź do zakładki Układ i format, a następnie wykonaj następujące kroki.

Zmień sposób wyświetlania błędów.   W terenie Format   zaznacz pole Wyświetlane są błędy. Wpisz w polu wartość, którą chcesz wyświetlić zamiast błędów. Aby wyświetlić błędy jako puste komórki, usuń cały tekst z pola.

Zmień sposób wyświetlania pustych komórek.   Zaznacz pole Wyświetl puste komórki. Wpisz w polu wartość, którą chcesz wyświetlić w pustych komórkach. Aby pozostawić je puste, usuń cały tekst z pola. Aby wyświetlić wartości zerowe, wyczyść to pole wyboru.

Jak ustawić zero w programie Excel? Podczas tworzenia raportów w programie Excel konieczne jest umieszczenie pustych komórek lub zero, albo wymagają usunięcia wartości zerowych. Jak mówi przysłowie: „pan jest panem” lub „kaprys za twoje pieniądze”. Ale okazuje się, że dla wielu powoduje to duże trudności. Wszystko zależy od ustawień programu. Albo umieszcza zera tam, gdzie jest to konieczne i niepotrzebne, albo wcale ich nie umieszcza. Dlatego dzisiaj rozważymy te dwa pytania.

Usuń lub ustaw zera w programie Microsoft Excel 2003

Aby to zrobić, przejdź do menu Servicei wybierz na samym dole Parametry.

Otworzy się okno o tej samej nazwie. Parametry.

Tab Zobaczw panelu Opcje okna, w środkowej kolumnie zaznacz pole Zero wartości. Teraz zostaną wyświetlone wszystkie wartości zerowe w komórkach.

W ten sam sposób usuń zera w exelu. Aby to zrobić, odznacz tę opcję, a zera nie będą wyświetlane.

Jak ustawić lub usunąć zera w programie Microsoft Excel 2007

Kliknij wielokolorową ikonę programu w lewym górnym rogu.

W menu, które otwiera się na samym dole, kliknij przycisk ParametryExcel.

W oknie, które zostanie otwarte ParametryExcel   przejdź do zakładki Opcjonalnie, aw dużym oknie po prawej stronie znajdź i zaznacz wpis -.

w Microsoft Excel 2010

Wszystko jest takie samo jak w programie Excel 2007, wystarczy najpierw otworzyć menu Plikidź do Parametrya następnie - Opcjonalnie – Pokaż opcje dla następnego arkusza   - usuń lub zaznacz pole - Pokaż zera w komórkach zawierających wartości null.

Wartości wyświetlane w komórkach arkusza kalkulacyjnego aplikacji Microsoft Office Excel są często generowane w wyniku wykonania zapisanych w nich formuł. Wynik obliczeń może być wartością zerową, co jest niepożądane w komórce. Zera nie poprawiają ogólnego postrzegania wyników, zwłaszcza jeśli w innych komórkach kolumny formuły wyświetlają tekst, a nie wartości liczbowe. Istnieje kilka sposobów rozwiązania tego problemu.

Będziesz potrzebować

• Edytor arkuszy kalkulacyjnych Microsoft Office Excel 2007 lub 2010.

Instrukcja obsługi

Aby wyłączyć wyświetlanie wartości zerowych we wszystkich komórkach otwartego arkusza dokumentu Excel, użyj jednego z ustawień w ogólnych ustawieniach edytora arkuszy kalkulacyjnych. Ustawienia te są otwierane za pomocą menu głównego - w wersji 2010 kliknij przycisk „Plik”, aby uzyskać do niego dostęp, aw wersji 2007 przeznaczony jest do tego okrągły przycisk Office. Kliknij pozycję menu „Opcje” (wersja 2010) lub kliknij przycisk „Opcje programu Excel” (wersja 2007).

Wybierz z listy sekcję „Zaawansowane” i przewiń do słów „Pokaż opcje dla następnego arkusza”. Odznacz „Pokaż zera w komórkach zawierających wartości zerowe” i kliknij OK.

Inna metoda pozwala ukryć wartości zerowe nie w całym arkuszu, ale w arbitralnie wybranej grupie komórek. Wybierz żądany obszar tabeli i kliknij wybór prawym przyciskiem myszy. W menu podręcznym wybierz wiersz „Format komórki”, aw lewej kolumnie okna, które zostanie otwarte, kliknij dolny wiersz - „Wszystkie formaty”.

W polu pod napisem „Typ” wprowadź następującą sekwencję znaków: „0; -0 ;; @” (bez cudzysłowów). Jeśli niezerowe wartości wybranego obszaru powinny być wyświetlane z określoną liczbą miejsc dziesiętnych, po każdym zera w tym rekordzie dodaj odpowiednią liczbę zer, oddzielając je przecinkiem. Na przykład, aby ustawić dokładność na setne, ten wpis powinien wyglądać następująco: „0,00; -0,00 ;; @”. Następnie kliknij OK, a zera nie będą już wyświetlane.

Trzecia metoda nie usuwa wartości zerowych, ale maluje je kolorem tła komórki, co czyni go niewidocznym. Ta metoda wykorzystuje formatowanie warunkowe - wybierz żądaną kolumnę lub zestaw kolumn i kliknij przycisk o tej samej nazwie w grupie poleceń „Style”. Z rozwijanej listy przejdź do sekcji „Reguły wyboru komórek” i wybierz wiersz „Równe”.

W lewym polu wyświetlonego formularza wpisz zero, a na liście prawego pola wybierz „Format niestandardowy”. Otworzy się okno dialogowe „Formatuj komórki”, na zakładce „Czcionka”, której potrzebujesz listy rozwijanej pod napisem „Kolor” - otwórz ją i wybierz kolor tła komórki w tabeli kolorów (zwykle biały). Kliknij OK w obu otwartych oknach dialogowych, a zadanie zostanie rozwiązane.