Formatowanie niestandardowe w arkuszu kalkulacyjnym MS Excel

Arkusz kalkulacyjne MS Excel oferuje nam szeroką gamę wbudowanych formatów standardowych takich jak: liczbowe, walutowe, księgowe, daty i czasu itp. Każdy z tych formatów możemy dodatkowo przystosowywać do naszych potrzeb […]

Arkusz kalkulacyjne MS Excel oferuje nam szeroką gamę wbudowanych formatów standardowych takich jak: liczbowe, walutowe, księgowe, daty i czasu itp. Każdy z tych formatów możemy dodatkowo przystosowywać do naszych potrzeb (ilość wyświetlanych miejsc po przecinku w formacie liczbowym, znak waluty dla formatu księgowego, sposób prezentacji liczb ujemnych itd.). Twórcy MS Excel dodali też kilka formatów specjalnych (kod pocztowy, numer telefony, numer PESEL i NIP).

Zdarza się jednak, że potrzebujemy formatu wykraczającego swoim wyglądem poza standardowe ramy, użyć możemy wtedy tzw. formatowania niestandardowego czyli narzędzia umożliwiającego nam własne zdefiniowanie jego wyglądu.

Formatowanie niestandardowe krok po kroku:

1)      Do okna formatowania zaznaczonej komórki lub obszaru komórek wchodzimy przez otwarcie menu prawym przyciskiem myszy i wybranie opcji Formatuj komórki… lub poprzez skrót klawiszowy Ctrl+1 (uwaga – nie możemy być w trybie edycji zaznaczonej komórki aktywnej – w takim przypadku otworzy nam się jedynie okno formatowania czcionki),

2)      Opcja formatowanie niestandardowe znajduje się na samym dole okna kategorii, format definiujemy sami w okienku po prawej stronie (Typ:) lub wybieramy jeden z kilkudziesięciu napisanych już formatów niestandardowych (poniżej),

3)      Struktura definicji formatu niestandardowego składa się z czterech sekcji rozdzielonych średnikami wg następującej kolejności :

format_liczby_dodatniej;     format_liczby_ujemnej;      format_zera;      format_tekstu

 

4)      W sekcjach można korzystać z następujących znaków:

Symbole wykorzystywane podczas konstrukcji formatów niestandardowych
, Rozgranicza prawą i lewą stronę formatu liczby po obu stronach przecinka
; Rozdziela sekcje
0 Gwarantuje określoną liczbę cyfr po każdej stronie przecinka np. 00,000
# Podobnie jak 0, ale nieznaczące zera nie są wyświetlane np.  0#,000
? Podobnie jak 0, ale w miejscach zer nieznaczących pozostawione zostaje miejsce do wyrównania liczb np. dla formatu ???,??? liczby 21,158 i 203,56 oraz 3,2 wyrównane zostaną względem przecinka
(spacja) Separator lub spacja wpisana bezpośrednio po formacie liczby w sekcji obcina 3 ostatnie cyfry z liczby (czyli dzieli liczbę przez 1000), analogicznie dwie spacje bezpośrednio po formacie to obcięcie 6 ostatnich cyfr (czyli dzielenie przez milion) itd.
/ Wymusza wyświetlanie części ułamkowej formatowanej liczby w postaci ułamka zwykłego np. ##0/### spowoduje wyświetlenie 5,25 jako 21/4
% Mnoży liczbę razy 100 i dodaje symbol %
[kolor] Możliwe kolory wpisywane słownie: [czarny], [niebieski], [błękitny], [zielony], [amarantowy], [czerwony], [biały], [żółty]lub w postaci liczbowej:[kolorN] gdzie  N to liczba od 1 do 56
d, m, r Symbole do formatowania dat (odpowiednio: dzień, miesiąc i rok) np. rrrr-mm-ddRóżne warianty definiowania fragmentów formatu daty:

d – oznacza dzień miesiąca, o ile mniejszy od 10-tego to jednocyfrowo,

dd – dzień miesiąca zawsze dwucyfrowo,

ddd – dzień miesiąca słownie skrótem,

dddd – dzień miesiąca słownie,

m – miesiąc jednocyfrowo,

mm – miesiąc zawsze dwucyfrowo,

mmm – miesiąc słownie skrótem,

mmmm – miesiąc słownie,

rr – rok w formie dwucyfrowej,

rrrr – rok w formie czterocyfrowej.

g, m, s Symbole do formatowania godzin (odpowiednio: godzina, minuta i sekunda) np. gg:mm:ss
@ Symbol umieszczany w sekcji tekstu (czwartej) oznaczający pozycję tekstu
„przykładowy_tekst” Tekst umieszczony w cudzysłowach można umieszczać w każdej z czterech sekcji np. ‘’Zysk”#0,000;”Strata”-#0,000;”ZERO”
* Gwiazdka i następujący po niej znak powoduje wypełnienie tym znakiem całej możliwej szerokości komórki, najczęściej stosowane ze spacją np.+* 0,000;-* 0,000 spowoduje odsunięcie symbolu + od właściwiej liczby dodatniej oraz symbolu – od właściwej liczby ujemnej na szerokość komórki (w obu sekcjach dodatniej i ujemnej po symbolu * znajduje się spacja, co powoduje rozdzielenie symboli + lub – od właściwiej liczby duża ilością odstępów)
[warunek] Do sekcji dodatniej lub ujemnej można poza formatowaniem koloru dodać dodatkowy warunek np. [zielony][>1000]+0,0;[czerwony][<-1000]-0,0Tak skonstruowany format zadziała tylko na liczby większe od 1000 (będą zielone i w formacie +0,0) oraz na liczby mniejsze od -1000 (będą czerwone i w formacie -0,0)

 

5)      Przykłady formatów niestandardowych z opisami:

a.      0” szt.” (do każdej dodatniej liczby dopisany zostanie skrót „ szt.”, dodatkowo nastąpi zaokrąglanie do liczb całkowitych),

b.       +0,00;-0,00 (liczby dodatnie i ujemne wyświetlane będą z dokładnością do 2 miejsc po przecinku dodatkowo przed liczbami dodatnimi pojawi się znak +),

c.       #,0;[czerwony](0,00) (liczby dodatnie wyświetlane z jednym miejscem po przecinku, dodatkowo jeśli mniejsze od jedności to bez zera przed przecinkiem, liczby ujemne wyświetlane czerwoną czcionką bez minusa, ale objęte nawiasem i z dwoma miejscami po przecinku),

d.      # ##0,00;-# ##0,00 (liczby dodatnie (ujemne) o ile większe (mniejsze) lub równe od 1000 (-1000) wyświetlane z separatorem tysięcy),

e.      0,00;-0,00;”ZERO”;[zielony]@ (zdefiniowany pełny format: liczby dodatnie i ujemne z dwoma miejscami po przecinku, zero wyświetlane słownie jako „ZERO”, a każdy wpisany tekst do formatowanych komórek będzie zapisany zieloną czcionką),

f.        dd.mm.rrrr” r.” (data zapisana z kropkami zamiast myślników oraz skrótem „r.” na końcu, dzień i miesiąc zawsze dwucyfrowe, rok czterocyfrowy),

g.      dddd (z całej daty pojawi się jedynie słownie zapisany dzień tygodnia – jest to dobra alternatywa w niektórych przypadkach dla funkcji DZIEŃ.TYG).

h.     0,0 ” tys.”;-0,0 ” tys.” (liczby dodatnie jak i ujemne przedstawione zostaną w formie tysięcy, obcięcie 3 zer realizuje spacja wpisana za fragmentem formatu „0,0” zarówno w części dotyczącej formatu dodatniego jak i ujemnego; dwie spacje obcinałyby 6 zer, oczywiście wtedy tekst w cudzysłowach trzeba by wymienić na ” mln”)

 

UWAGI

6)      Format niestandardowy nie wpływa na rzeczywistą wartość komórek, na których nadal można wykonywać np. operacje matematyczne (pełni tylko rolę prezentacyjną),

7)      Format niestandardowy w postaci ;;; (trzy średniki czyli de facto niezdefiniowanie formy żadnej z sekcji) umożliwia pseudo ukrywanie zawartości komórek (nadal widać ją w pasku formuły chociaż i tu można ją ukryć poprzez kombinację opcji Ukryj z zakładki Ochrona okna formatowania i włączenie opcji Ochrony arkusza),

8)      Jeśli jesteśmy pewni, że w  formatowanej komórce (obszarze komórek) nie pojawią się liczby ujemne, to możemy w definicji formatu niestandardowego pominąć sekcję dotyczącą liczb ujemnych (gdyby jednak się tam pojawiły efekt będzie dziwny, mogą dla takiej liczby pojawić się oba symbole minus plus -+),

9) Kolor wynikający z definicji formatu niestandardowego jest „silniejszy” od koloru czcionki, ale „słabszy” od koloru formatowania warunkowego,

10) W MS Excel z ustawieniami anglojęzycznymi często zamiast przecinka do rozdzielania części dziesiętnej liczby stosuje się kropki, ma to również wpływ na format niestandardowy np. dzielenie liczby przez tysiąc za pomocą spacji wpisanej za formatem liczby w sekcji w wersji z kropkami realizuje się za pomocą przecinka i spacji (również wpisywanych za formatem liczby w danej sekcji).