Excel проверка данных несколько значений

Обновлено: 02.07.2024

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

01.01.2001;
01/01/2001;
1 января 2001 года и т.д.

В статье описывается функционал, позволяющий упростить процесс сбора и проверки данных в Excel.

Где находится?

Для настройки параметров проверки вводимых значений необходимо на вкладке «Данные» в области «Работа с данными» кликнуть по иконке «Проверка данных» либо выбрать аналогичный пункт из раскрывающегося меню:

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

Настройка условия проверки

Изначально требуется выбрать тип проверяемых данных, что будет являться первым условием. Всего предоставлено 8 вариантов:

Целое число;
Действительное число;
Список;
Дата;
Время;
Длина текста;
Другой.

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

Самым необычным видом является выпадающий список.

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

Всплывающая подсказка ячейки Excel

Пример всплывающей подсказки в Excel:

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

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

Применить множественную проверку данных в одной ячейке (пример 1)

Например, я хочу настроить проверку данных для ячеек с такими критериями: если введено число, оно должно быть меньше 100, если введен текст, он должен быть в списке от D2 до D7, как показано на следующем снимке экрана:

1. Выберите ячейки, к которым вы хотите применить множественную проверку данных, затем нажмите Данные > проверка достоверности данных > проверка достоверности данных, см. снимок экрана:

2. В проверка достоверности данных диалоговое окно под Параметры tab выполните следующие операции:

(1.) Выберите На заказ из Разрешить раскрывающийся список;

(2.) Введите эту формулу: =OR(A2<$C$2,COUNTIF($D$2:$D$7,A2)=1) в Формула текстовое окно.

Внимание: В приведенной выше формуле A2 это ячейка, к которой вы хотите применить проверку данных, C2 и D2: D7 являются критериями, необходимыми для применения проверки данных на основе.

3. Затем нажмите OK, с этого момента в ячейки можно вводить только те значения, которые соответствуют критериям, в противном случае появится окно с предупреждением, напоминающее вам, см. снимок экрана:

Применить множественную проверку данных в одной ячейке (пример 2)

В этом примере я разрешу вводить только текст «Kutools for Excel» или дату между 12 и 1 в определенные ячейки, как показано на скриншоте ниже. Чтобы решить эту задачу, сделайте следующее:

1. К проверка достоверности данных диалоговое окно, а в диалоговом окне выполните следующие операции:

(1.) Щелкните Параметры вкладка;

(2.) Выберите На заказ из файла Разрешить раскрывающийся список;

(3.) Введите эту формулу: =OR(A2=$C$2,AND(A2>=DATE(2017,12,1), A2<=DATE(2017,12,31))) в Формула текстовое окно.

Внимание: В приведенной выше формуле A2 это ячейка, к которой вы хотите применить проверку данных, C2, 2017,12,1 и 2017,12,31 являются критериями, необходимыми для применения проверки данных на основе.

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

Применить множественную проверку данных в одной ячейке (пример 3)

В третьем примере я хочу, чтобы текстовая строка начиналась с «KTE» или «www», а если она начинается с «KTE», можно разрешить только 6 символов; если он начинается с «www», можно разрешить только 10 символов, см. снимок экрана:

1, Перейти к проверка достоверности данных диалоговое окно, в диалоговом окне выполните следующие операции:

(1.) Щелкните Параметры вкладка;

(2.) Выберите На заказ из файла Разрешить раскрывающийся список;

(3.) Введите эту формулу: =OR(AND(LEFT(A2,3)="KTE",LEN(A2)=6),AND(LEFT(A2,3)="www",LEN(A2)=10)) в Формула текстовое окно.

Внимание: В приведенной выше формуле A2 - это ячейка, к которой вы хотите применить проверку данных, и вы можете изменить критерии в формуле на свои собственные.

2. Затем нажмите OK кнопку, и теперь разрешено вводить только те текстовые значения, которые соответствуют указанным вами критериям, см. снимок экрана:

Внимание: Может потребоваться применить другие критерии проверки данных, вы можете создавать свои собственные формулы для разных критериев по своему усмотрению.

Недавно дочь обратилась с вопросом, нельзя ли в Excel выпадающий в ячейке список сделать контекстным, например, зависящим от содержания ячейки, находящейся слева от ячейки со списком? Я довольно давно не использовал в работе выпадающие списки, поэтому для начала решил освежить свои знания по вопросу проверки данных в Excel. Собственно, ответ на вопрос дочери см. Как обойти ограничение Excel, и сделать выпадающий список зависимым.

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

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

Средство проверки вводимых данных имеет принципиальный недостаток — если пользователь скопирует ячейку, а потом вставит ее в ячейку, в которой производится проверка вводимых данных, то все определенные для проверки формулы исчезнут.

Определение критерия проверки

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

1. Выделите ячейку или диапазон ячеек.

2. Выберите вкладку Данные, область Работа с данными → Проверка данных. Excel отобразит диалоговое окно Проверка вводимых значений.

3. Щелкните на вкладке Параметры (рис. 2).

Рис. 2. Вкладка Параметры диалогового окна Проверка вводимых значений

4. Выберите одну из опций из раскрывающегося списка Тип данных. В зависимости от выбранной опции может измениться внешний вид вкладки Параметры путем добавления или удаления дополнительных элементов управления (для определения формулы нужно выбрать опцию Другой).

5. С помощью имеющихся на этой вкладке элементов управления задайте критерий проверки данных. Доступные элементы управления зависят от выбора, сделанного на предыдущем шаге.

После выполнения этих действий в выделенной ячейке или диапазоне будет производиться проверка вводимых данных.

Типы проверяемых данных

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

Во вкладке Параметры диалогового окна Проверка вводимых значений содержатся две опции.

В Excel имеется команда Данные → Работа с данными → Проверка данных → Обвести неверные данные, после выбора которой все неверные значения будут обведены красным кружком (рис. 3).

Рис. 3. Ячейки с неверными значениями (значения которых больше 100) обведены кружками

Создание раскрывающегося списка

Возможно, проверка вводимых данных чаще всего используется для создания раскрывающегося списка значений. На рис. 4 приведен пример, в котором имена месяцев, содержащиеся в диапазоне А1:А12, используются для создания раскрывающегося списка.

Рис. 4. Список, созданный с помощью средства проверки данных

Чтобы создать такой список:

1. Введите список значений в один столбец или строку. Эти значения будут использоваться в раскрывающемся списке.

2. Выберите ячейку, которая должна содержать раскрывающийся список (в нашем примере – D3).

3. Во вкладке Параметры диалогового окна Проверка вводимых данных выберите тип данных Список и в поле Источник укажите диапазон, который содержит список значений (в нашем примере – $А$1:$А$12).

4. Удостоверьтесь, что установлен флажок Список допустимых значений.

5. Сделайте другие установки в диалоговом окне Проверка вводимых данных, как описано в предыдущем разделе.

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

Если список должен содержать небольшое количество значений, то их можно ввести непосредственно в поле Источник во вкладке Параметры диалогового окна Проверка вводимых значений (это поле появится, если выбрать из раскрывающегося списка Тип данных тип Список). Между вводимыми значениями нужно вставить разделитель, определенный в соответствии с региональными настройками (для России – это точка с запятой).

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

Проверка данных с использованием формул

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

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

Тип ссылок на ячейки в формулах для проверки данных

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

Следующий пример пояснит, почему именно относительные ссылки надо использовать в формулах для проверки данных. Предположим, что в диапазон В2:В10 необходимо только нечетные числа. В диалоговом окне Проверка вводимых значений нет такого типа данных, поэтому для создания критерия проверки данных надо использовать формулу. Для создания такого критерия проверки данных выполните следующие действия.

1. Выделите диапазон В2:В10 таким образом, чтобы ячейка В2 стала активизированной.

2. Выберите команду Данные → Работа с данными → Проверка данных, чтобы открыть диалоговое окно Проверка вводимых значений.

3. Перейдите на вкладку Параметры и в списке Тип данных выберите Другой.

4. Введите следующую формулу в поле Формула (рис. 5) =ЕНЕЧЁТ(В2). В этой формуле применена функция ЕНЕЧЁТ, которая возвращает значение ИСТИНА, если ее аргумент является нечетным числом.

6. Щелкните на кнопке ОК, чтобы закрыть диалоговое окно Проверка вводимых значений.

Рис. 5. Ввод формулы в диалоговое окно Проверка вводимых значений

Заметьте, что введенная формула содержит ссылку на верхнюю левую ячейку выделенного диапазона. Эта формула должна применяться ко всему диапазону ячеек, поэтому следует ожидать, что каждая ячейка этого диапазона содержит такую же формулу. Поскольку в формуле ссыпка на ячейку относительная, то эта формула изменяется для каждой отдельной ячейки диапазона В2:В10. Чтобы в этом удостовериться, поставьте курсор, например, в ячейку В5, и откройте диалоговое окно Проверка вводимых значений. В этом окне вы должны увидеть формулу =ЕНЕЧЁТ(В5)

В общем случае, когда вводится формула для проверки данных в диапазон ячеек, следует использовать относительную ссылку на активизированную ячейку, которой, как правило, является верхняя левая ячейка выделенного диапазона. Исключение составляют ситуации, когда надо сделать ссылку на некоторую конкретную ячейку. Например, вы хотите, чтобы в диапазон А1:В10 вводились только такие значения, которые превышают значение в ячейке С1. Для этого используется формула =А1>$С$1

В таком случае ссылка на ячейку С1 делается абсолютной и поэтому данная ссылка не меняется во всех ячейках выделенного диапазона.

Примеры формул для проверки данных

Здесь приведено несколько примеров, в которых для проверки вводимых данных используются формулы. Все формулы были введены непосредственно в поле Формула во вкладке Параметры диалогового окна Проверка вводимых значений.

Ввод только текста. Для того чтобы разрешить ввод только текста (и запретить ввод числовых значений) в ячейку или диапазон, используется следующая формула: =ЕТЕКСТ(А1). Здесь предполагается, что А1 является активизированной ячейкой выделенного диапазона.

Ввод значений, больших, чем в предыдущей ячейке. Следующая формула проверки данных позволяет ввести число в ячейку только в том случае, если оно больше, чем значение в предыдущей ячейке: =А2>А1. В формуле предполагается, что активизированной ячейкой выделенного диапазона является ячейка А2. Заметьте, что эту формулу нельзя использовать в первой строке рабочего листа.

Ввод только уникальных значений. Следующая формула проверки вводимых данных не позволит пользователю ввести в диапазоне А1:С20 повторяющиеся значения: =СЧЁТЕСЛИ($А$1:$С$20;А1)=1. Здесь предполагается, что А1 является активизированной ячейкой выделенного диапазона. Обратите внимание на то, что в качестве первого аргумента функции СЧЁТЕСЛИ ($А$1:$С$20) используется абсолютная ссылка. Вторым аргументом (А1) является относительная ссылка, которая меняется для каждой ячейки выделенного диапазона. На рис. 6 показано, как работает эта формула. Здесь сделана попытка ввести в ячейку А5 значение 2, которое уже есть в диапазоне А1:С20.

Рис. 6. Использование средства проверки данных для предотвращения ввода дублирующихся значений

Ввод текста, начинающегося с буквы А. В следующей формуле используется прием, который позволяет проводить проверку по заданному символу. В данном случае формула вернет значение ИСТИНА, если ввести в ячейку строку, которая будет начинаться с буквы А (независимо от регистра): =ЛЕВСИМВ(А1)= " а " . В этой формуле предполагается, что активизированной ячейкой выделенного диапазона является ячейка А1.

Ниже приведена немного модифицированная формула проверки данных. С помощью этой формулы можно организовать ввод строки, которая состоит из пяти букв и начинается с буквы А:
=СЧЁТЕСЛИ (А1; " А. " ) =1

[1] Цитируется по книге Джон Уокенбах. Microsoft Excel 2007. Библия пользователя. – М: ООО «И.Д. Вильямс», 2008. – С. 482–489.