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

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

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

  1. Выделите ячейки, которые вы хотите суммировать.

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

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

  4. Установите переключатель «Формулы».

  5. Щелкните ОК. Excel изменяет выбранные ячейки так, чтобы были выбраны только те, которые содержат формулы.

На этом этапе вы можете посмотреть в строке состояния (внизу окна Excel) и увидеть сумму выбранных ячеек, содержащих формулы.

Если вы предпочитаете подход на основе формул, одним из ключевых факторов здесь будет версия Excel, которую вы используете. Microsoft представила функцию ISFORMULA в выпуске Excel 2013, поэтому, если вы используете эту версию (или более позднюю), определить желаемую сумму довольно просто.

Просто используйте эту формулу:

=SUMPRODUCT(A1:A5,--ISFORMULA(A1:A5))

В этой формуле предполагается, что диапазон данных, который вы хотите суммировать, равен A1: A5. Знак «двойной минус» перед функцией ISFORMULA используется для преобразования значений ИСТИНА и ЛОЖЬ (возвращаемых ISFORMULA) в 1 или 0.

Если вы предпочитаете использовать формулу массива, вы можете использовать следующую формулу:

=SUM(IF(ISFORMULA(A1:A6),A1:A6))

Просто не забудьте ввести Ctrl + Shift + Enter, и вы получите правильный результат.

Если вы используете версию Excel старше Excel 2010, эти формулы не будут работать. Вместо этого вам придется полагаться на определяемую пользователем функцию, чтобы добиться цели:

Function SumFormulas(ByVal r As Range)

Dim c As Range     Dim s As Double

s = 0     For Each c In r.Cells              If c.HasFormula And IsNumeric(c) Then             s = s + c.Value         End If     Next c     SumFormulas = s End Function

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

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

=SumFormulas(A1:C7)

Подход на основе макросов также будет работать в версиях Excel после Excel 2010, если по какой-то причине вы не хотите полагаться на функцию ISFORMULA. (Например, если вам необходимо обеспечить совместимость со старыми версиями Excel.)

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

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