Как изменить язык в excel на английский. Программирование в Excel. Понятие программы, программирования, программного продукта. Системы и языки программирования

После установки надстройки появится новая функция =ПЕРЕВОД (ТЕКСТ ;ЯЗЫК ). Функция имеет всего 2 аргумента:

• ТЕКСТ - Собственно текст, который необходимо перевести. Максимальная длина текста составляет 10 000 символов
• ЯЗЫК - Направление для перевода. Может задаваться одним из следующих способов:

1. В виде пары кодов языков («с какого»-«на какой»), разделенных дефисом. Например, "en-ru" обозначает перевод с английского на русский.
2. В виде кода конечного языка (например "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 по Мск ограничение снимется и функция опять заработает. При превышении объема, функция возвращает значение "Текст не может быть переведен".

Немногие знают, что первая версия популярного продукта Microsoft Excel появилась в 1985 году. С тех пор он пережил несколько модификаций и востребован у миллионов пользователей по всему миру. При этом многие работают лишь с малой толикой возможностей этого табличного процессора и даже не догадываются, как им могло бы облегчить жизнь умение программирования в Excel.

Что такое VBA

Программирование в Excel осуществляется посредством языка программирования Visual Basic for Application, который изначально встроен в самый известный табличный процессор от Microsoft.

К его достоинствам специалисты относят сравнительную легкость освоения. Как показывает практика, азами VBA могут овладеть даже пользователи, которые не имеют навыков профессионального программирования. К особенностям VBA относится выполнение скрипта в среде офисных приложений.

Недостатком программы являются проблемы, связанные с совместимостью различных версий. Они обусловлены тем, что код программы VBA обращается к функциональным возможностям, которые присутствуют в новой версии продукта, но отсутствуют в старой. Также к минусам относят и чрезмерно высокую открытость кода для изменения посторонним лицом. Тем не менее Microsoft Office, а также IBM Lotus Symphony позволяют пользователю применять шифрование начального кода и установку пароля для его просмотра.

Объекты, коллекции, свойства и методы

Именно с этими понятиями нужно разобраться тем, кто собирается работать в среде VBA. Прежде всего необходимо понять, что такое объект. В Excel в этом качестве выступают лист, книга, ячейка и диапазон. Данные объекты обладают специальной иерархией, т.е. подчиняются друг другу.

Главным из них является Application, соответствующий самой программе Excel. Затем следуют Workbooks, Worksheets, а также Range. Например, для обращения к ячейке A1 на конкретном листе следует указать путь с учетом иерархии.

Что касается понятия "коллекция", то это группа объектов того же класса, которая в записи имеет вид ChartObjects. Ее отдельные элементы также являются объектами.

Следующее понятие — свойства. Они являются необходимой характеристикой любого объекта. Например, для Range — это Value или Formula.

Методы — это команды, показывающие, что требуется сделать. При написании кода в VBA их необходимо отделять от объекта точкой. Например, как будет показано в дальнейшем, очень часто при программировании в "Эксель" используют команду Cells(1,1).Select. Она означает, что необходимо выбрать ячейку с координатами

Вместе с ней нередко используется Selection.ClearContents. Ее выполнение означает очистку содержимого выбранной ячейки.

Как начать

Затем необходимо перейти в приложение VB, для чего достаточно воспользоваться комбинацией клавиш «Alt» и «F11». Далее:

• в строке меню, расположенном в верхней части окна, нажимают на иконку рядом с иконкой Excel;
• выбирают команду Mudule;
• сохраняют, нажав на иконку с изображением ;
• пишут, скажем так, набросок кода.

Он выглядит следующим образом:

Sub program ()

"Наш код

Обратите внимание, что строка «"Наш код» будет выделена другим цветом (зеленым). Причина в апострофе, поставленном в начале строки, который обозначает, что далее следует комментарий.

Теперь вы можете написать любой код и создать для себя новый инструмент в VBA Excel (примеры программ см. далее). Конечно, тем, кто знаком с азами Visual Basic, будет намного проще. Однако даже те, кто их не имеет, при желании смогут освоиться достаточно быстро.

Макросы в Excel

За таким названием скрываются программы, написанные на языке Visual Basic for Application. Таким образом, программирование в Excel — это создание макросов с нужным кодом. Благодаря этой возможности табличный процессор Microsoft саморазвивается, подстраиваясь под требования конкретного пользователя. Разобравшись с тем, как создавать модули для написания макросов, можно приступать к рассмотрению конкретных примеров программ VBA Excel. Лучше всего начать с самых элементарных кодов.

Пример 1

Задача: написать программу, которая будет копировать значение содержимое одной ячейки и затем записывать в другую.

Для этого:

• открывают вкладку «Вид»;
• переходят на пиктограмму «Макросы»;
• жмут на «Запись макроса»;
• заполняют открывшуюся форму.

Для простоты в поле «Имя макроса» оставляют «Макрос1», а в поле «Сочетание клавиш» вставляют, например, hh (это значит, что запустить программку можно будет блиц-командой «Ctrl+h»). Нажимают Enter.

Теперь, когда уже запущена запись макроса, производят копирование содержимого какой-либо ячейки в другую. Возвращаются на исходную пиктограмму. Нажимают на «Запись макроса». Это действие означает завершение программки.

• вновь переходят на строку «Макросы»;
• в списке выбирают «Макрос 1»;
• нажимают «Выполнить» (то же действие запускается начатием сочетания клавиш «Ctrl+hh»).

В результате происходит действие, которое было осуществлено в процессе записи макроса.

Имеет смысл увидеть, как выглядит код. Для этого вновь переходят на строку «Макросы» и нажимают «Изменить» или «Войти». В результате оказываются в среде VBA. Собственно, сам код макроса находится между строками Sub Макрос1() и End Sub.

Если копирование было выполнено, например, из ячейки А1 в ячейку C1, то одна из строк кода будет выглядеть, как Range(“C1”).Select. В переводе это выглядит, как «Диапазон(“C1”).Выделить», иными словами осуществляет переход в VBA Excel, в ячейку С1.

Активную часть кода завершает команда ActiveSheet.Paste. Она означает запись содержания выделенной ячейки (в данном случае А1) в выделенную ячейку С1.

Пример 2

Циклы VBA помогают создавать различные макросы в Excel.

Циклы VBA помогают создавать различные макросы. Предположим, что имеется функция y=x + x 2 + 3x 3 - cos(x). Требуется создать макрос для получения ее графика. Сделать это можно только, используя циклы VBA.

За начальное и конечное значение аргумента функции берут x1=0 и x2=10. Кроме того, необходимо ввести константу — значение для шага изменения аргумента и начальное значение для счетчика.

Все примеры макросов VBA Excel создаются по той же процедуре, которая представлена выше. В данном конкретном случае код выглядит, как:

Sub programm()

shag = 0.1

Do While x1 < x2 (цикл будет выполняться пока верно выражение x1 < x2)

y=x1 + x1^2 + 3*x1^3 - Cos(x1)

Cells(i, 1).Value = x1 (значение x1 записывается в ячейку с координатами (i,1))

Cells(i, 2).Value = y (значение y записывается в ячейку с координатами (i,2))

i = i + 1 (действует счетчик);

x1 = x1 + shag (аргумент изменяется на величину шага);

End Sub.

В результате запуска данного макроса в "Эксель" получаем два столбца, в первом из которых записаны значения для x, а во втором — для y.

Затем по ним строится график способом, стандартным для "Эксель".

Пример 3

Для реализации циклов в VBA Excel 2010, как и в других версиях, наряду с уже приведенной конструкцией Do While используется For.

Рассмотрим программу, которая создаст столбец. В каждой его ячейке будут записаны квадраты номера соответствующей строки. Использование конструкции For позволит записать ее очень коротко, без использования счетчика.

Сначала нужно создать макрос, как описано выше. Далее записываем сам код. Считаем, что нас интересуют значения для 10 ячеек. Код выглядит следующим образом.

For i = 1 to 10 Next

Команда переводится на «человеческий» язык, как «Повторять от 1 до 10 с шагом один».

Если ставится задача получить столбец с квадратами, например, всех нечетных чисел из диапазона от 1 до 11, то пишем:

For i = 1 to 10 step 1 Next.

Здесь step — шаг. В данном случае он равен двум. По умолчанию отсутствие этого слова в цикле означает, что шаг единичный.

Полученные результаты нужно сохранять в ячейки с номером (i,1). Тогда при каждом запуске цикла с увеличением i на величину шага автоматически будет расти и номер у строки. Таким образом, произойдет оптимизация кода.

В целом код будет выглядеть, как:

Sub program()

For i = 1 To 10 Step 1 (можно записать просто For i = 1 To 10)

Cells(i, 1).Value = i ^ 2 (т.е. в ячейку (i,1) записывается значение квадрата i)

Next (в некотором смысле играет роль счетчика и означает еще один запуск цикла)

End Sub.

Если все сделано правильно, в том числе запись и запуск макроса (см. инструкцию выше), то при его вызове каждый раз будет получаться столбец заданного размера (в данном случае состоящий из 10 ячеек).

Пример 4

В повседневной жизни сплошь и рядом возникает необходимость принять то или иное решение в зависимости от какого-то условия. Не обойтись без них и в VBA Excel. Примеры программ, где дальнейший ход выполнения алгоритма выбирается, а не предопределен изначально, чаще всего используют конструкцию If …Then (для сложных случаев) If …Then …END If.

Рассмотрим конкретный случай. Предположим, необходимо создать макрос для "Эксель", чтобы в ячейку с координатами (1,1) было записано:

1, если аргумент положительный;

0, если аргумент нулевой;

-1, если аргумент отрицательный.

Создание такого макроса для "Эксель" начинается стандартным способом, через использование «горячих» клавиш Alt и F11. Далее записывается следующий код:

Sub program()

x= Cells(1, 1).Value (эта команда присваивает x значение содержимого ячейки с координатами (1, 1))

If x>0 Then Cells(1, 1).Value = 1

If x=0 Then Cells(1, 1).Value = 0

If x<0 Then Cells(1, 1).Value = -1

End Sub.

Остается запустить макрос и получить в "Эксель" нужное значение для аргумента.

Функции VBA

Как вы уже могли заметить, программировать в самом известном табличном процессоре Microsoft не так уж сложно. Особенно, если научиться применять функции VBA. Всего в этом языке программирования, созданном специально для написания приложений в "Эксель" и Word, около 160 функций. Их можно разделить на несколько больших групп. Это:

• Математические функции. Применив их к аргументу, получают значение косинуса, натурального логарифма, целой части и пр.
• Финансовые функции. Благодаря их наличию и используя программирование в Excel, можно получать эффективные инструменты для ведения бухгалтерского учета и осуществления финансовых расчетов.
• Функции обработки массивов. К ним относятся Array, IsArray; LBound; UBound.
• Функции VBA Excel для строки. Это достаточно многочисленная группа. В нее входят, например, функции Space для создания строки с числом пробелов, равных целочисленному аргументу, или Asc для перевода символов в код ANSI. Все они имеют широкое применение и позволяют работать со строками в "Эксель", создавая приложения, значительно облегчающие работу с этими таблицами.
• Функции преобразования типа данных. Например, CVar возвращает значение аргумента Expression, преобразовав его в тип данных Variant.
• Функции работы с датами. Они значительно расширяют стандартные Так, функция WeekdayName возвращает название (полное или частичное) дня недели по его номеру. Еще более полезной является Timer. Он выдает число секунд, которые прошли с полуночи до конкретного момента дня.
• Функции для преобразования числового аргумента в разные системы счисления. Например, Oct выдает в восьмеричное представление числа.
• Функции форматирования. Важнейшей из них является Format. Она возвращает значение типа Variant с выражением, отформатированным согласно инструкциям, которые заданы в описании формата.
• и пр.

Изучение свойств этих функций и их применение позволит значительно расширить сферу применения "Эксель".

Пример 5

Попробуем перейти к решению более сложных задач. Например:

Дан бумажный документ отчета фактического уровня издержек предприятия. Требуется:

• разработать его шаблонную часть посредством табличного процессора "Эксель";
• составить программу VBA, которая будет запрашивать исходные данные для ее заполнения, осуществлять необходимые расчеты и заполнять ими соответствующие ячейки шаблона.

Рассмотрим один из вариантов решения.

Создание шаблона

Все действия осуществляются на стандартном листе в Excel. Резервируются свободные ячейки для внесения данных по названию компании-потребителя, сумме издержек, их уровня, товарооборота. Так как количество компаний (обществ), относительно которых составляется отчет, не зафиксировано, ячейки для внесения значений по итогам и ФИО специалиста заранее не резервируют. Рабочему листу присваивается новое название. Например, "Օтчет".

Переменные

Для написания программы автоматического заполнения шаблона, необходимо выбрать обозначения. Они будут использоваться для переменных:

• NN- номер текущей строки таблицы;
• TP и TF - планируемый и фактический товарооборот;
• SF и SP - фактическая и планируемая сумма издержек;
• IP и IF - планируемый и фактически уровень издержек.

Обозначим теми же буквами, но с «приставкой» Itog накопление итога по данному столбцу. Например, ItogTP - касается столбца таблицы, озаглавленного, как «планируемый товарооборот».

Решение задачи с использованием программирования на VBA

Используя введенные обозначения, получаем формулы для отклонений. Если требуется осуществить расчет в % имеем (F - P) / P * 100, а в сумме — (F - P).

Результаты этих вычислений можно лучше всего сразу внести в соответствующие ячейки таблицы "Эксель".

Для итогов по факту и прогнозу получают по формулам ItogP=ItogP + P и ItogF=ItogF+ F.

Для отклонений используют = (ItogF - ItogP) / ItogP * 100, если расчет ведется в процентах, а в случае суммарной величины — (ItogF - ItogP).

Результаты опять же сразу записываются в соответствующие ячейки, поэтому нет необходимости их присваивания переменным.

Перед запуском созданной программы, требуется сохранить рабочую книгу, например, под названием "Отчет1.xls".

Клавишу «Создать отчетную таблицу» требуется нажать всего 1 раз после ввода заголовочной информации. Следует знать и другие правила. В частности, кнопка «Добавить строку» должна нажиматься каждый раз после ввода в таблицу значений по каждому виду деятельности. После занесения всех данных требуется нажать кнопку «Закончить» и затем переключиться в окно "Эксель".

Теперь вы знаете, как решать задачи для Excel с помощью макросов. Умение применять vba excel (примеры программ см. выше) может понадобиться и для работы в среде самого популярного на данный момент текстового редактора "Ворд". В частности, можно путем записи, как показано в самом начале статьи, или через написание кода создавать кнопки меню, благодаря которым многие операции над текстом можно будет осуществлять нажатием дежурных клавиш или через вкладку "Вид" и пиктограмму "Макросы".

Добрый день, уважаемые читатели.
Сегодня я хочу показать как можно связать возможности языка 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 :

1. PutDataframe
2. 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 новых функции:

1. RunRFile
2. AsSimpleDF
3. 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.

Способ 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"е .

Теги: Добавить метки

Рабочим языком для большинства приложений фирмы Microsoft(в том числе, дляWordи Excel) в последнее время стала версияVisualBasicс названием VBA . В Excel этот язык применяется, начиная с Excel 5. VBA соединяет в себе все положительные черты самого простого языка программированияVisualBasicсо всеми вычислительными возможностями Excel. VBA имеет полный доступ ко всем командам и структурам Excel.

Дальнейшее изложение материала ориентировано на читателя, ознакомленного с программированием на VisualBasic, например в объеме методических пособий и .

Для знакомства с VBA Вам будет предложено записать с помощью Excel некоторую последовательность действий. У Excel есть средство, называемое макрорекордером, которое записывает все производимые Вами действия над рабочим листом и интерпретирует их как процедуру (макрос) VBA. Выполняя эту процедуру, можно повторно воспроизвести записанные действия, что может быть полезно при форматировании книги Excel.

11.1. Подготовка к записи и запись процедуры

Перед запуском макрорекордера следует подготовить рабочий лист, произведя все действия, которые не нужно включать в процедуру. Это такие действия, как создание нового рабочего листа или перемещение в конкретную часть листа. Когда Вы начнете запись, все, что Вы сделаете в 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(строки).

Сравните инструкции дальнейшей части кода процедуры с выполненными Вами действиями при записи процедуры. После сделанного пояснения кода процедуры Вам должно быть понятно назначение инструкций этой заключительной части кода процедуры.

Каждый пользователь сталкивался с тем, что иногда в Excel не найти подходящих инструментов, которые бы соответствовали потребностям. При всем изобилии возможностей Excel иногда нельзя автоматизировать решения некоторых задач, ведь совершенству нет предела. Идеальное решение – это предоставление возможности пользователю самому создавать свои специфические инструменты. Для этого были созданы макросы.

Написание макросов в Excel

Код макроса Excel написанный на языке Visual Basic for Application (VBA), а его выполняет инструмент приложения, к которому он присоединен. Большинство этих инструментов не доступно на уровне окна программы Excel. Как написать макрос.

Теперь продемонстрируем на примере информацию о том, как писать, редактировать и выполнять код макроса.

Чтобы написать макрос:

Примечание. Если в главном меню отсутствует закладка «РАЗРАБОТЧИК», тогда ее необходимо активировать в настройках: «ФАЙЛ»-«Параметры»-«Настроить ленту». В правом списке «Основные вкладки:» активируйте галочкой опцию «Разработчик» и нажмите на кнопку ОК.



Возможности макросов в Excel

Макросы позволяют автоматизировать процессы работы с документами и не только… Они могут практически одновременно выполнить тысячи инструментов за одну операцию (даже по одному клику мышкой). Таким образом расширяются возможности работы с программой.

С помощью макросов пользователь может сам создать свой инструмент, которого ему недостает в арсенале программы Excel. Например, когда нужно автоматически выделить каждую вторую строку одним кликом. Или нужно одновременно создать сразу определенное количество копий определенного рабочего листа. Список потребностей пользователей в автоматизации процессов работы можно продолжать до бесконечности.

Если бы не было возможности создавать макросы во всех программах, входящих в пакет MS Office. То множество операций в процессе рутинной работы пользователям приходилось бы выполнять вручную (выделять через одну строку кликая по каждому второму заголовку мышкой или копировать вставлять по одному листу). Ручная работа в лучшем случаи приводить к потере огромного количества времени, а в худшем – ведет к ошибкам или даже потере ценных данных.

Возможность создавать макросы и автоматизировать рабочие процессы бережет вашу работу от лишних потерь времени и возникновения ошибок. Далее рассмотрим, как создать макрос, который безошибочно и автоматически выполнить рутинную работу в один клик мышкой. Так же рассмотрим в каких местах рабочей книги Excel можно создавать и хранить макросы. Как их запустить и выполнить, а также как их максимально оптимизировать под свои потребности.

Excel предоставляет большой и изобильный арсенал инструментов для хранения и обработки огромного количества информации с данными. Хотя для самой универсальной аналитической программы Excel – хранение данных само по себе менее интересно, чем возможность их обрабатывать, структурировать и анализировать с презентацией в отчетах. Для этих целей служит сильнейший аналитических инструмент по обработке данных такой как «Сводные таблицы». Но и его можно еще более усовершенствовать с помощью макросов. И тогда возможности сводных таблиц не знают границ.