Створення меню в excel. Створення списку в Excel

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

Спосіб 1 - гарячі клавіші і список, що розкривається в excel

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

Цей пункт меню можна запустити поєднанням клавіш Alt+"Стрілка вниз"і програма автоматично запропонує у списку значення комірок, які ви раніше заповнювали даними. На зображенні нижче програма запропонувала 4 варіанти заповнення (дані Excel не показує). Єдина умова роботи даного інструменту – це між осередком, в який ви вводите дані зі списку і самим списком не повинно бути порожніх осередків.

Використання гарячих клавіш для розкриття списку даних, що випадає

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

Випадаючий список може працювати і у верхній частині з даними, які нижче за комірку

Спосіб 2 - найзручніший, найпростіший і найбільш гнучкий

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

Щоб створити перевірку введених значень, введіть ім'я раніше створеного списку

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

Крім списку, можна вводити дані вручну. Якщо введені дані не співпадуть з одним із даних – програма видасть помилку

А при натисканні на кнопку випадаючого списку в комірці ви побачите перелік значень із створеного раніше.

Спосіб 3 - як у excel зробити список, що випадає з використанням ActiveX

Щоб скористатися цим способом, необхідно, щоб у вас була включена вкладка «Розробник». За промовчанням ця вкладка відсутня. Щоб її увімкнути:

  1. Натисніть «Файл» у верхньому лівому куті програми.
  2. Виберіть «Параметри» та натисніть на нього.
  3. У вікні параметрів Excel у вкладці «Налаштувати стрічку» поставте галочку навпроти вкладки «Розробник».

Увімкнення вкладки «РОЗРОБНИК»

Тепер ви зможете скористатися інструментом "Поле зі списком (Елемент ActiveX)". У вкладці «Розробник» натисніть кнопку «Вставити» і знайдіть в елементах ActiveX кнопку «Поле зі списком (Елемент ActiveX)». Натисніть на неї.

Намалюйте даний об'єкт в excel список, що випадає в осередку, де вам необхідний список, що випадає.

Тепер потрібно налаштувати цей елемент. Щоб це зробити, необхідно увімкнути «Режим конструктора» та натиснути на кнопку «Властивості». У вас має відкритися вікно властивостей (Properties).

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

Але нас на етапі створення цікавлять лише три основні:

  1. ListFillRange - вказує діапазон осередків, з яких будуть братися значення для списку, що випадає. У моєму прикладі я вказав два стовпці (A2:B7 - далі покажу, як це використовувати). Якщо потрібно лише одне значення вказується A2:A7.
  2. ListRows - кількість даних у списку, що випадає. Елемент ActiveX відрізняється від першого способу тим, що можна вказати велику кількість даних.
  3. ColumnCount - вказує скільки стовпців даних вказувати у списку, що випадає.

У рядку ColumnCount я вказав значення 2 і тепер у списку випадають дані виглядають ось так:

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

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

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

Створюємо простий список, що випадає

Для цього в осередки А1:А7 вписуємо дані, які будуть відображатися у списку. Тепер виділимо комірку, в якій створимо список, що випадає – В2.

Переходимо на вкладку «Дані» та клацаємо по кнопці «Перевірка даних».

На вкладці "Параметри" у полі "Тип даних" вибираємо "Список". У полі «Джерело» можна ввести значення різними способами:

1 - вводимо значення для списку вручну, через точку з комою;

2 – вказуємо діапазон осередків, в які введені дані для списку, що випадає;

3 - виділяємо комірки з іменами, клацаємо по них правою кнопкою миші та вибираємо з меню "Присвоїти ім'я".

Виділяємо комірку В2 і в полі «Джерело» ставимо «=», потім пишемо створене ім'я.

Таким чином, ми створили простий список, що випадає в Excel.

Якщо у Вас є заголовок для стовпця, і значеннями потрібно заповнювати кожен рядок, то виділіть не один осередок, а діапазон осередків - В2: В9. Тоді можна буде вибирати зі списку потрібне значення в кожному осередку.

Додаємо значення у список, що випадає – динамічний список

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

Виділяємо діапазон осередків – D1:D8, потім на вкладці «Головна» натискаємо «Форматувати як таблицю» та вибираємо будь-який стиль.

Підтверджуємо розташування даних і ставимо галочку у полі «Таблиця із заголовками».

Вгорі пишемо заголовок таблиці - "Співробітники", і заповнюємо її даними.

Виділяємо комірку, в якій буде список, що випадає, і клацаємо по кнопочці «Перевірка даних». У наступному вікні, у полі «Джерело», пишемо наступне: =ДВССИЛ(«Таблица1»). У мене одна таблиця на аркуші, тому пишу "Таблиця1", якщо буде друга - "Таблиця2", і так далі.

Тепер додамо нове ім'я співробітника до нашого списку: Іра. У списку, що випадає, воно з'явилося. Якщо ми видалимо будь-яке ім'я з таблиці, зі списку воно також вилучиться.

Список, що випадає зі значеннями з іншого аркуша

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

На Листі 2, виділяємо одну комірку або діапазон комірок, потім клацаємо по кнопці «Перевірка даних».

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

Тепер можна дописувати імена на аркуші 1, вони будуть додаватися в списки, що випадають на аркуші 2.

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

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

Перший називаємо «Ім'я», другий – «Прізвище», третій – «Отч».

Зробимо ще один діапазон, у якому будуть прописані надані імена. Назвемо його "Співробітники".

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

У полі "Тип даних" виберіть "Список", у полі джерело - або введіть "=Співробітники", або виділіть діапазон комірок, якому присвоєно ім'я.

Перший список, що випадає, створений. Тепер у осередку F2 створимо другий список, який має залежати від першого. Якщо у першому виберемо "Ім'я", у другому з'явиться список імен, якщо виберемо "Прізвище" - список прізвищ.

Виділяємо комірку та клацаємо по кнопочці «Перевірка даних». У полі «Тип даних» вибираємо «Список», у полі джерело прописуємо наступне: =ДВССИЛ($Е$1). Тут Е1 - це осередок з першим списком, що випадає.

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

Якщо надалі, потрібно буде вписати значення діапазон, якому задано ім'я, наприклад, «Прізвище». Перейдіть на вкладку «Формули» та клацніть «Диспетчер імен». Тепер в імені діапазону вибираємо «Прізвище», і внизу замість останнього осередку С3 напишіть С10. Натисніть галочку. Після цього діапазон збільшиться, і в нього можна буде дописувати дані, які автоматично з'являтимуться у списку, що випадає.

Тепер Ви знаєте, як зробити список, що розкривається в 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 визначав які дані відобразити в другому списку.

Припустимо, що у нас є списки міст двох країн: Росія та США:

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

  • Створити два іменовані діапазони для осередків “ A2:A5” з ім'ям “Росія” та для осередків “ B2:B5” з назвою “США”. Для цього нам потрібно виділити весь діапазон даних для списків, що випадають:
  • Перейти на вкладку “ Формули” => клікнути у розділі “ Певні імена” на пункт “ Створити із виділеного“:
  • У спливаючому вікні “ Створення імен із виділеного діапазону” поставте галочку в пункт “ у рядку вище“. Зробивши це, Excel створить два іменовані діапазони "Росія" та "США" зі списками міст:
  • Натисніть “ ОК“
  • У осередку “ D2” створіть список, що випадає, для вибору країн “Росія” або “США”. Так, ми створимо перший список, що випадає, в якому користувач зможе вибрати одну з двох країн.

Тепер, для створення залежного списку, що випадає:

  • Виділіть комірку E2(або будь-який інший осередок, в якому ви хочете зробити залежний список, що випадає);
  • Клацніть по вкладці “ Дані” => “Перевірка даних”;
  • У спливаючому вікні “ Перевірка значень, що вводяться” на вкладці “ Параметри” у типі даних виберіть “ перелік“:
  • У розділі "Джерело" вкажіть посилання: =INDIRECT($D$2)або =ДВССИЛ($D$2);
  • Натисніть “ ОК“

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

Option Explicit Option Compare Text Dim Bu As Boolean Private Sub Worksheet_SelectionChange(ByVal Target As Range) If Target.CountLarge > 1 Then Exit Sub If Target.Row = 1 Then Me.TextBox1.Visible = False: Me.ListBox1.Visible = False: Exit Sub If Target.Column = 3 Then " номер стовпця, який вносимо значення bu = True With Me.TextBox1 .Top = Target.Top: .Text = Target.Value: .Activate End With Me.ListBox1 .Top = Target .Top + 5 If (.Top + .Height + ActiveWindow.PointsToScreenPixelsY(0) * Application.InchesToPoints(1) * 15 / 1440) > _ (ActiveWindow.Application.Height + ActiveWindow.Application.Top) Then _ .Top = .Top - .Height + Target.Height "* ActiveWindow.Zoom / 100 .Clear End With bu = False Me.TextBox1.Visible = True: Me.ListBox1.Visible = True Else Me.TextBox1.Visible = False: Me.ListBox1 .Visible = False End If End Sub Private Sub TextBox1_Change() If Len(TextBox1.Text) = 0 Or bu Then Exit Sub "при відсутності символів для пошуку - вихід Dim x, i As Long, txt As String, lt As Long, s As String txt = TextBox1.Text: lt = Len(TextBox1.Text) "Де шукаємо значення x = Sheets("номенклатура";).Columns(1).SpecialCells(2).Offset(1).Value "(! LANG: For i = 1 To UBound(x, 1)" поиск по первым буквам "If txt = Mid(x(i, 1), 1, lt) Then s = s & x(i, 1) & "~" For i = 1 To UBound(x, 1) "поиск по любому вхождению If InStr(x(i, 1), txt) Then s = s & "~" & x(i, 1) Next i ListBox1.List = Split(s, "~";) End Sub Private Sub TextBox1_KeyDown(ByVal KeyCode As MSForms.ReturnInteger, ByVal Shift As Integer) If KeyCode = 13 Or KeyCode = 9 Then With Me.TextBox1 ActiveCell.Value = .Value .Visible = False: ListBox1.Visible = False End With ActiveCell(2, 1).Select End If End Sub Private Sub ListBox1_Click() If ListBox1.ListIndex = -1 Then Exit Sub Application.EnableEvents = False bu = True With Me.ListBox1 ActiveCell.Value = .Value Me.TextBox1.Text = .Value Me.TextBox1.Visible = False: .Visible = False End With Application.EnableEvents = True bu = False End Sub Private Sub Worksheet_Change(ByVal Target As Range) Dim lReply As Long If Target.Column = 2 Then Exit Sub If Not Intersect(Target, Range("C2:C100000";)) Is Nothing Then If IsEmpty(Target) Then Exit Sub If WorksheetFunction.CountIf(Sheets("номенклатура";).Columns(1), Target) = 0 Then lReply = MsgBox("Добавить введенное имя " & Target & " в выпадающий список", vbYesNo + vbQuestion) If lReply = vbYes Then Worksheets("номенклатура";).Range("номенклатура";).Cells(Worksheets("номенклатура";).Range("номенклатура";).Rows.Count + 1, 1) = Target End If End If End If Sheets("номенклатура";).Range("номенклатура";).Sort Key1:=Sheets("номенклатура";).Range("A1";), Order1:=xlAscending, Header:=xlGuess, _ OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _ DataOption1:=xlSortNormal "этот код и поможет отсортировать в алфавитном порядке" End Sub !}

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

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

Другий спосібстворення списку, що випадає, в Excel більш витончений і універсальний. Виділяєте діапазон даних для списку, потім натискаєте на пункт меню Формула - Диспетчер імен - Створити . Заповнюєте поле Ім'я, і копіюєте його (воно Вам знадобиться пізніше). Ім'я повинно починатися з літери або символу підкреслення і не повинно містити пробілів. Натискаєте ОК. Закриваєте вікно.

Потім вибираєте комірку, в якій буде список Excel, що випадає (можна відразу виділити кілька осередків, якщо в них будуть однакові списки, що випадають). Після цього вибираєте пункт меню Дані — Перевірка даних потім у віконці Тип данихвиберіть рядок перелік, в полі Джерелопоставте знак рівно, і без пропуску вставте те, що Ви копіювали (значення поля Ім'я). Не забудьте про знак = , інакше нічого не вийде. Виглядає напис у полі Джерелоприблизно так: =Ім'я_діапазону . Натискаєте ОК .

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

Це може бути опитування, тест, або анкета із заздалегідь прописаними варіантами відповідей. Можна, наприклад, роздати файл Excel з анкетою групі людей, яких Ви хочете опитати, і отримати назад файли з відповідями, або роздруковані відповіді.

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

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

Щоб видалити з комірки список, що випадає, виділяєте комірку, вибираєте в меню Дані — Перевірка даних , та натискаєте на кнопку Очистити все .

При створенні таблиць часом дуже зручно використовувати списки, що випадають (інакше кажучи — розкриваються). Excel 2010 дозволяє це робити кількома способами. Розглянемо їх.

Спосіб 1. Створюємо список, що розкривається, в Excel 2010, використовуючи інструмент «Перевірка даних»

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

1. На вільному місці аркуша запишіть всі елементи списку, що розкривається, у стовпчик, кожен елемент — у своєму осередку.

2. Вкажіть ім'я діапазону комірок. Для цього:

  • клацніть верхню комірку списку і, затиснувши ліву кнопку миші, тягніть курсор вниз, поки весь список не буде виділено.
  • встановіть курсор у полі «Ім'я», ліворуч від рядка формул;
  • введіть ім'я списку та натисніть Enter.

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

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

4. Відкрийте вкладку «Дані» та натисніть кнопку «Перевірка даних». У вікні, перейдіть на вкладку «Параметри». У списку «Тип даних» виберіть «Список».

5. У рядку «Джерело» потрібно вказати адресу, звідки буде взято елементи створюваного списку. Адресою буде ім'я, яке ви надали діапазону осередків. Задати адресу можна кількома способами.

  • Вписати вручну, поставивши перед ним знак "рівно", наприклад, "=місяця". Реєстр літер не має значення.
  • Клацнувши мишею в рядку «Джерело» (для активації), виділити курсором усі елементи списку в таблиці.

6. Якщо потрібно створити повідомлення для введення, відкрийте однойменну вкладку. Напишіть текст, який з'являтиметься поруч із осередком з розкривним списком при її виділенні. На сусідній вкладці — «Повідомлення про помилку», так само можна написати текст, що повідомляє про помилки.

7. Підтвердьте введення натисканням «ОК», і список готовий. Для його відкриття натисніть кнопку зі стрілкою вниз, яка з'явиться поряд із осередком, що містить список.

Спосіб 2. Швидке створення списку, що випадає

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

1. Перерахуйте у стовпчик всі елементи майбутнього списку.

2. Виділіть комірку, що знаходиться під останнім елементом і натисніть клавіші Alt + стрілка вниз — список буде створено. Цей метод дозволить задати комірці значення одного з елементів.

Спосіб 3. Створення списку, що випадає, як елемента управління

Для застосування цього методу увімкніть відображення вкладки «Розробник»: відкрийте меню «Файл» – «Параметри» – «Налаштування стрічки». У стовпці «Основні вкладки» позначте галочкою пункт «Розробник». Підтвердьте дію натисканням «ОК» — вкладка буде створена.

1. Перерахуйте елементи майбутнього списку до стовпчика.

2. З меню «Вставити» вкладки «Розробник» оберіть «Елементи керування форми» — «Поле зі списком».

3. Намалюйте на аркуші курсором ваш майбутній список, що розкривається. Клацніть правою кнопкою миші по ньому і виберіть з меню пункт «Формат об'єкта».

4. Значення поля «Формувати список по діапазону» має бути список елементів — виділіть його курсором, і поле буде заповнено автоматично. У полі «Зв'язок із осередком» вказується адреса осередку, де буде показано порядковий номер виділеного елемента. Виберіть комірку та клацніть по ній. Поле "Кількість рядків списку" дозволяє налаштувати, скільки елементів буде відображено під час розкриття списку.

5. Підтвердьте введення та натисніть «OK». Список буде створено.

Спосіб 4. Створення списку, що випадає, як елемента ActiveX

Найскладніший метод, але має максимально гнучкі налаштування.

1. Створіть список описаним вище способом.

2. З меню «Вставити» вкладки «Розробник» оберіть «Елементи ActiveX» — «Поле зі списком».

3. Намалюйте на аркуші майбутній список, що розкривається.

4. Опція, яка дозволяє редагувати список, що випадає, називається «Режим конструктора». Якщо цей режим активний — буде виділено однойменну кнопку в розділі «Елементи керування», поруч із кнопкою «Вставити». Якщо кнопка не виділена, режим редагування вимкнено.

5. Щоб встановити параметри списку, натисніть кнопку «Властивості» того ж розділу. Відкриється вікно «Properties». Обидві вкладки цього вікна містять одні й самі параметри, розсортовані у першому випадку — за алфавітом, у другому — за категоріями.

6. Більшість налаштувань можна залишити за замовчуванням, а найнеобхідніше наведено нижче.

  • ListRows - аналог значення "Кількість рядків у списку", покаже, скільки рядків буде відображатися.
  • Font — Налаштування шрифту. Дозволяє вибрати шрифт та його зображення.
  • ForeColor — Виберіть із таблиці кольору шрифту.
  • BackColor – колір заднього фону.
  • ListFillRange — розташування списку елементів у форматі: лист («!» — роздільник) та діапазон осередків. Наприклад: Лист2! D2: D6. Прописується вручну.
  • LinkedCell - зв'язок з осередком. Вручну вказується адреса осередку, де показуватиметься порядковий номер виділеного елемента списку.

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

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

Спосіб 1: створюємо додатковий список

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

Крок 1: готуємо дані

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

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

Крок 2: вводимо ім'я діапазону

Щоб використовувати опцію вибору зі списку Excel, попередньо потрібно ввести ім'я діапазону з даними для майбутнього списку. Робиться це досить просто:

  1. Виділіть осередки, у яких перебувають у разі найменування товарів.
  2. Натисніть правою кнопкою миші (ПКМ) для виділення.
  3. Виберіть із меню опцію «Присвоїти ім'я».
  4. У вікні «Ім'я» введіть назву діапазону. Воно може бути абсолютно будь-яким.
  5. Натисніть "OK".

Другий крок виконано. Створений нами щойно діапазон осередків полегшить створення списку у майбутньому.

Крок 3: робимо список, що випадає

Тепер можна переходити безпосередньо до використання опції вибору зі списку Excel. Робиться це так:

  1. Виділіть потрібний діапазон осередків, в якому будуть розташовуватися списки, що випадають.
  2. Перейдіть на вкладку "Дані".
  3. У групі інструментів «Робота з даними» натисніть на кнопку «Перевірка даних».
  4. У вікні на вкладці «Параметри» виберіть зі списку «Тип даних» значення «Список».
  5. Введіть у полі «Джерело» назву раніше створеного діапазону комірок, попередньо поставивши знак рівності. У нашому випадку - "= Продукти".
  6. Натисніть "OK".

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

Спосіб 2: створення списку, що випадає, через меню «Розробника»

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

Як створити список, що випадає в осередку аркуша за допомогою меню «Розробника»? Як і попереднього разу, для кращого розуміння всі дії будуть поділені на етапи.

Крок 1: включаємо меню «Розробника»

Отже, в першу чергу необхідно активувати меню «Розробника», оскільки за замовчуванням немає серед інших вкладок.

  1. Натисніть кнопку «Файл».
  2. Натисніть на кнопку «Параметри».
  3. У однойменному вікні, що з'явилося, перейдіть в розділ «Налаштування стрічки».
  4. В області «Основні вкладки» встановіть позначку навпроти пункту «Розробник».
  5. Натисніть "OK".

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

Крок 2: вставляємо список, що випадає

Потрібно створити безпосередньо сам елемент «Випадаючий список». Для цього:

  1. Перейдіть на вкладку «Розробник».
  2. На аркуші створіть список товарів, який буде використовуватися для створення списку, що випадає.
  3. Натисніть за кнопкою «Вставити» і в додатковому меню виберіть «Поле зі списком».
  4. Клікніть по тій комірці, де розташовуватиметься сам список.

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

Крок 3: задаємо необхідні параметри

Щоб додати до списку пункти, необхідно:

  1. На панелі інструментів натиснути кнопку «Режим конструктора».
  2. Потім натиснути кнопку «Властивості елемента управління», що знаходиться поруч.
  3. У вікні з властивостями в графі ListFillRange введіть діапазон осередків, в якому знаходяться пункти майбутнього випадаючого списку.
  4. Тепер натисніть ПКМ за списком, що випадає, і в меню виберіть «Об'єкт ComboBox», а в підменю Edit.

Відразу після цього до списку, що випадає, будуть внесені зазначені пункти. Ось так просто можна виконати вибір зі списку Excel другим способом.

Спосіб 3: створення пов'язаного списку

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

Крок 1: створюємо додатковий перелік

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

Крок 2: Зв'язуємо перший список із другим

Ну а тепер перейдемо безпосередньо до основного – до створення другого елементу «Вибору зі списку» в Excel, який буде пов'язаний із першим.

  1. Встановіть курсор у тій комірці, де розташовуватиметься другий список.
  2. Відкрийте вікно «Перевірка значень», натиснувши на вкладці «Дані» за кнопкою «Перевірка даних».
  3. У вікні на вкладці «Параметри» виберіть зі списку «Тип даних» пункт «Список».
  4. У полі для введення «Джерело» введіть формулу «ДВССИЛ», яка посилається на перший список. В даному випадку вона виглядатиме таким чином: «=ДВССИЛ($B3)».
  5. Натисніть "OK".

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

Висновок

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



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