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

Рис. 11.4. Расчет квартального начисления заработной платы связыванием листов электронной книги

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

Рис. 11.5. Вид таблицы начисления квартальной заработной платы после сортировки по подразделениям

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

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

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

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

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

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

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

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

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

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

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

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

Рис. 11.8. Панель зависимостей

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

Цель занятия. Изучение технологии подбора параметра при обратных расчетах.

Задание 12.1. Используя режим подбора параметра, определить, при каком значении % Премии общая сумма заработной платы за октябрь будет равна 250 000 руб. (на основании файла «Зарплата», созданного в практических работах 10 и 11)

Результаты работы представлены на рис. 11.9.

Краткая справка. К исходным данным этой таблицы относятся значения оклада и %Премии, одинакового для всех сотрудников. Результатом вычислений являются ячейки, содержащие формулы, при этом изменение исходных данных приводит к изменению результатов расчетов. Использование операции «Подбор параметра» в Microsoft Excel позволяет производить обратный расчет, когда задается конкретное значение рассчитанного параметра и по этому значению подбирается некоторое удовлетворяющее заданным условиям значение исходного параметра расчета.

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

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

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

3. Осуществите подбор параметра командой Сервис/Подбор параметра (рис. 12.1).

Рис. 12.1. Задание параметров подбора параметра

В диалоговом окне «Подбор параметра» на первой строке в качестве подбираемого параметра укажите адрес общей итоговой суммы зарплаты (ячейка G19), на второй строке наберите заданное значение 250 000, на третьей строке укажите адрес подбираемого значения – %Премии (ячейка D4), затем нажмите кнопку ОК. В окне «Результат подбора параметра» дайте подтверждение подобранному параметру, нажмите кнопку ОК (рис. 12.2).

Рис. 12.2. Подтверждение результатов подбора параметра

Произойдет обратный пересчет %Премии. Результаты подбора параметра представлены на рис. 12.3: если сумма к выдаче равна 250 000 руб., то процент премии должен быть равен 203.

Рис. 12.3. Подбор значения процента премии для заданной общей суммы заработной платы, равной 250 000 руб.

Задание 12.2.Используя режим подбора параметра, определить штатное расписание фирмы. Исходные данные приведены на рис. 12.4

Рис. 12.4. Исходные данные для задания 12.2

Краткая справка. Известно, что в штате фирмы состоят:

– 6 курьеров;

– 8 младших менеджеров;

– 10 менеджеров;

– 3 заведующих отделами;

– 1 главный бухгалтер;

– 1 программист;

– 1 системный аналитик;

– 1 генеральный директор фирмы.

Общий месячный фонд зарплаты составляет 100 000 руб. Необходимо определить, какими должны быть оклады сотрудников фирмы.

Каждый оклад является линейной функцией от оклада курьера, а именно: зарплата = A t* х + B t , где х – оклад курьера; A t и B t – коэффициенты, показывающие:

A t– во сколько раз превышается значение х;

B t– на сколько превышается значение х.

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

1. Откройте редактор электронных таблиц Microsoft Excel.