Как сделать выпадающий список в таблице в 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
А теперь предположим, что бухгалтерия уже две недели шурует с этим файлом, и вдруг требует вставить им еще и варианты “Может быть” и “Частично”…
Простейший способ
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;> – и покажи мне разницу…“, но имеет место бывать.
- Выделите ячейки с данными, которые должны попасть в выпадающий список (например, наименованиями товаров).
- Выберите в меню Вставка – Имя – Присвоить (Insert – Name – Define) и введите имя (можно любое, но обязательно без пробелов!) для выделенного диапазона (например Товары). Нажмите ОК.
- Выделите ячейки (можно сразу несколько), в которых хотите получить выпадающий список и выберите в меню “Данные – Проверка” (Data – Validation). На первой вкладке “Параметры” из выпадающего списка “Тип данных” выберите вариант “Список” и введите в строчку “Источник” знак равно и имя диапазона (т.е. =Товары).
Можно сделать и так:
Выделить диапазон ячеек (А1, В1, С1 в данном примере), и претворить его в "реальный" список
В любом случае списку должно быть присвоено уникальное имя.
Почему это круто: список “Товары” можно будет потом произвольно увеличивать или уменьшать. Табличный редактор будет учитывать не определенные ячейки, расположенные в определенном месте, а список as is. И все изменения в списке будут распространяться на все ячейки, которые “проверяют его для создания выпадающих списков”.
Горячие клавиши
Курсор стоит на ячейке с выпадающим списком.
Excel
Alt+Down arrow.
То есть, Alt+стрелка “вниз”.
Calc
По-умолчанию не установлено.
В справке написано Ctrl+D, но в справке баг (увы).
Поэтому назначаем лично:
- Tools > Customize > Keyboard > Shortcut Keys
- Проскроллить и выбрать желаемое сочетание клавиш для открытия существующего списка. Я выбрал Ctrl+Down. Внимание, Alt+Down недоступно (вообще все сочетания с Alt тут недоступны для редактирования).
- В Functions > Category выбрать Edit.
- В Functions > Function выбрать Selection List.
- Нажать на кнопку Modify.
Дополнение
Всякие другие волшебства на тему выпадающих списков см. на Planeta Excel. Особенно “Ссылки по теме“.
Спасибо большое. Эта тема очень актуальна и в справке мелкосовта выплывает после получаса ковыряния, да и то в общих чертах, а в гугле – на третьей странице. Это именно то, что мне нужно.
Отдельное спасибо за рисунки-скриншоты!
Алексей, подскжите, пожалуйста, можно ли как-то сделать, чтобы в нужной ячейке оказался выпадающий список, в котором будут только уникальные записи из некой колонки?
Решение нашел сам автор вопроса.
Дано:
1. Вася
2. Петя
3. Вася
4. Коля
5. Маша
6. Коля
Нужен список:
1. Вася
2. Петя
3. Коля
4. Маша
Нашел тут http://www.get-digital-help.com/2009/03/30/how-to-extract-a-unique-list-and-the-duplicates-in-excel-from-one-column/ формулу для автоматического составления уникального ряда
Для русской версии Excel формула должна быть такая:
=ЕСЛИОШИБКА(ИНДЕКС(ИмяИсходногоНабора;ПОИСКПОЗ(0;СЧЁТЕСЛИ($B$1:B1;ИмяИсходногоНабора);0));”")
Потом я создаю выпадающий список на основе этого ряда.
Доброго времени суток!
Внимательнейше прочитал весь материал. Спасибо большое, все более-менее понятно и опробовано.
Вопрос. Как в О_о задать связь между выпадающим списком и его неким значением? Ну, например, у меня есть выпадающий список :
Вася
Коля
Петя
Саша.
У Васи вес 56 кг, у Коли 68, у Пети 94, у Саши 72. И если я выбираю в выпадающем списке Васю, у меня, скажем в ячейке рядом, должно вывалиться 56 кило.
Заранее благодарен.
С уважением.
В Excel это делается так.
Там почти под заголовком записи есть линк “Скачать пример”. В нем надо выбрать значение в ячейке F4, и в ячейке F5 подгружается список, который относится к выбранной опции в F4.
Открыл указанный пример в O_o – функция работает адекватно. Только при смене значений в F4 в О_о не обновляется автоматически значение ячейки F5.
Посмотрел. В О_о несколько не так, но мысль дали. Буду пробовать, если получится – отпишусь.
С уважением.
Делюсь, как обещал.
Итак, мы имеем таблицу (скачать работающий пример – после загрузки > поменять расширение загруженного файла c odt на ods):
Имя Вес
Вася 50
Петя 48
Жора 72
Григорий 101
То есть нам нужно выбрать имя – а в соседнем окошечке появляется вес искомого гражданина.
1. Выпадающий список я организовал может несколько сложно, но мне показалось это более удобным… Выделяем столбец имен без заголовка, то есть именно имена… Вот тавтологизм получился %) . Затем идем Данные-Определить диапазон. И назначаем имя для нашего столбца – например – Имена1. Далее (кстати описанный метод работает не только на текущей странице – я выпадающий список вынес на титульный лист а лист с таблицей спрятал – так изящнее) ставим курсор в необходимую нам ячейку и идем Данные-Проверка. В меню “допустимый тип данных” выбираем “диапазон ячеек”. Набираем – Имена1. Все, вуаля, выпадающий список у нас появился.
2. Далее несколько корявее. Воспользуемся функцией базы данных DGET. Я убил примерно полчаса, чтобы разобраться с ней – ну очень криво реализована. Для начала, придется продублировать заголовки столбцов исходной таблицы с именами и весами, причем абсолютно точно, иначе не работает. То есть над нашим выпадающим списком пишем – Имя, над соседней клеточкой, в которой будет выводиться вес, пишем – Вес. Это обусловлено синтаксисом функции, может у кого получится по другому, у меня не вышло нифига. Теперь начинаем священнодействовать. Собственно в ячейку, куда должен вес выводиться, забиваем функцию DGET. Следующим образом:
=DGET(Диапазон исходной таблицы, то есть тупо полностью выделяем исходную таблицу вместе с заголовками; “Вес” – в кавычках забиваем заголовок столбца, который будем выводить, в нашем случае действительно – Вес; а здесь должны быть выделены скопом следующие ячейки – те, где мы сверху подписывали Имя и Вес, ячейка с нашим выпадающим списком и ячейка, куда этот вес должен быть записан, то есть та, где мы сейчас функцию DGET набираем.) Скобка закрыта.
Блин, написал, перечитал, решил показать точно с циферками, на примере одной страницы.
A B
1 Имя Вес
2 Вася 50
3 Петя 48
4 Жора 72
5 Григорий 101
6
7
8 Имя Вес
9 Тут выпадает список Тут пишем =DGET(A1:B5; “Вес”; A8:B9)
Все, должно работать.
С уваженим.
Ага, хорошо, сделаю, в среду наверное – понедельник навалился как-то резко, вторник будет не лучше.
Линк на файл поставил во втором абзаце.
На вордпресс почему-то разрешено загружать только odt-файлы. Переименование помогло… Явный баг в правилах.
Придется после загрузки файла руками поменять его расширение с odt на ods.
Проверил – работает.
Странно… У меня тоже сайт на вордпрессе – документ .ods загрузился на ура и прекрасно доступен для скачивания. Версия ВП – 2.8.5
Мой на общем хостинге wordpress.com.
Тут наряду с удобствами и ряд иногда непонятных ограничений.
Огромное спасибо Андрей за подробное описание такой ВОЗМОЖНОСТИ автоматического изменения значения соседней ячейки при выборе из списка. Я долго искал описание и нашел!!! Хочу внести комментарии в Ваше подробное описание.
1. функция DGET() в русском написании выглядит как БИЗВЛЕЧЬ().
2. Проверил в своем Excel 2007 ваш пример, но в последенй строке “9 Тут выпадает список Тут пишем =DGET(A1:B5; «Вес»; A8:B9)” возникает циклическая ошибка. Нужно исправить диапазон в третьем секторе с “A8:B9″ на “A8:A9″ и все будет работать.
Еще раз благодарю за описание!!!!
Ага… Замечательно, спасибо взаимно, сделаю второй вариант своей разработки теперь под эксель.
ЗЫ. Алексей предложил выложить файл с примером – если не затруднит, сделайте для экселя.
Присоединяюсь к благодарностям Андрею.
Подскажите еще пожалуйста, как сделать чтобы “Вес” появлялся в ТОЙ же ячейке, где выбирается “Имя”.
Т.е. имеется на другом листе именованый список “Имя” (простите за тафтологию). Мы выбираем желаемое имя в выпадающем списке. Нужно чтобы в этой же ячейке отображалось значение веса.
Этим способом я так понимаю не получится – т.к. необходимы отдельные поля, на которые собственно и ссылается DGET. Может есть другой способ?
Спасибо
Андрей, большое спасибо за пример. Единственный минус в том, что необходимо дублировать заголовки столбцов над ячейкой-результатом. Таким образом если необходимо организовать несколько таких выпадающих списков – один под другим (это, например, может понадобиться при составлении раскладки на поход), – то придётся над каждым из них копировать всё те же заголовки, что, конечно, не страшно, но выглядит немного некрасиво.
Вдохновлённая Вашим примером, порылась в справке офиса (оказывается, иногда не бесполезно
) и нарыла такие функции в разделе “Функции электронных таблиц”: INDEX(Ссылка; Строка; Столбец; Диапазон) и
MATCH(Условие поиска; Массив; Тип).
Цитата из справки офиса: “Функция INDEX возвращает поддиапазон, для которого указаны номер строки и столбца или имя диапазона. В зависимости от контекста, функция INDEX возвращает ссылку или значение…
MATCH
Возвращает относительную позицию в массиве элемента, который совпадает с заданным значением. Функция возвращает позицию значения, найденного в массиве, в виде числа.”
Предлагаю свой вариант решения данной задачи, основанный на использовании упомянутых выше функций (качать тут).
Итак, раскладка.
1. Создаём таблицу соответствия значений. Например:
А В
1 Продукт Порционная норма, г
2 Гречка 70
3 Овсянка 50
4 Рис 80
5 Рожки 70
2. Определим диапазон product (ячейки А2:А5). Выпадающий список организовываем так же, как указано в посте от 05/02/2010, 11:47 (пункт 1).
А В
7 Завтрак Рожки (список)
8 Обед Гречка (список)
9 Ужин Рис (список)
3. Дополнительно создадим диапазон norma, состоящий из норм для данных продуктов (т.е. из чисел в правой колонке). (Ячейки В2:В5).
4. Далее, в ячейке, следующей за выпадающим списком (С7), пишем “=INDEX(norma;MATCH(B7;$Лист1.$A$2:$A$5;0);1)”
Таким образом, функция MATCH будет искать в диапазоне ячеек А2:А5 (product) значение, совпадающее со значением в ячейке B7, и выдаст его порядковый номер относительно этого списка product (не ссылку на ячейку, не номер строки, а именно порядковый номер в списке!). Так, в данном случае, в ячейке B7 выбран пункт “Рожки”. Результат функции MATCH равен 4.
После этого, функция INDEX выбирает из списка norma значение, которое имеет соответствующий порядковый номер. То есть, в нашем случае, порядковый номер = 4, а результат работы функции INDEX – 70 (значение ячейки В5).
5. Копируем функцию необходимое количество раз – готово.
Привет.
Спасибо за менюшки, первый вариант получался даже у меня, но никак не могу придумать как добавлять еще и цвет
Например Passed ( green)
Failed (red)
Not testable ( blue)
Точно знаю что сделать можно, так как пользовалась раньше файликом, в котором было такое раскрашенное меню. К сожалению файлика больше нет и скопи пастить уже нельзя
Буду очень благодарна за решение
Ключевое слово – Conditional Formatting
В Excel 2007 – Home > Styles > Conditional Formatting
В O_o – Format > Conditional Formatting
Спасибо
сижу разбираюсь с о-о, на работе еще excel помучаю
все супер!!! спасибо!!
а теперь хочется, чтобы в ячейке с выпадающим списком было значение по умолчанию, чтоб выбирать не каждый раз а только по необходимости, подскажите как это организовать в Excel.
Доброго Всем дня!!!!!!!! У меня есть один вопрос: есть столбец, где есть повторяющиеся имена. Хочу сделать выпадающий список на основе этих имен, но с условием: чтобы они (имена) в списке не повторялись. Посмотрел пример от Алексея Лупана – он не работает. Использую excel 2003.
Заранее спасибо!
Спасибо всем участникам!
Хотел добавить про менюшку и соответствующее значение в соседнюю ячейку. Проще воспользоваться функцией VLOOKUP
Список делаем как в посте on 05.02.2010 at 11:47
В ячейку где должен появлятся вес пишем формулу
=VLOOKUP (Условия поиска; Массив; Индекс; Порядок сортировки)
Условия поиска – ячейка с выпадающим списком
Массив – таблица (имена и вес)
индекс – номер нужного столбца (вес)
Порядки сортировки – 0 (без сортировки)
Для прмера из поста от 05.02.2010
A B
1 Имя Вес
2 Вася 50
3 Петя 48
4 Жора 72
5 Григорий 101
6
7
8 Имя Вес
9 Тут выпадает список Тут пишем =VLOOKUP(A9;$A$2:$B$5;2;0)
Если нужен пример, скажите куда залить. Надеюсь комунибудь будет полезно!
Работает. Спасибо!