7 декабря 2020 г.

Excel Конкатенация ячеек по условию или транспонировать и сцепить

Имеется таблица со столбцами Дата и Город. За одну дату городов может быть от 0 до бесконечности, каждый на отдельной строке.
Задача в новой таблице вывести в столбце А даты, а в столбце B все города с этой датой в одну ячейку.




Изначальная таблица не отсортирована по дате, исправляем. Дальше мысль проста, находим в искомой таблице первое совпадение и берем это как начало диапазона, потом считаем общее количество совпадений и используем его как конец диапазона, далее при помощи ДВССЫЛ формируем ссылку на диапазон и передаем ее функции объединить. Протаскиваем и вуаля.


Для наглядности и самопроверки я использую служебные столбцы для промежуточных вычислений:

 ABCDE
1ИскомоеПервое совпадениеПодсчет совпаденийФормирование ссылкиПолучение данных
201.дек=ЕСЛИОШИБКА(ПОИСКПОЗ(A2;Лист2!B:B;0);0)=СЧЁТЕСЛИ(Лист2!B:B;Лист1!A2)=ЕСЛИ(D2=0;"";"Лист2!D"&B2&":D"&B2+C2-1)=ЕСЛИОШИБКА(ОБЪЕДИНИТЬ(", ";ИСТИНА;ДВССЫЛ(D2;ИСТИНА));"")

Также добавлены проверки, чтобы было красиво. Обязательно из числа совпадений нужно вычесть один.

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

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