Transpunere VBA - Cum se utilizează Excel VBA Transpose?

Cuprins:

Anonim

VBA Transpun

Fiind un utilizator Microsoft Excel, este posibil să fi folosit frecvent funcția de transpunere care permite schimbarea între rânduri și coloane pentru un tablou. Convertirea rândurilor în coloane și coloane în rânduri este ceea ce face o funcție de transpunere pentru dvs. în excel. Numărul de rânduri devine un număr de coloane și invers. Înseamnă, dacă aveți 2 rânduri și 3 coloane în tabloul dvs., după transpunere se va schimba într-un tablou cu 3 rânduri și 2 coloane. În acest tutorial, vom parcurge VBA Transpose, care vă va permite să automatizați metoda de transpunere pe care să o utilizați în Excel.

Sintaxa de transpunere în Excel VBA

VBA Transpose are următoarea sintaxă:

Unde,

Arg1 : Este un argument necesar care nu este altceva decât o serie de celule pe care am dorit să le transpunem (adică tablou).

Prima parte a sintaxei nu este altceva decât o expresie sub care poate fi utilizată funcția Transpose. De exemplu, Funcția de lucru.

Cum se utilizează Excel VBA Transpose?

Vom învăța cum să folosiți un Transpose cu câteva exemple în Excel VBA.

Puteți descărca acest șablon VBA Transpose Excel aici - VBA Transpose Excel Model

Exemplul # 1 - Transpunerea VBA a unui tablou unidimensional

Să presupunem că lucrați la date cu liste (care este un tablou unidimensional) precum numele unui angajat dat („Lalit”, „Sneha”, „Etil”, „John”, „Cory”) și doriți această listă pentru a fi lipit în celule excel. Să vedem cum putem face acest lucru.

Urmați pașii de mai jos pentru a utiliza Transpunere în VBA.

Pasul 1: Introduceți un nou modul și definiți o nouă sub-procedură pentru a crea o macrocomandă în VBA.

Cod:

 Sub Trans_ex1 () End Sub

Pasul 2: Definiți o nouă variabilă care vă poate reține tableta unidimensională.

Cod:

 Sub Trans_ex1 () Dim Arr1 Ca variantă finală Sub

Pasul 3: Definiți lista ca un tablou folosind funcția Array. Aceasta ar fi lista pe care doriți să o inserați în foaia dvs. excel.

Cod:

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

Pasul 4: Utilizați acum metoda Range.Value pentru a defini intervalul în care aceste valori pe care doriți să le lipiți.

Cod:

 Sub Trans_ex1 () Dim Arr1 Ca variantă Arr1 = Array ("Lalit", "Sneha", "Ethyl", "John", "Cory") Interval ("A1: A5"). Value = End Sub

Pasul 5: Folosiți Application.WorksheetFunction.Transpose pe tabloul dat pentru a putea transpune lista definită sub variabila Arr1.

Cod:

 Sub Trans_ex1 () Dim Arr1 Ca variantă Arr1 = Array ("Lalit", "Sneha", "Ethyl", "John", "Cory") Interval ("A1: A5"). Value = Application.WorksheetFunction.Transpose (Arr1 ) End Sub

Aici, am definit un cod care va permite sistemului să transpună datele dintr-un tablou numit Arr1 și apoi să le stocheze în celula A1: A5 pe foaia de lucru activă.

Pasul 6: Apăsați butonul F5 sau Rulați sub VBE pentru a rula acest cod și a vedea ieșirea sub foaia de calcul activă Excel.

Puteți vedea că tabloul dat al listei este transpus într-o singură coloană și stocat sub celula A1 la A5.

Exemplul # 2 - Transpunerea VBA a tabloului bidimensional

Să presupunem că aveți o gamă bidimensională de angajați și salariile acestora, după cum urmează:

Aceasta este o informație bidimensională cu șase rânduri și două coloane. După transpunere, ar fi un tablou cu două rânduri și șase coloane.

Urmați pașii de mai jos pentru a utiliza Transpunere în VBA.

Pasul 1: Definiți o sub-procedură pentru a stoca o macrocomandă.

Cod:

 Sub Trans_Ex2 () End Sub

Pasul 2: Decideți intervalul în care doriți să transpuneți aceste date. Se poate face folosind funcția Sheets.Range.Value. Aș fi salvat tabloul transpus în celula D1 la I2.

Cod:

 Fișe Sub Trans_Ex2 () ("Exemplu # 2"). Gama ("D1: I2"). Valoare = Sub

Pasul 3: Utilizați WorksheetFunction.Transpose pentru a putea atribui o matrice A1: B6 pentru a transpune funcția.

Cod:

 Sub Trans_Ex2 () Fișe ("Exemplu # 2"). Gama ("D1: I2"). Valoare = WorksheetFunction.Transpose (End Sub

Pasul 4: Trebuie să specificăm argumentul pentru funcția Transpunere. Am vrut să transpunem o gamă de tablouri A1: B6. Așadar, folosiți ca rază („A1: B6”) ca argument.

Cod:

 Fișe Sub Trans_Ex2 () ("Exemplu # 2"). Gama ("D1: I2"). Valoare = WorksheetFunction.Transpose (Range ("A1: B6"))

Pasul 5: Apăsați butonul F5 sau Run pentru a rula acest cod și a vedea rezultatul.

Aici, gama gamei A1: B6 este transpusă și stocată într-o gamă a tabloului D1: I2 cu ajutorul funcției VBA Transpose în excel.

Exemplul # 3 - VBA Transpunerea unui tablou cu Pasta Special Method

De asemenea, putem transpune tabloul și lipiți la fel de speciale, precum și în Excel folosind Alt + E + S. Putem utiliza diferite operațiuni sub această metodă specială de paste.

Să luăm în considerare aceleași date pe care le-am folosit în exemplul precedent.

Urmați pașii de mai jos pentru a utiliza Transpunere în VBA.

Pasul 1: Definiți o sub-procedură pentru a stoca macro-ul.

Cod:

 Sub Trans_Ex3 () End Sub

Pasul 2: Definiți două variabile noi, una care poate reține tabloul sursă de date (sourceRng) și altele care pot păstra intervalul de matrice de ieșire (targetRng).

Cod:

 Sub Trans_Ex3 () Dim sursaRng Ca Excel.Range Dim taretRng Ca Excel.Range Sub Sub

Vă rugăm să rețineți că tipul acestor variabile definit ca (Excel.Range). Pentru că am vrut să transpunem datele care sunt un interval de matrice.

Pasul 3: Setați intervalul sursă ca A1: B6 (datele pe care am dorit să le transpunem) folosind funcția Sheets.Range.

Cod:

 Sub Trans_Ex3 () Dim sourceRng Ca Excel.Range Dim taretRng Ca Excel.Range Set sourceRng = Fișe ("Exemplul # 3"). Range ("A1: B6") End Sub

Pasul 4: Setați intervalul țintă / destinație ca D1: I2 (gama de celule în care va fi stocată ieșirea) folosind funcția Sheets.Range.

Cod:

 Sub Trans_Ex3 () Dim sourceRng Ca Excel.Range Dim taretRng Ca Excel.Range Set sourceRng = Fișe ("Exemplu # 3"). Range ("A1: B6") Set targetRng = Sheets ("Exemplu # 3"). Range ( "D1: I2") End Sub

Pasul 5: Acum, utilizați comanda Copiere pentru a copia intervalul de date sursă din foaia de lucru.

Cod:

 Sub Trans_Ex3 () Dim sourceRng Ca Excel.Range Dim taretRng Ca Excel.Range Set sourceRng = Fișe ("Exemplu # 3"). Range ("A1: B6") Set targetRng = Sheets ("Exemplu # 3"). Range ( "D1: I2") sourceRng.Copy End Sub

Pasul 6: Vom folosi funcția PasteSpecial pe targetRng variabilă pentru a salva ieșirea transpusă în intervalul țintă (D1: I2) din foaia de lucru.

Cod:

 Sub Trans_Ex3 () Dim sourceRng Ca Excel.Range Dim taretRng Ca Excel.Range Set sourceRng = Fișe ("Exemplu # 3"). Range ("A1: B6") Set targetRng = Sheets ("Exemplu # 3"). Range ( "D1: I2") sourceRng.Copy targetRng.PasteSpecial Paste: = xlPasteValues, Operație: = xlNone, SkipBlanks: = False, Transpose: = True End Sub
  • Paste : Permite lipirea valorilor într-un format diferit (cum ar fi Lipire ca valori, ca formule, format ca etc.). Este similar cu cel al Excel (facem Alt + E + S pentru a avea diferite opțiuni speciale de paste). În exemplul nostru, l-am setat pentru a lipi ca valori.
  • Operație : Există diferite operații care pot fi efectuate ca adunare, scădere, înmulțire, divizare (în același mod putem face în Excel).
  • SkipBlanks : Dacă este setat True, această comandă permite să omiteți semifabricatele din datele dvs. în timp ce efectuați diferite operații speciale de paste. Am setat-o ​​pe Fals, înseamnă că am vrut să nu omitem golurile.
  • Transpunere : Dacă este setat True, permite luarea transpunerii unui set de date.

Pasul 7: Rulați acest cod apăsând butonul F5 sau Run și vedeți ieșirea.

Lucruri de amintit

  • În timp ce lucrați pe un tablou unidimensional, acesta ar trebui să fie întotdeauna orizontal (Un rând, mai multe coloane) pentru a aplica metoda de transpunere.
  • Este obligatoriu să aflați numărul de rânduri și numărul de coloane în timp ce utilizați VBA Transpose în Excel. Dacă avem 3 rânduri și 5 coloane, după transpunere ar exista 5 rânduri cu trei coloane.
  • În mod normal, metoda Transpose nu include formatarea datelor sursă. Dacă doriți să aplicați același format cu cel al datelor sursă, fie trebuie să îl setați manual, fie utilizând opțiunea de lipire specială XlPasteFormat, veți putea menține formatul datelor sursă la datele țintă.
  • Numărul de elemente care pot fi utilizate într-un tablou nu poate depăși 5461.
  • Un tablou nu poate conține niciun element / șir având o lungime mai mare de 255. Dacă este inclus, acesta va cauza erori precum 13, nepotrivire tip, 5, apel sau argument de procedură nevalid, 1004, aplicație definită sau eroare definită obiect .
  • Matricea sursă nu poate conține nicio valoare nulă (cum ar fi „Nul”, „# N / A”).

Articole recomandate

Acesta a fost un ghid pentru VBA Transpose. Aici am discutat despre cum se utilizează Excel VBA Transpose împreună cu exemple practice și șablon Excel descărcabil. De asemenea, puteți parcurge și alte articole sugerate -

  1. Ghid pentru funcția de șiruri VBA
  2. Formula Excel TRANSPOSE
  3. Aflați cazul VBA în Excel
  4. Eliminați (ștergeți) rândurile goale din Excel