Прикладные системы обработки данных. присод_обмен. Задача 1 22 Задача 2 26 Задача 3 29 Задача 4 34 Список использованных источников 36 Теоретические вопросы

• Название: Задача 1 22 Задача 2 26 Задача 3 29 Задача 4 34 Список использованных источников 36 Теоретические вопросы
• Анкор: Прикладные системы обработки данных
• Дата: 24.12.2020
• Размер: 298.69 Kb.
• Имя файла: присод_обмен.docx
• Тип: Задача; #163743

Содержание

Теоретические вопросы 2

1.Решение системы нелинейных уравнений в Excel 2

2.Поиск оптимальных решений средствами Excel: подбор параметров для финансовых функций, использование инструмента Поиск решения. 13

3.Математический пакет MathCAD для решения инженерно-экономических задач: создание и применение гиперссылок. 19

Задачи 22

Задача №1 22

Задача №2 26

Задача №3 29

Задача №4 34

Список использованных источников 36

Теоретические вопросы

1. 
   Решение системы нелинейных уравнений в Excel
   

1.1 Циклические ссылки

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

Рассмотрим задачу нахождения корня уравнения методом Ньютона с использованием циклических ссылок. Возьмем для примера квадратное уравнение: х² – 5х+6=0, графическое представление которого приведено на рис. 1.1.


Рисунок 1.1 – График функции
Найти корень этого (и любого другого) уравнения можно, используя всего одну ячейку Excel.

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

Выберем произвольную ячейку, присвоим ей новое имя, скажем – Х, и введем в нее рекуррентную формулу, задающую вычисления по методу Ньютона:  ,

где F и F1 задают соответственно выражения для вычисления значений функции и ее производной. Для нашего квадратного уравнения после ввода формулы в ячейке появится значение 2, соответствующее одному из корней уравнения (рис. 8). В нашем случае начальное приближение не задавалось, итерационный вычислительный процесс начинался со значения, по умолчанию хранимого в ячейке Х и равного нулю. А как получить второй корень? Обычно это можно сделать изменением начального приближения. Решать проблему задания начальных установок в каждом случае можно по-разному. Мы продемонстрируем один прием, основанный на использовании функции ЕСЛИ. С целью повышения наглядности вычислений ячейкам были присвоены содержательные имена (рис. 1.2).


Рисунок 1.2 – определение начальных установок
• В ячейку Х_нач (В4) заносим начальное приближение – 5.

• В ячейку Х_текущ (С4) записываем формулу:

=ЕСЛИ(Х_текущ=0;Х_нач; Х_текущ-(Х_текущ^2-5*Х_текущ+6)/(2*Х_текущ-5)).

• В ячейку D4 помещаем формулу, задающую вычисление значения функции в точке Х_текущ, что позволит следить за процессом решения.

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

• Чтобы сменить начальное приближение, недостаточно изменить содержимое ячейки Х_нач и запустить процесс вычислений. В этом случае вычисления будут продолжены, начиная с последнего вычисленного значения. Чтобы обнулить значение, хранящееся в ячейке Х_текущ, нужно заново записать туда формулу. Для этого достаточно для редактирования выбрать ячейку, содержащую формулу, дважды щелкнув мышью на ней (при этом содержимое ячейки отобразится в строке формул). Щелчок по кнопке (нажатие клавиши) Enter запустит вычисления с новым начальным приближением.

1.2 Подбор параметра

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

Возьмем в качестве примера все то же квадратное уравнение х² –5х+6=0.



Рисунок 1.3 –Окно диалога Подбор параметра
Для нахождения корней уравнения выполним следующие действия:

• В ячейку С3 (рис. 1.3) введем формулу для вычисления значения функции, стоящей в уравнении слева от знака равенства. В качестве аргумента используем ссылку на ячейку С2, т.е. =С2^2-5*C2+6.

• В окне диалога Подбор параметра (рис. 3) в поле Установить в ячейке введем ссылку на ячейку с формулой, в поле Значение – ожидаемый результат, в поле Изменяя значения ячейки – ссылку на ячейку, в которой будет храниться значение подбираемого параметра (содержимое этой ячейки не может быть формулой).

• После нажатия на кнопку «ОК» Excel выведет окно диалога Результат подбора параметра. Если подобранное значение необходимо сохранить, то нажмите на «ОК», и результат будет сохранен в ячейке, заданной ранее в поле Изменяя значения ячейки. Для восстановления значения, которое было в ячейке С2 до использования команды Подбор параметра, нажмите кнопку Отмена.

При подборе параметра Excel использует итерационный (циклический) процесс. Количество итераций и точность устанавливаются в меню Сервис/Параметры/вкладка Вычисления. Если Excel выполняет сложную задачу подбора параметра, можно нажать кнопку Пауза в окне диалога Результат подбора параметра и прервать вычисление, а затем нажать кнопку Шаг, чтобы выполнить очередную итерацию и просмотреть результат. При решении задачи в пошаговом режиме появляется кнопка Продолжить – для возврата в обычный режим подбора параметра.

Вернемся к примеру. Опять возникает вопрос: как получить второй корень? Как и в предыдущем случае необходимо задать начальное приближение.



Рисунок 1.4 – Поиск второго корня
Это можно сделать следующим образом (рис. 1.4,а):

• В ячейку Х (С2) вводим начальное приближение.

• В ячейку _Хi (С3) вводим формулу для вычисления очередного приближения к корню, т.е. =X-(X^2-5*X+6)/(2*X-5). • В ячейку С4 поместим формулу, задающую вычисление значения функции, стоящей в левой части исходного уравнения, в точке Х_i.

• После этого выбираем команду Подбор параметра, где в качестве изменяемой ячейки принимаем ячейку С2. Результат вычислений изображен на рис. 1.4,б (в ячейке С2 – конечное значение, а в ячейке С3 – предыдущее).

Однако все это можно сделать и несколько проще. Для того чтобы найти второй корень, достаточно в качестве начального приближения (рис. 3) в ячейку C2 поместить константу 5 и после этого запустить процесс Подбор параметра.

1.3 Поиск решения

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

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

Найти: х₁ ,х₂, … , х_n

такие, что: F(х₁ ,х₂, … , х_n) → {Max; Min; = Value}

при ограничениях: G(х₁ ,х₂, … , х_n) → {≤ Value; ≥ Value; = Value}

Искомые переменные – ячейки рабочего листа Excel – называются регулируемыми ячейками. Целевая функция F(х₁ ,х₂, … , х_n), называемая иногда просто целью, должна задаваться в виде формулы в ячейке рабочего листа. Эта формула может содержать функции, определенные пользователем, и должна зависеть (ссылаться) от регулируемых ячеек. В момент постановки задачи определяется, что делать с целевой функцией. Возможен выбор одного из вариантов:

• найти максимум целевой функции F(х₁ ,х₂, … , х_n);

• найти минимум целевой функции F(х₁ ,х₂, … , х_n);

• добиться того, чтобы целевая функция F(х₁ ,х₂, … , х_n) имела фиксированное значение: F(х₁ ,х₂, … , х_n) = а.

Функции G(х₁ ,х₂, … , х_n) называются ограничениями. Их можно задать как в виде равенств, так и неравенств. На регулируемые ячейки можно наложить дополнительные ограничения: неотрицательности и/или целочисленности, тогда искомое решение ищется в области положительных и/или целых чисел.

Под эту постановку попадает самый широкий круг задач оптимизации, в том числе решение различных уравнений и систем уравнений, задачи линейного и нелинейного программирования. Такие задачи обычно проще сформулировать, чем решать. И тогда для решения конкретной оптимизационной задачи требуется специально для нее сконструированный метод. Решатель имеет в своем арсенале мощные средства решения подобных задач: метод обобщенного градиента, симплекс–метод, метод ветвей и границ.

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


Рисунок 1.5 –Окно диалога Поиска решений
После открытия диалога Поиск решения (рис. 1.5) необходимо выполнить следующие действия:

1) в поле Установить целевую ячейку ввести адрес ячейки, содержащей формулу для вычисления значений оптимизируемой функции, в нашем примере целевая ячейка – это С4, а формула в ней имеет вид: =C3^2-5*C3+6;

2) для максимизации значения целевой ячейки, установить переключатель максимальному значению в положение , для минимизации используется переключатель минимальному значению, в нашем случае устанавливаем переключатель в положение значению и вводим значение 0;

3) в поле Изменяя ячейки ввести адреса изменяемых ячеек, т.е. аргументов целевой функции (С3), разделяя их знаком «;» (или щелкая Рис. 12. Окно диалога Поиск решения 14 мышью при нажатой клавише Сtrl на соответствующих ячейках), для автоматического поиска всех влияющих на решение ячеек используется кнопка Предположить;

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

5) для запуска процесса поиска решения нажать кнопку Выполнить.

Для сохранения полученного решения необходимо использовать переключатель Сохранить найденное решение в открывшемся окне диалога Результаты поиска решения. После чего рабочий лист примет вид, представленный на рис. 1.6.


Рисунок 1.6 – Результаты поиска
Полученное решение зависит от выбора начального приближения, которое задается в ячейке С4 (аргумент функции). Если в качестве начального приближения в ячейку С4 ввести значение, равное 1,0, то с помощью Поиска решения найдем второй корень, равный 2,0.



Рисунок 1.7 – Настройка параметров Решателя

Опции, управляющие работой Поиска решения, задаваемые в окне Параметры (окно появляется, если нажать на кнопку Параметры окна Поиск решения), следующие (рис. 1.7):

• Максимальное время – ограничивает время, отведенное на процесс поиска решения (по умолчанию задано 100 секунд, что достаточно для задач, имеющих около 10 ограничений, если задача большой размерности, то время необходимо увеличить).

• Предельное число итераций – еще один способ ограничения времени поиска путем задания максимального числа итераций. По умолчанию задано 100, и, чаще всего, если решение не получено за 100 итераций, то при увеличении их количества (в поле можно ввести время, не превышающее 32767 секунд) вероятность получить результат мала. Лучше попытаться изменить начальное приближение и запустить процесс поиска заново.

•Относительная погрешность – задает точность, с которой определяется соответствие ячейки целевому значению или приближение к указанным ограничениям (десятичная дробь от 0 до 1).

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

• Сходимость – когда относительное изменение значения в целевой ячейке за последние пять итераций становится меньше числа (дробь из интервала от 0 до 1), указанного в данном параметре, поиск прекращается.

• Линейная модель – этот флажок следует включать, когда целевая функция и ограничения – линейные функции. Это ускоряет процесс поиска решения.

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

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

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

• Оценки – эта группа служит для указания метода экстраполяции – линейная или квадратичная, – используемого для получения исходных оценок значений переменных в каждом одномерном поиске. Линейная служит для использования линейной экстраполяции вдоль касательного вектора. Квадратичная служит для использования квадратичной экстраполяции, которая дает лучшие результаты при решении нелинейных задач.

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

• Метод поиска – служит для выбора алгоритма оптимизации. Метод Ньютона был рассмотрен ранее. В Методе сопряженных градиентов запрашивается меньше памяти, но выполняется больше итераций, чем в методе Ньютона. Данный метод следует использовать, если задача достаточно велика и необходимо экономить память, а также если итерации дают слишком малое отличие в последовательных приближениях.

Сохранить модель поиска решения можно следующими способами:

1) при сохранении книги Excel после поиска решения все значения, введенные в окнах диалога Поиск решения, сохраняются вместе с данными рабочего листа. С каждым рабочим листом в рабочей книге можно сохранить один набор значений параметров Поиска решения;

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

3) еще один способ сохранения параметров поиска – сохранение их в виде именованных сценариев. Для этого необходимо нажать на кнопку Сохранить сценарий диалогового окна Результаты поиска решений. Кроме вставки оптимальных значений в изменяемые ячейки Поиск решения позволяет представлять результаты в виде трех отчетов: Результаты, Устойчивость и Пределы. Для генерации одного или нескольких отчетов необходимо выделить их названия в окне диалога Результаты поиска решения.

2. 
   Поиск оптимальных решений средствами Excel: подбор параметров для финансовых функций, использование инструмента Поиск решения.
   

Финансовые функции

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

БЗ – расчет наращенной суммы капитала при постоянной процентной ставке и равномерных периодических выплатах;

БЗРАСПИС - возвращает будущее значение основного капитала после начисления сложных процентов. Функция используется для вычисления будущего значения инвестиции с переменной процентной ставкой.

ПЗ – расчет эквивалента будущей стоимости серии равномерных периодических платежей при постоянной процентной ставке;

КПЕР – количество периодов, за которые исходная сумма достигнет требуемой величины при постоянной процентной ставке и равномерных периодических выплатах;

НОРМА – величина процентной ставки, при которой исходная сумма достигнет требуемой величины при постоянных периодических выплатах; ППЛАТ – величина периодических выплат, которые позволят достичь заданной величины вклада за определенный период при начальной сумме вложений и постоянной процентной ставке. Периодические выплаты включают основной платеж и плату по процентам, возвращаемые функциями ОСНПЛАТ и ПЛПРОЦ.

ПРОЦПЛАТ - вычисляет выплаты за указанный период выплат.

Финансовые функции позволяют решать задачи двух типов:

• 
  Накопление капитала, размещенного на депозите в виде вклада, либо выданная в виде ссуды;
  
• 
  Пользование заемными средствами.
  

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

Замечание 1. В формулах вкладываемые денежные суммы (например, депозитные вклады) обозначаются отрицательными числами, а полученные денежные суммы – положительными (например, дивиденды). Замечание 2. Следует обратить внимание на выбор единиц измерения аргументов СТАВКА (НОРМА) и КПЕР. Для аргументов ставка и кпер должны использоваться согласованные единицы измерения (один и тот же период, например месяц). Если по двухгодичному займу при ставке 18 процентов годовых делаются ежемесячные выплаты, то в формуле нужно использовать значение 18%/12 для задания аргумента СТАВКА и значение 2*12 для аргумента КПЕР.
Использование технологий подбора параметра и построения сценариев

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

Часто удобно проанализировать ситуацию для нескольких возможных вариантов параметров. Это возможно с помощью меню Сервис/ Сценарии. В диалоговом окне Диспетчера сценария нужно нажать кнопку Добавить для создания первого сценария.

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

После нажатия «ОК» появится окно Значения параметров сценария, в поля которого нужно ввести значения параметров для первого сценария.

Таким образом, обычно создают несколько сценариев.

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

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

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

• 
  количество неизвестных (decision variable) – 200;
  
• 
  количество формульных ограничений (explicit constraint) на неизвестные – 100;
  
• 
  количество предельных условий (simple constraint) на неизвестные – 400.
  

По умолчанию в Excel надстройка Поиск решения отключена. Чтобы активизировать ее в Excel 2007, щелкните значок Кнопка Microsoft Office , щелкните Параметры Excel, а затем выберите категорию Надстройки. В поле Управление выберите значение Надстройки Excel и нажмите кнопку Перейти. В поле Доступные надстройки установите флажок рядом с пунктом Поиск решения и нажмите кнопку ОК.

В Excel 2003 и ниже выберите команду Сервис/Надстройки, в появившемся диалоговом окне Надстройки установите флажок Поиск решения и щелкните на кнопке ОК. Если вслед за этим на экране появится диалоговое окно с предложением подтвердить ваши намерения, щелкните на кнопке Да.

Процедура поиска решения 

1. Создадим таблицу с формулами, которые устанавливают связи между ячейками (рис. 1.8).


Рисунок 1.8 – Пример задачи линейного программирования

3. 
   Выделим целевую ячейку, которая должна принять необходимое значение, и выберите команду:
   

- В Excel 2007 Данные/Анализ/Поиск решения;

- В Excel 2003 и ниже Tools >Solver (Сервис > Поиск решения). Поле Set Target Cell (Установить целевую ячейку) открывшегося диалогового окна «Поиск решения» (рис 1.9) будет содержать адрес целевой ячейки.


Рисунок 1.9 – Окно «Поиск решения»
3. Установите переключатели Equal To (Равной), задающие значение целевой ячейки, — Мах (максимальному значению), Min (минимальному значению) или Value of (значению). В последнем случае введите значение в поле справа (рис 1.10).

4. Укажите в поле By Changing Cells (Изменяя ячейки), в каких ячейках программа должна изменять значения в поисках оптимального результата (рис 1.10).

5. Создайте ограничения в списке Subject to the Constraints (Ограничения). Для этого щелкните на кнопке Add (Добавить) и в диалоговом окне Add Constraint (Добавление ограничения) определите ограничение (рис 1.10).

6. Щелкните на кнопке на кнопке Options (Параметры), и в появившемся окне установите переключатель Неотрицательные значения (если переменные должны быть позитивными числами), Линейная модель (если задача, которую вы решаете, относится к линейным моделям)

7. Щелкнув на кнопке Solver (Выполнить), запустите процесс поиска решения.

8. Когда появится диалоговое окно Solver Results (Результаты поиска решения (рис 1.10)), выберите переключатель Keep Solve Solution (Сохранить найденное решение) или Restore Original Values (Восстановить исходные значения).


Рисунок 1.10 – Окно «Результаты поиска решения»
9. Щелкните на кнопке ОК.

3. 
   Математический пакет MathCAD для решения инженерно-экономических задач: создание и применение гиперссылок.
   

В современных электронных книгах и в документах сети Internet широко используются гиперссылки. Их разновидностью являются гипертекстовые ссылки. Гипертекстовая ссылка — это выделенное слово или фраза, активизация которой вызывает переход к какому-либо объекту, — например, к новому документу или к файлу. Гиперссылки позволяют организовать не просто линейные, а произвольные переходы от одного объекта к другому. Гипертекстовые ссылки в MathCAD имеют вид подчеркнутых снизу слов или фраз. Они организуются просто. Вначале текст ссылки выделяется, а затем нажимается кнопка инструментальной панели Insert Hyperlink. В появившемся простом окне нужно указать полное (с путем) имя файла, который будет загружаться, и отображаться в момент активизации фрагмента - гиперссылки. Можно также задать сообщение об ошибке, если файл не будет найден. С помощью гиперссылок можно готовить в среде MathCAD электронные учебники и книги высокого качества, имеющие множество разделов, качественные тексты с разнообразными выделениями, математические формулы и графики. Важно отметить, что такие учебники являются «живыми» — все примеры в них работают и их можно использовать с различными исходными данными, задаваемыми пользователями.

Гиперссылка (Hyperlink)

Гиперссылки — важный элемент создания сложных документов, обучающих программ и справочных систем. Напомним, что гиперссылка в системе MathCAD представляется подчеркнутым словом, при активизации которого вызывается связанный с ней документ (файл).

Для создания гиперссылки должен быть создан текстовый блок, в котором нужно выделить некоторый фрагмент текста, чаще всего какое-либо слово. Начало создания гиперссылки показано на рис. 2.1.1.

После этого надо выполнить операцию Hyperlink (Гиперссылка) с опцией New (Новая) для вывода диалогового окна задания гиперссылки Оно также показано на рис 1.11 под текстовым блоком. В этом окне следует указать в верхнем свободном поле полное имя файла, который будет вызываться данной гиперссылкой. В другом поле можно ввести текст сообщения, которое будет появляться в строке состояния (внизу экрана системы) при установке курсора мыши на гиперссылку.


Рисунок 1.11 — Подготовка к заданию гиперссылки
Обычно точное имя файла (с полным путем доступа к нему) запомнить трудно. Поэтому, активизируя клавишу Browse (Обзор) окна задания гиперссылки, можно вызвать окно поиска файла.

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

Для завершения создания гиперссылки достаточно нажать кнопку ОК окна создания гиперссылки. При отказе от ее создания надо нажать кнопку Cancel.

Если установить курсор мыши на гиперссылку, то в строке состояния (внизу экрана системы) появится сообщение, которое было задано ранее. Такие сообщения очень полезны при большом числе гиперссылок, поскольку позволяют судить об их назначении.

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

После ознакомления с вызываемым документом его можно закрыть. До этого возможно редактирование документа. Объектами гиперссылок Могут быть не только документы. Можно, к примеру, дать гиперссылку на рисунок, создаваемый графическим редактором, на электронную таблицу и т. д. Все это позволяет готовить вполне современные и удобные в применении документы не только расчетного, но и информационно-справочного и учебного характера.

Гиперссылку можно убрать с помощью опции Erase (Уничтожить), а также отредактировать, используя опцию Edit (Редактировать), выводящую окно редактирования гиперссылки.
Задачи

Задача №1

1. Разработать алгоритм нахождения значений заданной кусочно-ломаной функции.

2. На основании алгоритма построить электронную таблицу для вычисления значений кусочно-ломаной функций в диапазоне двух периодов, с заданным шагом h=0,1.

3. При решении задачи в Excel для вычисления значений функции при необходимости использовать встроенные функции ЕСЛИ и ОСТАТ.

4. По табличным данным с помощью Мастера диаграмм построить график функции (тип диаграммы - точечная).




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

Для построения графика функции необходимо сначала построить таблицу ее значений при различных значениях аргумента, причем аргумент для всех наших четырех функций пусть изменяется с фиксированным шагом 0,1. Выбор этого шага обусловлен необходимостью более наглядного отображения значения функции на интервале табуляции. Создается таблица, в ячейку А2 вводится 0, фиксируется курсор и выбирается команда правка/заполнить/прогрессия. После чего появится диалоговое окно

Прогрессия (рис. 2.1), которое заполняется следующим образом:

• 
  В группе расположение устанавливается переключатель по столбцам;
  
• 
  В группе тип – в положение арифметическая;
  
• 
  В поле шаг вводится значение шага 0,1;
  
• 
  В поле предельное значение устанавливается 3,9.
  

Выполняется команда ок.



Рисунок 2.1 – Окно «Прогрессия»
В ячейку В3 (рис. 2.2) вводится формула:

=ЕСЛИ(И(A3>=0;A3<1);EXP(2*A3); ЕСЛИ (И(A3>=1;A3<2);3-A3;ЕСЛИ(И(A3>=2;A3<3);КОРЕНЬ(A3+2);4-A3))) и нажимается enter. Указатель мыши устанавливается на маркере заполнения как показано ниже на рисунке и, при удержании левой кнопки мыши, протаскивается вниз до тех пор, пока не получится числовой ряд нужной длины.


Рисунок 2.2 – Таблица координат графика
Для построения графика функции выделяется диапазон ячеек А3:В42, вызывается мастер диаграмм с помощью команды вставка/диаграмма. Появится диалоговое окно мастер диаграмм, в котором выбирается график с маркерами, помечающими точки данных. График построен (рис. 2.3).



Рисунок 2.3 - Решение задачи в Microsoft Excel
Решение задачи в MathCAD:

Вводится график функции. Для этого вводится , <(>, , <)>, +<:>, добавляется оператор ― «Add line» из раздела ― «Programming», вводится уравнение системы для первого отрезка [0;1), добавляется оператора «if» из раздела ― «Programming». Повторяется ввод уравнений системы для оставшихся трех отрезков.

Строится таблица значений функции на отрезке [0;4). Определяются значения независимой переменной с шагом 0,1. Для этого вводится ,+<:>,<0>,<0.1>,<3.9>. Чтобы построить таблицу значений, вводится ,<(>,,<)>,<=>. На рабочем поле появится таблица значений.

Строится график функции. В панели «Graph» необходимо нажать на изображение

графика. В нижнюю помеченную позицию вводится имя аргумента, в помеченной позиции с левой стороны вводится «f(x)» и щелкается вне прямоугольной рамки. График построен (рис.2.4).



Рисунок 2.4 – Решение задачи в MathCAD

Задача №2

Магазин оптовой торговли реализует три вида продук­ции А, Б и В. Для этого используются два ограниченных ресурса — полезная площадь помещений, которая с учетом коэффициента обора­чиваемости составляет 450 м², и рабочее время работников магазина — 600 человеко-часов. Товарооборот должен быть не менее 240 тыс.$. Необхо­димо разработать план товарооборота, доставляющий максимум при­были. Затраты ресурсов на реализацию и получаемая при этом прибыль представлены в таблице.

Ресурсы	Затраты ресурсов			Объем ресурса
	А	Б	В
Полезная площадь, м2	1,5	2	3	450
Рабочее время, человеко-часов	3	2	1,5	600
Прибыль, тыс.долларов	50	65	70

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

Прибыль при производстве изделий равна:

F(x₁, x₂, x₃) = 50x₁ + 65x₂ + 70x₃,

где x₁, x₂, x₃ – объем производства изделий А, Б, В соответственно.

Необходимо найти оптимальный объем производства изделий каждого вида. При этом объем производства ограничен следующими условиями:



Вводим произвольные начальные значения для переменных x₁, x₂, x₃ (рис. 2.5). Вводим коэффициенты переменных в целевой функции.

Для ячейки G7 вводится формула =СУММПРОИЗВ(B7:D7;B5:D5) и копируется в ячейки:

G3 =СУММПРОИЗВ(B7:D7;B3:D3),

G4 =СУММПРОИЗВ(B7:D7;B4:D4),

G5 =СУММПРОИЗВ(B7:D7;B5:D5).


Рисунок 2.5 – Таблица исходных данных
Вызывается команда «Сервис – Поиск решения» (рис. 2.6) и в появившемся диалоговом окне устанавливается целевая ячейка G7, массив изменяемых значений (в данном случае – массив значений переменных) и ограничения, как показано на рисунке ниже.


Рисунок 2.6 – Параметры поиска решения

После заполнения окна поиск решений по кнопке параметры необходимо перейти в окно «параметры поиска решений» и установить там флажки напротив полей: линейная модель и неотрицательные значения. Далее выполняется команда ОК, и нажимается клавиша «выполнить» в окне «поиск решений».

При правильном выполнении на экране появится сообщение о найденном решении нажимаем ОК и получаем результат (рис. 2.7).


Рисунок 2.7 - Решение задачи в Microsoft Excel

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

Вводится классическая целевая функция f(x₁, x₂), задаются начальные значения переменных. В области действия функции «Given» вводятся классические ограничения на условие задачи. Далее вводится стандартная функция «Maximize», с помощью которой получаются искомые максимизированной матрицы и целевая функция (рис. 2.8).



Рисунок 2.8 – Решение задачи в среде MathCAD

Задача №3

При решении в электронных таблицах занесем данные в ячейки (рис. 2.5). Определители матриц найдем с помощью функции =МОПРЕД(), где в скобках указывается диапазон ячеек, содержащих матрицу: по диагонали -левый верхний угол – правый нижний.

1. 
   Чтобы найти решение системы методом Крамера нужно найти определитель матрицы из коэффициентов уравнений этой системы:
   

Рисунок 2.5 – Таблица исходных данных
А также найти значения определителей  _k, полученных заменой в определителе  столбца из коэффициентов при неизвестной x_k столбцом свободных членов системы.



Рисунок 2.6 – Матрицы для расчета определителей


После нахождения нужных определителей решение находится простым делением. Получим следующее решение (рис 2.7):


Рисунок 2.7 - Решение задачи в Microsoft Excel

2. 
   Чтобы решить систему уравнений с помощью обратной матрицы нужно воспользоваться свойством , где Х – решение системы, А – матрица коэффициентов системы, В – столбец свободных членов. Для решения в Excel занесем данные в таблицу (рис 2.8).
   
3. 
   
   

Рисунок 2.8 – Таблица исходных данных
С помощью функции =МОБР() вычислим обратную матрицу. Здесь нужно обратить внимание на то, что значение обратной матрицы есть также матрица, поэтому для ее вычисления необходимо выделить область ячеек для выведения результата, набрать формулу =МОБР(), нажать клавишу F12, а затем комбинацию клавиш Ctrl+Shift+Enter. В скобках функции МОБР указывается диапазон клеток, для которых находим обратную матрицу. Для нахождения решения выделим 3 ячейки для вывода результата и воспользуемся функцией =МУМНОЖ() для умножения матриц. С помощью этой функции умножим обратную матрицу на столбец свободных членов. Так как результат умножения есть матрица, то для получения результата проделаем те же действия: нажать клавишу F12, а затем комбинацию клавиш Ctrl+Shift+Enter и получим решение (рис 2.9):



Рисунок 2.9 – Решение задачи в Microsoft Excel
3. Для нахождения значения выражения 2А - (А² + B)B, где А и В –матрицы, воспользуемся функциями МУМНОЖ – для умножения двух матриц. Для того, чтобы сложить или вычесть 2 матрицы, можно сначала произвести эту операцию с первыми элементами матриц, а затем использовать автозаполнение. Для выполнения автозаполнения нужно подвести указатель мыши к правому нижнему углу клетки с формулой (черный квадрат), при этом указатель мыши примет вид черного крестика. Нажать левую кнопку мыши и удерживая ее размножить формулу на соседние ячейки. При этом произойдет копирование относительных ссылок: ссылки в формуле будут меняться относительно. Результат решения (рис. 2.10):


Рисунок 2.10 – Решение задачи в Microsoft Excel
Решение в MathCAD:

1. При решении в MathCAD аналогично вносим исходные данные, с помощью операнда  находим определители матриц и решение системы с помощью операции деления (рис. 2.11).



Рисунок 2.11 - Решение задачи в MathCAD
2. Для нахождения результата с помощью MathCAD аналогично введем исходные данные, затем с помощью оператора А^-1 вычислим обратную матрицу и найдем решение системы (рис. 2.12).


Рисунок 2.12 - Решение задачи в MathCAD
3. Для решения этой задачи с помощью MathCAD необходимо ввести исходные данные, расчеты производить поэтапно с помощью векторных значений. В результате получим ответ (рис. 2.13):



Рисунок 2.13 - Решение задачи в MathCAD

Задача №4

Определите текущую стоимость обычных ежеквартальных платежей размером 350 тыс. руб. в течение семи лет, если ставка процента – 11% годовых.

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

Для решения задачи используем встроенную функцию Excel – ПС.

Функция ПС возвращает приведенную (к текущему моменту) стоимость инвестиции. Приведенная (нынешняя) стоимость представляет собой общую сумму, которая на настоящий момент равноценна ряду будущих выплат. Например, когда вы занимаете деньги, сумма займа является приведенной (нынешней) стоимостью для заимодавца.

Синтаксис: ПС(ставка ;кпер;плт;бс;тип),

где Ставка    — процентная ставка за период: 11%/4 (т.к. квартал это 3 месяца, а в году 4 квартала);

Кпер    — общее число периодов платежей по аннуитету: 7*4 = 28;

Плт    — выплата, производимая в каждый период: -350 000 руб.;

Тип    — число 0 или 1, обозначающее, когда должна производиться выплата: 0.

Запишем значения аргументов функции в соответствующих полях.



Рис 2.14 - Аргументы функции ПС в MS Excel

В итоге получаем (рис 2.15):


Рис 2.15 - Решение задачи в Microsoft Excel
Решение задачи в MathCAD:



где:

S – конечная стоимость;

P - первоначальная стоимость;

R – размер погасительного платежа;

pr - процентная ставка (процентов годовых / 100);

d - количество месяцев в квартале;

y - количество месяцев в год

m - число периодов для начисления в году;

n - срок платежей (в годах).

Инициализируем переменные и введем объединенную формулу (рис. 2.16):



Рисунок 2.16 - Решение задачи в MathCAD

Список использованных источников

1. 
   Алексеев В.Ф., Васильцов С.А., Журавлев В.И. Справочник. Математические и финансово-экономические функции Excel.
   
2. 
   Алексеев В.Ф., Журавлев В.И., Делендик Е.В. Практикум. Операции над матрицами средствами электронной таблицы Excel.
   
3. 
   Алексеев В.Ф., Журавлев В.И., Делендик Е.В. Практикум. Решение оптимизационных задач средствами электронной таблицы Excel.
   
4. 
   Пимонов А.Г., Тынкевич М.А. Решение уравнений средствами Excel.
   
5. 
   Овчаренко Е.К., Ильина О.П., Балыбердин Е.В. Финансово-экономические расчеты в Excel. Издание 3-е, переработанное и дополненное – М.: Информационно-издательский дом «Филин», 1999. – 328с.