Мэнди интересуется, есть ли способ суммировать диапазон данных и включать в сумму только те ячейки, которые содержат формулу. Согласно потребностям Мэнди, если ячейка содержит явное значение, а не формулу, то ее не следует включать в сумму.
Есть много способов добиться желаемого результата, но Я остановлюсь только на некоторых из них.
Во-первых, если вам нужно определить сумму только один раз и она не отображается в самом рабочем листе, вы можете выполнить следующие действия:
- Выберите ячейки, которые вы хотите суммировать.
- Нажмите F5 . Excel отображает диалоговое окно “Перейти”.
- Нажмите кнопку “Специальная”. Excel отображает диалоговое окно «Перейти к специальному».
- Установите переключатель «Формулы».
- Нажмите кнопку «ОК». 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, эти формулы не будут работать. Вместо этого вам нужно будет полагаться на определяемую пользователем функцию, чтобы сделать трюк:
Функция 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 = sEnd Function
Обратите внимание, что код проверяет, содержит ли ячейка формулу (с использованием свойства HasFormula) и проверяет, является ли она числовой (с помощью функции IsNumeric). Оба они необходимы, потому что в ячейке может быть текстовая формула, и вы не хотите пытаться включать результаты такой формулы в свою сумму.
используйте эту функцию, вы должны просто использовать следующее в ячейке листа, предполагая, что вы хотите суммировать диапазон A1: C7:
= SumFormulas (A1: C7)
Подход на основе макросов также будет работать в версиях Excel после Excel 2010, если по какой-то причине вы не хотите полагаться на функцию ISFORMULA.. (Например, если вам необходимо обеспечить совместимость со старыми версиями Excel.)