Proste zadanie, jakim jest wyświetlanie miejsc dziesiętnych, czasami przysparza kłopotów użytkownikom arkuszy kalkulacyjnych. Jeśli na liście znajdują się zarówno duże, jak i małe liczby, pojawia się napięcie między zaokrąglaniem małych liczb do wartości całkowitych a utrudnianiem odczytu dużych liczb przez dodanie dwóch zer po przecinku.
W tym artykule opiszę, jak dodawać miejsca po przecinku na żądanie za pomocą niestandardowego formatu liczb. W części 2 tej serii przedstawię alternatywne rozwiązanie wykorzystujące funkcję MOD wraz z funkcją Formatowanie warunkowe Excela.
Rysunek 1 przedstawia przykładowy raport z danymi dotyczącymi sprzedaży. W kolumnach C i D znajdują się kwoty w walucie, więc do wszystkich komórek w tych kolumnach można zastosować takie samo formatowanie. Jednak w kolumnie A, zamiast wyświetlać kwotę w komórce A2 jako 3 383,00, chcielibyśmy przedstawić ją jako 3 383, ale jednocześnie pokazać kwotę w komórce A3 jako 0,50.
Rysunek 1: Możesz warunkowo wyświetlać liczby dziesiętne za pomocą niestandardowego formatu liczby.
Jak widać na rysunku 1, najprostszym sposobem wykonania naszego zadania jest utworzenie niestandardowego formatu liczbowego:
1. Zaznacz komórki, do których chcesz zastosować warunkowy format liczbowy.
2. Naciśnij klawisz Ctrl-1, aby wyświetlić okno dialogowe Formatuj komórki. W programie Excel 2007 i nowszych można kliknąć przycisk Liczba na karcie Strona główna, jak pokazano na rysunku 1. W programach Excel 2003 i Excel dla komputerów Mac można też wybrać polecenie Format, a następnie Komórki.
3. Na karcie Liczba w oknie dialogowym Formatuj komórki wybierz opcję Niestandardowe.
4. W polu Typ wpisz następujący kod formatujący: [>=1]#,##0;[<1]0.00
5. Kliknij przycisk OK, aby zamknąć okno dialogowe Formatuj komórki.
6. Jak widać na rysunku 1, teraz liczby mniejsze od 1 będą wyświetlane z dwoma miejscami po przecinku. Jednak, jak to często bywa w Excelu, to proste podejście może przynieść niezamierzony efekt uboczny. Jeśli zmienisz wartość komórki A2 na 3383,75, Excel wyświetli liczbę 384 zamiast 383,75. (W części 2 tej serii przedstawię rozwiązanie tego problemu za pomocą funkcji Formatowanie warunkowe i funkcji MOD programu Excel).
Dyskusja na temat niestandardowych formatów liczb, takich jak ten przedstawiony na Rysunku 1, może się szybko okazać bardzo skomplikowana, dlatego w tym artykule mogę przedstawić jedynie krótkie wyjaśnienia. W tym przypadku opracowałem warunkowy format liczbowy, który testuje dwa różne warunki:
- [>=1] - Ten test określa, czy liczba w komórce jest większa lub równa 1. Jeśli tak, Excel formatuje liczbę w taki sposób, aby w przypadku tysięcy, milionów i tak dalej pojawiały się przecinki, bez końcowych miejsc po przecinku.
- [<1] - Ten test określa, czy liczba w komórce jest mniejsza od 1, a jeśli tak, to Excel formatuje liczbę z początkowym zerem i dwoma końcowymi miejscami po przecinku.
W niestandardowych formatach liczbowych znak # oznacza znaki zastępcze, które będą używane w razie potrzeby, natomiast znak 0 oznacza, że wyświetlana będzie albo liczba rzeczywista, albo zerowy znak zastępczy. Testy warunkowe, takie jak te, których użyliśmy, są umieszczane w nawiasach kwadratowych, a każdy zestaw warunków jest oddzielony średnikiem. Rysunek 2 pokazuje, jak można zastosować utworzony niestandardowy format liczbowy do innych komórek arkusza lub usunąć go z listy, gdy nie jest już potrzebny.
Należy pamiętać, że niestandardowe formaty liczbowe, takie jak ten pokazany na rysunku 1, mają zastosowanie tylko do danego skoroszytu, więc jeśli potrzebujesz tej funkcji również w innych arkuszach, musisz utworzyć je ponownie.
Rysunek 2: Utworzone własne formaty liczbowe można łatwo zastosować w przyszłości.
Powiązany artykuł:
- Warunkowe wyświetlanie miejsc dziesiętnych w Excelu: Część 2
Przeczytaj więcej artykułów autorstwa Davida Ringstroma.
O autorze:
David H. Ringstrom, CPA, kieruje Accounting Advisors, Inc. z siedzibą w Atlancie, firmą konsultingową zajmującą się oprogramowaniem i bazami danych, świadczącą usługi szkoleniowe i doradcze na terenie całego kraju. Skontaktuj się z Davidem pod adresem [email protected] lub śledź go na Twitterze. David jest prelegentem na konferencjach poświęconych Microsoft Excel i prowadzi webcasty dla kilku dostawców usług CPE, w tym dla partnera CPE Link w AccountingWEB.
