Sprawdzanie, czy komórka jest ukryta

Autor: Piotr Dynia

Data: 31-03-2015 r.

W obszernych zestawieniach często ukrywa się wybrane wiersze lub kolumny, aby poddać analizie tylko część tabeli. Niezwykle przydatna jest wówczas możliwość odczytania, czy dana komórka jest ukryta czy nie. Stosuje się w takich przypadkach funkcję SUMY.POŚREDNIE. Dzięki niej można przeprowadzić obliczenia z pominięciem komórek ukrytych. Jednak przy obszernym zestawieniu o poziomym układzie metoda ta staje się mało efektywna.

Ze względu na to, że Excel nie oferuje oddzielnej funkcji arkuszowej przekazującej odpowiednią informację, zbuduj własną, która będzie działać tak samo, jak inne standardowo używane.

W tym celu:

Otwórz Edytor Visual Basic za pomocą kombinacji klawiszy lewy Alt + F11.
Następnie z menu Insert wybierz polecenie Module.
Wprowadź następujący kod funkcji:

Public Function WIDOCZNA(Cells As Range) As Boolean

WIDOCZNA = Not (Cells.EntireRow.Hidden Or Cells.EntireColumn.Hidden)

End Function

Teraz wywołaj polecenie File/Close and Return to Microsoft Excel. Edytor zostanie zamknięty i powrócisz do arkusza Excela.

Używanie funkcji jest proste i intuicyjne: wprowadza się ją jak standardową funkcję wbudowaną, poprzedziwszy wcześniej znakiem równości. Jako argument podaje się odniesienie do komórki, której stan chce się sprawdzić. W wyniku zwracana jest wartość PRAWDA, gdy komórka jest widoczna, oraz wartość FAŁSZ, gdy ukryta. Działanie formuły w arkuszu prezentuje rysunek 1.

Rys. 1. Nowa funkcja działa poprawnie

Dzięki zbudowanej funkcji można teraz wygodnie przeprowadzać obliczenia, decydując, czy dane w komórkach ukrytych powinny zostać uwzględnione czy nie.

Aby obliczyć sumę z zakresu liczb:

Załóż obok kolumnę pomocniczą, w której dokonasz sprawdzenia stanu komórek za pomocą nowo utworzonej funkcji.
Wprowadź do komórki C1 formułę:

=WIDOCZNA(B1)

i skopiuj ją w dół na wymagany zakres. Przykładowy arkusz wygląda następująco:

Rys. 2. Kolumna pomocnicza dla obliczeń

Aby obliczyć sumę podanej kolumny liczb z uwzględnieniem widocznych komórek:

Zastosuj następującą formułę tablicową:

=SUMA(B1:B8*C1:C8)

Zatwierdź ją kombinacją klawiszy Ctrl + Shift + Enter.

Ukryj teraz dowolny wiersz z zakresu ujętego formułą i zaobserwuj różnicę pomiędzy wynikiem sumowania standardowego a wykorzystującego dodatkową kolumnę z wynikami działania funkcji WIDOCZNA.

Rysunek 3 pokazuje wynik działania sumowania przy ukrytych wierszach 5. i 6.