Если у вас возникли сложности с курсовой, контрольной, дипломной, рефератом, отчетом по практике, научно-исследовательской и любой другой работой - мы готовы помочь.
Предоплата всего
Подписываем
ЗАДАНИЕ 1.
Перед вами стоит задача рассчитать заработную плату работников организации. Форма оплаты – оклад. Расчет необходимо оформить в виде таблиц. При расчете следует использовать данные таблицы 2
Оклад работника зависит от его квалификации (разряда). Эта зависимость должна быть представлена в виде соответствующей таблицы.
Размер удержания по исполнительным листам работника зависит от процента удержания. Сведения о работниках, с которых необходимо удерживать по исполнительным листам, и размере процента удержания должны быть представлены в виде соответствующей таблицы.
Выполнение.
1. Формирование таблиц.
1. Введите заголовок таблицы. Для этого установите указатель в ячейку A1. Введите текст: «Лицевой счет». Затем выделите диапазон ячеек A1:J1, выберите Объединить и поместить в центре.
2. Установите для диапазона ячеек шапки таблицы режим переноса текста при достижении правого края и выровняйте его по центру. Для этого:
3. Выполните команды Главная/Выравнивание/Выровнять по середине и Главная/Выравнивание/По центру.
4. Введите текст шапки таблицы в соответствии со следующим правилом:
|
Текущая клетка |
Набираемый текст |
|
A2 |
Таб. номер |
|
B2 |
Фамилия |
|
C2 |
Разряд |
|
D2 |
Должность |
|
E2 |
Отдел |
|
F2 |
Кол-во льгот |
|
G2 |
Факт. время (дн.) |
|
H2 |
Начислено з/п |
|
I2 |
Удержано |
|
J2 |
З/п к выдаче |
5. Разлинуйте таблицу (диапазон ячеек A2:J9).
6. Введите заголовок следующей таблицы. Установите указатель в ячейку B11. Введите текст: «Разрядная сетка».
7. Заполните шапку таблицы в соответствии с приведенными ниже рекомендациями:
|
Текущая клетка |
Набираемый текст |
|
B12 |
Разряд |
|
C12 |
Оклад |
8. Разлинуйте таблицу (диапазон ячеек В12:C30).
9. Введите заголовок следующей таблицы. Установите указатель в ячейку Е11. Введите текст: «Справочник по исполн. листам», нажмите Enter.
10. Заполните шапку таблицы в соответствии с приведенными ниже рекомендациями:
|
Текущая клетка |
Набираемый текст |
|
Е12 |
Таб. номер |
|
F12 |
% удерж. |
11. Разлинуйте таблицу (диапазон ячеек E12:F15).
12. Введите заголовок следующей таблицы. Для этого установите указатель в ячейку L1. Введите текст «Справочник работников», нажмите Enter. Затем выделите диапазон ячеек L1:P1 и выполните команду Объединить и поместить в центре.
13. Установите для диапазона ячеек шапки таблицы режим переноса текста при достижении правого края для диапазона L2:P2.
14. Заполните таблицу в соответствии с приведенными ниже рекомендациями:
|
Текущая клетка |
Набираемый текст |
|
L2 |
Табельный номер |
|
M2 |
Фамилия |
|
N2 |
Должность |
|
O2 |
Отдел |
|
P2 |
Дата поступления на работу |
15. Разлинуйте таблицу (диапазон ячеек L2:P9).
16. Введите заголовок следующей таблицы. Для этого установите указатель в ячейку A32. Введите текст «Ведомость начислений», нажмите Enter.
17. Установите для диапазона ячеек шапки таблицы режим переноса текста при достижении правого края для диапазона А33:D33.
18. Установите ширину столбца А равную 10. Для этого:
19. В ячейку А33 введите границу, разделяющую ячейку по диагонали (слева вниз направо).
20. Заполните шапку таблицы в соответствии с приведенными ниже рекомендациями:
|
Текущая клетка |
Набираемый текст |
|
А33 |
Начисл. таб., пробелы, номер |
|
В33 |
По окладу |
|
С33 |
Премия |
|
D33 |
Всего |
21. Разлинуйте таблицу (диапазон ячеек А33:D40).
22. Введите заголовок следующей таблицы. Для этого установите указатель в ячейку A42. Введите текст «Ведомость удержаний», нажмите Enter.
23. Установите для диапазона ячеек шапки табл. 4 режим переноса текста при достижении правого края для диапазона А43:Е43.
24. В ячейку А43 введите границу, разделяющую ячейку по диагонали.
25. Заполните шапку таблицы в соответствии с приведенными ниже рекомендациями:
|
Текущая клетка |
Набираемый текст |
|
А43 |
Удерж. таб., пробелы, номер |
|
В43 |
Подоходный налог |
|
С43 |
Пенсионный налог |
|
D43 |
Исполнительные листы |
|
E43 |
Всего |
26. Разлинуйте таблицу (диапазон ячеек А43:Е50).
2. Заполнение таблиц исходными данными
1. Заполните таблицу «Лицевой счет» на основании данных, приведенных в табл. 1
Таблица 1
Лицевой счет |
|||||||||
Таб. номер |
Фамилия |
Разряд |
Долж-ность |
Отдел |
Кол- во льгот |
Факт. время (дн.) |
Начис- лено з/п |
Удер- жано |
З/п к вы-даче |
|
1001 |
13 |
1 |
23 |
||||||
|
1002 |
17 |
3 |
23 |
||||||
|
1003 |
11 |
2 |
17 |
||||||
|
1004 |
5 |
0 |
8 |
||||||
|
1005 |
12 |
3 |
22 |
||||||
|
1006 |
7 |
2 |
23 |
||||||
|
1007 |
3 |
1 |
20 |
2. Заполните таблицу «Справочник работников» на основании данных, приведенных в табл. 2
Таблица 2
|
Справочник работников |
||||
Таб. номер |
Фамилия |
Должность |
Отдел |
Дата поступления на работу |
|
1001 |
Алексеева |
Нач. отдела |
1 |
15.04.2005 |
|
1002 |
Иванов |
Ст. инженер |
2 |
01.12.1999 |
|
1003 |
Петров |
Инженер |
2 |
12.01.2001 |
|
1004 |
Сидоров |
Экономист |
1 |
22.06.2010 |
|
1005 |
Кукушкин |
Секретарь |
1 |
24.04.1987 |
|
1006 |
Павленко |
Экономист |
2 |
12.12.1980 |
|
1007 |
Давыдова |
Инженер |
1 |
17.08.2008 |
3. Заполните числами колонку B в таблице «Разрядная сетка», используя функцию автозаполнения. Для этого:
4. Заполните колонку С в таблице «Разрядная сетка». Для этого:
5. Заполните таблицу «Справочник по исполнительным листам» на основании данных, приведенных в табл. 3
Таблица 3
Справочникпо исп. листам |
||
|
Таб. номер |
% удерж. |
|
|
1002 |
25 |
|
|
1005 |
20 |
|
|
1007 |
5 |
6. Заполните колонку А в формах таблиц «Ведомость начислений» и «Ведомость удержаний», скопировав в нее табельные номера из таблицы «Лицевой счет».
7. Введите дополнительную информацию:
|
Текущая клетка |
Вводимые данные |
|
E20 |
Размер мин. з/п |
|
F21 |
6000 |
|
E22 |
Кол. раб. дней в месяце |
|
F23 |
23 |
|
E24 |
% премии |
|
F24 |
Меньше 5 лет |
|
G24 |
От 5 до 10 лет |
|
H24 |
Больше 10 лет |
|
F25 |
10 |
|
G25 |
15 |
|
H25 |
25 |
|
E26 |
Размер прожит. минимума |
|
F27 |
5000 |
3. Ввод в таблицу формул
1. Установите курсор в клетку B3 и введите формулу заполнения фамилии на основании данных «Справочника работников» (ссылки на ячейки и диапазоны ячеек вводите, выделяя ячейки мышью, для ввода знаков $ нажимайте F4 после ввода каждого диапазона или ссылки, по окончании ввода формулы нажмите Enter):
=ВПР(А3;$L$3:$P$9;2;ложь)
Знак $ фиксирует координаты ячеек и диапазонов (при копировании формул они не изменяются).
Функция ВПР ищет в первом столбце таблицы искомое значение, затем перемещается по найденной строке к соответствующей ячейке и возвращает ее значение.
ВПР(искомое_значение;табл_массив;номер_столбца;интервальный_просмотр)
Искомое_значение – это значение, которое должно быть найдено в первом столбце таблицы. Искомое_значение может быть значением, ссылкой или текстовой строкой.
Табл_массив – это таблица с информацией, в первом столбце которой ищется искомое значение.
Номер_столбца – это номер столбца в таблице, из которого должно быть взято соответствующее значение.
Интервальный_просмотр – это логическое значение, которое определяет, нужно ли искать точное или приближенное значение. Если этот аргумент имеет значение ИСТИНА или опущен и точное значение не найдено, то возвращается приблизительно соответствующее значение, а именно: наибольшее значение, которое меньше, чем искомое_значение. Если этот аргумент имеет значение ЛОЖЬ, то функция ВПР ищет точное значение. Если таковое не найдено, то возвращается значение ошибки #Н/Д.
2. Скопируйте формулу определения фамилии в диапазон ячеек B4:B9. Для этого выделите ячейку, содержащую копируемую формулу, а затем перетащите маркер заполнения по диапазону, который нужно заполнить. Аналогично заполните диапазоны ячеек D3:D9 и E3:E9 (столбцы «Должность» и «Отдел») на основании данных «Справочника работников»)
3. Рассчитайте зарплату по окладу по формуле: ЗП окл = ОКЛ * ФТ/Т;
где ОКЛ – оклад работника в соответствии с его разрядом;
ФT – фактически отработанное время в расчетном месяце (дн.);
Т – количество рабочих дней в месяце.
Установите курсор в клетку В34 и наберите на клавиатуре формулу расчета начислений по окладу (текст формулы вводите без переноса в одну строку):
=ВПР(ВПР(A34;$А$3:$J$9;3;ложь);$В$13:$С$30;2; ложь)*ВПР(A34;$А$3:$J$9;7;ложь) /$F$23
Расшифруйте формулу письменно.
4. Скопируйте формулу начисления ЗП по окладу в диапазон В35:В40.
5. В ячейку С34 введите формулу расчета премии. Размер премии зависит от выслуги лет, определяемой как разность между текущей датой и датой поступления на работу. Соответственно формула для расчета премии будет иметь следующий вид:
=ЕСЛИ((СЕГОДНЯ()-ВПР(A34;$L$3:$P$9;5;ЛОЖЬ))/365<5;
B34*$F$25/100;ЕСЛИ((СЕГОДНЯ()-ВПР(A34;$L$3:$P$9;5;
ЛОЖЬ))/365<10;B34*$G$25/100;B34*$H$25/100))
Расшифруйте формулу письменно.
Функция ЕСЛИ возвращает одно значение, если заданное условие при вычислении дает значение ИСТИНА, и другое значение, если ЛОЖЬ.
ЕСЛИ(логическое_выражение;значение_если_истина;значение_если_ложь)
Логическое_выражение – это любое выражение, которое при вычислении дает значение ИСТИНА или ЛОЖЬ.
Значение_если_истина – это значение, которое возвращается, если логическое_выражение имеет значение ИСТИНА. Если логическое_выражение имеет значение ИСТИНА и значение_если_истина опущено, то возвращается значение ИСТИНА. Значение_если_истина может быть другой формулой.
Значение_если_ложь – это значение, которое возвращается, если логическое_выражение имеет значение ЛОЖЬ. Если логическое_выражение имеет значение ЛОЖЬ и значение_если_ложь опущено, то возвращается значение ЛОЖЬ. Значение_если_ложь может быть другой формулой.
Скопируйте формулу в ячейки С35:С40.
6. В ячейку D34 введите формулу расчета начисленной ЗП:
=B34+C34
Скопируйте формулу в ячейки D35:D40.
7. В ячейку H3 самостоятельно введите формулу для нахождения начисленной ЗП из таблицы «Ведомость начислений». Скопируйте формулу в ячейки H4:H9.
44. Рассчитайте величину подоходного налога: У пн = (ЗП - МЗП * Л ) * 0,12; где МЗП – минимальная зарплата; Л – количество льгот.
В ячейку B44 введите формулу расчета подоходного налога:
=(ВПР(A44;$A$3:$J$9;8;ЛОЖЬ)-$F$21*ВПР(A44;$A$3:$J$9;6; ЛОЖЬ))*0,12
Расшифруйте формулу письменно.
Скопируйте формулу в ячейки B45:B50.
8. Рассчитайте величину пенсионного налога: У пф = ЗП * 0,01.
В ячейку С44 введите формулу расчета пенсионного налога:
=ВПР(А44;$A$3:$J$9;8;ложь)*0,01
Расшифруйте формулу письменно.
Скопируйте формулу в ячейки С45:С50
9. Рассчитайте величину удержания по исполнительным листам: Уил = (ЗП - Упн ) * %ИЛ; где %ИЛ – процент удержания по исполнительным листам.
В ячейку D44 введите формулу расчета удержания по исполнительным листам:
=ЕСЛИ(ЕНД(ВПР(А44;$Е$13:$F$15;2;ложь));0;(ВПР(A44;$A$3:$J$9;8;ложь)-B44)*ВПР(А44;$E$13:$F$15;2;ложь)/100)
Расшифруйте формулу письменно.
Функция ЕНД проверяет значение ячейки.
ЕНД(значение)
Если значение ячейки ошибка #Н/Д, то функция возвращает значение ИСТИНА, в противном случае – ЛОЖЬ.
Скопируйте формулу в ячейки D45:D50.
10. В ячейку Е44 введите формулу расчета общей суммы удержания: =B44+C44+D44
Скопируйте формулу в ячейки E45:E50.
11. В ячейку I3 самостоятельно введите формулу для нахождения общей суммы удержания из таблицы «Ведомость удержаний». Скопируйте формулу в ячейки I4:I9.
12. Вычислите сумму к выдаче с помощью формулы массива {=H3:H9 - I3:I9}. Для этого выделите блок ячеек J3:J9, нажмите клавишу «=», выделите блок H3:H9, нажмите клавишу «-», выделите блок I3:I9, нажмите клавиши Ctrl +Shift+ Enter.
13. Используя автосуммирование, рассчитайте итоги в табл.1. Для этого в ячейку А10 введите текст «Итого:», установите указатель в ячейку H10 и выполните команду Автосумма. Если выбранный командой блок окажется верным – H3:H9, нажмите Enter. В противном случае выделите блок H3:H9 и нажмите Enter.
Повторите указанные действия для ячеек I10, J10.
4.Получение итоговых данных
1. Рассчитайте сумму начисленной заработной платы по отделу 1. Для этого в ячейку I12 введите «Итого по отделу 1». В ячейку J12 введите формулу:
=СУММЕСЛИ(Е3:E9;1;J3:J9)
Функция СУММЕСЛИ суммирует ячейки, отвечающие заданному критерию.
СУММЕСЛИ(диапазон;условие;диапазон_суммирования)
Диапазон – определяет интервал вычисляемых ячеек.
Условие – задает критерий в форме числа, выражения, который определяет, какая ячейка будет суммироваться.
Диапазон_суммирования – фактические ячейки для суммирования. Суммируются те ячейки диапазона, которые удовлетворяют условию. Если диапазон суммирования отсутствует, то суммируются ячейки аргумента «диапазон».
2. Самостоятельно введите в ячейку J13 формулу для расчета суммарной начисленной заработной платы по отделу 2.
3. Рассчитайте количество работников отдела 1. Для этого в ячейку I14 введите «Работает в 1 отделе». В ячейку J14 введите формулу:
=СЧЕТЕСЛИ(Е3:E9;1)
Функция СЧЕТЕСЛИ подсчитывает количество непустых ячеек в диапазоне, удовлетворяющих заданному критерию.
СЧЕТЕСЛИ(диапазон;критерий)
Диапазон – определяет интервал, в котором подсчитывается количество ячеек.
Критерий – задает критерий в форме числа, выражения, который определяет, какие ячейки следует подсчитывать.
4. Самостоятельно введите в ячейку J15 формулу для расчета количества работников отдела 2.
5. Аналогично рассчитайте суммарную начисленную заработную плату и количество работников по каждой должности.
5. Использование условного форматирования
1. С помощью условного форматирования данных можно быстро определить и продемонстрировать важные тенденции и отклонения в данных.
Рассмотрим возможности условного форматирования с гистограммой, отображающей пропорциональное значение величин в диапазоне ячеек G3: G10:
Используем возможности условного форматирования для диапазона ячеек J3:J9. Если значение суммы З/П к выдаче меньше прожиточного минимума, то необходимо значение вывести красным цветом с двойным подчеркиванием, иначе значение выводить синим цветом.
Для этого необходимо:
2. Для диапазона ячеек G3:G9 установите следующие форматы: если работник проработал целый месяц, вывести значение зеленым цветом, если проработал меньше месяца – вывести значение оранжевым цветом с одинарным подчеркиванием.
3. Сохраните результаты лабораторной работы.
ЗАДАНИЕ 2.
С помощью финансовых функций Формулы – Вставить функцию – Финансовые создайте кредитный калькулятор.
Изучите синтаксис функций ПЛТ, КПЕР, СТАВКА, ПС и заполните свободные поля.