Як зробити випадаючий список в excel за допомогою перевірки даних
Часто буває потрібно або здавати дані з певного списку, причому робити цю операцію багаторазово, або зробити форму заповнення клієнтом, де в певні місця можна заносити лише строго певні дані. Допомагає прискорити виконання таких операцій інструмент Excel, званий списком, що випадає. Розглянемо, як зробити випадаючий список в Excel.
Складання вихідних списків
Відкриваємо книгу Excel і в будь-якому її місці в стовпець (або в рядок, як вам зручніше) заносимо значення, які повинні складати список обмежених даних, що підлягають заповненню в яку-небудь клітинку. Якщо таких списків буде декілька, то краще для формування їх відвести окремий аркуш книги, а самі списки складати в стовпці, починаючи з першого рядка. Це може бути список товарів, одиниць виміру, документів, днів тижня і так далі. Нехай у нас буде три списки, що включають слова:
- картопля, буряк, морква, редиска;
- петрушка, кріп, щавель, шпинат;
- полуниця, вишня, черешня, аґрус.
Перші чотири слова заносимо в стовпець, починаючи з A1, другий - з B1, треті - з C1.
Щоб перейти безпосередньо до питання про те, як зробити випадаючий список в Excel, задамо імена цими списками. Для цього виділимо значення в першому стовпці, виберемо пункт меню: "Формули" - "Диспетчер імен" - "Присвоїти ім'я". У формі, що з'явилася в рядку "Ім'я" заповнюємо "овочі", натискаємо "ОК". Аналогічно повторюємо для інших списків, присвоївши їм імена "зелень" і "ягоди". У рядку "Область" залишаємо значення "Книга". Це дозволяє звертатися до даних списками з інших аркушів книги.
Створення списку в Excel
Тепер власне формуємо список, що випадає. Вибираємо, наприклад, осередок D1 на іншому аркуші відкритої книги, відкриваємо пункт меню: "Дані" - "Перевірка даних". У формі "Перевірка вводяться значень" у вкладці "Параметри" в поле "Тип даних" вибираємо "Список". Ставимо курсор в полі "Джерело", набираємо знак рівності і натискаємо клавішу F3. У меню, "Вставка імені" вибираємо ім'я потрібного нам списку, наприклад, "овочі", потім натискаємо "ОК". Перевіряємо, щоб стояли галочки в позиціях "Ігнорувати порожні клітинки" і "Список допустимих значень". Це забезпечує можливість не заповнювати це поле даними і вводити тільки дані зі списку з вказаним ім'ям. Тиснемо "ОК".
Тепер при виборі даної комірки праворуч від неї з'являється кнопка з трикутником вниз вістрям. Натиснувши на цю кнопочку, можна ввести дані шляхом вибору одного з варіантів списку. Спроба ввести інші дані викличе появу повідомлення про помилку.
Інші варіанти
При заповненні поля "Джерело" форми "Перевірка вводяться значень" можна застосувати ще два варіанти.
- Поставивши курсор в це поле, виділити на даному аркуші книги область допустимих значень списку. Формула з'явиться в полі після знаку рівності. Як зробити випадаючий список в Excel з іншого листа в цьому випадку? Треба аналогічний діапазон комірок виділити на цьому аркуші, а потім, поставивши курсор в поле після знаку рівності, написати найменування листа зі списком і поставити знак оклику. Увага: назва листа не повинно містити пробілів!
- Перерахувати допустимі значення в поле, розділяючи їх крапкою з комою. Це найпростіший спосіб, який не допускає звернення до списків на іншому аркуші книги.
Розширення діапазону допустимих значень
Недоліком описаних способів вирішення завдання, як зробити випадаючий список в Excel, є обмеження числа варіантів вводяться кількістю осередків заданих списків. Якщо надалі кількість допустимих для введення значень має збільшуватися, ці способи вимагають коректування вмісту поля "Джерело" для кожного осередку з списком, що випадає. Це незручно. Тому можна зробити розширюваний список, наприклад, на весь стовпець.
Для цього при завданні діапазону комірок в полі "Джерело" треба виділити весь стовпець натисканням на ліву кнопку миші при наведенні курсору на буквене позначення номера шпальти (при цьому курсор набуває вигляду стрілки вниз). У цьому випадку випадає список буде містити всі комірки стовпчика, навіть незаповнені. При подальшому заповненні комірок можна буде вибирати знову введені дані. Також при посиланні на ім'я списку можна заздалегідь розширити список на незаповнені клітинки стовпця, з тим щоб мати можливість подальшого коректування і доповнення списку новими вводяться даними.
Таким чином, ми отримали список, що випадає в Excel.
Подвійна посилання
Якщо вам треба мати можливість вводити в клітинку дані з декількох списків за вибором, то це можна зробити таким чином. Спочатку створюємо список списків, з яких треба вводити дані. Нехай це будуть наші три списки, зазначені в першому підзаголовку цієї статті. На цьому ж аркуші книги, починаючи, наприклад, з осередку F1, вводимо список, що містить імена раніше введених списків: "овочі", "зелень", "ягоди". Задаємо цього списку ім'я, наприклад, "Список 1".
Тепер (можна і на іншому аркуші цієї книги) вибираємо, наприклад, осередок G1 і задаємо для неї випадає список з посиланням на ім'я "Список 1". Наступну комірку, наприклад, H1, заповнюємо з урахуванням обраного значення в клітинці G1. Для цього в комірці H1 формуємо список, що випадає, як зазначено раніше, у полі "Джерело" вибираємо комірку G1, але допрацьовуємо запис в цьому полі, додаючи після знаку рівності ДВССИЛ, а іншу частину формули взявши в круглі дужки: = ДВССИЛ ($ G $ 1 ).
Тепер при виборі в клітинці G1, наприклад, значення "зелень", для заповнення осередку H1 буде запропонований вибір з значень цього списку.
Ознайомившись зі змістом статті та потренувавшись в тому, як зробити випадаючий список в Excel, ви зможете істотно прискорити заповнення клітинок книги часто повторюваними даними, забезпечуючи при цьому чітка відповідність даних, що вводяться заданих вами обмеженням.
