- PVSM.RU - https://www.pvsm.ru -
Добрый день, уважаемые читатели.
Сегодня я хочу показать как можно связать возможности языка R и офисного пакета MS Excel 2010. Ниже я расскажу о том, как можно расширить функционал встроенного языка VBA с помощью функций R, а поможет мне в этом надстройка RExcel [1]. Инструкцию по его установке можно без проблем найти в сети или на офф. сайте.
Для того, чтобы мы смогли использовать функции R из VBA необходимо в Excel открыть «редактор Visual Basic» (Alt + F11). После этого к проекту надо добавить модуль RExcelVBAlib, сделать это можно перейдя Tools->References и поставить галочку на нужном пункте.
Данный модуль содержит класс rinterface, по средством, которого и происходит взаимодействие составляющих нашей связки.
Для демонстрации я взял исходные данные по котировакам доллара с сайта «Финам» за период 16.12.2011 по 20.03.2014. Для примера на их основе средствами R построим график изменения ежемесячной цены открытия ('OPEN').
Для начала давайте напишем код на R, который будет выполнять поставленную задачу и сохраним его в файл, например agg_price.R
(он нам понадобиться в дальнейшем).
library(zoo)
agg_price_func <- function(x) {
y <- zoo(x$OPEN, as.Date(as.character(x$DATE),"%Y%m%d"))
new_y <- aggregate(y, as.yearmon, mean)
plot(new_y)
return (new_y)
}
Разберемся что делает данный код.
Сначала загружаем библиотеку zoo
, которая понадобиться нам для работы с временными рядами.
Затем создаем функцию, которая выполняет следующее:
Итак, код на R мы написали. Теперь посмотрим, как вызывать его из VBA.
Для этого есть несколько способов, которые будут показаны ниже.
Данный способ является самым простым для понимания и самым длинным по количеству строк кода. Код процедуры для него следующий:
Sub call_r_func()
RInterface.PutDataframe "open_price", Range("USD!A1:C535")
RInterface.RRun "library(zoo)"
RInterface.RRun "price <- zoo(open_price$OPEN, as.Date(as.character(open_price$DATE),""%Y%m%d""))"
RInterface.RRun "agg_price <- aggregate(price, as.yearmon, mean)"
RInterface.RRun "plot(agg_price)"
RInterface.InsertCurrentRPlot Range("OPEN_PRICE!A1"), widthrescale:=0.5, heightrescale:=0.5, closergraph:=True
End Sub
Как можно заметить в данной процедуре и используются 3 функции из rinterface:
PutDataframe
RRun
InsertCurrentRPlot
Функция PutDataframe
служит для загрузки данных в переменную языка R типа dataframe. В качестве первого параметра ей передается имя будущей переменной, которую будет использовать интерфейс. Вторым параметром будет диапазон значений, которые будет содержать переменная.
RRun
служит для выполнения команда интерпретатора R. В качестве параметра ей передается строка, которая будет выполнена им.
Последняя в списке InsertCurrentRPlot
выполняет вывод R графики на заданный лист MS Excel. В качестве первного парамерта ей передается ячека в которую будет выводиться график. Параметры widthrescale и heightrescale используются для масштабирования выводимого графика (в нашем случаем 50%). closergraph сигнализирует о том, что график, выведенный с помощью функции plot()
нужно закрыть.
После описанных выше функций работа процедуры call_r_func() не должна вызывать вопросов. Вкратце данная процедура просто выполнила описанный выше R скрипт построчно.
Плюсом данного способа является, то что весь код сосредоточен в VBA макросе, что может быть удобно для небольших задач.
Недостатком может является неудобная отладка кода на R.
Процедура для этого способа выглядит следующим образом:
Sub call_r_impotr_func_without_print()
RInterface.RunRFile "D:/agg_price.R"
RInterface.RunRCall "agg_price_func", AsSimpleDF(Range("USD!A1:C535"))
RInterface.InsertCurrentRPlot Range("OPEN_PRICE!H1"), widthrescale:=0.5, heightrescale:=0.5, closergraph:=True
End Sub
В данном коде появились 3 новых функции:
RunRFile
AsSimpleDF
RunRCall
Первая в данном списке RunRFile
позволяет выполнить код, находящийся в файле .r
AsSimpleDF
преобразует выбранный в параметре диапазон в тип набора данных (dataframe).
Функция RunRCall
выполняет вызов процедуры и результат ее не возвращает (return (...) игнорируется). В качестве первого параметра передается имя процедуры, либо ее код на R. Все последующие парамерты передают параметры определенные в процедуре (в нашем случае он один).
Из описания выше, становиться ясно, что наша процедура call_r_impotr_func_without_print() сначала выполняет внешний файл agg_price.R. Затем она вызывает функцию agg_price_func для набора данных из заданного диапазона. И в конце выводит график на заданный лист.
Плюсом у данного метода является простота отладки кода на R, т.к. он может быть написан в любом редакторе или IDE.
Из минусов надо отметить, что на выходе вместо одного файлика мы получим 2: xls и r.
Код процедуры:
Sub call_r_impotr_func_with_print()
RInterface.RunRFile "D:/agg_price.R"
RInterface.GetRApply "agg_price_func", Range("OPEN_PRICE!A19"), AsSimpleDF(Range("USD!A1:C535"))
RInterface.InsertCurrentRPlot Range("OPEN_PRICE!D19"), closergraph:=True
End Sub
У данной процедуры есть лишь одно небольшое отличие от предыдущей, а именно вместо
RunRCall
вызывается GetRApply
.
Отличие данной функции в том, что она может возвращать результат (return (...) не игнорируется), а не только выполнять како-то код. Хотя тем, кто знаком с определение [4] процедуры и функции, данное отличие понятно.
Кроме того, у GetRApply
после параметра с именем функции, также необходимо указать диапазон ячеек, куда будут выводиться значения, получившиеся при работе функции.
Плюсы и минусы данного подхода такие же, как и у предыдущего.
После выполнения данных VBA процедур, наш тестовый лист выглядит так:
В статье я постарался показать наиболее полезные варианты исполнения кода R из VBA. Кроме того в сжатой форме были описаны достоинства и недостатки всех этих способ. Также надо сказать, что RExcel может работать и без VBA.
Надо отметить, что показаны далеко не все функции интерфейса rinterface, но данный материал даст возможность с минимальными затратами времени освоить и не описанные функции.
Файл с процедурами можно взять у меня на GitHub'е [5].
Автор: kuznetsovin
Источник [6]
Сайт-источник PVSM.RU: https://www.pvsm.ru
Путь до страницы источника: https://www.pvsm.ru/programmirovanie/58855
Ссылки в тексте:
[1] RExcel: http://rcom.univie.ac.at/
[2] временной ряд: http://ru.wikipedia.org/wiki/%C2%F0%E5%EC%E5%ED%ED%EE%E9_%F0%FF%E4
[3] aggregate: http://www.statmethods.net/management/aggregate.html
[4] определение: http://ru.wikipedia.org/wiki/%D0%9F%D0%BE%D0%B4%D0%BF%D1%80%D0%BE%D0%B3%D1%80%D0%B0%D0%BC%D0%BC%D0%B0#.D0.92.D0.B8.D0.B4.D1.8B_.D0.BF.D0.BE.D0.B4.D0.BF.D1.80.D0.BE.D0.B3.D1.80.D0.B0.D0.BC.D0.BC
[5] GitHub'е: https://github.com/kuznetsovin/DataScience/raw/master/ArticleScripts/RExcelExample.xlsm
[6] Источник: http://habrahabr.ru/post/219229/
Нажмите здесь для печати.