Ексель вибрати зі списку. Рекомендації як швидко зробити список, що випадає в MS Excel. Пов'язані списки, що випадають

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

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

Реалізувати так званий список, що випадає, можна кількома методами, які ми і розглянемо нижче.

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

1. У допоміжній таблиці пишемо перелік усіх найменувань – кожен із нового рядка в окремому осередку. У результаті має вийти один стовпець із заповненими даними.
2. Потім відзначаємо всі ці комірки, натискаємо в будь-якому місці зазначеного діапазону правою кнопкою миші і в списку, що відкрився, клацаємо по функції "Присвоїти ім'я..".
   
3. На екрані з'явиться вікно створення імені. Називаємо список так, як хочеться, але за умови – першим символом має бути буква, також не допускається використання певних символів. Тут же передбачена можливість додавання списку примітки у текстовому полі. По готовності натискаємо OK.
   
4. Перемикаємось у вкладку "Дані" в основному вікні програми. Зазначаємо групу осередків, для яких хочемо задати вибір із нашого списку та натискаємо на значок “Перевірка даних” у підрозділі “Робота з даними”.
   
5. На екрані з'явиться вікно “Перевірка значень, що вводяться”. Перебуваючи у вкладці “Параметри” у вигляді даних зупиняємося на опції “Список”. У текстовому полі “Джерело” пишемо знак “рівно” (“=”) та назву щойно створеного списку. У разі – “=Найменування”. Натискаємо OK.
   
6. Все готово. Праворуч від кожного осередку обраного діапазону з'явиться невеликий значок зі стрілкою вниз, натиснувши яку можна відкрити перелік найменувань, який ми заздалегідь склали. Клацнувши за потрібним варіантом зі списку, він відразу ж буде вставлений в комірку. Крім того, значення в комірці тепер може відповідати тільки найменуванню зі списку, що виключить будь-які помилки.
   

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

1. Насамперед, ці інструменти потрібно знайти та активувати, оскільки за замовчуванням вони вимкнені. Переходимо в меню "Файл".
   
2. У переліку зліва знаходимо в самому низу пункт "Параметри" і клацаємо по ньому.
   
3. Переходимо до розділу "Налаштувати стрічку" і в області "Основні вкладки" ставимо галочку навпроти пункту "Розробник". Інструменти розробника будуть додані на стрічку програми. Натисніть OK, щоб зберегти налаштування.
   
4. Тепер у програмі є нова вкладка під назвою "Розробник". Через неї ми і працюватимемо. Спочатку створюємо стовпець з елементами, які будуть джерелами значень для нашого списку.
   
5. Перемикаємось у вкладі "Розробник". У підрозділі "Елементи управління" натискаємо кнопку "Вставити". У списку, що відкрився, в блоці функцій “Елементи ActiveX” клацаємо по значку “Поле зі списком”.
   
6. Далі натискаємо на потрібну комірку, після чого з'явиться вікно зі списком. Налаштовуємо його розміри за межами осередку. Якщо список виділено мишкою, на панелі інструментів буде активний режим конструктора. Натискаємо кнопку “Властивості”, щоб продовжити налаштування списку.
   
7. У параметрах знаходимо рядок “ListFillRange”. У стовпці поряд через двокрапку пишемо координати діапазону осередків, що становлять наш раніше створений список. Закриваємо вікно з параметрами, натиснувши на хрестик.
   
8. Потім клацаємо правою кнопкою миші по вікну списку, далі - за пунктом "Об'єкт ComboBox" і вибираємо "Edit".
   
9. В результаті ми отримуємо список, що випадає, із заздалегідь визначеним переліком.
   
10. Щоб вставити його в кілька осередків, наводимо курсор на правий нижній кут комірки зі списком, і як тільки він змінить вид на хрестик, затискаємо ліву кнопку миші і тягнемо вниз до самого нижнього рядка, в якому нам потрібен подібний список.
    

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

1. Для цього потрібно підготувати щонайменше три стовпці. У першому будуть заповнені найменування товарів, а у другому та третьому – їх можливі одиниці виміру. Стовпців з можливими варіаціями одиниць виміру може бути й більше.
   
2. Спочатку створюємо один загальний список для всіх найменувань продуктів, виділивши всі рядки стовпця "Найменування" через контекстне меню виділеного діапазону.
   
3. Задаємо йому ім'я, наприклад, "Харчування".
   
4. Потім так само формуємо окремі списки для кожного продукту з відповідними одиницями вимірювання. Для більшої наочності візьмемо як приклад першу позицію - "Цибуля". Зазначаємо комірки, що містять усі одиниці виміру для цього продукту, через контекстне меню присвоюємо ім'я, яке має повністю збігатися з найменуванням.
   Так само створюємо окремі списки для решти продуктів у нашому переліку.
5. Після цього вставляємо загальний список з продуктами у верхній осередок першого стовпця основної таблиці – як і в наведеному вище прикладі, через кнопку “Перевірка даних” (вкладка “Дані”).
   
6. Як джерело вказуємо “=Харчування” (згідно з назвою).
   
7. Потім клацаємо по верхньому осередку стовпця з одиницями виміру, також заходимо у вікно перевірки даних і в джерелі вказуємо формулу “ =ДВССИЛ(A2)де A2 – номер комірки з відповідним продуктом.
   
8. Списки готові. Залишилося тільки розтягнути їх усі рядки таблиці, як стовпця A, так стовпця B.
   

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

І так є кілька варіантів, як зробити списки для роботи в Microsoft Office Excel.

Варіант перший дуже простий. Якщо ви вводите схожі дані в одному стовпці зверху вниз, то вам достатньо лише стати на комірку під даними і натиснути комбінацію клавіш «Alt + стрілка вниз». Перед вами з'явиться список, з якого одним кліком можна вибрати потрібні Вам дані.

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

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

Наступним кроком вибираємо комірку, де буде розміщено наш список, що випадає, і переходимо на вкладку меню «Дані», натискаємо на іконку «Перевірка даних». У вікні нам потрібно вибрати тип даних, які будуть вводитися в нашому осередку. У нашому випадку ми вибираємо «Списки» і нижче через знак рівності прописуємо ім'я нашого діапазону та натиснути ОК. Для того, щоб застосувати список до всіх осередків, достатньо перед включенням перевірки даних просто виділити весь стовпець або потрібну вам область.

Введіть дані в комірки A1:A10, які будуть як джерело для списку. У нашому прикладі ми ввели цифри, вони з'являться у списку, що випадає. Виділіть комірку (Наприклад, E5), яка буде містити в список, що розкривається. Виберіть меню "Дані" -> "Перевірка даних", щоб відкрити діалогове вікно "Перевірка значень, що вводяться".

3. На вкладці «Параметри» виберіть параметр «Список» у розкривному меню. Переконайтеся, що потрібні прапорці.

4. Потім натисніть кнопку. Відобразиться наступне діалогове вікно.

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

6. Випадаючий список в Excel буде створено.

Якщо ваш список короткий, ви можете ввести елементи безпосередньо в «Джерело» на вкладці «Налаштування» у діалоговому вікні «Перевірка значень, що вводяться». Відділіть кожний пункт списку роздільниками, зазначеними в регіональних установках.
Якщо список повинен розташовуватися на іншому аркуші, ви можете скористатися параметром = List перед вказівкою діапазону даних.
Як створити список, що випадає в Екселі на основі даних з переліку

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

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

Перейти на вкладку "Дані" => розділ "Робота з даними" на панелі інструментів => вибираємо пункт "Перевірка даних".

У полі "Джерело" ввести діапазон назв фруктів =$A$2:$A$6 або просто поставити курсор миші в поле введення значень "Джерело" і потім мишкою вибрати діапазон даних:

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

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

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

Для цього нам знадобиться:
Вибрати осередок, в якому ми хочемо створити список, що випадає;
Перейти на вкладку "Дані" => розділ "Робота з даними" на панелі інструментів =>
Перевірка даних у Excel

У спливаючому вікні “Перевірка значень, що вводяться” на вкладці “Параметри” у типі даних вибрати “Список”:
Перевірка значень, що вводяться в Excel

У полі "Джерело" ввести значення "Так; Ні”.
Натискаємо "ОК"
Та ні

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

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

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

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

Для того щоб зробити список, що випадає, за допомогою формули ЗМІЩ необхідно зробити наступне:
Вибрати осередок, в якому ми хочемо створити список, що випадає;
Перейти на вкладку "Дані" => розділ "Робота з даними" на панелі інструментів => вибрати пункт "Перевірка даних":
Перевірка даних у Excel

У спливаючому вікні “Перевірка значень, що вводяться” на вкладці “Параметри” у типі даних вибрати “Список”:
Перевірка значень, що вводяться в 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.

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

виділіть комірки в діапазоні А2: А6, в які ви хочете вставити список, що випадає;

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

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

У вікні в розділі “Вставити” виберіть пункт “умови на значення”:

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

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

Натисніть на вкладку "Головна" на панелі інструментів;
Натисніть “Знайти та виділити” та виберіть “Виділити групу осередків”:

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

Натисніть "ОК"
Натиснувши "ОК", Excel виділить на аркуші всі осередки з списком, що випадає. Так ви зможете привести за все списки до загального формату, виділити межі і.т.д.

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

Іноді нам потрібно створити кілька списків, що випадають, причому, таким чином, щоб, вибираючи значення з першого списку, Excel визначав які дані відобразити в другому списку.
Припустимо, що у нас є списки міст двох країн: Росія та США:

Для створення залежного списку, що випадає, нам знадобиться:
Створити два іменованих діапазони для осередків “A2:A5” з ім'ям “Росія” та для осередків “B2:B5” під назвою “США”. Для цього нам потрібно виділити весь діапазон даних для списків, що випадають:
залежний випадаючий список в Excel

Перейти на вкладку "Формули" => клацнути в розділі "Підкреслені імена" на пункт "Створити з виділеного":
Залежні списки, що випадають в Excel

У спливаючому вікні "Створення імен із виділеного діапазону" поставте галочку в пункт "у рядку вище". Зробивши це, Excel створить два іменовані діапазони "Росія" та "США" зі списками міст:
залежний-випадаючий-список-у-excel

Натисніть "ОК"
У осередку “D2” створіть список, що випадає, для вибору країн “Росія” або “США”. Так, ми створимо перший список, що випадає, в якому користувач зможе вибрати одну з двох країн.

Тепер, для створення залежного списку, що випадає:
Виділіть комірку E2 (або будь-яку іншу комірку, в якій ви хочете зробити залежний список, що випадає);
Клацніть на вкладці "Дані" => "Перевірка даних";
У спливаючому вікні “Перевірка значень, що вводяться” на вкладці “Параметри” у типі даних виберіть “Список”:
Перевірка значень, що вводяться в Excel

Натисніть "ОК"

Тепер, якщо ви оберете в першому випадаючому списку країну "Росія", то в другому списку з'являться тільки ті міста, які відносяться до цієї країни. Також і у випадку, коли вибираєте "США" з першого списку.

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

1. Створити список значень, які надаватимуться на вибір користувачеві (у нашому прикладі це діапазон M1:M3), далі вибрати осередок в якому буде список, що випадає (у нашому прикладі це осередок К1), потім зайти у вкладку " Дані", група" Робота з даними", кнопка " Перевірка даних"

2. Вибираємо " Тип даних" -"перелікі вказуємо діапазон списку

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

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

4. Також необов'язково можна створити повідомлення, яке з'являтиметься при спробі ввести неправильні дані

Якщо Ви не зробите пункти 3 та 4, то перевірка данихпрацюватиме, але при активації осередку не з'являтиметься повідомлення користувачеві про його передбачувані дії, а замість повідомлення про помилку з вашим текстом з'являтиметься стандартне повідомлення.

5. Якщо список значень знаходиться на іншому аркуші, то вищеописаним чином створити список, що випадає, не вийде (до версії Excel 2010). Для цього необхідно буде присвоїти ім'я списку. Це можна зробити кількома способами. Перший: виділіть список і клацніть правою кнопкою миші, у контекстному меню виберіть " Присвоїти ім'я"

Для Excel версій нижче 2007 ті ж дії виглядають так:

Другий: скористайтесь Диспетчером імен(Excel версій вище 2003 - вкладка " Формули" - група " Певні імена"), який у будь-якій версії Excel викликається поєднанням клавіш Ctrl+F3.
Який би спосіб Ви не обрали в результаті Ви повинні будете ввести ім'я (я назвав діапазон зі списком list) та адресу самого діапазону (у нашому прикладі це "2"!$A$1:$A$3)

6. Тепер в осередку зі списком, що випадає, вкажіть в полі "Джерело" ім'я діапазону

7. Готово!

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

Тобто вручну, через ; (крапка з комою) вводимо список у полі " Джерело", в тому порядку в якому ми хочемо його бачити (значення введені зліва-направо відображатимуться в осередку зверху вниз).

При всіх своїх плюсах список, створений вищеописаним чином, має один, але дуже "жирний" мінус: перевірка даних працює тільки при безпосередньому введенні значень з клавіатури. Якщо Ви спробуєте вставити в комірку з перевіркою данихзначення буфера обміну, тобто скопійовані попередньо будь-яким способом, то Вам це вдасться. Більше того, вставлене значення з буфера ВИДАЛИТЬ ПЕРЕВІРКУ ДАНИХ І ВИПАДАЮЧИЙ СПИСОК З осередку, в яку вставили попередньо скопійоване значення. Уникнути цього штатними засобами Excel не можна.

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

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

Крок 1.Перейдіть у вкладку «Дані», яка розташована на верхній панелі, потім у блоці «Робота з даними»оберіть інструмент перевірки даних (на скріншоті показано, якою іконкою він зображений).

Крок 2Тепер відкрийте першу вкладку «Параметри»,та встановіть "Перелік"у переліку типу даних.

Крок 3Тепер у полі введення даних «Джерело»ви можете вказувати значення, які будуть складати список, що розкривається. Зробити це можна такими способами:

На замітку!Щоб не вводити діапазон комірок вручну, достатньо лівою кнопкою мишки виділити першу комірку зі значенням, поставити двокрапку і виділити мишею останню комірку зі значенням.

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

На замітку!Є ще один спосіб вказати значення джерела – написати в поле введення ім'я діапазону. Цей спосіб найшвидший, але перш ніж вдаватися до нього, потрібно створити іменований діапазон. Про те, як це зробити, ми поговоримо пізніше.

Відео — Створення списків, що випадають в Excel

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

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

1. Виділіть лівою кнопкою мишки діапазон для списку (у цьому прикладі це буде перелік дерев), потім відкрийте вкладку «Головна»та виберіть меню "Форматувати як таблицю".

   

2. Після цього з'явиться меню вибору стилів. Стиль ніякої ролі не грає, крім візуальної, тому вибирайте будь-який на ваш смак.

   

3. Далі з'явиться вікно підтвердження, мета якого – переконатися у правильності введеного діапазону. Тут важливо встановити галочку біля «Таблиця із заголовками», оскільки наявність заголовка у разі грає ключову роль.

   

4. Після виконаних процедур ви отримаєте наступний вид діапазону.

   

5. Тепер виділіть лівим кліком миші той осередок, в якому буде розташований список, що випадає, і перейдіть у вкладку «Дані»(У попередньому способі сказано, як це зробити).

   

6. У полі введення «Джерело»вам потрібно вписати функцію із синтаксисом «=ДВССИЛ(“Ім'я таблиці[Заголовок]”)». На скріншоті вказано конкретніший приклад.

   

Отже, список готовий. Виглядатиме він ось так.

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

Давайте протестуємо це. Для початку додамо в нашу нову відформатовану таблицю новий осередок «ялинка». Як бачите, це значення додалося до списку.

Якщо видалити комірку (в даному випадку ми видалили «берези»), це теж відобразиться на змісті списку, що розкривається.

На замітку!У цьому способі ми мали справу з так званою «розумною таблицею». Вона легко розширюється, і ця її властивість корисна для багатьох маніпуляцій з таблицями Excel, у тому числі і для створення списку, що випадає.

Залежні списки, що розкриваються

Щоб створити залежні списки, що випадають, дотримуйтесь подальшої покрокової інструкції:

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

   

   

2. Введіть ім'я та діапазон, а потім натисніть "ОК". Якщо перед цим виділяли потрібні осередки мишкою, то діапазон буде вказано автоматично. Також прийміть до уваги, що ім'я діапазону має бути унікальним і не повинно містити розділових знаків з пробілами.

   

3. За такою ж методикою зробіть стільки іменованих діапазонів, скільки логічних залежностей хочете створити. У цьому прикладі це ще два діапазони: «Чарівники»і «Трави».

   

4. Відкрийте вкладку «Дані»(у першому способі вказано шлях до неї) та вкажіть у джерелі назви іменованих діапазонів, як це показано на скріншоті.

   

   

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

   

   

Тепер вам відомі кілька способів, як створити меню, що випадає в . Це може стати вам у нагоді в ряді випадків, а особливо - у перевірці даних. Вибір правильного способу має здійснюватися залежно від цього, з яким типом таблиці ви працюєте. Якщо це "одноразова" таблиця, то підійде перший спосіб - він швидкий і легкий. Якщо ж таблиця вимагає постійних змін, то найкраще буде скористатися тим способом, який включає створення «розумних таблиць» і іменованих діапазонів.

Відео — Пов'язані списки: легко і швидко

ПРЕДСТАВТЕ СИТУАЦІЮ:Ми хочемо створити в Excel невелику табличку, де можна вибрати країну та відповідне їй місто. При цьому за допомогою списків, що випадають, необхідно обмежити доступні користувачам варіанти країн і міст, з яких вони можуть вибирати. У першому осередку ми зробимо вибір країни, а в другому будуть доступні тільки міста, що належать обраній країні. Думаю це зрозуміло?

Отже, давайте почнемо наш простий приклад з того, як можна створити пов'язаний (або залежний) список, що випадає в Excel? У осередку B1ми обиратимемо країну, а в осередку B2- Належить їй місто, як на прикладі:

Для початку необхідно створити базу даних. На другому аркуші я заніс список країн, які хочу дати користувачам на вибір у першому списку, що розкривається, а в сусідньому стовпці вказав числовий індекс, який відповідає одному зі списків міст. Списки міст розташовуються правіше в стовпцях D, Fі H. Так, наприклад, поряд з Франціякоштує індекс 2 , який відповідає списку міст 2 . Згодом Ви побачите, як цей індекс буде використаний.

Якщо Ви працюєте в Excel 2010, можете створити лист-джерело в окремій робочій книзі. Якщо ж у Вас версія Excel 2003 року і Ви плануєте використовувати іменований діапазон, то значення повинні знаходитися в тій же книзі, можна на іншому аркуші.

Ми будемо використовувати іменовані діапазони і зробимо так, щоб ці пов'язані списки, що випадають, працювали у всіх версіях Excel. Наступний крок – створити іменовані діапазони наших списків. На вкладці Formulas(Формули) є команда Name Manager(Диспетчер імен). Натиснувши на неї, відкриється діалогове вікно Name Manager(Диспетчер імен).

Натисніть кнопку New(Створити), щоб додати новий іменований діапазон. Відкриється діалогове вікно New Name(Створення імені).

В полі Name(Ім'я) введіть ім'я Countryдля нашого першого іменованого діапазону, а в полі Refers to(Діапазон) виберіть той, де зберігається список країн:

Sheet3!$A$3:$A$5

Імена діапазонів, що містять міста, можна присвоїти точно так само.

Тепер ми можемо створити списки, що випадають, у тих осередках, де планували вибирати дані. Виділіть комірку B1(у ній ми обиратимемо країну), відкрийте вкладку Data(Дані), натисніть Data Validation(Перевірка даних), а потім у випадаючому меню виберіть Data Validation(Перевірка даних).

Відкриється діалогове вікно Data Validation(Перевірка значень, що вводяться).

Ми хочемо надати користувачеві на вибір список варіантів, тому в полі Allow(Тип даних) виберіть List(Перелік). Це активує поле Source(Джерело), ​​де необхідно вказати ім'я діапазону з країнами. Введіть у цьому полі “=Country” та натисніть ОК. Тепер нам потрібно зробити другий список, що розкривається, щоб користувачі могли вибрати місто. Ми помістимо цей список, що розкривається, в осередок B2. А тепер увага – фокус! Нам потрібно перевірити вміст осередку під назвою країни (осередок B1), щоб отримати індекс відповідний базі даних з містами. Якщо користувач вибере Португалія, то ми повинні звернутися до бази з індексом 3 , у якій зберігаються назви міст Португалії. Ми скористаємось функцією ВВР(VLOOKUP) для пошуку значення з комірки B1у таблиці із назвами країн. Після того, як індекс буде відомий, ми оберемо список, який стане джерелом даних для нашого другого списку. Для цього напишемо таку формулу:

CHOOSE(VLOOKUP(B1,Sheet3!$A$3:$B$5,2,FALSE),England,France,Portugal)
=ВИБІР(ВПР(B1;Sheet3!$A$3:$B$5;2;БРЕХНЯ);England;France;Portugal)

Що робить ця формула? Вона шукає значення з комірки B1у списку країн та повертає відповідний індекс, який потім використовує функцію CHOOSE(ВИБІР), щоб вибрати 1-й, 2-й або 3-й іменований діапазон.

Ось так буде виглядати наш другий список, що розкривається: