Принципы работы функции ЕСЛИ в Excel

Функция ЕСЛИ является одной из самых популярных в Excel — с её помощью легко получать разные значения для множества ячеек в зависимости от выполнения какого-либо условия, находить ошибки и проверять, выполняются ли условия в выбранном диапазоне. Мы указываем критерий для проверки, а ЕСЛИ сравнивает его с данными в ячейках, а затем выдаёт необходимый результат. В статье рассмотрим, что делает функция ЕСЛИ в Excel, как её прописать и использовать для решения рабочих задач при одном или нескольких условиях.

В своей деятельности Excel помогает специалистам из различных сфер. Одни из них — аналитики данных. Стать Data Science и трудоустроиться помогают на курсах «Аналитик данных» от Skypro и «Data Scientist: с нуля до middle» от Нетологии.

Функция ЕСЛИ в Excel — что это и как работает

Функция создана для того, чтобы проверять условия — если оно выполняется, то возвращается одно значение, если нет — другое. Поэтому результат может быть два: ИСТИНА и ЛОЖЬ. Синтаксис функции ЕСЛИ в Excel выглядит таким образом:

=ЕСЛИ(логическое выражение, значение если «да», значение если «нет»)

Подробнее разбёрем, что значит каждый из составных компонентов этой функции в Excel:

• Логическое выражение — это обязательно условие. Оно возвращает значение «истина» или «ложь».
• Значение если «да» — действие, которое будет выполнено, если ответ верный.
• Значение если «нет» — ещё одно действие, которое выполняется в случае, когда ответ отрицательный.

Первый аргумент — логический вопрос. Ответом на него может быть лишь «да» или «нет», «истина» или «ложь». Чтобы правильно задать вопрос, нужно составить логическое выражение, используя операторы “=”, “>”, “<”, “>=”, “<=”, “<>”.

Курс «Excel для анализа данных» поможет разобраться даже в сложных функциях программы. Вы научитесь строить понятные интерактивные отчеты, работать с большим массивом данных, быстро анализировать и визуализировать логические концепты.

Простой пример функции ЕСЛИ в Excel: 

=ЕСЛИ(А1=30; [значение_если_истина]; [значение_если_ложь]) — если А1 равно 30, то выражение А1=30 даст значение ИСТИНА, а если меньше или больше 30, то ЛОЖЬ.

Второй пример: =ЕСЛИ(А1>30; [значение_если_истина]; [значение_если_ложь]) — если в ячейки А1 число больше 30, то А1>30 вернет ИСТИНА, а если число меньше, то ЛОЖЬ

В функции ЕСЛИ можно использовать текстовые аргументы в формуле. Пример: =ЕСЛИ(А1>=5;»Зачет сдал»;»Зачет не сдал»).

Представители IT-сферы в своей работе также пользуются Excel и другими специализированными программами. Менеджеры проектов разбираются в системе планирования и отслеживают прогресс, уделяют внимание аналитике и взаимодействию членов команды между собой. Project manager в IT — перспективная профессия, которую легко освоить на онлайн-курсах.

В аргумент логическое_выражение функции ЕСЛИ также можно вложить текстовое условие при любом использовании. 

Пример: =ЕСЛИ(А1=»Стол»;10;290).

Формула возвращает значение 10, если ячейка А1 содержит строку «Стол», и 290, если в ней находится другое логическое значение. 

Использование Excel требует предельного внимания и практики. Научиться работать с программой и быстро выполнять даже сложные задачи от руководства можно с помощью курсов:

• Excel для аналитиков — ProductStar
• Excel и Google-таблицы для роста бизнеса — Eduson Academy

Создание функции ЕСЛИ с одним условием

Сначала проверим, как функция в Excel работает с одним условием, а чуть ниже — с несколькими. Представим, что у нас есть задача — подобрать смартфон для клиента, который пришёл в магазин с техникой. Выбор большой, как и количество моделей, поэтому мы сделали таблицу в Excel и внесли в неё основные параметры каждого телефона. Это объём внутренней и внешней памяти, количество ядер, цвет, размер диагонали экрана. Для начала покажем, как использовать функцию ЕСЛИ в Excel с одним условием. Например, потенциальный покупатель заинтересован в моделях, которые стоят до 50 000 рублей.

Алгоритм программы изучит выбранный нами диапазон из таблицы и проверит, подходят ли данные из ячеек исходному запросу. Потом функция в Excel вернётся в указанную ячейку в виде формулы и покажет результат: ИСТИНА или ЛОЖЬ. Поэтому первым делом для использования функции нужно создать дополнительный столбец — назовём его «До 50 тыс. руб.».

Затем выделите первую ячейку созданного столбца и откройте окно для поиска функций. Существует два способа, чтобы сделать это: найдите вкладку «Формулы» и нажмите «Вставить функцию» или щелкните на «fx» в строке ссылок на любой из вкладок. Справа в окне «Построитель формул» через поиск найдите функцию ЕСЛИ и нажмите на «Вставить функцию». Теперь Excel предлагает заполнить поля комментариев «Лог_выражение», «Значение_если_истина», «Значение_если_ложь». В функции три аргумента — вы создаёте запрос и отмечаете два варианта ответа на него. 

«Лог_выражение» — это то, что будет проверять алгоритм функции. В нашем примере — стоимость товара. В этом случае необходимо, чтобы функция выбрала лишь те смартфоны, стоимость которых равна или меньше 50 000 рублей. 

Для этого в построителе формул функции в Excel поставьте курсор в окно «Лог_выражение». На нужно первое значение в столбце «Цена, руб.» из ячейки G2. Название ячейки перенесётся в «Лог_выражение» построителя формул, тут же появится и в строке ссылок. К значению G2 в формулу нужно вложить наш запрос: <=50000. Формула в строке ссылок теперь выглядит так: fx=ЕСЛИ(G2<=50000).

Бесплатный курс «Excel: простые шаги для оптимизации работы с данными» разработан специально для новичков, которые только изучают особенности инструмента. Этот симулятор поможет решить несколько реальных задач в реальном времени. Вы быстро поймёте, как с помощью функций и комментариев Excel оптимизировать собственную работу. 

«Значение_если_истина» — второй аргумент, если его значение будет соответствовать тому, что было указано в условии, именно этот результат отобразится в ячейке. Нам надо придумать, что будет возвращать функция, если посчитает, что товар стоит 50 тысяч рублей или дешевле. В окне аргумента истины введите подходящее значение — это может быть как символ, так и слово. Чтобы было более понятно, выберем текст «Подходящий вариант».

«Значение_если_ложь» — при несовпадении значения с условием функция приносит указанные данные. Под эту категорию попадают смартфоны дороже 50 000 рублей. В качестве третьего аргумента укажите текст «Не подходит».

В строке ссылок должна отобразиться следующая формула:

fx=ЕСЛИ(G2<=50000;»Подходящий вариант»;»Не подходит»).

Получить результат функции в реальном времени просто: нажмите «Готово» в построителе формул справа. В первой ячейке в созданном столбце будет виден результат: «Подходящий вариант». Цена телефона 37 500 рублей, покупатель сможет купить себе эту модель. Теперь протяните данные из первой ячейки вниз до конца списка, чтобы функция в Excel оценила стоимость всех товаров в таблице и вернула результат в виде текста — так вы поймёте соответствуют ли смартфоны критерию.

Системные администраторы, которые работают со списками и базами данных, также пользуются Excel для проверки и оптимизации. Это направление востребовано, а специалисты получают высокую заработную плату. Один из вариантов обучения — курс «Системный администратор: LINUX И BASH» от ProductStar.

Как сделать функцию ЕСЛИ с несколькими условиями

Для настройки функции ЕСЛИ в Excel с несколькими условиями мы добавим к ней другие операторы — «И» и «ИЛИ». В качестве примера этого случая их синтаксис можно расписать так:

И: =ЕСЛИ(И(условие; другое условие); значение, если ИСТИНА; значение, если ЛОЖЬ)

ИЛИ: =ЕСЛИ(ИЛИ(условие; другое условие); значение, если ИСТИНА; значение, если ЛОЖЬ).

Например, новому покупателю важно не одно условие, как выше, а три: объём памяти, цена и размер диагонали. Как же написать функцию ЕСЛИ в таком случае? Первый шаг — добавление нового столбца с параметрами («До 50 тыс.руб., 128 или 256 Гб, диаг. не менее 6.7». Далее аналогично с предыдущим примером вызываем окно в построителе формул и заполняем аргументы функции в Excel. 

Особое внимание уделим аргменту «Лог_выражение». В него необходимо добавить дополнительный аргумент «И», а затем открыть скобку. Сначала введём два варианта памяти — здесь нужен ещё один дополнительный аргумент «ИЛИ». Для него также нужна ещё одна скобка. Выбираем первое значение в столбце «Память» — ячейку C2. Пишем знак равенства и ищем в этом же столбце значение из запроса: «128 Гб» — ячейку С3. Ставим знак точки с запятой. Теперь опять выбираем первое значение столбца «Память», проставляем знак равенства, находим подходящие данные в другой ячейке: «256 Гб» — ячейка C4. Закрываем скобку, ставим точку с запятой.

Важно закрепить ячейки с данными о памяти. Если у вас Windows, по очереди выберите значения в строке ссылок, затем нажмите F4. На macOS действуем по-другому: выбираем все значения ячеек и нажимаем сочетание клавиш Cmd + T. Без закрепления не получится протянуть получившуюся формулу вниз, чтобы она эффективно сработала на всех строках таблицы в Excel. 

SEO-специалист может работать с данными, сортировать, производить фильтрацию и преобразование именно в Excel. Если давно мечтали об этой профессии, обратите внимание на онлайн-курс с сайта tutortop «Контекстная реклама и SEO».

Следующий шаг — надо выбрать первое значение из столбца «Диагональ экрана» — ячейку F2. Добавляем к нему значение >6,7. Опять ставим знак точки с запятой. 

Последнее значение — в столбце «Цена.» — ячейка G2. У нас есть чёткий запрос: <=50000. Теперь можно закрыть скобку.

Теперь формула в строке ссылок должна выглядеть так:

fx=ЕСЛИ(И(ИЛИ(B2=$C$3;C2=$B$8);F2>6,7;G2<=50000)).

Окошки «Значение_если_истина» и «Значение_если_ложь» заполним также — «Подходящий вариант», «Не подходит». 

Итоговый вариант после проверки:

fx=ЕСЛИ(И(ИЛИ(B2=$C$3;C2=$B$8);F2>6,7;G2<=50000));Подходящий вариант»;»Не подходит»).

В первой ячейке отобразится результат выполнения функции в Excel. Благодаря закреплению мы можем вниз до конца таблицы — так функция проверит все указанные данные о смартфонах и покажет, какие из них подойдут под требования покупателя.

Как сравнить данные в двух таблицах в Excel

Сделать это можно с помощью функции СЧЁТЕСЛИ. Функция входит в группу статистических функций и помогает найти число ячеек по выбранному критерию в Excel. В неё входит два аргумента: диапазон — значения, которые необходимо подсчитать и критерий — условие, созданное специально для подсчёта. 

Как пример в этом случае — у вас есть две таблицы с техническими характеристиками двух миксеров. Их надо сравнить, а отличия — выделить цветом. Выделяем диапазон с данными первого миксера. Затем нажимаем «Условное форматирование» → «Создать правило» → «Использовать формулу для определения форматируемых ячеек». 

В окне формулы для форматирования пишем функцию =СЧЁТЕСЛИ (сравниваемый диапазон; первая ячейка первой таблицы)=0. Результат:

=СЧЁТЕСЛИ($C$2:$C$15;A2)=0

Важно задать форматирование ячеек. Для этого возле образца нужно нажать на кнопку «Формат». Мы можем использовать заливку, ведь она наиболее удобна для этих целей. А в качестве диапазона назначаем имя столбца: =СЧЁТЕСЛИ(критерий;C2)=0. Так гораздо удобнее, чем прописывать весь диапазон вручную.

Вы бухгалтер и ищите отдельный курс для прокачки навыков? Eduson Academy предлагает пройти обучение по Excel и Google-таблицам специально для бухгалтеров. Вас ждёт много практики, интерактивные упражнения и личный куратор.

Подводим итоги

Функция ЕСЛИ в Excel имеет не одну форму, существует несколько разных видов, каждый из которых позволяет автоматически выполнять стандартные действия для расчёта, сравнения, выбора с одним или несколькими условиями. На примерах в разных случаях мы рассмотрели, как работает эта функция, как правильно её использовать, как составлять команды. Кстати, часто из-за функции ЕСЛИ таблицы в Excel называют программируемыми. Основа и различные виды функции помогают делать из листа в Excel настоящую программу, ведь благодаря такому подходу есть возможность действовать по сложным алгоритмам без ошибок.