Программирование на экселе

VBA Excel. Начинаем программировать с нуля

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

Знакомство с редактором VBA

1. Создайте новую книгу Excel и сохраните ее как книгу с поддержкой макросов с расширением .xlsm. В старых версиях Excel по 2003 год – как обычную книгу с расширением .xls.
2. Нажмите сочетание клавиш «левая_клавиша_Alt+F11», которое откроет редактор VBA. С правой клавишей Alt такой фокус не пройдет. Также, в редактор VBA можно перейти по ссылке «Visual Basic» из панели инструментов «Разработчик» на ленте быстрого доступа. Если вкладки «Разработчик» на ленте нет, ее следует добавить в настройках параметров Excel.

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

1. Нажмите кнопку «Module» во вкладке «Insert» главного меню. То же подменю откроется при нажатии на вторую кнопку после значка Excel на панели инструментов.

После нажатия кнопки «Module» вы увидите ссылку на него, появившуюся в проводнике слева.

Первая программа на VBA Excel

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

1. Откройте стандартный модуль двойным кликом по его ссылке в проводнике. Поместите в него курсор и нажмите кнопку «Procedure…» во вкладке «Insert» главного меню. Та же ссылка будет доступна при нажатии на вторую кнопку после значка Excel на панели инструментов.

В результате откроется окно добавления шаблона процедуры (Sub).

1. Наберите в поле «Name» имя процедуры: «Primer1», или скопируйте его отсюда и вставьте в поле «Name». Нажмите кнопку «OK», чтобы добавить в модуль первую и последнюю строки процедуры.

Имя процедуры может быть написано как на латинице, так и на кириллице, может содержать цифры и знак подчеркивания. Оно обязательно должно начинаться с буквы и не содержать пробелы, вместо которых следует использовать знак подчеркивания.

1. Вставьте внутрь шаблона процедуры следующую строку: MsgBox «Привет» .

Функция MsgBox выводит информационное сообщение с указанным текстом. В нашем примере – это «Привет».

1. Проверьте, что курсор находится внутри процедуры, и запустите ее, нажав клавишу «F5». А также, запустить процедуру на выполнение можно, нажав на треугольник (на изображении под пунктом меню «Debug») или на кнопку «Run Sub/UserForm» во вкладке «Run» главного меню редактора VBA Excel.

Если вы увидели такое сообщение, как на изображении, то, поздравляю – вы написали свою первую программу!

Работа с переменными

Чтобы использовать в процедуре переменные, их необходимо объявить с помощью ключевого слова «Dim». Если при объявлении переменных не указать типы данных, они смогут принимать любые доступные в VBA Excel значения. Комментарии в тексте процедур начинаются со знака «’» (апостроф).

Пример 2
Присвоение переменным числовых значений:

5 полезных функций Excel для начинающих программистов

Рабо­ту в таб­лич­ном редак­то­ре Excel тоже мож­но авто­ма­ти­зи­ро­вать и запро­грам­ми­ро­вать. Вы смо­же­те здо­ро­во помочь сво­им бух­гал­те­рам с отчё­та­ми, мар­ке­то­ло­гам — с ана­ли­зом рекла­мы, а сами научи­тесь раз­би­рать­ся в алго­рит­мах.

Мы раз­бе­рём 5 полез­ных функ­ций Excel. Если вы дума­е­те, что Excel — это толь­ко про циф­ры и таб­лич­ки, то вот вам видео, где в Excel запро­грам­ми­ро­ва­ли насто­я­щую 3D-игру.

Что­бы было про­ще разо­брать­ся, все фор­му­лы и при­ме­ры мы запи­са­ли в Excel-файл, кото­рый мож­но ска­чать и исполь­зо­вать для экс­пе­ри­мен­тов. Каж­дый при­мер — на отдель­ной вклад­ке сни­зу.

Это одна из самых важ­ных функ­ций в Excel. Как и в насто­я­щем язы­ке про­грам­ми­ро­ва­ния, она про­ве­ря­ет какое-то усло­вие, и если оно выпол­ня­ет­ся — пишет в ячей­ку что-то одно, а если нет — пишет что-то дру­гое.

В общем виде она выгля­дит так:

В усло­вии может быть что угод­но: срав­не­ние яче­ек, дру­гие фор­му­лы, срав­не­ния и мате­ма­ти­че­ские коман­ды — всё, что вам нуж­но про­ве­рить.

На прак­ти­ке мож­но сде­лать, напри­мер, так: пусть Excel про­ве­ря­ет воз­раст, и пишет доку­мент, кото­рый в этом воз­расте удо­сто­ве­ря­ет лич­ность. До 14 лет это сви­де­тель­ство о рож­де­нии, а после — пас­порт. Для это­го исполь­зу­ем такую коман­ду в ячей­ке:

Про­грам­ма про­ве­рит, что сто­ит в преды­ду­щей ячей­ке, и если там чис­ло мень­ше 14, то напи­шет «Сви­де­тель­ство о рож­де­нии», а если уже есть 14 лет, то «Пас­порт»:

СЧЁТЕСЛИ и СЧЁТЕСЛИМН

Часто быва­ет нуж­но посчи­тать коли­че­ство строк или запол­нен­ных яче­ек по каким-то пара­мет­рам, напри­мер узнать коли­че­ство работ­ни­ков стар­ше 40 лет, или посчи­тать все стро­ки, где есть сло­во «пере­мен­ная». Для это­го исполь­зу­ют функ­цию СЧЁТЕСЛИ:

Диа­па­зон — это груп­па яче­ек, в кото­рых мы будем что-то искать, а усло­вие — это по како­му пара­мет­ру мы решим, что ячей­ка нам под­хо­дит.

Допу­стим мы ведём семей­ный бюд­жет и нам нуж­но узнать, сколь­ко раз у нас были покуп­ки боль­ше 5000 руб­лей в одном чеке. Для это­го исполь­зу­ем такое:

Теперь послож­нее. Если нам нуж­но посчи­тать коли­че­ство яче­ек по несколь­ким усло­ви­ям одно­вре­мен­но, то исполь­зу­ют функ­цию СЧЁТЕСЛИМН:

То, что в пря­мо­уголь­ных скоб­ках — допол­ни­тель­ные усло­вия, их может быть мак­си­мум 127, а сами скоб­ки при этом ста­вить не нуж­но. Обра­ти­те вни­ма­ние что для каж­до­го ново­го усло­вия мож­но задать свой диа­па­зон. Функ­ция про­ве­рит все усло­вия, каж­дое в сво­ём диа­па­зоне, и если все усло­вия одно­вре­мен­но выпол­ня­ет­ся — посчи­та­ет коли­че­ство таких яче­ек.

Для при­ме­ра най­дём все тра­ты боль­ше 5000 в одном чеке, за кото­рые отве­ча­ет Миша:

Теперь у нас пер­вый диа­па­зон отве­ча­ет за сум­му в чеке, а вто­рой — за име­на, поэто­му и усло­вия у каж­до­го из них раз­ные.

Функ­ция воз­вра­ща­ет мак­си­маль­ное зна­че­ние из выбран­но­го диа­па­зо­на:

Давай­те исполь­зу­ем её, что­бы най­ти самую доро­гую покуп­ку в бюд­же­те из преды­ду­ще­го при­ме­ра. Для это­го запи­шем её так:

ПОИСКПОЗ

Поиск пози­ции часто исполь­зу­ют, когда нуж­но най­ти поло­же­ние опре­де­лён­но­го эле­мен­та в таб­ли­це:

Зна­че­ние — то, что нам нуж­но най­ти в таб­ли­це, диа­па­зон про­смот­ра — где имен­но мы будем искать наше зна­че­ние. Если в каче­стве диа­па­зо­на ука­жем стол­бец с циф­ра­ми, то функ­ция вер­нёт номер в этом столб­це, где встре­ча­ет­ся нуж­ная циф­ра.

Давай­те най­дём номер стро­ки с мак­си­маль­ной покуп­кой:

Это чис­ло нам пона­до­бит­ся для сле­ду­ю­щей функ­ции

ИНДЕКС

Эта функ­ция воз­вра­ща­ет зна­че­ние кон­крет­ной ячей­ки в ука­зан­ном диа­па­зоне:

=ИНДЕКС(диапазон; номер_строки; [номер_столбца])

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

Попро­бу­ем в нашем при­ме­ре с бюд­же­том най­ти того, кто сде­лал самую боль­шую покуп­ку. Мы уже зна­ем номер стро­ки, в кото­рой запи­са­на самая боль­шая сум­ма, и на этой же стро­ке запи­сан тот, кто эту сум­му потра­тил. Исполь­зу­ем это:

Когда Excel встре­тит такую фор­му­лу, он возь­мёт наш диа­па­зон из вто­ро­го столб­ца, затем возь­мёт номер стро­ки с самой боль­шой покуп­кой (она у нас в ячей­ке Е10, нашли в про­шлом при­ме­ре), и опре­де­лит, что самую круп­ную покуп­ку сде­лал Миша.

Что дальше

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

Начала программирования в Excel

Оптимизация

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

Хранение данных

Одним из недостатков традиционных подходов, при которых обучение тесно связывается с изучением некоторого языка программирования, является то, что базы данных, не являющиеся частью языка программирования, появляются где-то на поздних этапах. В Excel эта трудность преодолевается естественным путем. Благодаря инкапсуляции, о которой я говорил выше, рабочая книга представляет собой своеобразную базу данных. К этому следует добавить, что в Excel есть и специальные средства — списки Excel, — позволяющие выполнять специальные операции над данными: фильтрацию, поиск и другие типичные операции над данными. Нельзя не упомянуть, одно из главных назначений Excel состоит в том, чтобы служить средством анализа и отображения данных, поступающих от различных внешних источников данных — баз данных, хранилищ как структурированной, так и неструктурированной информации. Так что средств, позволяющих работать с собственными и внешними базами данных в Excel предостаточно.

Программирование на VBA

Excel дает возможность решать многие задачи, не требуя привлечения языка программирования, прежде всего, благодаря свойствам машины вычислений. Понятно, что такая возможность и делает Excel столь привлекательным для многочисленных пользователей. Но работа с машиной вычислений Excel полезна и при обучении программистов, тем более что процесс вычислений не является скрытым — все формулы видны, все значения доступны. Более того, различные графики могут помочь проследить за деталями процесса вычислений. Но, конечно, для программистов главное достоинство состоит в том, что они могут сочетать работу руками с программированием на языке VBA. Языку VBA я посвятил отдельную книгу [1], где достаточно много было сказано о тех его свойствах, которые делают этот язык привлекательным для целей начального обучения программированию. Он вполне подходит на роль «первого языка», с которого следует начинать осваивать программирование и который оказывает большое влияние на последующий стиль программирования.

Программированию на VBA в среде Excel будет посвящено основное содержание этой книги. Но сейчас я хочу немного отвлечься и сказать несколько слов об одном важном инструменте, интенсивно используемом в Excel и называемом MacroRecorder. Это средство, общее для многих приложений среды Office, обеспечивает возможность «программирования без программирования», позволяя получить программный текст — макрос, как результат выполнения некоторых операций над рабочим листом. Я говорю здесь о MacroRecorder еще и потому, что он одновременно является прекрасным инструментом, используемым при обучении программированию.

По существу, MacroRecorder — это обычный транслятор «необычного» языка действий. Включив запись макроса, Вы инициируете запись всех Ваших действий: выбор той или иной ячейки или области, запись в ячейку значения или формулы, вызов некоторой функции, построение диаграммы или таблицы и т.д. В любой момент Вы можете выключить запись действий. В результате MacroRecorder создаст макрос на языке VBA, запуск которого приведет к тому же эффекту, что и Ваши действия. Таким образом, один раз, сделав все руками, создав документ или его фрагмент, Вы бесплатно получаете программу, которая делает то же самое. Вся штука в том, что программу можно запускать многократно. Что же касается действий пользователя, возможно, весьма квалифицированно работающего в среде Office, то я уже не раз говорил, что они естественным образом интерпретируются, как действия над объектами того или иного приложения Office — вызов и изменение соответствующих свойств и методов этих объектов. Для программных сред, в которых пользователю предоставляются широкие возможности манипуляции над объектами, видимыми на экране, другими словами, для сред визуального программирования инструментарий, подобный MacroRecorder, совершенно естественная вещь, — все равно за действиями пользователя нужно следить и выполнять их. Транслируя действия пользователя, MacroRecorder создает макрос — процедуру без параметров на языке VBA. Созданный макрос можно выполнить в любой нужный момент. Более того, можно создать инструментальную кнопку и связать с ней созданный макрос, — теперь щелчок пользователя по кнопке и будет запускать макрос на выполнение.

Создание макросов с использованием MacroRecorder я и называю программированием без программирования. Макросы играют двоякую роль. Они полезны, когда задачу, которую в принципе можно решить без программирования, приходится решать многократно. Не менее важно, что макросы можно использовать для обучения программированию, в частности, использованию объектов Office и VBA. Часто трудно выбрать, как лучше запрограммировать решение некоторой задачи. Если это можно сделать вручную, то есть смысл создать макрос и посмотреть, как MacroRecorder решает подобную задачу. Анализ текста макросов напоминает мне исследование под микроскопом, — открывается масса неизвестных деталей.

Как работает машина вычислений Excel

Коль речь идет об основах программирования в Excel , то хочу попытаться достаточно точно описать семантику Excel , — как в нем проводятся вычисления. Как я уже говорил, документ Excel является рабочей книгой, каждый рабочий лист которой представляет конечную прямоугольную таблицу, элементы которой называются ячейками. Листы книги, строки, столбцы таблицы и сами ячейки изначально именованы. В любой части этой таблицы можно выделить некоторую подобласть, обычно, прямоугольный интервал ячеек и с ним работать. Пожалуй, удобнее сразу перейти к объектной терминологии. Замечу, одним из основных объектов Excel является объект Range . Ячейки, строки, столбцы, сама таблица целиком, любые ее подобласти, в том числе и не смежные, полученные, как пересечение или объединение интервалов — все это представимо единственным объектом Range . О многих свойствах и методах этого объекта будет рассказано в главе 3, посвященной объектам Excel . О некоторых скажу сейчас, рассматривая семантику вычислений . Благодаря тому, что объект Range имеет свойство Name , можно давать собственные имена используемым объектам, в том числе и ячейкам. Более важно, что объект Range имеет свойства Value и Formula . Свойство Value позволяет задать значение объекта, любого допустимого типа. Здесь существенно используются возможности универсального типа Variant , с которым совместим тип Range . Свойство Formula позволяет связать с объектом формулу, вычисляющую значение . Поскольку Excel позволяет работать с массивами, то можно вводить и специальный класс формул — формулы над массивами. Во многом возможности Excel определяются тем, насколько широк класс задаваемых формул. Формулы строятся из констант, переменных, роль которых играют объекты Range, и стандартных функций, объединенных знаками операций. Я уже говорил, сколь велик выбор стандартных функций, как общего назначения, так и ориентированных на конкретные предметные области, прежде всего финансы и статистику. Допустимо использование в формулах и функций, разработанных на VBA программистом.

Поскольку формула, записанная в одной ячейке, может ссылаться на другие ячейки таблицы, то между ячейками возникает отношение зависимости. Дадим точные формулировки: Если формула в ячейке Y содержит ссылку на ячейку X, то говорят, что Y непосредственно зависит от X, а X непосредственно предшествует Y. Обобщая понятие непосредственной зависимости, мы говорим, Y зависит от X, а X предшествует Y, если существует цепочка ячеек Z1, Z2, …ZK, начинающаяся с X и заканчивающаяся Y, такая, что каждые два соседние элемента цепочки связаны отношением непосредственной зависимости (непосредственного предшествования). Свойства Dependents и Precedents объекта Range сохраняют списки всех его зависимых и соответственно предшествующих ячеек. Это позволяет с одной стороны графически отображать на экране зависимости между ячейками и, что более важно, эффективно организовать вычисления при изменении значений в тех или иных ячейках.

Однако Excel не всегда в состоянии определить, существует ли зависимость между ячейками. Пусть, например, в ячейку A1 введено значение 2 , в ячейку B1- 7 , в ячейку B2 — формула » =A1+B1 «, а в ячейку A2 — формула » = myfunct1(B1) «, где myfunct1(X) — собственная функция , заданная программой:

Excel может определить, что ячейка B2 зависит от ячеек A1 и В1. Но он не распознает, что ячейка A2 также зависит от A1, он обнаружит только ее зависимость от B1. Причина в том, что Excel не анализирует тексты пользовательских функций, используемых в формулах, на наличие в них ссылок на ячейки таблицы. Не из-за того, что его разработчики поленились это сделать, но потому, что эта задача алгоритмически неразрешима в общем случае. Для определения отношения зависимости одной ячейки от другой используются лишь явные ссылки в параметрах вызываемых функций (B1 для ячейки A2 в нашем примере). Увидеть связи между ячейками можно на рисунке 1.1, где показан общий вид первого листа книги CourseFirst, с которой мы будем работать в ближайшее время.