Як створити розкривний список в Excel

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

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

Процес створення списку

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

Кількість способів введення інформації у спливаючий список досить велика.

1. Незалежне вказівка ​​елементів списку, розділених крапкою з комою, у полі «Джерело», розташованому на тій же вкладці того ж діалогового вікна.
   

   

2. Попередня вказівка ​​цінностей. Поле Джерело містить діапазон, у якому доступна необхідна інформація.
   

   

3. Вказівка ​​іменованого діапазону. Спосіб, який повторює попередній, але необхідно лише попередньо назвати діапазон.
   

   

Будь-який з цих способів дасть бажаний результат. Давайте розглянемо методи створення розкривних списків у реальних ситуаціях.

На основі даних зі списку

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

Щоб створити список у розкривному меню на основі цього набору інформації, вам потрібно зробити наступне:

1. Виберіть клітинку, зарезервовану для майбутнього списку.
2. Знайдіть вкладку Дані на стрічці. Там натискаємо «Перевірити дані».
   

   

3. Знайдіть пункт «Тип даних» і змініть значення на «Список».
   

   

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

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

З ручним записом даних

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

Припустимо, що перед нами стоїть завдання створити список, що містить два можливі варіанти: так і ні. Для виконання завдання необхідно:

1. Клацніть клітинку для списку.
2. Відкриваємо «Дані» і знаходимо там знайомий нам розділ «Перевірка даних».
   

   

3. Знову виберіть тип «Список».
   

   

4. Тут потрібно ввести «Так; Ні» як джерело. Ми бачимо, що інформація вводиться вручну за допомогою крапки з комою для перерахування.

Після натискання ОК маємо наступний результат.

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

Створення випадаючого списку за допомогою функції OFFSET

Крім класичного методу, можливе використання функції ВІДХОДЖЕННЯдля створення спадних меню.

Давайте відкриємо аркуш.

Щоб скористатися функцією випадаючого списку, потрібно виконати наступні дії:

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

   

3. Встановіть «Список». Це робиться так само, як і в попередніх прикладах. Нарешті, використовується така формула: =ЗМІЩЕННЯ(A$2$;0;0;5). Вводимо його там, де вказані комірки, які будуть використовуватися як аргумент.

Потім програма створить меню зі списком фруктів.

Синтаксис для цього:

=ЗМІЩЕННЯ(посилання,зміщення_рядка,зсув_стовпця,[висота],[ширина])

Ми бачимо, що ця функція має 5 аргументів. По-перше, задається адреса першої комірки, яку потрібно зсунути. Наступні два аргументи вказують кількість рядків і стовпців для зміщення. Говорячи про нас, аргумент Height дорівнює 5, оскільки він представляє висоту списку. 

Випадаючий список в Excel з підстановкою даних (+ за допомогою функції OFFSET)

У наведеному випадку ВІДХОДЖЕННЯ дозволяє створювати спливаюче меню, розташоване у фіксованому діапазоні. Недоліком цього методу є те, що після додавання елемента вам доведеться самостійно редагувати формулу.

Для створення динамічного списку з підтримкою введення нової інформації необхідно:

1. Виберіть комірку, яка вас цікавить.
2. Розгорніть вкладку «Дані» та натисніть «Перевірка даних».
3. У вікні знову виберіть пункт «Список» і в якості джерела даних вкажіть формулу: =СМЕЩ(A$2$;0;0;СЧЕТЕСЛИ($A$2:$A$100;”<>”))
4. Натисніть кнопку ОК.

Це містить функцію COUNTIF, щоб одразу визначити, скільки комірок заповнено (хоча він має набагато більшу кількість застосувань, ми просто пишемо його тут для певної мети).

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

Розкривний список із даними з іншого аркуша або файлу Excel

Класичний метод не працює, якщо вам потрібно отримати інформацію з іншого документа або навіть аркуша, що міститься в тому ж файлі. Для цього використовується функція НЕПРАВИЛЬНО, що дозволяє ввести в правильному форматі посилання на комірку, розташовану на іншому аркуші або взагалі – на файл. Вам потрібно зробити наступне:

1. Активуйте комірку, де ми розмістимо список.
2. Відкриваємо знайоме нам вікно. Там же, де ми раніше вказували джерела для інших діапазонів, у форматі вказана формула =INDIRECT(“[Список1.xlsx]Аркуш1!$A$1:$A$9”). Звичайно, замість List1 і Sheet1 ви можете вставити назви вашої книги та аркуша відповідно. 

Увага! Ім'я файлу вказано у квадратних дужках. У цьому випадку Excel не зможе використовувати файл, який зараз закритий, як джерело інформації.

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

Створення залежних спадних меню

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

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

1. Створіть перший список із назвами діапазонів.
   

   

2. У точці входу джерела по черзі підсвічуються потрібні індикатори.
   

   

3. Створіть 2-й список залежно від типу рослини, яку обрала людина. Крім того, якщо ви вкажете дерева в першому списку, то інформація в другому списку буде «дуб, граб, каштан» і далі. Формулу необхідно записати в місці введення джерела даних =НЕПРЯМИЙ(E3). E3 – комірка, що містить назву діапазону 1.=INDIRECT(E3). E3 – комірка з назвою списку 1.

Тепер все готово.

Як вибрати кілька значень зі списку, що випадає?

Іноді неможливо віддати перевагу лише одному значенню, тому потрібно вибрати більше одного. Потім потрібно додати макрос до коду сторінки. За допомогою комбінації клавіш Alt + F11 відкривається редактор Visual Basic. І код вставляється туди.

Приватний допоміжний робочий лист_Change (ByVal Target As Range)

    On Error Resume Next

    Якщо Not Intersect(Target, Range(«Е2:Е9»)) є нічим і Target.Cells.Count = 1 Тоді

        Application.EnableEvents = False

        Якщо Len (Target.Offset (0, 1)) = 0 Тоді

            Target.Offset (0, 1) = Ціль

        ще

            Target.End (xlToRight) .Offset (0, 1) = Ціль

        End If

        Target.ClearContents

        Application.EnableEvents = True

    End If

End Sub 

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

Приватний допоміжний робочий лист_Change (ByVal Target As Range)

    On Error Resume Next

    Якщо Not Intersect(Target, Range(«Н2:К2»)) Is Nothing And Target.Cells.Count = 1 Тоді

        Application.EnableEvents = False

        Якщо Len (Target.Offset (1, 0)) = 0 Тоді

            Target.Offset (1, 0) = Ціль

        ще

            Target.End (xlDown) .Offset (1, 0) = Target

        End If

        Target.ClearContents

        Application.EnableEvents = True

    End If

End Sub

І, нарешті, цей код використовується для запису в одну клітинку.

Приватний допоміжний робочий лист_Change (ByVal Target As Range)

    On Error Resume Next

    Якщо Not Intersect(Target, Range(«C2:C5»)) Is Nothing And Target.Cells.Count = 1 Тоді

        Application.EnableEvents = False

        newVal = Ціль

        Застосування. Скасувати

        oldval = Ціль

        Якщо Len (oldval) <> 0 і oldval <> newVal Тоді

            Target = Target & «,» & newVal

        ще

            Target = newVal

        End If

        Якщо Len (newVal) = 0, тоді Target.ClearContents

        Application.EnableEvents = True

    End If

End Sub

Діапазони можна редагувати.

Як зробити випадаючий список з пошуком?

У цьому випадку ви повинні спочатку використовувати інший тип списку. Відкривається вкладка «Розробник», після чого потрібно клацнути або тапнути (якщо екран сенсорний) по елементу «Вставити» – «ActiveX». Він має комбінований список. Вам буде запропоновано намалювати цей список, після чого він буде доданий до документа.

Крім того, він налаштовується за допомогою властивостей, де діапазон вказується в параметрі ListFillRange. Комірка, у якій відображається визначене користувачем значення, налаштовується за допомогою параметра LinkedCell. Далі потрібно просто записати перші символи, як програма автоматично запропонує можливі значення.

Випадаючий список з автоматичною заміною даних

Також є функція автоматичної заміни даних після додавання в діапазон. Це легко зробити:

1. Створіть набір комірок для майбутнього списку. У нашому випадку це набір кольорів. Вибираємо його.
   

   

2. Далі її потрібно оформити у вигляді таблиці. Потрібно натиснути однойменну кнопку і вибрати стиль таблиці.
   

   

   

Далі потрібно підтвердити цей діапазон, натиснувши кнопку «ОК».

Вибираємо отриману таблицю і даємо їй назву через поле введення, розташоване над стовпцем А.

Ось і все, таблиця є, і її можна взяти за основу для випадаючого списку, для якого потрібно:

1. Виділіть комірку, де знаходиться список.
2. Відкрийте діалогове вікно Перевірка даних.
   

   

3. Встановлюємо тип даних «Список», а в якості значень даємо назву таблиці через знак =.
   

   

   

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

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

Як скопіювати спадний список?

Для копіювання достатньо скористатися комбінацією клавіш Ctrl + C і Ctrl + V. Таким чином, випадаючий список буде скопійований разом з форматуванням. Для видалення форматування потрібно скористатися спеціальною вставкою (в контекстному меню ця опція з’являється після копіювання списку), де встановлено параметр «умови на значення».

Виберіть усі комірки, які містять розкривний список

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

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