Объединение 3-х и более списков в MS EXCEL

Иногда для удобства требуется объединить три и более списка значений в один.

Пусть дано 5 списков и все они разной длины (см. Файл примера).

Задача

Объединим все значения из 5 списков в один. Задача объединения 2-х списков решена в одноименной статье.

Решение

Сначала создадим именованный диапазон, содержащий значения из всех списков. Для этого:

  • выделите, диапазон A2:E10;
  • на вкладке Формулы в группе Определенные имена выберите команду Присвоить имя;
  • в поле Имя введите: Диапазон_Списков;
  • убедитесь, что в поле Диапазон введена формула =пример!$A$2:$E$10
  • нажмите ОК.

Для вывода всех значений из 5 списков в один столбец будем использовать функцию ИНДЕКС(). Эта функция будет последовательно выводить значения из всех ячеек диапазона Диапазон_Списков на основании их номера столбца и номера строки. Осталось только определить адреса не пустых ячеек.

Создадим массив пар (номер столбца; номер строки) для всех ячеек диапазона. Для этого применим трюк: значения пары будем хранить в виде обычного числа, но формировать его будем по определенному правилу: правая часть числа будет содержать номер строки (для этого выделяется два разряда, т.е. максимальная длина списка может быть 99), а левая часть числа будет содержать номер столбца. Например, число 512 будет означать: 5-й столбец, 12-ая строка. Естественно, при необходимости можно увеличить разрядность для хранения номеров строк (формула из файла примера позволяет столбцам иметь до 9999 строк).

Технически осуществим это так. Сначала определим номер столбца и строки левого верхнего угла нашего Диапазона_Списков. Для этого создайте две именованные формулы =МИН(СТОЛБЕЦ(Диапазон_Списков)) и =МИН(СТРОКА(Диапазон_Списков))

Создайте еще одну именованную формулу Адреса:
=ЕСЛИ(ЕПУСТО(Диапазон_Списков);""; --((СТОЛБЕЦ(Диапазон_Списков)-Мин_Столбец+1)&
ВЫБОР(ДЛСТР(СТРОКА(Диапазон_Списков)-Мин_Строка+1);"0";"")&СТРОКА(Диапазон_Списков)-Мин_Строка+1))

Эта формула вернет массив адресов из нашего диапазона {101;201;301;401;501: 102;202;302;"";502: 103;203;303;"";503: 10…}. Вместо адресов пустых ячеек в массиве содержатся значения Пустой текст (""). Номера столбцов и строк отсчитываются от левой верхней ячейки Диапазона_список.

Заключительный этап. Формируем объединенный список. Запишем в ячейке следующую формулу:
=ЕСЛИОШИБКА(ИНДЕКС(Диапазон_Списков; --ПРАВСИМВ(НАИМЕНЬШИЙ(Адреса;СТРОКА(Z1));2);
--ЛЕВСИМВ(НАИМЕНЬШИЙ(Адреса;СТРОКА(Z1)); ДЛСТР(НАИМЕНЬШИЙ(Адреса;СТРОКА(Z1)))-2));"")

Функция НАИМЕНЬШИЙ() будет последовательно извлекать все числа, содержащие адреса ячеек. Функция ПРАВСИМВ() будет извлекать из этих чисел номер строки, а функция ЛЕВСИМВ() – номер столбца. Эти две функции возвращают текстовые значения, поэтому применим двойное отрицание (--), чтобы преобразовать текст в число (см. статью Преобразование чисел из текстового формата в числовой (часть 1)).

Таким образом можно объединить нужное количество столбцов, каждый из которых длиной не более 99 строк.

Для объединения столбцов, каждый из которых длиной не более 9999 строк нужно использовать формулу

=ЕСЛИОШИБКА(ИНДЕКС(Диапазон_Списков;
--ПРАВСИМВ(НАИМЕНЬШИЙ(Адреса;СТРОКА(Z1));4);
--ЛЕВСИМВ(НАИМЕНЬШИЙ(Адреса;СТРОКА(Z1));ДЛСТР(НАИМЕНЬШИЙ(Адреса;СТРОКА(Z1)))-4)
);"")

Именованную формулу Адреса также придется подкорректировать:

=ЕСЛИ(ЕПУСТО(Диапазон_Списков);"";
--((СТОЛБЕЦ(Диапазон_Списков)-Мин_Столбец+1)&ВЫБОР(ДЛСТР(СТРОКА(Диапазон_Списков)-Мин_Строка+1);"000";"00";"0";"")&СТРОКА(Диапазон_Списков)-Мин_Строка+1)
)

Примечание: при объединении большого количества столбцов с количеством строк >100, расчет формулы может притормаживать.

Связанные статьи

Похожие задачи
Прочитайте другие статьи, решающие похожие задачи в MS Excel. Это позволит Вам решать широкий класс подобных задач.
Средняя: 5 (2 оценок)

Комментарии

Елена (не проверено)

Как скачать пример?

Дарья (не проверено)

В формулах =ЕСЛИ(ЕПУСТО(Диапазон_Списков)... и = ЕСЛИОШИБКА(ИНДЕКС(Диапазон_Списков)... Excel выдает ошибку "недомустимое имя"

Creator

О синтаксических правилах при создании имен можно прочитать в статье http://excel2.ru/articles/imena


Попробуйте разобраться, скачав файл примера, в нем формула работает без ошибок.

Михаил (не проверено)

Подскажите пожалуйста, а как быть, если длина объединенного списка в разы больше 99?
Если речь идет о нескольких тысячах, 5000-6000?

Михаил (не проверено)

Подскажите пожалуйста, а как быть, если объединенный список в разы больше 99?
5000-6000?

Creator

Для объединения до 9 столбцов (включительно), каждый из которых длиной не более 9999 строк нужно использовать формулу

=ЕСЛИОШИБКА(ИНДЕКС(Диапазон_Списков;
--ПРАВСИМВ(НАИМЕНЬШИЙ(Адреса;СТРОКА(Z1));4);
--ЛЕВСИМВ(НАИМЕНЬШИЙ(Адреса;СТРОКА(Z1));ДЛСТР(НАИМЕНЬШИЙ(Адреса;СТРОКА(Z1)))-4)
);"")

Именованную формулу Адреса также придется подкорректировать:

=ЕСЛИ(ЕПУСТО(Диапазон_Списков);"";
--((СТОЛБЕЦ(Диапазон_Списков)-Мин_Столбец+1)&ВЫБОР(ДЛСТР(СТРОКА(Диапазон_Списков)-Мин_Строка+1);"000";"00";"0";"")&СТРОКА(Диапазон_Списков)-Мин_Строка+1)
)

Жека (не проверено)

Это жесть, респект :)

Михаил (не проверено)

Спасибо огромное!
Ваши решения очень помогли мне! )
Можно Вас как-то отблагодарить?

Creator

Рад, что Вам пригодилось решение. Буду благодарен, если Вы разместите на своей страничке в социальных сетях ссылку, информацию о нашем сайте excel2.ru Это нам важно для продвижения сайта. Общение с пользователями делает сайт лучше, так как мы помогаем решать реальные задачи.

Ge (не проверено)

А чем вызвано ограничение в 9 столбцов и можно ли от этого избавиться?

Creator

Оооо! Действительно, это ограничение было лишь плодом моего воображения. Формула не имеет ограничения по столбцам. Спасибо за комментарий. Статью подправил. В файле примера теперь 11 столбцов!

Яндекс.Метрика