Запобігання Некоректному Введенню

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

Excel підтримує шість типів правил перевірки даних. Кожне з них контролює окремий аспект дозволених значень.

Випадаючі списки

Вільний ввід у категоріальних стовпцях ("Paid", "PAID", "paid", "Piad") створює несумісності, які непомітно порушують роботу фільтрів і зведених таблиць. Випадаючі списки повністю усувають цю проблему, змушуючи користувачів обирати значення замість введення вручну.

1. Статичний список: джерело вводиться безпосередньо у вікні перевірки даних: Paid,Pending,Cancelled;

2. Динамічний список: джерело посилається на іменований стовпець таблиці на іншому аркуші: =INDIRECT("Table1[Status]").

Правила перевірки

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

• Довжина тексту не аналізує саме значення — вона рахує символи за допомогою LEN(). Тому "1234" і "hello" обидва мають довжину 4, незалежно від типу. Корисно для номерів телефонів, поштових індексів або будь-яких полів із фіксованою кількістю символів;
• Ціле число перевіряє, чи потрапляє значення в числовий діапазон і не містить десяткової частини;
• Дробове число — це така ж перевірка меж, але дозволяє дробові значення, що корисно для цін або вимірювань;
• Дата — найскладніша. Оскільки дати зберігаються як серійні номери, правило на кшталт "більше за сьогодні" — це насправді числове порівняння: TODAY() повертає ціле число, і введена дата повинна бути більшою, щоб пройти перевірку.

Повідомлення для введення та сповіщення про помилки

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

Видалення дублікатів

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

1. Створення контрольованого випадаючого списку для статусу оплати
   
   

Знайдіть стовпець Payment Status у вашому наборі даних і виділіть усі рядки з даними, включаючи останній. Відкрийте Перевірку даних (Data Validation), виберіть Список (List) і введіть варіанти: Paid, Pending.

Натисніть будь-яку клітинку в стовпці, щоб переконатися, що з'явився випадаючий список. Спробуйте ввести інше значення — воно має бути обмежене або з'явиться попередження.

2. Перетворення випадаючого списку на динамічну систему

Створіть джерело для випадаючого списку на новому аркуші. У клітинці A1 введіть Statuses. У A2 та A3 введіть Paid і Pending. Перетворіть цей діапазон на таблицю за допомогою Ctrl/Cmd + T.

Поверніться до основного набору даних, виділіть стовпець Payment Status і знову відкрийте Перевірку даних. Замініть джерело на: =INDIRECT("TableName[Statuses]") Переконайтеся, що використовуєте фактичну назву вашої таблиці.

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

3. Видалення дублікатів записів

Виділіть увесь набір даних або таблицю. Перейдіть до Data → Remove Duplicates і виберіть унікальний стовпець, наприклад Order ID.

Excel покаже, скільки дублікатів було видалено — підтвердьте результат.