VBA Transpose - Как использовать Excel VBA Transpose?

Содержание:

Anonim

VBA Transpose

Будучи пользователем Microsoft Excel, вы, возможно, часто использовали функцию транспонирования, которая позволяет переключаться между строками и столбцами для массива. Преобразование строк в столбец и столбцов в строки - это то, что функция транспонирования делает для вас в Excel. Количество строк становится числом столбцов и наоборот. Значит, если у вас есть 2 строки и 3 столбца в вашем массиве, после транспонирования он изменится на массив с 3 строками и 2 столбцами. В этом руководстве мы рассмотрим VBA Transpose, которая позволит вам автоматизировать метод транспонирования, который вы будете использовать в Excel.

Синтаксис транспонирования в Excel VBA

VBA Transpose имеет следующий синтаксис:

Где,

Arg1 : это обязательный аргумент, который представляет собой не что иное, как диапазон ячеек, которые мы хотели транспонировать (т.е. массив).

Первая часть синтаксиса - это не что иное, как выражение, в котором можно использовать функцию Transpose. Например, WorksheetFunction.

Как использовать Excel VBA Transpose?

Мы узнаем, как использовать Transpose с несколькими примерами в Excel VBA.

Вы можете скачать этот шаблон VBA Transpose Excel здесь - Шаблон VBA Transpose Excel

Пример № 1 - VBA транспонирование одномерного массива

Предположим, вы работаете с данными со списками (которые являются одномерным массивом), такими как имена сотрудников («Лалит», «Снеха», «Этил», «Джон», «Кори»), и вам нужен этот список быть вставленным в ячейку Excel. Посмотрим, как мы можем это сделать.

Выполните следующие шаги, чтобы использовать Transpose в VBA.

Шаг 1: Вставьте новый модуль и определите новую подпроцедуру для создания макроса в VBA.

Код:

 Sub Trans_ex1 () End Sub

Шаг 2: Определите новую переменную, которая может содержать ваш одномерный массив.

Код:

 Sub Trans_ex1 () Dim Arr1 как вариант End Sub

Шаг 3: Определите список как массив, используя функцию Array. Это будет список, который вы хотите вставить в свой лист Excel.

Код:

 Sub Trans_ex1 () Dim Arr1 As Variant Arr1 = Array ("Lalit", "Sneha", "Ethyl", "John", "Cory") End Sub

Шаг 4: Теперь используйте метод Range.Value, чтобы определить диапазон, в который вы хотите вставить эти значения.

Код:

 Sub Trans_ex1 () Dim Arr1 As Variant Arr1 = Array ("Lalit", "Sneha", "Ethyl", "John", "Cory") Range ("A1: A5"). Значение = End Sub

Шаг 5: Используйте Application.WorksheetFunction.Transpose для данного массива, чтобы иметь возможность транспонировать список, определенный в переменной Arr1.

Код:

 Sub Trans_ex1 () Dim Arr1 As Variant Arr1 = Array ("Lalit", "Sneha", "Ethyl", "John", "Cory") Range ("A1: A5"). Значение = Application.WorksheetFunction.Transpose (Arr1 ) End Sub

Здесь мы определили код, который позволит системе транспонировать данные в массиве списков с именем Arr1, а затем сохранять их в ячейке A1: A5 на активном рабочем листе.

Шаг 6: Нажмите F5 или кнопку «Выполнить» в VBE, чтобы запустить этот код и увидеть результаты в активной электронной таблице Excel.

Вы можете видеть, что данный массив списка перемещается в один столбец и сохраняется в ячейках от A1 до A5.

Пример №2 - VBA транспонирование двумерного массива

Предположим, у вас есть двумерный массив сотрудников и их зарплаты, как показано ниже:

Это двумерные данные с шестью строками и двумя столбцами. После транспонирования это будет массив с двумя строками и шестью столбцами.

Выполните следующие шаги, чтобы использовать Transpose в VBA.

Шаг 1: Определите подпроцедуру для сохранения макроса.

Код:

 Sub Trans_Ex2 () End Sub

Шаг 2: Определите диапазон, в который вы хотите перенести эти данные. Это можно сделать с помощью функции Sheets.Range.Value. Я хотел бы сохранить транспонированный массив в ячейку от D1 до I2.

Код:

 Sub Trans_Ex2 () Sheets ("Пример # 2"). Диапазон ("D1: I2"). Значение = End Sub

Шаг 3: Используйте WorksheetFunction.Transpose, чтобы иметь возможность назначить массив A1: B6 для транспонирования функции.

Код:

 Sub Trans_Ex2 () Sheets ("Пример # 2"). Диапазон ("D1: I2"). Значение = WorksheetFunction.Transpose (End Sub

Шаг 4: Нам нужно указать аргумент для функции транспонирования. Мы хотели перенести диапазон массива A1: B6. Таким образом, используйте Range («A1: B6») в качестве аргумента.

Код:

 Sub Trans_Ex2 () Sheets ("Пример # 2"). Range ("D1: I2"). Value = WorksheetFunction.Transpose (Range ("A1: B6")) End Sub

Шаг 5: Нажмите F5 или кнопку Run, чтобы запустить этот код и увидеть результат.

Здесь диапазон массива A1: B6 транспонируется и сохраняется в диапазоне массива D1: I2 с помощью функции VBA Transpose в Excel.

Пример № 3 - VBA транспонирование массива с помощью специального метода вставки

Мы также можем транспонировать массив и вставлять как специальные, как в Excel, используя Alt + E + S. Мы можем использовать различные операции в этом специальном методе вставки.

Давайте рассмотрим те же данные, которые мы использовали в предыдущем примере.

Выполните следующие шаги, чтобы использовать Transpose в VBA.

Шаг 1: Определите подпроцедуру для сохранения макроса.

Код:

 Sub Trans_Ex3 () End Sub

Шаг 2. Определите две новые переменные, одна из которых может содержать массив источника данных (sourceRng), а другая - диапазон выходного массива (targetRng).

Код:

 Sub Trans_Ex3 () Dim sourceRng As Excel.Range Dim taretRng As Excel.Range End Sub

Обратите внимание, что тип этих переменных определен как (Excel.Range). Потому что мы хотели транспонировать данные, которые являются диапазоном массива.

Шаг 3: Установите диапазон источника как A1: B6 (данные, которые мы хотели взять для транспонирования), используя функцию Sheets.Range.

Код:

 Sub Trans_Ex3 () Dim sourceRng As Excel.Range Dim taretRng As Excel.Range Установить sourceRng = Sheets ("Пример # 3"). Диапазон ("A1: B6") End Sub

Шаг 4: Установите целевой / целевой диапазон как D1: I2 (диапазон ячеек, в котором будет сохранен вывод) с помощью функции Sheets.Range.

Код:

 Sub Trans_Ex3 () Dim sourceRng As Excel.Range Dim taretRng As Excel.Range Установить sourceRng = Sheets ("Пример # 3"). Range ("A1: B6") Установить targetRng = Sheets ("Пример # 3"). Range ( "D1: I2") End Sub

Шаг 5: Теперь используйте команду Копировать, чтобы скопировать диапазон исходных данных с вашего рабочего листа.

Код:

 Sub Trans_Ex3 () Dim sourceRng As Excel.Range Dim taretRng As Excel.Range Установить sourceRng = Sheets ("Пример # 3"). Range ("A1: B6") Установить targetRng = Sheets ("Пример # 3"). Range ( "D1: I2") sourceRng.Copy End Sub

Шаг 6: Мы собираемся использовать функцию PasteSpecial для переменной targetRng, чтобы сохранить транспонированный вывод в целевом диапазоне (D1: I2) на вашем рабочем листе.

Код:

 Sub Trans_Ex3 () Dim sourceRng As Excel.Range Dim taretRng As Excel.Range Установить sourceRng = Sheets ("Пример # 3"). Range ("A1: B6") Установить targetRng = Sheets ("Пример # 3"). Range ( «D1: I2») sourceRng.Copy targetRng.PasteSpecial Paste: = xlPasteValues, операция: = xlNone, SkipBlanks: = False, Transpose: = True End Sub
  • Вставить : позволяет вставлять значения в другом формате (например, Вставить как значения, как формулы, Как формат и т. Д.). Это похоже на Excel (мы делаем Alt + E + S, чтобы использовать разные специальные опции вставки). В нашем примере мы установили его для вставки в качестве значений.
  • Операция : Существуют различные операции, которые могут быть выполнены как сложение, вычитание, умножение, деление (так же, как мы можем сделать в Excel).
  • SkipBlanks : если установлено значение True, эта команда позволяет пропускать пропуски, если таковые имеются, из ваших данных при выполнении различных специальных операций вставки. Мы установили его в False, что означает, что мы хотели не пропускать пропуски.
  • Транспонирование : если установлено значение True, оно позволяет выполнять транспонирование массива данных.

Шаг 7: Запустите этот код, нажав F5 или кнопку Run, и посмотрите результат.

То, что нужно запомнить

  • При работе с одномерным массивом он всегда должен быть горизонтальным (одна строка, несколько столбцов), чтобы применять метод транспонирования.
  • Обязательно узнать количество строк и количество столбцов при использовании VBA Transpose в Excel. Если у нас есть 3 строки и 5 столбцов, после транспонирования будет 5 строк с тремя столбцами.
  • Обычно метод Transpose не включает форматирование исходных данных. Если вы хотите применить тот же формат, что и формат исходных данных, вы должны либо установить его вручную, либо с помощью специальной опции вставки XlPasteFormat, вы сможете сохранить формат исходных данных для целевых данных.
  • Число элементов, которые может принимать функция транспонирования в массиве, не может превышать 5461.
  • Массив не может содержать какой-либо элемент / строку длиной более 255. Если он включен, он вызовет ошибки, такие как 13, Несоответствие типов, 5, Недопустимый вызов или аргумент процедуры, 1004, ошибка приложения или объекта .
  • Исходный массив не может содержать никакого нулевого значения (например, «Null», «# N / A»).

Рекомендуемые статьи

Это был путеводитель по VBA Transpose. Здесь мы обсудили, как использовать Excel VBA Transpose вместе с практическими примерами и загружаемым шаблоном Excel. Вы также можете просмотреть наши другие предлагаемые статьи -

  1. Руководство по функции строки VBA
  2. Excel TRANSPOSE Formula
  3. Изучите VBA Case в Excel
  4. Удалить (Удалить) пустые строки в Excel