Добрый день, уважаемые читатели.
Сегодня я хочу показать как можно связать возможности языка R и офисного пакета MS Excel 2010. Ниже я расскажу о том, как можно расширить функционал встроенного языка VBA с помощью функций R, а поможет мне в этом надстройка RExcel . Инструкцию по его установке можно без проблем найти в сети или на офф. сайте.
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 , которая понадобиться нам для работы с временными рядами.
Затем создаем функцию, которая выполняет следующее:
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 :
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 новых функции:
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 после параметра с именем функции, также необходимо указать диапазон ячеек, куда будут выводиться значения, получившиеся при работе функции.
Плюсы и минусы данного подхода такие же, как и у предыдущего.
Теги: Добавить метки
После установки надстройки появится новая функция =ПЕРЕВОД (ТЕКСТ ;ЯЗЫК ). Функция имеет всего 2 аргумента:
После установки надстройки, функцией можно будет пользоваться аналогично встроенной. Вот так выглядит стандартное окно ввода параметров функции ПЕРЕВОД. Далее приводятся различные примеры применения функций перевода.
Как уже было сказано переменную ЯЗЫК можно задавать двумя способами. Проще всего указать код конечного языка. В этом случае Яндекс сам попытается определить язык источника.
Более сложный способ - явно указать исходный язык и язык на который вам необходимо перевести.
Сравнивая результаты перевода с явным указанием источника перевода и без. Мне они показались идентичными. Скорость работы функции тоже не изменилась.
Как правило, мы пользуемся двумя языками: русским и английским. Чтобы упростить жизнь себе и пользователям я сделал еще 4 разновидности функции:
=ПереводRu (ТЕКСТ ) - Переводит текст практически с любого языка на русский язык, используя сервис Яндекс.Перевод (язык источника указывать не нужно)
Пример=ПереводEn (ТЕКСТ ) - Переводит текст с любого языка на английский язык, используя сервис Яндекс.Перевод (язык источника указывать не нужно).
ПримерВам необходимо указать только текст, который вы хотите перевести. Сервис Яндекс.Перевод автоматически попытается определить исходный язык текста.
=ПереводEnRu (ТЕКСТ ) - Переводит текст с английского языка на русский язык, используя сервис Яндекс.Перевод.
ПримерУпрощенная функция для перевода с английского на русский язык. Вам необходимо указать только текст, который вы хотите перевести.
=ПереводRuEn (ТЕКСТ ) - Переводит текст с русского языка на английский язык, используя сервис Яндекс.Перевод.
ПримерУпрощенная функция для перевода с русского на английский язык. Вам необходимо указать только текст, который вы хотите перевести.
| Язык | Код | |
|---|---|---|
| албанский | sq | |
| английский | en | |
| арабский | ar | |
| армянский | hy | |
| азербайджанский | az | |
| белорусский | be | |
| болгарский | bg | |
| боснийский | bs | |
| вьетнамский | vi | |
| венгерский | hu | |
| голландский | nl | |
| греческий | el | |
| грузинский | ka | |
| датский | da | |
| иврит | he | |
| индонезийский | id | |
| итальянский | it | |
| исландский | is | |
| испанский | es | |
| каталанский | ca | |
| китайский | zh | |
| корейский | ko | |
| латышский | lv | |
| литовский | lt | |
| малайский | ms | |
| мальтийский | mt | |
| македонский | mk | |
| немецкий | de | |
| норвежский | no | |
| польский | pl | |
| португальский | pt | |
| румынский | ro | |
| русский | ru | |
| сербский | sr | |
| словацкий | sk | |
| словенский | sl | |
| тайский | th | |
| турецкий | tr | |
| украинский | uk | |
| финский | fi | |
| французский | fr | |
| хорватский | hr | |
| чешский | cs | |
| шведский | sv | |
| эстонский | et | |
| японский | ja |
К сожалению, в сутки Яндекс позволяет перевести бесплатно не более 1 млн символов. В связи с ростом пользователей, этот объем стал достигаться достаточно часто. В 00:00 по Мск ограничение снимется и функция опять заработает. При превышении объема, функция возвращает значение "Текст не может быть переведен".
Каждый пользователь сталкивался с тем, что иногда в Excel не найти подходящих инструментов, которые бы соответствовали потребностям. При всем изобилии возможностей Excel иногда нельзя автоматизировать решения некоторых задач, ведь совершенству нет предела. Идеальное решение – это предоставление возможности пользователю самому создавать свои специфические инструменты. Для этого были созданы макросы.
Код макроса Excel написанный на языке Visual Basic for Application (VBA), а его выполняет инструмент приложения, к которому он присоединен. Большинство этих инструментов не доступно на уровне окна программы Excel. Как написать макрос.
Теперь продемонстрируем на примере информацию о том, как писать, редактировать и выполнять код макроса.
Чтобы написать макрос:
Примечание. Если в главном меню отсутствует закладка «РАЗРАБОТЧИК», тогда ее необходимо активировать в настройках: «ФАЙЛ»-«Параметры»-«Настроить ленту». В правом списке «Основные вкладки:» активируйте галочкой опцию «Разработчик» и нажмите на кнопку ОК.
Макросы позволяют автоматизировать процессы работы с документами и не только… Они могут практически одновременно выполнить тысячи инструментов за одну операцию (даже по одному клику мышкой). Таким образом расширяются возможности работы с программой.
С помощью макросов пользователь может сам создать свой инструмент, которого ему недостает в арсенале программы Excel. Например, когда нужно автоматически выделить каждую вторую строку одним кликом. Или нужно одновременно создать сразу определенное количество копий определенного рабочего листа. Список потребностей пользователей в автоматизации процессов работы можно продолжать до бесконечности.
Если бы не было возможности создавать макросы во всех программах, входящих в пакет MS Office. То множество операций в процессе рутинной работы пользователям приходилось бы выполнять вручную (выделять через одну строку кликая по каждому второму заголовку мышкой или копировать вставлять по одному листу). Ручная работа в лучшем случаи приводить к потере огромного количества времени, а в худшем – ведет к ошибкам или даже потере ценных данных.
Возможность создавать макросы и автоматизировать рабочие процессы бережет вашу работу от лишних потерь времени и возникновения ошибок. Далее рассмотрим, как создать макрос, который безошибочно и автоматически выполнить рутинную работу в один клик мышкой. Так же рассмотрим в каких местах рабочей книги Excel можно создавать и хранить макросы. Как их запустить и выполнить, а также как их максимально оптимизировать под свои потребности.
Excel предоставляет большой и изобильный арсенал инструментов для хранения и обработки огромного количества информации с данными. Хотя для самой универсальной аналитической программы Excel – хранение данных само по себе менее интересно, чем возможность их обрабатывать, структурировать и анализировать с презентацией в отчетах. Для этих целей служит сильнейший аналитических инструмент по обработке данных такой как «Сводные таблицы». Но и его можно еще более усовершенствовать с помощью макросов. И тогда возможности сводных таблиц не знают границ.
Рабочим языком для большинства приложений фирмы Microsoft(в том числе, дляWordи Excel) в последнее время стала версияVisualBasicс названием VBA . В Excel этот язык применяется, начиная с Excel 5. VBA соединяет в себе все положительные черты самого простого языка программированияVisualBasicсо всеми вычислительными возможностями Excel. VBA имеет полный доступ ко всем командам и структурам Excel.
Дальнейшее изложение материала ориентировано на читателя, ознакомленного с программированием на VisualBasic, например в объеме методических пособий и .
Для знакомства с VBA Вам будет предложено записать с помощью Excel некоторую последовательность действий. У Excel есть средство, называемое макрорекордером, которое записывает все производимые Вами действия над рабочим листом и интерпретирует их как процедуру (макрос) VBA. Выполняя эту процедуру, можно повторно воспроизвести записанные действия, что может быть полезно при форматировании книги Excel.
Перед запуском макрорекордера следует подготовить рабочий лист, произведя все действия, которые не нужно включать в процедуру. Это такие действия, как создание нового рабочего листа или перемещение в конкретную часть листа. Когда Вы начнете запись, все, что Вы сделаете в Excel, будет записано в процедуре.
В фундаментальном руководстве Персона Р. по Excel 97 приведен наглядный пример записи процедуры. Здесь Вам предлагается повторить этот пример.
На этом подготовка к записи макроса закончена. Теперь все следующие производимые действия будут записаны и интерпретированы как инструкции VBA. Будут записаны все нажатия клавиш клавиатуры и кнопок мыши до тех пор, пока не будет нажата кнопка Остановить запись .
Выполните команду Сервис , Параметры .
10. Чтобы выключить отображение сетки, раскройте вкладку Вид и сбросьте флажок Сетка . Щелкните на кнопке ОК .
11. Выделите ячейку B5 и введите текст Розничная цена: и нажмите клавишу Enter .
12. Выделите ячейку C5 и выполните команду Формат , Ячейки .
13. Раскройте вкладку Защита и сбросьте флажок Защищаемая ячейка . Эти изменения отменят защиту выделенной ячейки от изменения ее содержимого, т.е. позже, когда Вы защитите от изменений весь рабочий лист, данные в этой ячейке можно будет изменять.
14. Раскройте вкладку Число и выберите формат Денежный с двумя разрядами дробной части и символом денежной единицы р., использующий выделение красным цветом для отрицательных значений. Затем щелкните на кнопке ОК .
15. Выделите ячейку B7, введите текст Цена с учетом скидки: и нажмите клавишу Enter .
16. Выделите ячейку B9, введите текст Размер скидки: и нажмите клавишу Enter .
17. Расширьте столбец B до такой степени, чтобы текст в ячейке B7 полностью отображался внутри столбца B. Для этого поместите указатель мыши на вертикальную черту между заголовками столбцов B и C и перетащите ее.
18. Выделите диапазон ячеек B5:B9 и установите в них выравнивание текста по правому краю. Для этого выберите команду Формат , Ячейки . Раскройте вкладку Выравнивание. В раскрывшемся списке по горизонтали выделите элемент по правому краю и щелкните на кнопке ОК .
19. Выделите ячейку C7 и выберите команду Формат , Ячейки . Раскройте вкладку Число и выберите формат Денежный с двумя разрядами дробной части и символом денежной единицы р ., использующий выделение красным цветом для отрицательных значений. Затем щелкните на кнопке ОК .
20. Введите в этой ячейке формулу =(1-C9)*C5 .
21. Выделите ячейку C9 и выберите команду Формат , Ячейки . Раскройте вкладку Число и выберите формат Процентный с двумя разрядами дробной части. Затем щелкните на кнопке ОК .
22. Задайте размер вкладки, введя значение 0,05 в ячейку C9 и нажав клавишу Enter .
23. Чтобы защитить ячейки рабочего листа от изменений, выберите команду Сервис , Защита , Защитить лист . В появившемся окне Защитить лист щелкните на кнопке ОК . Ваш рабочий лист теперь должен выглядеть так, как показано на рис. 11.2.24. Для остановки записи щелкните на кнопке Остановить запись на плавающей панели инструментов, которая появилась на рабочем листе в начале записи.
25. Проверьте правильность работы созданной Вами таблицы. Введите в ячейку C5 значение 100 и нажмите клавишу Enter . В ячейке C7 должен появиться результат 95,00р., а в ячейке C5 отобразиться значение 100,00р.
26. Теперь можно посмотреть на процедуру, которая получилась в результате записи. Для этого выполните команду Серви с , Макрос , Макросы .
27. Выделите имя макроса в предлагаемом списке и щелкните на кнопке Изменить .
28. Появится окно редактора Visual Basic, в котором откроется окно с кодом процедуры (рис. 11.3). Обратите внимание: Visual Basic здесь не является отдельным приложением. Он входит в состав Excel.
Ниже приведен текст этой процедуры.
|
|
|
Рис. 11.3. Окно редактора Visual Basic с кодом записанной процедуры Расчет_стоимости |
Sub Расчет_стоимости()
" Расчет_стоимости Макрос
Range("B5").Select
Range("C5").Select
Selection.Locked = False
Range("B7").Select
ActiveCell.FormulaR1C1 = "Цена с учетом скидки:"
Range("B9").Select
Range("B5:B9").Select
HorizontalAlignment = xlRight
VerticalAlignment = xlBottom
WrapText = False
Orientation = 0
AddIndent = False
ShrinkToFit = False
MergeCells = False
Range("C7").Select
Selection.NumberFormat = "#,##0.00$;#,##0.00$"
ActiveCell.FormulaR1C1 = "=(1-RC)*R[-2]C"
Range("C9").Select
Selection.NumberFormat = "0.00%"
ActiveCell.FormulaR1C1 = "5%"
ActiveSheet.Protect DrawingObjects:=True, _
Contents:=True, Scenarios:=True
29. Сравните этот код с выполненными Вами действиями при записи процедуры. Вы должны заметить, что каждому выполненному Вами действию соответствует одна или более строк процедуры.
Первое, что Вы сделали, когда начали запись процедуры – отключили отображение сетки активного рабочего листа. Этому действию соответствует строка процедуры:
ActiveWindow.DisplayGridlines = False
Эта инструкция VBA задает значение False свойству DisplayGridlines (отображать сетку) объекта ActiveWindow (активное окно). Активное окно – это то окно, в котором находится фокус ввода. Необязательно помещать в код процедуры имя рабочего листа, с которым Вы работаете, если этот лист активен, т.е. находится в активном окне. Благодаря этому процедура может применяться не только к одному рабочему листу, а к любому активному рабочему листу.
Следующие две строки программного кода Вашей процедуры активизируют ячейку B5 и помещают в нее текст «Розничная цена:»
Range("B5").Select
ActiveCell.FormulaR1C1 = "Розничная цена:"
В VBA отдельная ячейка рабочего листа не является объектом. Диапазон ячеек, например Range (“B2:D3”), является объектом даже тогда, когда он состоит из одной ячейки, напримерRange(“A1”). МетодSelect(выделить) активизирует ячейки, указанные как аргументы объектаRange. Ссылкой на активную ячейку являетсяActiveCell (активная ячейка). СвойствоFormulaR1C1 ячейки предназначено для записи в ячейке формулы.
Следующие четыре строки выделяют ячейку C5, затем применяют к ней числовой формат, снимают защиту ячейки и скрытие формул, действующие после защиты листа:
Range("C5").Select
Selection.NumberFormat = "#,##0.00$;#,##0.00$"
Selection.Locked = False
Selection.FormulaHidden = False
Последнюю из этих инструкций можно удалить, поскольку важно лишь снять защиту ячейки. Значение же ее свойства Hidden(скрытый) нам безразлично.
Следующие четыре инструкции вставляют текст в ячейки B7иB9:
Range("B7").Select
ActiveCell.FormulaR1C1 = "Цена с учетом скидки:"
Range("B9").Select
ActiveCell.FormulaR1C1 = "Размер скидки:"
Columns("B:B").ColumnWidth = 20.71
Можно выделить один или более столбцов с помощью метода Columns (столбцы). Ссылка на столбцы является аргументом этого метода. Затем можно использовать свойствоColumnWidth(ширина столбца) текущего выделения, чтобы установить ширину выделенных столбцов. Аналогичным образом можно выделить одну или несколько строк с помощью методаRows(строки).
Сравните инструкции дальнейшей части кода процедуры с выполненными Вами действиями при записи процедуры. После сделанного пояснения кода процедуры Вам должно быть понятно назначение инструкций этой заключительной части кода процедуры.
Язык программирования Visual Basic, встроенный в Excel, обладает всеми возможностями базового языка высокого уровня и имеет множество расширений, позволяющих получить доступ ко всем возможностям Excel. Поэтому будем называть язык программирования языком Excel Visual Basic (EVB).
Макросы
Макрос – это записанная последовательность команд и действий, сохранённая под уникальным именем, которую может выполнить Excel.
Excel Visual Basic – это язык макросов, т.е. вы можете писать программу на этом языке традиционным образом, а можете записать макрос с помощью команды Сервис – Макрос и использовать текст макроса наравне с остальным текстом вашей программы.
Microsoft Excel имеет встроенное средство для записи макроопределений – макрорекордер. Он записывает последовательность нажатий клавиш и перемещений мыши в виде операторов языка Visual Basic. В дальнейшем макрос можно редактировать, как обычный текстовый файл.
Тексты программ на языке Visual Basic хранятся в специальных листах рабочей книги. Эти листы носят название модулей .
Создание макроса автоматически записанного при заполнении таблицы Excel
1. Начать запись макроса можно через команду меню Сервис – Макрос – Начать запись или путём нажатия на кнопку с панели инструментов Visual Basic, которая вызывается посредством команды меню Вид – Панели инструментов – Visual Basic.
2. Далее появится диалоговое окно Запись макроса, по умолчанию Excel присваивает первому макросу имя Макрос1. Вы можете дать макросу другое имя, которое отражает выполняемые макросом действия. Введите в поле Имя макроса имя и нажмите на кнопку ОК .
3. Произвести необходимые действия, операции (например, форматирование ячеек или построение диаграммы, и т.д.).
4. Остановить запись можно через команду меню Сервис – Макрос – Остановить запись или путём нажатия на кнопку с панели инструментов Visual Basic.
Пример:
" Макрос записан 13.11.2002
ActiveCell.FormulaR1C1 = “=TODAY()”
Выполнение макроса
Чтобы выполнить макрос, выберите команду меню Сервис – Макрос – Макросы.
Откроется диалоговое окно со списком макросов. Выбрав нужный макрос, щёлкните по кнопке Выполнить .
Просмотр, редактирование и изменение макроса
Просмотр макроса осуществляется с помощью редактора Visual Basic. Для того, чтобы войти в редактор, выполнить Сервис – Макрос – Редактор Visual Basic.
Редактор Visual Basic представляет собой отдельное графическое окно, содержащее своё меню и панели инструментов.
Для просмотра содержимого книги в окне редактора необходимо открыть окно проекта. Через команду меню View – Project Explorer (Вид – Окно проекта) открывается окно проектаProject – VBA Project, в котором в папке Modyles открывается нужный модуль.
Внести изменения в макрос можно также через команды меню Сервис – Макрос – Макросы. В появившемся диалоговом окне Макросы, выбрать нужный макрос, далее нажать на кнопку Войти или Изменить .
Кнопки
Для удобства вызова макроса на выполнение макросу можно назначить кнопки:
§ 
Кнопка на панели инструментов
. Вид – Панели инструментов – Настройка.
Среди Команд выбрать Макросы – Настраиваемая кнопка и перенести её с помощью мыши на любую панель инструментов. Для работы с этой кнопкой нажать правую кнопку мыши (Назначить макрос). Редактирование кнопки возможно только в режиме настройки.
§ Командная кнопка . Вид – Панели инструментов – Формы . В панели инструментов Формы нужно выбрать кнопку – Кнопка. С помощью мыши нарисовать кнопку и правой кнопкой мыши Назначить макрос.
§ Графический объект. Вставка – Рисунок – Картинки . Правой кнопкой мыши Назначить макрос.
Принципы объектно-ориентированного программирования
В язык Visual Basic добавлены элементы и средства объектно-ориентированного программирования (ООП). ООП – это событийный язык программирования, ориентированный на работу с объектами.
Объекты
Объект – это то, чем вы управляете с помощью программы на языке Excel Visual Basic:
§ Cell (ячейка),
§ Range (диапазон),
§ Chart (диаграмма),
§ WorkSheet (рабочий лист),
§ WorkBook (рабочая книга).
Понятие объекта включает в себя параметры объекта (свойства) и программные средства для работы с ним (методы). В программе на языке Visual Basic необходимо идентифицировать объект, прежде чем применять к нему методы или изменять значения его свойства.
Методы
Метод – это программа, которая выполняет определённые действия над объектом.
Свойства
Свойства – это совокупность характеристик и атрибутов, описывающих объект.
| Синтаксис: | Объект. Свойство = выражение |
| Пример: | ActiveWindow. Heigt = HSize |
