Formatowanie niestandardowe w Excelu umożliwia wyświetlenie zawartości komórek w praktycznie dowolny sposób. Jest to bardzo rozbudowane narzędzie, które warto poznać, zwłaszcza kiedy tworzysz skoroszyty dla innych użytkowników Excela. Wiele osób nie wykorzystuje pełnych możliwości tego formatowania. Postaram się jednak maksymalnie je uprościć i opiszę tylko najważniejsze kody i możliwości.
Jak zapewne wiesz, do każdej komórki w Excelu możesz przypisać różnego rodzaju formatowanie. Po zaznaczenie komórek, które chcesz sformatować i przejściu do okna Formatowanie komórek (CTRL+1) zobaczysz następujące formaty:
- Ogólne
- Liczbowe
- Walutowe
- Księgowe
- Data
- Czas
- Procentowa
- Ułamkowe
- Naukowe
- Tekstowe
- Specjalne
- Niestandardowe
Dzisiaj napiszę o tym ostatnim formatowaniu, czyli niestandardowym. Dzięki niemu możesz przed lub po liczbie wstawić dowolne znaki, ustawić z ilu cyfr mają składać się liczby, ustawić procenty, wstawić instrukcje warunkowe, zmienić kolory czcionki oraz wiele więcej.
Wystarczy poznać kilka specjalnych znaków oraz konstrukcję formatowania którą wpisujemy w polu Typ:
Poniżej tego pola znajdziesz listę przykładowych kodów formatowania, które możesz wykorzystać. Na początek warto zacząć od tego i modyfikować poszczególne znaki.
Główna struktura formatowania niestandardowego wygląda następująco:
format_liczby_dodatniej; format_liczby_ujemnej; format_zera; format_tekstu
Jak widzisz możesz zastosować różne formatowania w zależności od zawartości komórki. Poszczególne sekcje formatowania oddzielamy średnikami. Najpierw podajemy formatowanie dla liczby dodatnich, następnie dla ujemnej, dla zera, a na końcu dla tekstu.
Nie trzeba podawać tych wszystkich sekcji. Jeżeli wpiszemy tylko jeden kod formatowania (bez średników) to wtedy, to formatowanie zostanie zastosowane do wszystkich liczb. Jeśli podamy dwie sekcje kodów to pierwsza sekcja będzie obowiązywała dla liczb dodatnich oraz zera, natomiast druga sekcja będzie odnosiła się do liczb ujemnych. Żeby przejść do sekcji formatu tekstu bez podawania pozostałych formatów należy w pierwszej kolejności umieści trzy średniki. Możliwe jest także wprowadzenie bez średników znaku @, który oznacza symbol tekstu. Takie formatowanie zostanie zastosowane tylko w przypadku wartości tekstowych w komórkach.
Formatowanie liczb
W przypadku formatowania liczb musisz znać następujące znaki (źródło: https://support.office.com/pl-pl/article/Tworzenie-lub-usuwanie-niestandardowego-formatu-liczb-2d450d95-2630-43b8-bf06-ccee7cbe6864):
0 (zero) – Ten symbol zastępczy cyfry powoduje wyświetlanie zer nieznaczących, jeśli liczba ma mniej cyfr niż jest zer w formacie. Jeśli na przykład wpisana liczba 8,9 powinna być wyświetlana jako 8,90, należy użyć formatu #,00.
# (hash) – Ten symbol zastępczy cyfry działa w analogiczny sposób jak 0 (zero). Program Excel nie wyświetla jednak dodatkowych zer w przypadku, gdy wpisywana liczba zawiera mniej cyfr po dowolnej stronie separatora niż jest symboli # w formacie. Jeśli na przykład formatem niestandardowym jest #,##, a w komórce zostanie wpisana liczba 8,9, program wyświetli liczbę 8,9.
? – Ten symbol zastępczy cyfry działa w analogiczny sposób jak 0 (zero). Program Excel dodaje jednak znak spacji dla zer nieznaczących po obu stronach separatora dziesiętnego tak, aby separatory dziesiętne były wyrównane w kolumnie. Na przykład format niestandardowy 0,0? powoduje wyrównanie separatorów dziesiętnych liczb 8,9 i 88,99 w kolumnie.
, (przecinek) – Ten symbol zastępczy cyfry umożliwia wyświetlanie separatora dziesiętnego w liczbie.
% (procent) – Ten symbol zamienia liczbę na procenty, czyli mnoży liczbę razy 100 i dodaje symbol %.
Formatowanie tekstu
Dodanie tekstu do formatowania w dowolnej sekcji odbywa się poprzez podanie tego tekstu w cudzysłowie i dodaniu kodu formatowania. Np. dla kodu 0” szt.” nastąpi zaokrąglenie do liczb całkowitych i dodanie tekstu oznaczającego jednostkę sztuki. Natomiast zapis tego typu „Zysk: „0,00;”Strata: „0,00 dla dodatniej liczby (np. 33) wyświetli Zysk: 33,00 a dla ujemnej Strata: 33,00.
Możemy także dodawać teksty w czwartej sekcji, czyli tekstowej. Tutaj zawartość komórki pobieramy symbolem małpy @. Jeżeli np. zaznaczamy komórki w których znajdują się męskie imiona i w formatowaniu niestandardowym wprowadzimy „Pan „@ to dla każdej komórki otrzymamy imię poprzedzone tekstem Pan.
Określanie kolorów czcionki
Za pomocą formowania niestandardowego możemy również nadawać kolory wartościom w komórkach. Wystarczy odpowiedni kod formatowania poprzedzić nazwą koloru umieszczoną w nawiasach kwadratowych. Mamy do dyspozycji następujące kolory:
- [Czarny]
- [Zielony]
- [Biały]
- [Niebieski]
- [Amarantowy]
- [Żółty]
- [Błękitny]
- [Czerwony]
Rozbudowując nasz poprzedni przykład z liczbami możemy użyć następującego zapisu: [zielony]”Zysk: „0,00;[czerwony]”Strata: „0,00
W ten sposób liczby dodatnie poprzedzone tekstem Zysk będą zapisane kolorem zielonym a liczby ujemne kolorem czerwonym. Umożliwia to nam zastosowanie prostego formatowania warunkowego.
Formatowanie niestandardowe warunkowe
Oprócz tego, że za pomocą poszczególnych sekcji możemy wydzielić formatowanie dla liczb dodatnich, ujemnych, zera oraz tekstu to dodatkowo możemy zastosować proste instrukcje warunkowe. Wstawiamy je poprzez umieszczenie w nawiasach kwadratowych operatora porównania ( <, >, <=, >=, =). Dany warunek obowiązuje do całej sekcji w której go umieścimy. Np. taki zapis: [Zielony][>50]”Zysk: „0,00;[Czerwony][<50]”Strata: „0,00 wyświetli kolorem zielonym i tekstem Zysk wartości większe niż 50. Wartości mniejsze niż 50 zostaną wyświetlone kolorem czerwonym i tekstem Strata. W przypadku, gdy żaden z argumentów nie jest prawdziwy to zastosowane będzie formatowanie z trzeciej sekcji. Można powiedzieć, że w ten sposób zmieniamy punkt zerowej wartości. Dobrze oddaje to następujący przykład: [Zielony][>50]”Zysk: „0,00;[Czerwony][<20]”Strata: „0,00;[Niebieski]”Po kosztach: „0,00
W powyższym przykładzie dla liczb z przedziału 20-50 zostanie dodany tekst Po kosztach: w kolorze niebieskim.
Wyświetlanie dni, miesięcy i lat
Za pomocą formatowania niestandardowego możemy również formatować wartości daty i czasu. Zasady obowiązują takie same jak w przypadku formatowania za pomocą funkcji.
Poniżej znajduje się lista dostępnych znaków zaczerpnięta ze strony Office.com:
m – Wyświetlanie miesiąca w postaci liczby bez zera wiodącego.
mm – Wyświetlanie miesiąca w postaci liczby z zerem wiodącym, jeśli to konieczne.
mmm – Wyświetlanie miesiąca w postaci skrótu (sty – gru).
mmmm – Wyświetlanie pełnej nazwy miesiąca (styczeń – grudzień).
mmmmm – Wyświetlanie miesiąca w postaci skrótu jednoliterowego (s – g).
d – Wyświetlanie dnia w postaci liczby bez zera wiodącego.
dd – Wyświetlanie dnia w postaci liczby z zerem wiodącym, jeśli to konieczne.
ddd – Wyświetlanie dnia w postaci skrótu (Pn – N).
dddd – Wyświetlanie pełnej nazwy dnia (poniedziałek – niedziela).
rr – Wyświetlanie roku w postaci liczby dwucyfrowej.
rrrr – Wyświetlanie roku w postaci liczby czterocyfrowej.
Wyświetlanie godzin, minut i sekund
d – Wyświetlanie godziny jako liczby bez zera wiodącego.
[g] – Wyświetlanie czasu w godzinach, jaki upłynął. Używając formuły zwracającej wartość czasu, w której liczba godzin przekracza 24, należy zastosować format liczb podobny do formatu [g]:mm:ss.
gg – Wyświetlanie godziny w postaci liczby z zerem wiodącym, jeśli to konieczne. Jeżeli format zawiera symbole AM lub PM, godzina będzie wyświetlana w formacie 12-godzinnym. W przeciwnym razie godzina będzie wyświetlana w formacie 24-godzinnym.
m – Wyświetlanie minut w postaci liczby bez zera wiodącego. UWAGA: Kod m lub mm musi występować bezpośrednio po kodzie g lub gg albo występować bezpośrednio przed kodem ss. W przeciwnym razie program Excel wyświetli miesiąc zamiast minut.
[m] – Wyświetlanie czasu w minutach, jaki upłynął. Używając formuły zwracającej wartość czasu, w której liczba minut przekracza 60, należy zastosować format liczb podobny do formatu [mm]:ss.
mm – Wyświetla minuty w postaci liczby z zerem wiodącym, jeśli to konieczne. UWAGA: Kod m lub mm musi występować bezpośrednio po kodzie g lub gg albo występować bezpośrednio przed kodem ss. W przeciwnym razie program Excel wyświetli miesiąc zamiast minut.
d – Wyświetlanie sekund w postaci liczby bez zera wiodącego.
[s] – Wyświetla czasu w sekundach, jaki upłynął. Używając formuły zwracającej wartość czasu, w której liczba sekund przekracza 60, należy zastosować format liczb podobny do formatu [ss].
ss – Wyświetlanie sekund w postaci liczby z zerem wiodącym, jeśli to konieczne. Jeżeli mają być wyświetlane ułamki sekund, należy użyć formatu liczb podobnego do formatu g:mm:ss,00.
Jeżeli masz pytania dotyczące formatowania niestandardowego to napisz je w komentarzu. Możesz także udostępnić tą stronę za pomocą przycisków Social Media. Mam nadzieję, że zostaniemy w kontakcie.
Drukuj do PDF