W tym artykule nauczymy się Różne sposoby dodawania zer (0s) z przodu w programie Excel.
Scenariusz:
Dodanie zera przed liczbą w programie Excel. Domyślny program Excel nie umieszcza zer przed liczbą w komórce. Czy kiedykolwiek próbowałeś wprowadzić do Excela jakieś dane, takie jak 000123? Prawdopodobnie szybko zauważysz, że Excel automatycznie usunie wiodące zera z numeru wejściowego. Może to być naprawdę denerwujące, jeśli chcesz mieć te wiodące zera w danych i nie wiesz, jak sprawić, by program Excel je zachował. Na szczęście istnieje kilka sposobów na uzupełnianie liczb zerami na początku. W tym artykule wyjaśnię różne sposoby dodawania lub utrzymywania zer wiodących do liczb.
Różne funkcje, aby dodać zero (0) z przodu
Jak wiemy, funkcja TEKST jest najczęściej używaną funkcją dodawania 0 na początku, ale tutaj wyjaśnię więcej sposobów dodawania zer (0s) do liczb.
- funkcja TEKST
- PRAWA funkcja
- Funkcja PODSTAWA
- Dodaj 0 w wartościach tabeli przestawnej
Formatuj jako tekst
Jednym z rozwiązań jest konwersja formatu liczby na tekst, ponieważ jeśli wprowadzimy 00xyz, program Excel uzna tę wartość za tekst i zachowa 0 na początku. Istnieją różne sposoby konwertowania komórki liczbowej na format tekstowy.
- Korzystanie z formatu niestandardowego
- Korzystanie z funkcji TEKST
- Używanie apostrofu ( ' ) na początku wartości podczas wprowadzania
Format niestandardowy
Rozumiemy te metody jeden po drugim. Użycie niestandardowego formatu do zmiany formatu to najprostszy sposób na zmianę już istniejących numerów w zakresie. Po prostu wybierz zakres i użyj skrótu Ctrl + 1 lub wybierz Więcej formatu liczb z rozwijanej listy na karcie Strona główna pokazująca Ogólne.
Zmień format z Ogólne na Tekst.
- Wybierz zakres komórek, w których chcesz wprowadzić wiodące zera.
- Przejdź do zakładki Strona główna.
- W sekcji Liczby kliknij menu rozwijane Format.
- Wybierz Tekst z opcji formatu.
Teraz, jeśli spróbujesz wprowadzić liczby z wiodącymi zerami, nie znikną, ponieważ są wprowadzane jako wartości tekstowe zamiast liczb. Użytkownicy programu Excel mogą dodać niestandardowe formatowanie do formatowania liczb z wiodącymi zerami.
Wygląda na to, że mają tylko wiodące zera. Podstawowe dane nie zostaną zmienione na tekst z dodanymi zerami.
Dodaj niestandardowy format, aby pokazać wiodące zera.
- Wybierz zakres komórek, do których chcesz dodać wiodące zera, i otwórz okno dialogowe Formatowanie komórek.
- Kliknij prawym przyciskiem myszy i wybierz Formatuj komórki.
- Użyj skrótu klawiaturowego Ctrl + 1.
- Przejdź do zakładki Numer.
- Wybierz Niestandardowe z opcji kategorii.
- Dodaj nowy format niestandardowy w polu Typ. Jeśli chcesz, aby całkowita liczba cyfr wraz z wiodącymi zerami wynosiła 6, dodaj 000000 jako format niestandardowy.
- Naciśnij przycisk OK.
TEKST Funkcja
Funkcja TEKST pozwoli Ci zastosować niestandardowe formatowanie do dowolnych danych liczbowych znajdujących się już w arkuszu kalkulacyjnym.
= TEKST (Wartość, Format)
- Wartość - jest to wartość, którą chcesz przekonwertować na tekst i zastosować formatowanie.
- Format - to formatowanie, które należy zastosować.
= TEKST ( B3, "000000" )
Jeśli chcesz dodać zera do liczby w komórce B3, aby całkowita liczba cyfr wynosiła 6, możesz użyć powyższej formuły.
Używanie apostrofu ( ' ) na początku
Możesz zmusić program Excel do wprowadzenia liczby jako tekstu, używając wiodącego apostrofu.
Oznacza to, że będziesz mógł zachować te zera z przodu podczas wprowadzania danych.
Ta metoda jest szybka i łatwa podczas wprowadzania danych. Wystarczy wpisać znak ' przed cyframi. To powie Excelowi, że dane mają być tekstem, a nie liczbą.
Po naciśnięciu klawisza Enter początkowe zera pozostaną widoczne w arkuszu. Znak ' nie będzie widoczny w arkuszu, ale nadal tam jest i można go zobaczyć na pasku formuły, gdy aktywny kursor komórki znajduje się na komórce.
PRAWA funkcja
Innym sposobem na umieszczenie zer na początku w formule jest użycie funkcji PRAWO. Możesz połączyć ciąg zer z liczbą, a następnie odciąć dodatki za pomocą funkcji PRAWO.
Funkcja PRAWY wyodrębni N znaków z prawej strony z wartości tekstowej.
= PRAWO ( Tekst, [Liczba])
- Tekst - jest to tekst, z którego chcesz wyodrębnić znaki.
- Numer (Opcjonalny)- Jest to liczba znaków do wyodrębnienia z tekstu. Jeśli ten argument nie zostanie wprowadzony, wyodrębniony zostanie tylko pierwszy znak.
= PRAWO ( "000000" & B3, 6 )
Powyższa formuła połączy kilka zer z początkiem liczby w komórce B3, a następnie zwróci 6 znaków z prawej strony, w wyniku czego zostaną wprowadzone zera wiodące.
PODSTAWA Funkcja
Zauważysz, że ten artykuł opisuje 9 sposobów dodawania wiodących zer, ale mój film na YouTube pokazuje tylko 8 sposobów.
To dlatego, że nie wiedziałem, że możesz użyć funkcji BASE, aby dodać wiodące zera, dopóki ktoś nie wspomni o tym w komentarzach wideo.
Funkcja PODSTAWA umożliwia konwersję liczby na reprezentację tekstową o zadanej podstawie.
Liczby, których zwykle używasz, mają podstawę 10, ale możesz użyć tej funkcji, aby przekonwertować liczbę o podstawie 10 na reprezentację o podstawie 2 (binarną).
= PODSTAWA ( liczba, podstawa, [mindługość])
- Liczba - jest to liczba, którą chcesz przekonwertować na wartość tekstową w innej podstawie.
- Podstawa - jest to podstawa, na którą chcesz przeliczyć wartość.
- Minimalna długość (Opcjonalny) - jest to minimalna długość znaków przekonwertowanej wartości tekstowej.
Użyj formuły
| =BAZA(B3,10,6) |
Powyższa formuła przekonwertuje liczbę w komórce B3 na liczbę o podstawie 10 (jest to już liczba o podstawie 10, ale zachowa ją jako podstawę 10) i przekonwertuje ją na wartość tekstową z co najmniej 6 znakami. Jeśli przekonwertowana wartość ma mniej niż 6 znaków, zostanie uzupełniona zerami, aby uzyskać co najmniej 6 znaków.
Zdecydowanie fajne nowe zastosowanie dla funkcji, której inaczej nigdy nie używam.
Dodaj zero (0) tylko przed liczbami, a nie na wartościach tekstowych
Tutaj rozważymy scenariusz, który obejmuje zarówno wartości tekstowe, jak i liczbowe na tej samej liście. Teraz Excel traktuje każdą komórkę inaczej pod względem formatu. Teraz, jak poinformować program Excel, że jeśli komórka ma wartość liczbową, dodaj 0 na początku lub jeśli komórka ma tekst lub inną wartość, pozostaw ją bez zmian.
W tym celu użyjemy kombinacji dwóch funkcji JEŻELI i funkcji CZY.LICZBA. Tutaj mamy kilka wartości do wypróbowania.
Formuła generyczna trwa jak
=JEŻELI(CZY.LICZBA(odn_komórki);"0"&odn_komórki;odn_komórki)
Tutaj
cell_ref : odwołanie do odpowiedniej komórki
Użyj formuły w komórce
| =JEŻELI(CZY.LICZBA(F3);"0"&F3; F3) |
Objaśnienie: Funkcja CZY.LICZBA sprawdza komórkę pod kątem liczby. Funkcja IF sprawdza, czy komórka ma liczbę, a następnie dodaje zero przed liczbą, w przeciwnym razie zwraca tylko wartość komórki.
Jak widać na powyższym obrazku, przed liczbą dodawane jest zero (0). Sprawdźmy, czy ta funkcja działa na innych wartościach. Skopiuj formułę do innych komórek, zaznacz komórki biorąc pierwszą komórkę, w której formuła jest już zastosowana, użyj klawisza skrótu Ctrl+D
dopełni wartość zerami, aby uzyskać co najmniej jedno zero przed znakami. Zdecydowanie fajne nowe zastosowanie dla funkcji, której inaczej nigdy nie używam.
Kolumna obliczeniowa Power Pivot
Istnieje inna opcja dodawania zer do tabeli przestawnej. Możesz dodać je do kolumny obliczeniowej w dodatku Power Pivot. W ten sposób możesz użyć nowej kolumny w obszarze Filtr, Wiersze lub Kolumna tabeli przestawnej.
= FORMAT ( Liczby[Liczba], "000000" )
W dodatku Power Pivot możesz dodać nową kolumnę i użyć funkcji FORMAT, aby utworzyć wiodące zera w kolumnie z formułą taką jak powyżej. Kolumna obliczeniowa oblicza wartość dla każdego wiersza, więc nie ma potrzeby zawijania funkcji wewnątrz komórki. Teraz możesz używać tej nowej kolumny w obszarze Wiersze tabeli przestawnej, tak jak każdej innej kolumny danych.
Streszczenie
To może być frustrujące, gdy wszystkie twoje zera znikają, gdy nie wiemy, dlaczego tak się dzieje i jak temu zapobiec. Jak widać, istnieje wiele sposobów, aby upewnić się, że wszystkie te zera pozostaną w Twoich danych. Niezależnie od Twoich wymagań, z pewnością jest dla Ciebie dobre rozwiązanie.
Oto wszystkie notatki obserwacyjne przy użyciu wzoru w Excelu
Uwagi:
- Formuła może być stosowana zarówno do tekstów, jak i liczb.
- Tekst Znajdź i zamień należy posortować w tabeli. Ponieważ używamy indeksów do znajdowania i zastępowania, upewnij się, że tekst „znajdź” ma ten sam indeks, co tekst „zamień”. Lepiej jest mieć obie listy obok siebie, tak jak w drugim przykładzie.
- Formuła ogólna ma dwie funkcje SUBSTITUTE. Będziesz potrzebować tylu funkcji zastępczych, ile masz zamienników do wykonania.
- symbole wieloznaczne pomagają w wyodrębnianiu wartości z podciągów.
- Funkcja obsługuje operatory logiczne, takie jak , , = ale są one używane przy użyciu znaku cudzysłowu ( " ) z argumentami. Ale jeśli używasz odwołania do komórki dla kryteriów, cytaty można zignorować.
- Argument kryteriów powinien używać cudzysłowu (") dla tekstów (jak "Gillen" ) lub liczb ( jak ">50" ).
Mam nadzieję, że ten artykuł o różnych sposobach dodawania zer (0s) z przodu w programie Excel jest objaśniający. Więcej artykułów na temat obliczania wartości i powiązanych formuł programu Excel znajdziesz tutaj. Jeśli podobały Ci się nasze blogi, podziel się nimi ze znajomymi na Facebooku. A także możesz śledzić nas na Twitterze i Facebooku. Chcielibyśmy usłyszeć od Ciebie, daj nam znać, jak możemy ulepszyć, uzupełnić lub unowocześnić naszą pracę i uczynić ją lepszą dla Ciebie. Napisz do nas na e-mail.
Excel REPLACE vs funkcja SUBSTITUTE: Funkcje REPLACE i SUBSTITUTE są najbardziej niezrozumianymi funkcjami. Aby znaleźć i zamienić dany tekst, używamy funkcji SUBSTITUTE. Gdzie REPLACE służy do zastępowania liczby znaków w ciągu.
Zamień tekst na końcu ciągu, zaczynając od pozycji zmiennej: Aby zastąpić tekst z końca ciągu, używamy funkcji REPLACE. Funkcja REPLACE używa pozycji tekstu w ciągu do zamiany.
Jak sprawdzić, czy ciąg zawiera jeden z wielu tekstów w programie Excel?: Aby sprawdzić, czy ciąg zawiera którykolwiek z wielu tekstów, używamy tej formuły. Używamy funkcji SUMA, aby zsumować wszystkie dopasowania, a następnie wykonujemy logikę, aby sprawdzić, czy ciąg zawiera którykolwiek z wielu ciągów.
Policz komórki zawierające określony tekst: Prosta funkcja LICZ.JEŻELI zdziała magię. Aby policzyć liczbę wielu komórek, które zawierają dany ciąg, używamy operatora wieloznacznego z funkcją LICZ.JEŻELI.
Popularne artykuły :
50 skrótów Excela, które zwiększą Twoją produktywność : Szybsze wykonywanie zadań w programie Excel. Te skróty pomogą Ci zwiększyć wydajność pracy w programie Excel.
Jak korzystać z funkcji WYSZUKAJ.PIONOWO w programie Excel : Jest to jedna z najczęściej używanych i popularnych funkcji programu Excel, która służy do wyszukiwania wartości z różnych zakresów i arkuszy.
Jak korzystać z funkcji IF w programie Excel? : Instrukcja IF w programie Excel sprawdza warunek i zwraca określoną wartość, jeśli warunek jest PRAWDA, lub zwraca inną określoną wartość, jeśli jest FAŁSZ.
Jak korzystać z funkcji SUMA.JEŻELI w programie Excel : To kolejna ważna funkcja deski rozdzielczej. Pomaga to zsumować wartości w określonych warunkach.
Jak korzystać z funkcji LICZ.JEŻELI w programie Excel : Policz wartości z warunkami za pomocą tej niesamowitej funkcji. Nie musisz filtrować danych, aby zliczyć określone wartości. Funkcja Countif jest niezbędna do przygotowania deski rozdzielczej.