Вопросы к лабораторной работе 16

• Сформулируйте понятие «массив» MSExcel.

• Сформулируйте правила построения массивов MSExcel.

• Перечислите основные свойства массива.

• Перечислите основные функции MSExcel для работы с матрицами.

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

• Как присвоить начальные значения массиву?

• Как с помощью массивов решить систему линейных уравнений?

• С использованием каких функций можно обратить и транспонировать матрицу?

• Как найти скалярное произведение матриц?

Лабораторная работа 17

ЦЕЛЬ: освоение приемов решения задач оптимизации (линейного и нелинейного программирования).

Для решения задач оптимизации используется инструментДанные анализ-Поиск решения. Предварительно необходимо, как говорят, поставить задачу, то есть записать на бумаге систему уравнений или неравенств (ограничений) и критерии оптимальности. Только после этого стоит приступать к ее решению. При этом можно выделить несколько этапов:

• для каждой переменной (неизвестной) следует отвести одну ячейку определить для нее имя. Желательно разместить эти ячейки рядом, а в клетках сверху записать их имена. Тогда процедура определения имени через пункт Формулы - Определение имени - Присвоить Имябудет проще, а анализ исходных данных – более наглядным. Для еще большей наглядности можно закрасить ячейки каким-либо цветом. В этих ячейках будет размещен результат решения задачи. Проблема здесь состоит в том, что искомые переменные в математике чаще всего называют и т.д. Многие стремятся дать такие же имена ячейкам, где они будут храниться. Однако сделать это нельзя, так как в таблице существуют ячейки с адресами X1, X2, X3. Поэтому следует использовать другие имена, например, XX1, XX2, XX3 и так далее, или буквы русского алфавита (что имеет свои достоинства и недостатки). Если же решается конкретная экономическая задача, где в качестве неизвестных выступают, например, виды продукции, то неизвестным можно присваивать имена в виде сокращенных названий видов продукции;

• отвести ячейку для критерия оптимальности и записать его в виде формулы, ссылаясь не на адреса, а на имена ячеек, определенных на предыдущем этапе, например: =xx1+4*xx2-3*xx3+2*xx4-xx5. Здесь в качестве имен использованы латинские буквы. Ячейке, содержащей критерий оптимальности, также можно присвоить имя. Особенно это удобно при решении экономической задачи. Например, ячейка может называться “Прибыль” или “затраты” и т.д., то есть то, что подлежит максимизации или минимизации;

• отвести на каждое ограничение дону ячейку (желательно разместив их друг под другом). В эти ячейки следует ввести левые части ограничений в виде формул. Знак сравнения и правая часть ограничения задаются позже. Например: =xx1+3*xx2+xx3-xx4-2*xx5.

• Вызвать инструментДанные – Анализ – Поиск решения. При этом на экран выводится окно, в котором будет нужно:

  1. указать ячейку, где находится критерий оптимальности;

  2. указать диапазон ячеек, где будет сформирован результат (ячейки переменных);

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

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

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

  6. нажать кнопкуВыполнить;

  7. оформить полученное решение в виде отчета. При решении реальных задач целесообразно сформировать и другие виды отчетов (они перечислены в меню). Каждый отчет записывается на новый лист;

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

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

1. В справке найдите раздел « Поиск решения» и изучите его. Потратьте на это не менее 10-15 минут. Скорее всего, вам не все будет понятно. Старайтесь уловить общий смысл изучаемого материала.

2. На чистом листе сформулируйте исходные данные для решения следующей задачи:

Например, ячейкам А2, В2, С2, D2, E2присвойте имена переменных, в нашем случае это хх1, хх2, хх3, хх4, хх5.

В ячейку А4введите ограничение =4*хх1+3*хх2+хх3+2*хх4+12*хх5 (левая часть первого уравнения),

в ячейку В4 - =13 (правая часть уравнения).

В ячейку А5 введите ограничение =хх2-хх3+2*хх4+8*хх5 (левая часть второго уравнения),

в ячейку В5 - =7 (правая часть уравнения).

В ячейке F2 будет находиться результат оптимальности (целевая функция =хх1+4*хх2-3*хх3+2*хх4-хх5)

Войдите Данные – Анализ - Поиск решения и в окне Параметры поиска решениявыполнить пункты с 1) по 4) и найдите решение

Ответ: X=(0,0,1,0,1) при F=-4.

3. Решите более сложную задачу.

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

Если вы решите задачу верно, то значение целевой функции F=53,125.

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