5.5Работа с надстройками в Excel

5.5.1Надстройка Пакет анализа

Рис. 5.51 Исходные данные для корреляционного анализа

Р е к о м е н д а ц и и п о в ы п о л н е н и ю

·Создайте рабочую книгу Надстройки и сохраните ее в папке Excel.

·Переименуйте Лист1, дав ему имя Корреляция.

·На листе Корреляция создайте таблицу, представленную на рис. 5.51.

Примечание. В данной задаче исследуемым показателемY является Реализация продукции, а факторами, влияющими на показательY, соответственно Расходы на рекламу X1 и Цена продукции X2, число наблюдений 15.

·На вкладке Данные нажмите кнопку[Анализ данных] и выберите в одноименном окне пункт Корреляция.

·В открывшемся окне Корреляция (рис. 5.52) задайте:

102

Рис. 5.52 Окно Корреляция с заданными параметрами

Примечание. При выделении ячеек и диапазонов на рабочем листе в диалоговом окне они автоматически преобразуются в абсолютные ссылки.

-тип расположения данных во входном диапазоне– в группе переключателей Группирование выберите – по строкам;

-флажок Метки в первом столбцедля указания того, что первый столбец исходного диапазона содержит названия строк;

103

Рис. 5.53 Корреляционная матрица

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

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

Р е к о м е н д а ц и и п о в ы п о л н е н и ю

·В книге Надстройки переименуйте Лист2, дав ему имя Скол_среднее.

·Укажите базовую линию объема продаж. Для этого внесите в столбец А,

начиная с ячейки А1, и заканчивая А24, следующие фактические данные по объемам продаж за прошедшие месяцы: 593, 570, 486, 854, 797, 362, 594, 271,

45, 254, 433, 529, 994, 319, 610, 748, 832, 193, 720, 415, 536, 850, 201, 833.

·Составьте сначала прогноз по функции СРЗНАЧ, введя в ячейку 3В

формулу =СРЗНАЧ(А1:АЗ) и затем скопировав ее в ячейки В4:В24.

·На вкладке Данные нажмите кнопку[Анализ данных] и выберите в одноименном окне пункт Скользящее среднее.

·В открывшемся окне Скользящее среднее (рис. 5.54) задайте:

-входные данные – установите курсор в полеВходной интервал и

выделите на листе Скол_среднее диапазон A1:A24, содержащий ряд базовой линии;

-количество месяцев, которые надо включить в подсчет скользящего среднего – в поле Интервал введите число 3.

104

cij – стоимость перевозки тонны бензина из i-го НПЗ до j-ой АЗС; ai – количество производимого бензина i-ым НПЗ;

bj – требуемое количество бензина на j-ой АЗС.

Необходимо определить оптимальный план перевозок, который полностью удовлетворяет спрос всех АЗС в бензине, и при этом суммарные затраты на транспортировку были минимальными. Тогда математическую модель задачи можно записать следующим образом:

Целевая функция – суммарные затраты на транспортировку бензина:

3 4

f(x) = ååcij xij ® min

i=1 j=1

Система ограничений:

4

å x ij = a i – поставка бензина i-ым НПЗ должна равняться его производству;

j =1

3

å x ij = b j – поставка бензина на j-ю АЗС должна равняться ее спросу;

i =1

Граничные условия:

xij ³ 0 – значения оптимального плана должны быть неотрицательными.

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

106

·В книге Надстройки переименуйте Лист3, дав ему имя Транспортная задача.

·На листе Транспортная задача для решения задачи создайте таблицы и заполните их исходными данными, как показано на рис. 5.55.

соответствии с математической постановкой задачи:

- сформируйте целевую функцию – в ячейку G5 введите формулу для расчета суммарных затрат на транспортировку бензина:

=СУММПРОИЗВ(C5:F7;C11:F13)

Данная формула рассчитывает сумму произведений стоимости перевозки от каждого НПЗ к каждой АЗС и соответствующих объемов перевозки.

- сформируйте ограничения – в ячейки G11:G13 введите формулы суммирования объемов перевозки с каждого НПЗ C14:F14и в – формулы суммирования объемов перевозки к каждой АЗС (рис. 5.56).

& В табл. 5.4 представлены основные составляющие математической постановки задачи оптимизации с учетом исходных данных, приведенных в

107

созданных таблицах.

Таблица 5.4

Составляющие математической постановки задачи оптимизации

загрузить ее нужно также, как кнопку [Пакет анализа] (см. теорию к п. 5.5.1).

·В окне Параметры поиска решения (рис. 5.57) выполните следующее:

-укажите ячейку с целевой функцией– установите курсор в поле

108

Оптимизировать целевую функцию и выделите мышью на листе

Транспортная задача ячейку G5;

- укажите критерий оптимальности целевой функции– в группе До

установите переключатель Минимум;

- задайте диапазон влияющих ячеек, представляющих решение задачи,

которые должны изменяться в процессе поиска решения задачи. Для этого в полеИзменяя ячейки переменных укажите адрес диапазона ячеек $C$11:$F$13, выделяя его мышью на листеТранспортная

задача.

· Добавьте ограничения, согласно математической постановке задачи(см.

табл. 5.4). Для этого нажмите [Добавить] и открывшемся окне Добавление ограничения выполните следующее:

- для задания первого ограничения в полеСсылка на ячейки укажите диапазон ячеек левой части неравенства– $C$14:$F$14 (диапазон выделяйте мышью на листеТранспортная задача); из раскрывающегося списка в центре окна выберите знак«=» (строгое равенство); в поле Ограничение укажите диапазон ячеек правой части неравенства – $C$15: $F$15 (рис. 5.58);

109

$H$11:$H$13 и граничное условие: $С$11:$F$13>=0 и нажмите [ОК] в

окне Добавление ограничения.

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

C11:F13 листа Транспортная задача (рис. 5.59).

Рис. 5.59 Результат количественного решения транспортной задачи

·В открывшемся окне Результаты поиска решения (рис. 5.60) установите:

-представление результатов в трех отчетах: в списке Отчеты выделите мышью Результаты, Устойчивость и Пределы;

-переключатель Сохранить найденное решение и нажмите [ОК].

111

Рис.5.60 Окно Результаты поиска решения

Результатом решения рассматриваемой задачи являются найденные

Задания для самостоятельной работы

Задание 1. Используя надстройку Пакет анализа, по данным задания2,

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

112

суммарных эксплуатационных расходах перевезти по каждой из н соответственно не менее300 и 200 ед. груза. Исходные данные указаны в таблице 5.5.

Таблица 5.5

Количество самолетов каждого типа, месячный объем перевозок каждым самолетом на каждой авиалинии и эксплуатационные расходы на

один самолет

оптимизационной задачи?

5.Назовите этапы решения задач с помощью надстройки Поиск решения.

6.Что такое целевая функция и ограничения, как они задаются для решения задачи с помощью надстройки Поиска решения?

Индивидуальные задания

113