Подсчёт с критерием «ИЛИ» в Excel

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

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

    =COUNTIF(A1:A8,"Google")+COUNTIF(A1:A8,"Facebook")
    =СЧЁТЕСЛИ(A1:A8;"Google")+СЧЁТЕСЛИ(A1:A8;"Facebook")

    Подсчёт с критерием или в Excel

  2. Однако, если потребуется подсчитать количество строк, которые содержат или "Google" или "Stanford" (рассматриваем сразу два столбца), то мы не сможем просто использовать формулу, дважды содержащюю функцию COUNTIF (СЧЕТЕСЛИ) (см. рисунок ниже). В этом случае строки, которые содержат и "Google" и "Stanford" (в одной строке) подсчитываются дважды, а они должны учитываться только один раз. 4 – вот ответ, который нам нужен.

    =COUNTIF(A1:A8,"Google")+COUNTIF(B1:B8,"Stanford")
    =СЧЁТЕСЛИ(A1:A8;"Google")+СЧЁТЕСЛИ(B1:B8;"Stanford")

    Подсчёт с критерием или в Excel

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

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

    Подсчёт с критерием или в Excel

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

    • Вспомним, что ИСТИНА = 1, а ЛОЖЬ = 0.
    • Для строки 1: =ЕСЛИ(ИСТИНА+ИСТИНА;1;0) ► =ЕСЛИ(2;1;0) ► 1, т.е. первая строка будет учитываться.
    • Для строки 2: =ЕСЛИ(ЛОЖЬ+ЛОЖЬ;1;0) ► =ЕСЛИ(0;1;0) ► 0, т.е. вторая строка учитываться не будет.
    • Для строки 3: =ЕСЛИ(ЛОЖЬ+ИСТИНА;1;0) ► =ЕСЛИ(1;1;0) ► 1, т.е. третья строка будет учитываться, и т.д.
  2. Теперь все, что нам нужно – это функция SUM (СУММ), которая сосчитает единицы. Для достижения этой цели мы добавляем функцию SUM (СУММ) и заменяем A1 на A1:A8, а B1 на B1:B8.

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

    Подсчёт с критерием или в Excel

  3. Ввод формулы закончим нажатием Ctrl+Shift+Enter.

    Подсчёт с критерием или в Excel

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

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

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

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

    Подсчёт с критерием или в Excel

05.08.2015 08:56
1398

Комментарии

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