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

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

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

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

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

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

Probability
Impact
Категория риска
Источник риска
Status

В столбце Risk Exposure будут рассчитанные значения, и вы не сможете вводить какие-либо данные. Даже колонкаS. No. настроен на получение рассчитанных значений, которые корректируются, даже если вы удаляете строку.

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

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

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

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

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

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

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

Категория Значения
Конечные пользователи
Покупатель
Управление
График
График
Окружающая среда
Продукт
Проект

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

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

Щелкните вкладку Параметры.
В разделе "Критерии проверки" в Allow: раскрывающийся список, выберите вариант List.

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Введите в ячейку Люди и нажмите Enter. Будет отображено сообщение об ошибке, которое вы установили для ячейки.

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

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

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

Установите допустимые критерии для столбца категории риска

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

На этом этапе вам нужно запомнить две вещи:

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

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

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

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

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

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

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

В данном случае у нас всего два значения - Internal и External.

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

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

Щелкните вкладку Параметры.
В разделе Критерии проверки в раскрывающемся списке Разрешить: выберите параметр Список.
Введите Internal, External в появившемся поле Source :.
Установите флажки Игнорировать пустые поля и раскрывающийся список в ячейках.

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

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

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

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

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

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

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

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

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

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

Проверьте сообщение об ошибке. Вы успешно настроили проверку данных для ячейки.
Установите допустимые критерии для столбца "Источник риска"
Примените критерии проверки данных к ячейкам I6 - I15 в столбце «Источник риска» (т.е. к тому же диапазону, что и в столбце «Категория риска»).

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

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

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

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

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

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

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

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

Щелкните вкладку Параметры.
В разделе Критерии проверки в раскрывающемся списке Разрешить: выберите Целое число.

В разделе «Данные» выберите:
Введите 1 в поле под Минимум:
Введите 5 в поле под Максимум:

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

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

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

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

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

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

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

Введите в ячейку целое число от 1 до 5. Он появляется в камере.

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

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

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

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

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

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

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

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

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

Задайте для столбца "Подверженность риску" рассчитанные значения

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

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

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

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

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

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

Выберите ячейки G6-G15
Щелкните правой кнопкой мыши и в появившемся раскрывающемся списке выберите Формат ячеек. Откроется диалоговое окно «Формат ячеек».
Щелкните вкладку Защита.
Отметьте опцию Locked.

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

Щелкните ОК.
Заштрихуйте ячейки G6-G15, чтобы указать, что это вычисленные значения.

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

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

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

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

Заштрихуйте ячейки B6-B15.

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

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

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

Щелкните правой кнопкой мыши вкладку рабочего листа.
В меню выберите «Защитить лист».

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

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

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

В Confirm Password появится диалоговое окно.

Введите пароль еще раз.
Щелкните ОК.

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