ЗАДАНИЕ 1. ПОСТРОЕНИЕ ПРОСТОЙ ЭЛЕКТРОННОЙ ТАБЛИЦЫ

Практическая работа №1 Работа в MS Excel

1. Создайте папку с именем группы. В ней создайте папку со своим именем. В ней создайте рабочую книгу Ехсеl c названием зарплата.

2. Начиная с клетки А1, создайте электронную таблицу по образцу, приведенному на рис. 1 (ведомость зарплаты). Сначала заполните строки 1, 2 и колонку А. Все заголовки и фамилии вводите с первой позиции клетки. Колонку Зарплата заполните значениями в пределах от 15000 руб. до 50 000 руб.

Рис. 1 Ведомость зарплаты

3. При заполнении клеток СЗ, DЗ используются формулы:

СЗ = ВЗ * $С$2;

D3 =B3 – C3.

Для ввода абсолютной ссылки – поставить курсор перед адресом ячейки в строке формул и нажать F4.

4. Диапазоны клеток СЗ:С10 и DЗ:D10 заполняются путем копирования соответствующих формул. Для этого нужно выделить блок ячеек и вызвать операцию вкладка Главная — группа Редактирование или с использованием мыши путем «протаскивания» за маркер заполнения в правом нижнем углу выделенной клетки.

5. Просмотрите формулы для всех сотрудников. Обратите внимание на автоматическое изменение некоторых адресов ячеек. Адрес какой ячейки не изменился? Почему? Обязательно найдите ответы на эти вопросы, при необходимости обратитесь к преподавателю.

6. Клетка В11 рассчитывается по формуле =СУММ(ВЗ:В10). В клетки С11, D11 эта формула копируется. Сохраните заполненную таблицу на первом листе файла Excel, который назовите ZP1.

7. Поменяйте у нескольких сотрудников зарплату. Для редактирования содержимого ячеек используйте клавишу F2 или двойной щелчок мышкой на соответствующей ячейке. Обратите внимание на изменение вычисляемых ячеек.

8. Установите налог 15%. Сравните полученные итоговые данные с предыдущими значениями.

9. Скопируйте таблицу на второй лист. Вставьте новые графы Стаж, Премия и Всего начислено после графы Зарплата, используя команду вкладки Главная — группа Ячейки — Вставить.

10. Самостоятельно задайте формулы для их вычисления, исходя из того, что премия составляет 45 процентов от зарплаты, а Всего начислено— это Зарплата + Премия. Отредактируйте все остальные формулы, руководствуясь задачей.

11. Удалите одну строку из таблицы (сотрудник уволен). Проверьте формулы итоговой строки, обратите внимание на изменение диапазонов в формулах.

12. Дополните таблицу еще тремя строками, включив их между, например, пятой и шестой строками (приняты три новых сотрудника). Заполните эти строки. Фамилии и зарплату введите, формулы скопируйте.

13. Вставьте перед колонкой Налог еще две колонки Пенсионный фонд и Налогооблагаемая база. Установите, что в пенсионный фонд удерживается в размере 1% начисленной зарплаты и премии, не входит в налогооблагаемую базу, т.е. Налогооблагаемая база вычисляется как Зарплата + Премия — Пенсионный фонд. Внесите все необходимые изменения в формулы.

14. Измените алгоритм расчета премии. Если стаж меньше определенной величины (например, 5 лет), то принимается ставка 12%, если больше — 20%. Формула должна использовать функцию ЕСЛИ. Как изменится формула, если шкала будет задана так: до 5 лет - 12%, от 5 лет до 15 — 20%, свыше 15 — 25%?

15. Проанализируйте полученные результаты.

16. Вставьте перед колонкой Фамилия новую графу Табельный номер и заполните ее значениями: 100, 101, 102 и т.д. (вкладка Главная — Редактирование — Заполнить — Прогрессия).

17. Под строкой Итого вставьте еще две строки для вычисления среднего и максимального значения начисленной и выданной зарплаты. Для этого воспользуйтесь встроенными статистическими функциями МАКС и СРЗНАЧ, укажите диапазон для работы этих функций.

18. Задайте следующие имена для диапазонов ячеек (выделить диапазон и в контекстном меню выбрать команду Имя Диапазона— ввести Имя или на вкладке Формулы в группе Определенные имена выбрать команду Присвоить имя):

Зарплата — для столбца с начисленными зарплатами;

Премия— для столбца с премиями;

Налог— для столбца с налогами;

Пенсионный фонд — для столбца с отчислениями в пенсионный фонд.

19. Выделите всю таблицу и выполните последовательность действий — Формулы— Определенные имена — Присвоить имя — Применить имена. Укажите применить все имена из списка. Проверьте изменения в формулах.

20. Вместо адресов ячеек в формулах должны появиться имена диапазонов, например формулы в столбце Всего начисленодолжны иметь вид = Зарплата + Премия.

21. Сохраните таблицу на втором листе под именем ZP2 в своем файле.