Як до випадаючого списку додати значення. Щоб створити таку, дотримуйтесь інструкцій. Список, що випадає в Excel з підстановкою даних

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

Відеоурок

Як створити список, що випадає в Екселі на основі даних з переліку

Припустимо, що у нас є перелік фруктів:

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

  • Перейти на вкладку “ Дані” => розділ “ Робота з даними ” на панелі інструментів => вибираємо пункт “ Перевірка даних “.
  • У спливаючому вікні “ Перевірка значень, що вводяться ” на вкладці “ Параметри” у типі даних вибрати “ перелік “:
  • В полі " Джерело” ввести діапазон назв фруктів =$A$2:$A$6або просто поставити курсор миші у поле введення значень “ Джерело” а потім мишкою вибрати діапазон даних:

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

Як зробити список, що випадає в Excel використовуючи ручне введення даних

На прикладі вище ми вводили список даних для випадаючого списку шляхом виділення діапазону осередків. Крім цього способу, ви можете вводити дані для створення списку вручну (необов'язково їх зберігати в будь-яких осередках).

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

  • Вибрати осередок, в якому ми хочемо створити список, що випадає;
  • Перейти на вкладку “ Дані” => розділ “ Робота з даними ” на панелі інструментів => вибрати пункт “ Перевірка даних “:
  • У спливаючому вікні “ Перевірка значень, що вводяться ” на вкладці “ Параметри” у типі даних вибрати “ перелік “:
  • В полі " Джерело” ввести значення “Так; Ні”.
  • Натискаємо “ ОК “

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

Якщо ви хочете одночасно створити список, що випадає, в декількох осередках - виділіть потрібні осередкита дотримуйтесь інструкцій вище.

Як створити список, що розкривається, в Ексель за допомогою функції ЗМІЩ

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

Наприклад, у нас є список із переліком фруктів:

Для того щоб зробити список, що випадає, за допомогою формули необхідно зробити наступне:

  • Вибрати осередок, в якому ми хочемо створити список, що випадає;
  • Перейти на вкладку “ Дані” => розділ “ Робота з даними ” на панелі інструментів => вибрати пункт “ Перевірка даних “:
  • У спливаючому вікні “ Перевірка значень, що вводяться ” на вкладці “ Параметри” у типі даних вибрати “ перелік “:
  • В полі " Джерело” ввести формулу: =ЗМІЩ(A$2$;0;0;5)
  • Натиснути “ ОК “

Система створить список, що випадає, з переліком фруктів.

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

На прикладі вище ми використали формулу =ЗМІЩ(посилання;сміщ_по_рядків;сміщ_по_стовпцям;[висота];[ширина]).

Ця функція містить п'ять аргументів. У аргументі “ посилання” (у прикладі $A$2) вказується з якого осередку починати зміщення. У аргументах сміщ_по_рядків” та "сміщ_по_стовпцям"(У прикладі вказано значення “0”) – на скільки рядків/стовпців потрібно зміщуватися для відображення даних. У аргументі “ [висота]” вказано значення “5”, що означає висоту діапазону осередків. Аргумент “ [ширина]” ми не вказуємо, тому що в нашому прикладі діапазон складається з однієї колонки.

Використовуючи цю формулу, система повертає вам як дані для списку, що випадає, діапазон осередків, що починається з осередку $A$2, що складається з 5 осередків.

Як зробити список в Excel з підстановкою даних (з використанням функції ЗМІЩ)

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

Для створення списку потрібно:

  • Вибрати осередок, в якому ми хочемо створити список, що випадає;
  • Перейти на вкладку “ Дані” => розділ “ Робота з даними ” на панелі інструментів => вибрати пункт “ Перевірка даних “;
  • У спливаючому вікні “ Перевірка значень, що вводяться ” на вкладці “ Параметри” у типі даних вибрати “ перелік “;
  • В полі " Джерело” ввести формулу: =ЗМІЩ(A$2$;0;0;РАХУНКИ($A$2:$A$100;”)<>”))
  • Натиснути “ ОК “

У цій формулі, в аргументі “[ висота]” ми вказуємо як аргумент, що означає висоту списку з даними – формулу , яка розраховує в заданому діапазоні A2:A100кількість не порожніх осередків.

Примітка: для коректної роботиформули, важливо, щоб у списку даних для відображення у меню, що випадає, не було порожніх рядків.

Як створити список, що випадає в Excel з автоматичною підстановкою даних

Для того щоб у створений вами список, що випадає, автоматично підвантажувалися нові дані, потрібно проробити наступні дії:

  • Створюємо список даних для відображення у списку, що випадає. У нашому випадку це список кольорів. Виділяємо список лівою кнопкою миші:
  • На панелі інструментів натискаємо пункт “ Форматувати як таблицю “:

  • З меню вибираємо стиль оформлення таблиці:


  • Натиснувши клавішу “ ОК” у спливаючому вікні, підтверджуємо обраний діапазон осередків:
  • Потім, виділимо діапазон даних таблиці для списку, що випадає, і привласним йому ім'я в лівому полі над стовпцем "А":

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

  • Вибрати осередок, в якому ми хочемо створити список;
  • Перейти на вкладку “ Дані” => розділ “ Робота з даними ” на панелі інструментів => вибрати пункт “ Перевірка даних “:
  • У спливаючому вікні “ Перевірка значень, що вводяться ” на вкладці “ Параметри” у типі даних вибрати “ перелік “:
  • У полі джерело вказуємо = "назва вашої таблиці" . У нашому випадку ми її назвали “ перелік “:


  • Готово! Створений випадаючий список, у ньому відображаються всі дані із зазначеної таблиці:

  • Для того щоб додати нове значення в список, що випадає - просто додайте в наступну після таблиці з даними комірку інформацію:

  • Таблиця автоматично розширить діапазон даних. Список, що випадає, відповідно поповниться новим значенням з таблиці:


Як скопіювати список, що випадає в Excel

В Excel є можливість копіювати створені списки, що випадають. Наприклад, в осередку А1 у нас є список, що випадає, який ми хочемо скопіювати в діапазон осередків А2: А6 .

Для того щоб скопіювати список з поточним форматуванням:

  • натисніть лівою клавішею миші на комірку зі списком, що випадає, яку ви хочете скопіювати;
  • CTRL+C ;
  • виділіть комірки в діапазоні А2: А6, в які ви хочете вставити список, що випадає;
  • натисніть клавіші на клавіатурі CTRL+V .

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

  • натисніть лівою клавішею миші на комірку зі списком, що випадає, який ви хочете скопіювати;
  • натисніть клавіші на клавіатурі CTRL+C ;
  • виберіть комірку, в яку ви хочете вставити список, що випадає;
  • натисніть праву кнопку миші => викличте меню, що випадає, і натисніть “ Спеціальна вставка “;
  • У вікні в розділі “ Вставити” виберіть пункт “ умови на значення “:
  • Натисніть “ ОК “

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

Як виділити всі осередки, що містять список, що випадає в Екселі

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

  • Натисніть на вкладку “ Головна” на панелі інструментів;
  • Натисніть “ Знайти та виділити ” і виберіть “ Виділити групу осередків “:
  • У діалоговому вікні виберіть “ Перевірка даних “. У цьому полі є можливість вибрати пункти Усіх” та “ Цих же “. “Усіх” дозволить виділити всі списки, що випадають на аркуші. Пункт “ цих же” покаже списки, що випадають, схожі за змістом даних у випадаючому меню. У нашому випадку ми обираємо “ всіх “:

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

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

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

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

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

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



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

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


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

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

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

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

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


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

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

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

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

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

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

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

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

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

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

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

    4. Вибір кількох значень із списку Excel, що випадає

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

    1. Створюємо стандартний списокза допомогою інструмента "Перевірка даних". Додаємо в вихідний кодлистя готовий макрос. Як це робити, описано вище. З його допомогою праворуч від випадаючого списку додаватимуться вибрані значення.
      2. Private Sub Worksheet_Change(ByVal Target As Range) On Error Resume Next If Not Intersect(Target, Range("Е2:Е9" )) (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. Щоб вибрані значення показувалися знизу, вставляємо інший код обробника.
      3. 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. Щоб вибрані значення відображалися в одному осередку, розділені будь-яким розділовим знаком, застосуємо такий модуль.

      4. 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

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

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

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

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

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

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

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

Варіант №0 - "Елементарний".

Роблячи черговий запис в комірку А9, при наборі першої літери найменування профілю, наприклад "Ш", Excel пропонує заповнити комірку словом "Швеллер". Після набору "Ш" достатньо натиснути кнопку "Введення" на клавіатурі - і слово буде введене в комірку.

«Мінус» даного варіанту є необхідність введення іноді кількох літер і неможливість заздалегідь створити довідник найменувань, що обмежує свободу діяльності користувача.

Переходимо безпосередньо до варіантів створення списків, що розкриваються.

Варіант №1 - "Найпростіший".

Якщо активувати мишею комірку А9, натиснути сполучення клавіш «Alt» «↓», то з'явиться список, що містить все раніше введені в цьому стовпці значення. Залишається лише вибрати мишею потрібний запис. Замість набору вищезгаданого поєднання клавіш можна натиснути правою кнопкоюмиші викликати контекстне меню і вибрати в ньому пункт «Вибрати зі списку, що розкривається…». В результаті побачимо той же список, що випадає.

У даному варіантіактивний осередок обов'язково повинен примикати знизу до діапазону значень, а сам діапазон не повинен містити порожніх осередків!

Варіант №2 - "Простий".

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

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

1. Створюємо список можливих значень, записавши їх у стовпець по одному в комірку. Допустимо це список в осередках А2 ... А8.

2. Активуємо комірку, в якій необхідно помістити список, що розкривається шляхом встановлення в неї курсору. Нехай це буде той самий осередок А9.

3. Вибираємо в головному меню кнопку "Дані" - "Перевірка ...".

4. У вікні «Перевірка значень, що вводяться» вибираємо вкладку «Параметри».

5. У полі «Тип даних:» зі списку, що розкривається (подібного до того, який ми створюємо) вибираємо значення «Список».

6. У полі «Джерело:» вказуємо діапазон, що містить список можливих значень.

7. Встановлюємо (якщо не встановлено за замовчуванням) прапорець «Список допустимих значень» та натискаємо кнопку «ОК».

Список, що розкривається, готовий. Його можна скопіювати як формули у будь-яку кількість осередків!

Варіант №3 - "Складний".

Цей варіант створення списку, що розкривається, не дивлячись на свою назву «Складний», по суті таким не є. Для створення списку, що випадає, в ньому використовується елемент «Поле зі списком» панелі інструментів «Форми».

Створимо список, що розкривається, цим способом.

1. Створюємо список-довідник у осередках А2…А8.

2. Вибираємо в головному меню кнопку "Вид" - "Панелі інструментів" - "Форми".

3. У панелі «Форми», що з'явилася, вибираємо «Поле зі списком» і малюємо його, наприклад, в осередку А9.

Елемент «Поле зі списком» розміщується не в самій комірці, а, як би, над нею! Елемент може бути більшим і перебувати над кількома осередками.

4. Клацаємо правою кнопкою миші по намальованому елементу і в з'явився контекстному менювибираємо "Формат об'єкта".

5. У вікні «Форматування об'єкта» на вкладці «Елемент управління» заповнюємо поля відповідно до рисунка, розташованого нижче і натискаємо «ОК».

6. Список, що розкривається, готовий. Він виводить порядковий номерелемента списку пов'язану комірку В9. (Можете призначити будь-яку зручну вам комірку, не обов'язково В9!)

Для виведення в будь-яку комірку самого значення зі списку-довідника застосуємо функцію ІНДЕКС. Допустимо, нам необхідно вивести значення в комірку А9, розташовану під елементом "Поле зі списком".

Для цього в комірку А9 запишемо формулу: = ІНДЕКС (A2: A8; В9)

Наочний приклад докладно розглянуто у статті «Статті». Можна перейти за посиланням та ознайомитися.

Список, що розкривається, створений цим способом плюс застосування функцій ІНДЕКС та/або ВПР надають безграничні можливостікористувачеві під час вилучення даних із різних базових довідкових таблиць.

Варіант №4 - «Найскладніший».

Для створення списку, що випадає, у цьому випадку використовується також елемент «Поле зі списком», але панелі інструментів «Елементи керування» (в MS Excel 2003). Це так звані елементи ActiveX. Тут все дуже схоже зовні на варіант №3, але значно ширше можливості налаштування та форматування елемента.

1. Вибираємо в головному меню кнопку "Вигляд" - "Панелі інструментів" - "Елементи управління".

2. У панелі «Елементи управління», що з'явилася, вибираємо «Поле зі списком» і малюємо його в осередку А9. ЕлементActiveX«Поле зі списком» розміщується не в самій комірці, а зверху, накриваючи її!

3. Натискаємо кнопку «Властивості» на панелі «Елементи керування» і у вікні «Properties» («Властивості») вручну вписуємо діапазон вихідних даних, адресу зв'язаної комірки (комірки, куди буде введено обране значення) і кількість рядків, що відображаються.

4. Далі за бажанням можна змінити шрифт, його колір, колір фону, і ще ряд параметрів… Нічого складного немає у використанні «Найскладнішого» варіанта – переконайтеся самі. Все інтуїтивно зрозуміло, хоч базові знання англійської мовине завадять!

5. Віджимаємо кнопку «Вихід з режиму конструктора» на панелі «Елементи управління» і перевіряємо роботу списку, що розкривається. Все працює! Вибране значення записано в комірку А9, у нашому прикладі – під елементом «Поле зі списком». Взагалі пов'язаним осередком може бути будь-яка крім осередків, де розташовується базовий список.

Підсумки.

Варіант №0 автоматизує деякою мірою заповнення осередків, але до списків, що розкриваються, звичайно, відношення не має і наведено тут під відповідним номером, як елементарний варіант автоматизації введення повторюваних даних.

На практиці я найчастіше створюю списки, що розкриваються в Excel, використовуючи варіанти №1 і №3, рідше — варіант №2 і зовсім рідко — варіант №4, хоча саме він є, безумовно, найбільш гнучким, що надає найширші можливості.

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

Підписуйтесь на анонси статей у вікні, розташованому наприкінці кожної статті або у вікні вгорі сторінки та не забувайте підтверджуватипідписку кліком за посиланням у листі, який прийде до вас на вказану пошту(може прийти в папку "Спам" - все залежить від налаштувань вашої пошти)!

Корисною чи ні, виявилася ця стаття для вас, шановні читачі? Напишіть про це у коментарях.

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

Як зробити списки в Excel 2007

Наприклад, я створив список міст Московської області. Виділяємо список та створюємо іменований діапазон. Для цього після натискання правої кнопки миші вибираємо в контекстному меню «Ім'я діапазону».

Задаємо ім'я "Місто_М_О" і тиснемо "ОК".

Тепер переходимо в той осередок, де ми хочемо мати список, що випадає, і переходимо на закладку «Дані» верхній панелі. Тут нам знадобиться кнопка "Перевірка даних", розташована в групі "Робота з даними". Вибираємо пункт "Перевірка даних".


У вікні вибираємо тип даних «Список» і в полі «Джерело» вводимо «=Місто_М_О», тобто задане нами ім'я діапазону, який містить список.


Ось, власне, і все. У заданому нами осередку з'явився список, з якого ми можемо вибирати будь-яке значення. При необхідності ми можемо використовувати той же список і в інших осередках.


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

Як це зробити в Excel 2003

Тут, щоб присвоїти ім'я діапазону, нам потрібно зайти в меню «Вставка»


І вікно присвоєння імені виглядає трохи інакше.


Також переходимо в потрібну нам комірку і в меню «Дані» вибираємо «Перевірка». А вікно, що відкрилося, буде таким же, як і в Excel 2007.
Підкорюйте Excel до нових зустрічей!



ПОХОДЖЕННЯ СТАТТІ