вторник, 18 октября 2022 г.

Excel Транспонированное объединение по условию (формула массива)

Задача:

Имеем 800 колонок и 12К строк, из 800 колонок в каждой строке заполнено 2-15 ячеек. Нужно в одну ячейку собрать "НазваниеКолонки: Значение" всех заполненных ячеек в строке.



Решение (это формула массива, применяется Ctrl+Shift+Enter):

=ОБЪЕДИНИТЬ(СИМВОЛ(10);ИСТИНА;ЕСЛИ(ЕПУСТО(LA!$AJ3:$AXA3);"";LA!$AJ$1:$AXA$1&": "&LA!$AJ3:$AXA3))

Что формула делает:

ЕСЛИ(ЕПУСТО(LA!$AJ3:$AXA3);"";LA!$AJ$1:$AXA$1&": "&LA!$AJ3:$AXA3)

Этот блок можно прочитать так - смотрим каждую ячейку  в диапазоне LA!$AJ3:$AXA3 (LA-Имя листа), если она пуста, то ставим "", иначе берем значение ячейки в строке 1 (название колонки) и 3 (значение колонки) и приводим к виду "НазваниеКолонки: Значение", и так до конца. В итоге мы получим массив текстов:

"";"";"";"Вес, кг: 0,143";"";"";"";"";"Материал: спанбонд";"";"";"";"";"";"Длина в упаковке, мм: 380";"Ширина в упаковке, мм: 240";"Высота в упаковке, мм: 40""";"";"";"";"";;"Цвет: белый";"";"";"";"";"";"";"";"";"";"";"Плотность ткани, г/м²: 40";"";"";"";"";"";"";"";"";"";"";"Тип застежки: молния";"";"";"";"";"";"";"";"";"";"";"";"";"";"";

Этот массив и будет параметром 3+ для функции Объединить() :

=ОБЪЕДИНИТЬ(СИМВОЛ(10);ИСТИНА;"";"";"";"Вес, кг: 0,143";"";"";"";"";"Материал: спанбонд";"";"";"";"";"";"Длина в упаковке, мм: 380";"Ширина в упаковке, мм: 240";"Высота в упаковке, мм: 40""";"";"";"";"";;"Цвет: белый";"";"";"";"";"";"";"";"";"";"";"Плотность ткани, г/м²: 40";"";"";"";"";"";"";"";"";"";"";"Тип застежки: молния";"";"";"";"";"";"";"";"";"";"";"";"";"";"";)

Символ(10) - перенос строки (активируется Главное-переносить строки), второй параметр в Истина удалит все пустые параметры 3+

И на выходе получаем:

"Вес, кг: 0,143
Материал: спанбонд
Длина в упаковке, мм: 380
Ширина в упаковке, мм: 240
Высота в упаковке, мм: 40
Цвет: белый
Плотность ткани, г/м²: 40
Тип застежки: молния"

После я могу сохранить эту таблицу в CSV и получится файл с многострочными строками.

Вот что было (2 строки пришлось растягивать на 2 монитора):

И вот что стало:



Комментариев нет:

Отправить комментарий