Formuła ogólna
=N(CELL("width",A1)>0)
Podsumowanie
Aby policzyć widoczne kolumny w zakresie, możesz użyć formuły pomocniczej opartej na funkcji KOMÓRKA z funkcją JEŻELI, a następnie podsumować wyniki za pomocą funkcji SUMA. W pokazanym przykładzie formuła w I4 to:
=SUM(key)
gdzie „klucz” to nazwany zakres B4: F4, a wszystkie komórki zawierają tę formułę skopiowaną w poprzek:
=N(CELL("width",B4)>0)
Aby zobaczyć zmianę licznika, musisz wymusić obliczenia za pomocą klawisza F9 lub wykonać inną zmianę arkusza, która wyzwala ponowne obliczenie. Poniżej znajduje się ten sam arkusz z widocznymi wszystkimi kolumnami:
Uwaga: wpadłem na główny pomysł tej formuły w doskonałej witrynie wmfexcel.com.
Wyjaśnienie
Nie ma bezpośredniego sposobu na wykrycie ukrytej kolumny z formułą w programie Excel. Możesz pomyśleć o użyciu funkcji SUMA CZĘŚCIOWA, ale SUMA CZĘŚCIOWA działa tylko z zakresami pionowymi. W rezultacie podejście opisane w tym przykładzie jest obejściem opartym na formule pomocniczej, którą należy wprowadzić w zakresie obejmującym wszystkie kolumny z zakresu zainteresowania. W tym przykładzie ten zakres to nazwany „klucz” zakresu.
W przedstawionym przykładzie kolumny C i E są ukryte. Formuła pomocnicza, wprowadzona w B4 i skopiowana przez B4: F4, jest oparta na funkcji CELL:
=CELL("width",B4)>0
Funkcja CELL zwróci tylko szerokość komórki w widocznej kolumnie. Gdy kolumna jest ukryta, ta sama formuła zwróci zero. Sprawdzając, czy wynik jest większy od zera, otrzymujemy wynik PRAWDA lub FAŁSZ. Funkcja N służy do przekształcania wartości TRUE na 1 i FALSE na zero, więc ostateczny wynik to 1, gdy kolumna jest widoczna, i 0, gdy kolumna jest ukryta. Ładny.
Aby policzyć widoczne kolumny, używamy formuły funkcji SUMA w I4:
=SUM(key)
gdzie „klucz” to nazwany zakres B4: F4.
Policz ukryte kolumny
Aby policzyć ukryte kolumny, formuła w I5 to:
=COLUMNS(key)-SUM(key)
Funkcja COLUMNS zwraca sumę kolumn z zakresu (5), a funkcja SUMA zwraca sumę widocznych kolumn (3), więc ostateczny wynik to 2:
=COLUMNS(key)-SUM(key) =5-3 =2
Z innymi operacjami
Gdy masz już „klucz kolumny”, możesz go używać w innych operacjach. Na przykład można SUMA wartości w widocznych kolumnach, używając funkcji SUM w następujący sposób:
=SUM(key*B6:F6)
Chociaż każda komórka w B6: F6 zawiera liczbę 25, SUMA zwróci 75, gdy kolumny C i E są ukryte, jak pokazano w przykładzie.
Uwaga: funkcja CELL jest funkcją ulotną. Funkcje niestabilne zwykle są przeliczane przy każdej zmianie arkusza, więc mogą powodować problemy z wydajnością. Niestety CELL nie odpala, gdy kolumna jest ukryta lub ponownie widoczna. Oznacza to, że nie zobaczysz poprawnych wyników, dopóki arkusz nie zostanie ponownie obliczony, albo z normalną zmianą, albo po naciśnięciu klawisza F9.
