6. Подбор параметра и итерации

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

Для этого используется возможность «Подбор параметра», соответствующее диалоговое окно вызывается командой Сервис – Подборпараметра.

В диалоговом окне ( рис.1.44) необходимо заполнить три поля:

1. В поле “Установить в ячейке” указывается адрес ячейки, для которой производится расчет значения. Эта ячейка обязательно должна содержать формулу.

2. В поле “Значение” заносится числовая величина, которая должна быть равна ячейке с формулой.

3. В поле “Изменяя значение ячейки” заносится адрес ячейки, изменяемой в ходе подбора параметра. Эта ячейка обязательно должна быть связана с ячейкой, содержащей формулу и обязательно должна влиять на нее.

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

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

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

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

Дополнительно на вкладке Вычисления можно указать параметры итераций:

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

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

Задача 1.38 В смешанном автомобильно-железнодорожном сообщении ежесуточно поставляют Q_сут = 600 т различных грузов в контейнерах грузоподъемностью q_об.к. = 20 т, _ст=0,8; Т_н=64 ч; Т_м=10 ч. За один оборот автомобиль тягач КамАЗ-5410 перевозит два контейнера. Дополнительные условия перевозок:

Таблица 1.26

Показатели	Автомобиль	Железная дорога
Расстояние перевозки, км	18	300
Эксплуатационная скорость, км/ч	18	100
Время складского хранения, ч	10	18

1) рассчитать потребное число контейнеров Х_ки автомобилей А_мдля выполнения заданного объема перевозок, интервалIих движения.

2) какова должна быть эксплуатационная скорость автомобиля, чтобы потребное количество автомобилей сократилось на 20%?

3) какое количество груза можно вывезти тем же подвижным составом, если интервал движения автомобилей увеличить до 10 минут?

Аналитическое решение

Для выполнения расчетов используются следующие формулы:

Число дней оборота контейнера

Д _об.к.= 1/24((2l_а/V_а+t _а)+(2l_ж/V_ж+t _ж)+_к)

. (1.28)

где l_а , l_ж– расстояние перевозки автомобильным и железнодорожным транспортом;

V_а , V_ж– эксплуатационная скорость перевозки автомобильным и железнодорожным транспортом;

_а , _ж– время складского хранения для автомобильного и железнодорожного транспорта.

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

. (1.29)

Время оборота автомобиля – тягача, ч

. (1.30)

Число оборотов за день

. (1.31)

Число контейнеров, перевозимых автомобилем за день

. (1.32)

Необходимое число автомобилей – тягачей

. (1.33)

Интервал движения тягачей

. (1.34)

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