Note: The other languages of the website are Google-translated. Back to English
Увійти  \/ 
x
or
x
Реєстрація  \/ 
x

or

Як транспонувати кожні 5 або n рядків з одного стовпця в кілька стовпців?

doc транспонувати кожні 5 рядків 1

Припустимо, у вас є довгі дані в стовпці A, і тепер ви хочете транспонувати кожні 5 рядків зі стовпця A у кілька стовпців, наприклад, транспонувати A1: A5 в C6: G6, A6: A10 в C7: G7 тощо. показано наступний знімок екрана. Як ви могли впоратися з цим завданням, не копіюючи та не вставляючи багаторазово в Excel?

Транспонуйте кожні 5 або n рядків з одного стовпця в кілька стовпців з формулою

Транспонуйте кожні 5 або n рядків з одного стовпця в кілька стовпців із кодом VBA

Транспонуйте кожні 5 або n рядків з одного стовпця в кілька стовпців за допомогою Kutools для Excel


В Excel ви можете застосувати таку формулу для транспонування кожного n рядків з одного стовпця в кілька стовпців, будь-ласка, виконайте наступне:

1. Введіть наступну формулу в порожню клітинку, куди ви хочете помістити результат, C1, наприклад, = ІНДЕКС ($ A: $ A, РЯДОК (A1) * 5-5 + Стовпчик (A1)), див. скріншот:

doc транспонувати кожні 5 рядків 2

примітки: У наведеній вище формулі, A: A - посилання на стовпець, яке потрібно транспонувати, та A1 - це перша комірка використовуваного стовпця, номер 5 вказує кількість стовпців, які будуть знаходити ваші дані, ви можете змінити їх відповідно до своїх потреб. І перша комірка списку повинна знаходитися в першому рядку аркуша.

2. Потім перетягніть маркер заповнення праворуч до п’яти комірок і продовжуйте перетягувати маркер заповнення до діапазону комірок, поки не відобразиться 0, див. Знімок екрана:

doc транспонувати кожні 5 рядків 3


Перетворюйте кожні 5 або n рядків з одного стовпця в кілька стовпців:

Щоб перетворити кожні 5 або n рядків з одного стовпця в кілька стовпців, Kutools для Excel's Діапазон трансформації утиліта може допомогти вам вирішити цю роботу якомога швидше. Це також може допомогти вам транспонувати діапазон даних в один рядок або стовпець. Клацніть, щоб завантажити Kutools для Excel!

doc транспонувати кожні 5 рядків 10

Kutools для Excel: з більш ніж 300 зручними надбудовами Excel, спробуйте безкоштовно без обмежень протягом 30 днів. Завантажте та безкоштовно пробну версію зараз!


Якщо ви не можете застосувати формулу правильно, наведений нижче код VBA також може вам допомогти.

1. Утримуйте клавішу ALT + F11 ключі, щоб відкрити Microsoft Visual Basic для додатків вікна.

2. Клацання Insert > Модуліта вставте наступний код у Модулі Вікно

Код VBA: Транспонуйте кожні 5 або n рядків з одного стовпця в кілька стовпців:

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. Після вставки коду, натисніть F5 клавішу, щоб запустити його, і з'явиться вікно запиту, щоб нагадати вам вибрати стовпець, який ви хочете транспонувати, див.

doc транспонувати кожні 5 рядків 4

4. Потім натисніть OK, і виберіть одну клітинку, куди ви хочете помістити результат, в інше спливаюче вікно, див. знімок екрана:

doc транспонувати кожні 5 рядків 5

5, І натисніть OK, дані в стовпці перетворено у п'ять потрібних вам стовпців, див. знімок екрана:

doc транспонувати кожні 5 рядків 6

примітки: У наведеному вище коді ви можете змінити номер 5 на інший номер, який вам потрібен.


Якщо у вас є Kutools для Excel, З його Діапазон трансформації Утиліта, ви можете швидко транспонувати один стовпець або рядок у кілька стовпців і рядків.

Kutools для Excel : з більш ніж 300 зручними надбудовами Excel, які можна спробувати без обмежень протягом 30 днів. 

після установки Kutools для Excel, будь ласка, виконайте наступне:

1. Виберіть дані у стовпці та натисніть Кутулс > Діапазон > Діапазон трансформації, див. скріншот:

2, в Діапазон трансформації діалогове вікно, виберіть Один стовпець для діапазону опція під Тип перетворення, а потім перевірте Фіксована вартість під Рядки на запис, а потім вкажіть кількість стовпців, до яких потрібно транспонувати Фіксована вартість , див. знімок екрана:

doc транспонувати кожні 5 рядків 8

3. А потім клацніть Ok у вікні, що з’явиться, виберіть клітинку для виведення результату, див. знімок екрана:

doc транспонувати кожні 5 рядків 9

4. Потім натисніть OK , і дані ваших стовпців будуть транспоновані кожні 5 рядків, як показано на наступному знімку екрана:

doc транспонувати кожні 5 рядків 6

Завантажте та безкоштовну пробну версію Kutools для Excel зараз!


Kutools для Excel: з більш ніж 300 зручними надбудовами Excel, спробуйте безкоштовно без обмежень протягом 30 днів. Завантажте та безкоштовно пробну версію зараз!

Найкращі інструменти для підвищення продуктивності офісу

Kutools для Excel вирішує більшість ваших проблем і збільшує продуктивність на 80%

  • Повторне використання: Швидко вставте складні формули, діаграми і все, що ви використовували раніше; Шифрувати комірки з паролем; Створити список розсилки та надсилати електронні листи ...
  • Супер формула бар (легко редагувати кілька рядків тексту та формули); Макет читання (легко читати та редагувати велику кількість комірок); Вставте у відфільтрований діапазон...
  • Об’єднати клітинки / рядки / стовпці без втрати даних; Вміст розділених комірок; Об'єднати повторювані рядки / стовпці... Запобігання дублюючим клітинам; Порівняйте діапазони...
  • Виберіть Повторюваний або Унікальний Рядки; Виберіть Пусті рядки (усі клітинки порожні); Супер знахідка та нечітка знахідка у багатьох робочих зошитах; Випадковий вибір ...
  • Точна копія Кілька клітинок без зміни посилання на формулу; Автоматичне створення посилань на кілька аркушів; Вставте кулі, Прапорці та інше ...
  • Витяг тексту, Додати текст, Видалити за позицією, Видаліть пробіл; Створення та друк проміжних підсумків підкачки; Перетворення вмісту комірок та коментарів...
  • Супер фільтр (зберегти та застосувати схеми фільтрів до інших аркушів); Розширене сортування за місяцем / тижнем / днем, частотою та іншим; Спеціальний фільтр жирним, курсивом ...
  • Поєднайте робочі зошити та робочі аркуші; Об’єднати таблиці на основі ключових стовпців; Розділіть дані на кілька аркушів; Пакетне перетворення xls, xlsx та PDF...
  • Понад 300 потужних функцій. Підтримує Office / Excel 2007-2019 та 365. Підтримує всі мови. Простота розгортання на вашому підприємстві чи в організації. Повна функція 30-денної безкоштовної пробної версії. 60-денна гарантія повернення грошей.
вкладка kte 201905

Вкладка Office забезпечує інтерфейс з вкладками для Office і значно спрощує вашу роботу

  • Увімкніть редагування та читання на вкладках у Word, Excel, PowerPoint, Publisher, Access, Visio та Project.
  • Відкривайте та створюйте кілька документів на нових вкладках того самого вікна, а не в нових вікнах.
  • Збільшує вашу продуктивність на 50% та зменшує сотні клацань миші для вас щодня!
дно офісної таблиці
Say something here...
symbols left.
You are guest
or post as a guest, but your post won't be published automatically.
Loading comment... The comment will be refreshed after 00:00.
  • To post as a guest, your comment is unpublished.
    Pradeep · 1 years ago
    Hi, How to do this for Colms to rows. Thank you
    • To post as a guest, your comment is unpublished.
      Jan steihn · 1 years ago
      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, ...
  • To post as a guest, your comment is unpublished.
    Maha · 2 years ago
    Thanks alot _/\_ Awesome job with =INDEX($A:$A,ROW(B1)*5-5+COLUMN(B1))
    worked perfectly
  • To post as a guest, your comment is unpublished.
    Arun Balaji VN · 2 years ago
    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
  • To post as a guest, your comment is unpublished.
    Sian · 3 years ago
    This is fricking AWESOME!!! Thank you SO much. Your example was exactly what I needed and it worked beautifully.
  • To post as a guest, your comment is unpublished.
    hamidreza · 3 years ago
    =INDEX($A:$A,ROW(A1)*5-5+COLUMN(A1))
    It was very simple and really useful. Thank you so much
  • To post as a guest, your comment is unpublished.
    Thank · 3 years ago
    It was great! Thank you! You saved me hours of manual work!
  • To post as a guest, your comment is unpublished.
    xplode · 4 years ago
    Thank you so much!
    It was really helpful. :)
  • To post as a guest, your comment is unpublished.
    MTVN · 4 years ago
    What formula to transpose every 5 rows to a single column?
  • To post as a guest, your comment is unpublished.
    hgimongu · 4 years ago
    Hi!

    May I ask you how to add Blank cell delimits records, instead of 5 as the data I am handling hasn't got fix number of rows. I believe the part which I should change may be around this part of the code:

    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

    Best
    • To post as a guest, your comment is unpublished.
      Mark.lewis2020 · 1 years ago
      I have the same issue , where the columns are not fixed, and there are empty lines, but I have a same character and end of each data set , that is "=" can I use this as delimiter , that breaks the loop, and next line is considered as new data set ?
    • To post as a guest, your comment is unpublished.
      Mark · 1 years ago
      did you get an answer for the same as i have the same issue , columns are not fixed in numbers, what should I do , can I use a particular character as a delimiter that breaks the loop and count next line and new data set ?