gototopgototop

Материалы Excel

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

Сейчас 233 гостей онлайн
Вставить в таблицу Excel. Связанные выпадающие списки в Excel.

Связанные выпадающие списки в Excel.

      Выпадающие списки в Excel бывают разные. Есть простой раскрывающийся список Excel в ячейке. Есть многоуровневые зависимые выпадающие списки в Excel. Это, когда, в зависимости от выбранных данных в первом столбце выпадающего списка, меняется выпадающий список в ячейках второго столбца, третьего, т.д.
      Здесь разберём двухуровневый зависимый выпадающий список в Excel.
Например, в первом столбце из выпадающего списка выбрали «Пальто». Во втором столбце появился выпадающий список размеров этого пальто. А, если в первом столбце этой же ячейки из выпадающего списка выбрали «Брюки», то во втором столбце будет выпадающий список с размерами брюк.
     Итак, сделаем две таблицы. Саму таблицу сделаем на странице книги «Таблица». А списки сделаем на странице «Размеры». У нас есть такая таблица.
И мы сделали такие списки. 
      Внимание!  
В списках названия столбцов (В, С, D) должны полностью совпадать с названием в первом столбце (у нас – это наименование товара – ячейки А2:А4 должны совпадать с ячейками В1:D1).
      Если наименований много, то столбец можно транспонировать в строку. Как это сделать, смотрите в статье «Как поменять местами столбцы и строки в Excel» тут.
      Как настроить Excel, чтобы при добавлении ячеек в список столбца A, автоматически писалось название нового столбца, смотрите в статье «Как добавить столбец в Excel автоматически".
      Как сделать в Excel динамический диапазон - чтобы размер диапазонов списков при добавлении или убавлении ячеек менялся автоматически, смотрите в статье «Чтобы размер таблицы Excel менялся автоматически».
      Теперь нужно присвоить имена всем этим спискам. У нас в списках четыре диапазона (четыре столбца). Легко и быстро сделать так.
      Как присвоить имя диапазону в Excel.
Выделяем диапазон ячеек всех списков сразу вместе с шапкой таблицы списков – у нас это диапазон А1:D4. На закладке «Формулы» в разделе «Определенные имена» нажимаем функцию «Создать из выделенного фрагмента». В появившемся диалоговом окне оставляем галочку только у строки «В строке выше». 
Нажимаем «ОК». Всё, имена присвоены. На закладке «Формулы» нажимаем функцию «Диспетчер имен».Здесь перечислены все наши диапазоны списков. Проверили всё. Можно подкорректировать размер диапазона.  Мы  уменьшили размер диапазона «Юбка», чтобы в выпадающем списке не было пустой строки.
      Теперь устанавливаем первый выпадающий список в ячейки столбца А.
У нас, в примере, мы выделяем диапазон  A2:A3. И, через «Проверки данных» на закладке «Данные», устанавливаем выпадающие списки. Тип данных – выбираем «Список». А в строке «Источник» указываем имя диапазона. Например, «=Наименование_товара».
 Подробнее, как установить выпадающий список, смотрите в статье «Выпадающий список в Excel».
      Устанавливаем зависимые выпадающие списки в столбце В. Это второй уровень выпадающих списков.
      Внимание!
Перед тем, как устанавливать выпадающие списки в столбце В, выберите в первой верхней ячейке столбца А любое значение. Главное, чтобы эта ячейка не была пустой. У нас – это ячейка А2.
      Выделяем диапазон в столбце В (у нас – это В2:В3). Снова через функцию «Проверка данных» выбираем «Тип данных» – список. А в строке «Источник» пишем такую формулу  =ДВССЫЛ(А2)
Этой формулой мы говорим Excel, что список нужно показывать, в зависимости от значения в ячейке столбца А.
      Здесь все просто. Но бывает название диапазона (столбца) состоит из нескольких слов. Например, «Зимние пальто». А в имени диапазона нельзя ставить пробел. Имя диапазона напишем так «Зимние_пальто». Но формула ДВССЫЛ не найдет этот диапазон.  Тогда формулу нужно написать так.    =ДВССЫЛ(ПОДСТАВИТЬ(A2;" ";"_"))
      Если список на другом листе, то в формуле указываем название этого листа. Напишем так.  =ДВССЫЛ(''Размеры!А2:А4'')
      Нажимаем «ОК». Теперь во втором столбце установлены выпадающие списки, которые меняются, в зависимости от того, что написано в ячейках первого столбца. Получилось так.
Связанные выпадающие списки в Excel.Копируем формулу вниз по столбцу. Мы создали двухуровневый выпадающий список в Excel.
      Другой способ сделать связанный выпадающий список в Excel, смотрите в статье «Как сделать связанные выпадающие списки в Excel легко».