2. Задачи

Задача 1

Построить в разных системах координат при графики следую­щих функций:

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

Решение в Microsoft Excel

Построим график функции . Создадим в Excel таблицу. Для этого зафиксируем курсор в ячейке А6 и выберем команду Правка → Заполнить → Прогрессия (заполним столбец А последовательностью до значения 2). В ячейку В6 введем формулу:

=(2+SIN(A6)^2)/(1+A6^2)

Растянем формулу до ячейки В41 (включительно).

Для построения графика функции выделим диапазон ячеек А5:В41, содержащий таблицу значений функции и ее аргумента, и вызовем мастер диаграмм с помощью команды Вставка → Диаграмма. Появляется окно Мастера диаграмм. Выбираем тип диаграммы «График». Нажимаем Далее. На закладке Ряд удаляем в соответствующем поле Ряд Х. Завершаем построение диаграммы, указав название диаграммы «График функции».

Построим график функции . Создадим в Excel таблицу. Для этого зафиксируем курсор в ячейке А6 и выберем команду Правка → Заполнить → Прогрессия (заполним столбец А последовательностью до значения 2 с шагом 0,1). В ячейку В6 введем формулу:

=ЕСЛИ(A6<=0;(3*(A6^2))/(1+A6^2);КОРЕНЬ(1+((2*A6)/(1+A6^2))))

Растянем формулу до ячейки В41 (включительно).

Для построения графика функции выделим диапазон ячеек А5:В41, содержащий таблицу значений функции и ее аргумента, и вызовем мастер диаграмм с помощью команды Вставка → Диаграмма. Появляется окно Мастера диаграмм. Выбираем тип диаграммы «График». Нажимаем Далее. На закладке Ряд удаляем в соответствующем поле Ряд Х. Завершаем построение диаграммы, указав название диаграммы «График функции».

График функции строится аналогично, только в ячейку В6 вводим формулу:

=ЕСЛИ(A6<0;3*A6+КОРЕНЬ(1+(A6^2));ЕСЛИ(A6>1;2*SIN(3*A6);2*COS(A6)*EXP(-2*A6)))

Для построения графика функции выделим диапазон ячеек А5:В41. Вызываем Мастер диаграмм. Далее выбираем вид диаграммы – Гладкие графики. На вкладке Ряд убираем аргумент из списка рядов. В окно «Подписи оси Х» вводим диапазон ячеек для аргумента «=$A$6:$A$41». Нажимаем далее. На вкладке Легенда снимаем птичку «Добавить легенду». На вкладке Заголовки вводим название диаграммы – «График функции Z».

Решение задачи в MathCAD

Вводим диапазон значений аргумента для функций с шагом 0,1

Вводим функции y(x), g(x), z(x), графики которых следует построить:

Выбираем команду Добавить → Графики → X-Y График. Затем в нижнюю метку вводим аргумент x, в левую – функцию для построения ее графика (y(x), g(x), z(x)). В результате получаем следующие графики:

Задача 2

Решение в Microsoft Excel

Решить системы линейных уравнений АХ=В, АA^TA²Х=В и вычислить зна­чение квадратичной формы z= Y^TA³A^TY, где

Заполним матрицы, для этого в ячейки A4:D7 запишем данные матрицы A, в F4:F7 – матрицы B, Н4:H8 матрицы – Y.

В ячейки B18, D18 и F18 запишем уравнения, и выражение квадратичной формы. В ячейках B20 и D20 выразим X для двух уравнений. Под ответ первого уравнения выделим ячейки B11:B14, для второго – D10:D14.

Решим первое уравнение, для этого выделим диапазон ячеек B11:B14 и запишем туда формулу {=МУМНОЖ(МОБР(A4:D7);F4:F7)} и нажмём CTRL+SHIFT+ENTER

Для решения второго уравнения организуем промежуточные таблицы A^T, A², (A²)^-1, (A^T)^-1 . Для нахождения матрицы A^T , т.е транспонированной матрицы выделим диапазон ячеек J4:M7 и запишем туда формулу {=ТРАНСП(A4:D7)} и нажмём CTRL+SHIFT+ENTER. Для нахождения A² выделим диапазон J10: M13 и запишем туда следующую формулу: {=МУМНОЖ(A4:D7;A4:D7)} после чего нажмём СTRL+SHIFT+ENTER. Для нахождения (A²)^-1 выделим диапазон J16:M19 и запишем туда формулу {=МОБР(J10:M13)} после чего нажмём СTRL+SHIFT+ENTER. Для нахождения (A^T)^-1 выделим диапазон O4:R7 и запишем туда формулу {=МОБР(J4:M7)} и нажмём СTRL+SHIFT+ENTER.

Далее поочерёдно произведём умножение ,и запишем ответ в диапазон ячеек выделенный для значения второго уравнения.

Для вычисления квадратичной формы организуем дополнительные таблицы вычислений Y^T, A³, Y^T находится аналогично А^T, а для нахождения A³ в выделенный диапазон вводим формулу {=МУМНОЖ(J10:M13;A4:D7)} и нажимаем СTRL+SHIFT+ENTER. И далее поочерёдно производим умножение ,

Решение задачи в MathCAD

Опишем наши три матрицы для этого выберем Добавить→Матрицу. Для матрицы А количество столбцов укажем 3, количество столбцов аналогичное, присвоим матрице имя А, для двух других B и Y количество строк будет равно 3, а количество столбцов – 1.

Для решения уравнения АХ=В будем использовать команду lsolve. Затем нажмём = и получим ответ уравнения.

Далее решим уравнение АA^TA²Х=В, этого нам надо помножить обе части на A^-1, то есть на обратную матрицу и в результате получим следующее выражение X=BA^-1∙(A^T)^-1∙(A²)^-1. В MathCAD мы можем записать это выражение следующим образом:

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

Задача 3

Транспортная задача. Имеются n пунктов производства и т пунктов рас­пределения продукции. Стоимость перевозки единицы продукции с і-го пункта производства в j-й центр распределения c_ij приведена в таблице, где под строкой понимается пункт производства, а под столбцом - пункт рас­пределения. Кроме того, в этой таблице в i-й строке указан объем производ­ства в і-м пункте производства, а в j-м столбце указан спрос в j-м центре распределения. Необходимо составить план перевозок по доставке требуе­мой продукции в пункты распределения, минимизирующий суммарные транспортные расходы.

	Стоимость перевозки единицы продукции				Объем производства
	5	14	8	9	22
	9	12	8	9	16
	6	22	8	5	17
	7	7	8	7	21
	8	9	8	6	23
Объем потребления	15	21	15	21

Решение задачи в Microsoft Excel

Проверим сбалансированность модели задачи. Модель является несбалансированной, т.к. суммарный объем производимой продукции в день больше суммарного объема потребности в ней. (22+16+17+21+23 > 15+21+15+21). Поэтому на предприятиях останется 27 единиц продукции.

Построим математическую модель. Объемы перевозок – это неизвестные переменные. Целевая функция – это общие затраты на перевозку необходимого количества продукции согласно плана перевозок.

Составим следующие таблицы в Excel.

При помощи надстройки «Поиск решения» оптимизируем план перевозки.

Оптимизированный план перевозки выглядит следующим образом:

Решение задачи в MathCAD

Пусть – величина перевозки от предприятия А к 1-му пункту потребления,– величина перевозки от предприятия А ко 2-му пункту потребления и т.д. Тогда определим целевую функцию:

Определим начальные значения переменных:

Начинаем блок решения с ключевого слова Given.

Далее задаем ограничения на значения переменных (согласно объемам потребления и объемам производства).

Минимизируем целевую функцию:

Теперь выводим оптимальные план перевозки, минимизирующий затраты.

Находим минимальное значение транспортных расходов:

Задача 4

Приобретен депозитный сертификат номинальной стои­мостью 100000 руб. за 95000 руб. со сроком погашения через 6 месяцев. Определите дисконт.

Решение задачи в Microsoft Excel

Создадим таблицу, куда внесём данные:

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

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

В итоге получаем:

Решение задачи в MathCAD

Для нахождения дисконта опишем данные:

Подставим формулу для вычисления ставки дисконтирования депозитного сертификата и получим ответ:

Получили значение равное 0,1, означающее, что ставка дисконтирования депозитного сертификата составляет 10%.

Литература

Алексеев В.Ф., Васильцов С.А., Журавлев В.И. Справочник. Математические и финансово-экономические функции Excel.

Алексеев В.Ф., Журавлев В.И., Делендик Е.В. Практикум. Операции над матрицами средствами электронной таблицы Excel.

Алексеев В.Ф., Журавлев В.И., Делендик Е.В. Практикум. Решение оптимизационных задач средствами электронной таблицы Excel.

Овчаренко Е.К.,Ильина О.П., Балыбердин Е.В. Финансово-экономические

расчеты в Excel. Издание 3-е, переработанное и дополненное – М.:

Информационно-издательский дом «Филин», 1999. – 328с.

32