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

  1. Отобразите лист, для которого вы хотите подсчитать.

  2. Выберите строку или столбец, в котором вы хотите подсчитать формулы.

  3. Нажмите F5 или Ctrl + G. Excel отображает диалоговое окно «Перейти».

  4. Щелкните кнопку Special. Excel отображает диалоговое окно «Перейти к специальному».

(См. Рис. 1.)

  1. Убедитесь, что выбран переключатель Формулы.

  2. Щелкните ОК.

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

image

Рисунок 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") = iCount End Sub

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

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

Function CountFormulas2(Source As Range)

CountFormulas2 = Source.SpecialCells(xlCellTypeFormulas, 23).Count End Function

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

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

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

Вот подход, использующий свойство HasFormula:

Function 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     Next     CountFormulas3 = iCount End Function

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

_Примечание: _

Если вы хотите узнать, как использовать макросы, описанные на этой странице (или на любой другой странице на сайтах ExcelTips), я подготовил специальную страницу, содержащую полезную информацию.

link: / excelribbon-ExcelTipsMacros [Щелкните здесь, чтобы открыть эту специальную страницу в новой вкладке браузера].

ExcelTips — ваш источник экономичного обучения Microsoft Excel.

Этот совет (13330) применим к Microsoft Excel 2007, 2010, 2013, 2016, 2019 и Excel в Office 365.