Читать «Как предсказать курс доллара. Расчеты в Excel для снижения риска проигрыша» онлайн - страница 17

Y расчет = 0,1231X + 32,1005=0,1231*1+ 32,1005=32,2236

Таким образом для наблюдения 1, то есть для торгового дня с порядковым №1 (27 июня 2014 года) расчетный курс доллара к рублю оказался равен 32,2236 рублям.

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

Остаток для наблюдения 1 = 33,6306-32,2236=1,4070 рублей.

Таблица 2.5. Вывод остатка

Источник: расчеты автора

Теперь нам нужно оценить относительную точность уравнения регрессии с учетом величины полученных остатков. С этой целью построим таблицу 2.6, в которую возьмем данные из таблицы 2.5. Плюс правее раздела «Наблюдение» добавим в новую таблицу раздел «Фактический курс доллара к рублю». Кроме того, для последующих расчетов в таблицу 2.6 добавим правее раздела «Остатки» еще разделы «Остатки по модулю» и «Средняя ошибка аппроксимации, в %».

«Остатки по модулю» в Excel легко найти с помощью функции ABS. Сделать это можно двумя способами.

Первый способ. В пустой ячейке (ячейка D2), расположенной в первом ряду под заголовком столбца «Остатки по модулю» (ячейка C2), нужно напечатать английскими буквами = ABS. Сразу всплывет подсказка этой функции, которую нужно щелкнуть левой кнопкой мышки, а затем навести курсор на ячейку с остатком для наблюдения 1 и закрыть скобку этой функции. Таким образом получим следующий результат: ABS(1,4070)= 1,4070, то есть «Остаток по модулю» для наблюдения 1 равен 1,4070.

Второй способ. Нужно навести курсор на ту же пустую ячейку (ячейка D2). Потом надо щелкнуть левой кнопкой мышки иконку ФОРМУЛЫ, расположенную вверху рабочего листа Excel, а затем щелкнуть появившуюся иконку Fx. После этого на экране возникнет диалоговое окно МАСТЕР ФУНКЦИЙ, в котором внизу надписи ВЫБЕРИТЕ ФУНКЦИЮ: остается только выбрать функцию ABS ‑ см. рис. 2.3.

Рис.2.3

В результате появится окно АРГУМЕНТЫ ФУНКЦИИ ABS – см. рис. 2.4. Сразу после этого надо навести курсор мышки на ячейку с численным значением остатка для наблюдения 1 (ячейка D2), равное 1,4070. Это нам, как и при первом способе работы с функцией, позволит получить тот же результат ABS(1,4070)= 1,4070.

Рис. 2.4

Далее, перемещая курсор мышки вниз по столбцу «Остатки по модулю» (ячейки D2:D110), найдем с помощью функции ABS остатки по модулю для всех остальных 109 наблюдений – см. таблицу 2.6. Прежде чем тянуть курсор мышки вниз, нужно сначала его навести на правый угол ячейки C35 и при этом убедиться, что он приобрел форму черного крестика.

Для тех, кто не силен в математике, скажу, что функция ABS, меняя знак у отрицательных остатков, у положительных остатков его не меняет. Например, отрицательный «Остаток по модулю» для наблюдения 105 становится положительным: ABS(-0,2392)= 0,2392. Если же суммировать все остатки, не беря их по модулю, то сумма их отрицательных и положительных значений будет взаимно погашаться и будет равна нулю для уравнений регрессии со свободным членом, а без свободного члена – близка к нулю. Все это не позволяет найти среднюю ошибку аппроксимации без преобразования остатков по модулю.