Подбор параметров. Поиск оптимальных решений

Команда Подбор параметра меню СЕРВИС позволяет определить неизвестное значение (параметр), которое будет давать желаемый результат. Технология использования команды следующая:

- решить нужную задачу с каким-либо начальным значением параметра;

- выбрать команду Подбор параметра меню СЕРВИС;

- в окне диалога Подбор параметра в поле Установить в ячейке задать абсолютную ссылку на ячейку, содержащую расчетную формулу, а в поле Значение – то значение, которое следует получить в качестве результата формулы;

- в поле Изменяя значение ячейки ввести ссылку на ячейку с параметром;

- нажать кнопку ОК или клавишу Enter, на экране появится окно диалога Результат подбора параметра;

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

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

По умолчанию команда Подбор параметра прекращает вычисления, когда выполняется 100 итераций или при получении результата, который находится в пределах 0,001 от заданного целевого значения. Если нужна большая точность, можно изменить используемые по умолчанию параметры на вкладке Вычисления команды Параметры меню СЕРВИС.

Команда Подбор параметра находит только одно решение, даже если задача имеет несколько решений.

Поиск решений может применяться для решения задач, которые включают много изменяемых ячеек, и помогает найти комбинацию переменных, которые максимизируют или минимизируют значение в целевой ячейке. Он также позволяет задать одно или несколько ограничений условий, которые должны выполняться при поиске решений. Для запуска этого инструмента следует выполнить команду Поиск решения меню СЕРВИС.

 

 

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

 

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

В поле Изменяя ячейки следует задать ячейки с переменными. Можно указать ссылки на ячейки или их имена. Если ячейки находятся в несмежных диапазонах, их следует разделять точкой с запятой. Вместо ввода ячеек можно нажать кнопку Предположить, и поиск решения сам предложит изменяемые ячейки, исходя из заданной целевой функции. Поле Изменяя ячейки нельзя оставить пустым и указанные в нем ячейки обязательно должны влиять на значение целевой ячейки.

Последний шаг определения поиска решений – задание ограничений. Он не является обязательным. Чтобы задать ограничения, следует в окне Поиск решения нажать кнопку Добавить и заполнить окно диалога Добавление ограничений. Ограничение состоит из трех компонентов: ссылки на ячейку, оператора сравнения и значения ограничения. В левой части от оператора сравнения кроме ссылки на ячейку может также задаваться ссылка на диапазон. В правой части может задаваться диапазон (той же размерности, что и в левой части), ссылка на ячейку или константное значение.

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

Найденные результаты решения задачи могут быть нецелыми. Их можно округлить или добавить новые ограничения, при которых результаты должны быть целыми числами. В последнем случае в качестве операции сравнения в окне Добавление ограничений следует выбрать Цел. Использование целочисленных ограничений в задаче поиска решений может увеличить сложность задачи в геометрической прогрессии, что приводит к существенным временным затратам. Некоторые задачи могут быть решены только при использовании целочисленных ограничений. В частности, целочисленные решения являются обязательными для задач, в которых переменные могут принимать только два значения, например 0 и 1. В этом случае в окне диалога Добавление ограничения можно использовать оператор сравнения Двоич.

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

Окно диалога Параметры поиска решений дает возможность задать еще ряд показателей, управляющих процессом поиска:

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

2) Относительная погрешность используется поиском решения для определения точности выполнения ограничений. Чем ближе это значение к 1, тем ниже точность. Задание относительной погрешности, меньше установленной по умолчанию (0,000 001) приводит к росту времени поиска решения.

3) Допустимое отклонение служит для задания допуск на отклонение от оптимального решения и применяется только в задачах с целочисленными ограничениями.

4) Переключатели Оценки, Разности и Метод поиска позволяют уточнить применяемые методы оптимизации, обычно используются значения, установленные по умолчанию.

5) Флажок Линейная модель может быть активизирован только для моделей, в которых отсутствуют нелинейные зависимости, т.е. график зависимости целевой функции от каждого ограничения может быть представлен прямой линией. Если этот флажок установлен, решение линейной задачи будет найдено быстрее.

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

Поиск решений может представлять свои результаты в виде трех отчетов: Результаты, Устойчивость и Пределы. Для генерации одного или нескольких отчетов следует выбрать их названия в окне диалога Результаты поиска решений. Каждый отчет сохраняется на отдельном листе текущей книги, а имена отчетов отображаются на ярлычках.

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

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

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

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

Графические средства

 

С помощью Microsoft Excel можно создавать сложные диаграммы для данных рабочего листа. Для построения диаграммы следует выделить любую ячейку из тех, что содержат исходные данные диаграммы. Затем выполнить команду Диаграмма меню ВСТАВКА или нажать копку Мастер диаграмм на стандартной панели инструментов. С помощью четырех окон диалога мастер диаграмм соберет всю информацию, необходимую для построения диаграммы. Чтобы построить диаграмму, не прибегая к помощи мастера диаграмм, следует выделить ячейку с исходными данными и нажать клавишу F11. Используя установки по умолчанию, Excel создает диаграмму на отдельном листе.

Основные компоненты диаграммы:

1) Тип диаграммы – в Excel типы диаграмм делятся на стандартные и нестандартные. К последним относятся как пользовательские типы диаграмм, создаваемые путем настройки пользовательских диаграмм, так и смешанные диаграммы Excel (например, гистограмма с графиком). При выборе типа диаграммы в диалоговом окне мастера диаграмм в специальной области приводится пример и краткое описание выбранной диаграммы.

2) Исходные данные диаграммы – задаются ссылкой на соответствующий диапазон. Исходные данные разбиваются по рядам (множество значений одного параметра). В свою очередь ряды состоят из точек – конкретных значений параметра. По умолчанию Excel считает, что диаграмма должна содержать меньше рядов, чем точек. Соответственно выбирается ориентация диаграммы. Пользователь может изменить ориентацию диаграммы, выбрав переключатель Ряды в строках/столбцах диалогового окна мастера диаграмм. Исходные данные необязательно должны располагаться в одном блоке ячеек. Чтобы построить диаграмму для несмежных данных, следует перед запуском мастера диаграмм выделить каждый отдельный блок исходных точек при нажатой клавише Ctrl.

3) Заголовки диаграммы. Пользователь может задать заголовок диаграммы и каждой из ее осей. При выводе текста в диаграмме Excel используют установленные по умолчанию шрифт, выравнивание и расположение. Заголовки – это обычные поля надписей, которые можно перемещать, форматировать и редактировать после создания диаграммы.

4) Оси Диаграммы. Наличие и количество осей определяется типом диаграммы. Пользователь может контролировать вывод осей на диаграмме. Если данные для оси X являются датами, то Excel распознает этот факт и автоматически применяет временное масштабирование к этой оси.

5). Линии сетки – это горизонтальные или вертикальные линии, которые помогают определить положение маркеров данных относительно шкал осей. В большинстве типов диаграмм Excel по умолчанию используется сетка определенного вида. Основные линии сетки продолжают основные деления шкалы. а промежуточные линии сетки – промежуточные деления. Excel автоматически определяет положение этих делений шкалы. Однако пользователь может изменить их расположение. Можно также удалить линии сетки или отобразить их в диаграмме.

6) Легенда диаграммы – это заголовки рядов, размещаемые по умолчанию в правой части диаграммы. Выбор заголовков рядов производится автоматически, если в выделенный диапазон включены заголовки строк и столбцов. В противном случае ряды именуются как Ряд1, Ряд 2 и т.д. Пользователь может изменить имя, введя нужный текст или ячейки с нужным текстом в поле Имя второго диалогового окна мастера диаграмм. Можно также изменить местоположение или вообще удалить легенду с диаграммы. Команды форматирования позволяют изменить используемый в диаграмме шрифт.

7) Заголовков значений - идентифицирует точки в рядах данных. Обычно отображаются вдоль оси X. Выбор заголовков значений производится автоматически, если в выделенный диапазон включены заголовки строк и столбцов. В противном случае для точек используются стандартные обозначения 1, 2, 3 и т.д. Их можно изменить, введя свои текстовые значения или ссылки на диапазон рабочего листа с нужными значениями в поле Подписи по оси х второго диалогового окна мастера диаграмм.

8). Подписи данных – это различного типа подписи, присоединяемые к маркерам данных и показывающие значения каждой точки. Подписи данных задаются на одноименной вкладке третьего окна диалога мастера диаграмм.

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

Excel может внедрить диаграмму в рабочий лист или помесить ее на отдельном листе диаграммы. При создании диаграммы в существующем рабочем листе ее размеры и положение устанавливаются по умолчанию. Настроить размеры и положение диаграммы можно с помощью мыши. Клавиши Ctrl или Shift позволяют сохранить положение центра диаграммы или ее пропорции (отношение ширины диаграммы к ее высоте) при изменении ее размеров. При изменении размеров диаграммы настраиваются также размеры всех ее текстовых элементов. Если пользователь не удовлетворен этой настройкой, можно использовать команды форматирования для увеличения или уменьшения размеров текста.

Диаграмма, созданная на отдельном листе, имеет стандартные размеры, которые сохраняются даже при изменении размеров окна книги. Пользователь может включить альтернативный режим вывода на экран, при котором размеры диаграммы автоматически настраиваются по размерам окна книги (флажок Масштабировать диаграмму по размеру окна на вкладке Диаграмма команды Параметры меню СЕРВИС).

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

В графиках, точечных и лепестковых диаграммах Excel обычно пропускает пустые ячейки, и на месте отсутствующих данных появляются разрывы. Есть два способа представления пустых данных на диаграмме:

- отображать отсутствующие точки данных нулевыми значениями;

- интерполировать отсутствующие точки данных.

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

Диаграмма сохраняется вместе с книгой, в которой она находится. Диаграмма становится доступной, когда открыта книга.

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

Если нужно изменить конкретный элемент диаграммы, его следует выделить (например, выбрав соответствующий элемент из раскрывающегося списка кнопки Элемент диаграммы на панели инструментов Диаграммы). Можно просто щелкнуть на нужном элементе диаграммы. Excel облегчает проверку правильности выделения элемента, выводя его наименование в левом конце строки формул.

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

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

При форматировании диаграммы можно выполнять следующие действия:

1) Изменить расположение элементов диаграммы.

2) Изменить размеры и переместить область построения. Область построения – это часть диаграммы, в которой отображаются данные.

3) Форматировать текстовые элементы диаграммы (в том числе изменять ориентацию текста).

4) Форматировать и изменять шкалы осей.

5) Задавать вывод и форматирование сетки.

6) Форматировать ряды и маркеры данных.

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

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

7) Отображать в графиках коридоры колебания и полосы повышения и понижения. Коридор колебания – это линия, соединяющая минимальное и максимальное значения и наглядно показывающая диапазон, в пределах которого изменяются значения в данной категории. Коридор колебания может быть показан только на плоских графиках.

Полоса повышения и понижения – это прямоугольник, нарисованных между точками данных первого и последнего ряда. Excel заполняет прямоугольник одним цветом или узором, если первый ряд расположен выше последнего, и контрастным цветом или узором в противоположном случае.

8) Отображать линии проекции в графиках и диаграммах с областями.

Линия проекции – это прямая, которая проходит от точки данных до оси категорий. Линии проекции особенно полезны в диаграммах с областями.

9) Отделять сектора круга и кольца.

10) Форматировать фоновые области.

11) Настраивать объемные виды диаграммы.

12) Добавлять, удалять, изменять данные в диаграммах, изменять порядок отображения рядов.

13) Использовать многоуровневые категории. Excel позволяет группировать категории. Например, рядами данных являются месяца, а категориями – различные города, в которых расположены предприятия, но категории классифицируются еще и по государствам, в которых находятся города.

14) Применять линии тренда. Линия тренда – это линия регрессии, которая аппроксимирует (сглаживает) точки данных, или линия скользящего среднего.

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

16) Изменять исходные значения с помощью маркеров данных. В плоских линейчатых диаграммах, гистограммах, графиках и точечных диаграммах можно проводить анализ типа «обратный, что будет, если». Можно перетащить вверх или вниз маркеры данных, в том числе и маркеры рисунков. Excel же соответственно подберет и изменит исходные значения в рабочем листе. Этот процесс иногда называют графическим подбором параметра.

В состав Excel входит специальная компонента Microsoft Map, которую можно использовать для наглядного представления географической информации. Карты можно использовать для вывода на экран географических статистических данных, например о плотности населения или об окружающей среде. В зависимости от используемой карты можно вывести на экран различные конкретные географические подробности, в том числе дороги, города, аэропорты и реки. Можно отобразить на экране данные по странам, штатам (провинциям), а также почтовым индексам.

Microsoft Map является надстройкой и не входит в типовую установку Excel. Для его установки следует запустить программу Setup и добавить этот компонент.

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

Создание карты производится командой Карта меню ВСТАВКА или с помощью кнопки Карта, расположенной на стандартной панели инструментов. После создания карты, ее можно изменить в соответствии с требованиями пользователя (после создания карты открывается окно диалога Элемент Microsoft Map).