Spis treści
Sformułowanie problemu
Załóżmy, że mamy taki stół, przy którym musimy „tańczyć” na co dzień:
Komu stół wydaje się mały – pomnóż go w myślach dwadzieścia razy przez obszar, dodając jeszcze kilka bloków i dwa tuziny dużych miast.
Zadanie polega na tymczasowym usunięciu z ekranu wierszy i kolumn, które są aktualnie niepotrzebne do pracy, tj.
- ukryj szczegóły według miesiąca, pozostawiając tylko kwartały
- ukryj sumy według miesięcy i kwartałów, pozostawiając tylko sumę na pół roku
- ukrywać miasta, które są w tej chwili niepotrzebne (pracuję w Moskwie – po co mam zobaczyć Petersburg?) itp.
W rzeczywistości istnieje morze przykładów takich stołów.
Metoda 1: Ukrywanie wierszy i kolumn
Metoda, szczerze mówiąc, jest prymitywna i niezbyt wygodna, ale można o niej powiedzieć dwa słowa. Wszelkie poprzednio wybrane wiersze lub kolumny w arkuszu można ukryć, klikając prawym przyciskiem myszy nagłówek kolumny lub wiersza i wybierając polecenie z menu kontekstowego Ukryj (Ukrywać):
W przypadku wyświetlania odwróconego wybierz sąsiednie wiersze/kolumny i, klikając prawym przyciskiem myszy, wybierz z menu odpowiednio pokaz (Odkryj).
Problem polega na tym, że z każdą kolumną i wierszem trzeba sobie radzić indywidualnie, co jest niewygodne.
Metoda 2. Grupowanie
Jeśli wybierzesz wiele wierszy lub kolumn, a następnie wybierz z menu Dane – Grupa i Struktura – Grupa (Dane — Grupa i Zarys — Grupa), zostaną one ujęte w nawias kwadratowy (zgrupowane). Ponadto grupy mogą być zagnieżdżane jedna w drugiej (dozwolone jest do 8 poziomów zagnieżdżenia):
Wygodniejszym i szybszym sposobem jest użycie skrótu klawiaturowego do grupowania wstępnie wybranych wierszy lub kolumn. Alt+Shift+strzałka w prawoi do rozgrupowania Alt+Shift+strzałka w lewo, Odpowiednio.
Ta metoda ukrywania niepotrzebnych danych jest znacznie wygodniejsza – możesz albo kliknąć na przycisk z „+"Lub"-„, lub na przyciskach z poziomem grupowania liczbowego w lewym górnym rogu arkusza – wtedy wszystkie grupy żądanego poziomu zostaną jednocześnie zwinięte lub rozwinięte.
Tak więc, jeśli Twoja tabela zawiera wiersze lub kolumny podsumowania z funkcją sumowania sąsiednich komórek, czyli szansa (nie w 100% prawdziwa), że Excel stworzy wszystkie potrzebne grupy w tabeli jednym ruchem – przez menu Dane – grupa i struktura – tworzenie struktury (Dane — Grupa i zarys — Utwórz zarys). Niestety taka funkcja działa bardzo nieprzewidywalnie i czasami robi kompletne bzdury na złożonych tabelach. Ale możesz spróbować.
W Excelu 2007 i nowszych wszystkie te radości są na karcie Dane (Data) w grupie Structure (Zarys):
Metoda 3. Ukrywanie zaznaczonych wierszy/kolumn za pomocą makra
Ta metoda jest prawdopodobnie najbardziej wszechstronna. Dodajmy pusty wiersz i pustą kolumnę na początku naszego arkusza i zaznaczmy dowolną ikoną te wiersze i kolumny, które chcemy ukryć:
Teraz otwórzmy Edytor Visual Basic (ALT + F11), wstaw nowy pusty moduł do naszej książki (menu Wstaw – moduł) i skopiuj tam tekst dwóch prostych makr:
Sub Hide() Dim cell As Range Application.ScreenUpdating = False 'Wyłącz aktualizację ekranu, aby przyspieszyć dla każdej komórki In ActiveSheet.UsedRange.Rows(1).Cells 'Iterowanie po wszystkich komórkach w pierwszym wierszu If cell.Value = "x " Then cell .EntireColumn.Hidden = True 'if in cell x - ukryj kolumnę Next For Each cell In ActiveSheet.UsedRange.Columns(1).Cells 'przejdź przez wszystkie komórki pierwszej kolumny If cell.Value = "x" Then cell.EntireRow.Hidden = True 'jeśli w komórce x - ukryj wiersz Next Application.ScreenUpdating = True End Sub Sub Show() Columns.Hidden = False 'anuluj wszystkie ukrywanie wierszy i kolumn Rows.Hidden = False End Sub Sub
Jak można się domyślić, makro Ukryj ukrywa i makro Pokazać – Wyświetla oznaczone z tyłu wiersze i kolumny. W razie potrzeby do makr można przypisać skróty klawiszowe (Alt + F8 i przycisk parametry) lub utwórz przyciski bezpośrednio na arkuszu, aby uruchomić je z zakładki Deweloper – Wstaw – Przycisk (Programista — Wstaw — Przycisk).
Metoda 4. Ukrywanie wierszy/kolumn o danym kolorze
Załóżmy, że w powyższym przykładzie, przeciwnie, chcemy ukryć sumy, czyli fioletowe i czarne wiersze oraz żółte i zielone kolumny. Wtedy nasze poprzednie makro będzie musiało zostać nieco zmodyfikowane poprzez dodanie, zamiast sprawdzania obecności „x”, sprawdzenia dopasowania koloru wypełnienia do losowo wybranych komórek próbki:
Sub HideByColor() Dim cell As Range Application.ScreenUpdating = False Dla każdej komórki In ActiveSheet.UsedRange.Rows(2).Cells If cell.Interior.Color = Range("F2").Interior.Color Then cell.EntireColumn.Hidden = True If cell.Interior.Color = Range("K2").Interior.Color Then cell.EntireColumn.Hidden = True Następny dla każdej komórki In ActiveSheet.UsedRange.Columns(2).Cells If cell.Interior.Color = Zakres ("D6").Interior.Color Then cell.EntireRow.Hidden = True If cell.Interior.Color = Range("B11").Interior.Color Then cell.EntireRow.Hidden = True Next Application.ScreenUpdating = True End Sub Nie możemy jednak zapomnieć o jednym zastrzeżeniu: to makro działa tylko wtedy, gdy komórki tabeli źródłowej zostały ręcznie wypełnione kolorem, a nie przy użyciu formatowania warunkowego (jest to ograniczenie właściwości Interior.Color). Na przykład, jeśli automatycznie podświetliłeś wszystkie transakcje w tabeli, w których liczba jest mniejsza niż 10, używając formatowania warunkowego:
… a chcesz je jednym ruchem ukryć, to poprzednie makro trzeba będzie „dokończyć”. Jeśli masz Excel 2010-2013, możesz wyjść za pomocą zamiast właściwości Wnętrze własność DisplayFormat.Wnętrze, który wyświetla kolor komórki, niezależnie od tego, jak został ustawiony. Makro do ukrycia niebieskich linii może wtedy wyglądać tak:
Sub HideByConditionalFormattingColor() Dim cell As Range Application.ScreenUpdating = False Dla każdej komórki In ActiveSheet.UsedRange.Columns(1).Cells If cell.DisplayFormat.Interior.Color = Range("G2").DisplayFormat.Interior.Color Then komórka .EntireRow.Hidden = Prawda następna Application.ScreenUpdating = Prawda koniec Sub Komórka G2 jest pobierana jako próbka do porównania kolorów. Niestety nieruchomość? Format wyswietlania pojawił się w Excelu dopiero od wersji 2010, więc jeśli masz Excel 2007 lub starszy, będziesz musiał wymyślić inne sposoby.
- Co to jest makro, gdzie wstawić kod makr, jak ich używać
- Automatyczne grupowanie na listach wielopoziomowych