Подсчет ячеек, содержащих формулу

Роб задался вопросом, есть ли способ подсчитать количество ячеек, содержащих формулы, в строке или столбце. Ответ довольно прост, используя функцию «Перейти к» в Excel. Выполните следующие действия:

  1. Отобразите рабочий лист, для которого вы хотите подсчитать.
  2. Выберите строку или столбец, в которых вы хотите подсчитать формулы.
  3. Нажмите F5 или Ctrl + G . Excel отображает диалоговое окно “Перейти”.
  4. Нажмите кнопку “Специальная”. Excel отображает диалоговое окно «Перейти к специальному». (См. Рис. 1.)
  5. Рис. 1. Диалоговое окно «Перейти к специальному».

  6. Убедитесь, что выбран переключатель «Формулы».
  7. Нажмите OK.

Вот и все. Excel выбирает все ячейки в строке или столбце, содержащие формулы. (Если вы пропустите шаг 2, Excel выберет все формулы на всем листе.) В нижней части экрана, в строке состояния, вы можете увидеть количество выбранных ячеек. (См. Рис. 2.)

Рис. 2. В строке состояния отображается количество выбранных ячеек.

Если по какой-то причине вы не видите счетчик в строке состояния, проверьте, чтобы убедиться, что у вас есть строка состояния, настроенная для отображения счетчиков. Просто щелкните правой кнопкой мыши любое пустое место в строке состояния и выберите «Подсчитать» в появившихся параметрах.

Если вы используете Excel 2013 или более позднюю версию, вы также можете использовать формулу, чтобы выяснить, как многие формулы находятся в диапазоне ячеек, как показано здесь:

= SUMPRODUCT (- ISFORMULA (A: A))

Этот пример возвращает количество всех формулы в столбце A; так же легко можно заменить в формуле другой диапазон ячеек. Какой бы диапазон вы ни указали, он не должен включать ячейку, в которую вы помещаете эту конкретную формулу – это приведет к циклической ссылке и вероятной ошибке.

При желании вы также можете использовать макрос для определить счет. В следующем примере используется тот же подход для определения счетчика, который был описан вручную на предыдущих шагах:

Sub CountFormulas1 () Dim Source As Range Dim iCount As Integer Set Source = ActiveSheet.Range («A : A «) iCount = Source.SpecialCells (xlCellTypeFormulas, 23) .Count ActiveSheet.Range (» D1 «) = iCountEnd Sub

Эта подпрограмма очень быстро возвращает количество всех формул: содержит ячейки в столбце A и помещает это значение в ячейку D1.

Было бы очень полезно, если бы этот подход можно было превратить в определяемую пользователем функцию, например эту:

Функция CountFormulas2 (Source As Range) CountFormulas2 = Source.SpecialCells (xlCellTypeFormulas, 23) .CountEnd Function

Однако это не сработает. Функция всегда возвращает количество ячеек в исходном диапазоне, а не количество ячеек, содержащих формулы.. Это эзотерическая ошибка VBA Excel, заключающаяся в том, что SpecialCells не работает в функциях; он работает только в подпрограммах. Microsoft даже не задокументировала это (которое я могу найти), поэтому я ссылаюсь на него как на «ошибку», а не как на «ограничение».

Существует фактическое ограничение того, что Однако метод SpecialCells может: он может содержать только диапазон до 8192 ячеек. Вы можете анализировать гораздо больший диапазон (как в случае, когда вы просматриваете весь столбец), но результирующее подмножество – результирующий диапазон – может содержать не более 8192 ячеек. Если он содержит больше, то SpecialCells «выйдет из строя» и вернет диапазон (и, следовательно, счетчик), который равен количеству ячеек в исходном диапазоне.

Если вы хотите создать пользователя -определенная функция для определения количества, вам нужно будет полагаться на что-то другое, кроме метода SpecialCells. Вот подход, использующий свойство HasFormula:

Функция CountFormulas3 (Source As Range) Dim c As Range Dim iCount As Integer iCount = 0 For Each c In Source If c.HasFormula Then iCount = iCount + 1 Следующая функция CountFormulas3 = iCountEnd

Если вы решите, чтобы этот макрос оценивал весь столбец или всю строку, будьте готовы немного подождать – макрос может пройти через некоторое время каждая ячейка в столбце или строке. Метод SpecialCells намного быстрее выводит результаты, чем пошаговый обход каждой ячейки.

Источник: frestage.ru

Бытовой вопрос