Приклад знаком нам за формами на сайтах. Зручно вибирати вже готові значення. Наприклад, ніхто не вводить місяць вручну, його беруть з такого переліку. Заповнити список, що випадає в Excel можна з використанням різних інструментів. У статті розглянемо кожен з них.
Як зробити випадаючий список в Excel
Як зробити випадаючий список в Excel 2010 або 2016 за допомогою однієї командою на панелі інструментів? На вкладці "Дані" в розділі "Робота з даними" знайдіть кнопку "Перевірка даних". Натисніть на неї і виберіть перший пункт.
Відкриється вікно. У вкладці "Параметри" в випадаючому розділі "Тип даних" виберіть "Список".
Знизу з'явиться рядок для зазначення джерел.
Вказувати інформацію можна по-різному.
- Ручний ввід
Введіть перелік через крапку з комою.
- Вибір діапазону значень з листа Excel
Для цього почніть виділяти осередки мишею.
Як відпустіть - вікно знову стане нормальним, а в рядку з'являться адреси.
- Створення списку в Excel з підстановкою даних
Спочатку призначимо ім'я. Для цього створіть на будь-якому аркуші таку таблицю.
Виділіть її та натисніть праву кнопку миші. Клацніть по команді "Присвоїти ім'я".
Введіть ім'я в рядок зверху.
Викличте вікно "Перевірка даних" і в полі "Джерело" вкажіть ім'я, поставивши перед ним знак "=".
У будь-якому з трьох випадків Ви побачите потрібний елемент. Вибір значення зі списку Excel відбувається за допомогою миші. Натисніть на нього і з'явиться перелік зазначених даних.
Ви дізналися, як створити список, що випадає в осередку Excel. Але можна зробити і більше.
Підстановка динамічних даних Excel
Якщо Ви додасте якесь значення в діапазон даних, які підставляються в перелік, то в ньому зміни не відбудеться, поки вручну НЕ будуть вказані нові адреси. Щоб зв'язати діапазон і активний елемент, необхідно оформити перший як таблицю. Створіть ось такий масив.
Виділіть її та на вкладці "Головна" виберіть будь-який стиль таблиці.
Обов'язково поставте галочку внизу.
Ви отримаєте таке оформлення.
Створіть активний елемент, як було описано вище. Як джерело введіть формулу
= ДВССИЛ ( "Таблиця 1 [Міста]")
Щоб дізнатися ім'я таблиці, перейдіть на вкладку "Конструктор" і подивіться його. Можете поміняти ім'я на будь-яке інше.
Функція ДВССИЛ створює посилання на клітинку або діапазон. Тепер ваш елемент в осередку прив'язаний до масиву даних.
Спробуємо збільшити кількість міст.
Зворотна процедура - підстановка даних зі списку в таблицю Excel, працює дуже просто. У осередок, куди треба вставити вибране значення з таблиці, введіть формулу:
= Адрес_ячейкі
Наприклад, якщо перелік даних знаходиться в осередку D1, то в осередку, куди будуть виведені вибрані результати введіть формулу
= 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. Сподіваємося, ця інформація допоможе вам.
Відмінного Вам дня!
