Note: The other languages of the website are Google-translated. Back to English
English English

Суммировать только ячейки, содержащие формулы в Excel

Предположим, у вас есть список значений в Excel, некоторые из них созданы с помощью формул, теперь вы просто хотите суммировать только ячейки, содержащие формулы, как показано на скриншоте ниже. Как можно было выполнить эту задачу в Excel?


Суммировать только ячейки, содержащие формулы в Excel

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

=SUMPRODUCT(range*ISFORMULA(range))
  • range: Диапазон данных, из которого вы хотите суммировать ячейки формулы.

Введите или скопируйте приведенную ниже формулу в пустую ячейку и нажмите Enter ключ, чтобы получить расчет, см. снимок экрана:

=SUMPRODUCT(B2:B12*ISFORMULA(B2:B12))


Пояснение к формуле:

= СУММПРОИЗВ (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(B2:B12*NOT(ISFORMULA(B2:B12)))


Используемая относительная функция:

  • SUMPRODUCT:
  • Функцию СУММПРОИЗВ можно использовать для умножения двух или более столбцов или массивов вместе, а затем получения суммы произведений.
  • ФОРМУЛА:
  • Функция Excel ISFORMULA возвращает ИСТИНА, если ячейка содержит формулу, и ЛОЖЬ, если нет.

Другие статьи:

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

Лучшие инструменты для работы в офисе

Kutools for Excel - поможет вам выделиться из толпы

Хотите быстро и безупречно выполнять свою повседневную работу? Kutools for Excel предлагает мощные расширенные функции 300 (объединение книг, сумма по цвету, разделение содержимого ячеек, дата преобразования и т. Д.) И экономия 80% времени для вас.

  • Рассчитан на 1500 сценариев работы, помогает решить 80% задач Excel.
  • Уменьшите количество нажатий на клавиатуру и мышь каждый день, избавьтесь от усталости глаз и рук.
  • Станьте экспертом по Excel за 3 минуты. Больше не нужно запоминать какие-либо болезненные формулы и коды VBA.
  • 30-дневная неограниченная бесплатная пробная версия. 60-дневная гарантия возврата денег. Бесплатное обновление и поддержка 2 года.
Лента Excel (с установленным Kutools for Excel)

Вкладка Office - включение чтения и редактирования с вкладками в Microsoft Office (включая Excel)

  • Одна секунда для переключения между десятками открытых документов!
  • Уменьшите количество щелчков мышью на сотни каждый день, попрощайтесь с рукой мыши.
  • Повышает вашу продуктивность на 50% при просмотре и редактировании нескольких документов.
  • Добавляет эффективные вкладки в Office (включая Excel), точно так же, как Chrome, Firefox и новый Internet Explorer.
Снимок экрана Excel (с установленной вкладкой Office)
Сортировать комментарии по
Комментарии (0)
Оценок пока нет. Оцените первым!
Здесь еще нет комментариев
Оставляйте свои комментарии
Публикация как гость
×
Оценить этот пост:
0   Характеристики
Предлагаемые места