Умовне форматування (5)
Списки та діапазони (5)
Макроси(VBA процедури) (63)
Різне (39)
Баги та глюки Excel (3)

Перевірка даних є непоганим інструментом Excel для контролю за змінами, що вносяться на аркушах, не вдаючись до допомоги VBA. З її допомогою можна обмежити введення в комірку, дозволивши вводити лише дати чи час, чи лише числа. До того ж ще й задати діапазон дат або межу чисел (наприклад від 1 до 10). Застосувань, я думаю, можна вигадати масу: для коректної роботи багатьох формул потрібні коректні вихідні дані. Отже, ми можемо за допомогою Перевірки данихдозволити користувачеві вводити лише той тип та діапазон даних, який може обробити формула, не повертаючи значення помилки. Розберемо детальніше.

Цей чудовий інструмент знаходиться: Дані (Data)-. Повинно з'явитися вікно:

Для початку треба виділити комірку (або діапазон осередків) для якої необхідно встановити перевірку. Потім йдемо в Дані (Data)-Перевірка даних (Data Validation).

Перша вкладка – Параметри (Settings)

В полі Тип даних (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)- дозволяє обмежити кількість символів, що вводяться в комірку. Дані можуть бути і числом, і текстом, і часом. Що завгодно, тільки повинні відповідати іншим умовам перевірки. Часто застосовується для контролю введення даних по ІПН, КПП, БІК, артикулам тощо.

Якщо подивитися на картинку на початку статті, можна побачити ще три поля, крім тих, які я перерахував: Значення (Data), Мінімум (Minimum)і Максимум (Maximum). Поле Значеннямістить кілька варіантів перевірки: Між, Поза, Рівно, Більше, Менше, Більше або рівно, Менше або одно 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.

• Інший (Custom)- на мій погляд найцікавіший і найпотужніший тип перевірки. Тут нам надається можливість ширше контролювати введення даних. Для заповнення є лише одне поле - Формула (Formula). У нього необхідно записати формулу і при кожному введенні значень у комірку, Excel перевірить введений вираз на ІСТИНУ. Якщо бути точніше, то Excel спочатку обчислить формулу в цьому полі орієнтуючись на введене в комірку з перевіркою даних значення, а потім перевірить - чи формула повертає значення ІСТИНА (TRUE). Якщо результатом буде ІСТИНА (TRUE), то введене значення буде збережено в комірці, а якщо БРЕХНЯ (FALSE), то буде видано вікно з повідомленням про помилку:
  
  

  Наведу найпростіший приклад. У комірку А1 введемо число 1. Виділяємо комірку В1 - призначаємо перевірку даних- Інший. У полі Формула вписуємо: = B1 = A1. Тепер в комірку В1 можна вписати тільки значення, яке повністю ідентичне значення в комірці А1.

  Важливо:якщо перевірка даних надалі буде скопійована в інші осередки, посилання будуть зміщені так, ніби це проста формула. Тому при створенні формул у перевірці даних необхідно враховувати можливість зміщення та за необхідності закріплювати посилання (знак долара - $ ).

Вкладка Повідомлення для введення (Input Message)

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

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

Вкладка Повідомлення про помилку (Error Alert)
Тут вказується відображати повідомлення про помилкове введення чи ні, і сам тип повідомлення про помилку.

• Виводити повідомлення про помилку.- у більшості випадків необхідно встановити галочку. Якщо галочка знята, то перевірка даних, що вводяться в комірку, не буде здійснюватися.
• Вид (Style)
  • Зупинка, Повідомлення (Stop, Information)- можна ввести лише дозволені перевіркою значення. Розрізняється тільки вид повідомлення (приклад повідомлення Зупинка наведено на малюнку вище).
  • Попередження (Warning)- у комірку можна ввести будь-яке значення, але при введенні значення, що суперечить перевірці, з'явиться попереджувальне повідомлення з підтвердженням введення даних.
• Заголовок (Title)- текст, який буде показано у заголовку повідомлення про помилку. Якщо не вказано, то в заголовку буде написано Microsoft Excel.
• Повідомлення (Error message)- безпосередньо текст повідомлення про помилку. Якщо не вказано, то буде показано текст приблизно наступного змісту:
  Це значення не відповідає обмеженням перевірки даних, встановленим для цього осередку
  (Ця величина не дає змогу змінити дані validation restrictions defined for this cell)

Але так само необхідно пам'ятати, що яку б умову на перевірку Ви не поставили - значення в осередку можна видалити, натиснувши кнопку 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"," :30,"texteffectdelay":500,"texteffectseparate":false,"texteffect1":"slide","texteffectslidedirection1":"right","texteffectslidedistance1":120,"texteffecteasing1":"easeOutCubic","text ,"texteffectdelay1":1000,"texteffect2":"slide","texteffectslidedirection2":"right","texteffectslidedistance2":120,"texteffecteasing2":"easeOutCubic","texteffectduration2":600,"text textcss":"display:block; padding:12px; text-align:left;","textbgcss":"display:block; position:absolute; top:0px; left:0px; width:100%; background-color:#333333;opacity:0.6;filter:alpha(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;","buttoncssress "","addgooglefonts":false,"googlefonts":"","textleftrightpercentforstatic":40))

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

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

Щоб перевірити дані в Excel для певних осередків, їх необхідно виділити, перейти на вкладку «Дані», та знайти у розділі «Робота з даними»меню "Перевірка даних". При натисканні на стрілочку праворуч від цього пункту з'являється три пункти меню, з яких нам потрібен перший "Перевірка даних…".

У віконці, що з'явилося «Перевірка значень, що вводяться»налаштовується перевірка. На вкладці «Параметри»вибирається зі списку тип даних, які будуть перевірятися та вводитися у виділений нами діапазон осередків. Наприклад виберемо "Ціле число".

Після вибору типу даних стає можливим вибір умови відповідності значення, що вводиться в графі «Значення:». Виберемо для прикладу «Між».

На наступній вкладці «Повідомлення для введення»можна вказати заголовок і саме повідомлення підказку, яке висвічуватиметься при активації осередку з перевіркою даних, що вводяться.

На вкладці "Повідомлення про помилку"можна вказати заголовок і саме повідомлення, що з'являється при помилковому введенні даних, а також дію, яка буде виробляється. На вкладці «Вид:»доступні три варіанти. «Зупинка»запобігає введенню неприпустимих даних та пропонує повторити спробу. «Попередження»видає повідомлення про помилку, але дозволяє продовжити введення неприпустимого значення. "Повідомлення"просто поінформує про неприпустиме введене значення.

Повідомлення підказка.

Вид «Зупинка».

Вид «Попередження».

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

Перевірка даних, що вводяться в Excel

Для того, щоб запобігти помилкам оператора комп'ютерного набору в Excel, будемо використовувати перевірку даних введення. Тим самим створимо йому комфортні умови для рутинної роботи, де складно не допустити помилок.

У нас є лист номенклатури товарів магазину:

Тепер перевіримо.У комірку B2 введіть натуральне число, а комірку B3 негативне. Як видно в осередку B3, дія оператора набору – заблокована. З'являється повідомлення про помилку: «Введене значення неправильне».

Примітка. За бажанням можна написати власний текст для помилки на третій закладці параметрів інструмента «Повідомлення про помилку».

Щоб видалити перевірку даних у Excel, потрібно: виділити відповідний діапазон осередків, вибрати інструмент і натиснути на кнопку «Очистити все» (вказано на другому малюнку).



Особливості перевірки даних

Даним способом перевіряються дані лише у процесі введення. Якщо дані вже введені, вони будуть не перевірені. Наприклад, у стовпці B не можна ввести текст після встановлення умов заповнення осередків. Але заголовок у осередку B1 «Ціна» залишився без попередження про помилку.

Увага! Якщо комірки будуть скопійовані, а не введені, то їх значення так само не будуть перевірені.

Щоб перевірити чи відповідають усі введені дані, певним умовам у стовпці і чи немає помилок, слід використовувати інший інструмент: «Дані»-«Перевірка даних»-«Обвести невірні дані».

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

Звичайно, можна виконати перевірку даних у стовпці за допомогою логічної функції Excel- «ЯКЩО». Або умовне форматування. Але застосування інструмента «Перевірка даних» – більш ефективне, зручне та продуктивне для цього завдання. Особливо якщо нам потрібно одночасно виконати перевірку кількома стовпцями. У такому випадку більш помітна раціональність його використання.

«Захист осередки Excelвід неправильно введених даних». Тут розглянемо інший варіант - як перевіряти в Excel правильність написання коду з літерами та числами.
Перший варіант.
Перевірка введення даних уExcel.
В нас така таблиця. У стовпці A писатимемо коди з літерами та числами.
У цих осередках встановимо перевірку даних відповідно до наших умов. Виділяємо осередки шпальти А (А31:А36).
На закладці «Дані» у розділі «Робота з даними» натискаємо кнопку «Перевірка даних». З'явиться діалогове вікно «Перевірка значень, що вводяться».
У цьому вікні на закладці "Параметри" у розділі "Умови перевірки" - "Тип даних" вибираємо функцію "Інший".
У рядку Формула пишемо таку формулу.
=І(ЛЕВСИМВ(A31)="Ф";ДЛСТР(A31)=3;ЕЧИСЛО(ЗНАЧЕНИЙ(ПРАВСІМВ(A31;2)))))
Пояснення до формули.
Цією формулою ми говоримо Excel, що в осередках стовпця А (з осередку А31) дані повинні починатися на літеру "ф" - це частина формули "ЛЕВСІМВ (А31) = "Ф"; .
Усього в осередку має бути написано 3 знаки – це функція у формулі – ДЛСТР(А31)=3.
А функція ЧИСЛО(ЗНАЧЕНИЙ(ПРАВСІМВ(A31;2))) говорить, що в осередку останніх 2 знаки повинні бути числами.
Натискаємо кнопку "ОК". Усі перевірку даних встановили. Перевіряємо.
У осередок А34 ми написали першу букву «а». Вийшло попереджувальне про помилку вікно.
Якщо ми введемо число менше або більше 3 знаків, знову вийде вікно, що вказує на помилку.
Увага!
Можна написати повідомлення – вказати у чому помилка, або написати підказку, як правильно написати код. Для цього потрібно перейти на закладку "Повідомлення про помилку" або на закладку "Повідомлення для введення".
Як встановити повідомлення про помилку або підказки для заповнення комірки, читайте у статті "Перевірка даних в Excel".
Другий варіант.
Перевірка даних, що вводяться вExcel.
У формулі можна написати будь-яку кількість знаків, будь-які літери, кілька літер тощо. Наприклад, таку формулу.
=І(ЛЕВСІМВ(C33)="Фрукт/";ДЛСТР(C32)=8;ЕЧИСЛО(ЗНАЧЕНИЙ(ПРАВСІМВ(C32;2)))))
Вийшов такий код.
Функцію перевірки даних можна використовувати для заборони виправлення даних у таблиці заднім числом. Докладніше про такий спосіб читайте у статті «Щоб не виправляли дані в таблиці Excelзаднім числом" .
Можна заборонити вводити повторювані дані. Це спосіб дивіться у статті «Заборонити вводити значення, що повторюються в Excel» .
В Excel можна зробити саму просту таблицюможна зробити складну таблицю, з фільтрами, і т.д. Якими способами зробити таблицю в Excel, дивіться у статті

При введенні великої кількості інформації в комірки таблиці легко припуститися помилки. У EXCEL існує інструмент для перевірки введених даних одразу після натискання клавіші ENTER – Перевірка даних.

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

A. Перевірка введених значень

Як видно на малюнку знизу, умови перевірки значення можна налаштувати дуже гнучко.

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

• Ціле число. У комірку дозволено введення лише цілих чисел, причому які належать певному діапазону;
• Справжнє. У комірку дозволено введення лише чисел, у тому числі з десятковою частиною (не можна ввести текст, дату ввести можна);
• Дата.Передбачається, що в комірку вводитимуться дати починаючи від 01.01.1900 до 31.12.9999. Детальніше про формат Дата - у статті
• Час. Передбачається, що в комірку з Перевіркою данийцього типу вводитиметься час. Наприклад, малюнку нижче наведено умова, як у осередок дозволено вводити час що належить лише другій половині дня, тобто. від 12:00:00 до 23:59:59. Замість утомливого введення значення 12:00:00 можна використовувати його числовий еквівалент 0,5. Можливість введення чисел замість часу випливає з того, що будь-якій даті в EXCEL зіставлено позитивне ціле число, а отже часу (бо це частина доби) відповідає дробова частина числа (наприклад, 0,5 – це опівдні). Числовим еквівалентом для 23:59:59 буде 0,99999.

• Довжина тексту. У комірку дозволено введення лише певної кількості символів. При цьому обмеження можна вводити і числа та дати, головне, щоб кількість введених символів не суперечила обмеження за довжиною тексту. Наприклад, при обмеженні кількості символів менше 5 не можна ввести дату пізніше 13/10/2173, т.к. їй відповідає число 99999, а 14/10/2173 - це 100000, тобто. 6 символів. Цікаво, що при обмеженні, наприклад, менше 5 символів, ви не зможете ввести в комірку формулу = КОРІНЬ (2), т.к. результат =1,4142135623731 (залежно від заданої в EXCEL точності), а ось =КОРІНЬ(4) - зможете, адже результат =2, а це тільки 1 символ.
• перелік. Напевно, найцікавіший тип даних. В цьому випадку введення значень у комірку можна обмежити раніше визначеним списком. Наприклад, якщо як джерело вказати через точку з комою одиниці виміру товару шт;кг;кв.м;куб.м, то нічого іншого, окрім цих 4-х значень зі списку, вам вибрати не вдасться. У джерелі можна вказати діапазон комірок, що містить заздалегідь сформований список або посилання на . Приклад наведено у статті
• Інший. У комірку дозволено введення значень, що задовольняють складнішим критеріям. Для завдання критеріїв потрібно використовувати формулу. Розглянемо цю умову докладніше.

При вибраному типі Інший,в полі ФормулаНеобхідно запровадити формулу до розрахунку логічного значення. Якщо воно набуває значення ІСТИНА, то таке значення дозволено вводити в комірку, якщо БРЕХНЯ, то ваше значення ввести не вийде. Як аргумент формули потрібно використовувати посилання на саму комірку, до якої застосовується Перевірка данихабо посилання на комірку, що залежить від неї. Наприклад,

Щоб	Введіть формулу	Пояснення
Комірка B2 містила лише текст	ЕТЕКСТ(B2)	У Тип данихнемає можливості вибрати тип Тексттому доводиться цього домагатися побічно. Ви можете Перевірку данихзастосувати прямо до осередку B2
Допустити введення значення в комірку B1 тільки у випадку, якщо після введення значення в комірці D1 буде більше 100, в D2 менше, ніж 400	І(D1>100; D2<400)	Перевірку данихзастосовуємо до осередку B1 . При цьому в осередку D1 введено формулу =B1*2 , а в D2 - Формула = B1 * 3 . Хоча ця формула еквівалентна обмеженню Справжнєз діапазоном від 50 до 133,33, але при більш складних зв'язках осередків, цей прийом може бути корисним
Значення в осередку, що містить вік працівника ( З 1 ), завжди має бути більше числа повних років роботи ( D1 ) плюс 18 (мінімальний вік прийому працювати)	=ЯКЩО(C1>D1+18;ІСТИНА;БРЕХНЯ)	При заповненні таблиці даними про вік та стаж роботи можна поставити цю перевірку для обох осередків ( C1 і D1 ). Для цього потрібно виділити відразу 2 осередки, викликати Перевірку данихі трохи модифікувати формулу =ЯКЩО($C1>$D1+18;ІСТИНА;БРЕХНЯ)
Усі дані в діапазоні осередків A1:A20 містили значення	=РАХУНКИ($A$1:$A$20;A1)=1 =ПОШУКПОЗ(A1;$A:$A;0)=РЯДОК(A1)	Необхідно виділити осередки А1: А20 , викликати інструмент Перевірка данихта ввести формулу. Другу формулу можна використовувати для всього стовпця А Для цього необхідно виділити не діапазон, а весь стовпець А
Значення в осередку, що містить ім'я коду продукту ( B5 ), завжди починалося зі стандартного префікса "ID-" і мало довжину не менше 10 знаків.	=І(ЛЕВСИМВ(B5;3)="ID-"; ДЛСТР(B5)>9)	Перевірку даних вводимо для комірки B5

При виділенні кількох осередків, де потрібно, не забувайте вказувати абсолютне посилання на осередки (наприклад, $A$1:$A$20 ).

При використанні інструменту Перевірка даних, передбачається, що в комірку вводитимуться константи ( 123, товар1, 01.05.2010та ін), хоча ніхто не забороняє вводити і формули. У цьому випадку перевірятиметься однаково результат обчислення формули. Взагалі вводити формули в осередки з перевіркою даних не раджу – легко заплутатися. У цьому випадку раджу використовувати.

Ст.Відображення коментаря, якщо комірка є поточною.

Використовуйте вкладку Повідомлення для виведення, щоб відобразити коментар.

На відміну від звичайної примітки ( Рецензування/ Примітка/ Створити примітку), яке пропадає після того, як курсор миші йде з комірки (якщо не активна опція Показати всі примітки), цей коментар відображається завжди, коли осередок виділено.

З.Виведення докладного повідомлення про помилку.

Після введення помилкового значення Перевірка данихможе відобразити докладне повідомленняпро те, що було зроблено негаразд. Це якийсь аналог Msgbox()з VBA.

D.Створення зв'язаних діапазонів (списків)

Е. Використання у правилах посилань на інші листи

В EXCEL 2007 в Перевірка даних, як і в не можна прямо вказати посилання на діапазони іншого аркуша, наприклад, так =Аркуш2!$A$1 . Дозволяють обійти це обмеження використання.

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

У Excel 2010 можна використовувати правила перевірки даних, що посилаються на значення на інших аркушах. В Excel 2007 та Excel 97-2003 перевірка даних цього типу не підтримується та не відображається на аркуші. Однак усі правила перевірки даних залишаються доступними у книзі та застосовуються при повторному відкритті книги в Excel 2010, якщо вони не були змінені у Excel 2007 чи Excel 97-2003.

F. Як спрацьовує Перевірка даних

Перевірка данихявно спрацьовує при введенні в комірку значень з клавіатури з наступним натисканням клавіші ENTER. У цьому випадку з'являється вікно з описом помилки.

Якщо значення вставляються через Буфер обміну (Вставити значення) або з використанням сполучення клавіш CTRL+ D(Копіювання значення з комірки зверху) або копіюються зверху вниз, то перевірка в явному вигляді не здійснюється. Крім того, при копіюванні значень можна взагалі випадково видалити правила Перевірки данихнаприклад, якщо в осередку джерела не визначено Перевірка даних, а дані з неї вставляються через Буфер обміна з використанням комбінації клавіш CTRL+V.

Пояснимо на прикладі. Припустимо, до осередку А1 застосована Перевірка данихз умовою перевірки Інший, де в полі формула введено = РЯДОК (A1) = 1, тобто. для всіх осередків з першого рядка умова Перевірки данихбуде приймати значення ІСТИНА, для інших рядків - БРЕХНЯ незалежно від змісту осередку.

Тепер виділимо осередок А2 і натиснемо CTRL+D. Значення з А1 скопіюється в А2 разом з умовою Перевірки даних. Незважаючи на те, що тепер умова Перевірки данихбуде приймати значення БРЕХНЯ, ніякого попереджуючого повідомлення виведено не буде. Щоб переконатися, що дані в осередках відповідають умовам визначеним у Перевірка даних, потрібно викликати команду меню Обвести невірні дані (). Осередки з невірними даними будуть обведені червоними овалами. Тепер знову виділимо вічко А2 та натиснемо клавішу F2(увійдемо в режим Правки), потім натиснемо ENTER- з'явиться вікно з повідомленням, що введене значення неправильне.

Існує ще один спосіб обходу перевірки даних. Припустимо, введення в комірку обмежено значеннями від 1 до 3. Тепер в будь-яку іншу комірку без Перевірки данихвведемо значення 4. Виділимо цей осередок, в Рядок формулвиділимо значення 4 і скопіюємо його в Буфер обміну. Тепер виділимо комірку з Перевіркою данихі натиснемо CTRL+V. Значення вставилося в комірку! Крім того, Перевірка данихзалишилася недоторканою на відміну від випадку, коли через Буфер обміну, наприклад, вставляється значення WORD. Щоб переконатися, що дані в осередку не відповідають умовам, визначеним у Перевірка даних, потрібно викликати команду меню Обвести невірні дані (Дані/ Робота з даними/ Перевірка даних/ Обвести невірні дані).

G. Пошук осередків з Перевіркою даних

Якщо на аркуші багато осередків з Перевіркою даних, то можна використовувати інструмент ( Головна/ Знайти та виділити/ Виділення групи осередків).

Опція Перевірка данихцього інструменту дозволяє виділити осередки, для яких проводиться перевірка допустимості даних (задана за допомогою команди Дані/ Робота з даними/ Перевірка даних). При виборі перемикача Усіхбудуть виділені всі такі осередки. При виборі опції Цих жвиділяються лише ті комірки, котрим встановлені самі правила перевірки даних, як і активної комірки.