Клітинки

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

Довідкові й навчальні матеріали, присвячені Excel
Клітинки
Клітинки
Застосування перевірки даних до клітинок

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

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

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

  1. Виберіть Data >Data Validation (Перевірка даних).

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

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

    • Ціле число , щоб обмежити клітинку прийняттям лише цілих чисел.

    • Decimal – дає змогу обмежити кількість десяткових чисел у клітинці.

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

    • Date – щоб клітинка приймала лише дату.

    • Time – щоб клітинка приймала лише час.

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

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

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

  4. Установіть інші обов'язкові значення на основі того, що ви вибрали для "Дозволити" та "Дані".

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

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

  7. Перейдіть на вкладку Оповіщення про помилку , щоб настроїти повідомлення про помилку та вибрати стиль.

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

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

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

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

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

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

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

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

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

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

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

Запитувати в користувачів припустимі записи

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

    Дія

    У спливаючому меню Style (Стиль ) виберіть

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

    Припинення

    Попереджати користувачів про неприпустимість даних і вимагати від них вибору "Так" або " Ні ", щоб указати, чи потрібно продовжити

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

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

    Важливо

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

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

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

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

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

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

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

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

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

Примітки.: 

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

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

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

Результат

Дії

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

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

  2. У списку Allow (Дозволити) виберіть Whole number (Увесь номер).

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

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

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

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

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

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

  2. У полі Allow (Дозволити ) виберіть Decimal (Десяткове значення).

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

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

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

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

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

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

  2. У полі Allow (Дозволити) виберіть Date (Дата).

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

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

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

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

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

  2. У полі Allow (Дозволити ) виберіть Time (Час).

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

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

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

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

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

  2. У полі Allow (Дозволити ) виберіть Text Length (Довжина тексту).

  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))

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

Порада.: Якщо ви власник малого бізнесу та шукаєте додаткові відомості про те, як налаштувати Microsoft 365, відвідайте сторінку Довідка та навчання для малого бізнесу.

Бажаєте отримати додаткову інформацію?

Створення розкривного списку

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

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

Facebook LinkedIn Електронна пошта

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

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

Виявити Спільнота

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

Переваги передплати на Microsoft 365

Навчальні матеріали з Microsoft 365

Захисний комплекс Microsoft

Центр спеціальних можливостей

Спільноти допомагають ставити запитання й відповідати на них, надавати відгуки та дізнаватися думки висококваліфікованих експертів.

Запитайте спільноту Microsoft

Спільнота Microsoft Tech

Оцінювачі Windows

Оцінювачі Microsoft 365