Excel: как создавать простые и зависимые раскрывающиеся списки

Выпадающие списки в Microsoft Excel (а также Word и Access) позволяют создать список допустимых вариантов, которые вы или другие пользователи можете выбрать для данного поля. Это особенно полезно для полей, требующих конкретной информации; поля с длинными или сложными данными, которые трудно подобрать; или поля, ответы на которые вы хотите контролировать.

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

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

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

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

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

1. Откройте новую книгу и добавьте вторую вкладку электронной таблицы (щелкните значок «+» в нижней части экрана на панели вкладок).

2. Переименуйте таблицу 1 как « нед » для рабочего листа, а электронную таблицу 2 как « списки

3. Введите имена 10 врачей (или другие соответствующие элементы) в столбец A от A1 до A10.

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

5. Выделите диапазон (A1: A10) или просто поместите курсор в любую ячейку в списке и нажмите Ctrl + T , чтобы преобразовать эту группу элементов в таблицу. Excel называет это таблицей 1, 2, 3 и т. Д., Что не является проблемой, если есть только одна таблица. Обязательно установите флажок «Моя таблица имеет заголовки».

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

JD Sartain/IDG Worldwide

6. Переместить в таблицу 1 (переименована в нед ). Введите данные, аналогичные показанным на следующем рисунке, например: Тип операции, Дата, Время и Хирург, или создайте свои собственные данные.

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

8. На вкладке «Данные» выберите Проверка данных > Проверка данных .

9. В диалоговом окне Проверка данных выберите вкладку Настройки . На панели Критерии проверки в поле Разрешить выберите параметр Список в раскрывающемся списке.

JD Sartain/IDG Worldwide

10. Перейдите к полю «Источник» и щелкните внутри этого поля.

11. Переместите курсор за пределы этого диалогового окна и выберите электронную таблицу lists на вкладках книги в нижней части экрана.

12. Выделите диапазон врачей, то есть от A2 до A11. Обратите внимание, что Excel добавляет этот диапазон в поле «Источник» (= списки! $ A $ 2: $ A $ 11) за вас.

13. Затем щелкните вкладку Сообщение ввода и введите заголовок и сообщение ввода для раскрывающегося списка.

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

15. Нажмите OK , и раскрывающийся список будет готов.

JD Sartain/IDG Worldwide

16. Вернитесь к электронной таблице wks и поместите курсор в ячейку D2. Обратите внимание на стрелку раскрывающегося списка, и ваше настраиваемое сообщение ввода отображается справа от каждой ячейки в этом столбце, который вы выбираете. Щелкните стрелку вниз и выберите врача из списка, который специализируется на данном типе операции в соответствующей строке столбца A. Например, поле доктора Саймона Уолтерса — это операция на бедре.

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

JD Sartain/IDG Весь мир

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

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

Для этого следующего упражнения представьте, что вы управляете небольшой сельской больницей, расположенной примерно в 50 милях от большого города, в котором есть три большие больницы с полным персоналом.. Ваша работа — назначить хирургов одного из этих трех крупных медицинских учреждений для приема пациентов в вашей больнице. «Основной» раскрывающийся список содержит выбор больниц (по местоположению), в которых работает каждый хирург. В раскрывающихся списках подменю указаны имена каждого хирурга, работающего в каждом из этих учреждений: Ист-Сайд, Вест-Сайд или Мидтаун.

A. Создайте списки

1. Сначала добавьте еще одну электронную таблицу и назовите ее lists2 .

2. В электронной таблице lists2 введите следующий заголовок для столбца A: Расположение больниц. В разделе «Расположение больниц» введите названия EastSide , WestSide и Midtown в ячейках A2, A3 и A4 соответственно (без пробелов и без использования одного слова).

3. Переместите курсор в первую ячейку под заголовком «Расположение больниц» (A2). Щелкните Главная > Форматировать как таблицу и выберите стиль таблицы в подменю, затем нажмите ОК .

4. Выберите расположение больниц в этом списке (A2: A4). Введите имя таблицы ( Расположение ) в поле «Имя» (над столбцом A) или нажмите Ctrl + T, чтобы преобразовать эти элементы в таблицу, в которой Excel называет таблицы 1, 2, 3 и т. Д. Наконец установите флажок Моя таблица имеет заголовки .

5. Чтобы переименовать таблицы, выберите Формулы > Диспетчер имен . Переместите курсор вниз к таблице 1 (2, 3, 4 и т. Д.), Затем нажмите кнопку Изменить .

6. В диалоговом окне Изменить имя введите новое имя ( Locations ).

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

JD Sartain/IDG Worldwide

7. Затем вы должны создать отдельную таблицу для каждого местоположения больницы. В таблице lists2 введите следующие заголовки для столбца B: East Side , C: West Side и D: Midtown (эти метки столбцов также будут вашими именами диапазонов без пробелов).

8. Введите имена врачей под каждым из этих трех столбцов (B, C, D).

9. Отформатируйте каждый список как именованную таблицу (повторите шаг 3 выше).

10. Выделите диапазон каждого столбца отдельно (B1: B8; C1: C7; D1: D9). Нажмите Ctrl + T , чтобы преобразовать эти группы элементов в таблицы, которым Excel называет таблицы 2, 3, 4 и т. Д., Затем установите флажок Моя таблица имеет заголовки . Повторите шаги 5 и 6 выше, чтобы переименовать таблицы. Помните, что в именах диапазонов нет пробелов.

JD Sartain/IDG Worldwide

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

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

1. Сначала вернитесь в электронную таблицу wks и удалите предыдущее раскрывающееся меню. в нижнем списке в столбце D под названием Хирурги. Создайте новый заголовок в столбце D1 под названием Местоположение и назовите столбец E1 Хирурги.

2. Выберите ячейки D1: E11, затем выберите Дом > Форматировать как таблицу , выберите стиль, установите флажок заголовков и нажмите ОК .

3. Затем выберите ячейки (D2: D11) для раскрывающегося списка главного меню.

4. На вкладке «Данные» выберите Проверка данных > Проверка данных .

5. В диалоговом окне Проверка данных выберите вкладку Настройки . На панели Критерии проверки в поле Разрешить выберите параметр Список в раскрывающемся списке.

6. В Sourc e , щелкните электронную таблицу list2 , выделите список «Местоположение больницы» без заголовка (A2: A4) и нажмите ОК .

JD Sartain/IDG Worldwide

7. Переместите курсор в ячейку E2.

8. Повторите шаги 8 и 9 выше.

9. На этот раз в поле Источник введите следующую формулу: = INDIRECT ($ D $ 2) — но это только для текущей ячейки — затем нажмите ОК .

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

10. Чтобы заполнить столбец (что очевидно), введите следующую формулу: = КОСВЕННО ($ D2) — да, без знака «$» на номере строки — затем скопируйте ячейка D2 вниз от D3 до D11. Это активирует весь диапазон.

JD Sartain/IDG Worldwide

11. Если вы хотите добавить сообщение ввода или предупреждение об ошибке, повторите шаги с 13 по 14 выше в разделе «Как создать простой раскрывающийся список».

C. Проверьте свою работу

А теперь пора протестировать свою работу. Щелкайте стрелки раскрывающегося списка (по одной) в столбце D (Местоположение).

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

2. Переместите курсор в столбец E (Хирург) и выберите врача из списка врачей в месте, указанном в столбце D.

JD Sartain/IDG Worldwide

D. Обход элементов из двух слов

Если вы хотите использовать два или более слов в раскрывающемся списке главного меню (например, Местоположение) и не хотите объединять слова вместе без пробела (например. , East Side вместо EastSide), введите эту формулу в зависимом раскрывающемся списке (Surgeon) Source в диалоговом окне Data Validation: = INDIRECT (SUBSTITUTE (D2, «», «»)) где D2 — адрес ячейки, «» означает кавычку-пробел-цитату, а «» означает кавычку-цитату без пробела. Перевод: замените ячейку D2, в которой есть пробел, на D2 без пробела.

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

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