Formatowanie liczb w Excel VBA
W arkuszach programu Excel liczby mają różne formaty. Być może znasz już wyskakujące okienko w programie Excel do korzystania z różnych formatów liczbowych:
Formatowanie liczb ułatwia ich czytanie i zrozumienie. Domyślnym formatem Excela dla liczb wprowadzanych do komórek jest format „Ogólny”, co oznacza, że liczba jest wyświetlana dokładnie tak, jak ją wpisałeś.
Na przykład, jeśli wprowadzisz okrągłą liczbę, np. 4238, zostanie wyświetlony jako 4238 bez separatora dziesiętnego lub tysięcy. Liczba dziesiętna, taka jak 9325,89, zostanie wyświetlona z kropką dziesiętną i miejscami dziesiętnymi. Oznacza to, że nie ustawi się w kolumnie z okrągłymi liczbami i będzie wyglądał wyjątkowo niechlujnie.
Ponadto bez pokazywania separatorów tysięcy trudno jest zobaczyć, jak duża jest w rzeczywistości liczba bez liczenia poszczególnych cyfr. Czy w milionach czy dziesiątkach milionów?
Z punktu widzenia użytkownika spoglądającego w dół kolumny liczb utrudnia to odczytanie i porównywanie.
W VBA masz dostęp do dokładnie tego samego zakresu formatów, które masz w interfejsie Excela. Dotyczy to nie tylko wartości wprowadzonej w komórce w arkuszu, ale także takich elementów, jak okna wiadomości, kontrolki UserForm, wykresy i wykresy oraz pasek stanu programu Excel w lewym dolnym rogu arkusza.
Funkcja Format jest niezwykle przydatną funkcją w VBA pod względem prezentacji, ale jest również bardzo złożona pod względem elastyczności oferowanej w sposobie wyświetlania liczb.
Jak korzystać z funkcji formatowania w VBA?
Jeśli wyświetlasz okno komunikatu, funkcja Format może być używana bezpośrednio:
| 1 | MsgBox Format(1234567.89, "#,##0.00") |
Spowoduje to wyświetlenie dużej liczby za pomocą przecinków do oddzielenia tysięcy i pokazania 2 miejsc po przecinku. Wynik wyniesie 1 234 567,89. Zera w miejscu skrótu zapewniają, że ułamki dziesiętne będą wyświetlane jako 00 w liczbach całkowitych oraz że będzie wiodące zero dla liczby mniejszej niż 1
Symbol hashtagu (#) reprezentuje symbol zastępczy cyfry, który wyświetla cyfrę, jeśli jest dostępna w tej pozycji, lub nic.
Możesz także użyć funkcji formatowania, aby zaadresować pojedynczą komórkę lub zakres komórek, aby zmienić format:
| 1 | Arkusze("Arkusz1").Range("A1:A10").NumberFormat = "#,##0.00" |
Ten kod ustawi zakres komórek (A1 do A10) na niestandardowy format, który oddziela tysiące przecinkami i pokazuje 2 miejsca po przecinku.
Jeśli sprawdzisz format komórek w interfejsie programu Excel, zobaczysz, że został utworzony nowy format niestandardowy.
Możesz także formatować liczby na pasku stanu programu Excel w lewym dolnym rogu okna programu Excel:
| 1 | Application.StatusBar = Format(1234567.89, "#,##0.00") |
Możesz usunąć to z paska stanu za pomocą:
| 1 | Application.StatusBar = "" |
Tworzenie ciągu formatującego
W tym przykładzie po każdej liczbie zostanie dodany tekst „Całkowita sprzedaż”, a także separator tysięcy
| 1 | Sheets("Arkusz1").Range("A1:A6").NumberFormat = "#,##0.00"" Całkowita sprzedaż""" |
Oto jak będą wyglądać Twoje liczby:
Zwróć uwagę, że komórka A6 ma formułę „SUM”, która będzie zawierać tekst „Całkowita sprzedaż” bez konieczności formatowania. Jeśli formatowanie zostanie zastosowane, jak w powyższym kodzie, nie umieści dodatkowego wystąpienia „Całkowitej sprzedaży” w komórce A6
Chociaż komórki wyświetlają teraz znaki alfanumeryczne, liczby nadal występują w postaci liczbowej. Formuła „SUM” nadal działa, ponieważ używa wartości liczbowej w tle, a nie sposobu formatowania liczby.
Przecinek w ciągu formatu zapewnia separator tysięcy. Zauważ, że wystarczy umieścić to w ciągu tylko raz. Jeśli liczba sięga milionów lub miliardów, nadal dzieli cyfry na grupy 3
Zero w ciągu formatu (0) to symbol zastępczy cyfry. Wyświetla cyfrę, jeśli tam jest, lub zero. Jego pozycjonowanie jest bardzo ważne, aby zapewnić jednolitość z formatowaniem
W ciągu formatującym znaki hash (#) nie będą wyświetlać nic, jeśli nie ma cyfry. Jeśli jednak istnieje liczba taka jak 0,8 (wszystkie ułamki dziesiętne), chcemy, aby była wyświetlana jako 0,80, aby była wyrównana z innymi liczbami.
Użycie jednego zera po lewej stronie przecinka dziesiętnego i dwóch zer po prawej stronie przecinka w ciągu formatu da wymagany wynik (0,80).
Gdyby po prawej stronie przecinka było tylko jedno zero, wynik byłby „0,8” i wszystko byłoby wyświetlane z jednym miejscem po przecinku.
Używanie ciągu formatującego do wyrównania
Możemy chcieć zobaczyć wszystkie liczby dziesiętne w zakresie wyrównanym do ich punktów dziesiętnych, tak aby wszystkie punkty dziesiętne znajdowały się bezpośrednio pod sobą, bez względu na to, ile miejsc dziesiętnych znajduje się na każdej liczbie.
Aby to zrobić, możesz użyć znaku zapytania (?) w ciągu formatu. Znak „?” wskazuje, że liczba jest wyświetlana, jeśli jest dostępna, lub spacja
| 1 | Arkusze("Arkusz1").Range("A1:A6").NumberFormat = "#,##0.00?" |
Spowoduje to wyświetlenie twoich liczb w następujący sposób:
Wszystkie kropki dziesiętne zbiegają się teraz pod sobą. Komórka A5 ma trzy miejsca po przecinku, co normalnie wyrzuci wyrównanie, ale użycie znaku „?” wyrównuje wszystko idealnie.
Używanie literalnych znaków w ciągu formatującym
Do ciągu formatu można dodać dowolny znak dosłowny, poprzedzając go ukośnikiem odwrotnym (\).
Załóżmy, że chcesz pokazać konkretny wskaźnik waluty dla swoich liczb, który nie jest oparty na Twoim regionie. Problem polega na tym, że jeśli używasz wskaźnika waluty, program Excel automatycznie odwołuje się do Twojego lokalnego i zmienia go na odpowiedni dla ustawień regionalnych ustawionych w Panelu sterowania systemu Windows. Może to mieć konsekwencje, jeśli Twoja aplikacja Excel jest dystrybuowana w innych krajach i chcesz mieć pewność, że niezależnie od ustawień regionalnych wskaźnik waluty jest zawsze taki sam.
Możesz również wskazać, że liczby są w milionach w następującym przykładzie:
| 1 | Arkusze("Arkusz1").Range("A1:A6").NumberFormat = "\$#,##0.00 \m" |
Spowoduje to wyświetlenie w arkuszu następujących wyników:
Używając odwrotnego ukośnika do wyświetlania znaków dosłownych, nie musisz używać odwrotnego ukośnika dla każdego pojedynczego znaku w ciągu. Możesz użyć:
| 1 | Sheets("Arkusz1").Range("A1:A6").NumberFormat = "\$#,##0.00 \mill" |
Spowoduje to wyświetlenie „młyn” po każdej liczbie w sformatowanym zakresie.
Możesz użyć większości znaków jako literałów, ale nie znaków zarezerwowanych, takich jak 0, #,?
Używanie przecinków w ciągu formatującym
Widzieliśmy już, że przecinków można używać do tworzenia separatorów tysięcy dla dużych liczb, ale można ich również używać w inny sposób.
Używając ich na końcu numerycznej części ciągu formatującego, działają jak skalery tysięcy. Innymi słowy, podzielą każdą liczbę przez 1000 za każdym razem, gdy pojawi się przecinek.
W przykładowych danych pokazujemy to ze wskaźnikiem, że jest w milionach. Wstawiając jeden przecinek do ciągu formatującego, możemy pokazać te liczby podzielone przez 1000.
| 1 | Arkusze("Arkusz1").Range("A1:A6").NumberFormat = "\$#,##0.00,\m" |
Spowoduje to podzielenie liczb przez 1000, chociaż oryginalna liczba nadal będzie znajdować się w tle w komórce.
Jeśli w ciągu formatującym umieścisz dwa przecinki, liczby zostaną podzielone przez milion
| 1 | Arkusze("Arkusz1").Range("A1:A6").NumberFormat = "\$#,##0.00,\m" |
Będzie to wynik używając tylko jednego przecinka (podziel przez 1000):
Tworzenie formatowania warunkowego w ciągu formatującym
Możesz skonfigurować formatowanie warunkowe w interfejsie programu Excel, ale możesz to również zrobić w kodzie VBA, co oznacza, że możesz programowo manipulować ciągiem formatu, aby wprowadzać zmiany.
Możesz użyć maksymalnie czterech sekcji w ciągu formatu. Każda sekcja jest oddzielona średnikiem (;). Cztery sekcje odpowiadają dodatniemu, ujemnemu, zerowemu i tekstowemu
| 1 | Zakres("A1:A7").NumberFormat = "#,##0.00;[Czerwony]-#,##0.00;[Zielony] #,##0.00;[Niebieski]” |
W tym przykładzie używamy tego samego skrótu, przecinka i zera, aby zapewnić separatory tysięcy i dwa miejsca po przecinku, ale teraz mamy różne sekcje dla każdego typu wartości.
Pierwsza sekcja dotyczy liczb dodatnich i nie różni się od tego, co widzieliśmy już wcześniej pod względem formatu.
Druga sekcja dla liczb ujemnych wprowadza kolor (czerwony), który jest utrzymywany w parze nawiasów kwadratowych. Format jest taki sam jak w przypadku liczb dodatnich, z tym wyjątkiem, że na początku dodano znak minus (-).
Trzecia sekcja dla liczb zerowych wykorzystuje kolor (zielony) w nawiasach kwadratowych z ciągiem liczbowym takim samym, jak dla liczb dodatnich.
Ostatnia sekcja dotyczy wartości tekstowych, a wszystko, czego potrzeba, to kolor (niebieski) ponownie w nawiasach kwadratowych
Jest to wynik zastosowania tego ciągu formatu:
Możesz przejść dalej z warunkami w ciągu formatu. Załóżmy, że chcesz pokazać każdą liczbę dodatnią powyżej 10 000 jako zieloną, a każdą inną liczbę jako czerwoną, możesz użyć tego ciągu formatującego:
| 1 | Zakres("A1:A7").NumberFormat = "[>=10000][Zielony]#,##0.00;[<10000][Czerwony]#,##0.00" |
Ten ciąg formatu zawiera warunki dla > = 10 000 ustawione w nawiasach kwadratowych, dzięki czemu kolor zielony będzie używany tylko wtedy, gdy liczba jest większa lub równa 10000
Oto wynik:
Używanie ułamków w ciągach formatujących
Ułamki nie są często używane w arkuszach kalkulacyjnych, ponieważ zwykle odpowiadają ułamkom dziesiętnym, które wszyscy znają.
Czasami jednak służą celowi. W tym przykładzie zostaną wyświetlone dolary i centy:
| 1 | Range("A1:A7").NumberFormat = "#,##0"" dolary i "" 00/100 "" centy """ |
Oto wynik, który zostanie wygenerowany:
Pamiętaj, że pomimo tego, że liczby są wyświetlane jako tekst, nadal znajdują się w tle jako liczby i nadal można na nich używać wszystkich formuł Excela.
Formaty daty i czasu
Daty są w rzeczywistości liczbami i można na nich używać formatów w taki sam sposób, jak w przypadku liczb. Jeśli sformatujesz datę jako liczbę liczbową, zobaczysz dużą liczbę po lewej stronie przecinka i liczbę miejsc dziesiętnych. Liczba po lewej stronie kropki dziesiętnej pokazuje liczbę dni, począwszy od 01.01.2000, a miejsca dziesiętne pokazują czas w oparciu o 24 godziny
| 1 | MsgBox Format(Now(), "dd-mmm-rrrr") |
Spowoduje to sformatowanie bieżącej daty na „08-lip-2020”. Użycie „mmm” dla miesiąca powoduje wyświetlenie pierwszych trzech znaków nazwy miesiąca. Jeśli chcesz pełną nazwę miesiąca, użyj „mmmm”
W ciągu formatu możesz uwzględnić czasy:
| 1 | MsgBox Format(Now(), "dd-mmm-rrrr gg:mm AM/PM") |
Wyświetli się „08-lip-2020, 13:25”
„gg:mm” reprezentuje godziny i minuty, a AM/PM używa zegara 12-godzinnego, a nie 24-godzinnego.
Do ciągu formatu możesz wstawiać znaki tekstowe:
| 1 | MsgBox Format(Now(), "dd-mmm-rrrr gg:mm AM/PM"" dzisiaj""") |
Spowoduje to wyświetlenie „08-lip-2020 13:25 dzisiaj”
Możesz również używać znaków dosłownych, używając odwrotnego ukośnika na początku w taki sam sposób, jak w przypadku ciągów formatu numerycznego.
Predefiniowane formaty
Program Excel ma wiele wbudowanych formatów liczb i dat, których można użyć w kodzie. Odzwierciedlają one głównie to, co jest dostępne w interfejsie do formatowania liczb, chociaż niektóre z nich wykraczają poza to, co zwykle jest dostępne w wyskakującym okienku. Ponadto nie masz elastyczności co do liczby miejsc dziesiętnych ani tego, czy używane są separatory tysięcy.
Ogólny numer
Ten format wyświetli liczbę dokładnie taką, jaka jest
| 1 | MsgBox Format(1234567.89, "Numer ogólny") |
Wynik wyniesie 1234567,89
Waluta
| 1 | Format MsgBox (1234567.894, "Waluta") |
Ten format doda symbol waluty przed liczbą, np. $, £ w zależności od lokalizacji, ale sformatuje również liczbę do 2 miejsc po przecinku i oddzieli tysiące przecinkami.
Wynik wyniesie 1 234 567,89 $
Naprawiony
| 1 | Format MsgBox (1234567.894, "Naprawiono") |
Ten format wyświetla co najmniej jedną cyfrę po lewej, ale tylko dwie cyfry po prawej stronie przecinka dziesiętnego.
Wynik wyniesie 1234567,89
Standard
| 1 | Format MsgBox (1234567.894, "Standard") |
Wyświetla liczbę z separatorami tysięcy, ale tylko do dwóch miejsc po przecinku.
Wynik wyniesie 1 234 567,89
Procent
| 1 | Format MsgBox (1234567.894, "Procent") |
Liczba jest mnożona przez 100, a na końcu liczby dodawany jest symbol procentu (%). Format wyświetla się do 2 miejsc po przecinku
Wynik wyniesie 123456789,40%
Naukowy
| 1 | Format MsgBox (1234567.894, "Naukowe") |
To konwertuje liczbę na format wykładniczy
Wynik wyniesie 1,23E+06
Tak nie
| 1 | Format MsgBox (1234567.894, "Tak/Nie") |
Wyświetla „Nie”, jeśli liczba wynosi zero, w przeciwnym razie wyświetla „Tak”
Wynik będzie „Tak”
Prawda fałsz
| 1 | Format MsgBox (1234567.894, "Prawda/Fałsz") |
Wyświetla „Fałsz”, jeśli liczba wynosi zero, w przeciwnym razie wyświetla „Prawda”
Wynik będzie „Prawda”
Wł/Wył
| 1 | Format MsgBox (1234567.894, "Wł/Wył") |
Wyświetla „Wył”, jeśli liczba wynosi zero, w przeciwnym razie wyświetla „Wł”
Wynik będzie „Włączony”
Data ogólna
| 1 | MsgBox Format(Now(), "Data ogólna") |
Spowoduje to wyświetlenie daty jako daty i godziny w notacji AM/PM. Sposób wyświetlania daty zależy od ustawień w Panelu sterowania systemu Windows (zegar i region | Region). Może być wyświetlany jako „mm/dd/rrrr” lub „dd/mm/rrrr”
Wynikiem będzie ‘7/7/2020 15:48:25’
Długa randka
| 1 | MsgBox Format(Now(), "Długa data") |
Spowoduje to wyświetlenie długiej daty określonej w Panelu sterowania systemu Windows (zegar i region | region). Zwróć uwagę, że nie obejmuje czasu.
Wynikiem będzie „Wtorek, 7 lipca 2022 r.”
Średnia data
| 1 | MsgBox Format(Now(), "Średnia data") |
Spowoduje to wyświetlenie daty określonej w ustawieniach daty krótkiej określonej przez ustawienia regionalne w Panelu sterowania systemu Windows.
Wynikiem będzie „07-lip-20”
Krótka randka
| 1 | MsgBox Format(Now(), "Krótka data") |
Wyświetla krótką datę zdefiniowaną w Panelu sterowania systemu Windows (zegar i region | Region). Sposób wyświetlania daty zależy od Twojego regionu. Może być wyświetlany jako „mm/dd/rrrr” lub „dd/mm/rrrr”
Wynikiem będzie „7/7/2020”
Długi czas
| 1 | MsgBox Format(Now(), "Długi czas") |
Wyświetla długi czas zgodnie z definicją w Panelu sterowania systemu Windows (zegar i region | region).
Wynik będzie „16:11:39”
Średni czas
| 1 | MsgBox Format(Now(), "Średni czas") |
Wyświetla średni czas określony przez ustawienia regionalne w Panelu sterowania systemu Windows. Zwykle jest to format 12-godzinny z wykorzystaniem godzin, minut i sekund oraz formatu AM/PM.
Wynik będzie „16:15”
Krótki czas
| 1 | MsgBox Format(Now(), "Krótki czas") |
Wyświetla średni czas określony w Panelu sterowania systemu Windows (zegar i region | region). Zwykle jest to format 24-godzinny z godzinami i minutami
Wynikiem będzie „16:18”
Niebezpieczeństwa związane z używaniem predefiniowanych formatów Excela w datach i godzinach
Korzystanie z predefiniowanych formatów dat i godzin w Excel VBA jest bardzo zależne od ustawień w Panelu sterowania systemu Windows, a także od ustawień regionalnych
Użytkownicy mogą łatwo zmienić te ustawienia, co będzie miało wpływ na sposób wyświetlania dat i godzin w programie Excel
Na przykład, jeśli tworzysz aplikację Excel, która używa predefiniowanych formatów w kodzie VBA, mogą one ulec całkowitej zmianie, jeśli użytkownik znajduje się w innym kraju lub używa innych ustawień regionalnych niż ty. Może się okazać, że szerokości kolumn nie pasują do definicji daty lub na użytkowniku kontrolki Active X, takiej jak kontrolka pola kombi (menu rozwijane), jest zbyt wąska, aby daty i godziny były wyświetlane poprawnie.
Podczas tworzenia aplikacji Excel musisz wziąć pod uwagę, gdzie znajdują się odbiorcy geograficznie
Zdefiniowane przez użytkownika formaty liczb
Istnieje wiele różnych parametrów, których możesz użyć podczas definiowania ciągu formatu:
| Postać | Opis |
| Zerowy ciąg | Brak formatowania |
| 0 | Symbol zastępczy cyfry. Wyświetla cyfrę lub zero. Jeśli istnieje cyfra dla tej pozycji, wyświetla cyfrę, w przeciwnym razie wyświetla 0. Jeśli jest mniej cyfr niż zer, otrzymasz początkowe lub końcowe zera. Jeśli po przecinku jest więcej cyfr niż zer, liczba jest zaokrąglana do liczby miejsc dziesiętnych wskazywanych przez zera. Jeśli przed przecinkiem jest więcej cyfr niż zer, będą one wyświetlane normalnie. |
| # | Symbol zastępczy cyfry. Wyświetla cyfrę lub nic. Działa to tak samo, jak symbol zastępczy zera powyżej, z wyjątkiem tego, że początkowe i końcowe zera nie są wyświetlane. Na przykład 0,75 byłoby wyświetlane przy użyciu zerowych symboli zastępczych, ale byłoby to 0,75 przy użyciu # symboli zastępczych. |
| . Kropka dziesiętna. | Tylko jeden dozwolony na ciąg formatu. Ten znak zależy od ustawień w Panelu sterowania systemu Windows. |
| % | Procentowy symbol zastępczy. Mnoży liczbę przez 100 i umieszcza znak % w miejscu, w którym występuje w ciągu formatu |
| , (przecinek) | Tysiąc separatora. Jest to używane, jeśli użyto 0 lub # symboli zastępczych, a ciąg formatujący zawiera przecinek. Jeden przecinek na lewo od przecinka oznacza zaokrąglenie do najbliższego tysiąca. Np. ##0, dwa sąsiednie przecinki po lewej stronie separatora tysięcy oznaczają zaokrąglanie do najbliższego miliona. Np. ##0,, |
| E- E+ | Format naukowy. Wyświetla liczbę wykładniczo. |
| : (okrężnica) | Separator czasu - używany podczas formatowania czasu na godziny, minuty i sekundy. |
| / | Separator daty - jest używany podczas określania formatu daty |
| - + £ $ ( ) | Wyświetla znak dosłowny.Aby wyświetlić znak inny niż wymieniony tutaj, poprzedź go odwrotnym ukośnikiem (\) |
Zdefiniowane przez użytkownika formaty dat i godzin
Wszystkie te znaki mogą być używane w ciągu formatującym podczas formatowania dat i godzin:
| Postać | Oznaczający |
| C | Wyświetla datę jako ddddd i godzinę jako ttttt |
| D | Wyświetlaj dzień jako liczbę bez wiodącego zera |
| dd | Wyświetlaj dzień jako liczbę z wiodącym zerem |
| ddd | Wyświetl dzień jako skrót (niedziela – sob) |
| dddd | Wyświetlaj pełną nazwę dnia (niedziela - sobota) |
| ddddd | Wyświetl numer seryjny daty jako pełną datę zgodnie z datą skróconą w ustawieniach międzynarodowych w Panelu sterowania systemu Windows |
| dddddd | Wyświetla numer seryjny daty jako pełną datę według daty długiej w ustawieniach międzynarodowych Panelu sterowania systemu Windows. |
| w | Wyświetla dzień tygodnia jako liczbę (1 = niedziela) |
| w W | Wyświetla tydzień roku jako liczbę (1-53) |
| m | Wyświetla miesiąc jako liczbę bez wiodącego zera |
| mm | Wyświetla miesiąc jako liczbę z wiodącymi zerami |
| mmm | Wyświetla miesiąc jako skrót (styczeń-grudzień) |
| mmmm | Wyświetla pełną nazwę miesiąca (od stycznia do grudnia) |
| Q | Wyświetla kwartał roku jako liczbę (1-4) |
| tak | Wyświetla dzień roku jako liczbę (1-366) |
| yy | Wyświetla rok jako dwucyfrową liczbę |
| rrrr | Wyświetla rok jako czterocyfrową liczbę |
| h | Wyświetla godzinę jako liczbę bez wiodącego zera |
| hh | Wyświetla godzinę jako liczbę z wiodącym zerem |
| n | Wyświetla minutę jako liczbę bez wiodącego zera |
| nn | Wyświetla minutę jako liczbę z wiodącym zerem |
| s | Wyświetla sekundę jako liczbę bez wiodącego zera |
| SS | Wyświetla sekundę jako liczbę z wiodącym zerem |
| tttt | Wyświetlaj numer seryjny czasu jako pełny czas. |
| AM/PM | Użyj zegara 12-godzinnego i wyświetlaj AM lub PM, aby wskazać przed lub po południu. |
| przed południem | Użyj zegara 12-godzinnego i użyj am lub pm, aby wskazać przed lub po południu |
| A/P | Użyj zegara 12-godzinnego i użyj A lub P, aby wskazać przed lub po południu |
| a/p | Użyj zegara 12-godzinnego i użyj a lub p, aby wskazać przed lub po południu |
