Лабораторная работа 3.
Темы работы.
Подбор параметра.
Поиск решения.
Подбор параметра.
В Excel есть возможность выполнять подбор значений в одной ячейке , являющейся параметром , так чтобы в целевой ячейке установилось значение, равное требуемому (рис. 53 ). Эту задачу можно выполнить через пункт меню СЕРВИС- ПОДБОР ПАРАМЕТРА… Подбор параметра осуществляется методом последовательных приближений.
  
Рис. 53
При использовании функции подбора параметра необходимо, чтобы ячейка с целевым значением содержала ссылку на ячейку с изменяемым значением, т. е. эти ячейки должны быть связаны формулами.
Решение может быть и не найдено, например, уравнение не имеет решений. В силу используемого метода решение может быть найдено приблизительно, т.е. значение в целевой ячейке после выполнения операции не совсем совпадает с требуемым. В этом случае можно поступить следующим образом:
Увеличить количество итераций поиска (по умолчанию 100)-
СЕРВИС – ПАРАМЕТРЫ- ВЫЧИСЛЕНИЯ – ПРЕДЕЛЬНОЕ ЧИСЛО ИТЕРАЦИЙ.
Увеличить относительную погрешность (по умолчанию 0,001) –
СЕРВИС – ПАРАМЕТРЫ – ВЫЧИСЛЕНИЯ – ОТНОСИТЕЛЬНАЯ ПОГРЕШНОСТЬ.
Изменить начальное значение параметра.
Если на параметр или на значение в целевой ячейке требуется наложить ограничения, или в задаче нужно использовать несколько параметров, то такая задача может быть решена средством «Поиск решения», приведенным в следующем разделе.
Упражнение 10. «Проходной балл».
Задание. По результатам экзаменов и заданному плану приёма абитуриентов (табл. 11) вычислить значение проходного балла, необходимого для обеспечения требуемого количества студентов.
Таблица 11
|
A
|
В
|
С
|
D
|
|
№пп
|
Фамилия
|
Набрано баллов
|
Принят, не принят
|
|
1
|
Арбузова
|
50
|
|
|
2
|
Богогмолов
|
49
|
|
|
3
|
Высотчина
|
50
|
|
|
4
|
Ганохина
|
30
|
|
|
5
|
Гиясова
|
97
|
|
|
6
|
Гончаренко
|
40
|
|
|
7
|
Грицай
|
35
|
|
|
8
|
Дудина
|
20
|
|
|
9
|
Зеров
|
45
|
|
|
10
|
Иванова
|
10
|
|
|
|
|
Принято
|
0
|
|
|
|
Проходной балл
|
0
|
|
|
|
План приёма
|
6
|
Порядок выполнения.
Вывести выражение для вычисления значений в столбце «Принят, не принят». Для этого нужно составить Логическую конструкцию как в подобном упражнении в лабораторной работе 2 («Результаты вступительных экзаменов»), только в условии поставить адрес проходного балла. (рис. 54).
Рис. 54
При проходном балле 0 , естественно, все будут приняты.
Организовать подсчёт количества принятых с помощью функции СЧЁТЕСЛИ – так же как в лабораторной работе 2. Раз все приняты, то в ячейке D12 появится число10.
Выделить ячейку D13. Вызвать диалог «Подбор параметра». Заполнить его как на рис. 55 , щёлкнуть ОК.
Рис. 55
В результате подсчета параметра значение проходного бала станет 37 и количество принятых станет равно плану приёма, (табл. 12 )
Таблица 12
№пп
|
Фамилия
|
Набрано баллов
|
Принят, не принят
|
1
|
Арбузова
|
50
|
Принят
|
2
|
Богогмолов
|
49
|
Принят
|
3
|
Высотчина
|
50
|
Принят
|
4
|
Ганохина
|
30
|
Не принят
|
5
|
Гиясова
|
97
|
Принят
|
6
|
Гончаренко
|
40
|
Принят
|
7
|
Грицай
|
35
|
Не принят
|
8
|
Дудина
|
20
|
Не принят
|
9
|
Зеров
|
45
|
Принят
|
10
|
Иванова
|
10
|
Не принят
|
|
|
Принято
|
6
|
|
|
Проходной балл
|
37
|
|
|
План приёма
|
6
| Упражнение 11. «Корни кубического уравнения».
Задание. Известно, что кубическое уравнение У=0,5Х3+2X2-X-3 на участке от -5 до 2 имеет три действительных корня. Найти корни уравнения методом подбора параметра
Порядок выполнения.
Подготовить таблицу (табл.13) .
Таблица 13
|
А
|
В
|
С
|
|
Корни уравнения У=0,5Х3+2Х2-Х-3
|
|
|
Левая часть
|
Корни
|
|
1
|
|
|
|
2
|
|
|
|
3
|
|
|
В ячейке В3 вывести формулу: =0,5*C3^3+2*C3^2-C3-3
С помощью маркёра заполнения продолжить эту формулу на ячейки В5, В5.
Так как в задании указана область поиска от -5 до 2, то расставим начальные значения корней -5,0 и 2 соответственно. Тогда исходное состояние таблицы примет вид табл. 14.
Таблица 14
|
А
|
В
|
С
|
|
Корни уравнения У=0,5Х3+2Х2-Х-3
|
|
|
Левая часть
|
Корни
|
|
1
|
-10,5
|
-5
|
|
2
|
-3
|
0
|
|
3
|
7
|
2
|
Установить курсор в ячейке В3, вызвать диалог «Подбор параметра», заполнить бланк как на рис. 56.
Рис. 56.
В результате выполнения этой операции в ячейке В3 появится число, записанное в экспоненциальной форме, близкое к нулю, а в ячейке значение первого корня -4,13264.
Повторить подбор параметра для ячеек В4 и В5 . В результате должна получиться табл. 14.
Таблица 14
|
Левая часть
|
Корни
|
1
|
-3,29599E-05
|
-4,1326416
|
2
|
-0,000197104
|
-1,1403808
|
3
|
0,000296781
|
1,2731184
| Самостоятельная работа 15.
Задание. Отделу выделена премия(варианты в табл. 15 ) . Распределить премию сотрудникам отдела в зависимости от должностного оклада (табл. 16 ).
Пояснения к выполнению.
Предполагается, что премия будет начисляться в процентах от оклада, т.е.
премия =оклад*процент.
Сумма же всех премий должна равняться общей выданной на отдел премии.
Таблица 15
|
1 вариант
|
2 вариант
|
3 вариант
|
4 вариант
|
5 вариант
|
премия
|
10 000
|
15 000
|
20 000
|
25 000
|
30 000
|
Таблица 16
№пп
|
Фамилия
|
Оклад
|
Премия
|
1
|
Арбузова
|
10 000
|
|
2
|
Богогмолов
|
7 000
|
|
3
|
Высотчина
|
5 000
|
|
4
|
Ганохина
|
5 000
|
|
5
|
Гиясова
|
2 000
|
|
|
|
Сумма премий
|
|
|
|
Общая премия
|
Из табл.
|
|
|
Процент
|
| |
