VBA Transpose - Sådan bruges Excel VBA-transponering?

Indholdsfortegnelse:

Anonim

VBA Transpose

Som Microsoft Excel-bruger har du måske ofte brugt transponeringsfunktion, der gør det muligt at skifte mellem rækker og kolonner for en matrix. Konvertering af rækker i kolonne og kolonner til rækker er hvad en transponeringsfunktion gør for dig i Excel. Antallet af rækker bliver et antal kolonner og vice versa. Betyder, hvis du har 2 rækker og 3 kolonner i dit array, vil det efter transponering skifte til et array med 3 rækker og 2 kolonner. I denne tutorial vil vi gennemgå VBA Transpose, som giver dig mulighed for at automatisere den transponeringsmetode, du skal bruge i Excel.

Syntaks for transponering i Excel VBA

VBA Transpose har følgende syntaks:

Hvor,

Arg1 : Det er et påkrævet argument, som ikke er andet end et område af celler, som vi ønskede at transponere (dvs. array).

Den første del af syntaxen er intet andet end et udtryk, under hvilket Transpose-funktion kan bruges. For eksempel WorksheetFunction.

Sådan bruges Excel VBA-transponering?

Vi lærer, hvordan man bruger et Transpose med få eksempler i Excel VBA.

Du kan downloade denne VBA Transpose Excel-skabelon her - VBA Transpose Excel Template

Eksempel nr. 1 - VBA-transponering af en-dimensionel array

Antag, at du arbejder på en data med lister (som er en en-dimensionel matrix) som navne på en medarbejder, der er givet ("Lalit", "Sneha", "Ethyl", "John", "Cory"), og du vil have denne liste at blive indsat i excel cellevis. Lad os se, hvordan vi kan gøre dette.

Følg nedenstående trin for at bruge Transpose i VBA.

Trin 1: Indsæt et nyt modul og definer en ny underprocedure for at oprette en makro i VBA.

Kode:

 Sub Trans_ex1 () End Sub

Trin 2: Definer en ny variabel, der kan indeholde din endimensionelle matrix.

Kode:

 Sub Trans_ex1 () Dim Arr1 Som Variant End Sub

Trin 3: Definer listen som en matrix ved hjælp af Array-funktionen. Dette ville være den liste, du vil indsætte i dit excelark.

Kode:

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

Trin 4: Brug nu metoden Range.Value til at definere det interval, hvor disse værdier, du vil indsætte.

Kode:

 Sub Trans_ex1 () Dim Arr1 Som variant Arr1 = Array ("Lalit", "Sneha", "Ethyl", "John", "Cory") Range ("A1: A5"). Værdi = End Sub

Trin 5: Brug Application.WorksheetFunction.Transpose på den givne matrix for at kunne transponere listen defineret under variabel Arr1.

Kode:

 Sub Trans_ex1 () Dim Arr1 Som variant Arr1 = Array ("Lalit", "Sneha", "Ethyl", "John", "Cory") Område ("A1: A5"). Værdi = Anvendelse. ArbejdsarkFunktion.Transpose (Arr1 ) Afslut under

Her har vi defineret en kode, der giver systemet mulighed for at transponere dataene i listearray kaldet Arr1 og derefter gemme dem under celle A1: A5 på det aktive regneark.

Trin 6: Tryk på F5 eller Run-knappen under VBE for at køre denne kode og se output under aktivt Excel-regneark.

Du kan se, at det givne array på listen transponeres i en enkelt kolonne og gemmes under celle A1 til A5.

Eksempel # 2 - VBA-transponering af to-dimensionel array

Antag, at du har en to-dimensionel række medarbejdere og deres lønninger som nedenfor:

Dette er en todimensionel data med seks rækker og to kolonner. Efter transponering ville det være en matrix med to rækker og seks kolonner.

Følg nedenstående trin for at bruge Transpose i VBA.

Trin 1: Definer en underprocedure til lagring af en makro.

Kode:

 Sub Trans_Ex2 () End Sub

Trin 2: Bestem det interval, hvor du vil transponere disse data. Det kan gøres ved hjælp af funktionen Sheets.Range.Value. Jeg ville gemme det transponerede array i celle D1 til I2.

Kode:

 Sub Trans_Ex2 () Sheets ("Eksempel # 2"). Område ("D1: I2"). Værdi = End Sub

Trin 3: Brug WorksheetFunction.Transpose for at kunne tildele en matrix A1: B6 til at transponere funktion.

Kode:

 Sub Trans_Ex2 () Sheets ("Eksempel # 2"). Område ("D1: I2"). Værdi = ArbejdsarkFunktion.Transpose (slut sub

Trin 4: Vi skal specificere argumentet for Transpose-funktion. Vi ønskede at transponere en række matrix A1: B6. Brug derfor Range (“A1: B6”) som et argument.

Kode:

 Sub Trans_Ex2 () Sheets ("Eksempel # 2"). Område ("D1: I2"). Værdi = ArbejdsarkFunktion.Transpose (Range ("A1: B6")) End Sub

Trin 5: Tryk på F5 eller Run-knappen for at køre denne kode og se output.

Her transporteres række af array A1: B6 og gemmes i et interval af array D1: I2 ved hjælp af VBA Transpose-funktion i excel.

Eksempel 3 - VBA Transponering af en matrix med Indsæt specialmetode

Vi kan også transponere matrixen og indsætte så speciel som i Excel ved hjælp af Alt + E + S. Vi kan bruge forskellige operationer under denne specielle metode til indsætning.

Lad os overveje de samme data, som vi har brugt i det foregående eksempel.

Følg nedenstående trin for at bruge Transpose i VBA.

Trin 1: Definer en underprocedure til lagring af makroen.

Kode:

 Sub Trans_Ex3 () End Sub

Trin 2: Definer to nye variabler, en, der kan indeholde datakildearrayet (sourceRng), og andre, der kan rumme outputarray-området (targetRng).

Kode:

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

Bemærk, at typen af ​​disse variabler defineret som (Excel.Range). Fordi vi ønskede at transponere dataene, som er et array-interval.

Trin 3: Indstil kildeafstanden som A1: B6 (de data, vi ønskede at tage transponering af) ved hjælp af Sheets.Range-funktion.

Kode:

 Sub Trans_Ex3 () Dim sourceRng Som Excel.Range Dim taretRng Som Excel.Range Indstil sourceRng = Sheets ("Eksempel # 3"). Område ("A1: B6") End Sub

Trin 4: Indstil mål- / destinationsområdet som D1: I2 (Cellenes område, hvor output bliver lagret) vha. Funktionen Sheets.Range.

Kode:

 Sub Trans_Ex3 () Dim sourceRng Som Excel.Range Dim taretRng Som Excel.Range Sæt kildeRng = Ark ("Eksempel # 3"). Område ("A1: B6") Sæt targetRng = Ark ("Eksempel # 3"). Område ( "D1: I2") Sluttund

Trin 5: Brug nu kommandoen Kopier til at kopiere kildedataområdet fra dit regneark.

Kode:

 Sub Trans_Ex3 () Dim sourceRng Som Excel.Range Dim taretRng Som Excel.Range Sæt kildeRng = Ark ("Eksempel # 3"). Område ("A1: B6") Sæt targetRng = Ark ("Eksempel # 3"). Område ( "D1: I2") sourceRng.Copy End Sub

Trin 6: Vi vil bruge funktionen PasteSpecial på variabel targetRng til at gemme det transponerede output under målområdet (D1: I2) i dit regneark.

Kode:

 Sub Trans_Ex3 () Dim sourceRng Som Excel.Range Dim taretRng Som Excel.Range Sæt kildeRng = Ark ("Eksempel # 3"). Område ("A1: B6") Sæt targetRng = Ark ("Eksempel # 3"). Område ( "D1: I2") sourceRng.Copy targetRng.PasteSpecial Paste: = xlPasteValues, Operation: = xlNone, SkipBlanks: = False, Transpose: = True End Sub
  • Indsæt : Gør det muligt at indsætte værdier i et andet format (som Indsæt som værdier, som formler, som format osv.). Det svarer til Excel (vi har Alt + E + S for at have forskellige indstillinger for indsæt særlige indstillinger). I vores eksempel har vi indstillet det til at indsætte som værdier.
  • Betjening : Der er forskellige operationer, der kan udføres som tilføjelse, subtraktion, multiplikation, opdeling (på samme måde som vi kan gøre i Excel).
  • SkipBlanks : Hvis dette er indstillet til sandt, tillader denne kommando at springe eventuelle emner fra dine data, mens du udfører forskellige indsæt specielle operationer. Vi satte den til False, betyder, at vi ikke ønskede at springe springerne over.
  • Transpose : Hvis det er indstillet til True, tillader det at transponere en række data.

Trin 7: Kør denne kode ved at trykke på F5 eller Run-knappen og se output.

Ting at huske

  • Når du arbejder på en endimensionel matrix, skal den altid være vandret (En række, flere kolonner) for at anvende transponeringsmetoden.
  • Det er obligatorisk at finde ud af antallet af rækker og antal kolonner, mens du bruger VBA Transpose i Excel. Hvis vi har 3 rækker og 5 kolonner, ville der efter transponering være 5 rækker med tre kolonner.
  • Normalt inkluderer Transpose-metoden ikke formatering af kildedataene. Hvis du vil anvende det samme format som kildedata, skal du enten manuelt indstille det eller bruge en speciel indstilling XlPasteFormat, så du kan holde formatet på kildedataene i måldataene.
  • Antallet af elementer Transpose-funktionen kan tage i en matrix kan ikke overstige 5461.
  • En matrix kan ikke indeholde noget objekt / streng med længde over 255. Hvis det er inkluderet, vil det forårsage fejl såsom 13, Type Mismatch, 5, Ugyldig procedureopkald eller argument, 1004, applikationsdefineret eller objektdefineret fejl .
  • Kildearrayet kan ikke indeholde nogen nulværdi (f.eks. “Null”, “# N / A”).

Anbefalede artikler

Dette har været en guide til VBA Transpose. Her diskuterede vi, hvordan man bruger Excel VBA Transpose sammen med praktiske eksempler og downloadbar excel-skabelon. Du kan også gennemgå vores andre foreslåede artikler -

  1. Vejledning til VBA-strengfunktion
  2. Excel TRANSPOSE-formel
  3. Lær VBA-sag i Excel
  4. Fjern (Slet) tomme rækker i Excel