Какой язык программирования в эксель. Как написать макрос в Excel на языке программирования VBA
Знаете ли вы, что в Excel реализован инструмент перевода текста на другой язык. С помощью мини-переводчика вы можете перевести фразу, абзац или весь файл, написанный на другом языке. Данный элемент использует функционал программы Microsoft Translator, который по умолчанию содержится в офисных приложениях Microsoft. В сегодняшней статье я опишу, как воспользоваться данным функционалом. Помимо этого, мы рассмотрим, как с помощью функций ВЕБСЛУЖБА и ФИЛЬТР.XML реализовать Яндекс переводчик в нашей Excel книге.
Перевод с помощью Microsoft Translator
Итак, у вас имеется текст, который необходимо перевести на другой язык. В нашем случае, я хочу перевести текст, находящийся в ячейках A1:A3 с русского на английский. Переходим во вкладку Рецензирование в группу Язык , щелкаем по кнопке Перевод. В правой части книги появится панель Справочные материалы, в которой необходимо указать переводимый текст и языки исходный и на который перевести.
Чтобы задать текст для перевода, есть два пути. Первый, банальный, ручками набиваем текст в поле Искать , указываем язык перевода, жмем кнопку со стрелкой, слева от поля, ждем пару секунд пока Microsoft Translator подберет нужные слова и наслаждаемся результатом перевода в этой же панельке.
Второй вариант более технологичный и предполагает, что текст, который требуется перевести, уже имеется на листе. Для этого, удерживая клавишу Alt, щелкаем по ячейке с переводимым текстом, в поле Искать панели Справочные материалы вы увидите фразу с выбранной ячейки, а ниже ее перевод. Если вы нажмете кнопку Вставить , находящуюся под переводом, Excel заменит исходный текст в ячейке на переведенный.
Данный инструмент прост в обращении и особенно выручает, если в Excel книге попадаются незнакомые слова.
Реализация Яндекс переводчика в Excel
Способ, описанный выше, не требует каких-либо особых навыков владения Excel, достаточно один раз воспользоваться им, и все станет понятно. Способ, описанный ниже, посложнее и тем интереснее, потребует от пользователей некоторой сноровки.
Итак, как я уже писал в статье об , в Excel 2013 реализован инструмент импорта данных в формате XML, с помощью функции . Первая функции использует API веб службы для выгрузки данных из интернета, вторая – возвращает конкретную информацию из выгруженного XML. Для реализации задуманной идеи в Excel мы воспользуемся переводчиком Яндекс, который имеет свой API. Переходим на страницу API Перевода Яндекс , где можно прочитать всю документацию и правила оформления результатов перевода, находим пункт Получите бесплатный API -ключ и щелкаем по нему (ВНИМАНИЕ! Чтобы получить ключ, у вас должен иметься профиль Яндекс).
Далее необходимо заполнить форму получения API-ключа, где надо указать от какой службы вам требуется ключ, прочитать пользовательское соглашение, поставить галочку о прочтении и нажать кнопку Получить API -ключ. Выглядит он следующим образом.
Данный ключ будет использоваться, как один из обязательных аргументов в передаваемом URL запросе к Яндекс службам. Следующий обязательный аргумент – это текст, который требуется перевести и третий аргумент – направление перевода, может задаваться как пара кодов языков (например, ru-en), либо в виде кода конечного языка (например, ru). Подробнее о синтаксисе URL запроса можете почитать на сайте Яндекс .
Таким образом, два обязательных аргумента у вас будут практически неизменными – ключ и язык перевода, а текст перевода будет переменным аргументом.
Важный момент, на который следует обратить внимание – прежде чем передавать запрос в Яндекс, необходимо изменить кодировку текста запроса, на понятный для веб службы язык. Для этого необходимо воспользоваться функцией КОДИР.URL, которая возвращает строку, зашифрованную в виде URL-адреса. К примеру, текст Самара будет выглядеть как %D0%A1%D0%B0%D0%BC%D0%B0%D1%80%D0%B0.
Добавив немного лоска нашем файлу, наш переводчик принял вполне удобочитаемый вид.
Чтобы написать компьютерную программу Вам необходимо знать, во-первых, какой-либо язык программирования. Во вторых у Вас на компьютере должна быть установлена специальная программа, которая скомпилирует написанный Вами код. В-третьих, Вам необходимо будет разработать и записать на бумаге алгоритм Вашей программы, чтобы перед глазами всегда была чёткая цель того, к чему мы идём (по крайней мере, я так всегда делаю, когда пишу более сложные программы).
Язык программирования подойдет любой, но при работе в Excel и написания программ под него я рекомендую использовать Visual Basic for Application (VBA), так как в Excel уже встроена специальный редактор для создания программ в Excel.
Язык программирования мы с Вами, думаю, легко освоим, мне удалось, и у Вас также получится. Поверьте, в Excel программировать намного проще, чем на каком-нибудь C++ или java. Потому что он создан для всех, неважно кто Вы инженер или бухгалтер, VBA создавался для людей, которые пользуются Excel. И если Вы освоили Excel, то следующим логическим шагом будет освоение VBA.
Покажу на примере, как создаётся программа на VBA.
Никакого алгоритма не придумываем, вместо него запишем такую фразу «"Нащ код».
1. Запускаем Excel.
2. Выполняем блиц-команду (зажимаем две клавиши) «Alt» и «F11».
3. Создаём шаблон и сохраняем его.
4. После чего записываем три строчки, при этом строчка, которая начинается с апострофа, является комментарием. Слово «Sub» указывает на начало программы, «program()» - название программы, которое можно изменить, например на «Макрос()».
Словосочетание «End Sub», говорит о том, что здесь программа заканчивается.Вместо строчки «"Наш код» можно записать в будущем наш алгоритм.
Программирование в Excel не такое сложно, как может показаться, необходимо иметь чёткий, записанный желательно на бумаге алгоритм, разрабатывать которые мы вскоре научимся, и некоторые основы VBA (ввод и вывод данных в программу, условия и циклы), для начала этого вполне будет достаточно.
Смотрите также в этом разделе.
Добрый день, уважаемые читатели.
Сегодня я хочу показать как можно связать возможности языка 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 . Данный шаг нужен потому, что исходные данные у нас содержат ежедневные данные, а нам надо перейти к месяцам.
- выводим график по месячным значениям
- возвращаем массив с месячными значения, на основе которых строился график.
Для этого есть несколько способов, которые будут показаны ниже.
Способ 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
- InsertCurrentRPlot
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
AsSimpleDF преобразует выбранный в параметре диапазон в тип набора данных (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"е .
Теги: Добавить метки
После установки надстройки появится новая функция =ПЕРЕВОД (ТЕКСТ ;ЯЗЫК ). Функция имеет всего 2 аргумента:
- ТЕКСТ - Собственно текст, который необходимо перевести. Максимальная длина текста составляет 10 000 символов
- ЯЗЫК - Направление для перевода. Может задаваться одним из следующих способов:
- В виде пары кодов языков («с какого»-«на какой»), разделенных дефисом. Например, "en-ru" обозначает перевод с английского на русский.
- В виде кода конечного языка (например "ru" ). В этом случае сервис Яндекс.Перевод пытается определить исходный язык автоматически.
После установки надстройки, функцией можно будет пользоваться аналогично встроенной. Вот так выглядит стандартное окно ввода параметров функции ПЕРЕВОД. Далее приводятся различные примеры применения функций перевода.
Пример 1
Как уже было сказано переменную ЯЗЫК можно задавать двумя способами. Проще всего указать код конечного языка. В этом случае Яндекс сам попытается определить язык источника.
Пример 2
Более сложный способ - явно указать исходный язык и язык на который вам необходимо перевести.
Сравнивая результаты перевода с явным указанием источника перевода и без. Мне они показались идентичными. Скорость работы функции тоже не изменилась.
Упрощенные функции перевода
Как правило, мы пользуемся двумя языками: русским и английским. Чтобы упростить жизнь себе и пользователям я сделал еще 4 разновидности функции:
=ПереводRu (ТЕКСТ ) - Переводит текст практически с любого языка на русский язык, используя сервис Яндекс.Перевод (язык источника указывать не нужно)
ПримерПример 3
=ПереводEn (ТЕКСТ ) - Переводит текст с любого языка на английский язык, используя сервис Яндекс.Перевод (язык источника указывать не нужно).
ПримерПример 4
Вам необходимо указать только текст, который вы хотите перевести. Сервис Яндекс.Перевод автоматически попытается определить исходный язык текста.
=ПереводEnRu (ТЕКСТ ) - Переводит текст с английского языка на русский язык, используя сервис Яндекс.Перевод.
ПримерПример 5
Упрощенная функция для перевода с английского на русский язык. Вам необходимо указать только текст, который вы хотите перевести.
=ПереводRuEn (ТЕКСТ ) - Переводит текст с русского языка на английский язык, используя сервис Яндекс.Перевод.
ПримерПример 6
Упрощенная функция для перевода с русского на английский язык. Вам необходимо указать только текст, который вы хотите перевести.
| Язык | Код | |
|---|---|---|
| албанский | 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 по Мск ограничение снимется и функция опять заработает. При превышении объема, функция возвращает значение "Текст не может быть переведен".
