Подбор параметра

ТЕМА: ЭЛЕКТРОННАЯ ТАБЛИЦА EXCEL. ИСПОЛЬЗОВАНИЕ МЕХАНИЗМОВ EXCEL ДЛЯ РЕШЕНИЯ ТИПОВЫХ ЭКОНОМИЧЕСКИХ ЗАДАЧ: ПОИСК РЕШЕНИЯ, ПОДБОР ПАРАМЕТРА. ОПТИМИЗАЦИОННЫЕ ЗАДАЧИ.

ЦЕЛЬ РАБОТЫ:

v Изучить механизм EXCEL Подбор параметра.

v Изучить механизм EXCEL Поиск решения.

v Научиться использовать механизмы EXCEL при решении типовых экономических задач: оптимизационной, транспортной и др.

ОСНОВНЫЕ ТЕОРЕТИЧЕСКИЕ СВЕДЕНИЯ

Подбор параметра.

При подборе параметра пользователь должен определить три обязательные составляющие:

v местоположение целевой ячейки (содержимое которой обязательно должно быть формулой);

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

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

Все три названные составляющие поиска объединены в диалоговом окне "Подбор параметра", которое вызывается из меню "Сервис" и представлено на рис.5.1.

Рис.5. 1 Диалоговое окно Подбор параметра.

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

Второе поле Значение должно содержать искомое значение целевой ячейки.

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

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

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

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

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