Istnieją sytuacje, w których musisz dodać wiodące zera do zestawu danych w programie Excel. Może tak być, jeśli prowadzisz w programie Excel rekordy, takie jak identyfikatory pracowników lub identyfikatory transakcji.
Na przykład możesz chcieć uzyskać spójny wygląd zestawu danych, jak pokazano poniżej:
W tym samouczku poznasz różne sposoby dodawania wiodących zer w programie Excel:
- Konwersja formatu na tekst
- Korzystanie z niestandardowego formatowania liczb
- Korzystanie z funkcji tekstu
- Korzystanie z funkcji REPT/LEN
- Korzystanie z VBA
Każda z tych metod ma swoje zalety i wady (omówione w każdej sekcji).
Zobaczmy, jak każdy z nich działa.
Dodaj wiodące zera, konwertując format na tekst
Kiedy użyć: Gdy masz mały zestaw danych liczbowych i planujesz wykonać tę edycję ręcznie.
Załóżmy, że masz identyfikatory pracowników działu marketingu, jak pokazano poniżej, i chcesz, aby te identyfikatory wyglądały spójnie, dodając wiodące zera.
Więc próbujesz zmienić identyfikator, wprowadzając wiodące zera (00001 zamiast 1).
Ale ku Twojemu zdumieniu Excel konwertuje go z powrotem na 1.
Dzieje się tak, ponieważ program Excel rozumie, że 00001 i 1 to te same liczby i powinny przestrzegać tych samych reguł wyświetlania.
Teraz tak frustrujące, jak może to być dla Ciebie, Excel ma swoje powody.
Aby więc wykonać pracę bez naginania reguł Excela, musisz wykorzystać fakt, że ta reguła nie dotyczy formatowania tekstu.
Oto, co musisz zrobić:
- Zaznacz komórki, w których chcesz ręcznie dodać wiodące zera.
- Przejdź do Strona główna → Grupa numerów i wybierz Tekst z listy rozwijanej.
Otóż to!
Teraz, gdy ręcznie wprowadzisz początkowe zera, Excel z łatwością się do tego zastosuje.
Przestroga: Po przekonwertowaniu formatu na tekst niektóre funkcje programu Excel nie będą działać poprawnie. Na przykład funkcja SUMA/LICZBA zignorowałaby komórkę, ponieważ jest ona w formacie tekstowym.
Dodaj wiodące zera za pomocą niestandardowego formatowania liczb
Kiedy użyć: Gdy masz numeryczny zestaw danych i chcesz, aby wynik był liczbowy (nie tekstowy).
Gdy wyświetlasz liczbę w określonym formacie, nie zmienia to podstawowej wartości liczby. Na przykład mogę wyświetlić liczbę 1000 jako 1000 lub 1000 lub 1000,00 lub 001000 lub 26-09-1902 (parzyste daty to liczby w zapleczu w Excelu).
Na wszystkie różne sposoby wyświetlania liczby, wartość liczby nigdy się nie zmienia. Zmienia się tylko sposób wyświetlania.
Aby dodać wiodące zera, możemy go sformatować, aby pokazać to w ten sposób, podczas gdy podstawowa wartość pozostanie niezmieniona.
Oto kroki, aby użyć tej techniki do dodawania wiodących zer w programie Excel:
- Zaznacz komórki, w których chcesz dodać wiodące zera.
- Przejdź do Home → Number Group i kliknij okno dialogowe (mała przechylona strzałka w prawym dolnym rogu). Spowoduje to otwarcie okna dialogowego Formatowanie komórek. Możesz też użyć skrótu klawiaturowego: Control + 1.
- W oknie dialogowym Formatowanie komórek na karcie Liczba wybierz opcję Niestandardowe na liście Kategoria.
- W polu Typ wpisz 00000
- Kliknij OK.
Spowoduje to, że wszystkie liczby będą zawsze wyświetlane jako pięć cyfr, gdzie wiodące zera są automatycznie dodawane, jeśli liczba jest mniejsza niż 5 cyfr. Więc 10 stałoby się 00010, a 100 stałoby się 00100.
W tym przypadku użyliśmy sześciu zer, ale jeśli Twoje dane zawierają liczby z większą liczbą cyfr, musisz odpowiednio użyć formatu.
Uwaga: ta technika zadziała tylko w przypadku liczbowego zestawu danych. Jeśli masz identyfikatory pracowników, takie jak A1, A2, A3 i tak dalej, to są one tekstowe i nie zmienią się po zastosowaniu niestandardowego formatu, jak pokazano powyżej.
Dodaj wiodące zera za pomocą funkcji TEKST
Kiedy użyć: Gdy chcesz, aby wynik był tekstem.
Funkcja TEKST umożliwia zmianę wartości na żądany format.
Na przykład, jeśli chcesz, aby 1 było wyświetlane jako 001, możesz użyć do tego funkcji TEKST.
Pamiętaj jednak, że funkcja TEKST zmieni format i uczyni go TEKSTEM. Oznacza to, że jeśli ustawisz 1 jako 001, program Excel potraktuje nowy wynik jako tekst z trzema znakami (tak jak abc lub xyz).
Oto jak dodać wiodące zera za pomocą funkcji TEKST:
- Jeśli masz liczby w kolumnie A (powiedzmy z A2:A100), wybierz B2:B100 i wprowadź następującą formułę:
=TEKST(A2;”00000”) - Naciśnij klawisze Control + Enter, aby zastosować formułę do wszystkich zaznaczonych komórek.
Spowoduje to wyświetlenie wszystkich liczb jako pięciu cyfr, gdzie wiodące zera są automatycznie dodawane, jeśli liczba jest mniejsza niż 5 cyfr.
Jedną z zalet konwertowania danych na tekst jest to, że można ich teraz używać w formułach wyszukiwania, takich jak WYSZUKAJ.PIONOWO lub INDEKS/DOPASUJ, aby pobrać szczegóły pracownika przy użyciu jego identyfikatora pracownika.
Uwaga: ta technika zadziała tylko w przypadku liczbowego zestawu danych. Jeśli masz identyfikatory pracowników, takie jak A1, A2, A3 i tak dalej, to są one tekstowe i nie zmienią się po zastosowaniu niestandardowego formatu, jak pokazano powyżej.
Dodaj wiodące zera za pomocą funkcji REPT i LEN
Kiedy użyć: Gdy masz zestaw danych, który jest numeryczny/alfanumeryczny i chcesz, aby wynik był tekstem.
Wadą korzystania z funkcji TEKST było to, że działała tylko z danymi liczbowymi. Ale jeśli masz alfanumeryczny zestaw danych (powiedzmy A1, A2, A3 itd.), funkcja TEKST nie powiedzie się.
W takich przypadkach wystarczy kombinacja funkcji REPT i LEN.
Oto jak to zrobić:
- Jeśli masz liczby w kolumnie A (powiedzmy z A2:A100), wybierz B2:B100 i wprowadź następującą formułę:
=POWT(0,5-DŁ(A2))&A2 - Naciśnij klawisze Control + Enter, aby zastosować formułę do wszystkich zaznaczonych komórek.
Spowoduje to, że wszystkie liczby/ciągi będą miały 5 znaków z wiodącymi zerami tam, gdzie jest to potrzebne.
Oto jak działa ta formuła:
- LEN(A2) podaje długość ciągu/liczb w komórce.
- =REPT(0,5-LEN(A2)) da liczbę 0, którą należy dodać. Tutaj użyłem 5 w formule, ponieważ była to maksymalna długość ciągu/liczb w moim zestawie danych. Możesz to zmienić zgodnie ze swoimi danymi.
- =REPT(0,5-LEN(A2))&A2 po prostu doda liczbę zer do wartości komórki. Na przykład, jeśli wartość w komórce to 123, zwróci to 00123.
Dodaj wiodące zera za pomocą funkcji niestandardowej (VBA)
Jeśli dodawanie wiodących zer w programie Excel jest czymś, co musisz robić dość często, dobrym pomysłem jest użycie funkcji niestandardowej.
Oto kod VBA, który utworzy prostą funkcję dodawania wiodących zer:
'Kod autorstwa Sumit Bansal z http://trumpexcel.com Funkcja AddLeadingZeroes(ref As Range, Length As Integer) Dim i As Integer Dim Wynik As String Dim StrLen As Integer StrLen = Len(ref) For i = 1 To Length If i <= StrLen Wtedy Wynik = Wynik & Środek(ref, i, 1) Else Wynik = "0" & Wynik Koniec Jeśli Dalej i AddLeadingZeroes = Wynik Koniec Funkcja
Po prostu dodaj ten kod do okna kodu modułu, a będziesz mógł go używać tak jak każdej innej funkcji arkusza.
Lub utwórz dla niego dodatek i udostępnij go współpracownikom.
