Выпадающие списки в Excel и Calc

Автор: | 16.01.2009

Как сделать выпадающий список в таблице в Excel или Calc.

Пример подобного списка:

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

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

Понятно, что в этой клинике зубы вырывают только “пакетным” способом, или по 10, или по 20, или сразу по 30, но никак не по 11 или 27?!

Еще бы.

Простейший способ

Подходит, когда будущий список содержит ограниченное количество вариантов. Например,

  • Да
  • Хз
  • Нет

Excel

Пишем на листе короткий список пациентов. Хватает даже одного – “Иван”.

Выделяем ячейку справа от “Ивана” (как на картинке), и выбираем пункты меню Data > Validation > Allow: List > Source.

Пункты “Data” и “Validation” в русскоязычных версиях называются “Данные” и “Проверка”

В поле ‘Source’ вписываем это:

Да;Хз;Нет

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

Внимание!

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

Итак, контора пишет:

Создаем выпадающий список

Создаем выпадающий список

Результат

В ячейке создан выпадающий список

В отдельной ячейке “под курсором” создан выпадающий список

Копируем эту ячейку as is (просто курсор находится “на ячейке”, жмем Ctrl+C) повсюду, куда нам нужно (ставим курсор, куда нужно, и жмем Ctrl+V). Можно скопировать даже в другой файл Excel или на другой лист.

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

Итого:

Итоговый список пациентов и колонка с выпадающим списком

Итоговый список пациентов и колонка с выпадающим списком

Calc

Все то же самое, выбираем пункты меню Data > Validity… > Allow: List > Entries.

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

  • Да
  • Хз
  • Нет
Составляем список в OpenOffice Calc

Составляем список в OpenOffice Calc

А теперь предположим, что бухгалтерия уже две недели шурует с этим файлом, и вдруг требует вставить им еще и варианты “Может быть” и “Частично”…

Простейший способ

Excel

Ставим курсор на ячейку, в которой содержится наш список, и снова взываем к ее редактированию (Data > Validation > Allow: List > Source).

Редактируем список. Но не используем клавиши “влево – вправо”.

Почему – просто попробуй, поймешь.

Обязательно жмакаем опцию “Apply these changes to all others cells with same range”. Это объяснит Excel, что внесенные изменения относятся ко всем ячейкам, которые содержат редактируемыми нами список.

[Ок].

Calc

Надо выбрать все ячейки, в которых находится наш список, снова пройти по Data > Validity… > Allow: List > Entries и изменить значения.

Мудрейший способ

Делаем ссылку на отдельно хранящийся список.

Excel

Пишем на листе короткий список пациентов. Хватает даже одного – “Иван”.

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

Пример:

  • ячейка А1 – Да
  • ячейка В1 – Хз
  • ячейка С1 – Нет
  • ячейка D1 – Может быть

Переходим к списку пациентов, выделяем первую ячейку в колонке “Заплатил?” (справа от “Ивана”). Ставим курсор туда, где должна будет начинаться будущая колонка с ячейками, которые содержат выпадающий список. В нашем случае – это колонка “Заплатил?” напротив ячейки со значением “Иван”.

Выбираем пункты меню Data > Validation > Allow: List > Source.

Пункты “Data” и “Validation” в русскоязычных версиях называются “Данные” и “Проверка”

В поле ‘Source’ вписываем это:

=$A$1:$C$1

или это

=A1:C1

Или ничего не вписываем, а просто кликаем на квадрат, который находится в правом краю поля Source. Окно превратится в узкую полоску. Мы не пугаемся, а курсором выделяем на листе диапазон ячеек, из которых потом будут взяты данные: A1, B1, C1, D1, E1, F1, G1, и тд, если нужно. Можно даже выделять пустые ячейки, рассчитывая заполнить их позже (мало ли что бухгалтерия придумает).

В процессе этого выделения ячеек поле Source будет заполняться самостоятельно.

По-умолчанию Excel запишет выделенный пользователем диапазон через знак “$” – он указывает, что строго-настрого нужна именно эта ячейка, брать данные только из нее, чтобы ни случилось.

Если указать просто =A1:C1, то при изменении расположения ячеек на листе (что часто бывает) Excel будет считать, что адрес указанного диапазона может быть изменен.

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

Чтобы ее “размножить” – хватаем за угол и тянем вниз… Или копируем куда-нибудь в другое место на листе.

Calc

Почти то же самое, но выбираем пункты меню Data > Validity… > Allow: Cell Range > Source.

Нужно указывать диапазон руками: $A$1:$C$1, к примеру. Замечу – без знака “=“.

Кстати

Можно организовать этот список в “реальный” список на языке табличного редактора.

Собственно, шаг необязательный, из разряда “Заголовок следует обрамлять тэгом <H1>, но можно и “неформально” обрамить его тэгом <span style=”font-size: 22px;> – и покажи мне разницу…“, но имеет место бывать.

  1. Выделите ячейки с данными, которые должны попасть в выпадающий список (например, наименованиями товаров).
  2. Выберите в меню Вставка – Имя – Присвоить (Insert – Name – Define) и введите имя (можно любое, но обязательно без пробелов!) для выделенного диапазона (например Товары). Нажмите ОК.

Можно сделать и так:

Выделить диапазон ячеек, и претворить его в "реальный" список

Выделить диапазон ячеек (А1, В1, С1 в данном примере), и претворить его в “реальный” список

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

  1. Выделите ячейки (можно сразу несколько), в которых хотите получить выпадающий список и выберите в меню “Данные – Проверка” (Data – Validation). На первой вкладке “Параметры” из выпадающего списка “Тип данных” выберите вариант “Список” и введите в строчку “Источник” знак равно и имя диапазона (т.е. =Товары).

Почему это круто: список “Товары” можно будет потом произвольно увеличивать или уменьшать. Табличный редактор будет учитывать не определенные ячейки, расположенные в определенном месте, а список as is. И все изменения в списке будут распространяться на все ячейки, которые “проверяют его для создания выпадающих списков”.

Горячие клавиши

Курсор стоит на ячейке с выпадающим списком.

Excel

Alt+Down arrow.

То есть, Alt+стрелка “вниз”.

Calc

По-умолчанию не установлено.

В справке написано Ctrl+D, но в справке баг (увы).

Поэтому назначаем лично:

  1. Tools > Customize > Keyboard > Shortcut Keys
  2. Проскроллить и выбрать желаемое сочетание клавиш для открытия существующего списка. Я выбрал Ctrl+Down. Внимание, Alt+Down недоступно (вообще все сочетания с Alt тут недоступны для редактирования).
  3. В Functions > Category выбрать Edit.
  4. В Functions > Function выбрать Selection List.
  5. Нажать на кнопку Modify.

Ctrl+Down

Дополнение

Всякие другие волшебства на тему выпадающих списков см. на Planeta Excel. Особенно “Ссылки по теме“.

Внимательное внимание!

Прием комментариев к этой записи завершён.

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

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

Вы же любите тестировать, правда?

Выпадающие списки в Excel и Calc: 70 комментариев

  1. Роман

    Так и есть, база данных. Начата давным давно, неизвестным программистом. Огромная гора документов. Пока что взятся за перевод всего этого “добра” в Access пока не хватает духу и к сожалению знания вышеупомянутого Access. Так что приходится сражаться силами Excel. Пытаюсь автоматизировать всё что можно.

  2. Рашид

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

  3. Алексей Лупан

    Надо указать в качестве источника ячейки с другого листа.
    Пример:
    =Лист1!A1:A10
    Можно и так (если знаете разницу):
    =Лист1!$A$1:$A$10
    Если ярлык листа называется не одним словом, а несколькими, тогда надо его названием взять в кавычки:
    =’Лист один’!A1:A10
    =’Лист один’!$B$14:$B$18

  4. Рашид

    не очень то получается.У меня есть таблица с списком где 100 строк и 20 столбцов и сегодня целый день пытаюсь упрастить работу по компонованию таблицы с 10 сроками из вышеуказанной таблицы. Я хотел связать таблицы, чтобы при выборе (к примеру) населенного пункта он мне выдал необходимую информацию из нижней таблицы. Возможно ли их как привязать

  5. Юрий

    Алексей Лупан!!!Вам ГИГАНТСКОЕ СПАСИБО!!!!не успевал с кучей документов и застрял с выпадающими списками…Вы меня спасли от припадка бешенства, потери самоуважения и полного неуспевания!!!:)))

  6. Genas

    Через индех вообще без проблем получается!

  7. Юля

    Алексей Лупан, спасибо Вам большое!!очень нужная статья, но есть вопрос: как программно переопределить диапазон ячеек в OOo.Calc? ну никак без этого =(

  8. Юля

    не знаю, как в Excel, мне Calc нужен 🙂

  9. avery

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

  10. александр

    Добрый день . помогите пожалуйста с Excel.
    Дано:
    номер варианта
    1
    2
    3
    значение для каждого варианта:
    100
    200
    300
    необходимо в Excel :
    что бы при выборе номера варианта в одной из соседних ячеек появлялось значение соответствующее выбранному мною варианту
    если не сложно скиньте пример на ящик Kovalenko-1982@tut.by
    Заранее огромное спасибо

  11. Amega

    Потратила 3 часа, чтобы найти то, что здесь написано таким ПРОСТЫМ И ПОНЯТНЫМ С ПЕРВОГО РАЗА ЯЗЫКОМ!
    Автор, вы ГЕНИЙ!!!!!!!!!!!!!!!

  12. Nik

    Приветствую.
    Подскажите как мне сделать так, чтобы при выборе какого либо значения из выпадающего списка, в других ячейках присваивались значения которые прописаны в другой таблице для заданного значения. т.е к примеру есть исходная таблица из 16 столбцов и 25 строк. по данным из первого столбца я делаю выпадающий список на другом листе. Как сделать так, чтобы при выборе к примеру 4 строчки из выпадающего списка, ячейкам ниже присваивались значения остальных 15 столбцов 4 строчки исходной таблицы.
    Заранее благодарен.

  13. Nik

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

  14. dmytroy

    Прочитал Добавление поля со списком (элемент ActiveX) из
    http://office.microsoft.com/ru-ru/excel-help/HP010236681.aspx.
    Хотелось бы усовершенствовать, а именно:
    1. Раскрывающийся список один на лист, а результат выборки
    заносится в произвольную ячейку, каждый раз выделяемую кликом
    мышки. Или в ячейку справа от такой выделенной;
    2. Результат выборки заносится только один раз, и остаётся
    неизменным, как бы не изменялся раскрывающийся список в дальнейшем.
    Заранее благодарен за совет.

Обсуждение закрыто.