Урок 5 из 8 · 25 мин · обновлено

Условия: ЕСЛИ, СЧЁТЕСЛИ и СУММЕСЛИ

Как формула выбирает результат по условию: функция ЕСЛИ, условия через И и ИЛИ, несколько вариантов через вложенные ЕСЛИ и ЕСЛИМН, подсчёт и сумма по условию через СЧЁТЕСЛИ и СУММЕСЛИ.

ЕСЛИ: выбор между двумя вариантами

Часто результат зависит от условия: заказ крупный или обычный, доставка платная или бесплатная. Для этого есть функция ЕСЛИ с тремя аргументами:

Формула
=ЕСЛИ(условие; что вернуть, если да; что вернуть, если нет)

Пометим крупные заказы — от 1000 рублей:

Формула
=ЕСЛИ(F2>=1000;"крупный";"обычный")
Результат
крупный

В F2 — 1250, условие верно, сработал второй аргумент. Для сравнения используют знаки =, <> (не равно), >, <, >=, <=.

Главное правило записи: текст в кавычках, числа без кавычек. Слово «крупный» без кавычек таблица примет за имя чего-то несуществующего и покажет #ИМЯ?. А число в кавычках станет текстом, и складывать его потом не получится.

И и ИЛИ: несколько условий

Магазин дарит закладку, если клиент купил блокноты, и не меньше пяти штук. Два условия должны выполниться вместе — для этого есть функция И:

Формула
=ЕСЛИ(И(C2="Блокнот";D2>=5);"подарок";"")
Результат
подарок

Две кавычки подряд "" — пустой текст: если подарка нет, ячейка выглядит пустой.

Функция ИЛИ возвращает ИСТИНА, когда верно хотя бы одно условие. Доставка бесплатна для заказов от 1500 рублей или для ежедневников, остальным — 300 рублей:

Формула
=ЕСЛИ(ИЛИ(F8>=1500;C8="Ежедневник");0;300)
Результат
0

В строке 8 ежедневник за 890 — сумма не дотягивает, зато сработало второе условие. Стоимость доставки здесь — числа, поэтому без кавычек: её можно будет сложить.

Больше двух вариантов

Разделим заказы на три размера: от 1500 — «крупный», от 800 — «средний», остальные — «мелкий». Можно вложить один ЕСЛИ в другой:

Формула
=ЕСЛИ(F2>=1500;"крупный";ЕСЛИ(F2>=800;"средний";"мелкий"))
Результат
средний

Читается по порядку: от 1500? Нет. Тогда от 800? Да — «средний». Порядок проверок важен: первым ставят самое строгое условие. Если начать с «от 800», заказ на 2000 тоже окажется «средним» — ведь 2000 больше 800, и до второй проверки дело не дойдёт.

В новых версиях Excel и в Google Таблицах то же самое можно записать без вложенности — функцией ЕСЛИМН. Пары «условие; результат» идут подряд, а последнее условие ИСТИНА срабатывает для всех остальных случаев:

Формула
=ЕСЛИМН(F2>=1500;"крупный";F2>=800;"средний";ИСТИНА;"мелкий")
Результат
средний

Если вариантов становится пять-шесть, формула превращается в лабиринт. Тогда удобнее вынести правила в отдельную табличку и искать по ней — как это делается, покажет урок 7.

СЧЁТЕСЛИ и СУММЕСЛИ: итоги по условию

Самые полезные в работе условные функции — те, что считают итоги без вспомогательных столбцов.

СЧЁТЕСЛИ — сколько ячеек подходит под условие. Сколько заказов у Ольги и сколько заказов от 1000 рублей:

Формула
=СЧЁТЕСЛИ(B2:B11;"Ольга")
=СЧЁТЕСЛИ(F2:F11;">=1000")
Результат
4
4

Условие со знаком сравнения пишут в кавычках: ">=1000". Если граница лежит в ячейке, её приклеивают знаком &: ">="&M5.

СУММЕСЛИ — сумма значений по условию. Аргументы: где проверять условие, само условие и что складывать.

Формула
=СУММЕСЛИ(B2:B11;"Иван";F2:F11)
Результат
3700

Таблица прошла по столбцу B, нашла строки Ивана и сложила суммы из F только в них. Когда условий несколько — например, Иван и только март, — есть родственники СЧЁТЕСЛИМН и СУММЕСЛИМН.

Попробуйте сами. Добавьте в учебную таблицу столбец I «Размер» с тремя вариантами: крупный, средний, мелкий. Справа, в L5:M7, посчитайте продажи каждого менеджера через СУММЕСЛИ. В L9:M9 посчитайте, сколько получилось средних заказов.

Решение
  1. В I1 — «Размер», в I2 — вложенный ЕСЛИ или ЕСЛИМН, затем протяните до I11:
Формула
=ЕСЛИ(F2>=1500;"крупный";ЕСЛИ(F2>=800;"средний";"мелкий"))

Крупными окажутся два заказа — ежедневники Ольги на 1780 и блокноты Ивана на 2000.

  1. В L5, L6, L7 — имена: Ольга, Иван, Пётр. В M5 — формула с закреплёнными диапазонами, чтобы её можно было протянуть вниз:
Формула
=СУММЕСЛИ($B$2:$B$11;L5;$F$2:$F$11)
Результат
4170

Протяните до M7: у Ивана будет 3700, у Петра — 2840. Условием здесь служит ячейка с именем, поэтому кавычки не нужны.

  1. В L9 — «Средних», в M9:
Формула
=СЧЁТЕСЛИ(I2:I11;"средний")
Результат
5

Проверка: 4170 + 3700 + 2840 = 10710 — столько же, сколько общий итог в M3. Если суммы по менеджерам не сходятся с итогом, ищите опечатку в имени: «Ольга » с пробелом в конце для формулы — другой человек.

Мини-тест: проверьте себя

Закрепите урок: несколько вопросов, разбор ответов — в конце.

  1. Что вернёт =ЕСЛИ(F2>=1000;"крупный";"обычный"), если в F2 лежит 800?

  2. Подарок положен, если товар — блокнот и куплено не меньше пяти штук. Какое условие подойдёт?

  3. Как посчитать, сколько заказов оформила Ольга?

  4. Что делает =СУММЕСЛИ(B2:B11;"Иван";F2:F11)?

  5. Вложенная формула проверяет F2>=800 раньше, чем F2>=1500. Что случится с заказом на 2000?