Расширяем возможности MS Excel 2010 c помощью R14.04.2014 09:33

Добрый день, уважаемые читатели.Сегодня я хочу показать как можно связать возможности языка R и офисного пакета MS Excel 2010. Ниже я расскажу о том, как можно расширить функционал встроенного языка VBA с помощью функций R, а поможет мне в этом надстройка RExcel. Инструкцию по его установке можно без проблем найти в сети или на офф. сайте.Постановка задачи и предварительные настройки Для того, чтобы мы смогли использовать функции R из VBA необходимо в Excel открыть «редактор Visual Basic» (Alt + F11). После этого к проекту надо добавить модуль RExcelVBAlib, сделать это можно перейдя Tools→References и поставить галочку на нужном пункте.Данный модуль содержит класс rinterface, по средством, которого и происходит взаимодействие составляющих нашей связки.Для демонстрации я взял исходные данные по котировакам доллара с сайта «Финам» за период 16.12.2011 по 20.03.2014. Для примера на их основе средствами R построим график изменения ежемесячной цены открытия ('OPEN').Основы работы с rinterface из RExcel Для начала давайте напишем код на 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, которая понадобиться нам для работы с временными рядами.Затем создаем функцию, которая выполняет следующее:

Преобразует наш набор данных во временной ряд. Индексами которого будут значения столбца 'Date', преобразованные в дату. Значения уровней данного рядя будут равны столобцу 'OPEN'. С помощью следующей строки мы агрегируем наши данные по месяцам с помощью функции aggregate. Данный шаг нужен потому, что исходные данные у нас содержат ежедневные данные, а нам надо перейти к месяцам. выводим график по месячным значениям возвращаем массив с месячными значения, на основе которых строился график. Итак, код на R мы написали. Теперь посмотрим, как вызывать его из VBA.Для этого есть несколько способов, которые будут показаны ниже.Способ 1. Построчное выполнение команд функции. Данный способ является самым простым для понимания и самым длинным по количеству строк кода. Код процедуры для него следующий: 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.Способ 2. Использование внешней функции для выводом графика. Процедура для этого способа выглядит следующим образом: 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 позволяет выполнить код, находящийся в файле .rAsSimpleDF преобразует выбранный в параметре диапазон в тип набора данных (dataframe).Функция RunRCall выполняет вызов процедуры и результат ее не возвращает (return (…) игнорируется). В качестве первого параметра передается имя процедуры, либо ее код на R. Все последующие парамерты передают параметры определенные в процедуре (в нашем случае он один).Из описания выше, становиться ясно, что наша процедура call_r_impotr_func_without_print () сначала выполняет внешний файл agg_price.R. Затем она вызывает функцию agg_price_func для набора данных из заданного диапазона. И в конце выводит график на заданный лист.Плюсом у данного метода является простота отладки кода на R, т.к. он может быть написан в любом редакторе или IDE.Из минусов надо отметить, что на выходе вместо одного файлика мы получим 2: xls и r.Способ 3. Использование внешней функции с выводом данных и графиком. Код процедуры: 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 (…) не игнорируется), а не только выполнять како-то код. Хотя тем, кто знаком с определение процедуры и функции, данное отличие понятно.Кроме того, у GetRApply после параметра с именем функции, также необходимо указать диапазон ячеек, куда будут выводиться значения, получившиеся при работе функции.Плюсы и минусы данного подхода такие же, как и у предыдущего.

Заключение После выполнения данных VBA процедур, наш тестовый лист выглядит так: В статье я постарался показать наиболее полезные варианты исполнения кода R из VBA. Кроме того в сжатой форме были описаны достоинства и недостатки всех этих способ. Также надо сказать, что RExcel может работать и без VBA.Надо отметить, что показаны далеко не все функции интерфейса rinterface, но данный материал даст возможность с минимальными затратами времени освоить и не описанные функции.Файл с процедурами можно взять у меня на GitHub’е.