Excel: правильно рассчитать условные суммы
Видео по теме: (May 2024).
В Excel вы можете рассчитать суммы, которые не учитывают все элементы выделенной области - так называемые условные суммы. Мы покажем вам, как сделать наиболее разумный расчет.
Три разные функции для условных сумм в Excel
Excel предлагает различные варианты и функции для условных итогов. Правильный выбор зависит от типа условий:
- Если фиксированное условие применяется к каждому рассматриваемому значению, используйте функцию SUMIF.
- Если необходимо принять во внимание несколько условий, существует также функция SUMIFS, начиная с Excel 2007.
- Однако при переменных условиях вам нужна матричная формула или функция SUM PRODUCT.
- В следующих параграфах мы объясняем три функции на примерах.
SUMIF: условные суммы с фиксированными условиями
- Предположим, что данные находятся в ячейках с A1 по A10 и различные значения в ячейках с B1 по C10 используются для условий.
- Первая задача с фиксированным условием означает, например, суммирование всех значений в столбце A, для которых соответствующее значение в столбце B приблизительно больше 10.
- Для этого используйте формулу »= SUMIF (B1: B10;«> 10 »; A1: A10)«.
- Первый аргумент »B1: B10« определяет область, подлежащую оценке, за которой следует условие »«> 10 »«. Обратите внимание, что условия с символами или операторами должны быть в кавычках.
- Наконец, »A1: A10« определяет область, подлежащую суммированию.
- Диапазоны для условий и дат также могут быть идентичными, так что формулы »= SUMIF (A1: A10;«> 10 »)« достаточно.
SUM IF: условные суммы с несколькими условиями
Начиная с Excel 2007, функция SUMIFS расширяет этот принцип до нескольких максимум до 127 условий:
- Обратите внимание на другой порядок аргументов. Обобщаемая область находится здесь первой. За этим следует область критериев и соответствующее условие, при котором области всегда должны указываться отдельно.
- Например, формула "= SUMIFS (A1: A10; B1: B10;"> 10 "; C1: C10;" <100 ")« суммирует все значения в столбце A, для которых соответствующие значения в столбце B больше 10 и соответствующие значения в столбце C меньше 100.
Суммарный продукт: условная сумма с переменными условиями
Для переменных переменных, таких как «если значение в столбце B больше значения в столбце C», требуется матричная формула для оценки:
- Значения, которые необходимо учитывать, выбираются умножением на логическое значение.
- Для этого введите «= SUM (A1: A10 * (B1: B10> C1: C10))» и всегда закрывайте запись, даже после изменения комбинацией клавиш [Ctrl] + [Shift] + [Enter], так что формула в скобках.
- В этом случае вы можете достичь того же результата с помощью функции "= СУММА ПРОДУКТА (A1: A10; 1 * (B1: B10> C1: C10))".
- Однако вы должны заметить, что сначала нужно умножить второй аргумент на 1, чтобы преобразовать логическое значение сравнения в число.
Примечание. Практически во всех случаях вы можете создать итоговую сумму с помощью вспомогательного столбца. Это также рекомендуется при разработке расчета для проверки результатов сложных формул. Совет: мы объясним, как добавить много ячеек в Excel здесь.