Як створити випадаючий список в Excel (перевірка даних)

By Mirko Peters · 2026-11-12

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

Automatically translated with AI from the original post. Read the English original

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

Головне

• Створіть випадаючий список в Excel через Дані > Перевірка даних: виберіть Список у полі Тип даних і вкажіть у полі Джерело свій перелік значень.
• Розмістіть значення в таблиці Excel (Ctrl+T), і випадаючий список зростатиме, коли ви додаватимете елементи в кінці переліку.
• Задайте стиль сповіщення про помилку: Зупинка блокує все, чого немає у списку, а Попередження та Повідомлення дозволяють інші записи.
• Перевірка стосується лише того, що людина вводить. Вставлення або заповнення клітинок може обійти список, тож захистіть аркуш, якщо список має бути суворим.
• Випадаючий список має ширину своєї клітинки, тож розширте стовпець, якщо довгі значення обрізаються.

Функція, на якій працює список, називається перевіркою даних. Посібник Microsoft зі створення випадаючого списку охоплює Excel для Microsoft 365, Excel 2024, 2021, 2019 і 2016. Обмеження та виправлення нижче взято з цієї сторінки, зі сторінки Microsoft про додавання та видалення елементів і з її нотаток про перевірку даних докладніше.

Створення випадаючого списку з діапазону клітинок

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

1. Введіть елементи. Запишіть їх в один стовпець на новому аркуші, по одному значенню в клітинці. Заголовок над ними можна залишити, просто ви не виділятимете його за мить.
2. Перетворіть на таблицю (рекомендовано). Виділіть будь-яку клітинку списку і натисніть Ctrl+T. Microsoft рекомендує це, оскільки випадаючі списки, побудовані на таблиці, оновлюються, коли ви додаєте або видаляєте елементи.
3. Виберіть цільову клітинку. Це клітинка, в якій має з'явитися випадаючий список. Ви також можете виділити кілька клітинок і призначити їм один і той самий список.
4. Відкрийте перевірку даних. Перейдіть на вкладку Дані і виберіть Перевірка даних.
5. Виберіть Список. На вкладці Параметри відкрийте поле Тип даних і виберіть Список.
6. Задайте джерело. Клацніть у полі Джерело, а потім виділіть свій список на аркуші. У прикладі Microsoft список розміщено в діапазоні A2:A9 на аркуші з назвою Cities, а рядок заголовка в A1 пропущено, щоб він не став варіантом вибору.
7. Позначте прапорці. Залиште Розкривний список у клітинці увімкненим, адже саме він показує стрілку. Увімкніть Ігнорувати порожні, якщо користувачі можуть залишати клітинку порожньою.
8. Натисніть OK.

Клацніть клітинку. З'явиться стрілка, і під нею відкриється список варіантів. Це і є весь спосіб.

Якщо команда «Перевірка даних» неактивна (сіра), Microsoft пояснює це тим, що аркуш може бути захищений або робоча книга може бути спільною. Знімте захист з аркуша або припиніть спільний доступ до книги та спробуйте ще раз. Команда також недоступна, поки ви вводите текст у клітинку, тому спершу натисніть Enter або Esc.

Розмістіть список на іншому аркуші

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

Введіть елементи безпосередньо

Для короткого списку, який ніколи не змінюється, наприклад Так, Ні, Можливо, можна обійтися без клітинок і ввести елементи в поле Джерело через кому. На сторінці Microsoft про редагування списків формат показано як Yes,No,Maybe, без пробілів після ком. Це швидко, але значення ховаються всередині правила перевірки. Щоб їх змінити, потрібно відкрити діалогове вікно «Перевірка даних». Для будь-якого списку довшого за кілька елементів використовуйте клітинки.

Зробіть список зручним для читання

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

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

Два додаткові вкладки в діалоговому вікні «Перевірка даних» перетворюють простий список на зручну форму.

Вхідне повідомлення

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

Сповіщення про помилку

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

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

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

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

Тримайте розкривний список в актуальному стані

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

Якщо джерелом є таблиця

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

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

Якщо джерелом є іменований діапазон

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

1. Перейдіть до Формули > Диспетчер імен.
2. Виберіть іменований діапазон.
3. У полі Refers to видаліть поточне посилання та виберіть усі клітинки, у яких міститься список.
4. Щоб зберегти зміни, натисніть Close, а потім Yes.

Якщо ви не пам'ятаєте назву, виберіть список і прочитайте її в полі Name Box поруч із рядком формул. Щоб створити іменований діапазон, скористайтеся командою Define Name на вкладці Formulas. Потім у полі Source введіть назву зі знаком рівності, наприклад =Choices. Іменований діапазон зручний, коли той самий список використовують для випадаючих списків на кількох аркушах.

Якщо список введено безпосередньо в полі Source

Виберіть клітинку з випадаючим списком, перейдіть до Data > Data Validation і змініть елементи на вкладці Settings. Установіть прапорець Apply these changes to all other cells with the same settings, щоб зміна поширилася на всі клітинки з тим самим правилом.

Чому випадаючий список перестає працювати

Більшість проблем спричиняє невеликий перелік причин. Microsoft описує їх на своїх сторінках.

Перевірка не блокує вставлені дані. Microsoft пояснює, що перевірка даних показує повідомлення та не допускає некоректних значень лише тоді, коли користувачі вводять дані безпосередньо в клітинку. Якщо хтось копіює значення звідкись в інше місце і вставляє його або перетягує маркер заповнення, правило до отриманих даних не застосовується. Якщо список має бути суворим, вимкніть Enable fill handle and cell drag-and-drop у розділі File > Options > Advanced > Editing options, а потім захистіть аркуш. Ви також втратите частину зручності, тож робіть це лише на аркушах, де точність важливіша за все інше.

Наявні значення не перевіряються. Якщо ви додаєте список до клітинок, які вже містять дані, Excel не позначає старі значення. Щоб знайти ті, що не відповідають правилам, виберіть клітинки та скористайтеся командою Data > Data Validation > Circle Invalid Data. Позначки зникнуть, щойно ви виправите записи.

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

Помилки у формулах. Microsoft зазначає, що перевірка ігнорується, поки формули в перевірених клітинках повертають помилки, наприклад #REF! або #DIV/0!. Також там сказано, що ручне обчислення може завадити правильній роботі перевірки, тож встановіть автоматичне обчислення (Automatic).

Пов'язані таблиці. Перевірку даних не можна додати до таблиці Excel, пов'язаної з сайтом SharePoint. Спершу відв'яжіть таблицю або перетворіть її на діапазон.

Пошук клітинок із правилом. На вкладці Home відкрийте Find & Select і виберіть Data Validation. Так буде виділено всі клітинки на аркуші, для яких увімкнено перевірку.

Видалення списку. Виберіть клітинку та скористайтеся командами Data > Data Validation > Settings > Clear All.

Excel для веббраузера

Excel для веббраузера — це місце, де варто перевірити файл, перш ніж на нього покладатися. На сторінці Microsoft про перевірку даних сказано, що для книг, які використовуються у Excel Web App, перевірку спочатку потрібно створити в настільній версії. Ця примітка застаріла, і поточна версія браузера може поводитися інакше, а сторінка Microsoft про випадаючі списки не містить кроків саме для веб-версії. Створіть список у настільному Excel, збережіть книгу та відкрийте її в браузері, щоб побачити, як вона працюватиме для ваших колег.

Типові помилки

• Вибір клітинки заголовка в діапазоні Source, щоб «Cities» з'являлося як варіант вибору.
• Введення елементів із пробілами післяком, а потім здивування, чому в записах з'являється зайвий пробіл.
• Використання іменованого діапазону без урахування того, що його потрібно редагувати, коли список зростає.
• Установлення для Error Alert значення Information з очікуванням, що це блокуватиме помилки друку. Заблокувати їх може лише Stop.
• Захист аркуша до того, як звільнено клітинки для введення.
• Розраховувати, що випадаючий список збереже вставлені дані чистими.
• Занадто вузький стовпець, через який довгі записи виглядають обрізаними, і люди обирають неправильний варіант.

Пов'язані теми для ознайомлення

• Списки, які розширюються самі. Найпростіше рішення — таблиці. Якщо потрібен більший контроль, є ще один варіант — іменований діапазон на основі формули. Перш ніж впроваджувати його, варто протестувати.
• Залежні випадаючі списки. Другий список, який змінюється залежно від першого вибору, наприклад країна та місто. Сторінки Microsoft, наведені вище, цього не охоплюють, тож розглядайте це як окремий проєкт і тестуйте на копії файлу.
• Форми та комбіновані поля. Microsoft має окрему статтю про додавання поля зі списком або комбінованого поля до аркуша. Ці елементи керування потребують більше налаштування, ніж перевірка даних.
• Приховування або показ списку залежно від іншої комірки. Зазвичай для цього потрібні умовне форматування або VBA, і тестувати це варто на копії файлу.
• Спочатку наведіть порядок. Якщо вихідний список містить повтори, видаліть їх до створення випадаючого списку. Наша стаття про те, як видалити дублікати в Excel, докладно пояснює це.

Наступний крок

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