Порядок работы. 1. Запустите редактор электронных таблиц Microsoft Excel и создайте новую электронную книгу

1. Запустите редактор электронных таблиц Microsoft Excel и создайте новую электронную книгу.

2. Создайте таблицу расчета заработной платы по образцу (см. рис. 5.1). Введите исходные данные — Табельный номер, ФИО и Оклад, % Премии = 27%, % Удержания = 13 %.

Рисунок 5.1 - Ведомость начисления заработной платы

При расчете премии используется формула:

Премия =Оклад * % Премии.

В ячейке D5 наберите формулу = С5 * $D$4 (ячейка D4 используется в виде абсолютной адресации) и скопируйте автозаполнением.

Рекомендации. Для удобства работы и формирования навыков работы с абсолютным видом адресации рекомендуется при оформлении констант окрашивать ячейку цветом, отличным от цвета расчетной таблицы. Тогда при вводе формул в расчетную окрашенная ячейка (т.е. ячейка с константой) будет вам напоминанием, что следует установить абсолютную адресацию (набором символов $ с клавиатуры или нажатием клавиши [F4]).

Формула для расчета «Всего начислено»:

Всего начислено = Оклад + Премия.

При расчете удержания используется формула:

Удержание = Всего начислено * % Удержания,

для этого в ячейке F5 наберите формулу = $F$4 * Е5.

Формула для расчета столбца «К выдаче»:

К выдаче = Всего начислено - Удержания.

3. Рассчитайте итоги по столбцам, а также максимальный, минимальный и средний доходы по данным колонки «К выдаче» (Вставка/Функция/категория — Статистические функции).

4. Переименуйте ярлычок листа 1, присвоив ему имя «Зарплата октябрь». Для этого дважды щелкните мышью по ярлычку и наберите новое имя. Можно воспользоваться командой Переименовать контекстного меню ярлычка, вызываемого правой кнопкой мыши. Результаты работы представлены на рис 5.2.

Краткая справка. Каждая рабочая книга Excel может содержать до 255 рабочих листов. Это позволяет, используя несколько листов, создавать понятные и четко структурированные документы, вместо того, чтобы хранить большие последовательные наборы данных на одном листе.

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

Краткая справка. Перемещать и копировать листы можно, перетаскивая их корешки (для копирования удерживайте нажатой клавишу [Ctrl]).

6. Присвойте скопированному листу название «Зарплата ноябрь». Исправьте название месяца в названии таблицы. Измените значение премии на 32%. Убедитесь, что программа произвела пересчет формул.

7.Между колонками «Премия» и «Всего начислено» вставьте новую колонку «Доплата» (Вставка/Столбец) и рассчитайте значение доплаты по формуле Доплата = Оклад * % Доплаты. Значение доплаты примите равным 5 %.

Рисунок 5.2 - Результаты начисления заработной платы

8.Измените формулу для расчета значений колонки «Всего начислено»:

Всего начислено = Оклад + Премия + Доплата.

Рисунок 5.3 – Копирование листа электронной книги

Рисунок 5.4 – Условное форматирование данных

9. Проведите условное форматирование значений колонки «К выдаче». Установите формат вывода значений между 5000 и 7000 зеленым цветом шрифта; меньше 5000 - красным; больше или равно 7000 - синим цветом шрифта (Формат/Условное форматирование) (рис. 5.4).

10.Проведите сортировку по фамилиям в алфавитном порядке по возрастанию (выделите фрагмент таблицы А5:G18), выберите меню Данные/Сортировка, сортировать по — Столбец В (рис. 5.6).

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

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

Рисунок 5.5 – Защита листа электронной книги

Рисунок 5.6 – Сортировка данных

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

13.Самостоятельносделать примечания к двум-трем ячейкам.

14. Выполнить условное форматирование премии за ноябрь месяц:

до 1000 руб. - желтым цветом заливки; от 1000 до 2000 руб. - зеленым цветом шрифта; свыше 2000 руб. - малиновым цветом заливки.

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

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

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

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

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

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

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

Рисунок 5.7 -Ведомость зарплаты за декабрь

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

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

Рисунок 5.8 - Гистограмма зарплаты за декабрь

6. Перед расчетом итоговых данных за квартал проведите сортировку по фамилиям валфавитном порядке (по возрастанию) в ведомости начисления зарплаты за октябрь.

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

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

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

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

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

= 'Зарплата декабрь'!F5 + 'Зарплата ноябрь'!F5 + 'Зарплата октябрь'!Е5.

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

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

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

10. Для расчета промежуточных итогов проведите сортировку по подразделениям, а внутри подразделений — по фамилиям. Таблица примет вид, как на рис. 5.10.

Рисунок 5.10 - Вид таблицы после сортировки

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

при каждом изменении в - Подразделение;

операция - Сумма;

добавить итоги по: Всего начислено, Удержания, К выдаче.

Отметьте галочкой операции «Заменить текущие итоги» и «Итоги под данными».

Рисунок 5.11 - Окно задания параметров расчета промежуточных итогов

Примерный вид итоговой таблицы представлен на рис. 5.12.

Рисунок 5.12 - Итоговый вид таблицы расчета квартальных итогов по зарплате

12. Изучите полученную структуру и формулы подведения промежуточных итогов, устанавливая курсор на разные ячейки таблицы. Научитесь сворачивать и разворачивать структуру до разных уровней (кнопками «+» и «-»).

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