Нелинейная регрессия в Microsoft Excel. Создание гиперссылок в системе MathCAD

СОДЕРЖАНИЕ

1. Microsoft Excel: нелинейная регрессия, проверка результатов регрессии

. MathCAD: создание и применение гиперссылок, основы работы с блоками документов

.1 Гиперссылка

.2 Основы работы с блоками документов

. Контур управления производством комплекса «Галактика»

. Задачи

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

.2 Построение графиков функции

.3 Финансовая задача 1

.4 Финансовая задача 2

Литература

1. MICROSOFT EXCEL: НЕЛИНЕЙНАЯ РЕГРЕССИЯ, ПРОВЕРКА РЕЗУЛЬТАТОВ РЕГРЕССИИ

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

Гипербола. Для приведения уравнения вида к линейному виду вводят новую переменную , тогда уравнение гиперболы принимает линейный вид . После этого используют формулы (1) и (2) для нахождений линейной функции, но вместо значений используются значения :

; .(3)

Экспонента. Для приведения к линейному виду уравнения экспоненты проведем логарифмирование:

;

;

.

Введем переменные и , тогда , откуда следует, что можно применять формулы (1) и (2), в которых вместо значений надо использовать :

; (4)

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

, .

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

Сила регрессионной связи для гиперболы и параболы определяется непосредственно по той же формуле что и для линейной модели.

Для анализа общего качества уравнения регрессии используют обычно множественный коэффициент детерминации R2, называемый также квадратом коэффициента множественной корреляции R. R2 (мера определенности) всегда находится в пределах интервала [0;1].

Если значение R2 близко к единице, это означает, что построенная модель объясняет почти всю изменчивость соответствующих переменных. И наоборот, значение R-квадрата, близкое к нулю, означает плохое качество построенной модели.

Коэффициент детерминации R2 показывает, на сколько процентов () найденная функция регрессии описывает связь между исходными значениями факторов X и Y

где - объясненная вариация; - общая вариация.

При вычислении коэффициента детерминации для экспоненты все значения параметра Y (исходные, регрессионные, среднее) необходимо заменить на их логарифмы, например, - на и т.д.

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

Для решения задачи регрессионного анализа в MS Excel выбираем в меню Сервис команду Анализ данных и инструмент анализа "Регрессия".

Рисунок 1.1. - Окно «Регрессия»

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

.Входной интервал Y - это диапазон данных по результативному признаку. Он должен состоять из одного столбца.

2.Входной интервал X - это диапазон ячеек, содержащих значения факторов (независимых переменных). Число входных диапазонов (столбцов) должно быть не больше 16.

.Флажок Метки, устанавливается в том случае, если в первой строке диапазона стоит заголовок.

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

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

.Выходной интервал/ Новый рабочий лист/ Новая рабочая книга - указать адрес верхней левой ячейки выходного диапазона.

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

.Флажок График нормальной вероятности необходимо сделать активным, если требуется вывести на лист точечный график зависимости наблюдаемых значений Y от автоматически формируемых интервалов перцентилей.

После нажатия кнопки ОК в выходном диапазоне получаем отчет.

2.MATHCAD: СОЗДАНИЕ И ПРИМЕНЕНИЕ ГИПЕРССЫЛОК, ОСНОВЫ РАБОТЫ С БЛОКАМИ ДОКУМЕНТОВ

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

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

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

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

Рисунок 2.1.1. - Подготовка к заданию гиперссылки

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

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

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

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

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

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

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

2.2 Основы работы с блоками документов

Документы системы MathCAD являются файлами, т. е. имеющими имена блоками хранения информации на магнитных дисках. Файлы можно создавать, загружать (открывать), записывать и распечатывать на принтере. Новый документ получает имя Untitled - безымянный и порядковый номер. Одновременно открыто может быть до 8-ми документов.

Возможные операции с файлами представлены в панели инструментов первой группой из трех кнопок:

1.New Worksheet (Создать) - создание нового документа с очисткой окна редактирования.

2.Open Worksheet (Открыть) - загрузка ранее созданного документа из диалогового окна.

.Save Worksheet (Сохранить) - запись текущего документа с его именем. Ниже мы рассмотрим эти операции более подробно.

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

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

1.Align Across (Выровнять по горизонтали) - блоки выравниваются по горизонтали.

2.Align Down (Выровнять вниз) - блоки выравниваются по вертикали, располагаясь сверху вниз.

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

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

1.Insert Function - вставка функции из списка, появляющегося (Вставить функции) в диалоговом окне.

2.Insert Utit (Вставить единицы) - вставка единиц измерения.

.Calculate (Пересчитать) - вычисление выделенного выражения.

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

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

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

регрессия гиперссылка контур excel mathcad

3. КОНТУР УПРАВЛЕНИЯ ПРОИЗВОДСТВОМ КОМПЛЕКСА «ГАЛАКТИКА»

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

·Модуль Контроллинг (решает задачи управленческого учета на предприятии, позволяет осуществлять расчет плановых и учет фактических затрат на производство с целью контроля и регулирования затрат в текущем периоде). Автоматизирует: Планово-экономический отдел;

·Модуль Корпоративное планирование (предназначен для планирования и учета производства и товародвижения в корпоративных кооперационных промышленных сетях). Автоматизирует: Планово-диспетчерский отдел; ПЭО; Службы Главного механика, Главного энергетика, Главного метролога;

·Модуль МТО (предназначен для автоматизации бизнес-процедур, связанных с планированием и контролем исполнения планов материально-технического снабжения). Автоматизирует: Отдел снабжения;

·Модуль Планирование производства (предназначен для плановых служб предприятия и позволяет решать широкий круг задач планирования: от создания плана выпуска готовой продукции до выдачи заданий цехам на производство полуфабрикатов и конечную сборку). Автоматизирует: Планово-диспетчерский отдел, ПЭО, Службы Главного механика, Главного энергетика, Главного метролога;

·Модуль Спецификации продуктов (предназначен для ведения и хранения информации о составе продукции и технологии ее изготовления). Автоматизирует: Технологический отдел, Проектно-конструкторский отдел, Отдел труда и зарплаты, Заводская лаборатория, Планово-производственный отдел;

·Модуль Управление заказами (предназначен для автоматизации бизнес-процедур, связанных с планированием и контролем исполнения планов по сбыту). Автоматизирует: Отдел сбыта;

·Модуль Управление производственной логистикой (предназначен для решения задач оперативного производственного учета посредством ведения первичных документов). Автоматизирует: Планово-диспетчерский отдел, Производство (цеха), Бухгалтерия;

·Модуль Управление ремонтами (предназначен для планирования потребностей в ресурсах, учета использования затрат на ремонтные и профилактические работы). Автоматизирует: Службы Главного, механика, Главного энергетика, Главного метролога.

Контур управления производством - решение для управления хозяйственной деятельностью предприятия с учетом современных стандартов управления ресурсами: MRP, MRP II.

В системе Галактика ERP реализованы основные функциональные элементы системы, поддерживающей стандарт MRP II:

формирование плана производства (MPS) на основании портфеля заказов и прогноза сбыта готовой продукции по периодам;

предварительная оценка выполнимости плана производства по ключевым ресурсам предприятия (оборудование, трудовые ресурсы, материалы);

корректировка плана производства при недостатке ресурсов или оценка объемов пополнения ресурсов: закупка оборудования, наем рабочей силы, сверхурочные работы, субподряд и т. д.;

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

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

контроль выполнения планов сбыта, производства и снабжения;

расчет нормативных и фактических затрат на производство, нормативной и фактической себестоимости продукции, анализ отклонений в затратах и себестоимости;

оценка экономических и финансовых показателей деятельности предприятия.

4. задачи

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

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

Стоимость перевозки единицы продукцииОбъем производства948821957712989918767620699515Объем потребления18201922

Решение средствами MS Excel.

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

Рисунок 4.1.1. - Исходные данные

На рис. представлена таблица, необходимая для решения задачи. Значения ячеек P получены суммированием значений по соответствующей строке, например, Р12=СУММ(K12:N12). Значения стоки 18, получены суммирование значений в соответствующем столбце, например, К18==СУММ(K12:K16).

Рисунок 4.1.2. - Подготовленная таблица для поиска решения

Теперь, используя исходные данные, введем на этом же листе требуемые объемы производства и стоимость перевозки (рис. 4.1.3.). Данные в ячейках D-G определяются по формулам D21=D12*K12; E21=E12*L12; F21=F12*M12; G21=G12*N12 и т.д. Значения в столбце I равны сумме значений соответствующих строк, например, I21=СУММ(D21:G21). Минимальные транспортные расходы будут показаны в ячейке I26 и вычисляются по формуле =СУММПРОИЗВ(D12:G16;K12:N16).

Рисунок 4.1.3. - Данные для расчета транспортных расходов

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

Рисунок 4.1.4. - Диалоговое окно «Поиск решения»

Поскольку в качестве критерия оптимизации нами выбрана минимизация транспортных расходов, в поле «Установить целевую ячейку» введите ссылку на ячейку, содержащую формулу расчета транспортных расходов. Чтобы минимизировать значение конечной ячейки путем изменения значений влияющих ячеек (влияющими, в данном случае это и изменяемые ячейки, являются ячейки, которые предназначены для хранения значений искомых неизвестных), переключатель установите в положение минимальному значению. В поле «Изменяя ячейки» введите ссылки на изменяемые ячейки, разделяя их запятыми; либо, если ячейки находятся рядом, указывая первую и последнюю ячейку, разделяя их двоеточием ($K$12:$N$16). Это означает, что для достижения минимальных транспортных расходов будут меняться значения в ячейках с K12 по N16.

В группе полей «Ограничения нажмите» кнопку «Добавить». Первое условие $P$12:$P$16 <= $I$12:$I$16. Оно означает, что значение в ячейке P12 должно быть меньше или равно значению в I12, в P13 меньше или равно, чем в I13, и так далее до P16 и I16. Так как невозможно вывести больше, чем произведено, первое значение должно быть не больше второго.

Второе условие $K$12:$N$16=целое. Оно означает, что значения цен должны быть целыми.

Третье, и последнее условие $D$18:$G$18<=$K$18:$N$18. Оно означает, что спрос должен быть удовлетворен полностью. Перевыполнение спроса допустимо, а недовыполнение - нет.

Введенные условия должны позволить найти наиболее оптимальный вариант решения задачи. Нажмите кнопку «Выполнить» для подбора решения.

Рисунок 4.1.5. - Решенная транспортная задача

Минимальные транспортные расходы при соблюдении всех условий равны 479 ден. ед.

Решение средствами Mathcad.

Сначала введем фиктивный столбец, равный избытку производства:

Рисунок 4.1.6. - Исходные данные

Дальнейшие расчеты производим в среде Mathcad.

Задаем начальные значения x.

задаем общую стоимость перевозок:

задаем условия:

используя встроенную функцию Mimimize, находим минимальные значения x1.x25.

находим минимальную стоимость перевозки:

Ответ: Минимальные транспортные расходы при соблюдении всех условий равны 479 ден. ед.

.2 Построение графиков функций

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

Решение средствами Excel.

Для построения графиков введем в ячейки от А2 до А42 значения от -2 до 2 с шагом 0,1. В ячейку В2 введем формулу, необходимую для построения графика функции y, в C2 - графика функции g, D2 - графика функции z и с помощью курсора протянем эти значения до ячеек B42, C42, D42 соответственно.

B2 = (1+EXP(3*A2))^(1/4); = ЕСЛИ(A2>0;2*(A2^2)*(COS(A2))^2;(3+SIN(A2))/(1+A2^2));= ЕСЛИ(A2<0;(ABS(A2))^(1/3);ЕСЛИ(A2>=1;(ABS(3-A2))/(1+A2);-2* A2 + ( A2/(1+A2))));

Рисунок 4.2.1. - Исходные данные

Для построения графиков воспользуемся пиктограммой «Мастер диаграмм»

Для всех трех графиков выбираем тип диаграммы «График» и подписи по x =A$2:$A$42. Значения рядов для графика функции y равны =$B$2:$B$42, для графика функции g - =C$2:$C$42, для графика функции z - $D$2:$D$42.

Рисунок 4.2.2. - График функции y

Рисунок 4.2.3. - График функции g

Рисунок 4.2.4. - График функции z

Решение средствами Mathcad.

Для построения графика функций введем значения х:

Далее зададим формулу по которой будет строиться график функции y:

Для построения графика выберем вкладку «графики»

После чего появиться шаблон, для построения графиков:

Рисунок 4.2.5. - Шаблон графика

По оси х введем переменную х, по оси y - y(x). График построен:

Рисунок 4.2.6. - График функции y

Аналогично строятся графики функций g и z, только с применением оператора if.

Рисунок 4.2.7. - График функции g

Рисунок 4.2.8. - График функции z

4.3 Финансовая задача 1

АО создает резервный фонд, для чего ежегодно в конце года переводит в банк по 10 млн. руб. Определите срок, за который на счету фирмы будет 100 млн. руб. при ставке процентов 0.1 и начислении процентов один раз в год.

, , ,

, лет.

Решение средствами Excel

Введем исходные данные (рис. 4.3.1.).

Рисунок 4.3.1. - Исходные данные

Введем в ячейку E7 формулу для расчета срока E7 =(LOG10((E5*E2)/(E4)+1))/(LOG10(1+E2)). Получили 7, 2725 лет.

Рисунок 4.3.2. - Результат решения задачи

Решение средствами Mathcad.

Введем исходные данные:

Введем формулу для расчета срока:

Введем n= и получим срок:

Ответ: срок, за который на счету фирмы будет 100 млн. руб. при ставке процентов 0.1 и начислении процентов один раз в год равен 7,273 лет.

4.4 Финансовая задача 2

Облигация номиналом 100000 руб. имеет купон 15% годовых с выплатой 1 раз в квартал. Определите размер купонной выплаты.

Решение средствами Excel.

Введем исходные данные:

номиналS100000купонi15%выплата 1 раз в квартал размер выплатыP Рисунок 4.4.1. - Исходные данные

Для решения задачи воспользуемся функцией ПРПЛТ(ставка;период;кпер;пс;бс;тип), которая возвращает сумму платежей процентов по инвестиции за данный период на основе постоянства сумм периодических платежей и постоянства процентной ставки. Введем данные в формулу:

Рисунок 4.4.2. - Ввод формулы

Получили, что размер купонной выплаты равен 3750 руб.

номиналS100000купонi15%выплата 1 раз в квартал размер выплатыP3 750 Рисунок 4.4.2. - Результат решения задачи

Ответ: Размер купонной выплаты 3750 руб.

Решение задачи средствами Mathcad.

Введем исходные данные:

Для расчета купонной выплаты воспользуемся формулой P = qN/100, где P - купонная выплата, q - купон, N - номинал. Так как выплаты происходят раз в квартал, то купонная выплата равна P = (qN/100)/4.

Рисунок 4.4.3. - Решение задачи

Ответ: размер купонной выплаты 3750 руб.

ЛИТЕРАТУРА

1.В.Ф. Алексеев, В.И. Журавлев, Е.В. Делендик «Практикум «Решение инженерно-экономических задач средствами MathCAD», БГУИР, электронный учебник, 39 с.

2.В.Ф. Алексеев, В.И. Журавлев, Е.В. Делендик «Практикум «Построение графиков, поверхностей и решение нелинейных уравнений средствами электронной таблицы Excel», БГУИР, электронный учебник, 30 с.

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

.В.Ф. Алексеев, С.А. Васильцов, В.И. Журавлев «Математические и финансово-экономические функции Excel. Справочник», БГУИР, электронный учебник, 107 с.

5.www.galaktika.by <http://www.galaktika.by>