Zachowywanie zer wiodących i dużych liczb

Czy importujesz lub wprowadzasz dane w programie Excel zawierające zera wiodące, takie jak 00123, lub duże liczby, takie jak 1234 5678 9087 6543? Są to na przykład numery PESEL, numery telefonów, numery kart kredytowych, kody produktów, numery kont lub kody pocztowe. Excel automatycznie usuwa zera wiodące i konwertuje duże liczby na notację naukową, na przykład 1,23E+15, aby umożliwić pracę nad formułami i operacjami matematycznych. W tym artykule opisano, jak zachować dane w oryginalnym formacie, który Excel traktować jako tekst.

Konwertowanie liczb na tekst podczas importowania danych tekstowych

Za Excel dodatku Get & Transform (Power Query) można formatować pojedyncze kolumny jako tekst podczas importowania danych. W tym przypadku importujemy plik tekstowy, ale kroki przekształcania danych są takie same w przypadku danych zaimportowanych z innych źródeł, takich jak XML, Sieć Web, JSON itp.

  1. Kliknij kartę Dane, a następnie pozycję Z tekstu/pliku CSV obok przycisku Pobierz dane. Jeśli nie widzisz przycisku Pobierz dane, przejdź do strony Nowe zapytanie >Z pliku > Z tekstu i przejdź do pliku tekstowego, a następnie naciśnij pozycję Importuj.

  2. Excel spowoduje załadowanie danych do okienka podglądu. Naciśnij pozycję Edytuj w okienku podglądu, aby załadować Edytor zapytań.

  3. Jeśli którekolwiek kolumny muszą zostać przekonwertowane na tekst, zaznacz kolumnę do przekonwertowania, klikając nagłówek kolumny, a następnie przejdź do pozycji Narzędzia > Przekształcanie >typ danych > pozycję Tekst.

    Power Query — dane po przekonwertowaniu na tekst

    Porada: Możesz zaznaczyć wiele kolumn, przytrzymując naciśnięty klawisz Ctrl i klikając lewym przyciskiem myszy.

  4. Następnie kliknij przycisk Zamień bieżący w oknie dialogowym Zmienianie typu kolumny i Excel przekonwertuje zaznaczone kolumny na tekst.

    Uzyskiwanie & przekształcania > Konwertowanie danych na tekst
  5. Gdy to zrobisz, kliknij przycisk Zamknij, & załaduj, a Excel dane zapytania zostaną zwrócone do arkusza.

    Jeśli Twoje dane u zostaną w przyszłości wprowadzone zmiany, możesz przejść do strony Odświeżanie >iExcel automatyczne aktualizowanie danych i stosowanie przekształceń.

W Excel 2010 i 2013 istnieją dwie metody importowania plików tekstowych i konwertowania liczb na tekst. Zalecaną metodą jest użycie dodatku Power Query, które jest dostępne po pobraniu dodatku Power Query. Jeśli nie możesz pobrać dodatku Power Query, możesz użyć Kreatora importu tekstu. W tym przypadku importujemy plik tekstowy, ale kroki przekształcania danych są takie same w przypadku danych zaimportowanych z innych źródeł, takich jak XML, Sieć Web, JSON itp.

  1. Kliknij kartę Power Query na Wstążce, a następnie wybierz pozycję Pobierz dane zewnętrzne > z tekstu.

  2. Excel spowoduje załadowanie danych do okienka podglądu. Naciśnij pozycję Edytuj w okienku podglądu, aby załadować Edytor zapytań.

  3. Jeśli którekolwiek kolumny muszą zostać przekonwertowane na tekst, zaznacz kolumnę do przekonwertowania, klikając nagłówek kolumny, a następnie przejdź do pozycji Narzędzia > Przekształcanie >typ danych > pozycję Tekst.

    Power Query — dane po przekonwertowaniu na tekst

    Porada: Możesz zaznaczyć wiele kolumn, przytrzymując naciśnięty klawisz Ctrl i klikając lewym przyciskiem myszy.

  4. Następnie kliknij przycisk Zamień bieżący w oknie dialogowym Zmienianie typu kolumny i Excel przekonwertuje zaznaczone kolumny na tekst.

    Uzyskiwanie & przekształcania > Konwertowanie danych na tekst
  5. Gdy to zrobisz, kliknij przycisk Zamknij, & załaduj, a Excel dane zapytania zostaną zwrócone do arkusza.

    Jeśli Twoje dane u zostaną w przyszłości wprowadzone zmiany, możesz przejść do strony Odświeżanie >iExcel automatyczne aktualizowanie danych i stosowanie przekształceń.

Stosowanie formatu niestandardowego do zera wiodącego

Jeśli chcesz rozwiązać ten problem tylko w skoroszycie, ponieważ nie jest używany przez inne programy jako źródło danych, możesz użyć formatu niestandardowego lub specjalnego, aby zachować zera wiodące. Działa to w przypadku kodów liczbowych zawierających mniej niż 16 cyfr. Ponadto możesz sformatować kody liczb za pomocą kresek lub innych znaków interpunkacyjnych. Aby na przykład numer telefonu był bardziej czytelny, możesz dodać kreskę między kodem międzynarodowym, kodem kraju/regionu, kodem kierunkowym, prefiksem i ostatnimi numerami.

Kod numeru

Przykład

Niestandardowy format liczb

Społecznościowe
zabezpieczenia

012345678

000-00-0000
012-34-5678 

Phone (Telefon)

0012345556789

00-0-000-000-0000
00-1-234-555-6789 

Poowiąz
kod

00123

00000
00123 

Czynności    

  1. Zaznacz komórkę lub zakres komórek, które chcesz sformatować.

  2. Naciśnij klawisze Ctrl+1, aby załadować okno dialogowe Formatowanie komórek.

  3. Wybierz kartę Liczby, na liście Kategoria kliknij pozycję Niestandardowe, a następnie w polu Typ wpisz format liczb, na przykład 000-00-0000 dla numeru PESZ lub 00000 w przypadku 5-cyfrowego kodu pocztowego.

    Porada: Możesz również kliknąć pozycję Specjalne, a następnie wybrać pozycję Kod pocztowy ,Kod pocztowy + 4, Telefon numerlub Numer PE PESZ.

    Aby uzyskać więcej informacji na temat niestandardowych kodów, zobacz Tworzenie lub usuwanie niestandardowego formatu liczb.

    Uwaga: Nie spowoduje to przywrócenia zer wiodących, które zostały usunięte przed formatowaniem. Ma to wpływ tylko na liczby wprowadzone po zastosowaniu formatu.

Stosowanie formatu przy użyciu funkcji TEKST

Możesz użyć pustej kolumny obok danych i użyć funkcji TEKST, aby przekonwertować ją na format, który chcesz.

Kod numeru

Przykład (w komórce A1)

Funkcja TEKST i nowy format

Społecznościowe
zabezpieczenia

012345678

=TEKST(A1;"000-00-0000")
012-34-5678

Phone (Telefon)

0012345556789

=TEKST(A1;"00-0-000-000-0000")
00-1-234-555-6789

Poowiąz
kod

00123

=TEKST(A1;"00000")
00123

Numery kart kredytowych są zaokrąglane w dół

Excel ma maksymalną dokładność do 15 cyfr znaczących, co oznacza, że dla każdej liczby zawierającej co najmniej 16 cyfr, na przykład numeru karty kredytowej, wszystkie liczby po 15 cyfrze są zaokrąglane w dół do zera. W przypadku kodów liczbowych o rozmiarze co najmniej 16 cyfr należy użyć formatu tekstowego. W tym celu możesz wykonać jedną z dwóch czynności:

  • Formatowanie kolumny jako tekstu

    Zaznacz zakres danych i naciśnij klawisze Ctrl+1, aby uruchomić okno dialogowe Formatowanie > komórki. Na karcie Liczby kliknij pozycję Tekst.

    Uwaga: Nie spowoduje to zmiany liczb, które zostały już wprowadzone. Ma to wpływ tylko na liczby wprowadzone po zastosowaniu formatu.

  • Używanie znaku apostrofu

    Możesz wpisać apostrof (') przed liczbą, a Excel potraktuje ją jako tekst.

Początek strony

Potrzebujesz dodatkowej pomocy?

Zawsze możesz zadać pytanie ekspertowi w społeczności technicznej programu Excel, uzyskać pomoc techniczną w społeczności witryny Answers bądź zasugerować nową funkcję lub ulepszenie w witrynie UserVoice dotyczącej programu Excel.

Potrzebna dalsza pomoc?

Rozwijaj umiejętności związane z pakietem Office
Poznaj szkolenia
Uzyskuj nowe funkcje w pierwszej kolejności
Dołącz do niejawnych testerów pakietu Office

Czy te informacje były pomocne?

×