Как сделать всплывающие таблицы в excel

Корпорация "Центр"

Как в офисе.

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

Как создать раскрывающийся список в excel

Иконка программы

При заполнении больших документов с множеством параметров появляется желание как-то упростить процесс. Для этого может быть полезным знание, как создать выпадающий список в excel. Это иногда может избавить от необходимости вводить одни и те же параметры. К примеру, когда вы заполняете названия брендов компании, которые повторяются, то в следующей ячейке страницы (без пропусков) нажмите Alt+стрелка вниз. У вас появится перечень введенных ранее данных этого столбца, из которого есть возможность выбрать необходимый вариант.

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

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

После этого появится дополнительное поле в верхнем меню под названием «Разработчик», где будет кнопка «Вставить». Там будет возможность выбрать пункт «Поле со списком» в подразделе «ActiveX». У вас появится возможность нарисовать поле в любом месте документа Эксель, где необходимо сделать выпадающий перечень. Далее этот элемент следует настроить:

  1. В той же вкладке перейдите на вкладку «Режим конструктора», нажмите кнопку «Свойства».
  2. Откроется много параметров, но основными являются только три, которые следует настроить.
  3. ListFillRange — здесь необходимо задать диапазон используемых значений.
  4. ListRows — тут задается количество данных для выпадающего меню.
  5. ColumnCount — здесь указывается количество столбцов, которые будут использованы.

Пошаговые действия

С функцией мультивыбора

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

  1. Создайте перечень данных, из которого будут браться значения.
  2. Выделите ячейки, для которых нужно сделать меню с мультивыбором.
  3. Во вкладке «Данные» в верхней части меню инструментов нажмите на кнопку «Проверка данных».
  4. В «Параметры» задайте значение «Список», а в «Источник» укажете диапазон ячеек, из которых будут браться значения.

Напротив ячеек будет возможность мультивыбора, данные из них будут заполняться на той же строке автоматически. Чтобы функция работала верно, нужно использовать программный код, который будет автоматически добавлять значения на ту же строку. Для этого по ярлычку списка кликните правой кнопкой и выберите «Исходный код». Без этого куска на языке программирования функция добавления работать не будет. Появится окно редактора Visual Basic, вставьте следующий кусок кода:

Private Sub Worksheet_Change(ByVal Target As Range)

On Error Resume Next

If Not Intersect(Target, Range("C2:C5«)) Is Nothing And Target.Cells.Count = 1 Then

If Len(Target.Offset(0, 1)) = 0 Then

Target.Offset(0, 1) = Target

Target.End(xlToRight).Offset(0, 1) = Target

С наполнением

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

  1. Выделите все ячейки, которые будут использованы для подстановки.
  2. Далее необходимо нажать на главной вкладке кнопку «форматировать как таблицу».
  3. Редактор Экселя позволит изменять столбцы по вашему желанию.

Раскрывающийся список в ячейке excel

Пример создания

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

  1. Создайте перечень значений, который будет использоваться для подстановки.
  2. Выделите все элементы столбца и нажмите правой кнопкой мыши на них.
  3. В меню выберите пункт «Присвоить имя. ».
  4. В открывшемся окне задайте имя, которое будет использовано в дальнейшей формуле. Оно должно начинаться с буквы алфавита, не иметь пробелов.
  5. Выделите ячейку или ячейки, которые будут содержать раскрывающееся меню. Перейдите во вкладку «Данные» и нажмите на «Проверка данных».
  6. В новом окне необходимо выбрать в пункте типа данных «Список», а ниже в пункте источник поставить знак «=» и ввести имя, которое было задано в 4 шаге инструкции.

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

В этом курсе:

  • Создание книги
    Статья
  • Вставка и удаление листов
    Статья
  • Как перемещать и копировать листы или их данные в Excel
    Статья
  • Печать листа или книги
    Статья
  • Использование Microsoft Excel в качестве калькулятора
    Статья
  • Автоматическое заполнение ячеек листа данными
    Статья
  • Создание раскрывающегося списка
    Статья

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

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

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

Таблица Excel, используемая в качестве источника для списка проверки данных

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

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

На ленте откройте вкладку Данные и нажмите кнопку Проверка данных.

Примечание: Если кнопка Проверка данных недоступна, возможно, лист защищен или является общим. Разблокируйте определенные области защищенной книги или отмените общий доступ к листу, а затем повторите шаг 3.

На вкладке Параметры в поле Тип данных выберите пункт Список.

Щелкните поле Источник и выделите диапазон списка. В примере данные находятся на листе "Города" в диапазоне A2:A9. Обратите внимание на то, что строка заголовков отсутствует в диапазоне, так как она не является одним из вариантов, доступных для выбора.

Параметры списка проверки данных

Если можно оставить ячейку пустой, установите флажок Игнорировать пустые ячейки.

Установите флажок Список допустимых значений

Откройте вкладку Сообщение для ввода.

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

Вкладка

Откройте вкладку Сообщение об ошибке.

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

Параметры сообщения об ошибке для раскрывающегося списка проверки данных

Не знаете, какой параметр выбрать в поле Вид?

Корпорация "Центр"

Чтобы отобразить сообщение, не препятствующее вводу данных, которые не содержатся в раскрывающемся списке, выберите вариант Сообщение или "Предупреждение". Если выбрать вариант "Сообщение", сообщение будет отображаться со значком , а если "Предупреждение" — со значком .

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

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

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

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

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

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

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

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

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

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

Выделите ячейки, для которых нужно ограничить ввод данных.

На вкладке Данные в группе Инструменты нажмите кнопку Проверка данных или Проверить.

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

Откройте вкладку Параметры и во всплывающем меню Разрешить выберите пункт Список.

Щелкните поле Источник и выделите на листе список допустимых элементов.

Диалоговое окно свернется, чтобы было видно весь лист.

Нажмите клавишу ВВОД или кнопку Развернуть , чтобы развернуть диалоговое окно, а затем нажмите кнопку ОК.

Значения также можно ввести непосредственно в поле Источник через запятую.

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

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

См. также

На новом листе введите данные, которые должны отображаться в раскрывающемся списке. Желательно, чтобы элементы списка содержались в таблице Excel.

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

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

На ленте откройте вкладку Данные и нажмите кнопку Проверка данных.

На вкладке Параметры в поле Разрешить выберите пункт Список.

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

Фрукты;Овощи;Зерновые культуры;Молочные продукты;Перекусы

Если можно оставить ячейку пустой, установите флажок Игнорировать пустые ячейки.

Установите флажок Список допустимых значений

Откройте вкладку Сообщение для ввода.

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

Откройте вкладку Сообщение об ошибке.

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

Нажмите кнопку ОК.

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

Дополнительные сведения

Вы всегда можете задать вопрос специалисту Excel Tech Community или попросить помощи в сообществе Answers community.

Корпорация "Центр"

Ссылка на основную публикацию