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