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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Добрый день, уважаемый читатель!

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

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

Ну что же, рассмотрим создание выпадающих списков и для чего же это нужно:

Я лично постоянно использую выпадающий список по всем 3 причинам. И она значительно упрощает мне работу с данными, я сознательно сокращаю к 0% возможность при введении первичных данных.

Ну вот 2 вопроса, что и для чего, я рассказал, а вот о том, как это сделать ниже и поговорим.

А делать список в ячейке будем в несколько этапов:

1. Определяем диапазон ячеек, в которых мы будем создавать фиксированный список.

2. Выделяем нужный нам диапазон и в меню выбираем пункт “Данные” — “Проверка данных”, в появившемся контекстном окне выбираем из указанного выбора пункт “Список”.

3. В разблокированной ниже строке указываем диапазон данных, которые должны быть у нас в выпадающем списке. Нажимаем “Ок” и работа сделана.

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

Рад вам помочь, пользуйтесь себе во благо!

"Прогресс человечества основывается на желании каждого человека жить не по средствам
"
Сэмюэль Батлер, философ

Итак, как же создать выпадающий список? В Excel за ввод данных в ячейке отвечает функция (команда) Проверка данных .

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

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

В нашем случае ФИО детей будут выводиться в столбце А , а выпадающий список будет напротив каждой фамилии в соответствующей ячейке столбца B : B1, B2, B3 и т.д.

Шаг 3 . Сделаем первый выпадающий список.

Для этого щелкнем в первой ячейке, в которой нужно сделать выпадающее меню (в нашем случае это ячейка B1 ) — на верхней панели Риббон перейдем на вкладку Данные — Проверка данных .

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

Выбираем Тип данных — Список .

И в Источнике указываем диапазон ячеек, которые будут значениями выпадающего списка (то есть откуда в выпадающем списке будут браться варианты выбора). В нашем случае это столбец H .

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

Окно свернется до полосочки.

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

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

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

Также данные для списка можно писать в Источнике через точку с запятой (по-русски) и через запятую (латинскими символами):

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

Как пользоваться выпадающим списком Execl?

Теперь в выбранной на шаге 3 ячейке появился выпадающий список, но, если ячейка не активна, его не видно.

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

Можно выбирать любое значение из списка.

Шаг 4. Создаем такие же списки в других ячейках.

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

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

Таким образом можно очень быстро заполнить таблицу однотипными данными.

Дополнительные настройки выпадающего списка

Дополнение 1. Подсказка при вводе данных в таблицу

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

Дополнение 2. Сообщение об ошибках

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

Можно настроить это сообщение при создании или редактировании выпадающего списка во вкладке Сообщение об ошибке .

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

ЧаВо

— Как удалить данные из ячейки, если в ней выпадающий список, а нужно сделать пустую ячейку .

— Нужно щелкнуть по ячейке и нажать клавишу DEL .

— Как добавить в список новые значения для выбора или удалить ненужные?

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

— Как вообще удалить выпадающий список, а не только одно значение?

— Зайдите Данные — Проверка данных — Тип данных: Любое значение .

— Как сделать выпадающий список с данными, расположенными на другой странице?

— Ранее мы говорили, что удобно использовать для разных данных, например, для основной таблицы использовать Лист1, а для всех данных, из которых формируются выпадающие списки, можно использовать Лист 2.

Как сделать выпадающий список из данных, расположенных на другом листе? Нужно диапазону с вариантами выпадающего списка присвоить имя (Выделить диапазон — Формулы — Присвоить имя ).

А затем на Шаге 3 в качестве источника указать это имя диапазона.

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

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

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

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

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

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 - так как это ширина нашего столбца с подгруппами.

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

Для чего нужны

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

Вариантов ввода может быть два:

• Только значения из созданного списка.
• Значения из списка и любые другие данные.

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

Как в Excel сделать выпадающие списки: общая методика

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

В ней имеется условие (критерий) проверки со строкой типа данных, где и выбираем значение «Список». В принципе, остальные поля пока можно оставить без изменений. Чуть ниже расположена строка, в которой будет вводиться источник значений. Вот он-то нас и интересует.

2007: ручной ввод данных

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

Источник из диапазона

Во втором случае можно использовать и более быстрое решение. Например, нам нужно ввести все значения в ячейках: начиная с A2 и заканчивая A4. В строке указания источника ставим курсор. А затем просто выделяем нужный диапазон. Программа сама поставит знак равенства, но пропишет значение не в виде «(A2:A4)», как это делается в соответствующей строке формул, а в варианте «=$A$2:$A$4».

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

Ввод именованных диапазонов (списков)

Наконец, еще один метод, позволяющий решить проблему того, как в Excel сделать выпадающие списки. Сначала создается сам диапазон с присвоением соответствующего имени. А после этого в строке источника нужно будет просто ввести его название, поставив перед ним знак равенства. Такая методика позволяет создавать списки с использованием данных и значений, которые располагаются на разных листах книги. И именно такой вариант в основном применяется в версиях редактора Excel 2007 и ниже, поскольку эти модификации не умеют работать с диапазонами, в которых указаны данные из разных листов.

Управление параметрами списка

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

Заключение

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