Note: The other languages of the website are Google-translated. Back to English

Як транспонувати кожні 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-2021 і 365. Підтримує всі мови. Легке розгортання на вашому підприємстві чи в організації. 30-денна безкоштовна пробна версія повних функцій. 60-денна гарантія повернення грошей.
вкладка kte 201905

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

  • Увімкніть редагування та читання на вкладках у Word, Excel, PowerPoint, Publisher, Access, Visio та Project.
  • Відкривайте та створюйте кілька документів на нових вкладках того самого вікна, а не в нових вікнах.
  • Збільшує вашу продуктивність на 50% та зменшує сотні клацань миші для вас щодня!
дно офісної таблиці
Коментарі (15)
Оцінено 5 з 5 · рейтинги 1
Цей коментар був мінімізований модератором на сайті
Привіт! Чи можу я запитати вас, як додати записи з роздільниками пустих клітинок замість 5, оскільки дані, які я обробляю, не мають фіксованої кількості рядків. Я вважаю, що частина, яку я повинен змінити, може бути навколо цієї частини коду: Для i = 1 To xLRow Крок 5 xRg.Cells(i).Resize(5).Copy xOutRg.Offset(xNRow, 0).PasteSpecial Paste: =xlPasteAll, Transpose:=True xNRow = xNRow + 1 Best
Цей коментар був мінімізований модератором на сайті
чи отримали ви відповідь на те саме, оскільки у мене така ж проблема, стовпці не фіксуються в числах, що мені робити, чи можу я використовувати певний символ як роздільник, який розриває цикл і підраховує наступний рядок і новий набір даних?
Цей коментар був мінімізований модератором на сайті
У мене така ж проблема, де стовпці не виправлені, і є порожні рядки, але в мене однаковий символ і кінець кожного набору даних, тобто "=" чи можу я використовувати це як роздільник, який порушує цикл і наступний рядок вважається новим набором даних?
Цей коментар був мінімізований модератором на сайті
Яку формулу транспонувати кожні 5 рядків в один стовпець?
Цей коментар був мінімізований модератором на сайті
Дуже дякую! Це було дуже корисно. :)
Цей коментар був мінімізований модератором на сайті
Це було чудово! Дякую! Ви заощадили мені години ручної роботи!
Цей коментар був мінімізований модератором на сайті
= ІНДЕКС ($ A: $ A, РЯДОК (A1) * 5-5 + Стовпчик (A1))
Це було дуже просто і дійсно корисно. Дуже дякую
Цей коментар був мінімізований модератором на сайті
Це страшенно ЧУДОВО!!! Дуже дякую. Ваш приклад був саме тим, що мені потрібно, і він чудово працював.
Цей коментар був мінімізований модератором на сайті
Я спробував лише варіант формули, і він чудово працював. До цього часу використовували (транспонувати формулу за допомогою ctrl+shift+enter), але є недолік, оскільки ми видалили багато зайвих рядків. коли рядки, які потрібно транспонувати для великих у порядку лакхів, то видалення їх у excel або неможливо, або займає багато часу.
цей підхід полегшив життя, копіюючи лише необхідні рядки... Дякую
Цей коментар був мінімізований модератором на сайті
Велике спасибі _/\_ Чудова робота з =INDEX($A:$A,ROW(B1)*5-5+COLUMN(B1))
працював ідеально
Цей коментар був мінімізований модератором на сайті
Привіт, як це зробити для Colms to rows. Дякую
Цей коментар був мінімізований модератором на сайті
Я переписав команду на цю:
=ІНДЕКС($A:$A,РЯДОК(A1)+((СТОЛПЦЕ(A1)-2)*5))
Це виконує його перевернуто.
Так:
1
2
3
4
5
6
7
8
9
...
стає
1, 6
2, 7
3, 8
4, 9
5 рік, ...
Цей коментар був мінімізований модератором на сайті
Я намагаюся зробити це так, як написано, і все, що я отримую, це помилка:508
Цей коментар був мінімізований модератором на сайті
Ти геній. Спасибі!
Оцінено 5 з 5
Цей коментар був мінімізований модератором на сайті
Привіт,

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. ..

Коротко, хеелллп...

Merci d'avance
Стефан
There are no comments posted here yet
Залишайте свої коментарі
Публікація як гість
×
Оцініть цю публікацію:
0   Персонажі
Рекомендовані місця

Слідуй за нами

Copyright © 2009 - WWW.extendoffice.com. | Всі права захищені. На основі ExtendOffice. | Карта сайту
Microsoft та логотип Office є товарними знаками або зареєстрованими товарними знаками Microsoft Corporation у США та / або інших країнах.
Захищений Sectigo SSL