Как мне объединить две строки в одну, где каждая строка заполняет пробелы другой?

Как мне обработать данные в следующем формате с несколькими строками:

и объединить их в одну строку, например:

Я немного расширил ваши данные, чтобы лучше понять, как работают следующие подходы. Входные данные выглядят так:

Функция Фильтр:

Один из способов решения этой проблемы заключается в использовании формульного подхода с функцией Фильтр (доступно только для Excel для Microsoft 365 или Excel 2021). Для этого подхода повторите ваши заголовки в столбцах F:I. В ячейку F2 введите следующую формулу: =UNIQUE(A2:A9); это даст вам массив ваших уникальных ID. Следующий шаг — ввести следующую формулу в ячейку G2 и скопировать ее в остальные ячейки вашего диапазона:

=FILTER(B$1:B$9,($A$1:$A$9=$F2)*(B$1:B$9<>""),"")

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

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

Альтернативный Формульный Подход:

Если эта функция недоступна для вас, другой формульный подход к этой проблеме может быть следующим. Сначала скопируйте ваши входные данные в диапазон F1:I9 и удалите все цвета соответственно. Теперь введите следующую формулу в ячейку G2 и скопируйте ее в диапазон G2:I9.

=IFERROR(IF(ISBLANK(B2),INDEX(B:B, MATCH(1,($A:$A=$A2)*(1-ISBLANK(B:B)),0)),B2), "")

Следующий шаг — скопировать ваши заголовки в диапазон K1:N1. В ячейку K2 введите следующую формулу для создания массива уникальных ID: =UNIQUE(F2:F9). Для получения соответствующей информации следующим шагом будет ввод следующей формулы в ячейку L2 и копирование ее в соответствующий диапазон L2:N4:

=INDEX($F$1:$I$9,MATCH($K2,$F$1:$F$9,0),MATCH(L$1,$F$1:$I$1,0))

Подход с Сводной Таблицей:

Наконец, можно использовать подход со сводной таблицей. Главная сложность этого подхода заключается в том, что у вас текстовые, а не числовые данные. Тем не менее, есть обходной путь, чтобы это сделать. Для этого подхода я думаю, имеет смысл немного изменить ваши данные и преобразовать их в таблицу Excel с помощью CTRL + T:

Выберите ваши данные и вставьте сводную таблицу через вкладку Вставка -> Сводная таблица -> убедитесь, что выбрана опция Добавить эти данные в модель данных. Перетащите ID в область строк, а типы в область столбцов. Найдите Table1 в Поля сводной таблицы -> щелкните правой кнопкой мыши -> Добавить меру....

Создайте следующую меру:

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

Вы можете получить желаемый результат, используя Power Query, доступный в Windows Excel 2010+ и Office 365 Excel

• Выберите любую ячейку в вашей исходной таблице
• Данные => Получить и преобразовать => Из таблицы/диапазона или Из листа
• Когда открывается интерфейс PQ, перейдите в Главная => Расширенный редактор
• Обратите внимание на имя таблицы в строке 2 кода.
• Замените существующий код на M-Code ниже
• Измените имя таблицы в строке 2 вставленного кода на “реальное” имя вашей таблицы
• Изучите любые комментарии и также окно Примененные шаги, чтобы лучше понять алгоритм и шаги

let

//измените следующую строку, чтобы отразить ваш фактический источник данных (вероятно, изменить имя таблицы)
    Source = Excel.CurrentWorkbook(){[Name="Table3"]}[Content],

//установите типы данных
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"ID", Int64.Type}, {"Type 1", type text}, {"Type 2", type text}, {"Type 3", type text}}),

//Группировать по ID, затем объединить данные по каждому типу.
//    Если есть только одна запись на тип, это вернет только одно значение
    #"Grouped Rows" = Table.Group(#"Changed Type", {"ID"}, {
        {"Type 1", each Text.Combine([Type 1],", "), type text},
        {"Type 2", each Text.Combine([Type 2],", "), type text},
        {"Type 3", each Text.Combine([Type 3],", "), type text}})
in
    #"Grouped Rows"