Создание раскрывающегося списка
Создание раскрывающегося списка
Чтобы упростить работу пользователей с листом, добавьте в ячейки раскрывающиеся списки. Раскрывающиеся списки позволяют пользователям выбирать элементы из созданного вами списка.
-
На новом листе введите данные, которые должны отображаться в раскрывающемся списке. Желательно, чтобы элементы списка содержались в
таблице Excel
. Если это не так, список можно быстро преобразовать в таблицу, выделив любую ячейку диапазона и нажав
Ctrl+T
.
Примечания:
-
Почему данные следует поместить в таблицу? Когда ваши данные находятся в таблице при
добавлении или удалении элементов из списка
, любые раскрывающиеся списки, основанные на этой таблице, будут автоматически обновляться. Дополнительные действия не требуются. -
Сейчас самое время
отсортировать данные в диапазоне или таблице
в раскрывающемся списке.
-
-
Выделите на листе ячейку, в которую требуется поместить раскрывающийся список.
-
Перейдите на вкладку
данных
на ленте, затем
в проверку данных
.Примечание:
Если вы не можете щелкнуть
проверку данных
, возможно, рабочий лист защищен или общедоступен.
Разблокируйте определенные области защищенной книги
или прекратите совместное использование листа, а затем повторите шаг 3. -
На вкладке
Параметры
в поле
Разрешить
щелкните
Список
. -
Щелкните поле
Источник
и выделите диапазон списка. В примере данные находятся на листе «Города» в диапазоне A2:A9. Обратите внимание на то, что строка заголовков отсутствует в диапазоне, так как она не является одним из вариантов, доступных для выбора.
-
Если можно оставить ячейку пустой, установите флажок
Игнорировать пустые
ячейки. -
Установите флажок
Список допустимых значений
-
Откройте вкладку
Сообщение для ввода.
-
Если вы хотите, чтобы при выборе ячейки появлялось всплывающее сообщение, установите флажок
Отображать подсказку, если ячейка является текущей
и введите заголовок и сообщение в соответствующие поля (до 225 символов). Если вы не хотите, чтобы сообщение отображалось, снимите этот флажок.
-
-
Откройте вкладку
Сообщение об ошибке.
-
Если вы хотите, чтобы при вводе значения, которого нет в списке, появлялось всплывающее сообщение, установите флажок
Выводить сообщение об ошибке,
выберите параметр в поле
Вид
и введите заголовок и сообщение. Если вы не хотите, чтобы сообщение отображалось, снимите этот флажок.
-
-
Не знаете, какой параметр выбрать в поле
Вид
?-
Чтобы отобразить сообщение, не препятствующее вводу данных, которые не содержатся в раскрывающемся списке, выберите вариант
Сведения
или Предупреждение. Если выбрать вариант «Сообщение», сообщение будет отображаться со значком
, а если «Предупреждение» — со значком
. -
Чтобы заблокировать пользователям ввод данных, которые не содержатся в раскрывающемся списке, выберите вариант
Остановка
.Примечание:
Если вы не добавили заголовок и текст, по умолчанию выводится заголовок «Microsoft Excel» и сообщение «Введенное значение неверно. Набор значений, которые могут быть введены в ячейку, ограничен».
-
Предлагаем скачать образец книги с несколькими примерами проверки данных, аналогичными примеру в этой статье. Вы можете воспользоваться ими или создать собственные сценарии проверки данных.
Скачать примеры проверки данных Excel
.
Ввод данных станет быстрее и точнее, если ограничить значения в ячейке вариантами из раскрывающегося списка.
Сначала создайте на листе список допустимых элементов, а затем выполните сортировку или расположите их в нужном порядке. В дальнейшем эти элементы могут служить источником для раскрывающегося списка данных. Если список небольшой, на него можно легко ссылаться и вводить элементы прямо в средстве проверки данных.
-
Создайте список допустимых элементов для раскрывающегося списка. Для этого введите элементы на листе в одном столбце или строке без пустых ячеек.
-
Выделите ячейки, для которых нужно ограничить ввод данных.
-
На вкладке
Данные
в группе
Инструменты
щелкните
Проверка данных
или
Проверить
.
Примечание:
Если команда проверки недоступна, возможно, лист защищен или книга является общей. Если книга является общей или лист защищен, изменить параметры проверки данных невозможно. Дополнительные сведения о защите книги см. в статье
Защита книги
. -
Откройте вкладку
Параметры
и во всплывающем меню
Разрешить
щелкните
пункт Список
. -
Щелкните поле
Источник
и выделите на листе список допустимых элементов.Диалоговое окно свернется, чтобы было видно весь лист.
-
Нажмите клавишу ВВОД или кнопку
Развернуть
, чтобы развернуть диалоговое окно, а затем щелкните
ОК
.Советы:
-
Значения также можно ввести непосредственно в поле
Источник
через запятую. -
Чтобы изменить список допустимых элементов, просто измените значения в списке-источнике или диапазон в поле
Источник
-
Можно указать собственное сообщение об ошибке, которое будет отображаться при вводе недопустимых данных. На вкладке
Данные
щелкните
Проверка данных
или
Проверить
, а затем щелкните
Сообщение об ошибке
.
-
См. также
Применение проверки данных к ячейкам
-
На новом листе введите данные, которые должны отображаться в раскрывающемся списке. Желательно, чтобы элементы списка содержались в
таблице Excel
.Примечания:
-
Почему данные следует поместить в таблицу? Когда ваши данные находятся в таблице при
добавлении или удалении элементов из списка
, любые раскрывающиеся списки, основанные на этой таблице, будут автоматически обновляться. Дополнительные действия не требуются. -
Теперь следует
отсортировать данные в том порядке, в котором они должны отображаться
в раскрывающемся списке.
-
-
Выделите на листе ячейку, в которую требуется поместить раскрывающийся список.
-
Откройте вкладку
Данные
на ленте и щелкните кнопку
Проверка данных
. -
На вкладке
Параметры
в поле
Разрешить
щелкните
Список
. -
Если вы уже создали таблицу с элементами раскрывающегося списка, щелкните поле
Источник
и выделите ячейки, содержащие эти элементы. Однако не включайте в него ячейку заголовка. Добавьте только ячейки, которые должны отображаться в раскрывающемся списке. Список элементов также можно ввести непосредственно в поле
Источник
через запятую. Например:
Фрукты;Овощи;Зерновые культуры;Молочные продукты;Перекусы
-
Если можно оставить ячейку пустой, установите флажок
Игнорировать пустые
ячейки. -
Установите флажок
Список допустимых значений
-
Откройте вкладку
Сообщение для ввода.
-
Если вы хотите, чтобы при выборе ячейки появлялось всплывающее сообщение, установите флажок
Показывать сообщения
и введите заголовок и сообщение в соответствующие поля (до 225 символов). Если вы не хотите, чтобы сообщение отображалось, снимите этот флажок.
-
-
Откройте вкладку
Сообщение об ошибке.
-
Если вы хотите, чтобы при вводе значения, которого нет в списке, появлялось всплывающее сообщение, установите флажок
Показывать оповещения
, выберите параметр в поле
Тип
, и введите заголовок и сообщение. Если вы не хотите, чтобы сообщение отображалось, снимите этот флажок.
-
-
Щелкните
ОК
.
После создания раскрывающегося списка убедитесь, что он работает так, как нужно. Например, рекомендуется проверить,
изменяется ли ширина столбцов и высота строк
при отображении всех ваших записей. Если вы решили изменить элементы раскрывающегося списка, см. статью
Добавление и удаление элементов раскрывающегося списка
. Чтобы удалить раскрывающийся список, см. статью
Удаление раскрывающегося списка
.
Дополнительные сведения
Вы всегда можете задать вопрос специалисту Excel Tech Community или попросить помощи в сообществе Answers community.
См. также
Добавление и удаление элементов раскрывающегося списка
Видео: создание раскрывающихся списков и управление ими
Общие сведения о таблицах Excel
Применение проверки данных к ячейкам
Блокировка и разблокировка определенных областей защищенного листа
Нужна дополнительная помощь?
Содержание
- 1 Инструкция для Excel
- 2 Основы того, как в «Экселе» сделать раскрывающийся список
- 3 Необходимые настройки для создания
- 4 Как в «Экселе» сделать раскрывающийся список – полезные примечания
- 5 Способ 1 — горячие клавиши и раскрывающийся список в excel
- 6 Способ 2 — самый удобный, простой и наиболее гибкий
- 7 Способ 3 — как в excel сделать выпадающий список с использованием ActiveX
Очень часто при создании таблиц в Excel хотелось бы использовать сворачиваемые списки. Оказывается, это очень просто!
Итак, как же это сделать?
1. Выделяем строки раздела, которые нужно сгруппировать, кроме заголовка.
2. Выбрать Данные -> Группировать
3. Если были выделены не целые строки при помощи их заголовков, а просто группа ячеек, то отобразится диалоговое окно, в котором пользователю будет предложено уточнить, что он группирует — строки или столбцы.
4. Готово!
Теперь раздел строк можно свернуть или развернуть, нажав на кнопки «+» или «-».
Для отображения строк определенного уровня нужно нажать на кнопки 1, 2 и т.п., соответствующие уровню.
По умолчанию кнопки «+»/ «-» расположены внизу, но чаще всего это неудобно и некрасиво. Поэтому сделаем небольшие изменения и разместим кнопку для группировки сверху.
Для этого в настройках раздела «Структура» в Excel нужно откорректировать расположение итоговых данных и снять флаг «итоги в строках под данными»:
Теперь сгруппированный список выглядит так:
Добавление раскрывающегося списка в таблицу Excel поможет сделать ввод данных более быстрым, предлагая для пользователей список элементов, из которых следует выбрать требуемое вместо того, чтобы выполнять ввод информации каждый раз. Когда вы помещаете такие данные в ячейку электронной таблицы, в ней начинает отображаться стрелка. Вы вводите данные, нажимая на стрелку, после чего выбираете нужную запись. Можно настроить в программе «Эксель» раскрывающийся список в течение нескольких минут, тем самым значительно улучшив скорость ввода данных. Ниже представлено описание этого процесса.
Откройте таблицу, на странице которой вы желаете создать раскрывающийся список. Создайте перечень пунктов, которые должны быть отражены в списке. Впишите данные в той последовательности, в которой они будут впоследствии открываться. Записи должны быть выполнены в одной и той же строке или столбце, и не должны содержать пустых ячеек.
Чтобы сделать список нужных элементов в отдельном листе, следует щелкнуть ярлычок листа, где вы хотите ввести данные. Выберите тип и затем выделите содержимое, которое появится в списке. Нажмите правой кнопкой мыши по выделенному диапазону ячеек и введите название для диапазона в поле «Имя», после чего выберите «OK». Вы можете защитить или скрыть лист, чтобы другие пользователи не могли вносить изменения в список.
Основы того, как в «Экселе» сделать раскрывающийся список
Нажмите на ячейку, которую вы выбрали для этой цели. Перейдите во вкладку «Данные» в ленте Microsoft Excel. Появится диалоговое окно под названием «Проверка данных». Перейдите во вкладку «Настройки» и нажмите «Список» из меню «Разрешить», расположенного в раскрывающемся списке. Нажмите на кнопку-переключатель в меню «Источник». Выберите список элементов, которые вы хотите включить в ваш раскрывающийся список в «Экселе».
Если вы создали имя диапазона в поле «Источник», введите значок равенства, после чего впишите название диапазона.
Необходимые настройки для создания
Установите или снимите флажок в графе «Игнорировать пустые» в зависимости от того, может ли ячейка, содержащая раскрывающийся список, оставаться пустой.
Перейдите на вкладку «Ввод сообщения», чтобы отобразить окно сообщения, открывающееся при нажатии выпадающего списка. Убедитесь, что флажок напротив пункта меню «Показать входное сообщение при выборе ячейки» установлен, и введите заголовок. Так вы выполните все указания по тому, как в «Экселе» сделать раскрывающийся список.
Перейдите на вкладку «Ошибка оповещения» для отображения сообщения об ошибке, если будут введены неправильные данные. Убедитесь, что предупреждение «Показать ошибку» включено путем установки соответствующего флажка.
Нажмите «OK» для того, чтобы были сохранены критерии проверки и создан раскрывающийся список.
Как в «Экселе» сделать раскрывающийся список – полезные примечания
Если выпадающая запись в списке больше, чем размер клетки, содержащей данный список, вы можете изменить ширину ячейки, чтобы просматривать весь текст.
Чтобы удалить раскрывающейся список, выберите ячейку, содержащую его. Перейдите на вкладку «Данные» в ленте Microsoft Excel. Нажмите кнопку «Проверка данных» из группы «Работа с данными». Перейдите на вкладку «Настройки», нажмите кнопку «Очистить все», а затем — «OK».
Как можно увидеть, руководство о том, как в «Экселе» сделать раскрывающийся список, является достаточно простым. Данная инструкция не требует каких-либо специальных знаний, необходимо лишь на начальном уровне уметь работать с электронными таблицами. Также стоит отметить, что в различных версиях программы «Эксель» пункты меню могут иметь некоторые различия с вышеуказанными, это следует учитывать при работе.
Для таблиц, которые используют постоянные и повторяющиеся данные (например фамилии сотрудников, номенклатура товара или процент скидки для клиента) чтобы не держать в голове и не ошибиться при наборе, существует возможность один раз создать стандартный список и при подстановке данных делать выборку из него. Данная статья позволит вам использовать 4 разных способа как в экселе сделать выпадающий список.
Способ 1 — горячие клавиши и раскрывающийся список в excel
Данный способ использования выпадающего списка по сути не является инструментом таблицы, который надо как либо настраивать или заполнять. Это встроенная функция (горячие клавиши) которая работает всегда. При заполнении какого либо столбца, вы можете нажать правой кнопкой мыши на пустой ячейке и в выпадающем списке выбрать пункт меню «Выбрать из раскрывающегося списка».
Этот же пункт меню можно запустить сочетанием клавиш Alt+»Стрелка вниз» и программа автоматически предложит в выпадающем списке значения ячеек, которые вы ранее заполняли данными. На изображении ниже программа предложила 4 варианта заполнения (дублирующиеся данные Excel не показывает). Единственное условие работы данного инструмента — это между ячейкой, в которую вы вводите данные из списка и самим списком не должно быть пустых ячеек.
Использование горячих клавиш для раскрытия выпадающего списка данных
При чем список для заполнения таким способом работает как в ячейке снизу, так и в ячейке сверху. Для верхней ячейки программа возьмет содержание списка из нижних значений. И опять же не должно быть пустой ячейки между данными и ячейкой для ввода.
Выпадающий список может работать и в верхней части с данными, которые ниже ячейки
Способ 2 — самый удобный, простой и наиболее гибкий
Данный способ предполагает создание отдельных данных для списка. При чем данные могут находится как на листе с таблицей, так и на другом листе файла Excel.
- Сперва необходимо создать список данных, который будет источником данных для подстановки в выпадающий список в excel. Выделите данные и нажмите правой кнопкой мыши. В выпадающем списке выберите пункт «Присвоить имя…».
Создание набора данных для списка
- В окне «Создание имени» задайте имя для вашего списка (это имя дальше будет использоваться в формуле подстановки). Имя должно быть без пробелов и начинаться с буквы.
Введите имя для набора данных
- Выделите ячейки (можно сразу несколько ячеек), в которых планируется создать выпадающий список. Во вкладке «ДАННЫЕ» вверху документа нажмите на «Проверка данных».
Создать выпадающий список можно сразу для нескольких ячеек
- В окне проверка вводимых значение в качестве типа данных задайте «Список». В строке «Источник:» введите знак равно и имя для ранее созданного списка. Данная формула позволит ввести значения только из списка, т.е. произведет проверку введенного значения и предложит варианты. Эти варианты и будут выпадающим списком.
Для создания проверки вводимых значений введите имя ранее созданного списка
При попытке ввести значение, которого нет в заданном списке, эксель выдаст ошибку.
Кроме списка можно вводить данные вручную. Если введенные данные не совпадут с одним из данных — программа выдаст ошибку
А при нажатии на кнопку выпадающего списка в ячейке вы увидите перечень значений из созданного ранее.
Способ 3 — как в excel сделать выпадающий список с использованием ActiveX
Чтобы воспользоваться этим способом, необходимо чтобы у вас была включена вкладка «РАЗРАБОТЧИК». По умолчанию эта вкладка отсутствует. Чтобы ее включить:
- Нажмите на «Файл» в левом верхнем углу приложения.
- Выберите пункт «Параметры» и нажмите на него.
- В окне настройки параметров Excel во вкладке «Настроить ленту» поставьте галочку напротив вкладки «Разработчик».
Включение вкладки «РАЗРАБОТЧИК»
Теперь вы сможете воспользоваться инструментом «Поле со списком (Элемент ActiveX)». Во вкладке «РАЗРАБОТЧИК» нажмите на кнопку «Вставить» и найдите в элементах ActiveX кнопку «Поле со списком (Элемент ActiveX)». Нажмите на нее.
Нарисуйте данный объект в excel выпадающий список в ячейке, где вам необходим выпадающий список.
Теперь необходимо настроить данный элемент. Чтобы это сделать, необходимо включить «Режим конструктора» и нажать на кнопку «Свойства». У вас должно открыться окно свойств (Properties).
С открытым окном свойств нажмите на ранее созданный элемент «Поле со списком». В списке свойств очень много параметров для настройки и вы сможете изучив их, настроить очень много начиная от отображения списка до специальных свойств данного объекта.
Но нас на этапе создания интересуют только три основных:
- ListFillRange — указывает диапазон ячеек, из которых будут браться значения для выпадающего списка. В моем примере я указал два столбца (A2:B7 — дальше покажу как это использовать). Если необходимо только одни значения указывается A2:A7.
- ListRows — количество данных в выпадающем списке. Элемент ActiveX отличается от первого способа тем, что можно указать большое количество данных.
- ColumnCount — указывает сколько столбцов данных указывать в выпадающем списке.
В строке ColumnCount я указал значение 2 и теперь в списке выпадающие данные выглядят вот так:
Как видите получился выпадающий список в excel с подстановкой данных из второго столбца с данными «Поставщик».
Вы можете легко свернуть или развернуть диапазон ячеек на листе Excel, используя функцию «Группировать» на вкладке «Данные». Например, если у Вас присутствует большой объем данных на листе Excel, то можно свернуть некоторое количество данных, связав их в группу, и сделать свой лист более организованным, имея только необходимые данные для отображения на листе в соответствии с Вашими требованиями. Ниже инструкция, которая подскажет Вам как свернуть или развернуть одновременно все сгруппированные ячейки.
Имеем сгруппированные ячейки вида как на рисунке 1.
Рисунок 1.
Как видно на рисунке у нас сгруппированы выделенные цветом диапазоны ячеек.
Для отображения лишь необходимой для нас информации (в данном примере нам интересны суммы) мы можем свернуть одновременно все сгруппированные ячейки нажатием левой кнопкой мышки на цифры «1, 2» находящийся в левой части экрана, как показано на рисунке 2. Цифра «» сворачивает все сгруппированные ячейки, цифра «» же, наоборот, разворачивает.
Рисунок 2.
Таким образом Вы можете управлять для Вас отображаемой информацией на Вашем листе Excel, делая просмотр более удобным. Так же Вы можете управлять и сгруппированными ячейками по вертикали.
Раскрывающийся список в Excel – один из методов упорядочивания данных, выполняющий как визуальную функцию, освобождая пространство на листе, так и функциональную – делая таблицу интерактивной и изменяемой. Существуют разные способы создания данного элемента, и об основных из них я хочу рассказать в рамках этого материала, чтобы даже у начинающих пользователей не осталось вопросов по этой теме.
Вариант 1: Группировка существующего списка
Создание выпадающего списка в Excel – задача нетрудная, она не требует от пользователя каких-то особых знаний. Первый метод подойдет в тех ситуациях, когда у вас уже есть готовый перечень чего-либо и вы хотите сделать так, чтобы каждая строка могла заменить собой другую, то есть названия можно было свободно менять, отталкиваясь от уже добавленных значений. Это может звучать сложно, но на деле все куда проще, в чем вы и убедитесь далее.
-
Обратите внимание на следующий скриншот. Это простой пример списка продуктов. Нужно сделать так, чтобы любое значение из этого списка можно было поставить в любую клетку столбца, с чем и поможет справиться выпадающий список. Сначала выделите все ячейки (игнорируя название столбца).
-
Перейдите на вкладку «Данные» и выберите инструмент «Проверка данных».
-
В новом окне в качестве типа данных укажите «Список», отыскав соответствующий вариант из выпадающего меню.
-
В качестве источника задайте те самые ячейки, выделив их левой кнопкой мыши. Нажмите «ОК» для применения настроек.
-
Теперь напротив каждой ячейки вы видите кнопку со стрелкой вниз, которая и отвечает за вызов раскрывающегося перечня.
-
Как видно, я поставил три одинаковых значения подряд, используя реализованный только что список. Точно так же можно менять все другие клетки, входящие в диапазон.
Комьюнити теперь в Телеграм
Подпишитесь и будьте в курсе последних IT-новостей
Подписаться
Вариант 2: Ручное добавление элементов списка
Еще один похожий вариант, но с немного другим смыслом. Подходит в тех случаях, когда у вас есть несколько клеток и значения, которые могут там располагаться. Выбор между ними и хочется осуществить при помощи рассматриваемой сегодня функции. Тогда принцип создания выглядит следующим образом:
-
Выделите заранее подготовленную область, где хотите расположить перечень. Перейдите на вкладку «Данные» и откройте «Проверка данных».
-
Выберите в качестве типа данных список, а в «Источник» перечислите значения, которые желаете в него добавить. Используйте «;», чтобы отделить одно значение от другого.
-
Подтвердите изменения и вернитесь к таблице. Теперь при развертывании списка вы сможете выбрать один из добавленных вариантов.
Вариант 3: Выбор массива с именем
В Excel есть функция, позволяющая присвоить массиву имя и в дальнейшем выбирать его при использовании формул. Как вы видели в предыдущих вариантах, объявление «Источника» при формировании выпадающего списка тоже похоже на создание формулы, а это значит, что в нем можно использовать массив, предварительно объявив его.
-
Выделите ячейки для заключения в единый массив, то есть те, значения которых должны отображаться в выпадающем списке. Слева сверху вы увидите отображение названия первой ячейки, по которому нужно кликнуть.
-
Измените имя массива на любое, начинающееся с буквы.
-
Создайте выпадающий список в ячейке листа, для чего снова перейдите в то же самое меню, предварительно выделив саму клетку.
-
В качестве источника укажите созданный массив, написав его название после «=».
-
В итоге у вас должен получиться перечень, куда входят все значения массива.
Вариант 4: Создание выпадающего списка из таблицы
Метод реализации данного варианта похож на предыдущий, но в качестве источника указывается сама таблица. Соответственно, ее сначала нужно сформировать из одного столбца, а затем перейти к созданию выпадающего перечня.
-
Выделите все ячейки, включая название столбца, и на главной вкладке нажмите кнопку «Сформировать как таблицу».
-
Выберите подходящий для вас тип оформления.
-
При появлении окна с расположением данных просто нажмите Enter, поскольку диапазон выбран предварительно.
-
Вы автоматически окажетесь на вкладке «Конструктор таблицы», где можете изменить ее имя на любое удобное, начинающееся с буквы.
-
Разверните список массивов, чтобы убедиться в наличии там созданной таблицы.
-
Используйте ее в качестве источника при создании выпадающего списка точно так же, как это было показано в предыдущих вариантах.
-
На следующем изображении вы видите, что список создан успешно и данные можно использовать в своих целях.
Только что вы увидели 4 способа создания выпадающего списка в ячейке Excel. Из инструкции понятно, что каждый из них обладает своими особенностями и подойдет при разном оформлении книги в программе. Однако во всех вариантах обращение происходит к одному и тому же инструменту, что значительно упрощает процесс запоминания всех принципов действий и позволяет выбрать любой из методов, когда это понадобится.
Как сделать скрываемые строки или столбцы в Excel (Эксель)
Статья о том, как реализовать в файле программы «Эксель» строки или столбцы, скрывающиеся за ненадобностью.
Опишем пошагово три способа реализации, обозначенной выше задачи – спрятать строки или столбцы.
Предположим у нас имеется огромная таблица в тысячу столбцов шириной и 150 тыс. строк высотой. Для осмысления и аналитики информации, содержащейся в таблице гораздо проще и приятнее работать с промежуточными итогами, скрывая лишние строки.
Скрыть лишние строки можно следующими способами:
- Первый способ — самый простой и примитивный заключается в том, чтобы выделить ненужные столбцы или строчки (сквозным выделением) и нажатием правой кнопки мыши в контекстном меню выбрать действие «Скрыть».
Показать скрытые строки или столбцы можно выделив нужный диапазон и выбрав в контекстном меню действие «Показать». - Второй способ немного сложнее. Данный способ заключается в группировке строк или столбцов при помощи опции «Группировать» во вкладке «Данные» меню быстрого доступа. Эта опция позволяет разбить информацию на группы и открывать или прятать строки(столбцы) нажатием(кликом) по крестику. Преимущество этого метода в том, что можно создавать несколько уровней группировки и отображать при необходимость данные только определенного уровня. То есть информация открывается ступенчато в виде «древа».
Эта опция позволяет разбить информацию на группы и открывать или прятать строки(столбцы) нажатием(кликом) по крестику. 
Преимущество этого метода в том, что можно создавать несколько уровней группировки и отображать при необходимость данные только определенного уровня. То есть информация открывается ступенчато в виде «древа».Группировка данных осуществляется следующим образом:
- Необходимо выделить столбцы или строки сквозным выделением (для каждой группы отдельно);
- Кликнуть по пиктограмме «Группировка» в разделе «Данные». Информация будет сгруппирована автоматически.
Если Вам недостаточно автоматической группировки, можете использовать опцию «Создание структуры…».
Следует помнить, что группировка захватывает соседнюю с выделенным диапазоном ячейку, поэтому выделять нужно на одну ячейку меньше, либо следует вставить между группируемыми диапазонами пустые строки — разделители.
-
Третий способскрыть ненужные данные и отображать только важную информацию — это сводные таблицы. Сводные таблицы преображают весь диапазон информации в нужную Вам структуру данных.
Важным преимуществом сводных таблиц является то, что вы можете конфигурировать структуру данных нужным вам способом в реальном времени в процессе проведения аналитики. Процесс создания сводной таблице описан в статье ниже по ссылке:
Как скрывать и группировать столбцы в Excel
Из этого руководства Вы узнаете и сможете научиться скрывать столбцы в Excel 2010-2013. Вы увидите, как работает стандартный функционал Excel для скрытия столбцов, а также научитесь группировать и разгруппировывать столбцы при помощи инструмента «Группировка».
Уметь скрывать столбцы в Excel очень полезно. Может быть множество причин не отображать на экране какую-то часть таблицы (листа):
- Необходимо сравнить два или более столбцов, но их разделяют несколько других столбцов. К примеру, Вы хотели бы сравнить столбцы A и Y, а для этого удобнее расположить их рядом. Кстати, в дополнение к этой теме, Вам может быть интересна статья Как закрепить области в Excel .
- Есть несколько вспомогательных столбцов с промежуточными расчётами или формулами, которые могут сбить с толку других пользователей.
- Вы хотели бы скрыть от посторонних глаз или защитить от редактирования некоторые важные формулы или информацию личного характера.
Читайте дальше, и вы узнаете, как Excel позволяет быстро и легко скрыть ненужные столбцы. Кроме того, из этой статьи Вы узнаете интересный способ скрыть столбцы с помощью инструмента «Группировка», который позволяет скрывать и отображать скрытые столбцы в одно действие.
Скрываем выбранные столбцы в Excel
Вы хотите скрыть один или несколько столбцов в таблице? Есть простой способ сделать это:
- Откройте лист Excel и выделите столбцы, которые необходимо скрыть.
Подсказка: Чтобы выделить несмежные столбцы, отметьте их щелчком левой кнопки мыши при нажатой клавише Ctrl.
- Кликните правой кнопкой мыши на одном из выбранных столбцов, чтобы вызвать контекстное меню, и выберите Скрыть (Hide) из списка доступных действий.
Подсказка: Для тех, кто любит быстрые клавиши. Скрыть выделенные столбцы можно нажатием Ctrl+0.
Подсказка: Вы можете найти команду Скрыть (Hide) на Ленте меню Главная > Ячейки > Формат > Скрыть и отобразить (Home > Cells > Format > Hide & UnHide).
Вуаля! Теперь Вы с лёгкостью сможете оставить для просмотра только нужные данные, а не нужные скрыть, чтобы они не отвлекали от текущей задачи.
Используем инструмент «Группировка», чтобы в один клик скрыть или отобразить столбцы
Те, кто много работает с таблицами, часто используют возможность скрыть и отобразить столбцы. Существует ещё один инструмент, который отлично справляется с этой задачей, – Вы оцените его по достоинству! Этот инструмент – «Группировка». Бывает так, что на одном листе есть несколько несмежных групп столбцов, которые нужно иногда скрывать или отображать – и делать это снова и снова. В такой ситуации группировка значительно упрощает задачу.
Когда Вы группируете столбцы, сверху над ними появляется горизонтальная черта, показывающая, какие столбцы выбраны для группировки и могут быть скрыты. Рядом с чертой Вы увидите маленькие иконки, которые позволяют скрывать и отображать скрытые данные буквально в один клик. Увидев такие иконки на листе, Вы сразу поймёте, где находятся скрытые столбцы и какие столбцы могут быть скрыты. Как это делается:
- Откройте лист Excel.
- Выберите ячейки, которые надо скрыть.
- Нажмите Shift+Alt+Стрелка вправо.
- Появится диалоговое окно Группирование (Group). Выберите Колонны (Columns) и нажмите OK, чтобы подтвердить выбор.
Подсказка: Еще один путь к этому же диалоговому окну: Данные >Группировать >Группировать (Data > Group > Group).
Подсказка: Чтобы отменить группировку выберите диапазон, содержащий сгруппированные столбцы, и нажмите Shift+Alt+Стрелка влево.
- Инструмент «Группировка» добавит специальные символы структуры на лист Excel, которые покажут какие именно столбцы входят в группу.
- Теперь по одному выделяйте столбцы, которые необходимо скрыть, и для каждого нажимайте Shift+Alt+Стрелка вправо.
Замечание: Объединить в группу можно только смежные столбцы. Если требуется скрыть несмежные столбцы, то придётся создавать отдельные группы.
- Как только Вы нажмёте сочетание клавиш Shift+Alt+Стрелка вправо, скрытые столбцы будут показаны, а возле черты над сгруппированными столбцами появится специальная иконка со знаком “–” (минус).
- Нажатие на минус скроет столбцы, и “–” превратится в “+“. Нажатие на плюс моментально отобразит все скрытые в этой группе столбцы.
- После выполнении группировки в верхнем левом углу появляются маленькие цифры. Их можно использовать для того, чтобы скрывать и отображать одновременно все группы одинакового уровня. Например, в таблице, показанной ниже, нажатие на цифру 1 скроет все столбцы, которые видны на этом рисунке, а нажатие на цифру 2 скроет столбцы С и Е. Это очень удобно, когда Вы создаёте иерархию и несколько уровней группировки.
Вот и всё! Вы научились пользоваться инструментом для скрытия столбцов в Excel. Кроме того, Вы научились группировать и разгруппировывать столбцы. Надеемся, что знание этих хитростей поможет Вам сделать привычную работу в Excel гораздо проще.
Normal testing
From the programmer’s worst friend
Группирование данных в Excel
Можно сворачивать/разворачивать строки на листе Excel.
Если строк много, то фича рулит.
Группированные строки в Excel
Как сгруппировать строки
- Выделить строки, которые хотим сгруппировать. Можно выделять как строки, так и просто ячейки «в столбце».
- Выбрать Данные >Группа и Структура >Группировать.
- Или в Office 2007 жмакнуть Shift+Alt+стрелка вправо. Появится диалог с вопросом: Что группировать будем — строки али колонки?
Официальная справка (очень рекомендую ознакомиться):
- Для отображения строк уровня щелкните соответствующие символы структуры.
- Для отображения или скрытия данных в многоуровневой структуре щелкайте символы структуры и .
Дело очень простое, как настройки маршрутизатора под Gentoo:
- После выделения столбца/строки жмем Alt+Shift+ для понижения уровня. Потом, при необходимости повысить уровень обратно — то же со .
- Уровней может быть (в Excel 2003) до восьми.
- Кроме минусов, можно кликать линии, от них идущие.
- Групповое изменение — маленькие кнопочки с цифрами в левом верхнем углу.
Но придумать себе проблему на эту тему все еще можно:
Как, епрст, сделать так, чтобы «плюсики» отображались вверху, а не внизу скрываемо/раскрываемого списка?
Office ХР
Данные > Группа и Структура > Настройка > убрать галку у опции «Итоги в строках под данными»
Office 2007
en
Data > Outline > Show the Outline dialog box > Settings > uncheck «Summary rows below detail»
ru
Данные > Структура > Показать меню (см. на картинке, куда надо нажать) > Настройки > убрать галку у опции «Итоги в строках под данными»
Смотри, куда жмакаешь
Раскрываемость списков неизменно и существенно повышается.
Странность в том, что для каждого листа эту операцию следует повторять… Разве что — использовать макрос, который будет это делать одним нажатием:
‘ Товарищ, это макрос для Office 2007
With ActiveSheet.Outline
.AutomaticStyles = False
.SummaryRow = xlAbove
.SummaryColumn = xlRight
End With
End Sub
А кто не знает, как использовать макросы в офисном пакете, тот просто нам завидует.
Плавная привязка записи к теме тестирования:
Дык, очевидно же, ёптыть…
Тест-кейсы в Excel забабахать может каждый бабуин, а как именно решить проблему с «поменять плюсики местами» — увы. Чтобы потом не сталкиваться с этой проблемой, сделал себе памятку в блоге. Всё это, конечно, неразрывно связано с тестированием…
То есть, не было никаких разрывов с ноября прошлого года.
Внимательное внимание!
Прием комментариев к этой записи завершён.
Тут нам не то, и не это. Не надо задавать вопросы о том, как сделать ещё что-то с этими прекрасными свертывающимися списками. Здесь даже не форум по Excel. Это блог о тестировании программного обеспечения.
Вы же любите тестировать, правда?
Ваша оценка:
Поделиться ссылкой:
Понравилось это:
Навигация по записям
Подскажите , а возможно ли группировать с изображением, чтоб картинка не болталась одна , когда группа свернута?
Не знаю, не пробовал.
У нас в компании есть форма, в которой мало того, что плюсик сверху остается, так еще и первая строка сгруппированного диапазона, стоящая напротив этого плюсика — не пропадает.
То есть в первую строку пишется заголовок, он виден и напротив него стоит «+», а потом выпадают составляющие проекта, например:
«+» ВЕНТИЛЯЦИЯ
— воздуховоды
— вентиляторы
— фильтры
И «Вентиляция» видна всегда. Как такое сделать? Знает кто-нибудь?
Первой линией группировки строк выберите линию выше первой составляющей проекта.
Добрый день!
Помогите, пожалуйста!
Как защитить лист и оставить возможность работы со структурой?
Необходимо защитить лист от внесения изменений и на листе имеется сгруппированные в структуру данные.
При установке обычной защиты теряется возможность работы с этой структурой.
Необходимо, чтобы наряду с защитой листа можно было еще пользоваться структурой.Т.е. чтобы пользователь мог просмотреть все в удобной форме, но не смог ничего изменить, что защищено.
Формой будут пользоваться несколько пользователей.Есть ячейки открытые для редактирования и закрытые.
Без группировок форма нечитабельна, но и без защиты она бесполезна((
Подскажите, пожалуйста, есть ли возможность совмещения и защиты листа/книги и сохранения действующей структуры?
красавы!. про плюсики и их место было полезно! два дня потратил!
Спасибо, было очень полезно
У меня к Вам 2 вопроса:
1. Можно ли так же «с помощью плюсика» группировать листы. т.е., например у меня склады по одной тематике, а я хочу их объединить как в одну папку, чтоб при нажатии на плюс или что либо другое, раскрывался подробный перечень имеющихся листов?
2. я выставляю защиту листа, оставляя лишь некоторые диапазоны для ввода пользователем. но тут проблема. на листе есть выпадающие списки и в случае защиты их нельзя менять, а пользователь должен это делать. уже снимала галочку защиты в формате объекта «выпадающий список», разрешала изменение диапазона с которым связана форма и ни-че-го!
1) не предполагаю, что подобное возможно.
2) создайте новый лист, раз на старом не работает, и сделайте там все с нуля.
Не подскажете, почему у меня кнопки «группировать», «разгруппировать» и многие другие неактивны? даже если я выделяю диапазон? какая-то защита? док старый и непонятно, куда рыть.. макросы есть, но там такого нет… включены фильтры и закрепление областей.. на новом листе то же самое… в новом документе — кнопки активны..
Вполне вероятно, что защита. Точнее на удалении сказать невозможно.
Здесь был вопрос, который интересует и меня. Но он остался без ответа.
Можно ли раскрывать/скрывать группу не только по +/-, но и нажатием, скажем, на заголовок этой группы?
«На заголовок» не скажу — для этого нужно написать/найти функцию, которая меняет значение линка после клика по нему (или по ячейке, в которой он находится), а искать это нет времени, простите.
1) Сделайте список на десять строк с таким вот содержимым:
blu
blo
спрятать
строку
через
макрос
bla
ble
bli
bldjad
2) сгруппируйте, если угодно, строки, но обязательно с третью по шестую.
3) Alt+F11 — добавьте два макроса
Sub HideRows()
Rows(«3:6»).SpecialCells(xlCellTypeConstants, 23).EntireRow.Hidden = True
End Sub
Sub ShowRows()
Rows(«3:6»).SpecialCells(xlCellTypeConstants, 23).EntireRow.Hidden = False
End Sub
Смысел ясен? Строки с 3 по 6 будут скрыты полностью. Второй макрос их раскрывает. При выполнении макроса HideRows на листе в нашем примере останутся только строки с латинскими буквами, а те, что содержат кириллицу, будут скрыты.
Кстати, чтобы управлять скрытием колонок, используйте такие команды
Columns(«A:С»).SpecialCells(xlCellTypeConstants, 23).EntireColumn.Hidden = True/False
4) Поставьте на лист две кнопки из меня «Формы», дайте им имена- «Спрятать» и «Показать», и назначьте выполнение этих макросов на эти кнопки. http://www.planetaexcel.ru/tip.php?aid=122 тут указано, как это делается.
Если найдете пример с обработкой клика по одной ячейке (которая будет заголовком группы) — опубликуйте тут, пожалуйста.
Спасибо! А нельзя ли при этом, чтобы скрывались не целые строки, а диапазон ячеек? Скажем, А3:В6.
Как в экселе скрыть строки плюсиком?
При работе с таблицами, в программе эксель, нередко приходится их редактировать, чтобы привести в прецизионный вид для руководства. Уметь скрывать строки плюсиком, является одним из элементов такой важной работы.
Давайте рассмотрим пошаговую инструкцию, как в экселе скрывать строки плюсиком.
Первый шаг. Открываем рабочую таблицу, в котором необходимо скрыть две строчки: выручка и себестоимость.
Второй шаг. Выделяем две строчки (3 и 4), которые будем скрывать.
Третий шаг. На верхней панели настройки эксель выбираем закладку «Данные», находим в ней блок «Структура» и нажимаем на иконку «Группировать».
Четвертый шаг. Из-под иконки выпадает дополнительное меню, в котором необходимо выбрать «Группировать».
Напротив, третий и четвертой строки слева появится плюсик, при нажатии на который, строки будут появляться. А при нажатии на минус, наоборот будут пропадать.
Если вам потребуется убрать данный плюсик, то выделяете данную область, снова переходите в закладку «Данные» и в блоке «Группировать», нажимаете на иконку «Разгруппировать».
Как сделать раскрывающийся список в Excel
Табличный редактор от компании Microsoft пользуется огромной популярностью. Такого успеха удалось добиться благодаря самому обширному функционалу среди всех редакторов, представленных на рынке программного обеспечения. Microsoft Excel является самым функциональным из них, но при этом он ещё и достаточно простой в использовании.
В любом случае, как бы то ни было, большинство пользователей даже не десятой части всего функционала не используют. Сегодня мы хотим рассказать Вам об одной интересной возможности – о создании раскрывающихся списков в Экселе. Наиболее часто они требуются для ведения баз данных небольших предприятий, поэтому если хотите сэкономить на услугах секретарши со знанием офисных программ – запоминайте!
Создаем раскрывающийся список в Экселе: способ первый
- Для начала нужно на свободном месте листа записать все необходимые данные, которые впоследствии должны будут выпадать при нажатии на список;
- Далее диапазону ячеек нужно задать имя. Смотрите, как это сделано на скриншоте и повторяйте. К слову, имя должно начинаться с буквы, пробелы недопустимы (используйте нижние подчёркивания);
В открывшемся окне выставьте всё точно также, как и на скриншоте ниже. Разумеется, сгруппированный список «Сотрудник» Вы должны изменить на свой, чтобы у Вас получился список с необходимыми выходными данными;
Готово! У нас получился выпадающий список!
Заметьте, насколько легко пользоваться программой Excel – любые задачи решаются действительно в несколько кликов, достаточно потратить порядка пары минут на изучение функционала. Именно по этой причине MS Office, как пакет офисных программ, является самым продвинутым и популярным на рынке.
Есть и более быстрый способ создания выпадающего списка, однако, у него присутствует небольшой минус – такой список может быть расположен исключительно под элементами, которые должны в нём выводиться.
Создаем раскрывающийся список в Excel: способ второй
Если Вас не смущает сказанное выше, в частности, расположение списка под элементами, то можете сэкономить на его создании пару минут. Нужно проделать следующие действия:
-
Задайте имя пустой ячейке под элементами будущего списка;
Одновременно нажимаем клавиши «лев. Alt + стрелка вниз», после чего у нас автоматически генерируется выпадающий список с располагающимися над ним элементами.
Как видите, здесь всё в разы проще, но есть ряд ограничений и с ними придётся мириться, поэтому если Вам важен функциональный список, то лучше всего пользоваться первым вариантом – он значительно более продвинутый.
Поздравляем, сегодня Вы научились делать раскрывающиеся списки в Экселе двумя стандартными методами, без подключения дополнительных модулей.
Похожие статьи
Skip to content
Одной из наиболее полезных функций при вводе данных является возможность использовать выпадающий список в Excel. Он позволяет выбирать значение из предварительно определенного перечня и разрешает вводить только те данные, которые соответствуют вашим требованиям. Мы предложим вам несколько простых способов, как создавать выпадающие списки в Excel. Более сложные способы, основанные на динамических диапазонах и использовании данных из других таблиц, мы также рассмотрим.
Зачем нужен выпадающий список?
Часто случается так, что в какой-то из колонок вашей таблицы нужно вводить одинаковые повторяющиеся значения. К примеру, фамилии сотрудников, названия товаров или делать выбор в ячейке Excel вида «да – нет». Что может случиться? Конечно, в первую очередь будут ошибки при вводе. Человеческий фактор ведь никто не отменял. Чем нам сие грозит? К примеру, когда мы решим подсчитать, сколько заказов выполнил каждый из менеджеров, то окажется, что фамилий больше, чем сотрудников. Далее придётся искать ошибки, исправлять их и вновь повторять расчет.
Ну и конечно же, все время руками вводить одни и те же слова – просто бессмысленная работа и потеря времени. Вот здесь-то выпадающие списки нам и пригодятся. При нажатии выпадает перечень заранее определённых значений, из которых просто необходимо указать какое-то одно.
Важно то, что вы теперь будете не писать, а выбирать их с помощью мыши или клавиатуры. Это значительно ускоряет работу, а также гарантирует защиту от случайных ошибок. Проверка того, что мы записали в таблицу, теперь уже не нужна.
1 — Самый быстрый способ создать выпадающий список.
Как проще всего добавить выпадающий список в таблицу Excel? Всего один щелчок правой кнопкой мыши по пустой клетке под столбцом с данными, затем команда контекстного меню «Выберите из раскрывающегося списка» (Choose from drop-down list). А можно просто стать в нужное место и нажать сочетание клавиш Alt+стрелка вниз. Появится отсортированный перечень уникальных ранее введенных значений.
Способ не работает, если нашу ячейку и столбец с записями отделяет хотя бы одна пустая строка или вы хотите ввести то, что еще не вводилось выше. На нашем примере это хорошо видно.
2 — Используем меню.
Давайте рассмотрим небольшой пример, в котором нам нужно постоянно вводить в таблицу одни и те же наименования товаров. Выпишите в столбик данные, которые мы будем использовать (например, названия товаров). В нашем примере — в диапазон G2:G7.
Выделите ячейку таблицы (можно сразу несколько), в которых хотите использовать ввод из заранее определенного перечня. Там мы разместим наш выпадающий список.
Далее в главном меню выберите на вкладке Данные – Проверка… (Data – Validation). Затем укажите пункт Тип данных (Allow) и выберите вариант Список (List). Поставьте курсор в поле Источник (Source) и впишите в него адреса с эталонными значениями элементов — в нашем случае G2:G7. Рекомендуется также использовать здесь абсолютные ссылки (для их установки нажмите клавишу F4).
Бонусом здесь идет возможность задать подсказку и сообщение об ошибке, если автоматически вставленное значение вы захотите изменить вручную. Для этого существуют вкладки Подсказка по вводу (Input Message) и Сообщение об ошибке (Error Alert).
В качестве источника значений для выпадающего списка в Excel можно использовать также и именованный диапазон.
К примеру, диапазону I2:I13, содержащему названия месяцев, можно присвоить наименование «месяцы». Затем имя можно ввести в поле «Источник».
Кроме того, и источник и в виде обычного диапазона ячеек, и именованный диапазон могут находиться на других листах вашей рабочей книги.
Но вы можете и не использовать диапазоны или ссылки, а просто определить возможные варианты прямо в поле «Источник». К примеру, чтобы реализовать в таблице Excel простейший выбор «да – нет», вы можете вписать туда –
Да;Нет
Используйте для разделения значений точку с запятой, запятую, либо другой символ, установленный у вас в качестве разделителя элементов. (Смотрите Панель управления — Часы и регион — Форматы — Дополнительно — Числа.)
3 — Создаем элемент управления.
Вставим на лист новый объект – элемент управления «Поле со списком» с последующей привязкой его к данным на листе Excel. Делаем:
- Откройте вкладку Разработчик (Developer). Если её не видно, то в Excel 2007 нужно нажать кнопку Офис – Параметры – флажок Отображать вкладку Разработчик на ленте (Office Button – Options – Show Developer Tab in the Ribbon) или в версии 2010–2013 и выше щелкните правой кнопкой мыши по ленте, выберите команду Настройка ленты (Customize Ribbon) и включите отображение вкладки Разработчик (Developer) с помощью флажка.
- Найдите нужный значок среди элементов управления (см.рисунок ниже).
Вставив элемент управления на рабочий лист, щелкните по нему правой кнопкой мышки и выберите в появившемся меню пункт «Формат объекта». Далее указываем диапазон ячеек, в котором записаны допустимые значения для ввода. В поле «Связь с ячейкой» укажем, куда именно поместить результат. Важно учитывать, что этим результатом будет не само значение из указанного нами диапазона, а только его порядковый номер.
Но нам ведь нужен не этот номер, а соответствующее ему слово. Используем функцию ИНДЕКС (INDEX в английском варианте). Она позволяет найти в списке значений одно из них соответственно его порядковому номеру. В качестве аргументов ИНДЕКС укажите диапазон ячеек (F5:F11) и адрес с полученным порядковым номером (F2).
Формулу в F3 запишем, как показано на рисунке:
=ИНДЕКС(F5:F11;F2)
Как и в предыдущем способе, здесь возможны ссылки на другие листы, на именованные диапазоны.
Обратите также внимание, что здесь мы не привязаны ни к какой конкретному месту таблицы. Таким списком Excel удобно пользоваться, поскольку его можно свободно «перетаскивать» мышкой в любое удобное место. Для этого на вкладке «Разработчик» нужно активизировать режим конструктора.
4 — Элемент ActiveX
Действуем аналогично предыдущему способу, но выбираем иконку чуть ниже — из раздела «Элементы ActiveX».
Определяем перечень допустимых значений (1). Обратите внимание, что здесь для показа можно выбирать сразу несколько колонок. Затем выбираем адрес, по которому будет вставлена нужная позиция из перечня (2).Указываем количество столбцов, которые будут использованы как исходные данные (3), и номер столбца, из которого будет происходить выбор для вставки на лист (4). Если укажете номер столбца 2, то в А5 будет вставлена не фамилия, а должность. Можно также указать количество строк, которое будет выведено в перечне. По умолчанию — 8. Остальные можно прокручивать мышкой (5).
Этот способ сложнее предыдущего, но зато возвращает сразу значение, а не его номер. Поэтому необходимость в промежуточной ячейке и обработке ее при помощи ИНДЕКС — отпадает. Думаю, таким списком пользоваться гораздо удобнее.
5 — Выпадающий список в Excel с автозаполнением
Задача: Создать перечень, в который будут автоматически добавляться значения из заданного динамического диапазона. Если в любую ячейку этого диапазона будут внесены изменения, то сразу же изменится и набор предлагаемых к выбору значений. Никакие формулы и настройки здесь корректировать не нужно.
Вот как автозаполнение может выглядеть на простом примере:
Способ 1. Укажите заведомо большой источник значений для списка.
Самая простая и несложная хитрость. В начале действуем по обычному алгоритму действий: в меню выбираем на вкладке Данные – Проверка … (Data – Validation). Из перечня Тип данных (Allow) выберите вариант Список (List). Поставьте курсор в поле Источник (Source). Зарезервируем в списке набор с большим запасом: например, до 55-й строки, хотя занято у нас только 7. Обязательно не забудьте поставить галочку в чекбоксе «Игнорировать пустые …». Тогда ваш «резерв» из пустых значений не будет вам мешать.
Действительно самый простой способ, но не слишком удобный. Ведь зарезервированное место может и закончиться…
Конечно, в качестве источника можно указать и весь столбец:
=$A:$A
Но обработка такого большого количества ячеек может несколько замедлить вычисления. Особенно в больших таблицах Excel.
Способ 2. Применяем именованный диапазон.
Именованный диапазон отличается от обычного тем, что ему присвоено определенное наименование. С ним гораздо проще работать, так как не нужно вводить ссылку, а достаточно просто указать его имя. Давайте рассмотрим небольшой пример.
В столбце А находятся имена сотрудников, которые мы будем вводить. Перечень может быть сколь угодно длинным. Нам необходимо, чтобы каждая новая запись включалась в раскрывающийся список без всяких дополнительных действий с нашей стороны.
Выделим имеющийся в нашем распоряжении перечень имен A2:A10. Затем присвоим ему название, заполнив поле «Имя», находящееся левее строки формул.
Так вы присвоите какое-то имя этому диапазону Excel.
Создадим в С2 перечень значений. В качестве источника для него укажем выражение
=имя
Недостатком работы с таким выпадающим списком в Excel является то, что новые значения нельзя просто дописывать в конец используемого перечня. Они останутся за пределами именованного диапазона. Если что-то нужно добавить, то их придется вставлять внутрь диапазона, использовав вставку пустой строки.
Перечень ещё можно отсортировать, чтобы удобно было пользоваться.
Главное неудобство пользования таким списком заключается в том, что используемый нами диапазон — статический. Автоматически его размеры измениться не могут. Согласитесь, не слишком удобный и технологичный способ. Слишком много ручных операций.
А теперь давайте пойдем дальше и посмотрим, как можно работать с динамическим диапазоном, который автоматически подстраивается под вводимые значения.
Способ 3. Выпадающий список на основе «умной» таблицы Excel.
Начиная с 2007 года таблица для Excel — уже не просто набор строк и столбцов. Если вы просто расположите показатели с привычном для нас табличном виде, то он не будет считать их таблицей. Существует специальное форматирование, после чего диапазон начинает вести себя как единое целое, приобретая целый ряд интересных свойств. В частности, он начинает сам отслеживать свои размеры, динамически изменяясь при корректировке данных.
Любой набор значений в таблице может быть таким образом преобразован. Например, A1:A8. Выделите их мышкой. Затем преобразуйте в таблицу, используя меню Главная — Форматировать как таблицу (Home — Format as Table). Укажите, что в первой строке у вас находится название столбца. Это будет «шапка» вашей таблицы. Внешний вид может быть любым: это не более чем внешнее оформление и ни на что больше оно не влияет.
Как уже было сказано выше, «умная» таблица хороша для нас тем, что динамически меняет свои размеры при добавлении в нее информации. Если в строку ниже нее вписать что-либо, то она тут же присоединит к себе её. Таким образом, новые значения можно просто дописывать. К примеру, впишите в A9 слово «кокос», и таблица тут же расширится до 9 строк.
Следовательно, автоматическое обновление набора используемой информации в списке можно организовать, если использовать содержимое какого-либо столбца «умной» таблицы.
Осталось только обозначить ее как источник. Проблема заключается в том, что программа в качестве источника в списке не понимает выражение вида
=Таблица1[Столбец1]
и не считает его формулой. Хотя в обычных выражениях на листе вашей рабочей книги это вполне будет работать. Эта конструкция обозначает ссылку на первый столбец. Но в поле «Источник» она почему-то игнорируется.
Чтобы использовать «умную таблицу» как источник, нам придется пойти на небольшую хитрость и воспользоваться функцией ДВССЫЛ (INDIRECT в английском варианте). Эта функция преобразует текстовую переменную в обычную ссылку.
Формула теперь будет выглядеть следующим образом:
=ДВССЫЛ(«Таблица5[Продукт]»)
Таблица5 — имя, автоматически присвоенное «умной таблице». У вас оно может быть другим. На вкладке меню Конструктор (Design) можно изменить стандартное имя на свое (но без пробелов!). По нему мы сможем потом адресоваться к нашей таблице на любом листе книги.
«Продукт» — название нашего первого и единственного столбца, присвоено по его заголовку.
Не забудьте также заключить все выражение в кавычки, чтобы обозначить его как текстовую переменную.
Теперь если в A9 вы допишете еще один фрукт (например, кокос), то он тут же автоматически появится и в нашем перечне. Аналогично будет, если мы что-то удалим. Задача автоматического увеличения выпадающего списка значений решена.
Надеемся, вы сможете теперь с помощью списков без ошибок вводить часто повторяющиеся данные в таблицу Excel при помощи выпадающего списка.
А вот еще полезная для вас информация:
Как сделать зависимый выпадающий список в Excel? — Одной из наиболее полезных функций проверки данных является возможность создания выпадающего списка, который позволяет выбирать значение из предварительно определенного перечня. Но как только вы начнете применять это в своих таблицах,…