Цифр технологии cfaf5a3ea6d16c316b8a26974d117a476dc6803c. Перечень лабораторных работ
 Скачать 119.21 Kb.
|
Решение экономических задач в системе электронных таблиц. | ||||||||||||||||||||||||||||||||||||
| Решите задачу своего варианта в пакете Excel. Сравните результаты с предыдущими способами. Найти минимум целевой функции Z=x1+x2 при ограничениях. 3x1+2x2 ≥ 11 4) x1+7x2 ≤ 29 7) x1-2x2 ≤ 1 4x1+2x2 ≥ 14 5) 2x1+7x2 ≤ 37 8) x1- x2 ≤ 2 9x1+2x2 ≥ 29 6) 3x1+7x2 ≤ 45 9) 3x1-2x2 ≤ 7 Неравенства выбирать согласно варианту, по одному из каждой группы. Варианты систем неравенств согласно номеру компьютера приведены в табл. 1 (указаны номера неравенств, образующих систему). Таблица 1
| ||||||||||||||||||||||||||||||||||||
Итоговая лабораторная работа по Excel | ||||||||||||||||||||||||||||||||||||
| ОБРАБОТКА РЕЗУЛЬТАТОВ ЭКСПЕРИМЕНТА ЗАДАНИЕ 1. На первом рабочем листе документа MS Excel ввести исходные дан- ные, соответствующие варианту задания. Проанализировать эксперимен- тальную зависимость. Построить график экспериментальных точек. 2. На втором рабочем листе средствами MS Excel рассчитать коэффи- циенты регрессии, коэффициент корреляции, среднеквадратичные отклоне- ния и суммарную ошибку. Построить в одной графической области экспери- ментальные точки и линию регрессии. 3. Третий рабочий лист должен содержать вычисление коэффициентов функциональной зависимости, соответствующей варианту задания. Расчет коэффициентов произвести аналитически при помощи метода наименьших квадратов, сведя задачу к задаче оптимизации. Построить в одной графиче- ской области экспериментальные точки и график подобранной функциональ- ной зависимости. Определить суммарную ошибку. 4. На четвертом рабочем листе построить линию тренда, если это воз- можно. Убедится в том, что вычисленные в п.3 коэффициенты совпадают с коэффициентами линии тренда. Провести сравнительный анализ полученных результатов и построить в одной графической области график эксперимен- тальных точек, линию регрессии и график полученной экспериментальной зависимости. 5. Озаглавить рабочие листы согласно тематике вычислений. Исходные данные, результаты вычислений и графики сопровождать соответствующими подписями и пояснениями. ВАРИАНТЫ ЗАДАНИЙ Вариант №1. P(s)=As3+Bs2+Сs+D S 0,00 1,00 1,50 2,00 2,50 3,00 3,50 4,00 4,50 5,00 P 10,00 50,10 39,58 15,40 23,68 33,60 57,78 100,90 149,50 256,00 Вариант № 2. G(s)=Aebs s 0,5 1,5 2 2,5 3 3,5 4 4,5 5 G 3,99 5,65 6,41 7,71 11,215 17,611 27,83 38,19 39,3 Вариант № 3. K(s)=Asb s 0,5 1 1,5 2 2,5 3 3,5 4 K 1,65 2,1 2 2,1 2,3 2,4 2,22 2,59 Вариант № 4. V(s)=AsbеCs s 0,2 0,7 1,2 1,7 2,2 2,7 3,2 V 2,3198 2,9569 2,3999 6,4357 6,5781 6,9459 14,6621 Вариант № 5. W(s)=A/(Bs+C) s 1 2 3 4 5 6 7 8 9 W 0,529 0,298 0,267 0,171 0,156 0,124 0,1 0,078 0,075 Вариант № 6. Q(s)=As2+Bs+C s 1 1,25 1,5 1,75 2 2,25 2,5 2,75 3 Q 5,21 4,196 3,759 3,672 4,592 4,621 5,758 7,173 9,269 Вариант № 7. Y=x/(Ax-B) x 3 3,1 3,2 3,3 3,4 3,5 3,6 3,7 3,8 3,9 Y 0,61 0,6 0,592 0,58 0,585 0,583 0,582 0,57 0,572 0,571 Вариант № 8. V=1/(A+Be-U) U 2 2,5 3 3,5 4 4,5 5 5,5 6 V 5,197 7,78 11,14 15,09 19,24 23,11 26,25 28,6 30,3 Вариант № 9. R=AtB+14,5 t 1 2 3 4 5 6 7 8 9 10 R 2,11 5,2 5,15 19,27 18,2 30,37 32 31 30,22 31,2 Вариант № 10. R=Ch2+Dh+K h 2 4 6 8 10 12 14 16 R 0,035 0,09 0,147 0,1 0,24 0,28 0,31 0,34 Вариант №11. G=DL+K L 0 0,5 1 1,5 2 2,5 3 3,5 4 G 2 2,39 2,81 3,25 3,75 4,11 4,45 4,85 5,25 Вариант № 12. Y=Ax3+Bx2+Cx+D x 1 1,5 2 2,5 3 3,5 4 4,5 5 Y 14,5 25 26,9 83,75 89,9 219,1 326,1 464 637,5 Вариант № 13. Y=Ax3+Cx+D x 1 1,5 2 2,5 3 3,5 4 4,5 5 Y 6,5 20,38 46,4 88,63 151,1 237,9 535 500,3 684,5 Вариант № 14. R=Ch2+K h 1 1,5 2 2,5 3 3,5 4 4,5 R 7,5 14,25 23,7 25,86 50,7 68,25 88,5 111,5 Вариант № 15. Z=At4+Ct2+K t 1 1,5 2 2,5 3 3,5 4 4,5 5 Z 2,2 10,6 35,6 90 191,1 359,2 618,7 997,9 1598,5 Вариант № 16. Z=At4+Bt3+Dt+K T 1 1,5 2 2,5 3 3,5 4 4,5 5 Z 2,21 9,83 30,5 74,5 155,2 288,86 494,5 794,69 1214,6 Вариант № 17. Z=At4+Bt3+Ct2+K t 1 1,5 2 2,5 3 3,5 4 4,5 5 Z 5,25 13,4 31,29 64,64 121,23 209,94 341,23 527,14 751 Вариант № 18. Z=At4+Dt+K t 0,4 0,6 0,8 1 1,2 1,4 1,6 1,8 2 Z 0,5 1,35 2,37 3,8 5,8 8,65 12,57 18,05 0,86 Вариант № 19. Y=Ax3+D x 0 0,2 0,4 0,6 0,8 1 1,2 1,4 1,6 1,8 Y 3,6 3,59 3,65 3,96 4,12 4,86 5,67 6,85 8,42 10,47 Вариант № 20. R=AtB t 1 1,5 2 2,5 3 3,5 4 4,5 5 R 2,19 14,8 57,15 163,2 384,5 793,5 1486 2585,5 4242,22 Вариант № 21. W(s)=1/(Bs-C) S 1 1,5 2 2,5 3 3,5 4 4,5 5 W 0,19 0,17 0,15 0,14 0,13 0,12 0,11 0,1 0,9 Вариант № 22. V(s)=sbеCs S 1 1,1 1,2 1,3 1,4 1,5 1,7 1,8 1,9 2 V 20 33 52,5 83,5 130,2 202,5 310 475 1079 1614 Вариант № 23. Y=x/(Ax+B) x 1 1,5 2 2,5 3 3,5 4 4,5 5 Y 0,214 0,221 0,2237 0,2258 0,2262 0,2268 0,2275 0,2283 0,2288 Вариант № 24. V(s)=AsеCs s 1 2 3 4 5 6 7 8 V 43,75 32,25 17,83 8,76 4 1,77 0,76 0,32 Вариант № 25. V(s)=Asbеs s 1 1,5 2 2,5 3 3,5 4 4,5 V 6,5 8,71 12,67 18,6 27,63 42,61 65,6 101,86 Вариант № 26. V=(A+BeU) U 0 0,5 1 1,5 2 2,5 3 3,5 V 3,597 4,597 5,5984 7,5987 11,269 17 26,5096 42,1599 Вариант №27. G=D/L+K L 1 1,13 1,25 1,38 1,5 1,63 1,75 1,88 2 G 3,8 3,2 2,6 2,2 1,8 1,6 1,4 1,3 1,1 Вариант № 28. V(s)=AsB s 1 2,3 2,9 4,1 5,2 5,9 6,8 8,1 9,2 V 2,4 29 58,5 165,5 337,5 493 754,5 11275 1868 Вариант № 29. K(s)=AeSb s 0 0,1 0,2 0,3 0,4 0,5 0,6 0,7 0,8 K 2,2 3,73 6,25 10,49 17,61 29,65 49,84 140,9 237,1 Вариант № 30. Z=At5+Bt4+Ct3+Dt2+Kt+L t 0,66 0,9 1,17 1,47 1,7 1,74 2,08 2,63 3,12 Z 38,9 68,8 64,4 66,5 64,95 59,36 82,6 90,63 113,5 Вариант № 31. R= At5+Bt4+Ct3+Dt2+L T 2 4 6 8 10 12 14 16 R 0,035 0,09 0,147 0,1 0,24 0,28 0,31 0,34 Вариант №32. G= At5+Bt4+Dt2+Kt+L t 0 0,5 1 1,5 2 2,5 3 3,5 4 G 2 2,39 2,81 3,25 3,75 4,11 4,45 4,85 5,25 Вариант № 33. Y= At5+Ct3+Dt2+Kt+L T 1 1,5 2 2,5 3 3,5 4 4,5 5 Y 14,5 25 26,9 83,75 89,9 219,1 326,1 464 637,5 Вариант № 34. Y= At5+Bt4+Ct3+Kt+L x 1 1,5 2 2,5 3 3,5 4 4,5 5 Y 6,5 20,38 46,4 88,63 151,1 237,9 535 500,3 684,5 Вариант № 35 R= At5+Ct3+Kt t 1 1,5 2 2,5 3 3,5 4 4,5 5 R 2,19 14,8 57,15 163,2 384,5 793,5 1486 2585,5 4242,22 Вариант № 36. R= At5+Dt2+Kt t 1 1,5 2 2,5 3 3,5 4 4,5 5 R 2,19 14,8 57,15 163,2 384,5 793,5 1486 2585,5 4242,22 Вариант № 37. W= At5+Dt2+L T 1 1,5 2 2,5 3 3,5 4 4,5 5 W 0,19 0,17 0,15 0,14 0,13 0,12 0,11 0,1 0,9 Вариант № 38. V= At5+L t 1 1,1 1,2 1,3 1,4 1,5 1,7 1,8 1,9 2 V 20 33 52,5 83,5 130,2 202,5 310 475 1079 1614 МЕТОДИЧЕСКИЕ УКАЗАНИЯ К ВЫПОЛНЕНИЮ ЛАБОРАТОРНОЙ РАБОТЫ Одной из распространенных задач в науке, технике, экономике является аппроксимация экспериментальных данных алгебраических данных аналитическими выражений. Возможность подобрать параметры уравнения таким образом, чтобы его решение совпало с данными эксперимента, зачастую является доказательством (или опровержением) теории. Рассмотрим следующую математическую задачу. Известные значения не- которой функции f образуют следующую таблицу: Таблица x x0 x1 x2 . . . . xN f(x) y0 y1 y2 . . . . yN Необходимо построить аналитическую зависимость y=f(x), наиболее близко описывающую результаты эксперимента. Построим функцию y=f(x, a0 a1, ..., ak) таким образом, чтобы сумма квадратов отклонений измеренных значений yi от расчетных f(xi ,a0, a1 , ..., ak) была наименьшей. Рисунок 1 Математически эта задача равносильна следующей: Найти значение параметров a0, a1, a2 , ...,ak при которых функция прини- мала минимальное значение. Эта задача сводится к решению системы уравнений Если параметры ai входят в зависимость y=f(x,ao,a1, …, ak) линейно, то мы получим систему линейных уравнений Решив систему, найдем параметры ao, a1, ..., ak и получим зависимость y = f(x, ao, a1, ..., ak). Линейная функция (Линия регрессии). Необходимо определить параметры функции y=ax+b. Составим функцию Продифференцируем выражение (15.4) по a и b, сформируем систему ли- нейных уравнений, решив которую мы получим следующие значения пара- метров: Подобранная прямая называется линией регрессии y на x, a и b назы- ваются коэффициентами регрессии. Чем меньше величина тем более обосновано предположение, что табличная зависимость описывается линейной функцией. Существует показатель, характеризующий тесноту линейной связи между x и y. Это коэффициент корреляции. Он рассчитывается по формуле: Коэффициент корреляции r и коэффициент регрессии a связаны соотношением Значение коэффициента корреляции удовлетворяет соотношению –1 ≤ r ≤ 1. Чем меньше отличается абсолютная величина r от единицы, тем ближе к линии регрессии располагаются экспериментальные точки. Если коэффици- ент корреляции равен нулю, то переменные x, y называются некоррелиро- ванными. Если r = 0, то это только означает, что между x, y не существует линейной связи, но между ними может существовать зависимость, отличная от линейной. Для того, чтобы проверить, значимо ли отличается от нуля коэффициент корреляции, можно использовать критерий Стьюдента. Вычисленное значе- ние критерия определяется по формуле: Значение t сравнивается со значением, взятым из таблицы распределе- ния Стьюдента в соответствии с уровнем значимости a и числом степеней свободы n-2. Если t больше табличного, то коэффициент корреляции значи- мо отличен от нуля. Реализация в MS Excel Вычисление коэффициентов регрессии осуществляется с помощью функции ЛИНЕЙН() ЛИНЕЙН(Значения_y; Значения_x; Конст; статистика) Значения_y — массив значений y. Алексеев Е. Р., Чеснокова О. В. Лабораторные работы по МЫ EXCEL 9 Значения_x— необязательный массив значений x, если массив х опущен, то предполагается, что это массив {1;2;3;...} такого же размера, как и Значе- ния_y. Конст— логическое значение, которое указывает, требуется ли, чтобы константа b была равна 0. Если Конст имеет значение ИСТИНА или опуще- но, то b вычисляется обычным образом. Если аргумент Конст имеет значение ЛОЖЬ, то b полагается равным 0 и значения a подбираются так, чтобы вы- полнялось соотношение y = ax. Статистика— логическое значение, которое указывает, требуется ли вер- нуть дополнительную статистику по регрессии. Если аргумент статистика имеет значение ИСТИНА, то функция ЛИНЕЙН возвращает дополнитель- ную регрессионную статистику. Если аргумент статистика имеет значение ЛОЖЬ или опущен, то функция ЛИНЕЙН возвращает только коэффициент a и постоянную b. Для вычисления множества точек на линии регрессии используется функция ТЕНДЕНЦИЯ ТЕНДЕНЦИЯ(Значения_y; Значения_x; Новые_значения_x; Конст) Значения_y— массив значений y, которые уже известны для соотношения y = ax + b. Значения_x— массив о значений x. Новые_значения_x— новый массив значений, для которых ТЕНДЕНЦИЯ возвращает соответствующие значения y. Если Но- вые_значения_x опущены, то предполагается, что они совпадают с массивом значений х. Конст — логическое значение, которое указывает, требуется ли, чтобы константа b была равна 0. Если Конст имеет значение ИСТИНА или опуще- но, то b вычисляется обычным образом. Если Конст имеет значение ЛОЖЬ, то b полагается равным 0, и значения a подбираются таким образом, чтобы выполнялось соотношение y = ax. Необходимо помнить, что результатом функций ЛИНЕЙН, ТЕНДЕНЦИЯ является множество значений – массив. Для расчета коэффициента корреляции используется функция КОРРЕЛ, возвращающая значения коэффициента корреляции КОРРЕЛ(Массив1;Массив2) Массив1 — массив значений y. Массив2 — массив значений y. Мас- сив1 и Массив2 должны иметь одинаковое количество точек данных. Рассмотрим построение линии регрессии с помощью MS Excel на при- мере следующей задачи. Известна табличная зависимость G(L). Построить линию регрессии и вычислить ожидаемое значение в точках 0, 0.75, 1.75, 2.8, 4.5. Введем таблицу значений в лист MS Excel и построим точечный график. Рабочий лист примет вид В ячейке M2 будет рассчитываться значение коэффициента корреля- ции, для чего туда следует ввести формулу =КОРРЕЛ(B1:J1;B2:J2). В ре- зультате рабочий лист примет вид Теперь с помощью функции ТЕНДЕНЦИЯ вычислим ожидаемое зна- чение в точках 0, 0.75, 1.75, 2.8, 4.5. Для этого в ячейки L9:L13 занесем эти значения, а ячейки M10:M13 введем формулу =ТЕНДЕНЦИЯ(B2:J2;B1:J1;L9:L13) для расчета ожидаемых значений. В результате получим следующие значения. X Y 0 1.64 0.75 2.35 1.75 3.30 2.8 4.30 4.5 5.92 Изобразим линию регрессии на диаграмме. Для этого выделим экспе- риментальные точки на графике, щелкнем правой кнопкой мыши и выберем команду Исходные данные. В появившемся диалоговом окне (см. рис. 5), для добавления линии регрессии щелкнем по кнопке Добавить. В качестве имени введем Линия регрессии, в качестве Значения Х L9:L13, в качестве Значения Y M9:M13. Далее выделяем линию регрессии, для изменения ее типа щелкаем правой кнопкой мыши и выбираем команду Тип диаграммы (рис. 6). Для форматирования линии регрессии дважды щелкаем по ней (рис. 7), можно изменить толщину линии, цвет, тип маркера и т.д. После форматирования графика рабочий лист примет вид, Квадратичная функция Необходимо определить параметры функции y = ao + a1 x + a2 x2. Составим S [yi a0 a1xi a 2xi ] Для этой функции запишем систему уравнений Для нахождения параметров ao, a1, a2 необходимо решить систему линей- ных алгебраических уравнений (6). Кубическая функция Необходимо определить параметры многочлена третьей степени: y = ao + a1 x + a2 x2 + a3 x3. Составим функцию S Для нахождения параметров ao, a1, a2, a3 необходимо решить систему четы- рёх линейных алгебраических уравнений. Подбор параметров функции y = a xb. Для нахождения параметров функции y = a xb проведем логарифмирова- ние функции y. Lg y = Lg a + b Lg x Сделаем замену Y = lg y; X = lg x. Получим линейную зависимость Y = A + b X. Найдем коэффициенты линии регрессии A и b. Затем определяем a = 10A. Мы получили значение параметров функции y=axb. Подбор параметров функции y = aebx. Прологарифмируем выражение y = aebx; Lg y = Lg a + bx Lg e; Проведём замену Y = Lg y. Вновь получаем линейную зависимость Y=Bx+A, где A = Lg a; B = b Lg e. Найдем A и B. Затем определим значение парамет- ров a и b, a = 10A и b = B / Lg e. Ниже проведены замены переменных, кото- рые преобразовывают функции вида y = f (x, a, b) к линейной зависимости Y= Ax+B. Подбор параметров функции y =axb ecx Прологарифмируем выражение y = axb ecx, после логарифмирования оно принимает вид: Lg y = Lg a + b Lg x +c Lg ex (9) Сделаем замену Y=Lgy, A=Lga, C=cLge. После замены выражение (9) при- нимает вид: Y = A+b Lg X+CX (10) Для функции (10) составим функцию S (см. формулу (1)): Параметры A, b и С следует выбрать таким образом, чтобы функция S была минимальной. Необходимым условием минимума S являются соотношения (2). Подставим (11) в (2), и после элементарных преобразований получим систему трёх линейных алгебраических уравнений для определения коэффи- циентов A, b и C. РЕАЛИЗАЦИЯ В MS EXCEL Построение различных аппроксимирующих зависимостей в MS Excel релизовано в виде свойства диаграммы – линии тренда. Рассмотрим механизм использования линии тренда на примере сле- дующей задачи. В результате эксперимента была определена некоторая табличная зависимость. Выбрать и построить аппроксимирующую зависимость. Построить графики табличной и подобранной аналитической зависимо- сти. Вычислить ожидаемое значение в указанных точках. x1=0.1539, x2=0.2569, x3=0.28 X 0.15 0.16 0.17 0.18 0.19 0.20 Y 4.4817 4.4930 5.4739 6.0496 6.6859 7.3891 Решение задачи можно разбить на следующие этапы. 1. Ввод исходных данных и построение точечного графика (см. рис. 9). 2. Выделяем экспериментальные точки, щелкаем правой кнопкой мы- ши и выбираем команду Добавить линию тренда. Появляется диа- логовое окно (см. рис. 10), позволяющее построить аппроксими- рующую зависимость. На первой вкладке выбираем вид аппрокси- мирующей зависимости (выбираем полиномиальную зависимостьв- торой степени). На второй необходимо определить параметры по- строения: a. Название аппроксимирующей зависимости b. Прогноз вперед (назад) на n единиц, этот параметр определя- ет, на сколько единиц вперед (назад) необходимо продлить линию тренда. c. Показывать ли точку пересечения кривой с прямой Y=const; d. Показывать аппроксимирующую функцию на диаграмме или нет (параметр показывать уравнение на диаграмме); Алексеев Е. Р., Чеснокова О. В. Лабораторные работы по МЫ EXCEL 18 Экспериментальные значения e. Помещать ли на диаграмму величину среднеквадратичного отклонения или нет (параметр поместить на диаграмму вели- чину достоверности аппроксимации); После щечка по кнопке OK диаграмма примет вид (см. рис. 11): 3. Для расчета ожидаемых значений в точках 0.1539, 0.2569, 0.28 вве- дем эти значения в ячейки B4:D4. В ячейку B5 введем формулу по- добранной аппроксимирующей зависимости (=371.62*B4^2- 68.093*B4+6.1891) и скопируем ее в ячейки C5, D5. Рабочий лист примет вид: 4. Добавим полученные расчетные значения на диаграмму. Для этого на диаграмме выделим экспериментальные значения, щелкнем пра- вой кнопкой мыши и выберем команду Исходные данные. Добавим Алексеев Е. Р., Чеснокова О. В. Лабораторные работы по МЫ EXCEL 20 туда Рассчитанные значения (см. рис. 12). 5. В результате диаграмма примет вид (см. рис. 13). Рисунок 12 y = 371.62x2 - 68.093x + 6.1891 Экспериментальные значения R2 = 0.9793 Рассчитанные значения Квадратичная зависимость Аналогично с помощью линии тренда можно подобрать и параметры других типов зависимостей (линейной, логарифмической и экспоненциаль- ной и т. д.). Решение задачи с помощью блока «Поиск решения» Рассмотрим на примере решения следующей задачи. В результате эксперимента получена следующая зависимость z(t) T 0,66 0,9 1,17 1,47 1,7 1,74 2,08 2,63 3,12 Z 38,9 68,8 64,4 66,5 64,95 59,36 82,6 90,63 113,5 Подобрать коэффициенты зависимости методом наименьших квадратов Z(t)=At4+Bt3+Ct2+Dt+K. Эта задача эквивалентна задаче нахождения минимума функции пяти переменных Введем табличную зависимость в рабочий лист MS Excel и построим график функции (см. рис. 14). Рассмотрим процесс решения задачи оптимизации (13). Пусть значения А, В, С, D и К хранятся в ячейках K1:K5. Теперь в 23 строку введем значения функции At4+Bt3+Ct2+Dt+K. В ячейку B23 введем значение функции в пер- вой точке (ячейка B1) =$K$1*B1^4 +$K$2*B1^3 +$K$3*B1^2 +$K$4*B1 +$K$5. Получим ожидаемое значение (в начале 0) в точке B1. Затем растянем эту формулу на весь диапазон B23:J23. В 24-ю строку введем квадраты раз- ности между экспериментальными и расчетными точками. В ячейку B24 вве- дем формулу =(B23-B2)^2 и растянем эту формулу на весь диапазон B24:J24. В ячейку В25 будем хранить суммарную квадратичную ошибку (см. формулу 13). Для этого введем формулу = СУММ(B24:J24). Теперь осталось с помощью решающего блока (Сервис \ Поиск реше- ния) решить задачу оптимизации без ограничений. Установит минимум в ячейки B25 (формула Σ= S(A,B,C,D,K) [zi Ati Bt Ct Dt K] ) из- меняя содержимое ячеек K1:K5 (переменные А, В, С, D и К) (см. рис. 15). Рисунок 15 После этого в ячейках K1:K5 получим значения параметров функции А, В, С, D и К функции At4+Bt3+Ct2+Dt+K. В ячейках B23:J23 получим ожи- даемые значение функции в исходных точках. Поместим эти точки в виде от- дельной линии на графике. В ячейке B25 будет храниться суммарная квадра- тичная ошибка. В результате рабочий лист примет вид (см. рис. 16). Рисунок 16__ | ||||||||||||||||||||||||||||||||||||