Как транспонировать или преобразовать столбцы и строки в одну строку?
Как объединить несколько строк и столбцов в одну длинную строку? Возможно, на первый взгляд это кажется простым: можно копировать данные по одному и вручную собирать их в одну строку. Однако при сотнях строк и столбцов такой подход отнимет массу времени и быстро утомит. Ниже я расскажу о нескольких быстрых способах справиться с этой задачей.
Транспонирование / преобразование столбцов и строк в одну строку с помощью формулы
Транспонирование / преобразование столбцов и строк в одну строку с помощью кода VBA
Транспонирование / преобразование столбцов и строк в одну строку с помощью Kutools для Excel
Транспонирование / преобразование столбцов и строк в одну строку с помощью формулы
Предположим, у вас есть диапазон данных, как на снимке экрана ниже. С помощью длинной формулы вы можете преобразовать эти данные в одну строку на новом листе.
Примените следующую формулу:
1. На новом листе текущей книги щёлкните ячейку A1 и скопируйте в неё эту формулу:=OFFSET(Sheet1!$A$1,((ROW()-1)*5)+(FLOOR(COLUMN()-1,4)/4),(COLUMN()-1)-(FLOOR(COLUMN()-1,4)))
Примечание: Sheet1!$A$1 — это ссылка на лист и диапазон, которые вы хотите использовать.
ROW()-1)*5 в приведённой выше формуле: 5 обозначает номер строки, а COLUMN()-1,4)/4, где 4 обозначает номер столбца. Вы можете изменить эти значения по своему усмотрению.
2. Затем перетащите маркер заполнения вправо, пока не появится число 0. В этом случае все данные из диапазона будут транспонированы в одну строку на новый лист. См. снимок экрана:
Транспонирование / преобразование столбцов и строк в одну строку с помощью кода VBA
Следующий код VBA также поможет вам преобразовать диапазон данных в одну строку.
1. Удерживая нажатыми клавиши ALT + F11, откройте окно Microsoft Visual Basic для приложений.
2. Нажмите Вставка > Модуль и вставьте следующий код в окно Модуль.
Sub TransformOneRow()
'Updateby20131120
Dim InputRng As Range, OutRng As Range
xTitleId = "KutoolsforExcel"
Set InputRng = Application.Selection
Set InputRng = Application.InputBox("Ranges to be transform :", xTitleId, InputRng.Address, Type:=8)
Set OutRng = Application.InputBox("Paste to (single cell):", xTitleId, Type:=8)
Application.ScreenUpdating = False
xRows = InputRng.Rows.Count
xCols = InputRng.Columns.Count
For i = 1 To xRows
InputRng.Rows(i).Copy OutRng
Set OutRng = OutRng.Offset(0, xCols + 0)
Next
Application.ScreenUpdating = True
End Sub3. Затем нажмите клавишу F5, чтобы запустить код. Появится диалоговое окно, в котором нужно выбрать диапазон данных для преобразования в строку, — после этого нажмите кнопку ОК. Затем откроется ещё одно диалоговое окно, где следует указать ячейку для вывода результата. См. снимки экрана:
 |
 |
 |
Затем нажмите кнопку ОК, и содержимое «Выберите диапазон» будет преобразовано в строку. См. снимок экрана:
 |
|
 |
Примечание: В строке Set OutRng = OutRng.Offset(0, xCols + 0) вы можете заменить значение 0 на любое другое в соответствии со своими потребностями.
Например, если вы хотите разделять результаты по исходным строкам одним столбцом, замените строку Set OutRng = OutRng.Offset(0, xCols + 0) на Set OutRng = OutRng.Offset(0, xCols + 1). Результат будет выглядеть следующим образом:
Транспонирование / преобразование столбцов и строк в одну строку с помощью Kutools для Excel
Первые два метода могут показаться сложными для новичков в Excel, поэтому мы рекомендуем удобный инструмент — Kutools для Excel.
Kutools для Excel включает более 300 удобных инструментов для Excel. Бесплатная пробная версия без ограничений на 30 дней! Получить сейчас
С помощью функции Преобразовать диапазон из Kutools для Excel вы можете быстро преобразовать диапазон в одну строку. Выполните следующие действия:
1. Выделите диапазон, который вы хотите транспонировать.
2. Нажмите Kutools > Диапазон > Преобразовать диапазон.
3. В диалоговом окне Преобразовать диапазон выберите опцию Диапазон в одну строку. См. снимок экрана:
4. Затем нажмите кнопку ОК и выберите ячейку для размещения результата в появившемся окне.
5. Нажмите кнопку ОК, и данные из диапазона транспонируются в одну строку. См. снимки экрана:
|
|
 |
Если вы хотите узнать больше об этой функции, посетите страницу Преобразовать диапазон.
Связанные статьи:
Как преобразовать строку в столбец в Excel?
Как транспонировать или преобразовать один столбец в несколько столбцов в Excel?
Как транспонировать или преобразовать столбцы и строки в один столбец?
Лучшие инструменты повышения продуктивности в Office
Раскройте весь потенциал Excel с помощью Kutools для Excel и ощутите эффективность как никогда раньше.Kutools для Excel предлагает более 300 расширенных функций для повышения продуктивности и Экономия времени.Нажмите здесь, чтобы получить нужную Вам функцию…
Office Tab добавляет в Office вкладки и значительно упрощает Вашу работу
- Включите редактирование и чтение во вкладках в Word, Excel, PowerPoint, Publisher, Access, Visio и Project.
- Открывайте и создавайте несколько документов во вкладках одного окна — вместо того чтобы использовать отдельные окна.
- Повышает вашу продуктивность на 50 % и экономит сотни кликов мышью каждый день!
Все надстройки Kutools — один установщик
Kutools for Office — набор надстроек для Excel, Word, Outlook и PowerPoint, а также Office Tab Pro, идеально подходящий командам, работающим с разными приложениями Office.
- Единый комплект— надстройки для Excel, Word, Outlook и PowerPoint + Office Tab Pro
- Один установщик, одна лицензия— настройка занимает считанные минуты (готово для MSI)
- Лучше работать вместе— оптимизированная продуктивность во всех приложениях Office
- 30-дневная полнофункциональная пробная версия— без регистрации и кредитной карты
- Лучшее соотношение цены и качества— экономия по сравнению с покупкой отдельных надстроек