gototopgototop

Материалы Excel

Сейчас на сайте

Сейчас 272 гостей онлайн
Сделать, создать, установить в таблице Excel. Как создать один список в Excel из трёх и более.

Как создать один список в Excel из трёх и более.

      Рассмотрим, как создать один список в Excel из трех и более списков. Как объединить два списка в Excel, читайте в статье «Как в Excel сделать список из двух». 
      Итак, у нас есть три списка. Нам их нужно объединить в один, но, не объединяя похожие слова в одну группу консолидацией, как это описано в статье «Как объединить таблицы в Excel» тут.  
В нашем примере, мы будем составлять один список из списков трех столбцов B, C, D.
Сначала присвоим имя диапазону всех этих столбцов. Как присвоить имя диапазону, смотрите в статье «Диапазон в Excel».
      Выделяем ячейки В1:D4. Через функцию «Присвоить имя» пишем имя диапазона. Мы напишем имя диапазона – «Размер». В строке  диалогового окна «Создание имени» стоит такая формула.   =Размеры!$B$1:$D$4   
Заполнили диалоговое окно так.
Нажимаем «ОК». 
      Теперь наша задача состоит в том, чтобы разместить последовательно все данные столбцов B, C, D из списков на листе «Размеры» в один столбец А на листе «Цена».  Для этого создадим две именованные формулы. Как присвоить имя формуле, смотрите в статье «Присвоить имя в Excel ячейке, диапазону, формуле».
      Нажимаем ячейку В2 на листе «Размеры». Нажимаем функцию «Присвоить имя» и заполняем  диалоговое окно. В строке «Имя» пишем «Столбец А». В строке «Диапазон» пишем такую формулу.  =МИН(СТОЛБЕЦ(Размер))
В формуле стоит слово «Размер» - это имя диапазона.
      И еще одну именованную формулу так же сделать, но формулу напишем такую.   =МИН(СТРОКА(Размер)) Имя у формулы будет – «Строка1».
Эти две формулы нужны Excel для определения номера строки и столбца в списке, чтобы выбирать из списка правильные данные.
      Теперь составим формулу, которая будет определять адрес наших ячеек в списке. Так же делаем именованную формулу, как две предыдущие, только формула будет такая.
=ЕСЛИ(ЕПУСТО(Размер);"";--((СТОЛБЕЦ(Размер)-СтолбецА+1)&ВЫБОР(ДЛСТР(СТРОКА(Размер)-Строка1+1);"0";"")&СТРОКА(Размер)-Строка1+1))
Назовем формулу  – «Адреса». Диалоговое окно заполнили так.Всё подготовили.
      Создание списка в Excel.     
Теперь пишем главную формулу в ячейке А2 списка на листе «Цены».
=ЕСЛИОШИБКА(ИНДЕКС(Размер;--ПРАВСИМВ(НАИМЕНЬШИЙ(Адреса;СТРОКА(Z1));2);--ЛЕВСИМВ(НАИМЕНЬШИЙ(Адреса;СТРОКА(Z1));ДЛСТР(НАИМЕНЬШИЙ(Адреса;СТРОКА(Z1)))-2));"")
Копируем формулу вниз по столбцу. Получился такой список.
Списки в Excel.Так можно объединить списки, длиной не более 99 ячеек. Если списки длиннее, то формула будет другая.
      Если ячеек в списках от 100 до 9999, то напишем формулы так.
    1.Именованная формула адреса  будет такая.
=ЕСЛИ(ЕПУСТО(Размер);" ";--((СТОЛБЕЦ(Размер)-СтолбецA+1)& ВЫБОР(ДЛСТР(СТРОКА(Размер)-Строка1+1);''000'';''00'';''0'';'''')&СТРОКА(Размер)-Строка1+1)
   2.А формула в ячейке на странице «Цена» будет такая.
=ЕСЛИОШИБКА(ИНДЕКС(Размер;--ПРАВСИМВ(НАИМЕНЬШИЙ(Адреса;СТРОКА(Z1));;);--ЛЕВСИМВ(НАИМЕНЬШИЙ(Адреса;СТРОКА(Z1));ДЛСТР(НАИМЕНЬШИЙ(Адреса;СТРОКА(Z1)))-4));'''')
      Как пронумеровать список в Excel, смотрите в статье "Автонумерация в Excel" здесь.
Если в списке написаны фамилии, и, нам нужно сделать из этого списка выпадающий список, то можно сделать алфавит только из букв, на которые начинаются фамилии в списке. Подробнее, смотрите в статье "Связанный выпадающий список в Excel по алфавиту" 
      Как сделать список в Excel без дублей, читайте в статье "Создание списка в Excel без повторов".