Як зробити випадаючий список в Excel за допомогою перевірки даних

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

Складання вихідних списків

Відкриваємо книгу Excel і в будь-якому її місці в стовпець або рядок, як вам зручніше) заносимо значення, які повинні складати список обмежених даних, що підлягають заповненню у яку-небудь клітинку. Якщо таких списків буде кілька, то краще для формування їх відвести окремий аркуш книги, а самі списки складати в стовпці, починаючи з першого рядка. Це може бути список товарів, одиниць виміру, документів, днів тижня і так далі. Нехай у нас буде три списки, що включають слова:

картопля, буряк, морква, редис;

петрушка, кріп, щавель, шпинат;

полуниця, вишня, черешня, аґрус.

Перші чотири слова заносимо в стовпець, починаючи з A1 другі - з B1 треті - C1. Щоб перейти безпосередньо до питання про те, як зробити випадаючий список в Excel, задамо імена цих списків. Для цього виділимо значення в першому стовпці, виберемо пункт меню: "Формули" - "Диспетчер імен" - "Присвоїти ім'я". В формі, що з'явилася в рядку "Ім'я" заповнюємо "овочі", натискаємо "ОК". Аналогічно повторюємо для інших списків, присвоївши їм імена "зелень" і "ягоди". У рядку "Галузь" залишаємо значення "Книга". Це дозволяє звертатися до даних списками з інших аркушів книги.

Створення списку в Excel

Тепер власне формуємо випадаючий список. Вибираємо, наприклад, комірку D1 на іншому аркуші відкритої книги, відкриваємо пункт меню: "Дані" - "Перевірка даних". У формі "Перевірка введених значень" у вкладці "Параметри" в полі "Тип даних" вибираємо "Список". Ставимо курсор в полі "Джерело", набираємо знак рівності і натискаємо клавішу F3. У меню "Вставка імені" вибираємо ім'я потрібного нам списку, наприклад, "овочі", потім натискаємо "ОК". Перевіряємо, щоб стояли галочки у позиціях "Ігнорувати пусті клітинки" і "Список допустимих значень". Це забезпечує можливість не заповнювати це поле даних і вводити дані із списку із зазначеним ім'ям. Тиснемо "ОК". Тепер при виборі даної клітинки праворуч від неї з'являється кнопка з трикутником вниз вістрям. Натиснувши на цю кнопку, можна ввести дані шляхом вибору одного з варіантів випадаючого списку. Спроба ввести інші дані викличе поява повідомлення про помилку.

Інші варіанти

При заповнення поля "Джерело" форми "Перевірка введених значень" можна застосувати ще два варіанти.

Поставивши курсор у це поле, виділити на даному аркуші книги область допустимих значень списку. Формула з'явиться в полі після знака рівності. Як зробити випадаючий список в Excel з іншого листа в цьому випадку? Треба аналогічний виділити діапазон клітинок на аркуші, а потім, поставивши курсор в полі після знака рівності, написати найменування листа зі списком і поставити знак оклику. Увага: назва аркуша не повинне містити пробілів!

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

Розширення діапазону допустимих значень

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

Подвійна посилання

Якщо вам потрібно мати можливість вводити в клітинку дані з декількох списків вибору, то це можна зробити таким чином. Спочатку створюємо список списків, з яких треба вводити дані. Нехай це будуть наші три списки, зазначені в першому підзаголовку даній статті. На цьому ж аркуші книги, починаючи, наприклад, з комірки F1 вводимо список, що містить імена раніше введених списків: "овочі", "зелень", "ягоди". Задаємо цього списку ім'я, наприклад, "Список 1". Тепер (можна і на іншому аркуші цієї книги) вибираємо, наприклад, клітинку G1 і задаємо для неї випадаючий список з посиланням на ім'я "Список 1". Наступну комірку, наприклад, H1 заповнюємо з урахуванням обраного значення в клітинці G1. Для цього в комірці H1 формуємо випадаючий список, як зазначено раніше, в полі "Джерело" вибираємо комірку G1 але допрацьовуємо запис у цьому полі, додаючи після знака рівності INDIRECT, а іншу частину формули взявши в круглі дужки: =INDIRECT($G$1). Тепер при виборі в комірці G1 наприклад, значення "зелень", для заповнення клітинки H1 буде запропонований вибір з значень цього списку. Ознайомившись зі змістом статті і потренувавшись у тому, як зробити випадаючий список в Excel, ви зможете істотно прискорити заповнення клітинок книги часто повторюваними даними, забезпечуючи при цьому чітке відповідність даних, що вводяться заданими вами обмежень.