Функция ЕСЛИ в excel является одной из наиболее часто встречаемых и часто используемых. Работа экономиста в excel немыслима без знания этой функции.

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

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

В общем виде функция ЕСЛИ в excel имеет следующий синтаксис:

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

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

Рассмотрим синтаксис данной функции на примере алгоритма вычисления суммы с НДС или без НДС.

Лог_выражение – это может быть любое значение или выражение (формула) которое принимает значение ИСТИНА или ЛОЖЬ. Так, например, С5=“с НДС” – это логическое выражение. Если в ячейке С5 находиться текст «с НДС», то логическое выражение принимает значение ИСТИНА, в противном случае – ЛОЖЬ, например, если там содержится текст «без НДС».

Значение_если_истина - значение или выражение (формула), которое возвращается, если аргумент «лог_выражение» имеет значение ИСТИНА. Так, например, если этот аргумент равен формуле С12*1,18, то при значении логического выражения равному ИСТИНА, вычисления ведется по этой формуле.

Если аргумент «лог_выражение» имеет значение ИСТИНА, а аргумент «значение_если_истина» не задан, возвращается значение 0 (ноль).

Значение_если_ложь - значение или выражение (формула), которое возвращается, если аргумент «лог_выражение» имеет значение ЛОЖЬ. Так, например, если этот аргумент равен формуле С12*1 или просто С12, то при значении логического выражения равному ЛОЖЬ, вычисления ведется по этой формуле, т.е., как в нашем примере, берется просто значение суммы из ячейки С12

Если аргумент «лог_выражение» имеет значение ЛОЖЬ, а аргумент «значение_если_ложь» опущен (т. е. после аргумента «значение_если_истина» отсутствует точка с запятой), то возвращается логическое значение ЛОЖЬ. Если аргумент «лог_выражение» имеет значение ЛОЖЬ, а аргумент «значение_если_ложь» пуст (т. е. после аргумента «значение_если_истина» стоит точка с запятой, а за ней - закрывающая скобка), то возвращается значение 0 (ноль).

Рассмотрим наиболее часто встречающиеся в работе экономиста и при финансовом моделировании ситуации, в котором нам поможет использование функции ЕСЛИ.

Использование текстовых значений.

Сразу хочу обратить ваше внимание, что при использовании текстовых выражений в формулах, необходимо заключать их в кавычки “текст” для корректной работы формул.

Например, нам надо добавить какой либо текстовый признак или комментарий, в зависимости от значения той или иной ячейки.

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

ЕСЛИ(Р20>1000;“превышение лимита”;“в рамках лимита”)

Скопировав эту формулу по всему проверяемому диапазону, мы можем теперь легко отфильтровать значения превышающие лимит, по созданному нами критерию.

Скрытие значений. Скрытие нулевых значений с функцией ЕСЛИ .

Практически повсеместно встречающаяся ситуация – это возникновение ошибки #ДЕЛ/0! при подготовке исходных форматов. Мы готовим исходный формат, в котором в одном из столбцов вычисляем темп роста одного показателя к другому. Как известно, для этого используется формула А/В*100%, но поскольку у нас еще нет данных, то возникает ошибка при делении значений из столбца А на нулевые значения из столбца В.

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

ЕСЛИ(В10=0;0;А10/В10) или =ЕСЛИ(В10=0;””;А10/В10)

В первом случае будет вместо ошибки #ДЕЛ/0! будет возвращено значение 0, а во втором случае будет просто пустая ячейка. Двойные кавычки возвращают пустое значение в ячейку.

Другой пример, проверка сходимости баланса. В этом случае, нам надо сравнить сумму актива и сумму пассива баланса, и в случае если они равны, т.е. например, С85-С160=0, нам надо скрыть нулевое значение, а в случае если есть расхождение – отобразить это значение.

ЕСЛИ(С85-С160=0;””; С85-С160)

Многоуровневые, вложенные вычисления с функцией ЕСЛИ.

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

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

Типичный пример, расчет скидки в зависимости от нескольких уровней объема продаж. Или заработка, в зависимости от нескольких уровней выработки.

Предположим у нас следующая матрица скидок:

До 100 000 рублей – 0%

От 100 001 до 300 000 рублей – 3%

От 300 001 до 500 000 рублей – 5%

Свыше 500 001 – 7%

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

Создаем формулу используя функцию ЕСЛИ:

Предположим, в ячейке D10 у нас находиться объем продаж.

ЕСЛИ(D10>=500001;D10*0,93;ЕСЛИ(D10>=300001;D10*0,95;ЕСЛИ(D10>=100001;D10*0,97;D10)))

Эта формула в зависимости от уровня объема продаж устанавливает тот или иной уровень скидок. Количество уровней вложения и соответственно уровней скидок может быть гораздо больше.

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

В логических выражениях для сравнения значений и формул можно использовать следующие операторы сравнения:

> Больше

< Меньше

>= Больше или равно

<= Меньше или равно

<> Не равно

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

В примере №3 мы как раз использовали оператор >= больше или равно для задания диапазона ссылок.

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

Функции И, ИЛИ, НЕ

Функция И в excel имеет следующий синтаксис:

И(логическое_значение1, [логическое_значение2], …)

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

Функция ИЛИ в excel имеет аналогичный синтаксис:

ИЛИ(логическое_значение1, [логическое_значение2],…)

Однако возвращает значение ИСТИНА, если хотя бы один из аргументов имеет значение ИСТИНА. Если все аргументы имеют значение ЛОЖЬ, то возвращается значение ЛОЖЬ.

Функция НЕ в excel имеет следующий синтаксис:

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

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

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

Предположим, нам надо сделать отбор наших дебиторов, которые задолжали нам за 3 месяца сумму большую 100 000 рублей. Допустим, в этом случае формула должна выводить критерий «злостный задолжник».

В ячейке В10 у нас будет срок задолженности в месяцах, а в ячейке С10 сумма задолженности.

Тогда формула будет иметь следующий вид:

ЕСЛИ(И(В10>=3;С10>100000);»злостный задолжник»;»»)

Если мы считаем критичным либо задолженность старше 3 месяцев, либо свыше 100 000 рублей, то формулу можно записать так:

ЕСЛИ(ИЛИ(В10>=3;С10>100000);»критичная задолженность»;»»)

В общем, как вы сами понимаете, возможности применения функции ЕСЛИ в excel безграничны.

Самое главное – это продумать алгоритм вычислений, а уж инструментария в MS Excel для его реализации вполне достаточно.

Microsoft Excel содержит дополнительные функции, которые можно применять для анализа данных с использованием условий. Например, для подсчета числа вхождений текстовой строки или числа в диапазоне ячеек можно использовать функции СЧЁТЕСЛИ и СЧЁТЕСЛИМН. Для вычисления суммы значений, попадающих в интервал, заданный текстовой строкой или числами, можно использовать функции СУММАЕСЛИ и СУММЕСЛИМН.

В статье речь пойдет о функции табличного редактора Excel «Если». Она связана с логическими возможностями приложения. Данную функцию можно отнести к наиболее распространенной, которая зачастую используется во время работы.

Ключевые возможности

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

Примеры применения

Функция имеет вид: =ЕСЛИ (задача; истина; ложь). Первой составной частью является логическое выражение. Оно способно выступать в виде фразы или числа. Например, «10» или «без НДС». Такой параметр обязательно следует заполнить. Истина представляет собой значение, отображающееся в качестве результата, если выражение верно. Ложь представляет собой данные, которые будут выданы, когда задача неверна.

Равенство параметров двух ячеек

Чтобы лучше понять возможности функции «Если» в Excel, необходимо привести примеры. Поэтому дальше стоит приступить к их рассмотрению. Нужно ввести в клетку C1 значение 8. После этого в поле по адресу D1 необходимо вписать формулу вида: =ЕСЛИ(C1<10; 1; 2). В результате программа самостоятельно начинает сравнение параметров из клетки C1 со значением 10. Когда оно достигнет десяти, в поле по адресу D1 можно увидеть единица. В противном случае редактор отобразит 2.

Можно рассмотреть еще один пример. Существует несколько студентов и их отметки, которые заработаны за экзамен. Данные следующие: 5, 4, а также 3 и 2. По условию задания предусматривается создание для каждого из учащегося текстового комментария «сдал» или «не сдал». Таким образом, если студент получил отметку три и выше, считается, что он сдал экзамен. Если его оценка ниже 3, студент не справился с заланием. Для решения подобной задачи следует написать следующую формулу: =ЕСЛИ(C1<3; «не справился»; «сдал»).

Программа начинает сравнивать результаты каждого студента. Если показатель меньше трех, в соответствующей клетке появится надпись «не справился». Когда отметка 3 и выше, в требуемой графе можно увидеть комментарий о том, что студент не сдал экзамен. Стоит отметить, что текстовые комментарии следует всегда писать в кавычках. Во время использования данной функции можно применять следующие операторы сравнения: < >, =, >, <, >=, <=.

Примеры с использованием условий «ИЛИ», «И»

Необходимо продолжить рассмотрение логических возможностей данного приложения. Существует возможность соединить функцию «Если» с операторами сравнения. Это следующие параметры:

«ИЛИ»;
«И».

Необходимо указать важное условие в Excel: когда оценка студента равна или меньше 5, но больше 3. При этом следует отобразить комментарий: «проходит» или «нет». Таким образом, проходят только те студенты, которые заработали пятерки и четверки. Чтобы записать данную задачу в табличном редакторе следует применить специальную формулу. Она имеет следующий вид: =ЕСЛИ(И(A1<=5; A1>3); «проходит», «нет»).

Если рассмотреть более сложный пример, понадобится использовать «ИЛИ» либо «И». Таким образом, можно ознакомиться с применением формулы в Excel, если существует несколько условий в задании. Например: =ЕСЛИ(ИЛИ(A1=5; A1=10); 100; 0). В данном случае можно сделать вывод, когда значение в клетке А1 равно 5 или 10, программа отображает результат 100. В противном случае он составляет 0. Существует возможность применить эти операторы, чтобы найти решение более сложным задачам.

Например, в базе требуется вычислить должников, которым требуется заплатить более 10000 рублей. Можно задать условие, что заем не погашался более полугода, то есть шести месяцев. Благодаря функции табличного редактора Excel «Если» позволяется в автоматическом режиме получить около соответствующих имен отметку «проблемный клиент». Допустим, в клетке A1 размещены данные, которые указывают на срок задолженности (месяцы). Поле B1 содержит сумму.

В данном случае формула представлена в следующем виде: =ЕСЛИ(И(A1>=6; B1>10000); «проблемный клиент»; «»). Таким образом, если выявлен человек, соответствующий указанным условиям, программа отображает напротив его имени соответствующий комментарий. Для других участников списка эта клетка останется пустой.

Также можно ознакомиться с примером, когда ситуация является критической. Необходимо ввести соответствующий комментарий. Как результат, формула получится следующей: =ЕСЛИ(ИЛИ(A1>=6; B1>10000); «критическая ситуация»; «»). Однако если программа определяет совпадения по одному из параметров как минимум, отображается соответствующее примечание.

Сложные задачи

Функция Excel «Если» применяется для того, чтобы избежать встроенных ошибок при делении на ноль. Кроме того, она используется и в некоторых других ситуациях. Первый случай обозначается, как «ДЕЛ/0». Его можно встретить достаточно часто. Обычно это происходит тогда, когда формула «A/B» требует копирования. В это же время показатель B в отдельных ячейках составляет 0. Чтобы избежать подобной ситуации, стоит использовать возможности рассматриваемого оператора. Таким образом, требуемая формула имеет следующий вид: =ЕСЛИ(B1=0; 0; A1/B1). Итак, если клетка B1 заполнена значением «ноль», редактор отобразит «0». В противном случае программа поделит показатель A1 на данные B1 и выдаст требуемый результат.

Скидка

Как показывает практика, зачастую возникают ситуации, которые рассматриваются далее. Потребуется рассчитать скидки, учитывая общую сумму средств, затраченных на покупку конкретного товара. Матрица, которая используется, выглядит следующим образом: менее 1000 - 0%; 1001-3000 - 3%; 3001-5000 - 5%; более 5001 - 7%. Можно ознакомиться с ситуацией, когда в Excel имеется база данных посетителей и сведения о сумме, потраченной на покупку. Далее следует рассчитать скидку для каждого клиента. Для этого понадобится применить следующее выражение: =ЕСЛИ(A1>=5001; B1*0,93; ЕСЛИ(А1>=3001; B1*0,95;..).

Система выполняет проверку общей суммы покупок. Если она превышает значение в 5001 рублей, выполняется умножение на 93 процента цены товара. Если превышена отметка в 3001 единиц, происходит аналогичное действие, однако уже учитывается 95%.

Основу любого логического выражения составляют две величины: истина (TRUE) и ложь (FALSE). С помощью логических выражений строятся многочисленные деревья решений. Самое простое – вопрос с ответом «ДА» или «НЕТ». В случае «ДА» выполняется одно действие, в случае «НЕТ» – другое. Для реализации условий существует программный оператор IF (ЕСЛИ).

В программе EXCEL есть функция IF , которая позволяет сделать самые невероятные логические конструкции, вкладывая операторы IF друг в друга. В старых версиях программы вложенность была ограничена 7 уровнями, ЕСЛИ в EXCEL 2010 не имеет ограничений, а в версии 2016 появилась новая функция ЕСЛИМН, учитывающая множественность вложений.

Познакомимся с многоликой функцией IF.

Функция IF

ЕСЛИ – стандартная функция программы EXCEL, которая выполняет проверку конкретного условия . Условие представляет собой вопрос, имеющий два ответа: истина и ложь.

Условие записывается в виде логического выражения, состоящего из правой и левой части, связанного логическими операторами сравнения: больше (>), меньше (<), равно (=), неравно (<>), а также >= и <=. Результат сравнения показывает, выполняется или нет это условие. В случае выполнения условия отрабатывает алгоритм ИСТИНА, в противном случае – ЛОЖЬ.

Синтаксис функции выглядит следующим образом:

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

Функции ЕСЛИ в EXCEL, примеры

Простым примером реализации функции IF может служить необходимость присвоения качественного признака ряду значений . Например, необходимо выделить в списочном кадровом составе предприятия всех работников моложе 35 лет. Для этого достаточно в новый столбец напротив каждой фамилии подставить формулу вида: =ЕСЛИ(ячейка_с_возрастом<35; 1;0). Таким образом, всем молодым людям будет присвоен качественный признак =1, который даст возможность работать только с нужной частью списка.

Интересен вариант работы с формулой IF, когда используется не цифровые данные, а текст.

Текст в формуле нужно выделять кавычками для корректной отработки функции .

Всем инженерам 2 категории премия составляет 30%, остальным работникам – 50%. Для реализации этого условия вставляем формулу: =ЕСЛИ(ячейка_профессия=“инженер 2 категории”; “премия 30%”; “премия 50%”). Все инженеры 2 категории промаркируются данными по премии в 30%, остальным работникам будет введено значение «премия 50%».

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

Применение вложенных IF

На предприятии при ведении табеля учёта рабочего времени возникает необходимость поставить месячную норму времени для расчёта зарплаты . У разных специалистов эта величина различается. С помощью функции IF можно легко решить эту задачу.

Пусть у дневного персонала месячная норма равна 162 часа, у сменного при 8-часовом рабочем дне она составит 148 часов, а при рабочем дне 7,2 часа норма будет равна 136 часов.

Тогда нужно проверить дневную норму работника (8 или 7,2 часа) и категорию работающего (смена или день). Реализовать такую проверку можно разными способами.

Вариант 1

Когда в качестве первой проверки берём дневную норму и вторую проверку делаем по истине, то формула будет следующая:

ЕСЛИ(ячейка_дневнаянорма=8; ЕСЛИ(ячейка_категория=“смена”;148;162);136)

Вариант 2

Первую проверку оставляем прежней, вторую проверяем по лжи, то выражение будет выглядеть так:

ЕСЛИ(ячейка_дневнаянорма=7.2; 136;ЕСЛИ(ячейка_категория=“смена”;148;162))

Вариант 3

Когда первую проверку делаем по категории, то имеем: =ЕСЛИ(ячейка_категория=“смена”;ЕСЛИ(ячейка_дневнаянорма =8;148;136);162)

Результат во всех случаях будет одинаковый.

Допустим, что есть ещё смена по 12 часов с нормой в 144 часа. Тогда для реализации такого алгоритма потребуется ещё одно вложение функции IF. Это уже третий уровень.

Следующее выражение даёт решение и в этом случае:

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

Одновременное применение двух условий

Многоликость функции IF выражается в том, что её можно применять одновременно с другими функциями. В EXCEL есть ряд полезных встроенных конструкций, использующих метод IF. Это такие формулы, как СУММЕСЛИ, СЧЕТЕСЛИ, СРЗНАЧЕСЛИ. С их помощью легко выполнять расчёты по конкретному условию. Но и саму конструкцию IF можно применить одновременно с другими функциями. Рассмотрим её одновременное использование с оператором И и ИЛИ.

Функция IF с формулой И

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

И(лог_выраж1; лог_выраж2;лог_выраж3;…)

Она проверяет истинность каждой из логических структур и возвращает ИСТИНУ только при истинности всех проверяемых выражений.

Где это можно использовать? Например, необходимо выделить из общего списка работников только слесарей 6 разряда, имеющих стаж работы больше 10 лет, то поможет конструкция вида:

ЕСЛИ(И(ячейка_профессия=”слесарь”;ячейка_разряд=6;ячейка_стаж>=10); 1;0)

Эта формула выделит в общем списке нужную категорию работников по установленному качественному признаку =1.

Функция IF с формулой ИЛИ

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

Конструкция ИЛИ имеет синтаксис аналогичный формуле И. Но ИСТИНА возвращается при условии истинности хотя бы одного логического выражения.

В нашем примере необходима формула:

ЕСЛИ(ИЛИ(ячейка_премия1квартал>=5000; ячейка_премия2квартал>=5000; ячейка_премия3квартал>=5000; ячейка_премия4квартал>=5000);1;0)

Последующая фильтрация данных по признаку 1 даст сделать нужный список трудящихся.

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

Логические выражения

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

Ниже представлен список операторов сравнения Excel:

Результатом логического выражения является логическое значение ИСТИНА (1) или логическое значение ЛОЖЬ (0).

Функция ЕСЛИ

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

Синтаксис:

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

Переместить курсор в ячейку D 2 . С помощью мастера функций выбрать из категории Логические функцию ЕСЛИ (рис. 27), а затем щелкнуть на кнопке ОК.

Диалоговое окно Аргументы функции (рис. 28) содержит три поля ввода. В поле Лог _ выражение необходимо ввести условие, которое определяет, превышает ли проданное кол-во товара 5 шт., следовательно введем в это поле С2>5 . В поле Значение_если_истина необходимо ввести формулу, которая вычисляет стоимость товара с учетом скидки, тогда введем в это поле B2*C2-B2*C2*0,1 . В поле Значение_если_ложь необходимо ввести формулу, которая вычисляет стоимость товара без учета скидки (условие С2>5 - Ложь), в тогда введем в это поле B2*C2 (см. рис. 29). Теперь щелкнем на кнопке OK.

Скопировать полученную формулу в смежные ячейки. Результаты вычислений формул приведены на рис. 30.

Функции ЕСЛИ могут быть вложены друг в друга в качестве значений аргументов значение_если_истина и значение_если_ложь. С помощью таких вложенных функций Если можно конструировать более сложные проверки. Рассмотрим на примерах, как необходимо использовать вложенной функцией ЕСЛИ .

На рис. 31 приведена таблица с данными. Необходимо:

Определить, на какую сумму продано товаров каждого вида.

Определить, на какую сумму продано товаров каждого вида с учетом скидки (сумма с учетом скидки = сумма - сумма * скидка). Скидка начисляется по следующему принципу: если продано товара на сумму более 2500 грн., то скидка составит 5%, если продано товара на сумму менее 1100 грн., то скидка составит 0%, в остальных случаях скидка составит 2%.

Для того чтобы выполнить первый пункт задания необходимо в ячейку D2 ввести формулу =C2*B2 .

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

Переместим курсор в ячейку E2 и с помощью мастера функций введем следующую формулу (рис. 33 – 34). Результаты решения приведены на рис. 35.

Щелкнуть тут

Рис. 35

Функции ИЛИ, И

Функции И и функция ИЛИ из категории Логические способны заметно улучшить наглядность и понятность сложных логических проверок. Функция ИЛИ и функция И позволяют задавать в формуле одновременно несколько условий, т.е. дают возможность создавать сложные логические выражения. Эти функции работают в сочетании с простыми операторами сравнения. Функции И и ИЛИ могут иметь до 30 логических аргументов и имеют синтаксис:

ИЛИ(логическое_значение1;логическое_значение2; ...)

И(логическое_значение1; логическое_значение2; ...)

Аргументы функций И, ИЛИ могут быть логическими выражениями, массивами или ссылками на ячейки, содержащие логические значения.

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

Предположим, что нужно вывести сообщение «Светофор», если содержимое ячейки B4 или «красный», или «зеленый», или «желтый». Если же она содержит любую другую информацию, то необходимо вывести сообщение «Это не светофор!!!».

ЕСЛИ(ИЛИ(B4= "зеленый" ;B4= "красный" ;B4= "желтый" );"Светофор"; "Это не светофор!!!")

Предположим, что нужно вывести на экран содержимое ячейки B4 , если она содержит число строго между 1 и 100 (1<=B4<=100), и сообщение "Значение вне интервала" в противном случае.

ЕСЛИ(И(B4>=1; B4<=100); B4; «Значение вне интервала»)

Есть список дат, необходимо определить, является ли выходным днем введенная дата. Решения задачи на рис. 36

Формулу в ячейке В2 можно вводить с клавиатуры (необходимо помнить синтаксис используемых вложенных функций) или с помощью мастера функции. Рассмотрим, как вводить формулу с помощью мастера функций:

Переместим курсор в ячейку В2, щелкнем на кнопке вставка функции и в категории логические выберем функцию Если .

Щелкнем в поле Лог _ выражение функции Если, а затем щелкнем на стрелке вниз в строке формул и выберем Другие функции (см. рис. 37). Теперь в категории Логические можно выбрать функцию ИЛИ .

Щелкнем в поле Логическое_выражение_1 функции ИЛИ , а затем щелкнем на стрелке вниз в строке формул и выберем Другие функции. В категории Дата и время выберем функцию ДЕНЬНЕД и в поле Дата_в_числом_формате введем А2 , а в поле Тип введем 2 .

ИЛИ ИЛИ , для того чтобы закончить ввод условия в первое поле этой функции (набираем = 6).

Щелкнем в поле Логическое_выражение_2 функции ИЛИ и повторим действия пункта 3 и 4, чтобы ввести второе условие ДЕНЬНЕД(А2;2)=7.

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

Информация о результатах первого модульного контроля и посещаемости студентами занятий выведена в таблице см. рис. 38. Необходимо вывести следующую информацию: Если средний бал студента меньше или равен 3,5 и он пропустил более 49 часов занятий по неуважительной причине, то необходимо вызвать родителей в деканат; если средний бал студента больше или 4,5 и он пропустил не более 10 часов занятий по неуважительной причине, то необходимо отправить родителям письмо благодарность. Решение задачи представлено на рис. 39.

Функции СЧЁТЕСЛИ и СУММЕСЛИ

Эти функции надо искать не в категории Логические , а в категориях Статистические и Математические , соответственно (или в полном алфавитном перечне).

СЧЁТЕСЛИ - подсчитывает количество ячеек в диапазоне, удовлетворяющих заданному условию, а СУММЕСЛИ - суммирует значения ячеек, удовлетворяющих заданному условию. Функция СУММЕСЛИ используется в тех случаях, когда необходимо суммировать не весь диапазон, а только ячейки, отвечающие некоторым условиям (критериям).

Синтаксис:

СЧЁТЕСЛИ (диапазон; критерий)

диапазон - диапазон, в котором нужно подсчитать ячейки.

критерий - критерий (условие) в форме числа, выражения или текста, который определяет, какие ячейки надо подсчитывать.

Функция СЧЁТЕСЛИ работает следующим образом: вычисляется количество ячеек диапазона, значение которых удовлетворяют условию (критерию) .

В задаче, рассмотренной в примере 10 необходимо определить количество студентов, у которых средний балл >=4.5.

Тогда в ячейку С15 необходимо ввести формулу: =СЧЁТЕСЛИ(B11:B13;">=4,5"). Результат приведен на рис. 40

Синтаксис:

СУММЕСЛИ (диапазон; критерий; диапазон_суммирования)

диапазон - диапазон ячеек проверяемых на критерий (условие).

критерий - критерий (условие) в форме числа, выражения или текста, определяющего суммируемые ячейки.

диапазон_суммирования - фактические ячейки для суммирования.

Функция СУММЕСЛИ работает следующим образом: ячейки из «диапазон_суммирования » суммируются, только если соответствующие им ячейки в аргументе «диапазон » удовлетворяют условию (критерию) . В тех случаях, когда диапазон вычисляемых ячеек (где проверяется условие) и диапазон фактических ячеек для суммирования совпадают, аргумент «диапазон_суммирования» можно не указывать.

В таблице на рис. 41 приведены объемы партий товара, полученные магазином. Необходимо просуммировать только объемы тех партий, значения которых превышают 20. Решение на рис. 42

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

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

Принцип действия довольно простой. Вы указываете какое-нибудь условие и что нужно делать в случаях истины и лжи.

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

Полное описание можно увидеть в окне «Вставка функции».

Нажмите на иконку
Выберите категорию «Полный алфавитный перечень».
Найдите там пункт «ЕСЛИ».
Сразу после этого вы увидите описание функции.

В качестве примера добавим столбец с премией для учителей высшей категории.

Затем необходимо выполнить следующие действия.

Перейдите на первую ячейку. Нажмите на иконку «Fx». Найдите там функцию «ЕСЛИ» (её можно отыскать в категории «Полный алфавитный указатель»). Затем кликните на кнопку «OK».

В результате этого появится следующее окно.

В поле логическое выражение введите следующую формулу.

В качестве адреса указываем ячейку, в которой содержится категория преподавателя.

После подстановки вы увидите, что данное выражение ложно.

Затем указываем значения дли «Истины» и «Лжи». В первом случае какое-то число, а во втором – ноль.

После этого мы увидим, что логический смысл выражения будет ложным.

Для сохранения нажимаем на кнопку «OK».

В результате использования этой функции вы увидите следующее.

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

Наведите курсор в правый нижний угол первой ячейки.
Если вы сделали всё правильно, то он превратится в черный крестик.

Теперь сделайте левый клик мыши и не отпуская палец опуститесь до конца таблицы.

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

В данном случае информации не так много. А теперь представьте, что такая таблица будет огромной. Ведь в организации всегда работает большое количество людей. Если работать в редакторе Word и делать такое сравнение квалификации сотрудников вручную, то кто-нибудь (вследствие ошибок, связанных с человеческим фактором) будет выпадать из списка. Формула в Экселе никогда не ошибется.

Использование условия «И»

Как правило, очень часто приходится использовать различные дополнительные проверки. Например, можно посчитать премию не просто для учителей высшей категории, а конкретно для математиков.

Для этого достаточно выполнить следующие действия.

Кликните на первую ячейку в столбце «Премия».
Затем нажмите на иконку «Fx».

Сразу после этого появится окно с используемой функцией со всеми указанными аргументами. Таким образом редактировать намного проще – непосредственно в ячейке.

В графе логическое выражение укажите следующую формулу. Для сохранения изменений нажмите на кнопку «OK».

И(D3="Высшая";E3="Математика")

Скопируйте эту формулу во все остальные ячейки.

Теперь премия будет только у одного человека, поскольку только у него высшая категория и он математик.

Использование условия «ИЛИ»

Перейдите в первую ячейку.
Кликните на иконку «Fx».

Текущее логическое выражение нас не устраивает.

Нужно будет поменять его на следующее.

ИЛИ(D3="Первая";D3="Вторая")

Сразу после этого мы увидим, что у первого преподавателя появилась премия, поскольку у него первая категория.

Дублируем эту формулу в остальные ячейки.

В результате этого мы увидим следующее.

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

В качестве демонстрации данного инструмента попробуем посчитать общую зарплату для учителей первой категории. Для этого создадим еще одну строку.

Затем выполним следующие шаги.

Переходим на соседнюю клетку и вызываем функцию (повторяем описанные выше действия) СУММЕСЛИ. Активной должна быть именно та ячейка, в которой будет выводиться сумма.

Сразу после этого вы увидите окно, в котором нужно указать аргументы функции:
- диапазон;
- критерий;
- диапазон суммирования.

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

Как только вы отпустите палец, всё вернется в прежний вид, а нужный диапазон подставится автоматически.

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

Затем нам необходимо выделить графу зарплата. Но перед этим кликаем на последнее поле – нужно, чтобы оно стало активным.

Только после этого можно указывать нужный диапазон.

Как только вы отпустите палец, увидите следующее.

Для сохранения формулы достаточно кликнуть на кнопку «OK».

В результате мы увидим, что общая зарплата учителей с первой категорией составила 1200.

Для этого необходимо сделать следующее.

Добавим строчку, где будем производить расчёты.

Переходим в нужную клетку и кликаем на иконку «Fx». Находим нужную функцию и нажимаем на кнопку «OK».

В появившемся окне следует указать нужный диапазон суммирования. В данном случае это графа зарплата.

Для этого достаточно выделить нужные ячейки и в результате этого они подставятся автоматически.

Теперь указываем «Диапазон условия 1». Например, здесь можно указать категорию сотрудников. Перед выделением обязательно кликните на это поле, иначе выделенные ячейки попадут в первую строку. Сразу после этого вы увидите новое поле для ввода.

Введите туда слово «Первая». Сразу после этого вы увидите поля для второго условия.

Делаем клик на поле «Диапазон условия 2». Затем выделяем столбик с предметом.

В строку «Условие 2» напишите нужный вам предмет. В данном случае – «Математика». Для того чтобы увидеть корректность вашей формулы, достаточно будет кликнуть в любое уже ранее указанное поле. В результате этого второе условие окажется в кавычках (согласно правилам) и вы увидите результат данной функции.

Для сохранения формулы необходимо нажать на кнопку «OK».

В итоге вы увидите следующее.

В редакторе Эксель для расчётов количества ячеек можно использовать формулу «СЧЕТ». Но как быть, если нужно посчитать не все ячейки? Более того, позиция нужных клеток заранее неизвестна, поскольку таблица может содержать большое количество данных.

В таких случаях нужно использовать СЧЕТЕСЛИ. В качестве примера посчитаем, сколько именно сотрудников имеют высшую категорию. Для этого нужно сделать следующее.

Добавим новую строку. Сделаем активной нужную ячейку и кликнем на иконку вставки функции.

В появившемся окне находим нужную формулу и нажимаем на кнопку «OK».

Затем вас попросят указать диапазон и критерий отбора.

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

Затем в поле критерий указываем слово «Высшая». Для сохранения нажмите на кнопку «OK».

В результате этого вы увидите следующее.

Добавляем новую строку для расчётов. Кликаем на нужную ячейку и вызываем окно «Вставка функции». Находим нужную и кликаем на кнопку «OK».

В графу «Диапазон условия» указываем поле «Категория». Для этого достаточно выделить нужные ячейки.

После клика в поле «Условие 1» у вас появится строка для второго диапазона.

Введите нужную категорию учителя. В данном случае – «Высшая».

После этого сделайте клик в поле «Диапазон условия 2» и выделите столбец с названием предмета.

Затем в последнее поле указываем слово «Математика». Для сохранения нажимаем на кнопку «OK».

Результат будет следующим.

Расчёт произошел корректно. В нашей таблице всего 1 преподаватель математики с высшей категорией.

Функция ПОИСКПОЗ

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

Для этого нужно сделать следующее.

Перейдите в какую-нибудь ячейку. Вызовите окно вставки функции. После того как найдете нужную, нажмите на кнопку «OK».

В появившемся окне нужно будет указать:
- искомое значение;
- просматриваемый массив;
- тип сопоставления.

В первое поле достаточно написать слово «География», поскольку именно этого преподавателя мы ищем.

Затем нужно выделить столбец с названием предметов (предварительно кликнув на второе поле).

В третьем параметре укажите цифру «0». Для сохранения нажмите на кнопку «OK».

В результате этого мы увидели, что ячейка со словом «География» находится под номером 8 (среди указанного диапазона).

Более подробно про третий параметр можно прочитать на официальном сайте центра поддержки компании Microsoft. Там написано следующее.

Использование условий в VBA

При помощи макросов можно сделать дополнительную проверку различных значений. Например, вы можете узнать, были ли найдены люди по вашему запросу. Разумеется, в данном случае с маленькой таблицей и так всё видно. Но как быть, если данных очень много?

Для этого необходимо выполнить следующие шаги.

По умолчанию вкладка с макросами скрыта от пользователей. Её нужно открыть. Нажмите на пункт меню «Файл».

Перейдите в раздел «Параметры».