1. Створення списку
2. Список, що випадає в Excel з підстановкою даних
3. Список, що випадає в Excel з даними з іншого листа / файлу
4. Як зробити залежні списки, що випадають
5. Вибір декількох значень зі списку Excel
6. Список, що випадає з пошуком

Під списком, що випадає розуміється зміст в одній комірці кількох значень. Коли користувач клацає по стрілочки праворуч, з'являється певний перелік. Можна вибрати конкретне.

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

Створення списку

Шлях: меню «Дані» - інструмент «Перевірка даних» - вкладка «Параметри». Тип даних - «Список».

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

1. Вручну через «точку-з-коми» в поле «Джерело».

2. Ввести значення заздалегідь. А в якості джерела вказати діапазон комірок зі списком.

3. Призначити ім'я для діапазону значень і в поле джерело вписати це ім'я.

Будь-який з варіантів дасть такий результат.

Список, що випадає в Excel з підстановкою даних

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

1. Виділяємо діапазон для списку. У головному меню знаходимо інструмент «Форматувати як таблицю».

2. Відкриються стилі. Вибираємо будь-хто. Для вирішення нашої задачі дизайн не має значення. Наявність заголовка (шапки) важливо. У нашому прикладі це осередок А1 зі словом «Дерева». Тобто потрібно вибрати стиль таблиці з рядком заголовка. Отримуємо наступний вигляд діапазону:

3. Ставимо курсор в осередок, де буде знаходитися список, що випадає. Відкриваємо параметри інструменту «Перевірка даних» (вище описаний шлях). В поле «Джерело» прописуємо таку функцію:

Протестуємо. Ось наша таблиця зі списком на одному аркуші:

Додамо в таблицю нове значення «ялинка».

Тепер видалимо значення «береза».

Здійснити задумане нам допомогла «розумна таблиця», яка легка «розширюється», змінюється.

Тепер зробимо так, щоб можна було вводити нові значення прямо в клітинку з цим списком. І дані автоматично додавалися в діапазон.

1. Сформуємо іменований діапазон. Шлях: «Формули» - «Диспетчер імен» - «Створити». Вводимо унікальну назву діапазону - ОК.

2. Створюємо список, що розкривається в будь-якому осередку. Як це зробити, вже відомо. Джерело - ім'я діапазону: = дерева.
3. Знімаємо галочки на вкладках «Повідомлення для введення», «Повідомлення про помилку». Якщо цього не зробити, Excel не дозволить нам вводити нові значення.

4. Викликаємо редактор Visual Basic. Для цього клацаємо правою кнопкою миші по назві листа і переходимо по вкладці «Оригінальний текст». Або одночасно натискаємо клавіші Alt + F11. Копіюємо код (тільки вставте свої параметри).
Private Sub Worksheet_Change (ByVal Target As Range) Dim lReply As Long If Target.Cells.Count> 1 Then Exit Sub If Target.Address = "$ C $ 2" Then If IsEmpty (Target) Then Exit Sub If WorksheetFunction.CountIf (Range ( "Дерева"), Target) = 0 Then lReply = MsgBox ( "Додати введене ім'я" & _ Target & "в список, що випадає?", vbYesNo + vbQuestion) If lReply = vbYes Then Range ( "Дерева") .Cells (Range ( "Дерева") .Rows.Count + 1, 1) = Target End If End If End If End Sub
5. Зберігаємо, встановивши тип файлу «з підтримкою макросів».

6. Переходимо на лист зі списком. Вкладка «Розробник» - «Код» - «Макрос». Швидкий доступ для швидкого виклику - Alt + F8. Вибираємо потрібне ім'я. Натискаємо «Виконати».

Коли ми введемо в порожню осередок списку нове найменування, з'явиться повідомлення: «Додати введене ім'я баобаб в список, що випадає?».

Натиснемо «Так» і додатися ще один рядок зі значенням «баобаб».

Список, що випадає в Excel з даними з іншого листа / файлу

Коли значення для списку розташовані на іншому аркуші або в іншій книзі, стандартний спосіб не працює. Вирішити завдання можна за допомогою функції ДВССИЛ: вона сформує правильне посилання на зовнішнє джерело інформації.

1. Робимо активної клітинку, куди хочемо помістити список, що розкривається.
2. Відкриваємо параметри перевірки даних. В поле «Джерело» вводимо формулу: = ДВССИЛ ( "[Спісок1.xlsx] Лист1! $ A $ 1: $ A $ 9").

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

Як зробити залежні списки, що випадають

Візьмемо три іменованих діапазону:

Це обов'язкова умова. Вище описано, як зробити звичайний список іменованих діапазоном (за допомогою «Диспетчера імен»). Пам'ятаємо, що ім'я не може містити пробілів і розділових знаків.

1. Створимо перший список, що випадає, куди увійдуть назви діапазонів.

2. Коли поставили курсор в поле «Джерело», переходимо на лист і виділяємо поперемінно потрібні комірки.

3. Тепер створимо другий список, що розкривається. У ньому повинні відображатися ті слова, які відповідають обраному в першому списку назвою. Якщо «Дерева», то «граб», «дуб» і т.д. Вводимо в поле «Джерело» функцію виду = ДВССИЛ (E3). E3 - осередок з ім'ям першого діапазону.


Вибір декількох значень зі списку Excel

Буває, коли із списку необхідно вибрати відразу кілька елементів. Розглянемо шляхи реалізації завдання.

1. Створюємо стандартний список за допомогою інструмента «Перевірка даних». Додаємо в вихідний код листа готовий макрос. Як це робити, описано вище. З його допомогою праворуч від списку будуть додаватися обрані значення.
Private Sub Worksheet_Change (ByVal Target As Range) On Error Resume Next If Not Intersect (Target, Range ( "Е2: Е9")) Is Nothing And Target.Cells.Count = 1 Then Application.EnableEvents = False If Len (Target.Offset (0, 1)) = 0 Then Target.Offset (0, 1) = Target Else Target. End (xlToRight) .Offset (0, 1) = Target End If Target.ClearContents Application.EnableEvents = True End If End Sub
2. Щоб обрані значення показувалися знизу, вставляємо інший код обробника.
Private Sub Worksheet_Change (ByVal Target As Range) On Error Resume Next If Not Intersect (Target, Range ( "Н2: К2")) 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
3. Щоб обрані значення відображалися в одній комірці, розділені будь-яким знаком пунктуації, застосуємо такий модуль.

Private Sub Worksheet_Change (ByVal Target As Range)
On Error Resume Next
If Not Intersect (Target, Range ( "C2: C5")) 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


Не забуваємо міняти діапазони на «свої». Списки створюємо класичним способом. А всю іншу роботу будуть робити макроси.

Список, що випадає з пошуком

1. На вкладці «Розробник» знаходимо інструмент «Вставити» - «ActiveX». Тут нам потрібна кнопка «Поле зі списком» (орієнтуємося на спливаючі підказки).

2. Клацаємо по значку - стає активним «Режим конструктора». Малюємо курсором (він стає «хрестиком») невеликий прямокутник - місце майбутнього списку.

3. Тиснемо «Властивості» - відкривається перелік налаштувань.

4. Вписуємо діапазон в рядок ListFillRange (руками). Осередок, куди буде виводитися вибране значення - в рядок LinkedCell. Для зміни шрифту і розміру - Font.

Завантажити приклад списку

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