Функция СЧЕТЗ в Excel — это мощный инструмент для подсчета количества ячеек, содержащих данные любого типа, включая числа, текст, даты и формулы. Однако в некоторых сценариях может потребоваться исключить формулы из подсчета, чтобы получить точные результаты подсчета данных. В этой статье мы рассмотрим различные способы достижения этой цели в Excel, а также приведем примеры кода.

Метод 1: использование функции ЕСЛИ
Функция ЕСЛИ позволяет нам условно включать или исключать ячейки из подсчета в зависимости от того, содержат ли они формулу. Вот пример того, как можно использовать функцию ЕСЛИ для исключения формул:

=COUNTA(range)-COUNTIF(range,"=*")

В этой формуле «диапазон» представляет собой диапазон ячеек, который вы хотите посчитать. Функция СЧЕТЕСЛИ с подстановочным знаком («=*») подсчитывает количество ячеек, содержащих формулы. Вычитание этого количества из общего количества с помощью COUNTA дает желаемый результат.

Метод 2: использование функции СУММПРОИЗВ
Функция СУММПРОИЗВ может использоваться для подсчета ячеек, соответствующих определенным критериям. Объединив ее с функцией ISFORMULA, вы можете исключить формулы из подсчета. Вот пример:

=SUMPRODUCT(--(NOT(ISFORMULA(range))))

В этой формуле «диапазон» представляет собой диапазон ячеек, который вы хотите посчитать. Функция ЕСФОРМУЛА возвращает массив значений ИСТИНА или ЛОЖЬ, указывающий, содержит ли каждая ячейка диапазона формулу. Функция НЕ преобразует эти значения в противоположные (ЛОЖЬ в ИСТИНА и ИСТИНА в ЛОЖЬ). Двойной отрицательный знак (–) приводит значения ИСТИНА и ЛОЖЬ к 1 и 0, позволяя СУММПРОИЗВ подсчитывать ячейки, которые не содержат формул.

Метод 3: применение макроса VBA
VBA Excel (Visual Basic для приложений) позволяет выполнить более расширенную настройку. Вы можете создать собственную функцию VBA, чтобы исключать формулы при подсчете ячеек. Вот пример такой функции:

Function CountExcludingFormulas(rng As Range) As Long
    Dim cell As Range
    Dim count As Long
    count = 0
    For Each cell In rng
        If Not cell.HasFormula Then
            count = count + 1
        End If
    Next cell
    CountExcludingFormulas = count
End Function

Чтобы использовать эту функцию, нажмите «Alt + F11», чтобы открыть редактор VBA. Вставьте новый модуль и вставьте код. Затем вы можете использовать эту функцию на своем листе, как любую другую функцию Excel:

=CountExcludingFormulas(range)

Когда точный подсчет данных имеет решающее значение в Excel, исключение формул становится важным. В этой статье мы рассмотрели три метода достижения этой цели: использование функции ЕСЛИ, использование функции СУММПРОИЗВ и применение макроса VBA. Используя эти методы, вы можете получить точное количество ячеек, исключая формулы, что повышает точность анализа данных в Excel.

Не забудьте оптимизировать формулу Excel COUNTA, чтобы исключить формулы и повысить эффективность анализа данных.