Подбор параметра и итерации
Вычислительные возможности электронных таблиц позволяют решать как «прямые» задачи, то есть расчеты значений по заданным формулам, так и «обратные» задачи, выполнять исследование области допустимых значений аргументов, а также подбирать значения аргументов для заданного значения функции. Это позволяет во многих случаях обходиться без соответствующего преобразования исходных формул и получение необходимого решения более простым способом.
Для этого используется возможность «Подбор параметра», соответствующее диалоговое окно вызывается командой Сервис – Подборпараметра.
В диалоговом окне ( рис.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)
Для решения второй части задания необходимо выразить эксплуатационную скорость автомобиля через количество автомобилей.