Перевірка даних Excel: додавання, використання, копіювання та видалення перевірки даних у Excel

1. Що таке перевірка даних у Excel?
2. Як додати перевірку даних у Excel?
3. Основні приклади перевірки даних
4. Розширені користувацькі правила перевірки данихn

Перевірка даних Excel: додавання, використання, копіювання та видалення перевірки даних у Excel

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

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

Зміст:

1. Що таке перевірка даних у Excel?

2. Як додати перевірку даних у Excel?

3. Основні приклади перевірки даних

3.1 Перевірка даних для цілих чисел і десяткових дробів
3.2 Перевірка даних за датою та часом
3.3 Перевірка даних щодо довжини тексту
3.4 Список перевірки даних (розкривний список)

4. Розширені спеціальні правила перевірки даних

4.1 Перевірка даних дозволяє лише номери або тексти
- Дозволяє вводити лише цифри за допомогою функції перевірки даних
- Дозволяє вводити лише текстові рядки за допомогою функції перевірки даних

5. Як відредагувати перевірку даних у Excel?

6. Як знайти та виділити комірки з валідацією даних у Excel?

7. Як скопіювати правило перевірки даних в інші комірки?

8. Як за допомогою перевірки даних обвести недійсні записи в Excel?

9. Як видалити перевірку даних у Excel?

Видаліть перевірку даних у вибраному діапазоні за допомогою функції перевірки даних
Видаліть перевірку даних у вибраному діапазоні за допомогою зручної функції
Видаліть перевірку даних з усіх аркушів з кодом VBA

1. Що таке перевірка даних у Excel?

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

Деякі основні способи використання функції перевірки даних:

1. Будь -яке значення: перевірка не виконується, ви можете ввести що -небудь у зазначені клітинки.
2. Цілісна цінність: допускаються лише цілі числа.
3. Десяткові: дозволяє вводити цілі числа, а також десяткові дроби.
4. Список: дозволено вводити або вибирати лише значення з попередньо визначеного списку. Значення відображаються у випадаючому списку.
5. Дата: дозволені лише дати.
6. Час: дозволено лише час.
7. Довжина тексту: дозволяють вводити лише задану довжину тексту.
8. На замовлення: створити спеціальні правила формул для перевірки введення даних користувачами.

2. Як додати перевірку даних у Excel?

У робочому аркуші Excel можна додати перевірку даних, виконавши такі дії:

1. Виберіть список клітинок, де потрібно встановити перевірку даних, а потім клацніть дані > Перевірка достовірності даних > Перевірка достовірності даних, див. скріншот:

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

Цінності: Введіть номери безпосередньо в полях критеріїв;
Посилання на клітинку: Посилання на клітинку на аркуші чи іншому аркуші;
Формули: Створюйте складніші формули як умови.

Як приклад, я буду створювати правило, яке дозволить вводити лише цілі числа від 100 до 1000, тут встановіть критерії, як показано нижче на скріншоті:

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

3.1) Додати вхідне повідомлення (необов’язково):

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

Перейти до Вхідна повідомлення та виконайте наведені нижче дії.

Перевірте Показувати вхідне повідомлення, коли вибрано комірку варіант;
Введіть потрібну назву та повідомлення -нагадування у відповідні поля;
Натисніть OK щоб закрити це діалогове вікно.

Тепер, коли ви вибираєте перевірену клітинку, вікно повідомлення відображатиметься так:

3.2) Створення значущих повідомлень про помилки (необов’язково):

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

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

Перевірте Показувати попередження про помилку після введення недійсних даних варіант;
У стиль у спадному списку виберіть потрібний потрібний тип сповіщення:
- Зупинити (за замовчуванням): Цей тип попередження забороняє користувачам вводити недійсні дані.
- попередження: Попереджає користувачів про недійсні дані, але не перешкоджає їх введенню.
- Інформація: Інформує користувачів лише про недійсний запис даних.
Введіть потрібний заголовок та попередження у відповідні поля;
Натисніть OK закрити діалогове вікно.

І тепер, при введенні недійсного значення, вікно повідомлення з повідомленням вискочить, як показано на скріншоті нижче:

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

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

Інформація варіант: Натисніть OK для введення недійсного запису або Скасувати щоб видалити запис.

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

3. Основні приклади перевірки даних

Під час використання цієї функції перевірки даних передбачено 8 вбудованих опцій для встановлення перевірки даних. Наприклад: будь -яке значення, цілі числа та десяткові дроби, дата та час, список, довжина тексту та спеціальна формула. У цьому розділі ми обговоримо, як використовувати деякі вбудовані параметри в Excel?

3.1 Перевірка даних для цілих чисел і десяткових дробів

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

2, в Перевірка достовірності даних у діалоговому вікні під Налаштування , виконайте такі операції:

Виберіть відповідний пункт Ціле число or Десятковий в дозволяти випадаюче вікно.
А потім виберіть один із критеріїв, які вам потрібні у дані box (У цьому прикладі я вибираю між варіант).
Tips : Критерії містять: між, не між, рівним, не рівним, більшим, меншим, більшим або рівним, меншим або рівним.
Потім введіть мінімальний та Максимальний потрібні вам значення (я хочу числа від 0 до 1).
Нарешті клацніть OK кнопки.

3. Тепер у вибрані клітинки дозволяється вводити лише цілі числа від 0 до 100.

3.2 Перевірка даних за датою та часом

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

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

2, в Перевірка достовірності даних у діалоговому вікні під Налаштування , виконайте такі операції:

Виберіть відповідний пункт Дата or Time в дозволяти випадаюче вікно.
А потім виберіть один із критеріїв, які вам потрібні у дані коробка (тут я вибираю більше варіант).
Tips : Критерії містять: між, не між, рівним, не рівним, більшим, меншим, більшим або рівним, меншим або рівним.
Потім введіть Початок вам потрібно (я хочу, щоб дати перевищували 8).
Нарешті клацніть OK кнопки.

3. Тепер у вибрані клітинки дозволено вводити лише дати, більші за 8.

3.3 Перевірка даних щодо довжини тексту

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

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

2, в Перевірка достовірності даних у діалоговому вікні під Налаштування , виконайте такі операції:

Select Довжина тексту від дозволяти випадаюче вікно.
А потім виберіть один із критеріїв, які вам потрібні у дані box (У цьому прикладі я вибираю менше варіант).
Tips : Критерії містять: між, не між, рівним, не рівним, більшим, меншим, більшим або рівним, меншим або рівним.
Потім введіть Максимальний кількість, яку потрібно обмежити (я хочу, щоб довжина тексту не перевищувала 10 символів).
Нарешті клацніть OK кнопки.

3. Тепер вибрані клітинки дозволяють вводити текстовий рядок менше 10 символів.

3.4 Список перевірки даних (розкривний список)

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

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

2, в Перевірка достовірності даних у діалоговому вікні під Налаштування , виконайте такі операції:

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

3. Тепер у клітинках було створено випадаючий список, як показано на скріншоті нижче:

Натисніть, щоб дізнатися більш детальну інформацію випадаючого списку…

4. Розширені спеціальні правила перевірки даних

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

4.1 Перевірка даних дозволяє лише номери або тексти

Дозволяє вводити лише цифри за допомогою функції перевірки даних

Щоб дозволити лише числа в діапазоні клітинок, виконайте наведені нижче дії.

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

2. Клацання дані > Перевірка достовірності даних > Перевірка достовірності даних, в вискочив Перевірка достовірності даних у діалоговому вікні під Налаштування вкладку, виконайте такі дії:

Select виготовлений на замовлення від дозволяти випадаючий список.
Потім введіть цю формулу: = ISNUMBER (A2) в Formula текстове вікно. (A2 це перша клітина вибраного діапазону, яку потрібно обмежити)
Натисніть OK , щоб закрити це діалогове вікно.

3. Відтепер у вибрані клітинки можна вводити лише цифри.

примітки: Це ІЗНОМ Функція дозволяє будь -які числові значення у перевірених клітинках, включаючи цілі числа, десяткові дроби, дроби, дати та час.

Дозволяє вводити лише текстові рядки за допомогою функції перевірки даних

Щоб обмежити записи клітинок лише текстом, можна скористатися Перевірка достовірності даних функція зі спеціальною формулою на основі ІСТЕКСТ функцію, будь ласка, зробіть так:

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

Select виготовлений на замовлення від дозволяти випадаючий список.
Потім введіть цю формулу: = ISTEXT (A2) в Formula текстове вікно. (A2 це перша клітина вибраного діапазону, яку потрібно обмежити)
Натисніть OK , щоб закрити це діалогове вікно.

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

4.2 Перевірка даних допускає лише буквено -цифрові значення

Для деяких цілей потрібно просто дозволити введення алфавітів та числових значень, але обмежити спеціальні символи, такі як ~,%, $, пробіл тощо.

Дозволити лише буквено -цифрові значення з функцією перевірки даних

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

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

Select виготовлений на замовлення від дозволяти випадаючий список.
Потім введіть формулу нижче Formula текстове вікно.
=IF(A2="",TRUE,IF(ISERROR(SUMPRODUCT(SEARCH(MID(A2,ROW(INDIRECT("1:"&LEN(A2))),1),"0123456789abcdefghijklmnopqrstuvwxyz"))),FALSE,TRUE))
Натисніть OK , щоб закрити це діалогове вікно.

примітки: У наведених формулах, A2 - це перша клітина вибраного діапазону, яку потрібно обмежити.

3. Тепер дозволено вводити лише алфавіти та числові значення, а спеціальні символи будуть обмежені під час введення, як показано на знімку екрана нижче:

Дозволити лише буквено -цифрові значення з дивовижною функцією

Можливо, вищезгадана формула є складною для нас, щоб зрозуміти та запам’ятати, тут я представлю зручну функцію - Запобігання друку of Kutools для Excel, з цією функцією, ви можете швидко вирішити цю роботу з легкістю.

після установки Kutools для Excel, будь ласка, зробіть так:

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

2. Потім натисніть кнопку Кутулс > Запобігання друку > Запобігання друку, див. скріншот:

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

4. Потім натисніть кнопку Ok і в наступних вікнах запитів натисніть Так > OK щоб завершити операцію. Тепер у вибраних клітинках дозволені лише алфавіти та числові значення, див. Скріншот:

4.3 Перевірка даних дозволяє текстам починати або закінчувати певними символами

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

Дозволити тексти починати або закінчувати певними символами лише з однією умовою

Наприклад, я хочу, щоб тексти починалися або закінчувалися на “CN” під час введення текстових рядків у певні клітинки, будь ласка, зробіть так:

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

2. Потім натисніть кнопку дані > Перевірка достовірності даних > Перевірка достовірності даних, в вискочив Перевірка достовірності даних у діалоговому вікні під Налаштування вкладку, виконайте такі дії:

Select виготовлений на замовлення від дозволяти випадаючий список.
Потім введіть формулу нижче Formula текстове вікно.
Begin with: =EXACT(LEFT(A2,2),"CN")
End with: =EXACT(RIGHT(A2,2),"CN")
Натисніть OK , щоб закрити це діалогове вікно.

примітки: У наведених формулах, A2 - це перша клітинка вибраного діапазону, число 2 кількість вказаних вами символів, CN - це текст, який потрібно розпочати або закінчити.

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

Порада: Наведені вище формули чутливі до регістру. Якщо вам не потрібен регістр, застосуйте наведені нижче формули CONTIF:

Begin with (non case sensitive): =COUNTIF(A2,"CN*")
End with (non case sensitive): =COUNTIF(A2,"*CN")

примітки: Зірочка * - це підстановка, яка відповідає одному або кільком символам.

Дозволити тексти починати або закінчувати певними символами з кількома критеріями (АБО логіка)

Наприклад, якщо ви хочете, щоб тексти починалися або закінчувалися на "CN" або "UK", як показано на скріншоті нижче, вам потрібно додати інший екземпляр EXACT за допомогою знака плюс (+). Виконайте такі дії:

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

Select виготовлений на замовлення від дозволяти випадаючий список.
Потім введіть формулу нижче Formula текстове вікно.
Begin with: =EXACT(LEFT(A2,2),"CN")+EXACT(LEFT(A2,2),"UK")
End with: =EXACT(RIGHT(A2,2),"CN")+EXACT(RIGHT(A2,2),"UK")
Натисніть OK , щоб закрити це діалогове вікно.

примітки: У наведених формулах, A2 - це перша клітинка вибраного діапазону, число 2 кількість вказаних вами символів, CN та UK це конкретні тексти, з яких ви хочете почати або закінчити.

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

Порада: Щоб не враховувати регістр, застосуйте наведені нижче формули CONTIF:

Begin with (non case sensitive): =COUNTIF(A2,"CN*")+COUNTIF(A2,"UK*")
End with (non case sensitive): =COUNTIF(A2,"*CN")+COUNTIF(A2,"*UK")

примітки: Зірочка * - це підстановка, яка відповідає одному або кільком символам.

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

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

Дозволені записи повинні містити один або один із багатьох конкретних текстів

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

Наприклад, щоб дозволити записи, які містять певний текстовий рядок, усі введені значення повинні містити текст “KTE”, як показано на скріншоті нижче, ви можете застосувати перевірку даних за допомогою спеціальної формули на основі функцій FIND та ISNUMBER. Будь ласка, зробіть так:

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

Select виготовлений на замовлення зі спадного списку Дозволити.
Потім введіть одну з наведених нижче формул у Formula текстове вікно.
=ISNUMBER(FIND("KTE",A2)) (Case sensitive)
=ISNUMBER(SEARCH("KTE",A2)) (Non case sensitive)
Натисніть OK , щоб закрити це діалогове вікно.

примітки: У наведених формулах, A2 - це перша клітинка вибраного діапазону, текст КТЕ - це текстовий рядок, який мають містити записи.

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

Дозволені записи повинні містити один із багатьох конкретних текстів

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

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

Select виготовлений на замовлення від дозволяти випадаючий список.
Потім введіть потрібну формулу нижче Formula текстове вікно.
=SUMPRODUCT(--ISNUMBER(FIND($C$2:$C$4,A2)))>0 (Case sensitive)
=SUMPRODUCT(--ISNUMBER(SEARCH($C$2:$C$4,A2)))>0 (Non case sensitive)
А потім натисніть OK закрити діалогове вікно.

примітки: У наведених формулах, A2 - перша клітина вибраного діапазону, C2: C4 - це список значень, яким потрібно дозволити записи, що містять будь -яке з них.

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

Дозволені записи не повинні містити один або один із багатьох конкретних текстів

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

Для перевірки записів не повинно міститися певного тексту, наприклад, щоб дозволити значення, які не повинні містити текст “KTE” у комірці, можна використовувати функції ISERROR та FIND для створення правила перевірки даних. Будь ласка, зробіть так:

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

Select виготовлений на замовлення від дозволяти випадаючий список.
Потім введіть одну з наведених нижче формул у Formula текстове вікно.
=ISERROR(FIND("KTE",A2)) (Case sensitive)
=ISERROR(SEARCH("KTE",A2)) (Non case sensitive)
Натисніть OK , щоб закрити це діалогове вікно.

примітки: У наведених формулах, A2 - це перша клітинка вибраного діапазону, текст КТЕ - це текстовий рядок, який записи не повинні містити.

3. Тепер буде заборонено вводити записи, які містять певний текст.

Дозволені записи не повинні містити один із багатьох конкретних текстів

Щоб запобігти введенню одного зі багатьох текстових рядків у списку, як показано на скріншоті нижче, потрібно зробити наступне:

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

Select виготовлений на замовлення від дозволяти випадаючий список.
Потім введіть формулу нижче Formula текстове вікно.
=SUMPRODUCT(--ISNUMBER(FIND($C$2:$C$4,A2)))=0 (Case sensitive)
=SUMPRODUCT(--ISNUMBER(SEARCH($C$2:$C$4,A2)))=0 (Non case sensitive)
А потім натисніть OK закрити діалогове вікно.

примітки: У наведених формулах, A2 - перша клітина вибраного діапазону, C2: C4 - це список значень, які потрібно запобігти, якщо записи містять одне з них.

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

4.5 Перевірка даних дозволяє лише унікальні значення

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

Дозволити лише унікальні значення з функцією перевірки даних

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

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

Select виготовлений на замовлення від дозволяти випадаючий список.
Потім введіть формулу нижче Formula текстове вікно.
=COUNTIF($A$2:$A$9,A2)=1
Натисніть OK , щоб закрити це діалогове вікно.

примітки: У наведеній вище формулі, A2: A9 - це діапазон клітинок, для якого потрібно дозволити лише унікальні значення, і A2 - це перша клітина вибраного діапазону.

3. Тепер дозволяється вводити лише унікальні значення, а при введенні повторюваних даних з’явиться попереджувальне повідомлення, див. Скріншот:

Дозволити лише унікальні значення з кодом VBA

Наступний код VBA також може допомогти вам запобігти введенню повторюваних значень, зробіть так:

1. Клацніть правою кнопкою миші вкладку аркуша, для якої потрібно дозволити лише унікальні значення, і виберіть Переглянути код з контекстного меню, що з’явиться Microsoft Visual Basic для додатків вікно, скопіюйте та вставте наступний код у порожній модуль:

Код VBA: дозволити унікальні значення лише в діапазоні клітинок:

Private Sub Worksheet_Change(ByVal Target As Range)
'Updateby Extendoffice
  Dim xRg As Range, iLong, fLong As Long
  If Not Intersect(Target, Me.[A1:A100]) Is Nothing Then
     Application.EnableEvents = False
     For Each xRg In Target
     With xRg
         If (.Value <> "") Then
          If WorksheetFunction.CountIf(Me.[A:A], .Value) > 1 Then
            iLong = .Interior.ColorIndex
            fLong = .Font.ColorIndex
            .Interior.ColorIndex = 3
            .Font.ColorIndex = 6
            MsgBox "Duplicate Entry !", vbCritical, "Kutools for Excel"
            .ClearContents
            .Interior.ColorIndex = iLong
            .Font.ColorIndex = fLong
          End If
       End If
     End With
     Next
     Application.EnableEvents = True
  End If
End Sub

примітки: У наведеному вище коді A1: A100 та A: A - це клітинки у стовпці, які потрібно запобігти повторенню, будь ласка, змініть їх відповідно до ваших потреб.

2. Потім збережіть і закрийте цей код, тепер, при введенні повторюваного значення в клітинку A1: A100, з'являється вікно з попередженням, як показано на скріншоті нижче:

Дозволити лише унікальні значення зі зручною функцією

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

після установки Kutools для Excel, будь ласка, зробіть так:

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

2. Потім натисніть кнопку Кутулс > Запобігання друку > Запобігти дублюванню, див. скріншот:

3. І з’явиться попереджувальне повідомлення, яке нагадуватиме, що перевірку даних буде видалено, якщо застосувати цю функцію, натисніть Так і в наступному вікні запиту натисніть OK, див. скріншоти:

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

4.6 Перевірка даних дозволяє лише великі / малі літери / правильні регістри

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

1. Виберіть діапазон клітинок, для якого потрібно вводити лише регістр, малий або належний текст.

Select виготовлений на замовлення від дозволяти випадаючий список.
А потім введіть одну з наведених нижче формул у Formula текстове вікно.
=AND(EXACT(A2,UPPER(A2)),ISTEXT(A2)) (only allow uppercase text)
=AND(EXACT(A2,LOWER(A2)),ISTEXT(A2)) (only allow lowercase text)
=AND(EXACT(A2,PROPER(A2)),ISTEXT(A2)) (only allow proper case text)
Натисніть OK , щоб закрити це діалогове вікно.

примітки: У наведеній вище формулі, A2 - це перша клітинка стовпця, яку потрібно використати.

3. Тепер прийматимуться лише ті записи, які відповідають створеному вами правилу.

4.7 Перевірка даних дозволяє значення, які існують / не існують в іншому списку

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

Наприклад, я хочу, щоб у діапазон клітинок, як показано на скріншоті нижче, вводилися лише значення в діапазоні С2: С4, щоб вирішити цю роботу, будь ласка, зробіть так:

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

Select виготовлений на замовлення від дозволяти випадаючий список.
А потім введіть одну з наведених нижче формул у Formula текстове вікно.
=COUNTIF($C$2:$C$4,A2)>0 (only allow values exist in another column)
=COUNTIF($C$2:$C$4,A2)=0 (prevent values exist in another column)
Натисніть OK , щоб закрити це діалогове вікно.

примітки: У наведеній вище формулі, A2 - це перша клітинка стовпця, яку потрібно використати, C2: C4 - це список значень, які потрібно запобігти або дозволити, якщо записи є одним із них.

3. Тепер можна вводити лише записи, створені вами створеним правилом, інші будуть заблоковані.

4.8 Перевірка даних змушує вводити лише формат номера телефону

Коли ви вводите інформацію про співробітників вашої компанії, в одному стовпці потрібно ввести номер телефону, щоб забезпечити швидке та точне введення телефонних номерів, у цьому випадку ви можете встановити перевірку даних для телефонних номерів. Наприклад, я просто хочу, щоб номер телефону у цьому форматі (123) 456-7890 дозволяв вводити на аркуші, у цьому розділі будуть представлені два швидкі хитрощі для вирішення цього завдання.

Форсувати лише формат номера телефону за допомогою функції перевірки даних

Щоб дозволити введення лише певного формату номера телефону, виконайте наведені нижче дії.

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

2, в Формат ячеек у діалоговому вікні під Номер вкладка, виберіть виготовлений на замовлення в лівій Категорія списку, а потім введіть потрібний формат телефонного номера у текстове поле Тип, наприклад, я буду використовувати це (###) ### - #### формат, див. знімок екрана:

3. Потім натисніть кнопку OK закрити діалогове вікно.

4. Після форматування комірок перейдіть до вибору клітинок, а потім відкрийте Перевірка достовірності даних діалогове вікно натисканням дані > Перевірка достовірності даних > Перевірка достовірності даних, у спливаючому діалоговому вікні під Налаштування , виконайте такі операції:

Select виготовлений на замовлення від дозволяти випадаючий список.
Потім введіть цю формулу = І (ІЗНОМ (A2), LEN (A2) = 10) у текстове поле Формула.
Натисніть OK , щоб закрити це діалогове вікно.

примітки: У наведеній вище формулі, A2 - це перша клітинка стовпця, у якій потрібно перевірити номер телефону.

5. Тепер, коли ви вводите 10-значний номер, він буде автоматично конвертований у певний формат номера телефону, як вам потрібно, перегляньте скріншоти:

примітки: Якщо введене число не містить 10 цифр, з’явиться вікно з попередженням, яке нагадує вам, перегляньте знімок екрана:

Примусово використовувати лише формат номера телефону з корисною функцією

Kutools для ExcelАвтора Перевірте номер телефону Ця функція також може допомогти вам змусити вводити лише формат номера телефону лише кількома клацаннями миші.

після установки Kutools для Excel, будь ласка, зробіть так:

1. Виберіть список клітинок, які дозволяють лише певний номер телефону, а потім натисніть Кутулс > Запобігання друку > Перевірте номер телефону, див. скріншот:

2, в Телефон діалогове вікно, виберіть потрібний формат номера телефону або ви можете створити власне форматування, натиснувши кнопку додавати , див. знімок екрана:

3. Після вибору або налаштування форматування номера телефону натисніть OK, тепер можна ввести лише номер телефону з певним форматуванням, інакше з’явиться попереджувальне повідомлення, яке нагадує вам, перегляньте скріншот:

4.9 Перевірка даних примушує вводити лише адреси електронної пошти

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

Форсувати лише формат електронних адрес із функцією перевірки даних

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

1. Виберіть клітинки, для яких потрібно ввести лише адреси електронної пошти, а потім клацніть дані > Перевірка достовірності даних > Перевірка достовірності даних.

2. В вискочив Перевірка достовірності даних у діалоговому вікні під Налаштування , виконайте такі операції:

Select виготовлений на замовлення від дозволяти випадаючий список.
Потім введіть цю формулу = ІЗМІР (ЗБІГ ("*@*.?*", A2,0)) в Formula текстове вікно.
Натисніть OK , щоб закрити це діалогове вікно.

примітки: У наведеній вище формулі, A2 - це перша клітинка стовпця, яку потрібно використати.

3. Тепер, якщо введений текст не є форматом електронної адреси, з'явиться вікно з попередженням, яке нагадує вам, перегляньте знімок екрана:

Примусово використовувати лише формат електронних адрес із зручною функцією

Kutools для Excel підтримує дивовижну функцію - Перевірте електронну адресуза допомогою цієї утиліти можна запобігти недійсним адресам електронної пошти одним натисканням кнопки.

після установки Kutools для Excel, будь ласка, виконайте наступне:

1. Виберіть клітинки, для яких дозволено введення лише електронних адрес, а потім клацніть Кутулс > Запобігання друку > Перевірте електронну адресу. Дивіться знімок екрана:

2. І тоді можна вводити лише форматування електронної адреси, інакше з’явиться вікно з попередженням, яке нагадує вам, перегляньте скріншот:

4.10 Перевірка даних примушує вводити лише IP -адреси

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

Форсувати лише формат IP -адрес за допомогою функції перевірки даних

Дозволити введення лише IP -адрес у певний діапазон клітинок, будь ласка, зробіть так:

1. Виберіть клітинки, для яких потрібно ввести лише IP -адресу, а потім клацніть дані > Перевірка достовірності даних > Перевірка достовірності даних.

Select виготовлений на замовлення від дозволяти випадаючий список.
Потім введіть формулу нижче Formula текстове вікно.
=AND((LEN(A2)-LEN(SUBSTITUTE(A2,".","")))=3,ISNUMBER(SUBSTITUTE(A2,".","")+0))
Натисніть OK , щоб закрити це діалогове вікно.

Примітка: У наведеній вище формулі A2 - це перша клітинка стовпця, яку потрібно використати.

3. Тепер, якщо ви введете недійсну IP -адресу у клітинку, з’явиться вікно з попередженням, яке нагадуватиме вам, як показано на скріншоті нижче:

Форсувати лише формат IP -адрес з кодом VBA

Тут наступний код VBA також може допомогти дозволити введення лише IP -адрес та обмежити інше введення, будь ласка, зробіть так:

1. Клацніть правою кнопкою миші вкладку аркуша та натисніть Переглянути код з контекстного меню, у відкритті Microsoft Visual Basic для додатків вікно, скопіюйте в нього код VBA нижче.

Код VBA: перевірка клітинок на прийняття лише IP -адреси

Private Sub Worksheet_Change(ByVal Target As Range)
'Update by ExtendOffice
Dim xArrIp() As String
Dim xIntIP1, xIntIP2, xIntIP3, xIntIP4 As Integer
If Intersect(Target, Range("A2:A10")) Is Nothing Then
    Exit Sub
Else
    If Target = "" Then
        Exit Sub
    End If
    xArrIp = Split(Target.Text, ".")
    If UBound(xArrIp) <> 3 Then
        GoTo EIP
    Else
    xIntIP1 = CInt(xArrIp(0))
    xIntIP2 = CInt(xArrIp(1))
    xIntIP3 = CInt(xArrIp(2))
    xIntIP4 = CInt(xArrIp(3))
    If (xIntIP1 < 1) Or (xIntIP1 > 255) _
    Or (xIntIP2 < 1) Or (xIntIP2 > 255) _
    Or (xIntIP3 < 1) Or (xIntIP3 > 255) _
    Or (xIntIP4 < 1) Or (xIntIP4 > 255) Then
    GoTo EIP
     End If
    End If
End If
Exit Sub
EIP:
    MsgBox "Please enter correct IP address"
    Target = ""
End Sub

примітки: У наведеному вище коді, A2: A10 - це діапазон комірок, у якому потрібно приймати лише IP -адреси.

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

Форсувати лише формат IP -адрес за допомогою простої функції

Якщо у вас є Kutools для Excel встановлено у вашій книзі Перевірте IP-адресу Ця функція також може допомогти вам вирішити це завдання.

після установки Kutools для Excel, будь ласка, зробіть так:

1. Виберіть клітинки, для яких дозволено введення лише IP -адрес, а потім клацніть Кутулс > Запобігання друку > Перевірте IP-адресу. Дивіться знімок екрана:

2. Після застосування цієї функції тепер можна вводити лише IP -адресу, інакше з’явиться вікно з попередженням, яке нагадуватиме вам, перегляньте знімок екрана:

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

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

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

2. Потім натисніть дані > Перевірка достовірності даних > Перевірка достовірності даних, в вискочив Перевірка достовірності даних у діалоговому вікні під Налаштування , виконайте такі операції:

Select виготовлений на замовлення від дозволяти випадаючий список.
Потім введіть формулу нижче Formula текстове вікно.
=SUM($B$2:$B$7)<=18000
Натисніть OK , щоб закрити це діалогове вікно.

примітки: У наведеній вище формулі, B2: B7 - це діапазон клітинок, якими потрібно обмежити записи.

3. Тепер, при введенні значень у діапазоні B2: B7, якщо сума значень менше 18000 18000 доларів США, перевірка проходить. Якщо будь -яке значення перевищує загальну суму XNUMX XNUMX доларів США, з’явиться вікно з попередженням, яке нагадуватиме вам.

4.12 Перевірка даних обмежує введення комірки на основі іншої комірки

Якщо ви хочете обмежити записи даних у списку комірок на основі значення в іншій комірці, функція перевірки даних також може допомогти вирішити цю роботу. Наприклад, якщо клітинка C1 - це текст "Так", діапазон A2: A9 може вводити будь -що, але, якщо клітинка C1 - це інший текст, нічого не можна вводити в діапазон A2: A9, як показано на скріншотах нижче :

Щоб вирішити цю проблему, зробіть ось що:

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

Select виготовлений на замовлення від дозволяти випадаючий список.
Потім введіть формулу нижче Formula текстове вікно.
=$C$1="Yes"
Натисніть OK , щоб закрити це діалогове вікно.

примітки: У наведеній вище формулі, C1 клітина містить конкретний текст, який ви хочете використовувати, а текст "Так” - це текст, на основі якого потрібно обмежити клітинки, будь ласка, змініть їх відповідно до ваших потреб.

3. Тепер, якщо у клітинці C1 є текст "Так", у діапазон A2: A9 можна ввести будь -що, якщо у клітинці C1 є інший текст, ви не зможете ввести жодне значення, див. Нижче демонстрація:

4.13 Перевірка даних дозволяє вводити лише будні або вихідні дні

Якщо вам потрібно лише робочі дні (з понеділка по п’ятницю) або вихідні дні (субота та неділя) для внесення до списку клітинок, Перевірка достовірності даних також може вам допомогти, виконайте такі дії:

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

Select виготовлений на замовлення від дозволяти випадаючий список.
Потім введіть одну з наведених нижче формул у Formula текстове поле, як вам потрібно.
=WEEKDAY(A2,2)<6 (allow only weekdays)
=WEEKDAY(A2,2)>5 (allow only weekends)
Натисніть OK , щоб закрити це діалогове вікно.

примітки: У наведеній вище формулі, A2 - це перша клітинка стовпця, яку потрібно використати.

3. Тепер ви можете ввести лише дату будня або вихідного дня у певних осередках відповідно до ваших потреб.

4.14 Перевірка даних дозволяє вводити дату на основі сьогоднішньої дати

Іноді вам може знадобитися дозволити вносити у список клітинок лише дати, більші або менші, ніж сьогодні. The Перевірка достовірності даних функція з СЬОГОДНІ функція може зробити вам користь. Будь ласка, зробіть так:

1. Виберіть список клітинок, куди потрібно ввести лише майбутню дату (дату, більшу за сьогоднішню).

Select виготовлений на замовлення від дозволяти випадаючий список.
Потім введіть формулу нижче Formula текстове вікно.
=A2>Today()
Натисніть OK , щоб закрити це діалогове вікно.

примітки: У наведеній вище формулі, A2 - це перша клітинка стовпця, яку потрібно використати.

3. Тепер у клітинки можна вводити лише дати, більші за сьогоднішню.

Порада:

1. Щоб дозволити введення минулої дати (дати меншої за сьогоднішню), будь ласка, застосуйте формулу нижче до Перевірки даних:

=A2<Today()

2. Дозволити введення дати в межах певного діапазону дат, таких як дати протягом наступних 30 днів, будь ласка, введіть формулу нижче у Валідацію даних:

=AND(A2>TODAY(),A2<=(TODAY()+30))

4.15 Перевірка даних дозволяє вводити час на основі поточного часу

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

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

Select Time від дозволяти випадаючий список.
Тоді виберіть менше дозволити лише час перед поточним часом, або більше щоб дозволити час після поточного часу, як вам потрібно від дані випадаюче.
А потім, у Час закінчення or Час початку введіть формулу нижче:
=TIME(HOUR(NOW()),MINUTE(NOW()),SECOND(NOW()))
Натисніть OK , щоб закрити це діалогове вікно.

примітки: У наведеній вище формулі, A2 - це перша клітинка стовпця, яку потрібно використати.

3. Тепер у певні клітинки можна ввести лише час до або після поточного часу.

4.16 Перевірка даних - дата конкретного або поточного року

Щоб дозволити вводити лише дати в певному році або поточному році, ви можете використовувати перевірку даних за допомогою спеціальної формули на основі функції РОК.

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

Select виготовлений на замовлення від дозволяти випадаючий список.
Потім введіть формулу нижче Formula текстове вікно.
=YEAR(A2)=2020
Натисніть OK , щоб закрити це діалогове вікно.

примітки: У наведеній вище формулі, A2 - це перша клітинка стовпця, яку потрібно використати, 2020 - це номер року, який потрібно обмежити.

3. І тоді можна вводити лише дати у 2020 році, якщо ні, то з'явиться вікно з попередженням, як показано на скріншоті нижче:

Порада:

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

=YEAR(A2)=YEAR(TODAY())

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

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

Дозволити ввести дату поточного тижня

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

Select Дата від дозволяти випадаючий список.
А потім, вибирайте між від дані випадаюче.
У Початок текстове поле, введіть цю формулу: = TODAY ()-WEEKDAY (TODAY (), 3)
У Дата закінчення текстове поле, введіть цю формулу: = СЬОГОДНІ ()-ТИЖНИК (СЬОГОДНІ (), 3) +6
Нарешті клацніть OK кнопки.

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

Дозволити вводити дату поточного місяця

Щоб дозволити вводити лише дати поточного місяця, виконайте наведені нижче дії.

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

Select Дата від дозволяти випадаючий список.
А потім виберіть між дані випадаюче.
У Початок текстове поле, введіть цю формулу: = ДАТА (РІК (СЬОГОДНІ ()), МІСЯЦЬ (СЬОГОДНІ ()), 1)
У Дата закінчення текстове поле, введіть цю формулу: = ДАТА (РІК (СЬОГОДНІ ()), МІСЯЦЬ (СЬОГОДНІ ()), ДЕНЬ (ДАТА (РІК (СЬОГОДНІ ()), МІСЯЦЬ (СЬОГОДНІ ())+1,1) -1))
Нарешті клацніть OK кнопки.

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

5. Як відредагувати перевірку даних у Excel?

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

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

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

3. Клацання OK зберегти зміни.

6. Як знайти та виділити комірки з валідацією даних у Excel?

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

1. Активуйте робочий аркуш, який потрібно знайти, і виберіть клітинки з валідацією даних.

2. Потім натисніть кнопку Головна > Знайти та вибрати > Перейти до спеціального, див. скріншот:

3, в Перейти до спеціального діалогове вікно, виберіть Перевірка даних > ВСІ, див. скріншот:

4. І всі клітинки з валідацією даних були вибрані відразу на поточному аркуші.

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

7. Як скопіювати правило перевірки даних в інші комірки?

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

1. Натисніть, щоб вибрати одну клітинку з правилом перевірки, яке потрібно використати, а потім натисніть Ctrl + C скопіювати його.

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

3. А потім клацніть правою кнопкою миші виділення, виберіть Спеціальна вставка варіант, див. скріншот:

4, в Спеціальна вставка діалогове вікно, виберіть Перевірка варіант, див. скріншот:

5. Клацання OK , тепер правило перевірки копіюється в нові клітинки.

8. Як за допомогою перевірки даних обвести недійсні записи в Excel?

Іноді вам може знадобитися створити правила перевірки даних для наявних даних, в цьому випадку деякі недійсні дані можуть з’явитися в діапазоні клітинок. Як перевірити недійсні дані та змінити їх? В Excel можна використовувати Обведіть недійсні дані функція виділення недійсних даних червоним колом.

Щоб обвести необхідні недійсні дані, слід застосувати файл Перевірка достовірності даних функція для встановлення правила для діапазону даних. Виконайте такі дії:

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

2. Потім натисніть кнопку дані > Перевірка достовірності даних > Перевірка достовірності даних, В Перевірка достовірності даних діалогове вікно, встановіть правило перевірки відповідно до ваших потреб, наприклад, тут я перевірю значення більше 500, див. скріншот:

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

Примітки:

1. Як тільки ви виправите недійсні дані, червоне коло автоматично зникне.
2. Це Обведіть недійсні дані Ця функція може обводити не більше 255 клітинок. Коли ви збережете поточну книгу, усі червоні кола будуть видалені.
3. Ці кола не друкуються.
4. Ви також можете видалити червоні кола, клацнувши дані > Перевірка достовірності даних > Очистити кола перевірки.

9. Як видалити перевірку даних у Excel?

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

Видаліть перевірку даних у вибраному діапазоні за допомогою функції перевірки даних

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

2. Потім натисніть дані > Перевірка достовірності даних > Перевірка достовірності даних, у спливаючому діалоговому вікні під Налаштування вкладка, клацніть Очистити всі , див. знімок екрана:

3. Потім натисніть кнопку OK , щоб закрити це діалогове вікно. І правило перевірки даних, застосоване до вибраного діапазону, було видалено одразу.

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

Видаліть перевірку даних у вибраному діапазоні за допомогою зручної функції

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

після установки Kutools для Excel, будь ласка, зробіть так:

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

2. Потім натисніть кнопку Кутулс > Запобігання друку > Очистити обмеження перевірки даних, див. скріншот:

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

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

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

1. Утримуйте клавішу ALT + F11 ключі, щоб відкрити Microsoft Visual Basic для додатків вікна.

2. Потім натисніть Insert > Модуліта вставте наступний макрос у Модулі вікна.

Код VBA: Видаліть правила перевірки даних на всіх аркушах:

Sub RemoveDataValidation()
'Updateby Extendoffice
  Dim xwsh As Worksheet
  For Each xwsh In ActiveWorkbook.Worksheets
    xwsh.Cells.Validation.Delete
  Next xwsh
End Sub

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

Найкращі інструменти для підвищення продуктивності офісу

Kutools для Excel вирішує більшість ваших проблем і збільшує продуктивність на 80%

Супер формула бар (легко редагувати кілька рядків тексту та формули); Макет читання (легко читати та редагувати велику кількість комірок); Вставте у відфільтрований діапазон...
Об’єднати клітинки / рядки / стовпці та Ведення даних; Вміст розділених комірок; Поєднуйте повторювані рядки та суму / середнє... Запобігання дублюючим клітинам; Порівняйте діапазони...
Виберіть Повторюваний або Унікальний Рядки; Виберіть Пусті рядки (усі клітинки порожні); Супер знахідка та нечітка знахідка у багатьох робочих зошитах; Випадковий вибір ...
Точна копія Кілька клітинок без зміни посилання на формулу; Автоматичне створення посилань на кілька аркушів; Вставте кулі, Прапорці та інше ...
Улюблені та швидко вставлені формули, Діапазони, діаграми та зображення; Шифрувати комірки з паролем; Створити список розсилки та надсилати електронні листи ...
Витяг тексту, Додати текст, Видалити за позицією, Видаліть пробіл; Створення та друк проміжних підсумків підкачки; Перетворення вмісту комірок та коментарів...
Супер фільтр (зберегти та застосувати схеми фільтрів до інших аркушів); Розширене сортування за місяцем / тижнем / днем, частотою та іншим; Спеціальний фільтр жирним, курсивом ...
Поєднайте робочі зошити та робочі аркуші; Об’єднати таблиці на основі ключових стовпців; Розділіть дані на кілька аркушів; Пакетне перетворення xls, xlsx та PDF...
Групування зведеної таблиці за номер тижня, день тижня та багато іншого ... Показати розблоковані, заблоковані клітини за різними кольорами; Виділіть клітини, які мають формулу / назву...

Детальніше ... Повні можливості 30-денної безкоштовної пробної версії ... Придбати ...

Вкладка Office - надає інтерфейс із вкладками в Office і значно полегшує вашу роботу

Увімкніть редагування та читання на вкладках у Word, Excel, PowerPoint, Publisher, Access, Visio та Project.
Відкривайте та створюйте кілька документів на нових вкладках того самого вікна, а не в нових вікнах.
Збільшує вашу продуктивність на 50% та зменшує сотні клацань миші для вас щодня!

Детальніше ... Повні можливості 30-денної безкоштовної пробної версії ... Придбати ...

Comments (1)

No ratings yet. Be the first to rate!

Ghomergher

about 2 years ago

#36199

This comment was minimized by the moderator on the site

Thanks For Sharing this Great Information. I loved it.

Report

There are no comments posted here yet

Please leave your comments in English

Posting as Guest

Name (Required)

Email (Required)

Rate this post:

Reset

0 Characters

змін

Підручники з функцій

Отримати допомогу?