Определим диапазон результата.

На листе определим не менее трех пустых строк ниже списка. Эти строки будут использованы в качестве диапазона результата отбора.

1. Скопируйте из списка заголовки фильтруемых столбцов.

2. Вставьте скопированные заголовки столбцов в первой пустой строке диапазона результата

Выберите пункт Фильтр в меню Данные, а затем — команду Расширенный фильтр. На листе появится диалоговое окно:

Чтобы показать результат фильтрации на месте исходного списка, скрыв ненужные строки, установите переключатель Обработка в положение Фильтровать список на месте.

Чтобы скопировать отфильтрованные строки в область листа, определенную как область результата, установите переключатель Обработка в положение Скопировать результат в другое место.

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

Чтобы убрать диалоговое на время выделения одного из этих диапазонов, нажмите соответствующую кнопку свертывания диалогового окна справа от поля, укажите диапазон ячеек, которые следует отобрать в определяемое поле (например, в поле Исходный диапазон) Кнопка разворачивания окна диалога справа от поля позволяет ,выделив диапазон, снова вывести на экран окно Расширенный фильтр.

Аналогичные действия следует повторить для определения двух других полей ( Диапазон условий./ Диапазон результата)

По завершению всех подготовительных действий, нажмите кнопку ОК.

На листе таблицы в области результата получим отобранные по критерию записи базы данных (списка).

Лабораторная работа №5.

Решение задач оптимизации с помощью надстройки «Поиск решения».

Цель работы -изучение методов решения задач оптимизации в приложении Microsoft Excel.

Общие сведения.

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

Для решения такого рода задач необходимо осуществить математическую формализацию задачи. Это означает,что нужно определитьпеременные решения и цель задачи. Затем с помощью этих переменных цель и ограничения на ресурсы описываются в виде формул.

Таким образом формализация задачи состоит из следующих шагов.

Шаг 1. Определение переменных задачи,значения которых нужно получить в пределах существующих ограничений.

Шаг 2. Определение цели и ограничений на ресурсы.

Шаг 3- Описание цели через переменные задачи.

Шаг 4. Описание ограничений через переменные задачи.

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

В лабораторной работе для этих целей используется пакет Поиск решения - оптимизирующая программа, встроенный в Microsoft Excel.

ПримерЗавод-производитель запчастей для автомобилей выпускает два различных типа деталей: Х и У. Завод располагает фондом рабочего времени в 4000 чел.-ч. в неделю. Для производства одной детали типа Х требуется 1 чел.-ч, а для производства одной детали типа У — 2 чел.-ч. Производственные мощности завода позволяют выпускать максимум 2250 деталей типа Х и 1750 деталей типа У в неделю.

Сколько деталей каждого типа следует производить, чтобы максимизировать общий доход за неделю, если доход от производства одной детали типа Х составляет 30 грн., а от производства одной детали типа У — 40 грн.

Постановка задачи

Шаг 1. Идентификация переменных.

Необходимо произвести n деталей типа Х и m деталей типа У в неделю.

Шаг 2.

Цель состоит в максимизации общего дохода за неделю.

Ограничения на процесс производства:

а) фонд рабочего времени — максимально возможный фонд рабочего времени составляет 4000 чел. -ч. в неделю.

6) производственная мощность- оборудование позволяет выпускать не более 2250 деталей типа Х и 1750 типа У в неделю.

Шаг 3. Целевая функция.

Пусть Р — общий доход за неделю

Р = 30 х + 40 у (грн/неделю).

Шаг 4. Ограничения на производственный процесс. Для каждого ограничения на ресурсы, необходимые для производства n деталей типа Х н m деталей типа У в неделю, ниже приведены коэффициенты и соответствующие им максимальные уровни наличных ресурсов.

Требуемый фонд рабочего времени: 1n + 2m <= 4000 чел.-ч. Требуемая производственная мощность: n <= 2250 деталей

m<= 1750 деталей

Решение

Решим задачу , предположив, что завод выпускает по 1000 штук деталей в неделю .

Шаг 1. Определение переменных.

Выделите две ячейки А3 и В3 на листе Excel под значения переменных n и m.

Введите в ячейку А1 - Количество деталей.

В А2,В2- названия этих деталей. В ячейки А3 и В3 занесите 1000 - в дальнейшем значения этий ячеек будут подобраны автоматически.

Шаг 2. Определение цели и ограничений.

Цель состоит вмаксимизации ежедневного дохода

Это целевая функция задачи —количественное соотношение, которое подлежит оптимизации.

Введите в ячейку А5 - Доход. В В5 - формулу для вычисления дохода от производства деталей, количество которых указаны в ячейках А3 и В3.