7 niesamowitych sztuczek dotyczących niestandardowego formatu liczb w programie Excel (musisz wiedzieć)

Formatowanie liczb niestandardowych w programie Excel to ubranie dla danych w komórkach programu Excel. Możesz je ubrać tak, jak chcesz. Wszystko, czego potrzebujesz, to trochę wiedzy na temat działania niestandardowego formatu liczb programu Excel.

Dzięki niestandardowemu formatowaniu liczb w programie Excel możesz zmienić sposób wyświetlania wartości w komórkach, ale jednocześnie zachowując nienaruszoną oryginalną wartość.

Na przykład mogę wyświetlić wartość daty w różnych formatach daty, podczas gdy wartość bazowa (liczba reprezentująca datę) pozostanie taka sama.

W tym samouczku omówię wszystko na temat niestandardowego formatu liczb w programie Excel, a także pokażę kilka naprawdę przydatnych przykładów, których możesz użyć w codziennej pracy.

Więc zacznijmy!

Konstrukcja niestandardowego formatu liczb Excel

Zanim pokażę Ci, jak to jest, pokrótce wyjaśnię, jak działa niestandardowe formatowanie liczb w programie Excel.

Zgodnie z projektem program Excel może akceptować następujące cztery typy danych w komórce:

• Liczba dodatnia
• liczba ujemna
• 0
• Ciągi tekstowe (może to obejmować zarówno ciągi tekstowe, jak i alfanumeryczne)

Innym typem danych, który może zaakceptować program Excel, są daty, ale ponieważ wszystkie wartości daty i godziny są przechowywane w programie Excel jako liczby, zostaną one uwzględnione jako część liczby dodatniej.

W przypadku dowolnej komórki w arkuszu w programie Excel możesz określić sposób wyświetlania każdego z tych typów danych w komórce.

A poniżej znajduje się składnia, w której możesz określić ten format.

;;;

Zauważ, że wszystkie te są oddzielone średnikami (;).

Wszystko, co wprowadzisz w komórce w programie Excel, należy do jednej z tych czterech kategorii, a zatem można określić dla tego niestandardowy format.

Jeśli wymieniasz tylko:

• Jeden format: Ma zastosowanie do wszystkich czterech sekcji. Na przykład, jeśli napiszesz po prostu Ogólne, zostanie to zastosowane we wszystkich czterech sekcjach.
• Dwa formaty: Pierwsza jest stosowana do liczb dodatnich i zer, a druga do liczb ujemnych. Format tekstu domyślnie staje się Ogólny.
• Trzy formaty: Pierwsza jest stosowana do liczb dodatnich, druga jest stosowana do liczb ujemnych, trzecia jest stosowana do zera, a tekst znika, ponieważ nic nie jest określone dla tekstu.

Jeśli chcesz dowiedzieć się więcej o niestandardowym formatowaniu liczb, gorąco polecam sekcję Pomoc Office.

Jak to działa i gdzie wprowadzić te formaty, omówiono w dalszej części tego samouczka. Na razie pamiętaj, że jest to składnia formatu dowolnej komórki

Jak działa niestandardowy format liczb w programie Excel

Następnie otwierasz nowy skoroszyt programu Excel, domyślnie wszystkie komórki we wszystkich arkuszach w tym skoroszycie mają format Ogólne, gdzie:

• Liczby dodatnie są pokazane tak, jak są (wyrównane do lewej)
• Liczby ujemne są pokazane ze znakiem minus (i wyrównane do lewej)
• Zero jest pokazany jako 0 (i wyrównany do lewej)
• Tekst jest pokazany tak, jak jest (wyrównany do prawej)

A jeśli wprowadzisz datę, jest ona wyświetlana na podstawie ustawień regionalnych (w formacie DD-MM-RRRR lub MM-DD-RRRR).

Tak więc domyślnie formatowanie liczb w komórce to:

Ogólne;Ogólne;Ogólne;Ogólne

Gdzie każdy typ danych jest sformatowany jako Ogólne (i postępuj zgodnie z regułami, o których wspomniałem powyżej, w zależności od tego, czy jest to liczba dodatnia, ujemna, 0 czy tekst)

Teraz ustawienie domyślne jest w porządku, ale jak zmienić format. Na przykład, jeśli chcę, aby moje liczby ujemne były wyświetlane w kolorze czerwonym lub chcę, aby moje daty były wyświetlane w określonym formacie, jak to zrobić.

Korzystanie z rozwijanego menu Format na Wstążce

Niektóre z często używanych formatów liczb można znaleźć na karcie Narzędzia główne w grupie Liczba.

Jest to menu rozwijane, które pokazuje wiele powszechnie używanych formatów, które można zastosować do komórki, po prostu wybierając komórkę, a następnie wybierając format z tej listy.

Na przykład, jeśli chcesz przekonwertować liczby na wartości procentowe lub przekonwertować daty na format krótkiej daty lub długi format daty, możesz to łatwo zrobić, korzystając z opcji na liście rozwijanej.

Korzystanie z okna dialogowego Formatuj komórki

Okna dialogowe formatowania komórek, w których uzyskujesz maksymalną elastyczność, jeśli chodzi o stosowanie formatów do komórki w programie Excel.

Ma już wiele gotowych formatów, których możesz użyć, lub jeśli chcesz stworzyć własny niestandardowy format liczb, możesz to również zrobić.

Oto jak otworzyć okno dialogowe Formatowanie komórek;

1. Kliknij kartę Strona główna
2. W grupie Numer kliknij ikonę uruchamiania okna dialogowego (mała pochylona strzałka w prawym dolnym rogu grupy).

Spowoduje to otwarcie okna dialogowego Formatowanie komórek. W nim znajdziesz wszystkie formaty w zakładce Numer (wraz z opcją tworzenia niestandardowych formatów komórek)

Możesz także użyć następującego skrótu klawiaturowego, aby otworzyć okno dialogowe formatowania komórek:

CONTROL + 1 (wszystkie klawisze Control, a następnie naciśnij klawisz 1).

Jeśli używasz Maca, możesz użyć CMD + 1

Teraz przedstawimy kilka praktycznych przykładów, w których stworzymy własne niestandardowe formaty liczb.

Przykłady użycia niestandardowego formatowania liczb w programie Excel

Teraz przedstawimy kilka praktycznych przykładów, w których stworzymy własne niestandardowe formaty liczb.

Ukryj wartość komórki (lub Ukryj wszystkie wartości w zakresie komórek)

Czasami możesz potrzebować ukryć zawartość komórki (bez ukrywania wiersza lub kolumny, w której znajduje się komórka).

Praktycznym przypadkiem jest to, że tworzysz pulpit nawigacyjny programu Excel i istnieje kilka wartości, których używasz na pulpicie nawigacyjnym, ale nie chcesz, aby użytkownicy je widzieli. więc możesz po prostu ukryć te wartości

Załóżmy, że masz zestaw danych, jak pokazano poniżej i chcesz ukryć wszystkie wartości liczbowe:

Oto jak to zrobić, używając niestandardowego formatowania w programie Excel:

1. Wybierz komórkę lub zakres komórek, dla których chcesz ukryć zawartość komórki
2. Otwórz okno dialogowe Formatowanie komórek (skrót klawiaturowy Control + 1)
3. Na karcie Numer kliknij opcję Niestandardowe
4. Wprowadź następujący format w polu Typ:

   ;;;

Jak to działa?

Format dla czterech różnych typów typów danych jest podzielony średnikami w następującym formacie:

;;;

W tej technice usunęliśmy format i określiliśmy tylko średnik, co oznacza, że ​​nie chcemy żadnego formatowania komórek. Jest to wskazówka dla programu Excel, aby ukryć dowolną zawartość znajdującą się w komórce.

Zauważ, że chociaż ukrywa to zawartość komórki, nadal tam jest. więc jeśli masz ukryte liczby, nadal możesz ich używać w obliczeniach (co sprawia, że ​​jest to świetna sztuczka w przypadku pulpitów nawigacyjnych i raportów programu Excel).

Wyświetlaj liczby ujemne w kolorze czerwonym

Domyślnie liczby ujemne są wyświetlane ze znakiem minus (lub w nawiasach w niektórych ustawieniach regionalnych).

Możesz użyć niestandardowego formatowania liczb, aby zmienić kolor liczb ujemnych i wyświetlić je na czerwono.

Załóżmy, że masz zestaw danych, jak pokazano poniżej i chcesz podświetlić wszystkie ujemne wartości na czerwono.

Poniżej znajdują się kroki, aby to zrobić:

1. Wybierz zakres komórek, w których chcesz wyświetlać liczby ujemne na czerwono
2. Otwórz okno dialogowe Formatowanie komórek (skrót klawiaturowy Control + 1)
3. Na karcie Numer kliknij opcję Niestandardowe
4. Wprowadź następujący format w polu Typ:

Ogólne;[Czerwony]-Generał

Powyższy format sprawi, że Twoje komórki z wartościami ujemnymi będą wyświetlane w kolorze czerwonym.

Jak to działa?

Gdy określisz tylko dwa formaty jako niestandardowy format liczb, pierwszy z nich jest uwzględniany dla liczb dodatnich i 0, a drugi jest uwzględniany dla liczb ujemnych (następna wartość domyślnie staje się formatem ogólnym).

W powyższym formacie, którego użyliśmy, określiliśmy w nawiasach kwadratowych kolor, który ma przyjąć ten format.

Tak więc, podczas gdy liczby ujemne przyjmują ogólny format, są one wyświetlane w kolorze czerwonym ze znakiem minus.

W przypadku, gdy chcesz, aby liczby ujemne zatrzymywały się na czerwono w nawiasach (zamiast znaku minus), możesz użyć poniższego formatu:

Ogólne;[Czerwony](Ogólny)

Dodaj tekst do liczb (np. Miliony/miliardy)

Jedną z niesamowitych rzeczy w niestandardowym formatowaniu liczb jest to, że możesz dodać do liczby przedrostek lub sufiks, zachowując jednocześnie oryginalny numer w komórce.

Na przykład mogę wyświetlić „100” jako „100 milionów dolarów”, jednocześnie mając oryginalną wartość w komórce, abym mógł użyć jej w obliczeniach.

Załóżmy, że masz zestaw danych, jak pokazano poniżej i chcesz pokazać te liczby ze znakiem dolara i słowem milion przed nim.

Poniżej znajdują się kroki, aby to zrobić:

1. Wybierz zbiór danych, w którym chcesz dodać tekst do liczb
2. Otwórz okno dialogowe Formatowanie komórek (skrót klawiaturowy Control + 1)
3. Na karcie Numer kliknij opcję Niestandardowe
4. Wprowadź następujący format w polu Typ:

$Ogólne "Milion"

Pamiętaj, że ten niestandardowy format liczb dotyczy tylko liczb. Jeśli wprowadzisz tekst w tej samej komórce, będzie wyglądał tak, jak jest.

Jak to działa?

W tym przykładzie do ogólnego formatu dodałem słowo milion w podwójnych cudzysłowach. wszystko, co dodaję w podwójnych cudzysłowach do formatu, jest wyświetlane tak, jak jest w komórce.

Dodałem również znak dolara przed ogólnym formatem, aby wszystkie moje liczby również pokazywały znak dolara przed liczbą.

Uwaga: Jeśli zastanawiasz się, dlaczego znak dolara nie znajduje się w cudzysłowie, podczas gdy słowo Milion jest w cudzysłowie, dzieje się tak dlatego, że program Excel rozpoznaje dolara jako symbol, który jest często używany w niestandardowym formatowaniu liczb i nie wymaga ciebie aby dodać do niego podwójne cudzysłowy. Możesz iść dalej i dodać podwójne cudzysłowy do znaku dolara, ale gdy zamkniesz okno dialogowe formatowania komórek i otworzysz je ponownie, zauważysz, że program Excel go usunął

Numery i tekst przebrań

Dzięki niestandardowemu formatowaniu liczb możesz ocenić maksymalnie dwa warunki i na podstawie tych warunków możesz zastosować format do komórki.

Załóżmy na przykład, że masz listę wyników dla niektórych uczniów i chcesz wyświetlić tekst Zaliczony lub Niezliczony w oparciu o wynik (gdzie każdy wynik poniżej 35 powinien wskazywać „Niepowodzenie”, w przeciwnym razie powinien wyświetlać się „Zaliczony”).

Poniżej znajdują się kroki, aby wyświetlić wyniki jako pozytywne lub negatywne zamiast wartości przy użyciu niestandardowego formatu liczb:

1. Wybierz zbiór danych, w którym masz wyniki
2. Otwórz okno dialogowe Formatowanie komórek (skrót klawiaturowy Control + 1)
3. Na karcie Numer kliknij opcję Niestandardowe
4. Wprowadź następujący format w polu Typ:

   [<35]"Niepowodzenie";"Zaliczenie"

Poniżej przedstawiamy, jak Twoje dane będą wyglądać po zastosowaniu powyższego formatu.

Zauważ, że nie zmienia to wartości w komórkach. Komórka nadal ma wyniki w wartościach liczbowych, wyświetlając „Powodzenie” lub „Niepowodzenie” w zależności od wartości komórki.

Jak to działa?

W tym przykładzie warunek należy określić w nawiasach kwadratowych. niestandardowe formatowanie liczb następnie ocenia wartość w komórce i odpowiednio stosuje format.

Jeśli więc wartość w komórce jest mniejsza niż 35, liczba jest ukryta, a zamiast tego wyświetlany jest tekst „Niepowodzenie”, dodając wszystkie inne przypadki, wyświetlane jest „Zaliczenie”.

Podobnie, jeśli chcesz przekonwertować zakres komórek, które mają zera i jedynki na PRAWDA i FAŁSZ, możesz użyć poniższego formatu:

[=0]"FAŁSZ";[=1]"PRAWDA"

Pamiętaj, że w niestandardowym formatowaniu liczb możesz użyć tylko dwóch warunków. Jeśli spróbujesz użyć więcej niż dwóch warunków, spowoduje to wyświetlenie monitu, który poinformuje Cię, że nie może zaakceptować tego formatu.

Ukryj tekst, ale wyświetl numery

Czasami, gdy pobierasz dane z baz danych, ponieważ są tam puste, są wypełniane wartościami np. Niedostępne lub NA lub -

Dzięki niestandardowemu formatowaniu liczb możesz ukryć wszystkie wartości tekstowe, zachowując liczby bez zmian. zapewnia to również, że oryginalne dane są nienaruszone (ponieważ nie usuwasz wartości tekstowej, po prostu ją ukrywasz)

Załóżmy, że masz zestaw danych, jak pokazano poniżej, w którym chcesz ukryć wszystkie wartości tekstowe:

Poniżej znajdują się kroki, aby to zrobić:

1. Wybierz zbiór danych, w którym masz dane
2. Otwórz okno dialogowe Formatowanie komórek (skrót klawiaturowy Control + 1)
3. Na karcie Numer kliknij opcję Niestandardowe
4. Wprowadź następujący format w polu Typ:

Ogólny; -Ogólny; Ogólny;

Zauważ, że użyłem formatu ogólnego dla liczb. Możesz wybrać dowolny format (np. 0, 0.#, #0.0%). Należy również zauważyć, że przed drugim formatem ogólnym znajduje się znak ujemny (-), ponieważ reprezentuje on format liczb ujemnych.

Jak to działa?

W tym przykładzie, ponieważ chcieliśmy ukryć wszystkie wartości tekstowe, po prostu nie określiliśmy dla nich formatu.

A ponieważ określiliśmy format dla pozostałych trzech typów danych (liczba dodatnia, liczba ujemna i zero), program Excel zrozumiał, że celowo pozostawiliśmy pusty format wartości tekstowej i nie powinien niczego pokazywać, jeśli komórka zawiera tekst strunowy.

Wyświetl liczby jako wartości procentowe (%)

Dzięki niestandardowemu formatowaniu liczb możesz także zmieniać liczby i wyświetlać je w procentach.

Załóżmy, że masz zestaw danych, jak pokazano poniżej, i chcesz przekonwertować te liczby, aby pokazać równoważną wartość procentową:

Poniżej znajdują się kroki, aby to zrobić:

1. Wybierz komórki, w których masz liczby (które chcesz przekonwertować na wartości procentowe)
2. Otwórz okno dialogowe Formatowanie komórek (skrót klawiaturowy Control + 1)
3. Na karcie Numer kliknij opcję Niestandardowe
4. Wprowadź następujący format w polu Typ:

0%

Spowoduje to zmianę liczb na ich wartości procentowe. Na przykład zmienia się od 0,11 do 11%.

Jeśli chcesz tylko przekonwertować liczbę na jej równoważną wartość procentową, lepszym sposobem na to byłoby użycie opcji procentowej w oknie dialogowym formatowania komórek.

Formatowanie niestandardowe może być przydatne, gdy chcesz mieć większą kontrolę nad sposobem wyświetlania wartości procentowej.

Na przykład, jeśli chcesz pokazać dodatkowy tekst z wartością procentową, wszystko, co chcesz pokazać ujemne wartości procentowe w kolorze czerwonym, lepiej jest użyć niestandardowego formatowania

Wyświetlaj liczby w innej jednostce (miliony jako miliardy lub gramy jako kilogramy)

Jeśli pracujesz z dużymi liczbami, możesz chcieć pokazać je w innej jednostce. na przykład, jeśli masz dane sprzedaży niektórych firm, możesz chcieć pokazać sprzedaż w milionach lub miliardach (przy zachowaniu oryginalnej wartości sprzedaży w komórkach)

Na szczęście możesz to łatwo zrobić dzięki niestandardowemu formatowaniu liczb.

Załóżmy, że masz zestaw danych, jak pokazano poniżej, i chcesz wyświetlić te wartości sprzedaży w milionach.

Poniżej znajdują się kroki, aby to zrobić:

1. Wybierz komórki, w których masz liczby
2. Otwórz okno dialogowe Formatowanie komórek (skrót klawiaturowy Control + 1)
3. Na karcie Numer kliknij opcję Niestandardowe
4. Wprowadź następujący format w polu Typ:

0.0,,

Powyższe dałoby wynik, jak pokazano poniżej (wartości w milionach w jednej cyfrze po przecinku):

Jak to działa?

W powyższym formacie zero przed przecinkiem oznacza, że ​​Excel powinien pokazywać całą liczbę (dokładnie tak, jak jest w komórce), a 0 po przecinku oznacza, że ​​powinien pokazywać tylko jedną cyfrę znaczącą po przecinku.

Więc 0.0 zawsze będzie liczbą z jedną cyfrą po przecinku.

Gdy dodasz przecinek po 0,0, obetnie on ostatnie trzy cyfry liczby. Na przykład 123456 stanie się 123,4 (ostatnie trzy cyfry są usuwane, a ponieważ musi pokazywać 1 cyfrę po przecinku, pokazuje 4)

Jeśli się nad tym zastanowisz, to jest to podobne do dzielenia liczby przez 1000. Jeśli więc chcesz pokazać liczbę w jednostce „miliony”, musisz dodać 2 przecinki, aby obcięła ostatnie 6 cyfr.

A jeśli chcesz również pokazać słowo milion lub alfabet M po liczbie, użyj poniższego formatu

0,0,, „M”