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

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

Створити багаторівневий залежний список, що випадає в Excel

Щоб створити багаторівневий залежний список, що розкривається, виконайте такі дії:

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

1. Спочатку створіть дані першого, другого і третього списку, що розкривається, як показано нижче:

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

2. Потім виберіть значення першого списку (виключаючи комірку заголовка), а потім дайте ім ім'я діапазону в полі Поле імені які, крім рядка формул, див. знімок екрану:

3. Потім виберіть дані другого списку, що розкривається, і натисніть Формули > Створити з вибраного, Див. знімок екрану:

4. У вискочившому Створити імена з вибору діалогове вікно, позначте тільки Верхній ряд варіант, див. знімок екрану:

5. Натисніть OK, І імена діапазонів були створені для кожного другого розкривного списку відразу, потім ви повинні створити імена діапазонів для значень третього розкривного списку, продовжуйте натискати Формули > Створити з вибраного, В Створити імена із виділеного діалогове вікно, позначте тільки Верхній ряд варіант, див. знімок екрану:

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

• Поради: Ви можете піти Менеджер імен діалогове вікно, щоб побачити всі створені імена діапазонів, які були розташовані в Менеджер імен діалогове вікно, як показано на скріншоті нижче:

По-третє, створіть список Data Validation, що випадає.

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

8. У перевірка достовірності даних діалогове вікно під Налаштування , виберіть Список з Дозволити список, що розкривається, а потім введіть цю формулу: = Континенти в Джерело текстове поле, див. знімок екрану:

Увага: У цій формулі Континентів - Ім'я діапазону перших значень, що розкриваються, створених на кроці 2, змініть його на свій розсуд.

9, Потім натисніть OK Кнопка, перший розкривний список був створений, як показано нижче:

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

• (1.) Виберіть Список з Дозволити список, що розкривається;
• (2.) Потім введіть цю формулу: = Непрямо (ПІДСТАВИТИ (I2; ""; "_")) в Джерело Текстове вікно.

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

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

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

• (1.) Виберіть Список з Дозволити список, що розкривається;
• (2.) Потім введіть цю формулу: = Непрямо (ПІДСТАВИТИ (J2; ""; "_")) у текстовому полі Джерело.

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

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

Створюйте багаторівневий залежний список, що випадає в Excel з дивовижною функцією

Можливо, описаний вище метод є проблемним для більшості користувачів, тут я представлю просту функцію.Динамічний список, що розкривається of Kutools for Excel, за допомогою цієї утиліти ви можете швидко створити залежний список з 2-5 рівнями всього за кілька кліків.

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

1. Спочатку слід створити дані у форматі, показаному на знімку екрана нижче:

2, Потім натисніть Кутулс > Список, що розкривається > Динамічний список, що розкривається, Див. знімок екрану:

3. У Залежний список, що розкривається діалоговому вікні виконайте такі дії:

• Перевірити Список, що розкривається, залежить від 3-5 рівнів варіант у Тип розділ;
• Вкажіть необхідний діапазон даних та вихідний діапазон.

4, Потім натисніть Ok Кнопка, тепер трирівневий список, що розкривається, був створений у вигляді наступної демонстрації:

Kutools for Excel - Зміцніть Excel більш ніж 300 необхідними інструментами. Насолоджуйтесь постійно безкоштовними функціями ІІ! Get It Now

Більш відносні статті зі списком:

• Автоматичне заповнення інших осередків при виборі значень у списку Excel, що розкривається.
• Припустимо, ви створили список, що розкривається, на основі значень в діапазоні осередків B8: B14. Коли ви вибираєте будь-яке значення в списку, ви хочете, щоб відповідні значення в діапазоні осередків C8: C14 автоматично заповнювалися у вибраному осередку. Наприклад, коли ви вибираєте Люсі в списку, вона автоматично заповнює рахунок 88 в осередку D16.

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

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

• Вибрати кілька елементів з списку, що розкривається, в комірку в Excel
• Список, що випадає, часто використовується в повсякденній роботі Excel. За замовчуванням у списку, що розкривається, можна вибрати тільки один елемент. Але в деяких випадках вам може знадобитися вибрати кілька елементів з списку, що розкривається, в одну комірку, як показано нижче. Як із цим впоратися в Excel?

• Створити список, що розкривається, з гіперпосиланнями в Excel
• У Excel додавання розкривного списку може допомогти нам вирішити нашу роботу ефективно і легко, але, якщо ви коли-небудь намагалися створити розкривний список з гіперпосиланнями, коли ви вибираєте URL-адресу з розкривного списку, буде відкриватися гіперпосилання автоматично? У цій статті я розповім про те, як створити список, що випадає з активованими гіперпосиланнями в Excel.

Найкращі інструменти для офісної роботи

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

Вкладка Office: інтерфейс із вкладками в Office та спрощення роботи

• Увімкнення редагування та читання з вкладками Word, Excel, PowerPoint , Видавець, доступ, Visio та проект.
• Відкривайте та створюйте кілька документів на нових вкладках одного вікна, а не у нових вікнах.
• Підвищує вашу продуктивність на 50% та скорочує кількість клацань мишею на сотні щодня!

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

Більшість з нас може створити список, що розкривається за допомогою функції перевірки даних в Excel, але іноді нам потрібен пов'язаний або динамічний список, що розкривається, це означає, що коли ви вибираєте значення в розкривному списку A і хочете, щоб значення, які потрібно оновити, в розкривному списку B. В Excel ми можемо створити динамічний список, що розкривається перевірка достовірності даних функції та НЕДІЛЬНІ функція. У цьому посібнику буде описано, як створювати залежні списки, що розкриваються в Excel.

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

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

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

1. По-перше, мені потрібно створити кілька імен діапазонів для цих стовпців та першого рядка категорій.

(1.) Створіть ім'я діапазону для категорій, перший рядок, виберіть A1: D1 та введіть ім'я діапазону. Харчовий продукт в Ім'я Box, Потім натисніть Enter .

(2.) Потім вам потрібно назвати діапазон для кожного зі стовпців, як зазначено вище, як показано нижче:

Порада — Панель навігації: пакетне створення декількох іменованих діапазонів та виведення списку на панелі Excel
Зазвичай ми можемо визначити лише один діапазон імен одночасно у Excel.Але в деяких випадках може знадобитися створити кілька іменованих діапазонів. Має бути досить втомливо постійно визначати імена одне одним. Kutools for Excel надає таку утиліту для швидкого пакетного створення декількох іменованих діапазонів та перерахування цих іменованих діапазонів у Область переходів для зручного перегляду та доступу.

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

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

Увага: Вам потрібно ввести у формулу те, що ви назвали своїми категоріями

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

5. Потім я можу створити другий список, що розкривається, вибрати одну порожню комірку і клацнути Дані > перевірка достовірності даних > перевірка достовірності даних знову ж, у перевірка достовірності даних діалогове вікно, натисніть Налаштування , виберіть Список з Дозволити список, що розкривається, і введіть цю формулу = непрямий (F1) в Джерело box, див. знімок екрана:

Увага: F1 вказує розташування осередку для першого списку, що я створив, ви можете змінити його на свій розсуд.

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

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

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

Демонстрація: створення динамічного списку, що розкривається в Excel

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

Припустимо, у вас є таблиця даних у RangeB2: E8, і ви хочете створити незалежні розкривні списки на основі таблиці даних у Range G2: H8. Тепер ви можете легко це зробити за допомогою Динамічний список, що розкривається особливість Kutools for Excel.

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

1. Натисніть Кутулс > Список, що розкривається > Динамічний список, що розкривається для увімкнення цієї функції.

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

(1) Позначте Список, що розкривається, залежить від рівня варіант;
(2) У полі «Діапазон даних» виберіть таблицю даних, на основі якої ви створюватимете незалежні списки, що розкриваються;
(3) У полі «Діапазон виводу» виберіть діапазон призначення, до якого будуть розміщені незалежні списки, що розкриваються.

3, Натисніть Ok .

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

Статті на тему:

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

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

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

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

Найкращі інструменти для офісної роботи

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

Вкладка Office: інтерфейс із вкладками в Office та спрощення роботи

• Увімкнення редагування та читання з вкладками Word, Excel, PowerPoint , Видавець, доступ, Visio та проект.
• Відкривайте та створюйте кілька документів на нових вкладках одного вікна, а не у нових вікнах.
• Підвищує вашу продуктивність на 50% та скорочує кількість клацань мишею на сотні щодня!

Випадаючий список унікальних значень. Автоматичне оновлення списку

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

Розглянемо особливості створення списків, що випадають на прикладі:

Вихідні дані:

Завдання:

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

Ми рухатимемося поетапно, приділяючи увагу всім можливостям цього інструменту.

Завантажити файли з цієї статті

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

Список, що випадає в Excel

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

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

Випадаючий список готовий!

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

Як створити залежний список, що випадає в Excel?

Існує кілька варіантів. Один з них, це поєднання іменованих діапазонів та функції ДВССИЛ .

Іменований діапазон у Excel – це осередок (або діапазон осередків), якому присвоєно ім'я.

Функція ДВССИЛ в Excel перетворює текст на посилання.

Спосіб 1: іменовані діапазони + функція ДВССИЛ

Спочатку створимо іменовані діапазони з адресами. Ім'я кожному надамо відповідно до міста.

Алгоритм створення іменованого діапазону: виділяємо діапазон, далі Формули - Задати ім'я.

У нас вийде 5 іменованих діапазону: Волгоград, Воронеж, Краснодар, Москва та Ростов_на_Дону.

Зверніть увагу, до імен діапазонів є перелік вимог. Наприклад, в імені не можуть бути пробіли, коми, дефіси та інші символи. Докладніше про створення іменованих діапазонів та роботу з ними ми говоримо у нашому безкоштовному курсі Основи Excel.

Тому замість дефісів у назві міста Ростов-на-Дону ми вкажемо допустимий символ – нижнє підкреслення.

Іменовані діапазони готові.

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

У осередку D2, який використовується як аргумент функції ДВССИЛ , знаходиться текстове вираз, яке збігається з ім'ям відповідного іменованого діапазону з назвами міст. У результаті функція повертає посилання відповідний іменований діапазон.

Залежний список адрес, що випадає, готовий.

Змінюючи значення в комірці D2 змінюються списки в комірці E2. Крім міста Ростов-на-Дону. У списку міст (осередок D2), що випадає, в назві використовується дефіс, а в іменованому діапазоні - нижнє підкреслення.

Щоб усунути цю невідповідність, перед тим як застосовувати функцію ДВССИЛ , обробимо значення функцією ПІДСТАВИТИ .

Функція ПОДСТАВИ замінює певний текст у текстовому рядку на нове значення. Замість: =ДВССИЛ(D2) вкажемо: =ДВССИЛ(ПІДСТАВИТИ(D2;"-";"_"))

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

Тепер залежний список, що випадає, працює і для міста, що містить у назві дефіси – Ростов-на-Дону. Повернемося до списку міст, що випадає.

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

Спочатку створимо з діапазону даних «розумну» таблицю Excel. Зробити це можна поєднанням клавіш Ctrl+T.

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

Як зробити список унікальних значень в Excel?

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

І включимо новий стовпець до діапазону «розумної» таблиці. "Конструктор" - "Розмір таблиці". Замість =$B$1:$C$17 вказуємо: =$A$1:$C$17

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

У комірку А2 додамо формулу масиву, яка формуватиме список унікальних міст:

Щоб Excel сприйняв нашу формулу як формулу масиву, тиснемо Ctrl + Shift + Enter .

Отримуємо список унікальних міст, який при додаванні нових рядків автоматично оновлюватиметься.

Зі списку унікальних міст створимо іменований діапазон (ми назвали його - «Унікальні»), який потім використовуємо як джерело для випадаючого списку міст.

"Перевірка даних" - "Список". У джерелі даних замість попереднього діапазону з назвами міст =$B$2:$B$18 , задаємо ім'я – =Унікальні

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

Щоб їх усунути, доопрацюємо іменований діапазон «Унікальні». У диспетчері імен, замість діапазону =Таблиця1[Унікальні] використовуємо: =ЗМІЩ(Лист1!$A$2;0;0;РАХУНОК(Таблиця1[Унікальні])-ЗРАХУВАТИ ПУСТОТИ(Таблиця1[Унікальні])))

де: Лист1!$A$2 – комірка зі значенням першого пункту списку унікальних значень

Таблиця1[Унікальні] – стовпець з переліком усіх пунктів списку

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

Повернімося до залежного списку адрес.Випадаючий список міст тепер динамічний, а адреси так і залишилися фіксованими іменованими діапазонами.

Як зробити залежний список, що автоматично оновлюється? Спосіб 2: ЗМІЩ+ПОШУКПОЗ+РАХУНКИ

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

У осередок F2 (залежний список адрес, що випадає) замість: =ДВССИЛ(ПІДСТАВИТИ(E2;"-";"_")) вставляємо: =ЗМІЩ($B$2;ПОШУКПОЗ(E2;$B$2:$B$18;0) -1;1;РАХУНКИ($B$2:$B$18;E2);1)

Для коректної роботи цього способу дані в стовпці з містом повинні бути відсортовані. Функція ЗМІЩ динамічно посилатиметься лише на осередки адрес певного міста.

Аргументи функції:

Посилання – беремо перший осередок нашого списку, тобто. $B$2

Зміщення рядками – вважає функція ПОШУКПОЗ , яка видає порядковий номер осередку з вибраним містом (E2) у заданому діапазоні ( $B$2:$B$18 )

Зміщення по стовпцях = 1, т.к. ми хочемо послатись на адреси в сусідньому стовпці (С)

Висота – обчислюємо з допомогою функції РАХУНКИ , яка підраховує кількість зустрілися у діапазоні ( $B$2:$B$18 ) потрібних нам значень – назв міст (E2)

Ширина = 1, т.к. нам потрібний один стовпець з адресами

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

З списками, що випадають, в Google таблицях все трохи інакше.

Список, що випадає в Google таблицях

У таблицях Google є аналогічний інструмент для створення випадаючих списків - "Перевірка даних".

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

"Дані" - "Налаштувати перевірку даних" - "Значення з діапазону"

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

Залежний список, що випадає в Google таблицях

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

Спосіб 1: іменовані діапазони + ДВССИЛ

Створимо іменовані діапазони з адресами. Ім'я кожному надамо відповідно до міста.

Виділяємо осередки - "Дані" - "Налаштувати іменовані діапазони"

Вказуємо ім'я та тиснемо готово. У нас вийде 5 іменованих діапазонів: Волгоград, Воронеж, Краснодар, Москва та Ростов_на_Дону.

Також, як і в Excel, в таблицях Google до імен діапазонів є список вимог.

Тому замість дефісів у назві міста Ростов-на-Дону вкажемо допустимий символ – нижнє підкреслення.

У Google таблицях ми не зможемо подібно до Excel задати функцію ДВССИЛ в інструменті «Перевірка даних». Тому, розмістимо результат функції ДВССИЛ в порожніх осередках правіше. Не забуваємо додати обробку значень від дефісів функцією ПІДСТАВИТИ. Докладніше про те, навіщо це потрібно, ми говорили раніше у прикладі Excel.

У комірці F1 введемо: =ДВССИЛ(ПІДСТАВИТИ(D2;"_";"-"))

Останній штрих у створенні залежного списку, в розділі «Налаштувати перевірку даних», як діапазон вказуємо список зі стовпця F:F.

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

Як автоматично оновити випадаючий список у таблицях Google при додаванні нових даних?

У списку міст, що випадає, достатньо розширити діапазон і замість =$A$2:$A$16 вказати: =$A$2:$A . Тепер при додаванні нового міста він автоматично з'являється у списку, що випадає.

Як автоматично оновити залежний список, що випадає в Google таблицях при додаванні нових даних?

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

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

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

Прихуємо допоміжні стовпці для зручності.

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

Висновок

Тепер Вам відомі кілька способів, як створити списки, що випадають в Excel і Google таблицях. Дивіться приклади і створюйте потрібні Вам списки.

Вивчити роботу в Excel Ви можете на наших курсах: безкоштовні онлайн-курси по Excel

Пройдіть безкоштовний тест на нашому сайті, щоб об'єктивно оцінити свій рівень володіння інструментами та функціями програми Excel: пройти безкоштовний тест

У нас Ви можете замовити виконання завдань за MS Excel та Google таблицями