Источник списка должен быть списком с разделителями или ссылкой на одну: Связанные (зависимые) выпадающие списки

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

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

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

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

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

3. На вкладке

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

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

4. Откройте вкладку Параметры и во всплывающем меню

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

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

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

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

   Советы: 

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

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

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

См. также

Применение проверки данных к ячейкам

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

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

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

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

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

   Источник через запятую. Например:

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

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

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

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

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

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

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

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

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

Добавление списка или списка на лист в Excel

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

Добавление списка на лист

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

2. На вкладке Разработчик нажмите кнопку Вставить.

   Примечание: Если вкладка Разработчик не отображается, на вкладке Файл выберите Параметры > Настроить ленту. В списке Основные вкладки установите флажок для вкладки Разработчик и нажмите кнопку ОК.

3. В разделе Элементы управления формы выберите элемент управления Список (элемент управления формы).

4. Щелкните ячейку, в которой нужно создать список.

5. Нажмите кнопку Свойства и на вкладке Элемент управления задайте необходимые свойства:

   • В поле Формировать список по диапазону введите диапазон ячеек, содержащий список значений.

     Примечание: Если нужно отобразить в списке больше элементов, можно изменить размер шрифта для текста.

   • В поле Связь с ячейкой введите ссылку на ячейку.

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

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

     Примечание:  Если вы хотите выбрать параметр набора значений или списка значений, подумайте о том, чтобы использовать элемент ActiveX “Список”.

Добавление поля со списком на лист

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

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

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

   Примечание: Можно также создать список на другом листе той же книги.

2. На вкладке Разработчик нажмите кнопку Вставить.

   Примечание: Если вкладка Разработчик не отображается, на вкладке Файл выберите Параметры > Настроить ленту. В списке Основные вкладки установите флажок для вкладки Разработчик и нажмите кнопку ОК.

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

   • в разделе Элементы управления формы выберите элемент управления Поле со списком (элемент управления формы);

     ИЛИ:

   • в разделе Элементы ActiveX выберите элемент управления Поле со списком (элемент ActiveX).

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

Советы: 

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

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

Форматирование элемента управления формы “Поле со списком”

1. Щелкните правой кнопкой мыши поле со списком и выберите команду Формат объекта.

2. Откройте вкладку Элемент управления и настройте следующие параметры.

   • Формировать список по диапазону: введите диапазон ячеек, содержащий список элементов.

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

     Например, в ячейке C1 отображается значение 3, если выбрать пункт Фруктовое мороженое, так как это третий элемент в списке.

     Совет: Чтобы вместо номера отображать сам элемент, можно воспользоваться функцией ИНДЕКС. В нашем примере поле со списком связано с ячейкой B1, а диапазон ячеек для списка — A1:A2. Если в ячейку C1 ввести формулу =ИНДЕКС(A1:A5;B1), то при выборе третьего пункта в ячейке C1 появится текст “Фруктовое мороженое”.

   • Количество строк списка: количество строк, которые должны отображаться, если щелкнуть стрелку вниз. Например, если список содержит 10 элементов и вы не хотите использовать прокрутку, вместо значения по умолчанию введите 10. Если ввести число, которое меньше количества элементов в списке, появится полоса прокрутки.

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

Форматирование элемента ActiveX “Поле со списком”

1. На вкладке Разработчик нажмите кнопку Режим конструктора.

2. Щелкните правой кнопкой мыши поле со списком и выберите пункт Свойства. Откройте вкладку Alphabetic (По алфавиту) и измените нужные свойства.

   Вот как можно настроить свойства поля со списком на этом рисунке:

   Настраиваемое свойство	Действие
   Цвет заливки	Щелкните свойство BackColor (Цвет фона), щелкните стрелку вниз, откройте вкладку Pallet (Палитра) и выберите цвет.
   Тип, начертание или размер шрифта	Щелкните свойство Font (Шрифт), нажмите кнопку … и выберите тип, размер или начертание шрифта.
   Цвет шрифта	Щелкните свойство ForeColor (Цвет текста), щелкните стрелку вниз, откройте вкладку Pallet (Палитра) и выберите цвет.
   Связь с ячейкой для отображения значения, выбранного в списке	Щелкните свойство LinkedCell (Связанная ячейка).
   Связывание поля со списком и списка элементов	Щелкните поле рядом со свойством ListFillRange (Диапазон элементов списка) и укажите диапазон ячеек для списка.
   Изменение количества отображаемых элементов списка	Щелкните поле ListRows и введите число элементов.

3. Закройте область Properties (Свойства) и нажмите кнопку Режим конструктора.

4. Завершив форматирование, можно щелкнуть правой кнопкой мыши столбец, который содержит список, и выбрать команду Скрыть.

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

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

См. также

Обзор форм, элементов управления формы и ActiveX элементов управления на сайте

Добавление флажков и переключателей (элементы управления формы)

Как сделать выпадающий список с другого листа в excel 2007?

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

Как же правильно создать выпадающий список в Excel 2007? Рассмотрим ниже.

Для начала, необходимо создать список тех значений, которые будут предоставляться на выбор для ввода в ячейке (в нашем случае, это диапазон ячеек F2:F8). Далее выделите ячейку (или группу ячеек) , в которой, собственно, и будет выпадать наш список (в нашем случае, это диапазон ячеек A2:A22).

После этого у Вас появится окошко «Проверка вводимых значений». В первой закладке «Параметры» выбираете «Тип данных» — «Список», а в графе «Источник» указываете диапазон списка.

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

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

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

После того как все сделано, жмете ОК.

Выпадающий список готов. Теперь, при выборе любой из ячеек диапазона A2:A22, будет появляться подсказка и выпадающий список (стрелочка справа от ячейки). Вот так

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

Для удобства список можно разместить на другом листе документа Excel 2007, но для создания выпадающего списка теперь придется задать ему имя. Делается это просто. Выделяете список данных, кликаете по нему правой кнопкой мыши и выбираете «Имя диапазона…». В открывшемся окне «Создание имени» в графе «Имя» задаете имя списку (без пробелов) и проверяете правильность диапазона (диапазон можете сразу выделить и скопировать, он нам понадобится позже) и жмете ОК.

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

Все! Готово! Теперь Вы знаете, как правильно создать выпадающий список в документе Excel 2007!

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

Допустим, когда я создавал таблицу учета компьютерного оборудования (еще давно) у себя на работе, то чтобы сделать весь процесс работы более удобным и быстрым, я делал выпадающий список в определенных столбцах и вставлял туда определенные знaчения. И когда я заполнял столбец «Операционная система» (А ведь не на всех компах она одна и та же), то я забивал не сколько значeний (7, 8, 8.1, 10), а потом просто выбирал это всё одним нажатием кнопки мыши.

И таким образом уже не нужно вбивать в кажую ячейку версию винды, или копировать из одной ячейки и вставлять в другую. В общем не буду вас томить, давайте лучше приступим. Давайте я покажу вам, как создать выпадающий список в excel, используя данные c другого листа. Для этого создадим некоторую табличку, к которой мы сможем это применить. Я буду делать это в 2013 версии, но для других версий процесс идентичный, так что не переживайте.

Подготовка

1. На листе №1 создайте несколько столбцов (т.е. дайте им заголовки), например дата, отдeл, наименование проблемы, причина неисправности, фамилия специалиста техподдержки и итог устранения. Так вот, дату проблему и причину мы трогать не будем, а вот oтдел и фамилию специалиста, устранившего неисправность, мы занесем в шаблон, так как вариантов тут может быть немного. 
2. Чтобы было удобнее создайте новый лист и обзовите его как-нибудь. Я бы назвал «Данные». Именно сюда мы и будем вносить все возможные значения, из которых потом и будем выбирать то, что нам надо. 
3. На листе 2 (Данные) начинайте записывать значения. В начале сделаем всё это для графы «Отдел». Я напишу в столбик несколько отделов, например отдeл статистики, администрация, бухгалтерия, отдел планирования, oтдел информационных технологий, ну и хватит пожалуй. Вы можете писать что-то свое.
4. Точно также напишите в столбик фамилии сотрудников, которые занимаются устранением проблем, например Иванов, Петров, Сидоров, Смирнов. Записали? Отлично. Тогда идем дальше. То же самое делаем с «Итогом устранения». Пишем данные, например устранено, на устранении, устранение невозможно. 

Основные действия

1.  Всё, подготовку мы завершили, теперь приступаем к самому действию. Переходите на главный лист и выделите весь столбец «Отдел». Для этого можете нажать на букву, соответствующую столбцу.
2. Теперь идите в меню «Данные» и нажмите на «Проверка данных». 
3. В появившемся окне в строке «Тип данных» выберите «Список» и нажмите «ОК», после чего встаньте в поле «Источник». И не закрывая это окошко переходите на второй лист «Данные» и выделите все значения, которые соответствуют отделу. Нажмите ОК. 
4. Теперь снова идите на первый (главный) лист и поставьте курсор в любую ячейку «Отдела». Заметили, что правее появилась маленькая стрелочка? Нажмите на нее и вы увидите все значения, которые вы выделили на втором листике. 

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

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

Ну а на сегодня я свою статью заканчиваю. Я надеюсь, что то, что вы сегодня узнали пригодится вам при работе в экселе. Если статья вам понравилась, то конечно же не забудьте подписаться на обновления моего блога. Ну а вас я с нетерпением буду снова ждать на страницах моего блога. Удачи вам и пока-пока!

С уважением, Дмитрий Костин

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

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

Рис.1

Теперь нужно выделить список значений (без заголовка) и на вкладке «Формулы», нажать кнопку «Присвоить имя» (Рис.2).

Рис.2

В появившемся окне заполняем поле «Имя» и жмем Ok (Рис. 3).

Рис.3

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

Рис.4

Теперь на вкладке «Данные» жмем кнопку «Проверка данных» (Рис.5).

Рис.5

В появившемся окне в поле «Тип данных» выбираем значение «Список», в поле «Источник:» вводим знак “=” (равно) и набираем имя списка назначенного нами на Рис.3. Далее жмем Ok (Рис.6).

Рис.6

Теперь, если вы выделите одну из ячеек, для которых создавался список, то справа от ячейки появится кнопка с маленьким треугольником. Нажав на этот треугольник вы увидите список значений для этой ячейки и сможете быстро выбрать нужное значение (Рис.7).

Рис.7

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

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

Второй способ создания выпадающего списка в Excel более изящный и универсальный. Выделяете диапазон данных для выпадающего списка, затем нажимаете на пункт меню Формула — Диспетчер имен — Создать. Заполняете поле Имя, и копируете его (оно Вам понадобится позже). Имя должно начинаться с буквы или символа подчеркивания, и не должно содержать пробелов. Нажимаете ОК. Закрываете окно.

Затем выбираете ячейку, в которой будет выпадающий список Excel (можно сразу выделить несколько ячеек, если в них будут одинаковые выпадающие списки). После этого выбираете пункт меню Данные — Проверка данных, затем в окошке Тип данных выберите строку Список, в поле Источник поставьте знак равно, и без пробела вставьте то, что Вы копировали (значение поля Имя). Не забудьте про знак =, иначе ничего не получится. Выглядит надпись в поле Источник примерно так: =Имя_диапазона. Нажимаете ОК.

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

Это может быть опрос, тест, или анкета с заранее прописанными вариантами ответов. Можно, например, раздать файл Excel с анкетой группе людей, которых Вы хотите опросить, и получить назад уже файлы с ответами, или распечатанные ответы.

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

Чтобы защитить лист Excel, выбираете пункт меню Редактировать — Защитить лист, и прописываете пароль и действия, которые разрешены для пользователей.

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

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

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

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

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

Также Вы можете подписаться на блог, и узнавать о всех новых статьях.
Это не займет много времени. Просто нажмите на ссылку ниже:
    Подписаться на блог: Дорога к Бизнесу за Компьютером

Как создать в Excel выпадающий список с данными из другого файла — Сводные таблицы Excel 2010

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

Способ 1. Функция ДВССЫЛ

В простом случае можно использовать функцию ДВССЫЛ (INDIRECT), чтобы сформировать правильную ссылку на внешний файл. Например, если необходимо создать выпадающий список с содержимым ячеек А1:А10 с листа Список из файла Товары.xls, нужно открыть окно проверки данных через вкладку Данные – Проверка данных (Data – Validation) и в поле Источник (Source) ввести следующую конструкцию: =ДВССЫЛ("[Товары.xls]Список!$A$1:$A$10").

Чтобы сформировать правильную ссылку на внешний файл можно использовать функцию ДВССЫЛ

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

Если файл с исходными данными для списка лежит в другой папке, необходимо указать полный путь к файлу, например, следующим образом: =ДВССЫЛ("'C:\Поставщики\[Товары.xls]Список'!$A$1:$A$10"). В данном случае не забудьте заключить в апострофы полный путь к файлу и имя листа. Минус этого способа только один – выпадающий список будет корректно работать только в том случае, если файл Товары.xls открыт.

Способ 2. Импорт данных

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

Сначала откройте файл-источник, где находятся эталонные значения для выпадающего списка (назовем его, допустим, Справочник.xlsx). Выделите диапазон с данными для списка и отформатируйте его как таблицу с помощью кнопки Форматировать как таблицу на вкладке Главная (Home – Format as Table). Обратите внимание, что у такой таблицы предварительно должна быть сделана «шапка» – строка заголовка. После этого файл Справочник можно сохранить и закрыть.

Теперь откроем книгу, где мы хотим создать выпадающий список (условно назовем ее Бланк.xlsx). Вставим чистый лист (Alt+F11), выберем на вкладке Данные – Существующие подключения – Найти другие (Data – Existing Connections – Browse for more) и укажем наш файл Справочник.xlsx. Появится диалоговое окно, в котором Excel спросит нас о том, какую именно таблицу мы хотим импортировать (если их в файле было несколько).

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

После нажатия на ОК появится еще одно последнее окно, где можно указать удобную ячейку для импорта и, нажав на кнопку Свойства (Properties), задать частоту обновления информации.

После нажатия на ОК появится еще одно последнее окно

Тут можно включить флажок Обновить при открытии файла (Refresh on open), чтобы каждый раз при открытии этой книги иметь последнюю версию списка.

Можно включить флажок Обновить при открытии файла

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

Excel загрузит данные из созданной таблицы

Если выделить импортированный список (диапазон А2:А7 в нашем случае), то в строке формул можно увидеть его имя, которое он автоматически получает при вставке.

В строке формул можно увидеть имя импортированного списка

Это имя также можно увидеть в Диспетчере имен на вкладке Формулы (Formulas – Name Manager).

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

1. Выделяем ячейки, где хотим создать выпадающие списки.
2. На вкладке Данные жмем на кнопку Проверка данных (Data – Validation).
3. Выбираем в раскрывающемся списке разрешенных типов данных вариант Список (List) и вводим в поле Источник (Source) следующую формулу: =ДВССЫЛ("Таблица_Справочник"). В англоязычной версии Excel это будет =INDIRECT("Таблица_Справочник").

Осталось создать выпадающий список

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

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

После нажатия на ОК список начнет работать и автоматически обновляться

Выпадающие списки

Excel – проверка данных

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

Что такое проверка данных?

В Microsoft Excel функция проверки данных помогает контролировать, что может быть введенным в ваш рабочий лист. Например, вы можете:

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

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

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

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

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

С помощью проверки данных вы можете создать раскрывающийся список параметров в клетка.Есть 3 простых шага:

1. Создайте таблицу элементов ИЛИ создайте список

2. Назовите список

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

Примечание : Проверка данных не является надежной. Это можно обойти вставив данные в ячейку или выбрав «Очистить»> «Очистить все», на вкладке “Главная” ленты.

1. Создайте таблицу товаров

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

ПРИМЕЧАНИЕ. В списке проверки данных может отображаться до 32 767 элементов из список на листе.

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

3. Выберите ячейку в списке и на вкладке Вставка ленты щелкните Таблица

4. Поставьте галочку в «Моя таблица с заголовками» и нажмите В ПОРЯДКЕ

Теперь таблица является именованной таблицей Excel.

2. Назовите список

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

Вы можете использовать Диспетчер имен, как показано на видео, или поле имени. Оба метода описаны ниже. Метод Name Box быстрее, но вы не можете добавить комментарий.

Используйте диспетчер имен

Это метод, показанный на видео. Вы можете установить имя, объем и комментарии.

1. Выберите одну из ячеек в списке имен сотрудников.
2. На ленте щелкните вкладку Формулы и в группе Определенные имена щелкните Определить имя
3. В диалоговом окне «Новое имя» введите имя из одного слова для выбранного диапазона, например EmpNames
4. Оставьте объем работ как книгу
5. Щелкните поле «Ссылается на» (на листе ячейка, которая в настоящее время указана в поле «Ссылается на», будет выделена)
6. Чтобы выбрать все имена в списке, наведите указатель мыши на верхнюю часть ячейки заголовка (B1) и, когда появится черная стрелка, щелкните, чтобы выбрать все имена без ячейки заголовка. (Обязательно нажимайте, когда стрелка находится в ячейке B1, а не в кнопке заголовка столбца B)
7. Имя таблицы и имя столбца появятся в поле «Ссылается на»: = Таблица1 [Сотрудники]
8. Нажмите ОК, чтобы ввести имя.

Используйте поле имени

Это быстрый способ присвоить имя диапазону ячеек.

1. Щелкните вверху ячейки заголовка, чтобы выделить все ячейки. в списке (заголовок выделяться не будет).

2. Щелкните поле Имя слева от строки формул
3. Введите имя из одного слова для выбранного диапазона, например EmpNames, а затем нажмите клавишу Enter , чтобы ввести имя.

• ПРИМЕЧАНИЕ: После того, как вы нажмите Enter, имя исчезнет, ​​а имя таблицы появится в поле имени.

3.

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

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

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

3. Сначала щелкните в поле Разрешить
4. Затем в раскрывающемся списке Разрешить выберите Список

5. Щелкните поле Источник, введите знак равенства и список имя, например:
   = EmpNames
   ИЛИ нажмите клавишу F3, чтобы просмотреть список имен, щелкните имя и нажмите ОК

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

8. Щелкните элемент в раскрывающемся списке, чтобы ввести его в ячейку.

Включить Другой лист

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

Посмотрите это видео, чтобы увидеть шаги, и ознакомьтесь с письменными инструкциями здесь: Список из другой книги

Использовать список с разделителями

Вместо того, чтобы обращаться к списку элементов на листе, вы можете введите список в поле «Источник» через запятую. Например:

Да, Нет, Возможно

Примечания :

1. Этот метод проверки данных чувствителен к регистру – если пользователь вводит ДА, будет отображаться предупреждение об ошибке.
2. пробелов можно вводить до или после допустимых элементов, и сообщение об ошибке не отображается, например ” Да ” будет разрешено. (Спасибо Питеру за этот совет.)

Разрешить записи, которых нет в раскрывающемся списке

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

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

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

Предупреждение об ошибке выключения

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

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

Другие варианты предупреждений об ошибках проверки данных

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

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

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

Получить образец файла

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

Дополнительные руководства

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

Индекс проверки данных Страница

Изменить название продукта на код

Dependent Drop Down Lists Видео

Скрыть использованные предметы в раскрывающемся списке

Список из другой книги

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

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

Советы по проверке данных

Проверка данных с помощью комбинированного списка

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

Советы по проверке данных Excel и устранение неполадок

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

ПРИМЕЧАНИЕ : Если вам нужна помощь с настройкой раскрывающегося списка, перейдите на страницу раскрывающихся списков Excel

Раскрывающийся список открывается с выбранным пустым местом

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

Получите образец файла в разделе загрузок.

Причина: пустые ячейки в списке источников

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

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

ПРИМЕЧАНИЕ : Другая проблема может возникнуть, если есть пробелы в исходном списке – недопустимые записи могут быть разрешены в камерах.

Исправление

: использование динамического списка источников

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

1. Лучшим вариантом является именованная таблица Excel
2. Другой вариант – динамический именованный диапазон, основанный на формуле

1.Именованная таблица Excel

Чтобы увидеть шаги по созданию таблицы Excel, вы можете посмотреть это короткое видео. На странице «Создание таблицы Excel» есть письменные инструкции.

2. Динамический именованный диапазон с формулой

Чтобы увидеть шаги по настройке динамического именованного диапазона, вы можете посмотреть это короткое видеоурок. Формула СМЕЩЕНИЯ показана под видео.

Формула СМЕЩЕНИЯ, используемая в этом примере:

• = СМЕЩЕНИЕ (Цены! $ B $ 2,0,0, COUNTA (Цены! $ B: $ B) -1,1)

Отсутствующие элементы в раскрывающемся списке

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

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

Причина: раскрывающееся меню на основе статического списка

Некоторые раскрывающиеся списки основаны на статическом списке с использованием определенного диапазона, например

.

Если в ячейку B5 ввести новый элемент, он не появится в раскрывающемся списке

Исправление

: изменение источника проверки данных

Чтобы решить проблему с отсутствующим элементом, выполните следующие действия:

• Выберите ячейки проверки данных
• На вкладке «Данные» ленты Excel щелкните «Проверка данных».
• На вкладке «Настройки» измените адрес диапазона * в поле «Источник», чтобы включить новые элементы.
• Щелкните OK, чтобы завершить изменение.

* Имя в поле источника

Вместо адреса вы можете увидеть имя в поле «Источник», например:

Чтобы исправить это:

• На вкладке «Формулы» ленты Excel щелкните «Диспетчер имен».
• Выбрать имя в списке
• В поле «Ссылается на» измените адрес, чтобы включить новые элементы.
• Щелкните галочку, чтобы завершить изменение, затем закройте Диспетчер имен

Предотвращение: использование динамических списков источников

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

1. Именованная таблица Excel
2. Динамический именованный диапазон, основанный на формуле

См. Видео выше, в котором показаны шаги для обоих вариантов

Отсутствующие стрелы

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

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

Вот несколько причин отсутствия стрелки для проверки данных. Щелкните ссылку для подробностей:

Только активная ячейка

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

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

• Щелкните вкладку Разработчик на ленте и щелкните Вставить
• Щелкните поле со списком в элементах управления формой
• На листе перетащите, чтобы добавить поле со списком нужного размера.
• Щелкните правой кнопкой мыши поле со списком и выберите Управление форматированием
• В поле Диапазон ввода введите имя или адрес списка
• Нажмите ОК

Поиск предметов

Если объекты скрыты на листе, раскрывающийся список проверки данных стрелки также будут скрыты.

Чтобы сделать объекты видимыми, используйте сочетание клавиш – Ctrl + 6

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

• Щелкните вкладку Файл на ленте и щелкните Параметры
• Щелкните категорию Advanced
• Прокрутите вниз примерно наполовину до раздела Параметры отображения для этого Рабочая тетрадь.
• В настройке «Для объектов показать:» щелкните Все
• Нажмите ОК

Опция раскрывающегося списка

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

1. Выберите ячейку, содержащую список проверки данных
2. На ленте щелкните вкладку Данные
3. Щелкните вверху кнопки Data Validation, чтобы открыть диалоговое окно.
4. На вкладке «Настройки» установите флажок в раскрывающемся списке «В ячейке»
5. Нажмите ОК

Excel 2013 Windows 8

У вас есть связанное изображение в книге Excel 2013 в окне 8, стрелка проверки данных может не отображаться в активной ячейке, если только вы нажимаете кнопку мыши.

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

1. Выберите ячейку со списком проверки данных
2. Щелкните за пределами окна Excel (например, щелкните Рабочий стол или щелкните в окне браузера)
3. Щелкните окно Excel, появится стрелка, и вы сможете выберите элемент из списка.

Морозильные панели

Параметр Freeze Panes может вызвать проблемы со стрелками раскрывающегося списка в все версии Excel.Были дополнительные проблемы в Excel 97 и ранее.

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

Спасибо Джону Констеблю за этот совет.

В Excel 97, если раскрывающийся список Проверка данных находится в замороженной области окна, стрелка раскрывающегося списка не отображается когда ячейка выбрана. В качестве обходного пути используйте Window | Split вместо Окно | Стоп-окна

ПРИМЕЧАНИЕ. Эта проблема была исправлена ​​в более поздних версиях.

• Без замороженного стекла
• 
  С замороженным стеклом

Коррупция

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

Или попробуйте восстановить файл при его открытии:

1. На ленте щелкните Файл, а затем щелкните Открыть
2. Щелкните Компьютер, затем щелкните Обзор
3. Выберите файл с отсутствующими стрелками проверки данных
4. В нижней части окна «Открыть» щелкните стрелку справа от Кнопка открытия
5. Нажмите “Открыть и восстановить”
6. При появлении запроса нажмите «Восстановить».

Удалено макросом

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

Например, приведенный ниже макрос удаляет все фигуры на активном листе.

• Если при запуске этого макроса отображается стрелка проверки данных , она будет удалена вместе с другими фигурами на листе.

Безопасные макросы : Для безопасного удаления других фигур без удаления стрелок проверки данных см. Макросы для удаления объектов на веб-сайте Рона де Брюэна.

 Sub DeleteShapesALL ()
ПРЕДУПРЕЖДЕНИЕ: удаляет стрелку значения данных.
'если это видно
Дим ш как форма
Dim ws как рабочий лист
Установить ws = ActiveSheet
Для каждой ши в ws.Shapes
  ш.Удалить
Следующий ш
Концевой переводник 

Допустимые записи запрещены

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

Например, этот список позволяет выбрать Да или Нет.

Однако, если вы наберете no , появится сообщение, что это недопустимо.

Причина: ограниченный список

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

Исправление: точная запись

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

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

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

Допускаются недопустимые записи

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

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

– Пустые ячейки в списке источников

– Предупреждение об ошибке отключено

Пустые ячейки в списке источников

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

Примечание : Эта проблема с пустыми ячейками не возникает, если исходным списком является адрес диапазона , например 1 австралийский доллар: 10 австралийских долларов

Причина: пустые ячейки в именованном диапазоне

На снимке экрана ниже столбец Manager имеет раскрывающийся список с 5 имен.

Однако, если в этом столбце введено другое имя, ошибки нет. тревога.Имени Билла нет в списке, но он был разрешен в ячейке.

Это происходит, когда именованный диапазон используется в качестве источника списка, и везде есть пустая ячейка в этом именованном диапазоне. В этом примере в конце именованного диапазона есть пустая ячейка: MgrList

.

Исправление: выключить игнорировать пустой

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

1. Выберите ячейку, содержащую список проверки данных
2. Выбрать данные | Проверка
3. На вкладке Настройки снимите галочку с Игнорировать пустая коробка .
4. Нажмите ОК

Предупреждение об ошибке

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

Исправление

: включение предупреждения об ошибке

Чтобы включить оповещение:

1. Выберите ячейку, содержащую список проверки данных
2. Выбрать данные | Проверка
3. На вкладке «Предупреждение об ошибке» установите флажок «Показать ошибку ». предупреждение после ввода неверных данных поле.
4. Нажмите ОК

Проблема с неверными данными в круге

Если вы используете функцию «Неверные данные в круге» в Excel, вы можете иногда получать неожиданные результаты.

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

Максимальное количество элементов в раскрывающемся списке

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

• Список может отображаться до 32 767 элементов из списка на рабочий лист.
• Если вы вводите элементы в диалоговом окне проверки данных (разделенный list) ограничение составляет 256 символов , включая разделители.

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

Размер выпадающего шрифта и длина списка

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

Размер шрифта

• Шрифт в раскрывающемся списке проверки данных – Tahoma, размер 8.
• Форматирование ячейки не влияет на шрифт в раскрывающемся списке.

Длина списка

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

ПРИМЕЧАНИЕ ; С помощью программирования вы можете временно увеличить рабочий лист, чтобы размер шрифта проверки данных казался больше. См. Раздел для обходных путей, чтобы.

Список источников на разных листах

ПРИМЕЧАНИЕ : это влияет только на старые версии Excel

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

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

Чтобы избежать этой проблемы, воспользуйтесь одним из следующих способов:

Выпадающий список с символами

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

Причина: выпадающий шрифт

В раскрывающемся списке всегда отображается шрифт Tahoma . Это НЕ , на которое влияет форматирование:

• Ячейки исходного списка ИЛИ
• Ячейка с выпадающим списком

Исправление: использовать символы Tahoma

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

Получите образец файла в разделе загрузок.

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

Для создания списка символов:

1. На листе выберите ячейку, с которой вы хотите начать список символов
2. Нажмите клавишу Alt и на цифровой клавиатуре введите номер для символ, который вы хотите вставить.Несколько примеров показаны в список ниже, и вы можете поэкспериментировать, чтобы найти другие символы.
   Примечание. Чтобы увидеть все коды, перейдите в список альтернативных кодов в Википедии.
3. Нажмите Enter и введите другие символы в ячейки ниже. в В списке, показанном выше, клавиша Alt использовалась с номерами 30, 29 и 31, для создания списка со стрелками вверх и вниз и двунаправленной стрелкой.

Для создания выпадающего списка с символами:

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

Раскрывающийся список можно открыть с помощью мыши или клавиатуры, а прокручивать список можно с помощью мыши или сочетаний клавиш.

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

• Мышь: щелкните стрелку ячейки
• Клавиатура: нажмите Alt + стрелка вниз

Прокрутка элементов списка

Мышь

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

Клавиатура

• Нажимайте клавиши со стрелками вверх или вниз, для непрерывной прокрутки
• Нажимайте клавиши со стрелками вверх или вниз, чтобы прокручивать по одному элементу за раз.
• Нажмите кнопку «Домой» или «Завершить», чтобы перейти в начало или конец списка.
• Нажмите кнопку Page Up или Page Down для перехода на одну страницу вверх или вниз
• Нажмите кнопку Page Up или Page Down для непрерывной прокрутки страницы

Выпадающие списки на защищенном листе

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

• ячейка заблокирована
• лист защищен

ПРИМЕЧАНИЕ : В Excel 2000 и более ранних версиях

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

Выпадающие списки и события изменений

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

Чтобы увидеть пример, перейдите к образцам рабочих листов. страницу, а под заголовком Filters найдите Product List by Категория и загрузите список продуктов .zip файл.

В Excel 97 выбор элемента из раскрывающегося списка Проверка данных не вызывает событие изменения, если только элементы списка были введены в диалоговом окне «Проверка данных». В этой версии вы можете добавить кнопку на лист и запустить код, щелкнув кнопка. Чтобы увидеть пример, перейдите к Образцу На странице рабочих листов под заголовком Filters найдите Product Составьте список по категории и загрузите ProductsList97.застежка-молния файл.

Другой вариант в Excel 97 – использовать событие Calculate для запуска код. Для этого обратитесь к ячейке с проверкой данных в формуле на листе, например = ПОИСКПОЗ (C3, Список категорий, 0) . Затем добавьте код фильтра для события Calculate рабочего листа. Чтобы увидеть пример, перейдите на страницу Образцов рабочих листов и под заголовком Filters найдите Список продуктов по категории , и загрузите ProductsList97Calc.zip файл.

Временно расширить раскрывающийся список

Раскрывающийся список Проверка данных – это ширина ячейки, в которой он находится, минимум до 3/4 “. Вы можете использовать событие SelectionChange временно расширить столбец, когда он активен, а затем сузить его когда вы выбираете ячейку в другом столбце.

Например, с ячейками проверки данных в столбце A:

 Private Sub Worksheet_SelectionChange (значение ByVal как диапазон)
  Если Target.Счетчик> 1, затем выйти из подпрограммы
   Если Target.Column = 1, то
       Target.Columns.ColumnWidth = 20
   Еще
       Столбцы (1) .ColumnWidth = 5
   Конец, если
Конец подписки
 

Чтобы добавить этот код на рабочий лист:

1. Щелкните правой кнопкой мыши вкладку листа и выберите «Просмотреть код».
2. Скопируйте код и вставьте его в модуль кода.
3. Измените ссылку на столбец с 4, чтобы она соответствовала вашему рабочему листу.

Увеличить раскрывающийся список

Шрифт в раскрывающемся списке проверки данных – Tahoma, размер 8. В Excel нет настройки, чтобы увеличить этот размер шрифта, чтобы его было легче читать.

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

Есть несколько обходных путей, которые можно использовать для увеличения шрифта проверки данных:

1) Используйте макрос для отображения поля со списком или списка

2) Постоянно увеличивайте масштаб листа (вручную)

3) Временно увеличьте масштаб с помощью макроса

Использовать поле со списком или поле со списком

Чтобы упростить чтение элементов проверки данных, вы можете использовать программирование с полем со списком или списком, чтобы показать записи.Шрифт в них может быть любого размера, и вы также можете настроить их так, чтобы одновременно отображалось более 8 элементов по умолчанию.

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

Постоянное изменение настройки масштабирования

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

Вот снимок экрана с раскрывающимся списком с уровнем масштабирования 100%. В строке 2 ячейки отформатированы шрифтом Cambria, размер 12.

• Чтобы упростить чтение раскрывающегося списка, настройте масштаб рабочего листа на 120% или другой параметр, который вам больше нравится.
• Затем, чтобы остальная часть листа выглядела «без увеличения», уменьшите размер шрифта в ячейках листа и сузьте столбцы.

Вот тот же рабочий лист с масштабом 120%.Шрифт в строке 2 был уменьшен с 12 до 10, поэтому он выглядит примерно того же размера, что и раньше.

Временное изменение настройки масштабирования

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

Ниже приведены 3 примера макросов:

– Масштабирование при выборе одной конкретной ячейки

– Масштабирование при выборе одной из определенных ячеек

– Масштабирование при любой ячейке с проверкой данных список выбран

Увеличение масштаба при выделении определенной ячейки

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

 Private Sub Worksheet_SelectionChange (значение ByVal как диапазон)
  Если Target.Address = "$ A $ 2", то
    ActiveWindow.Zoom = 120
  Еще
    ActiveWindow.Zoom = 100
  Конец, если
Концевой переводник 

Чтобы добавить этот код на рабочий лист:

1. Щелкните правой кнопкой мыши вкладку листа и выберите «Просмотреть код».
2. Скопируйте код и вставьте его в модуль кода.
3. Измените ссылку на ячейку с $ A $ 2, чтобы она соответствовала вашему листу.

Увеличение масштаба при выборе определенных ячеек

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

 Private Sub Worksheet_SelectionChange (значение ByVal как диапазон)
  Если Target. Cells.Count> 1, то выйдите из Sub
  Если пересечение (Target, Range ("A1, B3, D9")) равно ничто, тогда
    ActiveWindow.Масштаб = 100
  Еще
    ActiveWindow.Zoom = 120
  Конец, если
Концевой переводник 

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

Следующий код изменит настройку масштабирования на 120% при любом выбирается ячейка со списком проверки данных.

 Private Sub Worksheet_SelectionChange (значение ByVal как диапазон)
  Уменьшить l Увеличить до тех пор, пока
  Dim lZoomDV как долго
  Dim lDVType As Long
  lZoom = 100
  lZoomDV = 120
  lDVType = 0

  Применение.EnableEvents = False
  При ошибке Возобновить Далее
  lDVType = Target.Validation.Type
  
    При ошибке GoTo errHandler
    Если lDVType <> 3 Тогда
      С ActiveWindow
        Если .Zoom <> lZoom То
          .Zoom = lZoom
        Конец, если
      Конец с
    Еще
      С ActiveWindow
        Если . Zoom <> lZoomDV Тогда
          .Zoom = lZoomDV
        Конец, если
      Конец с
    Конец, если

exitHandler:
  Application.EnableEvents = True
  Выйти из подводной лодки
errHandler:
  Перейти к exitHandler
Концевой переводник 

Получить файлы примеров

Выбрано пустое поле : В этом образце файла показан пример выбора пустого элемента при открытии раскрывающегося списка: Удалить Пробелы с файлом образца динамического диапазона

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

для недопустимых записей

Выпадающие символы : В этой книге есть пример выпадающего списка с символами шрифта Tahoma: Данные Список валидации с символами

Дополнительные руководства

Основы проверки данных

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

Заголовки букв в раскрывающемся списке

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

Советы по проверке данных

Проверка данных с помощью комбинированного списка

Список с разделителями

– excel.

newusers Импортировать списки?
Есть ли способ скопировать или импортировать участников (список адресов электронной почты). адреса) из глобального списка адресов в Excel таблица? Мне удалось вставить документ Word, но я ищу не те результаты. Спасибо!! Вы можете использовать LDIFDE для выполнения запроса, затем вы можете вставить результаты в Таблица Excel. написал в сообщении новости: [email protected] … > Есть ли способ скопировать или импортировать участников (список адресов электронной почты). > адреса) из глобального списка адресов в Excel > таблица? Я смог поместить в Wo…

Где я могу найти список известных проблем с CRM 1.2 и обходных путей?
Я запускаю тест CRM 1.2 от MSDN Universal, и у меня есть номер мелких странных проблем. Есть ли список известных проблем и обходных путей? размещено где угодно? Были ли выпущены какие-либо патчи или другие технические обновления статьи? Есть ли какая-то отслеживаемая группа новостей службы поддержки или что-то еще, что я стоит ли покупать, чтобы иметь возможность более эффективно оценивать товар? Спасибо, Билл Уолтер Это единственная общедоступная группа новостей по CRM, кроме иностранного языка. версии.Для поддержки вам потребуется соглашение с Microsoft MBS. Чувствовать Здесь можно задать любые технические вопросы, так как обычно мы можем получить …

Может ли отправленный список рассылки Показывать только одного получателя в сообщениях электронной почты
Это переписывание предыдущего сообщения, которое не было решено. Я повторяя это здесь, чтобы получить новый старт / лучший ответ. Также обратите внимание что я не очень разбираюсь в технике, поэтому, если есть решение этой проблемы, пожалуйста, посоветуйте мне очень простым языком.Заранее спасибо. Сценарий: я новый пользователь Outlook 2007. Допустим, у меня большая группа клиентов. Я хочу создать шаблонное электронное письмо для всех получатели. Однако я хочу, чтобы каждое отправленное письмо получал один клиент, и в то же время, чтобы этот клиент не знал о другом клиенты (Это …

Проблема со списком рассылки Outlook 2000
Я давно создал список рассылки. В нем было 10 человек. В прошлый раз я изменяю, добавляю или удаляю людей в списке рассылки и использую это отправить. Я обнаружил, что электронное письмо отправляют только 3 людям. Это должны быть отправлены 10 разным людям. Скажите, пожалуйста, почему это произойдет и как это решить. Большое спасибо, Амон …

Сравнение имени и фамилии в двух списках # 2
У меня есть два источника данных, каждый из которых содержит около 8000 имен отдельно поля для имени и фамилии. Я могу поместить их на отдельные листы или добавить один к другому.Мне нужно сопоставить их и узнать где их нет. Запутался с консолидацией, но не уверен, что это удастся работать более чем с одним столбцом за раз. К вашему сведению, один набор данных взят из база данных школьного транспорта и набор данных два взяты из основного база данных учеников из того же школьного округа. ———————————————— ~~ Сообщение отправлено с http://www.ExcelTip.com/ ~~ Просмотр и публикация сообщений usenet dir . ..

Невозможно увидеть получателей в глобальном списке при использовании OWA
У меня Exchange 2003 с Outlook 2003 в качестве клиента.Каждый раз, когда я использую OWA и пытаюсь создать новое письмо, нажимаю TO, чтобы выбрать имя получателя из глобального списка, я получаю другой экран, который, как я ввести имя для поиска. Я хочу отображать имена, чтобы я мог выбирать из список. Вот экран, который я получаю каждый раз, когда нажимаю TO. Я должен видеть имя каждого, чтобы я мог выбрать. Если я подключаюсь локально, я получаю список, и проблем нет. Этот бывает только с OWA. Спасибо ————————————————– —————————— OWA d…

При загрузке в Outlook 2003 создается файл «список потребностей» вместо файла d / l
Когда у меня есть электронное письмо с вложением, я перехожу в скачать, скачивает и иногда переименовывает в «список потребностей». Он того же размера, что и оригинал файл, однако он не может быть открыт какой-либо известной программой. В в данном случае это должен быть файл autocad .dwg, но он имеет случалось пару раз. имя в поле сохранения правильно, вы загружаете его, переходите в этот каталог и нет того, чего вы ожидали, вместо этого нужно список есть.Кто-нибудь из присутствующих здесь раньше? Я искал в Google, и группы новостей и файлы MShelp и ничего не нашел. Любые идеи будут …

Сортировка большого списка
Привет всем! Иметь проблему. Я разрабатываю приложение, которое читает большую базу данных файл (DBF) и отображение его в элементе управления списком. Из-за размера файл (> 15 МБ, позже будет увеличен) Использую виртуальный список (владелец данные). Но теперь мне нужно отсортировать данные в списке. AFAIK, чтобы подтвердить это, я должен прочитать весь файл.В этом случае моя программа запускается через несколько минут !!! Это не должно быть так [(C) Pink Floyd] … Есть ли способы как можно быстрее загрузить большой объем данных? Или я просто должен смириться, потому что сортировка большого количества d . ..

Списки рассылки не реплицируются
Мы готовимся к переходу с Exchange 5.5 на 2003. Я Я использую инструменты exdeploy и выполняю все шаги. По какой-то причине единственное, что не удается replicate – это списки рассылки.Когда я открываю систему Manager и посмотрите на почтовые ящики 5.5, я не вижу ни одного списки распространения … так что я не могу их переместить к серверу 2003 года. Любые идеи? Вы смотрели на это: http://www.msexchange.org/tutorials/Site-Consolidation-Tools-Exchange2003.ht мл “KJ” написал в сообщении. news: 025d01c499d3 $ c8b8f410 $ a601280a …

Все списки адресов # 2
Я пытаюсь скрыть адреса «Все пользователи», «Все группы» и «Все контакты» списки под пустым контейнером “Все списки адресов” и укажите компанию конкретные списки адресов «Все».Каждый раз, когда я пытаюсь скрыть ненужные списки адресов, они все еще видны пользователям, и когда они пытаются просмотреть список, появляется ошибка “Эта закладка Is Not Valid ». Есть ли способ отфильтровать списки адресов “Все пользователи и т. Д.”, Чтобы один домен может видеть только свой домен? или есть успешный способ ‘скрыть’ ли …

Вставить список рисунков не работает для изображений
Версия: 2004 Операционная система: Mac OS X 10.5 (Leopard) Процессор: Intel Я могу сгенерировать список всех вставленных мной объектов по заголовку: Рисунок, Пример, Таблица, Песня, Диаграмма.Но когда я пытаюсь создать список вставленных мною изображений (заголовок читается как Изображение …), я получаю следующее сообщение: Ошибка! Таблицы цифр не найдены. Это как если бы макрос, который читает список заголовков, не имеет изображения во внутреннем словаре. У меня есть подписи к рисункам в текстовом поле, которое отформатировано как «плотное», чтобы можно было обтекать текстом; может это быть причиной проблемы? Если это так, h …

ячейка, чтобы иметь значения с разделителями-запятыми на основе текста
У меня есть таблица с именем «220_reference» с именем столбца «Номер детали», имеющим примерное значение ниже: 4047122 (Все тире нет. ), 4057222 (№ All Dash), 4058222 (№ All Dash), 4060122 (Все номера тире) Все значение находится в одной ячейке, представляющей «Номер детали». столбец (определяется как общий тип, я полагаю, текст). Достаточно просто. Но мне нужно взять любое число, у которого есть “(All Dash нет.) “после него и поиск по столбцу в другой таблице, чтобы получить любой строки, в которых есть этот номер (текст). Имя другой таблицы – “220” с …

НОВОЕ МЕНЮ
Привет, В Excel 2003 можно было создать новое меню, а затем выбирать команды для такое новое меню.Раньше я создавал список файлов для открытия (отличный от «самый последний» список). Как это сделать в Excel 2007? Спасибо. «CF» написал в сообщении. новости: [email protected] … > Привет, > В Excel 2003 можно было создать новое меню, а затем выбирать команды для > вот такое новое меню. Раньше я создавал список файлов для открытия (отличных от > в > «самый последний» список). > & g …

Список потерянных контактов 04-22-10
Я занимаюсь живой почтой Windows … у меня не было проблем с получением моих контактов из Outlook Express. Теперь, когда я синхронизировал сообщения из моей учетной записи электронной почты Hotmail и моего Telus адрес электронной почты в Windows Live, мой список контактов состоит только из двух контактов из учетной записи Hotmail. Все остальные ушли. Они все еще в Адресная книга, но я не могу получить к ней доступ Как мне вернуть его в Почту Windows Live ??? Помоги пожалуйста.Вы вошли в учетную запись Hotmail при запуске Windows Live Mail. Это необязательно и никак не связано с получением вашей почты. Когда вы входите в систему, синхронизация WLM …

список рассылки
привет всем, мне нужно создать список контактов из контакта Outlook для обмена. это потому, что мне нужно централизовать адресный контакт в глобальном списке адресов на обмен. все эти контакты будут отправлены через Outlook с внешней учетной записью провайдера. могу ли я создать этот тип списка рассылки на бирже или это проще делиться одним контактом мировоззрения с другим.Благодарность Джулио Привет, Если вы создаете контакты с поддержкой почты в своем AD, они будут опубликованы. тебе GAL. Это при условии, что у вас нет настраиваемых глобальных списков адресов, проблем с AD и т. Д. и Т. Д. Оливер Вы также можете поделиться адресными книгами …

Поставщики со списком самых популярных товаров
Может ли кто-нибудь сообщить мне, как отображать поставщиков в списке самых популярных товаров? Мне это очень нужно. Я пробовал свой путь, но безуспешно. – Огромное спасибо Установите запрашиваемую вами пробную версию RMS Toolkit и установите флажок Custom. Отчеты / шаблоны во время установки.Теперь запустите Manager, Reports, Custom, Top Items Report. Этот отчет включает Поставщики, отдел, категория и подописание в виде столбцов и фильтра опции. В наш набор инструментов RMS входит более 100 бесплатных отчетов и шаблонов. пробный. Посетите: www.digitalretailer.com/trial Грег Решения для цифровой розничной торговли www.digitalretailer.com/RMSaddins (v1.x и …

Отправлять электронные письма с именем, отличным от того, которое указано в GAL
Привет всем, у меня быстрый вопрос: Мой клиент недоволен тем, как его электронные письма отправляются из MS Outlook.В глобальном списке адресов на нашем сервере перечислены имена как [Фамилия], [Имя]. Это так, как этого хочет большинство наших клиентов. Однако он хочет, чтобы его [Имя Фамилия]. Итак, все получатели получают от него письма увидит его имя как имя, фамилию. Мы не можем сделать это для него наш GAL, так как это, очевидно, вызовет несоответствие, если мы сделаем это только для одного имени. Но я подумал, что опубликую здесь, чтобы узнать, есть ли что-нибудь еще можно сделать, чтобы разместить его. Заранее благодарим за вашу помощь …

Импорт списка из Excel в Word Проблемы с расстоянием между этикетками при печати
Когда я импортирую свой список рассылки из Excel в Word для печати почтовых этикеток (30 на страницу) на моих этикетках большие промежутки между строками. Например они выглядят так: Джон Доу P.O. Коробка 000 Город, штат, почтовый индекс А потом, если у меня есть название компании до Джона Доу, его обрезают. Есть способ уменьшить расстояние между линиями, чтобы оно лучше подходило? у меня есть придумали, как делать по одному, но у нас есть 4000 таких, которые нужно распечатать! Я бы хотел, чтобы это выглядело так: Джон Доу P.O. Коробка 000 Город, штат, почтовый индекс Спасибо за любую помощь! Звучит как вопрос для группы новостей Word, не так…

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

Введите текст из раскрывающегося списка в Excel
Здравствуйте, Я создал раскрывающийся список, но список становится слишком большим и пользователь должен прокрутить список вниз, чтобы выбрать данные. Есть ли функция, которую я могу использовать для отображения данных, начинающихся с одной и той же строки. Например: если я наберу «Будь» в своем поле, в поле может отобразиться список. которые начинаются с “БЫТЬ или БЫТЬ”.Я создал раскрывающийся список, используя «Данные», выбрал «Проверка» и на вкладке «Разрешить» выберите «Список». Спасибо за вашу помощь. Бекси Бекси Если ваш раскрывающийся список из проверки данных, у вас не может быть автозаполнения. T …

гиперссылок из списка? используя excel 2000
jacob Хорошо, я перейду к вопросу. Я использую функцию списка проверки данных в A1 с диапазоном имена в Z-файле. В списке указаны имя и номер телефона, а также адрес электронной почты.Я пытаюсь вставить гиперссылку электронной почты в в той же ячейке, поэтому, когда элемент списка выбран из раскрывающегося списка, вы можете нажать на A1, чтобы отправить адрес электронной почты. Гиперссылка электронной почты было бы нужно изменить вместе с названием. Я не думаю, что это сработает, так что насчет создания интерактивной ссылки в A2, который меняется с …

Могу ли я объединить список адресов электронной почты из exel в Outlook?
без необходимости вводить каждый по отдельности, могу ли я объединить адреса электронной почты в группу? Привет MDuda См. Эту страницу http: // www.rondebruin.nl/mail/importcontacts.htm – С уважением, Рон де Брюин http://www.rondebruin.nl/tips.htm «MDuda» написал в сообщении news: [email protected] … > Без необходимости вводить каждый по отдельности, могу ли я объединить адреса электронной почты > в группу? . ..

Какой файл искать на моем жестком диске, чтобы восстановить мой список контактов Entourage?
Версия: 2004 Операционная система: Mac OS X 10.6 (Snow Leopard) Привет,

У меня есть резервная копия всего моего жесткого диска на резервном диске.Хорошие новости.

Но …

Мне только что установили новый диск, и мне пришлось переустановить Entourage (слишком долго, чтобы объяснять почему), и мои контакты Entourage не были перенесены на новый водить машину.

Я хочу импортировать свой файл контактов со своего резервного диска, но не знаю, какой файл искать и где искать.

Есть предложения? Спасибо 06.02.10 6:45 в статье 59bb259f.-1@webc …

список зависимых полей со списком с косвенной ссылкой
Я хотел бы создать зависимое поле со списком.У меня есть ячейка, связанная с другим полем со списком, скажем, C1. У меня есть один список в D1: D4, другой список в E1: E3. В C2 у меня есть значение «D1: D4», в C3 – «E1: E3». Для диапазона ввода для поля со списком я попробовал “= КОСВЕННО (ЕСЛИ (C1 = 1, C2, C3))”, но получил только пустой список. Это «незаконно»? Спасибо, Июэ Дебра Далглиш показывает, как это сделать с помощью Data | Validation. http://www.contextures.com/xlDataVal02.html Может, тебе стоит использовать ее технику в своих комбинированных списках. Если нет, то отправьте ответ с дополнительной информацией … Выпадающие списки из…

Как скопировать список имен файлов в документ Word?
У меня есть список имен файлов в каталоге, и я хочу скопировать и вставить имена файлов только в документ WORD. Как мне выполнить эту задачу? См. Бесплатную утилиту Printfolders, которую вы можете загрузить с моего веб-сайта. сайт. – <>> <> <<>> <<> <>> <> <<> <>> <<>> <<> Грэм Мэр – лучший игрок конкурса Word Мой сайт www.gmayor.com Веб-сайт Word MVP http://word.mvps.org <>> <> <<>> <<> <>> <> <<> <>> <<>> <<> & q …

ПРОБЛЕМЫ ПРОВЕРКИ ДАННЫХ – Недовольство Excel [Книга]

Я менеджер инженерной компании и нанял безработного политолога для ввода данных и других делопроизводства. Мы не платим ему много, но наше дело в том, что он узнает о компьютерах за наши деньги, когда занимается вводом данных.Есть только одна проблема: он так плохо печатает, что делает массу ошибок – добавляет лишние числа, опускает некоторые из них, даже набирает буквы вместо цифр. Разве Excel не может каким-то образом отметить ошибку до того, как он введет данные?

Секрет в использовании проверки данных. Щелкните ячейку (или группу выбранных ячеек), а затем включите функцию проверки, выбрав «Данные» → «Проверка» и щелкнув вкладку «Настройки». В раскрывающемся меню Разрешить выберите тип критерия проверки, который вы хотите использовать, а затем укажите его параметры в раскрывающихся меню, которые появляются ниже.Тип проверки, выбранный в раскрывающемся списке Разрешить, определяет, какие другие параметры отображаются ниже. Например, если вы выбираете Целое число, вы можете указать, должны ли введенные значения находиться между двумя другими значениями, а не между двумя другими значениями, или меньше или больше значения. Вы можете жестко закодировать значение, выбрав «равно» в раскрывающемся меню «Данные» и введя значение в поле «Значение». На рис. 1-19 показаны типы критериев, которые вы можете создать.

Рисунок 1-19. Ограничить действия коллег еще никогда не было так просто.

Например, если он должен вводить почтовые индексы, и вы хотите убедиться, что он не пропустил цифры и не добавил лишних, установите правило проверки длины текста, чтобы требовать число от 5 до 5. Если он должен ввести 94607, а он по ошибке набирает 9460, он получит сообщение об ошибке: «Введенное вами значение недействительно».

Если вы хотите убедиться, что он не забыл ввести данные в критическую ячейку, снимите флажок «Игнорировать пустое поле» в диалоговом окне «Проверка данных».Это заставляет его что-то вводить в камеру. (При желании вы можете точно определить, что ему нужно ввести, выбрав соответствующую запись в раскрывающемся списке Разрешить.) Если поле «Игнорировать пустое» неактивно, временно выберите что-либо, кроме «Любое значение» в раскрывающемся списке Разрешить. ; это делает доступным поле «Игнорировать пустое». После того, как вы снимете этот флажок, вы можете вернуться к «Любому значению», если хотите, а поле «Игнорировать пустое», хотя и выделено серым, останется снятым, так что ему придется вводить что-то в ячейку.

Если вы хотите запретить пользователям вводить данные в ячейку, вы можете ограничить их выбор записей из списка. Уловка: выберите Список в раскрывающемся меню Разрешить. Когда вы это сделаете, появится поле под названием «Источник». Вы можете определить значения списка, выбрав диапазон ячеек на листе или введя значения в поле «Источник», разделив их запятыми. Записи в поле «Источник» могут включать пробелы, точки с запятой и почти любые другие символы. Единственный символ, который вы не можете использовать, – это тот, который разделяет записи, т.е.е., запятая. Например, ваш список категорий может выглядеть так: планы обслуживания, аксессуары, продажи, на месте.

Как можно преобразовать список, разделенный запятыми, в ячейки в столбце для Lt?

Приведенные ниже инструкции можно найти на веб-сайте Microsoft.

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

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

Текст в столбцы

1. Выделите столбец, содержащий ваш список.
2. Перейти к Данные > Текст в столбцы .
3. Выберите С разделителями . Щелкните Далее .
4. Выберите Запятая . Щелкните Далее .
5. Выберите Общий или Текст , в зависимости от того, что вы предпочитаете.
6. Оставьте Назначение как есть или выберите другой столбец. Нажмите Готово .

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

Чтобы получить значения слева от запятой: = 0 + LEFT (K1, FIND (“,”, K1) -1)

Чтобы получить значения справа от запятой: = 0 + RIGHT (K1, LEN (K1) -FIND (“,”, K1))

, где K1 содержит начальную строку, например 401. 50,0.027 ** 0+, перед тем, как формулы преобразуют извлеченные подстроки в числовые данные.

Управленческие курсы в лейтенантах

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

1. Файл должен быть в формате .csv. Щелкните здесь, чтобы получить дополнительную информацию о создании файла CSV.
2. Строка заголовка файла должна содержать три столбца с точными именами: адрес электронной почты, имя, фамилия (при использовании Excel удалите запятые и вставьте по одной фразе в каждую ячейку)
3. Первый столбец файла должен содержать адреса электронной почты пользователей. Все адреса должны быть в формате a @ b.c. Например, [email protected]
4. Второй столбец файла предназначен для заданных имен пользователей. Он должен иметь заголовок «заданное имя», но ввод имен в столбец необязателен.
5. Третий столбец – это фамилии пользователей. Он должен иметь заголовок «имя семейства», но ввод имен в столбцы необязателен.
6. В файле не должно быть дополнительных столбцов или пустых строк.

Создание зависимых раскрывающихся списков с условной проверкой данных

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

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

** ПРИМЕЧАНИЯ:

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

Мы рассмотрим следующие решения:

• Одна таблица – самые сложные формулы; простое постоянное управление данными; нелетучие; удаляет пустые варианты
• Две таблицы – более простые формулы; более сложное постоянное управление данными; энергонезависимая
• INDIRECT – самые простые формулы; летучий; включает пустые варианты

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

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

В КОСВЕННОМ подходе используется непостоянная функция КОСВЕННО. Формулы в этом подходе, безусловно, проще всего реализовать на начальном этапе, однако функция ДВССЫЛ является непостоянной, что означает, что вы можете заметить общее снижение производительности вашей книги в зависимости от ее размера, количества формул и т. Д.Этот вариант будет включать в себя пустые варианты в раскрывающемся списке. Это хороший вариант, если вам нужны простые формулы, а книга относительно небольшая. Обратите внимание, что при таком подходе, если вы измените имя таблицы, вам также потребуется обновить соответствующие формулы проверки данных. Спасибо Дэнни за ваше блестящее предложение!

Мы начнем с моего предпочтительного подхода – подхода «одна таблица». Первоначально его сложнее настроить, но после того, как он будет сделан, со временем легче будет управлять выбором. После того, как мы рассмотрим этот подход, мы обсудим подход двух таблиц.У него более простые формулы, и поэтому его проще настроить на начальном этапе, но с течением времени сложнее управлять выбором. В заключение мы рассмотрим вариант КОСВЕННО, который имеет самые простые формулы.

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

Хорошо, хватит стратегии, перейдем к тактике.

Сохранение вариантов выбора в таблице

Сначала сохраняет раскрывающийся список вариантов в таблице. Функция таблицы была впервые представлена ​​в Excel 2007, поэтому этот подход работает только с версиями Excel 2007 и более поздними версиями.

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

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

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

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

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

Именованные ссылки, извлекающие правильный выбор

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

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

Мы будем использовать диспетчер имен (Формулы> Диспетчер имен), чтобы создать новое имя.

Наше новое имя будет dd_regions и будет ссылаться на строку заголовка таблицы tbl_choices [#Headers] , как показано на скриншоте ниже:

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

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

Давайте посмотрим, для чего нам нужна формула, а затем посмотрим, как эти функции помогают.

Простая версия

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

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

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

 = ИНДЕКС (выбор_таблицы ,, ПОИСКПОЗ (регион, dd_regions, 0)) 

Функция ИНДЕКС возвращает ссылку на диапазон, который начинается с tbl_choices (аргумент №1), включает все строки (аргумент №2) и включает столбец, заголовок которого соответствует выбранной области (аргумент №3, вычисленный функцией ПОИСКПОЗ) .

Снимок экрана показан ниже для справки.

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

Настройка проверки данных

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

Проверка данных для ячейки ввода области настроена так, чтобы разрешить список, равный dd_regions , как показано ниже:

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

Пока все хорошо! Теперь, для важного момента, выпадающий список условных повторений, мы настраиваем проверку данных и разрешаем список, равный dd_reps , как показано ниже:

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

Теперь, когда мы возвращаемся к рабочему листу, мы замечаем, что варианты раскрывающегося списка меняются в зависимости от выбранного региона… вау… это сработало! Это показано на скриншоте ниже:

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

Расширенная версия

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

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

Настроить ссылку на диапазон в стиле A1 легко: достаточно указать верхний левый угол диапазона и нижний правый угол диапазона, например A1: G12 или, возможно, B5: D10.Аналогичным образом можно использовать функцию ИНДЕКС. Мы могли бы использовать две функции ИНДЕКС, чтобы указать диапазон, например: ИНДЕКС (…): ИНДЕКС (…). Первая функция ИНДЕКС возвращает ссылку на ячейку, которая представляет верхнюю левую ячейку в диапазоне, а вторая функция ИНДЕКС возвращает ссылку на ячейку, которая представляет нижнюю правую ячейку в диапазоне.

Чтобы упростить понимание формулы, которую мы будем использовать при проверке данных, мы просто напишем пару вспомогательных формул и сохраним их как имена.

Во-первых, нам понадобится ярлык для ссылки на номер столбца выбранного региона. Итак, мы создали новое имя dd_col_num , которое ссылается на следующую формулу:

 = ПОИСКПОЗ (регион, dd_regions, 0) 

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

Далее нам нужен ярлык для ссылки на столбец выбранного региона. Не номер столбца, а весь столбец таблицы.Итак, мы создали новое имя dd_col , которое ссылается на следующую формулу:

 = ИНДЕКС (выбор_таблицы ,, dd_col_num) 

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

Наконец, нам нужно новое имя для использования в раскрывающемся списке rep, поэтому мы установили dd_reps2 для ссылки на следующую формулу:

 = ИНДЕКС (выбор_таблицы, 1, dd_col_num): ИНДЕКС (выбор_таблицы, COUNTA (dd_col), dd_col_num) 

Как видите, мы используем идею INDEX (…): INDEX (…) для возврата диапазона. Первая функция ИНДЕКС возвращает верхнюю левую ячейку, а вторая функция ИНДЕКС возвращает нижнюю правую ячейку. Первая функция ИНДЕКС просматривает tbl_choices (аргумент № 1) и возвращает ссылку на ячейку на пересечении первой строки данных таблицы (аргумент № 2) и столбца выбранной области (аргумент № 3, как вычислено с помощью dd_col_num). названная формула).

Вторая функция ИНДЕКС возвращает ячейку в пределах tbl_choices (аргумент № 1) на пересечении последней ячейки с данными (аргумент № 2, вычисленный функцией COUNTA) и столбцом выбранной области (аргумент № 3).

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

Это показано на скриншоте ниже:

В качестве альтернативы использованию функции COUNTA для подсчета количества ячеек со значениями мы могли бы использовать функцию MATCH, чтобы найти последнюю ячейку с данными. Чтобы продемонстрировать этот метод, который будет включать любые пустые ячейки между повторениями, мы установили новое имя dd_reps3 следующим образом:

 = ИНДЕКС (dd_col, 1): ИНДЕКС (dd_col, ПОИСКПОЗ ("*", dd_col, -1)) 

Как видите, вместо того, чтобы пытаться получить последнее повторение с помощью COUNTA, этот подход вместо этого использует MATCH, сопоставляя подстановочный знак (*) в столбце rep и -1 для большего, чем.Дополнительные сведения см. В справке Excel по функции ПОИСКПОЗ.

Все три из этих параметров: dd_reps , dd_reps2 и dd_reps3 включены в файл загрузки ниже для справки.

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

Краткое описание шагов:

• Настройте таблицу для хранения основных раскрывающихся вариантов с именем tbl_primary
• Установите именованный диапазон dd_primary, который ссылается на tbl_primary
• Настройте основную ячейку раскрывающегося ввода с проверкой данных и разрешите список, равный dd_primary
• Настройте таблицу для хранения дополнительных раскрывающихся вариантов с именем tbl_secondary
• Установите именованный диапазон dd_secondary, который извлекает связанные варианты
• Настройте вторичную ячейку раскрывающегося ввода с проверкой данных и разрешите список, равный dd_secondary

Вот подробные инструкции.

Во-первых, нам нужно сохранить основные раскрывающиеся варианты в таблице с именем tbl_primary:

Затем мы устанавливаем новое пользовательское имя dd_primary, которое относится к таблице:

Затем мы настраиваем проверку данных в основной входной ячейке, чтобы разрешить список, равный dd_primary:

Результирующая первичная ячейка ввода показана ниже:

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

Мы устанавливаем список вариантов в таблице с именем tbl_secondary, который должен быть отсортирован в порядке возрастания по основному столбцу:

Далее идет именованный диапазон dd_secondary. Это единственная сложная часть. Мы будем использовать функции ИНДЕКС / ПОИСКПОЗ, чтобы определить количество повторений для выбранного региона. Поскольку наша основная входная ячейка хранится на листе с именем Две таблицы в ячейке C6, мы зададим имя dd_secondary для ссылки на:

 = ИНДЕКС (tbl_secondary [Rep], MATCH ('Две таблицы'! $ C $ 6, tbl_secondary [Region], 0), 1):
ИНДЕКС (tbl_secondary [Rep], MATCH ('Две таблицы'! $ C $ 6, tbl_secondary [Region], 1), 1) 

Как видите, в этой формуле для задания диапазона используется оператор диапазона (:). Первая ячейка в диапазоне определяется первой функцией ИНДЕКС. Последняя ячейка в диапазоне определяется последней функцией ИНДЕКС.

Затем мы настраиваем проверку данных на вторичной входной ячейке, чтобы разрешить список, равный dd_secondary:

Полученный рабочий лист теперь содержит условные раскрывающиеся ячейки ввода:

Несколько строк ввода

Как видите, на приведенном выше снимке экрана предполагается только одна запись, но что, если вам нужно настроить это так, чтобы пользователи могли вводить несколько строк? Нет проблем, секрет кроется в имени dd_secondary.

Хотя именованные диапазоны часто устанавливаются с помощью абсолютных ссылок, например, как имя dd_secondary использует абсолютную ссылку «Две таблицы»! $ C $ 6, они также могут быть настроены с помощью относительных ссылок. Когда вы задаете имя с относительной ссылкой, имеет значение активная ячейка. То есть активная ячейка во время установки имени будет указывать базовое местоположение для относительной ссылки. Эту идею легко представить, подумав о обычных формулах. Когда вы пишете формулу для обычной ячейки, в которой используется относительная ссылка, ссылка указывается относительно ячейки, содержащей формулу.Та же идея применяется при настройке именованной ссылки. Активная ячейка, выбранная в тот момент, когда вы открываете диалоговое окно нового имени, будет использоваться для определения того, как оценивается относительная ссылка на ячейку.

Мы можем использовать эту идею для создания нескольких входных строк. Мы создаем имя dd_secondary, используя относительную ссылку на первичную входную ячейку, в то время как активная ячейка является первой вторичной входной ячейкой:

Поскольку наша книга уже имеет имя dd_secondary, мы будем использовать вместо этого dd_secondary2 и установить имя, равное:

 = ИНДЕКС (tbl_secondary [Rep], MATCH (Multiple! B7, tbl_secondary [Region], 0), 1):
ИНДЕКС (tbl_secondary [Rep], MATCH (Multiple! B7, tbl_secondary [Region], 1), 1) 

В этой версии имени вы заметите, что функция ПОИСКПОЗ находит значение на листе Multiple в ячейке B7, которое является относительной ссылкой. Поскольку активной ячейкой, когда мы настраивали имя, было C7, имя будет использовать значение слева.

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

Это продемонстрировано в образце файла на листе “Несколько”.

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

В поле источника списка проверки данных для основного раскрывающегося списка вы должны использовать это:

 = КОСВЕННО ("tbl_reps [#Headers") 

Это указывает проверке данных на преобразование текстовой строки «tbl_reps [#Headers]» в соответствующую ссылку на таблицу для строки заголовка. Обратите внимание, что, поскольку имя таблицы встроено в формулу, если вы измените имя таблицы, вам также потребуется обновить формулу проверки данных.

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

 = КОСВЕННО ("tbl_reps [" & $ A $ 1 & "]") 

Функция ДВССЫЛ указывает Excel преобразовать текстовую строку в ссылку на структурированную таблицу для имени столбца, указанного в A1.

Функция ДВССЫЛ является непостоянной, что означает, что она обновляется каждый раз при пересчете книги. Это может снизить производительность в больших книгах. Но в небольших книгах вы не должны замечать снижения производительности и должны работать нормально.

Спасибо Danny за это чистое альтернативное решение!

Этот подход продемонстрирован в книге ConditionalDropDown3b ниже.

Заключение

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

Дополнительные ресурсы

• Чтобы загрузить файл Excel, используемый для снимков экрана выше, который включает таблицу, именованные формулы и ячейки проверки данных: ConditionalDropdown
• Пример нескольких строк ввода: ConditionalDropdown_2
  
• Использование КОСВЕННОГО подхода: ConditionalDropdown3b
• Получить дополнительный раскрывающийся список из отфильтрованной сводной таблицы: ConditionalDropdownPT
• Если вам нужно пройти более двух уровней, вы можете просто продолжить эту стратегию и создать раскрывающиеся таблицы выбора для каждого дополнительного зависимого раскрывающегося списка.
• Если вы не изучали именованные ссылки, таблицы или проверку данных, все эти темы рассматриваются в книге Excel University Volume 1 и онлайн-курсе Excel.
• КОСВЕННАЯ функция для извлечения значений из связанных таблиц

Выпадающий список

Excel – как создавать, редактировать и удалять списки проверки данных

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

Выпадающий список

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

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

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

Создание раскрывающихся списков со значениями, разделенными запятыми

Это самый быстрый трехэтапный способ создания раскрывающегося списка во всех версиях Excel 2016, 2013, 2010, 2007 и 2003.

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

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

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

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

На ленте Excel перейдите на вкладку «Данные»> группа «Инструменты для работы с данными» и щелкните «Проверка данных» .

org/ListItem”> 3. Введите элементы списка и выберите параметры.

В окне Data Validation на вкладке Settings выполните следующие действия:

• В поле Разрешить выберите Список .
• В поле Источник введите элементы, которые должны отображаться в раскрывающемся меню, через запятую (с пробелами или без них).
• Убедитесь, что в раскрывающемся списке в ячейке установлен флажок ; в противном случае стрелка раскрывающегося списка не появится рядом с ячейкой.
• Установите или снимите флажок Игнорировать пустой в зависимости от того, как вы хотите обрабатывать пустые ячейки.
• Нажмите ОК, и все готово!

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

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

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

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

1. Введите записи для раскрывающегося списка.

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

Например, давайте создадим раскрывающийся список ингредиентов для ваших любимых рецептов:

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

2. Создайте именованный диапазон.

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

• Выберите все записи, которые вы хотите включить в раскрывающийся список, щелкните их правой кнопкой мыши и выберите Определить имя из контекстного меню.Кроме того, вы можете щелкнуть Диспетчер имен на вкладке Формулы или нажать Ctrl + F3.
• В диалоговом окне Диспетчер имен щелкните Новый .
• В поле Имя введите имя для записей, убедитесь, что в поле Относится к отображается правильный диапазон, а затем нажмите OK. Убедитесь, что в названии диапазона нет пробелов и дефисов, используйте вместо них символы подчеркивания (_).

Наконечник. Более быстрый способ создать именованный диапазон в Excel – выбрать ячейки и ввести имя диапазона непосредственно в поле имени .По завершении нажмите Enter, чтобы сохранить вновь созданный именованный диапазон. Для получения дополнительной информации, пожалуйста, посмотрите, как определить имя в Excel.

3. Примените проверку данных.

Щелкните ячейку, в которой должен отображаться раскрывающийся список – это может быть диапазон ячеек или весь столбец на том же листе, где находится ваш список записей, или на другом листе. Затем перейдите на вкладку Data , щелкните Data Validation и настройте правило:

• В поле Разрешить выберите Список .
• В поле Source введите имя, которое вы дали своему диапазону, со знаком равенства, например = Ingredients .
• Убедитесь, что в раскрывающемся списке в ячейке установлен флажок .
• Нажмите ОК.

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

Если исходный список содержит более 8 элементов, в раскрывающемся списке будет полоса прокрутки, подобная этой:

Список проверки данных Excel на основе таблицы

Вместо использования обычного именованного диапазона вы можете преобразовать данные в полнофункциональную таблицу Excel ( Insert> Table или Ctrl + T), а затем создать список проверки данных из этой таблицы. Вот как:

1. Создайте именованный диапазон для столбца данных, не включая ячейку заголовка в таблице.Чтобы обратиться к ячейкам данных, вы можете использовать один из следующих методов:
   • Наведите указатель мыши на верхнюю часть ячейки заголовка, и когда появится черная стрелка вниз, щелкните, чтобы выбрать все ячейки в столбце таблицы без заголовка. Затем введите имя выбранного диапазона в поле Имя .
     
   • Откройте диспетчер имен и введите структурированную ссылку, например, = Таблица [столбец] в поле Относится к :
     
2. Создайте список проверки данных на основе именованного диапазона, как описано в предыдущем примере.

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

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

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

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

Создать динамическое (автоматически обновляемое) раскрывающееся меню Excel

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

Самый простой способ создать такой динамически обновляемый раскрывающийся список в Excel – это создать именованный список на основе таблицы. Если по какой-то причине вы предпочитаете обычный именованный диапазон, укажите на него формулу СМЕЩЕНИЕ, как описано ниже.

1. Вы начинаете с создания обычного раскрывающегося списка на основе именованного диапазона, как описано выше.
2. На шаге 2 при создании имени вы помещаете следующую формулу в поле Относится к .

   = СМЕЩЕНИЕ (Sheet1! $ A $ 1,0,0, COUNTA (Sheet1! $ A: $ A), 1)

   Где:

   • Sheet1 – имя листа
   • A – столбец, в котором расположены элементы вашего выпадающего списка
   • $ A $ 1 – ячейка, содержащая первый элемент списка

Как видите, формула состоит из 2 функций Excel – СМЕЩЕНИЕ и СЧЁТ. Функция COUNTA считает все непустые пробелы в указанном столбце. СМЕЩЕНИЕ принимает это число и возвращает ссылку на диапазон, который включает только непустые ячейки, начиная с первой ячейки, указанной в формуле.

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

Как работает эта формула

В Microsoft Excel функция СМЕЩЕНИЕ (ссылка, строки, столбцы, [высота], [ширина]) используется для возврата ссылки на диапазон, состоящий из указанного количества строк и столбцов. Чтобы заставить его возвращать динамический, т.е. непрерывно изменяющийся диапазон, мы указываем следующие аргументы:

• ссылка – ячейка $ A $ 1 в Sheet1, которая является первым элементом вашего раскрывающегося списка;
• строк и столбцы равны 0, потому что вы не хотите сдвигать возвращаемый диапазон ни по вертикали, ни по горизонтали;
• высота – количество непустых ячеек в столбце A, возвращаемое функцией СЧЁТ;
• ширина -1, т.е.е. один столбец.

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

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

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

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

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

1. Создайте именованный диапазон для исходного списка.

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

2. Создайте именованную ссылку в основной книге.

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

Примечание. Вы должны заключить имя книги в апострофы (‘), если оно содержит пробелы. Например: = 'Source Book.xlsx'! Source_list

3. Применить проверку данных

В основной книге выберите ячейки для раскрывающегося списка, щелкните Данные> Проверка данных и введите имя, созданное на шаге 2, в поле Источник .

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

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

1. Создайте имя диапазона в исходной книге с помощью формулы СМЕЩЕНИЕ, как описано в разделе «Создание динамического раскрывающегося списка».
2. В основной книге примените проверку данных обычным способом.

Проверка данных Excel не работает

Параметр проверки данных неактивен или отключен? Это может произойти по нескольким причинам:

• Раскрывающиеся списки нельзя добавлять на защищенные или общие рабочие листы. Снимите защиту или прекратите совместное использование листа, а затем попробуйте снова щелкнуть Data Validation .
• Вы создаете раскрывающийся список из таблицы Excel, связанной с сайтом SharePoint. Отключите таблицу или удалите форматирование таблицы и повторите попытку.

Дополнительные параметры для раскрывающегося списка Excel

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

Отображать сообщение при щелчке по ячейке с раскрывающимся списком

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

• В диалоговом окне Проверка данных (вкладка Данные> Проверка данных ) перейдите на вкладку Входное сообщение .
• Убедитесь, что опция Показывать входное сообщение при выборе ячейки отмечена.
• Введите заголовок и сообщение в соответствующие поля (до 225 символов).
• Нажмите кнопку OK , чтобы сохранить сообщение и закрыть диалоговое окно.

Результат в Excel будет выглядеть примерно так:

Разрешить пользователям вводить свои данные в поле со списком

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

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

1. В диалоговом окне «Проверка данных » (вкладка «Данные »> «Проверка данных ») перейдите на вкладку «Предупреждение об ошибке » .
2. Установите флажок «Показать предупреждение об ошибке после ввода недопустимых данных », если вы хотите отображать предупреждение, когда пользователь пытается ввести некоторые данные, которых нет в раскрывающемся меню.Если вы не хотите показывать какие-либо сообщения, снимите этот флажок.
3. Чтобы отобразить предупреждающее сообщение, выберите один из вариантов в поле Style и введите заголовок и сообщение. Либо Информация , либо Предупреждение позволит пользователям вводить свой собственный текст в поле со списком.
   • Сообщение Информация рекомендуется, если ваши пользователи часто вводят свои собственные варианты выбора.
   • Предупреждение Сообщение побудит пользователей выбрать элемент из раскрывающегося списка, а не вводить свои собственные данные, хотя это не запрещает пользовательский ввод.
   • Остановить (по умолчанию) запретит людям вводить какие-либо данные, которых нет в раскрывающемся списке Excel.

   И вот как ваше настраиваемое предупреждающее сообщение может выглядеть в Excel:
   

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

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

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

Редактирование раскрывающегося списка, разделенного запятыми

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

1. Выберите ячейку или ячейки, которые ссылаются на ваш список проверки данных Excel, i. е. ячейки, содержащие раскрывающийся список, который вы хотите отредактировать.
2. Щелкните Проверка данных (лента Excel> вкладка Данные).
3. Удалите или введите новые элементы в поле Источник .
4. Нажмите OK, чтобы сохранить изменения и закрыть окно Excel Data Validation .
   

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

Редактирование раскрывающегося меню на основе диапазона ячеек

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

1. Перейдите к электронной таблице, содержащей элементы, отображаемые в раскрывающемся списке, и отредактируйте список по своему усмотрению.
2. Выберите ячейку или ячейки, содержащие раскрывающийся список.
3. Щелкните Data Validation на вкладке Data .
4. В окне Excel Data Validation на вкладке «Параметры» измените ссылки на ячейки в поле «Источник». Вы можете отредактировать их вручную или щелкнуть значок Свернуть диалоговое окно .
5. Нажмите кнопку OK , чтобы сохранить изменения и закрыть окно.
   

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

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

1. Добавить или удалить элементы в названном диапазоне.
   Откройте рабочий лист, содержащий ваш именованный диапазон, удалите или введите новые записи. Не забудьте расположить элементы в том порядке, в котором они должны отображаться в раскрывающемся списке Excel.
2. Измените ссылку на именованный диапазон.
   • На ленте Excel перейдите на вкладку «Формулы »> «Диспетчер имен ». Либо нажмите Ctrl + F3, чтобы открыть окно Name Manager .
   • В окне Name Manager выберите именованный диапазон, который вы хотите обновить.
   • Измените ссылку в поле « Относится к », щелкнув значок Свернуть диалоговое окно и выбрав все записи в раскрывающемся списке.
   • Нажмите кнопку Закрыть , а затем в появившемся подтверждающем сообщении нажмите Да , чтобы сохранить изменения.
   Наконечник. Чтобы избежать необходимости обновлять ссылки именованного диапазона после каждого изменения исходного списка, вы можете создать динамическое раскрывающееся меню Excel.В этом случае раскрывающийся список будет автоматически обновляться во всех связанных ячейках, как только вы удалите или добавите новые записи в список.

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

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

Удаление раскрывающегося меню из выбранных ячеек

1. Выберите ячейку или несколько ячеек, из которых вы хотите удалить раскрывающиеся списки.
2. Перейдите на вкладку Data и щелкните Data Validation .
3. На вкладке «Настройки» нажмите кнопку Очистить все .
   

   Этот метод удаляет раскрывающиеся меню из выбранных ячеек, но сохраняет текущие выбранные значения.

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

Удаление раскрывающегося списка Excel из всех ячеек текущего листа

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

1. Выберите любую ячейку, содержащую раскрывающийся список.
2. Щелкните Data Validation на вкладке Data .
3. В окне «Проверка данных» на вкладке «Настройки» установите флажок « Применить эти изменения ко всем другим ячейкам с такими же настройками ».

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

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

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