Как создать столбцы Родитель/Детя из иерархии в Excel

Мне нужно перевести иерархию в файле Excel в столбцы Родитель/Ребенок, используя либо скрипт VBA, либо формулы в столбцах.

Например, это начальный файл, где ребенок — это первая непустая запись слева направо, а родитель — первая непустая запись, идущая вверх от ячейки, находящейся на 1 столбец влево от ребенка:

Мне нужен способ (VBA?), чтобы перевести столбцы D-I в столбцы B и C (и добавить столбец K как столбец A)

Так что результат будет:

Моя самая большая сложность заключается в том, что такие ячейки, как H6, H7 и H8, все имеют родителя G5, а G5, G9 и G12 все имеют родителя F4.

Шаги (по крайней мере, в моей голове, если использовать VBA – хотя я могу путать/усложнять его):

1. Начните с K3 (корень находится там только как родитель для других записей)
2. Вставьте K3 в A3
3. Найдите первую непустую запись из D3-I3 (в данном случае E3) и скопируйте/вставьте это в C3 (ребенок)
4. От E3 перейдите на 1 столбец влево, затем найдите первую непустую запись, идущую вверх (D2), и скопируйте это в B3 (родитель)
5. Вернитесь к нашей начальной точке для этой строки (K3), перейдите на 1 строку вниз и повторите до конца записей (столбец K никогда не будет пустым, пока список не будет исчерпан)

Если есть способ сделать это на основе формул, я буду более чем рад использовать это, так как это может быть проще для реализации?

Заранее спасибо.

Решение с использованием VBA.
Шаги:

• Считать данные из столбцов D:K в двумерный массив
• Создать пустой двумерный массив (для вывода)
• Обойти данные:
  • Когда значение найдено в первых 7 столбцах, сохранить эти данные в одномерный массив, называемый parents.
  • Если мы не в первом столбце (который является “корнем”), записать значение в столбец 3 (ребенок) и родителя следующего более высокого уровня в столбец 2 (родитель).
• Когда завершите, запишите выходной массив в ваш лист.

Код довольно прост и выглядит следующим образом:

Dim ws as Worksheet
Set ws = ThisWorkbook.Sheets(1)   ' <-- Подстройте под свои нужды
Dim inputData, outputData
' Подсчет количества строк (мы пропустим строку заголовка)
Dim rowCount As Long
rowCount = ws.Range("A1").CurrentRegion.Rows.Count - 1
' Чтение данных
inputData = ws.Range("D2").Resize(rowCount, 8)
' Создание выходного массива
ReDim outputData(1 To rowCount, 1 To 3)
' Обработка данных построчно
Dim parents(1 To 7) As String
Dim row As Long, col As Long
For row = 1 To UBound(inputData)
    For col = 1 To 7
        If inputData(row, col) <> "" Then
            parents(col) = inputData(row, col)
            If col > 1 Then
                outputData(row, 1) = inputData(row, 8)      ' Тип
                outputData(row, 2) = parents(col - 1)       ' Родитель
                outputData(row, 3) = inputData(row, col)    ' Ребенок
            End If
        End If
    Next col
Next row
' Запись результата на лист
ws.Range("A2").Resize(rowCount, 3) = outputData

Вот набор формул, которые вы можете заполнить вниз, без необходимости в VBA. (Эти формулы могут не работать в более ранних версиях Excel)

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

Столбец A довольно прост:

=$K3

Столбцы B и C немного сложнее и используют функцию TAKE, функцию TOCOL и функцию CHOOSECOLS

Столбец B:

=TAKE(TOCOL(CHOOSECOLS($D$1:$I2, MATCH($C3, $D3:$I3, 0)-1), 3), -1)

Столбец C:

=TAKE(TOCOL($D3:$I3, 3), 1)

(Альтернативой для столбца C может быть =TEXTJOIN("", TRUE, $D3:$I3))

Столбец C просто берет текущую строку, использует TOCOL, чтобы удалить все пустые ячейки или ошибки, а затем возвращает первую оставшуюся запись с помощью TAKE.

Столбец B затем смотрит, в каком столбце содержится значение из столбца C (с помощью MATCH), берет предыдущий столбец (с помощью CHOOSECOLS) и использует TOCOL, чтобы удалить все пустые ячейки или ошибки. Затем он возвращает последнюю оставшуюся запись с помощью TAKE (поэтому это -1: отрицательные числа отсчитываются назад от конца)

Если вы хотите использовать эти формулы в VBA, вы можете установить Range.Formula, затем Calculate диапазон (или лист), и, наконец, установить Range.Value = Range.Value, чтобы ‘упростить’ данные.