Wprowadzając nową wartość liczbową do komórki możesz zdecydować w jakim formacie ją wyświetlić. Excel posiada bardzo wiele wbudowanych formatów liczb. Jednakże czasami możesz stwierdzić, że żaden z nich nie spełnia pokładanych w nim oczekiwań. Wtedy jedynym rozwiązaniem jest stworzenie niestandardowego formatu liczbowego. Jak tego dokonać? O tym właśnie piszę w tym artykule.
Na wstępie chcę zaznaczyć, iż zdecydowałem się podzielić ten tekst na dwie części. W pierwszej, którą właśnie czytasz, wprowadzę Cię w tematykę formatowania niestandardowego oraz opiszę formaty liczbowe i walutowe. W drugiej, która ukaże się za tydzień, skupię się tylko na formatach daty i czasu.
Decyzja o podziale artykułu na dwie części podyktowana była przede wszystkim tym, że temat formatowania niestandardowego jest dość obszerny i nie chciałbym Cię od razu zasypywać ogromem informacji, a tym samym zniechęcać do dalszego czytania.
Tworzenie niestandardowych formatów liczbowych i walutowych
Zdecydowana większość użytkowników nie ma problemu z formatowaniem wartości umieszczonych w komórkach pod warunkiem, że wszystkie niezbędne polecenia znajdują się w grupie Liczba na karcie Narzędzia główne. Jeśli jednak wspomniane wartości należy sformatować w nietypowy sposób, to często pojawia się problem, ponieważ nie wiadomo jak to zrobić.
W celu zdefiniowania nowego niestandardowego formatu liczb należy wyświetlić okno dialogowe Formatowanie komórek używając do tego celu skrótu klawiaturowego Ctrl + 1 lub klikając przycisk Format liczb w grupie Liczba na karcie Narzędzia główne.
Następnie po kliknięciu kategorii Niestandardowe zostaje wyświetlona lista Typ zawierająca kody wszystkich wbudowanych formatów liczbowych, daty i czasu. Na końcu tej listy znajdziesz również formaty, które sam zdefiniujesz. Jeśli przyjrzysz się bliżej tym kodom to zauważysz, że niektóre z nich są dostępne w grupie Liczba.
Teraz możesz przeanalizować każdy z nich. Porównać ich składnię oraz sposób w jaki działają. Dla przykładu wybierając formatowanie walutowe w grupie Liczba na karcie Narzędzia główne Excel wprowadza do zaznaczonej komórki lub zakresu komórek następujący kod # ##0,00 zł.
Ten prosty w budowie kod definiuje jak będzie wyświetlana zawartość komórki – tysiące oddzielone od setek (# ##), wartość zaokrąglona do dwóch miejsc po przecinku (0,00), a na końcu symbol waluty (zł).
Zanim przystąpisz do tworzenia niestandardowych formatów liczbowych warto, abyś dokładnie zapoznał się co oznaczają poszczególne kody. Poniżej prezentuję ich pełną listę.
[minti_table style=”1″]
| Kod | Opis |
|---|---|
| 0 (zero) | Symbol zastępczy cyfry określający liczbę znaków, które mają się pojawić po każdej stronie separatora dziesiętnego (,). Przykładowo, używając formatu 0,000 wartość 1,75 zostanie wyświetlona jako 1,750. Format 000,00 wyświetli wartość 1,75 jako 001,75. Jeżeli wartość w komórce zawiera większą ilość znaków określających część dziesiętną aniżeli liczba zer umieszczona z prawej strony separatora dziesiętnego w formacie, to w takim przypadku wyświetlana wartość jest zaokrąglana. Dla przykładu używając formatu 0,00 wartość 1,567 zostanie wyświetlona jako 1,57, a w formacie 0,0 jako 1,6. |
| ? | Symbol zastępczy cyfry działający w analogiczny sposób jak 0 (zero) z wyjątkiem tego, że znak zastępczy (?) dodaje znak spacji dla zer nieznaczących z każdej strony separatora dziesiętnego (,). Przykładowo, używając formatu 0,0? wartości 1,56 oraz 1,5 zostaną wyświetlone w ten sam sposób, wyrównując je według separatora dziesiętnego. |
| # | Symbol zastępczy cyfry, który działa jak 0 (zero). Nie wyświetla dodatkowych zer w przypadku, gdy wpisywana liczba zawiera mniej cyfr z dowolnej strony separatora dziesiętnego (,) aniżeli jest symboli w formacie. Kod ten informuje Excela, w którym miejscu ma być wyświetlony znak separatora dziesiętnego (,) lub inne separatory. Dla przykładu używając formatu #,### wartość 15,67 zostanie wyświetlona jako 15,67, a wartość 39,625 jako 39,625. |
| , | Symbol separatora dziesiętnego określający ile znaków liczbowych (0 lub #) pojawi się z jego prawej lub lewej strony. Jeśli format liczby zawiera tylko symbol (#) z lewej strony separatora dziesiętnego, to wszystkie wartości poniżej 1 będą wyświetlane z separatorem lecz bez zera wiodącego np. wartość 0,75 sformatowana kodem #,## zostanie zaprezentowana jako ,75. Aby uniknąć takiego wyświetlania należy użyć zera (0) jako pierwszego znaku po lewej stronie separatora dziesiętnego. Wtedy w komórce pojawi się wartość 0,75. |
| % | Symbol procentu, który mnoży wartość przez 100 i wprowadza znak %. |
| / | Symbol formatu ułamkowego wyświetla część dziesiętną w postaci ułamka liczby. Liczba symboli zastępczych cyfry występujących w formacie określa dokładność wyświetlanej wartości. Przykładowo, wartość 0,169 sformatowaną kodem #?/? Excel wyświetli jako 1/6, natomiast stosując kod #??/?? zaprezentuje jako 12/71. |
| E-, E+, e-, e+ | Symbol określający format naukowy, wyświetlający z prawej strony wykładnika E lub e liczbę miejsc przesunięcia separatora dziesiętnego (,). Liczba zer (0) lub symboli zastępczych cyfry (#) z prawej strony E lub e określa minimalną liczbę znaków w wykładniku. Jeśli zastosujesz format E+ lub e+ to znak minus (-) pojawi się przy ujemnych wykładnikach, a znak plus (+) zostanie wyświetlonych przy dodatnich wykładnikach. Używając formatu E- lub e- mówisz Excelowi, aby ten wstawił ujemny znak (-) przy negatywnych wykładnikach. |
| $ – + / ( ) spacja | Standardowe znaki formatowania, których użycie spowoduje dodanie wskazanego znaku do tworzonego formatu. |
| \ | Symbol znaku kreski ułamkowej odwróconej (ang. backslash) poprzedza każdy znak, który chcesz wyświetlić w komórce (poza znakami $ – + / ( ) i spacja). |
| _ | Symbol podkreślenia, którego zastosowanie w kodzie wstawia spację równą szerokości następującego po niej znaku. Przykładowo, jeśli format zawiera kod _X, to efektem jego działania jest wstawienie odstępu równego szerokości dużej litery X. Tego symbolu należy używać w celach wyrównania wartości w komórkach. |
| “Tekst” | Ciąg znaków umieszczony w podwójnych cudzysłowach. W tym przypadku cały łańcuch tekstowy może być wprowadzony do komórki bez konieczności powtarzania znaku backslash po każdym znaku. |
| * | Symbol umożliwiający powtarzanie umieszczonego za nim znaku w celu wypełnienia całej szerokości komórki. Należy pamiętać o tym, że w formacie można zastosować tylko jeden symbol powtórzenia (*). Przykładowo, używając formatu 0,00*- Excel wyświetli wartość zaokrągloną do dwóch miejsc po przecinku, a za nią powtórzy znak minus (-) do końca szerokości komórki. |
| @ | Symbol zastępczy, którego zastosowanie wstawia tekst z komórki (jeśli ta go zawiera) do formatu w miejsce symbolu (@). Dla przykładu, jeśli kod formatu wygląda następująco „Szablon wygenerowany przez ” @, to komórka będzie wyświetlać tekst „Szablon wygenerowany przez Exceldlaksiegowych.pl” w przypadku kiedy w komórce umieszczony był ciąg tekstowy „Exceldlaksiegowych.pl”. Jak zdążyłeś zauważyć trochę tych kodów jest i każdy ma specyficzne zadanie do wykonania. Gorąco zachęcam Cię do przeglądania tej listy i utrwalania sobie zebranych informacji, które pozwolą Ci zrozumieć w jaki sposób są stworzone formaty i jak działają. |
[/minti_table]
Budowa formatów liczbowych
Format liczb może być złożony z maksymalnie czterech sekcji kodów, z których każda definiuje format odpowiednio dla liczb dodatnich, liczb ujemnych, wartości zerowych oraz tekstu. Wszystkie sekcje rozdzielone są od siebie średnikami.
WARTOŚCI DODATNIE;WARTOŚCI UJEMNE;WARTOŚCI ZEROWE;TEKST
Tworząc niestandardowy format liczb nie musisz określać wszystkich sekcji. Jeśli dla przykładu zdefiniujesz tylko dwie, to pierwsza sekcja będzie odpowiadać za formatowanie wartości dodatnich oraz zerowych, a druga sekcja za formatowanie wartości ujemnych. W przypadku, kiedy opiszesz tylko jedną sekcję, ta zostanie użyta dla wszystkich liczb bez względu na ich znak czy wartość. Jeżeli będziesz chciał pominąć którąkolwiek sekcję kodu budując nowy format, to w tym celu musisz użyć średnika zamykającego daną sekcję.
Dodatkowo warto wiedzieć, że tworząc format niestandardowy można określać kolory, które będą przypisane do poszczególnych sekcji. Aby to uczynić wystarczy w nawiasach kwadratowych umieścić jeden z ośmiu dostępnych kolorów, przy czym należy pamiętać o tym, że definicja koloru znajduje się zawsze na początku opisywanej sekcji.
Podstawowe osiem kolorów, którymi dysponujemy w trakcie definiowania formatów to:
- [Czarny]
- [Zielony]
- [Biały]
- [Niebieski]
- [Amarantowy]
- [Żółty]
- [Błękitny]
- [Czerwony]
Dobrze. Znasz już kody, wiesz jakie sekcje składają się na format, potrafisz również dodać kolory. Ale czy zdawałeś sobie sprawę, że możesz także stosować warunki w formacie niestandardowym liczb? Tak na marginesie, je również należy definiować w nawiasach kwadratowych.
Nadszedł czas, abyśmy wspólnie przeanalizowali krok po kroku, sekcja po sekcji przykładowy zapis formatu niestandardowego:
[Zielony]_-* # ##0,00 zł_-;[Czerwony]-* # ##0,00 zł_-;[Niebieski]_-* “-“?? zł_-;_-@_-
Przedstawiony format zawiera wszystkie cztery sekcje kodu oddzielone od siebie średnikiem.
Kod [Zielony]_-* # ##0,00 zł_-
Definiuje pierwszą sekcję formatu dla wartości dodatnich. Zaczyna się od określenia koloru czcionki (w tym przypadku jest to kolor zielony), następnie pojawia się symbol podkreślenia wstawiający odstęp szerokości znaku znajdującego się za nim, czyli znaku minusa (-). W dalszej części następuje symbol powtórzenia (*) oraz spacja, która jest wstawiana do komórki na całej jej szerokości. Kolejny krok to określenie formatu dla liczb oddzielającego tysiące od setek oraz definiujący dwa miejsca po przecinku. Potem spacja, określenie waluty (zł) i ponownie wstawienie odstępu szerokości znaku minusa (-).
Kod [Czerwony]-* # ##0,00 zł_-
Definiuje drugą sekcję formatu dla wartości ujemnych, które będą wyświetlane w kolorze czerwonym. Wartości będą zaczynać się od znaku minus (-), po którym następować będzie powtórzenie odstępu (spacja) na całej szerokości komórki. Wartości liczbowe są sformatowane do dwóch miejsc po przecinku, a tysiące oddzielone od setek. Po nich pojawia się symbol waluty (zł), a na zakończenie wstawiany jest odstęp szerokości znaku minusa (-).
Kod [Niebieski]_-* „-„?? zł_-
Definiuje trzecią sekcję formatu dla wartości zerowych, do której przypisany jest kolor niebieski. Ponownie na początku zdefiniowany jest odstęp szerokości znaku minus (-), a potem wstawiana jest spacja na całej szerokości. Kolejnym elementem jest widoczny znak minus (-), a za nim dwa symbole zastępcze dla zer nieznaczących, po których następuje spacja oraz symbol waluty (zł). Całość jest zakończona wstawieniem odstępu szerokości znaku minusa (-).
Kod _-@_-
Określa czwartą i ostatnią sekcję formatu, tym razem dla tekstu. Rozpoczyna się odstępem szerokości znaku minus (-), po której pojawia się tekst umieszczony w komórce i kończy również odstępem tej samej szerokości.
Jak widzisz stworzenie formatu nie jest nadzwyczaj trudne. Jedyne o czym należy pamiętać to oddzielanie poszczególnych sekcji średnikami oraz właściwe zastosowanie kodów przedstawionych w tabeli powyżej. Możesz również tworzyć własne formaty modyfikując już istniejące. W tym przypadku najpierw wybierz format najbardziej zbliżony do tego, który chcesz zdefiniować, a następnie dokonaj niezbędnych zmian i gotowe. To najlepsza recepta na sukces.
Warto zapamiętać!
Modyfikując istniejący (wbudowany w Excelu) kod formatu, stary pozostaje nienaruszony, natomiast tworzony jest nowy. Ponadto Excel uniemożliwia usunięcie wbudowanych formatów. Usuwać możesz tylko te utworzone przez użytkownika.
Poniżej znajdziesz krótką checklistę poleceń, które musisz wykonać jeśli chcesz stworzyć lub usunąć formaty niestandardowe.
Aby stworzyć nowy format liczbowy lub księgowy:
KROK 1: Zaznacz komórkę zawierającą wartość liczbową.
KROK 2: Na karcie Narzędzia główne, w grupie Liczba, kliknij przycisk Księgowy, a następnie użyj skrótu klawiaturowego Ctrl + 1.
KROK 3: W oknie dialogowym Formatowanie komórek, w zakładce Liczby, kliknij kategorię Niestandardowe.
KROK 4: W polu Typ zmodyfikuj kod według potrzeb, a potem kliknij przycisk OK.
Aby usunąć niestandardowy format:
KROK 1: Użyj skrótu klawiaturowego Ctrl + 1.
KROK 2: W oknie dialogowym Formatowanie komórek, w zakładce Liczby, kliknij kategorię Niestandardowe.
KROK 3: Zaznacz format niestandardowy, który chcesz usunąć.
KROK 4: Kliknij przycisk Usuń poniżej listy Typ, a następnie kliknij przycisk OK.
Każdy nowo utworzony format niestandardowy liczb jest przechowywany i używany tylko w pliku, w którym został zdefiniowany. Jeśli chcesz użyć go w innym skoroszycie, musisz zapisać bieżący jako szablon programu Excel, za pomocą którego możliwe jest utworzenie nowego skoroszytu.
I to by było na tyle w temacie niestandardowych formatów liczbowych i walutowych. W kolejnym tekście przedstawię formaty daty i czasu, oczywiście omawiając je na konkretnych przykładach.
Jeśli podobał Ci się ten artykuł i uważasz, że jest przydatny to proszę podziel się informacją o nim ze swoimi znajomymi – na Facebooku, Twitterze, Google+, LinkedIn, albo skomentuj go poniżej.
Z góry serdecznie dziękuję!