Робота з спадаючими списками в Microsoft Excel

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

Використання випадаючих списків

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

процедура створення

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

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

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

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

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

   Тепер у вікні перевірки даних в області «Джерело» потрібно встановити символ «=», а потім відразу ж після нього вписати то ім'я, яке ми присвоїли діапазону. Програма відразу ідентифікує зв'язок між найменуванням і масивом, і підтягне той перелік, який в ньому розташований.

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

   Для того, щоб перетворити діапазон в «розумну» таблицю, виділяємо його і пересуваємося у вкладку «Головна». Там клацають по кнопці «Форматувати як таблицю», яка розміщена на стрічці в блоці «Стилі». Відкривається велика група стилів. На функціональність таблиці вибір конкретного стилю ніяк не впливає, а тому вибираємо будь-який з них.

   Після цього відкривається невелике віконце, в якому міститься адреса виділеного масиву. Якщо виділення було виконано вірно, то тут нічого змінювати не потрібно. Так як у нашого діапазону немає заголовків, то у пункту «Таблиця із заголовками» галочки бути не повинно. Хоча саме в вашому випадку, можливо, заголовок і буде застосовуватися. Так що нам залишається просто натиснути на кнопку «OK».

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

   Якщо перелік розміщений в іншій книзі, то для коректного його відображення потрібно застосувати функцію ДВССИЛ. Зазначений оператор призначений для того, щоб формувати «суперабсолютние» посилання на елементи листа в текстовому вигляді. Власне при цьому процедура буде виконуватися практично точно така ж, як і в раніше описуваних випадках, тільки в області «Джерело» після символу «=» слід вказати найменування оператора - «ДВССИЛ». Після цього в дужках як аргумент цієї функції повинен бути вказана адреса діапазону, включаючи найменування книги і листа. Власне, як показано на зображенні нижче.

3. На цьому ми б могли і закінчити процедуру, натиснувши на кнопку «OK» у віконці перевірки даних, але при бажанні можна вдосконалити форму. Переходимо в розділ «Повідомлення для введення» віконця перевірки даних. Тут в області «Повідомлення» можна написати текст, який будуть бачити користувачі, наводячи курсор на елемент листа з спадаючим списком. Записуємо то повідомлення, яке вважаємо за потрібне.
4. Далі подорожуємо у розділ «Повідомлення про помилку». Тут в області «Повідомлення» можна ввести текст, який буде спостерігати користувач при спробі ввести некоректні дані, тобто, будь-які дані, відсутні в випадаючому списку. В області «Вид» можна вибрати значок, яким буде супроводжуватися попередження. Вводимо текст повідомлення і клацають по «OK».

урок: Як зробити випадаючий список в Ексель

виконання операцій

Тепер давайте розберемося, як працювати з тим інструментом, який ми вище створили.

1. Якщо ми встановимо курсор на будь-який елемент листа, до якого був застосований спадаючий список, то побачимо інформаційне повідомлення, введене нами раніше в вікні перевірки даних. Крім того, справа від осередку з'явиться піктограма у вигляді трикутника. Саме вона служить для доступу до вибору облікових елементів. Клацають по даному трикутнику.
2. Після кліка по ньому меню з облікових об'єктів буде відкрито. У ньому розташовані всі елементи, які були раніше внесені через вікно перевірки даних. Вибираємо той варіант, який вважаємо за потрібне.
3. Обраний варіант відобразиться в осередку.
4. Якщо ж ми спробуємо ввести в клітинку будь-яке значення, яке відсутнє в переліку, то дана дія заблокується. При цьому, якщо ви вносили повідомлення попередження в вікно перевірки даних, то воно відобразитися на екрані. Потрібно в вікні попередження натиснути на кнопку «Скасування» і з наступного спроби ввести коректні дані.

Таким способом при необхідності заповнюємо всю таблицю.

Додавання нового елемента

Але що робити, якщо потрібно все-таки додати новий елемент? Дії тут залежать від того, як саме ви сформували перелік у вікні перевірки даних: введений вручну або підтягується з табличного масиву.

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

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

2. Запускається віконце, де слід зробити вибір об'єкта вставки. Вибираємо варіант «Рядок» і тиснемо на кнопку «OK».
3. Отже, порожній рядок додана.
4. Вписуємо в неї те значення, яке бажаємо, щоб відобразилося в випадаючому списку.
5. Після цього повертаємося до того табличному масиву, в якому розміщений розкривається перелік. Клікнувши по трикутнику, праворуч від будь-якого елементу масиву, ми бачимо, що до вже наявних обліковим елементам було додано потрібне нам значення. Тепер при бажанні його можна вибрати, щоб вставити в елемент таблиці.

Але що робити, якщо перелік значень підтягується не з окремої таблиці, а був внесений вручну? Щоб додати елемент в цьому випадку теж є свій алгоритм дій.

1. Виділяємо весь табличний діапазон, в елементах якого розміщений випадаючий перелік. Переходимо у вкладку «Дані» і знову натискаємо на кнопку «Перевірка даних» в групі «Робота з даними».
2. Запускається вікно перевірки введених даних. Переміщаємося в розділ «Параметри». Як бачимо, всі налаштування тут точно такі ж, як ми їх виставили раніше. Нас в даному випадку буде цікавити область «Джерело». Додаємо туди до вже має переліком через крапку з комою (;) то значення або значення, які хочемо побачити у випадаючому списку. Після додавання клацають по «OK».
3. Тепер, якщо ми відкриємо випадає перелік в табличному масиві, то побачимо там доданий значення.

видалення елемента

Видалення облікового елементу проводиться по точно таким же алгоритмом, що і додавання.

1. Якщо дані підтягуються з табличного масиву, то тоді переходимо до цієї таблиці і клацають правою кнопкою миші по комірці, де розташовано значення, яке слід видалити. У контекстному меню зупиняємо вибір на варіанті «Видалити ...».
2. Відкривається вікно видалення осередків практично аналогічне тому, яке ми бачили при їх додаванні. Тут знову встановлюємо перемикач в позицію «Рядок» і клацають по «OK».
3. Рядок з табличного масиву, як бачимо, видалена.
4. Тепер повертаємося до тієї таблиці, де знаходяться осередки з випадає переліком. Клацають по трикутнику праворуч від будь-якого елементу. У списку, що розкрився бачимо, що віддалений елемент відсутній.

Що ж робити, якщо значення додавалися в вікно перевірки даних вручну, а не за допомогою додаткової таблиці?

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

повне видалення

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

1. Виділяємо весь масив, де розташований розкривається перелік. Пересуваємося у вкладку «Головна». Клацаємо по іконі «Очистити», яка розміщується на стрічці в блоці «Редагування». У меню, вибираємо позицію «Очистити все».
2. При виборі даної дії в виділених елементах листа будуть видалені всі значення, очищено форматування, а крім того, досягнута головна мета поставленого завдання: розкривається перелік буде вилучений і тепер в осередку можна вводити будь-які значення вручну.

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

1. Виділяємо діапазон порожніх клітинок, який рівнозначний діапазону елементів масиву з розкривається переліком. Пересуваємося у вкладку «Головна» і там натискаємо на іконку «Копіювати», яка локалізується на стрічці в області «Буфер обміну».

   Також замість цього дії можна клікнути по позначеному фрагменту правою кнопкою мишки і зупинитися на варіанті «Копіювати».

   Ще простіше відразу після виділення застосувати набір кнопок Ctrl + C.

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

   Другий варіант дій - це клацнути по виділенню правою кнопкою мишки і зупинити вибір на варіанті «Вставити» в групі «Параметри вставки».

   Нарешті, є можливість просто позначити потрібні комірки і набрати поєднання кнопок Ctrl + V.

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

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

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

1. Виділяємо весь фрагмент, в якому розташовані елементи з розкривається переліком. Пересуваємося у вкладку «Дані» і клацають по значку «Перевірка даних», який, як ми пам'ятаємо, розміщений на стрічці в групі «Робота з даними».
2. Відкривається вже добре знайоме нам вікно перевірки введених даних. Перебуваючи в будь-якому розділі зазначеного інструменту, нам потрібно зробити єдине дію - натиснути на кнопку «Очистити все». Вона розташовується в нижньому лівому кутку вікна.
3. Після цього вікно перевірки даних можна закривати, натиснувши на стандартну кнопку закриття в його верхньому правому куті у вигляді хрестика або на кнопку «OK» в нижній частині вікна.
4. Потім виділяємо будь-яку з осередків, в якій раніше був розміщений розкривається перелік. Як бачимо, тепер немає ні підказки при виділенні елемента, ні трикутника для виклику списку праворуч від осередку. Але при цьому недоторканим залишилося форматування і все до цього введені значення за допомогою списку. Це означає, що з поставленим завданням ми впоралися успішно: інструмент, який більше нам не потрібен, видалений, але результати його роботи залишилися цілими.

Як бачимо, спадаючий список може значно полегшити введення даних в таблиці, а також запобігти введенню некоректних значень. Це дозволить скоротити кількість помилок при заповненні таблиць. Если же какое-нибудь значение нужно добавить дополнительно, то всегда можно провести процедуру редактирования. Вариант редактирования будет зависеть от способа создания. После заполнения таблицы можно удалить выпадающий список, хотя делать это и не обязательно. Большинство пользователей предпочитают его оставлять даже после окончания работы по заполнению таблицы данными.

• ← Розворот таблиці в Microsoft Excel
• Створення посилань в Microsoft Excel →