Excel: Використовувати таблицю як розкривний список перевірки даних

Перевірка даних: Як зробити комірку, щоб вибрати значення зі спадного меню

Створення таблиці в MS Excel може дуже засмутити, якщо ви повторюєте одне і те ж введення знову і знову.

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

Давайте подивимося, як використовувати правило перевірки, функцію, яка робить це можливим.

У таблиці нижче наведено перелік витрат фізичної особи. Дата, вид витрат та вартість вводяться кожного разу, коли витрачаються гроші.

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

Рішення 1: Створіть список безпосереднім введенням

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

Виберіть Перевірка даних на вкладці Дані в Excel, вона може відображатися лише як невеликий значок.

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

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

Просто підтвердьте, натиснувши ОК, і ваш розкривний список перевірки буде створений для вибраних комірок.

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

Рішення 2: Виберіть діапазон існуючих значень

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

Найкращою практикою при створенні декількох розкривних списків перевірки у Microsoft Excel є наявність одного або декількох конкретних аркушів, що містять лише різні списки перевірки, щоб полегшити їх пошук та оновлення.

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

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

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

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

Встановіть записи перевірки даних на іншому аркуші

Стати Excel Pro: Приєднуйтесь до нашого курсу!

Підніміть свої навички від початківців до героя з нашим курсом Excel 365, розробленим для того, щоб зробити вас досвідченими лише на кількох сеансах.

Зареєструйтесь сюди

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

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

Потім клацніть у будь-якому місці стилізованої таблиці та введіть назву таблиці на вкладці оформлення таблиці.

Це дозволить вам посилатися на цю таблицю де завгодно, включаючи список перевірки даних, просто ввівши назву таблиці замість діапазону!

Як дозволити пусті клітинки у списку перевірки даних

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

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

Потім ви зможете очистити клітинку, виділивши її та видаливши вміст, не отримуючи повідомлення про помилку із зазначенням, що вміст комірок заборонено.

Як вимкнути порожні значення у списку перевірки даних

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

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

Повернувшись до випадаючого списку валідації даних, з’явиться помилка - і неможливо буде залишити клітинку пустою.

Як видалити розкривний список перевірки даних

Щоразу, коли ви закінчите з обмеженням діапазону дозволених значень у списку, виберіть будь-яку комірку в цьому списку.

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

Потім натисніть «Очистити все», і вибрану вами перевірку даних буде видалено з книги MS Excel!

VBA: Додати список перевірки

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

Діапазон ("a1"). Перевірка _. Змінити xlValidateList, xlValidAlertStop, "= $ B $ 1: $ B $ 5"

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