Общая формула
=N(CELL("width",A1)>0)
Резюме
Чтобы подсчитать видимые столбцы в диапазоне, вы можете использовать вспомогательную формулу, основанную на функции ЯЧЕЙКА с ЕСЛИ, а затем подсчитать результаты с помощью функции СУММ. В показанном примере формула I4 имеет следующий вид:
=SUM(key)
где «ключ» - это именованный диапазон B4: F4, и все ячейки содержат эту формулу, скопированную через:
=N(CELL("width",B4)>0)
Чтобы увидеть изменение счетчика, вы должны принудительно выполнить расчет с помощью F9 или выполнить другое изменение рабочего листа, которое запускает пересчет. Ниже представлен тот же рабочий лист со всеми видимыми столбцами:
Примечание: я столкнулся с основной идеей этой формулы на отличном сайте wmfexcel.com.
Объяснение
Нет прямого способа обнаружить скрытый столбец с формулой в Excel. Вы можете подумать об использовании функции ПРОМЕЖУТОЧНЫЙ ИТОГ, но ПРОМЕЖУТОЧНЫЙ ИТОГ работает только с вертикальными диапазонами. В результате подход, описанный в этом примере, представляет собой обходной путь, основанный на вспомогательной формуле, которую необходимо ввести в диапазон, включающий все столбцы в интересующей области. В этом примере этот диапазон является именованным диапазоном «ключ».
В показанном примере столбцы C и E скрыты. Вспомогательная формула, введенная в B4 и скопированная в B4: F4, основана на функции ЯЧЕЙКА:
=CELL("width",B4)>0
Функция ЯЧЕЙКА вернет только ширину ячейки в видимом столбце. Когда столбец скрыт, та же формула вернет ноль. Проверяя, больше ли результат нуля, мы получаем результат ИСТИНА или ЛОЖЬ. Функция N используется для приведения ИСТИНА к 1 и ЛОЖЬ к нулю, поэтому конечный результат равен 1, когда столбец виден, и 0, когда столбец скрыт. Ницца.
Для подсчета видимых столбцов мы используем формулу функции СУММ в I4:
=SUM(key)
где «ключ» - это именованный диапазон B4: F4.
Подсчет скрытых столбцов
Для подсчета скрытых столбцов формула в I5 выглядит так:
=COLUMNS(key)-SUM(key)
Функция COLUMNS возвращает общее количество столбцов в диапазоне (5), а функция SUM возвращает сумму видимых столбцов (3), поэтому окончательный результат равен 2:
=COLUMNS(key)-SUM(key) =5-3 =2
С другими операциями
Когда у вас есть «ключ столбца», вы можете использовать его с другими операциями. Например, вы можете использовать СУММ в видимых столбцах следующим образом:
=SUM(key*B6:F6)
Хотя каждая ячейка в B6: F6 содержит число 25, СУММ вернет 75, когда столбцы C и E скрыты, как показано в примере.
Примечание. Функция ЯЧЕЙКИ является непостоянной. Изменчивые функции обычно пересчитываются при каждом изменении рабочего листа, поэтому они могут вызвать проблемы с производительностью. К сожалению, CELL не срабатывает, когда столбец скрывается или снова становится видимым. Это означает, что вы не увидите правильных результатов до тех пор, пока таблица не будет пересчитана, либо с обычным изменением, либо путем нажатия F9.
