Как использовать проверку данных в Excel (плюс примеры)

8 апреля 2022 г.

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

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

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

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

• Запретить время и даты, выходящие за пределы определенного диапазона.

• Запретить значения, если они слишком длинные или слишком короткие.

• Разрешить только числовые значения.

• Разрешить только числа в пределах определенного диапазона.

• Разрешить только текстовые значения.

• Найдите неточные записи в проверенных ячейках.

• Отображение входного сообщения для пользователя Excel.

• Отображение предупреждающих сообщений при вводе неверных данных.

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

Вот список шагов по проверке данных:

1. Выберите все ячейки, которые вы хотите проверить

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

2. Откройте всплывающее окно «Проверка данных».

Нажмите на вкладку «Данные», которая находится между вкладками «Формулы» и «Обзор». Найдите группу «Инструменты данных» и нажмите кнопку с надписью «Проверка данных». Кроме того, вы можете открыть всплывающее окно «Проверка данных», нажав клавиши «Alt», «D» и «L» на клавиатуре. Если вы выполняете это сочетание клавиш, убедитесь, что вы нажимаете каждую клавишу одну за другой, а не все одновременно.

3. Установите правило проверки данных

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

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

4. Проверьте свой выбор

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

5. Создайте входное сообщение

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

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

6. Добавьте предупреждение об ошибке

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

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

7. Нажмите «ОК», чтобы закрыть всплывающее окно «Проверка данных».

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

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

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

1. Щелкните любую ячейку, содержащую ваше правило проверки.

2. Нажмите клавиши «Ctrl» и «C» на клавиатуре, чтобы скопировать эту ячейку.

3. Выделите все ячейки, которые хотите проверить, и используйте клавишу «Ctrl», если хотите выделить несмежные ячейки.

4. Щелкните правой кнопкой мыши свой выбор, выберите «Специальная вставка» и выберите «Проверка».

5. Нажмите «ОК», чтобы добавить правило проверки в новые ячейки.

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

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

Пример 1

Вот пример правила проверки целых чисел:

Владелец цветочного магазина продает различные виды цветов. У каждого цветка есть определенный номер продукта в диапазоне от 2000 до 2500. Владелец отслеживает свой инвентарь в документе Excel.

Они хотят упростить процесс ввода данных и свести к минимуму количество ошибок, поэтому создают правило проверки данных для всех ячеек в столбце А. Правило состоит в том, что ни одна ячейка в столбце А не может содержать число, отличное от 2000 и 2500. Если возникает ошибка, как в ячейке A4, владелец цветочного магазина запрограммировал отображение сообщения об ошибке, в котором говорится: «Пожалуйста, введите четырехзначный номер продукта от 2000 до 2500».

ABC1Номер продукта**Название продукта**Количество22001Тюльпаны1532002Одуванчики154203Орхидеи2552004Нарциссы3062005Калифорнийские маки15

Пример 2

Вот пример правила проверки дат:

Руководитель группы пытается зафиксировать отсутствие сотрудников в первую неделю апреля. Они создают правило проверки, поэтому в столбец А можно вводить только даты между 01.04 и 08.04. После ввода дат в ячейке A3 появляется предупреждение об ошибке, так как там написано «4/22». «4/22» выходит за пределы определенного диапазона, поэтому пользователь может узнать, что он допустил ошибку, и исправить запись так, чтобы она звучала как «4/2».

AB1Дата**Количество отсутствий**24/1534/22244/3154/5064/67### Пример 3

Вот пример правила проверки для списка:

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

Это создает раскрывающееся меню, которое позволяет пользователю подтвердить, встречался ли сотрудник с менеджером или нет. В этом примере ячейка B7 выдает ошибку, поскольку в ней нет значения «Да» или «Нет». Менеджер может использовать эту информацию, чтобы знать, что ему все еще нужно подтвердить статус Патрика Уитакера.

AB1Имя сотрудника**Необходима встреча с менеджером?*2Коллинз, Ирэн, Да3Даллас, ДжонНет4Мерриман, МоллиNo5Пэрриш, НэнсиДа6Ричардсон, МэриНет7Уитакер, Патрикн/aОбратите внимание, что ни одна из компаний, упомянутых в этой статье, не связана с компанией Indeed.*