Материал: ЭКСЕЛЬ2 2013 Методичка Расширенные возможности

26

Microsoft Excel 2013. Уровень 2. Расширенные возможности

ПРИМЕР: Рассчитать премию сотрудникам, исходя из условия. Если стаж сотрудника более 8 лет, то премия составляет 30% от оклада, в противном случае – 5000 р.

=ЕСЛИ(C2>8;D2*30%;5000) – в зависимости от стажа работы (ячейка C2) вычисляется премия: если стаж работы более 8 лет, то премия равна 30% от оклада (ячейка D2), в противном случае – 5000 р.

ПРАКТИКУМ:

Открыть файл 01_5 Логические функции.

На листе ЕСЛИ1 выполнить задание.

Сохранить сделанные изменения.

Функции, объединяющие несколько условий в одно

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

И(Логическое_значение1;Логическое_значение2;) – проверяет, все ли аргументы имеют значение ИСТИНА, и возвращает значение ИСТИНА, если истинны все аргументы.

AND(Logical1;Logical2;)

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

ИЛИ(Логическое_значение1;Логическое_значение2;) – проверяет, имеет ли хотя бы один из аргументов значение ИСТИНА. Значение ЛОЖЬ возвращается только в том случае, если все аргументы имеют значение ЛОЖЬ.

OR(Logical1;Logical2;)

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

27

Microsoft Excel 2013. Уровень 2. Расширенные возможности

ПРИМЕР: Выдать Бонус в размере 22 тыс. р. только тем сотрудникам, стаж работы которых не менее 7 лет и коэффициент надежности при этом более 0,7.

=ЕСЛИ(И(C2>=7;E2>0,7);22000;0) – в зависимости от стажа работы (ячейка C2) и коэффициента надежности (ячейка E2) вычисляется Бонус: если стаж работы не менее 7 лет и при этом коэффициент надежности более 0,7, то размер Бонуса 22000 р., иначе бонуса нет – 0 р.

ПРИМЕР: Определить каким сотрудникам полагается подарок. Подарок выдается только сотрудникам, работающих из отделов ТКБ и ОТД.

=ЕСЛИ(ИЛИ(G2="ТКБ";G2="ОТД");"ПОДАРОК";"") – в зависимости от кода отдела (ячейка G2) определяется наличие/отсутствие Подарка.

28

Microsoft Excel 2013. Уровень 2. Расширенные возможности

Вложенность функции ЕСЛИ

ПРИМЕР: Вычислить годовую премию сотрудникам как коэффициент премии от оклада в зависимости от стажа работы: 2 при стаже менее 5 лет, 3 при стаже от 5 до 10 лет включительно и 5 при стаже свыше 10 лет.

Логическая схема решения задачи с вложенной функцией ЕСЛИ:

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

=ЕСЛИ(C3<5;2;ЕСЛИ(C3<=10;3;5)), где C3 – ячейка со значение стажа работы.

Затем всё умножается на значение оклада D3 и конечная формула определения премии:

=ЕСЛИ(C3<5;2;ЕСЛИ(C3<=10;3;5))*D3.

Альтернативные варианты:

=ЕСЛИ(C3<5;2;ЕСЛИ(И(C3>=5;C3<=10);3;5))*D3

=ЕСЛИ(C3<=5;D3*2;ЕСЛИ(C3<=10;D3*3;D3*5))

29

Microsoft Excel 2013. Уровень 2. Расширенные возможности

ПРАКТИКУМ:

Открыть файл 01_5 Логические функции.

На листе ЕСЛИ2 выполнить задание.

Сохранить сделанные изменения.

САМОСТОЯТЕЛЬНЫЕ УПРАЖНЕНИЯ

Открыть файл УПРАЖНЕНИЯ ЭКСЕЛЬ2. Выполнить задание на листе 5-1, 5-2, 5-3 и 5-4.

ЕСЛИОШИБКА(Значение;Значение_если_ошибка) – возвращает указанное значение, если вычисление по формуле вызывает ошибку, в противном случае возвращает результат формулы.

IFERROR(Value;Value_if_error)

Значение [Value] – обязательный аргумент (формула), проверяемый на возникновение ошибок.

Значение_если_ошибка [Value_if_error] – обязательный аргумент. Значение, возвращаемое при ошибке при вычислении по формуле – может быть числом, текстом или формулой для вычисления. Возможны следующие типы ошибок: #Н/Д, #ЗНАЧ!,

#ССЫЛКА!, #ДЕЛ/0!, #ЧИСЛО!, #ИМЯ? и #ПУСТО!.

ПРИМЕР: Вычислить среднюю сумму заказа. В случае если сотрудник не оформил не один заказ, то вывести текст в ячейке «Заказов нет»

=ЕСЛИОШИБКА(C2/D2;"Заказов нет") – при возникновении ошибки (деление на ноль), в ячейке выводится текст "Заказов нет".

ПРАКТИКУМ:

Открыть файл 01_5 Логические функции.

На листе ЕСЛИ3 выполнить задание.

На листе ЕСЛИ4 выполнить задание самостоятельно.

Сохранить сделанные изменения.

30

Microsoft Excel 2013. Уровень 2. Расширенные возможности

Текстовые функции

Помимо анализа числовых данных, так же встречаются и задачи по обработке текстовых данных.

Объединить данные из нескольких ячеек в одну, можно двумя способами: используя функцию СЦЕПИТЬ или оператор сцепки &.

СЦЕПИТЬ(Текст1;Текст2;) – объединение несколько текстовых строк в одну.

CONCATENATE(Text1;Text2;)

=СЦЕПИТЬ(A2;" ";B2;" ";C2) – объединение фамилии (ячейка А2) с именем (ячейка В2) и отчеством (ячейка С2) с разделителями – пробелами " ".

Альтернативный способ объединения текстовых ячеек с разделителями пробелами " " по формуле: =A2&" "&B2&" "&C2.

СЖПРОБЕЛЫ(Текст) – удаляет из текста лишние пробелы, кроме одиночных между словами.

TRIM(Text)

ПРИМЕР: Удалить лишние пробелы из исходной строки.