LINEBURG


<< Пред. стр.

страница 55
(всего 83)

ОГЛАВЛЕНИЕ

След. стр. >>


$ -17Е -500
-100 -125 -150 -20С -25Q
i
О.
18%
Л
С
20%
24%

Рис. 6.15. Таблица подстановки с двумя параметрами


ПРИМЕЧАНИЕ
Ячейка 1 соответствует ячейке подстановки для Параметра 1 (подстановка по строкам),
ячейка 2 — соответственно для Параметра 2 (подстановка по столбцам). Эти ячейки могут
располагаться в произвольном месте на том же самом или даже другом листе. В данном
случае используются ячейки М10 и М11 соответственно для Параметра 1 и Параметра 2.


4. На пересечении строки и столбца, содержащих значения параметров — ячей­
ка М12, ввести формулу для вычисления БЗ.
5. Использовать ссылку на ячейки подстановки:
=БЗ(М10/12; 3*12;МП;-1000:0)
6. Выделить блок ячеек, начиная с ячейки с формулой. Блок ячеек включает
все строки и столбцы со значениями параметров.
7. Выполнить команду меню Данные • Таблица подстановки.
Появляется диалоговое окно Таблица подстановки для указания адреса ячей­
ки подстановки — Подставлять значения по столбцам в М11, Подставлять зна­
чения по строкам в М10. Нажать кнопку ОК.
8. В результате формируется массив формул вида {=ТАБПИцА(М11 ;М10)}.
9. Закрыть файл с сохранением с помощью команды меню Файл • Закрыть.

Информационная технология поиска решения
Microsoft Excel обеспечивает решение задач линейного и нелинейного програм­
мирования ограниченной размерности. Для реализации оптимизационных рас­
четов в Microsoft Excel необходимо установить надстройку Поиск решения с по­
мощью команды меню Сервис • Надстройки и правильно подготовить данные
оптимизационной модели на листе. Модель задачи задается в диалоговом окне
Поиск решения. Модель использует целевую функцию, которая записывается в
виде формулы в отдельной ячейке. Для целевой функции указывается: макси­
мизация, минимизация или равенство фиксированному значению. В процессе
поиска решения изменяются значения в указанных ячейках, соответствующих
переменным, при соблюдении ограничений.
457
Среда моделирования Microsoft Excel


Дополнительные настройки оптимизации выполняются в диалоговом окне Па­
раметр поиска решения. В частности, можно ограничить время выполнения по­
иска решения и выполнения промежуточных вычислений, максимальное вре­
мя — не более 9 часов, точность, с которой найденное решение соответствует
целевому значению, допустимое отклонение для переменных от оптимального
значения. Для задач с нелинейной целевой функцией задается параметр сходи­
мости, который влияет на прекращение поиска. Если относительные изменения
значения целевой функции за последние пять итераций меньше указанного чис­
ла, поиск прекращается. Выполняется установка типа модели — линейная, если
целевая функция линейная. Можно выводить результаты итераций, выполнять
автоматическое масштабирование параметров модели.
При решении задачи можно выбрать метод экстраполяции оценок переменных
для каждого шага поиска — линейная или квадратичная (для задач с нелиней­
ной целевой функцией), метод численного дифференцирования для целевой функ­
ции — прямые или центральные разности (для задач с нелинейной целевой
функцией), метод поиска — метод Ньютона (требуется много оперативной па­
мяти) или метод сопряженных градиентов (больше итераций). Основным огра­
ничением модели является максимальное число переменных — 200. Несколько
оптимизационных моделей на одном листе можно сохранять и загружать по
мере необходимости.
Если решение найдено, его можно сохранить либо восстановить исходные зна­
чения переменных. Результат решения можно сохранить в качестве сценария.
По результатам решения создаются отчеты. Отчет по результатам — сведения о
целевой функции с указанием ячейки, исходного и конечного значения, сведе­
ния о переменных с указанием списка ячеек, исходных и конечных значений,
сведения об ограничениях с указанием списка ячеек, формул, вычисленных зна­
чений и статуса и разницы (свободного остатка). Отчет по устойчивости — сведе­
ния о чувствительности модели (изменение целевой функции при изменении
переменных и ограничений). Отчет по пределам — сведения о нижних и верхних
границах значений переменных. Нижний предел — наименьшее значение пере­
менной, верхний предел — наибольшее значение переменной (значения всех
прочих переменных фиксированы и удовлетворяют ограничениям).

ПРИМЕЧАНИЕ
Некоторые отчеты не создаются, если заданы дополнительные условия, например, значе­
ния переменных — только целые числа.

Информационные технологии решения оптимизационных задач в Microsoft Excel
рассматриваются на конкретном примере.
Пример 9
Поиск оптимальной производственной программы по критерию максимума сум­
марной прибыли. Ограничение — расход ресурсов на выпуск продукции. Задача
относится к категории оптимизационных, поскольку допускает множество ре­
шений. Выбор оптимального решения выполняется с помощью целевой функ­
ции — максимум прибыли. Коэффициенты целевой функции — прибыль на
458 Глава 6. Специальные информационные технологии анализа данных в Microsoft Excel


единицу продукции. Ограничения — запас ресурсов, необходимых для изготов­
ления продукции. На выпуск единицы продукции затрачиваются ресурсы со­
гласно норме расхода. Дополнительное ограничение на объем выпуска каждого
вида продукции (или некоторых из них) — целые числа.
Последовательность действий.
1. Открыть рабочую книгу Анализ.х^ с помощью команды меню Файл • Открыть.
2. Вставить, если необходимо, новый лист с помощью команды меню Вставка •
Лист.
3. Переименовать новый лист с помощью команды меню Формат • Лист • Пере­
именовать, лист — Решение.
4. С помощью команды меню Вставка • Имя создать именованный блок ячеек в
одну строку для коэффициентов целевой функции. Имя блока — Коэффици­
енты, размерность блока — по числу переменных. Ввести значения коэффици­
ентов целевой функции.
5. С помощью команды меню Вставка • Имя создать именованный блок ячеек в
одну строку для переменных. Имя блока — Результат, размерность блока —
по числу переменных. Ввести нули в ячейки блока переменных для проверки
правильности вводимых формул.

ПРИМЕЧАНИЕ
Размерности блоков РЕЗУЛЬТАТЫ и КОЭФФИЦИЕНТЫ должны совпадать.

6. Ввести в свободную ячейку формулу для целевой функции:
=СУММПР0ИЗВ(КОЭФФИЦИЕНТЫ РЕЗУЛЬТАТЫ)
7. С помощью команды меню Вставка • Имя создать именованный блок для ячей­
ки целевой функции. Имя блока — ЦЕЛЬ.
8. Подготовить матрицу нормативов затрат ресурсов на выпуск единицы про­
дукции (рис. 6.16). С помощью команды меню Вставка • Имя создать имено­
ванный блок для матрицы нормативов — НОРМАТИВЫ.

ПРИМЕЧАНИЕ
Размерность блока НОРМАТИВЫ по горизонтали (число столбцов) совпадает с размерно­
стью блоков РЕЗУЛЬТАТЫ и КОЭФФИЦИЕНТЫ.

9. С помощью команды меню Вставка • Имя создать именованный блок ячеек в
один столбец для расчета потребности в ресурсах на выпуск продукции. Имя
блока - ПОТРЕБНОСТИ.
10. С помощью команды меню Вставка • Имя создать именованный блок ячеек в
1 столбец для задания ограничений ресурсов. Имя блока — ЗАПАСЫ. Ввести
ограничения на расход ресурсов в ячейки блока ЗАПАСЫ.

ПРИМЕЧАНИЕ
Размерности блоков ПОТРЕБНОСТИ и ЗАПАСЫ совпадают. Высота этих блоков (число
строк) соответствует высоте блока НОРМАТИВЫ.
459
Среда моделирования Microsoft Excel

КОЭФФИЦИЕНТЫ

i I
РЕЗУЛЬТАТЫ


ЦЕЛЬ




ПОТРЕБНОСТИ




ЗАПАСЫ
НОРМАТИВЫ




Рис. 6.16. Схема размещения информации на листе для задачи
линейного программирования


11. Ввести формулу расчета потребности для первого ресурса:
=СУММПР0И38($А8:$Р8;Результат)
A8:F8 — ячейки первой строки блока НОРМАТИВЫ Для тиражирования фор­
мулы расчета потребности в каждом ресурсе следует использовать абсолют­
ные ссылки на координаты столбцов и относительные ссылки на номера
строк, так как номера строк будут изменяться.
12. Тиражировать формулу потребности для первого ресурса во все ячейки бло­
ка ПОТРЕБНОСТИ.
13. Выполнить команду меню Сервис • Поиск решения В диалоговом окне Поиск
решения сформировать модель оптимизационной задачи (рис. 6.17).
14. Вызвать диалоговое окно Параметры поиска решения, нажав на кнопку Пара­
метры. Задать параметры для решения задачи оптимизации (рис 6.18).
15. Запустить решение задачи, нажав на кнопку Выполнить.




Рис. 6.17. Модель задачи линейного программирования
460 Глава 6. Специальные информационные технологии анализа данных в Microsoft Excel




Рис. 6.18. Параметры поиска решений

ПРИМЕЧАНИЕ
Если оптимальное решение не существует, следус1 попытаться, в первую очередь, изме­
нить параметры поиска решения. В случае неудачного решения можно изменить исход­
ные данные (запасы ресурсов, нормы расхода).

16. В конце решения сформировать отчет Результаты.
17. Закрыть файл с сохранением с помощью команды меню Файл • Закрыть.

Информационная технология статистического
анализа и прогнозирования
Microsoft Excel предоставляет большое число встроенных функций категории
Статистические и специализированные информационные технологии статисти­
ческого анализа — Пакет анализа. Пакет анализа устанавливается как дополни­
тельная надстройка с помощью команды меню Сервис • Надстройка. После уста­
новки в меню команды Сервис появляется новый пункт — Анализ данных.

Описательная статистика
Описательная статистика — самый распространенный прием анализа числовых
данных, с помощью которого вычисляются статистические оценки случайной
величины.
Средние значения случайной величины имеют ту же размерность, что и сама
случайная величина, вычисляются:
• средняя арифметическая для оценки математического ожидания случайной
величины — функция СРЗНАЧ;
• средняя геометрическая для оценки средних темпов роста, нахождение зна­
чения, равноудаленного от других значений — функция СРГЕОМ;
• средняя гармоническая для оценки средней суммы обратных величин — функ­
ция СРГАРМ.
461
Среда моделирования Microsoft Excel


Между средними величинами существуют соотношения:
X <X <X
ср гарм tp ieo\i — tp арифм "
Основные характеристики случайной величины:
• число значений — функция СЧЕТ;
• сумма значений — функция СУММ;
• дисперсия — характеризует разброс значений случайной величины около
средней арифметической, размерность дисперсии — размерность случайной
величины в квадрате. Различают дисперсию по выборочной совокупности
значений случайной величины — функция ДИСП и по генеральной совокуп­
ности — функция ДИСПР;
Q стандартное отклонение имеет ту же размерность, что и случайная величина.
Различают стандартное отклонение по выборке — функция СТАНДОТКЛОН),
стандартное отклонение по генеральной совокупности — СТАНДОТКЛОНП;
• средний модуль отклонений, нивелируется знак отклонения от среднего, яв­
ляется показателем силы вариации — функция СРОТКЛ;
• доверительный интервал для среднего значения случайной величины — функ­
ция ДОВЕРИТ;
• средняя квадратическая ошибка среднего — вычисляется как отношение
СТАНДОТКЛОН к корню квадратному из числа элементов выборки;
• минимальное значение случайной величины — функция МИН;
• максимальное значение случайной величины — функция МАКС;
• интервал — размах вариации, равный разности максимального и минималь­
ного значений случайной величины;
• порядковое наибольшее значение случайной величины — функция НАИБОЛЬ­
ШИЙ;
• порядковое наименьшее значение случайной величины — функция НАИМЕНЬ­
ШИЙ.
Мера взаимного расположения данных в массиве значений характеризуется с по­
мощью функций МОДА, КВАРТИЛЬ, МЕДИАНА, ПЕРСЕНТИЛЬ, ПРОЦЕНТРАНГ.
Мода — наиболее вероятное значение случайной величины. При симметричном
распределении относительно среднего мода совпадает с математическим ожида­
нием. Если значения случайной величины не повторяются, мода отсутствует.
Множество значений случайной величины делится на 4 равные части по числу
переменных — квартили. В квартилях значения переменных упорядочены по
возрастанию. Указывается номер части (квартиля) и соответствующее началь­
ное значение переменной определенного квартиля:
• 0 — минимальное значение;
• 1 — значение 25-го персентиля (персентиль — одна сотая доля массива зна­
чений случайной величины);
• 2 — значение 50-го персентиля или медианы;
462 Глава 6 Специальные информационные технологии анализа данных в Microsoft Excel


• 3 — значение 75-го персентиля;
• 4 — максимальное значение.
Форма распределения случайной величины характеризуется значениями асим­
метрии и эксцесса — функции СКОС и ЭКСЦЕСС соответственно.
Асимметрия служит для оценки симметричности распределения случайной ве­
личины относительно средней. Если асимметрия — положительное число, рас­
пределение имеет сдвиг в сторону положительных значений, иначе — в сторону
отрицательных значений. Эксцесс является характеристикой остроконечности
или сглаженности кривой распределения плотности вероятности случайной ве­
личины. Эксцесс равен нулю для нормального распределения, положителен для
остроконечных и отрицателен для сглаженных по сравнению с нормальной плот­
ностью распределения.
Пакет анализа обеспечивает наиболее быстрый способ формирования описательной
статистики. Команда меню Сервис • Анализ данных вызывает диалоговое окно
Инструменты анализа, в котором выбирается Описательная статистика. Исходные
данные для анализа располагаются в ячейках строк или столбцов таблицы и мо­
гут иметь метки. Для входного интервала указывается ориентация — по строкам
или столбцам, наличие метки строки или столбца.
Описательная статистика вычисляет статистические показатели: среднее, медиа­
на, стандартное отклонение, эксцесс, интервал, максимум, счет, k-й наимень­
ший, к-й наибольший, стандартная ошибка, мода, дисперсия, асимметричность,
минимум, сумма, доверительный интервал для заданного уровня надежности.
Результаты описательной статистики выводятся в указанное место (текущий
лист, другой лист, новая книга).

Пример 10
Применить методы описательной статистики для анализа сальдо счетов 051, 052
за период 1999-2001 гг. Исходные данные соответствуют месячным сальдо. Раз­
мерность массива исходных данных — 2x36 элементов.
Последовательность действий.
1. Открыть рабочую книгу Анализ.х1э с помощью команды меню Файл • Открыть.
2. Вставить, если необходимо, новый лист с помощью команды меню Вставка •
Лист.
3. Переименовать новый лист с помощью команды меню Формат • Лист • Пере­
именовать, лист — Статистика.
4. Разместить исходные данные в столбцах и указать их названия — Учетный
период, Сальдо 051 и Сальдо 052.
5. С помощью команды меню Сервис • Анализ данных вызвать диалоговое окно
Анализ данных. Выбрать инструмент анализа — Описательная статистика. Ука­
зать параметры описательной статистики (рис. 6.19):
463
Среда моделирования Microsoft Excel


О входной интервал — блок ячеек, содержащий сальдо по счетам, включая
названия столбцов1, группирование — по столбцам, указать наличие ме­
ток в первой строке;
О выходной интервал — Новый рабочий лист;
О указать Итоговая статистика; уровень надежности — 95 %; вычислять к-й
наименьший — 2; к-й наибольший — 2.
О нажать кнопку ОК.
6. Закрыть файл с сохранением с помощью команды меню Файл • Закрыть.




J95'%
''tP %х/^ща#шхж° " '

:1Р яшщфмт/к * 'Р ;"-

Рис. 6.19. Параметры описательной статистики

Таблица 6.6. Результаты описательной статистики

Сальдо счета 051 Сальдо счета 052 Функция
Показатель
9564,75
Среднее 13591,92 СРЗНАЧ(...У
116,0136717 127,6127101 СТАНДОТКЛОН/
Стандартная
ошибка КОРЕНЬ(СЧЕТ(. ))
9547,5 МЕДИАНА( ) или
Медиана 13821
КВАРТИЛЬ (. ., 2)
#н/д 9540
Мода МОДА( . )
696,0820303 765,6762604
Стандартное СТАНОТКЛОЩ ..)
отклонение
Продолжение *&


1
Можно одновременно выделить смежные столбцы исходных данных либо получить по­
казатели описательной статистики для каждого столбца отдельно
2
Символ . - заменитель исследуемого диапазона ячеек
464 Глава 6. Специальные информационные технологии анализа данных в Microsoft Excel


Таблица 6.6. Продолжение

Сальдо счета 052 Функция
Сальдо счета 051
Показатель
586260,1357 ДИСЩ...)
Дисперсия 484530,1929
выборки
-0,809054376 ЭКСЦЕСС(...)
-0,341929136
Эксцесс
Асимметричность -0,823444644 0,016596036 СКОС(...)
Интервал 2441 2587 МАКС(...)-МИН(...)
МИН(...)
Минимум 12054 8375
Максимум 14495 10962 МАКС(...)
СУММ(...)
Сумма 489309 344331
СЧЕТ(...)
Счет 36 36
14480 НАИБОЛЬШИЙ(...;2)
10932
Наибольший(2)
НАИМЕНЬШИЙ(...;2)
Наименьший(2) 12063 8377
Уровень ДОВЕРИТ(0,05);
235,52 259,07
надежности СТАНДОТКЛОЩ...;
(95,0 %) СЧЕТ(...))

В выходной таблице содержатся показатели итоговой статистики. Так, 95 % всех
значений сальдо по счету 051 находятся в диапазоне 13591, 92±235,52; для счета
052 в диапазоне — 9564,75±259,07. Дисперсия, стандартная ошибка и интервал
значений для сальдо счета 052 больше, что свидетельствует о значительном от­
клонении сальдо по учетным периодам.

Прогнозирование значений
Существуют различные методы прогнозирования, учитывающие характер про­

<< Пред. стр.

страница 55
(всего 83)

ОГЛАВЛЕНИЕ

След. стр. >>

Copyright © Design by: Sunlight webdesign