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

Вэтом разделе вы научитесь:

sиспользовать функциональные возможности надстроек Пакет анализа и Поиск решения;

sпрогнозировать значения экономических показателей;

sрешать задачи оптимизации.

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

& В состав приложенияExcel входит набор средств анализа данных–

надстройка Пакет анализа, предназначенный для решения сложных стати-

стических и инженерных задач. Средства, которые включены в Пакет анализа,

доступны через команду Анализ данных в меню Сервис. Если она отсутствует в указанном меню, то необходимо ее загрузить командой Сервис/Надстройки,

установив флажок Пакет анализа в открывшемся окне Надстройки.

При выборе в менюСервис пункта Анализ данных, открывается одно-

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

Рис. 5.43 Окно Анализ данных

Для анализа данных с помощью этих инструментов следует указать вход-

ные данные и выбрать параметры.

Диалоговое окно каждого инструмента включает в себя элементы управле-

ния (поля ввода, раскрывающиеся списки, флажки, переключатели и т.п.), ко-

торые задают определенные параметры выполнения.

80

Рассмотрим работу надстройки Пакет анализа на примере инструментов

Корреляция и Скользящее среднее.

Задание 1. Используя инструмент Корреляция, по исходным данным(рис. 5.44) определить степень влияния на объем реализации продукции трех видов расходов: труд, сырье и оборудование.

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

& Инструмент Корреляция используется для определения тесноты связи между исследуемыми показателями. Такая задача возникает, когда необходимо оценить степень влияния некоторых факторов X на исследуемый показатель Y.

Для оценки влияния факторов используются коэффициенты корреляции между показателями Y и X. Для получения надежных оценок должно выпол-

няться условие: k £ n/3, где k – количество факторов, n – количество наблюде-

ний.

Коэффициенты корреляции представляются в виде таблицы, значения ко-

торых лежат в интервале [-1,1]. Положительное значение коэффициента свиде-

тельствует о положительной связи между показателями, .е. при увеличении одного из показателей второй показатель также статистически возрастает. От-

рицательное значение коэффициента свидетельствует об отрицательной связи

81

между показателями, т.е. при увеличении одного показателя второй статисти-

чески уменьшается. Чем ближе абсолютное значение коэффициента 1,к тем теснее связь между показателями.

Связь считается достаточно сильной, если коэффициент корреляции по аб-

солютной величине больше 0.7, и слабой, если меньше 0.4. Если значение ко-

эффициента равно нулю, то связь между показателями отсутствует.

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

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

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

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

·Откройте инструмент Корреляция командой Сервис/Анализ данных/ Кор-

реляция.

· В окне Корреляция (рис. 5.45) задайте:

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

лите на листе Корреляция диапазон A3:D20, содержащий анализируемые данные;

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

телей Группирование выберите – По столбцам;

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

-параметры вывода – активизируйте переключатель Выходной интервал,

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

ячейку F3, соответствующую адресу левой верхней ячейки выходного диа-

пазона;

- нажмите кнопку [ОК]. В результате определяется корреляционная мат-

рица рассматриваемых показателей и размещается на листеКорреляция,

начиная с ячейкиF3. Результаты решения задачи определения степени влияния на объем реализации продукции трех видов расходов с помощью инструмента Корреляция представлены на рис. 5.46. Из корреляционной

82

матрицы следует, что на объем реализации наибольшее влияние оказывает

сырье (коэффициент корреляции равен 0,98).

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

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

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

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

них двух лет происходило значительное сокращение штата торговых работни-

ков. Прогнозные значения сравнить с фактическими данными.

& Инструмент Скользящее среднее используется для расчета значений в прогнозируемом периоде на основе среднего значения переменной для указан-

ного числа предшествующих периодов. Скользящее среднее, в отличие от про-

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

гих процессов.

Базовая линия – это числовое выражение результатов наблюдений на про83

тяжении длительного периода времени. Каждое ее значение – это данные, сня-

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

ними значениями.

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

·В книге Пакет анализа переименуйте Лист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.

· Откройте инструмент Скользящее среднее командой Сервис/Анализ дан-

ных/Скользящее среднее.

· В окне Скользящее среднее (рис. 5.47) задайте:

Рис. 5.47 Окно Скользящее среднее с заданными параметрами

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

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

линии;

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

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

84

выделите мышью на листеСкол_среднее ячейку D1, соответствующую адресу левой верхней ячейки выходного диапазона;

- флажок Вывод графика для вывода диаграммы с графиком фактиче-

ских значений и прогнозом, линией тренда скользящего среднего. Нажми-

те [ОК].

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

стройки, будут смещены на одну строку вверх.

· Сохраните книгу Пакет анализа.

5.5.2 Надстройка Поиск решения

& Надстройка Поиск решения используется для решения задач оптими-

зации. Для таких задач характерно обязательное наличие несколькихальтер-

нативных решений и наличие некоторой оценочной функции для количест-

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

Задача оптимизации в общем виде включает следующие элементы:

· целевая функция – показывает, в каком смысле решение должно быть опти-

мальным. Возможны три вида целевой функции: максимизация, минимизация,

назначение заданного значения.

·ограничения – устанавливают зависимости между переменными, могут быть односторонними и двусторонними, записанными в виде двух односторонних;

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

Важная характеристика задачи оптимизации − ееразмерность, которая определяется числом переменныхn и числом ограниченийm. Необходимым требованием является условие n>m. При n<m задачи решения не имеют. Сис-

тему уравнений, для которых n=m рассматривают как задачу оптимизации,

имеющую одно допустимое решение. Ее можно решать как обычную задачу

85

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

Таким образом, задача имеет оптимальное решение, если она удовлетворя-

ет двум требованиям: имеет более одного решения, т.е. существуют допусти-

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

мое решение должно быть оптимальным, т. е. наилучшим из допустимых.

Надстройка Поиск решенияпозволяет найти оптимальное значение формулы, содержащейся в целевой ячейке. Чтобы получить по формуле, со-

держащейся в целевой ячейке, заданный результат, процедура изменяет значе-

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

Типичной задачей оптимизации является транспортная задача.

Задание 3. Используя надстройку Поиск решения, рассчитать оптимальный план перевозок бензина определенной марки между нефтеперерабатывающими заводами (НПЗ) и автозаправочными станциями (АЗС). Заданы:

1) объемы производства бензина (т):

НПЗ №1 – 10, НПЗ №2 – 14, НПЗ №3 – 17 2) объемы потребления бензина (т):

АЗС №1 – 15, АЗС №2 – 12, АЗС №3 – 8.5, АЗС №4 – 5.5 3) стоимость транспортировки 1 тонны бензина между НПЗ и АЗС (табл. 5.3).

Таблица 5.3

Стоимость транспортировки одной тонны бензина между НПЗ и АЗС, усл. ед.

Пункты производства

Пункты потребления	АЗС №1	АЗС №2	АЗС №3	АЗС №4
НПЗ №1	3	5	7	11
НПЗ №2	1	4	6	3
НПЗ №3	5	8	12	7

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

·Постройте математическую модель задачи.

&Математической модель задачи оптимизации – специальная запись постановки и условий решения задачи оптимизации с использованием понятий

86

математики и математической символики. Применительно к конкретной задаче

оптимизации математическая модель соответствуетматематической поста-

новке данной задачи.

Математическая постановка задачи: определить оптимальный план пе-

ревозок бензина, чтобы удовлетворить потребности всех АЗС, а суммарные за-

траты на транспортировку минимизировать. Математическая постановка задачи требует указания: целевой функции (в данной задаче – суммарные затраты на транспортировку бензина); ограничений (в данной задаче – объемы производ-

ства бензина на каждом НПЗ и потребности в бензине на каждой АЗС).

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

·Переименуйте Лист1, дав ему имя Транспортная задача.

·На листе Транспортная задача для решения задачи создайте таблицы и за-

полните их исходными данными, как показано на рис. 5.48.

Рис. 5.48 Исходные таблицы для решения транспортной задачи

87

· Перейдите в режим отображения формул и задайте формулы в соответствии

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

-сформируйте целевую функцию – в ячейку G5 введите формулу для

расчета суммарных затрат на транспортировку бензина:

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

Данная формула рассчитывает сумму произведений стоимости перевозки от

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

- сформируйте ограничения – в ячейки G11:G13 введите формулы сум-

мирования объемов перевозки с каждого НПЗ и вC14:F14 – формулы сум-

мирования объемов перевозки к каждой АЗС (рис. 5.49).

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

становки задачи оптимизации с учетом исходных данных, приведенных в соз-

данных таблицах.

Таблица 5.4

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

Элемент	Описание
Целевая	Суммарные затраты на транспортировку бензина	должны
функция	быть минимальными, т.е. G5 → min		рав
Ограничения	1) Суммарные поставки бензина на одну АЗС должны-
	няться потребности в бензине на данной АЗС, т.е.
	C14=C15
	D14= D15
	E14=E15
	F14=F15
	2) Суммарные поставки бензина одним НПЗ должны быть
	равны количеству производимого бензина, т.е.
	G11 = H11
	G12 = H12
	G13= H13
Граничные	Значения искомых переменных в оптимальном	решении
условия	должны быть только положительными, т.е.:
	С11:F13>=0

88

Рис. 5.49 Таблицы с формулами целевой функции и ограничений

·Откройте надстройку Поиск решения командой Сервис/Поиск решения.

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

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

новить целевую ячейку и выделите мышью на листе Транспортная за-

дача ячейку G5 (или введите абсолютный адрес ячейки $G$5 вручную);

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

- укажите вид целевой функции– в группе Равной установите переклю-

чатель в положение минимальному значению для нахождения решения с минимальным значением целевой функции;

- задайте диапазон влияющих ячеек, т.е. ячеек, которые должны изме-

няться в процессе поиска решения задачи, – в поле Изменяя ячейки ука-

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

89

Рис. 5.50 Окно Поиск решения с заданными параметрами

· Добавьте ограничения, соответствующие ограничениям исходной постанов-

ки решаемой транспортной задачи(см. табл. 5.4). Для этого нажмите[Доба-

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

- для задания первого ограничения в полеСсылка на ячейку укажите диапазон ячеек левой части неравенства– $C$14:$F$14 (диапазон выде-

ляйте мышью на листеТранспортная задача); из списка в центре окна выберите знак «=» (строгое равенство); в поле Ограничение укажите диа-

пазон ячеек правой части неравенства – $C$15: $F$15 (рис. 5.51);

Рис. 5.51 Окно задания ограничения

- для добавления первого ограничения к параметрам поиска решения на-

жмите кнопку [Добавить], после чего оно появится в списке Ограничения

окна Поиск решения (рис. 5.50);

- аналогично добавьте второе ограничение: $G$11:$G$13= $H$11:$H$13 и

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

90

Примечание. Для изменения или удаления ограничений в списке поляОграничения используются соответственно кнопки [Изменить] и [Удалить].

· Запустите процесс вычисления оптимального плана нажатием кнопки[Вы-

полнить] в окне Поиск решения. После завершения вычисления, найденные оптимальные значения будут вставлены в ячейкиC11:F13 листа Транспорт-

ная задача (рис. 5.52).

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

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

- представление результатов в трех отчетах: Результаты, Устойчивость

и Пределы;

- установите переключатель Сохранить найденное решение и нажмите

[ОК].

91

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

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

Отчет Результаты содержит целевую ячейку, список изменяемых ячеек и ограничений. Этот отчет также содержит информацию для каждого ограниче-

ния о таких параметрах, как состояние и разница.

Отчет Устойчивость информирует, насколько целевая ячейка чувстви-

тельна к изменениям в ограничениях.

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

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

мальные значения плана перевозок бензина(ячейки С11:F13). Найденному ре-

шению соответствует минимальное значение целевой функции – 208,5.

· Сохраните книгу Поиск решения.

Задание 4. Представить преподавателю результаты работы в папкеExcel с

файлами Пакет анализа Поиск решения.

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

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

ческими данными.

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

92

продукции, обеспечивающий предприятию максимальную прибыль. Заданы:

1) на предприятии осуществляется выпуск четырех видов продукции 1,АА2,

А3, А4;

2)для выпуска продукции используют три вида сырья С1, С2, С3;

3)объемы выделенного сырья, нормы расходы сырья и прибыль на единицу продукции при изготовлении каждого вида продукции приведены в табл. 5.5.

					Таблица 5.5
Нормы расхода сырья и прибыль от реализации единицы продукции
Виды	Запасы		Виды продукции
сырья	сырья	А1	А2	А3	А4
С1	35	4	2	2	3
С2	31	1	1	2	3
С3	42	3	1	2	1
Прибыль		14	10	14	11

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

1.Каково назначение надстройки Пакет анализа?

2.Какие основные элементы управления содержат диалоговые окна инстру-

ментов для анализа данных?

3.Каково назначение надстройки Поиск решения?

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

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

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

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

Исходные данные для решения задачи:

1.Фабрика выпускает три вида тканей: I вида, II вида и III вида.

2.Суточное плановое задание составляет не менее90 м ткани I вида, 70 м – II

вида и 60 м – III вида.

93

3.Имеются суточные ресурсы: 780 ед. производственного оборудования, 850

ед. сырья и 790 ед. электроэнергии.

4.Расход суточных ресурсов на один метр каждой ткани выбрать из табл. 5.6

согласно варианту, заданному преподавателем.

5.Цена за 1 м ткани I вида равна 80 у. е., II вида – 70 у. е., III вида – 60 у. е.

				Таблица 5.6
	Расход суточных ресурсов, ед.
Вариант	Ресурсы		Ткани
		I вид	II вид	III вид
1-5	Оборудование	2,3,5,4,3	3,6,5,1,2	4,6,2,4,2
	Сырье	1,5,3,4,3	4,2,3,1,5	5,1,1,2,3
	Электроэнергия	3,2,6,4,3	4,6,1,2,3	1,5,3,4,3
6-10	Оборудование	2,1,3,4,4	1,5,3,4,3	4,2,6,4,2
	Сырье	4,6,1,2,3	4,4,5,3,2	3,6,5,1,2
	Электроэнергия	3,3,2,1,5	1,5,3,4,3	2,1,2,3,5
11-15	Оборудование	4,2,3,1,5	3,3,6,4,1	4,2,3,1,5
	Сырье	3,6,5,1,2	5,1,1,2,3	1,5,3,4,3
	Электроэнергия	3,2,3,1,1	4,4,2,1,5	4,6,1,2,3

94