Суммировать только ячейки, содержащие формулы в Excel
Предположим, у вас есть список значений в Excel, некоторые из них созданы с помощью формул, теперь вы просто хотите суммировать только ячейки, содержащие формулы, как показано на скриншоте ниже. Как можно было выполнить эту задачу в Excel?
Суммировать только ячейки, содержащие формулы в Excel
В Excel вы можете создать простую формулу на основе функций СУММПРОИЗВ и ISFORMULA для суммирования только ячеек формулы в диапазоне ячеек, общий синтаксис:
- range: Диапазон данных, из которого вы хотите суммировать ячейки формулы.
Введите или скопируйте приведенную ниже формулу в пустую ячейку и нажмите Enter ключ, чтобы получить расчет, см. снимок экрана:
Пояснение к формуле:
= СУММПРОИЗВ (B2: B12 * ISFORMULA (B2: B12)):
- ISFORMULA (B2: B12): Эта функция ISFORMULA используется для проверки наличия формулы в ячейке. Если ячейка содержит формулу, она вернет ИСТИНА, а в противном случае - ЛОЖЬ. Итак, вы получите такой массив: {ЛОЖЬ; ИСТИНА; ЛОЖЬ; ЛОЖЬ; ИСТИНА; ЛОЖЬ; ЛОЖЬ; ИСТИНА; ЛОЖЬ; ЛОЖЬ; ИСТИНА}.
- B2:B12*ISFORMULA(B2:B12)= {50;150;35;45;200;80;95;45;50;30;108}*{FALSE;TRUE;FALSE;FALSE;TRUE;FALSE;FALSE;TRUE;FALSE;FALSE;TRUE}: Математическая операция устанавливает значения ИСТИНА и ЛОЖЬ в 1 и 0. Итак, умножение двух массивов будет таким: {50; 150; 35; 45; 200; 80; 95; 45; 50; 30; 108} * {0; 1; 0; 0; 1; 0; 0; 1; 0; 0; 1} = {0; 150; 0; 0; 200; 0; 0; 45; 0; 0; 108}.
- SUMPRODUCT(B2:B12*ISFORMULA(B2:B12))= SUMPRODUCT({0;150;0;0;200;0;0;45;0;0;108}): Наконец, функция СУММПРОИЗВ суммирует все значения в массиве, чтобы получить результат: 503.
Советы: Суммировать только значения, пропускать формулы
Если вам нужно суммировать значения, пропуская ячейки формулы, вы можете добавить функцию НЕ в приведенную выше формулу следующим образом:
Используемая относительная функция:
- SUMPRODUCT:
- Функцию СУММПРОИЗВ можно использовать для умножения двух или более столбцов или массивов вместе, а затем получения суммы произведений.
- ФОРМУЛА:
- Функция Excel ISFORMULA возвращает ИСТИНА, если ячейка содержит формулу, и ЛОЖЬ, если нет.
Другие статьи:
- Сумма, если начинается или заканчивается конкретным текстом или символами
- Чтобы суммировать значения, если соответствующие ячейки начинаются или заканчиваются определенным значением, вы можете применить функцию СУММЕСЛИ с подстановочным знаком (*), чтобы получить ее. В этой статье мы подробно расскажем, как пользоваться формулой.
- Сумма, если ячейки содержат определенный текст в другом столбце
- Из этого туториала Вы узнаете, как суммировать значения, если ячейки содержат определенный или частичный текст в другом столбце. В качестве примера возьмем диапазон данных ниже, чтобы получить общее количество продуктов, содержащих текст «Футболка», с этой задачей в Excel могут справиться как функция СУММЕСЛИ, так и функция СУММПРОИЗВ.
- Суммирование наименьших или нижних значений N на основе критериев
- В предыдущем руководстве мы обсудили, как суммировать наименьшие n значений в диапазоне данных. В этой статье мы выполним дополнительную расширенную операцию - суммируем наименьшие n значений на основе одного или нескольких критериев в Excel.
Лучшие инструменты для работы в офисе
Kutools for Excel - поможет вам выделиться из толпы
Kutools для Excel может похвастаться более чем 300 функциями, Гарантия того, что то, что вам нужно, находится на расстоянии одного клика...
Вкладка Office - включение чтения и редактирования с вкладками в Microsoft Office (включая Excel)
- Одна секунда для переключения между десятками открытых документов!
- Уменьшите количество щелчков мышью на сотни каждый день, попрощайтесь с рукой мыши.
- Повышает вашу продуктивность на 50% при просмотре и редактировании нескольких документов.
- Добавляет эффективные вкладки в Office (включая Excel), как в Chrome, Edge и Firefox.
