ПРАКТИЧЕСКИЕ ЗАДАНИЯ

ПРАКТИЧЕСКАЯ РАБОТА №5

Анализ данных в таблицах Excel

ПРАКТИЧЕСКИЕ ЗАДАНИЯ

1. Скопировать файл «Организация_14».

2. В таблицу Список_сотрудников добавить столбец «Премия к 8 Марта». Ввести в ячейке столбца 5000 для штатных сотрудниц, 2000 – женщин-совместителей. Значения 5000 и 2000 ввести в отдельные ячейки и в формулах указывать ссылки на эти ячейки.

3. Определить общую сумму премий.

4. Определить, при какой величине премии для штатных сотрудниц общая сумма премий не превысит 80000.

5. На отдельном рабочем листе в какую-либо ячейку ввести величину сумму дохода за год (произвольную).

Подсчитать величину налога с общей суммы доходов. Схема расчета налога:

- доход до 50000 – налог 12% от дохода;

- доход до 80000 – налог 6000 + 20% от суммы, превышающей 50000;

- свыше 80000 – налог 12000 + 30% от суммы, превышающей 80000.

6. Подсчитать, при какой сумме дохода налог составит 4000 руб.

 

7. Создать рабочую книгу «Поиск решения».

 

 

8. Задача Линейная модель

Завод «Лакокраска» производит два типа краски: Алую и Белую.

Производственные мощности позволяют выпускать в месяц не более 500тонн краски суммарно. Одна тонна Алой краски приносит прибыли в среднем 2000руб. (от 1500 до 2300 руб), а одна тонна Белой краски – 2500 руб. ( от 2100 до 3000 руб).

Отдел маркетинга требует, чтобы Алой краски за месяц произвели не менее 200 тонн, поскольку уже есть договора на такое количество, а Белую краску нельзя производить более 150 тонн, поскольку большее количество трудно реализовать.

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

Табл. 1

Сырье	Алая краска, т	Белая краска, т	Месячный запас, т
Сырье 1	0,05	0,10
Сырье 2	0,07	0,08
Сырье 3	0,04	0,07

 

Построим математическую модель.

Суммарная прибыль при производстве Х1тонн Алой каски при прибыли С1=2000 р/тонна и Х2 Белой краски при прибыли С2= 2500р/тонна составит:

Суммарная прибыль Z = 2000*Х1+2500*Х2 (руб) Это – целевая функция, которую надо максимизировать.

Теперь запишем ограничения:

1.Суммарный объем производства краски не должен превышать 500 тонн: Х1+Х2 <=500

2.Маркетинговые требования:Х1>=200 и Х2<=150

3. Ограничения на сырье (табл. 1). Таким образом, на производство Х1тонн Алой краски и Х2 Белой краски потребуется 0,05*Х1+0,1*Х2 тонн сырья 1. Эта величина не должна превышать 50 тонн. Отсюда получаем значение: 0,05*Х1+0,10*Х2 <=50.Аналогично с сырьем 2 и 3:

0,07*Х1+0,08*Х2 <=30

0,04*Х1+0,07*Х2 <=25

4.Еще одно ограничение, что Х1 иХ2 не должны быть отрицательными: Х1>=0; Х2>=0. Поскольку в п.2 мы уже писали, что Х1>=200,то неравенствоХ1>=0 исключаем.

Таким образом:

Максимизировать Z = 2000*Х1+2500*Х2

Х1>=200;

Х2<=150;

0,05*Х1+0,10*Х2 <=50.

0,07*Х1+0,08*Х2 <=30

0,04*Х1+0,07*Х2 <=25

Начинаем максимизировать прибыль предприятия от производства названных красок с помощью Excel.

В ячейки В4 и С4 вначале ставим произвольные значения (100 тонн).

В ячейку D8 вставляем нашу целевую формулу:

B8*B4+C8*C4

В диапазоне В11:С17 записаны коэффициенты функций ограничений, в диапазоне D11:D17 вычисляются значения левых частей ограничений, в диапазоне Е11:Е17 записаны знаки неравенств ограничений, в F11:F17 – значения правых частей ограничений.

 

Произведем вычисления левых частей целевого уравнения в ячейках D11:D17:

=СУММПРОИЗВ($B$4:$C$4;B11:C11)

Приступим к Поиску Решения: Данные- Анализ – Поиск решения.

 

 

 

 

 

 

 

 

 

 

 

 

9. Решить следующую задачу:

каждая корова должна получать в сутки не менее 9 единиц белков, не менее 8 единиц жиров, не менее 12 единиц углеводов; в хозяйстве используются два вида корма; содержание питательных веществ в каждом виде корма и стоимость 1 кг корма представлено в таблице на Рис. 5. 6. Требуется составить дневной рацион коровы нужной питательности при минимальной стоимости.

Питательные вещества	кол-во единиц пит. веществ в 1 кг корма
корм 1	корм 2
белки
жиры
углеводы
Ст-ть 1 кг корма

Рис. 5. 6

10.
Решить задачу: кондитерское предприятие выпускает 4 вида печенья; на складе имеется определенный запас продуктов, используемых в каждом виде печенья; определить, при каком объеме выпуска каждого вида печенья предприятие получит максимальную прибыль. Данные для задачи представлены на Рис. 5. 7.

Рис. 5. 7

11.
Решить задачу: общая площадь посевных площадей фермерского хозяйства – 300 га; хозяйство выращивает картофель, капусту, свения урожайности, ресурсов для выращивания каждой культуры, лимита по каждой культуре, стоимости выращивания единицы каждой культуры, цены единицы продукции задать самостоятельно.

Рис. 5. 8

 

12. Рассчитать ежемесячные выплаты по кредиту в 45000 руб, выданному на 5 лет, для различных значений процентной ставки.

13. Рассчитать ежемесячные выплаты по кредиту в 45000 руб для различных значений процентной ставки и разных сроках выплаты кредита.

14. Рассчитать варианты сроков выплаты кредита в 45000 руб для разных вариантов процентной ставки при выплате по кредиту 800 руб ежемесячно.

15. Рассчитать варианты сроков выплаты кредита в 45000 руб для разных вариантов процентной ставки и значений ежемесячной выплаты.

 

16. Создать рабочую книгу «Сводные таблицы». Скопировать в нее таблицу из рабочей книги Pr_sv_2.

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

18. Подсчитать количество знаков и количество страниц каждого автора для каждой главы.

19. Изменить структуру первой сводной таблицы, добавив в нее в качестве поля страниц Дату.

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

21. В первой сводной таблице изменить функцию на Максимум.

22. Подсчитать количество знаков в материалах каждой главы, подготовленных к каждому значению даты. Выполнить группировку данных по полю Дата.

23. Изменить несколько значений поля Знаки в исходной таблице. Обновить значения в сводной таблице.

24. На новый рабочий лист скопировать таблицу из файла Организация.

25. Подсчитать распределение суммарного оклада по должностям и полу сотрудников.

26. Определить максимальный и минимальный стаж для сотрудников каждой должности и пола.

27. Подсчитать суммарный оклад для мужчин и для женщин. Обеспечить возможность просмотра данных отдельно по каждой должности.

28. Подсчитать количество сотрудников одинакового возраста на каждой должности. Выполнить группировку данных по годам рождения. Сформировать группы: 30-е годы, 40-е годы, 50-е годы и т.д.

 

29. Создать рабочую книгу «Консолидация». Скопировать в нее таблицу с Листа1 рабочей книги Exc_3. Присвоить листу имя «2005 год».

30. Скопировать таблицу «Расход» на 2 листа. Присвоить листам имена «2006 год», «2007 год». Изменить некоторые данные на листах «2006 год», «2007 год».

31. На новом листе выполнить консолидацию по позиции таблиц на листах «2005 год», «2006 год», «2007 год».

32. Удалить одну из ссылок. Пересчитать результат консолидации.

33. Добавить ссылку. Пересчитать результат Консолидации.

34. Скопировать на новый лист книги Консолидация таблицу из книги Pr_sv_3. Заменить названия месяцев: апр – на янв, май – на фев, июн – мар. Присвоить листу имя Квартал_1.

35. Скопировать таблицу на 3 листа. Присвоить листам имена Квартал_2, Квартал_3, Квартал_4. Изменить названия месяцев.

36. На новом листе выполнить консолидацию по категории таблиц на листах Квартал_1, … Квартал_4.

37. На новом листе выполнить консолидацию для категорий Книга, Видео.

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