Спеціальні формули перевірки даних

Цей підручник покаже вам, як створювати спеціальні формули для перевірки даних у Excel та Google Таблицях

Перевірка даних - потрібно починати з - Excel

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

1. Виділіть необхідний діапазон, наприклад: B3: B8.
2. На стрічці виберіть Дані> Інструменти даних> Перевірка даних.

3. Виберіть На замовлення зі спадного списку Дозволити, а потім введіть таку формулу:

= ТОЧНО (ВЛІВО (B3,4), "FRU-")

Формула використовує 2 функції ТОЧНО та ВЛІВО щоб визначити, чи перші 4 символи, введені в клітинку, ідентичні "FRU-"

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

5. Переконайтеся, що галочка "Показувати вхідне повідомлення, коли вибрано клітинку”, А потім введіть потрібну назву та повідомлення.
6. Якщо ви хочете додати сповіщення про помилку, натисніть Сповіщення про помилку.

7. Ви можете змінити стиль з Стій до обох Увага або Інформація якщо вам потрібно, а потім введіть свою назву та повідомлення про помилку.
8. Натисніть в порядку.
9. Якщо ви використовували Вхідні дані Опція повідомлення, на екрані з’явиться коментар, який поінформує користувача про правило.

10. Введіть “FRI-124” у комірку В3. Якщо ви вибрали Стій, з'явиться наступне вікно повідомлення.

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

12. Якщо ви вибрали Інформація, тоді з'явиться це вікно повідомлення.

13. Якщо натиснути в порядку, вам буде дозволено продовжувати введення неправильних даних у клітинку.
14. Натисніть Скасувати щоб вийти з повідомлення або в порядку для введення тексту в клітинку.

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

Ми можемо написати власну формулу, щоб переконатися, що дані в комірці допускають лише великі літери, якщо в клітинку введено текст.

1. Виділіть необхідний діапазон, наприклад: B3: B8.
2. На стрічці виберіть Дані> Інструменти даних> Перевірка даних.
3. Виберіть На замовлення зі спадного списку Дозволити, а потім введіть таку формулу:

= ТОЧНА (B3, ВЕРХНЯ (B3))

Формула використовує 2 функції ТОЧНО та ВЕРХНЯ щоб визначити, чи текст, введений у клітинку, пишеться з великого регістру. Клітинки з сумішшю числа та тексту вважаються текстом, а числа ігноруються у правилі.

4. Натисніть в порядку.
5. Введіть “fru-124” у комірку В3.
6. Якщо ви використовували опцію "Попередження про помилку", з'явиться ваше спеціальне попередження та повідомлення про помилку. Якщо ви не використовували цю опцію, з'явиться стандартне попередження.

7. Натисніть Скасувати, щоб вийти з повідомлення, або Повторити, щоб повторно ввести правильний текст у клітинку.
8. Введіть “123456” у клітинку В3.
9. Це буде дозволено, оскільки це число, а не текст.

У нашому наступному прикладі ми гарантуємо, що в комірку можна вводити лише текст верхнього регістру.

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

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

ПРИМІТКА. Якщо ви вводите інформацію в клітинку, яка починається з тексту, але містить цифри, Excel розгляне текст інформації.

1. Виділіть необхідний діапазон, наприклад: B3: B8.
2. На стрічці виберіть Дані> Інструменти даних> Перевірка даних.
3. Виберіть На замовлення зі спадного списку Дозволити, а потім введіть таку формулу:

= І (ТОЧНЕ (B3, ВЕРХНЕ (B3)), ISTEXT (B3))

Формула використовує 4 функції І, ТОЧНА, ВЕРХНЯ та ТЕКСТ щоб визначити, чи текст, введений у комірки, пишеться з великого регістру І щоб визначити, чи введена інформація насправді є текстом, а не чистим числом.

4. Натисніть в порядку.
5. Введіть “fru-124” у комірку В3.
6. Якщо ви використовували опцію "Попередження про помилку", з'явиться ваше спеціальне попередження та повідомлення про помилку. Якщо ви не використовували цю опцію, з'явиться стандартне попередження.

7. Натисніть Скасувати, щоб вийти з повідомлення, або Повторити, щоб повторно ввести правильний текст у клітинку.
8. Введіть “123456” у клітинку В3.
9. Ви знову отримаєте повідомлення про помилку.
10. Натисніть Скасувати, щоб вийти з повідомлення, або Повторити, щоб повторно ввести правильний текст у клітинку.

Перевірка даних запобігає просторам у Excel

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

1. Виділіть необхідний діапазон, наприклад: B3: B8.
2. На стрічці виберіть Дані> Інструменти даних> Перевірка даних.
3. Виберіть На замовлення зі спадного списку Дозволити, а потім введіть таку формулу:

= B3 = ЗАМІНА (B3, ””, “”)

Формула використовує функцію SUBSTITUTE, щоб перевірити відсутність пробілів.

4. Натисніть в порядку.
5. Введіть “FRU - 124” у комірку В4.
6. Якщо ви використовували опцію "Попередження про помилку", з'явиться ваше спеціальне попередження та повідомлення про помилку. Якщо ви не використовували цю опцію, з'явиться стандартне попередження.

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

Перевірка даних запобігає дублюванню в Excel

Ми можемо написати власну формулу, щоб запобігти введенню повторюваної інформації в діапазон клітинок.

1. Виділіть необхідний діапазон, наприклад: B3: B8.
2. На стрічці виберіть Дані> Інструменти даних> Перевірка даних.
3. Виберіть Клієнт зі спадного списку Дозволити, а потім введіть таку формулу:

= COUNTIF ($ B $ 3: $ B $ 8, B3) <2

Формула використовує COUNTIF функції та використання АБСОЛЮТИ в діапазоні B3: B8, щоб переконатися, що це список, який COUNTIF Функція дивиться, коли перевіряє, чи немає повторюваних значень.

4. Натисніть в порядку.
5. Введіть “FRU-123” у комірку D4.
6. Якщо ви використовували опцію "Попередження про помилку", з'явиться ваше спеціальне попередження та повідомлення про помилку. Якщо ви не використовували цю опцію, з'явиться стандартне попередження.

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

Перевірка даних існує у списку в Excel

Ми можемо написати власну формулу, щоб переконатися, що в клітинку вводиться лише певний текст.

1. Виділіть необхідний діапазон, наприклад: D3: D8.
2. На стрічці виберіть Дані> Інструменти даних> Перевірка даних.
3. Виберіть Спеціальне зі спадного списку Дозволити, а потім введіть таку формулу:

= COUNTIF ($ F $ 6: $ F $ 8, D3)> 0

Формула використовує COUNTIF функції та використання АБСОЛЮТИ у діапазоні F3: F8, щоб переконатися, що це список, який COUNTIF Функція дивиться, коли перевіряє, чи введено правильний текст.

4. Натисніть в порядку.
5. Введіть "одинарний" у комірку D4.
6. Якщо ви використовували опцію "Попередження про помилку", з'явиться ваше спеціальне попередження та повідомлення про помилку. Якщо ви не використовували цю опцію, з'явиться стандартне попередження.

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

Перевірка даних не існує у списку в Excel

Ми можемо використовувати спеціальну формулу, щоб переконатися, що певний текст не вводиться в клітинку.

1. Виділіть необхідний діапазон, наприклад: C3: C8.
2. На стрічці виберіть Дані> Інструменти даних> Перевірка даних.
3. Виберіть Клієнт зі спадного списку Дозволити, а потім введіть таку формулу:

= COUNTIF ($ F $ 6: $ F $ 8, C3) = 0

Формула використовує COUNTIF функції та використання АБСОЛЮТИ у діапазоні F3: F8, щоб переконатися, що це список, який містить COUNTIF Функція дивиться, коли перевіряє, чи введено правильний текст.

4. Натисніть в порядку.
5. Введіть "яловичина" у клітинку С4.
6. Якщо ви використовували опцію "Попередження про помилку", з'явиться ваше спеціальне попередження та повідомлення про помилку. Якщо ви не використовували цю опцію, з'явиться стандартне попередження.

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

У програмі Excel перевіряються лише цифри

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

1. Виділіть необхідний діапазон, наприклад: E3: E8.
2. На стрічці виберіть Дані> Інструменти даних> Перевірка даних.
3. Виберіть Клієнт зі спадного списку Дозволити, а потім введіть таку формулу:

= ISNUMBER (F3: F8)

Формула використовує функцію ISNUMBER для того, щоб забезпечити введення числа в комірки в діапазоні.

4. Натисніть в порядку.
5. Введіть “дев’ять” у комірку F4.
6. Якщо ви використовували опцію "Попередження про помилку", з'явиться ваше спеціальне попередження та повідомлення про помилку. Якщо ви не використовували цю опцію, з'явиться стандартне попередження.

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

Перевірка даних не перевищує значення в Excel

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

1. Виділіть необхідний діапазон, наприклад: E3: E8.
2. На стрічці виберіть Дані> Інструменти даних> Перевірка даних.
3. Виберіть Клієнт зі спадного списку Дозволити, а потім введіть таку формулу:

= E3 <= $ G $ 6

Формула an АБСОЛЮТНО в діапазоні G6, щоб переконатися, що це значення перевіряється правилом при введенні даних в E3.

4. Натисніть в порядку.
5. Введіть “9” у клітинку E4.
6. Якщо ви використовували опцію "Попередження про помилку", з'явиться ваше спеціальне попередження та повідомлення про помилку. Якщо ви не використовували цю опцію, з'явиться стандартне попередження.

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

Перевірка даних не перевищує загальну суму в Excel

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

1. Виділіть необхідний діапазон, наприклад: F3: F8.
2. На стрічці виберіть Дані> Інструменти даних> Перевірка даних.
3. Виберіть Клієнт зі спадного списку Дозволити, а потім введіть таку формулу:

= SUM ($ F $ 3: $ F $ 8) <= $ H $ 6

Формула використовує SUM функції та використання АБСОЛЮТИ у діапазоні F3: F8, щоб переконатися, що це список, який SUM Функція дивиться, коли перевіряє, чи сумарна сума діапазону не більша за значення, введене в H6.

4. Натисніть в порядку.
5. Введіть “40” у комірку F4.
6. Якщо ви використовували опцію "Попередження про помилку", з'явиться ваше спеціальне попередження та повідомлення про помилку. Якщо ви не використовували цю опцію, з'явиться стандартне попередження.

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

Перевірка даних Будні дати Тільки в Excel.

Ми можемо використовувати спеціальну формулу, щоб переконатися, що лише дні будня вводяться, коли дати використовуються в Excel.

1. Виділіть необхідний діапазон, наприклад: G3: G8.
2. На стрічці виберіть Дані> Інструменти даних> Перевірка даних.
3. Виберіть Клієнт зі спадного списку Дозволити, а потім введіть таку формулу:

= ТИДЕНЬ (F3,2) <6

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

4. Змініть дату в G5, щоб показати суботу (наприклад, 9^го Травень 2022 р.).
5. Якщо ви використовували опцію "Попередження про помилку", з'явиться ваше спеціальне попередження та повідомлення про помилку. Якщо ви не використовували цю опцію, з'явиться стандартне попередження.

7. Натисніть Скасувати або Повторити, щоб ввести альтернативну дату.

Майбутні дати перевірки даних Тільки в Excel

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

1. Виділіть необхідний діапазон, наприклад: G3: G8.
2. На стрічці виберіть Дані> Інструменти даних> Перевірка даних.
3. Виберіть Клієнт зі спадного списку Дозволити, а потім введіть таку формулу:

= G3> СЬОГОДНІ ()

Формула використовує СЬОГОДНІ функція, щоб перевірити, чи дата, введена у клітинку, більша за сьогоднішню.

4. Змініть дату в G5 на вчора.
5. Якщо ви використовували опцію "Попередження про помилку", з'явиться ваше спеціальне попередження та повідомлення про помилку. Якщо ви не використовували цю опцію, з'явиться стандартне попередження.

6. Натисніть Скасувати або Повторити, щоб ввести альтернативну дату.

Перевірку даних потрібно починати з Таблиць Google

1. Виділіть необхідний діапазон, наприклад: B3: B8.
2. У меню виберіть Дані> Перевірка даних.

3. Діапазон клітинок уже буде заповнено.

4. Виберіть Спеціальна формула є зі спадного списку Критерії.

5. Введіть формулу.

= ТОЧНО (ВЛІВО (B3,4), "FRU-")

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

7. Якщо потрібно, ви можете ввести текст довідки щодо перевірки.

8. Натисніть Зберегти.

9. Натисніть B3, щоб побачити текст довідки про перевірку

10. Тип FRI-123
11. Якщо ви вибрали Показати попередження, з'явиться таке повідомлення.

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

Решта прикладів спеціальних формул у Таблицях Google працюють точно так само.