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

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

Тема : СВЯЗАННЫЕ ТАБЛИЦЫ . РАСЧЕТ ПРОМЕЖУТОЧНЫХ ИТОГОВ

В ТАБЛИЦАХ MICROSOFT EXCEL

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

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

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

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

2.Скопируйте содержимое листа «Зарплата ноябрь» на новый лист электронной книги

(Правка/Переместить/скопировать лист). Не за­будьте для копирования поставить галочку в окошке Создавать копию.

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

4. ИЗМСНИ1С значение Премии на 46%, Доплаты — на 8%. Убедитесь, что программа произвела пересчет формул.

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

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

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

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

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

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

 

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

 

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

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

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

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

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

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

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

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

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

14. Рассчитайте промежуточные итоги по подразделениям, ис­пользуя формулу суммирования. Для этого выделите всю таблицу и выполните команду Данные/Итоги Задайте параметры подсчета промежуточных итогов: при каждом изменении в — Под­разделение; операция  - Сумма; добавить итоги по - Всего на­числено, Удержания, К выдаче. Отметьте галочкой операции «За­менить текущие итоги» и «Итоги  под данными».

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

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

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

 

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

Задание 11.2. Исследуйте графическое отображение зависимостей ячеек друг от друга

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

Скопируйте содержимое листа «Зарплата октябрь» на новый лист. Листу с копией дайте имя «Зависимости». Откройте панель Зависимости (Сервис/Зависимости/Панель зависимостей). Изучите назначение инструментов панели, задерживая на них указатель мыши.

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

 

Результаты работы.

 

 

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

 

 

                          Рис. 11.3 Зависимости в таблице расчета зарплаты