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

При работе с листом Excel иногда вы можете столкнуться с такой проблемой: как преобразовать или транспонировать диапазон данных в один столбец? (См. следующие скриншоты:) Сейчас я представлю вам три быстрых способа решения этой проблемы.

Транспонирование/Преобразование столбцов и строк в один столбец с помощью формулы

Транспонирование/Преобразование столбцов и строк в один столбец с помощью Kutools для Excel

Транспонирование/Преобразование столбцов и строк в один столбец с помощью кода VBA

Транспонирование/Преобразование столбцов и строк в один столбец с помощью формулы

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

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

2. После указания имени диапазона щелкните пустую ячейку, в этом примере я щелкну ячейку E1, а затем введу эту формулу: =INDEX(MyData,1+INT((ROW(A1)-1)/COLUMNS(MyData)),MOD(ROW(A1)-1+COLUMNS(MyData),COLUMNS(MyData))+1).

Примечание: MyData — это имя диапазона выбранных данных, вы можете изменить его по своему усмотрению.

3. Затем перетащите формулу вниз до ячейки, пока не появится информация об ошибке. Все данные в диапазоне будут транспонированы в один столбец. Смотрите скриншот:

Транспонирование/Преобразование столбцов и строк в один столбец с помощью Kutools для Excel

Может быть, формула слишком длинная, чтобы её запомнить, а код VBA имеет некоторые ограничения для вас, в этой ситуации не беспокойтесь, здесь я представлю вам более простой и многофункциональный инструмент — Kutools для Excel, с его утилитой Преобразовать диапазон, и вы сможете решить эту проблему быстро и удобно.

После бесплатной установки Kutools для Excel выполните следующие действия:

1. Выберите диапазон, который хотите транспонировать.

2. Щелкните Kutools > Преобразовать диапазон, см. скриншот:

3. В диалоговом окне Преобразовать диапазон выберите опцию Диапазон в одну колонку, см. скриншот:

4. Затем нажмите OK и укажите ячейку для размещения результата в появившемся окне.

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

Если вы хотите преобразовать столбец в диапазон с фиксированными строками, вы также можете использовать функцию Преобразовать диапазон для быстрого выполнения этой задачи.

Транспонирование/Преобразование столбцов и строк в один столбец с помощью кода VBA

С помощью следующего кода VBA вы также можете объединить несколько столбцов и строк в один столбец.

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

2. Щелкните Вставить > Модуль и вставьте следующий код в окно Модуль.

Sub ConvertRangeToColumn()
'Updateby20131126
Dim Range1 As Range, Range2 As Range, Rng As Range
Dim rowIndex As Integer
xTitleId = "KutoolsforExcel"
Set Range1 = Application.Selection
Set Range1 = Application.InputBox("Source Ranges:", xTitleId, Range1.Address, Type:=8)
Set Range2 = Application.InputBox("Convert to (single cell):", xTitleId, Type:=8)
rowIndex = 0
Application.ScreenUpdating = False
For Each Rng In Range1.Rows
    Rng.Copy
    Range2.Offset(rowIndex, 0).PasteSpecial Paste:=xlPasteAll, Transpose:=True
    rowIndex = rowIndex + Rng.Columns.Count
Next
Application.CutCopyMode = False
Application.ScreenUpdating = True
End Sub

3. Нажмите клавишу F5, чтобы запустить код, и отобразится диалоговое окно для выбора диапазона для преобразования. Смотрите скриншот:

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

5. И нажмите Ok, тогда содержимое ячеек диапазона будет преобразовано в список одного столбца, см. скриншот:

Связанные статьи:

Как изменить строку на столбец в Excel?

Как транспонировать / преобразовать один столбец в несколько столбцов в Excel?

Как транспонировать / преобразовать столбцы и строки в одну строку?