Введенные значения. Проверка вводимых значений в Excel. Сообщение для ввода данных

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

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

Приготовьте лист доходов и расходов как показано на рисунке:

Прибыль – это естественно доход минус расход. Допустим нам нужно проверить, в какие дни прибыль падала ниже 40$. Решение следующее:

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

Окно контрольного значения в Excel

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

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

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

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

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

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

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

 ПРАКТИКУМ:

 В открытом файле 04_1 Защита , установить защиту на открытие файла, указав парольExcel2010 . Закрыть файл.

 Открыть файл Защита , снять пароль.

Проверка вводимых значений

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

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

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

Установка ограничений на ввод данных

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

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

1. Выделить диапазон ячеек.

2. На вкладке Данные , в группеРабота с данными , выбрать

Проверка данных .

3. В диалоговом окне Проверка вводимых значений на вкладкеПараметры ,

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

 Любое значение – нет ограничений на ввод данных.

 Целое число – ввод только целых чисел.

 Действительное – ввод как целых, так и дробных чисел.

 Дата – ограничение на ввод даты.

 Время – ввод только времени.

 Длина текста –

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

Microsoft Excel 2010. Уровень 2. Расширенные возможности

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

 Список – ввод данных из предлагаемого списка (нет ограничений по типу данных). Максимум может содержать 32767 элементов.Источник списка можно:

 Ввести с клавиатуры, разделяя через точку с запятой; (разделитель зависит от

региональных настроек – может быть запятая , ).

 Выделить ячейки с любого листа текущей книги.

 выбрать именованный диапазон с помощью клавиши F3 .

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

4. На вкладке Сообщение для ввода

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

подсказку, если ячейка является текущей .

5. На вкладке Сообщение об ошибке

В поле Вид , выбрать:

 Останов – запрет ввода неверных данных, появляется сообщение об ошибке.

 Предупреждение – ввод неверных данных допускается, при подтверждении пользователем –Да

 Сообщение – ввод неверных данных разрешается при подтверждении пользователем -ОК .

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

сообщение об ошибке .

При вводе неверных данных в ячейку отобразилось сообщение для ввода (подсказка) и предупреждение:

Microsoft Excel 2010. Уровень 2. Расширенные возможности

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

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

Для этого на вкладке Данные в группеРабота с данными , открыть список кнопкиПроверка данных и выбратьОбвести неверные данные в группе Работа с данными, открыть список кнопки Проверка данных и выбрать Удалить обводку неверных данных ] ] [ : <доп опции> ])

где

<запрос>
строка, содержащая вопрос;

<исходное значение>
значение, которое будет предложено по умолчанию;

<макс. ширина>
значение ширины поля для ввода строки;

<мин>, <макс>
соответственно минимальное и максимальное значение для ввода числа. Если задан только один параметр, то он считается максимальным, а минимальное значение равно нулю.

<доп опции>
для числа - число знаков после запятой, если параметр не указан может быть введено число с любым числом знаков;
для даты и времени - возможность ввода пустой даты и пустого времени;

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

строка (например, ВВЕСТИ_ЗНАЧЕНИЕ ("Введите назначение платежа" , "Оплата за " ));

число (например, ВВЕСТИ_ЗНАЧЕНИЕ ("Введите ставку налога" , 20));

дата (например, ВВЕСТИ_ЗНАЧЕНИЕ ("Введите дату начала работы" , 01.01.2010));

время (например, ВВЕСТИ_ЗНАЧЕНИЕ ("Во сколько прибыла машина" , ВРЕМЯ_ТЕК ));

логика (например, ВВЕСТИ_ЗНАЧЕНИЕ ("Делать свертку" , ДА ));

множество (например, ВВЕСТИ_ЗНАЧЕНИЕ ("НДС" , ндсПлюс ));

массив (например, ВВЕСТИ_ЗНАЧЕНИЕ ("Выберите вариант" , [ "Вариант1" , "Вариант2" , "Вариант3" ])).

В случае массива номер исходного значения вводится через запятую после перечисленных вариантов.

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

Пример 1:

Ниже приведен пример, в котором с помощью функции ВВЕСТИ_ЗНАЧЕНИЕ вводится номер записи в базе данных, а затем осуществляется переход на эту запись. При выполнении функции на экране появится диалоговое окно, содержащее графу для ввода номера записи:

По умолчанию в графе будет установлено значение, заданное вторым аргументом функции (в данном случае 1). Третий и четвертый параметры ограничивают соответственно минимальный и максимальный номер записи (в данном случае 1 и БД_ВСЕГО ). В переменную номер будет записано введенное в диалоговом окне значение.

номер = ВВЕСТИ_ЗНАЧЕНИЕ ("Введите номер колонки: " , 1, 1, колонки. ВСЕГО )

колонки. ПЕРЕЙТИ (номер)

Пример 2:

Ниже приведен пример, в котором при помощи функции ВВЕСТИ_ЗНАЧЕНИЕ предлагается выбрать один из предложенных вариантов. При выполнении функции на экране появится диалоговое окно, в котором будут перечислены варианты, заданные вторым параметром функции:

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

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

период_ид = ВВЕСТИ_ЗНАЧЕНИЕ ("Выберите период" ,[ "День" , "Пять Дней" , "Неделя" ,

"Декада" , "Месяц" , "Квартал" , "Год" ])

Набор вариантов можно также указать и с помощью переменной:

выбор!1 = "День"

выбор!2 = "Пять Дней"

выбор!3 = "Неделя"

выбор!5 = "Месяц"

выбор!6 = "Квартал"

выбор!7 = "Год"

период_ид = ВВЕСТИ_ЗНАЧЕНИЕ ("Выберите период" ,выбор)

дата_нач = ПЕРИОД_НАЧ (ДАТА_НАЧ ,период_ид)

дата_кон = ПЕРИОД_КОН (ДАТА_КОН ,период_ид)

В бланках и отчетах возможно использование функции ВВЕСТИ_ЗНАЧЕНИЕ! , которая не выводит диалога ввода. Вместо этого в контекстном меню бланка или диалоге параметров отчета, содержащего данную функцию, появляется опция, определяющая возвращаемый функцией результат.

Синтаксис:

ВВЕСТИ_ЗНАЧЕНИЕ! (<запрос> , <тип> или <множество> или <список значений> )

где

<запрос>
строковая константа, содержащая вопрос;

<тип>
тип ввода , возможно СТРОКА , ЧИСЛО , ЛОГИКА , ДАТА , ВРЕМЯ ;

<множество>
идентификатор множества;

<список значений>
список значений в квадратных скобках.

После восклицательного знака можно поставить.

Примеры:

ВВЕСТИ_ЗНАЧЕНИЕ! ("Заключение комиссии" , СТРОКА )

ВВЕСТИ_ЗНАЧЕНИЕ! ("Анализ" , [ "Полный" , "Частичный" ])

ВВЕСТИ_ЗНАЧЕНИЕ! ("Вид учета НДС" , ндсПлюс )