Перевірка даних є непоганим інструментом Excel для контролю за змінами, що вносяться на аркушах змінами, не вдаючись до допомоги VBA. З її допомогою можна обмежити введення в осередок, дозволивши вводити тільки дати або час, або тільки числа. Та до того ж ще й задати діапазон дат або межа чисел (наприклад від 1 до 10) .Прімененій, я думаю, можна придумати масу: для коректної роботи багатьох формул потрібні коректні вихідні дані. Отже, ми можемо за допомогою Перевірки даних дозволити користувачеві вводити тільки той тип і діапазон даних, який може обробити формула, яка не повертаючи значення помилки. Розберемо докладніше.
Цей чудовий інструмент знаходиться: Дані (Data) - Перевірка даних (Data Validation). Повинно з'явитися вікно: 
Для початку треба виділити осередок (або діапазон комірок) для якої необхідно встановити перевірку. Потім йдемо в Дані (Data) - Перевірка даних (Data Validation).
В поле Тип даних (Allow) - обирається безпосередньо тип даних, який повинен бути записаний в осередку. Усього доступно 8 типів: Будь-яке значення, Ціле число, Справжнє, Список, Дата, Час, Довжина тексту, Інший (Any Value, Whole number, Decimal, List, Date, Time, Text lenght, Custom). Пункт Будь-яке значення (Any Value) встановлено за замовчуванням, перевірка не здійснюється. Детально про пункт Списки (List) і як їх створювати можна подивитися і почитати в статті випадають списки . Там все детально і з нюансами розписано про списки в перевірці даних, тому в даній статті розглянемо залишилися 6 типів. Якщо коротко, то при виборі пункту Список в осередку з'являється список, що випадає допустимих значень. І ввести можна тільки те значення, яке присутнє в списку 
Решта типів даних:
- Ціле число (Whole number) - говорить сама за себе. Після установки такої перевірки в клітинку можна буде внести тільки ціле число. Тобто число, яке не має дробового залишку (9,1 наприклад вже не можна буде ввести). Так само не можна буде ввести довільний текст. Найчастіше подібна перевірка застосовується в полях для запису кількості штук товару і т.п. Тобто там, де не може бути дрібних значень.
- Дійсне (Decimal) - теж, що і в попередньому пункті, але ввести можна будь-яке число - хоч ціле, хоч дробове, але неможливо буде ввести текст
- Дата (Date) - В осередку може міститися тільки значення дати. Дата може бути записана в будь-якому форматі, допустимому в поточній локалізації Windows. Тут той же нюанс, що і перевіркою на числа, тільки у зворотний бік - будь-яка дата це число, тому по суті можна буде ввести будь-яке ціле число, яке може бути переведено Excel-м в дату.
- Час (Time) - можна записувати значення в форматі часу для поточної локалізації і так само як з датою в клітинку можна буде вводити не тільки час, а й будь-які числа: цілі або дробові
- Довжина тексту (Text lenght) - дозволяє обмежити кількість вводяться в осередок символів. Дані можуть бути і числом і текстом і часом. Чим завгодно, тільки повинні відповідати іншим умовам перевірки. Часто застосовується для контролю введення даних по ІПН, КПП, БИК, артикулами і т.п.
- Інший (Custom) - на мій погляд самий цікавий і найпотужніший тип перевірки. Тут ми можемо більш широко контролювати введення даних. Для заповнення є тільки одне поле - Формула (Formula). У нього необхідно записати формулу і при кожному введенні значень в клітинку, Excel перевірить введене вираз на істину. Якщо бути точніше то Excel спочатку вирахує формулу в цьому полі орієнтуючись на введене в клітинку з перевіркою даних значення, а потім перевірить - чи повертає формула значення ІСТИНА (TRUE). Якщо результатом буде ІСТИНА (TRUE), то введене значення буде збережено в осередку, а якщо БРЕХНЯ (FALSE), то буде видано вікно з повідомленням про помилку
Наведу простий приклад. У осередок А1 введемо число 1. Виділяємо комірку В1 - призначаємо перевірку даних-Інший. В поле Формула вписуємо: = B1 = A1. Тепер в клітинку В1 можна вписати тільки значення, яке повністю ідентично значенням в осередку А1.
Важливо: якщо перевірка даних в подальшому буде скопійована в інші осередки, то посилання будуть зміщені так, ніби це проста формула. Тому при створенні формул в перевірці даних необхідно враховувати можливість зміщення і при необхідності закріплювати посилання (знак долара - $).
- Виводити повідомлення про помилку (Show error alert after invalid data is entered) - в більшості випадків необхідно встановити галочку. Якщо галочка знята, то перевірка введених в осередок даних не буде здійснюватися.
- Вид (Style)
- Зупинка, Повідомлення (Stop, Information) - можна ввести тільки дозволені перевіркою значення. Різниться тільки вид повідомлення (приклад повідомлення Зупинка наведено на малюнку вище).
- Попередження (Warning) - в клітинку можна ввести будь-яке значення, але при введенні значення, що суперечить перевірці, з'явитися попереджувальне повідомлення із підтвердженням введення даних.
- Тема (Title) - текст, який буде показаний в заголовку повідомлення про помилку. Якщо не вказано, то в заголовку буде написано Microsoft Excel.
- Повідомлення (Error message) - безпосередньо текст самого повідомлення про помилку. Якщо не вказано, то буде показаний текст приблизно такого змісту:
Це значення не відповідає обмеженням по перевірці даних, встановленим для цієї комірки
(This value does not match the data validation restrictions defined for this cell)
Примітка: застосувавши дані типи перевірок, внести в комірку можна тільки число. Занести текст Excel вже не дозволить. Однак варто пам'ятати, що дати і час Excel сприймає і зберігає саме як ЧИСЛОВІ значення, тому введення дати і часу теж буде дозволений, якщо він не суперечить іншим умовам перевірки (див.нижче). Але для перевірки Ціле число дозволений буде введення тільки дати, тому що час Excel зберігає як дробове значення (крім 24:00:00 і 00:00:00, які Excel сприймає як 1 і 0 відповідно). Більш докладно про сприйняття Excel-ем даних читайте в статті Як Excel сприймає дані? .
Якщо подивитися на картинку на початку статті, то можна побачити ще три поля, крім тих, які я перерахував: Значення (Data), Мінімум (Minimum) і Максимум (Maximum). Поле Значення містить кілька варіантів перевірки: Між, Поза, Рівно, Більше, Менше, Більше або дорівнює, Менше або дорівнює (between, not between, equal to, not equal to, greater than, less than, greater than or equal to, less than or equal to) і доступно воно тільки при виборі Типу даних Ціле число, Справжнє, Дата, Час, Довжина тексту. Для інших типів поле Значення недоступно. Залежно від того, який пункт обраний в поле Значення з'являються додаткові поля: Мінімум і Максимум. При цьому може бути тільки одне поле і назва може бути іншим. Наприклад, для варіанта Так само буде показано тільки одне поле і називатися воно буде Значення (Value). Але тут немає ніяких підводних каменів і розібратися з цими полями можна не напружуючись і без півлітри :)
По суті всі пункти досить красномовні і пояснювати детально кожен, думаю, сенсу немає. Наприклад, Між (between) - вказується інтервал дат чисел, в який повинно входити умова (наприклад ціле число від 1 до 12: Мінімум = 1, Максимум = 12). Якщо користувач спробує ввести число за межами зазначеного інтервалу (скажімо число 0, -3 або 14), то Excel видасть повідомлення про помилку (див.нижче). Варіант Більше або дорівнює (greater than or equal to) дозволить вносити тільки позитивні значення більше нуля, якщо вказати в поле Мінімум значення 1.
Трохи більшу увагу слід приділити варіанту перевірки Поза (not between). При встановленому значенні Поза, в клітинку можна буде внести тільки дані, які не входять в діапазон, зазначений в полях Мінімум і Максимум. Тобто при тих же умовах від 1 до 12 ввести можна буде і 0 і -3 і 14, але не можна буде ввести 1 або 10.
При цьому в полях для введення значень допускається вказати посилання на осередок. Наприклад, в комірці A1 записана початкова дата прийому заявок, в B1 - кінцева дата. І треба встановити в осередках A2: A50 перевірку на інтервал дат, зазначених як раз в A1 і B1. Для цього виділяємо комірки A2: A50 і створюємо в них перевірку даних: Тип даних: Дата, Значення: Між, Початкова дата: = A1, Кінцева дата: = B1. Тепер можна регулювати інтервал без зміни самої перевірки даних - просто змінюючи значення осередків A1 і B1.
Вкладка Повідомлення для введення (Input Message)
Тут вказується текст, який буде відображатися при виділенні комірки з перевіркою даних: 
Найзручніше в даному повідомлення то, що можна не встановлювати саму перевірку даних, а заповнити тільки цю вкладку. Тоді повідомлення буде з'являтися при виділенні комірки, але ніяких обмежень для введення в осередок не буде. Тобто використовувати цю можливість просто для додаткового інформування про найбільш зручною форматі даних або просто вказувати тип вводиться в осередок інформації.
Вкладка Повідомлення про помилку (Error Alert)
Тут вказується відображати повідомлення про помилковому введенні чи ні, і сам тип виведеного повідомлення про помилку.
Але так само необхідно пам'ятати, що будь-яке умова на перевірку Ви не поставили - значення в осередку можна видалити, натиснувши кнопку Del. Або скопіювавши осередок з іншої книги або аркуша і вставивши на місце перевірки даних - перевірка зникне, тому що вся осередок буде замінена скопійованої. І від цього ніяк не втекти - такий перевірку зробили розробники ...
Як скопіювати перевірку даних на інші осередки
Все дуже просто - копіюємо осередок з потрібною перевіркою даних -виделяем осередки для створення в них такого ж умови -Права кнопка миші - Спеціальна вставка (Paste Special) -в вікні вибираємо Умови на значення (Validation) - Ок: 
Як видалити перевірку даних з комірок
Виділяємо необхідні осередки вкладка Дані (Data) - Перевірка даних (Data Validation). В поле Тип даних (Allow) встановлюємо Будь-яке значення (Any Value) - Ок.
Маленька хитрість використання перевірки даних
Якщо перевірка даних на осередки була встановлена вже після того, як дані були внесені, то дуже непогано зрозуміти, чи всі дані відповідають умовам перевірки. Зробити це нескладно. Після того, як осередкам була призначена перевірка даних переходимо на вкладку Дані (Data) - Перевірка даних (Data Validation) -раскриваем меню і вибираємо Обвести невірні дані (Circle Invalid Data). Всі осередки, дані в яких не відповідають умовам перевірки даних будуть виділені червоною лінією: 
Це можна використовувати не тільки коли необхідна перевірка даних, але і просто для визначення помилкових значень. Наприклад, є список товару на складі і є лист, в який цей товар вносився співробітниками вручну. Необхідно визначити які найменування товару не відповідають тим, які заведені на складі. Створюємо перевірку даних Список ( випадають списки ), Вказуючи в якості джерела список товару на складі. Далі поширюємо цю перевірку на осередки з занесеними співробітниками даними. І тепер застосовуємо функцію Обвести невірні дані.
Відразу після виправлення невірних значень на ті, які є в списку обведення зникає. Якщо в повному обсязі значення треба виправляти, а обведення проте треба видалити, то після всіх потрібних правок просто переходимо на вкладку Дані (Data) - Перевірка даних (Data Validation) -раскриваем меню і вибираємо Видалити обведення невірних даних (Clear Validation Circles)
Стаття допомогла? Поділися посиланням з друзями! 
Відео уроки
{ "Bottom bar": { "textstyle": "static", "textpositionstatic": "bottom", "textautohide": true, "textpositionmarginstatic": 0, "textpositiondynamic": "bottomleft", "textpositionmarginleft": 24, " textpositionmarginright ": 24," textpositionmargintop ": 24," textpositionmarginbottom ": 24," texteffect ":" slide "," texteffecteasing ":" easeOutCubic "," texteffectduration ": 600," texteffectslidedirection ":" left "," texteffectslidedistance " : 30, "texteffectdelay": 500, "texteffectseparate": false, "texteffect1": "slide", "texteffectslidedirection1": "right", "texteffectslidedistance1": 120, "texteffecteasing1": "easeOutCubic", "texteffectduration1": 600 , "texteffectdelay1" 1000, "texteffect2": "slide", "texteffectslidedirection2": "right", "texteffectslidedistance2": 120, "texteffecteasing2": "easeOutCubic", "texteffectduration2": 600, "texteffectdelay2": 1500, " textcss ":" display: block; padding: 12px; text-align: left; "," textbgcss ":" display: block; position: absolute; top: 0px; left: 0px; width: 100%; height: 100% ; background-color: # 333333; opacity: 0.6; filter: a lpha (opacity = 60); "," titlecss ":" display: block; position: relative; font: bold 14px \ "Lucida Sans Unicode \", \ "Lucida Grande \", sans-serif, Arial; color: #fff; "," descriptioncss ":" display: block; position: relative; font: 12px \ "Lucida Sans Unicode \", \ "Lucida Grande \", sans-serif, Arial; color: #fff; margin-top: 8px; "," buttoncss ":" display: block; position: relative; margin-top: 8px; "," texteffectresponsive ": true," texteffectresponsivesize ": 640," titlecssresponsive ":" font-size: 12px; "," descriptioncssresponsive ":" display: none! important; "," buttoncssresponsive ": "", "addgooglefonts": false, "googlefonts": "", "textleftrightpercentforstatic": 40}}
