Перевірка даних в Excel: методи та особливості

Табличний редактор Microsoft Excel під час роботи вимагає введення величезної кількості даних. У цьому процесі легко можна припуститися помилки, а так як програмою багато хто користується для створення звітності – можуть з'явитися супутні проблеми. Щоб не допустити таких проблем, розробниками запроваджена особлива функція - перевірка даних в Excel. Крім перевірки присутні і багато інші функції, які дозволяють здійснювати цю роботу. Перерахуємо існуючі типи перевірок.

Перевірка на введені значення

Програма Microsoft Office Excel дозволяє гнучко налаштувати введені значення. При цьому правильність буде перевірятися по одному або декільком параметрам. Завдяки цьому можна налаштувати пошук і клітинку.

Числові перевірки

Для записування в клітинку таблиці можна вирішити тільки один тип даних. До них відносяться: Цілочисельні значення в комірку можна ввести лише числа без дробової частини. При цьому можна також обмежити їх значення певним діапазоном або заборонити введення негативних. Це добре підходить для номерів столбцев і рядків. Дійсні числа. У комірку можна вводити тільки числові значення, які можуть включати дробову частину (до десятих). Однак при цьому неможливо ввести будь-який текст. Можна додатково вводити дату. При виборі параметра "Дата" з'явиться можливість вводити числові дані тільки в певному форматі. Для цього здійснюються перевірка і пошук по введеному числу, і при неправильному виборі формату з'явиться помилка. Діапазон значень: від першого січня 1900 року до 31 грудня 9999. Якщо вибрати тип обмежень по часу, як і у випадку з датою, можна буде ввести лише часовий проміжок у визначеному форматі. Крім того, можна обмежити введені значення, наприклад, ввівши дані тільки після полудня. Також існує можливість вводити дані в клітинку програми Microsoft Office Excel допомогою числового еквіваленту. Так, 12:00 відповідає число 0.5. Це обумовлюється способами зберігання даних в Microsoft Office Excel. В додатку за дату та час відповідає дробове число. На прикладі полудня: 12/24 = 0.5.

Текстова перевірка

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


Перевірка даних в Excel по довжині введеного тексту. Підходить для прізвищ і назв компаній. При цьому дозволено вводити в певну комірку тільки заздалегідь заданий обсяг символів. Також можна вводити числа і дати. Проте тут існують свої особливості. З-за зберігання дат в пам'яті у вигляді десяткового числа не вийде ввести дату пізніше 13/10/2173 але тільки якщо обмежити тривалість введеного значення 5 символами. Те ж саме відноситься і до формул. Якщо результат формули занадто довгий, запис у комірку не буде зроблена. Список обмежень. Перевірка введених даних в Excel здійснюється за допомогою заздалегідь заданого списку обмежень. При цьому можна заздалегідь задати певний список обмежень. Крім того, можна задавати значення у списку за допомогою посилання на клітинку або іменованої формули. Список можна заповнювати різними способами.

За допомогою формули

Існує окремий параметр, якщо ні один із стандартних не відповідає потребам користувача. Перевірка даних в Excel в такому випадку здійснює за формулою. У текстове поле буде запропоновано ввести формулу, що відповідає бажаному типу значень, які будуть вводитися в клітинку. Таким чином, в Excel перевірка даних у клітинці здійснюється більш точно.
Деякі способи введення. Для того щоб осередок містила тільки текст без символу, можна визначити для введення виключно текстові значення. Так як у звичайній перевірці даних неможливо обмежити вводяться значення і в текстове поле можна помилково ввести число. Обмежити введення за умови, що в якійсь із осередків значення виходить за межі заздалегідь заданого діапазону. Додати перевірку введеного значення з формулою "ЯКЩО". В такому разі можна буде ввести лише ті значення, які відповідають істині у формулі. Таким чином, можна, наприклад, не давати вводити помилковий вік або суми грошей. Крім того, можна в діапазоні клітинок вводити тільки певні значення, які не будуть перетинатися. При цьому існують обмеження на введення формул. Тому краще скористатися умовним форматуванням.

Висновок коментаря у разі, якщо клітинку

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

Висновок повідомлення про помилку

Як зробити перевірку даних в Excel? Якщо користувач вводить некоректне значення, то можна вивести повідомлення про помилку і запропонувати ввести значення заново. За фактом додаток повністю відповідає функції MessageBox з вбудованого мови програмування в Microsoft Excel Visual Basic Application. У вікні вибору перевірки потрібно перейти в розділ «Повідомлення про помилку», потім поставити галочку в чекбоксі «Виводити повідомлення про помилку», вибрати тип повідомлення, потім ввести заголовок і довільне повідомлення в текстове поле. Перевірка вводу даних в Excel в такому випадку здійснюється по двом умовам. Види повідомлень про помилку: "Зупинити" – скасувати введення неправильної інформації, вивести задане повідомлення. "Попередження" – при виборі такого типу повідомлення відбудеться попередження про неправильності введених даних, програма запропонує виправити помилку. "Повідомлення" – приблизно те ж, що і попередження, але виглядає менш вражаюче і не пропонує змінити вміст комірки.

Використання посилань на інші листи

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

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

Принцип роботи перевірки даних

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

Як знайти комірку з перевіркою даних

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