Предъявите результаты преподавателю.

Практическая работа № 6

Тема» Относительная и абсолютная адресация в Excel»

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

1. Произвести расчеты по приведенным формулам.

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

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

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

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

2. Переименовать ярлык Листа 1, присвоив ему имя «Зарплата за октябрь»

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

4. Изменить значение премии на 32%. Убедится, что был произведен перерасчет формул.

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

Доплата = Оклад * %Доплаты

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

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

7. Провести условное форматирование значений колонки «К выдаче». Установить формат вывода значений между 7000 и 10000 – зеленым цветом, меньше 7000 – красным, больше или равно – синим цветом шрифта.

8. Провести сортировку по фамилиям в алфавитном порядке по возрастанию.

9. Поставить к ячейке Премия комментарий «Премия пропорциональна окладу».

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

11. Защитить лист «Зарплата за ноябрь» от изменений. Задать пароль на лист, сделать подтверждение пароля. Убедиться в том, что лист защищен и невозможно удаление данных.

12. Сохранить электронную книгу под именем Зарплата. Предъявите результаты преподавателю.

Практическая работа № 7

Тема «Связанные таблицы. Расчет промежуточных итогов в таблицах MS Excel»

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

Теоретические сведения:

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

= ‘Зарплата декабрь’!F5 + ‘Зарплата ноябрь’!F5 + ‘Зарплата октябрь’!F5

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

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

3.Графическое отображение зависимостей ячеек друг от друга: Сервис – Зависимости – Панель зависимостей. Устанавливая курсор на ячейку в каждом столбце и вызывая зависимости кнопками Влияющие ячейки и Зависимые ячейки, можно увидеть стрелки, указывающие на зависимость ячейки от других ячеек и ее влияние на другие ячейки.

Порядок выполнения работы:

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

1. Открыть созданный ранее файл «Зарплата». Скопировать содержимое листа «Зарплата за ноябрь» на новый лист электронной книги. Присвоить этому листу название «Зарплата за декабрь». Исправить название месяца в ведомости на декабрь.

2. Изменить значение премии на 46%, Доплаты – на 8%. Убедится в том, что был произведен перерасчет формул.

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

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

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

6. Отредактировать лист «Итоги за квартал» согласно образцу на рисунке. Для этого удалить в основной таблице колонки Оклад и Премия, а также строку с численными значениями процентов премии и удержаний и строку Всего. Между названием таблицы и самой таблицей вставить пустую строку. Вставить новый столбец Подразделение.

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

8. Для расчета промежуточных итогов провести сортировку по подразделениям, а внутри подразделений – по фамилиям. Таблица примет следующий вид:

9.

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

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

11. Сохранить файл Зарплата с произведенными изменениями.