Объединение нескольких списков в MS EXCEL (без формул массива))

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

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

Как видно из рисунка выше, названия городов, относящиеся к разным странам, размещены в соответствующих столбцах.

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

Более правильно хранить перечень городов вот в такой таблице.

Платой за такое "правильное" размещение является необходимость ввода повторных значений стран.

СОВЕТ: О советах по созданию таблиц с правильной структурой читайте в статье Советы по построению таблиц в MS EXCEL.

Тем не менее, иногда встречаются задачи, в которых такое размещение данных оправдано (см. Многоуровневый связанный список в MS EXCEL).

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

Аналогичная задача была решена в статье Объединение 3-х и более списков в MS EXCEL. Здесь мы решим ее без формул массива, но нам понадобится создать дополнительный лист с формулами.

Решение

На листе Связи выведем перечень всех стран с листа Города и подсчитаем количество городов у каждой страны =СЧЁТЗ(СМЕЩ(города!$B$2;;ПОИСКПОЗ(A2;города!$B$1:$V$1;0)-1;21)).

Количество городов позволит нам определелить не только количество раз, которое нужно повторить название страны в итоговой таблице, но и смещение для каждой страны. Т.е. страна США будет иметь смещение 1 (т.е. в таблице идет первой), затем через 12 строк (12 - число городов в США из таблицы на листе Города) идет страна Мексика (ее смещение =13, т.е. начинается с 13-й строки таблицы) и т.д.

Смещение рассчитаем с помощью формулы =ЕСЛИ(B2;СУММ($B$1:B1)+1;-1)

У стран без городов вместо смещения будет выведена ошибка #Н/Д.

Примечание: в функции СУММ() использована относительная и абсолютная ссылка на В1.

Теперь создадим таблицу, в которой все города будут выведены в один столбец (см. лист таблица в файле примера).

Сначала сформируем сквозную нумерацию городов в столбце А (см. статью Числовые последовательности в MS EXCEL (порядковые номера 1,2,3... и др.)

Затем, используя связку функций ИНДЕКС() и ПОИСКПОЗ(), выведем названия стран нужное количество раз (количество соответствует количеству городов) с помощью формулы =ИНДЕКС(Связи!$A$2:$A$22;ПОИСКПОЗ(A8;Связи!$C$2:$C$22;1)).

Примечание: функции ИНДЕКС() и ПОИСКПОЗ() позволяют сформировать аналог "левого ВПР()". Подробнее - здесь.

Нам также понадобится счетчик городов для каждой страны =СЧЁТЕСЛИ($B$7:B8;B8)

И наконец, формула =СМЕЩ(города!$B$2;C8-1;ПОИСКПОЗ(B8;города!$B$1:$V$1;0)-1) вернет нам перечень городов из листа Города, но расположенный в одном столбце.

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

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