Объединение нескольких списков в 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) вернет нам перечень городов из листа Города, но расположенный в одном столбце.