Přejít k hlavnímu obsahu

Jak transponovat každých 5 nebo n řádků z jednoho sloupce do více sloupců?

doc transponovat každých 5 řádků 1

Předpokládejme, že ve sloupci A máte dlouhá data, a nyní chcete transponovat každých 5 řádků ze sloupce A do více sloupců, například transponovat A1: A5 do C6: G6, A6: A10 až C7: G7 atd. Jako je zobrazen následující snímek obrazovky. Jak byste mohli zvládnout tento úkol bez opakovaného kopírování a vkládání v aplikaci Excel?

Transponujte každých 5 nebo n řádků z jednoho sloupce do více sloupců pomocí vzorce

Transponujte každých 5 nebo n řádků z jednoho sloupce do více sloupců pomocí kódu VBA

Transponujte každých 5 nebo n řádků z jednoho sloupce do více sloupců pomocí Kutools pro Excel


V aplikaci Excel můžete použít následující vzorec k převedení všech n řádků z jednoho sloupce do více sloupců, postupujte takto:

1. Zadejte následující vzorec do prázdné buňky, kam chcete vložit výsledek, například C1, = INDEX ($ A: $ A, ŘÁDEK (A1) * 5-5 + SLOUPEC (A1)), viz screenshot:

doc transponovat každých 5 řádků 2

Poznámka: Ve výše uvedeném vzorci, A: je odkaz na sloupec, který chcete provést, a A1 je první buňka použitého sloupce, číslo 5 označuje počet sloupců, které vaše data naleznou, můžete je podle potřeby změnit. A první buňka seznamu musí být umístěna na prvním řádku v listu.

2. Poté přetáhněte rukojeť výplně doprava na pět buněk a pokračujte v tažení rukojeti výplně dolů do rozsahu buněk, dokud se nezobrazí 0, viz screenshot:

doc transponovat každých 5 řádků 3


Transformujte každých 5 nebo n řádků z jednoho sloupce do více sloupců:

Chcete-li transformovat každých 5 nebo n řádků z jednoho sloupce do více sloupců, Kutools pro Excel's Rozsah transformace vám pomůže vyřešit tuto práci co nejrychleji. Také vám může pomoci transponovat rozsah dat do jednoho řádku nebo sloupce. Klikněte a stáhněte si Kutools pro Excel!

doc transponovat každých 5 řádků 10

Kutools pro Excel: s více než 300 praktickými doplňky aplikace Excel, můžete vyzkoušet bez omezení do 30 dnů. Stáhněte si a vyzkoušejte zdarma hned teď!


Pokud nemůžete vzorec správně použít, pomůže vám také následující kód VBA.

1. Podržte ALT + F11 klávesy pro otevření Microsoft Visual Basic pro aplikace okno.

2, klikněte Vložit > Modula vložte následující kód do Modul Okno.

Kód VBA: Transponujte každých 5 nebo n řádků z jednoho sloupce do více sloupců:

Public Sub TransposeData()
'updateby Extendoffice
    Dim xLRow As Long
    Dim xNRow As Long
    Dim i As Long
    Dim xUpdate As Boolean
    Dim xRg As Range
    Dim xOutRg As Range
    Dim xTxt As String
    On Error Resume Next
    xTxt = ActiveWindow.RangeSelection.Address
    Set xRg = Application.InputBox("Please select data range(only one column):", "Kutools for Excel", xTxt, , , , , 8)
    Set xRg = Application.Intersect(xRg, xRg.Worksheet.UsedRange)
    If xRg Is Nothing Then Exit Sub
    If (xRg.Columns.Count > 1) Or _
       (xRg.Areas.Count > 1) Then
        MsgBox "the used range only contain one column", , "Kutools for Excel"
        Exit Sub
    End If
    Set xOutRg = Application.InputBox("please select output range(specify one cell):", "Kutools for Excel", xTxt, , , , , 8)
    If xOutRg Is Nothing Then Exit Sub
    Set xOutRg = xOutRg.Range(1)
    xUpdate = Application.ScreenUpdating
    Application.ScreenUpdating = False
    xLRow = xRg.Rows.Count
    For i = 1 To xLRow Step 5
        xRg.Cells(i).Resize(5).Copy
        xOutRg.Offset(xNRow, 0).PasteSpecial Paste:=xlPasteAll, Transpose:=True
        xNRow = xNRow + 1
    Next
    Application.ScreenUpdating = xUpdate
End Sub

3. Po vložení kódu stiskněte F5 klíč k jeho spuštění a zobrazí se okno s výzvou, které vám připomene výběr sloupce, který chcete transponovat, viz screenshot:

doc transponovat každých 5 řádků 4

4. Pak klikněte na tlačítko OKa vyberte jednu buňku, do které chcete umístit výsledek do jiného vyskakovacího pole, viz screenshot:

doc transponovat každých 5 řádků 5

5, A klikněte na tlačítko OK, data ve sloupci byla převedena na pět sloupců, které potřebujete, viz screenshot:

doc transponovat každých 5 řádků 6

Poznámka: Ve výše uvedeném kódu můžete změnit číslo 5 na jiné číslo, které potřebujete.


Pokud máte Kutools pro Excel, S jeho Rozsah transformace nástroj, můžete rychle transponovat jeden sloupec nebo řádek do více sloupců a řádků.

Kutools pro Excel : s více než 300 praktickými doplňky Excel, můžete si je vyzkoušet bez omezení do 30 dnů. 

Po instalaci Kutools pro Excel, postupujte následovně:

1. Vyberte data ve sloupci a poté klikněte Kutools > Rozsah > Rozsah transformace, viz screenshot:

2. V Rozsah transformace dialogové okno vyberte Jeden sloupec k rozsahu možnost podle Typ transformace, a pak zkontrolujte Pevná hodnota pod Řádky na záznam, pak zadejte počet sloupců, které chcete transponovat do Pevná hodnota rámeček, viz screenshot:

doc transponovat každých 5 řádků 8

3. A pak klikněte na tlačítko Ok Tlačítko, ve vyskakovacím poli vyberte buňku pro výstup výsledku, viz screenshot:

doc transponovat každých 5 řádků 9

4. Pak klikněte na tlačítko OK tlačítko a vaše data sloupce byla transponována každých 5 řádků, jak ukazuje následující snímek obrazovky:

doc transponovat každých 5 řádků 6

Stáhněte si zdarma zkušební verzi Kutools pro Excel!


Kutools pro Excel: s více než 300 praktickými doplňky aplikace Excel, můžete vyzkoušet bez omezení do 30 dnů. Stáhněte si a vyzkoušejte zdarma hned teď!

Nejlepší nástroje pro produktivitu v kanceláři

Populární funkce: Najít, zvýraznit nebo identifikovat duplikáty   |  Odstranit prázdné řádky   |  Kombinujte sloupce nebo buňky bez ztráty dat   |   Kolo bez vzorce ...
Super vyhledávání: Více kritérií VLookup    VLookup s více hodnotami  |   VLookup na více listech   |   Fuzzy vyhledávání ....
Pokročilý rozevírací seznam: Rychle vytvořte rozevírací seznam   |  Závislý rozbalovací seznam   |  Vícenásobný výběr rozevíracího seznamu ....
Správce sloupců: Přidejte konkrétní počet sloupců  |  Přesunout sloupce  |  Přepnout stav viditelnosti skrytých sloupců  |  Porovnejte rozsahy a sloupce ...
Doporučené funkce: Zaměření mřížky   |  Návrhové zobrazení   |   Velký Formula Bar    Správce sešitů a listů   |  Knihovna zdrojů (Automatický text)   |  Výběr data   |  Zkombinujte pracovní listy   |  Šifrovat/dešifrovat buňky    Odesílat e-maily podle seznamu   |  Super filtr   |   Speciální filtr (filtr tučné/kurzíva/přeškrtnuté...) ...
Top 15 sad nástrojů12 Text Tools (doplnit text, Odebrat znaky, ...)   |   50+ Graf Typ nemovitosti (Ganttův diagram, ...)   |   40+ Praktické Vzorce (Vypočítejte věk na základě narozenin, ...)   |   19 Vložení Tools (Vložte QR kód, Vložit obrázek z cesty, ...)   |   12 Konverze Tools (Čísla na slova, Přepočet měny, ...)   |   7 Sloučit a rozdělit Tools (Pokročilé kombinování řádků, Rozdělit buňky, ...)   |   ... a více

Rozšiřte své dovednosti Excel pomocí Kutools pro Excel a zažijte efektivitu jako nikdy předtím. Kutools for Excel nabízí více než 300 pokročilých funkcí pro zvýšení produktivity a úsporu času.  Kliknutím sem získáte funkci, kterou nejvíce potřebujete...

karta kte 201905


Office Tab přináší do Office rozhraní s kartami a usnadňuje vám práci

  • Povolte úpravy a čtení na kartách ve Wordu, Excelu, PowerPointu, Publisher, Access, Visio a Project.
  • Otevřete a vytvořte více dokumentů na nových kartách ve stejném okně, nikoli v nových oknech.
  • Zvyšuje vaši produktivitu o 50%a snižuje stovky kliknutí myší každý den!
Comments (15)
Rated 5 out of 5 · 1 ratings
This comment was minimized by the moderator on the site
Bonjour,

J'ai, dans mon unique colonne de données, des cellules placées aléatoirement toutes les x lignes et qui contiennent une suite de caractères.

Je voudrait que cette cellule soit dans la première colonne de la ligne transposée, avec, dans les colonnes suivantes, la transposition des lignes qui la suivent, jusqu'à la répétition de ma chaine de caractères, qui signifierait le début de la ligne suivante...

Bref, Heeelllp...

Merci d'avance
Stéphane
This comment was minimized by the moderator on the site
You're a genius. Thanks!
Rated 5 out of 5
This comment was minimized by the moderator on the site
I try doing this just as written, and all I get is Error:508
This comment was minimized by the moderator on the site
Hi, How to do this for Colms to rows. Thank you
This comment was minimized by the moderator on the site
I've rewritten the command to this one:
=INDEX($A:$A,ROW(A1)+((COLUMN(A1)-2)*5))
This executes it inverted.
So:
1
2
3
4
5
6
7
8
9
...
becomes
1, 6
2, 7
3, 8
4, 9
5, ...
This comment was minimized by the moderator on the site
Thanks alot _/\_ Awesome job with =INDEX($A:$A,ROW(B1)*5-5+COLUMN(B1))
worked perfectly
This comment was minimized by the moderator on the site
I tried only the the formula option and it worked great. Till now have been using (Transpose formula with ctrl+shift+enter) but there the disadvantage for we have delete many extra rows. when the rows to be transposed for large in the order of lakhs, then deleting them in excel is either impossible or takes a lot of time.
this approach made the life easier by only copying the required rows... Thanks
This comment was minimized by the moderator on the site
This is fricking AWESOME!!! Thank you SO much. Your example was exactly what I needed and it worked beautifully.
This comment was minimized by the moderator on the site
=INDEX($A:$A,ROW(A1)*5-5+COLUMN(A1))
It was very simple and really useful. Thank you so much
This comment was minimized by the moderator on the site
It was great! Thank you! You saved me hours of manual work!
This comment was minimized by the moderator on the site
Thank you so much! It was really helpful. :)
There are no comments posted here yet
Load More
Please leave your comments in English
Posting as Guest
×
Rate this post:
0   Characters
Suggested Locations