Створюємо пов’язані списки, що випадають в Excel – найпростіший спосіб!

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

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

Для початку потрібно створити базу даних. На другому аркуші я заніс список країн, які хочу дати користувачам на вибір в першому списку, а в сусідньому стовпці вказав числовий індекс, який відповідає одному зі списків міст. Списки міст розташовуються правіше в шпальтах D, F і H. Так, наприклад, поруч з France варто індекс 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), щоб отримати індекс відповідний базі даних з містами. Якщо користувач вибере Portugal, то ми повинні звернутися до бази з індексом 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-й іменований діапазон.

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

В результаті ми отримаємо два пов’язаних (або залежних) випадають списку. Якщо ми вибираємо країну France, в пов’язаному списку у нас будуть міста тільки з Франції.

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

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