Построить диаграмму сводной таблицы

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

1. Постройте гистограмму соотношения продаж каждого менеджера той или иной фирме.

2. Перед построением диаграммы следует удалить из сводной таблицы все данные общих итогов.

Как удалить общие итоги?

- откройте ранее созданную сводную таблицу

- выделите произвольную ячейку таблицы.

- откройте вкладку Параметры.

- в команде Сводные таблицы выберите – параметры

- на вкладке диалогового окна Итоги и фильтры удалите флажки Общие Итоги по строкам и Общие итоги по столбцам.

- щелкните на кнопке Готово.

3. После этого постройте гистограмму аналогично построению круговой диаграммы. Один из вариантов гистограммы приведен на рис. 9.

4.  Редактирование диаграмм

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

5. Сохраните работу в своей папке.

ОБРАЗЕЦ ВЫПОЛНЕНИЯ ЗАДАНИЙ

Задание 1

Задание 2

Задание 3

КРИТЕРИИ ОЦЕНКИ

Все задания выполняются строго по порядку.

За каждое задание студент получает 1 балл.

Оценка «5» ставится при выполнении всех заданий.

Оценка «4» ставится при выполнении 4-х заданий.

Оценка «3» ставится при выполнении 3-х заданий.

Практическая работа Создание штатного расписания с использованием функций подбора параметра

Время проведения – 2 часа

Цель занятия: Изучение технологии подбора параметра при обратных расчетах, возможностей MS Excel для анализа данных

Вопросы для подготовки к работе:

1. Алгоритм работы с функцией подбора параметра.

2. Определение типа данных в ячейках.

3. Понятие абсолютной и относительной адресации.

4. Редактирование и форматирование данных.

Литература: Теоретическая часть методических указаний,   [1.стр. с.262-271]

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

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

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

Зарплата=А_i*х+B_i, где х-оклад курьера; А_i, B_i – коэффициенты, показывающие:

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

B_{i – на сколько} превышается значение х.

1. Создайте таблицу штатного расписания по приведенному образцу в табл.10. введите исходные данные в рабочий лист электронной книги.

Таблица 10

2. Выделите отдельную ячейку D3 для зарплаты курьера (переменная х)и все расчеты задайте с учетом этого. В ячейку D3 временно введите произвольное число.

3. В столбце D введите формулу для расчета заработной платы по каждой должности. Например для ячейки D6 формула примет вид: =B6*$D$3+C6(ячейка D3 задана в виде абсолютной ссылки). Скопируйте формулу из ячейки D6 вниз по столбцу автозаполнением.

4. В столбце F задайте формулу расчета заработной платы всех работающих в данной должности, например для ячейки F6 формула имеет вид = D6*E6. Скопируйте формулу на весь диапазон.

5. В ячейке F14 автосуммированием вычислите суммарный фонд заработной платы фирмы.

6. Произведите подбор параметра зарплат сотрудников фирмы для суммарной заработной платы, равной 100000р. Для этого на вкладке Данные/Работа с данными/Анализ, что, если активизируйте команду Подбор параметра (рис.40)

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

Рисунок 40

После ввода команды произойдет расчет заработной платы сотрудником при заданном условии.

Задание 2.Создать штатное расписание по предлагаемым параметрам

1. Используя режим подбора параметра и таблицу штатного расписания, определить заработные платы сотрудником фирмы для ряда значение фонда: 250000р., 300000р., расчет сохранить на следующих листах рабочей книги.

2. Используя режим подбора параметра и таблицу штатного расписания, определить заработные платы сотрудников фирмы, изменив самостоятельно коэффициенты A и B и изменяя количество сотрудников по должностям: Курьер, Младший менеджер, Менеджер, Зав. отделом для фонда з/п в 400000р. расчет сохранить на следующем листе рабочей книги.

Задание 3. Построить диаграммы, подготовить документ к печати.

1. Для штатных расписаний, построенных в задании 2, постройте диаграмму по зарплате сотрудников, добавьте: подписи данных, название диаграммы. Разместите все на тех же листах, где находится штатное расписание.

2. Вставьте нижний колонтитул с ФИО, датой выполнения работы, подготовьте документ к печати.

Задание 4.

Используя режим подбора параметра, определите штатное расписание фирмы. Известно, что в штате фирмы может состоять 8-12 рекламных агентов,  2 зав. отделами, 1 программист, 1 гл. бухгалтер, 1 ген. директор. Общий месячный фонд зарплаты 280 тыс. руб. Каждый оклад является линейной функцией от оклада рекламного агента, а именно: зарплата =А*х+20%*х, где А коэффициент принимает значения от 1 до 3.

Задание 5.

Используя режим подбора параметра решите экономическую задачу: Фирма производит изделия и продает их по цене 90 руб. Ежемесячные постоянные затраты составляют 5000 руб., переменные затраты на единицу изделия – 30 руб. Требуется:

­ определить количество изделий для получения прибыли 3000 руб.;

­ определить точку безубыточности, т.е. вычислить количество изделий, при котором прибыль равна 0;

Исходные данные могут быть представлены в виде (Рис.41):

 

Рис. 41 Исходные данные

ОБРАЗЕЦ ВЫПОЛНЕНИЯ ЗАДАНИЙ

Задание 1

 

 

Задание 2-3

КРИТЕРИИ ОЦЕНКИ

Все задания выполняются строго по порядку.

За каждое задание студент получает 1 балл.

Оценка «5» ставится при выполнении всех заданий.