Решение нелинейного уравнения.

Простейшим случаем задачи оптимизации является определение корня нелинейного уравнения. Уравнение f (х) = 0 будет нелинейным, если в его правой части присутствует неизвестная в степени, отличной от 1, или имеются трансцендентные функции sin x, In x и т.д. Для некоторых уравнений существуют методы, позволяющие получить точное решение (например, квадратное уравнение). Однако большинство из них не имеет точного аналитического решения.

Чаще всего нелинейное уравнение имеет несколько корней, поэтому его приближённое решение состоит из двух этапов.

На первом этапе производится грубый подбор или отделение корня, то есть находятся все или хотя бы один отрезок, на котором есть корень. Для этого следует просчитать таблицу значений функции на произвольно выбранном интервале и построить график. Напомним, что корень - это значение х, при котором левая часть уравнения равна 0. На графике это отрезок, на котором функция пересекает ось X, соответственно в таблице -отрезок на котором функция меняет знак. Если на выбранном интервале корней не оказалось, следует поискать их на другом отрезке, просто изменив значения в столбце аргументов.

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

Рис. 11. Использование средства «Поиск решения» для уточнения корня нелинейного уравнения

На Рис..11 показан пример заполнения окна «Поиск решения» и дополнительной формы «Добавление ограничения» при решении задачи нахождения корней нелинейного уравнения.

Транспортная задача

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

Типовая постановка задачи.

Имеются несколько заводов, производящих строительные конструкции, и несколько строек, где они используются.

Для решения задачи удобно использовать две таблицы:

· Таблицу, содержащую исходные данные;

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

Таблица исходных данных содержит.

· Мощности заводов;

· потребности строек;

· стоимость доставки одной строительной конструкции с любого завода на любую стройку;

Таблица перевозок содержит:

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

· Счетчики доставленных на каждую стройку изделий;

· Счетчики изделий, взятых с каждого завода.

Ограничения, необходимые для корректного решения задачи.

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

· Количество изделий, доставленное на каждую стройку, точно соответствует потребности данной стройки;

· Количество изделий, задранных с каждого завода, точно соответствует мощности данного завода.

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

Целевая функция - общая стоимость перевозок, необходимых для обеспечения строек.

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

Решение задачи — план перевозок, при котором значение целевой функции (стоимости перевозок) - минимальна.

 

Вопросы для самоконтроля по разделу Microsoft Excel

1. Из чего состоит адрес ячейки?

2. Что такое селектор?

3. Что такое маркер заполнения?

4. Какие формы принимает курсор? При выполнении, каких операций используется каждая из них?

5. Какую форму принимает курсор при копировании информации в смежные ячейки?

6. Как выделить несколько смежных ячеек (блок ячеек)?

7. Как выделить несколько строк?

8. Как выделить несколько столбцов?

9. Как выделить всю электронную таблицу?

10.Как перейти на новый лист?

11.Как изменить имя листа?

12.Какие типы данных можно записать в ячейку?

13.Как изменить формат числа?

14.Как изменить формат даты?

15.Приведите правило записи формул?

16.Как заполнить столбец таблицы значениями от -2, -1,8,..., 2?

17.Как скопировать в ячейки А2 - А12 формулу, записанную в ячейке А1?

18.Как записать формулу для расчёта значения функции У=Х², если значение X записано в ячейке А5?

19.Как объединить несколько ячеек?

20.Как написать в ячейке наклонный текст в две строки?

21. Как построить круговую диаграмму (описать, на каких шагах мастера диаграмм, какие действия производятся)?

22.Назовите основные типы диаграмм.

23.Как изменить тип существующей диаграммы?

24.Как изменить название существующей диаграммы?

25.Как изменить размещение существующей диаграммы?

26.Как изменить название оси существующей диаграммы?

27.Как изменить имя ряда существующей диаграммы?

28.Что такое линия тренда?

29.Перечислите основные типы встроенных функций.

30.Как вызвать мастер функций (приведите два способа)?

31.Как при помощи мастера функций создать формулу =Sin(Cos(a2))?

32.Как изменить формат одного из графиков диаграммы?

33.Как убрать (добавить) маркеры на графике?

34.Как изменить цвет линии на графике?

35.Как нарисовать график круглыми красными маркерами?

36.Приведите пример нескольких логических функций.

37. Приведите пример логической функции И.

38.Приведите пример логической функции ИЛИ.

39. Для чего нужна логическая функция ЕСЛИ?

40.Приведите пример логической функции ЕСЛИ.

41.Написать формулу для расчёта составной функции У, которая равна X при X >= 0, или Sin(X) при X < 0, если X находится в ячейке A3.

42.Написать формулу, выдающую «Верное решение», когда в ячейке С7 записано число 77, или сообщение «Ошибка», если это не так.

43.Написать формулу, выдающую «Неплохо», когда в ячейке С7 записано число 5 или число 4, или сообщение «Посредственно», если это не так.

44.Как создать рамки для таблицы?

45.Как создать внешние рамки для таблицы в виде двойных красных линий?

46. Для чего нужна круговая диаграмма и диаграмма с областями?

47.Для чего нужны точечная, линейчатая, биржевая диаграммы?

48.Как найти матрицу, обратную заданной?

49.Как перемножить две матрицы?

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

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

52.Для чего нужна функция СУММПРОИЗВ?

53.На какие клавиши нужно нажать, чтобы получить ответ в виде массива?

54.Для чего нужны функции ГПР и ВПР?

55.Для чего используются функции МОБР и МУМНОЖ?

56.Как решить систему линейных уравнений с помощью функций МОБР и МУМНОЖ?

57.Для чего используется средство «Подбор параметра»?

58.Для чего используется средство «Поиск решения»?

59.Приведите последовательность действий для решения оптимизационной задачи средством «Подбор параметра».

60.Приведите последовательность действий для решения оптимизационной задачи средством «Поиск решения».

61.Привести пример использования функции СУММПРОИЗВ.

62.Привести пример использования функции СУММ.

63.Привести пример оптимизационной задачи, которая может быть решена средством «Поиск решения».

64.Как воспользоваться средством «Подбор параметра»?

65.Как воспользоваться средством «Поиск решения»?

66.Как добавить ограничения и повторно запустить средство «Поиск решения»?

67. Что должно быть записано в ячейке, чтобы её можно было бы использовать как целевую ячейку в средстве «Поиск решения»?

68.Привести пример использования функции.