Excel инструмент проверка данных

При вводе большого количества информации в ячейки таблицы легко допустить ошибку. В EXCEL существует инструмент для проверки введенных данных сразу после нажатия клавиши ENTER – Проверка данных.

Инструмент

Проверка данных

(

) не был бы столь популярным, если бы его функциональность ограничивалась бы только собственно проверкой. Ниже кратко перечислены основные приемы работы с этим инструментом.

A. Проверка введенных значений

Как видно на рисунке снизу, условия проверки вводимого значения можно настроить очень гибко.

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

• 
  Целое число
  
  . В ячейку разрешен ввод только целых чисел, причем принадлежащих определенному диапазону;
• 
  Действительное
  
  . В ячейку разрешен ввод только чисел, в том числе с десятичной частью (нельзя ввести текст, дату ввести можно);
• 
  Дата.
  
  Предполагается, что в ячейку будут вводиться даты начиная от 01.01.1900 до 31.12.9999. Подробнее о формате Дата  — в статье

  Как Excel хранит дату и время

• 
  Время
  
  . Предполагается, что в ячейку с
  
  Проверкой данный
  
  этого типа будет вводиться время. Например, на рисунке ниже приведено условие, когда в ячейку разрешено вводить время принадлежащее только второй половине дня, т.е. от 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-х значений из списка вам выбрать не удастся. В источнике можно указать диапазон ячеек, содержащий заранее сформированный список или ссылку на

  Именованную формулу

  . Пример приведен в статье

  Выпадающий (раскрывающийся) список

• 
  Другой
  
  . В ячейку разрешен ввод значений удовлетворяющих более сложным критериям. Для задания критериев необходимо использовать формулу. Рассмотрим это условие подробнее.

При выбранном типе

Другой,

в поле

Формула

нужно ввести формулу для расчета логического значения. Если оно принимает значение ИСТИНА, то такое значение разрешено вводить в ячейку, если ЛОЖЬ, то ваше значение ввести не получится. В качестве аргумента формулы нужно использовать ссылку на саму ячейку, к которой применяется

Проверка данных

или ссылку на зависящую от нее ячейку. Например,

При выделении нескольких ячеек, там где нужно, не забывайте указывать абсолютную ссылку на ячейки (например,

$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. Поиск ячеек с Проверкой данных

Если на листе много ячеек с

Проверкой данных

, то можно использовать инструмент

Выделение группы ячеек

(

).

Опция

Проверка данных

этого инструмента позволяет выделить ячейки, для которых проводится проверка допустимости данных (заданная с помощью команды

). При выборе переключателя

Всех

будут выделены все такие ячейки. При выборе опции

Этих

же

выделяются только те ячейки, для которых установлены те же правила проверки данных, что и для активной ячейки.

Without question, Microsoft Excel is the world’s most popular spreadsheet application. It features calculation, VBA, graphing tools, and pivot tables to manage mountains of data. You can also use it to organize, format, and calculate formulas within your spreadsheets’ cells. Excel also provides powerful visuals that summarize and illustrate data insights using charts and graphs.

As expansive as those features may seem, Excel is a pretty straightforward software to learn. Once mastered, you’ll appreciate how much its tools simplify your workload. Take data validation, for instance. If you’re entering data within a spreadsheet and find most of the entries to be repetitive, you can use data validation to create a drop-down list instead of typing out the value each time. Or perhaps you’re catching a lot of invalid responses within the shared workbook and want to prevent certain entries. 

If either of those scenarios sounds familiar, keep reading to see how you can use data validation to improve your workflow. 

What is data validation?

In simplistic terms, data validation is a tool within Excel that allows users to control what data is permissible in their worksheets. Users can create a drop-down list in a single cell, designate custom rules for what they can enter, and set criteria on restricted entries. For example, one can use data validation to ensure a value contained within a cell is between X and Y number, ensure a text entry is less than 30 characters, or ensure a date occurs within the next 15 days. 

Data validation criteria is also useful when you want to display error messages when you or another user inputs invalid data, or if you are looking to present predefined choices within a drop-down menu. 

One thing that data validation does an exceptional job of is creating foolproof sheets. By telling Excel what values a user can select, the spreadsheet can be kept clean, precise, and to the point. 

There are many use cases for drop-down lists in Excel, such as getting people to complete a form or managing large sets of data. 

Where is the data validation option? 

Because it’s one of the most commonly used tools within Excel, the data validation prompt is conveniently available on the Excel ribbon. Simply navigate to the Data tab, and click “Data Validation.” 

If you’re having trouble finding the data validation tool within Excel’s interface, the image below will give you a better idea of where to find it. If your Excel ribbon is hidden, you won’t be able to see it. All you have to do is click on the tab that says data, and it will expand to give you a full view of the data options, including data validation.

How do I create a drop-down list?

Now that we know where to locate the data validation tool, we can get started with learning how to use it. 

First on our list is the drop-down list. As we hinted at earlier in the blog, Excel’s drop-down lists are an excellent way to present a menu of choices within a single cell in a spreadsheet. When you’re dealing with a large amount of data, these drop-down lists will be your key to keeping your spreadsheet clean and organized.

Creating a drop-down list is very simple. Here are the complete steps:

1. The first thing you want to do is create a new blank sheet. This is where you’ll enter all the options that you want on your list. You could create this list on the same spreadsheet where the drop-down menu will appear, but having a designated sheet for list values will make for a cleaner, more organized workbook. 
2. In the blank sheet, add all of the items that you would like to appear in your drop-down list. These should all be entered in a single row or column, and be sure to list them in the order that you expect them to appear in the drop-down menu. 
3. Once you’ve entered in the complete list, you should select the entire range of cells that contain your entries. So if you entered True and False in cells A2 and A3, select cells A2 and A3. After that, right-click on the selection and select the option to “Define Name.”
4. In the “Name” field, enter a name for your list. Most people use a name that is closely related to the entries. For instance, a list of produce might be named Produce. Whatever you decide to label it, be careful not to include any spaces between the characters in the name. 
5. You should also see a field for ‘Scope.” Select “Workbook” here so that your drop-down list is available to use within all of the sheets in your workbook. 
6. Now that you have named your list and made it accessible, you can click “OK” to save your settings. Next, you’ll want to navigate to the cell where you want the list to appear.
7. Once you have selected a cell, you’ll want to go back to the “Data” ribbon that we talked about earlier and click on “Data Validation.” You should see a prompt that looks similar to the image above. 
8. Stay on the “Settings” tab and go to the “Allow” menu. Choose the list option so that Excel knows that you want to add a drop-down menu to the selected cell. 
9. After clicking on the “list” option, a “Source” field should appear. This is where you will enter the name of the list that you created in the fourth step. Enter an “=” sign followed by the list name.
10. Finally, click “OK,” highlight any one of the cells. Now when you select the cell that you chose to insert the drop-down list, a drop-down arrow will appear. This is how you pull up your menu to see all of the available options. Simply select a choice from the drop-down list to add it to the cell. 

You can use worksheet references to supply values to a drop-down menu. For example, all items within a specific range can be defined as drop-down list options within the data validation settings window. Keep in mind that you will always have to enter ranges as absolute addresses with dollar signs. These characters prevent the range from changing if the data validation runs across other cells in the worksheet. 

Consider this to be one of the most important advanced Excel skills for professionals.

What are the different types of validation options? 

There are eight different data validation rules.

• Any Value: No validation is needed. This is the default option. 
• Whole Number: Only whole numbers are allowed. For example, users can accept any number between 1 and 10 as input. 
• Decimal: Only allows decimal values. 
• List: Only allows values from a predefined list. This is one of the most common data validation options. You can enter the allowed values using the “Settings” tab under drop-down menu controls.
• Dates: Only dates within a specified range are allowed.
• Time: Only times within a specified range are allowed.
• Text length: Only accepts input based on a certain text length. 
• Custom: Allows validation using a custom formula, which can create additional criteria for data validation. For example, custom data validation can require users to input data in all uppercase characters, containing a set character string or a date within the next 30 days. 

Depending on the validation criteria, Settings will allow you to set multiple parameters. For example, allowing a Whole Number will prompt you to choose from the following options, followed by a “Maximum” option.

• Between: Will only apply to whole numbers within a set range
• Not between: Will exclude all whole numbers within the specified range
• Equal to: Will only apply to the value listed
• Not equal to: Will exclude the value listed
• Greater than: Will only apply to whole numbers greater than the value listed
• Less than: Will only apply to whole numbers lower than the value listed
• Greater than or equal to: Will apply to the number listed, along with every number with a greater value
• Less than or equal to: Will apply to the number listed, along with every number with a lesser value

What other functions are available in the data validation window?

In addition to the “Settings” tab as an option to enter validation criteria for data in a cell, there are two other tabs for “Input Message” and “Error Alert.”

The “Input Message” tab allows users to display a message when a user selects a cell with data validation applied. It can be as detailed or as vague as you like, informing the user what is allowed or not allowed. Using the “Input Message” tab is completely optional.

The “Error Alert” tab controls what happens when validation does not occur. For example, setting the style to “Stop” prevents the input from being entered in the first place. Once the “Stop” alert window appears, users have the option to Retry or Cancel. Error alerts comprise a style, a title, and an error message. 

Other styles like “Information” or “Warning” inform users to enter non-validation values. Warning windows have “Yes,” “No,” and “Cancel” to prompt accepting, rejecting (edit), and canceling the invalid data. Information windows have two options: “OK” (accept) and “Cancel” (remove). It is very easy to set an error message.

Can I use a list on a different sheet to create a drop-down? 

If you want to avoid using a named Excel table, create a named range and apply that as a drop-down list source. The source list can be on the same sheet or a different sheet than the drop-down list. 

Can I copy and paste drop-down lists in Excel?

Yes, you may copy and paste all data validation cells to other cells. Doing this will carry all of the data validation rules over to the new cell.

To apply a drop-down list from one cell to a range, copy the cell and paste it across the range. This will make the drop-down list available and copy the formatting as well.

All about delimited lists

In addition to referring to an items list on a worksheet, you can type a list directly in the “Source” box. You should separate each item with a comma. 

Here is an example.

Excellent, Good, Fair, Poor

There are two caveats with delimited lists.

1) All words are case-sensitive. Typing a word in all CAPS will display an error message.

2) Additional spaces before or after items are accepted. No error message will appear. 

Users are also able to type items that do not appear in the drop-down list. Simply go to the Error Alert tab and remove the checkmark to turn it off. 

Can I perform data validation using Google Sheets?

Yes, data validation is compatible with Google Sheets. Google Sheets utilizes a more straightforward process than Excel, eliminating the need to name a cell range. 

Here are steps you can take to create a data validation drop-down list using Google Sheets.

1. Visit a blank or existing worksheet. As with Excel, creating a new sheet helps keep the existing spreadsheet clean and organized. 
2. Right-click on the cell where you like the drop-down list to appear.
3. Select “Data Validation” towards the bottom of the pop-up menu
4. Within the dialog box, navigate to the Criteria: field and select Select Data Range
5. Go to the worksheet and highlight all of the data that you want to appear in the drop-down list.
6. Click OK and Save to close the dialog box.
7. The preselected cell will display a drop-down arrow. Click the arrow to view all items in the drop-down list. Users also have the option to type the input, but the input must be identical to how it appears with no misspellings or spacing issues. 
8. The selected value will populate in the cell. 

Data validation is one of the easiest things to learn for most first-time Excel users. Even better, the tool becomes progressively easier over time with practice.

What are some popular data validation rules?

Here are some of the most common data validation rules.

Preventing future dates

Entering the wrong dates on a spreadsheet occurs more often than you think. Data validation allows users to set a date in the past or apply today’s date.

1. Open the Data Validation window
2. From the ‘Allow’ List, select ‘Date.’
3. From the ‘Data’ dropdown, select “Less than or equal to from”
4. In the ‘End Date’ box, type in the formula =TODAY()

The ‘TODAY () formula automatically returns today’s date.

Preventing duplicate values

To prevent users from entering duplicate values, perform the following steps.

1. Select the range.
2. Open the Data Validation window.
3. From the ”’Allow” List, select “Custom.”
4. In the “Formula” box, enter the following formula – =COUNTIF($A$2:$A$20,A3)=1.
5. Optional: visit the Input Message and Error Alert tab if you wish to display an input or error alert message.

This counts the number of cells in the range that are equal to the value in cell A3. Note that an absolute reference ($A$2:$A$20) was created to carry over the formula to other cells.

Set a text length

You may also create validation rules to restrict the number of characters in a cell. Let’s use the steps below to confine text length in a cell to seven characters.

1. Open the Data Validation window from the Data tab.
2. From the ‘Data’ dropdown, select “equal to.”
3. Under the ‘Length’ box, type in the number seven as ‘7.’ This tells Excel that it should not allow more than seven characters within the given cell.

Require specific text for entries

1. Open the Data Validation window.
2. From the “Allow” list, select “Custom.”
3. Enter the following formula: ISNUMBER(FIND(“TEXT”.A2)).

In this formula, the FIND function is what allows you to search for the word “TEXT.”  If Excel does not find “TEXT” anywhere in the spreadsheet, then an error appears. The ISNUMBER portion lets users know if the FIND function was successful, returning the value “True” if found and “False” if not found.

All in all, the data validation tool is an excellent way to make your spreadsheets error-free. This makes it easier to pass between different collaborators without the risk of worksheet formulas or cell contents being severely compromised.

Иногда возникает необходимость перестраховаться от ошибок перед вводом в ячейки данных несоответствующим определенным условиям. Например, в номенклатуре магазина товаров и цен недолжно быть возможности ввода отрицательных чисел и нулей. Ведь магазин не раздает товар с доплатой или за бесплатно.

Проверка вводимых данных в Excel

Для того, чтобы предотвратить ошибки оператора компьютерного набору в Excel, будем использовать проверку данных ввода. Тем самым создадим ему комфортные условия для рутинной работы, где сложно не допустить ошибки.

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

1. Выделите столбец B и выберите инструмент «Данные»-«Проверка данных».



2. Заполняем значениями поля на вкладке «Параметры» как показано на рисунке:



3. На вкладке «Сообщение для ввода» создаем особое примечание, в котором при помещения курсора на ячейку будет отображаться условия для ее заполнения. И жмем ОК.

Теперь проверим. В ячейку B2 введите натуральное число, а в ячейку B3 отрицательное. Как видно в ячейке B3 действие оператора набора – заблокировано. Отображается сообщение об ошибке: «Введенное значение неверно».

Примечание. При желании можно написать собственный текст для ошибки на третей закладке настроек инструмента «Сообщение об ошибке».

Чтобы удалить проверку данных в Excel нужно: выделить соответствующий диапазон ячеек, выбрать инструмент и нажать на кнопку «Очистить все» (указано на втором рисунке).



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

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

Внимание! Если ячейки будут скопированы, а не введены то их значения так же не будут проверены.

Чтобы проверить соответствуют ли все введенные данные, определенным условиям в столбце и нет ли там ошибок, следует использовать другой инструмент: «Данные»-«Проверка данных»-«Обвести неверные данные».

Если значения в столбце B должны соответствовать определенным условиям, но содержит ошибки, то все они будут обведены красным овалом. Этот инструмент очень удобно использовать, когда нужно проверить уже введенные или скопированные данные.

Конечно, можно выполнить проверку данных в столбце с помощью логической функции Excel – «ЕСЛИ». Или условное форматирование. Но применение инструмента «Проверка данных» – более эффективно, удобно и продуктивно для данной задачи. Особенно если нам нужно одновременно выполнить проверку по нескольким столбцам. В таком случаи более заметна рациональность его использования.

Хитрости »

1 Май 2011              113497 просмотров

Проверка данных

Проверка данных является неплохим инструментом Excel для контроля за вносимыми на листах изменениями, не прибегая к помощи VBA. С её помощью можно ограничить ввод в ячейку, разрешив вводить только определенные даты либо числа. При этом можно запретить вводить текст в тех ячейках, в которых должны быть исключительно числа, а так же задать диапазон чисел(например, только от 1 до 1000).
Для чего это вообще может быть нужно? Например, есть некий шаблон с формулами и для корректной работы формул требуются правильно введенные исходные данные. С помощью Проверки данных можно разрешить пользователю вводить только тот тип и интервал данных, который может обработать формула, не возвращая значение ошибки или некорректный результат.
Иными словами, проверка данных предназначена для контроля вводимых в ячейки данных. При правильном использовании очень мощная и удобная вещь в различных шаблонах, которые должны заполняться разными людьми.

Чтобы использовать проверку вводимых данных необходимо выделить ячейку или несколько ячеек, ввод данных в которые надо отслеживать и ограничить. Далее переходим на вкладку Данные(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) — тоже, что и в предыдущем пункте, но ввести можно любое число — хоть целое, хоть дробное, но невозможно будет ввести текст

Примечание: применив данные типы проверок, внести в ячейку можно только число. Занести текст Excel уже не разрешит. Однако стоит помнить, что дата и время Excel воспринимает и хранит именно как ЧИСЛОВЫЕ значения, поэтому ввод даты и времени тоже будет разрешен, если он не противоречит остальным условиям проверки(см.ниже). Но для проверки Целое число разрешен будет ввод только даты, т.к. время Excel хранит как дробное значение(кроме 24:00:00 и 00:00:00, которые Excel воспринимает как 1 и 0 соответственно). Более подробно о восприятии Excel-ем данных читайте в статье Как Excel воспринимает данные?.

• Дата(Date) — В ячейке может содержаться только значение даты. Дата может быть записана в любом формате, допустимом в текущей локализации Windows. Здесь тот же нюанс, что и проверкой на числа, только в обратную сторону — любая дата это число, поэтому по сути можно будет ввести любое целое число, которое может быть переведено Excel-м в дату.
• Время(Time) — можно записывать значения в формате времени для текущей локализации и так же как с датой в ячейку можно будет вводить не только время, но и любые числа: целые или дробные
• Длина текста(Text lenght) — позволяет ограничить количество вводимых в ячейку символов. Данные могут являться и числом и текстом и временем. Чем угодно, только должны соответствовать остальным условиям проверки. Часто применяется для контроля ввода данных по ИНН, КПП, БИК, артикулам и т.п.

Если посмотреть на картинку в начале статьи, то можно увидеть еще три поля, помимо тех, которые я перечислил: Значение(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.

• Другой(Custom) — на мой взгляд самый интересный и самый мощный тип проверки. Здесь нам предоставляется возможность более широко контролировать ввод данных. Для заполнения есть только одно поле — Формула(Formula). В него необходимо записать формулу и при каждом вводе значений в ячейку, Excel проверит введенное выражение на ИСТИНУ. Если быть точнее то Excel сначала вычислит формулу в этом поле ориентируясь на введенное в ячейку с проверкой данных значение, а потом проверит — возвращает ли формула значение ИСТИНА(TRUE). Если результатом будет ИСТИНА(TRUE), то введенное значение будет сохранено в ячейке, а если ЛОЖЬ(FALSE), то будет выдано окно с сообщением об ошибке:
  

  Приведу простой пример. В ячейку А1 введем число 1. Выделяем ячейку В1 — назначаем проверку данных-Другой. В поле Формула вписываем: =B1=A1. Теперь в ячейку В1 можно вписать только значение, которое полностью идентично значению в ячейке А1.
  Обращаю особое внимание на то, что в большинстве случаев в вводимой формуле должно происходить некое сравнение с той ячейкой, в которой создается проверка данных. Например, если мы хотим, чтобы в В1 можно было ввести только значение текущей даты, то мы должны записать формулу так:
  =B1=СЕГОДНЯ()
  =B1=TODAY()
  Т.е. мы сравниваем введенное в В1 значение с выражением, записанным правее от знака равенства. А правее у нас функция СЕГОДНЯ(TODAY), которая возвращает текущую дату.

  Важно: если проверка данных в дальнейшем будет скопирована в другие ячейки, то ссылки будут смещены так, будто это простая формула. Поэтому при создании формул в проверке данных необходимо учитывать возможность смещения и при необходимости закреплять ссылки(знак доллара — $).

---

 
Вкладка

Сообщение для ввода (Input Message)

Здесь указывается текст, который будет отображаться при выделении ячейки с проверкой данных:

Самое удобное в данном сообщение то, что можно не устанавливать саму проверку данных, а заполнить только эту вкладку. Тогда сообщение будет появляться при выделении ячейки, но никаких ограничений для ввода в ячейку не будет. Т.е. использовать эту возможность просто для дополнительного информирования о предпочтительном формате данных или просто указывать тип вводимой в ячейку информации.

 
Вкладка

Сообщение об ошибке(Error Alert)

Здесь указывается отображать сообщение об ошибочном вводе или нет, и сам тип выводимого сообщения об ошибке.

• Выводить сообщение об ошибке(Show error alert after invalid data is entered) — в большинстве случаев необходимо установить галочку. Если галочка снята, то проверка вводимых в ячейку данных не будет осуществляться.
• Вид(Style)
  • Останов, Сообщение(Stop, Information) — можно ввести только разрешенные проверкой значения. Различается только вид сообщения(пример сообщения Останов приведен на рисунке выше).
  • Предупреждение(Warning) — в ячейку можно ввести любое значение, но при вводе значения, противоречащего проверке, появиться предупреждающее сообщение с подтверждением ввода данных.
• Заголовок(Title) — текст, который будет показан в заголовке сообщения об ошибке. Если не указан, то в заголовке будет написано Microsoft Excel.
• Сообщение(Error message) — непосредственно текст самого сообщения об ошибке. Если не указан, то будет показан текст примерно следующего содержания:
  Это значение не соответствует ограничениям по проверке данных, установленным для этой ячейки
  (This value doesn’t match the data validation restrictions defined for this cell)

---

Как скопировать проверку данных на другие ячейки
Все очень просто — копируем ячейку с нужной проверкой данных -выделяем ячейки для создания в них такого же условия -Правая кнопка мыши —Специальная вставка(Paste Special) -в окне выбираем Условия на значения(Validation) —Ок:


---

Как удалить проверку данных из ячеек
Выделяем необходимые ячейки -вкладка Данные(Data)—Проверка данных(Data Validation). В поле Тип данных(Allow) устанавливаем Любое значение(Any Value) —Ок.

---

Маленькая хитрость использования проверки данных
Если проверка данных на ячейки была установлена уже после того, как данные были внесены, то очень неплохо понять, все ли данные соответствуют условиям проверки. Сделать это несложно. После того, как ячейкам была назначена проверка данных переходим на вкладку Данные(Data)—Проверка данных(Data Validation) -раскрываем меню и выбираем Обвести неверные данные(Circle Invalid Data). Все ячейки, данные в которых не соответствуют условиям проверки данных будут обведены красной линией:

Это можно использовать не только когда необходима проверка данных, но и просто для определения ошибочных значений. Например, есть список товара на складе и есть лист, в который этот товар вносился сотрудниками вручную. Необходимо определить какие наименования товара не соответствуют тем, которые заведены на складе. Создаем проверку данных Список(Выпадающие списки), указывая в качестве источника список товара на складе. Далее распространяем эту проверку на ячейки с занесенными сотрудниками данными. И теперь применяем функцию Обвести неверные данные.

Сразу после исправления неверных значений на те, которые есть в списке обводка исчезает. Если не все значения надо исправлять, а обводку тем не менее надо удалить, то после всех нужных правок просто переходим на вкладку Данные(Data) —Проверка данных(Data Validation) -раскрываем меню и выбираем Удалить обводку неверных данных(Clear Validation Circles)

---

Как обойти проверку данных или маленькая ложка дегтя
Необходимо всегда помнить, что какое бы условие на проверку не было установлено — значение в ячейке можно удалить, нажав кнопку Del. Либо просто скопировать ячейку из другой книги или листа и вставить на место проверки данных — проверка исчезнет, т.к. вся ячейка будет заменена скопированной. И от этого никак не уйти — такой проверку сделали разработчики. И даже защита листа здесь не поможет, т.к. для работы проверки данных ячейка не должна быть защищена.

---

Статья помогла? Поделись ссылкой с друзьями!

    Видеоуроки

---

Поиск по меткам

Access
apple watch
Multex
Power Query и Power BI
VBA управление кодами
Бесплатные надстройки
Дата и время
Записки
ИП
Надстройки
Печать
Политика Конфиденциальности
Почта
Программы
Работа с приложениями
Разработка приложений
Росстат
Тренинги и вебинары
Финансовые
Форматирование
Функции Excel
акции MulTEx
ссылки
статистика