Как объединить несколько последних непустых значений ячеек из смежных столбцов

Question

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

Я знаю, как получить последние непустые значения ячеек из одного столбца, применив

=IFERROR(LOOKUP(2,1/(INDIRECT("A1:A"&ROW())<>""),INDIRECT("A1:A"&ROW())),"")

Я также знаю, как объединить несколько значений, применяя

=TEXTJOIN("",TRUE,G4:L4)

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

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

Исходная формула ={1,0,0;0,2,0;0,0,3;4,0,0;0,5,0;0,0,6} Возврат массива после применения формулы ={1,0,0;1,2,0;1,2,3;4,0,0;4,5,0;4,5,6}

2. использовать массив если;
3. цикл по строкам, а не по столбцам.

Но мне не удаётся разработать правильную формулу.

Любая помощь будет оценена по достоинству. Спасибо.

Решение для персонажей указано в ссылке Решение по регистру символов

Answer 1

Если ваши данные такие, как вы показываете, и в каждом из четырех столбцов строки имеется только одна запись, то:

E1: =A1
E2: =TEXTJOIN("",,IF(A2="", E1,""),A2:D2)

и заполнить вниз

РедактироватьУчитывая ваш обновленный пример ввода и вывода:
Это можно сделать с помощью Power Query, доступного в Windows Excel 2010+ и Excel 365 (Windows или Mac).

Чтобы использовать Power Query

• Выберите ячейку в таблице данных.
• Data => Get&Transform => from Table/Range
• Когда открывается редактор PQ:Home => Advanced Editor
• Обратите внимание на таблицу.Имяв строке 2
• Вставьте M-код ниже вместо того, что вы видите
• Измените имя таблицы в строке 2 на то, которое было сгенерировано изначально.
• Прочитайте комментарии и изучите, Applied Stepsчтобы понять алгоритм.

Основной алгоритм состоит из заполнения строк по горизонтали, столбцов по вертикали, а затем возврата списка отдельных записей из каждой строки.

М-код

let

//Change next line to reflect your actual data source (table name)
    Source = Excel.CurrentWorkbook(){[Name="Table14"]}[Content],

//set the data types to text
    #"Changed Type" = Table.TransformColumnTypes(Source,
        List.Transform(Table.ColumnNames(Source), each {_, type text})),

//Fill across all rows, then down all columns
    #"Transposed Table" = Table.Transpose(#"Changed Type"),
    fillRows = Table.FillDown(#"Transposed Table", Table.ColumnNames(#"Transposed Table")),

    #"Transposed Table1" = Table.Transpose(fillRows),
    fillCols = Table.FillDown(#"Transposed Table1", Table.ColumnNames(#"Transposed Table1")),

//Results column combines the Distinct entries only in each of the four columns
    #"Added Custom" = Table.AddColumn(fillCols, "Output", 
        each Text.Combine(List.Distinct({[Column1],[Column2],[Column3],[Column4]}),""), type text),

//Remove uneeded columns
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"Column1", "Column2", "Column3", "Column4"})
in
    #"Removed Columns"