Суммирование с критерием «ИЛИ» в Excel

Суммирование с критерием «ИЛИ» в Excel может оказаться несколько сложнее, чем вы ожидаете. В этой статье вы найдёте несколько простых, но полезных примеров.

  1. Начнем с простого. Допустим, необходимо просуммировать ячейки, которые отвечают следующим критериям: содержат слова "Google" или "Facebook" (один столбец с критериями). С этим легко справится следующая формула:

    =SUMIF(A1:A8,"Google",B1:B8)+SUMIF(A1:A8,"Facebook",B1:B8)
    =СУММЕСЛИ(A1:A8;"Google";B1:B8)+СУММЕСЛИ(A1:A8;"Facebook";B1:B8)

    Суммирование с критерием или в Excel

  2. Если же у нас будет 2 столбца с критериями, и мы захотим также просуммировать ячейки в строках, которые содержат "Google" или "Stanford" (два диапазона критериев), то не можем просто использовать функцию SUMIF (СУММЕСЛИ) дважды (см. рисунок ниже), как в предыдущем примере. Ячейки со значениями в строках 1 и 7 просуммированы два раза, хотя должны подсчитываться только один раз. 10 – вот правильный результат!

    =SUMIF(A1:A8,"Google",C1:C8)+SUMIF(B1:B8,"Facebook",C1:C8)
    =СУММЕСЛИ(A1:A8;"Google";C1:C8)+СУММЕСЛИ(B1:B8;"Facebook";C1:C8)

    Суммирование с критерием или в Excel

  3. Нам нужна формула массива. Используем функцию IF (ЕСЛИ), чтобы проверить, попадаются ли критерии "Google" или "Stanford" в строке:

    =IF((A1="Google")+(B1="Stanford"),1,0)*C1
    =ЕСЛИ((A1="Google")+(B1="Stanford");1;0)*C1

    Суммирование с критерием или в Excel

    Пояснение:

    • ИСТИНА = 1, ЛОЖЬ = 0.
    • Для первой строки, функция IF (ЕСЛИ) возвращает следующее значение:

      =IF(TRUE+TRUE,1,0)*3 ► =IF(2,1,0)*3 ► 3
      =ЕСЛИ(ИСТИНА+ИСТИНА;1;0)*3 ► =ЕСЛИ(2;1;0)*3 ► 3

      Таким образом, значение 3 будет учитываться.

    • Для второй строки:

      =IF(FALSE+FALSE,1,0)*5 ► =IF(0,1,0)*5 ► 0
      =ЕСЛИ(ЛОЖЬ+ЛОЖЬ;1;0)*5 ► =ЕСЛИ(0;1;0)*5 ► 0

      Таким образом, значение 5 учитываться не будет.

    • А для третьей:

      =IF(FALSE+TRUE,1,0)*2 ► =IF(1,1,0)*2 ► 2
      =ЕСЛИ(ЛОЖЬ+ИСТИНА;1;0)*2 ► =ЕСЛИ(1;1;0)*2 ► 2

      То есть, значение 2 учитываться будет, и т.д.

  4. Теперь всё, чего нам не хватает, это функции SUM (СУММ), которая суммирует эти значения. Давайте добавим функцию SUM (СУММ) и заменим А1 на A1:A8, B1 на B1:B8 и C1 на С1:C8.

    =SUM(IF((A1:A8="Google")+(B1:B8="Stanford"),1,0)*C1:C8)
    =СУММ(ЕСЛИ((A1:A8="Google")+(B1:B8="Stanford");1;0)*C1:C8)

    Суммирование с критерием или в Excel

  5. Закончим, нажав Ctrl+Shift+Enter.

    =SUM(IF((A1:A8="Google")+(B1:B8="Stanford"),1,0)*C1:C8)
    =СУММ(ЕСЛИ((A1:A8="Google")+(B1:B8="Stanford");1;0)*C1:C8)

    Суммирование с критерием или в Excel

Примечание: Строка формул указывает, что это формула массива, заключая её в фигурные скобки {}. Их не нужно вводить самостоятельно. Они исчезнут, когда вы начнете редактировать формулу.

  1. Пояснение:

    • Диапазон (массив констант), созданный с помощью функции IF (ЕСЛИ), хранится в памяти Excel, а не в ячейках листа.
    • Массив констант выглядит следующим образом: {1;0;1;0;1;0;1;0}.
    • Он умножается на C1:C8 и это дает {3;0;2;0;4;0;1;0}.
    • Последний массив констант используется в качестве аргумента для функции SUM (СУММ), давая результат 10.
  2. Мы можем сделать ещё один шаг вперёд. К примеру, подсчитать количество строк, которые содержат "Google" и "Stanford", либо "Columbia".

    =SUM(IF((A1:A8="Google")*(B1:B8="Stanford")+(B1:B8="Columbia"),1,0)*C1:C8)
    =СУММ(ЕСЛИ((A1:A8="Google")*(B1:B8="Stanford")+(B1:B8="Columbia");1;0)*C1:C8)

    Суммирование с критерием или в Excel

07.08.2015 16:35
1391

Комментарии

Нет комментариев. Ваш будет первым!