Як створити розкривний список у Excel (єдине, що вам потрібно)

Випадаючий список-це чудовий спосіб дати користувачеві можливість вибрати із заздалегідь визначеного списку.

Його можна використовувати, коли користувач заповнює форму або створює інтерактивні інформаційні панелі Excel.

Випадаючі списки досить поширені на веб-сайтах/у додатках і дуже інтуїтивно зрозумілі для користувача.

Перегляд відео - Створення розкривного списку в Excel

У цьому посібнику ви дізнаєтесь, як створити розкривний список у Excel (для цього потрібно всього кілька секунд) разом із усіма чудовими речами, які ви можете з ним зробити.

Як створити розкривний список в Excel

У цьому розділі ви дізнаєтесь, які кроки необхідно створити для створення розкривного списку Excel:

1. Використання даних із клітин.
2. Введення даних вручну.
3. Використовуючи формулу OFFSET.

#1 Використання даних із клітин

Скажімо, у вас є список елементів, як показано нижче:

Нижче наведено кроки для створення розкривного списку Excel:

1. Виберіть клітинку, де потрібно створити розкривний список.
2. Перейдіть до Дані -> Інструменти даних -> Перевірка даних.
3. У діалоговому вікні Перевірка даних на вкладці Налаштування виберіть Список як критерій перевірки.
   • Щойно ви виберете Список, з’явиться поле джерела.
4. У полі джерела введіть = $ A $ 2: $ A $ 6, або просто клацніть у полі Джерело та виділіть клітинки за допомогою миші та натисніть OK. Це додасть розкривний список у клітинку С2.
   • Переконайтеся, що прапорець випадаючого параметра In-cell (що перевіряється за замовчуванням). Якщо цей параметр не позначено, клітинка не відображає спадне меню, однак ви можете вручну ввести значення у списку.

Примітка: Якщо ви хочете створити розкривні списки у кількох клітинках за один раз, виберіть усі клітинки, де ви хочете його створити, а потім виконайте наведені вище кроки. Переконайтеся, що посилання на клітинки є абсолютними (наприклад, $ A $ 2), а не відносними (наприклад, A2, або A $ 2, або $ A2).

#2 Введенням даних вручну

У наведеному вище прикладі посилання на клітинки використовуються у полі Джерело. Ви також можете додавати елементи безпосередньо, ввівши їх вручну у поле джерела.

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

• Виберіть клітинку, де потрібно створити розкривний список (у цьому прикладі клітинку С2).
• Перейдіть до Дані -> Інструменти даних -> Перевірка даних.
• У діалоговому вікні Перевірка даних на вкладці Налаштування виберіть Список як критерій перевірки.
  • Щойно ви виберете Список, з’явиться поле джерела.
• У полі джерела введіть Так, Ні
  • Переконайтеся, що прапорець випадаючого параметра Внутрішня комірка.
• Натисніть OK.

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

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

Примітка: Якщо ви хочете створити розкривні списки у кількох клітинках за один раз, виберіть усі клітинки, де ви хочете його створити, а потім виконайте наведені вище кроки.

#3 Використання формул Excel

Окрім вибору з комірок та введення даних вручну, ви також можете використовувати формулу у полі джерела для створення розкривного списку Excel.

Будь-яку формулу, яка повертає список значень, можна використовувати для створення розкривного списку в Excel.

Наприклад, припустимо, що у вас є набір даних, як показано нижче:

Нижче наведено кроки для створення розкривного списку Excel за допомогою функції OFFSET:

• Виберіть клітинку, де потрібно створити розкривний список (у цьому прикладі клітинку С2).
• Перейдіть до Дані -> Інструменти даних -> Перевірка даних.
• У діалоговому вікні Перевірка даних на вкладці Налаштування виберіть Список як критерій перевірки.
  • Щойно ви виберете Список, з’явиться поле джерела.
• У полі Джерело введіть таку формулу: = ЗМІЩЕННЯ ($ A $ 2,0,0,5)
  • Переконайтеся, що прапорець випадаючого параметра Внутрішня комірка.
• Натисніть OK.

Буде створено випадаючий список із переліком усіх назв фруктів (як показано нижче).

Примітка: Якщо ви хочете створити розкривний список у кількох клітинках за один раз, виберіть усі клітинки, де ви хочете його створити, а потім виконайте наведені вище кроки. Переконайтеся, що посилання на клітинки є абсолютними (наприклад, $ A $ 2), а не відносними (наприклад, A2, або A $ 2, або $ A2).

Як працює ця формула ??

У наведеному вище випадку ми використовували функцію OFFSET для створення випадаючого списку. Він повертає список елементів з ra

Він повертає список елементів із діапазону A2: A6.

Ось синтаксис функції OFFSET: = OFFSET (посилання, рядки, cols, [висота], [ширина])

Потрібно п'ять аргументів, де ми вказали посилання як A2 (початкова точка списку). Рядки/коди вказано як 0, оскільки ми не хочемо зміщувати осередку відліку. Висота вказана як 5, оскільки у списку є п’ять елементів.

Тепер, коли ви використовуєте цю формулу, вона повертає масив зі списком п’яти фруктів у A2: A6. Зверніть увагу: якщо ви введете формулу в клітинку, виділіть її та натисніть F9, ви побачите, що вона повертає масив назв фруктів.

Створення динамічного випадаючого списку в Excel (використання OFFSET)

Наведена вище методика використання формули для створення розкривного списку може бути розширена для створення динамічного випадаючого списку. Якщо ви використовуєте функцію OFFSET, як показано вище, навіть якщо ви додасте до списку більше елементів, спадне меню не оновлюватиметься автоматично. Щоразу, коли ви змінюєте список, вам доведеться оновлювати його вручну.

Ось спосіб зробити його динамічним (і це не що інше, як незначна зміна у формулі):

• Виберіть клітинку, де потрібно створити розкривний список (у цьому прикладі клітинку С2).
• Перейдіть до Дані -> Інструменти даних -> Перевірка даних.
• У діалоговому вікні Перевірка даних на вкладці Налаштування виберіть Список як критерій перевірки. Щойно ви виберете Список, з’явиться поле джерела.
• У полі джерела введіть таку формулу: = OFFSET ($ 2,0,0 $, COUNTIF ($ A $ 2: $ A $ 100, ""))
• Переконайтеся, що прапорець випадаючого параметра In-cell.
• Натисніть OK.

У цій формулі я замінив аргумент 5 на COUNTIF ($ A $ 2: $ A $ 100 »).

Функція COUNTIF підраховує непусті клітинки в діапазоні A2: A100. Отже, функція OFFSET налаштовує себе так, щоб включати всі не порожні клітинки.

Примітка:

• Щоб це працювало, між заповненими клітинками НЕ повинно бути порожніх клітинок.
• Якщо ви хочете створити розкривний список у кількох клітинках за один раз, виберіть усі клітинки, де ви хочете його створити, а потім виконайте наведені вище кроки. Переконайтеся, що посилання на клітинки є абсолютними (наприклад, $ A $ 2), а не відносними (наприклад, A2, або A $ 2, або $ A2).

Копіювати вставні випадаючі списки в Excel

Ви можете скопіювати вставити клітинки з валідацією даних в інші клітинки, і вона також скопіює перевірку даних.

Наприклад, якщо у вас є розкривний список у комірці С2, і ви хочете застосувати його до С3: С6, просто скопіюйте комірку С2 і вставте її у С3: С6. Це призведе до копіювання випадаючого списку та зробить його доступним у C3: C6 (разом із випадаючим списком він також скопіює форматування).

Якщо ви хочете скопіювати лише спадне меню, а не форматування, виконайте наведені нижче дії.

• Скопіюйте випадаючу клітинку.
• У спадному меню виберіть клітинки, куди потрібно скопіювати.
• Перейдіть додому -> Вставити -> Спеціальна вставка.
• У діалоговому вікні Спеціальна вставка виберіть Перевірка у параметрах Вставити.
• Натисніть OK.

Це призведе лише до копіювання випадаючого меню, а не до форматування скопійованої комірки.

Обережно під час роботи з випадаючим списком Excel

Під час роботи зі спадними списками в Excel потрібно бути обережним.

Під час копіювання комірки (яка не містить випадаючого списку) над коміркою, що містить випадаючий список, випадаючий список втрачається.

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

Як вибрати всі клітинки, у яких є випадаючий список

Іноді важко дізнатися, які клітинки містять випадаючий список.

Отже, є сенс позначити ці клітинки, надавши їм чітку рамку або колір фону.

Замість того, щоб вручну перевіряти всі комірки, існує швидкий спосіб вибрати всі клітинки, у яких є випадаючі списки (або будь-яке правило перевірки даних).

• Перейдіть додому -> Знайти та вибрати -> Перейти до спеціального.
• У діалоговому вікні Перейти до спеціального виберіть Перевірка даних
  • Перевірка даних має два варіанти: усі та однакові. Усі вибрали б усі клітинки, до яких застосовано правило перевірки даних. Те ж саме буде вибрано лише ті клітинки, які мають те саме правило перевірки даних, що і правило активної комірки.
• Натисніть OK.

Це миттєво виділить усі клітинки, до яких застосовано правило перевірки даних (це також включає випадаючі списки).

Тепер ви можете просто відформатувати клітинки (задати рамку або колір фону), щоб вони були візуально видимими, і ви випадково не скопіювали на них іншу клітинку.

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

Створення випадаючого списку залежного / умовного Excel

Ось відео про те, як створити залежний випадаючий список у Excel.

Якщо ви віддаєте перевагу читанню, а не перегляду відео, продовжуйте читати.

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

Вони називаються залежними або умовними випадаючими списками.

Нижче наведено приклад умовного/залежного випадаючого списку:

У наведеному вище прикладі, коли пункти, перелічені у "Випадаючому меню 2", залежать від вибору, зробленого у "Випадаючому меню 1".

Тепер подивимося, як це створити.

Ось кроки для створення залежного / умовного випадаючого списку в Excel:

• Виберіть клітинку, у якій потрібно розташувати перший (основний) випадаючий список.
• Перейдіть до Дані -> Перевірка даних. Відкриється діалогове вікно перевірки даних.
• У діалоговому вікні перевірки даних на вкладці налаштувань виберіть Список.
• У полі Джерело вкажіть діапазон, який містить елементи, які мають відображатися у першому розкривному списку.
• Натисніть OK. Це створить спадне меню 1.
• Виберіть весь набір даних (A1: B6 у цьому прикладі).
• Перейдіть до Формули -> Визначені імена -> Створити з виділення (або ви можете скористатися комбінацією клавіш Control + Shift + F3).
• У діалоговому вікні "Створити ім'я з виділення" встановіть прапорець "Верхній рядок" і зніміть прапорці з усіх інших. Це створює 2 діапазони назв ("Фрукти" та "Овочі"). Фрукти з назвою асортимент стосуються всіх фруктів у списку, а назва овочів - усіх овочів зі списку.
• Натисніть OK.
• Виберіть клітинку, у якій потрібно розташувати залежний/умовний випадаючий список (E3 у цьому прикладі).
• Перейдіть до Дані -> Перевірка даних.
• У діалоговому вікні Перевірка даних на вкладці налаштувань переконайтеся, що вибрано Список у.
• У полі Джерело введіть формулу = НЕПРЯМА (D3). Тут D3 - це клітинка, яка містить основне випадаюче меню.
• Натисніть OK.

Тепер, коли ви робите вибір у спадному меню 1, параметри, перелічені у випадаючому списку 2, автоматично оновлюються.

Завантажте файл прикладу

Як це працює? - Умовний випадаючий список (у комірці E3) відноситься до = INDIRECT (D3). Це означає, що коли ви вибираєте «Фрукти» у клітинці D3, випадаючий список у E3 посилається на іменований діапазон «Фрукти» (за допомогою функції «ПРОМІЖНА») і, отже, перераховує всі елементи цієї категорії.

Важлива примітка під час роботи з умовними випадаючими списками в Excel:

• Після того, як ви зробили вибір, а потім змінили батьківське спадне меню, залежне спадне меню не зміниться, а отже, буде неправильним. Наприклад, якщо обрати США як країну, а потім вибрати штат Флорида, а потім повернутися назад і змінити країну на Індію, штат залишиться Флоридою. Ось чудовий підручник Debra щодо очищення залежних (умовних) випадаючих списків у Excel при зміні виділення.
• Якщо в головній категорії є більше одного слова (наприклад, "Сезонні фрукти" замість "Фрукти"), то вам потрібно використати формулу = НЕПРЯМОЮ (ЗАМІНА (D3, "", "_")), а не проста функція непряма, показана вище. Причиною цього є те, що Excel не дозволяє пробіли в іменованих діапазонах. Тож коли ви створюєте іменований діапазон із використанням кількох слів, Excel автоматично вставляє підкреслення між словами. Отже, іменним асортиментом "Сезонні фрукти" буде "Сезонні фрукти". Використання функції SUBSTITUTE у функції INDIRECT забезпечує пробіли є перетворюється на підкреслення.