Объеденим значения, расположенные в нескольких столбцах, в один столбец.
Пусть однотипные значения хранятся в нескольких списках, расположенных в разных столбцах (см. файл примера ).
Как видно из рисунка выше, названия городов, относящиеся к разным странам, размещены в соответствующих столбцах.
Вообще, хранить однотипные данные в разных столбцах в 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) вернет нам перечень городов из листа Города, но расположенный в одном столбце.
© Copyright 2013 - 2024 Excel2.ru. All Rights Reserved
Комментарии