Создать рабочую таблицу расчет премии предприятия
мБВПТБФПТОБС ТБВПФБ № 1
уПЪДБОЙЕ НОПЗПУФТБОЙЮОПК ЬМЕЛФТПООПК ЛОЙЗЙ РТЙ ТБУЮЕФЕ ЪБТБВПФОПК РМБФЩ Ч Microsoft Excel
гЕМШ ЪБОСФЙС. рТЙНЕОЕОЙЕ ПФОПУЙФЕМШОПК Й БВУПМАФОПК БДТЕУБГЙЙ ДМС ЖЙОБОУПЧЩИ ТБУЮЕФПЧ. уПТФЙТПЧЛБ, ХУМПЧОПЕ ЖПТНБФЙТПЧБОЙЕ Й ЛПРЙТПЧБОЙЕ УПЪДБООЩИ ФБВМЙГ. тБВПФБ У МЙУФБНЙ ЬМЕЛФТПООПК ЛОЙЗЙ.
ъБДБОЙЕ. уПЪДБФШ ФБВМЙГЩ ЧЕДПНПУФЙ ОБЮЙУМЕОЙС ЪБТБВПФОПК РМБФЩ ЪБ ДЧБ НЕУСГБ ОБ ТБЪОЩИ МЙУФБИ ЬМЕЛФТПООПК ЛОЙЗЙ, РТПЙЪЧЕУФЙ ТБУЮЕФЩ, ЖПТНБФЙТПЧБОЙЕ Й УПТФЙТПЧЛХ ДБООЩИ. йУИПДОЩЕ ДБООЩЕ РТЕДУФБЧМЕОЩ Ч ФБВМЙГЕ 1.1.
фЕИОПМПЗЙС ЧЩРПМОЕОЙЕ ТБВПФЩ:
1. пФЛТПКФЕ ТЕДБЛФПТ ЬМЕЛФТПООЩИ ФБВМЙГ Microsoft Excel Й УПЪДБКФЕ ОПЧХА ЬМЕЛФТПООХА ЛОЙЗХ У ОБЪЧБОЙЕН «ъБТРМБФБ».
2. уПЪДБКФЕ ОБ «мЙУФЕ 1» ФБВМЙГХ ТБУЮЕФБ ЪБТБВПФОПК РМБФЩ РП ПВТБЪГХ (ФБВМЙГБ 1.1). чЩДЕМЙФЕ ПФДЕМШОЩЕ СЮЕКЛЙ ДМС ЪОБЮЕОЙК %рТЕНЙЙ (D4) Й %хДЕТЦБОЙС (F4). чЧЕДЙФЕ ЙУИПДОЩЕ ДБООЩЕ: фБВЕМШОЩК ОПНЕТ, жйп Й пЛМБД; %рТЕНЙЙ = 27%; %хДЕТЦБОЙС = 13%.
фБВМЙГБ 1.1 — йУИПДОЩЕ ДБООЩЕ ДМС ЪБДБОЙС
чЕДПНПУФШ ОБЮЙУМЕОЙС ЪБТБВПФОПК РМБФЩ ЪБ ПЛФСВТШ
| фБВ. ОПНЕТ | жБНЙМЙС й.п. | пЛМБД (ТХВ.) | рТЕНЙС (ТХВ.) | чУЕЗП ОБЮЙУМЕОП (ТХВ.) | хДЕТЦБОЙС (ТХВ.) | л ЧЩДБЮЕ (ТХВ.) |
| 27% | 13% | |||||
| 200 | рЕФТПЧБ й.м. | 4500 | ? | ? | ? | ? |
| 201 | йЧБОПЧБ й.з. | 4850 | ? | ? | ? | ? |
| 202 | уФЕРБОПЧБ б.ы. | 5200 | ? | ? | ? | ? |
| 203 | ыПТПИПЧ у.н. | 5550 | ? | ? | ? | ? |
| 204 | зБМЛЙО ч.ц. | 5900 | ? | ? | ? | ? |
| 205 | рПТФОПЧ н.ф. | 6250 | ? | ? | ? | ? |
| 206 | пТМПЧБ о.о. | 6600 | ? | ? | ? | ? |
| 207 | уФЕРБОЛЙОБ б.ч. | 6950 | ? | ? | ? | ? |
| 208 | цБТПЧБ з.б. | 7300 | ? | ? | ? | ? |
| 209 | уФПМШОЙЛПЧБ п.д. | 7650 | ? | ? | ? | ? |
| 210 | дТЩОЛЙОБ у.у. | 8000 | ? | ? | ? | ? |
| 211 | ыРБТП о.з. | 8350 | ? | ? | ? | ? |
| 212 | ыБЫЛЙО т.о. | 8700 | ? | ? | ? | ? |
| нБЛУЙНБМШОЩК ДПИПД | ? | |||||
| нЙОЙНБМШОЩК ДПИПД | ? | |||||
| уТЕДОЙК ДПИПД | ? | |||||
рТПЙЪЧЕДЙФЕ ТБУЮЕФЩ ЧП ЧУЕИ УФПМВГБИ ФБВМЙГЩ.
рТЙ ТБУЮЕФЕ «рТЕНЙЙ» ЙУРПМШЪХКФЕ ЖПТНХМХ рТЕНЙС = пЛМБД И %рТЕНЙЙ, Ч СЮЕКЛЕ D5 ОБВЕТЙФЕ ЖПТНХМХ =$D$4*C5 (СЮЕКЛБ D4 ЙУРПМШЪХЕФУС Ч ЧЙДЕ БВУПМАФОПК БДТЕУБГЙЙ). уЛПРЙТХКФЕ ОБВТБООХА ЖПТНХМХ ЧОЙЪ РП УФПМВГХ БЧФПЪБРПМОЕОЙЕН.
лТБФЛБС УРТБЧЛБ. дМС ХДПВУФЧБ ТБВПФЩ Й ЖПТНЙТПЧБОЙС ОБЧЩЛПЧ ТБВПФЩ У БВУПМАФОЩН ЧЙДПН БДТЕУБГЙЙ ТЕЛПНЕОДХЕФУС РТЙ ПЖПТНМЕОЙЙ ЛПОУФБОФ ПЛТБЫЙЧБФШ СЮЕКЛХ ГЧЕФПН, ПФМЙЮОЩН ПФ ГЧЕФБ ТБУЮЕФОПК ФБВМЙГЩ. фПЗДБ РТЙ ЧЧПДЕ ЖПТНХМ Ч ТБУЮЕФОХА СЮЕКЛХ ПЛТБЫЕООБС СЮЕКЛБ У ЛПОУФБОФПК ВХДЕФ ЧБН ОБРПНЙОБОЙЕН П ФПН, ЮФП УМЕДХЕФ ХУФБОПЧЙФШ БВУПМАФОХА БДТЕУБГЙА (ОБВПТПН У ЛМБЧЙБФХТЩ Ч БДТЕУЕ УЙНЧПМПЧ $ ЙМЙ ОБЦБФЙЕН ЛМБЧЙЫЙ [F4]).
жПТНХМБ ДМС ТБУЮЕФБ «чУЕЗП ОБЮЙУМЕОП»: чУЕЗП ОБЮЙУМЕОП = пЛМБД + рТЕНЙС.
рТЙ ТБУЮЕФЕ «хДЕТЦБОЙС» ЙУРПМШЪХКФЕ ЖПТНХМХ хДЕТЦБОЙС = чУЕЗП ОБЮЙУМЕОП И %хДЕТЦБОЙК, Ч СЮЕКЛЕ F5 ОБВЕТЙФЕ ЖПТНХМХ = $F$4 * е5.
жПТНХМБ ДМС ТБУЮЕФБ УФПМВГБ «л ЧЩДБЮЕ»: л ЧЩДБЮЕ = чУЕЗП ОБЮЙУМЕОП — хДЕТЦБОЙС.
3. тБУУЮЙФБКФЕ ЙФПЗЙ РП УФПМВГБН, Б ФБЛЦЕ НБЛУЙНБМШОЩК, НЙОЙНБМШОЩК Й УТЕДОЙК ДПИПДЩ РП ДБООЩН ЛПМПОЛЙ «л ЧЩДБЮЕ» (чУФБЧЛБ/жХОЛГЙС/ЛБФЕЗПТЙС уФБФЙУФЙЮЕУЛЙЕ).
4. рЕТЕЙНЕОХКФЕ СТМЩЮПЛ «мЙУФБ 1», РТЙУЧПЙЧ ЕНХ ЙНС «ъБТРМБФБ ПЛФСВТШ». дМС ЬФПЗП ДЧБЦДЩ ЭЕМЛОЙФЕ НЩЫША РП СТМЩЮЛХ Й ОБВЕТЙФЕ ОПЧПЕ ЙНС. нПЦОП ЧПУРПМШЪПЧБФШУС ЛПНБОДПК «рЕТЕЙНЕОПЧБФШ» ЛПОФЕЛУФОПЗП НЕОА СТМЩЮЛБ, ЧЩЪЩЧБЕНПЗП РТБЧПК ЛОПРЛПК НЩЫЙ. тЕЪХМШФБФЩ ТБВПФЩ РТЕДУФБЧМЕОЩ Ч ФБВМЙГЕ 1.2.
фБВМЙГБ 1.2 — йФПЗПЧЩК ЧЙД ФБВМЙГЩ ТБУЮЕФБ ЪБТБВПФОПК РМБФЩ ЪБ ПЛФСВТШ
чЕДПНПУФШ ОБЮЙУМЕОЙС ЪБТБВПФОПК РМБФЩ ЪБ ПЛФСВТШ
| фБВ. ОПНЕТ | жБНЙМЙС й.п. | пЛМБД (ТХВ.) | рТЕНЙС (ТХВ.) | чУЕЗП ОБЮЙУМЕОП (ТХВ.) | хДЕТЦБОЙС (ТХВ.) | л ЧЩДБЮЕ (ТХВ.) |
| 27% | 13% | |||||
| 200 | рЕФТПЧБ й.м. | 4500 | 1215,00 | 5715,00 | 742,95 | 4972,05 |
| 201 | йЧБОПЧБ й.з. | 4850 | 1309,50 | 6159,50 | 800,74 | 5358,77 |
| 202 | уФЕРБОПЧБ б.ы. | 5200 | 1404,00 | 6604,00 | 858,52 | 5745,48 |
| 203 | ыПТПИПЧ у.н. | 5550 | 1498,50 | 7048,50 | 916,31 | 6132,20 |
| 204 | зБМЛЙО ч.ц. | 5900 | 1593,00 | 7493,00 | 974,09 | 6518,91 |
| 205 | рПТФОПЧ н.ф. | 6250 | 1687,50 | 7937,50 | 1031,88 | 6905,63 |
| 206 | пТМПЧБ о.о. | 6600 | 1782,00 | 8382,00 | 1089,66 | 7292,34 |
| 207 | уФЕРБОЛЙОБ б.ч. | 6950 | 1876,50 | 8826,50 | 1147,45 | 7679,06 |
| 208 | цБТПЧБ з.б. | 7300 | 1971,00 | 9271,00 | 1205,23 | 8065,77 |
| 209 | уФПМШОЙЛПЧБ п.д. | 7650 | 2065,50 | 9715,50 | 1263,02 | 8452,49 |
| 210 | дТЩОЛЙОБ у.у. | 8000 | 2160,00 | 10160,00 | 1320,80 | 8839,20 |
| 211 | ыРБТП о.з. | 8350 | 2254,50 | 10604,50 | 1378,59 | 9225,92 |
| 212 | ыБЫЛЙО т.о. | 8700 | 2349,00 | 11049,00 | 1436,37 | 9612,63 |
| нБЛУЙНБМШОЩК ДПИПД | 9999,35 | |||||
| нЙОЙНБМШОЩК ДПИПД | 4972,05 | |||||
| уТЕДОЙК ДПИПД | 7485,7 | |||||
5. уЛПРЙТХКФЕ УПДЕТЦЙНПЕ МЙУФБ «ъБТРМБФБ ПЛФСВТШ» ОБ ОПЧЩК МЙУФ (рТБЧЛБ/рЕТЕНЕУФЙФШ/уЛПРЙТПЧБФШ МЙУФ). нПЦОП ЧПУРПМШЪПЧБФШУС ЛПНБОДПК «рЕТЕНЕУФЙФШ/уЛПРЙТПЧБФШ» ЛПОФЕЛУФОПЗП НЕОА СТМЩЮЛБ. оЕ ЪБВХДШФЕ ДМС ЛПРЙТПЧБОЙС РПУФБЧЙФШ ЗБМПЮЛХ Ч ПЛПЫЛЕ «уПЪДБЧБФШ ЛПРЙА» (ТЙУХОПЛ 1.3).
тЙУХОПЛ 1.3 — лПРЙТПЧБОЙЕ МЙУФБ ЬМЕЛФТПООПК ЛОЙЗЙ
лТБФЛБС УРТБЧЛБ. рЕТЕНЕЭБФШ Й ЛПРЙТПЧБФШ МЙУФЩ НПЦОП, РЕТЕФБУЛЙЧБС ЙИ СТМЩЛЙ (ДМС ЛПРЙТПЧБОЙС ХДЕТЦЙЧБКФЕ ОБЦБФПК ЛМБЧЙЫХ [Ctrl]).
рТЙУЧПКФЕ УЛПРЙТПЧБООПНХ МЙУФХ ОБЪЧБОЙЕ «ъБТРМБФБ ОПСВТШ». йУРТБЧШФЕ ОБЪЧБОЙЕ НЕУСГБ Ч ОБЪЧБОЙЙ ФБВМЙГЩ. йЪНЕОЙФЕ ЪОБЮЕОЙЕ «рТЕНЙЙ» ОБ 32%. хВЕДЙФЕУШ, ЮФП РТПЗТБННБ РТПЙЪЧЕМБ РЕТЕУЮЕФ ЖПТНХМ.
7. нЕЦДХ ЛПМПОЛБНЙ «рТЕНЙС» Й «чУЕЗП ОБЮЙУМЕОП» ЧУФБЧШФЕ ОПЧХА ЛПМПОЛХ — «дПРМБФБ» (ЧЩДЕМЙФЕ УФПМВЕГ е «чУЕЗП ОБЮЙУМЕОП» Й ЧЩРПМОЙФЕ ЛПНБОДХ чУФБЧЛБ/уФПМВГЩ); ТБУУЮЙФБКФЕ ЪОБЮЕОЙЕ ДПРМБФЩ РП ЖПТНХМЕ дПРМБФБ = пЛМБД И %дПРМБФЩ. ъОБЮЕОЙЕ ДПРМБФЩ РТЙНЙФЕ ТБЧОЩН 5%.
8. йЪНЕОЙФЕ ЖПТНХМХ ДМС ТБУЮЕФБ ЪОБЮЕОЙК ЛПМПОЛЙ «чУЕЗП ОБЮЙУМЕОП»: чУЕЗП ОБЮЙУМЕОП = пЛМБД + рТЕНЙС + дПРМБФБ. уЛПРЙТХКФЕ ЖПТНХМХ ЧОЙЪ РП УФПМВГХ.
9. рТПЧЕДЙФЕ ХУМПЧОПЕ ЖПТНБФЙТПЧБОЙЕ ЪОБЮЕОЙК ЛПМПОЛЙ «л ЧЩДБЮЕ». хУФБОПЧЙФЕ ЖПТНБФ ЧЩЧПДБ ЪОБЮЕОЙК НЕЦДХ 7000 Й 10 000 — ЪЕМЕОЩН ГЧЕФПН ЫТЙЖФБ, НЕОШЫЕ ЙМЙ ТБЧОП 7000 — ЛТБУОЩН ГЧЕФПН ЫТЙЖФБ, ВПМШЫЕ ЙМЙ ТБЧОП 10 000 — УЙОЙН ГЧЕФПН ЫТЙЖФБ (жПТНБФ/хУМПЧОПЕ ЖПТНБФЙТПЧБОЙЕ).
10. рТПЧЕДЙФЕ УПТФЙТПЧЛХ РП ЖБНЙМЙСН Ч БМЖБЧЙФОПН РПТСДЛЕ РП ЧПЪТБУФБОЙА (ЧЩДЕМЙФЕ ЖТБЗНЕОФ ФБВМЙГЩ У 5-К РП 18-А УФТПЛЙ ВЕЪ УФТПЛЙ «чУЕЗП», ЧЩВЕТЙФЕ НЕОА «дБООЩЕ/уПТФЙТПЧЛБ», УПТФЙТПЧБФШ РП — уФПМВЕГ ч).
11. рПУФБЧШФЕ Л СЮЕКЛЕ D3 ЛПННЕОФБТЙЙ «рТЕНЙС РТПРПТГЙПОБМШОБ ПЛМБДХ» (чУФБЧЛБ/рТЙНЕЮБОЙЕ), РТЙ ЬФПН Ч РТБЧПН ЧЕТИОЕН ХЗМХ СЮЕКЛЙ РПСЧЙФУС ЛТБУОБС ФПЮЛБ, ЛПФПТБС УЧЙДЕФЕМШУФЧХЕФ П ОБМЙЮЙЙ РТЙНЕЮБОЙС.
12. ъБЭЙФЙФЕ МЙУФ «ъБТРМБФБ ОПСВТШ» ПФ ЙЪНЕОЕОЙК (уЕТЧЙУ/ъБЭЙФБ/ъБЭЙФЙФШ МЙУФ). ъБДБКФЕ РБТПМШ ОБ МЙУФ, УПЪДБКФЕ РПДФЧЕТЦДЕОЙЕ РБТПМС. хВЕДЙФЕУШ, ЮФП МЙУФ ЪБЭЙЭЕО Й ОЕЧПЪНПЦОП ХДБМЕОЙЕ ДБООЩИ. уОЙНЙФЕ ЪБЭЙФХ МЙУФБ (уЕТЧЙУ/ъБЭЙФБ/уОСФШ ЪБЭЙФХ МЙУФБ).
Источник
Задание 1. 1. Создайте таблицу расчета заработной платы по образцу Введите исходные данные — Табельный номер, ФИО и Оклад
1. Создайте таблицу расчета заработной платы по образцу Введите исходные данные — Табельный номер, ФИО и Оклад, % Премии = 27 %, % Удержания = 13 %.
Примечание. Выделите отдельные ячейки для значений % Премии (D4) и % Удержания (F4).
2. Произведите расчеты во всех столбцах таблицы.
При расчете Премии используется формула Премия = Оклад х % Премии, в ячейке D5 наберите формулу = $D$4 * С5 (ячейка D4 используется в виде абсолютной адресации – для применения параметров адресации нажмите клавишу [F4]) и скопируйте автозаполнением.
Формула для расчета «Всего начислено» = Оклад + Премия.
При расчете Удержания используется формула = Всего начислено * % Удержания,
для этого в ячейке F5 наберите формулу = $F$4 * Е5.
Формула для расчета столбца «К выдаче» = Всего начислено – Удержания.
3. Рассчитайте итоги по столбцам, а также максимальный, минимальный и средний доходы по данным колонки «К выдаче» (Формулы/Вставить функцию/категория — Статистические функции).
4. Переименуйте ярлычок Листа 1, присвоив ему имя «Зарплата октябрь». Для этого дважды щелкните мышью по ярлычку и наберите новое имя. Можно воспользоваться командой Переименовать контекстного меню ярлычка, вызываемого правой кнопкой мыши.
5. Скопируйте содержимое листа «Зарплата октябрь» на новый лист (пр.клавиша мыши по листу/Переместить/Скопировать…или зажмите клавишу CTRL и перетащите лист правее). Не забудьте для копирования поставить галочку в окошке Создавать копию.
6. Присвойте скопированному листу название «Зарплата ноябрь». Исправьте название месяца в названии таблицы. Измените значение Премии на 32 %.
Убедитесь, что программа произвела пересчет формул.
7. Между колонками «Премия» и «Всего начислено» вставьте новую колонку «Доплата» и рассчитайте значение доплаты по формуле = Оклад х % Доплаты. Значение доплаты примите равным 5 %.
8. Измените формулу для расчета значений колонки «Всего начислено» = Оклад + Премия + Доплата.
9. Поставьте к ячейке D3 комментарии «Премия пропорциональна окладу» (Рецензирование/Создать примечание), при этом в правом верхнем углу ячейки появится красная точка, которая свидетельствует о наличии примечания. Конечный вид расчета заработной платы за ноябрь приведен на рисунке
10. Сохраните созданную электронную книгу под именем «Зарплата» в своей папке.
11. Построить круговую диаграмму начисленной суммы к выдаче всех сотрудников зa ноябрь месяц.
Задание 2.
Рассчитать зарплату за декабрь и построить диаграмму. Создать итоговую таблицу ведомости квартального начисления заработной платы, провести расчет промежуточных итогов по подразделениям.
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. Сохраните файл «Зарплата» с произведенными изменениями.
Источник