Czy kiedykolwiek importujesz lub wprowadzasz w programie Excel dane zawierające zera wiodące, takie jak 00123, lub duże liczby, takie jak 1234 5678 9087 6543? Przykładowe 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 notację naukową, na przykład 1,23E+15, aby umożliwić pracę nad formułami i operacjami matematycznymi. W tym artykule opisano, jak zachować dane w oryginalnym formacie, który program Excel traktuje jako tekst.
Ustawianie automatycznej 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 i Excel 2024 dla komputerów Mac.
Użyj funkcji Automatyczna konwersja danych programu Excel, aby zmienić domyślne zachowanie programu Excel dla następujących automatycznych konwersji danych:
-
Usuwanie zer wiodących z tekstu liczbowego i konwertowanie na liczbę.
-
Obcinanie danych liczbowych do 15 cyfr dokładności i konwertowanie na liczbę wyświetlaną w notacji naukowej.
-
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 automatycznej konwersji danych.
Konwertowanie liczb na tekst podczas importowania danych tekstowych
Formatowanie pojedynczych kolumn jako tekstu podczas importowania danych za pomocą funkcji Przekształcanie & (Power Query) w programie Excel. W takim przypadku importujemy plik tekstowy, ale kroki przekształcania danych są takie same w przypadku danych importowanych z innych źródeł, takich jak xml, sieć Web, JSON itp.
-
Wybierz kartę Dane , a następnie pozycję Z tekstu/pliku CSV obok przycisku Pobierz dane . Jeśli nie widzisz przycisku Pobierz dane , przejdź do pozycji Nowe zapytanie > Z pliku > Z tekstu i przejdź do pliku tekstowego, a następnie naciśnij Importuj.
-
Program Excel załaduje dane do okienka podglądu. Naciśnij Edytuj w okienku podglądu, aby załadować Edytor Power Query.
-
Jeśli jakiekolwiek kolumny muszą zostać przekonwertowane na tekst, zaznacz kolumnę do przekonwertowania, klikając nagłówek kolumny, a następnie przejdź do pozycji Narzędzia główne > Przekształcanie > typ danych , > wybierz pozycję Tekst.
Porada: Możesz zaznaczyć wiele kolumn za pomocą Ctrl i kliknięcia lewym przyciskiem myszy.
-
Następnie wybierz pozycję Zamień bieżący w oknie dialogowym Zmienianie typu kolumny , a program Excel przekonwertuje zaznaczone kolumny na tekst.
-
Po zakończeniu wybierz pozycję Zamknij & Załaduj, a program Excel zwróci dane zapytania do arkusza.
Jeśli dane zmienią się w przyszłości, możesz przejść do pozycji Dane > Odśwież, a program Excel automatycznie zaktualizuje dane i zastosuje przekształcenia za Ciebie.
Stosowanie formatu niestandardowego w celu zachowania zer wiodących
Jeśli chcesz rozwiązać ten problem tylko w skoroszycie, ponieważ nie jest on używany przez inne programy jako źródło danych, możesz użyć niestandardowego lub specjalnego formatu, aby zachować zera wiodące. Działa to w przypadku kodów liczbowych zawierających mniej niż 16 cyfr. Ponadto kody liczb można formatować za pomocą kresek lub innych znaków interpunkcyjnych. Aby na przykład zwiększyć czytelność numeru telefonu, możesz dodać kreskę między kodem międzynarodowym, kodem kraju/regionu, numerem kierunkowym, prefiksem i kilkoma ostatnimi numerami.
|
Kod numeru |
Przykład |
Niestandardowy format liczb |
|---|---|---|
|
Społeczny bezpieczeństwo |
012345678 |
000-00-0000 012-34-5678 |
|
Phone (Telefon) |
0012345556789 |
00-0-000-000-00000 00-1-234-555-6789 |
|
Pocztowy kod |
00123 |
00000 00123 |
Czynności
-
Zaznacz komórkę lub zakres komórek, które chcesz sformatować.
-
Naciśnij Ctrl+1 , aby załadować okno dialogowe Formatowanie komórek .
-
Wybierz kartę Liczba , a następnie 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 wybrać 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 usuniętych przed formatowaniem. Będzie to miało wpływ tylko na liczby wprowadzone po zastosowaniu formatu.
Stosowanie formatu za pomocą funkcji TEKST
Możesz użyć pustej kolumny obok danych i przekonwertować ją na odpowiedni format za pomocą funkcji TEKST .
|
Kod numeru |
Przykład (w komórce A1) |
FUNKCJA TEKST i nowy format |
|---|---|---|
|
Społeczny bezpieczeństwo |
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ść programu Excel wynosi 15 cyfr znaczących, co oznacza, że w przypadku dowolnej liczby zawierającej co najmniej 16 cyfr, takiej jak numer karty kredytowej, wszystkie liczby przeszłe przez 15 cyfrę są zaokrąglane w dół do zera. W przypadku kodów liczbowych zawierających 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 Ctrl+1 , aby uruchomić okno dialogowe Formatowanie komórek . Na karcie Liczba wybierz pozycję Tekst.
Uwaga: Nie spowoduje to zmiany liczb, które zostały już wprowadzone. Będzie to miało wpływ tylko na liczby wprowadzone po zastosowaniu formatu.
-
Używanie apostrofu
Przed liczbą możesz wpisać apostrof ('), a program Excel potraktuje go jako tekst.
Potrzebujesz dodatkowej pomocy?
Zawsze możesz zadać pytanie ekspertowi w społeczności technicznej programu Excel lub uzyskać pomoc techniczną w Społecznościach.
