Пример расчета и начисления заработной платы. Ведомость начисления заработной платы Создать таблицу ведомости начисления заработной платы

Практическая работа № 14 Задание 1 1. Создайте таблицу учета товаров, пустые столбцы сосчитайте по формулам. 70,00 курс доллара Таблица учета проданного товаров цена в цена в долларах всего в рублях за за 1 рублях 1 товар товар № п\п назван ие поставле но продано 1 товар 1 50 43 170 2 товар 2 65 65 35 3 товар 3 50 43 56 4 товар 4 43 32 243 5 товар 5 72 37 57 осталось Всего 2. Отформатируйте таблицу по образцу. 3. Постройте круговую диаграмму, отражающую процентное соотношение проданного товара. 4. Сохраните работу в собственной папке под именем Учет товара. Задание 2 1. Составьте таблицу для выплаты заработной платы для работников предприятия. Расчет заработной платы. № п/п Фамилия, И.О. Полученн Налоговые Налогооб лагаемый ый доход вычеты доход 1 Молотков А.П. 18000 1400 2 Петров А.М. 9000 1400 3 Валеева С. Х. 7925 0 4 Гараев А.Н. 40635 2800 5 Еремин Н.Н. 39690 1400 6 Купцова Е.В. 19015 2800 Сумма налога, К выплате НДФЛ Итого 2. Сосчитайте по формулам пустые столбцы. Налогооблагаемый доход = Полученный доход – Налоговые вычеты. Сумма налога = Налогооблагаемый доход*0,13. К выплате = Полученный доход-Сумма налога НДФЛ. 3. Сохраните работу в собственной папке под именем Расчет. Задание №3 1. Создайте таблицу оклада работников предприятия. Оклад работников предприятия статус категория оклад премии начальник 1 15 256,70р. 5 000,00р. инженеры 2 10 450,15р. 4 000,00р. рабочие 3 5 072,37р. 3 000,00р. 2. Ниже создайте таблицу для вычисления заработной платы работников предприятия. Заработная плата работников предприятия № п/п фамилия рабочего категория рабочего 1 Иванов 3 2 Петров 3 3 Сидоров 2 4 Колобков 3 5 Пентегова 3 6 Алексеева 3 7 Королев 2 8 Бурин 2 9 Макеев 1 10 Еремина 3 оклад рабочего ежемесячн ые премии подоходн ый налог (ПН) Итого 3. Оклад рабочего зависит от категории, используйте логическую функцию ЕСЛИ. Ежемесячная премия рассчитывается таким же заработна я плата (ЗП) 4. 5. 6. 7. 8. 9. образом. Подоходный налог считается по формуле: ПН=(оклад+премяя)*0,13. Заработная плата по формуле: ЗП=оклад+премия-ПН. Отформатируйте таблицу по образцу. Отсортируйте таблицу 2 в алфавитном порядке. На предприятии произошли изменения, внесите данные изменения в таблицу: a. ежемесячные премии в не зависимости от статуса и категории выплачиваются всем по 3000 рублей; b. оклад рабочего вырос на 850 рублей; c. Макеев вышел на пенсию; d. Иванов поднялся по службе и стал инженером, Королев – начальником, а вот Бурина за нарушение дисциплины сократили до рабочего. Найдите максимальную и минимальную зарплату сотрудников с помощью функции МИН(МАКС). С помощью условного форматирования выделите ячейки красным цветом тех сотрудников, чья зарплата РАВНА МАКСИМАЛЬНОЙ. Сохраните работу в собственной папке под именем Зарплата. Задание № 4. 1. Создайте рабочую книгу, состоящую из трех рабочих листов. 2. Первый лист назовите ИТОГИ. В нем должен содержаться отчет о финансовых результатах предприятия за месяц. Отчет о финансовых результатах предприятия за сентябрь Выручка Расход Прибыль 3. Второй лист назовите ВЫРУЧКА. Постройте таблицу Выручки от продаж за текущий месяц. Сосчитайте пустые столбцы по формулам. Выручка от продажи товара за сентябрь курс доллара 32 № Наименование п/п товара Цена в долларах 1 1 Товар 1 Цена в рублях Количество товара 5 Итого в рублях 2 Товар 2 3 10 3 Товар 3 5 15 4 Товар 4 7 20 5 Товар 5 9 25 6 Товар 6 11 30 7 Товар 7 13 35 8 Товар 8 15 40 9 Товар 9 17 45 10 Товар 10 19 50 Итого 4. Третий лист назовите РАСХОДЫ. В него занесите Расходы предприятия за текущий месяц. Расходы предприятия за сентябрь № п/п Расходы Сумма в рублях 1 Заработная плата 2500 2 Коммерческие 4000 3 Канцелярские 5500 4 Транспортные 7000 5 Прочее 8500 Итого 5. Заполните первый лист, используя ссылки на соответствующие листы. 6. Сохраните работу в собственной папке под именем Итоги.

Расчетная ведомость формы Т-51 составляется в том случае, если сотруднику перечисляется заработная плата на платежную карту одного из банков. Для расчета работника она использоваться не может (в отличие от расчетно-платежной). Заполнение платежной и расчетно-платежных форм при этом необязательно.

ФАЙЛЫ

Кем проводится

Обязанности по заполнению этой бумаги в подавляющем большинстве случаев ложатся на бухгалтера по заработной плате. Если в компании бухгалтер один, то на него. Каждой формирующейся ведомости он обязан присвоить порядковый номер. Удобнее делать это с начала года или отчетного периода, создавая новые каждый календарный месяц.

Какие документы создаются на ее основе

Информация из расчетной ведомости поступает в платежную ведомость, а уже согласно ей производятся начисления заработной платы. В этом процессе принимает участие только последняя графа Т-51. Для того чтобы ее сформировать, необходим табель учета рабочего времени. Все эти документы оформляются по каждому работнику отдельно.

Периодичность заполнения

В большинстве случаев зарплата работникам выдается два раза в месяц. Такие условия указаны в Трудовом кодексе Российской Федерации, за его нарушение компания рискует понести административную ответственность. Причем первая выплата считается авансовой (обычно это процент от оклада), а вторая – основной (оставшаяся часть суммы). Таким образом, для аванса будет оформляться простая платежная ведомость (в ней указывается сумма, которая выплачена в первой половине месяца).

Форма Т-51 служит для иллюстрации и документального оформления основной части выдачи заработной платы сотрудникам учреждения.

Столбец «Удержано и зачтено» в табличной части документа при этом должен учитывать и авансовую часть — данные из первой бумаги.

Кем утверждена

Этот документ был утвержден Постановлением Госкомстата Российской Федерации от 5.01.2004 г. №1. Упоминание об этом факте должно присутствовать на бланке, в верхней правой части.

Форма

Удобнее всего заполнять графы документа в электронном виде, в программе 1С. Обязательно нужно переводить ведомость в бумажный вариант не реже раза в месяц. Но допустимо и ее ведение целиком в бумажном виде.

Если работа ведется в 1С и требуется какая-либо корректировка даты (нужно создать не текущим числом), то для этого в «Параметрах» выбирается нужное число либо выбирается «Таблица», затем «Вид» и «Редактирование» и меняются данные нужной ячейки в ручном режиме.

Алгоритм заполнения

Для полноценной функциональности бумага может быть оформлена и в единственном экземпляре. В верхней части документа заполняются:

Основные реквизиты. Код по ОКПО уже вписан в бланк — 0301010. ОКУД заполняется.
Полное наименование фирмы, при наличии – структурного подразделения компании, внутри которой заполняется форма.
Название ведомости, ее номер, дата постановки подписей.
Период, за который производились вычисления.

Дата составления документа может быть выбрана произвольно, но при условии, что этот день не будет раньше последнего дня в текущем месяце и не позже фактического дня списания денежных средств с кассы организации.

Помимо этого, на второй странице ведомости располагается таблица, каждый столбец которой должен быть заполнен (в противном случае в ячейке таблицы ставится прочерк).

Всего документ содержит 18 столбцов со следующими наименованиями:

порядковый номер сотрудника, которому предназначается выплата;
табельный номер этого же работника;
фамилия, имя и отчество (последние сокращаются до инициалов);
занимаемая должность, профессия или специализация, которой занимается сотрудник;
оклад либо тарифная ставка;
сколько дней или часов было отработано в указанный период (отдельно указываются выходные и будние дни);
сумма, которая начислена организацией этому сотруднику за месяц (столбец разделяется на разные виды плат, включая «общий» столбец, который обобщает данные);
какая сумма была удержана и зачтена ранее (аванс, налог на доходы и пр.);
долги работника перед организацией или, наоборот, точная сумма;
сколько всего денежных средств положено выплатить работнику по этой ведомости.

Кем подписывается

На распечатанном документе обязательно должна быть указана должность, подпись и расшифровка подписи того должностного лица, которое составляло документ. Это может быть главный бухгалтер или бухгалтер по кадрам.

ВНИМАНИЕ! Ведомость не будет действительна без печати организации на последней странице.

Кстати, по нормам допустимо заполнять столько строк ведомости, сколько необходимо. У нее может быть два, три, четыре и более листов, составленных согласно образцу табличной части бумаги.

Работник перед получением заработной платы имеет право в любой момент ознакомиться со сформированным документом.

Нюансы заполнения

Табельный номер работника при заполнении автоматически берется из его . Бумага может быть распечатана в единственном экземпляре. Она помещается в архив организации. Никаких исправлений на уже заполненной бумаге быть не должно. Все необходимые корректировки выполняются в электронном виде.

Если выполнение служебных обязанностей не предполагает нахождение сотрудника во , то ведомость поступает в архив компании. Она должна храниться там, согласно установленным требованиям, минимум 5 лет.

Сроки выплат

После заполнения ведомости денежные средства должны поступить сотруднику как можно раньше. Максимально допустимый срок задержки при этом – 5 рабочих дней. Если выплата не была произведена в срок, то на ведомости проставляется отметка «Депонировано».

Важный момент! Данные столбца документа «К выплате» должны точно совпадать с столбцом в форме Т-49 «Сумма». Если они не равны, значит, в бухгалтерские расчеты по выплате заработной платы закралась ошибка.

ЛАБОРАТОРНАЯ РАБОТА 3

Тема: Относительная и абсолютная адресация в табличном процессоре MS EXCEL. Связанные таблицы, расчет промежуточных итогов в таблицах MS EXCEL. Подбор параметра, организация обратного расчета

Цель. Изучение информационной технологии применения относительной и абсолютной адресации для финансовых расчетов. Связывание листов электронной книги. Расчет промежуточных итогов. Структурированные таблицы. Изучение технологии подбора параметра при обратных расчетах.

Задание 1. Создать таблицы ведомости начисления заработной платы за два месяца на разных листах электронной книги, произвести расчеты, форматирование, сортировку и защиту данных.

Исходные данные представлены на рис. 1.1, результаты работы - на рис. 1.2 и 1.3.

Порядок работы

Запустите редактор электронных таблиц Microsoft Excel и создайте новую электронную книгу.

Создайте таблицу расчета заработной платы по образцу (см. рис. 2.1).

Введите исходные данные - Табельный номер, ФИО и Оклад, % Премии = 27 %, % Удержания = 13 %.

Выделите отдельные ячейки для значений % Премии (D4) и % Удержания (F4).

Рис. 1.1. Исходные данные для Задания 1

3. Произведите расчеты во всех столбцах таблицы.

При расчете Премии используется формула Премия = Оклад * % Премии. В ячейке D5 наберите формулу =$D$4xC5 (ячейка D4 используется в виде абсолютной адресации). Скопируйте набранную формулу вниз по столбцу автозаполнением.

Краткая справка . Для удобства работы и формирования навыков работы с абсолютным видом адресации рекомендуется при оформлении констант окрашивать ячейку цветом, отличным от цвета расчетной таблицы. Тогда при вводе формул в расчетную ячейку окрашенная ячейка с константой будет вам напоминанием, что следует установить абсолютную адресацию (набором символа $ с клавиатуры или нажатием клавиши ).

Формула для расчета «Всего начислено»:

Всего начислено = Оклад + Премия.

При расчете Удержания используется формула:

Удержания = Всего начислено х % Удержаний.

Для этого в ячейке F5 наберите формулу: =$F$4xE5. Формула для расчета столбца «К выдаче»:

К выдаче = Всего начислено - Удержания.

Рассчитайте итоги по столбцам, а также максимальный, минимальный и средний доходы по данным колонки «К выдаче» (Вставка/Функция/категория - Статистические функции).

Переименуйте ярлычок Листа 1, присвоив ему имя «Зарплата октябрь». Для этого дважды щелкните мышью по ярлычку и наберите новое имя. Можно воспользоваться командой Переименовать контекстного меню ярлычка, вызываемого правой кнопкой мыши. Результаты работы представлены на рис. 2.2.

Краткая справка. Каждая рабочая книга Excel может содержать до 255 рабочих листов. Это позволяет, используя несколько листов, создавать понятные и четко структурированные документы, вместо того чтобы хранить большие последовательные наборы данных на одном листе.

6. Скопируйте содержимое листа «Зарплата октябрь» на новый лист (Правка/Переместить/Скопировать лист). Можно воспользоваться командой Переместить/Скопировать контекстного меню ярлычка. Не забудьте для копирования поставить галочку в окне Создавать копию.

Краткая справка. Перемещать и копировать листы можно, перетаскивая их корешки (для копирования удерживайте нажатой клавишу ).

Рис. 1.2. Итоговый вид таблицы расчета заработной платы за октябрь

Присвойте скопированному листу название «Зарплата ноябрь». Исправьте название месяца в названии таблицы. Измените значение Премии на 32%. Убедитесь, что программа произвела, пересчет формул.

Между колонками «Премия» и «Всего начислено» вставьте новую колонку «Доплата» (Вставка/ Столбец) и рассчитайте значение доплаты по формуле:

Доплата = Оклад х % Доплаты.

Значение доплаты примите равным 5 %.

9. Измените формулу для расчета значений колонки «Всего на числено»:

Всего начислено = Оклад + Премия + Доплата.

Проведите условное форматирование значений колонки «К вьщаче». Установите формат вывода значений между 7000 и 10000 - зеленым цветом шрифта, меньше 7000 - красным, больше или равно 10 000 - синим цветом шрифта (Формат/Условное форматирование) (рис. 2.3).

Проведите сортировку по фамилиям в алфавитном порядке по возрастанию (выделите фрагмент таблицы с 5 по 18 строки без итогов - выберите меню Данные/Сортировка, сортировать по - Столбец В).

Поставьте к ячейке D3 комментарии «Премия пропорциональна окладу» (Вставка/Примечание); при этом в правом верхнем углу ячейки появится красная точка, которая свидетельствует о наличии примечания. Конечный вид таблицы расчета заработной платы за ноябрь приведен на рис. 2.4.

Рис. 1.3. Условное форматирование данных

13. Защитите лист «Зарплата ноябрь» от изменений (Сервис/ Защита/Защитить лист). Задайте пароль на лист, сделайте подтверждение пароля.

Убедитесь, что лист защищен и удаление данных невозможно. Снимите защиту листа (Сервис/Защита/Снять защиту листа).

14. Сохраните созданную электронную книгу под именем «Зарплата» в своей папке.

Рис. 1.4. Конечный вид таблицы расчета зарплаты за ноябрь

Дополнительные задания

Задание 1.2. Сделать примечания к двум-трем ячейкам.

Задание 1.3. Выполнить условное форматирование оклада и премии за ноябрь месяц: до 2000 - желтым цветом заливки; от 2000 до 10 000 - зеленым цветом шрифта; свыше 10 000 - малиновым цветом заливки, белым цветом шрифта.

Задание 1.4. Защитить лист зарплаты за октябрь от изменений.

Проверьте защиту. Убедитесь в неизменяемости данных. Снимите защиту со всех листов электронной книги «Зарплата».

Задание 1.5. Построить круговую диаграмму начисленной суммы к выдаче всех сотрудников за ноябрь месяц.

Порядок работы

Запустите редактор электронных таблиц Microsoft Excel и откройте созданный в практической работе 2 файл «Зарплата».

Скопируйте содержимое листа «Зарплата ноябрь» на новый лист электронной книги (Правка/Переместить/Скопировать лист).

Присвойте скопированному листу название «Зарплата декабрь». Исправьте название месяца в названии таблицы.

Измените значения Премии на 46 %, Доплаты - на 8 %. Убедитесь, что программа произвела пересчет формул (рис. 2.1).

5. По данным таблицы «Зарплата декабрь» постройте гистограмму дохода сотрудников. В качестве подписей оси X выберите фамилии сотрудников. Проведите форматирование диаграммы. Конечный вид гистограммы приведен на рис. 2.2.

Рис. 2.1. Ведомость зарплаты за декабрь

Перед расчетом итоговых данных за квартал проведите сортировку по фамилиям в алфавитном порядке (по возрастанию) в таблице расчета зарплаты за октябрь.

Скопируйте содержимое листа «Зарплата октябрь» на новый лист (Правка/Переместить/ Скопировать лист).

Рис. 2.2. Гистограмма зарплаты за декабрь

Присвойте скопированному листу название «Итоги за квартал». Измените название таблицы на «Ведомость начисления заработной платы за четвертый квартал».

Отредактируйте лист «Итоги за квартал» согласно образцу на рис. 2.3. Для этого удалите в основной таблице колонки «Оклад» и «Премия», а также строку 4 с численными значениями: % Премии и % Удержания и строку 19 «Всего». Удалите также строки с расчетом максимального, минимального и среднего доходов под основной таблицей. Вставьте пустую строку 3.

Вставьте новый столбец «Подразделение» {Вставка/Столбец) между столбцами «Фамилия» и «Всего начислено». Заполните столбец «Подразделение» данными по образцу (рис. 2.3).

Произведите расчет квартальных начислений, удержаний и суммы к выдаче как сумму начислений за каждый месяц (данные по месяцам располагаются на разных листах электронной книги, поэтому к адресу ячейки добавится адрес листа).

Краткая справка. Чтобы вставить в формулу адрес или диапазон ячеек с другого листа, следует во время ввода формулы щелкнуть по вкладке этого листа и выделить на нем нужные ячейки. Вставляемый адрес будет содержать название этого листа.

В ячейке D5 для расчета квартальных начислений «Всего начислено» формула имеет вид:

Зарплата декабрь!Р5 + Зарплата ноябрь!Р5 + + Зарплата октябрь! Е5.

Аналогично произведите квартальный расчет столбца «Удержания» и «К выдаче».

Рис. 2.3. Таблица для расчета итоговой квартальной заработной платы

Примечание. При выборе начислений за каждый месяц делайте ссылку на соответствующую ячейку из таблицы соответствующего листа электронной книги «Зарплата». При этом произойдет связывание ячеек листов электронной книги.

12. В силу однородности расчетных таблиц зарплаты по месяцам для расчета квартальных значений столбцов «Удержания» и «К выдаче» достаточно скопировать формулу из ячейки D5 в ячейки Е5 и F5.

Рис. 2.4. Расчет квартального начисления заработной платы связыванием листов электронной книги

Рис. 2.5. Вид таблицы начисления квартальной заработной платы после сортировки по подразделениям

Для расчета квартального начисления заработной платы для всех сотрудников скопируйте формулы вниз по столбцам D, Е и F. Ваша электронная таблица примет вид, как на рис. 2.4.

Рис. 2.6. Окно задания параметров расчета промежуточных итогов

Для расчета промежуточных итогов проведите сортировку по подразделениям, а внутри подразделений - по фамилиям. Таблица примет вид, как на рис. 2.5.

Рассчитайте промежуточные итоги по подразделениям, используя формулу суммирования. Для этого выделите всю таблицу и выполните команду Данные/Итоги (рис. 2.6). Задайте параметры подсчета промежуточных итогов:

при каждом изменении - в Подразделение; операция - Сумма;

добавить итоги: Всего начислено, Удержания, К выдаче. Отметьте галочкой операции «Заменить текущие итоги» и «Итоги под данными».

Примерный вид итоговой таблицы представлен на рис. 2.7.

Рис. 2.7. Итоговый вид таблицы расчета квартальных итогов по зарплате

15. Изучите полученную структуру и формулы подведения промежуточных итогов, устанавливая курсор на разные ячейки таблицы. Научитесь сворачивать и разворачивать структуру до разных уровней (кнопками «+» и «-»).

Краткая справка. Под структурированием понимаются многоуровневая группировка строк и столбцов таблицы и создание элементов управления, с помощью которых легко можно скрывать и раскрывать эти группы.

16. Сохраните файл «Зарплата» с произведенными изменениями.

Задание 3. Используя режим подбора параметра, определите штатное расписания фирмы.

Исходные данные приведены на рис. 3.1.

Краткая справка . Известно, что в штате фирмы состоят:

6 курьеров;

8 младших менеджеров;

10 менеджеров;

3 заведующих отделами;

1 главный бухгалтер;

1 программист;

1 системный аналитик;

1 генеральный директор фирмы.

Рис. 3.1. Исходные данные для Задания 3

Общий месячный фонд зарплаты составляет 100 000 р. Необходимо определить, какими должны быть оклады сотрудников фирмы.

Каждый оклад является линейной функцией от оклада курьера, а именно:

Зарплата = А*х + В„

где х - оклад курьера; А-, и Д- - коэффициенты, показывающие: А-, - во сколько раз превышается значение х; Д - на сколько превышается значение х.

Порядок работы

Запустите редактор электронных таблиц Microsoft Excel.

Создайте таблицу штатного расписания фирмы по приведенному образцу (см. рис. 3.1). Введите исходные данные в рабочий лист электронной книги.

Выделите отдельную ячейку D3 для зарплаты курьера (переменная «х») и все расчеты задайте с учетом этого. В ячейку D3 временно введите произвольное число.

В столбце D введите формулу для расчета заработной платы по каждой должности. Например, для ячейки D6 формула расчета имеет вид: = B6*$D$3 + С6 (ячейка D3 задана виде абсолютной адресации). Далее скопируйте формулу из ячейки D6 вниз по столбцу азтокопированием в интервале ячеек D6:D13.

В столбце F задайте формулу расчета заработной платы всех работающих в данной должности. Например, для ячейки F6 формула расчета имеет вид: = D6*E6. Далее скопируйте формулу из ячейки F6 вниз по столбцу автокопированием в интервале ячеек F6:F13.

В ячейке F14 вычислите суммарный фонд заработной платы фирмы.

5. Произведите подбор зарплат сотрудников фирмы для сум марной заработной платы в сумме 100 000 р. Для этого в меню Сервис активизируйте команду Подбор параметра.

В поле Установить в ячейке появившегося окна введите ссылку на ячейку F14, содержащую формулу расчета фонда заработной платы.

В поле Значение наберите искомый результат 100 000.

В поле Изменяя значение ячейки введите ссылку на изменяемую ячейку D3, в которой находится значение зарплаты курьера, и щелкните по кнопке ОК. Произойдет обратный расчет зарплаты сотрудников по заданному условию при фонде зарплаты, равном 100 000 р.

6. Сохраните созданную электронную книгу под именем «Штат ное расписание» в своей папке.

Задание 4. Используя режим подбора параметра и таблицу расчета штатного расписания (см. Задание 3), определите величину заработной платы сотрудников фирмы для ряда заданных значений фонда заработной платы.

Порядок работы

Выберите коэффициенты уравнений для расчета согласно табл. 3.1 (один из пяти вариантов расчетов).

Методом подбора параметра последовательно определите зарплаты сотрудников фирмы для различных значений фонда заработной платы: 100 000, 150 000, 200 000, 250 000, 300 000, 350 000, 400 000 р. Результаты подбора значений зарплат скопируйте в табл. 3.2 в виде специальной вставки.

Краткая справка. Для копирования результатов расчетов в виде значений необходимо выделить копируемые данные, произвести запись в буфер памяти (Правка/Копировать), установить курсор в первую ячейку таблицы ответов соответствующего столбца, задать режим специальной вставки (Правка/Специальная вставка), отметив в качестве объекта вставки - значения (Правка/Специальная вставка/вставитъ - Значения) (рис. 3.2).

Специальная вставка данных в виде значений позволяет копировать данные, полученные в результате расчетов, без дальнейшей зависимости их от пересчета формул.

Таблица 3.1

Выбор исходных данных

Должность	Вариант 1	Вариант 2	Вариант 3	Вариант 4	Вариант 5


менеджер
Менеджер
Зав. отделом
бухгалтер
Программист
Системный аналитик
Ген. директор

Таблица 3.2

Результаты подбора значений заработной платы

Фонд заработной платы, р.
Должность	Зарплата сотрудника	Зарплата сотрудника	Зарплата сотрудника	Зарплата сотрудника	Зарплата сотрудника	Зарплата сотрудника	Зарплата сотрудника

Младший менеджер
Менеджер
Зав. отделом
Главный бухгалтер
Программист
Системный аналитик
Ген. директор

Рис. 3.2. Специальная вставка значений данных

КОНТРОЛЬНЫЕ ВОПРОСЫ

Что называется абсолютной адресаций?

Что называется относительной адресацией?

Рассчитайте заработную плату сотрудников при фонде зарплаты 600000?

1. Создайте таблицу расчета заработной платы по образцу Введите исходные данные - Табельный номер, ФИО и Оклад, % Премии = 27 %, % Удержания = 13 %.

Примечание. Выделите отдельные ячейки для значений % Премии (D4) и % Удержания (F4).

2. Произведите расчеты во всех столбцах таблицы.

При расчете Премии используется формула Премия = Оклад х % Премии , в ячейке D5 наберите формулу = $D$4 * С5 (ячейка D4 используется в виде абсолютной адресации – для применения параметров адресации нажмите клавишу ) и скопируйте автозаполнением.

Формула для расчета «Всего начислено» = Оклад + Премия.

При расчете Удержания используется формула = Всего начислено * % Удержания,

для этого в ячейке F5 наберите формулу = $F$4 * Е5 .

Формула для расчета столбца «К выдаче» = Всего начислено – Удержания.

3. Рассчитайте итоги по столбцам, а также максимальный, минимальный и средний доходы по данным колонки «К выдаче» (Формулы/Вставить функцию/категория - Статистические функции ).

4. Переименуйте ярлычок Листа 1, присвоив ему имя «Зарплата октябрь». Для этого дважды щелкните мышью по ярлычку и наберите новое имя. Можно воспользоваться командой Переименовать контекстного меню ярлычка, вызываемого правой кнопкой мыши.

5. Скопируйте содержимое листа «Зарплата октябрь» на новый лист (пр.клавиша мыши по листу/Переместить/Скопировать…или зажмите клавишу CTRL и перетащите лист правее). Не забудьте для копирования поставить галочку в окошке Создавать копию .

6. Присвойте скопированному листу название «Зарплата ноябрь». Исправьте название месяца в названии таблицы. Измените значение Премии на 32 %.

Убедитесь, что программа произвела пересчет формул.

7. Между колонками «Премия» и «Всего начислено» вставьте новую колонку «Доплата» и рассчитайте значение доплаты по формуле = Оклад х % Доплаты . Значение доплаты примите равным 5 %.

8. Измените формулу для расчета значений колонки «Всего начислено» = Оклад + Премия + Доплата.

9. Поставьте к ячейке D3 комментарии «Премия пропорциональна окладу» (Рецензирование/Создать примечание), при этом в правом верхнем углу ячейки появится красная точка, которая свидетельствует о наличии примечания. Конечный вид расчета заработной платы за ноябрь приведен на рисунке

10. Сохраните созданную электронную книгу под именем «Зарплата» в своей папке.

11. Построить круговую диаграмму начисленной суммы к выдаче всех сотрудников зa ноябрь месяц.

1. Откройте созданный в Занятии 1 файл «Зарплата».

2. Скопируйте содержимое листа «Зарплата ноябрь» на новый лист электронной книга. Не забудьте для копирования поставить галочку в окошке Создавать копию .

3. Присвойте скопированному листу название «Зарплата декабрь». Исправьте название месяца в ведомости на декабрь.

4. Измените значение Премии на 46%, Доплаты - на 8 %. Убедитесь, что программа произвела пересчет формул.

5. По данным таблицы «Зарплата декабрь» постройте гистограмму доходов сотрудников. В качестве подписей оси X выберите фамилии сотрудников. Проведите, форматирование диаграммы. Конечный вид гистограммы приведен на рисунке.

6. Перед расчетом итоговых данных за квартал проведите сортировку по фамилиям в алфавитном порядке (по возрастанию) в ведомостях начисления зарплаты за октябрь-декабрь.

7. Скопируйте содержимое листа «Зарплата октябрь» на новый лист. Не забудьте для копирования поставить галочку в окошке Создавать копию .

8. Присвойте скопированному листу название «Итоги за квартал». Измените название таблицы на «Ведомость начисления заработной платы за 4 квартал».

9. Отредактируйте лист «Итоги за квартал». Для этого удалите в основной таблицы колонки Оклада и Премии, а также строку 4 с численными значениями % Премии и % Удержания и строку 19 «Всего». Удалите также строки с расчетом максимального, минимального и среднего доходов под основной таблицей. Вставьте пустую третью строку.

10. Вставьте новый столбец «Подразделение» (Главная/Ячейки/Вставить столбец на лист) между столбцами «Фамилия» и «Всего начислено». Заполните столбец «Подразделение» данными по образцу

11. Произведите расчет квартальных начислений, удержаний и суммы к выдаче как сумму начислений за каждый месяц (данные по месяцам располагаются на разных листах электронной книги, поэтому к адресу ячейки добавится адрес листа).

В ячейке D5 для расчета квартальных начислений «Всего начислено» формула имеет вид:

= "Зарплата декабрь"!Р5 + "Зарплата ноябрь"!Р5 +

+ "Зарплата октябрь"!Е5.

Аналогично произведите квартальный расчет «Удержания» и «К выдаче».

Для расчета квартального начисления заработной платы для всех сотрудников скопируйте формулы в столбцах D , Е и F . Ваша электронная таблица примет вид, как на рисунке.

12. Для расчета промежуточных итогов проведите сортировку по подразделениям, а внутри подразделений - по фамилиям.

13. Подведите промежуточные итоги по подразделениям, используя формулу суммирования. Для этого выделите всю таблицу и выполните команду Данные/Промежуточные итоги. Задайте параметры подсчета промежуточных итогов:

при каждом изменении в - Подразделение ;

операция - Сумма ;

добавить итоги по : Всего начислено , Удержания , К выдаче .

Отметьте галочкой операции «Заменить текущие итоги» и «Итоги под данными».

Примерный вид итоговой таблицы представлен на рисунке.

14. Изучите полученную структуру и формулы подведения промежуточных итогов, устанавливая курсор на разные ячейки таблицы. Научитесь сворачивать и разворачивать структуру до разных уровней (кнопками «+» и «-»).

16. Сохраните файл «Зарплата» с произведенными изменениями.

Структура контрольной работы

Контрольная работа состоит из двух частей: теоретическойи практической.

Контрольное задание должно быть выполнено на компьютере в приложениях MicrosoftOffice.

Первый теоретический вопрос должен быть выполнен в программе MicrosoftOfficePowerPoint. Минимальное количество слайдов – 8 шт. В слайдах должны быть: маркированный или нумерованный список, таблица, диаграмма, вставлены рисунки и настроена анимация.Обязательно должна быть гиперссылка на текстовый файл по своей теме (или на контрольную работу) и гиперссылка для возврата в презентацию из текстового файла. Распечатайте слайды, используя режим «Образец выдач», и оформите как Приложение

Второй теоретический вопрос выполняется в Microsoft Office Word объемом 8 – 10 листов на листах бумаги формата А4. Текст должен быть напечатан шрифтом Times New Roman размером 12 — 14 пт; параметры страницы – поля: верхнее и нижнее 2, левое и правое 2; интервал между строками 1,5.

В конце работы приводится список использованной литературы, ставится подпись и датавыполнения.

Выполненное в полном объеме контрольное задание высылается на рецензирование. Если контрольное задание не зачтено, то студент дорабатывает его в соответствии с замечаниями. При этом должен быть полностью сохранен исходный текст, рецензия и замечания преподавателя на полях.

Структура контрольной работы:

1.Титульный лист, с вашим фотоснимком (Приложение А)

3.Контрольная работа

4.Приложения

5.Список литературы (не менее 5 наименований)

6.Носитель информации (диск) с файлами презентации, электронной таблицы. Диск подписать

Контрольная работа кроме печатного варианта должна быть представлена в электронном виде на компакт диске. Диск подписать.

Контрольная работа высылается в адрес Академии по почте для регистрации на кафедру Информатики за месяц до сессии

Перед началом сессии ОБЯЗАТЕЛЬНО узнать зачтена или нет контрольная работа

Практическая часть.

Практическое задание выполняется с помощью программного приложения MS Excel.

1.Составьте таблицу «Ведомость начисления заработной платы» (образец на стр.3), введя произвольные исходные данные (10 строк), последняя строка с вашей собственной фамилией и окладом.

2.В ячейку вне таблицы (лучше внизу) введите количество рабочих дней в месяце, по которому осуществляется расчет заработной платы.

3.Введите расчетные формулы в столбцы первой строки,.

a. «Начислено, за отработанное время» — «Оклад * Отработано, дней / количество рабочих дней в месяце».
В формуле графы «Начислено» используйте абсолютную адресацию на ячейку с количеством рабочих дней в месяце

b.Премия- 20% от«Начислено, за отработанное время»

c.Уральский коэффициент -15% от («Начислено, за отработанное время» + Премия)

d.Итого начислено –этосумма (Начислено, за отработанное время»,Премия и Уральский коэффициент)

e.Подоходный налог – 13% от «Итого начислено»

f.Профсоюз — 1% «Итого начислено»

g.Аванс не более 50% от суммы «Итого начислено»

4.Скопируйте формулы во все строки таблицы.

5.Определите суммы по столбцам таблицы (кроме столбца Отработано, дней), используя функцию суммирования и указав диапазон ячеек.

6.Вычислите среднюю заработную плату по столбцу «Итого начислено»

7.Вычислите максимальный оклад

8.Отформатируйте шапку таблицы и числовые данные. Оформите границы таблицы.

9.Отсортируйтеданные таблицы по столбцу ФИО

10.Постройте две диаграммы: одна (гистограмма или график) — по данным столбцов ФИО и К выдаче на ОТДЕЛЬНОМ листе,
вторая (круговая)- по данным столбцов ФИО и Премия.
Заголовок, название осей и подписи данных – обязательно.

11.Скопируйте таблицу на второй лист и выполните настройку программы так, чтобы вместо значений в ячейках были расчетные формулы

12.Переименуйтелисты наТаблицаи Формулы

13.Сделайте предварительный просмотр созданной таблицы.

14.Распечатайте дветаблицы: одна со значениями, а вторая в режиме отображения формул.

15.Распечатайте диаграмму.

Ведомость начисления заработной платы

№ п /п

Ф.И.О.

Оклад, руб.

Отработано, дней

Начислено, за отработанное время, руб.

Премия, руб