Дисциплина Информационные технологии в профессиональной деятельности
Специальность 080108 «Банковское дело»
Практическая работа. Использование логических функций для анализа данных.
(2 часа)
Цели: Совершенствование приемов работы в табличном процессоре MS Excel. Освоение приемов работы c логическими функциями MS Excel.
Тип урока: практическое занятие.
Ход занятия:
Организационный момент.
Сообщение темы и цели занятия.
Изучение нового материала
Подведение итогов.
Основные понятия.
Создание сложных формул связано, как правило, с использованием встроенных логических функций MS Excel.
^ Функция |
Описание
|
ЕСЛИ (логич_выражение; значение_если_истина; значение_если_ложь)
IF ( )
| Логическое ветвление
(допускается до 7 вложений):
логич_выражение – любое значение или выражение, принимающее значение ИСТИНА или ЛОЖЬ;
значение_если_истина – значение, которое возвращается, если логич_выражение равно ИСТИНА
значение_если_ложь – значение, которое возвращается, если логич_выражение равно ЛОЖЬ
|
И (логич_значение 1; логич_значение 2; …)
FND ( )
|
^
возвращает значение ИСТИНА, если все аргументы имеют значение ИСТИНА; возвращает значение ЛОЖЬ, если хотя бы один аргумент имеет значение ЛОЖЬ
|
ИЛИ (логич_значение 1; логич_значение 2; …)
OR ( )
|
^
возвращает значение ИСТИНА, если хотя бы один из аргументов имеет значение ИСТИНА; возвращает значение ЛОЖЬ, если все аргументы имеют значение ЛОЖЬ
|
НЕ (логич_ значение)
NOT ( )
|
^
изменяет на противоположное значение своего аргумента
|
Задания для практической работы.ЗАДАНИЕ 1: Сформировать на рабочем листе ведомость «Расчет заработной платы для работников научно – проектного отдела «Альфа»»
В ячейку A2 поместить название – расчет заработной платы работников научного – проектного отдела «Альфа», отцентрировать по левому краю (например, командой Формат/ Ячейки/ вкладка выравнивание либо соответствующей кнопкой по правому краю на панели инструментов).
В ячейки A3:K3 ввести название полей ведомости: № пп., Фамилия И.О., Должность, Тарифная ставка, Стаж, k, Надбавка за стаж, Итого, Процент налога, Удержать, Выплата.
К шапке ведомости – к каждому столбцу – создать скрытые примечания. Примечания создаются командой Вставка/Примечание (также можно использовать панель инструментов Рецензирование, которая добавляется командой Вид/Панели инструментов/Рецензирование):
№ пп – номер работника отдела;
Фамилия И.О. – заносятся все фамилии работающих в научно – проектном отделе;
Должность – занимаемая должность на момент заполнения ведомости;
Тарифная ставка – денежный эквивалент занимаемой должности;
Стаж – вносится целое число отработанных лет на момент заполнения ведомости;
k – коэффициент за стаж работы;
Надбавка за стаж – денежный эквивалент за стаж работы;
Итого – начисление заработной платы с учетом тарифной ставки и стажа работы;
Процент налога – определяет процент отчислений в бюджет;
Удержать – денежный эквивалент отчислений в бюджет;
Выплата – сумма, предназначенная к выдаче.
При расчетах в ведомости учитывать следующее:
k, Надбавка за стаж, Итого, Процент налога, Удержать, Выплата – вычисляются с помощью соответствующих формул, с использованием автозаполнения или копирования формулы.
Коэффициент k присваивается из следующего расчета: 0,1 – отработано до 5 лет включительно, 0,2 – от 5 до 10 лет включительно, 0,25 – от 10 до 15 лет включительно, 0,3 – свыше 15 лет. Формула ячейки F4:
=ЕСЛИ(E4<=5;0,1;ЕСЛИ (И(E4>5;E4<=10));0,2; ЕСЛИ (И(E4>10;E4<=15);0,25;0,3)))
Надбавка за стаж – денежный эквивалент за стаж работы. Формула для ячейки G4:
=D4*F4
Пользовательский формат числа для ячейки G4:
# ##0,00р.;
(вводится командой Формат/Ячейки/вкладка Число, из списка Числовые форматы выбрать Все форматы и в поле Тип ввести указанный формат).
Итого – тарифная ставка с учетом стажа. Формула для ячейки H4:
=D4+G4
Пользовательский формат для ячейки H4:
# ##0, 00р;
Процент налога – учитывает, что 2% - начисленные (по Итого) составляет до7000р. Включительно, 10% - более 7000р. до 10000р. включительно, 20% - более 10000р. До 25000р. Включительно, 35% - превышающие 25000р. Формула для ячейки I4:
=ЕСЛИ(H4<=7000;0,02;ЕСЛИ(И(H4>7000;H4<=10000);0,1;ЕСЛИ(И(H4>10000;H4<=25000);2,0,35)))
Формат числа для ячейки I4 – Процентный.
Удержать – денежный эквивалент налогов. Формула для ячейки J4:
=H4*I4
Пользовательский формат числа для ячейки J4:
# ##0,00р.;
Выплата – сумма к выдаче: Итого без Удержать.
Требования к столбцу Стаж:
Создать пользовательский формат данных, учитывающий стаж работы: до 5 лет – данные представлены желтым цветом, от 5 до 10 – синим, от 10 до 15 – зеленым, свыше 15 – красным.
Воспользоваться командой Формат/Ячейки и ввести пользовательский формат для ячейки E4:
(Красный)# ##0;
а также использовать команду Формат/Условное форматирование.
В случае ввода отрицательного числа лет должно появляться соответствующее окно. Для проверки ввода чисел использовать команду Данные/Проверка/ вкладка Сообщение об ошибке.
Для поля тарифная ставка – вывести постоянное сообщение: ^ . Для получения которого использовать команду Данные/Проверка/ вкладка Сообщение для ввода.
В случае отрицательных значений в столбце Тарифная ставка появляется соответствующее предупреждение: ^ . Оно формируется через пользовательский формат:
# ##0,00р.;(Красный) «Тарифная ставка не может быть отрицательной!»
Задания для самостоятельной работы.^
Ведомость переоценки основных средств производств
|
|
|
|
|
|
|
|
|
|
|
|
|
Наименование объекта
|
Балансовая стоимость (БС)
|
Износ объекта
(ИО)
|
Остаточная стоимость (ОС)
|
Восстановительная полная стоимость (ВПС)
|
Восстановительная остаточная стоимость (ВОС)
|
Отдел менеджмента и маркетинга
|
19087,8
|
568,8
|
|
|
|
Отдел транспортировок
|
407,2
|
203
|
|
|
|
Сборочный цех
|
673
|
198,8
|
|
|
|
Отделочный цех
|
821,6
|
401,2
|
|
|
|
Склад №1
|
598,7
|
131
|
|
|
|
Склад №2
|
610
|
311,2
|
|
|
|
Склад №3
|
756,8
|
159,5
|
|
|
|
Итого
|
|
|
|
|
|
|
|
|
|
|
|
^
|
|
|
|
|
|
ОС=БС-ИО
|
|
|
|
|
|
ВПС=БС*К
|
|
|
|
|
|
ВОС=ОС*К
|
|
|
|
|
|
|
|
|
|
|
|
где К - коэффициент, равный:
|
|
|
|
|
|
3,3 - если БС меньше либо равен 650 млн руб.;
|
|
|
|
|
4,2 - если БС больше 650 млн руб., но меньше 1000 млн руб.;
|
|
|
5,1 - если БС равен 1000 млн руб. или более.
|
|
|
|
| Контрольные вопросы:
Перечислить логические функции MS Excel.
В каких случаях используются логические функции.
Как работает функция Если?
Как работает функция И?
Как работает функция ИЛИ?
Как работает функция НЕ?
Придумать примеры использования логических функций.
|
