Нужен для того, чтобы при заполнении легко и быстро выбирать нужное значение. Мы рассмотрим два способа создания раскрывающегося списка в Excel 2007 .
Первый способ самый простой, но и наименее пригодный к применению. Под столбцом с данными нужно нажать правой клавишей на ячейку правой клавишей мыши, и нажать на строку Выбрать из раскрывающегося списка . Затем выбираете нужное значение, нажимаете на него, и это значение появляется в ячейке. Данный способ действует только для ячейки непосредственно под списком, и список не должен содержать пустых ячеек.
Второй способ создания выпадающего списка в Excel более изящный и универсальный. Выделяете диапазон данных для выпадающего списка, затем нажимаете на пункт меню Формула — Диспетчер имен — Создать . Заполняете поле Имя , и копируете его (оно Вам понадобится позже). Имя должно начинаться с буквы или символа подчеркивания, и не должно содержать пробелов. Нажимаете ОК . Закрываете окно.
Затем выбираете ячейку, в которой будет выпадающий список Excel (можно сразу выделить несколько ячеек, если в них будут одинаковые выпадающие списки). После этого выбираете пункт меню Данные — Проверка данных , затем в окошке Тип данных выберите строку Список , в поле Источник поставьте знак равно, и без пробела вставьте то, что Вы копировали (значение поля Имя ). Не забудьте про знак = , иначе ничего не получится. Выглядит надпись в поле Источник примерно так: =Имя_диапазона . Нажимаете ОК .
Справа от ячейки появится значок выпадающего списка, в котором можно выбрать одно из значений. После выбора это значение появится в ячейке. Ячейку с выпадающим списком Excel можно сделать на другом листе, так, чтобы на одном листе были данные для списков, а на другом — ячейка с выпадающим списком, или несколько таких ячеек.
Это может быть опрос, тест, или анкета с заранее прописанными вариантами ответов. Можно, например, раздать файл Excel с анкетой группе людей, которых Вы хотите опросить, и получить назад уже файлы с ответами, или распечатанные ответы.
Для надежности можно лист с данными для выпадающих списков скрыть или защитить. Чтобы скрыть лист Excel , нажимаете на его название правой клавишей мыши, и выбираете Скрыть . Чтобы отобразить скрытые листы, нажимаете на название любого открытого листа Excel правой клавишей мыши, и выбираете Отобразить .
Чтобы защитить лист Excel , выбираете пункт меню Редактировать — Защитить лист , и прописываете пароль и действия, которые разрешены для пользователей.
Чтобы удалить из ячейки выпадающий список, выделяете ячейку, выбираете в меню Данные — Проверка данных , и нажимаете на кнопку Очистить все .
Выпадающие списки – очень удобный объект, который используется практически во всех приложениях. Кроме этого, они очень популярны на веб-страницах, поскольку намного проще данные выбирать, чем вносить их вручную. Особенно подобные формы востребованы при регистрации. Но их также часто используют и в Экселе. В данной статье мы рассмотрим, как сделать выпадающий список в редакторе Excel.
Для этой цели существует несколько способов. Рассмотрим их более внимательно. Первым делом необходимо создать таблицу с небольшим перечнем.
Для того чтобы произошла подстановка данных из таблицы, достаточно выполнить следующее.
Эту комбинацию можно будет использовать всегда. В дальнейшем необязательно вызывать контекстное меню.
Данный способ отлично работает и тогда, когда в таблице содержатся повторяющиеся значения. При этом в выпадающем списке будут только уникальные записи.
Обратите внимание на то, что этот метод не работает, если вы выберите клетку, выше которой нет никакой информации.
В этом случае необходимо:
Если вы сделаете активной другую ячейку, то иконка выпадающего списка исчезнет. Она отображается только тогда, когда эта клетка становится активной. Поэтому не стоит пугаться и думать, что у вас всё исчезло.
Для того чтобы использовать более продвинутые объекты, нужно использовать вкладку «Разработчик». По умолчанию она отключена. Для того чтобы её активировать, необходимо следовать следующей инструкции.
Для создания списков при помощи этого инструмента, необходимо сделать следующее:
Для того чтобы воспользоваться этим элементом, необходимо выполнить следующие операции.
При этом значения и свойства будут меняться в зависимости от того, какой элемент будет активен в данный момент. Здесь вы сможете изменить всё, даже шрифт.
Убрать объекты ActiveX довольно просто.
Для того чтобы создать двухуровневые списки, нужно выполнить несколько простых операций:
В графе «Тип данных» снова указываем «Список». В поле источник укажите следующую формулу.
=ДВССЫЛ(B11)В качестве аргумента указываем ссылку на ту ячейку, в которой у нас подгружается ассортимент товаров. Более подробно о ней можно прочитать на сайте Microsoft.
После нажатия на «OK» вы увидите ошибку источника данных. Ничего страшного тут нет. Кликните на «Да».
Дело в том, что в данный момент в клетке «Товар» у нас ничего не выбрано. Как только там будет какое-нибудь слово, нужный список автоматически загрузится.
В вышеописанном методе нужно было создавать дополнительные «имена» для каждого пункта. Но можно сделать и иначе. Представьте, что у вас на листе расположено огромное количество данных. Кроме этого, эта информация может меняться, дополняться или удаляться. Как быть в этом случае? Вручную каждый раз всё настраивать заново – очень плохая затея.
В подобных ситуациях нужно использовать динамическое построение выпадающих списков. Это делается следующим образом.
После сохранения появится ошибка о том, что источник пустой. Нажимаем на кнопку «Да».
Данный способ удобен, когда в таблице находится очень большое количество информации. При этом она может размещаться хаотично, а не последовательно, как в нашем примере.
Самое главное, чтобы значения в первом столбце писались одинаково. Достаточно допустить одну опечатку, и эта строка не попадет во вторую ячейку, поскольку происходит дословный поиск значений.
Для того чтобы сделать двойной выбор, нужно выполнить следующее.
Private Sub Worksheet_Change(ByVal Target As Range)
On Error Resume Next
If Not Intersect(Target, Range(«B11»)) Is Nothing And Target.Cells.Count = 1 Then
Application.EnableEvents = False
NewSelectWord = Target
Application.Undo
BeforeWord = Target
If Len(BeforeWord) <> 0 And BeforeWord <> NewSelectWord Then
Target = Target & «,» & NewSelectWord
Target = NewSelectWord
If Len(NewSelectWord) = 0 Then Target.ClearContents
Application.EnableEvents = True
Во всех остальных случаях значения будут только накапливаться.
В данной статье мы рассмотрели всевозможные способы для создания выпадающих списков в редакторе Excel. Если у вас что-то не получается, возможно, вы используете неподходящую таблицу. Еще раз внимательно посмотрите на используемые формулы, поскольку там могут быть опечатки. Также очень важно проверить правильность ссылок на ячейки. Если у вас отсутствует вкладка «Разработчик», значит, вы её неправильно активировали или же упустили этот момент из виду.
Некоторым пользователям намного проще воспринимать информацию при помощи видео. Именно для них мы подготовили специальный ролик, в котором описываются все ранее указанные методы.
Добрый день, уважаемый читатель!
В этой статье, я хотел бы поговорить о том, что такое выпадающий список в ячейке, как его сделать, ну и соответственно для чего же он нужен?
Это список закреплённых значений, которые доступны только с указанного диапазона значений. Это означает, что в указанную вами ячейку могут попасть данные только соответствующие значениям заданного диапазона, данные, которые не соответствуют – вводиться, не будут. В ячейке появляется возможность выбора значений, которые предлагает фиксированный список в ячейке.
Ну что же, рассмотрим создание выпадающих списков и для чего же это нужно:
Я лично постоянно использую выпадающий список по всем 3 причинам. И она значительно упрощает мне работу с данными, я сознательно сокращаю к 0% возможность при введении первичных данных.
Ну вот 2 вопроса, что и для чего, я рассказал, а вот о том, как это сделать ниже и поговорим.
А делать выпадающий список в ячейке будем в несколько этапов:
1. Определяем диапазон ячеек, в которых мы будем создавать фиксированный список.
2. Выделяем нужный нам диапазон и в меню выбираем пункт “Данные” — “Проверка данных” , в появившемся контекстном окне выбираем из указанного выбора пункт “Список” .
3. В разблокированной ниже строке указываем диапазон данных, которые должны быть у нас в выпадающем списке. Нажимаем “Ок” и работа сделана.
В более старых версиях Excel, нет возможности формировать выпадающий список в ячейкеиспользуя данные других листов, поэтому имеет смысл создавать списки в том же листе и прятать их . Также при необходимости вы можете сформировать с вертикального списка – горизонтальный с помощью возможности .
А на этом у меня всё! Я очень надеюсь, что всё вышеизложенное вам понятно. Буду очень благодарен за оставленные комментарии, так как это показатель читаемости и вдохновляет на написание новых статей! Делитесь с друзьями прочитанным и ставьте лайк!
Прогресс человечества основывается на желании каждого человека жить не по средствам
Сэмюэль Батлер, философ
Если Вы заполняете таблицу в Excel, и данные в столбце могут иногда повторяться, например, название товара, или имя сотрудника, то, чтобы не вводить нужный параметр каждый раз, проще и легче один раз создать выпадающий список и выбирать из него значение.
В статье мы рассмотрим, как сделать выпадающие списки различного вида в таблице Эксель.
Для этого, в ячейки А1:А7 вписываем данные, которые будут отображаться в списке. Теперь выделим ячейку, в которой создадим выпадающий список – В2.
Переходим на вкладку «Данные» и кликаем по кнопочке «Проверка данных».
На вкладке «Параметры» в поле «Тип данных» выбираем «Список». В поле «Источник» можно ввести значения различными способами:
1 – вводим значения для списка вручную, через точку с запятой;
2 – указываем диапазон ячеек, в которые введены данные для выпадающего списка;
3 – выделяем ячейки с именами, кликаем по ним правой кнопкой мыши и выбираем из меню «Присвоить имя».
Выделяем ячейку В2 и в поле «Источник» ставим «=», затем пишем созданное имя.
Таким образом, мы создали простой выпадающий список в Excel.
Если у Вас есть заголовок для столбца, и значениями нужно заполнять каждую строку, то выделите не одну ячейку, а диапазон ячеек – В2:В9. Тогда можно будет выбирать из выпадающего списка нужное значение в каждой ячейке.
При этом мы будем дописывать значения в нужный диапазон, а они будут автоматически добавляться в выпадающий список.
Выделяем диапазон ячеек – D1:D8, затем на вкладке «Главная» нажимаем «Форматировать как таблицу» и выбираем любой стиль.
Подтверждаем расположение данных и ставим галочку в поле «Таблица с заголовками».
Вверху пишем заголовок таблицы – «Сотрудники», и заполняем ее данными.
Выделяем ячейку, в которой будет выпадающий список и кликаем по кнопочке «Проверка данных». В следующем окне, в поле «Источник», пишем следующее: =ДВССЫЛ(«Таблица1»). У меня одна таблица на листе, поэтому пишу «Таблица1», если будет вторая – «Таблица2», и так далее.
Теперь добавим новое имя сотрудника в наш список: Ира. В выпадающем списке оно появилось. Если мы удалим любое имя из таблицы, из списка оно тоже удалится.
Если таблица с выпадающими списками находится на одном листе, а данные для этих списков – на другом, то данная функция нам очень поможет.
На Листе 2, выделяем одну ячейку или диапазон ячеек, затем кликаем по кнопочке «Проверка данных».
Переходим на Лист 1, ставим курсор в поле «Источник» и выделяем нужный диапазон ячеек.
Теперь можно дописывать имена на Листе 1, они будут добавляться в выпадающие списки на Листе 2.
Предположим, у нас есть три диапазона: имена, фамилии и отчества сотрудников. Для каждого, нужно присвоить имя. Выделяем ячейки оного диапазона, можно и пустые – в них со временем можно будет добавлять данные, которые будут появляться в выпадающем списке. Кликаем по ним правой кнопкой мыши и выбираем из списка «Присвоить имя».
Первый называем «Имя», второй – «Фамилия», третий – «Отч».
Сделаем еще один диапазон, в котором будут прописаны присвоенные имена. Назовем его «Сотрудники».
Делаем первый выпадающий список, который будет состоять из названия диапазонов. Выделяем ячейку Е1 и на вкладке «Данные» выбираем «Проверка данных».
В поле «Тип данных» выберите «Список», в поле источник – или введите «=Сотрудники», или выделите диапазон ячеек, которому присвоено имя.
Первый выпадающий список создан. Теперь в ячейке F2 создадим второй список, который должен зависеть от первого. Если в первом выберем «Имя», во втором отобразится список имен, если выберем «Фамилия» – список фамилий.
Выделяем ячейку и кликаем по кнопочке «Проверка данных». В поле «Тип данных» выбираем «Список», в поле источник прописываем следующее: =ДВССЫЛ($Е$1). Здесь Е1 – это ячейка с первым выпадающим списком.
По такому принципу можно делать зависимые выпадающие списки.
Если в дальнейшем, нужно будет вписать значения в диапазон, которому задано имя, например, «Фамилия». Перейдите на вкладку «Формулы» и кликните «Диспетчер имен». Теперь в имени диапазона выбираем «Фамилия», и внизу, вместо последней ячейки С3, напишите С10. Нажмите галочку. После этого диапазон увеличится, и в него можно будет дописывать данные, которые автоматически будут появляться в выпадающем списке.
Теперь Вы знаете, как сделать раскрывающийся список в Excel.
Как создать выпадающий список, состоящий сразу из нескольких ячеек (скажем чтобы наименование было со стоимостью)
Спасибо, всё получилось.
Выпадающий список со значениями с другого листа не работает, так как окно когда открыто проверка данных не дает работать с другими окнами, тем более с другим листом!
Зависимый выпадающий список позволяет сделать трюк, который очень часто хвалят пользователи шаблонов Excel. Трюк, который делает работу проще и быстрее. Трюк, благодаря которому ваши формы будут удобны и приятны.
Пример использования зависимого выпадающего списка для создания удобной формы заполнения документов, с помощью которых продавцы заказывали товары. Из всего ассортимента они должны были выбрать те продукты, которые они собирались продать.
Каждый продавец сначала определял товарную группу, а затем конкретный товар из этой группы. Форма должна включать полное имя группы и определенный индекс товара. Поскольку набирать это вручную было бы слишком трудоемким (и раздражающим) занятием, я предложил очень быстрое и простое решение - 2 зависимых выпадающих списка.
Первым был список всех категорий продуктов, второй - список всех продуктов, находящихся в выбранной категории. Поэтому я создал выпадающий список, зависимый от выбора, сделанного в предыдущем списке (здесь вы найдете материал о том, как создать два зависимых раскрывающихся списка).
Тот же самый результат хочет получить пользователь шаблона домашнего бюджета где нужна категория и подкатегория расходов. Пример данных находится на рисунке ниже:
Так, например, если мы выберем категорию Развлечения, то в списке подкатегорий должно быть: Кинотеатр, Театр, Бассейн. Очень быстрое решение, если в своем домашнем бюджете вы хотите проанализировать более подробную информацию.
Признаюсь, что в предложенном мной варианте домашнего бюджета я ограничиваюсь только категорией, поскольку для меня такого разделения расходов вполне достаточно (название расходов / доходов рассматривается как подкатегория). Однако, если вам нужно разделить их на подкатегории, то метод, который я описываю ниже, будет идеальным. Смело используйте!
А конечный результат выглядит следующим образом:
Для того чтобы этого достичь, необходимо сделать немного другую таблицу данных, чем если бы мы создавали один раскрывающийся список. Таблица должна выглядеть так (диапазон G2:H15):
В эту таблицу необходимо ввести категорию и рядом с ней ее подкатегории. Имя категории должно повторяться столько раз, сколько есть подкатегорий. Очень важно, чтобы данные были отсортированы по столбцу Категория. Это будет чрезвычайно важно, когда позже будем писать формулу.
Можно было бы также использовать таблицы с первого изображения. Разумеется, формулы были бы разными. Однажды даже я нашел в сети такое решение, но оно мне не понравилось, потому что там была фиксированная длина списка: а значит, иногда список содержал пустые поля, а иногда и не отображал все элементы. Конечно, я могу избежать этого ограничения, но признаюсь, что мне больше нравится мое решение, поэтому к тому решению я больше не возвращался.
Ну хорошо. Теперь, по очереди я опишу шаги создания зависимого выпадающего списка.
Это необязательный шаг, без него мы сможем без проблем справиться с этим. Однако мне нравится использовать имена, потому что они значительно облегчают как написание, так и чтение формулы.
Присвоим имена двум диапазонам. Список всех категорий и рабочий список категорий. Это будут диапазоны A3:A5 (список категорий в зеленой таблице на первом изображении) и G3:G15 (список повторяющихся категорий в фиолетовой рабочей таблице).
Для того чтобы назвать список категорий:
Такое же действие совершите для диапазона рабочего списка категорий G3:G15, который вы можете вызвать «Рабочий_Список». Этот диапазон мы будем использовать в формуле.
Это будет просто:
Результат следующий:
Раскрывающийся список для категории.
Сейчас будет весело. Создавать списки мы умеем - только что это сделали для категории. Только единственный вопрос: «Как сказать Excelю выбрать только те значения, которые предназначены для конкретной категории?» Как вы, наверное, догадываетесь, я буду использовать здесь рабочую таблицу и, конечно же, формулы.
Начнем с того, что мы уже умеем, то есть с создания раскрывающегося списка в ячейке B12. Поэтому выберите эту ячейку и нажмите «Данные» / «Проверка данных», а в качестве типа данных - «Список».
В источник списка введите следующую формулу:
Вид окна «Проверка вводимых значений»:
Как видите, весь трюк зависимого списка состоит в использовании функции СМЕЩ. Ну хорошо, почти весь. Помогают ей функции ПОИСКПОЗ и СЧЕТЕСЛИ. Функция СМЕЩ позволяет динамически определять диапазоны. Вначале мы определяем ячейку, от которой должен начинаться сдвиг диапазона, а в последующих аргументах определяем его размеры.
В нашем примере диапазон будет перемещаться по столбцу Подкатегория в рабочей таблице (G2:H15). Перемещение начнем от ячейки H2, которая также является первым аргументом нашей функции. В формуле ячейку H2 записали как абсолютную ссылку, потому что предполагаю, что мы будем использовать раскрывающийся список во многих ячейках.
Поскольку рабочая таблица отсортирована по Категории, то диапазон, который должен быть источником для раскрывающегося списка, будет начинаться там, где впервые встречается выбранная категория. Например, для категории Питание мы хотим отобразить диапазон H6:H11, для Транспорта - диапазон H12: H15 и т. д. Обратите внимание, что все время мы перемещаемся по столбцу H, а единственное, что изменяется, это начало диапазона и его высота (то есть количество элементов в списке).
Начало диапазона будет перемещено относительно ячейки H2 на такое количество ячеек вниз (по числу), сколько составляет номер позиции первой встречающейся категории в столбце Категория. Проще будет понять на примере: диапазон для категории Питание перемещен на 4 ячейки вниз относительно ячейки H2 (начинается с 4 ячейки от H2). В 4-ой ячейке столбца Подкатегория (не включая заголовок, так как речь идет о диапазоне с именем Рабочий_Список), есть слово Питание (его первое появление). Мы используем этот факт собственно для определения начала диапазона. Послужит нам для этого функция ПОИСКПОЗ (введенная в качестве второго аргумента функции СМЕЩ):
Высоту диапазона определяет функция СЧЕТЕСЛИ. Она считает все встречающиеся повторения в категории, то есть слово Питание. Сколько раз встречается это слово, сколько и будет позиций в нашем диапазоне. Количество позиций в диапазоне - это его высота. Вот функция:
Конечно же, обе функции уже включены в функцию СМЕЩ, которая описана выше. Кроме того, обратите внимание, что как в функции ПОИСКПОЗ, так и в СЧЕТЕСЛИ, есть ссылка на диапазон названный Рабочий_Список. Как я уже упоминал ранее, не обязательно использовать имена диапазонов, можно просто ввести $H3: $H15. Однако использование имен диапазонов в формуле делает ее проще и легко читаемой.
Вот и все:
Скачать пример зависимого выпадающего списка в Excel
Одна формула, ну не такая уж и простая, но облегчающая работу и защищает от ошибок при вводе данных!
Скачать файл, используемый в видеоуроке:
{"Bottom bar":{"textstyle":"static","textpositionstatic":"bottom","textautohide":true,"textpositionmarginstatic":0,"textpositiondynamic":"bottomleft","textpositionmarginleft":24,"textpositionmarginright":24,"textpositionmargintop":24,"textpositionmarginbottom":24,"texteffect":"slide","texteffecteasing":"easeOutCubic","texteffectduration":600,"texteffectslidedirection":"left","texteffectslidedistance":30,"texteffectdelay":500,"texteffectseparate":false,"texteffect1":"slide","texteffectslidedirection1":"right","texteffectslidedistance1":120,"texteffecteasing1":"easeOutCubic","texteffectduration1":600,"texteffectdelay1":1000,"texteffect2":"slide","texteffectslidedirection2":"right","texteffectslidedistance2":120,"texteffecteasing2":"easeOutCubic","texteffectduration2":600,"texteffectdelay2":1500,"textcss":"display:block; padding:12px; text-align:left;","textbgcss":"display:block; position:absolute; top:0px; left:0px; width:100%; height:100%; background-color:#333333; opacity:0.6; filter:alpha(opacity=60);","titlecss":"display:block; position:relative; font:bold 14px \"Lucida Sans Unicode\",\"Lucida Grande\",sans-serif,Arial; color:#fff;","descriptioncss":"display:block; position:relative; font:12px \"Lucida Sans Unicode\",\"Lucida Grande\",sans-serif,Arial; color:#fff; margin-top:8px;","buttoncss":"display:block; position:relative; margin-top:8px;","texteffectresponsive":true,"texteffectresponsivesize":640,"titlecssresponsive":"font-size:12px;","descriptioncssresponsive":"display:none !important;","buttoncssresponsive":"","addgooglefonts":false,"googlefonts":"","textleftrightpercentforstatic":40}}