Дисциплина Информационные технологии в профессиональной деятельности
Специальность 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.
В каких случаях используются логические функции.
Как работает функция Если?
Как работает функция И?
Как работает функция ИЛИ?
Как работает функция НЕ?
Придумать примеры использования логических функций.
|