Как сделать выборку в excel из списка
Выпадающий список – невероятно полезный инструмент, способный помочь сделать работу с информацией более комфортным. Он дает возможность вместить в ячейке сразу несколько значений, с которыми можно работать, как и с любыми другими. Чтобы выбрать нужное, достаточно нажать на значок стрелочки, после чего появляется перечень значений. После выбора определенного, ячейка автоматически заполняется им, и формулы пересчитываются, исходя из него.
Excel предусматривает множество различных методов генерации выпадающего меню, и кроме того, дает возможность гибко настраивать их. Давайте проанализируем эти методы более подробно.
Процесс создания списка
Чтобы сгенерировать всплывающее меню, следует кликнуть на пункты меню по пути «Данные» – «Проверка данных». Откроется диалоговое окошко, где надо найти вкладку «Параметры» и нажать по ней, если она до этого еще не была открыта. В нем есть множество настроек, но нам важен пункт «Тип данных». Из всех значений «Список» – это то, что надо.
Количество методов, которыми осуществляется ввод информации во всплывающий перечень, достаточно большое.
Любой из этих методов выдаст требуемый результат. Давайте разберем методы генерации раскрывающихся перечней в реальных ситуациях.
На основе данных из перечня
Допустим, у нас есть таблица, описывающая виды разных фруктов.
5
Для создания перечня в раскрывающемся меню, основываясь на этом наборе информации, нужно совершить такие действия:
- Выбрать ячейку, отведенную под будущий перечень.
- На ленте найти вкладку «Данные». Там осуществляем нажатие по «Проверка данных». 6
- Найти пункт «Тип данных» и переключить значение на «Список». 7
- В поле, обозначающем опцию «Источник», ввести нужный диапазон. Обратите внимание, что нужно указывать абсолютные ссылки, чтобы при копировании списка информация не смещалась. 8
Кроме этого, предусмотрена функция генерации списков сразу больше, чем в одной ячейке. Чтобы этого добиться, следует выбрать их все, и совершить аналогичные описанным раньше действия. Снова необходимо удостовериться, что записаны абсолютные ссылки. Если в адресе нет значка доллара возле названия колонки и ряда, то нужно их добавить путем нажатия клавиши F4 до тех пор, пока возле названия столбца и ряда не будет стоять знак $.
С ручной записью данных
В приведенной раньше ситуации перечень записывался путем выделения необходимого диапазона. Это удобный метод, но иногда необходимо вручную записывать данные. Это даст возможность избежать дублирования информации в рабочей книге.
Допустим, перед нами стоит задача создать список, содержащий два возможных варианта выбора: да и нет. Чтобы реализовать поставленную задачу, необходимо:
После нажатия «ОК» у нас появился следующий результат.
11
Далее программой будет автоматически создано раскрывающееся меню в подходящей ячейке. Вся информация, которую пользователь указал качестве пунктов всплывающего перечня. Правила создания перечня в нескольких ячейках аналогичные предыдущим за тем лишь исключением, что следует указывать информацию вручную с использованием точки с запятой.
Создание раскрывающегося списка при помощи функции СМЕЩ
Кроме классического метода возможно применение функции СМЕЩ , чтобы генерировать выпадающие меню.
12
Чтобы применять функцию для выпадающего списка надо выполнить такое:
- Выбрать интересующую ячейку, где надо разместить будущий список.
- Открыть последовательно вкладку «Данные» и окно «Проверка данных». 13
- Задаем «Список». Делается это аналогично предыдущим примерам. Наконец, используется такая формула: =СМЕЩ(A$2$;0;0;5). Мы ее вводим там, где задаются ячейки, которые будут использоваться в качестве аргумента.
Потом программой создастся меню с перечнем фруктов.
Синтаксис этой такой:
=СМЕЩ(ссылка;смещ_по_строкам;смещ_по_столбцам;[высота];[ширина])
Видим, что сия функция предусматривает 5 аргументов. Сначала дается первый адрес ячейки для смещения. Следующие два аргумента указывают, сколько рядов и колонок, на какое происходит смещение. Если говорить о нас, в качестве аргумента «Высота» приводится значение 5, поскольку оно отображает высоту перечня.
Выпадающий список в Excel с подстановкой данных (+ с использованием функции СМЕЩ)
В приведенном случае СМЕЩ позволила создать всплывающее меню, расположенное в фиксированном диапазоне. Недостаток этого метода – после добавления пункта придется самостоятельно редактировать формулу.
Чтобы создать динамический перечень с поддержкой ввода новой информации, необходимо:
- Осуществить выделение интересующей ячейки.
- Раскрыть вкладку «Данные» и нажать по «Проверка данных».
- В открывшемся окошке снова осуществляем выбор пункта «Список» и источником данных указываем такую формулу: =СМЕЩ(A$2$;0;0;СЧЕТЕСЛИ($A$2:$A$100;”<>”))
- Нажимаем «ОК».
Здесь содержится функция СЧЕТЕСЛИ , чтобы сразу определять, сколько ячеек заполнено (хотя у нее есть значительно большее количество применений, просто мы записываем ее здесь для конкретной цели).
Чтобы формула функционировала нормально, надо проследить есть ли на пути формулы пустые ячейки. Их быть не должно.
Выпадающий список с данными другого листа или файла Excel
Классический метод не функционирует, если требуется получить информацию из другого документа или даже содержащегося в этом же файле листа. Для этого используется функция ДВССЫЛ , позволяющая ввести в верном формате ссылку на ячейку, находящуюся в другом листе или вообще – файле. Необходимо выполнить такие действия:
- Активировать ячейку, где размещаем перечень.
- Открываем уже знакомое нам окно. В том же месте, где мы ранее указывали источники на другие диапазоны, указывается формула в формате =ДВССЫЛ(“[Список1.xlsx]Лист1!$A$1:$A$9”) . Естественно, вместо Список1 и Лист1 можно вставлять свои имена книги и листа соответственно.
Внимание! Имя файла указывается в квадратных скобках. При этом Excel не сможет применять в качестве источника информации файл, закрытый в данный момент.
Также следует отметить, что название файла сам по себе есть смысл указывать только если требуемый документ располагается в той же папке, что и та, где будет вставляться перечень. Если нет, то необходимо полностью указывать адрес этого документа.
Создание зависимых выпадающих списков
Зависимый список – это тот, на содержимое которого влияет выбор пользователя в другом перечне. Допустим, перед нами открыта таблица, содержащая три диапазона, каждому из которых присвоено имя.
24
Нужно действовать по таким шагам для генерации перечней, на результат которых влияет опция, выбранная в другом списке.
- Создать 1-й перечень с именами диапазонов. 25
- В месте ввода источника один за одним выделяются требуемые показатели. 26
- Создать 2-й перечень, зависящий от типа растений, который предпочел человек. Как вариант, если в первом указать деревья, то информацией во втором списке станет «дуб, граб, каштан» и дальше. Необходимо записать в месте ввода источника данных формулу =ДВССЫЛ(E3). E3 – ячейка содержащая название диапазона 1.=ДВССЫЛ(E3). E3 – ячейка с наименованием списка 1.
Теперь все готово.
27
Как выбрать несколько значений из выпадающего списка?
Иногда нет возможности отдать предпочтение только одному значению, поэтому надо выбрать больше одного. Тогда надо добавить в код страницы макрос. С использованием комбинации клавиш Alt + F11 открывается редактор Visual Basic. И туда вставляется код.
Private Sub Worksheet_Change(ByVal Target As Range)
On Error Resume Next
If Not Intersect(Target, Range(«Е2:Е9»)) Is Nothing And Target.Cells.Count = 1 Then
Application.EnableEvents = False
If Len(Target.Offset(0, 1)) = 0 Then
Target.Offset(0, 1) = Target
Target.End(xlToRight).Offset(0, 1) = Target
Target.ClearContents
Application.EnableEvents = True
Чтобы содержимое ячеек показывались внизу, вставляем в редактор такой код.
Private Sub Worksheet_Change(ByVal Target As Range)
On Error Resume Next
If Not Intersect(Target, Range(«Н2:К2»)) Is Nothing And Target.Cells.Count = 1 Then
Application.EnableEvents = False
If Len(Target.Offset(1, 0)) = 0 Then
Target.Offset(1, 0) = Target
Target.End(xlDown).Offset(1, 0) = Target
Target.ClearContents
Application.EnableEvents = True
Ну и наконец, для записи в одной ячейке используется этот код.
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
Target = newVal
If Len(newVal) = 0 Then Target.ClearContents
Application.EnableEvents = True
Как сделать выпадающий список с поиском?
В этом случае надо изначально использовать другой тип перечня. Открывается вкладка «Разработчик», после чего надо кликнуть или тапнуть (если экран сенсорный) на элемент «Вставить» – «ActiveX». Там есть «Поле со списком». Будет предложено нарисовать этот список, после чего он добавится в документ.
Далее он настраивается через свойства, где в опции ListFillRange прописывается диапазон. Ячейка, где отобразиться определенное пользователем значение, настраивается с помощью опции LinkedCell. Далее нужно просто записывать первые символы, как программа автоматически подскажет возможные значения.
Выпадающий список с автоматической подстановкой данных
Также предусмотрена функция, что данные подставляются автоматически после их добавления к диапазону. Сделать это проще простого:
Далее нужно подтвердить этот диапазон путем нажатия клавиши «ОК».
17
Выделяем получившуюся таблицу и даем ей имя через поле ввода, находящееся сверху столбца А.
18
Все, таблица есть, и она может использоваться в качестве основы для выпадающего списка, для чего надо:
Все, ячейка готова, и в ней показываются названия цветов, как нам изначально и было нужно. Теперь добавлять новые позиции можно просто записывая их в ячейку, располагающуюся немного ниже непосредственно за последней.
22
В этом и заключается преимущество таблицы, что диапазон автоматически увеличивается при добавлении новых данных. Соответственно, это самый удобный способ добавления списка.
23
Как скопировать выпадающий список?
Для копирования достаточно использовать комбинацию клавиш Ctrl + C и Ctrl + V. Так выпадающий список будет скопирован вместе с форматированием. Чтобы убрать форматирование, нужно воспользоваться специальной вставкой (в контекстном меню такая опция появляется после копирования списка), где выставляется опция «условия на значения».
Выделение всех ячеек, содержащих выпадающий список
Чтобы выполнить эту задачу, необходимо воспользоваться функцией «Выделить группу ячеек» в группе «Найти и выделить».
29
После этого откроется диалоговое окно, где следует в меню «Проверка данных» выбрать пункты «Всех» и «Этих же». Первый пункт выделяет все списки, а второй – только похожие на определенные.
При работе с таблицами Excel довольно часто приходится проводить отбор в них по определенному критерию или по нескольким условиям. В программе сделать это можно различными способами при помощи ряда инструментов. Давайте выясним, как произвести выборку в Экселе, используя разнообразные варианты.
Выполнение выборки
Выборка данных состоит в процедуре отбора из общего массива тех результатов, которые удовлетворяют заданным условиям, с последующим выводом их на листе отдельным списком или в исходном диапазоне.
Способ 1: применение расширенного автофильтра
Наиболее простым способом произвести отбор является применение расширенного автофильтра. Рассмотрим, как это сделать на конкретном примере.
-
Выделяем область на листе, среди данных которой нужно произвести выборку. Во вкладке «Главная» щелкаем по кнопке «Сортировка и фильтр». Она размещается в блоке настроек «Редактирование». В открывшемся после этого списка выполняем щелчок по кнопке «Фильтр».
- равно;
- не равно;
- больше;
- больше или равно;
- меньше.
При использовании любого из двух вышеуказанных методов фильтрация будет удалена, а результаты выборки – очищены. То есть, в таблице будет показан весь массив данных, которыми она располагает.
Способ 2: применение формулы массива
Сделать отбор можно также применив сложную формулу массива. В отличие от предыдущего варианта, данный метод предусматривает вывод результата в отдельную таблицу.
-
На том же листе создаем пустую таблицу с такими же наименованиями столбцов в шапке, что и у исходника.
Опять набираем сочетание клавиш Ctrl+Shift+Enter.
Теперь у нас имеется готовая выборка по указанному ограничению в отдельной надлежащим образом оформленной таблице.
Способ 3: выборка по нескольким условиям с помощью формулы
Так же, как и при использовании фильтра, с помощью формулы можно осуществлять выборку по нескольким условиям. Для примера возьмем всю ту же исходную таблицу, а также пустую таблицу, где будут выводиться результаты, с уже выполненным числовым и условным форматированием. Установим первым ограничением нижнюю границу отбора по выручке в 15000 рублей, а вторым условием верхнюю границу в 20000 рублей.
-
Вписываем в отдельном столбце граничные условия для выборки.
В последующие колонки вписываем точно такие же формулы, только изменив координаты сразу после наименования оператора ИНДЕКС на соответствующие нужным нам столбцам, по аналогии с предыдущим способом.
Способ 4: случайная выборка
В Экселе с помощью специальной формулы СЛЧИС можно также применять случайный отбор. Его требуется производить в некоторых случаях при работе с большим объемом данных, когда нужно представить общую картину без комплексного анализа всех данных массива.
-
Слева от таблицы пропускаем один столбец. В ячейке следующего столбца, которая находится напротив первой ячейки с данными таблицы, вписываем формулу:
Как видим, выборку в таблице Excel можно произвести, как с помощью автофильтра, так и применив специальные формулы. В первом случае результат будет выводиться в исходную таблицу, а во втором – в отдельную область. Имеется возможность производить отбор, как по одному условию, так и по нескольким. Кроме того, можно осуществлять случайную выборку, использовав функцию СЛЧИС.
Мы рады, что смогли помочь Вам в решении проблемы.
Отблагодарите автора, поделитесь статьей в социальных сетях.
Опишите, что у вас не получилось. Наши специалисты постараются ответить максимально быстро.
Помогла ли вам эта статья?
Еще статьи по данной теме:
Все это конечно здорово.
Фильтр ставится и по другому быть не может.
Однако не понятно как работать с этими данными.
Допустим я дошел до пункта 6. и у меня набор выручек подходящих по условиям. теперь мне нужно сосчитать какова сумма выручек ЭТОЙ ВЫБОРКИ, какая среднедневная выручка ЭТОЙ ВЫБОРКИ и каков средний вес ЭТОЙ ВЫБОРКИ в общей сумме выручек.
При попытке высчитать сумму выручек ЭТОЙ ВЫБОРКИ — стандартными средствами( формула сумм)- получаем просто сумму ВСЕХ выручек.
о следующих шагах говорить вообще не имеет смысла тогда.
Спасибо очень занимательно, а то я вечно забываю как ставить фильтр в таблицах)
Создание ниспадающих списков позволяет не только экономить время при выборе варианта в процессе заполнения таблиц, но и оградить себя от ошибочного внесения некорректных данных. Это очень удобный и практичный инструмент. Давайте выясним, как активировать его в Excel, и как им пользоваться, а также узнаем некоторые другие нюансы обращения с ним.
Использование выпадающих списков
Ниспадающие, или как принято говорить, раскрывающиеся списки чаще всего применяют в таблицах. С их помощью можно ограничить круг вносимых в табличный массив значений. Они позволяют выбирать для внесения значения только из заранее подготовленного перечня. Это одновременно ускоряет процедуру внесения данных и оберегает от ошибки.
Процедура создания
Прежде всего, давайте выясним, как создать ниспадающий список. Легче всего это сделать при помощи инструмента под названием «Проверка данных».
-
Выделяем столбец табличного массива, в ячейках которого планируется разместить выпадающий список. Передвигаемся во вкладку «Данные» и клацаем по кнопке «Проверка данных». Она локализирована на ленте в блоке «Работа с данными».
Если выбран ввод вручную, то каждый списочный элемент требуется вписать в область через точку с запятой (;).
Если вы хотите подтянуть данные из уже существующего табличного массива, то следует перейти на лист, где тот расположен (если он размещен на другом), поставить курсор в область «Источник» окна проверки данных, а затем выделить массив ячеек, где находится перечень. Важно, чтобы в каждой отдельной ячейке располагался отдельный списочный элемент. После этого координаты указанного диапазона должны отобразиться в области «Источник».
Ещё один вариант установки связи – это присвоение массиву с перечнем имени. Выделяем диапазон, в котором указаны значения данных. Слева от строки формул находится область имён. По умолчанию в нём при выделении диапазона отображается координаты первой выделенной ячейки. Мы же для наших целей просто вписываем туда наименование, которое считаем более подходящим. Главные требования к имени состоят в том, чтобы оно было уникальным в пределах книги, не имело пробелов и обязательно начиналось с буквы. Теперь именно по данному наименованию будет идентифицироваться тот диапазон, который мы до этого выделили.
Теперь в окне проверки данных в области «Источник» нужно установить символ «=», а затем сразу же после него вписать то имя, которое мы присвоили диапазону. Программа сразу идентифицирует связь между наименованием и массивом, и подтянет тот перечень, который в нем расположен.
Но намного эффективнее получится использовать перечень, если его преобразовать в «умную» таблицу. В такой таблице будет легче менять значения, тем самым автоматически изменяя списочные элементы. Таким образом, данный диапазон фактически превратится в таблицу подстановок.
Для того, чтобы преобразовать диапазон в «умную» таблицу, выделяем его и передвигаемся во вкладку «Главная». Там клацаем по кнопке «Форматировать как таблицу», которая размещена на ленте в блоке «Стили». Открывается большая группа стилей. На функциональность таблицы выбор конкретного стиля никак не влияет, а поэтому выбираем любой из них.
После этого открывается небольшое окошко, в котором содержится адрес выделенного массива. Если выделение было выполнено верно, то тут ничего изменять не нужно. Так как у нашего диапазона нет заголовков, то у пункта «Таблица с заголовками» галочки быть не должно. Хотя конкретно в вашем случае, возможно, заголовок и будет применяться. Так что нам остается просто нажать на кнопку «OK».
После этого диапазон будет отформатирован, как таблица. Если его выделить, то можно заметить в области имён, что наименование ему было присвоено автоматически. Это наименование можно использовать для вставки в область «Источник» в окне проверки данных по описанному ранее нами алгоритму. Но, если вы хотите использовать другое название, то его можно заменить, просто вписав в область имен.
Выполнение операций
Теперь давайте разберемся, как работать с тем инструментом, который мы выше создали.
Таким способом при необходимости заполняем всю таблицу.
Добавление нового элемента
Но что делать, если требуется все-таки добавить новый элемент? Действия тут зависят от того, как именно вы сформировали перечень в окне проверки данных: введен вручную или подтягивается из табличного массива.
-
Если данные для формирования списка подтягиваются из табличного массива, то переходим к нему. Выделяем ячейку диапазона. Если это не «умная» таблица, а простой диапазон данных, то нужно вставить строку в середину массива. Если же применяете «умную» таблицу, то в этом случае достаточно просто вписать требуемое значение в первой строке под ней и данная строка тут же будет включена в табличный массив. Это как раз то преимущество «умной» таблицы, о котором мы упоминали выше.
Но что делать, если перечень значений подтягивается не из отдельной таблицы, а был внесен вручную? Для добавления элемента в этом случае тоже есть свой алгоритм действий.
-
Выделяем весь табличный диапазон, в элементах которого размещен выпадающий перечень. Переходим во вкладку «Данные» и снова кликаем по кнопке «Проверка данных» в группе «Работа с данными».
Удаление элемента
Удаление списочного элемента проводится по точно такому же алгоритму, что и добавление.
-
Если данные подтягиваются из табличного массива, то тогда переходим к этой таблице и клацаем правой кнопкой мыши по ячейке, где расположено значение, которое следует удалить. В контекстном меню останавливаем выбор на варианте «Удалить…».
Что же делать, если значения добавлялись в окно проверки данных вручную, а не при помощи дополнительной таблицы?
-
Выделяем табличный диапазон с раскрывающимся перечнем и переходим в окошко проверки значений, как это мы уже делали ранее. В указанном окне перемещаемся в раздел «Параметры». В области «Источник» выделяем курсором то значение, которое требуется удалить. Затем жмем на кнопку Delete на клавиатуре.
Полное удаление
В то же время, существуют и ситуации, когда выпадающий список нужно полностью удалить. Если вам не важно, чтобы введенные данные были сохранены, то произвести удаление очень просто.
-
Выделяем весь массив, где расположен раскрывающийся перечень. Передвигаемся во вкладку «Главная». Щелкаем по иконке «Очистить», которая размещается на ленте в блоке «Редактирование». В открывшемся меню выбираем позицию «Очистить все».
Кроме того, если пользователю не нужно сохранять введенные данные, то существует ещё один вариант удалить ниспадающий перечень.
-
Выделяем диапазон пустых ячеек, который равнозначен диапазону элементов массива с раскрывающимся перечнем. Передвигаемся во вкладку «Главная» и там кликаем по иконке «Копировать», которая локализируется на ленте в области «Буфер обмена».
Также вместо этого действия можно кликнуть по обозначенному фрагменту правой кнопкой мышки и остановиться на варианте «Копировать».
Второй вариант действий – это щелкнуть по выделению правой кнопкой мышки и остановить выбор на варианте «Вставить» в группе «Параметры вставки».
При желании таким же образом можно вставить не пустой диапазон, а скопированный фрагмент с данными. Недостаток ниспадающих списков как раз состоит в том, что в них нельзя вручную вписать данные, отсутствующие в перечне, но их можно скопировать и вставить. При этом проверка данных не сработает. Более того, как мы выяснили, сама структура выпадающего списка будет уничтожена.
Зачастую, требуется все-таки убрать выпадающий список, но при этом оставить те значения, которые с помощью него были введены, и форматирование. В этом случае следует произвести более корректные действия по удалению указанного инструмента заполнения.
-
Выделяем весь фрагмент, в котором расположены элементы с раскрывающимся перечнем. Передвигаемся во вкладку «Данные» и клацаем по значку «Проверка данных», который, как мы помним, размещен на ленте в группе «Работа с данными».
Как видим, ниспадающий список может значительно облегчить введение данных в таблицы, а также предотвратить введение некорректных значений. Это позволит сократить количество ошибок при заполнении таблиц. Если же какое-нибудь значение нужно добавить дополнительно, то всегда можно провести процедуру редактирования. Вариант редактирования будет зависеть от способа создания. После заполнения таблицы можно удалить выпадающий список, хотя делать это и не обязательно. Большинство пользователей предпочитают его оставлять даже после окончания работы по заполнению таблицы данными.
Мы рады, что смогли помочь Вам в решении проблемы.
Отблагодарите автора, поделитесь статьей в социальных сетях.
Опишите, что у вас не получилось. Наши специалисты постараются ответить максимально быстро.
При работе в программе Microsoft Excel в таблицах с повторяющимися данными, очень удобно использовать выпадающий список. С его помощью можно просто выбирать нужные параметры из сформированного меню. Давайте выясним, как сделать раскрывающийся список различными способами.
Создание дополнительного списка
Самым удобным, и одновременно наиболее функциональным способом создания выпадающего списка, является метод, основанный на построении отдельного списка данных.
Прежде всего, делаем таблицу-заготовку, где собираемся использовать выпадающее меню, а также делаем отдельным списком данные, которые в будущем включим в это меню. Эти данные можно размещать как на этом же листе документа, так и на другом, если вы не хотите, чтобы обе таблице располагались визуально вместе.
Выделяем данные, которые планируем занести в раскрывающийся список. Кликаем правой кнопкой мыши, и в контекстном меню выбираем пункт «Присвоить имя…».
Открывается форма создания имени. В поле «Имя» вписываем любое удобное наименование, по которому будем узнавать данный список. Но, это наименование должно начинаться обязательно с буквы. Можно также вписать примечание, но это не обязательно. Жмем на кнопку «OK».
Переходим во вкладку «Данные» программы Microsoft Excel. Выделяем область таблицы, где собираемся применять выпадающий список. Жмем на кнопку «Проверка данных», расположенную на Ленте.
Открывается окно проверки вводимых значений. Во вкладке «Параметры» в поле «Тип данных» выбираем параметр «Список». В поле «Источник» ставим знак равно, и сразу без пробелов пишем имя списка, которое присвоили ему выше. Жмем на кнопку «OK».
Выпадающий список готов. Теперь, при нажатии на кнопку у каждой ячейки указанного диапазона будет появляться список параметров, среди которых можно выбрать любой для добавления в ячейку.
Создание выпадающего списка с помощью инструментов разработчика
Второй способ предполагает создание выпадающего списка с помощью инструментов разработчика, а именно с использованием ActiveX. По умолчанию, функции инструментов разработчика отсутствуют, поэтому нам, прежде всего, нужно будет их включить. Для этого, переходим во вкладку «Файл» программы Excel, а затем кликаем по надписи «Параметры».
В открывшемся окне переходим в подраздел «Настройка ленты», и ставим флажок напротив значения «Разработчик». Жмем на кнопку «OK».
После этого, на ленте появляется вкладка с названием «Разработчик», куда мы и перемещаемся. Чертим в Microsoft Excel список, который должен стать выпадающим меню. Затем, кликаем на Ленте на значок «Вставить», и среди появившихся элементов в группе «Элемент ActiveX» выбираем «Поле со списком».
Кликаем по месту, где должна быть ячейка со списком. Как видите, форма списка появилась.
Затем мы перемещаемся в «Режим конструктора». Жмем на кнопку «Свойства элемента управления».
Открывается окно свойств элемента управления. В графе «ListFillRange» вручную через двоеточие прописываем диапазон ячеек таблицы, данные которой будут формировать пункты выпадающего списка.
Далее, кликаем по ячейке, и в контекстном меню последовательно переходим по пунктам «Объект ComboBox» и «Edit».
Выпадающий список в Microsoft Excel готов.
Чтобы сделать и другие ячейки с выпадающим списком, просто становимся на нижний правый край готовой ячейки, нажимаем кнопку мыши, и протягиваем вниз.
Связанные списки
Также, в программе Excel можно создавать связанные выпадающие списки. Это такие списки, когда при выборе одного значения из списка, в другой графе предлагается выбрать соответствующие ему параметры. Например, при выборе в списке продуктов картофеля, предлагается выбрать как меры измерения килограммы и граммы, а при выборе масла растительного – литры и миллилитры.
Прежде всего, подготовим таблицу, где будут располагаться выпадающие списки, и отдельно сделаем списки с наименованием продуктов и мер измерения.
Присваиваем каждому из списков именованный диапазон, как это мы уже делали ранее с обычными выпадающими списками.
В первой ячейке создаём список точно таким же образом, как делали это ранее, через проверку данных.
Во второй ячейке тоже запускаем окно проверки данных, но в графе «Источник» вводим функцию «=ДВССЫЛ» и адрес первой ячейки. Например, =ДВССЫЛ($B3).
Как видим, список создан.
Теперь, чтобы и нижние ячейки приобрели те же свойства, как и в предыдущий раз, выделяем верхние ячейки, и при нажатой клавише мышки «протаскиваем» вниз.
Всё, таблица создана.
Мы разобрались, как сделать выпадающий список в Экселе. В программе можно создавать, как простые выпадающие списки, так и зависимые. При этом, можно использовать различные методы создания. Выбор зависит от конкретного предназначения списка, целей его создания, области применения, и т.д.
Мы рады, что смогли помочь Вам в решении проблемы.
Отблагодарите автора, поделитесь статьей в социальных сетях.
Опишите, что у вас не получилось. Наши специалисты постараются ответить максимально быстро.
Суть запроса на выборку – выбрать из исходной таблицы строки, удовлетворяющие определенным критериям (подобно применению фильтра ). В отличие от фильтра отобранные строки будут помещены в отдельную таблицу.
Пусть имеется таблица с перечнем сотрудников (см. лист Сотрудники в файле примера ). Все сотрудники работают в дирекциях, а дирекции состоят из отделов и руководителей дирекций. В каждой строке таблицы содержится фамилия сотрудника, номер его телефона, номер его комнаты и наименование подразделения, к которому он относится.
Задача
Необходимо отобразить всех сотрудников выбранного отдела.
Решение с помощью стандартного фильтра
Это можно легко сделать с помощью стандартного фильтра EXCEL. Выделите заголовки таблицы и нажмите CTRL+SHIFT+L . Через выпадающий список у заголовка Отделы выберите нужный отдел и нажмите ОК.
Будут отображены все сотрудники выбранного отдела.
Решение с помощью трехуровневого Связанного списка
Другим подходом является использование трехуровневого Связанного списка с использованием элементов управления формы , где из исходной таблицы Сотрудники последовательно выбирая Дирекцию и Отдел, можно быстро отобразить всех сотрудников соответствующего отдела в отдельной таблице.
Преимущества использования трехуровневого Связанного списка – субъективны. Кому-то нравится работать с фильтром, кому-то со списками. Работать со списками несколько быстрее и информативнее (выбрав дирекцию, автоматически получим список всех ее отделов). Кроме того, в отличие от фильтра отобранные строки будут помещены в отдельную таблицу - своеобразный отчет, который можно форматировать в стиль отличный от исходной таблицы. В этот отчет можно вынести не все столбцы, а только нужные (хотя после применения фильтра ненужные столбцы можно скрыть).
Основной недостаток – сложность реализации трехуровневого Связанного списка . Но, единожды его создав и поняв принцип работы, этот недостаток в достаточной мере компенсируется.
Алгоритм создания запроса на выборку следующий:
ШАГ 1
Сначала создадим Лист Списки , в котором будут содержаться перечень дирекций и названия отделов (см. файл примера ).
Перечень дирекций (столбец А ) будет извлекаться формулой массива из исходной таблицы с перечнем сотрудников:
Подробности работы этой формулы можно прочитать в статье Отбор уникальных значений .
Перечень отделов (диапазон B 2: E 8 ) будет извлекаться аналогичной формулой массива в соответствующие столбцы на Листе Списки :
=ЕСЛИОШИБКА(ИНДЕКС(Сотрудники[Отдел]; ПОИСКПОЗ(0;ЕСЛИ(B$1=Сотрудники[Дирекция];0;1)+ СЧЁТЕСЛИ($B$1:B1;Сотрудники[Отдел]);0));"")
ШАГ 2
Теперь создадим Лист Просмотр , в котором будут содержаться перечень сотрудников выбранного отдела и два списка (дирекции и отделы), сформированных на основе Элемента управления форм Список .
Первый список создадим для вывода перечня дирекций. Источником строк для него будет созданный ранее динамический диапазон Дирекции. Свяжем его с ячейкой А1 .
Теперь создадим Динамический диапазон Выбранная_дирекция , который будет содержать название выбранной дирекции:
Также создадим Динамический диапазон Отделы , который будет содержать перечень отделов выбранной дирекции и служить источником строк для второго списка:
И, наконец, для вывода фамилий сотрудников (ячейка B 6 ), их номеров телефонов и комнат используем зубодробительную формулу:
Читайте также: