Работа в Excel с таблицами и данными в них выстроена таким образом, чтобы пользователь мог комфортно обрабатывать и анализировать их. Для этого в программу встроены различные инструменты . Их использование предполагает наличие у пользователя некоторых знаний, но с ними Excel превращается в мощнейшее средство анализа. Разработчик Office старается большинство своих программ упростить так, чтобы ими мог полноценно пользоваться любой человек.
Электронную таблицу можно превратить в инструмент для анализа данных
Одной из самых распространённых причин для создания всплывающего списка является использование данных из ячейки в формуле Excel. Предусмотреть конечное количество вариантов проще, поэтому целесообразно будет дать выбор из нескольких значений, чтобы пользователь мог выбрать из готового набора . Кроме того, может быть ещё и другая причина: заранее заданный стиль документа. Например, для отчётов или других официальных документов. Одно и то же название отдела можно написать по-разному. Если этот документ позже будет обрабатываться машиной, более правильно будет использовать единый стиль заполнения, а не ставить перед ней задачу распознавания, к примеру, по ключевым словам. Это может внести элемент неточности в её работу.
Техническая сторона вопроса
Перед тем, как сделать выпадающий список в Excel, сформируйте на листе в диапазоне ячеек необходимые варианты. Проследите за тем, чтобы в этом перечне не было пустых строк, иначе Эксель не сможет создать нужный объект на листе. Введённые значения в строках можно отсортировать по алфавиту. Для этого найдите в Ленте настроек вкладку данные и нажмите на «Сортировку». Когда работа с данными у вас закончится, выделите нужный диапазон. В нём не должно быть пустых строк, это важно! Программа не сможет создать список с пустым элементом внутри себя, потому что пустая строка не будет восприниматься в качестве данных для выбора. При этом перечень данных вы можете сформировать и на другом листе, не только на том, где будет располагаться поле ввода. Допустим, вы не хотите, чтобы они были доступны для редактирования другим пользователям. Тогда имеет смысл расположить их на скрытом листе.
После того, как вы сформировали перечень данных, выделите ячейку, в которой должен быть создан выпадающий список. В Ленте настроек Excel на вкладке «Данные» найдите кнопку «Проверка». При нажатии на неё откроется диалоговое окно. В нём вам нужно выбрать пункт «Разрешить» и установить его значение на «Список». Так в этой ячейке способ ввода будет изменён на выбор из доступных вариантов. Но пока что эти варианты не определены. Для того, чтобы добавить их в созданный объект, в поле «Источник» введите диапазон данных. Чтобы не впечатывать их вручную, нажмите на значок ввода в правой части поля, тогда окно свернётся, и вы привычным выделением мышкой сможете выбрать нужные ячейки. Как только вы отпустите левую кнопку мыши, окно откроется снова. Осталось нажать ОК, и в выделенной ячейке появится треугольник, значок выпадающего списка. Нажав на него, вы получите перечень вариантов, введённых вами ранее. После этого, если варианты расположены на отдельном листе, его можно скрыть, кликнув правой кнопкой мыши на его название внизу рабочего окна и выбрав одноимённый пункт в контекстном меню.
При выделении этой ячейки рядом с ней появятся несколько кнопок. Чтобы упростить пользователю задачу ввода, вы можете с помощью этой кнопки задать имя ячейки. То же самое вы можете сделать выше, рядом с окном ввода формул есть соответствующий пункт. Так список будет понятнее, ведь пользователю не придётся гадать по его значениям, что именно тут нужно выбрать. Кроме того, в диалоговом окне можно внести сообщение-подсказку, которое будет отображено при наведении курсора на ячейку. Если ячейка не должна оставаться пустой, уберите галочку «Игнорировать пустые значения». Флажок «Список допустимых значений» должен быть установлен в любом случае.
Удаление списка
Когда выпадающий список больше не нужен, его можно удалить из документа. Для этого выделите ячейку на листе Excel, содержащую его, и перейдите в Ленте настроек на вкладку «Данные» - «Проверка данных». Там во вкладке параметров нажмите на кнопку «Очистить всё». Объект будет удалён, но при этом диапазон данных останется без изменений, то есть значения не будут удалены.
Если Вам приходится работать с книгой Excel, в которой постоянно фигурирует один и тот же список данных, или может быть Вы просто не хотите множество раз использовать действие Копировать > Вставить , то было бы очень удобно иметь заранее настроенный и сохранённый список, чтобы Excel мог помочь Вам в работе. Использование настраиваемых списков – это хорошее решение для данной ситуации. Далее я покажу, как создать настраиваемый список в Excel.
Создаем пользовательский список в Excel
Я продемонстрирую это на примере списка школьных клубов. Мне нужно вести учёт денег на балансе каждого из них, а также наметить ежедневный бюджет. Я начал с того, что ввёл названия клубов, – это та информация, которая мне будет нужна постоянно.
В этот момент я бы также запустил проверку орфографии, это делается простым нажатием F7 .
Затем я выделил эту область, нажал на кнопку Office и в нижней части открывшегося меню щелкнул по Excel Options (Параметры Excel).
В разделе Popular (Общие) Вы найдёте пункт Create lists for use in sorts and fills sequences (Создавать списки для сортировки и заполнения) – кликните по Edit Custom Lists (Изменить списки).
Если Вы работаете в Excel 2010, то Вам нужен другой маршрут. Откройте вкладку File (Файл) и нажмите Options (Параметры). Затем пролистайте вниз, найдите кнопку Edit Custom Lists (Изменить списки).
Далее откроется диалоговое окно, в котором можно добавить информацию, которую планируется использовать многократно. Если Вы посмотрите на списки, уже подготовленные Microsoft, то увидите среди них те, которыми все так часто пользуются. Они не могут быть изменены или удалены. Однако, если Вы захотите удалить или изменить созданный Вами список, это можно сделать в любой момент. Для этого кликните по своему списку и делайте с ним что захотите.
Создаем список с нуля
У меня есть два способа, как создать настраиваемый список. Я могу создать его вручную, введя каждое значение в поле List entries (Элементы списка) и нажав Add (Добавить). Если Вы выберете этот путь, то столкнетесь с некоторыми ограничениями. Поле List entries (Элементы списка) позволяет ввести не более 255 символов. Будьте внимательны к тому, из скольких символов состоит каждая Ваша запись!
Подсказка: Если вы планируете вводить список в поле List entries (Элементы списка) вручную, не ставьте лишние пробелы между элементами. Если пробелы будут стоять до или после элемента, то Microsoft их просто не станет учитывать, а если между словами одного элемента, то все они будут сохранены.
Создание списка из существующего диапазона данных
Другой способ добавить элементы в настраиваемый список – импортировать данные. Если Вы выделите их прежде чем откроете окно меню, выбранный диапазон будет автоматически вставлен в соответствующее поле. Вам останется только нажать Import (Импорт) и Excel создаст список из содержащегося в ячейках текста. Если Вы не выделили текст заранее, поставьте курсор в поле рядом с кнопкой Import (Импорт) и выделите ячейки с данными для нового списка.
Помните, у нас было ограничение на количество символов, которое можно ввести в поле List entries (Элементы списка)? Только не при импорте! Теперь максимальный размер списка где-то 2000 символов! Нажмите ОК , чтобы закрыть окно с параметрами списка, и ещё раз ОК , чтобы закрыть окно параметров Excel.
Для пользовательского списка можно импортировать только текстовые значения. Если Вам нужно создать настраиваемый список с календарными датами или числами, то придётся использовать поле List entries (Элементы списка).
Вот некоторые моменты, которые Вы должны знать о настраиваемый списках… Все списки привязываются к компьютеру. Их настройки сохраняются на том компьютере, на котором Вы в данный момент работаете. Если Вы возьмёте файл с рабочего компьютера, чтобы поработать с ним дома, то придётся еще раз создать такой же пользовательский список на домашнем компьютере. Если Вы использовали настраиваемый список, чтобы сделать сортировку, то его элементы останутся в ячейках Excel, но среди списков он показан не будет.
Использование пользовательских списков в Excel
Итак, наш настраиваемый список готов к использованию. Выделите ячейку и введите с клавиатуры любой элемент этого списка. Нажмите на маркер автозаполнения (небольшой квадратик в правом нижнем углу ячейки), и потяните за него, чтобы продолжить последовательность. Чуть правее я вписал “Monday”, затем нажал на маркер и потянул вправо, Excel заполнил ячейки автоматически.
Замечательное свойство этой функции состоит в том, что Excel одинаково заполнит как столбец, так и строку, как в прямом направлении, так и в обратном, начнёте Вы с первого элемента списка, с середины или с последнего элемента… Как только Вы щелкните и начнете протаскивать мышь, Excel определит, что Вы хотите сделать, и вставит соответствующие данные.
Сортировка по пользовательскому списку
Одна из приятных возможностей, которую дает работа с настраиваемыми списками, – это возможность упорядочить данные по любому из списков, сохранённых на Вашем компьютере. Кликните по одному или нескольким столбцам, затем нажмите Sort & Filter (Сортировка и фильтр), раскройте выпадающее меню Order (Порядок), кликните Custom List (Настраиваемый список) и выберите список, по которому желаете выполнить сортировку.
Сортировка может включать более одного столбца. Если Вы хотите добавить ещё один уровень и упорядочить сначала по месяцам, а затем по номеру счёта, то можете кликнуть Add Level (Добавить уровень) и определить, каким образом должны отображаться данные. Когда закончите, нажмите ОК . Теперь информация упорядочена по выбранному списку!
Все достаточно просто! Применение пользовательских списков очень удобно особенно для тех, кто хочет избежать многократного копирования и вставки данных. Если Вы знаете, что есть набор данных, который в любой момент может понадобиться, почему бы не сделать из него настраиваемый список? Кто знает, возможно это сохранит Вам гораздо больше времени, чем Вы можете предположить… и оставит шефа с мыслью, что Вам пришлось изрядно потрудиться. Пусть он так думает.
Выпадающие списки – очень удобный объект, который используется практически во всех приложениях. Кроме этого, они очень популярны на веб-страницах, поскольку намного проще данные выбирать, чем вносить их вручную. Особенно подобные формы востребованы при регистрации. Но их также часто используют и в Экселе. В данной статье мы рассмотрим, как сделать выпадающий список в редакторе Excel.
Для этой цели существует несколько способов. Рассмотрим их более внимательно. Первым делом необходимо создать таблицу с небольшим перечнем.
Для того чтобы произошла подстановка данных из таблицы, достаточно выполнить следующее.
- Перейдите на первую пустую клетку после вашего списка.
- Сделайте правый клик. Затем выберите указанный пункт.
- В результате этого появится следующий список.
- Для перехода по нему достаточно нажать на горячие клавиши Alt +↓ .
Эту комбинацию можно будет использовать всегда. В дальнейшем необязательно вызывать контекстное меню.
- Затем для выбора можно использовать только стрелочки (↓ и ). Для того чтобы вставить нужный продукт (в нашем случае), достаточно нажать на клавишу Enter .
Данный способ отлично работает и тогда, когда в таблице содержатся повторяющиеся значения. При этом в выпадающем списке будут только уникальные записи.
Обратите внимание на то, что этот метод не работает, если вы выберите клетку, выше которой нет никакой информации.
Стандартный
В этом случае необходимо:
- Выделить нужные ячейки. Перейти на вкладку «Формулы». Нажать на кнопку «Определенные имена». Выбрать пункт «Диспетчер имён».
- Затем кликнуть на «Создать».
- Далее нужно будет указать желаемое имя (нельзя использовать символ тире или пробел). В графе диапазон произойдет автозаполнение, поскольку нужные ячейки были выделены в самом начале. Для сохранения нажмите на «OK».
- Затем закройте это окно.
- Выберите ячейку, в которой будет раскрываться будущий список. Откройте вкладку «Данные». Кликните на указанную иконку (на треугольник). Нажмите на пункт «Проверка данных».
- Нажмите на «Тип данных». Необходимо задать значение «Список».
- Вследствие этого появится поле «Источник». Кликните туда.
- Затем выделите нужные ячейки. Ранее созданное имя автоматически подставится. Для продолжения нажимаем на «OK».
- Благодаря этим действиям вы увидите вот такой элемент.
Если вы сделаете активной другую ячейку, то иконка выпадающего списка исчезнет. Она отображается только тогда, когда эта клетка становится активной. Поэтому не стоит пугаться и думать, что у вас всё исчезло.
Как включить режим разработчика
Для того чтобы использовать более продвинутые объекты, нужно использовать вкладку «Разработчик». По умолчанию она отключена. Для того чтобы её активировать, необходимо следовать следующей инструкции.
- Нажмите на меню «Файл».
- Перейдите в раздел «Параметры».
- Откройте категорию «Настроить ленту». Затем поставьте галочку напротив пункта «Разработчик». Для сохранения информации кликните на «OK».
Элементы управления
Для создания списков при помощи этого инструмента, необходимо сделать следующее:
- Выделите свою таблицу данных. Перейдите на вкладку «Разработчик». Кликните на иконку «Вставить». Нажмите на указанный элемент.
- Также изменится иконка указателя.
- Выделите какой-нибудь прямоугольник. Именно таких размеров и будет ваша будущая кнопка. Её необязательно делать слишком большой. В нашем случае это только пример.
- После этого сделайте правый клик мышкой по этому элементу. Затем выберите пункт «Формат объекта».
- В окне «Форматирование объекта» необходимо:
- Указать диапазон значений для формирования списка.
- Выбрать ячейку, в которую будет выводиться результат.
- Указать количество строк будущего списка.
- Нажать на «OK» для сохранения.
- Кликните на этот элемент. После этого вы увидите варианты для выбора.
- Вследствие этого вы увидите какое-нибудь число. 1 – соответствует первому слову, а 2 – второму. То есть в этой ячейке выводится лишь порядковый номер выбранного слова.
ActiveX
Для того чтобы воспользоваться этим элементом, необходимо выполнить следующие операции.
- Перейдите на вкладку «Разработчик». Нажмите на иконку «Вставить». На этот раз выберите другой инструмент. Он выглядит точно так же, но находится в другой группе.
- Обратите внимание на то, что у вас включится режим конструктора. Кроме этого, изменится внешний вид указателя.
- Нажмите куда-нибудь. В этом месте появится выпадающий список. Если вы хотите его увеличить, то для этого достаточно потянуть за его края.
- Кликните на указанную иконку.
- Благодаря этому в правой части экрана появится окно «Properties», в котором вы сможете изменить различные настройки для выбранного элемента.
При этом значения и свойства будут меняться в зависимости от того, какой элемент будет активен в данный момент. Здесь вы сможете изменить всё, даже шрифт.
- В поле «ListFilRange» укажите диапазон ячеек, в котором находятся ваши данные для будущего списка. Заполнение данных должно быть очень аккуратным. Достаточно указать одну неправильную букву, и вы увидите ошибку.
- Далее необходимо кликнуть правой кнопкой мыши по созданному элементу. Выберите «Объект Combobox». Затем – «Edit».
- Благодаря этим действиям вы увидите, что внешний вид объекта стал другим. Исчезнет возможность изменения размера.
- Теперь вы можете спокойно выбрать что-нибудь из этого списка.
- Для завершения необходимо отключить «Режим конструктора». После этого книга примет стандартный внешний вид.
- Также необходимо закрыть окно свойств.
Убрать объекты ActiveX довольно просто.
- Перейдите на вкладку «Разработчик».
- Активируйте «Режим конструктора».
- Кликните на этот объект.
- Нажмите на горячую клавишу Delete .
- И всё сразу же исчезнет.
Связанные списки
Для того чтобы создать двухуровневые списки, нужно выполнить несколько простых операций:
- Создайте какую-нибудь похожую таблицу. Главное условие – нужно добавить для каждого пункта несколько дополнительных вариантов выбора.
- Затем выделите первую строку. Не целиком, а только возможные варианты. Вызовите контекстное меню при помощи правого клика. Выберите пункт «Присвоить имя…».
- Укажите желаемое имя и сохраните настройку. Вставка диапазона ячеек произойдет автоматически, поскольку вы предварительно выбрали нужные клетки.
- Повторяем те же самые действия и для остальных строчек. Выберите любую клетку, в которой будет расположен будущий список товаров. Откройте вкладку «Данные» и нажмите на инструмент «Проверка данных».
- В этом окне необходимо выбрать пункт «Список».
- Затем кликнуть на поле «Источник» и выбрать нужный диапазон ячеек.
- Для сохранения используйте кнопку «OK».
- Выберите вторую ячейку, в которой будет создан динамический список. Перейдите на вкладку «Данные» и повторите те же самые действия.
В графе «Тип данных» снова указываем «Список». В поле источник укажите следующую формулу.
=ДВССЫЛ(B11)В качестве аргумента указываем ссылку на ту ячейку, в которой у нас подгружается ассортимент товаров. Более подробно о ней можно прочитать на сайте Microsoft.
- Обязательно сохраните все внесенные изменения.
После нажатия на «OK» вы увидите ошибку источника данных. Ничего страшного тут нет. Кликните на «Да».
Дело в том, что в данный момент в клетке «Товар» у нас ничего не выбрано. Как только там будет какое-нибудь слово, нужный список автоматически загрузится.
- Выберите что-нибудь из предлагаемых товаров.
- Только после этого вы увидите, что во второй ячейке появились варианты выбора для соответствующего пункта.
- Попробуйте указать что-то другое из вашего ассортимента. И вы увидите, что список сразу же изменится. Это очень удобно, поскольку ситуации, когда второе «меню» зависит от первого, бывают очень часто.
Связанные с поиском списки
В вышеописанном методе нужно было создавать дополнительные «имена» для каждого пункта. Но можно сделать и иначе. Представьте, что у вас на листе расположено огромное количество данных. Кроме этого, эта информация может меняться, дополняться или удаляться. Как быть в этом случае? Вручную каждый раз всё настраивать заново – очень плохая затея.
В подобных ситуациях нужно использовать динамическое построение выпадающих списков. Это делается следующим образом.
- Создайте какую-нибудь таблицу, которая будет выглядеть следующим образом. В правой части мы указали уникальные записи в первом столбце. В нашем случае – это города.
- Выберите любую клетку. Перейдите на вкладку «Данные» и нажмите на инструмент «Проверка данных».
- Выбираем нужный тип данных. В источнике указываем необходимый диапазон. Сохраняем при помощи кнопки «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
- Затем вернитесь к книге и выберите что-нибудь.
- Повторите это действие еще раз.
- Благодаря этому коду теперь можно выбирать несколько значений. Все описанные выше методы этого сделать не позволяли, так как всегда происходило затирание ранее выбранного слова.
- Для очистки этой клетки нужно использовать клавишу Delete .
Во всех остальных случаях значения будут только накапливаться.
Заключение
В данной статье мы рассмотрели всевозможные способы для создания выпадающих списков в редакторе Excel. Если у вас что-то не получается, возможно, вы используете неподходящую таблицу. Еще раз внимательно посмотрите на используемые формулы, поскольку там могут быть опечатки. Также очень важно проверить правильность ссылок на ячейки. Если у вас отсутствует вкладка «Разработчик», значит, вы её неправильно активировали или же упустили этот момент из виду.
Видеоинструкция
Некоторым пользователям намного проще воспринимать информацию при помощи видео. Именно для них мы подготовили специальный ролик, в котором описываются все ранее указанные методы.
Как создать в Экселе выпадающий список? Все давно знают о том, как хорошо Excel работает с таблицами и разного рода формулами, но мало кто знает, что здесь можно делать выпадающие списки. И сегодня речь пойдет о них.
И так есть несколько вариантов, как сделать выпадающие списки для работы в Microsoft Office Excel.
Вариант первый очень прост. Если вы вводите схожие данные в одном столбце сверху в низ, то вам достаточно всего лишь встать на ячейку под данными и нажать комбинацию клавиш «Alt + стрелка вниз». Перед вами появится выпадающий список, с которого одним кликом можно выбрать нужные Вам данные.
Недостатком данного метода является то, что он рассчитан на последовательный способ ввода данных и если вы станете на любую другую ячейку в столбце то выпадающий список будет пустым.
Вариант второй дает больше возможностей, его еще считают стандартным. Сделать можно это через проверку данных. В первую очередь нам нужно выделить диапазон данных, которые попадут в наш список, и присвоить ему имя.
Отредактировать данный диапазон можно через вкладку меню «Формулы» выбрав пиктограмму «Диспетчер имен». В нем вы можете создать новый выпадающий список, редактировать действующий или просто удалить не нужный.
Следующим шагом выбираем ячейку, где будет размещен наш выпадающий список и переходим на вкладку меню «Данные», нажимаем на иконку «Проверка данных». В открывшемся окне нам нужно выбрать тип данных, которые будут вводиться в нашей ячейке. В нашем случае мы выбираем «Списки» и ниже через знак равенства прописываем имя нашего диапазона, и нажать ОК. Для того чтобы применить список ко всем ячейкам достаточно перед включением проверки данных просто выделить весь столбец или нужную вам область.
Есть и другие варианты более сложные для создания выпадающего списка, такие как: вставка через вкладку меню «Разработчик», где можно вставить выпадающие списки как часть элемента формы или часть элемента ActiveX. Или написать соответствующие макросы для создания и работы выпадающих списков.
Введите данные в ячейки A1:A10, которые будут выступать в качестве источника для списка. В нашем примере мы ввели цифры, они появятся в выпадающем списке. Выделите ячейку (Например, E5), которая будет содержать в раскрывающийся список. Выберите меню «Данные» -> «Проверка данных», чтобы открыть диалоговое окно «Проверка вводимых значений».
3. На вкладке «Параметры» выберите параметр «Список» в раскрывающемся меню. Убедитесь, что установлены нужные флажки.
4. Затем, нажмите на кнопку. Появится следующее диалоговое окно.
5. Выберите элементы, которые появятся в выпадающем списке на листе с помощью мыши, кликните на кнопку и вернитесь обратно к окну «Проверка вводимых значений», затем нажмите кнопку «ОК».
6. Выпадающий список в Excel будет создан.
Если ваш список короткий, вы можете ввести элементы непосредственно в «Источник» на вкладке «Настройка» в диалоговом окне «Проверка вводимых значений». Отделите каждый пункт списка разделителями, указанными в региональных настройках.
Если список должен располагаться на другом листе, вы можете воспользоваться параметром «= List» перед указанием диапазона данных.
Как создать выпадающий список в Экселе на основе данных из перечня
Представим, что у нас есть перечень фруктов:
Как сделать выпадающий список в Excel
Для создания выпадающего списка нам потребуется сделать следующие шаги:
Перейти на вкладку “Данные” => раздел “Работа с данными” на панели инструментов => выбираем пункт “Проверка данных”.
В поле “Источник” ввести диапазон названий фруктов =$A$2:$A$6 или просто поставить курсор мыши в поле ввода значений “Источник” и затем мышкой выбрать диапазон данных:
Если вы хотите создать выпадающие списки в нескольких ячейках за раз, то выберите все ячейки, в которых вы хотите их создать, а затем выполните указанные выше действия. Важно убедиться, что ссылки на ячейки являются абсолютными (например, $A$2), а не относительными (например, A2 или A$2 или $A2).
Как сделать выпадающий список в Excel используя ручной ввод данных
На примере выше, мы вводили список данных для выпадающего списка путем выделения диапазона ячеек. Помимо этого способа, вы можете вводить данные для создания выпадающего списка вручную (необязательно их хранить в каких-либо ячейках).
Например, представим что в выпадающем меню мы хотим отразить два слова “Да” и “Нет”.
Для этого нам потребуется:
Выбрать ячейку, в которой мы хотим создать выпадающий список;
Перейти на вкладку “Данные” => раздел “Работа с данными” на панели инструментов =>
Проверка данных в Excel
Во всплывающем окне “Проверка вводимых значений” на вкладке “Параметры” в типе данных выбрать “Список”:
Проверка вводимых значений в Excel
В поле “Источник” ввести значение “Да; Нет”.
Нажимаем “ОК”
Да - Нет
После этого система создаст раскрывающийся список в выбранной ячейке. Все элементы, перечисленные в поле “Источник”, разделенные точкой с запятой будут отражены в разных строчках выпадающего меню.
Если вы хотите одновременно создать выпадающий список в нескольких ячейках – выделите нужные ячейки и следуйте инструкциям выше.
Как создать раскрывающийся список в Эксель с помощью функции СМЕЩ
Наряду со способами описанными выше, вы также можете использовать формулу СМЕЩ для создания выпадающих списков.
Например, у нас есть список с перечнем фруктов:
Для того чтобы сделать выпадающий список с помощью формулы СМЕЩ необходимо сделать следующее:
Выбрать ячейку, в которой мы хотим создать выпадающий список;
Перейти на вкладку “Данные” => раздел “Работа с данными” на панели инструментов => выбрать пункт “Проверка данных”:
Проверка данных в Excel
Во всплывающем окне “Проверка вводимых значений” на вкладке “Параметры” в типе данных выбрать “Список”:
Проверка вводимых значений в Excel
В поле “Источник” ввести формулу: =СМЕЩ(A$2$;0;0;5)
Нажать “ОК”
Система создаст выпадающий список с перечнем фруктов.
Как эта формула работает?
На примере выше мы использовали формулу =СМЕЩ(ссылка;смещ_по_строкам;смещ_по_столбцам;[высота];[ширина]).
Эта функция содержит в себе пять аргументов. В аргументе “ссылка” (в примере $A$2) указывается с какой ячейки начинать смещение. В аргументах “смещ_по_строкам” и “смещ_по_столбцам” (в примере указано значение “0”) – на какое количество строк/столбцов нужно смещаться для отображения данных.
В аргументе “[высота]” указано значение “5”, которое обозначает высоту диапазона ячеек. Аргумент “[ширина]” мы не указываем, так как в нашем примере диапазон состоит из одной колонки.
Используя эту формулу, система возвращает вам в качестве данных для выпадающего списка диапазон ячеек, начинающийся с ячейки $A$2, состоящий из 5 ячеек.
Как сделать выпадающий список в Excel с подстановкой данных (с использованием функции СМЕЩ)
Если вы используете для создания списка формулу СМЕЩ на примере выше, то вы создаете список данных, зафиксированный в определенном диапазоне ячеек. Если вы захотите добавить какое-либо значение в качестве элемента списка, вам придется корректировать формулу вручную.
Ниже вы узнаете, как делать динамический выпадающий список, в который будут автоматически загружаться новые данные для отображения.
Для создания списка потребуется:
Выбрать ячейку, в которой мы хотим создать выпадающий список;
Перейти на вкладку “Данные” => раздел “Работа с данными” на панели инструментов => выбрать пункт “Проверка данных”;
Во всплывающем окне “Проверка вводимых значений” на вкладке “Параметры” в типе данных выбрать “Список”;
В поле “Источник” ввести формулу: =СМЕЩ(A$2$;0;0;СЧЕТЕСЛИ($A$2:$A$100;”<>”))
Нажать “ОК”
В этой формуле, в аргументе “[высота]” мы указываем в качестве аргумента, обозначающего высоту списка с данными – формулу СЧЕТЕСЛИ, которая рассчитывает в заданном диапазоне A2:A100 количество не пустых ячеек.
Примечание: для корректной работы формулы, важно, чтобы в списке данных для отображения в выпадающем меню не было пустых строк.
Как создать выпадающий список в Excel с автоматической подстановкой данных
Для того чтобы в созданный вами выпадающий список автоматически подгружались новые данные, нужно проделать следующие действия:
Создаем список данных для отображения в выпадающем списке. В нашем случае это список цветов. Выделяем перечень левой кнопкой мыши:
выпадающий список с автоматиеской подстановкой в эксель
На панели инструментов нажимаем пункт “Форматировать как таблицу”:
Из раскрывающегося меню выбираем стиль оформления таблицы
Нажав клавишу “ОК” во всплывающем окне, подтверждаем выбранный диапазон ячеек:
Присваиваем имя таблице в правой верхней ячейке над колонкой “А”:
Таблица с данными готова, теперь можем создавать выпадающий список. Для этого необходимо:
Выбрать ячейку, в которой мы хотим создать список;
Перейти на вкладку “Данные” => раздел “Работа с данными” на панели инструментов => выбрать пункт “Проверка данных”:
Во всплывающем окне “Проверка вводимых значений” на вкладке “Параметры” в типе данных выбрать “Список”:
В поле источник указываем =”название вашей таблицы”. В нашем случае мы ее назвали “Список”:
Поле источник автоматическая подстановка данных в выпадающий список Эксель
Готово! Выпадающий список создан, в нем отображаются все данные из указанной таблицы:
Для того чтобы добавить новое значение в выпадающий список – просто добавьте в следующую после таблицы с данными ячейку информацию:
Таблица автоматически расширит свой диапазон данных. Выпадающий список соответственно пополнится новым значением из таблицы:
Автоматическая подстановка данных в выпадающий список эксель
Как скопировать выпадающий список в Excel
В Excel есть возможность копировать созданные выпадающие списки. Например, в ячейке А1 у нас есть выпадающий список, который мы хотим скопировать в диапазон ячеек А2:А6.
Для того чтобы скопировать выпадающий список с текущим форматированием:
нажмите левой клавишей мыши на ячейку с выпадающим списком, которую вы хотите скопировать;
выделите ячейки в диапазоне А2:А6, в которые вы хотите вставить выпадающий список;
нажмите сочетание клавиш на клавиатуре CTRL+V.
Так, вы скопируете выпадающий список, сохранив исходный формат списка (цвет, шрифт и.т.д). Если вы хотите скопировать/вставить выпадающий список без сохранения формата, то:
нажмите левой клавишей мыши на ячейку с выпадающим списком, который вы хотите скопировать;
нажмите сочетание клавиш на клавиатуре CTRL+C;
выберите ячейку, в которую вы хотите вставить выпадающий список;
нажмите правую кнопку мыши => вызовите выпадающее меню и нажмите “Специальная вставка”;
выпадающий список в excel
В появившемся окне в разделе “Вставить” выберите пункт “условия на значения”:
Нажмите “ОК”
После этого, Эксель скопирует только данные выпадающего списка, не сохраняя форматирование исходной ячейки.
Как выделить все ячейки, содержащие выпадающий список в Экселе
Иногда, сложно понять, какое количество ячеек в файле Excel содержат выпадающие списки. Есть простой способ отобразить их. Для этого:
Нажмите на вкладку “Главная” на Панели инструментов;
Нажмите “Найти и выделить” и выберите пункт “Выделить группу ячеек”:
В диалоговом окне выберите пункт “Проверка данных”. В этом поле есть возможность выбрать пункты “Всех” и “Этих же”. “Всех” позволит выделить все выпадающие списки на листе. Пункт “этих же” покажет выпадающие списки схожие по содержанию данных в выпадающем меню. В нашем случае мы выбираем “всех”:
Выпадающий список в Excel. Как найти все списки
Нажмите “ОК”
Нажав “ОК”, Excel выделит на листе все ячейки с выпадающим списком. Так вы сможете привести за раз все списки к общему формату, выделить границы и.т.д.
Как сделать зависимые выпадающие списки в Excel
Иногда нам требуется создать несколько выпадающих списков, причем, таким образом, чтобы, выбирая значения из первого списка, Excel определял какие данные отобразить во втором выпадающем списке.
Предположим, что у нас есть списки городов двух стран Россия и США:
Для создания зависимого выпадающего списка нам потребуется:
Создать два именованных диапазона для ячеек “A2:A5” с именем “Россия” и для ячеек “B2:B5” с названием “США”. Для этого нам нужно выделить весь диапазон данных для выпадающих списков:
зависимый выпадающий список в Excel
Перейти на вкладку “Формулы” => кликнуть в разделе “Определенные имена” на пункт “Создать из выделенного”:
Зависимые выпадающие списки в Excel
Во всплывающем окне “Создание имен из выделенного диапазона” поставьте галочку в пункт “в строке выше”. Сделав это, Excel создаст два именованных диапазона “Россия” и “США” со списками городов:
зависимый-выпадающий-список-в-excel
Нажмите “ОК”
В ячейке “D2” создайте выпадающий список для выбора стран “Россия” или “США”. Так, мы создадим первый выпадающий список, в котором пользователь сможет выбрать одну из двух стран.
Теперь, для создания зависимого выпадающего списка:
Выделите ячейку E2 (или любую другую ячейку, в которой вы хотите сделать зависимый выпадающий список);
Кликните по вкладке “Данные” => “Проверка данных”;
Во всплывающем окне “Проверка вводимых значений” на вкладке “Параметры” в типе данных выберите “Список”:
Проверка вводимых значений в Excel
Нажмите “ОК”
Теперь, если вы выберите в первом выпадающем списке страну “Россия”, то во втором выпадающем списке появятся только те города, которые относятся к этой стране. Также и в случае, когда выбираете “США” из первого выпадающего списка.
ПРЕДСТАВЬТЕ СИТУАЦИЮ: Мы хотим создать в Excel небольшую табличку, где можно выбрать страну и соответствующий ей город. При этом с помощью выпадающих списков, необходимо ограничить доступные пользователям варианты стран и городов, из которых они могут выбирать. В первой ячейке мы сделаем выбор страны, а во второй будут доступны только принадлежащие выбранной стране города. Думаю, это понятно?
Итак, давайте начнём наш простой пример с того, как можно создать связанный (или зависимый) выпадающий список в Excel? В ячейке B1 мы будем выбирать страну, а в ячейке B2 – принадлежащий ей город, как на примере:
Для начала нужно создать базу данных. На втором листе я занес список стран, которые хочу дать пользователям на выбор в первом раскрывающемся списке, а в соседнем столбце указал числовой индекс, который соответствует одному из списков городов. Списки городов располагаются правее в столбцах D , F и H . Так, например, рядом с France стоит индекс 2 , который соответствует списку городов 2 . Позже Вы увидите, как этот индекс будет использован.
Если Вы работаете в Excel 2010, то можете создать лист-источник в отдельной рабочей книге. Если же у Вас версия Excel 2003 года, и Вы планируете использовать именованный диапазон, то значения должны находиться в той же книге, можно на другом листе.
Мы будем использовать именованные диапазоны и сделаем так, чтобы эти связанные выпадающие списки работали во всех версиях Excel. Следующий шаг – создать именованные диапазоны для наших списков. На вкладке Formulas (Формулы) есть команда Name Manager (Диспетчер имён). Нажав на нее, откроется диалоговое окно Name Manager (Диспетчер имён).
Нажмите кнопку New (Создать), чтобы добавить новый именованный диапазон. Откроется диалоговое окно New Name (Создание имени).
В поле Name (Имя) введите имя Country для нашего первого именованного диапазона, а в поле Refers to (Диапазон) выберите тот, в котором хранится список стран:
Sheet3!$A$3:$A$5
Имена диапазонам, содержащим города, можно присвоить точно таким же образом.
Теперь мы можем создать выпадающие списки в тех ячейках, где планировали выбирать данные. Выделите ячейку B1 (в ней мы будем выбирать страну), откройте вкладку Data (Данные), нажмите Data Validation (Проверка данных), а затем в выпадающем меню выберите Data Validation (Проверка данных).
Откроется диалоговое окно Data Validation (Проверка вводимых значений).
Мы хотим дать пользователю на выбор список вариантов, поэтому в поле Allow (Тип данных) выберите List (Список). Это активирует поле Source (Источник), где необходимо указать имя диапазона со странами. Введите в этом поле “=Country” и жмите ОК . Теперь нам нужно сделать второй раскрывающийся список, чтобы пользователи могли выбрать город. Мы поместим этот раскрывающийся список в ячейку B2 . А теперь внимание – фокус! Нам нужно проверить содержимое ячейки с названием страны (ячейка B1), чтобы получить индекс соответствующий базе данных с городами. Если пользователь выберет Portugal , то мы должны обратиться к базе с индексом 3 , в которой хранятся названия городов Португалии. Мы воспользуемся функцией ВПР (VLOOKUP) для поиска значения из ячейки B1 в таблице с названиями стран. После того как индекс будет известен, мы выберем список, который станет источником данных для нашего второго выпадающего списка. Для этого напишем такую формулу:
CHOOSE(VLOOKUP(B1,Sheet3!$A$3:$B$5,2,FALSE),England,France,Portugal)
=ВЫБОР(ВПР(B1;Sheet3!$A$3:$B$5;2;ЛОЖЬ);England;France;Portugal)
Что же делает эта формула? Она ищет значение из ячейки B1 в списке стран и возвращает соответствующий индекс, который затем использует функция CHOOSE (ВЫБОР), чтобы выбрать 1-й, 2-й или 3-й именованный диапазон.
Вот так будет выглядеть наш второй раскрывающийся список:
