Загальна формула
=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(key*B6:F6)
Хоча кожна комірка в B6: F6 містить число 25, SUM поверне 75, коли стовпці C та E приховані, як показано в прикладі.
Примітка: Функція CELL - це летюча функція. Нестабільні функції зазвичай перераховуються при кожній зміні робочого аркуша, тому вони можуть спричинити проблеми з продуктивністю. На жаль, КЛІТИНИ не спрацьовують, коли стовпець прихований або знову стає видимим. Це означає, що ви не побачите правильних результатів, доки аркуш не перерахує, ні при звичайній зміні, ні натисканням клавіші F9.
