Вибір кілька значень excel з списку, що розкривається. Створення списку, що випадає в Excel. Найшвидший спосіб

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

Припустимо, коли я створював таблицю обліку комп'ютерного обладнання (ще давно) у себе на роботі, то щоб зробити весь процес роботи більш зручним і швидким, я робив список, що випадає, у певних стовпцях і вставляв туди певні значення. І коли я заповнював стовпець. Операційна система» (А адже не на всіх комп'ютерах вона одна і та ж), то я забивав кілька значень (7, 8, 8.1, 10), а потім просто вибирав це все одним натисканням кнопки миші.

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

Підготовка

Основні дії

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

До речі в таких документах для зручнішого відображення краще. Тоді буде взагалі все круто.

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

З повагою, Дмитро Костін

ПРЕДСТАВТЕ СИТУАЦІЮ:Ми хочемо створити в 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-й іменований діапазон.

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Висновок

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

Найпростіше це завдання виконати так. Натисканням правою кнопкою по комірці під стовпцем з даними викликаємо контекстне меню. Тут цікавить поле Вибрати зі списку. Те саме можна зробити, натиснувши комбінацію клавіш Alt+Стрілка вниз.

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

Стандартний спосіб

Потрібно виділити діапазон осередків, з яких буде створено випадаючий список, після чого Вставка – Ім'я – Присвоїти(Excel 2003). У більш нової версії(2007, 2010, 2013, 2016) перейдіть на вкладку Формули, де у розділі Певні іменавиявіть кнопку Диспетчер імен.

Тиснемо кнопку Створити, вносите ім'я, можна будь-яке, після чого ОК.

Виділіть комірки(або кілька), де хочете вставити перелік потрібних полів. У меню виберіть Дані – Тип даних – перелік. В полі Джереловнесіть раніше створене ім'я або можна просто вказати діапазон, що буде рівносильно.

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

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

Використовуємо елементи керування

Метод заснований на вставціелемента управління, званому « поле зі списком«, Яке буде діапазон даних.

Виберіть вкладку Розробник(для Excel 2007/2010), в інших версіях потрібно активувати цю вкладку на стрічці в параметрах – Налаштувати стрічку.

Переходимо на цю вкладку – тиснемо кнопку Вставити. В елементах управління вибираємо Поле зі списком(не ActiveX) і натисніть . Намалюйте прямокутник.

Правою кнопкою по ньому – Формат об'єкту.

Зв'язком з коміркою виберіть поле, куди слід помістити порядковий номер елемента у списку. Потім тиснемо ОК.

Застосування елементів ActiveX

Все, як і в попередньому, тільки вибираємо Поле зі списком(ActiveX).

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

Відео

таблиці Excel. Якщо це не так, список можна швидко перетворити на таблицю, виділивши будь-яку комірку діапазону і натиснувши клавіші CTRL+T.

Примітки:

Завантаження прикладів

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

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

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

Див. також