Информатика. Методические указания для выполнения контрольных работ (стр. 12 )

Чтобы использовать средство «Поиск решения», были выполнены следующие действия:

1)  В ячейки С10 было записано граничное значение х – 2 , а в ячейке D10 - формула для расчета функции ( =C10^2+2*C10+5).

2)  Выполнена команда «Поиск решения» меню «Сервис».

В появившемся окне «Поиск решения» (рисунок 17) указан адрес функции (поле «Установить целевую»), значение которого должна достигнуть функция (опция и поле «Значение»), адрес аргумента (поле «Изменяя ячейки»). Кроме того, в список «Ограничения» добавлены ограничения, задающие отрезок изменения аргумента (ячейка С10) от –2 до -1. После щелчка по кнопке «Выполнить» производится поиск решения путем поиска подходящего значения аргумента (-1,328), которое записывается в ячейку С10 , при этом в ячейке D10 появляется значение функции 0,000. Это говорит о том, что поиск решения прошёл успешно и корень равен -1,328. На рисунке 17 показаны абсолютные адреса, появившиеся в соответствующих полях после щелчка по ячейке. Для корректировки ограничений используются кнопки: «Добавить», «Изменить» и «Удалить». Две первые вызывают окно «Добавление ограничения», здесь указываются адрес ячейки с формулой, значение которой ограничивается (поле «Ссылка на ячейку:»), ограничивающий знак (раскрывающийся список знаков) и значение (адрес ячейки со значением), с которым происходит сравнение (поле «Ограничение:»). В качестве ограничивающего знака могут использоваться операции сравнения: >=, <=, = или слова: «цел» или «двоич», в этом случае ячейка, указанная в ссылке, может принимать только целые или двоичные значения соответственно и поле «Ограничение» не заполняется. Например, на рис. 17 показан процесс создания первого ограничения. Кнопки: «ОК», «Отмена», «Добавить» окна «Добавление ограничения» используются, когда последнее ограничение создано, процесс создания ограничения нужно прервать, нужно перейти к созданию следующего ограничения соответственно.

Таблица 13 - Исходные данные для транспортной задачи

Затраты на перевозки	Потребители	Мощность завода
стройка 1	стройка 2	стройка 3	стройка 4
Поставщики	ЖБИ-1	12	5	13	15	200
ЖБИ-8	11	12	10	15	300
Завод 3	14	15	8	20	400
Потребность стройки	100	150	200	450

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

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

Для её решения (приложение1) были созданы две расчётные таблицы: «План перевозок» и «Затраты на перевозки».

В первой таблице заполняются:

ü  Ячейки С3:F5 - произвольными цифрами, например, единицами, вместо которых после вызова и выполнения режима «поиск решения» появляются числа, являющиеся решением задачи.

ü  Строка «Итого» и столбец «Всего» - формулами, суммирующими объёмы перевозок по каждому заводу и стройке. Например, формулы в ячейках G3 и C6 выглядят так: =СУММ(C3:F3) и =СУММ(C3:C5) соответственно.

Во второй таблице ячейка «Общие затраты на перевозки» рассчитывается как сумма произведений объёма перевозки с каждого завода на каждую стройку на соответствующую стоимость перевозки. Формула, записанная в ячейке G14, выглядит так: =СУММПРОИЗВ(C3:F5;C10:F12).

В приложении 1 приведено окно «Поиск решения», в котором в качестве целевой функции выбрана ячейка, где вычисляются общие затраты на перевозки, указано направление оптимизации (опция «минимальному значению»), заданы изменяемые ячейки и ограничения. Изменяемыми будут ячейки, где указан объём перевозки с каждого завода на каждую стройку, они могут быть только не отрицательными – первая строка ограничений. Кроме того, на каждую стройку нужно привести именно столько строительных конструкций, сколько нужно (соответствующие ячейки строк «Итого» и «Потребность стройки» равны), а с каждого завода вывести всю продукцию (соответствующие ячейки столбцов «Всего» и «Мощность завода» равны) – вторая и третья строки ограничений. После завершения решения была построена трёхмерная гистограмма (приложение 1), иллюстрирующая оптимальный план перевозок. Каждый её столбик - это объём перевозки с одного из заводов на одну из строек.

3.10. Требования к оформлению отчета

Отчет по каждой задаче должен содержать несколько основных элементов:

Ø  постановка задачи;

Ø  средства, используемые для решения;

Ø  описание формул и диалоговых окон (или распечатка заполненных диалоговых окон и фрагментов таблиц в режиме формул);

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

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

Ø  настроить изображение в окне книги Microsoft Excel при помощи прокрутки и масштабирования;

Ø  сделать «фотографию экрана» нажав кнопку «Print Screen»;

Ø  открыть (если он не был открыт заранее) любой графический редактор, например «Paint» и командой «вставить» поместить в окно редактора «фотографию экрана»;

Ø  выделить нужный фрагмент изображения и скопировать его в текстовый документ; по необходимости настроить параметры изображения, например режим обтекания.

Пример оформления контрольной работы приведен в приложении 1.

3.11 Варианты заданий

Задание №1.

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

Рассчитать таблицу значений функций, приведённых в индивидуальном задании. Графу «аргумент (Х)» заполнить элементами арифметической последовательности с помощью манипулятора «мышь», а в графах «Значения функции» записать формулы со ссылками на соответствующую ячейку с аргументом. Функции, интервалы и шаг изменения аргумента приведены в таблице 14.

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

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

Отредактировать её так, чтобы первый график был изображён линией, второй - маркерами, третий - линией с маркерами и все графики черного цвета.

Таблица 14- Варианты индивидуального задания №1

Вариант	Функции	Значение аргумента (Х)
У1=	У2=	У3=	Начальное	Конечное	Шаг
1	12 Ln(X)	0,1 Exp(X)	4X-5	2	5	0,2
2	0,5X2	0,05X3-2	5Ln(X)	2,4	6	0,2

Окончание таблицы 14

3	2X+1,5	X2-3,5X	2 Exp(X)	-1	1	0,1
4	1/X	1/(X2+1)	1/(X+5)	2	12	0,5
5	20/X	2X+1,5	0,08X3-2Х+3	2	8	0,2
6	35/(X3+5)	20/(X+5)	0,05X3-2Х+3	1	7	0,3
7	100/(X2+1)	Ln(X)	0,05X3-2	3	9	0,3
8	0,6X2	X3 +5X2+3,5X	8	-5	5	0,4
9	2X+1,5	0,6X2	0,5X3-2	-2	2	0,1
10	X2	0,5X2-3,5X	0,05X3+2Х-1	-7	7	0,5
11	80Ln(X)	17- X2-3,5X	|X3-2,6|	2	8	0,2
12	0,01ХExp(X)	(180Х-1)/(X2+1)	0,5X3+2Х-1	2	8	0,2
13	ХLn(X)	Exp(X) /(X2+1)	0,6X2	1	7	0,3
14	0,5X2	0,1X3- 0,5X2-3,5X	120/X	3	9	0,3
15	1,3X2	(80Х-1)/(X2+1)	2ХLn(X)	1	5	0,4
16	2X+3,5	18Ln(X)/(X+5)	0,05X3+2Х-6	2	6	0,1
17	20X+1,5	-0,5X3+ 0,5X2+3X	0,8ХExp(0,55X)	-8	8	0,5
18	X2 /(Х+2)	(3X2-3,5X ) /(X+1)	0,08X3-2Х-3	2	8	0,2
19	15Ln(X)	3XExp(X)	0,6X2	2	6	0,4
20	1/X	-0,2X+1,5	Ln(X)	1	5	0,5
21	2X+1,5Ln(X)	5X2-3,5X	1,8 Exp(X-1)	1	4	0,1
22	12/X	11/(X2+1)	25/(X+5)	2	12	0,5
23	150/X	8,5X+5	0,1X3-5Х+3	2	8	0,2
24	500/(X+5)	350/(X2+1,5)	0,5X3-2Х+3	1	7	0,3
25	95/(X2+1)	95/Ln(X)	0,2X3-28	3	9	0,3
26	6X2	X3- 0,5X2-3,5X	0,95ХExp(X)-32	-5	5	0,4
27	2X+1,5	0,6X2	0,5X3-2	-2	2	0,1
28	X2	|X2-3,5X|	0,05X3+2Х-1	-7	7	0,5
29	18Ln(X)	15- X2-3,5X	0,3(X3-126)	2	8	0,2
30	0,01ХExp(X)	(8Х-1)/(X2+1)	0,45X3+2Х-1	2	8	0,2

Задание №2

На двух листах (диаграмма строится на отдельном листе) выполнить индивидуальное задание: