Подсчет с критерием «ИЛИ» в Excel может оказаться сложнее, чем кажется на первый взгляд. В этой статье вы найдёте несколько нетрудных, но полезных примеров.
- Начнем с простого. Требуется подсчитать количество ячеек, содержащих «Google» или «Facebook» (первый столбец). Вот формула, которая сделает это:
=COUNTIF(A1:A8,"Google")+COUNTIF(A1:A8,"Facebook")
=СЧЁТЕСЛИ(A1:A8;"Google")+СЧЁТЕСЛИ(A1:A8;"Facebook")
- Однако, если потребуется подсчитать количество строк, которые содержат или «Google» или «Stanford» (рассматриваем сразу два столбца), то мы не сможем просто использовать формулу, дважды содержащюю функцию COUNTIF (СЧЕТЕСЛИ) (см. рисунок ниже). В этом случае строки, которые содержат и «Google» и «Stanford» (в одной строке) подсчитываются дважды, а они должны учитываться только один раз. 4 – вот ответ, который нам нужен.
=COUNTIF(A1:A8,"Google")+COUNTIF(B1:B8,"Stanford")
=СЧЁТЕСЛИ(A1:A8;"Google")+СЧЁТЕСЛИ(B1:B8;"Stanford")
- Вот почему нам нужна формула массива. Мы используем функцию IF (ЕСЛИ), чтобы проверить, попадается ли в строке «Google» или «Stanford»:
=IF((A1="Google")+(B1="Stanford"),1,0)
=ЕСЛИ((A1="Google")+(B1="Stanford");1;0)
- Пояснение:
- Вспомним, что ИСТИНА = 1, а ЛОЖЬ = 0.
- Для строки 1:
=ЕСЛИ(ИСТИНА+ИСТИНА;1;0) ► =ЕСЛИ(2;1;0) ► 1
, т.е. первая строка будет учитываться. - Для строки 2:
=ЕСЛИ(ЛОЖЬ+ЛОЖЬ;1;0) ► =ЕСЛИ(0;1;0) ► 0
, т.е. вторая строка учитываться не будет. - Для строки 3:
=ЕСЛИ(ЛОЖЬ+ИСТИНА;1;0) ► =ЕСЛИ(1;1;0) ► 1
, т.е. третья строка будет учитываться, и т.д.
- Теперь все, что нам нужно – это функция 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))
- Ввод формулы закончим нажатием Ctrl+Shift+Enter.
Примечание: Строка формул указывает, что это формула массива, заключая её в фигурные скобки {}. Их не нужно вводить самостоятельно. Они исчезнут, когда вы начнете редактировать формулу.
- Пояснение:
- Диапазон (массив констант), созданный с помощью функции IF (ЕСЛИ) хранится в памяти Excel, а не в ячейках листа.
- Массив констант выглядит следующим образом: {1;0;1;0;1;0;1;0}.
- Этот массив констант используется в качестве аргумента для функции SUM (СУММ), давая результат 4.
- Мы можем сделать ещё один шаг вперёд. К примеру, подсчитать количество строк, которые содержат «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))
Урок подготовлен для Вас командой сайта office-guru.ru
Источник: http://www.excel-easy.com/examples/count-with-or-criteria.html
Перевела: Ольга Гелих
Правила перепечатки
Еще больше уроков по Microsoft Excel