Пункт списка знаком нам по формам на сайтах. Подбирать готовые ценности удобно. Например, месяц никто не вводит вручную, он берется из такого списка. Вы можете создать раскрывающийся список в Excel с помощью различных инструментов. В этой статье мы рассмотрим каждый из них.
Как создать раскрывающийся список в Excel 2010 или 2016 с помощью команды на панели инструментов? На вкладке «Данные» в разделе «Инструменты для работы с данными» найдите кнопку «Проверка данных». Щелкните по нему и выберите первый элемент.
Откроется окно. На вкладке «Параметры» в раскрывающемся разделе «Тип данных» выберите «Список».
Ниже появится линия, указывающая на источники.
вы можете указать информацию несколькими способами.
Сначала присвоим имя. Для этого создайте такую таблицу на любом листе.
Выберите его и щелкните правой кнопкой мыши. Щелкните команду «Присвоить имя».
Введите имя в строку выше.
Вызвать окно «Проверка данных» и указать имя в поле «Источник», поставив перед ним знак «=».
В одном из трех случаев вы увидите нужный элемент. Выбор значения из раскрывающегося списка Excel осуществляется с помощью мыши. Щелкните по нему, и появится список указанных данных.
Вы узнали, как создать раскрывающийся список в ячейке Excel. Но можно сделать больше.
Если вы добавите значение в диапазон данных, который заменяется в списке, изменения не произойдут, пока новые адреса не будут указаны вручную. Чтобы связать диапазон и активный элемент, первый должен быть оформлен в виде таблицы. Создайте такой массив.
Выделите его и во вкладке «Главная» выберите любой стиль таблицы.
Убедитесь, что вы установили флажок ниже.
Вы получите такой дизайн.
Создайте активный элемент, как описано выше. Введите формулу как источник
= КОСВЕННО («Таблица1 [Город]»)
Чтобы узнать название таблицы, перейдите на вкладку «Дизайн» и посмотрите на нее. Вы можете изменить название на любое другое.
Функция ДВССЫЛ создает ячейку или ссылку на диапазон. Теперь ваш элемент в ячейке связан с массивом данных.
Попробуем увеличить количество городов.
Обратная процедура — замена данных из раскрывающегося списка в электронной таблице Excel работает очень просто. В ячейку, в которую вы хотите вставить выбранное значение из таблицы, введите формулу:
= Cell_address
Например, если список данных находится в ячейке D1, в ячейке, где будут отображаться выбранные результаты, введите формулу
= D1
Откройте выпадающее окно настроек и выберите «Любое значение» в разделе «Тип данных».
Ненужный элемент исчезнет.
Иногда в Excel нужно создать несколько списков, когда один зависит от другого. Например, у каждого города есть несколько адресов. При выборе первого варианта мы должны получать только адреса выбранного населенного пункта.
В этом случае дайте каждому столбцу имя. Выделите без первой ячейки (имени) и нажмите правую кнопку мыши. Выберите «Присвоить имя».
Это будет название города.
При названии Санкт-Петербурга и Нижнего Новгорода вы получите ошибку, так как название не может содержать пробелы, подчеркивания, специальные символы и т.д.
Поэтому мы переименуем эти города, вставив знак подчеркивания.
Создадим первый элемент в ячейке A9 обычным способом.
А во втором пишем формулу:
= КОСВЕННО (LA9)
Сначала вы увидите сообщение об ошибке. Согласен.
Проблема в том, что выбранное значение отсутствует. Как только город будет выбран в первом списке, второй будет работать.
вы можете использовать динамический диапазон данных для второго элемента. Дешевле, если количество адресов будет расти.
Создадим выпадающий список городов. Именованный диапазон выделен оранжевым.
Для второго списка нужно ввести формулу:
= СМЕЩЕНИЕ ($ A $ 1; ПОИСК ($ E $ 6; $ A: $ A; 0) -1; 1; СЧЁТЕСЛИ ($ A: $ A, $ E $ 6); 1)
Функция OFFSET возвращает ссылку на диапазон, который смещен от первой ячейки на указанное количество строк и столбцов: = OFFSET (начало; вниз; вправо; size_in_lines; size_in_columns)
ПОИСКПОЗ возвращает номер ячейки с городом, выбранным в первом списке (E6) в указанной области SA: $ A.
СЧЁТЕСЛИ подсчитывает количество совпадений в диапазоне со значением в указанной ячейке (E6).
Мы связали раскрывающиеся списки в Excel с условием совпадения и поиском по диапазону.
Нам часто требуется получить несколько значений из набора данных. Вы можете просматривать их в разных ячейках или объединять в одну. В любом случае нужен макрос.
Щелкните правой кнопкой мыши вкладку листа ниже и выберите Просмотреть код».
Откроется окно разработчика. В него нужно вставить следующий алгоритм.
Private Sub Worksheet_Change (ByVal Target As Range) При ошибке Возобновить Далее, если не пересечь (Target, Range («C2: F2»)) Ничего E Target.Cells.Count = 1 Тогда Application.EnableEvents = False Если Len (Target.Offset (1, 0)) = 0 Тогда Target.Offset (1, 0) = Target Else Target.End (xlDown) .Offset (1, 0) = Target End If Target.ClearContents Application.EnableEvents = True End If End Sub
Обратите внимание, что в строке
Если не пересекается (Target, Range («E7»)) Is Nothing E Target.Cells.Count = 1 Then
Вы должны написать адрес ячейки со списком. У нас будет Е7.
Вернитесь к листу Excel и создайте список в ячейке E7.
При выборе значения будут отображаться под ним.
Следующий код позволит вам накапливать значения в ячейке.
Private Sub Worksheet_Change (ByVal Target As Range) При ошибке Возобновить Далее Если не пересечь (Target, Range («E7»)) Ничего E Target.Cells.Count = 1 Тогда Application.EnableEvents = False newVal = Целевое приложение.Отменить oldval = Target If Len (oldval) 0 And oldval newVal Then Target = Target & «,» & newVal Else Target = newVal End If Len (newVal) = 0 Тогда Target.ClearContents Application.EnableEvents = True End If End Sub
Как только вы переместите указатель на другую ячейку, вы увидите список выбранных городов. Прочтите эту статью, чтобы создать объединенные ячейки в Excel.
Мы объяснили, как добавлять и редактировать раскрывающийся список в ячейке Excel. Надеемся, эта информация вам поможет.
