1. Используя Команду «Подбор параметра», определить, при каком значении аргумента x функция y (см. по варианту задание 8 лабораторной работы №8) возвратит указанное значение (табл. 9.3).
Таблица 9.3
Исходные данные для выполнения задания 1
| Вариант | ||||||||||||
| Значение функции y | 2,5 | -8 | 7,06 | 0,5 | 3,2 | 1,2 | -2 | 7,85 | 1,5 | 9,3 | 5,6 |
2. Используя надстройку "Поиск решения", решите задачу согласно варианту.
2.1. Трикотажная фабрика использует для производства свитеров и кофточек чистую шерсть, силон и нитрон, запасы, которых составляют соответственно 900, 400 и 300 кг. Количество пряжи каждого вида (в кг), необходимой для изготовления 1 изделия, а также прибыль, получаемая от его реализации, приведены в табл. 9.4. Определите план выпуска изделий, максимизирующий прибыль.
Таблица 9.4
Исходные данные для выполнения задания 2.1
| Вид сырья | Затраты пряжи на 1 изделие | |
| Свитера | Кофточки | |
| Шерсть Силон Нитрон | 0,4 0,2 0,1 | 0,2 0,1 0,1 |
| Прибыль | 0,6 | 0,5 |
2.2. При подкормке посева нужно внести на 1 га почвы не менее 8 ед. химического вещества вида А, 21 ед. — вещества вида Б, 16 ед. — вещества вида В. Сельскохозяйственное предприятие закупает комбинированные удобрения двух видов (I и II). В табл. 9.5 указаны содержание химических веществ и цена на единицу массы каждого вида удобрений. Минимизируйте расходы по закупке необходимого количества удобрений.
Таблица 9.5
Исходные данные для выполнения задания 2.2
| Химическое вещество | Содержание вещества в единице массы удобрения | |
| I | II | |
| А Б В | ||
| Цена |
2.3. Изделия четырех типов проходят последовательную обработку на двух станках. Время обработки одного изделия каждого типа на каждом из станков приведено в табл. 9.6.
Таблица 9.6
Исходные данные для выполнения задания 2.3
| Станок | Время обработки одного изделия, ч | |||
| Тип 1 | Тип 2 | Тип 3 | Тип 4 | |
Затраты на производство одного изделия каждого типа определяются как величины, прямо пропорциональные времени использования станков (в машино-часах). Стоимость Машино-часа составляет 10 и 15 долларов для станков 1 и 2 соответственно. Допустимое время использования станков для обработки изделий всех типов ограничено следующими значениями: 500 машино-часов для станка 1 и 380 машино-часов для станка 2. Цены изделий типов 1, 2, 3 и 4 равны 65, 70, 55 и 45 долларов соответственно. Составить план производства, максимизирующий чистую прибыль.
2.4. Цех выпускает три вида изделий (табл. 9.7). Суточные ресурсы: 780 ед. производственного оборудования (станки, машины и т. п.), 850 ед. сырья (металл и т.п.) и 790 ед. электроэнергии. Их расход на одно изделие указан в таблице. Стоимость изделия I — 8 ден. ед., изделия II — 7 ден. ед., изделия III — 6 ден. ед. Сколько надо производить изделий каждого вида, чтобы стоимость выпущенной продукции была максимальной?
Таблица 9.7
Исходные данные для выполнения задания 2.4
| Ресурсы | Расход ресурсов на изделие | ||
| I | II | III | |
| Оборудование Сырье Электроэнергия |
2.5. Найти оптимальное сочетание посевов трех культур: пшеницы, гречихи и картофеля (табл. 9.8). Эффективность возделывания названных культур (в расчете на 1 га) характеризуется показателями, значения которых приведены в таблице. Производственные ресурсы: 6000 га пашни, 5000 чел.-дней труда механизаторов, 9000 чел.-дней ручного труда. Критерий оптимальности — максимум прибыли.
Таблица 9.8
Исходные данные для выполнения задания 2.5
| Показатель | Пшеница | Гречиха | Картофель |
| Урожайность, ц Затраты труда механизаторов, чел.-дней Затраты ручного труда, чел.-дней | 0,5 0,5 | 0,5 | |
| Прибыль от реализации 1 ц продукции, ден. ед. |
2.6. Для изготовления обуви четырех моделей на фабрике используются два сорта кожи. Ресурсы рабочей силы и материала, затраты труда и материала для изготовления каждой пары обуви, а также прибыль от реализации единицы продукции приведены в табл. 9.9. Составить план выпуска обуви по ассортименту, максимизирующий прибыль.
Таблица 9.9
Исходные данные для выполнения задания 2.6
| Ресурсы | Запас ресурса | Затраты ресурсов на одну пару обуви по моделям | |||
| Рабочее время, чел.-ч | |||||
| Кожа 1-го сорта | |||||
| Кожа 2-го сорта | |||||
| Прибыль, ден.ед. |
2.7. Из листов стального проката необходимо выкроить 800 заготовок А размером 4х5 м и 400 заготовок Б размером 2х3 м. Раскрой можно производить четырьмя способами. В табл. 9.10 указано количество заготовок каждого типа, получаемых при раскрое одного листа различными способами. Составьте такой план раскроя, чтобы расход материала был минимальным.
Таблица 9.10
Исходные данные для выполнения задания 2.7
| Заготовка | Количество заготовок при способе раскроя | |||
| I | II | III | IV | |
| А Б |
2.8. Предприятие может выпускать продукцию П1, П2, П3 и П4, сбыт любого количества которой обеспечен. При производстве продукции расходуются различные ресурсы, запасы которых и удельные затраты приведены в табл. 9.11, там же указана и цена продукции. Найти оптимальный план выпуска продукции, максимизирующий выручку предприятия от реализованной продукции.
Таблица 9.11
Исходные данные для выполнения задания 2.8
| Ресурсы | Запас ресурса | Нормы расхода на единицу продукции | |||
| П1 | П2 | П3 | П4 | ||
| Трудовые ресурсы, чел.-ч Полуфабрикаты, кг Станочное оборудование, станко-ч | |||||
| Цена единицы продукции, ден. ед |
2.9. На предприятии освоены четыре технологии производства основной продукции. В табл. 9.12 указаны запасы потребляемых ресурсов, затраты их в течение месяца и объемы выпуска готовой продукции при каждой технологии за тот же период. Установите такое время работы предприятия по каждой технологии, при котором выпуск продукции будет максимальным, а расход ресурсов не превысит их наличия.
Таблица 9.12
Исходные данные для выполнения задания 2.9
| Ресурсы | Запас ресурса | Расход ресурса при технологии | |||
| I | II | III | IV | ||
| Р1 Р2 Р3 | |||||
| Объем выпуска продукции |
2.10. Фирма производит два продукта А и В, рынок сбыта которых неограничен. Каждый продукт должен быть обработан каждой машиной I, II, III. Время обработки для каждого из изделий приведено в табл.9.13.
Таблица 9.13
Исходные данные для выполнения задания 2.10
| I | II | III | |
| А | 0,5 | 0,4 | 0,2 |
| В | 0,25 | 0,3 | 0,4 |
Время работы машин I, II, III соответственно 40, 36 и 36 часов в неделю. Прибыль от изделий А и В составляет соответственно 5 и 3 долл. Фирме надо определить недельные нормы выпуска изделий А и В, максимизирующие прибыль.
2.11. Торговое предприятие реализует товары Т1, Т2 и Т3, используя при этом площади торговых залов и время обслуживающего персонала. Затраты указанных ресурсов на продажу одной партии товара каждого вида, их объемы и прибыль, получаемая от реализации каждой партии товара, приведены в табл. 9.14. Найдите оптимальную структуру товарооборота, обеспечивающую предприятию максимальную прибыль.
Таблица 9.14
Исходные данные для выполнения задания 2.11
| Ресурсы | Запас ресурса | Затраты ресурсов по товарам | ||
| Т1 | Т2 | Т3 | ||
| Время, чел.-ч Площадь, м2 | 0,5 0,1 | 0,7 0,3 | 0,6 0,2 | |
| Прибыль, ден. ед. |
2.12. Фирма производит три вида продукции (А, В, С), для выпуска каждого требуется определенное время обработки на всех четырех устройствах I, II, III, IV (табл. 9.15).
Таблица 9.15
Исходные данные для выполнения задания 2.12
| Вид продукции | Время обработки, ч | Прибыль, долл. | |||
| I | II | III | IV | ||
| А | |||||
| В | |||||
| С |
Пусть время работы на устройствах Соответственно 84, 42,21 и 42 часа. Определите, какую продукцию и в каких количествах необходимо производить для максимизации прибыли. (Рынок сбыта для каждого продукта неограничен).
Контрольные вопросы
1. Назначение команды «Подбор параметра».
2. Назначение и параметры надстройки «Поиск решения».
3. Какие задачи можно решать с помощью надстройки «Поиск решения»? Какими свойствами они обладают.
4. Понятие «целевая функция» в задачах оптимизации.
5. Задание ограничений в задачах оптимизации.
Лабораторная работа №10
Работа с базами данных
Цель работы: изучить инструменты работы с базами данных MS Excel; приобрести навыки по созданию списков в MS Excel их форматированию, фильтрации, построению сводных таблиц, подведению итогов и анализу данных.
Методические указания
Обработка списков данных. Основные понятия.
MS Excel имеет развитые инструментальные средства для организации встроенных, расположенных непосредственно на рабочем листе, баз данных с помощью списков. Список - это совокупность, обладающая свойством однородности структуры. Обычно список представлен в виде двумерной таблицы. Информация, представляющая единое целое и занимающая одну строку таблицы, называется записью. Одни и те же поля для различных записей предназначаются для данных одного типа. Столбцам таблицы присваиваются уникальные имена полей, которые заносятся в первую строку таблицы. Эта строка называется строкой заголовка списка.
Диапазоном базы данных называют область на рабочем листе, в которой находится таблица, выступающая в роли базы данных.
Диапазоном извлеченных значений называют область рабочего листа, в которую помещаются значения, полученные из списка в результате поиска, фильтрации и т.д.
Диапазоном критериев называют область рабочего листа, в которую помещаются критерии поиска.
