Всем привет, меня зовут Андрей!
В данной статье мне бы хотелось рассказать про макросы. Что такое макрос?
Макрос – это прежде всего компьютерный код, или программа, которая написана на языке VBA – Visual Basic for Applications. Макрос – одна из основных частей не только Excel, но и других продуктов MS Office. Потому язык VBA так и назван – ведь «for applications» так и переводится – «для приложений». А такие программы, как Word или Excel – это и есть те самые приложения.
Для тех, кто хоть немного знаком с английским языком или с любой версией языка программирования бейсик, работа с макросами не составит труда. Основная цель макросов – упростить работу с Excel. Как я уже говорил, и макросы, и язык VBA существуют не только при Excel – они есть при многих продуктах MS Office. Но раз мой блог – об Excel, то я буду рассказывать именно об Excel. И о тех макросах, которые помогут в работе с Excel.
Приведем пример, содержащий работу макросов в Excel. Пусть нам нужно заполнить таблицу, в которой содержится около 3600 строк и четыре столбца. Но для начала покажем только верхнюю часть этой таблицы:
Итак, мы видим следующее: в столбце A – значения углов (от 0 до 1 градуса, с шагом 0,1); в столбце B – синусы этих углов, в столбце C – косинусы, в столбце D – тангенсы.
Пока строк в таблице мало, заполнять эту таблицу не сложно. Сначала заполняем «шапку» (заголовки столбцов), затем в ячейку A2 вводим нуль. Потом в ячейку B2 вводим следующую формулу:
=SIN(РАДИАНЫ(A2)).
Аналогично вводятся формулы для других столбцов второй строки. Например, формула для C2:
=COS(РАДИАНЫ(A2)).
И формула для D2:
=TAN(РАДИАНЫ(A2)).
Почему именно «радианы»? Потому, что в столбце A у нас приводятся градусы в углах, а все тригонометрические функции без функции «радианы» предполагают, что наши исходные цифры находятся не в углах, а в радианах. Эта дополнительная функция – просто для преобразования градусов в радианы.
Теперь заполним следующую строку таблицы – ту, в которой находится угол в 0,1 градуса и все его тригонометрические функции. Со столбцами от B до D все понятно и просто – достаточно просто скопировать те формулы, что были раньше в тех же столбцах (строка 2). При небольшом количестве строк такой вариант вполне допустим. Поскольку все эти формулы содержат относительные ссылки (что это такое – мы подробно рассматривали в прошлой статье моего канала), то при копировании этих формул на другие строки изменится и сами формулы, и их значения, но не изменится главное: столбцы всегда будут показывать основные тригонометрические функции углов, причем и угол, и его основные функции расположены в одной строке, в той же последовательности, что и в заголовке, или «шапке» таблицы. Эта закономерность будет справедлива для любой строки таблицы. Пока строк мало – все легко и просто.
С тригонометрическими функциями все понятно. Но что касается остальных ячеек таблицы – то есть столбца A (ячейка A3) – там нам нужна такая формула:
=A2+1.
Теперь наша таблица полностью готова для заполнения. Еще раз повторим те формулы и значения, что содержатся в верхних трех строках Excel:
Теперь видно, почему мы обратили особое внимание на третью строчку. Эта таблица уже готова к дальнейшему заполнению. Как это понимать? Если мы выделим все ячейки третьей строки (от A3 до D3 включительно), скопируем данные этих ячеек, а затем выделим ту область, в которую нам необходимо вставить эти данные и выполним команду «Вставить», то мы получим продолжение заполнения данной таблицы. Если мы выделим только ячейку A4 и выполним команду «вставить» – то заполнится все 4 ячейки – от A4 до D4 включительно. Это правило работает всегда: если мы скопировали несколько ячеек и нам даже не известно, сколько именно ячеек в скопированной области, нам при вставке достаточно выбрать левую верхнюю из тех ячеек, в которых должна будет находиться скопированная область.
Если нам надо продлить таблицу на несколько строк – здесь все будет происходить аналогичным образом. Сначала выделяем все те ячейки, которые надо скопировать (в нашем примере это те же ячейки - от A3 до D3 включительно), затем выделяем несколько ячеек в столбце A. Если выделим ячейки A4 и A5 – то таблица продлится только на две строки, если выделим десять ячеек в столбце A под ячейкой A3 – то вся таблица пролится на 10 строк.
Но если число строк перевалит за несколько тысяч, то такой способ (копирование формул из одной строки таблицы и их последующая вставка на другие строки) будет не самым лучшим вариантом, особенно в тех случаях, если оперативная память компьютера не очень большая, или уже занята другими программами и приложениями.
И вот тогда на помощь придут... макросы! Оказывается, длинную таблицу можно заполнить не только с помощью тандема операций «копировать-вставить». Можно воспользоваться макросами. Конечно же, некоторые макросы могут выполнять, кроме прочих функций, функции копирования и вставки, но при больших объемах информации такое использования макросов, хотя и возможно, но не желательно и даже иногда может привести к «зависанию» программы.
Прежде всего, хочется сказать буквально несколько слов о том, как именно создаются макросы. Есть как минимум два способа. Первый способ достаточно простой.
Для начала найдем вкладку «Разработчик». Если этой вкладки на нашей ленте нет, ее можно добавить с помощью меню Файл→Параметры→Настройка ленты→разработчик.
Пока нас интересует только левая часть этой вкладки. Кнопка «Visual Basic» позволяет переходить в редактор Visual Basic; кнопка «Макросы» предполагает выполнение различных действий с макросами:
Почему в данной ситуации кнопка «Создать» не активна? Все очень просто: в графе «Имя макроса» есть имя уже существующего макроса (это имя Макрос1), а два макроса с одним именем не могут существовать. Не созданный макрос можно только создать, а с уже созданным макросом можно выполнять различные действия.
Ну и третья кнопка на вкладке «Разработчик» - это кнопка «Запись макроса. Во она:
Эта кнопка «Запись макроса» - одна из самых интересных. Если создание макроса с помощью кнопки «Макросы» предполагало непосредственно программирование, то есть знание каких-то команд или операторов языка VBA, то с кнопкой «Запись макроса» все гораздо проще – после нажатия этой кнопки можно выполнять обычные действия в электронной таблице Excel – создавать данные разных форматов, писать как числа, так и текст, создавать формулы, форматировать ячейки. Excel сам будет не только сохранять наши данные непосредственно в самой таблице Excel, но и писать автоматически программу-макрос, позволяющую делать эти действия несколько раз. Сразу после нажатия на кнопку запись «Запись макроса» изменится на «Остановить запись». Нажатие на кнопку «Остановить запись» приведет к завершению записи макроса.
Конечно же, не всякий макрос можно записать только лишь с помощью этой кнопки, но благодаря редактированию любой макрос можно сделать лучше.
Итак, вернемся к нашей тригонометрической таблице.
Допустим, что нам нужно заполнить около 3600 строк. Начнем заполнение со столбца A.
Напишем простой макрос. Назовем его Макрос1:
Sub Макрос1()
For i = 2 To 3602
Cells(i, 1).Select
ActiveCell.FormulaR1C1 = (i - 2) / 10
Next i
End Sub
Если мы выполним этот макрос, то увидим следующее:
- во-первых, столбец A будет заполнен числами от 0 до 360 с шагом 0,1;
- во-вторых, столбец A будет заполнен достаточно быстро – всего за несколько секунд;
- в-третьих, столбец A будет состоять не из формул, а из значений. Например, если раньше мы вводили в ячейку A3 формулу «=A2+1», и эта формула всегда была видна при наведении на эту ячейку, то теперь – мы видим только конкретные результаты. Кстати, чем больше формул и чем меньше констант, например текстовых или числовых, тем лучше для файла Excel – ведь наличие многих формул предполагает постоянный пересчет тех ячеек, которые содержат эти формулы. Чем меньше формул, тем быстрее работает Excel.
Кстати, аналогичным образом – с помощью макросов – можно заполнить и другие столбцы нашей тригонометрической таблицы.
Вот как будет выглядеть весь макрос «тригонометрические функции» (назовем этот макрос «Функции»):
Sub Функции()
For i = 2 To 3602
Cells(i, 1).Select
ActiveCell.FormulaR1C1 = (i - 2) / 10
Cells(i, 2).Select
ActiveCell.FormulaR1C1 = "=SIN(RADIANS(RC[-1]))"
Cells(i, 3).Select
ActiveCell.FormulaR1C1 = "=COS(RADIANS(RC[-2]))"
Cells(i, 4).Select
ActiveCell.FormulaR1C1 = "=TAN(RADIANS(RC[-3]))"
Next i
End Sub
Как видно, «Sub Функции()» - это начало макроса и его название. «End sub» - это окончание макроса, именно так завершается каждый макрос.
А теперь приведем еще один пример. И снова начнем без макросов. Предположим, что нам нужно создать таблицу умножения. Но не простую, а большую – 100 х 100.
Но вначале – небольшое отступление от основной темы. Здесь самое время рассказать про закрепление областей. Что это такое и в каких случаях оно бывает достаточно удобным?
Если у нас есть большая таблица. в которой много строк и (или) много столбцов, то закрепление областей позволит существенно улучшить работу с этой таблицей.
Итак, заполняем таблицу умножения 100 х 100.
Для начала заполним «шапку» - заголовки строк и столбцов. Есть несколько способов это сделать.
Способ 1.
1. В ячейку B1 вводим единицу (цифру 1), в ячейку C1 – формулу:
=B1+1
2. Копируем формулу из ячейки C1 в диапазон ячеек D1-CW1, получим нужный нам результат – то есть числа от 1 до 100 включительно в первой строке Excel, в ячейках от B1 до CW1 включительно.
Кстати, как скопировать формулы в достаточно большой диапазон? Тут нам поможет закрепление областей.
В таблице – фрагмент файла Excel. Как быстро заполнить цифры в первой строке от единицы до ста?
- сначала заполняем первые несколько чисел (в ячейку B1 – единицу. в ячейку C1 – нужную нам формулу);
- применяем закрепление областей – фиксируем несколько строк и несколько столбцов (перед выполнением команды закрепленияобластей необходимо активной ячейкой сначала решить, какие именно области мы хотим закрепить. Поскольку в нашем примере E4 - это правая нижняя граница закрепления, то перед выполнением команды закрепления нужно сделать активной ячейку F5 - чуть ниже и правее той области. Левая верхняя граница активной ячейки совпадет с правой нижней границей закрепленной области. Иногда можно закреплять только несколько строк или несколько столбцов). Кстати, если сначала выделить левую ячейку из какого-то диапазона, затем нажать Shift и, не отпуская Shift, выделить правую ячейку большого диапазона, то выделится весь диапазон. Это справедливо даже для тех случаев, если левая ячейка диапазона будет расположена левее границы закреплния областей, а правая ячейка - правее. Если в нужном нам диапазоне не одна строка, а несколько строк, тогда нас будут интересовать левая верхняя и правая нижняя ячейки большого диапазона. В принципе, тут и понимается важность операции по закреплению областей;
- затем переходим к крайней правой ячейке того диапазона, в который мы должны ввести нужную нам формулу. В нашем случае это ячейка CW1. Если ввести в поле Имя «CW1», то активной станет именно ячейка CW1.
Кстати, о поле Имя. Оно находится чуть выше ячейки A1:
В данном примере в графе «Имя» (в поле «Имя») мы видим DA9. Это значит, что активна ячейка DA9. Обычно в графе «Имя» - либо адрес активной ячейки, либо адрес верхней левой ячейки (если выделена не одна ячейка), либо имя выделенной ячейки или выделенного диапазона ячеек.
Но продолжим заполнять таблицу умножения.
3. Аналогично заполняем заголовок строк: сначала в ячейку A2 вводим единицу, затем в ячейку A3 вводим формулу:
=A2+1
4. Затем нужно скопировать эту формулу на весь диапазон ячеек – от A4 до A101 включительно. Как мы уже говорили, при закрепленных областях это будет сделать достаточно просто.
Кстати, мы уже говорили, что есть несколько вариантов заполнения «шапки» таблицы умножения, то есть заголовков строк и столбцов.
Рассмотрим второй вариант.
1. В ячейку B1 вводим единицу, в ячейку B2 – двойку.
2. Выделим мышкой ячейки B1 и B2, а курсор – след от мыши – аккуратно разместим в нижний правый угол выделенных ячеек таким образом, чтобы курсор изменил свой вид до своеобразного крестика – как на рисунке:
Кстати – это интересно: если нажать кнопку Prt Scr – Print Screen – то скопируется всё, кроме... курсора, следа от «мышки». Как этого избежать? Все достаточно просто.
1. Устанавливаем программу, которая позволяет писать видео с компьютера (записывать все то, что делается на компьютере). Одна из доступных и бесплатных программ – OBS.
2. Начинаем запись на видео всего того, что делается на компьютере.
3. Создаем на компьютере именно ту картинку, включая след от «мышки», которую мы в конце концов должны получить в виде графического файла.
4. Останавливаем запись видео.
5. С помощью программ просмотра видео запускаем тот файл, который нам нужен (только что записанное видео с компьютера), и находим в нем нужную нам картинку.
6. Ставим видео на паузу в нужном нам месте.
7. Нажимаем кнопку Prt Scr. Очевидно, что тот след от мыши, который был на видео, останется в оперативной памяти компьютера. И когда мы войдем в графический редактор и выполним команду «Вставить», то получится вставка рисунка экрана вместе с тем следом курсора мыши, который нас интересует.
Итак, продолжим заполнять «шапку» от нашей таблицы умножения. После того, как мы выделили мышкой две ячейки и поместили след от мышки в правый нижний угол этого выделения, как было и показано на рисунке, нам нужно будет нажать на левую кнопку мыши и, не отпуская эту кнопку, вести курсор вправо. Нужные нам цифры заполнятся автоматически.
Кстати, заголовки строк можно заполнить аналогичным образом – сперва заполняем единицу и двойку (ячейки A2 и A3), затем выделяем две ячейки с единицей и двойкой, а потом перемещаем след от мыши в правый нижний угол выделенных ячеек. И, не отпуская левой кнопки мыши, переводим курсор вниз.
Есть и третий способ заполнения «шапки».
В ячейку B1 вводим формулу:
=СТОЛБЕЦ()-1
и тогда уже после этого, подведя курсор к правой нижней части ячейки B1, можно проделывать то, что мы проделывали ранее, после заполнения ячейки B2.
Конечно же, для «шапки» строк будет такая формула:
=СТРОКА()-1.
Допустим, «шапку», то есть заголовки строк и столбцов нашей таблицы умножения, мы уже заполнили. Теперь останется самое главное – заполнить саму таблицу. Как будем заполнять? Здесь есть тоже несколько вариантов.
Первый вариант – самый простой. С помощью одной формулы.
Если в ячейку B2 – это левая верхняя ячейка того диапазона, где нам нужна эта формула, ввести формулу:
=$A2*B$1
и скопировать эту формулу на весь диапазон от B2 до CW101 включительно, то наша таблица будет заполнена полностью. Кстати. в прошлой статье мы писали достаточно подробно о том, какие бывают ссылки в Excel. А на данном примере можно видеть: правильно составленная формула с поставленными на правильное место значками «доллара» существенно упрощает работу в Excel, минимизирует число разных формул.
А теперь перейдем ко второму способу заполнения нашей таблицы умножения.
Если до этого мы сохраняли наш файл – то его нужно сохранить под другим названием. Главное – под другим расширением. Нас интересует расширение «Книга Excel с поддержкой макросов».
Кстати, еще одна интересная «фишечка». Если нажать только одну кнопку «Print Screen», то скопируется весь экран – кроме курсора от «мышки». Но если нас интересует не весь экран, а какая-то одна экранная форма, как это и представлено в нашем случае, то нужно нажать одновременно с этой кнопкой еще и Alt. Тогда другая информация с экрана не будет загружена в оперативную память.
Конечно же, с помощью макросов можно будет заполнить таблицу умножения быстрее. Вот наш макрос:
Sub ТаблицаУмножения()
For i = 2 To 101
For j = 2 To 101
Cells(i, j).Activate
ActiveCell.FormulaR1C1 = "=RC1*R1C"
Next j
Next i
End Sub
Если мы запустим этот макрос, то увидим, что на заполнение таблицы умножения размеро 100 на 100 уйдет всего несколько секунд. Чтобы избавиться от формул и заменить их константами, можно выделить весь лись Excel, скопировать его, и тут же все вставить с помощью специальной вставки, задействовав переключатель "Значения".
Кстати, как видно из макроса ТаблицаУмножения, в макросах возможны вложенные циклы - несколько операторов for и next.
А на этом пока всё, в новых статьях я буду рассказывать ещё больше интересного о работе с электронными таблицами Excel.