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

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

Завантажте наші приклади

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

Завантажити приклади перевірки даних Excel

Додавання перевірки даних до клітинки або діапазону

Примітка : Перші три кроки з цього розділу потрібно виконати, щоб додати перевірку даних будь-якого типу. Кроки 4–8 призначено для створення розкривного списку.

  1. Виділіть одну або кілька клітинок, які потрібно перевірити.

  2. На вкладці Дані в групі Знаряддя даних натисніть кнопку Перевірка даних.

    Параметр "Перевірка даних" у групі "Знаряддя даних" на вкладці "Дані"
  3. На вкладці Параметри в списку Тип даних виберіть пункт Список.

    Вкладка "Параметри" в діалоговому вікні "Перевірка даних"
  4. У полі Джерело введіть значення списку, розділені крапкою з комою. Наприклад:

    1. Щоб обмежити кількість відповідей (на запитання на кшталт "У вас є діти?") двома варіантами, введіть Так;Ні.

    2. Щоб обмежити оцінювання якості репутації постачальника трьома значеннями, введіть Низька;Середня;Висока.

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

      Практичні поради. Крім того, можна створити список значень, посилаючись на будь-який діапазон клітинок у книзі. Краще за все створити власний список і відформатувати його як таблицю Excel (на вкладці Основне в групі Стилі натисніть кнопку Формат таблиці й виберіть потрібний стиль таблиці). Після цього виберіть діапазон даних у таблиці даних, тобто ту частину таблиці, яка містить лише ваш список без заголовку (у цьому випадку "Відділ"), і дайте їй змістовне ім’я в полі "Ім’я" над стовпцем A.

      У полі "Ім’я" введіть змістовне ім’я для списку

    У полі перевірки даних Джерело додайте щойно визначене ім’я зі знаком рівності "=" на початку замість того, щоб вводити значення списку.

    Перед іменем таблиці введіть знак "="

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

    Примітка : Радимо розмістити такі списки на окремому аркуші (і приховати їх за потреби), щоб ніхто не міг їх змінити.

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

    Список припустимих значень, який відображається поруч із клітинкою
  6. Щоб указати спосіб обробки пустих (нульових) значень, установіть або зніміть прапорець Ігнорувати пусті клітинки.

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

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

Примітки : 

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

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

  • Видаліть правила перевірки даних. Виділіть клітинку або клітинки, що містять перевірку, яку потрібно видалити, послідовно виберіть елементи Дані > Перевірка даних і в діалоговому вікні "Перевірка даних" натисніть кнопку Очистити все, а потім – OK.

У наведеній нижче таблиці перелічено інші типи перевірки даних і описано способи їх додавання до аркушів.

Результат

Дії

Обмежити введення даних цілими числами в певних межах.

  1. Виконайте кроки 1–3, описані в розділі Додавання перевірки даних до клітинки або діапазону вище.

  2. У списку Тип даних виберіть пункт Ціле число.

  3. У списку Значення виберіть потрібний тип обмеження. Наприклад, щоб установити нижню та верхню межу, виберіть значення між.

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

    Діалогове вікно "Умова перевірки"

    Ви також можете ввести формулу, яка повертає числове значення.

    Уявімо, що ви перевіряєте дані у клітинці F1. Щоб у цій клітинці встановити значення мінімального обмеження вирахувань, у два рази більше за кількість дітей, у списку Значення виберіть пункт більше або дорівнює, а потім у поле Мінімум введіть формулу =2*F1.

Обмежити введення даних десятковими числами в певних межах.

  1. Виконайте кроки 1–3, описані в розділі Додавання перевірки даних до клітинки або діапазону вище.

  2. У списку Тип даних виберіть пункт Дійсне.

  3. У списку Значення виберіть потрібний тип обмеження. Наприклад, щоб установити нижню та верхню межу, виберіть значення між.

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

    Ви також можете ввести формулу, яка повертає числове значення. Наприклад, щоб у клітинці E1 установити значення максимального обмеження для комісій і бонусів у "6 %" від заробітної платні продавця, у списку Значення виберіть пункт менше або дорівнює, а потім у поле Максимум введіть формулу =E1*6%.

    Примітка : Щоб надати користувачам можливість вводити відсоткові значення, наприклад "20 %", у списку Тип даних виберіть пункт Дійсне, укажіть потрібний тип обмеження в полі Значення, введіть мінімальне, максимальне або конкретне значення як десяткове число, наприклад 0,2, а потім відобразіть клітинку з перевіркою даних у вигляді відсоткового значення. Щоб це зробити, клацінть клітинку, а потім на вкладці Основне у групі Число натисніть кнопку Відсотковий формат Зображення кнопки .

Обмежити введення даних датами в певному діапазоні.

  1. Виконайте кроки 1–3, описані в розділі Додавання перевірки даних до клітинки або діапазону вище.

  2. У списку Тип даних виберіть пункт Дата.

  3. У списку Значення виберіть потрібний тип обмеження. Наприклад, щоб надати можливість вводити дати після певного дня, виберіть пункт більше.

  4. Введіть припустимий діапазон дат початку й завершення або конкретну дату.

    Ви також можете ввести формулу, яка повертає значення дати. Наприклад, щоб настроїти часові рамки між сьогоднішньою датою та датою, яка настане через 3 дні, у полі Значення виберіть пункт між, у поле Дата початку введіть формулу =TODAY(), а в поле Дата завершення – формулу =TODAY()+3.

    Параметри умови перевірки, які обмежують введення дати певними часовими рамками

Обмежити введення даних часом на певному проміжку.

  1. Виконайте кроки 1–3, описані в розділі Додавання перевірки даних до клітинки або діапазону вище.

  2. У списку Тип даних виберіть пункт Час.

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

  4. Введіть час початку, завершення або конкретний час, щоб надати дозвіл. Щоб ввести певний час, використовуйте формат "гг:хх".

    Наприклад, у клітинці E2 вказано час початку (08:00), у клітинці F2 – час завершення (17:00), і вам потрібно обмежити час проведення наради цими часовими рамками. Для цього в полі Значення виберіть пункт між, у полі Час початку введіть =E2, а в полі Час завершення – =F2.

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

Обмежити введення даних текстом визначеної довжини.

  1. Виконайте кроки 1–3, описані в розділі Додавання перевірки даних до клітинки або діапазону вище.

  2. У списку Тип даних виберіть пункт Довжина тексту.

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

  4. У цьому випадку потрібно обмежити введення даних 25 символами, тому в полі Значення виберіть пункт менше або дорівнює, а в полі Максимум введіть 25.

    Приклад перевірки даних із текстом обмеженої довжини

Обчислити припустимі значення, беручи за основу вміст іншої клітинки.

  1. Виконайте кроки 1-3, описані в наведеному вище розділі Додавання перевірки даних до клітинки або діапазону. У списку Тип даних виберіть потрібний тип даних.

  2. У списку Значення виберіть потрібний тип обмеження.

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

    Наприклад, якщо потрібно, щоб користувачі могли вводити записи для рахунку тільки тоді, коли результат не перевищуватиме бюджет у клітинці E1, у списку Тип даних виберіть пункт Ціле число, у списку "Значення" виберіть пункт "менше або дорівнює", а в поле Максимум введіть формулу =E1.

    Параметри перевірки для обчислення на основі вмісту іншої клітинки

Примітка : Нижче наведено приклади, у яких встановлено параметр "Інший". У такому разі, щоб настроїти умови, потрібно записати формули. На поле "Значення" увагу можна не звертати, оскільки його вимкнуто, якщо вибрано параметр "Інший".

Щоб переконатися в тому, що

Введіть таку формулу

Клітинка, що містить код товару (C2), завжди починається зі стандартного префіксу ID- та складається принаймні з 10 символів (понад 9).

=AND(LEFT(C2; 3) ="ID-";LEN(C2) > 9)

Приклад 6. Формули для перевірки даних

Клітинка з іменем продукту (D2) містить лише текст.

=ISTEXT(D2)

Приклад 2. Формули для перевірки даних

Значення клітинки, що містить дані про день народження іншого користувача (B6), має бути більшим за кількість років, указаних у клітинці B4.

=IF(B6<=(TODAY()-(365*B4));TRUE;FALSE)

Приклад перевірки даних, у якому введення обмежено мінімальним віком

Усі дані в діапазоні клітинок A2:A10 містять унікальні значення.

=COUNTIF($A$2:$A$10;A2)=1

Приклад 4. Формули для перевірки даних

Примітка : Спочатку вам потрібно ввести формулу перевірки даних для клітинки A2, а потім скопіювати формулу клітинки A2 до діапазону A3:A10 так, щоб другий аргумент функції COUNTIF відповідав поточній клітинці. Тобто частинка A2)=1 зміниться на A3)=1, A4)=1 і так далі.

Додаткові відомості

Адреса електронної пошти, записана в клітинці B4, містить символ "@".

=ISUMBER(FIND("@";B4)

Приклад перевірки даних, яка дає можливість переконатися, що адреса електронної пошти містить символ "@"

  • Чому команду "Перевірка даних" не активовано на стрічці? Команда може бути недоступною з наведених нижче причин.

    • Можливо, таблицю Microsoft Excel пов’язано із сайтом SharePoint. Тоді в неї не можна додати правило перевірки даних. Щоб це зробити, потрібно видалити зв’язок між таблицею Excel і сайтом або перетворити таблицю на діапазон.

    • Можливо, зараз вводяться дані. Якщо в клітинку вводяться дані, команда "Перевірка даних" на вкладці Дані недоступна. Щоб завершити цей процес, натисніть клавішу Enter або Esc.

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

  • Чи можна змінити розмір шрифту? Ні, розмір шрифту фіксований. Щоб змінити розмір зображення, ви можете лише настроїти масштабування свого екрана в нижньому правому куті вікна Excel. Крім того, ви можете використовувати поле зі списком ActiveX. Див. статтю Додавання на аркуш списку або поля зі списком.

  • Чи можуть дані перевірки заповнюватись або вибиратись автоматично, коли я вводжу текст? Ні, така функція доступна, лише коли використовується поле зі списком ActiveX.

  • Чи можна вибрати кілька елементів у списку перевірки даних? Лише коли використовується поле зі списком або список ActiveX.

  • Чи можна вибрати елемент у списку перевірки даних і заповнити ним інший список? Так! Ця функція називається "залежною перевіркою даних". Докладні відомості про створення залежних розкривних списків див. в цій статті.

  • Як можна повністю видалити правила перевірки даних з аркуша? Скористайтеся елементами Перейти > Виділити. На вкладці Основне в групі Редагування натисніть кнопку Знайти й виділити (або натисніть клавішу F5 чи клавіші Ctrl+G на клавіатурі), а потім виберіть елементи Виділити > перевірка даних і встановіть перемикач усіх (щоб знайти всі клітинки з перевіркою даних) або цих самих (щоб виділити всі клітинки з певними атрибутами перевірки даних).

    Діалогове вікно "Виділення групи клітинок"

    Потім у діалоговому вікні перевірки даних (вкладка Дані > Перевірка даних) послідовно натисніть кнопки Очистити все й OK.

  • Чи можна змусити користувача ввести дані в клітинку з перевіркою даних? Ні, але за допомогою VBA (Visual Basic for Applications) можна перевіряти, чи введено дані за певних обставин, наприклад коли користувач зберігає або закриває книгу. Якщо він ще не ввів дані, ви можете не дозволити йому продовжити, доки він не зробить свій вибір.

  • Як можна розфарбувати клітинки на основі вибору в списку перевірки даних? Ви можете скористатись умовним форматуванням. У цьому випадку радимо використати параметр Форматувати лише клітинки, які містять.

    Параметр "Форматувати лише клітинки, які містять"
  • Як можна перевірити адресу електронної пошти? Ви можете перевірити, чи введені дані містять символ @ (Інший > Формула). У цьому випадку використовується формула =ISNUMBER(FIND("@";D2)). Функція FIND шукає символ @, а знайшовши його, повертає його порядковий номер положення в текстовому рядку та дозволяє введення. Не знайшовши цей символ, функція FIND повертає помилку та не дозволяє введення.

Маєте запитання щодо конкретних функцій?

Опублікуйте запитання на форумі спільноти Microsoft Excel

Допомога в удосконаленні програми Excel

Маєте пропозиції щодо того, як покращити наступну версію програми Excel? Тоді перегляньте розділи на веб-сайті Excel User Voice.

Див. також

Додаткові відомості про перевірку даних

Відео. Створення розкривних списків і керування ними

Додавання або вилучення елементів розкривного списку

Видалення розкривного списку

Отримуйте нові функції раніше за інших
Приєднайтеся до оцінювачів Office

Ця інформація корисна?

Дякуємо за ваш відгук!

Дякуємо за відгук! Схоже, вам може стати в нагоді допомога одного з наших спеціалістів служби підтримки Office, з яким ми вас можемо з’єднати.

×