Заполнение таблиц исходными данными
29. Заполните таблицу «Лицевой счет» на основании данных, приведенных в табл. 1 (см. стр. 8)
30. Заполните таблицу «Справочник работников» на основании данных, приведенных в табл. 2 (см. стр. 9)
31. Заполните числами колонку B в таблице «Разрядная сетка», используя функцию автозаполнения. Для этого:
· введите «1» в ячейку B13;
· установите указатель в ячейку B13 на маркер в правом нижнем углу. Указатель мыши примет форму креста;
· удерживая клавишу Ctrl и левую клавишу мыши, протащите указатель по диапазону B14:B30. Диапазон ячеек B14:B30 заполнился числами от 1 до 18.
32. Заполните колонку С в таблице «Разрядная сетка». Для этого:
· введите «10000» в ячейку C13.
· выделите диапазон ячеек С13:С30;
· выполните команду Гланая/Редактирование/Заполнить /Прогрессия;
· в окне Прогрессия выберите Расположение – по столбцам, Тип – арифметическая, в поле ввода Шаг введите 500;
· нажмите OK.
33. Заполните таблицу «Справочник по исполнительным листам» на основании данных, приведенных в табл. 6 (см. стр. 10).
34. Заполните колонку А в формах табл. 3, 4, скопировав в нее табельные номера из таблицы «Лицевой счет». Для этого выделите диапазон ячеек А3:А9, нажмите правую кнопку мыши, выберите в меню команду Копировать. Установите указатель в ячейку А34, нажмите правую клавишу мыши, выберите в меню команду Вставить. Затем установите указатель в ячейку А44, нажмите правую клавишу мыши и выберите в меню команду Вставить.
35. Введите дополнительную информацию:
Текущая клетка | Вводимые данные |
E20 | Размер мин. з/п |
F21 | |
E22 | Кол. раб. дней в месяце |
F23 | |
E24 | % премии |
F24 | Меньше 5 лет |
G24 | От 5 до 10 лет |
H24 | Больше 10 лет |
F25 | |
G25 | |
H25 | |
E26 | Размер прожит. минимума |
F27 |
Ввод в таблицу формул
36. Установите курсор в клетку B3 и введите формулу заполнения фамилии на основании данных «Справочника работников» (ссылки на ячейки и диапазоны ячеек вводите, выделяя ячейки мышью, для ввода знаков $ нажимайте F4после ввода каждого диапазона или ссылки, по окончании ввода формулы нажмите Enter):
=ВПР(А3;$L$3:$P$9;2;ложь)
Знак $ фиксирует координаты ячеек и диапазонов (при копировании формул они не изменяются).
В случае возникновения ошибки определите источник возникновения ошибки. Для этого установите указатель в ячейку с формулой и нажмите на вкладкеФормулыв группеЗависимости формулкнопку группыПроверка наличия ошибок и выберите нужный пункт.
37. Скопируйте формулу определения фамилии в диапазон ячеек B4:B9. Для этого выделите ячейку, содержащую копируемую формулу, а затем перетащите маркер заполнения по диапазону, который нужно заполнить.
Примечание. Маркер заполнения – это небольшой черный квадрат в правом нижнем углу выделенной ячейки или диапазона . При наведении на маркер заполнения указатель принимает вид черного креста.
38. Аналогично заполните диапазоны ячеек D3:D9 и E3:E9 (столбцы «Должность» и «Отдел») на основании данных «Справочника работников»)
39. Установите курсор в клетку В34 и наберите на клавиатуре формулу расчета начислений по окладу (текст формулы вводите без переноса в одну строку):
=ВПР(ВПР(A34;$А$3:$J$9;3;ложь);$В$13:$С$30;2; ложь)*
ВПР(A34;$А$3:$J$9;7;ложь) /$F$23
40. Скопируйте формулу начисления ЗП по окладу в диапазон В35:В40.
41. В ячейку С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.
42. В ячейку D34 введите формулу расчета начисленной ЗП:
=B34+C34
Скопируйте формулу в ячейки D35:D40.
43. В ячейку H3 самостоятельно введите формулу для нахождения начисленной ЗП из таблицы «Ведомость начислений». Скопируйте формулу в ячейки H4:H9.
44. В ячейку B44 введите формулу расчета подоходного налога:
=(ВПР(A44;$A$3:$J$9;8;ЛОЖЬ)-$F$21*ВПР(A44;$A$3:$J$9;6; ЛОЖЬ))*0,12
Скопируйте формулу в ячейки B45:B50.
45. В ячейку С44 введите формулу расчета пенсионного налога:
=ВПР(А44;$A$3:$J$9;8;ложь)*0,01
Скопируйте формулу в ячейки С45:С50
46. В ячейку D44 введите формулу расчета удержания по исполнительным листам:
=ЕСЛИ(ЕНД(ВПР(А44;$Е$13:$F$15;2;ложь));0;(ВПР(A44;
$A$3:$J$9;8;ложь)-B44)*ВПР(А44;$E$13:$F$15;2;ложь)/100)
Скопируйте формулу в ячейки D45:D50.
47. В ячейку Е44 введите формулу расчета общей суммы удержания: =B44+C44+D44
Скопируйте формулу в ячейки E45:E50.
48. В ячейку I3 самостоятельно введите формулу для нахождения общей суммы удержания из таблицы «Ведомость удержаний». Скопируйте формулу в ячейки I4:I9.
49. Вычислите сумму к выдаче с помощью формулы массива {=H3:H9 - I3:I9}. Для этого выделите блок ячеек J3:J9, нажмите клавишу «=», выделите блок H3:H9, нажмите клавишу «-», выделите блок I3:I9, нажмите клавиши Ctrl +Shift+ Enter.
50. Используя автосуммирование, рассчитайте итоги в табл.1. Для этого в ячейку А10 введите текст «Итого:», установите указатель в ячейку H10 и выполните командуФормулы/Биб-лиотека функций/Автосумма. Если выбранный командой блок окажется верным – H3:H9, нажмите Enter. В противном случае выделите блок H3:H9 и нажмите Enter.
Повторите указанные действия для ячеек I10, J10.
Получение итоговых данных
51. Рассчитайте сумму начисленной заработной платы по отделу 1. Для этого в ячейку I12 введите «Итого по отделу 1». В ячейку J12 введите формулу:
=СУММЕСЛИ(Е3:E9;1;J3:J9)
52. Самостоятельно введите в ячейку J13 формулу для расчета суммарной начисленной заработной платы по отделу 2.
53. Рассчитайте количество работников отдела 1. Для этого в ячейку I14 введите «Работает в 1 отделе». В ячейку J14 введите формулу:
=СЧЕТЕСЛИ(Е3:E9;1)
54. Самостоятельно введите в ячейку J15 формулу для расчета количества работников отдела 2.
55. Аналогично рассчитайте суммарную начисленную заработную плату и количество работников по каждой должности.