Excel/PowerQuery/VBA, Анализ данных

Используем макросы в работе с таблицами

Время прочтения: 3 мин.

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

Макросы – команда (макрокоманда) с чёткой запрограммированостью шагов, зафиксированных пользователем. При помощи зашитого в Microsoft языка сценариев Visual Basic for Application (VBA) производится запись макросов. Существуют следующие способы таких записей, использующие:

  1. VBA: лично пишем макросы путем программирования. Для этого задействуем редактор VBA находящийся в версиях Microsoft Excel.
  2. Macro Recorder: в этом процессе Excel сам сохраняет любое действие пользователя, которое станет элементом макроса, далее инструмент записи макросов перезапишет эти действия в шаги в VBA.

Хочу поделиться опытом использования в работе некоторых таких макросов.

1. VBA

  • замена применённой формулы на её результат. Для этого используются специальные вставки (выбрать диапазон – копировать — спец.вставка — значение). Упрощаем эти действия макросом следующего содержания:
Sub FormulaText ()
‘код макроса: каждая ячейка выделенной
области меняет формулы на их значения
For Each cell In Selection 
cell.Formula = cell.Value
Next cell
‘информация о завершении работы
MsgBox "Готово !"
End Sub
  • исключить ручной способ поиска и маркирования дубликатов. Приведенный ниже макрос ищет дубликаты и подсвечивает их:
Sub Dublikat ()
 ' задаем переменные диапазона
Dim MyRange As Range
Dim MyCell As Range
 ' задаем выделение диапазона
Set MyRange = Selection
 ' задаем цикл через диапазон
 For Each MyCell In MyRange
 'задаем функцию СЧЕТЕСЛИ, определяя значений в диапазоне
If WorksheetFunction.CountIf(MyRange, MyCell.Value) > 1 Then
' используем цвет по rgb
 MyCell.Interior.ColorIndex = 27
End If
'возвращаем следующую ячейку
Next MyCell
End Sub

2. Macro Recorder

При работе с текстовыми файлами возникает необходимость их экспорта в Excel (например, получение информации из банковских АС по созданным ранее шаблонам, в которых меняются периоды выгрузки, объекты, а структура остается неизменной). В этом случае используется мастер импорта текста (выбираем формат файла, символы-разделители, форматы данных столбцов).

Для исключения дублирования вызова мастера импорта текста, с помощью macro recorder создаем макрос, позволяющий экономить наше время на импорт файлов:

— запускаем макрорекордер (разработчик-запуск макроса), задаем имя;

— открываем текстовый файл, с помощью мастера импорта выполняем все необходимые нам настройки;

— останавливаем запись макрорекордера.

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

  1. добавление кнопки запуска макроса на панель быстрого доступа (Файл-параметры-панель быстрого доступа-выбрать команды из: макросы – добавить — изменить);
  2. сочетание клавиш (Разработчик-макросы-параметры макроса).

Таким образом макросы могут существенно увеличить эффективность работы с таблицами — просто выбрав одну команду, нажав клавишу либо щелкнув на панели инструментов.

Советуем почитать