Практически все офисные работники встречаются с задачами, которые связаны с обработкой больших массивов данных или рутинными вещами, когда руками приходится копировать и вставлять большой поток информации. В такие минуты кажется, что тратишь часы впустую, и «машина» наверняка справилась бы с рутиной за секунду. Делаем решительный шаг в сторону автоматизации рутины.
Кстати, мы ранее писали о том, сколько времени можно сэкономить, приложив усилия и записав макрос.
Как создать макрос в VBA/Excel?
Самый простой и быстрый способ — через запись макроса. Заходим в «Разработчик» и открываем окно для формирования запросов в VBA.
Не нашли «Разработчик»?
Файл → Параметры → Настроить ленту
На панели «Основные вкладки» отметьте галочкой «Разработчик» и нажмите «ОК».
Приступим к записи макроса. Для удобства можно дать макросу название.
Вводим команду. В нашем случае в ячейке С6 введем формулу
=C2+C3+C4+C5
Останавливаем запись макроса и проверяем его работу.
Меняем номера в ячейках C2 – C5. Переходим в «Макросы» и нажимаем «Выполнить».
Результат посчитан верно.
Что записал макрос? Как это выглядит в коде?
Переходим в «Разрабочик» → «Макросы» → выбираем наш макрос → нажимаем «Изменить»
Видим код.
Перейти в это поле можно проще: достаточно воспользоваться комбинацией Alt + F11, которая переводит нас в редактор кода VBA.
В это поле вы можете вставлять совершенно любой макрос. Если вы не специалист в VBA — достаточно будет подсмотреть готовые макросы на просторах интернета и скопировать их в свой документ.
Примеры
1. С помощью макросов можно копировать данные из одного файла в другой.
Sub CopyFiletoAnotherWorkbook()
‘Copy the data
Sheets(“Example 1”).Range(“A1:C5”).Copy
‘Create a new workbook
Workbooks.Add
‘Paste the data
ActiveSheet.Paste
‘Turn off application alerts
Application.DisplayAlerts = False
‘Save the newly file. Change the name of the directory.
ActiveWorkbook.SaveAs Filename:=“C:\EXAMPLE 2.xlsx”
‘Turn application alerts back on
Application.DisplayAlerts = True
End Sub
2. С помощью макросов можно удалять дубли. Ранее мы писали о том, как это сделать без помощи VBA.
Sub DelDups_OneList()
Dim iListCount As Integer
Dim iCtr As Integer
‘ Turn off screen updating to speed up macro.
Application.ScreenUpdating = False
‘ Get count of records to search through.
iListCount = Sheets(“Sheet1”).Range(“A1:A100”).Rows.Count
Sheets(“Sheet1”).Range(“A1”).Select
‘ Loop until end of records.
Do Until ActiveCell = “”
‘ Loop through records.
For iCtr = 1 To iListCount
‘ Don’t compare against yourself.
‘ To specify a different column, change 1 to the column number.
If ActiveCell.Row <> Sheets(“Sheet1”).Cells(iCtr, 1).Row Then
‘ Do comparison of next record.
If ActiveCell.Value = Sheets(“Sheet1”).Cells(iCtr, 1).Value Then
‘ If match is true then delete row.
Sheets(“Sheet1”).Cells(iCtr, 1).Delete xlShiftUp
‘ Increment counter to account for deleted row.
iCtr = iCtr + 1
End If
End If
Next iCtr
‘ Go to next record.
ActiveCell.Offset(1, 0).Select
Loop
Application.ScreenUpdating = True
MsgBox “Done!”
End Sub
Макрос находит повторяющиеся элементы в диапазоне А1:A100 и удаляет их. Важная особенность макроса — необходимость отсутствия в массиве пустых ячеек. Если список содержит пустые ячейки, необходимо отсортировать данные в возрастающем порядке, чтобы все пустые ячейки отображались в конце списка.
3. С помощью макросов можно сравнивать два списка и удалять повторяющуюся информацию.
Следующий пример макроса сравнивает один (ключевой) список с другим и удаляет повторяющиеся элементы во втором списке, которые находятся в главном списке. Первый список находится на листе Sheet1 в диапазоне А1:A10. Второй список располагается на Лист2 в диапазоне А1:A100. Для того, чтобы воспользоваться макросом выберите любой лист и затем запустите макрос.
Sub DelDups_TwoLists()
Dim iListCount As Integer
Dim iCtr As Integer
‘ Turn off screen updating to speed up macro.
Application.ScreenUpdating = False
‘ Get count of records to search through (list that will be deleted).
iListCount = Sheets(“Sheet2”).Range(“A1:A100”).Rows.Count
‘ Loop through the “master” list.
For Each x In Sheets(“Sheet1”).Range(“A1:A10”)
‘ Loop through all records in the second list.
For iCtr = 1 To iListCount
‘ Do comparison of next record.
‘ To specify a different column, change 1 to the column number.
If x.Value = Sheets(“Sheet2”).Cells(iCtr, 1).Value Then
‘ If match is true then delete row.
Sheets(“Sheet2”).Cells(iCtr, 1).Delete xlShiftUp
‘ Increment counter to account for deleted row.
iCtr = iCtr + 1
End If
Next iCtr
Next
Application.ScreenUpdating = True
MsgBox “Done!”
End Sub
Возможности VBA достаточно обширны. Мы привели лишь несколько примеров, однако вы можете изучить тему более детально самостоятельно, записавшись на курс «Excel Academy», просматривая обучающие видео и наши статьи.
Об одном хитром макросе для гигантской таблицы рассказывал Александр Вальцев, CEO SF Education, в видео.
Автор: Фомкина Ирина, эксперт SF Education