Как сделать разворачивающийся список в Excel?

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

Способ 1 — горячие клавиши и раскрывающийся список в excel

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

Этот же пункт меню можно запустить сочетанием клавиш Alt+»Стрелка вниз» и программа автоматически предложит в выпадающем списке значения ячеек, которые вы ранее заполняли данными.

На изображении ниже программа предложила 4 варианта заполнения (дублирующиеся данные Excel не показывает).

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

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

Способ 2 — самый удобный, простой и наиболее гибкий

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

1. Сперва необходимо создать список данных, который будет  источником данных для подстановки в выпадающий список в excel. Выделите данные и нажмите правой кнопкой мыши. В выпадающем списке выберите пункт «Присвоить имя…».
   
2. В окне «Создание имени» задайте имя для вашего списка (это имя дальше будет использоваться в формуле подстановки). Имя должно быть без пробелов и начинаться с буквы.
   
3. Выделите ячейки (можно сразу несколько ячеек), в которых планируется создать выпадающий список. Во вкладке «ДАННЫЕ» вверху документа нажмите на «Проверка данных».
   
4. В окне проверка вводимых значение в качестве типа данных задайте «Список». В строке «Источник:» введите знак равно и имя для ранее созданного списка. Данная формула позволит ввести значения только из списка, т.е. произведет проверку введенного значения и предложит варианты. Эти варианты и будут выпадающим списком.

Для создания проверки вводимых значений введите имя ранее созданного списка

При попытке ввести значение, которого нет в заданном списке, эксель выдаст ошибку.

Кроме списка можно вводить данные вручную. Если введенные данные не совпадут с одним из данных — программа выдаст ошибку

Чтобы воспользоваться этим способом, необходимо чтобы у вас была включена вкладка «РАЗРАБОТЧИК». По умолчанию эта вкладка отсутствует. Чтобы ее включить:

1. Нажмите на «Файл» в левом верхнем углу приложения.
2. Выберите пункт «Параметры» и нажмите на него.
3. В окне настройки параметров Excel во вкладке «Настроить ленту» поставьте галочку напротив вкладки «Разработчик».

Теперь вы сможете воспользоваться инструментом «Поле со списком (Элемент ActiveX)». Во вкладке «РАЗРАБОТЧИК» нажмите на кнопку «Вставить» и найдите в элементах ActiveX кнопку «Поле со списком (Элемент ActiveX)». Нажмите на нее.

Нарисуйте данный объект в excel выпадающий список в ячейке, где вам необходим выпадающий список.

Теперь необходимо настроить данный элемент. Чтобы это сделать, необходимо включить «Режим конструктора» и нажать на кнопку «Свойства». У вас должно открыться окно свойств (Properties).

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

Но нас на этапе создания интересуют только три основных:

1. ListFillRange — указывает диапазон ячеек, из которых будут браться значения для выпадающего списка. В моем примере я указал два столбца (A2:B7 — дальше покажу как это использовать). Если необходимо только одни значения указывается A2:A7.
2. ListRows — количество данных в выпадающем списке. Элемент ActiveX отличается от первого способа тем, что можно указать большое количество данных.
3. ColumnCount — указывает сколько столбцов данных указывать в выпадающем списке.
4. В строке ColumnCount я указал значение 2 и теперь в списке выпадающие данные выглядят вот так:
5. Как видите получился выпадающий список в excel с подстановкой данных из второго столбца с данными «Поставщик».

Источник: http://sprosivideo.com/vypadayushhij-spisok-eksel/

Как сделать раскрывающийся (выпадающий) список в Excel 2007, 2010, 2013

Проще всего эту задачу выполнить следующим образом. Нажатием правой кнопки по ячейке под столбцом с данными вызываем контекстное меню. Здесь интересует поле Выбрать из раскрывающегося списка. То же самое можно сделать, нажав комбинацию клавиш Alt+Стрелка вниз.

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

Стандартный способ

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

В более новой версии (2007, 2010, 2013, 2016) перейдите на вкладку Формулы, где в разделе Определенные имена обнаружите кнопку Диспетчер имен.

Жмем кнопку Создать, вносите имя, можно любое, после чего ОК.

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

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

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

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

Используем элементы управления

Метод основан на вставке элемента управления, называемом «поле со списком«, которое будет представлять собой диапазон данных.

Выберите вкладку Разработчик (для Excel 2007/2010), в других версиях потребуется активировать эту вкладку на ленте в параметрах – Настроить ленту.

Переходим на эту вкладку – жмем кнопку Вставить. В элементах управления выбираем Поле со списком (не ActiveX) и нажмите по значку. Нарисуйте прямоугольник.

Правой кнопкой по нему – Формат объекта.

Далее выберите Формировать по диапазону и выделите требуемые поля.

Связью с ячейкой выберите то поле, куда следует поместить порядковый номер элемента в списке. Затем жмем ОК.

Применение элементов ActiveX

Все, как и в предыдущем только выбираем Поле со списком (ActiveX).

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

В отличии от остальных способов этот позволяет настраивать шрифты, цвета и осуществить быстрый поиск.

Источник: http://composs.ru/vypadayuschij-spisok-v-excel/

Пошаговое руководство по созданию выпадающих списков в Excel

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

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

Чтобы ввести что-то в это поле, надо щелкнуть на стрелочку, а затем указать необходимую информацию.

Как сделать выпадающий список?

 

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

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

Именно для этого разработчики Microsoft Excel добавили в программу инструменты для проверки данных, в число которых входит и создание раскрывающегося списка.

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

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

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

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

1. Выделить нужные для работы ячейки.
2. В поле, где указывается имя ячейки (A1 или B2), необходимо написать другое название (например, «товары»).
3. Кликнуть в панели меню пункты «Данные — Проверка».
4. В появившемся окне надо заполнить информацию на вкладке «Параметры»: в пункте «Тип данных» указать «Список», а в пункте «Источник» — название ячеек (=товары).
5. Нажать кнопку «ОК».

Задаем дополнительные параметры выпадающих списков

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

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

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

Во второй вкладке аналогичным образом можно создать предупреждение, которое будет показано при неверном вводе. Кроме того, есть возможность выбрать вид ошибки:

• «останов» — программа сообщит об ошибке и не разрешит ввести ошибочные данные;
• «предупреждение» — программа сообщит об ошибке, но пользователь сможет сам решить, сохранять или нет эту информацию;
• «сообщение» — стандартное сообщение.

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

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

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

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

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

Создание одного списка на основании другого

Готовый пример связанного выпадающего списка Excel

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

Сначала необходимо прописать марки машин (например, Audi, Renault, Nissan) и присвоить этим ячейкам имя («авто»). Чуть ниже нужно написать название запчастей и тоже присвоить имя этим полям. Обязательно нужно, чтобы поля с запчастями Nissan назывались Nissan и т.д.

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

Здесь тоже надо щелкнуть в панели меню пункты «Данные — Проверка», но в поле «Источник» нужно написать следующее: =ДВССЫЛ(A5). А5 — это название поля, в котором указан автомобиль из колонки «Марка авто».

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

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

Источник: https://itguides.ru/soft/excel/vypadayushhij-spisok-v-excel.html

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

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

когда список значений одного выпадающего списка зависит от значения, выбранного в другом выпадающем списке или просто забитого в ячейку. Представим ситуацию: есть ячейка А2. В ней создан выпадающий список со значениями: Овощи, Фрукты, Мясо, Напитки.

А в ячейке В2 нам нужен такой список, чтобы значения этого самого списка изменялись в зависимости от того, какое значение мы укажем в ячейке А2 — т.е. список выбранной категории продуктов.

Например выбрали в А2 значение Овощи — в В2 появился выпадающий список, содержащий значения: Морковь, Капуста, Картошка, Редиска, Помидоры. Выбрали в А2 Мясо — в В2 появился выпадающий список, содержащий значения: Говядина, Телятина, Свинина, Курица, Индейка. И т.д.

Подготовка
Для начала нам потребуется создать все эти списки. Что-то вроде этого:

Далее для каждого из этих списков необходимо назначить именованный диапазон. Создать можно любым способом из описанных в этой статье.

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

В приложенном к статье примере диапазоны имеют имена категорий — их можно видеть в заголовках.

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

Создание зависимых списков
В ячейке А2 создаем «список списков» — основной список, на основании значений которого будет создаваться второй список. Этот список может быть создан любым способом (как создать выпадающий список). Назовем его Список категорий.

Но теперь вместо прямого указания имени списка необходимо указать ссылку на именованный диапазон, который мы выберем в Списке категорий(ячейка А2), на основании его имени. В этом нам поможет функция ДВССЫЛ (INDIRECT).

Просто записываем эту формулу в поле Источник (Source): =ДВССЫЛ($A2)

На что обратить внимание: если вы планируете распространять такой список на столбец, то ссылка должна выглядеть именно так: $A2. Перед цифрой не должно быть знака доллара ($A$2 — неправильно). Иначе зависимый список будет всегда формироваться исключительно на основании значении ячейки А2.

Источник из другой книги
Сами списки товара могут находится и в другой книге. Если книга называется Книга со списком.xls и на Лист1 в ячейке А1 в этой книге находится имя нужного нам списка, то формула будет выглядеть так:
=ДВССЫЛ(«‘[Книга со списком.xls]Лист1’!»&$A$1)
На что обратить внимание: лучше всегда перед именем книги и после имени листа ставить апостроф — ‘. Так вы избежите проблем и недопонимания, если имя листа или книги содержит пробелы и иные специфические символы. В отличии от списков внутри одной книги в данном случае знак доллара должен быть и перед буквой и перед цифрой.

Источник: https://www.excel-vba.ru/chto-umeet-excel/svyazannye-vypadayushhie-spiski/

Группирование данных в Excel

Можно сворачивать/разворачивать строки на листе Excel.

Если строк много, то фича рулит.

Группированные строки в Excel

Как сгруппировать строки

1. Выделить строки, которые хотим сгруппировать. Можно выделять как строки, так и просто ячейки «в столбце».
2. Выбрать Данные > Группа и Структура >Группировать.
3. Или в Office 2007 жмакнуть Shift+Alt+стрелка вправо. Появится диалог с вопросом: Что группировать будем — строки али колонки?

Официальная справка (очень рекомендую ознакомиться):

1. Для отображения строк уровня щелкните соответствующие символы структуры.
2. Для отображения или скрытия данных в многоуровневой структуре щелкайте символы структуры и .

Дело очень простое, как настройки маршрутизатора под Gentoo:

1. После выделения столбца/строки жмем Alt+Shift+ для понижения уровня. Потом, при необходимости повысить уровень обратно — то же со .
2. Уровней может быть (в Excel 2003) до восьми.
3. Кроме минусов, можно кликать линии, от них идущие.
4. Групповое изменение — маленькие кнопочки с цифрами в левом верхнем углу.

Но придумать себе проблему на эту тему все еще можно:

Как, епрст, сделать так, чтобы «плюсики» отображались вверху, а не внизу скрываемо/раскрываемого списка?

Office ХР

Данные > Группа и Структура > Настройка > убрать галку у опции «Итоги в строках под данными»

Office 2007

en

Data > Outline > Show the Outline dialog box > Settings > uncheck «Summary rows below detail»

ru

Данные > Структура > Показать меню (см. на картинке, куда надо нажать) > Настройки > убрать галку у опции «Итоги в строках под данными»

Раскрываемость списков неизменно и существенно повышается.

Странность в том, что для каждого листа эту операцию следует повторять… Разве что — использовать макрос, который будет это делать одним нажатием:

Sub OutlinePlusesInTop()‘ Товарищ, это макрос для Office 2007

With ActiveSheet.Outline.AutomaticStyles = False.SummaryRow = xlAbove.SummaryColumn = xlRightEnd With End Sub

А кто не знает, как использовать макросы в офисном пакете, тот просто нам завидует.

Плавная привязка записи к теме тестирования:

Дык, очевидно же, ёптыть…

Тест-кейсы в Excel забабахать может каждый бабуин, а как именно решить проблему с «поменять плюсики местами» — увы. Чтобы потом не сталкиваться с этой проблемой, сделал себе памятку в блоге. Всё это, конечно, неразрывно связано с тестированием…

То есть, не было никаких разрывов с ноября прошлого года.

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

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

Источник: https://testitquickly.com/2009/03/18/excel-group-ungroup/

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

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

В записи мы разберем, как сделать выпадающие списки разного вида в таблице Excel.

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

Для этого, в ячейки А1:А7 вписываем данные, которые будут отображаться в списке. В данный момент выделим ячейку, в которой создадим выпадающий список – В2.

Переходим во вкладку «Данные» и щёлкаем по кнопочке «Проверка данных».

На вкладке «Параметры» в поле «Тип данных» выбираем «Список». В поле «Источник» можно ввести значения разными способами:

• 1  вводим значения для списка вручную, через точку с запятой;
• 2  указываем диапазон ячеек, в которые введены данные для раскрывающегося списка;
• 3  выделяем ячейки с именами, щёлкаем по ним правой клавишей мышки и выбираем из меню «Присвоить имя».
• Дальше впишите «Имя» для выделенных данных.
• Выделяем ячейку В2 и в поле «Источник» ставим «=», затем пишем созданное имя.
• Тем самым, мы создали простой выпадающий список в Excel.

Если у вас имеется заголовок для столбца, и значениями надо заполнять каждую строку, то выделите не одну ячейку, а диапазон ячеек – В2:В9. Так можно будет выбирать из выпадающего списка нужное значение в каждой ячейке.

Добавляем значения в выпадающий список – динамический список

1. При этом мы будем дописывать значения в нужный диапазон, а они будут автоматически добавляться в выпадающий список.
2. Выделяем диапазон ячеек – D1:D8, затем на вкладке «Главная» щёлкаем «Форматировать как таблицу» и выбираем любой стиль.
3. Подтверждаем расположение данных и ставим галочку в поле «Таблица с заголовками».
4. Вверху пишем заголовок таблицы – «Сотрудники», и заполняем ее данными.

Выделяем ячейку, в которой будет раскрывающийся список и щёлкаем по кнопочке «Проверка данных». В следующем окошке, в поле «Источник», пишем следующее: =ДВССЫЛ(«Таблица1[Сотрудники]»).

У меня одна таблица на листе, так что пишу «Таблица1», если будет вторая – «Таблица2», и так далее.

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

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

• Если таблица с выпадающими списками находится на одном листе, а данные для этих списков – на другом, то данная функция нам очень поможет.
• На Листе 2, выделяем одну ячейку или диапазон ячеек, затем щёлкаем по кнопочке «Проверка данных».
• Переходим на Лист 1, ставим курсор в поле «Источник» и выделяем нужный диапазон ячеек.
• В данный момент можно дописывать имена на Листе 1, они будут добавляться в выпадающие списки на Листе 2.

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

Допустим, у нас есть три диапазона: имена, фамилии и отчества сотрудников. Для каждого, надо присвоить имя. Выделяем ячейки оного диапазона, можно и пустые – в них со временем можно будет добавлять данные, которые будут появляться в выпадающем списке. Кликаем по ним правой клавишей мышки и выбираем из списка «Присвоить имя».

Первый называем «Имя», второй – «Фамилия», третий – «Отч».

Сделаем еще один диапазон, в котором будут прописаны присвоенные имена. Назовем его «Сотрудники».

Делаем первый выпадающий список, который будет состоять из названия диапазонов. Выделяем ячейку Е1 и на вкладке «Данные» выбираем «Проверка данных».

В поле «Тип данных» выберите «Список», в поле источник – или введите «=Сотрудники», или выделите диапазон ячеек, которому присвоено имя.

Первый выпадающий список создан. В данный момент в ячейке F2 создадим второй список, который должен зависеть от первого. Если в первом выберем «Имя», во втором отобразится список имен, если выберем «Фамилия» – список фамилий.

Выделяем ячейку и щёлкаем по кнопочке «Проверка данных». В поле «Тип данных» выбираем «Список», в поле источник прописываем следующее: =ДВССЫЛ($Е$1). Тут Е1 – это ячейка с первым выпадающим списком.

По такому принципу можно делать зависимые выпадающие списки.

Если в будущем, надо будет вписать значения в диапазон, которому задано имя, к примеру, «Фамилия». Перейдите во вкладку «Формулы» и кликните «Диспетчер имен».

Затем в имени диапазона выбираем «Фамилия», и внизу, вместо последней ячейки С3, напишите С10. Нажмите галочку.

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

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

Источник: https://it-territoriya.ru/kak-sdelat-vypadayushhij-spisok-v-excel/