Список, що випадає в Excel: як зробити і зв’язати дані

Приклад знаком нам за формами на сайтах. Зручно вибирати вже готові значення. Наприклад, ніхто не вводить місяць вручну, його беруть з такого переліку. Заповнити список, що випадає в 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

Як тільки Ви переведете покажчик на іншу клітинку, Ви побачите перелік обраних міст.

Ссылка на основную публикацию