VBA транспониране

Като потребител на 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“) и искате този списък да бъде залепена в excell cell pamet. Нека видим как можем да направим това.

Следвайте стъпките по-долу, за да използвате Transpose във VBA.

Стъпка 1: Поставете нов модул и дефинирайте нова под-процедура за създаване на макрос в VBA.

Код:

 Sub Trans_ex1 () Край Sub

Стъпка 2: Определете нова променлива, която може да побере вашия едномерен масив.

Код:

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

Стъпка 3: Определете списъка като масив, използвайки функцията Array. Това ще бъде списъкът, който искате да поставите във вашия лист за ексел.

Код:

 Sub Trans_ex1 () Dim Arr1 като вариант Arr1 = Array („Lalit“, „Sneha“, „Ethyl“, „John“, „Cory“) End 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 като вариант Arr1 = масив (диапазон „Lalit“, „Sneha“, „Ethyl“, „John“, „Cory“) („A1: A5“). Value = Application.WorksheetFunction.Transpose (Arr1 ) Край Sub

Тук сме дефинирали код, който ще позволи на системата да транспонира данните в списъка масив, наречен Arr1 и след това да го съхранява под клетка A1: A5 на активния работен лист.

Стъпка 6: Натиснете F5 или Run бутона под 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 (Край Sub

Стъпка 4: Трябва да посочим аргумента за функция Transpose. Искахме да транспонираме диапазон от масив A1: B6. По този начин, използвайте като аргумент Range ("A1: B6").

Код:

 Sub Trans_Ex2 () Таблици ("Пример №2"). Обхват ("D1: I2"). Стойност = WorksheetFunction.Transpose (обхват ("A1: B6")) Край Sub

Стъпка 5: Натиснете F5 или Run бутона, за да стартирате този код и да видите изхода.

Тук диапазонът от масив 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 End 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 = Sheets ("Пример # 3"). "D1: I2") Край Sub

Стъпка 5: Сега използвайте командата Copy, за да копирате обхвата на изходните данни от работния си лист.

Код:

 Sub Trans_Ex3 () Dim sourceRng като Excel.Range Dim taretRng като Excel.Range Задайте sourceRng = Таблици ("Пример # 3"). Обхват ("A1: B6") Задайте targetRng = Sheets ("Пример # 3"). "D1: I2") sourceRng.Copy End Sub

Стъпка 6: Ще използваме функцията PasteSpecial на променлива targetRng, за да запишем транспонирания изход под целевия диапазон (D1: I2) във вашия работен лист.

Код:

 Sub Trans_Ex3 () Dim sourceRng като Excel.Range Dim taretRng като Excel.Range Задайте sourceRng = Таблици ("Пример # 3"). Обхват ("A1: B6") Задайте targetRng = Sheets ("Пример # 3"). "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 реда с три колони.
Обикновено методът за транспониране не включва форматиране на изходните данни. Ако искате да приложите същия формат като този на изходните данни, трябва ръчно да го зададете или използвайки специална опция XlPasteFormat за поставяне, ще можете да задържите формата на изходните данни към целевите данни.
Броят на елементите, които функцията за транспониране може да поеме в масив, не може да надвишава 5461.
Масивът не може да съдържа нито един елемент / низ с дължина повече от 255. Ако е включен, той ще доведе до грешки като 13, Тип несъответствие, 5, Невалиден извикване на процедурата или аргумент, 1004, дефинирано от приложението или обект, дефинирана грешка .
Изходният масив не може да съдържа нулева стойност (като "Null", "# N / A").

Препоръчителни статии

Това е ръководство за VBA Transpose. Тук обсъдихме как да използвате Excel VBA Transpose заедно с практически примери и шаблон за Excel, който можете да изтеглите. Можете да разгледате и другите ни предложени статии -

Ръководство за функция на VBA String
Excel TRANSPOSE Формула
Научете VBA Case в Excel
Премахване (Изтриване) празни редове в Excel

VBA транспониране - Как да използвате Excel VBA Transpose?

Съдържание: