Практическая работа №6 по дисциплине "ИТ в ПД"

Практическая работа №6

Сводные таблицы. Решение оптимизационных задач

Цель работы:

­         научиться создавать сводные таблицы, познакомиться с этапами решения оптимизационных задач, овладеть навыками составления математической модели задачи нахождения оптимального плана выпуска продукции и ее решения в среде ЭТ MS Excel с помощью надстройки «Поиск решения».

Время выполнения работы  2 часа.

Оборудование, приборы, аппаратура, материалы: персональный компьютер, MS Excel.

Ход выполнения работы:

Задание №1

А) Заполнить таблицу, занеся недостающие данные, рассчитав % удержания по следующему правилу:

·         При количестве иждивенцев более 3-х – 0%

·         При трех – 5%

·         При двух – 10%

·         При одном 12%

·         Если нет – 14%

Расчет оформить с помощью вложенных функций – ЕСЛИ.

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

Б)  Заполните таблицу, создав ее на листе Январь.  Скопируйте таблицу на три других листа, назвав их Февраль, Март. Измените данные второго столбца, на каждом листе (по вашему усмотрению). На четвертом листе создайте макет таблицы сводных показателей (Продано и выручка за квартал 1)

Заполните таблицу, суммируя данные, находящиеся в соответствующих ячейках листов показателей Январь-Март, используя формулу связи. На пятом листе (Продано и выручка за квартал 1) получите аналогичную таблицу, но с помощью консолидации

Задание №2

Постановка задачи. Пусть предприятие располагает запасами сырья трех видов – цемент, щебень и арматура в количествах b₁=18, b₂=120 и b₃= 42 условных единиц соответственно. Из этого сырья может быть изготовлено два вида изделий – плиты перекрытия и фундаментные блоки. Известны так же значения а_ij – количество единиц i-го вида сырья, идущего на изготовление единицы j-го изделия и с_j – доход, получаемый от реализации одной единицы изделия каждого вида (i=1,2,3; j=1,2). Все указанные величины представлены в табл. 1.

Таблица 1. Данные к задаче составления оптимального плана

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

Порядок выполнения задания №2

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

2. На следующем листе, с именем «Оптимальный план», создайте таблицу, подобную таблице математической постановки задачи. Таблица отличается от таблицы 1 наличием столбца «Расход сырья». В него будут занесены левые части ограничений по запасам сырья (см. пункт 3.1) и в результате решения рассматриваемой задачи будут найдены фактические расходы сырья каждого вида. Добавьте столбец «Остаток сырья» для занесения в ячейки столбца соответствующих формул.

3. Создайте вторую таблицу, указав в ней выпускаемые изделия и переменные математической модели. В ячейках Е10:F10 поместите нулевые (начальные) значения искомых переменных х₁ и х₂.

4. В ячейку F12 введите формулу целевой функции, которая для решаемой задачи имеет вид = E6*E10+F6*F10. Завершив ввод нажатием клавиши Enter, получим в ячейке F12 нулевое значение, т.к. пока равны нулю значения переменные х₁ и х₂.

5. Введите формулу =E3*E10+F3*F10 для ограничения по цементу в ячейку С3. Завершив ввод нажатием клавиши Enter, получим в ячейке С3 нулевое значение, т.к. пока равны нулю переменные х₁ и х₂. Скопируйте эту формулу, автозаполнением, в ячейки С4 и С5, предварительно заменив относительную ссылку на ячейки Е10 и F10 на абсолютную при помощи клавиши F4. При этом формула примет вид =E3*$E$10+F3*$F$10, а в ячейках С4 и С5 снова получим нулевые значения. В ячейку D3 занесите формулу вычисления остатков сырья первого вида =B3− C3 и скопируйте ее автозаполнением в ячейки D4 и D5.

6. Наберите команду Данные → Поиск решения. В появившемся диалоговом окне надстройки «Поиск решения» необходимо выполнить следующие установки:

6.1. Заполните поле «Установить целевую ячейку». В зависимости от решаемой задачи, можно выбрать поиск наименьшего или наибольшего значения для целевой ячейки или же установить в ней конкретное числовое значение. Для рассматриваемой задачи выполните ссылку на ячейку F12, где записана формула целевой функции.

6.2. Установите радиокнопку «Равной максимальному значению».

6.2. Выполните ссылки на изменяемые ячейки Е10 и F10, в которые помещены нулевые начальные значения искомых переменных х₁ и х₂. Изменяемые ячейки – это те ячейки, значения в которых будут подбираться так, чтобы оптимизировать результат в целевой ячейке. Для надстройки «Поиск решения» можно указать до 200 изменяемых ячеек. К ним предъявляются два основных требования: они не должны содержать формул и изменение их значений должно приводить к изменению результата в целевой ячейке, т.е. целевая ячейка должна быть зависима от изменяемых.

6.3. Введите ограничения по запасам сырья и естественные условия неотрицательности переменных х₁ и х₂, для этого:

а) щелкните по кнопке «Добавить» диалогового окна и в появившемся окне «Добавление ограничения» выполните следующие установки:

Задание таких ограничений означает, что расход сырья каждого вида на выполнение производственной программы не должен превышать его запаса на предприятии. Щелчок по кнопке ОК приводит к закрытию диалогового окна «Изменение ограничения», при этом само условие заносится в раздел «Ограничения:» диалогового окна надстройки «Поиск решения».

б) ещё раз щелкните по кнопке «Добавить» диалогового окна «Поиск решения» и в появившемся окне «Добавление ограничения» выполните следующие установки:

Задание таких условий обеспечивает неотрицательность переменных. Щелкните по кнопке ОК – все ограничения занесены, и диалоговое окно надстройки «Поиск решения» примет вид:

7. Щелкните по кнопке «Выполнить». Если математическая модель задачи составлена верно и решение найдено, то появится диалоговое окно:

Щелчок по кнопке ОК позволяет сохранить найденное оптимальное решение, имеющее для нашей задачи следующий вид:

Проанализируем полученное с помощью ЭТ MS Excel оптимальное решение и сделаем выводы.

Выводы. Анализ полученного решения показавает, что для получения максимальной прибыли в 32 тыс. рублей предприятию необходимо выпустить 14 фундаментных блока и 1,3 плиты перекрытия. При этом цемент и арматура будут израсходованы полностью, а остаток щебня составит 44,6 м³.

8. Добавьте для переменных х₁ и х₂ условие целочисленности, “запрещающее” выпуск не целых изделий:

При этом полученное оптимальное решение примет вид:

Проанализируем целочисленное решение и сделаем выводы.

Выводы. Анализ решения показывает, что прибыль предприятия уменьшилась на одну тысячу рублей, составив 31 тыс. рублей. План выпуска – 14-ть фундаментных блоков и одна плита перекрытия. При этом запас арматуры израсходован полностью, а остатки цемента и щебня составили 1 и 53 условных единиц соответственно.

9. Задайте дополнительное условие об обязательной поставке плит перекрытия в количестве 3-х штук:

Тогда соответствующее оптимальное решение примет вид:

Проанализируем полученное решение и сделаем выводы.

Выводы. Суммарная прибыль предприятия составила 27 тыс. рублей при плане выпуска 3 плиты перекрытия и 9 фундаментных блоков. При этом цемент использован полностью, а остатки щебня и арматуры составили 18 и 15 условных единиц соответственно.

10. Сохраните результаты вычислений в Своей папке.

Контрольные вопросы

1.      Назовите способы создания сводных таблиц.

2.      Что такое консолидация данных?

3.      Как пользоваться мастером сводных таблиц?

4.      Назовите этапы решения оптимизационных задач?

5.      Что такое  целевая функция?

Используемые источники:

1.      Михеева Е.В., Информационные технологии в профессиональной деятельности. – М.: Издательский центр «Академия», 2012.

2.      Михеева Е.В., Практикум по Информационным технологиям в профессиональной деятельности. – М.: Издательский центр «Академия», 2007.

Скачано с www.znanio.ru