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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Детальніше ... Завантажити безкоштовно... Придбати ... 

---

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

• Увімкніть редагування та читання на вкладках у Word, Excel, PowerPoint, Publisher, Access, Visio та Project.
• Відкривайте та створюйте кілька документів на нових вкладках того самого вікна, а не в нових вікнах.
• Збільшує вашу продуктивність на 50% та зменшує сотні клацань миші для вас щодня!

Детальніше ... Завантажити безкоштовно... Придбати ...