Задание 1. Построение простой электронной таблицы

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

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

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

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

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

С2 = В2 * $В$12;

D2 =B2 – C2.

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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