Сделать задание в excel

Предотвращение неверного ввода данных в электронные таблицы

Вопросы для допуска к работе

Назначение инструмента «Проверка данных»

Возможности инструмента «Проверка данных»

Ситуации, в которых полезно использовать проверку данных

Виды сообщений, возникающих при проверке данных

Как ограничить ввод данных значениями в раскрывающемся списке?

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

Как выбрать способ сообщения о вводе недопустимых данных?

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

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

Как ограничить ввод данных датами в заданном интервале?

Как ограничить ввод данных временем в заданном интервале?

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

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

Каким образом можно использовать формулы для вычисления допустимого для ввода значения?

Тренинг

Выполнить задание тренинга «Создание списков для автоматизации ввода в MS Excel».

Общее задание

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

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

Индивидуальное задание

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

Оцениваются количество и качество проверок, количество и информативность сообщений.

Проверка данных в ячейках

Общие сведения о проверке данных 1

Ситуации, в которых полезно использовать проверку данных 3

Сообщения проверки данных 4

Добавление проверки данных в ячейке или диапазоне ячеек 7

Ограничение ввода данных значениями в раскрывающемся списке 7

Ограничение ввода данных для целого числа в определенных пределах 9

Ограничение ввода данных десятичными числами в определенных пределах 9

Ограничение ввода данных датами в заданном интервале времени 10

Ограничение ввода данных временем в заданном интервале 10

Ограничение ввода данных текстом определенной длины 11

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

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

Общие сведения о проверке данных

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

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

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

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

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

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

Проверку данных можно настроить в диалоговом окне Проверка данных.

Ситуации, в которых полезно использовать проверку данных

Проверку данных можно использовать в указанных ниже целях.

• Ограничение данных предопределенными элементами списка.  Например, типы отделов можно ограничить следующими: "Отдел продаж", "Бухгалтерия", "Отдел исследования и разработки" и "ИТ-отдел". Аналогично можно создать список значений из любого диапазона ячеек на листе.

• Ограничение использования чисел за пределами указанного диапазона.  Например, можно указать минимальный предел отчислений, равный числу, которое в два раза превышает количество детей, указанное в определенной ячейке.
• Ограничение дат, находящихся за пределами определенного промежутка времени.  Например, можно указать промежуток времени от текущей даты до трех дней после нее.
• Ограничение времени, которое находится за пределами определенного промежутка времени.   Например, можно указать промежуток времени, когда можно подавать завтрак, равный пяти часам после момента открытия ресторана.
• Ограничение количества текстовых знаков.  Например, можно ограничить количество знаков, которые можно вводить в ячейку, десятью. Аналогично можно указать определенную длину для поля полного имени (ячейка C1), чтобы она была равна текущей длине поля имени (ячейка A1) и поля фамилии (ячейка B1) плюс десять знаков.
• Проверка данных на основе формул или значений в других ячейках.  Например, проверку данных можно использовать для указания максимально допустимого предела для сборов и премий, равного 3 600 р., на основе общего планируемого значения заработной платы. Если пользователь в этой ячейке введет значение, большее 3 600 р., появится сообщение о необходимости проверки вводимых данных.

Сообщения проверки данных

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

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

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

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

• Если пользователь вводит данные в ячейку посредством копирования или заполнения.
• Если формула в ячейке выдает недопустимый результат.
• Если макрос (Макрос. Действие или набор действий, используемые для автоматизации выполнения задач. Макросы записываются на языке программирования Visual Basic для приложений (VBA)) вводит в ячейку недопустимые данные.

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

В списке ниже перечислены советы и рекомендации по использованию проверки данных в Excel.

• Если необходимо защитить (Защита. Свойства, которые не позволяет другим лицам просматривать определенные элементы листа или книги или иным способом получать доступ к ним) лист или книгу, выполните это действие после настройки параметров проверки. Перед тем как защитить лист, убедитесь, что разблокированы любые ячейки, которые необходимо проверить. В противном случае пользователям не удастся ввести данные в ячейки.
• Если требуется открыть общий доступ к книге, можно сделать это после настройки проверки данных. Если книга открыта для общего доступа, изменение параметров проверки данных невозможно, пока не будет закрыт общий доступ, однако проверка указанных ячеек такой книги программой Microsoft Excel будет осуществляться по-прежнему.
• Можно применить поверку данных к ячейкам, в которые уже введены данные. Однако в приложении Excel отсутствует возможность автоматического уведомления о том, что в существующих ячейках имеются недопустимые данные. В этом случае можно выделить недопустимые данные на листе с помощью кругов. Когда недопустимые данные найдены, круги можно скрыть. При исправлении недопустимых данных они исчезают автоматически.

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

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

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

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

Отсутствуют ошибки в формулах.   Убедитесь, что формулы в ячейках, для которых включена проверка, не являются причиной возникновения ошибок, таких как #ССЫЛКА! или #ДЕЛ/0!. Пока ошибки не будут устранены, приложение Excel будет пропускать проверку данных.

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

Добавление проверки данных в ячейке или диапазоне ячеек

Ограничение ввода данных значениями в раскрывающемся списке

Изменить шрифт или размер шрифта элементов в списке невозможно.

1. Выберите для проверки одну или несколько ячеек.
2. На вкладке Данные в группе Работа с данными выберите команду Проверка данных.

3. В диалоговом окне Проверка данных откройте вкладку Параметры.
4. В поле Тип данных выберите значение Список.
5. Щелкните поле Источник, а затем введите значения списка, используя разделитель элементов списка Microsoft Windows (по умолчанию — запятые).

Например:

• Для ограничения ввода ответа на вопрос (например, "Есть ли у вас дети?") двумя вариантами введите Да, Нет.
• Для ограничения рейтинга качества производителя тремя позициями введите Низкое, Среднее, Высокое.

Можно также создать список значений с помощью ссылки на диапазон ячеек в любой части книги.

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

6. Убедитесь, что установлен флажок Список допустимых значений. В противном случае рядом с ячейкой не будет отображена стрелка раскрывающегося списка.
7. Чтобы указать, как обрабатывать пустые (нулевые) значения, установите или снимите флажок Игнорировать пустые ячейки.

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

8. Дополнительно может отобразиться сообщение для ввода при выделении ячейки.

Отображение подсказки

1. Откройте вкладку Сообщения для ввода.
2. Установите флажок Отображать подсказку, если ячейка является текущей.
3. Введите заголовок и текст сообщения.

9. Определите способ, которым Microsoft Office Excel будет сообщать о вводе неверных данных.

Выбор способа сообщения о вводе недопустимых данных

Перейдите на вкладку Сообщение об ошибке и убедитесь, что флажок Выводить сообщение об ошибке установлен.

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

Выберите один из указанных ниже параметров для поля Вид.

Для отображения информационного сообщения, не запрещающего ввод неверных данных, выберите значение Сообщение.

Для отображения предупреждения, не запрещающего ввод неверных данных, выберите значение Предупреждение.

Для предотвращения ввода неверных данных выберите значение Останов.

Введите заголовок и текст для сообщения (до 225 знаков).

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

10. Протестируйте правильность работы проверки данных.

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

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

Ограничение ввода данных для целого числа в определенных пределах

Выполнить п.п. 1-3 (см. Ограничение ввода данных значениями в раскрывающемся списке)

4. В поле Тип данных выберите значение Целое число.

5. В поле Значение выберите необходимый тип ограничения. Например, для задания верхнего и нижнего пределов выберите ограничение Между.

6. Введите минимальное, максимальное или определенное разрешенное значение. Можно также ввести формулу, которая возвращает числовое значение.

Например, чтобы задать минимальное ограничение, равное значению ячейки F1, умноженному на 2, выберите пункт Больше или равно в поле Значение и введите формулу =2*F1 в поле Минимум.

Выполнить п.п. 7-10 (см. Ограничение ввода данных значениями в раскрывающемся списке)

Ограничение ввода данных десятичными числами в определенных пределах

Выполнить п.п. 1-3 (см. Ограничение ввода данных значениями в раскрывающемся списке)

4. В поле Тип данных выберите значение Действительное.

5. В поле Значение выберите необходимый тип ограничения. Например, для задания верхнего и нижнего пределов выберите ограничение Между.

6. Введите минимальное, максимальное или определенное разрешенное значение. Можно также вывести формулу, которая возвращает числовое значение.

Например, для задания максимального значения комиссионных и премиальных в размере 6% от заработной платы продавца в ячейке E1 выберите пункт Меньше или равно в поле Значение и введите формулу =E1*6% в поле Максимум.

Примечание.  Для разрешения ввода значений процентов, например 20 %, в поле Тип данных выберите значение Действительное, в поле Значение выберите необходимый тип ограничения, введите минимальное, максимальное или определенное значение в виде десятичного числа, например 2, а затем отобразите ячейку проверки данных в виде процентного значения, выделив ячейку и нажав кнопку Процентный формат на вкладке Главная в группе элементов Число.

Выполнить п.п. 7-10 (см. Ограничение ввода данных значениями в раскрывающемся списке)

Ограничение ввода данных датами в заданном интервале времени

Выполнить п.п. 1-3 (см. Ограничение ввода данных значениями в раскрывающемся списке)

В поле Тип данных выберите значение Дата.

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

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

Например, для задания интервала времени между текущей датой и датой через 3 дня после текущей выберите ограничение Между в поле Значение, введите =СЕГОДНЯ() в поле Минимум и затем введите =СЕГОДНЯ()+3 в поле Максимум.

Выполнить п.п. 7-10 (см. Ограничение ввода данных значениями в раскрывающемся списке)

Ограничение ввода данных временем в заданном интервале

Выполнить п.п. 1-3 (см. Ограничение ввода данных значениями в раскрывающемся списке)

В поле Тип данных выберите значение Время.

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

Укажите время начала, окончания или определенное время, которое необходимо разрешить. Также можно ввести формулу, которая возвращает значение времени.

Например, для задания в ячейке H1 интервала времени для подачи завтрака, равного пяти часам после момента открытия ресторана, в поле Значение выберите значение Между, в поле Время начала введите =H1, а затем в поле Время конца введите =H1+"5:00".

Выполнить п.п. 7-10 (см. Ограничение ввода данных значениями в раскрывающемся списке)

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

Выполнить п.п. 1-3 (см. Ограничение ввода данных значениями в раскрывающемся списке)

В поле Тип данных выберите значение Длина текста.

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

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

Например, для задания конкретной длины для поля имени и фамилии (C1), равной текущей длине поля имени (A1) и поля фамилии (B1) плюс 10, выберите значение ограничения Меньше или равно в поле Значение и введите =СУММ(ДЛСТР(A1);ДЛСТР(B1);10) в поле Максимум.

Выполнить п.п. 7-10 (см. Ограничение ввода данных значениями в раскрывающемся списке)

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

Выполнить п.п. 1-3 (см. Ограничение ввода данных значениями в раскрывающемся списке)

В поле Тип данных выберите нужный тип данных.

В поле Значение выберите необходимый тип ограничения.

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

Например, чтобы допустить ввод сведений для счета только тогда, когда итог не превышает бюджет в ячейке E4, выберите значение Действительное в списке Тип данных, ограничение Меньше или равно в списке Значение, а в поле Максимум введите запись =E4.

Выполнить п.п. 7-10 (см. Ограничение ввода данных значениями в раскрывающемся списке)

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

Выполнить п.п. 1-3 (см. Ограничение ввода данных значениями в раскрывающемся списке)

В поле Тип данных выберите значение Другой.

В поле Формула введите формулу для расчета логического значения (ИСТИНА для верных или ЛОЖЬ для неверных данных). Например:

Выполнить п.п. 7-10 (см. Ограничение ввода данных значениями в раскрывающемся списке)