Рекомендации по использованию средства анализа данных ms Excel "Таблицы подстановки"

С использованием средства анализа данных MS Excel "Таблицы подстановки" можно оценить влияние нескольких параметров на некоторую величину. Создание таблицы подстановки осуществляется с помощью команды Данные / Таблица подстановки.

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

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

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

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

1. Выделить диапазон ячеек со списком входных данных и диапазон ячеек с формулами.

2. Выберите команду Данные / Таблица подстановки.

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

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

5. После ввода адресов параметров нажать на кнопку ОК. В результате будет создана таблица подстановки.

Ход работы:

Для различных вариантов построить математическую модель процесса, обеспечивающую максимум выручки: IT-компания выпускает 6 видов программных продуктов. Время работы, расход ресурсов, оплата, а также объём трудовых ресурсов в предстоящем временном периоде представлены в каждом из нижеприведённых вариантов (см. индивидуальные задания).

Пример выполнения задания:

IT-продукт	1	2	3	4	5	6	Ресурсы
Время изготовления (часов)	6	7	9	10	11	14	504
Труд, чел./ч	15	13	11	9	7	6	684
Цена, тыс./грн.	67,2	56,3	52,1	51,2	40,3	41,6

Математическая постановка задачи

Целевая функция:

6 7,2*х1+56,3*х2+52,1*х3+51,2*х4+40,3*х5+41,6*х6 max

Набор ограничений:

6х1+7х2+9х3+10х4+11х5+14х6<=504,

15x1+13x2+11x3+9x4+7x5+6x6<=684.

Для решения задач линейного программирования в Microsoft Excel подключите к Excel надстройку «Поиск решения» командой Параметры|Надстройки: выберите Поиск решения и щелкните на кнопке ОК.

Для решения задачи нужно подготовить исходную таблицу на рабочем листе Microsoft Excel. Она может выглядеть, например, так:

В данном случае ячейки В6…В11 пусты, в них Excel позже разместит оптимальное базовое решение. В ячейки I1 и I2 нужно занести формулы, соответствующие ограничениям линейной модели. Эти формулы будут выглядеть так:

=A1*B6+B1*B7+C1*B8+D1*B9+E1*B10+F1*B11

=A2*B6+B2*B7+C2*B8+D2*B9+E2*B10+F2*B11

В ячейку I3 нужно занести формулу, соответствующую целевой функции, а именно:

=A3*B6+B3*B7+C3*B8+D3*B9+E3*B10+F3*B11

Теперь выполните команду Данные|Поиск решения:

В данном случае целевой ячейкой является I3, она стремится к максимальному решению.

Изменяемые ячейки: В6… и В11.

Теперь нужно добавить ограничения. Их будет — собственно ограничения линейной модели (их 2) и ограничения, касающиеся переменных B6:B11 — они должны быть  0 (6 ограничений) и целочисленными (еще 6 условий). Чтобы добавить ограничения, нажмите кнопку Добавить.

Ограничения, касающиеся линейной модели, показаны на следующем рисунке:

Вот что получится в окне «Поиск решения» после щелчка на кнопке Выполнить:

Вывод: максимальную прибыль 3456 тыс. грн. обеспечивает выпуск продукции Х1=24 и х4= 36.