Transponace VBA - Jak používat Excel VBA Transpose?

Obsah:

Anonim

VBA Transpozice

Jako uživatel aplikace Microsoft Excel můžete často používat funkci transpozice, která umožňuje přepínání mezi řádky a sloupci pro pole. Převod řádků do sloupců a sloupců do řádků je to, co pro vás ve vynikajícím provedení transponuje funkce. Počet řádků se stává počtem sloupců a naopak. To znamená, že pokud máte ve svém poli 2 řádky a 3 sloupce, po transpozici se změní na pole se 3 řádky a 2 sloupci. V tomto tutoriálu projdeme VBA Transpose, která vám umožní automatizovat metodu transpozice, kterou používáte v Excelu.

Syntaxe transpozice v Excelu VBA

VBA Transpose má následující syntaxi:

Kde,

Arg1 : Je to povinný argument, který není ničím jiným než řadou buněk, které jsme chtěli transponovat (tj. Maticí).

První část syntaxe není nic jiného než výraz, pod kterým lze použít funkci transponování. Například WorksheetFunction.

Jak používat Excel VBA Transpose?

Naučíme se, jak používat transplantaci s několika příklady v Excelu VBA.

Tuto šablonu VBA Transpose Excel si můžete stáhnout zde - VBA Transpose Excel Template

Příklad č. 1 - VBA transpozice jednorozměrného pole

Předpokládejme, že pracujete na datech se seznamy (což je jednorozměrné pole), jako jsou jména daných zaměstnanců („Lalit“, „Sneha“, „Ethyl“, „John“, „Cory“) a chcete tento seznam být vložen do excelentní buňky. Uvidíme, jak to dokážeme.

Chcete-li použít transpozici ve VBA, postupujte podle níže uvedených kroků.

Krok 1: Vložte nový modul a definujte nový dílčí postup pro vytvoření makra ve VBA.

Kód:

 Sub Trans_ex1 () End Sub

Krok 2: Definujte novou proměnnou, která pojme vaše jednorozměrné pole.

Kód:

 Sub Trans_ex1 () Dim Arr1 jako Konec varianty Sub

Krok 3: Definujte seznam jako pole pomocí funkce Array. To by byl seznam, který jste chtěli vložit do svého Excelu.

Kód:

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

Krok 4: Nyní použijte metodu Range.Value k definování rozsahu, ve kterém se mají tyto hodnoty vložit.

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žijte Application.WorksheetFunction.Transpose v daném poli, abyste mohli transponovat seznam definovaný pod proměnnou 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 ) End Sub

Zde jsme definovali kód, který umožní systému transponovat data do pole seznamu nazvaného Arr1 a poté je uložit do buňky A1: A5 na aktivním listu.

Krok 6: Stiskem tlačítka F5 nebo Run pod VBE spustíte tento kód a uvidíte výstup v aktivní Excel tabulce.

Můžete vidět, že dané pole seznamu je transponováno do jednoho sloupce a uloženo pod buňkou A1 až A5.

Příklad č. 2 - VBA Transpozice dvourozměrného pole

Předpokládejme, že máte dvojrozměrné pole zaměstnanců a jejich platy, jak je uvedeno níže:

Jedná se o dvourozměrná data se šesti řádky a dvěma sloupci. Po transpozici by to bylo pole se dvěma řádky a šesti sloupci.

Chcete-li použít transpozici ve VBA, postupujte podle níže uvedených kroků.

Krok 1: Definujte dílčí postup pro uložení makra.

Kód:

 Sub Trans_Ex2 () End Sub

Krok 2: Určete rozsah, do kterého chcete tato data transponovat. To lze provést pomocí funkce Sheets.Range.Value. Uložil bych transponované pole do buňky D1 až I2.

Kód:

 Sub Trans_Ex2 () Listy ("Příklad # 2"). Rozsah ("D1: I2"). Hodnota = End Sub

Krok 3: Pomocí WorksheetFunction.Transpose můžete přiřadit matici A1: B6 k transpozici funkce.

Kód:

 Sub Trans_Ex2 () Listy ("Příklad # 2"). Rozsah ("D1: I2"). Hodnota = WorksheetFunction.Transpose (End Sub

Krok 4: Musíme zadat argument pro funkci transponování. Chtěli jsme transponovat řadu polí A1: B6. Jako argument tedy použijte Range („A1: B6“).

Kód:

 Sub Trans_Ex2 () Listy ("Příklad č. 2"). Rozsah ("D1: I2"). Hodnota = WorksheetFunction.Transpose (Range ("A1: B6")) End Sub

Krok 5: Stisknutím klávesy F5 nebo Run spusťte tento kód a podívejte se na výstup.

Zde je rozsah pole A1: B6 transponován a uložen do rozsahu pole D1: I2 pomocí funkce VBA Transpose excel.

Příklad č. 3 - VBA Transpozice pole pomocí speciální metody Vložit

Můžeme také transponovat pole a vložit stejně speciální jako v Excelu pomocí Alt + E + S. V rámci této speciální metody vložení můžeme použít různé operace.

Uvažujme stejná data, jaká jsme použili v předchozím příkladu.

Chcete-li použít transpozici ve VBA, postupujte podle níže uvedených kroků.

Krok 1: Definujte dílčí postup pro uložení makra.

Kód:

 Sub Trans_Ex3 () End Sub

Krok 2: Definujte dvě nové proměnné, jednu, která může obsahovat pole zdroje dat (sourceRng) a další, které mohou obsahovat rozsah výstupního pole (targetRng).

Kód:

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

Upozorňujeme, že typ těchto proměnných je definován jako (Excel.Range). Protože jsme chtěli transponovat data, která jsou rozsahem polí.

Krok 3: Pomocí funkce Sheets.Range nastavte zdrojový rozsah jako A1: B6 (data, která jsme chtěli převzít).

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: Pomocí funkce Sheets.Range nastavte cílový / cílový rozsah jako D1: I2 (rozsah buněk, ve kterém 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") End Sub

Krok 5: Nyní pomocí příkazu Kopírovat zkopírujte rozsah zdrojových dat z vašeho listu.

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 funkci PasteSpecial na proměnné targetRng k uložení transponovaného výstupu do cílového rozsahu (D1: I2) ve vašem pracovním listu.

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
  • Vložit : Umožňuje vložit hodnoty v jiném formátu (například Vložit jako hodnoty, Vzorce, Jako formát atd.). Je to podobné jako u Excelu (pro Alt + E + S máme různé speciální možnosti vložení). V našem příkladu jsme nastavili vložení jako hodnoty.
  • Operace : Existují různé operace, které lze provádět jako sčítání, odčítání, násobení, dělení (stejným způsobem jako v Excelu).
  • SkipBlanks : Je-li nastaveno na True, tento příkaz umožňuje přeskočit mezery, pokud existují, z vašich dat při provádění různých speciálních operací vložení. Nastavili jsme to na False, což znamená, že jsme nechtěli přeskočit mezery.
  • Transpozice : Pokud je nastavena na hodnotu True, umožňuje převzít transpozici pole dat.

Krok 7: Spusťte tento kód stisknutím klávesy F5 nebo Run a podívejte se na výstup.

Co si pamatovat

  • Při práci na jednorozměrném poli by vždy mělo být vodorovné (jeden řádek, více sloupců), aby se mohla použít metoda transpozice.
  • Je nutné zjistit počet řádků a počet sloupců při použití VBA Transpose v Excelu. Pokud máme 3 řádky a 5 sloupců, bude po transpozici 5 řádků se třemi sloupci.
  • Metoda Transpose obvykle nezahrnuje formátování zdrojových dat. Pokud chcete použít stejný formát jako u zdrojových dat, musíte jej nastavit ručně nebo pomocí speciální volby vložit XlPasteFormat, budete moci držet formát zdrojových dat na cílová data.
  • Počet prvků, které může transponovat funkce v poli, nesmí přesáhnout 5461.
  • Pole nemůže obsahovat žádnou položku / řetězec, který má délku větší než 255. Pokud je zahrnuta, způsobí chyby, jako například 13, neshoda typu, 5, neplatné volání procedury nebo argument, 1004, chyba definovaná aplikací nebo objekt .
  • Zdrojové pole nemůže obsahovat nulovou hodnotu (například „Null“, „# N / A“).

Doporučené články

Toto byl průvodce VBA Transpose. Zde jsme diskutovali o tom, jak používat Excel VBA Transpose spolu s praktickými příklady a stahovatelnou šablonou Excel. Můžete si také prohlédnout naše další doporučené články -

  1. Průvodce funkcí řetězce VBA
  2. Excel TRANSPOSE vzorec
  3. Naučte se případ VBA v Excelu
  4. Odebrat (odstranit) prázdné řádky v Excelu