Клітинки

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

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

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

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

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

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

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

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

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

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

  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%, виберіть пункт Decimal у списку тип даних виберіть потрібний тип обмеження в полі , введіть значення Мінімальне, максимальне або певних як десяткове число, наприклад .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 Online.

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

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

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

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

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

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

×