Przejdź do głównej zawartości

Jak przetransponować co 5 lub n wierszy z jednej kolumny do wielu kolumn?

doc transponuj co 5 wierszy 1

Przypuśćmy, że masz długie dane w kolumnie A, a teraz chcesz transponować co 5 wierszy z kolumny A do wielu kolumn, na przykład transponować A1: A5 do C6: G6, A6: A10 do C7: G7 itd. następujący zrzut ekranu. Jak poradzić sobie z tym zadaniem bez wielokrotnego kopiowania i wklejania w programie Excel?

Transponuj co 5 lub n wierszy z jednej kolumny do wielu kolumn za pomocą formuły

Transponuj co 5 lub n wierszy z jednej kolumny do wielu kolumn za pomocą kodu VBA

Transponuj co 5 lub n wierszy z jednej kolumny do wielu kolumn za pomocą Kutools for Excel


W programie Excel możesz zastosować następującą formułę, aby transponować każde n wierszy z jednej kolumny do wielu kolumn, wykonaj następujące czynności:

1. Wprowadź następującą formułę do pustej komórki, w której chcesz umieścić wynik, na przykład C1, = INDEKS ($ A: $ A, WIERSZ (A1) * 5-5 + KOLUMNA (A1))zobacz zrzut ekranu:

doc transponuj co 5 wierszy 2

Note: W powyższym wzorze, Odp .: A to odniesienie do kolumny, które chcesz przetransponować, a A1 jest pierwszą komórką używanej kolumny, liczbą 5 wskazuje liczbę kolumn, które zostaną zlokalizowane przez Twoje dane, możesz je zmienić według potrzeb. Pierwsza komórka listy musi znajdować się w pierwszym wierszu arkusza.

2. Następnie przeciągnij uchwyt wypełniania w prawo do pięciu komórek i kontynuuj przeciąganie uchwytu wypełniania w dół do zakresu komórek, aż wyświetli się 0, patrz zrzut ekranu:

doc transponuj co 5 wierszy 3


Przekształć co 5 lub n wierszy z jednej kolumny na wiele kolumn:

Aby przekształcić co 5 lub n wierszy z jednej kolumny na wiele kolumn, Kutools dla programu Excel's Zakres transformacji narzędzie może pomóc w rozwiązaniu tego zadania tak szybko, jak to tylko możliwe. Może również pomóc w transpozycji zakresu danych do pojedynczego wiersza lub kolumny. Kliknij, aby pobrać Kutools dla programu Excel!

doc transponuj co 5 wierszy 10

Kutools dla programu Excel: z ponad 300 poręcznymi dodatkami do programu Excel, które można wypróbować bez ograniczeń w ciągu 30 dni. Pobierz i bezpłatną wersję próbną teraz!


Jeśli nie możesz poprawnie zastosować formuły, następujący kod VBA również może Ci pomóc.

1. Przytrzymaj ALT + F11 klawisze, aby otworzyć Microsoft Visual Basic for Applications okno.

2. Kliknij wstawka > Modułi wklej następujący kod w Moduł Okno.

Kod VBA: transponuj co 5 lub n wierszy z jednej kolumny do wielu kolumn:

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 wklejeniu kodu naciśnij F5 klawisz, aby go uruchomić, a pojawi się okno zachęty przypominające o wybraniu kolumny, którą chcesz transponować, patrz zrzut ekranu:

doc transponuj co 5 wierszy 4

4. Następnie kliknij OKi wybierz jedną komórkę, w której chcesz umieścić wynik w innym wyskakującym okienku, zobacz zrzut ekranu:

doc transponuj co 5 wierszy 5

5, i kliknij OKdane w kolumnie zostały przekonwertowane na pięć potrzebnych kolumn, patrz zrzut ekranu:

doc transponuj co 5 wierszy 6

Note: W powyższym kodzie możesz zmienić numer 5 na inny numer, którego potrzebujesz.


Jeśli Kutools dla programu Excel, Z siedzibą w Zakres transformacji narzędzie, możesz szybko transponować jedną kolumnę lub wiersz do wielu kolumn i wierszy.

Kutools dla programu Excel : z ponad 300 poręcznymi dodatkami Excela, które można wypróbować bez ograniczeń w ciągu 30 dni. 

Po zainstalowaniu Kutools dla programu Excelwykonaj następujące czynności:

1. Wybierz dane w kolumnie, a następnie kliknij Kutools > łodzie > Zakres transformacjizobacz zrzut ekranu:

2, w Zakres transformacji okno dialogowe, wybierz Pojedyncza kolumna do zakresu opcja pod Typ transformacji, a następnie sprawdź Stała wartość pod Wiersze na rekord, a następnie określ liczbę kolumn, do których chcesz transponować w pliku Stała wartość pudełko, patrz zrzut ekranu:

doc transponuj co 5 wierszy 8

3. A następnie kliknij Ok przycisk, w wyskakującym okienku wybierz komórkę, aby wyświetlić wynik, patrz zrzut ekranu:

doc transponuj co 5 wierszy 9

4. Następnie kliknij OK przycisk, a dane kolumny zostały transponowane co 5 wierszy, jak pokazano na poniższym zrzucie ekranu:

doc transponuj co 5 wierszy 6

Pobierz i bezpłatną wersję próbną Kutools dla programu Excel teraz!


Kutools dla programu Excel: z ponad 300 poręcznymi dodatkami do programu Excel, które można wypróbować bez ograniczeń w ciągu 30 dni. Pobierz i bezpłatną wersję próbną teraz!

Najlepsze narzędzia biurowe

Popularne funkcje: Znajdź, wyróżnij lub zidentyfikuj duplikaty   |  Usuń puste wiersze   |  Łącz kolumny lub komórki bez utraty danych   |   Okrągły bez wzoru ...
Super wyszukiwanie: Wiele kryteriów VLookup    Wiele wartości VLookup  |   Przeglądanie pionowe na wielu arkuszach   |   Wyszukiwanie rozmyte ....
Zaawansowana lista rozwijana: Szybko twórz listę rozwijaną   |  Zależna lista rozwijana   |  Lista rozwijana wielokrotnego wyboru ....
Menedżer kolumn: Dodaj określoną liczbę kolumn  |  Przesuń kolumny  |  Przełącz stan widoczności ukrytych kolumn  |  Porównaj zakresy i kolumny ...
Polecane funkcje: Fokus siatki   |  Widok projektu   |   Duży pasek formuły    Menedżer skoroszytów i arkuszy   |  Biblioteka zasobów (Automatyczny tekst)   |  Selektor dat   |  Połącz arkusze   |  Szyfruj/odszyfruj komórki    Wysyłaj e-maile według listy   |  Super filtr   |   Specjalny filtr (filtruj pogrubienie/kursywa/przekreślenie...) ...
15 najlepszych zestawów narzędzi12 Tekst Tools (Dodaj tekst, Usuń znaki, ...)   |   50 + Wykres rodzaje (Wykres Gantta, ...)   |   40+ Praktyczne Wzory (Oblicz wiek na podstawie urodzin, ...)   |   19 Wprowadzenie Tools (Wstaw kod QR, Wstaw obraz ze ścieżki, ...)   |   12 Konwersja Tools (Liczby na słowa, Przeliczanie walut, ...)   |   7 Połącz i podziel Tools (Zaawansowane wiersze łączenia, Podział komórki, ...)   |   ... i więcej

Zwiększ swoje umiejętności Excela dzięki Kutools for Excel i doświadcz wydajności jak nigdy dotąd. Kutools dla programu Excel oferuje ponad 300 zaawansowanych funkcji zwiększających produktywność i oszczędzających czas.  Kliknij tutaj, aby uzyskać funkcję, której najbardziej potrzebujesz...

karta kte 201905


Karta Office wprowadza interfejs z zakładkami do pakietu Office i znacznie ułatwia pracę

  • Włącz edycję i czytanie na kartach w programach Word, Excel, PowerPoint, Publisher, Access, Visio i Project.
  • Otwieraj i twórz wiele dokumentów w nowych kartach tego samego okna, a nie w nowych oknach.
  • Zwiększa produktywność o 50% i redukuje setki kliknięć myszką każdego dnia!
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