Зміст
![Ниспадающий список в Microsoft Excel](https://cdn.daad.org.ua/images/7998/1d3ac99166dba9cb53aff86a2db1ebc6.png)
Створення спадаючих списків дозволяє не тільки економити час при виборі варіанту в процесі заповнення таблиць, а й захистити себе від помилкового внесення некоректних даних. Це дуже зручний і практичний інструмент. Давайте з'ясуємо, як активувати його в Excel, і як ним користуватися, а також дізнаємося деякі інші нюанси поводження з ним.
Використання випадаючих списків
Спадаючі, або як прийнято говорити, розкриваються списки найчастіше застосовують в таблицях. З їх допомогою можна обмежити коло внесених в табличний масив значень. Вони дозволяють вибирати для внесення значення тільки з заздалегідь підготовленого переліку. Це одночасно прискорює процедуру внесення даних і оберігає від помилки.
Процедура створення
Перш за все, давайте з'ясуємо, як створити спадаючий список. Найлегше це зробити за допомогою інструменту під назвою " Перевірка даних» .
- Виділяємо стовпець табличного масиву, в осередках якого планується розмістити список, що випадає. Пересуваємося у вкладку»дані" і клацаємо по кнопці " Перевірка даних» . Вона локалізована на стрічці в блоці " робота з даними» .
- Запускається вікно інструменту " Перевірка значень»
. Переходимо в розділ
»Параметри"
. В області
" Тип даних»
з переліку вибираємо варіант
»Список"
. Після цього переміщаємося до поля
»джерело"
. Тут потрібно вказати групу найменувань, призначену для використання в списку. Ці назви можна внести вручну, а можна вказати на них посилання, якщо вони вже розміщені в документі Excel в іншому місці.
Якщо вибрано введення вручну, то кожен списковий елемент потрібно вписати в область через крапку з комою ( ; ).
Якщо ви хочете підтягнути дані з уже існуючого табличного масиву, то слід перейти на лист, де той розташований (якщо він розміщений на іншому), поставити курсор в область «джерело» вікна перевірки даних, а потім виділити масив осередків, де знаходиться перелік. Важливо, щоб в кожній окремій комірці розташовувався окремий списковий елемент. Після цього координати вказаного діапазону повинні відобразитися в області «джерело» .
Ще один варіант установки зв'язку – це присвоєння масиву з переліком імені. Виділяємо діапазон, в якому вказані значення даних. Зліва від рядка формул знаходиться область імен. За замовчуванням в ньому при виділенні діапазону відображається координати першої виділеної комірки. Ми ж для наших цілей просто вписуємо туди найменування, яке вважаємо більш відповідним. Головні вимоги до імені полягають в тому, щоб воно було унікальним в межах книги, не мало пробілів і обов'язково починалося з літери. Тепер саме по даному найменуванню буде ідентифікуватися той діапазон, який ми до цього виділили.
Тепер у вікні перевірки даних в області «джерело» потрібно встановити символ «=» , а потім відразу ж після нього вписати те ім'я, яке ми присвоїли діапазону. Програма відразу ідентифікує зв'язок між найменуванням і масивом, і підтягне той перелік, який в ньому розташований.
Але набагато ефективніше вийде використовувати перелік, якщо його перетворити в «розумну» таблицю. У такій таблиці буде легше змінювати значення, тим самим автоматично змінюючи спискові елементи. Таким чином, даний діапазон фактично перетвориться в таблицю підстановок.
Для того, щоб перетворити діапазон в "розумну" таблицю, виділяємо його і пересуваємося у вкладку»Головна" . Там клацаємо по кнопці " форматувати як таблицю» , яка розміщена на стрічці в блоці «стилі» . Відкривається велика група стилів. На функціональність таблиці вибір конкретного стилю ніяк не впливає, а тому вибираємо будь-який з них.
Після цього відкривається невелике віконце, в якому міститься адреса виділеного масиву. Якщо виділення було виконано вірно, то тут нічого змінювати не потрібно. Так як у нашого діапазону немає заголовків, то у пункту " таблиця з заголовками» галочки бути не повинно. Хоча конкретно у вашому випадку, можливо, заголовок і буде застосовуватися. Так що нам залишається просто натиснути на кнопку «OK» .
Після цього діапазон буде відформатований, як таблиця. Якщо його виділити, то можна помітити в області Імен, що найменування йому було присвоєно автоматично. Це найменування можна використовувати для вставки в область»джерело" у вікні перевірки даних за описаним раніше нами алгоритмом. Але, якщо ви хочете використовувати іншу назву, то його можна замінити, просто вписавши в область імен.
Якщо перелік розміщений в іншій книзі, то для коректного його відображення потрібно застосувати функцію ДВСИЛ . Зазначений оператор призначений для того, щоб формувати «суперабсолютні» посилання на елементи листа в текстовому вигляді. Власне при цьому процедура буде виконуватися практично точно така ж, як і в раніше описуваних випадках, тільки в області »джерело" після символу «=» слід вказати найменування оператора – " ДВСИЛ» . Після цього в дужках в якості аргументу даної функції повинен бути вказана адреса діапазону, включаючи найменування книги і листа. Власне, як показано на зображенні нижче.
- На цьому ми б могли і закінчити процедуру, натиснувши на кнопку «OK» у віконці перевірки даних, але при бажанні можна вдосконалити форму. Переходимо в розділ " повідомлення для введення» віконця перевірки даних. Тут в області »повідомлення" можна написати текст, який будуть бачити користувачі, Наводячи курсор на елемент листа з спадаючим списком. Записуємо те повідомлення, яке вважаємо за потрібне.
- Далі переміщаємося в розділ " повідомлення про помилку» . Тут в області »повідомлення" можна ввести текст, який буде спостерігати користувач при спробі ввести некоректні дані, тобто, будь-які дані, відсутні в спадаючому списку. В області " Вид» можна вибрати значок, яким буде супроводжуватися попередження. Вводимо текст повідомлення і клацаємо по «OK» .
![Переход в окно проверки данных в Microsoft Excel](https://cdn.daad.org.ua/images/7998/e68f5586f3367371d1ac462421ea84c6.png)
![Использование функции ДВССЫЛ в поле Источник окна проверки вводимых значений в Microsoft Excel](https://cdn.daad.org.ua/images/7998/0f7efbfe4a5b8944e60ecf54f7e5a40a.png)
![Сообщение для ввода в окне проверки вводимых значений в Microsoft Excel](https://cdn.daad.org.ua/images/7998/9e0a3b6fc0cd6a4d9dbecb63cecc67bb.png)
![Сообщение об ошибке в окне проверки вводимых значений в Microsoft Excel](https://cdn.daad.org.ua/images/7998/3545625689299f85b84d63ba54ebf37c.png)
Урок: як зробити випадаючий список в Екселі
Виконання операцій
Тепер давайте розберемося, як працювати з тим інструментом, який ми вище створили.
- Якщо ми встановимо курсор на будь-який елемент листа, до якого був застосований спадаючий список, то побачимо інформаційне повідомлення, введене нами раніше у вікні перевірки даних. Крім того, праворуч від осередку з'явиться піктограма у вигляді трикутника. Саме вона служить для доступу до вибору спискових елементів. Клацаємо по даному трикутнику.
- Після кліка по ньому меню з спискових об'єктів буде відкрито. У ньому розташовані всі елементи, які були раніше внесені через вікно перевірки даних. Вибираємо той варіант, який вважаємо за потрібне.
- Вибраний варіант відобразиться в комірці.
- Якщо ж ми спробуємо ввести в комірку будь-яке значення, яке відсутнє в переліку, то дана дія заблокується. При цьому, якщо ви вносили повідомлення попередження у вікно перевірки даних, то воно відобразитися на екрані. Потрібно у вікні попередження натиснути на кнопку " скасування» і з наступної спроби ввести коректні дані.
![Сообщение для ввода при установки курсора на ячейку в Microsoft Excel](https://cdn.daad.org.ua/images/7998/853806437b4edc4f95875289765c3ba0.png)
![Ниспадающий список открыт в Microsoft Excel](https://cdn.daad.org.ua/images/7998/aa5ff085aee8195ecbc44a76a1292ca2.png)
![Вариант из ниспадающего списка выбран в Microsoft Excel](https://cdn.daad.org.ua/images/7998/a21296a034bed6bb9bbf9eb4345106b5.png)
![Введено некорректное значение в Microsoft Excel](https://cdn.daad.org.ua/images/7998/26f7967b5ec8dc8c10bbeb48a58e9693.png)
Таким способом При необхідності заповнюємо всю таблицю.
Додавання нового елемента
Але що робити, якщо потрібно все-таки додати новий елемент? Дії тут залежать від того, як саме ви сформували перелік у вікні перевірки даних: введений вручну або підтягується з табличного масиву.
- Якщо дані для формування списку підтягуються з табличного масиву, то переходимо до нього. Виділяємо осередок діапазону. Якщо це не» розумна " таблиця, а простий діапазон даних, то потрібно вставити рядок в середину масиву. Якщо ж застосовуєте "розумну" таблицю, то в цьому випадку досить просто вписати необхідне значення в першому рядку під нею і даний рядок тут же буде включена в табличний масив. Це якраз та перевага» розумної " таблиці, про яку ми згадували вище.
Але припустимо, що ми маємо справу з більш складним випадком, використовуючи звичайний діапазон. Отже, виділяємо осередок в середині зазначеного масиву. Тобто, над цією коміркою і під нею повинні бути ще рядки масиву. Клацаємо по позначеному фрагменту правою кнопкою миші. В меню вибираємо варіант " вставити...» .
- Запускається віконце, де слід зробити вибір об'єкта вставки. Вибираємо варіант «рядок» і тиснемо на кнопку «OK» .
- Отже, порожній рядок додано.
- Вписуємо в неї те значення, яке бажаємо, щоб відобразилося в спадаючому списку.
- Після цього повертаємося до того табличного масиву, в якому розміщений розкривний перелік. Клікнувши по трикутнику, праворуч від будь-якої комірки масиву, ми бачимо, що до вже наявних спискових елементів було додано потрібне нам значення. Тепер при бажанні його можна вибрати, щоб вставити в елемент таблиці.
![Переход к вставке ячейки в Microsoft Excel](https://cdn.daad.org.ua/images/7998/082645a9a2b290e7d27629c3e812a9f8.png)
![Выбор объекта вставки в окне добавления ячеек в Microsoft Excel](https://cdn.daad.org.ua/images/7998/1ff1ce4676c588e2fa2e28a2a8cb6c75.png)
![Пустая строка добавлена в Microsoft Excel](https://cdn.daad.org.ua/images/7998/e83817f2691498ee0c8b5bc8233b8697.png)
![Значение добавлено в массив ячеек в Microsoft Excel](https://cdn.daad.org.ua/images/7998/8c2ab4a8b4638f86092e3c97079904f9.png)
![Добавленное значение присутствует в ниспадающем списке в Microsoft Excel](https://cdn.daad.org.ua/images/7998/3270dcc40f43938dc0d0541ef29bad48.png)
Але що робити, якщо перелік значень підтягується не з окремої таблиці, а був внесений вручну? Для додавання елемента в цьому випадку теж є свій алгоритм дій.
- Виділяємо весь табличний діапазон, в Елементах якого розміщений випадає перелік. Переходимо у вкладку»дані" і знову натискаємо на кнопку " Перевірка даних» в групі " робота з даними» .
- Запускається вікно перевірки даних, що вводяться. Переміщаємося в розділ «Параметри» . Як бачимо, всі налаштування тут точно такі ж, як ми їх виставили раніше. Нас в даному випадку буде цікавити область «джерело» . Додаємо туди до вже має переліку через крапку з комою ( ; ) то значення або значення, які хочемо побачити в випадаючому списку. Після додавання клацаємо по «OK» .
- Тепер, якщо ми відкриємо випадає Перелік в табличному масиві, то побачимо там додане значення.
![Переход в окно проверки данных в программе Microsoft Excel](https://cdn.daad.org.ua/images/7998/99b89294d43bb24de1f89dee1b8ae090.png)
![Добавление нового значение в поле Источник в окне проверки вводимых значений в Microsoft Excel](https://cdn.daad.org.ua/images/7998/70d706d7fdaae2b2cd8ea04775f5e692.png)
![Значение появилось в выпадающем списке в Microsoft Excel](https://cdn.daad.org.ua/images/7998/2b097b4bad6abfce70a54d713b943808.png)
Видалення елемента
Видалення облікового елемента проводиться за точно таким же алгоритмом, що і додавання.
- Якщо дані підтягуються з табличного масиву, то тоді переходимо до цієї таблиці і клацає правою кнопкою миші по осередку, де розташоване значення, яке слід видалити. У контекстному меню зупиняємо вибір на варіанті " видалити ... » .
- Відкривається вікно видалення комірок практично аналогічне тому, яке ми бачили при їх додаванні. Тут знову встановлюємо перемикач в позицію «рядок» і клацаємо по «OK» .
- Рядок з табличного масиву, як бачимо, видалена.
- Тепер повертаємося до тієї таблиці, де знаходяться осередки з випадаючим переліком. Клацаємо по трикутнику праворуч від будь-якого осередку. У розкрився списку бачимо, що віддалений елемент відсутній.
![Переход к удалению ячейки в Microsoft Excel](https://cdn.daad.org.ua/images/7998/5e27812d36022fd1160cd782455c2c73.png)
![Удаление строки через окно удаления ячеек в Microsoft Excel](https://cdn.daad.org.ua/images/7998/f1c97f324ef08792995f10b66ed2354c.png)
![Строка удалена в Microsoft Excel](https://cdn.daad.org.ua/images/7998/6b3abdecf819ffc67d9948a0d7c03e47.png)
![Удаленной элемент отсутствует в выпадающем списке в Microsoft Excel](https://cdn.daad.org.ua/images/7998/234bb2ac9d6b74b12c5c62cf83b75e51.png)
Що ж робити, якщо значення додавалися в вікно перевірки даних вручну, а не за допомогою додаткової таблиці?
- Виділяємо табличний діапазон з розкривним переліком і переходимо в віконце перевірки значень, як це ми вже робили раніше. У зазначеному вікні переміщаємося в розділ «Параметри» . В області «джерело» виділяємо курсором то значення, яке потрібно видалити. Потім тиснемо на кнопку Delete на клавіатурі.
- Після того, як елемент видалений, клацаємо по «OK» . Тепер його не буде і в випадаючому переліку, точно так же, як це ми бачили і в попередньому варіанті дій з таблицею.
![Удаление элемента в поле Источник в окне проверки вводимых значений в Microsoft Excel](https://cdn.daad.org.ua/images/7998/8c3509426775dd9b97800e105a4cedec.png)
![Удаление элемента в поле Источник в окне проверки вводимых значений в Microsoft Excel](https://cdn.daad.org.ua/images/7998/02ee159512c4865362080139db6662c0.png)
Повне видалення
У той же час, існують і ситуації, коли список, що випадає потрібно повністю видалити. Якщо вам не важливо, щоб введені дані були збережені, то зробити Видалення дуже просто.
- Виділяємо весь масив, де розташований розкривний перелік. Пересуваємося у вкладку»Головна" . Клацаємо по іконці »Очистити" , яка розміщується на стрічці в блоці «редагування» . У меню вибираємо позицію " Очистити все» .
- При виборі даного дії в виділених елементах листа будуть видалені всі значення, очищено форматування, а крім того, досягнута головна мета поставленого завдання: розкривний перелік буде видалений і тепер в осередку можна вводити будь-які значення вручну.
![Удаление элемента в поле Источник в окне проверки вводимых значений в Microsoft Excel](https://cdn.daad.org.ua/images/7998/4476161c889c7e528593e8911498cf03.png)
![Удаление элемента в поле Источник в окне проверки вводимых значений в Microsoft Excel](https://cdn.daad.org.ua/images/7998/cd5a972b1e4e0ebe551ac6c57317f6ef.png)
Крім того, якщо користувачеві не потрібно зберігати введені дані, то існує ще один варіант видалити спадаючий перелік.
- Виділяємо діапазон порожніх осередків, який рівнозначний діапазону елементів масиву з розкривним переліком. Пересуваємося у вкладку»Головна"
і там натискаємо по іконці
" копіювати»
, яка локалізується на стрічці в області
" Буфер обміну»
.
Також замість цього дії можна клікнути по позначеному фрагменту правою кнопкою мишки і зупинитися на варіанті «копіювати» .
Ще простіше відразу після виділення застосувати набір кнопок Ctrl+C .
- Після цього виділяємо той фрагмент табличного масиву, де розташовуються випадають елементи. Тиснемо на кнопку»вставити"
, локалізовану на стрічці у вкладці
»Головна"
у розділі
" Буфер обміну»
.
Другий варіант дій-це клацнути по виділенню правою кнопкою мишки і зупинити вибір на варіанті»вставити" у групі «Параметри вставки» .
Нарешті, є можливість просто позначити потрібні комірки і набрати поєднання кнопок Ctrl+V .
- При будь-якому з вищевказаних дій замість осередків, що містять значення і спадаючі списки, буде вставлений абсолютно чистий фрагмент.
![Диапазон очищен с помощью копирования в Microsoft Excel](https://cdn.daad.org.ua/images/7998/e709110e39c99541950d3c9c744c067a.png)
При бажанні таким же чином можна вставити не порожній діапазон, а скопійований фрагмент з даними. Недолік спадаючих списків якраз полягає в тому, що в них не можна вручну вписати дані, відсутні в переліку, але їх можна скопіювати і вставити. При цьому перевірка даних не спрацює. Більш того, як ми з'ясували, сама структура списку, що випадає буде знищена.
Найчастіше, потрібно все-таки прибрати список, що випадає, але при цьому залишити ті значення, які за допомогою нього були введені, і форматування. В цьому випадку слід зробити більш коректні дії з видалення зазначеного інструменту заповнення.
- Виділяємо весь фрагмент, в якому розташовані елементи з розкривним переліком. Пересуваємося у вкладку «дані» і клацаємо по значку " Перевірка даних» , який, як ми пам'ятаємо, розміщений на стрічці в групі " робота з даними» .
- Відкривається вже добре знайоме нам вікно перевірки даних, що вводяться. Перебуваючи в будь-якому розділі вказаного інструменту, нам потрібно зробити єдину дію &8212; натиснути на кнопку " Очистити все» . Вона розташовується в нижньому лівому кутку вікна.
- Після цього вікно перевірки даних можна закривати, натиснувши на стандартну кнопку закриття в його верхньому правому куті у вигляді хрестика або на кнопку «OK» у нижній частині вікна.
- Потім виділяємо будь-яку з осередків, в якій раніше був розміщений розкривається перелік. Як бачимо, тепер немає ні підказки при виділенні елемента, ні трикутника для виклику списку праворуч від комірки. Але при цьому недоторканим залишилося форматування і все до цього введені значення за допомогою списку. Це означає, що з поставленим завданням ми впоралися успішно: інструмент, який більше нам не потрібен, видалений, але результати його роботи залишилися цілими.
![Переход в окно проверки данных для отключения выпадающего списка в Microsoft Excel](https://cdn.daad.org.ua/images/7998/f8b8fe5adc9087d08064dfa40f2ffe4c.png)
![Удаление выпадающего списка через окно проверки данных в Microsoft Excel](https://cdn.daad.org.ua/images/7998/4913d897a58a86c5d4061dc1aa96d549.png)
![Закрытие окна проверки данных в Microsoft Excel](https://cdn.daad.org.ua/images/7998/7ace031e584a6ba97ed47464daaebc1c.png)
![Выделение ячейки в Microsoft Excel](https://cdn.daad.org.ua/images/7998/dbe66296ca05345d55f5a89209960b0c.png)
Як бачимо, спадаючий список може значно полегшити введення даних в таблиці, а також запобігти введенню некоректних значень. Це дозволить скоротити кількість помилок при заповненні таблиць. Якщо ж якесь значення потрібно додати додатково, то завжди можна провести процедуру редагування. Варіант редагування буде залежати від способу створення. Після заповнення таблиці можна видалити список, що випадає, хоча робити це і не обов'язково. Більшість користувачів вважають за краще його залишати навіть після закінчення роботи по заповненню таблиці даними.