Zachowywanie zer wiodących i dużych liczb

Dotyczy
Excel dla Microsoft 365 Excel dla Microsoft 365 dla komputerów Mac Excel 2024 Excel 2024 dla komputerów Mac Excel 2021 Excel 2019 Excel 2016

Czy kiedykolwiek importujesz lub wprowadzasz w programie Excel dane zawierające zera wiodące, takie jak 00123, lub duże liczby, na przykład 1234 5678 9087 6543? Przykładami są numery PESEL, numery telefonów, numery kart kredytowych, kody produktów, numery kont lub kody pocztowe. Program Excel automatycznie usuwa zera wiodące i konwertuje duże liczby na wartości w notacji naukowej, na przykład 1,23E+15, aby umożliwić wykonywanie na nich formuł i operacji matematycznych. Ten artykuł dotyczy zachowywania danych w oryginalnym formacie, który program Excel traktuje jak tekst.

Ustawianie automatycznego konwersji danych

Ważne

Ta funkcja jest dostępna w programach Excel dla Microsoft 365, Excel dla Microsoft 365 dla komputerów Mac, Excel 2024, Excel 2024 dla komputerów Mac.

Funkcja automatycznej konwersji danych programu Excel umożliwia zmianę domyślnego zachowania programu Excel dla następujących operacji automatycznego konwersji danych:

  • Usuwanie zer wiodących z tekstu numerycznego i konwertowanie ich na liczby.
  • Obcięcie danych numerycznych do dokładności 15 cyfr i przekonwertowanie ich na liczbę wyświetlaną w notacji wykładniczej.
  • Konwertowanie danych liczbowych otaczających literę "E" na notację naukową.
  • Konwertowanie ciągłego ciągu liter i cyfr na datę.

Aby uzyskać więcej informacji, zobacz Ustawianie automatycznego konwersji danych.

Konwertowanie liczb na tekst podczas importowania danych tekstowych

Użyj środowiska pobierania & przekształcania (Power Query) w programie Excel, aby formatować poszczególne kolumny jako tekst podczas importowania danych. W tym przypadku importujemy plik tekstowy, ale kroki przekształcania danych są takie same dla danych zaimportowanych z innych źródeł, takich jak XML, sieć Web, JSON itp.

  1. Wybierz kartę Dane , a następnie pozycję Z tekstu/pliku CSV obok przycisku Pobierz dane . Jeśli przycisk Pobierz dane nie jest widoczny, przejdź do pozycji Nowe zapytanie>z pliku>z tekstu , przejdź do pliku tekstowego, a następnie naciśnij pozycję Importuj.

  2. Program Excel załaduje dane do okienka podglądu. Naciśnij pozycję Edytuj w okienku podglądu, aby załadować edytor Power Query.

  3. Jeśli jakieś kolumny wymagają przekonwertowania na tekst, wybierz kolumnę do przekonwertowania, klikając nagłówek kolumny, a następnie przejdź do Strona główna>Przekształcanie>typu danych> Wybierz tekst.

    Power Query — dane po przekonwertowaniu na tekst

    Porada

    Możesz zaznaczyć wiele kolumn za pomocą kombinacji klawiszy Ctrl+Kliknięcie lewym przyciskiem myszy.

  4. Następnie wybierz pozycję Zamień bieżące w oknie dialogowym Zmienianie typu kolumny , a program Excel przekonwertuje zaznaczone kolumny na tekst.

    Pobierz & Przekształć > Konwertowanie danych na tekst

  5. Po zakończeniu wybierz pozycję Zamknij & Załaduj, a program Excel zwróci dane zapytania do arkusza.
    Jeśli Twoje dane zmienią się w przyszłości, możesz przejść doodświeżaniadanych>, a program Excel automatycznie zaktualizuje dane i zastosuje przekształcenia.

Zachowywanie zer wiodących przy użyciu formatu niestandardowego

Jeśli chcesz rozwiązać problem tylko w skoroszycie, ponieważ nie jest on używany przez inne programy jako źródło danych, możesz zachować zera wiodące w formacie niestandardowym lub specjalnym. Ta funkcja działa w przypadku kodów liczbowych zawierających mniej niż 16 cyfr. Ponadto kody liczbowe można formatować przy użyciu myślników i innych znaków interpunkcyjnych. Na przykład aby numer telefonu był bardziej czytelny, można dodać kreskę między numerem kierunkowym międzynarodowym, kodem kraju/regionu, numerem kierunkowym, prefiksem i kilkoma ostatnimi cyframi.

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
Pocztowy
kod
00123 00000
00123

Kroki 

  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ę Numerowanie , na liście Kategoria wybierz pozycję Niestandardowe , a następnie w polu Typ wpisz format liczb, na przykład 000-00-0000 dla kodu PESEL lub 00000 dla pięciocyfrowego kodu pocztowego.

    Porada

    Możesz również wybrać pozycję Specjalne, a następnie wybierz pozycję Kod pocztowy, Kod pocztowy + 4, Numer telefonu lub Numer PESEL.

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

    Uwaga

    Nie spowoduje to przywrócenia zer wiodących, które zostały usunięte przed formatowaniem. Dotyczy to tylko liczb wprowadzonych 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ć je na odpowiedni format.

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-000-0000")
00-1-234-555-6789
Pocztowy
kod
00123 =TEKST(A1;"00000")
00123

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

Maksymalna dokładność w programie Excel wynosi 15 cyfr znaczących, co oznacza, że w przypadku dowolnej liczby zawierającej 16 lub więcej cyfr, takiej jak numer karty kredytowej, liczby po piętnastej cyfrze są zaokrąglane w dół do zera. W przypadku kodów liczbowych składających się z 16 cyfr lub więcej należy użyć formatu tekstowego. Aby to zrobić, wykonaj jedną z dwóch czynności:

  • Formatowanie kolumny jako tekstu
    Zaznacz zakres danych i naciśnij klawisze Ctrl+1 , aby otworzyć okno dialogowe Formatowanie komórek . Na karcie Liczba zaznacz pozycję Tekst.

    Uwaga

    Nie spowoduje to zmiany numerów, które zostały już wprowadzone. Dotyczy to tylko liczb wprowadzonych po zastosowaniu formatu.

  • Używanie znaku apostrofu
    Możesz wpisać apostrof (') przed liczbą, a program Excel potraktuje ją jak tekst.

Początek strony

Potrzebujesz dalszej pomocy?

Zawsze możesz zadać pytanie ekspertowi w społeczności technicznej programu Excel lub uzyskać pomoc techniczną w społecznościach.