Технология заполнения таблиц исходными данными

 

22. На листе «Таблица с расчетными данными» заполните таблицу 1 «Лицевой счет» на основании исходных данных, приведенных в таблице 1 (см. Таблица 1«Лицевой счет»).

23. На листе «Таблица с расчетными данными» заполните таблицу «Справочник разрядов и окладов» на основании данных, приведенных в таблице 2 (см. Таблица 2«Справочник разрядов и окладов»).

24. Заполните числами колонку «B14» и «С14» в таблице «Справочник разрядов и окладов», используя функцию автозаполнения. Для этого введите число «1» в ячейку «B14». Затем установите указатель в ячейку «B14» на маркер в правом нижнем углу. Указатель мыши примет форму креста. Удерживая клавишу «Ctrl» и левую клавишу мыши, протащите указатель по диапазону «B14:B32». Диапазон ячеек «B14:B32» заполнился числами от «1 до 19».

25. Заполните колонку «С14» в таблице «Справочник разрядов и окладов». Для этого введите «800» в ячейку «C14». Установите указатель в ячейку «C14». Затем выберите вкладку «Главная/Редактирование/Заполнить/Прогрессия».

В появившемся окне «Прогрессия» выберите расположение «По столбцам», тип «Арифметическая», введите в поле ввода «Шаг» 400, в поле ввода «Предельное значение» 7600 и щелкните по кнопке «OK».

26. На листе «Таблица с расчетными данными» введите дополнительную информацию. Для этого введите заголовок таблицы 5 «Дополнительная информация». Далее установите указатель в ячейку «L1» и введите текст: «Дополнительная информация». Затем выделите диапазон ячеек «L1:Р1». В наборе команд«Выравнивание» нажмите кнопку пиктографического меню «Объединить и поместить в центре». По окончании нажмите «Enter».

27. Введите текст шапки таблицы 5. «Дополнительная информация», в соответствии с приведенными ниже рекомендациями (см. Таблица 5 «Дополнительная информация»).

Текущая клетка Вводимые данные

 

L2 Размер мин. з/п

L3 200

M2 Кол-во раб. дней в месяце

М3 23

Ввод в таблицу функций

28. Ввод функции и формул начинается со знака равенства «=».

29.Для корректного ввода функций в таблицу используется технология «Мастера функций».

30. На рабочем листе «Таблица с расчетными данными» таблицы 1 «Лицевой счет» установите курсор в клетку «F3» и введите с помощью «Мастера функций» функцию «ВПР» для заполнения «Количества льгот» используя данные таблицы 3 «Справочник работников» листа «Справочник работающих в организации» (Рис.6 - 7).

= ВПР(A3; ’Справочник работающих в организации’! $A$3:$D$9; 2;ложь)

Рис. 6 Технология заполнения диапазона ячеек «Количество льгот» функцией «ВПР»

Рис. 7 Технология ввода «Количество льгот»

 

Знак $ (абсолютная ссылка), которая фиксирует координаты ячеек и диапазонов (при копировании формул они не изменяются). Для ввода знаков $ нажимайте «F4» после ввода каждого диапазона или ссылки, по окончании ввода функции нажмите «Enter»):

В случае возникновения ошибки определите источник возникновения ошибки. Для этого на вкладке «Формула/Зависимости формул/Проверка наличия ошибок/Источник ошибок» установите указатель в ячейку с формулой и выберите в меню команду «Сер­вис/Зависимо­сти/Источ­ник ошибки».

Скопируйте формулу определения «Количество льгот» в диапазон ячеек «F4:F9»

31.Аналогично заполните диапазон ячеек «G3:G9» шапки «Должность» на основании данных таблицы 3 «Справочник работников»

32. Установите курсор в клетку «H3» таблицы1 «Лицевой счет» и с помощью «Мастера функций» введите формулу для расчета начислений по окладу.(Рис. 8-9):

= ВПР (C3;$B$14:$C$32;2;ложь)*E3 /$M$3

Рис.8 Технология ввода формулы «Начисление з/п» функцией «ВПР»

 

Рис. 9 «Начисление по окладу»

33. Скопируйте формулу начисления ЗП по окладу в диапазон «H4:H9».

34. В ячейку «I3» таблицы 1 «Лицевой счет» введите формулу расчета подоходного налога, применив функцию «ВПР» (Рис. 10).

Рис. 10 Формула расчета подоходного налога

 

35.Скопируйте формулу в ячейки «I4:I9».

36.В ячейку «J3» таблицы 1 «Лицевой счет» введите формулу расчета заработной платы к выдаче с помощью технологии создания формулы массивов:

Для этого введите знак равенство, выделите блок «H3:H9», нажмите клавишу со знаком минус «-», выделите блок «I3:I9», нажмите одновременно комбинацию клавиши «Ctrl» «Shift» Enter» (Рис. 11).

Рис. 11 Формула массива

Формула массивов создана: { = H3:H9– I3:I9}. Скопируйте формулу в ячейки «J4:J9» (Рис. 12).

 

 

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

 

37. Используя технологию суммирования, рассчитайте итоги в таблице 1 «Лицевой счет».

Для этого в ячейку «А10» введите текст «Итого:», установите указатель в ячейку «J10» и нажмите кнопку пиктографического меню «Сумма».

Если выбранный командой блок окажется верным – «J3:J9», нажмите «Enter». В противном случае выделите блок «J3:J9», нажмите «Enter».

38. Используя таблицу 4 «Справочник праздничных дней» листа «Праздничные дни» подсчитать количество рабочих дней в январе месяце применив функцию «ЧИСТРАБДНИ» (Рис. 13).

 

=ЧИСТРАБДНИ (“01.01.2009”; “31.01.2009”; ”B2:B14”)

 

Рис. 13 Подсчет количества рабочих дней в январе месяце

 

Получение итоговых данных

39. Рассчитайте сумму начисленной заработной платы по «1 отделу». Для этого в ячейку «I13» рабочего листа «Таблица с расчетными данными» введите «Итого по отделу 1». В ячейку «J13» введите функцию «СУММЕСЛИ»: (Рис.14)

 

=СУММЕСЛИ (D3:D9; 1; H3:H9)

Рис. 14 Начисление зарплаты по 1 отделу с помощью функции «СУММЕСЛИ»

40. Рассчитайте сумму начисленной заработной платы по «1 и 2 отделу». Для этого в ячейку «I14» рабочего листа «Таблица с расчетными данными» введите «Итого по отделу 1 и 2». В ячейку «J14» введите соответствующую функцию (Рис. 15):

 

=СУММЕСЛИ (D3:D9; <3; J3:J9)

Рис. 15 Начисление зарплаты по 1 и 2 отделу с помощью функции «СУММЕСЛИ»

 

41.Рассчитайте количество работников «1отдела». Для этого в ячейку «I15» введите «Работает в 1 отделе». В ячейку «J15» введите соответствующую функцию (Рис. 16).

=СЧЕТЕСЛИ (D3:D9; 1)

Рис. 16 Расчет количества

работников 1 отдела

 

42. Самостоятельно введите в ячейку «I16» «Кол-во работающих 2 отдела». В ячейку «G16» введите функциюдля расчета количества работников «2 отдела».

43. Самостоятельно введите в ячейку «I17» функцию для расчета суммарной начисленной заработной платы по «2 отделу».

44. Самостоятельно введите в ячейку «I18» функцию для расчета суммы начисленной заработной платы по должности «Инженер».

 

Стилевое оформление таблиц

45. Отформатируйте колонки таблиц с результатами вычислений по формулам с помощью вкладки «Главная/Число/Уменьшить разрядность».

Для этого выделите диапазон «A3:J9» таблицы «Лицевой счет» рабочего листа «Таблица с расчетными данными» и нажимайте кнопку пиктографического меню «Уменьшить разрядность», до тех пор, пока в форматируемых областях не окажутся целые значения.

46. Выполните выравнивание. Для этого выделите диапазон «A2:J9» таблицы «Лицевой счет» рабочего листа ««Таблица с расчетными данными» и нажмите кнопку пиктографического меню в наборе команд «Выравнивание» «По центру». Повторите указанные действия для всех таблиц.

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

* выделите диапазон несмежных ячеек (выделить первый диапазон, и затем, удерживая клавишу «Ctrl», выделите остальные) – «A1», «A12», «L1» – рабочего листа - «Таблица с расчетными данными»;

* выберите из контекстного меню команду «Формат ячейки»;

* на вкладке «Шрифт» выберите из списка «Шрифт» Arial Cyr, начертание – курсив, размер – 14, из списка «Цвет» – синий;

* щелкните по кнопке «OK».

48. Измените цвет символов шрифта в таблицах. Для этого в наборе команды «Шрифт» щелкните по кнопке пиктографического меню «Цвет текста».

49. Выполните оформление таблиц цветом и узором с помощью вкладки «Главная/Шрифт/Цвет заливки».

Если выбранное оформление не понравилось, отмените его. Для этого нажмите кнопку пиктографического меню «Цвет заливки» и выберите «Нет заливки».

50. Сохраните результаты лабораторной работы на флешке с именем «Lab_1.xls».

ЛАБОРАТОРНАЯ РАБОТА № 2