Мы видели функцию транспонирования на листе Excel, когда мы вставляем любую таблицу данных на рабочий лист, транспонирование меняет положение строк и столбцов, т.е. строки становятся столбцами, а столбцы становятся строками в таблице данных, теперь, поскольку это рабочий лист функцию в VBA, мы используем ее с методом Application.worksheet в VBA.
Как транспонировать в VBA?
Переключение строк и столбцов - это один из методов манипулирования данными, который делают почти все пользователи в Excel. Процесс преобразования горизонтальных данных в вертикальные и вертикальных данных в горизонтальные в Excel называется «транспонированием». Я уверен, что вы должны быть знакомы с транспонированием на обычном листе, в этой статье мы покажем вам, как использовать метод транспонирования в кодировании VBA.
Мы можем транспонировать в VBA двумя способами.
- Транспонировать с помощью формулы TRANSPOSE.
- Транспонировать с помощью специального метода вставки.
При транспонировании мы меняем местами строки на столбцы и столбцы на строки. Например, если данные находятся в массиве 4 X 3, они становятся массивом 3 X 4.
Давайте посмотрим на несколько примеров транспонирования столбца в строку в VBA.
# 1 - Транспонирование VBA с использованием формулы TRANSPOSE
Подобно тому, как мы используем TRANSPOSE в excel, мы можем использовать формулу TRANSPOSE и в VBA. У нас нет формулы TRANSPOSE в VBA, поэтому нам нужно использовать класс функции рабочего листа.
Для примера посмотрите на изображение данных ниже.
Мы попробуем транспонировать этот массив значений. Выполните следующие шаги, чтобы транспонировать данные.
Шаг 1. Запустите подпроцедуру
Код:
Sub Transpose_Example1 () End Sub
Шаг 2: Сначала нам нужно решить, куда мы собираемся транспонировать данные. В этом я решил переместить из ячейки D1 в H2. Итак, введите код VBA как диапазон («D1: H2»). Значение =
Код:
Sub Transpose_Example1 () Диапазон ("D1: H2"). Значение = End Sub 
Шаг 3: Теперь в вышеупомянутом диапазоне нам нужно значение диапазона от A1 до B5. Чтобы добраться до этого открытого класса «Функция рабочего листа», выберите формулу «Транспонировать».
Шаг 4: В аргументе 1 укажите диапазон источника данных, то есть диапазон («A1: D5») .
Код:
Sub Transpose_Example1 () Range ("D1: H2"). Value = WorksheetFunction.Transpose (Range ("A1: D5")) End Sub 
Хорошо, мы закончили кодирование формулы TRANSPOSE. Теперь запустите код, чтобы увидеть результат в диапазоне ячеек от D1 до H2.
Как мы видели на изображении выше, он преобразовал диапазон ячеек из столбцов в строки.
# 2 - Транспонирование VBA с использованием специального метода вставки
Мы также можем транспонировать, используя метод Специальной вставки. Рассмотрим те же данные и для этого примера.
Первое, что нам нужно сделать для транспонирования, - это скопировать данные. Поэтому напишите код как Range («A1: B5»). Скопируйте
Код:
Sub Transpose_Example2 () Диапазон ("A1: B5"). Копировать End Sub 
Следующее, что нам нужно сделать, это решить, куда мы будем вставлять данные. В этом случае я выбрал D1 в качестве желаемой ячейки назначения.
Код:
Sub Transpose_Example2 () Диапазон ("A1: B5"). Копировать диапазон ("D1"). Конец подписки
После того, как желаемая ячейка назначения выбрана, нам нужно выбрать «Специальный метод вставки».
С помощью специальной вставки мы можем выполнять все действия, которые у нас есть с помощью обычных специальных методов вставки на листе.
Игнорируйте все параметры и выберите последний параметр, т.е. транспонирование, и сделайте его ИСТИННЫМ.
Код:
Sub Transpose_Example2 () Диапазон ("A1: B5"). Копировать диапазон ("D1"). PasteSpecial Transpose: = True End Sub 
Это также перенесет данные, как и в предыдущем методе.
Таким образом, мы можем использовать формулу TRANSPOSE или специальный метод Paste, чтобы транспонировать данные для переключения строк в столбцы и столбцов в строки.
То, что нужно запомнить
- Если мы используем функцию рабочего листа TRANSPOSE, необходимо обязательно рассчитать количество строк и столбцов для транспонирования данных. Если у нас есть 5 строк и 3 столбца, то при транспонировании он становится 3 строками и 5 столбцами.
- Если вам нужно такое же форматирование при использовании специальной вставки, вы должны использовать аргумент типа вставки как «xlPasteFormats»
Вы можете скачать этот шаблон VBA Transpose Excel здесь - Шаблон VBA Transpose Excel