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

Проверка данных в Excel — ОфисГуру

Перейти к содержанию

Search for:

Главная » Уроки MS Excel

Автор Антон Андронов На чтение 2 мин Опубликовано 30.08.2015

• Пример проверки данных
• Как создать правило проверки данных
• Сообщение для ввода
• Оповещение об ошибке
• Результат проверки данных

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

Содержание

1. Пример проверки данных
2. Как создать правило проверки данных
3. Сообщение для ввода
4. Сообщение об ошибке
5. Результат проверки данных

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

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

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

Чтобы создать правило проверки данных, следуйте нашей инструкции:

1. Выделите ячейку С2.
2. На вкладке Данные (Data) нажмите кнопку Проверка данных (Data Validation).

   На вкладке Параметры (Settings) диалогового окна Проверка вводимых значений (Data Validation) сделайте следующее:

   • Из выпадающего списка Тип данных (Allow) выберите Целое число (Whole number).
   • Из выпадающего списка Значение (Data) выберите Между (Between).
   • Введите минимальное и максимальное значения.

Сообщение для ввода

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

1. Поставьте галочку напротив
   Отображать подсказку, если ячейка является текущей (Show input message when cell is selected).
2. Введите заголовок.
3. Введите само сообщение.

Сообщение об ошибке

Если пользователи игнорируют сообщение для ввода и вводят недопустимое число, вы можете показать им уведомление об ошибке. Перейдите на вкладку Сообщение об ошибке (Error Alert) и сделайте следующее:

1. Поставьте галочку напротив параметра Выводить сообщение об ошибке (Show error alert after invalid data is entered).
2. Введите заголовок.
3. Введите сообщение об ошибке.

4. Нажмите ОК.

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

1. Выделите ячейку С2.

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

   Результат:

Примечание: Чтобы удалить проверку данных из ячейки, выделите её и на вкладке

Данные (Data) нажмите кнопку Проверка данных (Data Validation). Затем кликните по Очистить все (Clear All). Чтобы быстро выбрать все ячейки с проверкой данных, используйте инструмент Выделение группы ячеек (Go To Special).

Урок подготовлен для Вас командой сайта office-guru.ru
Источник: http://www.excel-easy.com/basics/data-validation.html
Перевела: Ольга Гелих
Правила перепечатки
Еще больше уроков по Microsoft Excel

Оцените качество статьи. Нам важно ваше мнение:

Adblock
detector

Анализ данных Excel — проверка данных

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

Для любой ячейки на листе вы можете

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

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

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

• Вероятность
• Влияние
• Категория риска
• Источник риска
• Статус

Столбец Risk Exposure будет иметь рассчитанные значения, и вы не сможете ввести какие-либо данные. Даже в столбце

S. No. установлены расчетные значения, которые корректируются, даже если вы удаляете строку.

Теперь вы узнаете, как настроить такой лист.

Подготовьте структуру для рабочего листа

Чтобы подготовить структуру для рабочего листа –

• Начните с чистого листа.
• Поместите заголовок в строке 2.
• Поместите заголовки столбцов в строке 3.
• Для заголовков столбцов Вероятность, Воздействие и Риск –
  • Щелкните правой кнопкой мыши на ячейке.
  • Нажмите на Формат ячеек из выпадающего списка.
  • В диалоговом окне «Формат ячеек» перейдите на вкладку «Выравнивание».
  • Введите 90 под ориентацией.
• Объедините и отцентрируйте ячейки в строках 3, 4 и 5 для каждого заголовка столбца.
• Формат границ для ячеек в строках 2 – 5.
• Отрегулируйте ширину строки и столбца.

Ваш рабочий лист будет выглядеть следующим образом –

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

В ячейках M5 – M13 введите следующие значения (M5 – заголовок, а M6 – M13 – значения)

Значения категории

Конечные пользователи

Покупатель

управление

График

График

Среда

Товар

проект

• Нажмите на первую ячейку под столбцом Категория риска (H6).
• Нажмите вкладку ДАННЫЕ на ленте.
• Нажмите Проверка данных в группе Инструменты данных.
• Выберите Проверка данных … из раскрывающегося списка.

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

• Нажмите вкладку Настройки.
• В разделе «Критерии проверки» в раскрывающемся списке « Разрешить:» выберите « Список» .

• Выберите диапазон M6: M13 в появившемся поле Source :.
• Установите флажки Пропустить пустое поле и раскрывающийся список внутри ячейки.

Установить входное сообщение для категории риска

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

Установить оповещение об ошибке для категории риска

Чтобы установить предупреждение об ошибке –

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

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

Для выбранной первой ячейки в категории риска

• Критерии проверки данных установлены
• Входное сообщение установлено
• Предупреждение об ошибке установлено

Теперь вы можете проверить свои настройки.

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

Входное сообщение отображается правильно.

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

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

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

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

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

Выберите значение из выпадающего списка. Появляется в клетке.

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

Наконец, попробуйте ввести неверную запись и проверьте предупреждение об ошибке.

Введите People в ячейку и нажмите Enter. Появится сообщение об ошибке, установленное для ячейки.

• Проверьте сообщение об ошибке.
• У вас есть возможность либо повторить попытку, либо отменить. Проверьте оба варианта.

Вы успешно установили проверку данных для ячейки.

Примечание. Очень важно проверить правильность написания и грамматику ваших сообщений.

Установите действительные критерии для столбца категории риска

Теперь вы готовы применить критерии проверки данных ко всем ячейкам в столбце «Категория риска».

На данный момент вам нужно запомнить две вещи –

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

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

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

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

Следуйте инструкциям ниже

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

Проверка данных установлена ​​для всех выбранных ячеек.

Нажмите на последний выбранный столбец и подтвердите.

Проверка данных для столбца Категория риска завершена.

Установить значения проверки для источника риска

В этом случае у нас есть только два значения – Внутреннее и Внешнее.

• Нажмите в первой ячейке под столбцом Источник риска (I6)
• Нажмите вкладку ДАННЫЕ на ленте
• Нажмите Проверка данных в группе Инструменты данных.
• Выберите Проверка данных … из раскрывающегося списка.

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

• Нажмите вкладку Настройки.
• В разделе «Критерии проверки» в раскрывающемся списке «Разрешить:» выберите параметр «Список».
• Введите Внутренний, Внешний в поле Source: появится.
• Установите флажки Пропустить пустое поле и раскрывающийся список внутри ячейки.

Установите входное сообщение для источника риска.

Установите оповещение об ошибке для источника риска.

Для выбранной первой ячейки в разделе Источник риска –

• Критерии проверки данных установлены
• Входное сообщение установлено
• Предупреждение об ошибке установлено

Теперь вы можете проверить свои настройки.

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

Входное сообщение отображается правильно.

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

• Проверьте, совпадают ли значения с введенным вами – Внутренний и Внешний.

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

Проверьте, совпадают ли значения с введенным вами – Внутренний и Внешний.

Оба набора значений совпадают. Выберите значение из раскрывающегося списка. Появляется в клетке.

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

Введите Financial в ячейку и нажмите Enter. Появится сообщение об ошибке, установленное для ячейки.

• Проверьте сообщение об ошибке. Вы успешно установили проверку данных для ячейки.

• Установите действительные критерии для столбца источника риска

• Примените критерии проверки данных к ячейкам I6 – I15 в столбце Источник риска (т. Е. Тот же диапазон, что и в столбце Категория риска).

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

Установите действительные критерии для столбца источника риска

Примените критерии проверки данных к ячейкам I6 – I15 в столбце Источник риска (т. Е. Тот же диапазон, что и в столбце Категория риска).

Проверка данных установлена ​​для всех выбранных ячеек. Проверка данных для столбца Источник риска завершена.

Установить значения проверки для статуса

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

• Установите значения списка как Open, Closed.

• Примените критерии проверки данных к ячейкам K6 – K15 в столбце «Статус» (т. Е. Тот же диапазон, что и в столбце «Категория риска»).

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

Установите значения списка как Open, Closed.

Примените критерии проверки данных к ячейкам K6 – K15 в столбце «Статус» (т. Е. Тот же диапазон, что и в столбце «Категория риска»).

Проверка данных установлена ​​для всех выбранных ячеек. Проверка данных для статуса столбца завершена.

Установите значения проверки для вероятности

Значения показателя вероятности риска находятся в диапазоне 1-5, 1 – низкий, а 5 – высокий. Значение может быть любым целым числом от 1 до 5, включая оба.

• Нажмите в первой ячейке под столбцом Источник риска (I6).
• Нажмите вкладку ДАННЫЕ на ленте.
• Нажмите Проверка данных в группе Инструменты данных.
• Выберите Проверка данных … из раскрывающегося списка.

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

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

• Выберите между под данными:
• Введите 1 в поле под минимумом:
• Введите 5 в поле под Максимум:

Установить входное сообщение для вероятности

Установите предупреждение об ошибке для вероятности и нажмите ОК.

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

• Критерии проверки данных установлены.
• Входное сообщение установлено.
• Предупреждение об ошибке установлено.

Теперь вы можете проверить свои настройки.

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

Входное сообщение отображается правильно.

Введите целое число от 1 до 5 в ячейку. Появляется в клетке.

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

Введите 6 в ячейку и нажмите Enter. Появится сообщение об ошибке, установленное для ячейки.

Вы успешно установили проверку данных для ячейки.

• Установите действительные критерии для столбца вероятности.

• Примените критерии проверки данных к ячейкам E6 – E15 в столбце «Вероятность» (т. Е. Тот же диапазон, что и в столбце «Категория риска»).

Установите действительные критерии для столбца вероятности.

Примените критерии проверки данных к ячейкам E6 – E15 в столбце «Вероятность» (т. Е. Тот же диапазон, что и в столбце «Категория риска»).

Проверка данных установлена ​​для всех выбранных ячеек. Проверка данных для столбца Вероятность завершена.

Установить значения проверки для воздействия

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

Примените критерии проверки данных к ячейкам F6 – F15 в столбце «Воздействие» (т. Е. Тот же диапазон, что и в столбце «Категория риска»).

Проверка данных установлена ​​для всех выбранных ячеек. Проверка данных для столбца Воздействие завершена.

Установите риск столбца с расчетными значениями

Риск подверженности рассчитывается как произведение вероятности риска и воздействия риска.

Подверженность риску = вероятность * влияние

Введите = E6 * F6 в ячейке G6 и нажмите Enter.

0 будет отображаться в ячейке G6, поскольку E6 и F6 пусты.

Скопируйте формулу в ячейки G6 – G15. 0 будет отображаться в ячейках G6 – G15.

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

• Выберите ячейки G6-G15

• Щелкните правой кнопкой мыши и в раскрывающемся списке выберите Формат ячеек. Откроется диалоговое окно «Формат ячеек».

• Нажмите вкладку Защита.

• Проверьте опцию Заблокировано .

Выберите ячейки G6-G15

Щелкните правой кнопкой мыши и в раскрывающемся списке выберите Формат ячеек. Откроется диалоговое окно «Формат ячеек».

Нажмите вкладку Защита.

Проверьте опцию Заблокировано .

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

• Нажмите ОК.
• Затените ячейки G6-G15, чтобы указать, что они являются рассчитанными значениями.

Форматировать значения серийного номера

Вы можете оставить это пользователю, чтобы заполнить столбец S. No. Однако, если вы отформатируете значения S. No., лист будет выглядеть более презентабельно. Кроме того, он показывает, на сколько строк отформатирован лист.

Введите = row () – 5 в ячейке B6 и нажмите Enter.

1 появится в ячейке B6. Скопируйте формулу в ячейки B6-B15. Значения 1-10 появляются.

Затенить клетки B6-B15.

Заворачивать

Вы почти закончили свой проект.

• Скрыть столбец M, который содержит значения категории данных.
• Формат Границы для ячеек B6-K16.

• Щелкните правой кнопкой мыши на вкладке листа.
• Выберите Защитить лист из меню.

Откроется диалоговое окно «Защитить лист».

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

Вы защитили заблокированные ячейки в столбце Risk Exposure от ввода данных и оставили доступными для редактирования оставшиеся разблокированные ячейки. Нажмите ОК.

Откроется диалоговое окно подтверждения пароля .

• Введите пароль еще раз.
• Нажмите ОК.

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

Проверка данных в Excel | CustomGuide

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

Создать правило проверки

1. Выберите ячейки, которые вы хотите проверить.
2. Перейдите на вкладку Данные .
3. Нажмите кнопку Проверка данных .
4. Щелкните стрелку списка Разрешить .
5. Выберите тип данных, которые вы хотите разрешить.
   • Любое значение: Критерии проверки не применяются.
   • Целое число: Разрешает целое число между минимальным и максимальным установленными пределами.
   • Десятичный: Позволяет вводить десятичное число или процент в виде десятичного числа между установленными пределами.
   • Список: Позволяет значение из списка вариантов. В ячейке появляется стрелка списка, и пользователи могут выбирать из списка.
   • Дата: Позволяет указать дату в установленных пределах.
   • Время: Позволяет указать время в установленных пределах.
   • Длина текста: Позволяет использовать текст, содержащий определенное количество символов.
   • Пользовательский: Позволяет ввести формулу для расчета того, что разрешено в ячейке.
6. Укажите правила проверки данных.

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

7. Нажмите OK .

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

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

Добавление входных данных и сообщений об ошибках

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

1. Выберите ячейки, в которых вы хотите, чтобы входное сообщение появилось.
2. Перейдите на вкладку Данные .
3. Нажмите кнопку Проверка данных .
4. Перейдите на вкладку Входное сообщение .
5. Введите входное сообщение.
6. Перейдите на вкладку Предупреждение об ошибке .
7. Выберите стиль предупреждения об ошибке.
   • Стоп: Запрещает пользователям добавлять неверные данные в ячейку.
   • Предупреждение: Предупреждает, что введенные данные недействительны, но пользователи могут нажать Да , чтобы принять неверную запись, Нет , чтобы изменить ее, или Отмена , чтобы удалить ее.
   • Информация: Информирует пользователей о том, что введенные данные недействительны, но пользователи могут нажать OK , чтобы принять неверную запись, или Cancel , чтобы удалить ее.
8. Введите сообщение об ошибке.
9. Нажмите OK .
10. Выберите ячейку с входным сообщением.

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

БЕСПЛАТНЫЙ краткий справочник

Нажмите, чтобы загрузить

Бесплатно для распространения с нашими комплиментами; мы надеемся, что вы рассмотрите наше платное обучение.

11 замечательных примеров проверки данных

Это гостевой пост Алана Мюррея из Computergaga.

Data Validation — очень полезный инструмент Excel. Это часто остается незамеченным, поскольку пользователи Excel стремятся изучить преимущества сводных таблиц, диаграмм и формул.

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

В этом сообщении блога мы рассмотрим 11 полезных примеров того, что может сделать проверка данных.

Чтобы применить эти правила проверки данных;

1. Сначала выберите диапазон ячеек, к которым вы хотите применить проверку.
2. Щелкните вкладку «Данные», а затем кнопку «Проверка данных» на ленте.
3. На вкладке Параметры выберите критерии правила проверки.

Разрешить ввод только в верхнем регистре

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

Ячейки должны принимать как текст, так и числа, но текст должен быть в верхнем регистре.

Для этого мы можем использовать формулу с функциями ПРОПИСНАЯ и ТОЧНАЯ.

Функция ПРОПИСЬ, вероятно, говорит сама за себя. Он преобразует текст в верхний регистр.

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

В этом примере проверка применялась к диапазону A2:A6. Выберите «Пользовательский» в списке «Разрешить» и введите следующую формулу в поле «Формула».

=EXACT(A2,UPPER(A2))

Предотвратить будущие даты

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

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

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

Выберите «Дата» в списке «Разрешить», а затем «Меньше или равно» в «Данные».

В поле Дата окончания введите приведенную ниже формулу.

=СЕГОДНЯ()

Функция СЕГОДНЯ возвращает текущую дату с компьютера. Невероятно полезно. Ознакомьтесь с другими замечательными функциями даты Excel (https://www.howtoexcel.org/category/functions/date-and-time/).

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

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

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

Если вам нужен простой список, такой как «Открыто» и «Закрыто» или «Да» и «Нет», имеет смысл вводить записи.

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

Для этого списка щелкните в поле «Источник», а затем перейдите и выберите ячейки, содержащие элементы.

В этом примере элементы находились в диапазоне A1:A5 листа Names.

Зависимые выпадающие списки

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

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

Для этого мы сначала должны назвать каждый список. Например, диапазон A2:A4 называется город, диапазон B2:B6 называется Кардифф и так далее.

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

1. Выберите диапазон для имени, например. А2:А4.
2. Щелкните в поле имени слева от строки формул.
3. Введите имя, которое вы хотите применить, и нажмите Enter.

Список в ячейке F2 создается так же, как и в предыдущем примере. В поле «Источник» вы можете ввести =city, чтобы сослаться на именованный диапазон.

Для зависимого списка в ячейке G2 выделение в ячейке F2 необходимо преобразовать в ссылку на именованные диапазоны. Это делается с помощью функции ДВССЫЛ (узнайте больше замечательных примеров функции ДВССЫЛ).

=ДВССЫЛ(F2)

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

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

Предотвратить повторяющиеся значения

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

Используя функцию СЧЁТЕСЛИ в пользовательской формуле, мы можем.

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

=СЧЁТЕСЛИ($A$2:$A$8;A2)=0

Разрешить только числовой или текстовый ввод

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

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

=ISNUMBER(A2)

Это позволит использовать любые числовые значения, включая даты и время.

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

=ISTEXT(A2)

Проверка записи на основе другой ячейки

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

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

Следующая функция ЕСЛИ проверяет, не пуста ли ячейка A2, и если да, то показывает список из именованного диапазона.

=ЕСЛИ(A2<>””,местоположение)

Разрешить ввод только рабочих дней

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

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

=ДЕНЬНЕД(A2,2)<=5

Функция ДЕНЬНЕД возвращает число, представляющее день недели. В функции WEEKDAY 2 указывает, что неделя начинается с понедельника как 1 и заканчивается в воскресенье как 7.

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

Ограничение длины текста

Записи могут иметь определенную длину текста или не более определенного количества символов. Создание правила проверки может помочь нам в этом.

В этом примере я хочу ограничить длину текста ровно 9 символами.

Выберите «Длина текста» в списке «Разрешить», «равно» в списке «Данные», а затем введите 9для длины.

Записи содержат определенный текст

Использование формул в правилах проверки позволяет нам тестировать и проверять практически все.

В этом последнем примере мы гарантируем, что записи содержат определенный текст.

Функция НАЙТИ используется для поиска определенной текстовой строки (в данном примере «ENG») и возврата ее положения в ячейке. Если FIND не находит текст, возвращается ошибка.

Функция ISNUMBER используется для проверки успешности выполнения функции FIND (если она вернула число). Если да, то ISNUMBER возвращает значение True, в противном случае — значение False. На этом наш проверочный тест завершен.

Вот используемая формула.

=IЧИСЛО(НАЙТИ(«ENG»,A2))

Примечание. Функция НАЙТИ чувствительна к регистру. Вместо этого можно использовать функцию ПОИСК, если вам не нужно сопоставлять регистр текста.

Создание осмысленных сообщений об ошибках

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

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

Перейдите на вкладку «Предупреждение об ошибке» в окне «Проверка данных».

Выберите стиль из списка «Стоп», «Предупреждение» или «Информация».

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

Введите заголовок и сообщение об ошибке для предупреждения об ошибке.