Архитектура Аудит Военная наука Иностранные языки Медицина Металлургия Метрология Образование Политология Производство Психология Стандартизация Технологии |
Заполнение таблиц исходными данными
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. Введите дополнительную информацию:
Ввод в таблицу формул 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. Аналогично рассчитайте суммарную начисленную заработную плату и количество работников по каждой должности.
Стилевое оформление таблиц
56. Отформатируйте колонки с результатами вычислений по формулам. Для этого выделите диапазон B36: D40 и на вкладке Главная в группе Число нажимайте кнопку пиктографического меню Уменьшить разрядность до тех пор, пока в форматируемых областях не окажутся целые значения. Повторите указанные действия для диапазонов С36: С40; D36: D40. Аналогично уменьшите разрядность в соответствующих столбцах таблиц «Ведомость удержаний» и «Лицевой счет». 57. Выполните выравнивание. Для этого выделите диапазон A2: J9 и на вкладке Главная в группе Выравнивание нажмите кнопку пиктографического меню По центру. Повторите указанные действия для диапазонов H10: J10, E12: F15; A34: D40; A44: E50. Выделите диапазон B33: D33 и на вкладке Главная в группе Выравнивание нажмите кнопку пиктографического меню Выровнять по середине. Повторите указанные действия для диапазонов, B43: E43; B2: J2. и L2: P2 58. Выберите для заголовков шрифт, отличный от установленного, и измените его начертание. Для этого: · выделите диапазоны несмежных ячеек (выделить первый диапазон и затем, удерживая клавишу Ctrl, выделите остальные) – A1, L1, B11, E11, A32, A42; · выберите из контекстного меню команду Формат ячеек; · на вкладке Шрифт выберите из списка Шрифт – Arial Cyr, Начертание – курсив, Размер – 14, из списка Цвет – синий; · нажмите OK. 59. Измените цвет символов шрифта в справочных таблицах с синего на зеленый, используя кнопку пиктографического меню Цвет текста на вкладке Главная в группе Шрифт. 60. Выполните оформление таблиц цветом и узором. Для этого: · выделите диапазон несмежных ячеек A2: J2, A33: D33, A43: E43; · выберите из контекстного меню команду Формат ячеек; · на вкладке Шрифт выберите цвет «синий», а на вкладке Заливка узор для заполнения «тонкий, перевернутый, диагональный, штриховой»; · нажмите OK. Для диапазонов А3: А9, А34: А40, А44: А50 установите цвет заливки «зеленый» и узор для заполнения «25% серый». Для установки цвета можно воспользоваться кнопкой пиктографического меню Цвет заливки на вкладке Главная в группе Шрифт. Самостоятельно установите любой цвет заливки и выберите узор для диапазонов B12: C12, Е12: F12. Если выбранное оформление не понравилось, отмените его. Для этого на вкладке Главная в группе Шрифт нажмите кнопку пиктографического меню Цвет заливки и выберите «нет заливки».
Популярное:
|
Последнее изменение этой страницы: 2016-05-03; Просмотров: 704; Нарушение авторского права страницы