Практическая работа №4. Тема: Составление условий в электронных таблицах.

Часть I.

Задание 1.

Составьте электронную таблицу результатов сдачи экзаменов.

Для этого:

1) Заполните данными электронную таблицу:

Результаты экзаменов по математике
Студент	Экзамен1	Экзамен2	Экзамен3	Экзамен4	Среднее	Пропуски	Сдал/Не сдал
Азимов	87	90	79	96		2
Булавин	92	94	94	97		5
Гаврилов	96	95	95	80		0
Пашков	85	87	87	88		4
Рейн	81	88	88	85		1

2) Создайте формулу, которая возвращала бы текстовое значение Сдал, если студент по результатам экзаменов набирает средний балл выше 75 и при этом пропустил не больше 5 занятий без уважительных причин. Для этого в ячейку H4 введите формулу =ЕСЛИ(И(G4<5;F4>75); «Сдал»; «Не сдал») и скопируйте ее в ячейки H4:H8. Из табл. на рис. 1 видно, что только один студент не сдал экзамен, поскольку он имеет больше 5 пропусков занятий.

3) Если в формуле вместо функции И использовать функцию ИЛИ, то все студенты сдали бы экзамен, поскольку у всех средний балл выше 75. Измените в формуле функцию И на ИЛИ и посмотрите как изменились результаты вычислений.

4) Результаты выполнения задания покажите преподавателю.

На рис. 1 представлена результирующая таблица к заданию 1, п.1)-2):

Рис. 1. Результаты выполнения задания 1, п. 1)-2).

Обратите внимание, что функция ИЛИ возвращает ИСТИНА, если выполняется хотя бы одно условие в логическом выражении; функция И возвращает ИСТИНА только в случае, когда все логические выражения истинны.

Задание 2.

Создайте на рабочем листе таблицу «Примеры условий»:

1	A	B	C
2	Исходные данные		Результат
3	2	3	=ЕСЛИ(А13>B13;A13;B13)
4	5	1	=ЕСЛИ(А14>B14; “да”; “нет”)
5	Слово	4	=ЕСЛИ(ИЛИ(ЕПУСТО(А15);ЕТЕКСТ(А15)); “да”; “нет”)
6	15	-3	=ЕСЛИ(B16<0;1;ЕСЛИ(В16=0;0;А16/B16))

1) В ячейку С13 введите большее из значений, содержащихся, в соседних слева ячейках.

2) В ячейку С14 введите слово “Да”, если значение ячейки А14 больше значения ячейки B14. В противном случае введите слово “Нет”.

3) Создайте формулу, записывающую в ячейку С15 слово “Да”, если ячейка А15 пуста или содержит текст. В противном случае в ячейке С15 должно появляться слово “Нет”.

4) Сформируйте формулу, определяющую значение ячейки С16 как:

- 1, если содержимое ячейки B16 меньше 0;

- 0, если содержимое ячейки B16 равно 0;

- формулу A16/B16, если B16, больше 0.

Результат выполнения задания 2, см. рис.2:

Рис. 2. Таблица с результатами выполнения задания 2.

5) Результат выполнения задания покажите преподавателю.

Часть II.

Задание 1. Составьте электронную таблицу для решения любого квадратного уравнения с помощью дискриминанта:

Решение квадратного уравнения
Коэффициент а		Значение дискриминанта
Коэффициент b
Коэффициент c
Корень x1
Корень x2

Запишите в соответствующие ячейки формулы и условия для вычисления дискриминанта и корней уравнения со ссылкой на ячейки с исходными данными. Проверьте работу таблицы на примерах:

(два корня 1; 0,2)

(один корень 0,25)

(корней нет).

Для этого:

1) Запишите в ячейки B3:B5 значения коэффициентов a,b,c. В ячейку С3 введите формулу: = B4^2-4*B3*B5.

2) В ячейку B7 введите формулу =(-$B$4+КОРЕНЬ($C$3))/(2*$B$3); В ячейку B8 введите формулу =(-$B$4-КОРЕНЬ($C$3))/(2*$B$3).

3) В ячейку С7 введите формулу =ЕСЛИ(C3>0;" уравнение имеет два корня";" "); в ячейку С8 введите формулу =ЕСЛИ(C3=0;"уравнение имеет один корень";" "); в ячейку С9 введите формулу =ЕСЛИ(C3<0;"уравнение не имеет действительных корней"; " ").

4) Произведите вычисления для трех указанных примеров. Результат вычислений покажите преподавателю.

Результат выполнения задания см. рис. 3: а); б; в).

Рис. 3а). Вычисление корней квадр. уравн. для D>0. Рис. 3б). Вычисление корней квадр. уравн. для D=0.

5) Ответьте на вопрос, почему в ячейках B7 и B8 определяется ошибка #ЧИСЛО?

6) Как можно изменить ссылки в формулах в ячейках B7 и B8, чтобы результат вычислений не изменился?

7) Произведите самостоятельные вычисления для примеров квадратных уравнений, указанных преподавателем.

Рис. 3в). Вычисление корней квадр. уравн. для D<0.

Задание 2.

Решение квадратного уравнения с использованием утилиты «Подбор параметра».

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

1) Перейдите на новый лист в своем рабочем файле. В ячейку А1 запишите заголовок: «Решение квадратного уравнения с помощью утилиты «Подбор параметра»».

2) В редакторе Microsoft Equation (приложение Microsoft Word) наберите формулу и скопируйте ее на рабочий лист вExcel.

2) В ячейку С5 введите начальное приближение для искомого корня (значение 0).

3) В ячейку С7 введите формулу =C5^2-4*C5+3 для вычисления по значению переменной полинома, соответствующего решаемому уравнению (см. рис. 4):

Рис. 4. Ввод исходных данных и запуск утилиты «Подбор параметра».

4) Далее запустите утилиту «Подбор параметра», для этого выберите: Данные → Работа с данными → Подбор параметра (см. рис.4). В открывшемся диалоговом окне указываем целевая ячейка С7 (в поле Установить в ячейке), значение целевой ячейки 0 (поле Значение), изменяемая ячейка С5 (поле Изменяя значение ячейки). Диалоговое окно Подбор параметра (см. рис. 5):

Рис. 5. Ввод параметров поиска меньшего корня в диалоговом окне Подбор параметра.

5) После щелчка на ОК получаем вполне неплохой результат для меньшего корня уравнения (см. рис. 6):

Рис. 6. Результат поиска меньшего корня уравнения .

6) Чтобы найти больший корень необходимо перед активизацией утилиты Подбор параметра указать в изменяемой ячейке другое начальное приближение. Введите в ячейку С5 значение 5. В поле Значение окна Подбор параметра введите значение 0 (см. рис. 7):

Рис. 7. Ввод параметров поиска большего корня в уравнении .

Рис. 8. Результат поиска большего корня в уравнении .

7) Произведите самостоятельно вычисления, используя утилиту «Подбор параметра» для примеров квадратных уравнений из задания 1. Результат вычислений покажите преподавателю.

Немного теории:

В использованном методе есть недостатки: 1) нет уверенности в том, что найдены все корни уравнения; 2) даже если известно общее количество решения не всегда можно предугадать, какое из решений будет найдено; 3) неудобно каждый раз запускать утилиту и выполнять настройки. Если настройки разные - тут уж никуда не денешься. Если настройки одни и те же (меняется только начальное значение изменяемой ячейки), то желательно процесс автоматизировать, учитывая, что значение ячейки изменяется в рабочем документе, а не в окне настроек.

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