Как трансформировать длинный вертикальный столбец в таблицу с несколькими столбцами

В процессе формирования выполнений для Транснефти столкнулась с такой проблемой как распределение данных из одного вертикального столбца по нескольким ячейкам заданной таблицы.

Суть в следующем. Формирование КС-6 и КС-2 для Транснефти происходит не по сметам, а по строкам из расчета к договору. Каждая строка имеет уникальный шифр. В КС-6 внесены все строки из приложения к договору (в нашем договоре это порядка 1400 позиций).

Как трансформировать длинный вертикальный столбец в таблицу с несколькими столбцами

В КС-2 в каждом конкретном отчетном периоде выводятся только те позиции, работы по которым мы сейчас закрываем.

И на обратной стороне КС-2 и КС-6 необходимо прописать все позиции, которые закрываются в данном отчетном периоде, чтобы их подтвердил Транснефть Надзор.

Раньше требований к порядку предоставления данной информации не было, поэтому и особых проблем данная операция не вызывала – я ставила в КС-6а фильтр на все сделанные работы в данном отчетном периоде (потому что если выкатывать из КС-2, то позиции с выполненными работами скопируются вместе с подзаголовками, а это уже лишнее:)), копировала в блокнот и разбивала на примерно равные строки, затем вставляла в Excel:

Однако в прошлом месяце Заказчик прислал новые требования к оформлению обратной стороны форм КС-6 и КС-2 и теперь они должны выглядеть вот так:

А это уже проблема. Ладно, если у нас в выполнении 20 позиций, можно и руками перенести. А если 120? Или 520?

Встал вопрос – как же распределить данные из одного вертикального столбца по нескольким столбцам таблицы при помощи формул Excel?

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

Палочкой-выручалочкой оказалась китайская нейросеть Deepseek. Сначала на мой промт «Как распределить данные из одного вертикального столбца по нескольким ячейкам таблицы» я получила те же некорректные ответы как и в интернете:

А затем, в процессе диалога Deepseek понял задачу и тут же ее решил:

Содержание

1. Формула `ДВССЫЛ` + `СМЕЩ` (Универсальный способ для любой таблицы)

Это классический метод, который работает во всех версиях Excel и Google Таблиц.

Допустим:

У вас есть данные в столбце A:A (начиная с A1)
Вы хотите создать таблицу с n столбцами (например, с 3 колонками)

Формула для первой ячейки таблицы (например, C1):

excel

=ДВССЫЛ("A" & (СТРОКА()-СТРОКА($C$1))*3+СТОЛБЕЦ()-СТОЛБЕЦ($C$1)+1)

Разберем на примере:

Вы начинаете строить таблицу в ячейке C1
Хотите иметь 3 колонки
В ячейку C1 вставляете:

excel

=ДВССЫЛ("A" & (СТРОКА()-СТРОКА($C$1))*3+СТОЛБЕЦ()-СТОЛБЕЦ($C$1)+1)

Протягиваете формулу вправо и вниз

Как это работает:

(СТРОКА()-СТРОКА($C$1))*3 — вычисляет смещение по строкам
СТОЛБЕЦ()-СТРОКА($C$1) — вычисляет смещение по столбцам
Формула автоматически подставляет нужные адреса: A1, A2, A3 в первой строке, A4, A5, A6 во второй и т.д.

2. Функция ИНДЕКС (Более современный и понятный способ)

Для Excel и Google Таблиц:

excel

=ИНДЕКС($A:$A; (СТРОКА()-начальная_строка)*число_колонок + СТОЛБЕЦ() - начальный_столбец + 1)

Конкретный пример:

Данные в столбце A (с A1)
Создаем таблицу в диапазоне C1:E10 (3 колонки)
В ячейку C1 вводим:

excel

=ИНДЕКС($A:$A; (СТРОКА(C1)-СТРОКА($C$1))*3 + СТОЛБЕЦ(C1)-СТОЛБЕЦ($C$1) + 1)

Протягиваем на всю область C1:E10

3. Функция `WRAPCOLS` (Только для новых версий Excel 365)

Самое простое решение, если у вас последняя версия Excel:

excel

=WRAPCOLS(A:A; 3)

Где:

A:A — ваш исходный столбец
3 — количество колонок в новой таблице

Функция автоматически распределит все значения по указанному количеству столбцов.

4. Макрос VBA (Для обработки больших объемов данных)

Если нужно делать это регулярно, можно создать простой макрос:

vba

Sub РаспределитьСтолбец()

    Dim SourceRange As Range

    Dim TargetCell As Range

    Dim NumColumns As Integer

    Dim i As Long, j As Long, k As Long

    NumColumns = 3 ' Укажите нужное количество столбцов

    Set SourceRange = Range("A1:A" & Cells(Rows.Count, 1).End(xlUp).Row)

    Set TargetCell = Range("C1") ' Начало таблицы

    k = 1

    For i = 1 To Application.WorksheetFunction.RoundUp(SourceRange.Rows.Count / NumColumns, 0)

        For j = 1 To NumColumns

            If k <= SourceRange.Rows.Count Then

                TargetCell.Offset(i - 1, j - 1).Value = SourceRange.Cells(k).Value

                k = k + 1

            End If

        Next j

    Next i

End Sub

5. Power Query (Самый мощный инструмент)

Шаги:

Выделите ваш столбец
Данные → Из таблицы/диапазона
В редакторе Power Query:
1. Добавить столбец → Индексный столбец
1. Добавить столбец → Пользовательский столбец с формулой:

text

Number.Mod([Индекс], 3) // 3 - количество столбцов

Добавить столбец → Пользовательский столбец с формулой:

text

Number.IntegerDivide([Индекс], 3)

Сводный столбец по значению
Закрыть и загрузить

Итог: Какой способ выбрать?

Для разового использования → Формула ИНДЕКС
Если у вас Excel 365 → Функция WRAPCOLS (самый простой!)
Для регулярного использования → Power Query или макрос VBA
Для Google Таблиц → Используйте способ с формулой ИНДЕКС

Для себя я выбрала первые 2 варианта, т.к. массив данных небольшой и нет смысла сильно заморачиваться (пока), а вот функции WRAPCOLS (в русскоязычной версии Excel ВПСТОЛБЦОВ или СТРОКСПАН (в зависимости от версии Excel) у меня, к сожалению, нет, так что попробовать ее не удалось.

Скачать пример можно тут (таблица на 8 столбцов).