Логические функции в Excel. Часть №1. Excel если функции

Использование функции ЕСЛИ в Excel

≡ 11 Октябрь 2015 · Рубрика: Могучий MS Excel

А А А

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

Теория функции ЕСЛИ

Как вы думаете, что общего между следующими двумя записями:

Если значение в ячейке С1 больше 100 тогда в текущую ячейку записать «План выполнен», иначе записать в текущую ячейку «План не выполнен»;
=ЕСЛИ(C2>100;»План выполнен»;»План не выполнен»).

Думаю любой смог догадаться, что обе записи идентичны по смысловой нагрузке и отличаются по способу записи. Фраза в пункте 1 записана на человеческом языке, а в пункте два записана в формате языка функции Excel.

Функция ЕСЛИ имеет несложный синтаксис:

ЕСЛИ(логическое_выражение;[значение_если_истина];[значение_если_ложь])

Логическое_выражение – обязательный параметр, который будет проверяться. Либо одно из двух булевых значение.

Значение_если_истина – значение, которое будет записано в текущую ячейку, в случае истинности логического выражения.

Значение_если_ложь — значение, которое будет записано в текущую ячейку, в случае лжи логического выражения.

Особенностью значение_если_истина и значение_если_ложь является то, что это не обязательные параметры.

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

Далее в разделе я попробую привести основные примеры записи функции ЕСЛИ.

Пример №1 – Необязательные параметры

Для многих становится непонятным, что означает не обязательный параметр, поэтому поясняю так. Мы можем записать в любую ячейку функцию ЕСЛИ(ИСТИНА;;). Данное выражение хоть и кажется бессмысленными с нашей точки зрение, но хочу сразу сказать, что с точки зрения Excel приведенная запись корректна. Мы видим что второй и третий параметры просто упущены.

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

Предположим, фермер ведет учет урожайности своих фруктовых деревьев. У него есть 10 яблонь. Все они именованы по следующему принципу Яблоня 1, Яблоня 2, Яблоня 3 и т.д.. Фермер составляет таблицу хочет, чтобы напротив груш с урожайность большей средней ставились плюсики. Таблица на рисунке демонстрирует решение данной задачи.

Использование функции ЕСЛИ в таблицах

Колонке D таблицы записана функция =ЕСЛИ(C4>C14;»+»;) и в ней третий параметр отсутствует. При этом она полностью выполняет поставленную задачу и показывает фермеру интересующую его информацию. Теперь, когда фермер, когда фермер получит новый урожай и подставить новые значение в коронку «Урожайность», значение колонки D(Оценка) изменятся в соответствии с новыми данными.

К таблице можно добавить, что в ячейку С14 записана формула, определяющая среднюю урожайность, использовалась функция СРЗНАЧ.

Пример №2 – вспомогательная колонка для выборки

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

Использование функции ЕСЛИ для сложных таблиц

При решении данной задачи использовалась функция ЕСЛИ. Записываемая формула имеет такой вид

ЕСЛИ(проверка_ячейки_в_колонке_С_на_совпадение_с_С2;

записать_стоимость_из_колонки_F;

записать_0).

Сначала мы проверяем, совпадает ли запись фрукта или ягоды текущей строки с искомым значением, в примере это строка таблицы С12. Т.к. значение записанные в С2 и С12 не совпали, то мы в колонке выборка ставим 0. В противном случае мы должны были перенести соответствующее значение из колонки «Стоимость». В Итоговой строке «Сумма» мы видим сколько денег потрачено на закупку груш. При изменении значения в ячейке С2 на другое, таблица перещитается соответствующим образом.

Заключение

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

poznayit.ru

Функция ЕСЛИ (IF) - Справочник

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

Описание функции ЕСЛИ

Функция ЕСЛИ возвращает одно значение, если указанное условие дает в результате значение ИСТИНА, и другое значение, если условие дает в результате значение ЛОЖЬ. Например, формула =ЕСЛИ(A1>10,"Больше 10","10 или меньше")

возвращает строку «Больше 10», если значение в ячейке A1 больше 10, и «10 или меньше», если оно меньше или равно 10.

Синтаксис

=ЕСЛИ(лог_выражение; [значение_если_истина]; [значение_если_ложь])

Аргументы

лог_выражениезначение_если_истиназначение_если_ложь

Обязательный аргумент. Любое значение или выражение, дающее в результате значение ИСТИНА или ЛОЖЬ. Например, A10=100 — логическое выражение; если значение в ячейке A10 равно 100, это выражение принимает значение ИСТИНА, в противном случае — значение ЛОЖЬ. В этом аргументе может использоваться любой оператор сравнения.

Необязательный аргумент. Значение, которое возвращается, если аргумент лог_выражение соответствует значению ИСТИНА. Например, если данный аргумент — строка «В пределах бюджета», а аргумент лог_выражение соответствует значению ИСТИНА, функция ЕСЛИ возвращает текст «В пределах бюджета». Если аргумент лог_выражение соответствует значению ИСТИНА, а аргумент значение_если_истина опущен (т. е. после аргумента лог_выражение есть только запятая), возвращается значение 0. Чтобы отобразить слово ИСТИНА, используйте логическое значение ИСТИНА в качестве этого аргумента.

Необязательный аргумент. Значение, которое возвращается, если аргумент лог_выражение соответствует значению ЛОЖЬ. Например, если данный аргумент — строка «Превышение бюджета», а аргумент лог_выражение соответствует значению ЛОЖЬ, функция ЕСЛИ возвращает текст «Превышение бюджета». Если аргумент лог_выражение соответствует значению ЛОЖЬ, а аргумент значение_если_ложь опущен (т. е. после аргумента значение_если_истина нет запятой), функция ЕСЛИ возвращает логическое значение ЛОЖЬ. Если аргумент лог_выражение соответствует значению ЛОЖЬ, а значение аргумента значение_если_ложь пусто (т. е. после аргумента значение_если_истина стоит только запятая), функция ЕСЛИ возвращает значение 0 (ноль).

Замечания

Для создания более сложных проверок в качестве аргументов значение_если_истина и значение_если_ложь можно использовать до 64 вложенных функций ЕСЛИ (использование вложенных функций ЕСЛИ см. в примере 3). Кроме того, для проверки нескольких условий можно использовать функции ПРОСМОТР, ВПР, ГПР и ВЫБОР (использование функции ПРОСМОТР см. в примере 4).
Если какой-либо из аргументов функции ЕСЛИ является массивом, при выполнении функции ЕСЛИ вычисляются все элементы массива.
Приложение Excel предоставляет дополнительные функции, которые можно применять для анализа данных с использованием условий. Например, для подсчета числа вхождений текстовой строки или числа в диапазон ячеек используйте функции листа СЧЁТЕСЛИ и СЧЁТЕСЛИМН. Для вычисления суммы значений, попадающих в диапазон, заданный текстовой строкой или числами, используйте функции СУММЕСЛИ и СУММЕСЛИМН.

Пример

Видео работы функции

Сравнение двух файлов Excel

Дополнительные материалы

Как сравнить два файла MS Excel

Поделиться ссылкой:

Нажмите здесь, чтобы поделиться контентом на Facebook. (Открывается в новом окне)
Нажмите, чтобы поделиться в Google+ (Открывается в новом окне)
Нажмите, чтобы поделиться на Twitter (Открывается в новом окне)
Нажмите, чтобы поделиться на LinkedIn (Открывается в новом окне)
Нажмите, чтобы поделиться записями на Tumblr (Открывается в новом окне)
Нажмите, чтобы поделиться записями на Pinterest (Открывается в новом окне)
Нажмите, чтобы поделиться записями на Pocket (Открывается в новом окне)
Нажмите, чтобы поделиться в Telegram (Открывается в новом окне)
Нажмите, чтобы поделиться в Skype (Открывается в новом окне)
Нажмите, чтобы поделиться в WhatsApp (Открывается в новом окне)

msoffice-prowork.com

Логические функции в Excel. Часть №1

Доброго времени суток уважаемый читатель!

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

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

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

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

Функция ЕСЛИ;
Функция ИСТИНА;
Функция ЛОЖЬ;
Функция НЕ.

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

Функция ЕСЛИ

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

Функция ЕСЛИ возвращает один результат в случаях, если аргумент «логическое выражение» при вычислении получает ответ «ИСТИНА» и совсем другой, если ответ «ЛОЖЬ». Функция может содержать до семи логических условий внутри себя для аргумента «_значение если истина_», а для аргумент «_значение если ложь_» умеет выполнять функции или команды дальнейшего действия (к примеру, использование функции ПЕРЕЙТИ).

Синтаксис функции:

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

Логическое выражение – указывается практически любое выражение или значение, которое в процессе вычисления получает значение «ИСТИНА» или «ЛОЖЬ»;
Значение если истина – это значение, которое будет возвращено, если аргумент «Логическое выражение» получит результат «ИСТИНА». В случае, когда этот аргумент не указан в формуле, возвращается результат «ИСТИНА»;

Значение если ложь — это значение, которое будет возвращено, если аргумент «Логическое выражение» получит результат «ЛОЖЬ». В случае, когда этот аргумент опущен в формуле, возвращается результат «ЛОЖЬ».

Пример №1:

Рассмотрим усложнённый пример со многими условиями, вложенными в функцию ЕСЛИ. У нас есть персонал, которому в зависимости от стажа работы, производится доплата к ставке заработной платы. Всего будет три доплаты, стаж работы 3 года – 10%, 10 лет – 20% и 20 лет – 30%. Для получения результата и автоматического расчёта доплаты в зависимости от стажа нам нужна формула такого вида:

=ЕСЛИ(C2>=20;»30%»;ЕСЛИ(C2>=10;»20%»;ЕСЛИ(C2>=3;»10%»;»0%»))).

Пример №2:

Рассмотрим еще один сложный пример, в котором мы будем определять, и идентифицировать виды банковских карточек по их номерам. Для первого аргумента используем текстовую функцию ЛЕВСИМВ для захвата первых 4 цифр, которые и будут условием определения. Для карты «Visa Electron» установим идентификатор 1234, для карты «Maestro» — 5678 и все остальные карты будут по умолчанию «MasterCard Standard». Теперь собственно формула, которая поможет нам определить результат будет такова:

=ЕСЛИ( ЛЕВСИМВ (B14;4)= «1234»; «Visa Electron»; ЕСЛИ ( ЛЕВСИМВ (B14;4)=»5678»; «Maestro»; «MasterCard Standard»)).

Функция ИСТИНА

Абсолютно простая логическая функция ИСТИНА, не имеет никаких аргументов и умеет возвращать всего лишь логическое значение «ИСТИНА». Имеет смысл использовать только в связке с другими функциями.

Синтаксис функции:

=ИСТИНА().

Аргументы отсутствуют.

Функция ЛОЖЬ

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

Синтаксис функции:

=ЛОЖЬ().

Аргументов нет.

Функция НЕ

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

Синтаксис функции:

= НЕ(_логическое_значение_), где:

Логическое значение – это выражение, ссылка на ячейку или значение, которое как результат вычислений дает логическое значение «ИСТИНА» или «ЛОЖЬ». Функция НЕ при получении такого результата меняет его: «ЛОЖЬ» на «ИСТИНА» и «ИСТИНА» на «ЛОЖЬ».

Пример:

Обращаю внимание! Все логические функции в своих выражениях используют знаки сравнения «=», «<», «>», «<=», «>=» или «<>», в результате чего получаются значения «ИСТИНА» и «ЛОЖЬ».

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

Не забудьте поблагодарить автора!

Богатство приносит не большое состояние, а скромные потребности.Эпиктет (Epictetus)

Статья помогла? Поделись ссылкой с друзьями, твитни или лайкни!

topexcel.ru

Вложенные функции Excel (функция ЕСЛИ и функция ВПР)

Итак, мы уже рассмотрели как работает функция Если и функция ВПР. Иногда требуется за один шаг и выполнить какое-либо условие и найти нужное значение в таблице. Рассмотрим простой пример. Допустим у нас есть таблица с данными о цене товара в рублях и в долларах, мы бы хотели не пользуясь фильтрами или поиском по странице выводить стоимость товара по интересующему нас артикулу. Возьмем уже использованную нами таблицу данных:

таблица данных

Мы хотели бы что бы при вводе в ячейку С111 нужного артикула выводилась цена товара либо в рублях либо в у.е. Договоримся, что в соседней ячейке Е111 мы будем указывать вручную в каких единицах выводить стоимость (в следующих статьях мы рассмотрим как избавиться от ручных операций и создать например Чекбокс (флажек) или список выбираемых значений) . Итак поиск по артиклу мы выполняем с помощью функции ВПР: ВПР(C111;C115:E128;2;0) — поиск по цене в рублях, и ВПР(C111;C115:E128;3;0) — поиск по цене в у.е. Теперь осталось применить функцию Если для выбора между двумя функциями ВПР, напомним как работает функция Если: ЕСЛИ(лог_выражение; [значение_если_истина]; [значение_если_ложь]), а в нашем случае ЕСЛИ(условие; [ВПР 1 ]; [ВПР 2]), получили вложенные функции:

=ЕСЛИ(E111="рубли";ВПР(C111;C115:E128;2;0);ВПР(C111;C115:E128;3;0))

Готовая формула

Таким образом мы получили готовую форму, с двумя условиями: артикул, валюта.

Таблица готова

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

sirexcel.ru

Вложенные функции ЕСЛИ в Excel

Функция IF (ЕСЛИ) может быть вложенной в случае, когда нужно проверить несколько условий одновременно. Значение FALSE (ЛОЖЬ) может измениться на другое, если функция продолжит проверку. Для примера посмотрите на формулу ниже:

=IF(A1=1,"Bad",IF(A1=2,"Good",IF(A1=3,"Exellent","No Valid Score")))=ЕСЛИ(A1=1;"Bad";ЕСЛИ(A1=2;"Good";ЕСЛИ(A1=3;"Exellent";"No Valid Score")))

Если значение в ячейке A1 равно 1, функция возвращает Bad.
Если значение в ячейке A1 равно 2, функция возвращает Good.
Если значение в ячейке A1 равно 3, функция возвращает Excellent.
Если ячейка A1 принимает другое значение, функция возвращает No Valid Score.

Вот еще один пример:

=IF(A1<=10,350,IF(A1<=20,700,IF(A1<=30,1400,2000)))=ЕСЛИ(A1<=10;350;ЕСЛИ(A1<=20;700;ЕСЛИ(A1<=30;1400;2000)))

Если значение в ячейке A1 меньше или равно 10, функция возвращает 350.
Если значение в ячейке A1 больше, чем 10, но меньше или равно 20, функция возвращает 700.
Если значение в ячейке A1 больше, чем 20, но меньше или равно 30, функция возвращает 1400.
Если значение в ячейке A1 больше, чем 30, функция возвращает 2000.

Примечание: Возможно, в своей формуле вы захотите использовать «<» вместо «<=«, чтобы немного изменить границы.

Оцените качество статьи. Нам важно ваше мнение:

office-guru.ru