Перейти до основного
Office
Клітинки

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

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

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

Ваш браузер не підтримує відео. Інсталюйте Microsoft Silverlight, Adobe Flash Player або Internet Explorer 9.

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

Завантажити приклад книги з усіма прикладами перевірки даних у цій статті

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

  2. Виберіть дані >перевірка даних.

    Перевірка даних

  3. На вкладці настройки в розділі дозволитивиберіть потрібний параметр.

    • Ціле число – щоб обмежити вміст, який приймає лише цілі числа.

    • Десяткове значення – щоб обмежити вміст, який приймає лише десяткові числа.

    • Список – вибір даних із розкривного списку.

    • Дата – щоб обмежити вміст, який приймає лише дату.

    • Час – щоб обмежити вміст, який приймає лише час.

    • Довжина тексту – щоб обмежити довжину тексту.

    • Настроювана – для настроюваної формули.

  4.  У розділі данівиберіть умову:

    • між

    • не між

    • дорівнює

    • не дорівнює

    • більше

    • менше

    • більше або дорівнює

    • менше або дорівнює

  5. На вкладці настройки в розділі дозволитивиберіть потрібний параметр.

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

  7. Установіть прапорець пропустити пустий прапорець, якщо потрібно пропустити Пусті пробіли.

  8. Якщо потрібно додати заголовок і повідомлення для правила, перейдіть на вкладку введення повідомлення та введіть назву та повідомлення.

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

  10. Натисніть кнопку OK.

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

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

Обмежити введення даних

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

  2. На вкладці дані натисніть кнопку перевірка даних > перевірка даних.

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

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

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

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

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

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

  2. На вкладці дані натисніть кнопку перевірка даних > перевірка даних.

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

  3. На вкладці Введіть повідомлення встановіть прапорець Відображати підказку, коли вибрано параметр "стільниковий ".

  4. У полі заголовок Введіть заголовок повідомлення.

  5. У полі введення повідомлення введіть повідомлення, яке потрібно відобразити.

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

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

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

  2. На вкладці дані натисніть кнопку перевірка даних > перевірка даних.

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

  3. На вкладці оповіщення про помилку в полі заголовок Введіть заголовок повідомлення.

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

  5. Виконайте одну з таких дій:

    Дія

    На панелі Стиль виберіть пункт

    Вимагати від користувачів усунути помилку, перш ніж продовжити

    Припинення

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

    Попередження

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

    Увага!

Обмежити введення даних

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

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

    Початок роботи

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

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

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

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

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

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

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

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

    Початок роботи

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

  3. На вкладці Введіть повідомлення встановіть прапорець Відображати підказку, коли вибрано параметр "стільниковий ".

  4. У полі заголовок Введіть заголовок повідомлення.

  5. У полі введення повідомлення введіть повідомлення, яке потрібно відобразити.

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

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

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

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

    Початок роботи

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

  3. На вкладці оповіщення про помилку в полі заголовок Введіть заголовок повідомлення.

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

  5. Виконайте одну з таких дій:

    Дія

    На панелі Стиль виберіть пункт

    Вимагати від користувачів усунути помилку, перш ніж продовжити

    Припинення

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

    Попередження

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

    Увага!

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

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

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

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

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

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

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

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

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

Примітки.: 

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

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

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

Результат

Дії

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

  1. Виконайте кроки 1-2 вище.

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

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

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

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

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

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

  1. Виконайте кроки 1-2 вище.

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

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

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

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

    Примітка.: Щоб дозволити користувачу вводити відсотки, наприклад 20%, виберіть пункт десяткове значення в полі дозволити , виберіть тип обмеження, яке потрібно використовувати в полі дані , введіть мінімальне, максимальне або певне значення як десяткове число, наприклад 2, а потім – Щоб відобразити у відсотках значення даних, виберіть її, а потім натисніть кнопку " відсотковий стиль " Зображення кнопки в групі " число " на вкладці " основне ".

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

  1. Виконайте кроки 1-2 вище.

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

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

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

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

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

  1. Виконайте кроки 1-2 вище.

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

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

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

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

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

  1. Виконайте кроки 1-2 вище.

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

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

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

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

  1. Виконайте кроки 1-2 вище.

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

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

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

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

Примітки.: 

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

  • Знімки екрана в цій статті взято в Excel 2016; але функціональні можливості однакові в Інтернет-версія Excel.

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

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

Клітинка, що містить код товару (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, містить символ "@".

= ISNUMBER (FIND ("@"; B4))

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

Потрібна додаткова допомога?

Ви завжди можете поставити запитання експерту в спільноті Tech (у розділі Excel), отримати підтримку в спільноті, що допомагає знайти відповіді на запитання, або запропонувати нову функцію чи вдосконалення на форумі Excel User Voice.

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

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

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

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

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

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

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

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

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

×