Automatyczne formatowanie numerów w tabelach przestawnych programu Excel

Długoletnią frustracją związaną z tabelami przestawnymi w programie Microsoft Excel jest fakt, że wszelkie pola zawierające liczby muszą być zawsze ręcznie formatowane w celu dodania przecinków i/lub usunięcia miejsc dziesiętnych. Ponadto zmiany formatowania muszą być dokonywane po jednym polu na raz, aby zapewnić, że kolejne zmiany nie spowodują zaniku formatowania liczb podczas odświeżania tabeli przestawnej.

W tym artykule pokażę, jak pojedyncza linia kodu programującego może spowodować, że Excel automatycznie sformatuje numery w miarę dodawania nowych pól do tabeli przestawnej.Stwórzmy najpierw tabelę przestawną, jak pokazano na rysunku 1. Dane bazowe składają się z trzech kolumn: Produkt , Przypadki sprzedane , oraz Sprzedaż ogółem . Aby utworzyć tabelę obrotową:

  1. Kliknij dowolną komórkę w swoich danych źródłowych. Upewnij się, że każda kolumna ma unikalny tytuł, który pojawia się w jednej komórce.
  2. Wybierz menu Excela Wstaw .
  3. Kliknąć polecenie PivotTable .
  4. Kliknąć OK w oknie dialogowym Tworzenie tabeli obrotu .
  5. Pojawi się nowy arkusz z pustą tabelą przestawną.

Rysunek 1: Polecenie PivotTable pojawia się w menu programu Excel Insert .

Wstawić .

Następnym krokiem jest dodanie pól do tabeli przestawnej:

  1. Wybierz Produkt .
  2. Wybierz Przypadki sprzedane . Zauważ, że to pole nie ma żadnego formatowania liczb.
  3. Kliknij prawym przyciskiem myszy Suma sprzedanych przypadków .
  4. Wybierz Format numeru . Jeśli wybierzesz Formatuj komórki sformatujesz tylko jedną komórkę, zamiast całej kolumny.
  5. Wybierz Numer .
  6. Opcjonalnie ustawić liczbę miejsc po przecinku na 0 .
  7. 0

  8. Zaznaczyć pole wyboru Użyć 1000 Separatora .
  9. Kliknij OK .
  10. Numery w kolumnie Suma przypadków sprzedanych są teraz sformatowane.

Rysunek 2: Domyślnie tabele przestawne nie mają żadnego formatowania liczb.

:

Wykonywanie tych czynności za każdym razem, gdy dodajesz nowe pole do tabeli przestawnej, może być numbumbingiem umysłu, więc zamiast tego zautomatyzujemy to za pomocą makra w Excelu. Pierwszym krokiem jest ustalenie, czy posiadasz Osobisty podręcznik do makra . Aby to zrobić, należy określić, czy polecenie Unhide jest wyłączone w menu Excela View . Jeśli tak, wykonaj te kroki:

  1. Wybierz menu Excela Widok .
  2. Kliknąć dolną połowę przycisku Makra .
  3. Kliknij Zapisz Makro .
  4. Z menu rozwijanego wybierz Personal Macro Workbook .
  5. Kliknąć OK w oknie dialogowym Record Macro . W tym celu nie są konieczne żadne zmiany.
  6. Kliknij przycisk Stop Recording , który będzie kwadratem obok słowa Gotowy na dole ekranu programu Excel.

Rysunek 3: Włączyć, a następnie wyłączyć rejestrator makro, aby utworzyć osobisty skoroszyt z makrami.

Makra, które umieszczasz w Personal Macro Workbook są dostępne do użytku w każdym skoroszycie Excela, więc mogą one stać się rozszerzeniami samego Excela. Następnym krokiem jest stworzenie samego makra, jak pokazano na rysunku 4:

  1. Wybierz menu Excela Widok .
  2. Kliknąć polecenie Unhide .
  3. Kliknij OK , aby odinstalować podręcznik PERSONAL.XLSB .
  4. Aktywuj ponownie menu Excela Widok .
  5. Kliknij górną połowę polecenia Makra .
  6. Wpisz nazwę dla swojego makra, np. PivotTableNumberFormat . Pierwszy znak musi być literą i nie może mieć żadnych spacji.
  7. Kliknąć przycisk Create .

Rysunek 4: Makra w ramach Osobisty podręcznik do makr może być używany w ramach

W tym momencie na ekranie pojawi się Visual Basic Editor . Skopiuj i wklej te linie kodu do makra, jak pokazano na rysunku 5:

On Error Resume Next

ActiveSheet.PivotTables(ActiveCell.PivotTable.Name).DataBodyRange.NumberFormat = “#,##0;(#,##0)”

On Error GoTo 0

Rysunek 5: Jedna linia kodu programującego ustawi tabelę przestawną, aby automatycznie formatować nowe kolumny, które dodasz.

Jeśli zdecydujesz się na wpisanie tego ręcznie, ostatni znak na Error GoTo 0 to zero . Oba W wierszu Error znajduje się instrukcja dla Excela, aby pominąć kod programu, który się nie powiedzie; tak jak gdybyś miał uruchomić to makro, gdy kursor nie znajduje się w tabeli przestawnej.

W tym momencie możesz zamknąć Visual Basic Editor , który przywróci Cię do Excela. Personal Macro Workbook powinien jeszcze pojawić się na ekranie. Kliknij polecenie Ukryj w menu Excela Widok , aby ukryć ten podręcznik, który uruchamia się automatycznie po otwarciu Excela, ale pozostaje wizualnie ukryty, aby nie przeszkadzać.

Następne kroki to dodanie ikony do paska narzędzi szybkiego dostępu , aby można było uruchomić makro za pomocą jednego kliknięcia:

  1. Kliknij strzałkę na końcu paska narzędzi szybkiego dostępu .
  2. Wybierz Więcej poleceń .
  3. Wybierz Makra z listy Wybierz polecenia z listy .
  4. Kliknij dwukrotnie na makro, które będzie miało nazwę PERSONAL.XLSB!PivotTableNumberFormat .
  5. Opcjonalnie: CIick Zmodyfikuj , aby zmienić ikonę związaną z tym poleceniem, jak również napis.
  6. Kliknij OK .

Rysunek 6: Makro można uruchomić jednym kliknięciem myszy po przypisaniu go do ikony Paska narzędzi szybkiego dostępu .

Aby przetestować swoją pracę, najpierw należy wyczyścić formatowanie liczb z kolumny Suma przypadków sprzedanych . Jednym ze sposobów jest wybranie numerów, a następnie wybranie Ogólnych z listy w sekcji Numery w menu głównym programu Excel. Jak pokazano na rysunku 7:

  1. Kliknąć dowolną komórkę w tabeli przestawnej.
  2. Kliknij ikonę, którą dodałeś do paska narzędzi szybkiego dostępu .
  3. Numery w kolumnie Suma przypadków sprzedanych powinny być ponownie sformatowane.
  4. Wybrać Sprzedaż ogółem z pola Tabela obrotowa .
  5. Nowa kolumna Suma sprzedaży ogółem powinna pojawić się w tabeli przestawnej i być automatycznie sformatowana.

Rysunek 7: Makro wprowadza tabelę przestawną w stan, w którym nowe pola są automatycznie przypisywane do formatowania numerów.

Musisz uruchomić to makro na każdej tworzonej przez siebie tabeli przestawnej, ale po dodaniu dowolnych pól liczbowych zostaną one automatycznie sformatowane. Jeśli chcesz wstawić miejsca dziesiętne, zmień kod formatu w makrze na ” #,##0.00;(#,##0.00)” lub użyj ” $#,##0;($#,##0)” jeśli chcesz mieć znaki dolara bez miejsc dziesiętnych.

Po zamknięciu programu Excel należy kliknąć Tak lub Zapisz , gdy pojawi się monit o zapisanie zmian wprowadzonych w Osobistym Makroboku . Jeśli chcesz usunąć utworzoną ikonę Quick Access Toolbar , kliknij prawym przyciskiem myszy na ikonę i wybierz Remove From Toolbar .