Обща формула
=N(CELL("width",A1)>0)
Обобщение
За да преброите видими колони в диапазон, можете да използвате помощна формула, базирана на функцията CELL с IF, след това да се получат резултатите с функцията SUM. В показания пример формулата в I4 е:
=SUM(key)
където "ключ" е наименованият диапазон B4: F4 и всички клетки съдържат тази формула, копирана в:
=N(CELL("width",B4)>0)
За да видите промяната на броя, трябва да принудите изчислението с F9 или да извършите друга промяна на работния лист, която задейства преизчисляване. По-долу е един и същ работен лист с всички видими колони:
Забележка: Натъкнах се на основната идея за тази формула на отличния сайт wmfexcel.com.
Обяснение
Няма директен начин за откриване на скрита колона с формула в Excel. Може да помислите да използвате функцията SUBTOTAL, но SUBTOTAL работи само с вертикални диапазони. В резултат на това подходът, описан в този пример, е заобиколно решение, основано на помощна формула, която трябва да бъде въведена в диапазон, който включва всички колони в обхвата на интереса. В този пример този диапазон е наименованият диапазон "ключ".
В показания пример колоните C и E са скрити. Помощната формула, въведена в B4 и копирана през B4: F4, се основава на функцията CELL:
=CELL("width",B4)>0
Функцията CELL ще връща само ширина за клетка във видима колона. Когато колона е скрита, същата формула ще върне нула. Чрез проверка дали резултатът е по-голям от нула, получаваме TRUE или FALSE резултат. Функцията N се използва за принуждаване на TRUE до 1 и FALSE до нула, така че крайният резултат е 1, когато се вижда колона, и 0, когато колона е скрита. Хубаво.
За да броим видими колони, използваме формулата на функцията SUM в I4:
=SUM(key)
където "ключ" е наименованият диапазон B4: F4.
Бройте скритите колони
За да броите скрити колони, формулата в I5 е:
=COLUMNS(key)-SUM(key)
Функцията COLUMNS връща общите колони в диапазона (5), а функцията SUM връща сумата от видимите колони (3), така че крайният резултат е 2:
=COLUMNS(key)-SUM(key) =5-3 =2
С други операции
След като поставите „ключа на колоната“ на място, можете да го използвате с други операции. Например, можете да SUM стойности във видими колони, като използвате SUM по следния начин:
=SUM(key*B6:F6)
Въпреки че всяка клетка в B6: F6 съдържа числото 25, SUM ще върне 75, когато колона C и E са скрити, както е показано в примера.
Забележка: Функцията CELL е летлива функция. Летливите функции обикновено се преизчисляват при всяка промяна на работния лист, така че могат да причинят проблеми с производителността. За съжаление CELL не се задейства, когато дадена колона е скрита или отново е видима. Това означава, че няма да видите правилни резултати, докато работният лист не преизчисли, или с нормална промяна, или чрез натискане на F9.
