Transpozícia VBA

Ako používateľ programu Microsoft Excel ste často používali funkciu transpozície, ktorá umožňuje prepínanie medzi riadkami a stĺpcami pre pole. Prevedenie riadkov do stĺpcov a stĺpcov do riadkov je to, čo pre vás vyniká funkcia transpozície. Počet riadkov sa stáva počtom stĺpcov a naopak. Znamená to, že ak máte vo svojom poli 2 riadky a 3 stĺpce, po transpozícii sa zmení na pole s 3 riadkami a 2 stĺpcami. V tomto tutoriále prejdeme VBA transpozíciu, ktorá vám umožní automatizovať metódu transpozície, ktorú chcete použiť v Exceli.

Syntax transponovania v aplikácii Excel VBA

Transpozícia VBA má nasledujúcu syntax:

Kde,

Arg1 : Je to povinný argument, ktorý nie je ničím iným ako rozsahom buniek, ktoré sme chceli transponovať (tj pole).

Prvá časť syntaxe nie je nič iné ako výraz, podľa ktorého možno použiť funkciu transponovania. Napríklad WorksheetFunction.

Ako používať Excel VBA Transpozícia?

Naučíme sa, ako používať transpozíciu s niekoľkými príkladmi v programe Excel VBA.

Túto šablónu VBA transponovaného Excelu si môžete stiahnuť tu - šablónu VBA transponovať Excel

Príklad č. 1 - VBA transpozícia jednorozmerného poľa

Predpokladajme, že pracujete na údajoch so zoznamami (čo je jednorozmerné pole), ako sú mená daných zamestnancov („Lalit“, „Sneha“, „Ethyl“, „John“, „Cory“) a chcete tento zoznam byť vložený do vynikajúcich buniek. Pozrime sa, ako to môžeme urobiť.

Ak chcete použiť transpozíciu vo VBA, postupujte podľa nasledujúcich krokov.

Krok 1: Vložte nový modul a definujte nový podprogram na vytvorenie makra vo VBA.

kód:

 Sub Trans_ex1 () End Sub

Krok 2: Definujte novú premennú, ktorá môže obsahovať vaše jednorozmerné pole.

kód:

 Sub Trans_ex1 () Dim Arr1 ako koniec variantu Sub

Krok 3: Definujte zoznam ako pole pomocou funkcie Array. Toto by bol zoznam, ktorý ste chceli vložiť do svojho hárka programu Excel.

kód:

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

Krok 4: Teraz použite metódu Range.Value na definovanie rozsahu, do ktorého sa majú vložiť tieto hodnoty.

kód:

 Sub Trans_ex1 () Dim Arr1 As Variant Arr1 = Array ("Lalit", "Sneha", "Ethyl", "John", "Cory") Range ("A1: A5"). Hodnota = End Sub

Krok 5: Použite Application.WorksheetFunction.Transpose na danom poli, aby ste mohli transponovať zoznam definovaný v premennej Arr1.

kód:

 Sub Trans_ex1 () Dim Arr1 As Variant Arr1 = Array ("Lalit", "Sneha", "Ethyl", "John", "Cory") Range ("A1: A5"). Hodnota = Application.WorksheetFunction.Transpose (Arr1 ) Koniec pod

Tu sme definovali kód, ktorý umožní systému transponovať údaje do poľa zoznamu s názvom Arr1 a potom ich uložiť do bunky A1: A5 na aktívnom pracovnom hárku.

Krok 6: Stlačením klávesy F5 alebo Run pod VBE spustite tento kód a pozrite si výstup pod aktívnou tabuľkou programu Excel.

Môžete vidieť, že dané pole zoznamu je transponované do jedného stĺpca a uložené pod bunkou A1 až A5.

Príklad č. 2 - VBA Transpozícia dvojrozmerného poľa

Predpokladajme, že máte dvojrozmerný rad zamestnancov a ich platy, ako je uvedené nižšie:

Toto sú dvojrozmerné údaje so šiestimi riadkami a dvoma stĺpcami. Po transponovaní by to bolo pole s dvoma riadkami a šiestimi stĺpcami.

Ak chcete použiť transpozíciu vo VBA, postupujte podľa nasledujúcich krokov.

Krok 1: Definujte čiastkový postup na uloženie makra.

kód:

 Sub Trans_Ex2 () End Sub

Krok 2: Vyberte rozsah, v ktorom chcete tieto údaje transponovať. Môže sa to vykonať pomocou funkcie Sheets.Range.Value. Uložil by som transponované pole do bunky D1 až I2.

kód:

 Sub-Trans_Ex2 () listy ("Príklad č. 2"). Rozsah ("D1: I2"). Hodnota = End Sub

Krok 3: Pomocou WorksheetFunction.Transpose môžete priradiť pole A1: B6 na transponovanie funkcie.

kód:

 Sub-listy Trans_Ex2 () ("Príklad č. 2"). Rozsah ("D1: I2"). Hodnota = WorksheetFunction.Transpose (End Sub

Krok 4: Musíme špecifikovať argument pre funkciu transponovania. Chceli sme transponovať celý rad polí A1: B6. Ako argument použite rozsah („A1: B6“).

kód:

 Podskupiny Trans_Ex2 () ("Príklad č. 2"). Rozsah ("D1: I2"). Hodnota = Pracovný list.Funkcia .ranspozícia (Rozsah ("A1: B6")) Koniec Sub

Krok 5: Stlačením F5 alebo Run spustite tento kód a uvidíte výstup.

Tu je rozsah poľa A1: B6 transponovaný a uložený do rozsahu poľa D1: I2 pomocou funkcie VBA Transpose excel.

Príklad č. 3 - Transpozícia poľa VBA pomocou špeciálnej metódy vloženia

Môžeme tiež transponovať pole a vložiť tak špeciálne ako v Exceli pomocou Alt + E + S. V rámci tejto špeciálnej metódy vloženia môžeme použiť rôzne operácie.

Zoberme si rovnaké údaje, aké sme použili v predchádzajúcom príklade.

Ak chcete použiť transpozíciu vo VBA, postupujte podľa nasledujúcich krokov.

Krok 1: Definujte podprogram na uloženie makra.

kód:

 Sub Trans_Ex3 () End Sub

Krok 2: Definujte dve nové premenné, jednu, ktorá môže uchovávať pole zdroja údajov (sourceRng) a ďalšie, ktoré môžu uchovávať rozsah výstupného poľa (targetRng).

kód:

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

Upozorňujeme, že typ týchto premenných je definovaný ako (Excel.Range). Pretože sme chceli transponovať údaje, ktoré sú rozsahom polí.

Krok 3: Pomocou funkcie Sheets.Range nastavte rozsah zdroja ako A1: B6 (údaje, ktoré sme chceli transponovať).

kód:

 Sub Trans_Ex3 () Dim sourceRng As Excel.Range Dim taretRng As Excel.Range Set sourceRng = Sheets ("Example # 3"). Range ("A1: B6") End Sub

Krok 4: Pomocou funkcie Sheets.Range nastavte cieľový / cieľový rozsah ako D1: I2 (rozsah buniek, v ktorých bude výstup uložený).

kód:

 Sub Trans_Ex3 () Dim sourceRng As Excel.Range Dim taretRng As Excel.Range Set sourceRng = Sheets ("Example # 3"). Range ("A1: B6") Set targetRng = Sheets ("Example # 3"). Range ( "D1: I2") Koniec sub

Krok 5: Teraz pomocou príkazu Kopírovať skopírujte rozsah zdrojových údajov z vášho pracovného hárka.

kód:

 Sub Trans_Ex3 () Dim sourceRng As Excel.Range Dim taretRng As Excel.Range Set sourceRng = Sheets ("Example # 3"). Range ("A1: B6") Set targetRng = Sheets ("Example # 3"). Range ( "D1: I2") sourceRng.Copy End Sub

Krok 6: Použijeme funkciu PasteSpecial na premennú targetRng na uloženie transponovaného výstupu do cieľového rozsahu (D1: I2) vo vašom pracovnom hárku.

kód:

 Sub Trans_Ex3 () Dim sourceRng As Excel.Range Dim taretRng As Excel.Range Set sourceRng = Sheets ("Example # 3"). Range ("A1: B6") Set targetRng = Sheets ("Example # 3"). Range ( "D1: I2") sourceRng.Copy targetRng.PasteSpecial Paste: = xlPasteValues, Operation: = xlNone, SkipBlanks: = False, Transpose: = True End Sub

Prilepiť : Umožňuje vkladať hodnoty v inom formáte (napríklad Prilepiť ako hodnoty, Vzorce, Ako formát atď.). Je to podobné ako v Exceli (robíme Alt + E + S, aby sme mali rôzne špeciálne možnosti vloženia). V našom príklade sme ho nastavili na vloženie ako hodnoty.
Operácia : Existujú rôzne operácie, ktoré je možné vykonávať ako sčítanie, odčítanie, násobenie, delenie (rovnako ako v Exceli).
SkipBlanks : Ak je nastavená na True, tento príkaz umožňuje vynechať medzery, ak nejaké existujú, z vašich údajov pri vykonávaní rôznych špeciálnych operácií vloženia. Nastavili sme to na False, čo znamená, že sme nechceli preskočiť medzery.
Transpozícia : Ak je nastavená na true, umožňuje vykonať transpozíciu súboru údajov.

Krok 7: Spustite tento kód stlačením klávesy F5 alebo Run a pozrite si výstup.

Čo je potrebné pamätať

Pri práci na jednorozmernom poli by vždy malo byť horizontálne (jeden riadok, viac stĺpcov), aby sa mohla použiť metóda transpozície.
Pri použití transpozície VBA v programe Excel je potrebné zistiť počet riadkov a počet stĺpcov. Ak máme 3 riadky a 5 stĺpcov, po transpozícii by bolo 5 riadkov s tromi stĺpcami.
Metóda transponovania obvykle nezahŕňa formátovanie zdrojových údajov. Ak chcete použiť rovnaký formát ako zdrojové údaje, musíte ho nastaviť ručne alebo pomocou špeciálnej voľby vložiť XlPasteFormat, budete môcť držať formát zdrojových údajov k cieľovým údajom.
Počet prvkov, ktoré môže transponovať funkcia v poli, nesmie prekročiť 5461.
Pole nemôže obsahovať žiadnu položku / reťazec s dĺžkou viac ako 255. Ak je zahrnutá, spôsobí chyby, ako napríklad 13, nesúlad typu, 5, neplatné volanie procedúry alebo argument, 1004, chyba definovaná aplikáciou alebo objekt .
Zdrojové pole nemôže obsahovať nulovú hodnotu (napríklad „Null“, „# N / A“).

Odporúčané články

Toto bol návod na transpozíciu VBA. Tu sme diskutovali o tom, ako používať Excel VBA Transpose spolu s praktickými príkladmi a stiahnuteľnou šablónou Excel. Môžete si tiež prečítať naše ďalšie navrhované články -

Sprievodca funkciou String VBA
Excel TRANSPOSE vzorec
Naučte sa prípad VBA v Exceli
Odstrániť (odstrániť) prázdne riadky v programe Excel

Transpozícia VBA - Ako používať Excel VBA Transpozícia?

Obsah: