Microsoft Excel: выпадающие списки - TurboComputer.ru
Рейтинг статьи
1 звезда2 звезды3 звезды4 звезды5 звезд (пока оценок нет)
Загрузка...

Microsoft Excel: выпадающие списки

Создание раскрывающегося списка

Чтобы упростить работу пользователей с листом, добавьте в ячейки раскрывающиеся списки. Раскрывающиеся списки позволяют пользователям выбирать элементы из созданного вами списка.

Видео

На новом листе введите данные, которые должны отображаться в раскрывающемся списке. Желательно, чтобы элементы списка содержались в таблице Excel. Если это не так, список можно быстро преобразовать в таблицу, выделив любую ячейку диапазона и нажав клавиши CTRL+T.

Почему данные следует поместить в таблицу? Потому что в этом случае при добавлении и удалении элементов все раскрывающиеся списки, созданные на основе этой таблицы, будут обновляться автоматически. Дополнительные действия не требуются.

Выделите на листе ячейку, в которую требуется поместить раскрывающийся список.

На ленте откройте вкладку Данные и нажмите кнопку Проверка данных.

Примечание: Если кнопка Проверка данных недоступна, возможно, лист защищен или является общим. Разблокируйте определенные области защищенной книги или отмените общий доступ к листу, а затем повторите шаг 3.

На вкладке Параметры в поле Тип данных выберите пункт Список.

Щелкните поле Источник и выделите диапазон списка. В примере данные находятся на листе “Города” в диапазоне A2:A9. Обратите внимание на то, что строка заголовков отсутствует в диапазоне, так как она не является одним из вариантов, доступных для выбора.

Если можно оставить ячейку пустой, установите флажок Игнорировать пустые ячейки.

Установите флажок Список допустимых значений

Откройте вкладку Подсказка по вводу.

Если вы хотите, чтобы при выборе ячейки появлялось всплывающее сообщение, установите флажок Отображать подсказку, если ячейка является текущей и введите заголовок и сообщение в соответствующие поля (до 225 символов). Если вы не хотите, чтобы сообщение отображалось, снимите этот флажок.

Откройте вкладку Сообщение об ошибке.

Если вы хотите, чтобы при вводе значения, которого нет в списке, появлялось всплывающее сообщение, установите флажок Выводить сообщение об ошибке, выберите параметр в поле Вид и введите заголовок и сообщение. Если вы не хотите, чтобы сообщение отображалось, снимите этот флажок.

Не знаете, какой параметр выбрать в поле Вид?

Чтобы отобразить сообщение, не препятствующее вводу данных, которые не содержатся в раскрывающемся списке, выберите вариант Сообщение или “Предупреждение”. Если выбрать вариант “Сообщение”, сообщение будет отображаться со значком , а если “Предупреждение” — со значком .

Чтобы заблокировать пользователям ввод данных, которые не содержатся в раскрывающемся списке, выберите вариант Остановка.

Примечание: Если вы не добавили заголовок и текст, по умолчанию выводится заголовок “Microsoft Excel” и сообщение “Введенное значение неверно. Набор значений, которые могут быть введены в ячейку, ограничен”.

После создания раскрывающегося списка убедитесь, что он работает правильно. Например, рекомендуется проверить, изменяется ли ширина столбцов и высота строк при отображении всех ваших записей.

Если список элементов для раскрывающегося списка находится на другом листе и вы хотите запретить пользователям его просмотр и изменение, скройте и защитите этот лист. Подробнее о защите листов см. в статье Блокировка ячеек.

Если вы решили изменить элементы раскрывающегося списка, см. статью Добавление и удаление элементов раскрывающегося списка.

Чтобы удалить раскрывающийся список, см. статью Удаление раскрывающегося списка.

Предлагаем скачать образец книги с несколькими примерами проверки данных, аналогичными примеру в этой статье. Вы можете воспользоваться ими или создать собственные сценарии проверки данных. Скачать примеры проверки данных Excel.

Ввод данных станет быстрее и точнее, если ограничить значения в ячейке вариантами из раскрывающегося списка.

Сначала создайте на листе список допустимых элементов, а затем выполните сортировку или расположите их в нужном порядке. В дальнейшем эти элементы могут служить источником для раскрывающегося списка данных. Если список небольшой, на него можно легко ссылаться и вводить элементы прямо в средстве проверки данных.

Создайте список допустимых элементов для раскрывающегося списка. Для этого введите элементы на листе в одном столбце или строке без пустых ячеек.

Выделите ячейки, для которых нужно ограничить ввод данных.

На вкладке Данные в группе Инструменты нажмите кнопку Проверка данных или Проверить.

Примечание: Если команда проверки недоступна, возможно, лист защищен или книга является общей. Если книга является общей или лист защищен, изменить параметры проверки данных невозможно. Дополнительные сведения о защите книги см. в статье Защита книги.

Откройте вкладку Параметры и во всплывающем меню Разрешить выберите пункт Список.

Щелкните поле Источник и выделите на листе список допустимых элементов.

Диалоговое окно свернется, чтобы было видно весь лист.

Нажмите клавишу ВВОД или кнопку Развернуть , чтобы развернуть диалоговое окно, а затем нажмите кнопку ОК.

Значения также можно ввести непосредственно в поле Источник через запятую.

Чтобы изменить список допустимых элементов, просто измените значения в списке-источнике или диапазон в поле Источник.

Можно указать собственное сообщение об ошибке, которое будет отображаться при вводе недопустимых данных. На вкладке Данные нажмите кнопку Проверка данных или Проверить, а затем откройте вкладку Сообщение об ошибке.

См. также

На новом листе введите данные, которые должны отображаться в раскрывающемся списке. Желательно, чтобы элементы списка содержались в таблице Excel.

Почему данные следует поместить в таблицу? Потому что в этом случае при добавлении и удалении элементов все раскрывающиеся списки, созданные на основе этой таблицы, будут обновляться автоматически. Дополнительные действия не требуются.

Выделите на листе ячейку, в которую требуется поместить раскрывающийся список.

На ленте откройте вкладку Данные и нажмите кнопку Проверка данных.

На вкладке Параметры в поле Разрешить выберите пункт Список.

Если вы уже создали таблицу с элементами раскрывающегося списка, щелкните поле Источник и выделите ячейки, содержащие эти элементы. Однако не включайте в него ячейку заголовка. Добавьте только ячейки, которые должны отображаться в раскрывающемся списке. Список элементов также можно ввести непосредственно в поле Источник через запятую. Например:

Фрукты;Овощи;Зерновые культуры;Молочные продукты;Перекусы

Если можно оставить ячейку пустой, установите флажок Игнорировать пустые ячейки.

Установите флажок Список допустимых значений

Откройте вкладку Сообщение для ввода.

Если вы хотите, чтобы при выборе ячейки появлялось всплывающее сообщение, установите флажок Показывать сообщения и введите заголовок и сообщение в соответствующие поля (до 225 символов). Если вы не хотите, чтобы сообщение отображалось, снимите этот флажок.

Откройте вкладку Сообщение об ошибке.

Если вы хотите, чтобы при вводе значения, которого нет в списке, появлялось всплывающее сообщение, установите флажок Показывать оповещения, выберите параметр в поле Тип и введите заголовок и сообщение. Если вы не хотите, чтобы сообщение отображалось, снимите этот флажок.

Нажмите кнопку ОК.

После создания раскрывающегося списка убедитесь, что он работает правильно. Например, рекомендуется проверить, изменяется ли ширина столбцов и высота строк при отображении всех ваших записей. Если вы решили изменить элементы раскрывающегося списка, см. статью Добавление и удаление элементов раскрывающегося списка. Чтобы удалить раскрывающийся список, см. статью Удаление раскрывающегося списка.

Дополнительные сведения

Вы всегда можете задать вопрос специалисту Excel Tech Community, попросить помощи в сообществе Answers community, а также предложить новую функцию или улучшение на веб-сайте Excel User Voice.

Microsoft Excel: выпадающие списки

При работе в программе Microsoft Excel в таблицах с повторяющимися данными, очень удобно использовать выпадающий список. С его помощью можно просто выбирать нужные параметры из сформированного меню. Давайте выясним, как сделать раскрывающийся список различными способами.

Создание дополнительного списка

Самым удобным, и одновременно наиболее функциональным способом создания выпадающего списка, является метод, основанный на построении отдельного списка данных.

Прежде всего, делаем таблицу-заготовку, где собираемся использовать выпадающее меню, а также делаем отдельным списком данные, которые в будущем включим в это меню. Эти данные можно размещать как на этом же листе документа, так и на другом, если вы не хотите, чтобы обе таблице располагались визуально вместе.

Выделяем данные, которые планируем занести в раскрывающийся список. Кликаем правой кнопкой мыши, и в контекстном меню выбираем пункт «Присвоить имя…».

Открывается форма создания имени. В поле «Имя» вписываем любое удобное наименование, по которому будем узнавать данный список. Но, это наименование должно начинаться обязательно с буквы. Можно также вписать примечание, но это не обязательно. Жмем на кнопку «OK».

Переходим во вкладку «Данные» программы Microsoft Excel. Выделяем область таблицы, где собираемся применять выпадающий список. Жмем на кнопку «Проверка данных», расположенную на Ленте.

Открывается окно проверки вводимых значений. Во вкладке «Параметры» в поле «Тип данных» выбираем параметр «Список». В поле «Источник» ставим знак равно, и сразу без пробелов пишем имя списка, которое присвоили ему выше. Жмем на кнопку «OK».

Читайте также:  10 популярных статистических функций в Microsoft Excel

Выпадающий список готов. Теперь, при нажатии на кнопку у каждой ячейки указанного диапазона будет появляться список параметров, среди которых можно выбрать любой для добавления в ячейку.

Создание выпадающего списка с помощью инструментов разработчика

Второй способ предполагает создание выпадающего списка с помощью инструментов разработчика, а именно с использованием ActiveX. По умолчанию, функции инструментов разработчика отсутствуют, поэтому нам, прежде всего, нужно будет их включить. Для этого, переходим во вкладку «Файл» программы Excel, а затем кликаем по надписи «Параметры».

В открывшемся окне переходим в подраздел «Настройка ленты», и ставим флажок напротив значения «Разработчик». Жмем на кнопку «OK».

После этого, на ленте появляется вкладка с названием «Разработчик», куда мы и перемещаемся. Чертим в Microsoft Excel список, который должен стать выпадающим меню. Затем, кликаем на Ленте на значок «Вставить», и среди появившихся элементов в группе «Элемент ActiveX» выбираем «Поле со списком».

Кликаем по месту, где должна быть ячейка со списком. Как видите, форма списка появилась.

Затем мы перемещаемся в «Режим конструктора». Жмем на кнопку «Свойства элемента управления».

Открывается окно свойств элемента управления. В графе «ListFillRange» вручную через двоеточие прописываем диапазон ячеек таблицы, данные которой будут формировать пункты выпадающего списка.

Далее, кликаем по ячейке, и в контекстном меню последовательно переходим по пунктам «Объект ComboBox» и «Edit».

Выпадающий список в Microsoft Excel готов.

Чтобы сделать и другие ячейки с выпадающим списком, просто становимся на нижний правый край готовой ячейки, нажимаем кнопку мыши, и протягиваем вниз.

Связанные списки

Также, в программе Excel можно создавать связанные выпадающие списки. Это такие списки, когда при выборе одного значения из списка, в другой графе предлагается выбрать соответствующие ему параметры. Например, при выборе в списке продуктов картофеля, предлагается выбрать как меры измерения килограммы и граммы, а при выборе масла растительного – литры и миллилитры.

Прежде всего, подготовим таблицу, где будут располагаться выпадающие списки, и отдельно сделаем списки с наименованием продуктов и мер измерения.

Присваиваем каждому из списков именованный диапазон, как это мы уже делали ранее с обычными выпадающими списками.

В первой ячейке создаём список точно таким же образом, как делали это ранее, через проверку данных.

Во второй ячейке тоже запускаем окно проверки данных, но в графе «Источник» вводим функцию «=ДВССЫЛ» и адрес первой ячейки. Например, =ДВССЫЛ($B3).

Как видим, список создан.

Теперь, чтобы и нижние ячейки приобрели те же свойства, как и в предыдущий раз, выделяем верхние ячейки, и при нажатой клавише мышки «протаскиваем» вниз.

Всё, таблица создана.

Мы разобрались, как сделать выпадающий список в Экселе. В программе можно создавать, как простые выпадающие списки, так и зависимые. При этом, можно использовать различные методы создания. Выбор зависит от конкретного предназначения списка, целей его создания, области применения, и т.д.

Выпадающий список в Excel с помощью инструментов или макросов

Под выпадающим списком понимается содержание в одной ячейке нескольких значений. Когда пользователь щелкает по стрелочке справа, появляется определенный перечень. Можно выбрать конкретное.

Очень удобный инструмент Excel для проверки введенных данных. Повысить комфорт работы с данными позволяют возможности выпадающих списков: подстановка данных, отображение данных другого листа или файла, наличие функции поиска и зависимости.

Создание раскрывающегося списка

Путь: меню «Данные» – инструмент «Проверка данных» – вкладка «Параметры». Тип данных – «Список».

Ввести значения, из которых будет складываться выпадающий список, можно разными способами:

  1. Вручную через «точку-с-запятой» в поле «Источник».
  2. Ввести значения заранее. А в качестве источника указать диапазон ячеек со списком.
  3. Назначить имя для диапазона значений и в поле источник вписать это имя.

Любой из вариантов даст такой результат.

Выпадающий список в Excel с подстановкой данных

Необходимо сделать раскрывающийся список со значениями из динамического диапазона. Если вносятся изменения в имеющийся диапазон (добавляются или удаляются данные), они автоматически отражаются в раскрывающемся списке.

  1. Выделяем диапазон для выпадающего списка. В главном меню находим инструмент «Форматировать как таблицу».
  2. Откроются стили. Выбираем любой. Для решения нашей задачи дизайн не имеет значения. Наличие заголовка (шапки) важно. В нашем примере это ячейка А1 со словом «Деревья». То есть нужно выбрать стиль таблицы со строкой заголовка. Получаем следующий вид диапазона:
  3. Ставим курсор в ячейку, где будет находиться выпадающий список. Открываем параметры инструмента «Проверка данных» (выше описан путь). В поле «Источник» прописываем такую функцию:

Протестируем. Вот наша таблица со списком на одном листе:

Добавим в таблицу новое значение «елка».

Теперь удалим значение «береза».

Осуществить задуманное нам помогла «умная таблица», которая легка «расширяется», меняется.

Теперь сделаем так, чтобы можно было вводить новые значения прямо в ячейку с этим списком. И данные автоматически добавлялись в диапазон.

  1. Сформируем именованный диапазон. Путь: «Формулы» – «Диспетчер имен» – «Создать». Вводим уникальное название диапазона – ОК.
  2. Создаем раскрывающийся список в любой ячейке. Как это сделать, уже известно. Источник – имя диапазона: =деревья.
  3. Снимаем галочки на вкладках «Сообщение для ввода», «Сообщение об ошибке». Если этого не сделать, Excel не позволит нам вводить новые значения.
  4. Вызываем редактор Visual Basic. Для этого щелкаем правой кнопкой мыши по названию листа и переходим по вкладке «Исходный текст». Либо одновременно нажимаем клавиши Alt + F11. Копируем код (только вставьте свои параметры).
  5. Сохраняем, установив тип файла «с поддержкой макросов».
  6. Переходим на лист со списком. Вкладка «Разработчик» – «Код» – «Макросы». Сочетание клавиш для быстрого вызова – Alt + F8. Выбираем нужное имя. Нажимаем «Выполнить».

Когда мы введем в пустую ячейку выпадающего списка новое наименование, появится сообщение: «Добавить введенное имя баобаб в выпадающий список?».

Нажмем «Да» и добавиться еще одна строка со значением «баобаб».

Выпадающий список в Excel с данными с другого листа/файла

Когда значения для выпадающего списка расположены на другом листе или в другой книге, стандартный способ не работает. Решить задачу можно с помощью функции ДВССЫЛ: она сформирует правильную ссылку на внешний источник информации.

  1. Делаем активной ячейку, куда хотим поместить раскрывающийся список.
  2. Открываем параметры проверки данных. В поле «Источник» вводим формулу: =ДВССЫЛ(“[Список1.xlsx]Лист1!$A$1:$A$9”).

Имя файла, из которого берется информация для списка, заключено в квадратные скобки. Этот файл должен быть открыт. Если книга с нужными значениями находится в другой папке, нужно указывать путь полностью.

Как сделать зависимые выпадающие списки

Возьмем три именованных диапазона:

Это обязательное условие. Выше описано, как сделать обычный список именованным диапазоном (с помощью «Диспетчера имен»). Помним, что имя не может содержать пробелов и знаков препинания.

  1. Создадим первый выпадающий список, куда войдут названия диапазонов.
  2. Когда поставили курсор в поле «Источник», переходим на лист и выделяем попеременно нужные ячейки.
  3. Теперь создадим второй раскрывающийся список. В нем должны отражаться те слова, которые соответствуют выбранному в первом списке названию. Если «Деревья», то «граб», «дуб» и т.д. Вводим в поле «Источник» функцию вида =ДВССЫЛ(E3). E3 – ячейка с именем первого диапазона.

Выбор нескольких значений из выпадающего списка Excel

Бывает, когда из раскрывающегося списка необходимо выбрать сразу несколько элементов. Рассмотрим пути реализации задачи.

  1. Создаем стандартный список с помощью инструмента «Проверка данных». Добавляем в исходный код листа готовый макрос. Как это делать, описано выше. С его помощью справа от выпадающего списка будут добавляться выбранные значения.
  2. Чтобы выбранные значения показывались снизу, вставляем другой код обработчика.
  3. Чтобы выбираемые значения отображались в одной ячейке, разделенные любым знаком препинания, применим такой модуль.

Private Sub Worksheet_Change( ByVal Target As Range)
On Error Resume Next
If Not Intersect(Target, Range( “C2:C5” )) Is Nothing And Target.Cells.Count = 1 Then
Application.EnableEvents = False
newVal = Target
Application.Undo
oldval = Target
If Len(oldval) <> 0 And oldval <> newVal Then
Target = Target & “,” & newVal
Else
Target = newVal
End If
If Len(newVal) = 0 Then Target.ClearContents
Application.EnableEvents = True
End If
End Sub

Не забываем менять диапазоны на «свои». Списки создаем классическим способом. А всю остальную работу будут делать макросы.

Выпадающий список с поиском

  1. На вкладке «Разработчик» находим инструмент «Вставить» – «ActiveX». Здесь нам нужна кнопка «Поле со списком» (ориентируемся на всплывающие подсказки).
  2. Щелкаем по значку – становится активным «Режим конструктора». Рисуем курсором (он становится «крестиком») небольшой прямоугольник – место будущего списка.
  3. Жмем «Свойства» – открывается перечень настроек.
  4. Вписываем диапазон в строку ListFillRange (руками). Ячейку, куда будет выводиться выбранное значение – в строку LinkedCell. Для изменения шрифта и размера – Font.

При вводе первых букв с клавиатуры высвечиваются подходящие элементы. И это далеко не все приятные моменты данного инструмента. Здесь можно настраивать визуальное представление информации, указывать в качестве источника сразу два столбца.

Двухуровневый выпадающий список в Excel

Имеется несколько способов создания выпадающего списка. Выбор одного из них зависит от структуры имеющихся у вас данных.

Первый способ создания двухуровнего списка

Первый способ основывается на создание «умной» таблицы, заголовок которой содержит значения первого выпадающего списка (группы), а строки таблицы соответствуют значениям второго выпадающего списка (подгруппы). Значения элементов подгруппы должны располагаться в соответствующем столбце группы, как на рисунке ниже.

Теперь приступим к созданию первого выпадающего списка группы (в моем случае – список стран):

  1. Выберите ячейку, в которую будете вставлять выпадающий список;
  2. Переходим на вкладку ленты Данные;
  3. Выбираем команду Проверка данных;
  4. В выпадающем списке выбираем значение Список;
  5. В поле Источник указываем следующую формулу =ДВССЫЛ(“Таблица1[#Заголовки]”).

Формула ДВССЫЛ возвращает ссылку на диапазон заголовков «умной» таблицы. Преимущество использования такой таблицы в том, что при добавлении столбцов, выпадающий список будет автоматически расширен.

Осталось создать второй зависимый выпадающий список – список подгрупп.

Смело повторяем 4 первых пункта описанных выше. Источником в окне Проверка данных для второго выпадающего списка будет служить формула =ДВССЫЛ(“Таблица1[“&F2&”]”). Ячейка F2 в данном случае – значение первого выпадающего списка.

Можно использовать и обычную «неумную» таблицу, однако в этом случае вам придется вручную изменять диапазоны заголовков и строк. В рассмотренном примере это происходит автоматически.

Второй способ создания двухуровнего списка

Второй способ удобно применять, когда данные выпадающего списка записаны в два столбца. В первом идет наименование группы, а во втором – подгруппы.

ВАЖНО! Перед созданием зависимого списка по подгруппам необходимо отсортировать исходную таблицу по первому столбцу (столбец с группой) далее будет понятно зачем это делается.

Для создания выпадающего групп нам понадобится дополнительный столбец, содержащий уникальные значения групп из исходной таблицы. Для создания этого списка используйте функцию удаления дубликатов или воспользуйтесь командой Уникальные из надстройки VBA-Excel.

Теперь создадим выпадающий список групп. Для этого выполните 4 первых пункта из первого способа создания двухуровнего списка. В качестве Источника укажите диапазон уникальных значений групп. Тут все стандартно.

Рекомендация: удобно в качестве источника указывать именованный диапазон. Для его создания откройте Диспетчер имен со вкладки Формулы и присвойте имя диапазону с уникальными значениями.

Теперь самая сложная часть – указать в Источнике динамическую ссылку на диапазон со значениями второго выпадающего списка (списка подгрупп). Решать ее будем с помощью функции СМЕЩ(ссылка, смещ_по_строкам, смещ_по_столбцам, [высота], [ширина]), которая возвращает ссылку на диапазон, отстоящий от ячейки или диапазона ячеек на заданное число строк и столбцов.

  • Ссылка в нашем случае – $A$1 – верхний левый угол исходной таблицы;
  • Смещ_по_строкамПОИСКПОЗ(F3;$A$1:$A$67;0)-1 – номер строки со значением искомой группы (в моем случае страны ячейка F3) минус единица;
  • Cмещ_по_столбцам1 – так как нам необходим столбец с подгруппами (городами);
  • [Высота]СЧЁТЕСЛИ($A$1:$A$67;F3) – количество подгрупп в искомой группе (количество городов в стране F3);
  • [Ширина]1 – так как это ширина нашего столбца с подгруппами.

Выпадающий список в EXCEL

При заполнении ячеек данными, часто необходимо ограничить возможность ввода определенным списком значений. Например, имеется ячейка, куда пользователь должен внести название департамента, указав где он работает. Логично, предварительно создать список департаментов организации и позволить пользователю лишь выбирать значения из этого списка. Этот подход поможет ускорить процесс ввода и уменьшить количество опечаток.

Выпадающий список можно создать с помощью Проверки данных

В этой статье создадим Выпадающий список с помощью Проверки данных ( Данные/ Работа с данными/ Проверка данных ) с типом данных Список .

Выпадающий список можно сформировать по разному.

А. Простейший выпадающий список – ввод элементов списка непосредственно в поле Источник

Самым простым способом создания Выпадающего списка является ввод элементов списка непосредственно в поле Источник инструмента Проверка данных .

Предположим, в ячейке B 1 необходимо создать выпадающий список для ввода единиц измерений. Выделим ячейку B 1 и вызовем Проверку данных.

Если в поле Источник указать через точку с запятой единицы измерения шт;кг;кв.м;куб.м , то выбор будет ограничен этими четырьмя значениями.

Теперь смотрим, что получилось. Выделим ячейку B 1 . При выделении ячейки справа от ячейки появляется квадратная кнопка со стрелкой для выбора элементов из выпадающего списка .

Недостатки этого подхода: элементы списка легко потерять (например, удалив строку или столбец, содержащие ячейку B 1 ); не удобно вводить большое количество элементов. Подход годится для маленьких (3-5 значений) неизменных списков. Преимущество : быстрота создания списка.

Б. Ввод элементов списка в диапазон (на том же листе, что и выпадающий список)

Элементы для выпадающего списка можно разместить в диапазоне на листе EXCEL, а затем в поле Источник инструмента Проверки данных указать ссылку на этот диапазон.

Предположим, что элементы списка шт;кг;кв.м;куб.м введены в ячейки диапазона A 1: A 4 , тогда поле Источник будет содержать =лист1!$A$1:$A$4

Преимущество : наглядность перечня элементов и простота его модификации. Подход годится для редко изменяющихся списков. Недостатки : если добавляются новые элементы, то приходится вручную изменять ссылку на диапазон. Правда, в качестве источника можно определить сразу более широкий диапазон, например, A 1: A 100 . Но, тогда выпадающий список может содержать пустые строки (если, например, часть элементов была удалена или список только что был создан). Чтобы пустые строки исчезли необходимо сохранить файл.

Второй недостаток: диапазон источника должен располагаться на том же листе, что и выпадающий список , т.к. для правил Проверки данных нельзя использовать ссылки на другие листы или книги (это справедливо для EXCEL 2007 и более ранних).

Избавимся сначала от второго недостатка – разместим перечень элементов выпадающего списка на другом листе.

B. Ввод элементов списка в диапазон (на любом листе)

В правилах Проверки данных (также как и Условного форматирования ) нельзя впрямую указать ссылку на диапазоны другого листа (см. Файл примера ):

Пусть ячейки, которые должны содержать Выпадающий список , размещены на листе Пример,

а диапазон с перечнем элементов разместим на другом листе (на листе Список в файле примера ).

Для создания выпадающего списка, элементы которого расположены на другом листе, можно использовать два подхода. Один основан на использовании Именованного диапазона , другой – функции ДВССЫЛ() .

Используем именованный диапазон Создадим Именованный диапазон Список_элементов, содержащий перечень элементов выпадающего списка (ячейки A 1: A 4 на листе Список ) . Для этого:

  • выделяем А1:А4 ,
  • нажимаем Формулы/ Определенные имена/ Присвоить имя
  • в поле Имя вводим Список_элементов , в поле Область выбираем Книга ;

Теперь на листе Пример , выделим диапазон ячеек, которые будут содержать Выпадающий список .

  • вызываем Проверку данных ;
  • в поле Источник вводим ссылку на созданное имя: =Список_элементов .

Примечание Если предполагается, что перечень элементов будет дополняться, то можно сразу выделить диапазон большего размера, например, А1:А10 . Однако, в этом случае Выпадающий список может содержать пустые строки.

Избавиться от пустых строк и учесть новые элементы перечня позволяет Динамический диапазон . Для этого при создании Имени Список_элементов в поле Диапазон необходимо записать формулу = СМЕЩ(Список!$A$1;;;СЧЁТЗ(Список!$A:$A))

Использование функции СЧЁТЗ() предполагает, что заполнение диапазона ячеек ( A:A ), который содержит элементы, ведется без пропусков строк (см. файл примера , лист Динамический диапазон ).

Используем функцию ДВССЫЛ()

Альтернативным способом ссылки на перечень элементов, расположенных на другом листе, является использование функции ДВССЫЛ() . На листе Пример , выделяем диапазон ячеек, которые будут содержать выпадающий список, вызываем Проверку данных , в Источнике указываем =ДВССЫЛ(“список!A1:A4”) .

Недостаток : при переименовании листа – формула перестает работать. Как это можно частично обойти см. в статье Определяем имя листа .

Ввод элементов списка в диапазон ячеек, находящегося в другой книге

Если необходимо перенести диапазон с элементами выпадающего списка в другую книгу (например, в книгу Источник. xlsx ), то нужно сделать следующее:

  • в книге Источник.xlsx создайте необходимый перечень элементов;
  • в книге Источник.xlsx диапазону ячеек содержащему перечень элементов присвойте Имя , например СписокВнеш;
  • откройте книгу, в которой предполагается разместить ячейки с выпадающим списком;
  • выделите нужный диапазон ячеек, вызовите инструмент Проверка данных, в поле Источник укажите = ДВССЫЛ(“[Источник.xlsx]лист1!СписокВнеш”) ;

При работе с перечнем элементов, расположенным в другой книге, файл Источник . xlsx должен быть открыт и находиться в той же папке, иначе необходимо указывать полный путь к файлу. Вообще ссылок на другие листы лучше избегать или использовать Личную книгу макросов Personal.xlsx или Надстройки .

Если нет желания присваивать имя диапазону в файле Источник.xlsx , то формулу нужно изменить на = ДВССЫЛ(“[Источник.xlsx]лист1!$A$1:$A$4”)

СОВЕТ: Если на листе много ячеек с правилами Проверки данных , то можно использовать инструмент Выделение группы ячеек ( Главная/ Найти и выделить/ Выделение группы ячеек ). Опция Проверка данных этого инструмента позволяет выделить ячейки, для которых проводится проверка допустимости данных (заданная с помощью команды Данные/ Работа с данными/ Проверка данных ). При выборе переключателя Всех будут выделены все такие ячейки. При выборе опции Этих же выделяются только те ячейки, для которых установлены те же правила проверки данных, что и для активной ячейки.

Примечание : Если выпадающий список содержит более 25-30 значений, то работать с ним становится неудобно. Выпадающий список одновременно отображает только 8 элементов, а чтобы увидеть остальные, нужно пользоваться полосой прокрутки, что не всегда удобно.

В EXCEL не предусмотрена регулировка размера шрифта Выпадающего списка . При большом количестве элементов имеет смысл сортировать список элементов и использовать дополнительную классификацию элементов (т.е. один выпадающий список разбить на 2 и более).

Например, чтобы эффективно работать со списком сотрудников насчитывающем более 300 сотрудников, то его следует сначала отсортировать в алфавитном порядке. Затем создать выпадающий список , содержащий буквы алфавита. Второй выпадающий список должен содержать только те фамилии, которые начинаются с буквы, выбранной первым списком. Для решения такой задачи может быть использована структура Связанный список или Вложенный связанный список .

Выпадающий список в Excel: как сделать

У пользователей, которые довольно часто работают в Excel и ведут этой программе свои базы данных, наверняка нередко возникает потребность выбрать значение ячейки из заранее определенно перечня.

К примеру, у нас есть перечень наименований товаров, и наша задача – заполнить каждую ячейку определенного столбца таблицы, используя данный список. Для этого нужно создать список всех наименований, а затем реализовать возможность их выбора в нужных ячейках. Такое решение избавит от необходимости писать (копировать) одно и то же название вручную много раз, а также спасет от опечаток и прочих возможных ошибок, особенно, когда речь идет о больших таблицах.

Реализовать так называемый выпадающий список можно несколькими методами, которые мы и рассмотрим ниже.

Выпадающий список через контекстное меню

Самым простой и понятный метод, при котором нужно предварительно создать список в другом месте документа. Можно разместить его рядом с таблицей, либо создать новый лист и составить перечень там, чтобы не “засорять” исходный документ лишними элементами и данными.

  1. Во вспомогательной таблице пишем перечень всех наименований – каждый с новой строки в отдельной ячейке. В итоге должен получиться один столбец с заполненными данными.
  2. Затем отмечаем все эти ячейки, нажимаем в любом месте отмеченного диапазона правой кнопкой мыши и в открывшемся списке кликаем по функции “Присвоить имя..”.
  3. На экране появится окно “Создание имени”. Называем список так, как хочется, но с условием – первым символом должна быть буква, также не допускается использование определенных символов. Здесь же предусмотрена возможность добавления списку примечания в соответствующем текстовом поле. По готовности нажимаем OK.
  4. Переключаемся во вкладку “Данные” в основном окне программы. Отмечаем группу ячеек, для которых хотим задать выбор из нашего списка и нажимаем на значок “Проверка данных” в подразделе “Работа с данными”.
  5. На экране появится окно “Проверка вводимых значений”. Находясь во вкладке “Параметры” в типе данных останавливаемся на опции “Список”. В текстовом поле “Источник” пишем знак “равно” (“=”) и название только что созданного списка. В нашем случае – “=Наименование”. Нажимаем OK.
  6. Все готово. Справа от каждой ячейки выбранного диапазона появится небольшой значок со стрелкой вниз, нажав на которую можно открыть перечень наименований, который мы заранее составили. Щелкнув по нужному варианту из списка, он сразу же будет вставлен в ячейку. Кроме того, значение в ячейке теперь может соответствовать только наименованию из списка, что исключит любые возможные опечатки.

Создание списка с применением инструментов разработчика

Создать выпадающий список можно и другим образом – через инструменты разработчика с применением технологии ActiveX. Метод несколько сложнее описанного выше, но он предлагает более широкий набор инструментов по настройке списка: можно будет задать количество элементов, размер и внешний вид самого окна со списком, необходимость соответствия значения в ячейке с одним из значений списка и многое другое.

  1. В первую очередь, эти инструменты нужно найти и активировать, так как по умолчанию они выключены. Переходим в меню “Файл”.
  2. В перечне слева находим в самом низу пункт “Параметры” и щелкаем по нему.
  3. Переходим в раздел “Настроить ленту” и в области “Основные вкладки” ставим галочку напротив пункта “Разработчик”. Инструменты разработчика будут добавлены на ленту программы. Кликаем OK, чтобы сохранить настройки.
  4. Теперь в программе есть новая вкладка под названием “Разработчик”. Через нее мы и будем работать. Сначала создаем столбец с элементами, которые будут источниками значений для нашего выпадающего списка.
  5. Переключаемся во вкладу “Разработчик”. В подразделе “Элементы управления” нажимаем на кнопку “Вставить”. В открывшемся перечне в блоке функций “Элементы ActiveX” кликаем по значку “Поле со списком”.
  6. Далее нажимаем на нужную ячейку, после чего появится окно со списком. Настраиваем его размеры по границам ячейки. Если список выделен мышкой, на панели инструментов будет активен “Режим конструктора”. Нажимаем на кнопку “Свойства”, чтобы продолжить настройку списка.
  7. В открывшихся параметрах находим строку “ListFillRange”. В столбце рядом через двоеточие пишем координаты диапазона ячеек, составляющих наш ранее созданный список. Закрываем окно с параметрами, щелкнув на крестик.
  8. Затем кликаем правой кнопкой мыши по окну списка, далее – по пункту “Объект ComboBox” и выбираем “Edit”.
  9. В результате мы получаем выпадающий список с заранее определенным перечнем.
  10. Чтобы вставить его в несколько ячеек, наводим курсор на правый нижний угол ячейки со списком, и как только он поменяет вид на крестик, зажимаем левую кнопку мыши и тянем вниз до самой нижней строки, в которой нам нужен подобный список.

Связанный список

У пользователей также есть возможность создавать и более сложные взаимозависимые списки (связанные). Это значит, что список в одной ячейке будет зависеть от того, какое значение мы выбрали в другой. Например, в единицах измерения товара мы можем задать килограммы или литры. Если вы выберем в первой ячейке кефир, во второй на выбор будет предложено два варианта – литры или миллилитры. А если в первую ячейки мы остановимся на яблоках, во второй у нас будет выбор из килограммов или граммов.

  1. Для этого нужно подготовить как минимум три столбца. В первом будут заполнены наименования товаров, а во втором и третьем – их возможные единицы измерения. Столбцов с возможными вариациями единиц измерения может быть и больше.
  2. Сначала создаем один общий список для всех наименований продуктов, выделив все строки столбца “Наименование”, через контекстное меню выделенного диапазона.
  3. Задаем ему имя, например, “Питание”.
  4. Затем таким же образом формируем отдельные списки для каждого продукта с соответствующими единицами измерения. Для большей наглядности возьмем в качестве примера первую позицию – “Лук”. Отмечаем ячейки, содержащие все единицы измерения для этого продукта, через контекстное меню присваиваем имя, которое полностью должно совпадать с наименованием.Таким же образом создаем отдельные списки для всех остальных продуктов в нашем перечне.
  5. После этого вставляем общий список с продуктами в верхнюю ячейку первого столбца основной таблицы – как и в описанном выше примере, через кнопку “Проверка данных” (вкладка “Данные”).
  6. В качестве источника указываем “=Питание” (согласно нашему названию).
  7. Затем кликаем по верхней ячейке столбца с единицами измерения, также заходим в окно проверки данных и в источнике указываем формулу “=ДВССЫЛ(A2)“, где A2 – номер ячейки с соответствующим продуктом.
  8. Списки готовы. Осталось его только растянуть их все строки таблицы, как для столбца A, так и для столбца B.

Заключение

Благодаря выпадающим спискам можно значительно облегчить ведение баз данных в Microsoft Excel. Несмотря на кажущуюся сложность в выполнении данной задачи, на деле все оказывается куда проще. Главное – строго следовать очередности описанных выше действий, в зависимости от выбранного метода, и тогда все непременно получится.

Ссылка на основную публикацию
Adblock
detector