Вибір зі спливаючого списку excel. Створення списку, що випадає в Excel. Застосування елементів ActiveX
Елемент списку знайомий нам за формами на сайтах. Зручно вибирати вже готові значення. Наприклад, ніхто не вводить місяць вручну, його беруть із такого переліку. Заповнити випадаючий список в Excel можна за допомогою різних інструментів. У статті розглянемо кожен із них.
Як зробити список, що випадає в Excel
Як зробити список, що випадає в Excel 2010 або 2016 за допомогою однієї командою на панелі інструментів? На вкладці «Дані» у розділі «Робота з даними» знайдіть кнопку «Перевірка даних». Натисніть і виберіть перший пункт.
Відкриється вікно. У вкладці «Параметри» у розділі «Тип даних» виберіть «Список». 
Знизу з'явиться рядок для вказівки джерел. 
Вказувати інформацію можна по-різному.
Спочатку призначимо ім'я. Для цього створіть на будь-якому аркуші таблицю. 
Виділіть її та натисніть праву кнопку миші. Клацніть на команді «Присвоїти ім'я». 
Введіть ім'я у рядок зверху. 
Викличте вікно «Перевірка даних» і введіть ім'я в полі «Джерело», поставивши перед ним знак «=». 
У будь-якому із трьох випадків Ви побачите потрібний елемент. Вибір значення зі списку Excel відбувається за допомогою миші. Натисніть і з'явиться перелік зазначених даних. 
Ви дізналися, як створити список, що випадає, в комірці Excel. Але можна зробити й більше.
Підстановка динамічних даних Excel
Якщо Ви додасте якесь значення до діапазону даних, які підставляються до переліку, то в ньому зміни не відбудеться, доки вручну не будуть вказані нові адреси. Щоб зв'язати діапазон та активний елемент, необхідно оформити перший як таблицю. Створіть такий масив. 
Виділіть його та на вкладці «Головна» виберіть будь-який стиль таблиці. 
Обов'язково встановіть галочку внизу. 
Ви отримаєте таке оформлення. 
Створіть активний елемент, як описано вище. Як джерело введіть формулу
ДВССИЛ("Таблица1[Місто]")
Щоб дізнатися ім'я таблиці, перейдіть на вкладку «Конструктор» та перегляньте його. Можете змінити ім'я на будь-яке інше. 
Функція ДВССИЛ створює посилання на комірку або діапазон. Тепер ваш елемент у комірці прив'язаний до масиву даних.
Спробуємо збільшити кількість міст. 
Зворотня процедура - підстановка даних зі списку, що випадає в таблицю Excelпрацює дуже просто. У комірку, куди треба вставити вибране значення таблиці, введіть формулу:
Адреса_осередки
Наприклад, якщо перелік даних знаходиться в осередку D1, то в осередку, куди будуть виведені вибрані результати, введіть формулу
Як прибрати (видалити) список, що випадає в Excel
Відкрийте вікно налаштування списку та виберіть «Будь-яке значення» в розділі «Тип даних». 
Непотрібний елемент зникне.
Залежні елементи
Іноді в Excel трапляється необхідність створення кількох переліків, коли один залежить від іншого. Наприклад, кожне місто має кілька адрес. При виборі у першому ми маємо отримати лише адреси обраного населеного пункту. 
У цьому випадку дайте ім'я кожному стовпцю. Виділіть без першої комірки (назви) та натисніть праву кнопку миші. Виберіть пункт «Присвоїти ім'я». 
Це буде назва міста. 
При назві Санкт-Петербурга і Нижнього НовгородаВи отримаєте помилку, оскільки ім'я не може містити пробілів, символів підкреслення, спеціальних символіві т.д. 
Тому перейменуємо ці міста, поставивши нижнє підкреслення. 
Перший елемент у осередку A9 створюємо звичайним чином. 
А в другому пропишемо формулу:
ДВССИЛ(A9)
Спочатку Ви побачите повідомлення про помилку. Погоджуйтесь. 
Проблема у відсутності вибраного значення. Як тільки в першому переліку буде обрано місто, друге запрацює. 
Як налаштувати залежні списки, що випадають в Excel з пошуком
Можна використовувати динамічний діапазон даних для другого елемента. Це зручніше, якщо кількість адрес зростатиме.
Створимо перелік міст, що випадає. Помаранчевим виділено іменований діапазон. 
Для другого переліку потрібно запровадити формулу:
ЗМІЩ($A$1;ПОШУКПОЗ($E$6;$A:$A;0)-1;1;РАХУНКИ($A:$A;$E$6);1)
ПОШУКПОЗ повертає номер комірки з обраним у першому списку (E6) містом у зазначеній області SA:$A.
РАХУНКИ вважає кількість збігів в діапазоні зі значенням у зазначеній комірці (E6). 
Ми отримали пов'язані списки, що випадають в Excel з умовою на збіг і пошуком діапазону для нього.
Мультивибір
Часто нам необхідно отримати кілька значень із набору даних. Можна вивести їх у різні осередки, а можна поєднати в одну. У кожному разі необхідний макрос.
Натисніть на ярлику аркуша внизу праву кнопку миші та виберіть команду «Переглянути код». 
Відкриється вікно розробника. У нього слід вставити наступний алгоритм.
Private Sub Worksheet_Change(ByVal Target As Range) On Error Resume Next If Not Intersect(Target, Range("C2:F2")) Is Nothing And Target.Cells.Count = 1 Then Application.EnableEvents = False If Len(Target.Offset (1, 0)) = 0 Then Target.Offset(1, 0) = Target Else Target.End(xlDown).Offset(1, 0) = Target End If Target.ClearContents Application.EnableEvents = True End If End Sub
Зверніть увагу, що у рядку
If Not Intersect(Target, Range("E7")) Is Nothing And Target.Cells.Count = 1 Then
Слід проставити адресу осередку зі списком. В нас це буде E7.
Поверніться на лист Excel і створіть у осередку E7 список. 
При виборі значення будуть відображатися під ним. 
Наступний код дозволить накопичувати значення в комірці.
Private Sub Worksheet_Change(ByVal Target As Range) On Error Resume Next If Not Intersect(Target, Range("E7")) Is Nothing And Target.Cells.Count = 1 Then Application.EnableEvents = False newVal = Target Application.Undo oldval = Target If Len(oldval)<>0 And oldval<>newVal Then Target = Target & "," & newVal Else Target = newVal End If If Len(newVal) = 0 Then Target.ClearContents Application.EnableEvents = True End If End Sub
Як тільки Ви переведете вказівник на іншу комірку, Ви побачите список вибраних міст. Для прочитайте цю статтю. 
Ми розповіли, як додати та змінити випадаючий список у осередок Excel. Сподіваємось, ця інформація допоможе вам.
Чудового Вам дня!
Список, що випадає в Excel - це досить зручна функція, яка допоможе створити більш складні документи і зробити роботу зручною для візуального сприйняття користувача.
Декілька найпоширеніших типів випадаючих списків, які можна створити в Excel:
Зробити список в Ексель з мультивибором
Створити список у осередку програми можна за допомогою вбудованих інструментів панелі керування.
Розглянемо докладніше всі основні та найпоширеніші типи, і процес їх створення на практиці.
Порада!Класичний спливаючий список програми має функцію мультивибору, тобто користувач, натиснувши відповідну групу, може вибрати відповідний варіант.
Мультивибір необхідний, коли за допомогою одного переліку потрібно задати значення кількох осередків документа.
Зовнішній вигляд з мультивибором для заповнення кількох осередків.
- Виділіть комірки. Якщо подивитися на малюнок, виділяти потрібно починаючи з C2 і закінчуючи C5;
- Знайдіть вкладку "Дані", яка розташована на головній панелі інструментів у вікні програми. Потім натисніть клавішу перевірки даних, як показано на малюнку нижче;
Перевірка даних
- У вікні відкрийте першу вкладку і виберіть елемент, як показано на малюнку. Таким чином, у даних місцях створиться перелік. У текстовому полі вкажіть діапазон комірок, які будуть заповнюватися при кожному виборі елементів.
Заповнення інформації для створення списку, що випадає, з мультивибором значень осередків
Прімер заповнення:
Таким чином ви створите класичний горизонтальний списокз функцією мультивибору.
Однак, щоб він заповнювався автоматично, необхідно створити макрос із наступним вихідним кодом, що показано на малюнку.
Програмний код для створення макросу
- Гарячі клавіші Excel - Найнеобхідніші варіанти
- Формули EXCEL з прикладами - Інструкція застосування
- Як побудувати графік в Excel - Інструкція
Створити список у Екселі з наповненням
Стандартний перелік із наповненням дозволить вам автоматизувати процес введення інформації. При натисканні на неї з'явиться вибір її можливих значень.
Користувачеві залишається лише вибрати потрібне значення для заповнення.
Список користувачів з наповненням
Найпростіший спосіб створити такий перелік – використовувати «розумні таблиці програми».
З їх допомогою можна легко та швидко форматувати необхідні вам види списків із наповненням:
- Виділіть необхідні клітинки та натисніть у головній вкладці на клавішу «Форматувати як таблицю»;
Приклад форматування та розташування клавіш:
Процес форматування
- За допомогою вбудованого конструктора можна змінювати стовпці таблиці та їх призначення.
Форматування переліку з наповненням за допомогою «розумних таблиць»
Створити список, що розкривається, в осередку (версія програми 2010)
Також можна створювати перелік у осередку аркуша.
Приклад наведено на малюнку нижче:
Приклад у осередку листа
Щоб створити такий, виконайте інструкції:
Заповнення та перевірка значень, що вводяться в процесі створення списку із заповненням осередку аркуша
Також вам може бути цікаво:
- Округлення в Excel - Покрокова інструкція
- Таблиця Ексель - Створення та налаштування
Підсумки
У статті були розглянуті основні типи списків, що випадають, і способи їх створення. Пам'ятайте, що процес їхнього створення ідентичний у таких версіях програми: 2007, 2010, 2013.
Випадні списки допоможуть вам не тільки налаштувати більше швидку роботувсього документа, а й зробити його адаптованим до роботи з великими проектами.
Тематичні відеоролики до статті:
Створення списків, що випадають, в Excel
4 способи створити список, що випадає на аркуші Excel.
Як зробити список, що розкривається, в Excel
Як до екселя зробити випадаючий список Як додати спливаюча список до екселя Як створити в ексел список, що розкривається
Виберіть для завдання списку. У меню відкрийте пункти "Дані" - "Перевірка". Після чого перейдіть у новому вікні у вкладку «Параметри» і в полі «Тип даних», що відкрилося, встановіть рядок «Список». При цьому в тому ж таки вікні з'явиться поле «Джерело». Введіть символ «=» та ім'я виділеного діапазону, яке було задано осередкам з даними. Щоб застосувати параметри, натисніть «Enter» або «Ok». Це варіант найпростішого списку, що випадає.
При цьому в тому ж таки вікні з'явиться поле «Джерело». Введіть символ «=» та ім'я виділеного діапазону, яке було задано осередкам з даними. Натисніть «Enter» або «Ok», щоб застосувати встановлені параметри. Це варіант найпростішого списку, що випадає.
В Excel існує можливості для завдання списку, що має більш складну конструкцію. Для цього застосовують елемент керування під назвою "поле зі списком", що вставляється на робочий лист Excel. Щоб його встановити, відкрийте в меню пункти "Вид", потім "Панелі інструментів" та підпункт "Форми".
Виберіть на панелі елементів керування значок «поле зі списком» - це і є список, що випадає. Намалюйте мишкою прямокутник у формі поля. Виділіть правою кнопкою миші намальований список та вкажіть команду «Формат об'єкта…».
У діалоговому вікні, що з'явилося, у полі «Формувати список по діапазону» задайте потрібний діапазон комірок. Для цього мишкою виділіть комірки, які повинні потрапити в даний список Excel, що випадає. У полі «Зв'язок із коміркою» встановіть номер комірки, щоб вивести порядковий номер елемента, вибраного у списку. Вкажіть необхідну кількість рядків створюваного списку. Кнопка «Ок» застосує всі встановлені параметри, і список готовий до використання.
Найпростіше це завдання виконати так. Натисканням правою кнопкою по комірці під стовпцем з даними викликаємо контекстне меню. Тут цікавить поле Вибрати зі списку. Те саме можна зробити, натиснувши комбінацію клавіш Alt+Стрілка вниз.
Однак, такий метод не спрацює, якщо бажаєте створити список в іншій комірці, що не входить в діапазон і більш ніж одного до або після. Це дозволить зробити наступний метод.
Стандартний спосіб
Потрібно виділити діапазон осередків, з яких буде створено випадаючий список, після чого Вставка – Ім'я – Присвоїти(Excel 2003). У більш нової версії(2007, 2010, 2013, 2016) перейдіть на вкладку Формули, де у розділі Певні імена
виявіть кнопку Диспетчер імен.
Тиснемо кнопку Створити, вносите ім'я, можна будь-яке, після чого ОК.
Виділіть комірки(або кілька), де хочете вставити перелік потрібних полів. У меню виберіть Дані – Тип даних – перелік. В полі Джереловнесіть раніше створене ім'я або можна просто вказати діапазон, що буде рівносильно. 
Тепер отриманий осередок можна скопіюватиу будь-яке місце на аркуші, вона міститиме перелік необхідних елементів таблиці. Також її можна розтягнути, щоб отримати діапазон зі списками, що випадають. 
Цікавий момент – при зміні даних у діапазоні, заснований на ньому список також змінюватиметься, тобто він буде динамічним.
Використовуємо елементи керування
Метод заснований на вставціелемента управління, званому « поле зі списком«, Яке буде діапазон даних.
Виберіть вкладку Розробник(для Excel 2007/2010), в інших версіях потрібно активувати цю вкладку на стрічці в параметрах – Налаштувати стрічку.
Переходимо на цю вкладку – тиснемо кнопку Вставити. В елементах управління вибираємо Поле зі списком(не ActiveX) і натисніть . Намалюйте прямокутник.
Правою кнопкою по ньому – Формат об'єкту.
Зв'язком з коміркою виберіть поле, куди слід помістити порядковий номер елемента у списку. Потім тиснемо ОК.
Застосування елементів ActiveX
Все, як і в попередньому, тільки вибираємо Поле зі списком(ActiveX). 
Всім привіт, дорогі друзі та гості мого блогу. І знову з вами я, Дмитре Костіне, і сьогодні мені хочеться ще розповісти вам про екселе, а точніше про одну чудову фішку, яку я тепер завжди застосовую. Чи стикалися ви із ситуацією. коли заповнюєте таблицю й у якомусь стовпці необхідно постійно вводити одне з кількох значень. Ееєе. Давайте краще розповім вам на прикладі.
Припустимо, коли я створював таблицю обліку комп'ютерного обладнання (ще давно) у себе на роботі, то щоб зробити весь процес роботи більш зручним і швидким, я робив список, що випадає, у певних стовпцях і вставляв туди певні значення. І коли я заповнював стовпець. Операційна система» (А адже не на всіх комп'ютерах вона одна і та ж), то я забивав кілька значень (7, 8, 8.1, 10), а потім просто вибирав це все одним натисканням кнопки миші.
І таким чином вже не потрібно вбивати в кажу осередок версію вінди, або копіювати з одного осередку і вставляти в інший. Загалом не буду вас нудити, давайте краще приступимо. Давайте я покажу вам, як створити список, що випадає в Excel, використовуючи дані з іншого аркуша. Для цього створимо деяку табличку, до якої ми зможемо це застосувати. Я робитиму це в 2013 версії, але для інших версій процес ідентичний, так що не переживайте.
Підготовка
Основні дії
Тепер так само попрацюйте з графами «Прізвище фахівця»і «Підсумок усунення», після чого знову поверніться на головний аркуш та починайте повноцінно працювати з таблицею. Ви самі побачите, як це здорово і зручно, коли можна вибрати дані з доступних заздалегідь підготовлених значень. Завдяки цьому рутинне заповнення таблиць полегшується. 
До речі в таких документах для зручнішого відображення краще. Тоді буде взагалі все круто.
Ну, а на сьогодні я свою статтю закінчую. Я сподіваюся, що те, що ви сьогодні дізналися, стане вам у нагоді при роботі в екселі. Якщо стаття вам сподобалася, звичайно ж не забудьте підписатися на оновлення мого блогу. Ну а вас я з нетерпінням знову чекатиму на сторінках мого блогу. Удачі вам і поки що!
З повагою, Дмитро Костін