Контроль данных.

В некоторых случаях целесообразно контролировать данные, вводимые пользователем в ячейки таблицы. Excel позволяет задать тип данных, которые пользователь может ввести в ячейку, и диапазон допустимых значений. Например, можно задать, что в ячейку можно ввести только целое число в диапазоне от 1 до 100. Можно также контролировать дробные числа, даты, время и строки. В случае если вводимые данные неверны, на экране появляется сообщение об ошибке, текст которого определяет разработчик таблицы. Чтобы не допустить ввод в ячейку неверных данных, нужно определить условие проверки и сообщение об ошибке. Для этого надо выделить ячейку или диапазон, для которого нужно определить процедуру контроля и выбрать команду “Данные”->“Проверка”. Во вкладке “Параметры” диалогового окна определить условие проверки.

В списке “Тип данных” надо выбрать тип данных, которые могут быть введены в ячейку. Для большинства таблиц можно точно задать тип допустимых данных. Например, ячейки могут содержать только целые числа или дробные. После выбора типа данных в списке “Значение” нужно выбрать способ сравнения вводимого в ячейку значения с эталоном, который может представлять собой диапазон или константу. Возможна проверка вводимого значения на принадлежность диапазону или сравнение с константой (=, <>, >, <, >=, <=). После этого, в зависимости от выбранного способа сравнения, нужно задать границы диапазона или константу (граничное значение). Например, если вводимое значение должно лежать в диапазоне от 1 до 100, то в списке “Тип данных” надо выбрать “Целое число”, в списке “Значение” - “между”, в поля “Минимум” и “Максимум” соответственно ввести 1 и 100.

Стандартное сообщение об ошибке - малоинформативное. Разработчик может определить свое, более конкретное сообщение, которое будет появляться на экране как реакция на ввод ошибочных данных. Для этого надо выбрать ячейку или диапазон, ввод данных в который контролируется, выбрать команду “Данные”->“Проверка” и во вкладке “Сообщение об ошибке” ввести параметры сообщения: тип сообщения, заголовок и текст сообщения. Excel позволяет выводить сообщения трех типов: “Останов”, “Предупреждение” и Сообщение. Каждому сообщению соответствуют свой значок и набор командных кнопок.

Примеры. Пример №1.

Условие. С помощью подбора параметра найти корень нелинейного уравнения

.

Решение.

Уравнение имеет 3 корня (так как уравнение третьей степени).

1. Протабулируем функцию на отрезке .

Столбец A будем использовать для значений , столбец B - для значений .

В ячейку A1 введем значение “-3” (без кавычек). В ячейку A2 введем значение “=A1+0,5”. При этом в ячейке отобразится число “-2,5”. Используя автозаполнение диапазона, заполним ячейки A3-A13. Для этого нужно подвести курсор мыши в правый нижний угол ячейки A2 и, держа нажатой левую клавишу мыши, перетащим курсор до ячейки A13. При этом ячейки A3-A13 автоматически заполнятся числами от -2 до 3 с шагом 0,5.

В ячейку B1 введем формулу “=A1^3+2*A1^2-A1-1”. Используя автозаполнение, заполним данной формулой ячейки B2-B13. При этом они заполнятся значениями функций, где значения будут браться из соответствующей ячейки столбца A.

2. Строим график данной функции.

Выделяем диапазон ячеек A1-B13, и выбираем команду “Вставка”->“Диаграмма”. Выбираем тип диаграммы “График”->“Точечный”. Нажимаем кнопку “Готово”.

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

3. Приближенные значения корней можно определить из графика. Но наибольший интерес представляют точные значение. Уточним значения корней с помощью средства “Подбор параметра”. Корень уравнения находится на пересечении графика с осью . В этой точке функция меняет знак (с “+” на “-” или с “-” на “+”). В данном случае первый корень находится в промежутке , второй корень - , третий - .

Ячейки B16, B17, B18 будем использовать для получения точных значений корней. В ячейку B16 введем приближенное значение первого корня (какое-либо значение из промежутка , например “-2,5”). В ячейку B17 введем приближенное значение второго корня (какое-либо значение из промежутка , например “-0,5”). В ячейку B18 введем приближенное значение третьего корня (значение из промежутка , например “0,5”).

В ячейки C16, C17, C18 введем формулы, зависящие от ячеек B16, B17, B18 соответственно. В ячейку C16 введем формулу “=B16^3+2*B16^2-B16-1”, в ячейку C17 - “=B17^3+2*B17^2-B17-1”, в ячейку C18 - “=B18^3+2*B18^2-B18-1”. При этом, в ячейках C16, C17, C18 отобразатся значения функции в приближенных значениях корней.

4. Уточним первый корень. Выберем команду “Сервис”->“Подбор параметра”. В поле “Установить в ячейке” записываем адрес ячейки с формулой (C16). В поле “Значение” записываем значение “0”, так как в точке с корнем функция равна нулю. В поле “Изменяя значение ячейки” записываем адрес ячейки с приближенным значением корня (B16). Нажимаем кнопку “ОК”. Подбор сопровождается появлением соответствующего диалога, в котором отображается найденное значение параметра. Нажимаем кнопку “ОК”. При этом в ячейке D1 заносится точное значение корня. Повторяем процедуру поиска второго и третьего корня. В итоге получаем значения корней .

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