Елена Михеева - Практикум по информационным технологиям в профессиональной деятельности

Рис. 10.4. Условное форматирование данных

10. Проведите сортировку по фамилиям в алфавитном порядке по возрастанию (выделите фрагмент таблицы с 5-й по 18-ю строки без строки «Всего», выберите меню Данные/Сортировка, сортировать по – Столбец В) (рис. 10.5).

Рис. 10.5. Сортировка данных

11. Поставьте к ячейке D3 комментарии «Премия пропорциональна окладу» (Вставка/Примечание), при этом в правом верхнем углу ячейки появится красная точка, которая свидетельствует о наличии примечания. Конечный вид таблицы расчета заработной платы за ноябрь приведен на рис. 10.6.

Рис. 10.6. Конечный вид зарплаты за ноябрь

12. Защитите лист «Зарплата ноябрь» от изменений (Сервис/Защита/Защитить лист). Задайте пароль на лист, создайте подтверждение пароля (рис. 10.7).

Убедитесь, что лист защищен и невозможно удаление данных. Снимите защиту листа (Сервис/Защита/Снять защиту листа).

Рис. 10.7. Защита листа электронной книги

Рис. 10.8. Подтверждение пароля

13. Сохраните созданную электронную книгу под именем «Зарплата» в своей папке.

Дополнительные задания

Задание 10.2. Сделать примечания к двум – трем ячейкам

Задание 10.3.Выполнить условное форматирование оклада и премии за ноябрь:

до 2000 – желтым цветом заливки;

от 2000 до 10 000 – зеленым цветом шрифта;

свыше 10 000 – малиновым цветом заливки, белым цветом шрифта.

Задание 10.4.Защитить лист зарплаты за октябрь от изменений

Проверьте защиту. Убедитесь в неизменяемости данных. Снимите защиту со всех листов электронной книги «Зарплата».

Задание 10.5.Построить круговую диаграмму начисленной суммы к выдаче всем сотрудникам за ноябрь.

Цель занятия.Связывание листов электронной книги. Расчет промежуточных итогов. Структурированные таблицы.

Задание 11.1.Рассчитать зарплату за декабрь и построить диаграмму. Создать итоговую таблицу ведомости квартального начисления заработной платы, провести расчет промежуточных итогов по подразделениям

Порядок работы

1. Откройте редактор электронных таблиц Microsoft Excel и откройте созданный в практической работе 10 файл «Зарплата».

2. Скопируйте содержимое листа «Зарплата ноябрь» на новый лист электронной книги (Правка/Переместить/скопировать лист). Не забудьте для копирования поставить галочку в окошке Создавать копию.

3. Присвойте скопированному листу название «Зарплата декабрь». Подправьте название месяца в названии таблицы.

4. Измените значение Премии на 46 %, Доплаты – на 8 %. Убедитесь, что программа произвела пересчет формул (рис. 11.1).

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

5. По данным таблицы «Зарплата декабрь» постройте гистограмму дохода сотрудников. В качестве подписей оси Х выберите фамилии сотрудников. Проведите форматирование диаграммы. Конечный вид гистограммы приведен на рис. 11.2.

6. Перед расчетом итоговых данных за квартал проведите сортировку по фамилиям в алфавитном порядке (по возрастанию) в таблице расчета зарплаты за октябрь (выделите фрагмент таблицы с 5-й по 18-ю строки без строки «Всего», выберите меню Данные/ Сортировка, сортировать по – Столбец В).

7. Скопируйте содержимое листа «Зарплата декабрь» на новый лист (Правка/Переместить/скопировать лист). Не забудьте для копирования поставить галочку в окошке Создавать копию.

8. Присвойте скопированному листу название «Итоги за квартал». Измените название таблицы на «Ведомость начисления заработной платы за 4 квартал».

Рис. 11.2. Гистограмма зарплаты за декабрь

9. Отредактируйте лист «Итоги за квартал» согласно образцу на рис. 11.3. Для этого удалите в основной таблице (см. рис. 11.1) колонки оклада, премии и доплаты, а также строку 4 с численными значениями %Премии и %Удержания и строку 19 «Всего». Удалите также строки с расчетом максимального, минимального и среднего доходов под основной таблицей. Вставьте пустую третью строку.

10. Вставьте новый столбец «Подразделение» (Вставка/Столбцы) между столбцами «Фамилия» и «Всего начислено». Заполните столбец «Подразделение» данными по образцу (см. рис. 11.3).

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

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

Краткая справка. Чтобы вставить в формулу адрес или диапазон ячеек с другого листа, следует во время ввода формулы щелкнуть по вкладке этого листа и выделить на нем нужные ячейки. Вставляемый адрес будет содержать название этого листа.

В ячейке D5 для расчета квартальных начислений «Всего начислено» формула имеет вид

= “Зарплата декабрь”F5 +’ Зарплата ноябрь’F5 + “Зарплата октябрь”!E5.

Аналогично произведите квартальный расчет столбцов «Удержания» и «К выдаче».

Примечание. При выборе начислений за каждый месяц делайте ссылку на соответствующую ячейку из таблицы соответствующего листа электронной книги «Зарплата». При этом произойдет связывание ячеек листов электронной книги.

12. В силу однородности расчетных таблиц зарплаты по месяцам для расчета квартальных значений столбцов «Удержания» и «К выдаче» достаточно скопировать формулу из ячейки D5 в ячейки E5 и F5 (рис. 11.4).

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