Очень полезный инструмент Excel для проверки введенных данных. Для повышения удобства работы с данными можно использовать возможности выпадающих списков: замену данных, просмотр данных с другого листа или файла, наличие функции поиска и зависимостей.
Путь: меню «Данные» — инструмент проверки данных — вкладка «Параметры». Тип данных — «Список».
Вы можете ввести значения, из которых будет составляться раскрывающийся список, несколькими способами:
Любой вариант даст такой результат.
вам необходимо создать раскрывающийся список со значениями динамического диапазона. Если в существующий диапазон вносятся какие-либо изменения (добавляются или удаляются данные), они автоматически возвращаются в раскрывающийся список.
Давайте проверим это. Вот наша таблица со списком на одном листе:
Добавим в таблицу новое «древовидное» значение».
Теперь уберем значение «береза».
«Умный стол», который легко «расширяется» и меняется, помог нам реализовать задуманное.
Теперь давайте сделаем так, чтобы вы могли вводить новые значения прямо в ячейку с этим списком. И данные были добавлены в диапазон автоматически.
Когда мы вводим новое имя в пустую ячейку выпадающего списка, будет отображаться сообщение: «Добавить введенное имя баобаба в выпадающий список?».
Нажмите «Да», и будет добавлена еще одна строка со значением «баобаб».
Когда значения для раскрывающегося списка находятся на другом листе или в другой книге, стандартный метод не работает. Решить проблему можно с помощью функции КОСВЕННЫЙ — это будет правильная ссылка на внешний источник информации.
Имя файла, из которого берется информация для списка, заключено в квадратные скобки. Этот файл должен быть открыт. Если книга с нужными значениями находится в другой папке, необходимо указать полный путь.
Возьмем три именованных диапазона:
Это обязательное условие. Выше описано, как сделать обычный список именованным диапазоном (с помощью диспетчера имен). Помните, что имя не может содержать пробелов и знаков препинания.
Это происходит, когда вам нужно выбрать несколько элементов одновременно из раскрывающегося списка. Обдумайте способы выполнения поставленной задачи.
Частный вторичный рабочий лист_Изменение (значение ByVal как диапазон)
В случае ошибки Продолжить Далее
Если не пересекается (Target, Range («C2: C5»)) равно Nothing и Target.Cells.Count = 1, тогда
Application.EnableEvents = False
newVal = Цель
Отмена заявки
oldval = Цель
Если Len (oldval) 0 И oldval newVal Тогда
Target = Target & «,» & newVal
Другой
Target = newVal
Конец, если
Если Len (newVal) = 0, то Target.ClearContents
Application.EnableEvents = True
Конец, если
Конец подзаголовка
Не забудьте поменять диапазоны на наши. Списки составляем классическим способом. А всю остальную работу сделают макросы.
По мере ввода нескольких первых букв с клавиатуры соответствующие элементы выделяются. И это далеко не все приятные моменты этого средства. Здесь можно настроить визуальное представление информации, указать в качестве источника одновременно две колонки.
Выпадающий список в Excel — пожалуй, один из самых удобных способов работы с данными. Вы можете использовать их как для заполнения форм, так и для создания громоздких информационных панелей и таблиц. Выпадающие списки часто используются в приложениях на смартфонах, веб-сайтах, они интуитивно понятны для обычного пользователя.
Нажмите кнопку ниже, чтобы загрузить файл с примерами раскрывающихся списков в Excel:
Допустим, у нас есть список фруктов:
Чтобы создать выпадающий список, вам необходимо выполнить следующие действия:
Если вы хотите создать раскрывающиеся списки в нескольких ячейках одновременно, выберите все ячейки, в которых вы хотите их создать, а затем выполните указанные выше действия. Важно убедиться, что ссылки на ячейки являются абсолютными (например, $ A $ 2), а не относительными (например, A2 или A $ 2 или $ A2).
В приведенном выше примере мы ввели список данных для раскрывающегося списка, выделив диапазон ячеек. Помимо этого метода, вы можете ввести данные, чтобы вручную создать раскрывающийся список (вам не нужно хранить его в какой-либо ячейке).
Например, предположим, что мы хотим отразить два слова «Да» и «Нет» в раскрывающемся меню. Для этого нам понадобятся:
Далее система создаст выпадающий список в выбранной ячейке. Все элементы, перечисленные в поле «Источник», разделенные точкой с запятой, будут отображаться в разных строках раскрывающегося меню.
Если вы хотите создать раскрывающийся список сразу в нескольких ячейках, выберите необходимые ячейки и следуйте приведенным выше инструкциям.
Наряду с методами, описанными выше, вы также можете использовать формулу СМЕЩЕНИЕ для создания раскрывающихся списков.
Например, у нас есть список со списком фруктов:
Чтобы создать раскрывающийся список с использованием формулы СМЕЩЕНИЕ, вам необходимо сделать следующее:
Система создаст выпадающий список со списком фруктов.
В приведенном выше примере мы использовали формулу = OFFSET (ref; row_offset; column_offset; [height]; [width]).
Эта функция содержит пять аргументов. Аргумент «ссылка» ($ A $ 2 в примере) указывает, с какой ячейки начинать смещение. В аргументах «offset_by_strings» и «offset_by_columns» (в примере указано значение «0») — сколько строк / столбцов необходимо переместить для отображения данных. Аргумент [высота] указывает значение «5», которое обозначает высоту диапазона ячеек. Мы не указываем аргумент «[ширина]», поскольку в нашем примере диапазон состоит из одного столбца.
Используя эту формулу, система возвращает в качестве данных для раскрывающегося списка диапазон ячеек, начинающийся с ячейки $ A $ 2, состоящий из 5 ячеек.
Если вы используете формулу СМЕЩЕНИЕ в предыдущем примере для создания списка, вы создаете список фиксированных данных в определенном диапазоне ячеек. Если вы хотите добавить значение как элемент списка, вам нужно будет вручную отредактировать формулу. Ниже вы узнаете, как создать динамический раскрывающийся список, в который новые данные будут автоматически загружаться для просмотра.
Для создания списка вам понадобятся:
В этой формуле в аргументе «[высота]» мы указываем формулу СЧЁТЕСЛИ в качестве аргумента, обозначающего высоту списка с данными, который вычисляет количество непустых ячеек в заданном диапазоне A2: A100.
Примечание. Для правильной работы формулы важно, чтобы в списке данных не было пустых строк, отображаемых в раскрывающемся меню.
Чтобы новые данные автоматически загружались в созданный раскрывающийся список, необходимо сделать следующее:
Таблица с данными готова, теперь мы можем создать выпадающий список. Это требует:
В Excel есть возможность копировать созданные раскрывающиеся списки. Например, в ячейке A1 у нас есть раскрывающийся список, который мы хотим скопировать в диапазон ячеек A2: A6 .
Чтобы скопировать раскрывающийся список с текущим форматированием:
Затем вы скопируете раскрывающийся список, сохранив исходный формат списка (цвет, шрифт и т.д.). Если вы хотите скопировать / вставить раскрывающийся список без сохранения формата, тогда:
После этого Excel будет копировать только данные раскрывающегося списка без сохранения исходного форматирования ячеек.
Иногда бывает сложно понять, сколько ячеек в файле Excel содержат раскрывающиеся списки. Есть простой способ их просмотреть. Из-за этого:
Нажав «ОК», Excel выберет все ячейки в раскрывающемся списке на листе. Таким образом вы можете одновременно привести все списки в общий формат, выделить края и т.д.
Иногда вам нужно создать несколько раскрывающихся списков таким образом, чтобы, выбирая значения из первого списка, Excel определял, какие данные отображать во втором раскрывающемся списке.
Допустим, у нас есть списки городов двух стран, России и США:
Для создания зависимого выпадающего списка нам понадобятся:
ТАКЖЕ ЧИТАЙТЕ: Обзор Amd Phenom II X6 1055T
Теперь, чтобы создать зависимое раскрывающееся меню:
Теперь, если вы выберете страну «Россия» в первом раскрывающемся списке, во втором раскрывающемся списке появятся только города, принадлежащие этой стране. Даже если вы выберете «США» из первого раскрывающегося списка.
Для таблиц, в которых используются постоянные и повторяющиеся данные (например, имена сотрудников, ассортимент продукции или процент скидки для клиентов), чтобы не учитывать это и не допускать ошибок при вводе текста, вы можете создать стандартный список один раз и сделать выбор из него при замене данных. Эта статья позволит вам использовать 4 различных способа создания раскрывающегося списка в Excel.
Этот способ использования раскрывающегося списка на самом деле не является табличным инструментом, который нужно каким-либо образом настраивать или компилировать. Это встроенная функция (горячие клавиши), которая работает всегда. При заполнении любого столбца вы можете щелкнуть правой кнопкой мыши пустую ячейку и выбрать пункт меню «Выбрать из раскрывающегося списка» в »раскрывающемся списке».
Тот же пункт меню можно запустить с помощью комбинации клавиш Alt + Стрелка вниз, и программа автоматически предложит значения ячеек, которые вы ранее заполнили данными в раскрывающемся списке. На изображении ниже программа предложила 4 варианта заполнения (в Excel не отображаются повторяющиеся данные). Единственное условие для работы этого инструмента — отсутствие пустых ячеек между ячейкой, в которую вы вводите данные из списка, и самим списком.
Кроме того, список для составления таким образом работает как в ячейке ниже, так и в ячейке выше. Для верхней ячейки программа будет брать содержимое списка из нижних значений. Опять же, между данными и ячейкой ввода не должно быть пустых ячеек.
Этот метод предполагает создание отдельных данных для списка. При этом данные могут быть размещены как на листе с таблицей, так и на другом листе файла Excel.
Если вы попытаетесь ввести значение, которого нет в указанном списке, Excel вернет ошибку.
И когда вы нажмете кнопку раскрывающегося списка в ячейке, вы увидите список значений из ранее созданного.
Чтобы использовать этот метод, у вас должна быть включена вкладка «РАЗРАБОТЧИК». По умолчанию эта вкладка отсутствует. Чтобы включить его:
Теперь у вас должна быть возможность использовать инструмент Combo Box Tool (элемент управления ActiveX). На вкладке РАЗРАБОТЧИК нажмите кнопку «Вставить» и найдите кнопку «Поле со списком (элемент управления ActiveX)» в элементах управления ActiveX. Нажмите здесь.
Нарисуйте данный объект в раскрывающемся списке Excel в той ячейке, где раскрывающийся список нужен.
Теперь вам нужно настроить этот элемент. Для этого активируйте «Режим дизайна» и нажмите кнопку «Свойства». Вы должны открыть окно свойств).
В открытом окне «Свойства» щелкните созданный ранее элемент Combo Box. Список свойств содержит множество параметров для настройки, и после их просмотра вы можете настроить многие из них, от представления списка до специальных свойств этого объекта.
Но на этапе создания нас интересуют только три основных:
В строке ColumnCount я указал значение 2, и теперь раскрывающиеся данные в списке выглядят так:
Как видите, мы получили выпадающий список в Excel с заменой данных из второго столбца данными «Поставщик».
