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

or

Как перенести каждые 5 или n строк из одного столбца в несколько столбцов?

документ транспонировать каждые 5 строк 1

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

Перенести каждые 5 или n строк из одного столбца в несколько столбцов с формулой

Транспонировать каждые 5 или n строк из одного столбца в несколько столбцов с кодом VBA

Перенести каждые 5 или n строк из одного столбца в несколько столбцов с помощью Kutools for Excel


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

1. Введите следующую формулу в пустую ячейку, в которую вы хотите поместить результат, например C1, = ИНДЕКС ($ A: $ A; СТРОКА (A1) * 5-5 + КОЛОНКА (A1)), см. снимок экрана:

документ транспонировать каждые 5 строк 2

Внимание: В приведенной выше формуле A: это ссылка на столбец, которую вы хотите транспонировать, и A1 первая ячейка используемого столбца, число 5 указывает количество столбцов, в которых будут располагаться ваши данные, вы можете изменить их по своему усмотрению. И первая ячейка списка должна располагаться в первой строке рабочего листа.

2. Затем перетащите маркер заливки вправо к пяти ячейкам и продолжайте перетаскивать маркер заливки вниз в диапазон ячеек, пока не отобразится 0, см. Снимок экрана:

документ транспонировать каждые 5 строк 3


Преобразуйте каждые 5 или n строк из одного столбца в несколько столбцов:

Чтобы преобразовать каждые 5 или n строк из одного столбца в несколько столбцов, Kutools for Excel's Диапазон преобразования Утилита поможет вам решить эту задачу как можно быстрее. Это также может помочь вам перенести диапазон данных в одну строку или столбец. Нажмите, чтобы загрузить Kutools for Excel!

документ транспонировать каждые 5 строк 10

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


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

1. Удерживайте ALT + F11 , чтобы открыть Microsoft Visual Basic для приложений окно.

2. Нажмите Вставить > модульи вставьте следующий код в модуль Окно.

Код 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 нажмите клавишу, чтобы запустить его, и появится диалоговое окно, напоминающее вам о выборе столбца, который вы хотите транспонировать, см. снимок экрана:

документ транспонировать каждые 5 строк 4

4, Затем нажмите OKи выберите одну ячейку, в которую вы хотите поместить результат, в другое всплывающее окно, см. снимок экрана:

документ транспонировать каждые 5 строк 5

5, И нажмите OK, данные в столбце были преобразованы в пять столбцов, которые вам нужны, см. снимок экрана:

документ транспонировать каждые 5 строк 6

Внимание: В приведенном выше коде вы можете изменить номер 5 на другой номер, который вам нужен.


Если у вас есть Kutools for Excel, С его Диапазон преобразования утилиту, вы можете быстро перенести один столбец или строку на несколько столбцов и строк.

Kutools for Excel : с более чем 300 удобными надстройками Excel, бесплатно и без ограничений в течение 30 дней. 

После установки Kutools for Excel, пожалуйста, сделайте следующее:

1. Выберите данные в столбце, а затем щелкните Kutools > Диапазон > Диапазон преобразования, см. снимок экрана:

2. В Диапазон преобразования диалоговое окно, выберите Один столбец для диапазона вариант под Тип трансформации, а затем проверьте Фиксированная стоимость под Строк на запись, затем укажите количество столбцов, которые вы хотите перенести в Фиксированная стоимость box, см. снимок экрана:

документ транспонировать каждые 5 строк 8

3, Затем нажмите Ok кнопку, в появившемся окне выберите ячейку для вывода результата, см. снимок экрана:

документ транспонировать каждые 5 строк 9

4, Затем нажмите OK , и данные вашего столбца были перенесены каждые 5 строк, как показано на следующем снимке экрана:

документ транспонировать каждые 5 строк 6

Загрузите бесплатную пробную версию Kutools for Excel прямо сейчас!


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

Лучшие инструменты для работы в офисе

Kutools for Excel решает большинство ваших проблем и увеличивает вашу производительность на 80%

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

Вкладка Office: интерфейс с вкладками в Office и упрощение работы

  • Включение редактирования и чтения с вкладками в Word, Excel, PowerPoint, Издатель, доступ, Visio и проект.
  • Открывайте и создавайте несколько документов на новых вкладках одного окна, а не в новых окнах.
  • Повышает вашу продуктивность на 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 · 11 months 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 · 1 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 · 1 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 · 2 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 · 3 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 · 11 months 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 · 11 months 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 ?