VBA Transpose - Як використовувати Excel VBA Transpose?

Зміст:

Anonim

VBA Transpose

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

Синтаксис Transpose в Excel VBA

VBA Transpose має такий синтаксис:

Де,

Arg1 : це необхідний аргумент, який є не що інше, як коло комірок, які ми хотіли перенести (тобто масив).

Перша частина синтаксису - це не що інше, як вираз, під яким можна використовувати функцію Transpose. Наприклад, WorkheetFunction.

Як використовувати Excel VBA Transpose?

Ми дізнаємось, як використовувати Transpose з кількома прикладами в Excel VBA.

Ви можете завантажити цей шаблон VBA Transpose Excel тут - VBA Transpose Excel шаблон

Приклад №1 - Перенесення VBA одновимірного масиву

Припустимо, ви працюєте над даними зі списками (що представляє собою одновимірний масив), на зразок імен працівника ("Lalit", "Sneha", "Ethyl", "John", "Cory"), і вам потрібен цей список бути вклеєними у вікно Excel. Подивимося, як ми можемо це зробити.

Виконайте наведені нижче дії, щоб використовувати Transpose у VBA.

Крок 1: Вставте новий модуль і визначте нову під-процедуру для створення макросу в VBA.

Код:

 Sub Trans_ex1 () Кінець Sub

Крок 2: Визначте нову змінну, яка може вмістити ваш одномірний масив.

Код:

 Sub Trans_ex1 () Dim Arr1 як варіант завершення Sub

Крок 3: Визначте список як масив за допомогою функції Array. Це був би список, який ви хотіли вставити на свій аркуш excel.

Код:

 Sub Trans_ex1 () Dim Arr1 як варіант Arr1 = масив ("Lalit", "Sneha", "Ethyl", "John", "Cory") Кінець Sub

Крок 4: Тепер використовуйте метод Range.Value, щоб визначити діапазон, в який ці значення ви хотіли вставити.

Код:

 Sub Trans_ex1 () Dim Arr1 як варіант Arr1 = масив (діапазон "Lalit", "Sneha", "Ethyl", "John", "Cory") ("A1: A5"). Value = End Sub

Крок 5: Використовуйте Application.WorksheetFunction.Перекладіть на даний масив, щоб мати можливість перенести список, визначений в змінній Arr1.

Код:

 Sub Trans_ex1 () Dim Arr1 As Variant Arr1 = масив (діапазон "Lalit", "Sneha", "Ethyl", "John", "Cory") ("A1: A5"). Value = Application.WorksheetFunction.Transpose (Arr1 ) Закінчення Sub

Тут ми визначили код, який дозволить системі перенести дані у масив списку під назвою Arr1, а потім зберегти їх у комірці A1: A5 на активному робочому аркуші.

Крок 6: Натисніть кнопку F5 або Запустити під VBE, щоб запустити цей код і побачити результат під дією електронної таблиці Excel.

Ви можете бачити, що даний масив списку переміщується в один стовпчик і зберігається під коміркою від А1 до А5.

Приклад №2 - Перенесення VBA двовимірного масиву

Припустимо, у вас є двовимірний масив працівників та їх зарплати, як показано нижче:

Це двовимірні дані з шістьма рядками та двома стовпцями. Після перенесення це буде масив з двома рядками та шістьма стовпцями.

Виконайте наведені нижче дії, щоб використовувати Transpose у VBA.

Крок 1: Визначте підпроцедуру для зберігання макросу.

Код:

 Sub Trans_Ex2 () Кінець Sub

Крок 2: Визначте діапазон, куди потрібно перенести ці дані. Це можна зробити за допомогою функції Sheets.Range.Value. Я б економив перекладений масив у комірку D1 до I2.

Код:

 Sub Trans_Ex2 () Таблиці ("Приклад №2"). Діапазон ("D1: I2"). Значення = Кінець Sub

Крок 3: Використовуйте WorksheetFunction.Transpose, щоб мати можливість призначити масив A1: B6 для переміщення функції.

Код:

 Sub Trans_Ex2 () Таблиці ("Приклад №2"). Діапазон ("D1: I2"). Значення = WorksheetFunction.Transpose (Кінець підрозділу

Крок 4: нам потрібно вказати аргумент для функції транспонування. Ми хотіли перенести діапазон масиву A1: B6. Таким чином, використовуйте діапазон ("A1: B6") як аргумент.

Код:

 Sub Trans_Ex2 () Таблиці ("Приклад №2"). Діапазон ("D1: I2"). Значення = WorksheetFunction.Transpose (Діапазон ("A1: B6")) Кінець Sub

Крок 5: Натисніть кнопку F5 або кнопку «Запустити», щоб запустити цей код і побачити вихід.

Тут діапазон масиву A1: B6 переміщується і зберігається в діапазон масиву D1: I2 за допомогою функції VBA Transpose в excel.

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

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

Розглянемо ті самі дані, які ми використовували в попередньому прикладі.

Виконайте наведені нижче дії, щоб використовувати Transpose у VBA.

Крок 1. Визначте підпроцедуру для зберігання макросу.

Код:

 Sub Trans_Ex3 () Кінець Sub

Крок 2: Визначте дві нові змінні: одну, яка може містити масив джерел даних (sourceRng) та інші, які можуть містити діапазон вихідного масиву (targetRng).

Код:

 Sub Trans_Ex3 () Dim sourceRng Як ​​Excel.Range Dim taretRng як Excel.Range Кінець Sub

Зверніть увагу, що тип цих змінних визначений як (Excel.Range). Тому що ми хотіли перенести дані, які є діапазоном масиву.

Крок 3: Встановіть діапазон джерела як A1: B6 (дані, які ми хотіли перенести) за допомогою функції Sheets.Range.

Код:

 Sub Trans_Ex3 () Dim sourceRng як Excel.Range Dim taretRng як Excel.Range Встановити sourceRng = Таблиці ("Приклад №3"). Діапазон ("A1: B6") Кінець Sub

Крок 4: Встановіть цільовий / цільовий діапазон як D1: I2 (Діапазон комірок, де буде зберігатися вихід) за допомогою функції Sheets.Range.

Код:

 Sub Trans_Ex3 () Dim sourceRng як Excel.Range Dim taretRng як Excel.Range Встановити sourceRng = Таблиці ("Приклад №3"). Діапазон ("A1: B6") Встановити targetRng = Таблиці ("Приклад №3"). "D1: I2") Закінчення Sub

Крок 5: Тепер скопіюйте команду Copy, щоб скопіювати діапазон вихідних даних зі свого робочого аркуша.

Код:

 Sub Trans_Ex3 () Dim sourceRng як Excel.Range Dim taretRng як Excel.Range Встановити sourceRng = Таблиці ("Приклад №3"). Діапазон ("A1: B6") Встановити targetRng = Таблиці ("Приклад №3"). "D1: I2") sourceRng.Копіювати Закінчення Sub

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

Код:

 Sub Trans_Ex3 () Dim sourceRng як Excel.Range Dim taretRng як Excel.Range Встановити sourceRng = Таблиці ("Приклад №3"). Діапазон ("A1: B6") Встановити targetRng = Таблиці ("Приклад №3"). "D1: I2") sourceRng.Копіюйте targetRng.PasteSpecial Paste: = xlPasteValues, Операція: = xlNone, SkipBlanks: = False, Transpose: = True End Sub
  • Вставити : Дозволяє вставляти значення в інший формат (наприклад, Вставити як значення, як Формули, Як Формат тощо). Це аналогічно тому, що і у Excel (Ми робимо Alt + E + S, щоб мати різні спеціальні параметри вставки). У нашому прикладі ми встановили його для вставки як значення.
  • Операція : Є різні операції, які можна виконати, як додавання, віднімання, множення, ділення (так само, як і в Excel).
  • SkipBlanks : Якщо встановлено True, ця команда дозволяє пропускати пробіли, якщо такі є, з ваших даних, виконуючи різні спеціальні операції вставки. Ми встановили його на False, значить, ми хотіли не пропускати заготовки.
  • Транспонування : Якщо встановлено значення True, воно дозволяє здійснити перенесення масиву даних.

Крок 7: Запустіть цей код, натиснувши кнопку F5 або Запустити і подивіться вихід.

Що потрібно пам’ятати

  • Працюючи над одновимірним масивом, він завжди повинен бути горизонтальним (один рядок, кілька стовпців), щоб застосувати метод транспонування.
  • Обов’язково з’ясовується кількість рядків і кількість стовпців під час використання VBA Transpose в Excel. Якщо у нас є 3 ряди та 5 стовпців, після транспонування було б 5 рядків з трьома стовпцями.
  • Зазвичай метод Transpose не включає форматування вихідних даних. Якщо ви хочете застосувати той самий формат, що і вихідні дані, вам доведеться встановити його вручну, або за допомогою спеціальної опції XlPasteFormat вставити формат вихідних даних до цільових даних.
  • Кількість елементів, які функція транспонування може приймати в масиві, не може перевищувати 5461.
  • Масив не може містити жодного елемента / рядка довжиною більше 255. Якщо він включений, він спричинить помилки, такі як 13, невідповідність типу, 5, недійсний виклик або аргумент процедури, 1004, визначена додатком або визначена об'єктом помилка .
  • Масив джерела не може містити жодного нульового значення (наприклад, "Null", "# N / A").

Рекомендовані статті

Це було керівництвом щодо транспорту VBA. Тут ми обговорили, як використовувати Excel VBA Transpose разом із практичними прикладами та шаблоном Excel, який можна завантажити. Ви також можете ознайомитися з іншими запропонованими нами статтями -

  1. Посібник з функції рядків VBA
  2. Формула Excel TRANSPOSE
  3. Дізнайтеся про справу VBA в Excel
  4. Видаліть (видаліть) порожні рядки в Excel