Лабораторная работа 1 по EXCEL план работы Общие приемы работы с таблицей
Работа добавлена на сайт samzan.net: 2015-07-10
Поможем написать учебную работу
Если у вас возникли сложности с курсовой, контрольной, дипломной, рефератом, отчетом по практике, научно-исследовательской и любой другой работой - мы готовы помочь.
Предоплата всего
от 25%
Подписываем
договор
4
PAGE 2
Лабораторная работа №1 по EXCEL
(план работы)
- Общие приемы работы с таблицей.
- Выполнить настройку кнопок на ленте панели инструментов. Добавить новую вкладку "Моя вкладка". В ней создать несколько групп кнопок. Добавить кнопку "Блокировать ячейку" из категории "Формат". Добавить другие кнопки таким образом, чтобы в каждой группе было как минимум 5 кнопок. Добавить кнопку "Быстрая печать" на панель быстрого доступа. Все эти действия выполнить щелкнув правой кнопкой мыши по ленте панели инструментов и выбрав пункт меню "Настройка ленты".
- Увеличить до 30 список открывавшихся ранее файлов (ФАЙЛ/Параметры/Дополнительно).
- Увеличить до 5 количество листов в книге (ФАЙЛ/Параметры/Общие).
- Заменить имя 1-го листа на "Справочник" - щелчок правой клавишей по ярлыку листа.
- Освоить добавление и перемещение листов, используя правую клавишу мыши
- Освоить ввод, просмотр и корректировку примечаний к ячейкам таблицы – щелчок правой клавишей по ячейке – "Добавить примечание", "Изменить примечание", "Удалить примечание" и "Отобразить примечание".
- Освоить стандартные функции СУММ, ВПР, СУММЕСЛИ, ЕСЛИ, И, ИЛИ и НЕ.
- Освоить технологию копирования значений и формул.
- Освоить приемы создания пользовательских форматов данных.
- Освоить технологию размещения в ячейке наклонных линий и надписей. Для этого выделить какую-либо ячейку и выполнить ее форматирование, выбрав границу по диагонали. Ввести в ячейку два слова: Стоимость и Расстояние. После ввода 1-го слова нажать комбинацию Alt+Enter для ввода 2-го слова во 2-ую строку ячейки. Отрегулировать положение слов в углах ячейки, расстановкой пробелов. Расположить слова по наклону параллельно диагональной линии.
- Создать таблицы для расчета заработной платы 20 работникам предприятия.
1. Создать рабочую книгу Расчет зарплаты.xls. Сохранить ее с указанным именем в папке "Мои документы". Установить включенным флажок "Всегда сохранять резервную копию", щелкнув по кнопке "Сервис" в окне сохранения.
Переименовать текущий лист рабочей книги в лист с именем "Справочник". Для этого щелкнуть правой клавишей мыши по ярлычку листа и в контекстном меню выбрать пункт "Переименовать". Ввести в поле ярлыка новое название.
2. Ввести заголовок – "Справочник работников предприятия". Установить жирный шрифт заголовка. Заголовок по стилю оформления должен в точности повторять представленный ниже пример:
Таблица 1
|
Справочник работников предприятия
|
|
Таб. номер
|
Фамилия, Имя, Отчество
|
Разряд
|
Должность
|
Член профсоюза
|
Дата поступления
|
Отдел
|
Кол-во льгот по налогообложению
|
% удержания алиментов
|
3. Выделить ячейки заголовка и правым щелчком мыши выбрать команду ФОРМАТ ЯЧЕЕК. Выбрать на вкладке "Выравнивание" и указать горизонтальное по значению и вертикальное по верхнему краю.
4. Ввести в таблицу 20 строк данных о сотрудниках организации с информацией с учетом следующих правил:
- Табельные номера (4 знака) вводить в произвольном порядке. Табельные номера должны быть уникальными;
- Разряд вводить в соответствии с табл.2;
- Отелы номеровать числами: 1,2,3;
- Количество льгот по налогообложению от 0 до 5;
- Предусмотреть 5 вариантов должностей;
- Для членов профсоюза вводить 1, для других работников – 0;
- Установить пользовательский формат даты ДД.ММ.ГГГГ. Для этого выделить ячейки с датами и в контекстном меню выполнить пункт "Формат ячеек". На вкладке "Число" установить в окне "Числовые форматы" (все форматы), а в окне тип изменить или ввести ДД.ММ.ГГГГ
- Процент удержания алиментов указать для 5-х человек в диапазоне от 25 до 50.
5. Отсортировать строки таблицы по возрастанию табельного номера.
Необходимо рассчитать:
|
Показатель
|
Формула
|
Примечание
|
|
начисленную зарплату
|
ЗП=ЗПР*ФТ/Т
|
|
|
удержание в социальные фонды
|
УСФ=ЗП*0,01
|
|
|
удержание подоходного налога
|
УПН=(ЗП-УСФ-МРОТ*Л)*ПРПДН/100
|
|
|
удержание профсоюзных взносов
|
УПВЗН=ЗП*ПРВЗН/100
|
только для членов профсоюза
|
|
удержание алиментов
|
УАЛ=ПРАЛ*(ЗП-УПН)/100
|
только для лиц, выплачивающих алименты
|
|
зарплату к выдаче
|
ЗПВ=ЗП-УСФ-УПН-УПВЗН
|
|
где:
ФT - фактически отработанное время (дней),
Л - количество льгот,
ЗПР - оклад работника в соответствии с его разрядом;
МРОТ – минимальный размер оплаты труда (4611 рублей),
Т – плановое количество рабочих дней в месяце,
ПРАЛ – процент удержания алиментов,
ПРВЗН – процент удержания профсоюзных взносов,
ПРПДН – процент удержания подоходного налога.
Оклад работника зависит от его квалификации (разряда). Эта зависимость представлена в "Справочнике окладов" (Таблица 2).
Справочник окладов
Таблица 2
|
Разряд
|
Оклад
|
|
1
|
8000
|
|
2
|
12000
|
|
3
|
16000
|
|
4
|
20000
|
|
5
|
24000
|
|
6
|
28000
|
|
7
|
32000
|
В процессе решения задачи необходимо будет задавать размер минимальной зарплаты и количество рабочих дней в месяце.
6. На листе "Справочник" в ячейках L2:L12 создать список праздничных дней текущего года, например: 01.01.2012, 02.01.2012, 03.01.2012, 04.01.2012, 05.01.2012, 07.01.2012, 23.02.2012, 08.03.2012, 01.05.2012, 09.05.2012, 12.06.2012, и т.д. Установить для этих ячеек соответствующий формат отображения дат.
- Создать таблицу для расчета зарплаты за ЯНВАРЬ месяц.
|
Расчет зарплаты за январь
|
|
Таб. номер
|
Фамилия, Имя, Отчество
|
Оклад, руб.
|
Отработано дней
|
Начислено рублей
|
Удержано в социальные фонды, руб.
|
Удержано в профсоюзный фонд, руб.
|
Льготы по налогообложению, руб.
|
Подоходный налог, руб.
|
Удержано алиментов, руб.
|
Удержано всего, руб.
|
Зарплата к выдаче, руб.
|
- Перейти на другой лист рабочей книги. Переименовать его в "Январь". Ввести "шапку" таблицы.
- Заполнить колонку с табельными номерами работников (20 строк) в произвольном порядке.
- Ввести расчетные формулы в строку первого работника.
- Для определения фамилии по табельному номеру ввести в ячейку формулу поиска фамилии в справочнике: =ВПР().
- Для вставки формулы установить курсор в заданную клетку и щелкнуть по кнопке "fx". Найдите и выберите в окне со списком функций функцию ВПР.
- Щелкнуть мышью по клетке с таб. номером. В поле "Искомое значение" появится соответствующий адрес.
- Щелкнуть мышью по полю "Таблица", активизируя его. Выделить мышью область клеток справочной таблицы, перейдя на лист справочника. В активизированном поле появятся соответствующие адреса справочной таблицы. Последовательно устанавливать курсор на адреса клеток замените адреса на абсолютные.
- Занести в поле "Номер столбца" значение 2 (для фамилии).
- Ввести в поле "Интервальный просмотр" текст "ЛОЖЬ" или число 0.
- Ввести внизу под расчетной таблицей справочные данные для текущего месяца:
- размер минимальной оплаты труда;
- процент удержания подоходного налога;
- процент удержания профсоюзных взносов;
- процент удержания для выплат пенсионный фонд;
- текущая расчетная дата, например 01.01.2012;
- плановое количество рабочих дней в месяце.
- Рассчитать плановое количество рабочих дней в январе, используя стандартную функцию
ЧИСТРАБДНИ(нач_дата;кон_дата;праздники)
Перед вводом этой функции убедиться, что она доступна, т.е. имеется в списке стандартных функций категории "Дата и время". Если этой функции в списке нет, то ввести ее в список, выполнив команду ФАЙЛ/Параметры/Надстройки/Перейти и включить в появившемся окне флажок "Пакет анализа".
Ввести в качестве начальной даты текст: "01.01.2012"
Ввести в качестве конечной даты текст: "31.01.2012"
Ввести в качестве 3-го аргумента (праздники) диапазон ячеек на листе "Справочник", содержащий список праздничных дней.
- После ввода и отладки всех формул 1-ой строки скопировать их вниз для каждого работника. Предусмотреть невозможность отрицательных значений подоходного налога.
- Присвоить имя "Справ_раб" диапазону ячеек справочника работников предприятия. Для этого выделить блок ячеек справочника, вызвать контекстное меню правой кнопкой мыши и выполнить команду Присвоить имя.
- Присвоить другим способом имя "Спр_окл" диапазону ячеек справочника окладов и имя "Праздники" диапазону ячеек справочника праздничных дней. Для этого выделить соответствующий диапазон ячеек и щелкнуть левой клавишей мыши по полю имен. Ввести в поле имен имя диапазона. Проверить правильность присвоения имен, выбрав их из списка имен и просмотрев выделенные области. Заменить во всех ранее введенных формулах адреса диапазонов на их имена.
- Добавить столбцы для вычисления сумм удержанных алиментов с учетом текущей даты и срока окончания удержания. Дополнить справочник информацией по срокам окончания удержания.
- Рассчитать сумму "Удержано всего" по каждому работнику.
- Рассчитать сумму "Зарплата к выдаче" как разность между начисленной суммой и суммой "Удержано всего". Предусмотреть невозможность выдачи отрицательных сумм.
- Подсчитать итоговые суммы по всем работникам в целом и в т.ч. по каждому отделу. Для этого использовать функции СУММ и СУММЕСЛИ.