Если у вас возникли сложности с курсовой, контрольной, дипломной, рефератом, отчетом по практике, научно-исследовательской и любой другой работой - мы готовы помочь.
Предоплата всего
Подписываем
Лабораторная работа № 2
Тема: Связанные таблицы. Расчет промежуточных итогов в таблицах
MS EXCEL
Цель: изучить технологию связывания листов электронной книги, структурирования таблиц.
Задание 1. Рассчитать зарплату за декабрь и построить диаграмму. Создать итоговую таблицу ведомости квартального начисления заработной платы, провести расчет промежуточных итогов по подразделениям.
Порядок работы
1. Запустите редактор электронных таблиц Microsoft Excel и заполнить
1 лист зарплатой за октябрь, 2 лист зарплатой за ноябрь, копируя данные «Зарплата за октябрь»
2. Скопируйте содержимое листа «Зарплата октябрь» на новый лист электронной книги (Правка/Переместить/Скопировать лист). Не забудьте для копирования поставить галочку в окошке Создавать копию.
3. Присвойте скопированному листу название «Зарплата ноябрь», оправьте название месяца в ведомости на декабрь.
4. Аналогичным образом создайте «Зарплата декабрь»
Рисунок 1- «Зарплата за октябрь»
Рисунок 2 – Ведомость зарплаты за октябрь
Рисунок 3– Ведомость зарплаты за ноябрь
Рисунок 4 - Ведомость зарплаты за декабрь
4. Измените значение Премии на 46 %, Доплаты — на 8 %. Убедитесь, что программа произвела пересчет формул (см. рисунок 4).
5. По данным таблицы «Зарплата декабрь» постройте гистограмму доходов сотрудников. В качестве подписей оси X выберите фамилии сотрудников. Проведите форматирование диаграммы. Конечный вид гистограммы приведен на рисунке 5.
Рисунок 5 -Гистограмма зарплаты за декабрь
6. Перед расчетом итоговых данных за квартал проведите сортировку по фамилиям в алфавитном порядке (по возрастанию) в ведомостях начисления зарплаты за октябрь—декабрь.
7. Скопируйте содержимое листа «Зарплата октябрь» на новый лист (Правка/Переместить/Скопировать лист). Не забудьте для копирования поставить галочку в окошке Создавать копию.
8. Присвойте скопированному листу название «Итоги за квартал». Измените название таблицы на «Ведомость начисления заработной платы за 4 квартал».
9. Отредактируйте лист «Итоги за квартал» согласно образцу на рисунке 6. Для этого удалите в основной таблице (см. рисунок 1) колонки Оклада и Премии, а также строку 4 с численными значениями % Премии и % Удержания и строку 19 «Всего». Удалите также строки с расчетом максимального, минимального и среднего доходов под основной таблицей. Вставьте пустую третью строку.
10. Вставьте новый столбец «Подразделение» (Вставка/Столбец) между столбцами «Фамилия» и «Всего начислено». Заполните столбец «Подразделение» данными по образцу (см. Рисунок 6).
11. Произведите расчет квартальных начислений, удержаний и суммы к выдаче как сумму начислений за каждый месяц (данные по месяцам располагаются на разных листах электронной книги, поэтому к адресу ячейки добавится адрес листа).
Краткая справка. Чтобы вставить в формулу адрес или диапазон ячеек с другого листа, следует во время ввода формулы щелкнуть по закладке этого листа и выделить на нем нужные ячейки. Вставляемый адрес будет содержать название этого листа.
В ячейке D5 для расчета квартальных начислений «Всего начислено формула имеет вид
Рисунок 6 - Таблица для расчета итоговой квартальной заработной платы
= 'Зарплата декабрь'!Fб + 'Зарплата ноябрь'!F5 + + 'Зарплата октябрь'!Е5.
Аналогично произведите квартальный расчет «Удержания» и «К выдаче».
Примечание. При выборе начислений за каждый месяц делайте ссылку на соответствующую ячейку из таблицы соответствующего листа электронной книги «Зарплата». При этом произойдет связывание информации соответствующих ячеек листов электронной книги.
12. В силу однородности расчетных таблиц зарплаты по месяцам для расчета квартальных значений столбцов «Удержание» и «К выдаче» достаточно скопировать формулу из ячейки D5 в ячейки Е5 и F5
(см. рисунок 7).
Для расчета квартального начисления заработной платы для всех сотрудников скопируйте формулы в столбцах D, Е и F. Ваша электронная таблица примет вид, как на рисунке 7.
13. Для расчета промежуточных итогов проведите сортировку по подразделениям, а внутри подразделений — по фамилиям. Таблица примет вид, как на рисунке 8.
14. Подведите промежуточные итоги по подразделениям, используя формулу суммирования. Для этого выделите всю таблицу и выполните команду Данные/Итоги (см. рисунок 9). Задайте параметры подсчета промежуточных итогов:
при каждом изменении в — Подразделение;
операция — Сумма;
добавить итоги по: Всего начислено, Удержания, К выдаче.
Рисунок 7 - Расчет квартального начисления заработной платы, связыванием листов электронной книги
Рисунок 8 - Вид таблицы начисления квартальной заработной платы после сортировки по подразделениям
Отметьте галочкой операции «Заменить текущие итоги» и «Итоги под данными».
Примерный вид итоговой таблицы представлен на рисунке 10.
15. Изучите полученную структуру и формулы подведения промежуточных итогов, устанавливая курсор на разные ячейки таблицы. Научитесь сворачивать и разворачивать структуру до разных }ровней (кнопками «+» и «-»).
Краткая справка. Под структурированием понимается многоуровневая группировка строк и столбцов таблицы и создание элементов управления, с помощью которых легко можно скрывать и раскрывать эти группы.
Рисунок 9 - Окно задания параметров расчета промежуточных итогов
Рисунок 10 - Итоговый вид таблицы расчета квартальных итогов по зарплате
16. Сохраните файл «Зарплата» с произведенными изменениями (Файл/Сохранить).
Задание 2. Исследовать графическое отображение зависимостей ячеек друг от друга.
Порядок работы
Скопируйте содержимое листа «Зарплата октябрь» на новый лист. Копии присвойте имя «Зависимости». Откройте панель «Зависимости» (Сервис/Зависимости/Панель зависимостей) (см. рисунок 11). Изучите назначение инструментов панели, задерживая на них указатель мыши.
Рисунок 11 - Панель зависимостей
Рисунок 12 - Зависимости в таблице расчета зарплаты
Устанавливайте курсор на ячейку в каждом столбце и вызывайте зависимости кнопками Влияющие ячейки и Зависимые ячейки панели «Зависимости». Появятся стрелки, указывающие на зависимость ячейки от других ячеек и ее влияние на другие ячейки. Примерный вид таблицы с зависимостями приведен на рисунке 12.
Сохраните файл «Зарплата» с произведенными изменениями.
Тема: Подбор параметра. Организация обратного расчета.
Цель: Изучение технологии подбора параметра при обратных расчетах.
Задание 1. Используя режим подбора параметра, определить, при каком значении % Премии общая сумма заработной платы за октябрь будет равна 250 000 р. (на основании файла «Зарплата»)
Краткая справка. К исходным данным этой таблицы относятся значения Оклада и % Премии, одинакового для всех сотрудников. Результатом вычислений являются ячейки, содержащие формулы, при этом изменение исходных данных приводит к изменению результатов расчетов. Использование операции «Подбор параметра» в MS Excel позволяет производить обратный расчёт, когда задается конкретное значение рассчитанного параметра, и по этому значению подбирается некоторое удовлетворяющее заданным условиям, значение исходного параметра расчета.
Порядок работы
1. Запустите редактор электронных таблиц Microsoft Excel и откройте созданный файл «Зарплата».
2. Скопируйте содержимое листа «Зарплата октябрь» на новый лист электронной книги (Правка/Переместить/Скопировать лист). Не забудьте для копирования поставить галочку в окошке Создавать копию. Присвойте скопированному листу имя «Подбор параметра».
3. Осуществите подбор параметра командой Сервис/Подбор параметра (см. рисунок 1).
В диалоговом окне Подбор параметра на первой строке в качестве подбираемого параметра укажите адрес общей итоговой суммы зарплаты (ячейка G19), на второй строке наберите заданное значение 250 000, на третьей строке укажите адрес подбираемого значения % Премии (ячейка D4), затем нажмите кнопку ОК. В окне Результат подбора параметра дайте подтверждение подобранному параметру нажатием кнопки 0К (см. рисунок 2).
Произойдет обратный пересчет % Премии. Результаты подбора
(см. рисунок 3):
если сумма к выдаче равна 250 000 р., то % Премии должен быть 203 %.
Рисунок 1 - Задание параметров подбора параметра
Рисунок 2 - Подтверждение результатов подбора параметра
Рисунок 3 - Подбор значения % Премии для заданной общей суммы заработной платы, равной 250000 р.
Задание 2. Используя режим подбора параметра, определить штатное расписания фирмы. Исходные данные приведены на рисунке 4.
Краткая справка. Известно, что в штате фирмы состоит:
• 6 курьеров;
• 8 младших менеджеров;
• 10 менеджеров;
• 3 заведующих отделами;
• 1 главный бухгалтер;
• 1 программист;
• 1 системный аналитик;
• 1 генеральный директор фирмы.
Рисунок 4 - Исходные данные для Задания 2
Общий месячный фонд зарплаты составляет 100000 р. Необходимо определить, какими должны быть оклады сотрудников фирмы.
Каждый оклад является линейной функцией от оклада курьера, а именно: зарплата = Аi*x+Bi, где х-оклад курьера; Аi и Bi— коэффициенты, показывающие:
Аi — во сколько раз превышается значение х;
Bi — на сколько превышается значение х.
Порядок работы
1. Запустите редактор электронных таблиц Microsoft Excel.
2. Создайте таблицу штатного расписания фирмы по приведенному образцу (см. рисунок 4). Введите исходные данные в рабочий лист электронной книги.
3. Выделите отдельную ячейку D3 для зарплаты курьера (переменная «х») и все расчеты задайте с учетом этого. В ячейку D3 временно введите произвольное число.
4. В столбце D введите формулу для расчета заработной платы по каждой должности. Например, для ячейки D6 формула расчета имеет следующий вид: = В6 * $D$3 + С6 (ячейка D3 задана в виде абсолютной адресации). Далее скопируйте формулу из ячейки D6 вниз по столбцу автокопированием.
В столбце F задайте формулу расчета заработной платы всех работающих в данной должности. Например, для ячейки F6 формула расчета имеет вид = D6 * Е6. Далее скопируйте формулу из ячейки F6 вниз по столбцу автокопированием.
В ячейке F14 автосуммированием вычислите суммарный фонд заработной платы фирмы.
5. Произведите подбор зарплат сотрудников фирмы для суммарной заработной платы, равной 100 000 р. Для этого в меню Сервис активизируйте команду Подбор параметра.
В поле Установить в ячейке появившегося окна введите ссылку7 на ячейку F14, содержащую формулу расчета фонда заработной платы;
в поле Значение наберите искомый результат 100 000;
в поле Изменяя значение ячейки введите ссылку на изменяемую ячейку D3, в которой находится значение зарплаты курьера, и щелкните по кнопке ОК. Произойдет обратный расчет зарплаты сотрудников по заданному условию при фонде зарплаты, равном 100 000 р.
6. Присвойте рабочему листу имя «Штатное расписание 1». Сохраните созданную электронную книгу под именем «Штатное расписание» в своей папке.
Анализ задач показывает, что с помощью MS Excel можно решать линейные уравнения. Задания 1 и 2 показывают, что поиск значения параметра формулы — это не что иное, как численное решение уравнений. Другими словами, используя возможности программы MS Excel, можно решать любые уравнения с одной переменной.
Задание 3. Используя режим подбора параметра и таблицу расчета штатного расписания (см. задание 2), определить заработные платы сотрудников фирмы для ряда заданных значений фонда заработной платы,
Порядок работы
1. Скопируйте содержимое листа «Штатное расписание 1» на новый лист и присвойте копии листа имя «Штатное расписание 2». Выберите коэффициенты уравнений для расчета согласно таблица 1 (один из пяти вариантов расчетов).
2. Методом подбора параметра последовательно определите зарплаты сотрудников фирмы для различных значений фонда заработной платы: 100000, 150000, 200 000, 250000, 300 000, 350 000, 400000 р. Результаты подбора значений зарплат скопируйте в таблицу 2 в виде специальной вставки.
Таблица 1
|
Должность |
Вариант 1 |
Вариант 2 |
Вариант 3 |
Вариант 4 |
Вариант 5 |
|||||
|
Коэф-т А |
Коэф-т В |
Коэф-т А |
Коэф-т В |
Коэф-т А |
Коэф-т В |
Коэф-т А |
Коэф-т В |
Коэф-т А |
Коэф-т В |
|
|
Курьер |
1 |
0 |
1 |
0 |
1 |
0 |
1 |
0 |
1 |
0 |
|
Младший менеджер |
1,2 |
500 |
1,3 |
0 |
1,3 |
700 |
1,4 |
0 |
1,45 |
500 |
|
Менеджер |
2,5 |
800 |
2,6 |
500 |
2,7 |
700 |
2,6 |
300 |
2,5 |
1000 |
|
Зав.отделом |
3 |
1500 |
3,1 |
1200 |
3,2 |
800 |
3,3 |
700 |
3,1 |
1000 |
|
Главный бухгалтер |
4 |
1000 |
4,1 |
1200 |
4,2 |
500 |
4,3 |
0 |
4,2 |
1200 |
|
Программист |
1,5 |
1200 |
1,6 |
800 |
1,7 |
50 |
1,6 |
1000 |
1,5 |
1300 |
|
Системый аналитик |
3,5 |
0 |
3,6 |
500 |
3,7 |
800 |
3,6 |
1000 |
3,5 |
1500 |
|
Ген. директор |
5 |
2500 |
5,2 |
2000 |
5,3 |
1500 |
5,5 |
1000 |
5,4 |
3000 |
Таблица 2
Краткая справка. Для копирования результатов расчетов в виде значений необходимо выделить копируемые данные, произвести запись в буфер памяти (Правка/Копировать), установить курсор в соответствующую ячейку таблицы ответов, задать режим специальной вставки (Правка/Специальная вставка), отметив в качестве объекта вставки — значения (Правка/Специальная вставка/ вставить — значения) (см. рисунок 5).
Рисунок 5 - Специальная вставка значений данных
Специальная вставка информации в виде значений позволяет копировать значения, полученные в результате расчетов, без дальнейшей их зависимости от пересчета формул.