Преобразовать «столбцы в строки» в Excel через VBA

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

«Столбцы в строки» с помощью VBA

Для тех, кто не сталкивался с такой «необходимостью», я приведу небольшой пример с двумя таблицами:

Левая таблица — исходная. В ней все данные хранятся в столбцах. Из такой таблицы крайне неудобно будет строить сводную: Excel каждый столбец будет рассматривать как отдельное поле, которое по отдельности надо будет перетаскивать при разработки структуры сводной таблицы. Это не будет большой проблемой, если таких столбцов несколько. А если 12, как в примере? Или 50, или еще больше? Вот в таком случае, максимально удобно будет использовать таблицу, у которой столбцы преобразованы в строки.

Правая таблица — искомая. Именно такой вариант таблицы максимально удобен для построения сводной. И целью моей статьи является показать, как можно преобразовать левую табличку в правую посредством языка VBA.

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

«Столбцы в строки»: разработка формы

Для начала нам необходимо создать форму, с помощью которой мы будем преобразовывать столбцы в строки: выделять исходный диапазон для преобразования, конечную ячейку для вставки новой таблицы, а также вводить количество столбцов, которые необходимо преобразовать. Итак, заходим в меню «Разработчик» и создаем новую форму.

Выделять диапазоны (исходный и конечный) мы будем с помощью элемента управления RefEdit (если данный элемент управления отсутствует на панели инструментов, то его необходимо добавить: щелкните по рабочей форме, затем нажмите «Tools» — «Additional Controls» и поставьте галочку рядом с «RefEdit.Ctrl»). Вводить необходимое количество обрабатываемых столбцов мы будем через элемент TextBox (его переименуем в Clmn_Count для удобства). Также добавляем CommandButton, то есть кнопку, для запуска самого основного макроса. Получается что-то вроде этого:

По нажатию на кнопку «Выполнить преобразование» будет выполняться следующий код:

Private Sub CommandButton1_Click()
s1 = RefEdit1.Value 
s2 = RefEdit2.Value
If s1 = "" Then MsgBox "Диапазон не выделен": Exit Sub
If s2 = "" Then MsgBox "Начальный диапазон не выделен": Exit Sub
Set r1 = Range(s1) 'преобразовываем в диапазон
Set r2 = Range(s2) 'преобразовываем в диапазон
If (r2.Rows.Count > 1) Or (r2.Columns.Count > 1) Then MsgBox "Конечный диапазон должен состоять из одной ячейки": Exit Sub
Prepare
Collapse Clmn_Count.Value, r1, r2 'преобразовать столбцы в строки
Ended
End Sub

Переменным s1 и s2 присваиваем значения полей RefEdit1 и RefEdit2 соответственно. Затем эти переменные проверяются: если они пустые, то уведомляем пользователя и выходим из процедуры, а если с ними все в порядке — преобразовываем их в диапазоны r1 и r2 соответственно. Проверяем второй диапазон на количество столбцов и строк: если этот диапазон не является ячейкой (т.е. строк и столбцов больше 1) — тоже уведомляем пользователя и завершаем процедуру.

Если все проверки пройдены — запускаем поочередно три процедуры:

1. Prepare — активация кода оптимизации
2. Collapse Clmn_Count.Value, r1, r2 — запуск основной обработки, преобразующей столбцы в строки
3. Ended — деактивация кода оптимизации

Пройдемся по каждому из них.

Prepare

Код оптимизации, взятый мной с Хабра. Отключает некоторые функции Excel, ускоряя выполнение макросов:

Public Sub Prepare()
    Application.ScreenUpdating = False 'отключение обновления экрана
    Application.Calculation = xlCalculationManual 'отключение автоматического пересчета формул
    Application.EnableEvents = False 'отключение обработки событий
    ActiveSheet.DisplayPageBreaks = False 'отключить отображение разрывов страниц
    Application.DisplayStatusBar = False 'отключить статусную строку в Excel
    Application.DisplayAlerts = False 'отключить отображение ошибок и предупреждений
End Sub

Collapse Clmn_Count.Value, r1, r2

Основной макрос, которые как раз выполняет нужную нам функцию: преобразовывает столбцы в строки. У него есть три аргумента: количество столбцов для преобразования (используется TextBox, который у нас называется Clmn_Count), и два диапазона (исходная табличка и конечная ячейка). Сам по себе макрос довольно сложный для восприятия, но я постараюсь вкратце объяснить его принцип работы на небольшом примере:

Private Sub Collapse(ByVal col_num As Integer, ByVal rng_source As Range, ByVal rng_new As Range)
lastrow = rng_source.Row + rng_source.Rows.Count - 1
NewRow = rng_new.Row
NewColumn = rng_new.Column
k = col_num
For q = 1 To (rng_source.Columns.Count - k) 'заполнение заголовков новой таблице по данным старой
    Cells(NewRow, NewColumn + q - 1) = Cells(rng_source.Row, rng_source.Column + q - 1)
Next q
lastrow_new = rng_new.Row + 1 'Сдвиг на 1 строку вниз от заголовка основного столбца
For i = rng_source.Row + 1 To lastrow 'перебираем все строки исходника
    Z = rng_source.Column + rng_source.Columns.Count - k 'z - первый столбец свертываемого значения
    For j = lastrow_new To lastrow_new + k - 1 'транспонируем столбцы в строки
        For q = 1 To (rng_source.Columns.Count - k)
            Cells(j, NewColumn + q - 1) = Cells(i, rng_source.Column + q - 1) 'Столбец с основными значениями
        Next q
        Cells(j, NewColumn + q - 1) = Cells(rng_source.Row, Z) 'Заголовки свертываемых столбцов в строки
        Cells(j, NewColumn + q) = Cells(i, Z)  'Значения свертываемых столбцов в строки
        Z = Z + 1
    Next j
    lastrow_new = lastrow_new + col_num
Next i
End Sub

1. Сначала копируем заголовки, которые у нас статичны (в нашем примере это заголовок «Клиент» и «SKU»)
2. Затем берем новую строку исходной таблицы и выполняем определенный алгоритм 3 раза (то есть столько раз, сколько у нас столбцов):
   • Копируем строку с наименованиями «Клиента» и «SKU»
   • Затем делаем сдвиг на Z столбцов вправо (изначально Z = 1 и наращивается, в нашем случае, до 3)
   • Копируем заголовок данного столбца (сначала это «Январь»), а затем соответствующее значение
   • Наращиваем Z (для сдвига на следующий месяц), и повторяем все сначала
3. Затем спускаемся на новую строку исходной таблицы и повторяем пункт 2 снова, до тех пор, пока не переберем все строки исходной таблицы:

На гифке показан наглядный пример выполнения данного алгоритма. После того, как выполнение макроса завершилось и все столбцы в строки преобразованы — переходим к макросу Ended.

Ended

Включение функций Excel, которые мы отключили с помощью макроса Prepare

Public Sub Ended()
    Application.ScreenUpdating = True
    Application.Calculation = xlCalculationAutomatic
    Application.EnableEvents = True
    Application.DisplayStatusBar = True
    Application.DisplayAlerts = True
End Sub

Заключение

В общем-то, на этом работа завершена. Принцип работы макроса очень прост: вызываем форму, выделяем исходную таблицу с заголовками(!), вводим количество столбцов, которое у нас должно быть преобразовано (как пример: если есть столбцы январь-декабрь, то вводим 12), затем выбираем ячейку, в которую мы хотим вставить исходную таблицу и нажимаем «Выполнить преобразование». И все, столбцы в строки преобразованы.

Преобразовать столбцы в строки: скачать пример

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