ЕСЛИ логическая функция
Примеры использования функции ЕСЛИ





Как выполнить обработку ошибок с помощью ЕСЛИОШИБКА. Как ЕСЛИ выполнить проверку данных по нескольким условиям - И, ИЛИ, вложенные ЕСЛИ

Конспекты

Курсы

Практикум


Скачать файл
Теги: Бесплатный курс Excel, самоучитель Excel, конспекты тренинга Excel, без регистрации.

Примеры использования функции ЕСЛИ

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

Примеры и особенности использования логических функций ЕСЛИ

Для занятия Вам потребуется книга Excel с заготовками таблиц, по которым составлен конспект - этот файл прикреплен в самом низу страницы (без проблем откроется в Excel версии 2007 и новее)

 

Знакомство с ЕСЛИ

В этом уроке будем разбираться, как устроена функция ЕСЛИ [IF], в каких случаях ее применять и каким именно образом, потому что нередки ситуации, когда алгоритм расчета Вам известен и понятен, но применить его нужно только в каком-то определенном случае, в зависимости от выполнения некоторых условий. И вот тут на помощь приходит функция ЕСЛИ [IF]

Для начала разберем такую задачу - при работе в Excel неоднократно сталкиваемся с ситуацией, когда функция не может выполнить расчет в силу определенных причин (деление на ноль, нет данных, неверно написано имя функции или адрес ячейки (часто использованы русские буквы для названий столбцов вместо английских) и пр.), и для таких случаев предусмотрена возможность вывести в ячейке любое значение, которое пользователю будет удобно, вместо стандартного сообщения об ошибке, начинающегося со знака #

В моем примере (Таблица 1) нужно высчитать среднюю цену за 1 коробку продукции, причем не по каждой из позиций указано количество коробок, и как следствие возникает ошибка вида #ДЕЛ/0!, от которой очень хотелось бы избавиться. Сделать это можно, если вложить расчет средней цены (в ячейках столбца Цена за 1 коробку простая формула, при помощи которой Всего, руб. делим на Кол-во коробок)

Кстати, если проверять и исправлять расчеты времени нет, а документ нужно срочно распечатать, то можно установить настройку, которая позволит не выводить на печать сообщения об ошибках: вкладка Разметка страницы [Page Layout] – группа Параметры страницы [Page Setup] – кнопка вызова одноименного окна настроек в нижнем правом углу группы (серая диагональная стрелочка) и далее в окне Параметры страницы [Page Setup] переходим на вкладку Лист [Sheet] и в правой центральной части находим список команд Ошибки ячеек как: [Cell error as:], из которого нужно выбрать <нет> [<blank>]. В электронной версии документа значения ошибок останутся, а вот на печать выводится не будут – неплохо, правда?

Если же конечная задача поставлена следующим образом: не выводить сообщения об ошибках в ячейках, то поступаем следующим образом:

У меня получилось вот так =ЕСЛИОШИБКА(C5/B5;0):

Чтобы завершить оформление примера, хочу убрать с листа нули, а для этого либо в формате ячеек [format cell] нужно выбрать Финансовый [Accounting], либо отключить эту настройку здесь: Файл [File] – Параметры [Options] – Дополнительно [Advanced] – Показывать параметры для следующего листа / Параметры отображения листа [Display_options_for_this_worksheet] – и снимаем флажок Показывать нули в ячейках, которые содержат нулевые значения [Show_a_zero_in_cells_that_have_zero_value]

ЕСЛИ

В рамках данного примера можно поставить и другую задачу: посчитать в ячейке среднюю цену, но если она получается больше 2.000 руб. за 1 ед., то вместо результата вывести слово  «Много!»  Для такого решения потребуется уже функция ЕСЛИ [IF] в чистом виде: E5=ЕСЛИ(C5/B5>=2000;"Много!";C5/B5)

Конечно, помните, что составляя первую формулу, условие в ней может и не выполняться, но при правильных настройках функция сама выберет вариант ответа из двух полей значение_если_истина и значение_если_ложь. В этом и суть - Вы задаёте проверку, варианты действий на оба случая - хоть выполнилась проверка, хоть нет, а функция уже в каждой строке проверит Ваше условие и поймёт, что делать - у неё ведь есть от Вас инструкция :)

Правда, после выполнения всех манипуляций, в ячейках для строк, в которых по количеству коробок ячейка осталась пуста, нужно бы предусмотреть обработку ошибки, а для этого вкладываем весь получившийся расчет в функцию ЕСЛОИШИБКА [IFERROR], алгоритм действий уже был расписан выше: =ЕСЛИОШИБКА(ЕСЛИ(C5/B5>=2000;"Много!";C5/B5);0)

 

Вложенные ЕСЛИ

В следующем примере (Таблица 2 на том же листе) нужно предусмотреть скидку на следующих условиях: если количество единиц товара составит тысячу и более, то данные столбца Всего, руб. нужно умножить на 100%-Скидка макс. (если умножить на значение скидки, то и получится сумма скидки как таковой, а не стоимость товара за минусом скидки), в противном случае (если ложь) просто устанавливаем ссылку на текущее значение столбца Всего, руб. без изменений: =ЕСЛИ(B21>=1000;C21*(100%-F21);C21)

Но это только часть задачи. Дальше возникла необходимость предусмотреть скидку вполовину от максимальной для количества от 500 ед. Что делать в таком случае? Вкладывать следующую ЕСЛИ [IF] в расчет и вот каким образом: для аргумента Значение_если_ложь [Value_if_false] разделить расчет на две части (изменения по сравнению с предыдущим расчетом выделены заливкой): =ЕСЛИ(B21>=1000; C21*(100%-F21); ЕСЛИ(B21>=500; C21*(100%-F21/2); C21))

Чтобы правильно вложить функции ЕСЛИ [IF] друг в друга, соблюдайте простое правило – начните отсекать ваши условия с одной стороны, последовательно, чтобы каждый раз новой функцией работать в уже оставшемся интервале. В моем примере можно было соблюсти вот такой порядок: если меньше 500, то скидки нет (просто ссылка на ячейку), в противном случае если количество меньше 1.000 (при этом оно уже не может быть меньше 500 – этот вариант мы разобрали только что), тогда применяем скидку в половину от максимальной, ну а для всех оставшихся случаев – максимальную. Попробуйте - потренируйтесь. Вкладывать функции ЕСЛИ [IF] нужно в том случае, когда для одной и той же ячейки нужно выполнить несколько разных проверок

 

Как задать несколько проверок для одного условия?

Но бывает так, что одно условие формируется путе мпроверки сразу нескольких ячеек - в таком случае вложенные ЕСЛИ [IF] не подойдут, тут нужен другой инструмент. Например, скидку будем применять только в том случае, если количество больше либо равно тысячи или же доставка не учтена в стоимости (см. Таблица 3). На самом деле это легко сделать, если привлечь на помощь функцию ИЛИ [OR]  – в скане условие проверки выделено заливкой: =ЕСЛИ(ИЛИ(B37>=1000;D37="нет");C37*(100%-E37);C37)

И конечно же для проверки нескольких условий, но не одного из них, а одновременно всех, используем функцию И [AND] – расчет выглядит точно также, стало другим только имя функции: =ЕСЛИ(И(B37>=1000;D37="нет");C37*(100%-E37);C37)

ВАЖНО (!) При работе с конспектами помним, что все названия программных продуктов являются зарегистрированными торговыми марками. Никакая часть настоящего издания ни в каких целях не может быть воспроизведена в какой бы то ни было форме и какими бы то ни было средствами, будь то электронные и механические, включая фотокопирование и запись на магнитный носитель, если на это нет письменного разрешения автора

Похожие материалы

Пользовательские имена ячеек и диапазонов
Имена ячеек
Поиск влияющих и зависимых ячеек. Пользовательские имена - способы создания имен, использование в новых формулах, подстановка имен в старые формулы.

Консолидация данных, операции с группой листов
Консолидация
Обычная СУММ и команда Консолидация позволяют получить итоговый отчёт на основании нескольких исходных таблиц данных. Они могут быть идентичными ли отличаться друг от друга.

Правила и закономерности, по которым работает Excel
Общие принципы, лежащие в основе любого инструмента программы Excel
Как понять программу Excel? Это несложно, список правил и настроек приведён в этом уроке.

Удаление дубликатов. Поиск удаленных данных
Удаление дубликатов
На вкладе Данные есть кнопка Удалить дубликаты, которая поможет составить справочник уникальных значений или избавиться от задвоенных записей в таблице.

Заказчики

Я сотрудничаю с многими известными компаниями. Вот некоторые из них:

ВТБ МегаФон Глобус Абсолют

Связаться с автором

Оставьте ваше имя и адрес электронной почты, в ближайшее время я свяжусь
с вами для консультации и отвечу на вопросы!



Оставляя заявку, вы соглашаетесь на обработку ваших персональных данных.