1. ExcelTable.com работа с таблицами: работа с функциями в Excel на примерах [Электронный ресурс]. – Режим доступа https://exceltable.com/funkcii-excel/rabota-s-funkciyami-excel (дата обращения 28.01.2019).
2. Служба поддержки Office: функции Excel (по категориям) [Электронный ресурс]. – Режим доступа https://support.office.com/ (дата обращения 28.01.2019).
3. Математические функции Excel [Электронный ресурс]. – Режим доступа http://office-menu.ru/uroki-excel/13-uverennoe-ispolzovanie-excel/39-matematicheskie-funktsii-excel (дата обращения 28.01.2019).
Практическое занятие №11 Использование логических функций Excel .
1. Цель работы:
Научиться работать с логической функцией ЕСЛИ.
2. Пояснения к работе:
Обучающийся выполняет работу по заданию.
Перед выполнением задания изучить лекционный материал и теоретические сведения.
При выполнении практической работы обучающийся должен:
знать состав логических функций;
уметь правильно записывать логические функции.
Теоретические сведения
Функция ИСТИНА не принимает никаких аргументов и просто возвращает логическое значение «ИСТИНА».
Синтаксис: =ИСТИНА()
Функция ЛОЖЬ аналогична функции ИСТИНА, за исключением то, что возвращает противоположный результат ЛОЖЬ.
Синтаксис: =ЛОЖЬ()
Функция И возвращает логическое значение ИСТИНА, если все аргументы функции вернули истинное значение. Если хотя бы один аргумент возвращает значение ЛОЖЬ, то вся функция вернет данное значение.
В виде аргументов должны приниматься условия либо ссылки на ячейки, возвращающие логические значения. Количество аргументов не может превышать 255. Первый аргумент является обязательным.
Рассмотрим таблицу истинности данной функции:
| И | ИСТИНА | ЛОЖЬ |
| ИСТИНА | ИСТИНА | ЛОЖЬ |
| ЛОЖЬ | ЛОЖЬ | ЛОЖЬ |
Синтаксис : =И(Логическое_значение1; [Логическое_значение1];…)
Пример использования:
В первом примере видно, что все аргументы возвращают истинное значение, следовательно, и сама функция вернет истинный результат.
Во втором примере функция никогда не вернет значение ИСТИНА, т.к. условие ее второго аргумента заранее неравно.
Функция ИЛИ возвращает логическое значение ИСТИНА, если хотя бы один аргумент функции вернет истинное значение.
В виде аргументов принимаются условия либо ссылки на ячейки, возвращающие логические значения. Количество аргументов не может превышать 255. Первый аргумент является обязательным.
Таблица истинности функции ИЛИ:
| ИЛ И | ИСТИНА | ЛОЖЬ |
| ИСТИНА | ИСТИНА | ИСТИНА |
| ЛОЖЬ | ИСТИНА | ЛОЖЬ |
Синтаксис : =ИЛИ(Логическое_значение1; [Логическое_значение2];…)
В качестве примера, рассмотрите примеры функции И, все они вернут результат ИСТИНА, т.к. первый аргумент является истинным.
Функция НЕ принимает в виде аргумента всего одно логическое значение и меняет его на противоположное, т.е. значение ИСТИНА она изменит на ЛОЖЬ и наоборот.
Таблица истинности функции И с применением функции НЕ:
| Н Е ( И()) | ИСТИНА | ЛОЖЬ |
| ИСТИНА | ЛОЖЬ | ИСТИНА |
| ЛОЖЬ | ИСТИНА | ИСТИНА |
Таблица истинности функции ИЛИ с применением функции НЕ:
| Н Е ( ИЛ И ()) | ИСТИНА | ЛОЖЬ |
| ИСТИНА | ЛОЖЬ | ЛОЖЬ |
| ЛОЖЬ | ЛОЖЬ | ИСТИНА |
Синтаксис: = НЕ(логическое_значение)
Функция ЕСЛИ является одной из самых полезных, имеющихся в Excel, функций. Она проверяет результат переданного ей логического выражения и возвращает результаты в зависимости от того истинно он или ложно.
Синтаксис: =ЕСЛИ(Логическое_выражение;[Значение_если_истина];[Значение_если_ложь])
Примеры использования функции:
Рассмотрим первый простой пример, чтобы понять, как функция работает.
Умышлено в первый аргумент функции вставить функцию ИСТИНА. В результате проверки, будет возвращен 2 аргумент (значение_если_истина), 3 аргумент будет опущен.
Теперь приведем пример использования вложенности одной функции ЕСЛИ в другую. Такой подход может понадобиться, когда при выполнении (или невыполнении) одного условия требуется дополнительная проверка.
Условия примера:
Имеются банковские карточки с номерами, начинающимися с первых четырех цифр, которые являются идентификатором вида карты:
1111 – Visa;
2222 – Master Card.
Используем нашу функцию для определения типа карты.
Функция, применяемая в данном примере, выглядит так:
= ЕСЛИ ( ЛЕВСИМВ(A2;4)="1111 "; "Visa";ЕСЛИ(ЛЕВСИМВ(A2;4)="2222";"Master Card";"карта не определена"))
Помимо самой рассматриваем функции, в примере используется текстовая функция ЛЕВСИМВ, которая возвращает часть текста из строки, начиная с левого края, в количестве символов, заданном вторым ее аргументом. С ее помощью мы проверяем, являются ли они равными строке «1111», если да, возвращаем результат «Visa», если нет, то выполняем вложенную функцию ЕСЛИ.
Подобным образом можно достичь значительной вложенности и организовывать сложные проверки.
Функция СУММЕСЛИ производит суммирование элементов, которые соответствуют заданным условиям.
Синтаксис:
=СУММЕСЛИ(диапазон_условия; критерий;[диапазон_суммирования]),
где диапазон_условия – обязательный атрибут. Ссылка на ячейку или диапазон ячеек, которые необходимо проверить на совпадение с условием;
критерий – обязательный атрибут. Содержит в себе конкретное значение либо условие для проверки. Условия типа больше, меньше, равно либо их комбинации всегда заключаются в кавычки.
диапазон_суммирования – необязательный атрибут. Ссылка на ячейку либо диапазон ячеек, которые необходимо просуммировать в случае, если элемент диапазона условия подходит под критерий. Если аргумент не указан, то по умолчанию он принимает значение первого аргумента. Также, если диапазон указан не правильно, т.е. для вертикального диапазона условия, указан горизонтальный диапазон суммирования, то последний заменяется на вертикальный, не меняя своего первого элемента, т.е. претерпевает транспонирование.
Пример использования:
В данном примере производится суммирование чисел, которые больше 2. Так как диапазон суммирования не указан, то по умолчанию принимает диапазон условия.
В следующем примере используются разные типы диапазонов, поэтому 3 аргумент меняет ссылку с A1:B1 на A1:A2, и функция возвращает значение 2.
При совместном использовании текстовых и числовых значений в диапазоне условия, проверяться будут либо те, либо другие. Рассмотрите последние два примера.
В первом случае, необходимо произвести суммирование по B1:B5, если элемент из A1:A5 больше нуля. Возвращаемое значение 4, так как текстовый элемент A3 игнорируется.
Теперь изменим условие и найдем сумму, если элементы для условия больше или равняются «а». По условиям сортировки все числа являются меньшими любым буквам, поэтому результат должен быть 5. Но так как в условии задано сравнение с текстовой строкой, то все числовые значения отбрасываются. Чтобы они учитывались, их необходимо перевести в текстовый формат. Также можно использовать массивы, для лучшего контроля перевода чисел в текст – {=СУММ(ЕСЛИ(ТЕКСТ(A1:A5;0)<="а";B1:B5;0))}.
Задание
Заполнить таблицу, следуя нижеуказанному порядку.
Порядок выполнения работы
1. Создайте таблицу оклада работников предприятия.
Таблица 1. Исходные данные
| Оклад работников предприятия | ||||
| статус | категория | оклад | премии | |
| начальник | 1 | 15 256,70р. | 5 000,00р. | |
| инженеры | 2 | 10 450,15р. | 4 000,00р. | |
| рабочие | 3 | 5 072,37р. | 3 000,00р. | |
2. Ниже создайте таблицу для вычисления заработной платы работников предприятия.
Таблица 2. Расчёт заработной платы работников
| Заработная плата работников предприятия | |||||||
| № п/п | фамилия рабочего | категория рабочего | оклад рабочего | ежемесячные премии | подоходный налог (ПН) | заработная плата (ЗП) | |
| 1 | Иванов | 3 |
|
|
|
| |
| 2 | Петров | 3 |
|
|
|
| |
| 3 | Сидоров | 2 |
|
|
|
| |
| 4 | Колобков | 3 |
|
|
|
| |
| 5 | Пентегова | 3 |
|
|
|
| |
| 6 | Алексеева | 3 |
|
|
|
| |
| 7 | Королев | 2 |
|
|
|
| |
| 8 | Бурин | 2 |
|
|
|
| |
| 9 | Макеев | 1 |
|
|
|
| |
| 10 | Еремина | 3 |
|
|
|
| |
| Итого |
| ||||||
3. Оклад рабочего зависит от категории, используйте логическую функцию ЕСЛИ. Ежемесячная премия рассчитывается таким же образом. Подоходный налог считается по формуле: ПН=(оклад+премяя)*0,13. Заработная плата по формуле: ЗП=оклад+премия-ПН.
4. Отсортируйте таблицу 2 в алфавитном порядке.
6. На предприятии произошли изменения, внесите данные изменения в таблицу:
а) ежемесячные премии в независимости от статуса и категории выплачиваются всем по 3000 рублей;
б) оклад рабочего вырос на 850 рублей;
в) Макеев вышел на пенсию;
г) Иванов поднялся по службе и стал инженером, Королев – начальником, а вот Бурина за нарушение дисциплины сократили до рабочего.
7. Сохраните работу.
6. Контрольные вопросы:
1. Какие логические функции вам известны?
2. Опишите последовательность составления логической функции ЕСЛИ.
3. Как отсортировать список по алфавитному порядку?
4. Как работать с функциями И/ ИЛИ?
Содержание отчета
Отчет должен быть выполнен в соответствии с Общими требованиями к оформлению документов учебной деятельности обучающихся. Отчет должен содержать следующие разделы:
1. Наименование работы.
2. Цель работы.
3. Конечные результаты выполненной работы в виде скриншотов.
4. Ответы на контрольные вопросы.
5. Вывод.
Дата: 2019-04-23, просмотров: 291.
