Разное

В экселе проверка данных: Применение проверки данных к ячейкам

Содержание

Дополнительные сведения о проверке данных

Перейти к основному контенту Microsoft

Поддержка

Поддержка

Поддержка

  • Главная
  • Microsoft 365
  • Office
  • Windows
  • Surface
  • Xbox
  • Больше
    • Все продукты Microsoft

    Как сделать выпадающий список в Excel с помощью проверки данных :: SYL. ru

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

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

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

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

    Первые четыре слова заносим в столбец, начиная с A1, вторые – с B1, третьи – с C1.

    Чтобы перейти непосредственно к вопросу о том, как сделать выпадающий список в Excel, зададим имена этим спискам. Для этого выделим значения в первом столбце, выберем пункт меню: «Формулы» — «Диспетчер имен» — «Присвоить имя». В появившейся форме в строке «Имя» заполняем «овощи», нажимаем «ОК». Аналогично повторяем для других списков, присвоив им имена «зелень» и «ягоды». В строке «Область» оставляем значение «Книга». Это позволяет обращаться к данным спискам из других листов книги.

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

    Теперь собственно формируем выпадающий список. Выбираем, например, ячейку D1 на другом листе открытой книги, открываем пункт меню: «Данные» – «Проверка данных». В открывшейся форме «Проверка вводимых значений» во вкладке «Параметры» в поле «Тип данных» выбираем «Список». Ставим курсор в поле «Источник», набираем знак равенства и нажимаем клавишу F3. В появившемся меню «Вставка имени» выбираем имя нужного нам списка, например, «овощи», затем нажимаем «ОК». Проверяем, чтобы стояли галочки в позициях «Игнорировать пустые ячейки» и «Список допустимых значений». Это обеспечивает возможность не заполнять это поле данными и вводить только данные из списка с указанным именем. Жмем «ОК».

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

    Другие варианты

    При заполнении поля «Источник» формы «Проверка вводимых значений» можно применить еще два варианта.

    1. Поставив курсор в это поле, выделить на данном листе книги область допустимых значений списка. Формула появится в поле после знака равенства. Как сделать выпадающий список в Excel с другого листа в этом случае? Надо аналогичный диапазон ячеек выделить на этом листе, а затем, поставив курсор в поле после знака равенства, написать наименование листа со списком и поставить восклицательный знак. Внимание: название листа не должно содержать пробелов!
    2. Перечислить допустимые значения в поле, разделяя их точкой с запятой. Это самый простой способ, который не допускает обращение к спискам на другом листе книги.

    Расширение диапазона допустимых значений

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

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

    Таким образом, мы получили выпадающий список в Excel.

    Двойная ссылка

    Если вам надо иметь возможность вводить в ячейку данные из нескольких списков по выбору, то это можно сделать таким образом. Сначала создаем список списков, из которых надо вводить данные. Пусть это будут наши три списка, указанные в первом подзаголовке данной статьи. На этом же листе книги, начиная, например, с ячейки F1, вводим список, содержащий имена ранее введенных списков: «овощи», «зелень», «ягоды». Задаем этому списку имя, например, «Список 1».

    Теперь (можно и на другом листе данной книги) выбираем, например, ячейку G1 и задаем для нее выпадающий список со ссылкой на имя «Список 1». Следующую ячейку, например, h2, заполняем с учетом выбранного значения в ячейке G1. Для этого в ячейке h2 формируем выпадающий список, как указано ранее, в поле «Источник» выбираем ячейку G1, но дорабатываем запись в этом поле, добавляя после знака равенства ДВССЫЛ, а остальную часть формулы взяв в круглые скобки: =ДВССЫЛ($G$1).

    Теперь при выборе в ячейке G1, например, значения «зелень», для заполнения ячейки h2 будет предложен выбор из значений этого списка.

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

    Советы и трюки по анализу данных в Microsoft Excel в сфере информационной безопасности

    Советы и трюки по анализу данных в Microsoft Excel в сфере информационной безопасности

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

    Авторы: Jake Nicastro, David Pany

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

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

    Один из наиболее эффективных инструментов для выполнения подобного анализа — Microsoft Excel, доступный практически всегда и полезный при решении широкого спектра задач. В этой статье мы рассмотрим разные трюки в Экселе, используемые для совместного анализа разнородных типов данных.

    Объединение разных типов данных в одну таблицу

    Утилиты наподобие FireEye Redline включают в себя функции для объединения разных типов событий в единой временной шкале. При использовании индивидуальных парсеров или артефактов в нестандартном формате бывает сложно объединить разнородной информации общую картину. Нормализация данных при помощи Excel в один набор с простыми и понятными столбцами позволяет состыковать разные типы.

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

    $SI Created

    $SI Modified

    File Name

    File Path

    File Size

    File MD5

    File Attributes

    File Deleted

    2019-10-14 23:13:04

    2019-10-14 23:33:45

    Default.rdp

    C:\Users\
    attacker\Documents\

    485

    c482e563df19a40
    1941c99888ac2f525

    Archive

    FALSE

    Event Gen Time

    Event ID

    Event Message

    Event Category

    Event User

    Event System

    2019-10-14 23:13:06

    4648

    A logon was attempted using explicit credentials.

    Subject:
    Security ID: DomainCorp\Administrator
    Account Name: Administrator
    Account Domain: DomainCorp
    Logon ID: 0x1b38fe
    Logon GUID: {00000000-0000-0000-0000-000000000000}
    Account Whose Credentials Were Used:
    Account Name: VictimUser
    Account Domain: DomainCorp
    Logon GUID: {00000000-0000-0000-0000-000000000000}
    Target Server:
    Target Server Name: DestinationServer
    Additional Information:
    Process Information:

    Process ID: 0x5ac
    Process Name: C:\Program Files\Internet Explorer\iexplore.exe
    Network Information:
    Network Address: —
    Port: -

    Logon

    Administrator

    SourceSystem

    KeyModified

    Key Path

    KeyName

    ValueName

    ValueText

    Type

    2019-10-14 23:33:46

    HKEY_USER\Software\Microsoft\Terminal Server Client\Servers\

    DestinationServer

    UsernameHInt

    VictimUser

    REG_SZ

    Поскольку вышеуказанные необработанные наборы данных имеют разные колонки и типы, довольно трудно проводить анализ в общем контексте. Если мы отформатируем данные при помощи конкатенации строк в Экселе, то сможем легко скомбинировать информацию в единую систему. Для форматирования можно использовать оператор «&» вместе с функцией присоединения, которая может пригодиться в поле с итогами.

    Пример команды для объединения данных, имеющих отношение к файловой системе и разделенных амперсантами, может выглядеть так: “=D2 & » | » & C2 & » | » & E2 & » | » & F2 & » | » & G2 & » | » & h3”. Комбинируя эту функцию форматирования в сочетании с колонками «Timestamp» и «Timestamp Type» мы можем получить все информацию для первичного анализа.

    Timestamp

    Timestamp Type

    Event

    2019-10-14 23:13:04

    $SI Created

    C:\Users\attacker\Documents\ | Default. rdp | 485 | c482e563df19a401941c99888ac2f525 | Archive | FALSE

    2019-10-14 23:13:06

    Event Gen Time

    4648 | A logon was attempted using explicit credentials.

    Subject:
    Security ID: DomainCorp\Administrator
    Account Name: Administrator
    Account Domain: DomainCorp
    Logon ID: 0x1b38fe
    Logon GUID: {00000000-0000-0000-0000-000000000000}
    Account Whose Credentials Were Used:
    Account Name: VictimUser
    Account Domain: DomainCorp
    Logon GUID: {00000000-0000-0000-0000-000000000000}
    Target Server:
    Target Server Name: DestinationServer
    Additional Information:
    Process Information:
    Process ID: 0x5ac
    Process Name: C:\Program Files\Internet Explorer\iexplore. exe
    Network Information:
    Network Address: —
    Port: — | Logon | Administrator | SourceSystem

    2019-10-14 23:33:45

    $SI Modified

    C:\Users\attacker\Documents\ | Default.rdp | 485 | c482e563df19a401941c99888ac2f525 | Archive | FALSE

    2019-10-14 23:33:46

    KeyModified

    HKEY_USER\Software\Microsoft\Terminal Server Client\Servers\ | DestinationServer | UsernameHInt | VictimUser

    После объединения трех разных таблиц и сортировки полученной информации по полю timestamp обнаруживается, что с аккаунта «DomainCorp\Administrator» из системы «SourceSystem» было подключение к серверу «DestinationServer» при помощи учетной записи «DomainCorp\VictimUser» через RDP.

    Преобразование часовых поясов

    Один из наиболее важных элементов при своевременной реакции на инциденты и криминалистическом анализе – хронометраж событий. Временной анализ позволяет выявить новые свидетельства, когда становится понятно, что происходило до или после интересующего нас события. Не менее важен точный хронометраж в отчетах. Однако стыковка времени может стать проблемой, если анализируемые системы находятся в разных часовых поясах. Мы в компании Mandiant преобразуем все временные метки в формат UTC (Coordinated Universal Time; Всемирное скоординированное время), чтобы исключить путаницу между часовыми поясами и учесть специфические настройки, как, например, переход на летнее время и региональные летние сезоны.

    Конечно, в разных системах фиксируемое время событий не всегда находится в одном формате. Иногда мы можем иметь дело с местным временем, иногда в формате UTC, и, как упоминалось ранее, разные географические локации сильно усложняют жизнь. При объединении данных в единой временной шкале вначале важно разобраться, какой формат используется в логах источника: локальное время или UTC. Если в логах используется локальное время, нужно уточнить, в каком часовом поясе находится источник, а затем воспользоваться функцией TIME() (в Экселе) для преобразования временных меток в формат UTC.

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

    Логи электронной почты хранят время в формате UTC и содержат следующую информацию:

    Рисунок 1: Логи электронной почты

    Логи авторизаций хранят время в формате EDT (Eastern Daylight Time; Восточное Дневное Время) и содержат следующую информацию:

    Рисунок 2: Логи авторизаций

    Веб-логи приложения также хранят время в формате EDT и содержат следующую информацию:

    Искать значения в списке данных

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

    Что ты хочешь сделать?

    Ищите значения по вертикали в списке, используя точное соответствие

    Ищите значения по вертикали в списке, используя приблизительное совпадение

    Найдите значения по вертикали в списке неизвестного размера, используя точное соответствие

    Ищите значения в списке по горизонтали, используя точное соответствие

    Ищите значения в списке по горизонтали, используя приблизительное совпадение

    Создайте формулу поиска с помощью мастера поиска (только для Excel 2007)

    Вертикальный поиск значений в списке с использованием точного соответствия

    Для выполнения этой задачи можно использовать функцию ВПР или комбинацию функций ИНДЕКС и ПОИСКПОЗ.

    Примеры VLOOKUP

    Для получения дополнительной информации см. Функцию ВПР.

    Примеры INDEX и MATCH

    На простом английском языке это означает:

    = ИНДЕКС (мне нужно возвращаемое значение из C2: C10, которое будет СОВПАДАТЬ (Kale, который находится где-то в массиве B2: B10, где возвращаемое значение — первое значение, соответствующее Kale))

    Формула ищет первое значение в C2: C10, которое соответствует Kale (в B7), и возвращает значение в C7 ( 100 ), которое является первым значением, которое соответствует Kale .

    Для получения дополнительной информации см. Функции ИНДЕКС и ПОИСКПОЗ.

    Верх страницы

    Вертикальный поиск значений в списке с использованием приблизительного соответствия

    Для этого используйте функцию ВПР.

    Важно: Убедитесь, что значения в первой строке отсортированы в порядке возрастания.

    В приведенном выше примере VLOOKUP ищет имя ученика, у которого 6 опозданий в диапазоне A2: B7. В таблице нет записи для 6 tardies, поэтому функция VLOOKUP ищет следующее наивысшее совпадение меньше 6 и находит значение 5, связанное с первым именем Dave , и, таким образом, возвращает Dave .

    Для получения дополнительной информации см. Функцию ВПР.

    Верх страницы

    Вертикальный поиск значений в списке неизвестного размера с использованием точного соответствия

    Для выполнения этой задачи используйте функции СМЕЩЕНИЕ и ПОИСКПОЗ.

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

    C1 — верхняя левая ячейка диапазона (также называемая начальной ячейкой).

    MATCH («Апельсины», C2: C7,0) ищет апельсины в диапазоне C2: C7.Не следует включать начальную ячейку в диапазон.

    1 — это количество столбцов справа от начальной ячейки, откуда должно быть возвращаемое значение. В нашем примере возвращаемое значение — из столбца D: Продажи .

    Верх страницы

    Искать значения в списке по горизонтали, используя точное соответствие

    Для выполнения этой задачи используйте функцию HLOOKUP. См. Пример ниже:

    HLOOKUP ищет столбец Sales и возвращает значение из строки 5 в указанном диапазоне.

    Для получения дополнительной информации см. Функцию HLOOKUP.

    Верх страницы

    Поиск значений по горизонтали в списке, используя приблизительное совпадение

    Для выполнения этой задачи используйте функцию HLOOKUP.

    Важно: Убедитесь, что значения в первой строке отсортированы в порядке возрастания.

    В приведенном выше примере HLOOKUP ищет значение 11000 в строке 3 в указанном диапазоне.Он не находит 11000 и, следовательно, ищет следующее по величине значение меньше 1100 и возвращает 10543.

    Для получения дополнительной информации см. Функцию HLOOKUP.

    Верх страницы

    Создание формулы поиска с помощью мастера поиска (только Excel 2007)

    В Excel 2007 мастер подстановки создает формулу подстановки на основе данных листа, которые имеют метки строк и столбцов. Мастер поиска поможет вам найти другие значения в строке, если вы знаете значение в одном столбце, и наоборот.Мастер поиска использует ИНДЕКС и ПОИСКПОЗ в создаваемых им формулах.

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

    2. На вкладке Формулы в группе Решения щелкните Поиск .

    3. Если команда Lookup недоступна, вам необходимо загрузить программу-надстройку мастера поиска.

      Как загрузить программу надстройки мастера поиска

    4. org/ListItem»>

      Нажмите кнопку Microsoft Office , щелкните Параметры Excel , а затем щелкните категорию Надстройки .

    5. В поле Управление щелкните Надстройки Excel , а затем щелкните Перейти .

    6. В диалоговом окне Доступны надстройки установите флажок рядом с Мастер поиска , а затем нажмите ОК .

    7. Следуйте инструкциям мастера.

    Верх страницы

    как добавлять, использовать и удалять

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

    При настройке книги для пользователей часто может потребоваться контролировать ввод информации в определенные ячейки, чтобы убедиться, что все записи данных точны и согласованы. Помимо прочего, вы можете разрешить в ячейке только определенный тип данных, например числа или даты, или ограничить числа определенным диапазоном, а текст — заданной длиной. Вы можете даже захотеть предоставить заранее определенный список допустимых записей, чтобы исключить возможные ошибки. Проверка данных Excel позволяет делать все это во всех версиях Microsoft Excel 2016, 20013, 2010 и ниже.

    Что такое проверка данных в Excel?

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

    Вот лишь несколько примеров того, что может делать проверка данных Excel:

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

    Например, вы можете настроить правило, ограничивающее ввод данных 4-значными числами от 1000 до 9999. Если пользователь вводит что-то другое, Excel покажет предупреждение об ошибке, объясняющее, что он сделал неправильно:

    Как выполнить проверку данных в Excel

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

    1. Откройте диалоговое окно «Проверка данных»

    Выберите одну или несколько ячеек для проверки, перейдите на вкладку Data > группу Data Tools и нажмите кнопку Data Validation .

    Вы также можете открыть диалоговое окно «Проверка данных», нажав Alt> D> L, при этом каждая клавиша будет нажата отдельно.

    2. Создайте правило проверки Excel

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

    • Значения — введите числа в поля критериев, как показано на скриншоте ниже.
    • Ссылки на ячейки — создайте правило на основе значения или формулы в другой ячейке.
    • Формулы — позволяют выражать более сложные условия, как в этом примере.

    В качестве примера давайте создадим правило, которое ограничивает пользователей вводом целого числа от 1000 до 9999:

    С настроенным правилом проверки либо щелкните OK , чтобы закрыть окно Data Validation , либо переключитесь на другую вкладку, чтобы добавить входное сообщение или / или предупреждение об ошибке.

    3. Добавьте входное сообщение (необязательно)

    Если вы хотите отобразить сообщение, объясняющее пользователю, какие данные разрешены в данной ячейке, откройте вкладку Входное сообщение и выполните следующие действия:

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

    Как только пользователь выберет проверенную ячейку, появится следующее сообщение:

    4. Отображение предупреждения об ошибке (необязательно)

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

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

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

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

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

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

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

    Чтобы настроить пользовательское сообщение об ошибке, перейдите на вкладку Error Alert и определите следующие параметры:

    • Установите флажок Показывать предупреждение об ошибке после ввода неверных данных. (обычно выбрано по умолчанию).
    • В поле Style выберите нужный тип предупреждения.
    • Введите заголовок и текст сообщения об ошибке в соответствующие поля.
    • Нажмите ОК .

    И теперь, если пользователь вводит неверные данные, Excel отобразит специальное предупреждение, объясняющее ошибку (как показано в начале этого руководства).

    Примечание. Если вы не набираете собственное сообщение, появится предупреждение Stop по умолчанию со следующим текстом: Это значение не соответствует ограничениям проверки данных, определенным для этой ячейки .

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

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

    Как вы уже знаете, критерии проверки определяются на вкладке Параметры диалогового окна Проверка данных (вкладка Данные > Проверка данных ).

    Целые и десятичные числа

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

    • Равно или не равно указанное число
    • Больше или меньше указанное число
    • Между два числа или не между , чтобы исключить этот диапазон чисел

    Например, вот как вы создаете правило проверки Excel, которое разрешает любое целое число больше 0:

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

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

    Аналогичным образом, чтобы проверить время, выберите Время в поле Разрешить , а затем определите необходимые критерии.

    Например, чтобы разрешить только даты между Дата начала в B1 и Дата окончания в B2, примените это правило проверки даты Excel:

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

    Длина текста

    Чтобы разрешить ввод данных определенной длины, выберите Длина текста в поле Разрешить и выберите критерии проверки в соответствии с вашей бизнес-логикой.

    Например, чтобы ограничить ввод до 10 символов, создайте это правило:

    Примечание. Параметр Длина текста ограничивает количество символов, но не тип данных, что означает, что указанное выше правило разрешает и текст, и числа длиной менее 10 символов или 10 цифр соответственно.

    Список проверки данных Excel (раскрывающийся)

    Чтобы добавить раскрывающийся список элементов в ячейку или группу ячеек, выберите целевые ячейки и выполните следующие действия:

    1. Откройте диалоговое окно Data Validation (вкладка Data > Data Validation ).
    2. На вкладке Настройки выберите Список в Разрешить
    3. В поле Источник введите элементы списка проверки Excel, разделенные запятыми. Например, чтобы ограничить ввод пользователя тремя вариантами, введите Да, Нет, Н / Д .
    4. Убедитесь, что раскрывающееся поле в ячейке выбрано, чтобы рядом с ячейкой появилась стрелка раскрывающегося списка.
    5. Нажмите ОК .

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

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

    Другие способы создания списка проверки данных в Excel

    Поставка списков, разделенных запятыми, непосредственно в поле Source — это самый быстрый способ, который хорошо работает для небольших раскрывающихся списков, которые вряд ли когда-либо изменятся.В других сценариях вы можете действовать одним из следующих способов:

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

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

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

    Как редактировать данные проверки в Excel

    Чтобы изменить правило проверки Excel, выполните следующие действия:

    1. Выберите любую из проверенных ячеек.
    2. Откройте диалоговое окно Data Validation (вкладка Data > Data Validation ).
    3. Внесите необходимые изменения.
    4. Выберите Применить эти изменения ко всем другим ячейкам с теми же настройками. Установите флажок , чтобы скопировать изменения, внесенные вами во все другие ячейки с исходными критериями проверки.
    5. Нажмите ОК , чтобы сохранить изменения.

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

    Как скопировать правило проверки данных Excel в другие ячейки

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

    Чтобы скопировать правило проверки в Excel, выполните следующие 4 быстрых шага:

    1. Выберите ячейку, к которой применяется правило проверки, и нажмите Ctrl + C, чтобы скопировать ее.
    2. Выберите другие ячейки, которые нужно проверить. Чтобы выделить несмежные ячейки, нажмите и удерживайте клавишу Ctrl при выборе ячеек.
    3. Щелкните выделение правой кнопкой мыши, выберите Специальная вставка и выберите Проверка из контекстного меню. Или нажмите сочетание клавиш Paste Special > Validation : Ctrl + Alt + V, затем N.
    4. Нажмите ОК .

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

    Как найти ячейки с проверкой данных в Excel

    Чтобы быстро найти все проверенные ячейки на текущем листе, перейдите на вкладку Home > Редактирование группы и нажмите Найти и выбрать > Проверка данных :

    Это выберет все ячейки, к которым применены какие-либо правила проверки данных:

    Как удалить проверку данных в Excel

    В целом, есть два способа удалить валидацию в Excel: стандартный подход, разработанный Microsoft, и метод без использования мыши, разработанный гиками Excel, которые никогда не уберут руки с клавиатуры, если в этом нет крайней необходимости (например,грамм. взять чашку кофе 🙂

    Метод 1. Обычный способ удаления проверки данных

    Обычно для удаления проверки данных на листах Excel необходимо выполнить следующие действия:

    1. Выберите ячейку (я) с проверкой данных.
    2. На вкладке Data нажмите кнопку Data Validation .
    3. На вкладке Настройки нажмите кнопку Очистить все , а затем нажмите ОК .

    Советы:
    1. Чтобы удалить проверку данных из всех ячеек на текущем листе, используйте функцию «Найти и выбрать», чтобы выбрать все проверенные ячейки.
    2. Чтобы удалить правило определенной проверки данных , выберите любую ячейку с этим правилом, откройте диалоговое окно Data Validation , установите флажок Применить эти изменения ко всем другим ячейкам с теми же настройками , а затем нажмите Очистить Все кнопки .

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

    Метод 2: Специальная вставка для удаления правил проверки данных

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

    1. Выберите пустую ячейку без проверки данных и нажмите Ctrl + C, чтобы скопировать ее.
    2. Выберите ячейки, из которых вы хотите удалить проверку данных.
    3. Нажмите Ctrl + Alt + V, затем N, что является ярлыком для Специальная вставка > Проверка данных .
    4. Нажмите Enter. Выполнено!

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

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

    Проверка данных Excel на основе другой ячейки

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

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

    Например, чтобы разрешить любое целое число, кроме числа в A1, выберите , не равное критерию в поле Data , и введите = $ A $ 1 в поле Value :

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

    Например, чтобы ограничить пользователей вводом дат после сегодняшней даты, введите формулу = СЕГОДНЯ () в некоторую ячейку, скажем B1, а затем настройте правило проверки даты на основе этой ячейки:

    Или вы можете ввести формулу = СЕГОДНЯ () непосредственно в поле Дата начала , что даст тот же эффект.

    Правила проверки на основе формул

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

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

    = МИН (1 австралийский доллар: 10 австралийских долларов)

    = МАКС (1 австралийский доллар: 10 австралийских долларов)

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

    Как найти неверные данные на листе

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

    Чтобы найти недопустимые данные, которые попали в ваши рабочие листы до добавления проверки данных, перейдите на вкладку Data и щелкните Data Validation > Circle Invalid Data .

    Это выделит все ячейки, которые не соответствуют критериям проверки:

    Как только вы исправите неверную запись, кружок автоматически исчезнет. Чтобы удалить все круги, перейдите на вкладку Data и нажмите Data Validation > Clear Validation Circles .

    Как защитить рабочий лист с помощью проверки данных

    Если вы хотите защитить рабочий лист или книгу паролем, сначала настройте требуемые параметры проверки данных, а затем защитите лист. Важно, чтобы вы разблокировали проверенные ячейки до защиты рабочего листа, иначе ваши пользователи не смогут вводить какие-либо данные в эти ячейки. Подробные инструкции см. В разделе Как разблокировать определенные ячейки на защищенном листе.

    Как предоставить общий доступ к книге с проверкой данных

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

    Проверка данных Excel не работает

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

    Проверка данных не работает для скопированных данных

    Проверка данных в Excel предназначена для запрета вводить недопустимые данные непосредственно в ячейку, но не может помешать пользователям копировать недопустимые данные.Хотя нет никакого способа отключить ярлыки копирования / вставки (кроме использования VBA), вы можете, по крайней мере, предотвратить копирование данных, перетаскивая ячейки. Для этого перейдите к Файл > Параметры > Расширенный > Параметры редактирования и снимите флажок Включить дескриптор заполнения и перетаскивание ячейки .

    Проверка данных Excel недоступна в режиме редактирования ячейки

    Команда проверки данных недоступна (выделена серым цветом), если вы вводите или изменяете данные в ячейке. После завершения редактирования ячейки нажмите Enter или Esc, чтобы выйти из режима редактирования, а затем выполните проверку данных.

    Проверка данных не может быть применена к защищенной или общей книге

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

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

    При проверке данных на основе формул в Excel необходимо проверить три важных момента:

    • Формула проверки не возвращает ошибок.
    • Формула не ссылается на пустые ячейки.
    • Используются соответствующие ссылки на ячейки.

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

    Ручной пересчет включен

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

    Для получения дополнительной информации см. Сравнение автоматического расчета и ручного расчета.

    Вот как вы добавляете и используете проверку данных в Excel. Благодарю вас за чтение и надеюсь увидеть вас в нашем блоге на следующей неделе!

    Вас также может заинтересовать

    Проверка данных в Excel — Easy Excel Tutorial

    Пример проверки данных | Создать правило проверки данных | Входное сообщение | Предупреждение об ошибке | Результат проверки данных

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

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

    В этом примере мы ограничиваем пользователей вводом целого числа от 0 до 10.

    Создание правила проверки данных

    Чтобы создать правило проверки данных , выполните следующие шаги.

    1. Выберите ячейку C2.

    2. На вкладке «Данные» в группе «Работа с данными» щелкните «Проверка данных».

    На вкладке Настройки:

    3.В списке разрешенных щелкните Целое число.

    4. В списке данных щелкните между.

    5. Введите минимальное и максимальное значения.

    Входное сообщение

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

    На вкладке «Входное сообщение»:

    1. Установите флажок «Показывать входное сообщение, когда ячейка выбрана».

    2. Введите заголовок.

    3.Введите входное сообщение.

    Предупреждение об ошибке

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

    На вкладке «Предупреждение об ошибке»:

    1. Установите флажок «Показывать предупреждение об ошибке после ввода неверных данных».

    2. Введите заголовок.

    3. Введите сообщение об ошибке.

    4. Щелкните OK.

    Результат проверки данных

    1.Выберите ячейку C2.

    2. Попробуйте ввести число больше 10.

    Результат:

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

    Ограничение записей числовыми значениями (Microsoft Excel)

    Обратите внимание: Эта статья написана для пользователей следующих версий Microsoft Excel: 97, 2000, 2002 и 2003. Если вы используете более позднюю версию (Excel 2007 или новее), этот совет может не сработать для вас . Чтобы посмотреть версию этого совета, написанного специально для более поздних версий Excel, щелкните здесь: Ограничение записей числовыми значениями.

    У Гэри есть рабочий лист, который люди в его офисе используют для ввода данных.Он хочет убедиться, что в конкретную ячейку они могут вводить только числовое значение. Он задается вопросом, как запретить им вводить другие записи — например, текст, дату или время — в ячейку.

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

    1. Выберите ячейку, которую вы хотите использовать для ввода значения.
    2. Выберите «Проверка» в меню «Данные». Excel отображает вкладку Параметры диалогового окна Проверка данных.(См. Рисунок 1.)
    3. Рисунок 1. Вкладка «Настройки» диалогового окна «Проверка данных».

    4. В раскрывающемся списке «Разрешить» выберите «Целое число» или «Десятичное число» в зависимости от того, какой тип числового ввода вы хотите разрешить. Excel изменяет элементы управления, доступные в диалоговом окне.
    5. Убедитесь, что в раскрывающемся списке «Данные» выбрано значение «Между».
    6. В полях «Минимум» и «Максимум» введите нижнюю и верхнюю границы того, что вы хотите, чтобы пользователи вводили в ячейку.
    7. На других вкладках диалогового окна введите сообщение ввода и сообщение об ошибке, если необходимо.
    8. Щелкните OK.

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

    Например, если вы настроили проверку данных, чтобы разрешить только значения от 20 000 до 21 000, тогда кто-то может ввести дату 11.06.1956, потому что Excel анализирует дату до целого числа 20 617.Наилучший способ обработки даты и времени — отформатировать ячейку так, чтобы в ней использовался числовой формат, что не позволит Excel отображать дату и время.

    ExcelTips — ваш источник экономичного обучения Microsoft Excel. Этот совет (2425) применим к Microsoft Excel 97, 2000, 2002 и 2003. Вы можете найти версию этого совета для ленточного интерфейса Excel (Excel 2007 и новее) здесь: Ограничение записей числовыми значениями .

    Автор Биография

    Аллен Вятт

    Аллен Вятт — всемирно признанный автор, автор более чем 50 научно-популярных книг и многочисленных журнальных статей. Он является президентом Sharon Parq Associates, компании, предоставляющей компьютерные и издательские услуги. Узнать больше о Allen …

    Что такое сноски и концевые сноски

    Сноски и концевые сноски часто используются в научных и официальных текстах как способ предоставить дополнительную информацию о a…

    Узнайте больше

    Отправка отдельных листов по электронной почте

    У вас есть один лист, который вы хотите отправить кому-то по электронной почте, но не хотите, чтобы он видел остальные листы в …

    Узнайте больше

    Проверка длины выделенного текста

    Нужно знать, выделил ли пользователь текст перед запуском макроса? Вот как это сделать.

    Узнайте больше .

Добавить комментарий

Ваш адрес email не будет опубликован. Обязательные поля помечены *