Использование табличного процессора Excel

Использование табличного процессора Excel

Для реализации численных методов

В инженерных и экономических расчетах

 

Методическая разработка

по курсу «Информатика»

для студентов всех форм обучения

 

Нижний Новгород 2000

 

Составители: В. Ф. Билюба, В. Н. Ершов, С. Н. Митяков, О. И. Митякова,

С. П. Никитенкова, Н. Я. Николаев

 

УДК 651. 3. 06

 

Использование табличного процессора Excel для реализации численных методов в инженерных и экономических расчетах: Метод. разработка по курсу «Информатика» для студентов всех форм обучения / НГТУ;

Сост.: В. Ф. Билюба, В. Н. Ершов, С. Н. Митяков, О. И. Митякова,

С. П. Никитенкова, Н. Я. Николаев. Нижний Новгород, 2000. 37 с.

 

Изложены элементы численных методов в инженерных и экономических расчетах. Приведены примеры их реализации с использованием табличного процессора Microsoft Excel.

 

Научный редактор Н.С. Петрухин

 

Редактор Е. В. Комарова

 

 

Подп. 22.06.2000. Формат 60х84¹/16. Бумага газетная. Печать офсетная.

Печ. л. 2,3. Уч. -изд. л. 2,1. Тираж 300 экз. Заказ 410.

_______________________________________________

 

Нижегородский государственный технический университет.

Типография НГТУ. 603600, Н. Новгород, ул. Минина, 24.

 

 

© Нижегородский государственный

технический университет, 2000

Введение

 

При решении многочисленных инженерных и экономических задач обыч­но реальное явление заменяется математической моделью. Модель является уп­рощенным представлением реальности и обычно содержит некоторое количе­ство уравнений. Главной задачей моделирования является максимальное при­ближение к реальности при достаточной простоте модели. В ряде случаев уда­ется найти аналитическое решение задачи. Однако в большинстве своем прихо­дится использовать численные методы. Эти методы предполагают применение ЭВМ и сводятся к некоторым действиям над числами. При этом в большинстве случаев решение является приближенным.

Существуют различные подходы к реализации численных методов. Тради­ционный подход предполагает построение алгоритма метода с последующим программированием на языке высокого уровня. В последнее время широко ис­пользуются специализированные программные продукты - математические па­кеты типа MathCad, которые существенно упрощают процесс составления ал­горитма и обладают встроенными библиотеками и графическими возможно­стями. В данной работе описан еще один подход, позволяющий в ряде случаев существенно ускорить процесс решения задачи. Он основан на использовании табличного процессора Excel, широко распространенного среди пользователей. Вместе с тем, применение данного программного продукта для реализации численных методов до сих пор не нашло соответствующего отражения в лите­ратуре.

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

Ознакомительные практические занятия.

Ввод и обработка текстовых и числовых данных. Использование

Подготовка и форматирование документа Excel.

Построение диаграммы

Последовательность действий:

1. Регистрация пользователя.

2. Запуск программы Excel.

3. Открыть свою рабочую книгу.

4. Переименовать следующий рабочий лист как «Прейскурант».

5. В ячейку А1 ввести название (текст) прейскуранта (рис. 2).

6. В ячейку А2 ввести текст «Курс пересчета»:, в ячейку В2 текст «1 у.е.=», в ячейку С2 ввести текущий курс пересчета.

7. В ячейку А3 ввести текст «Наименование товара», в ячейку В3 текст «Цена (у.е.)», в ячейку С3 текст «Цена (руб.)».

8. В последующие ячейки столбца А ввести 10 названий товаров, включенных в прейскурант.

9. В соответствующие ячейки столбца В ввести цены товаров в условных единицах.

10. В ячейку С4 ввести формулу с абсолютной ссылкой «=В4*$C$2”, используемую для пересчета из у.е. в рубли.

11. Методом автозаполнения протягиванием мыши заполнить весь столбец С.

12. Изменить курс пересчета в ячейке С2.

13. Выделить методом протягивания диапазон А1:С1 и дать команду Формат Ячейки. На вкладке «Выравнивание» задать выравнивание по горизонтали «По центру» и установить флажок «Объединение ячеек».

14. На вкладке «Шрифт» задать размер шрифта 14 и в списке «Начертание» выбрать вариант «Полужирный» и нажать <ОК>.

15. Щелкнуть правой кнопкой мыши на ячейке В2 и выбрать в контекстном меню команду «Формат ячеек». Задать выравнивание по горизонтали «По правому краю» и нажать <ОК>.

16. Щелкнуть правой кнопкой мыши на ячейке С2 и выбрать в контекстном меню команду «Формат ячеек». Задать выравнивание по горизонтали «По левому краю» и нажать <ОК>.

17. Выделить методом протягивания диапазон В2:С2. Щелкнуть на раскрывающей кнопке рядом с кнопкой «Границы» на панели инструментов «Форматирование» и задать для этих ячеек широкую внешнюю рамку (кнопка в правом нижнем углу открывшейся палитры).

18.Дважды щелкнуть на границе между заголовками столбцов А и В, В и С, С и D (при этом изменяется ширина столбцов А, В, С).

19.Сохранить рабочую книгу, выйти из Excel.

20.Запустить программу Excel.

21. Открыть свою рабочую книгу.

Рис. 2.

15. Переименовать следующий рабочий лист как «Обработка эксперимента».

16. В столбец А, начиная с ячейки А1, вводится произвольный набор из 15 значений независимой переменной.

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

18. Методом протягивания выделить все заполненные ячейки столбцов А и В.

19. Щелкнуть на значке «Мастер диаграмм» на стандартной панели инструментов.

20. В списке «Тип» выбрать пункт «Точечная» (для отображения графика, заданного парами значений). В палитре «Вид» выбрать пункт, где маркеры не соединяются кривыми. Щелкнуть на кнопке «Далее».

21. Убедиться в правильности данных на диаграмме. На вкладке «Ряд» в поле «Имя» указать «Результаты измерений». Щелкнуть на кнопке «Далее».

22. Выбрать вкладку «Заголовки». Убедиться, что заданное название ряда данных автоматически использовано как заголовок диаграммы. Заменить его, введя в поле «Название диаграммы» заголовок «Экспериментальные точки». Щелкнуть на кнопке «Далее».

23. Установить переключатель «Отдельном». По желанию, задать произвольное имя добавленного рабочего листа. Щелкнуть на кнопке «Готово».

24. Убедиться, что диаграмма построена и внедрена в новый рабочий лист. Рассмотреть ее и щелкнув на построенной кривой, выделить ряд данных.

25. Дать команду «Формат Выделенный ряд». Открыть вкладку «Вид».

26. На панели «Линия» открыть палитру «Цвет» и выбрать красный цвет. В списке «Тип линии» выбрать «Пунктир».

27. На панели «Маркер» выбрать в списке «Тип маркера» треугольный маркер. В палитрах «Цвет» и «Фон» выбрать зеленый цвет.

28. Щелкнуть на кнопке <ОК>. Снять выделение с ряда данных и посмотреть, как изменился вид графика.

29. Сохранить рабочую книгу, выйти из Excel.

 

С одним неизвестным

Постановка задачи

Дано уравнение F(x)=0. Это - общий вид нелинейного уравнения с одним неизвестным. Как правило, алгоритм нахождения корня состоит из двух этапов.

Отыскание приближенного значения корня или отрезка на оси абсцисс, его содержащего.

Уточнение приближенного значения корня до некоторой точности.

На первом этапе применяется шаговый метод отделения корней, на втором - один из методов уточнения (метод половинного деления, метод Ньютона или метод простой итерации).

 

2.2. Шаговый метод

Дано уравнение F(x)=0. Задан интервал поиска [ x ₀, x ₁]. Требуется найти интервал [ a, b ] длиной h, содержащий первый корень уравнения, начиная с левой границы интервала поиска.

Алгоритм метода:

Установить интервал [ a, b ] на начало интервала поиска (a = x ₀).

Определить координату точки b (b = a+h), а также значения функции в точках a и b: F(a) и F(b).

Проверить условие F(a)*F(b)<0. Если условие не выполнено - передвинуть интервал [ a, b ] на один шаг (a = b) и перейти к пункту 2. Если условие выполнено - закончить алгоритм.

Решением являются координаты точек a и b. Отрезок [ a,b ] содержит корень уравнения, поскольку функция F(x) на его концах имеет разные знаки (рис. 3).

 

 

 

Рис. 3

Найдя первый корень, можно продолжить поиск корней по тому же алгоритму. В этом случае определяются отрезки, содержащие все корни уравнения на интервале поиска [x₀,x₁]. Если на всем интервале поиска ни разу не было выполнено условие F(a)*F(b)<0, то данный интервал вообще не содержит корней.

2.3. Метод половинного деления

Метод основан на последовательном сужении интервала, содержащего единственный корень уравнения F(x)=0 до тех пор, пока не будет достигнута заданная точность e. Пусть задан отрезок [ a,b ], содержащий один корень уравнения. Этот отрезок может быть предварительно найден с помощью шагового метода.

Алгоритм метода:

Определить новое приближение корня x в середине отрезка [ a,b ]: x=(a+b)/2.

Найти значения функции в точках a и x: F(a) и F(x).

Проверить условие F(a)*F(x)<0. Если условие выполнено, то корень расположен на отрезке [ a,x ] (рис. 4). В этом случае необходимо точку b переместить в точку x (b=x). Если условие не выполнено, то корень расположен на отрезке [ x,b ]. В этом случае необходимо точку a переместить в точку x (a=x).

Перейти к пункту 1 и вновь поделить отрезок пополам. Алгоритм продолжить до тех пор, пока не будет выполнено условие ï F(x) ï<e.

 

 

2.4. Метод Ньютона

Задан отрезок [ a,b ], содержащий корень уравнения F(x)=0. Уточнение значения корня производится путем использования уравнения касательной. В качестве начального приближения задается тот из концов отрезка [ a,b ], где значение функции и ее второй производной имеют одинаковые знаки (т.е. выполняется условие F(x₀)*F¢¢(x₀)>0). В точке F(x₀) строится касательная к кривой y = F(x) и ищется ее пересечение с осью x. Точка пересечения принимается за новую итерацию. Итерационная формула имеет вид:

Итерационный процесс продолжается до тех пор, пока не будет выполнено условие ï F(x) <eï, где e - заданная точность.

Метод простой итерации

Метод основан на замене исходного уравнения F(x)=0 на эквивалентное x =j(x). Функция j(x) выбирается таким образом, чтобы на обоих концах отрезка [a,b] выполнялось условие сходимости êj¢(x) ê< 1. В этом случае в качестве начального приближения можно выбрать любой из концов отрезка. Итерационная формула имеет вид

Итерационный процесс продолжается до тех пор, пока не будет выполнено условие ï F(x) <eï, где e - заданная точность.

 

Реализация в пакете Excel

В качестве примера рассмотрим уравнение x ² - 4 x + 3 = 0. Интервал поиска [0;3,3], шаг h = 0,3. Решим его, используя различные численные методы, а также специальные возможности пакета Excel - «Подбор параметра» и «Поиск решения».

Последовательность действий (см. рис. 5):

Оформить заголовок в строке 1 «Численные методы решения нелинейного уравнения».

Оформить заголовок в строке 3 «Шаговый метод».

В ячейки B4 и C4 записать заголовки рядов - соответственно x и F(x).

В ячейки B5 и B6 ввести первые два значения аргумента - 0 и 0,3.

Выделить ячейки B5-B6 и протащить ряд данных до конечного значения (3,3), убедившись в правильном выстраивании арифметической прогрессии.

В ячейку C5 ввести формулу «=B5*B5-4*B5+3».

Скопировать формулу на остальные элементы ряда, используя прием протаскивания. В интервале C5:C16 получен ряд результатов вычисления функции F(x). Видно, что функция дважды меняет знак. Корни уравнения расположены на интервалах [0,9;1,2] и [3;3,3].

Для построения графика зависимости F(x) используем Мастер диаграмм (тип «Точечная», маркеры соединяются гладкими кривыми).

Оформить заголовок в строке 17 «Методы уточнения».

Ввести в ячейку E18 заголовок «Метод половинного деления» (выравнивание по центру).

Ввести в ячейку H18 текст «е=», а в ячейку I18 значение точности «0,001».

В области C19:I19 оформить заголовок таблицы (ряд C - левая граница отрезка «a», ряд D - середина отрезка «x», ряд E - правая граница отрезка «b», ряд F - значение функции на левой границе отрезка «F(a)», ряд G - значение функции на середине отрезка «F(x)», ряд H - произведение «F(a)*F(x)», ряд I - проверка достижения точности «êF(x)ê<е».

Ввести первоначальные значения концов отрезка: в ячейку C20 «0,9», в ячейку E20 «1,2».

Ввести в ячейку D20 формулу «=(C20+E20)/2».

Рис. 5

15. Ввести в ячейку F20 формулу «=C20*C20-4*C20+3».

16. Ввести в ячейку G20 формулу «=D20*D20-4*D20+3».

17. Ввести в ячейку H20 формулу «=F20*G20».

18. Ввести в ячейку I20 формулу «=ЕСЛИ(ABS(G20)<$I$18;²корень²,² ²)».

19. Ввести в ячейку C21 формулу «=ЕСЛИ(H20<0;C20;D20)».

20. Ввести в ячейку E21 формулу «=ЕСЛИ(H20<0;D20;E20)».

21. Скопировать ячейку D20 в ячейку D21, ячейки F20:I20 в ячейки F21:I21.

22. Выделить область C21:I21 и протащить ее по вертикали вплоть до появления в ряду I сообщения «корень» (ячейка I27).

23. Ввести в ячейку C28 заголовок «Метод Ньютона» (выравнивание по левому краю).

24. Ввести в ячейку C29 текст «е=», а в ячейку D29 значение точности «0,000001».

25. Убедиться, что при x=0,9 значение функции и ее второй производной имеют одинаковые знаки.

26. В области B30:E30 оформить заголовок таблицы (ряд B - значение аргумента «x», ряд C - значение функции «F(x)», ряд D - производная функции «F¢(x)», ряд E - проверка достижения точности «êF(x)ê<е».

27. В ячейку B31 ввести первоначальное значение аргумента «0,9».

28. Ввести в ячейку C31 формулу «=B31*B31-4*B31+3».

29. Ввести в ячейку D31 формулу «=2*B31-4».

30. Ввести в ячейку E31 формулу «=ЕСЛИ(ABS(C31)<$D$29;²корень²,² ²)».

31. Ввести в ячейку B32 формулу «=B31-C31/D31».

32. Скопировать ячейки C31:E31 в ячейки C32:E32.

33. Выделить область B32:E32 и протащить ее по вертикали вплоть до появления в ряду E сообщения «корень» (ячейка E34).

34. Ввести в ячейку G28 заголовок «Метод простой итерации» (выравнивание по левому краю).

35. Ввести в ячейку H29 текст «е=», а в ячейку I29 значение точности «0,001».

36. Выбрать функцию j(x), удовлетворяющую условию сходимости. В нашем случае такой функцией является функция S(x)=(x*x+3)/4.

37. В области G30:J30 оформить заголовок таблицы (ряд G - значение аргумента «x», ряд H - значение функции «F(x)», ряд I - значение вспомогательной функции «S(x)», ряд J - проверка достижения точности «êF(x)ê<е».

38. В ячейку G31 ввести первоначальное значение аргумента «0,9».

39. Ввести в ячейку H31 формулу «=G31*G31-4*G31+3».

40. Ввести в ячейку I31 формулу «=(G31*G31 +3)/4».

41. Ввести в ячейку J31 формулу «=ЕСЛИ(ABS(H31)<$I$29;²корень²,² ²)».

42. Ввести в ячейку G32 формулу «=I31».

43. Скопировать ячейки H31:J31 в ячейки H32:J32.

44. Выделить область G32:J32 и протащить ее по вертикали вплоть до появления в ряду J сообщения «корень» (ячейка J39).

45. Выделить ряд x, полученный с помощью метода половинного деления (ячейки D20:D27). Используя Мастер диаграмм, построить зависимость x от номера итерации (тип диаграммы «График»). Определить заголовок ряда «Метод половинного деления».

46. Добавить на график еще два ряда: «Метод Ньютона» - ячейки B31:B34 и «Метод простой итерации» - ячейки G31:G39. Для каждого ряда использовать сою маркировку. График показывает, что наибольшую скорость сходимости имеет метод Ньютона.

47. Ввести в ячейку C57 заголовок «Подбор параметра» (выравнивание по левому краю).

48. Ввести в ячейку C59 текст «x», а в ячейку D59 - «F(x)».

49. Занести в ячейку C60 начальное значение переменной (например, ноль).

50. Ввести в ячейку столбца D60 формулу «=C60*C60-4*C60+3».

51. Дать команду «Сервис» «Подбор параметра».

52. В поле «Установить в ячейке» указать ячейку D60, в которой занесена формула, в поле «Значение» задать 0 (ноль), в поле «Изменяя значение ячейки» указать ячейку C60, где занесено начальное значение переменной.

53. Щелкнуть <ОК> и посмотреть на результат подбора, отображенный в диалоговом окне «Результаты подбора параметра».

54. Нажать <ОК>, чтобы сохранить полученные значения.

55. Повторить расчет п.п. 49-54, задав другое начальное значение в ячейке C60. Совпали ли результаты вычисления?

56. Ввести в ячейку H57 заголовок «Поиск решения» (выравнивание по левому краю).

57. Ввести в ячейку H59 текст «x», а в ячейку I59 - «F(x)».

58. Занести в ячейку H60 начальное значение переменной (например, ноль).

59. Ввести в ячейку столбца I60 формулу «=H60*H60-4*H60+3».

60. Дать команду «Сервис» «Поиск решения».

61. В поле «Установить целевую ячейку» указать ячейку $I$60, в которой занесена формула, в поле «Равной» установить «значению 0», в поле «Изменяя ячейки» указать ячейку $H$60, в поле «Ограничения» установить два ограничения «$H$60>=0,9 и $H$60<=1,2».

62. Нажать кнопку «Выполнить». Появится сообщение, что решение найдено.

63. Нажать кнопку <ОК>, результат будет помещен в рабочий лист.

64. Повторить расчет п.п. 58-63, задав другое начальное значение в ячейке H60. Совпали ли результаты вычисления?

2.7. Задача максимизации прибыли предприятия

Одной из распространенных экономических задач является задача макси­мизации прибыли предприятия. Известно, что балансовая прибыль есть разница между выручкой и затратами на производство продукции P=N-Z. В общем слу­чае выручка от реализации продукции может быть представлена полиномом 2-й степени от количества продукции N=a₀Q+a₁Q². Нелиней­ность может быть связана с тем, что в условиях монополии цена единицы про­дукции k может уменьшаться с ростом количества выпущенной продукции Q:

k=a₀+a₁Q (a₀> 0, a ₁<0). В свою очередь, функция затрат может быть представ­лена полиномом 3-й степени Z=b₀+b₁Q+b₂Q² +b₃Q³. Кубическая нелинейность может объясняться тем, что при производстве малой партии товаров издержки быстро растут, затем с ростом Q темп роста издержек уменьшается, но по дос­тижении некоторого критического значения Q начинает работать «закон убы­вающей отдачи», в соответствии с которым издержки вновь начинают расти ус­коренными темпами. Прибыль максимальна, когда dP/dQ = 0. С помощью паке­та Excel решим данную задачу, полагая заданными коэффициенты: b₀ = 10, b₁=1, b₂ = -0.1, b₃ = 0.01, a₀= 5, a₁= -0.1.

Последовательность действий при реализации в пакете Excel (рис. 6):

1. Оформить заголовок в строке 1 «Максимизация прибыли».

2. В ячейки A3, ВЗ, СЗ, D3 и ЕЗ записать заголовки рядов - соответственно Q, N, Z, P, и dP/dQ.

3. В ячейки F3, F4, F5, F6, F9, F10 записатьназвания коэффициентов - со­ответственно b₀, b₁, b₂, b₃, a₀, a₁.

4. В ячейки G3, G4, G5, G6, G9, G10 записать значения коэффициентов -соответственно 10; 1; -0,1; 0,01; 5; -0,1.

5. В ячейку Н5 ввести текст «Издержки Z=b0+bl*Q+b2*Q^2+b3*Q^{^}3»

6. В ячейку Н6 ввести текст «Выручка N=a0*Q+a1*Q^2»

7. В ячейку Н7 ввести текст «Прибыль P=N-Z»

8. В ячейки А4 и А5 ввести первые два значения аргумента - 0 и 1.

9. Выделить ячейки А4-А5 и протащить ряд данных до конечного значения

(21), убедившись в правильном выстраивании арифметической прогрессии. 10. В ячейку В4 ввести формулу «=A4*$G$9+A4*A4*$G$10».

11. Скопировать формулу на остальные элементы ряда, используя прием протаскивания. В интервале В4:В25 получен ряд результатов вычисления вы­ручки N(Q).

12. В ячейку С4 ввести формулу «=$G$3+A4*$G$4+A4*A4*$G$5+A4*A4*A4* $G$6».

13. Скопировать формулу на остальные элементы ряда, используя прием протаскивания. В интервале С4:С25 получен ряд результатов вычисления из­держек Z(Q).

14.В ячейку D4 ввести формулу «=B4-C4».

15. Скопировать формулу на остальные элементы ряда, используя прием протаскивания. В интервале D4:D25 получен ряд результатов вычисления при­были P(Q).

16. В ячейку Е4 ввести формулу «=($G$9-$G$4)+2*($G$10-$G$5)*A4-3*$G$6* А4*А4».

17. Скопировать формулу на остальные элементы ряда, используя прием протаскивания. В интервале Е4:Е25 получен ряд результатов вычисления dP/dQ для различных значений Q.

18. Построить на одной диаграмме графики зависимостей N(Q), Z(Q) и P(Q), ис­пользуя соответствующие ряды данных.

19. Построить на отдельной диаграмме зависимость dP/dQ от Q. Точка пересе­чения графика с осью абсцисс дает значение Q, соответствующее макси­мальной прибыли (шаговый метод).

 

Рис.6

 

Постановка задачи

Дана система n алгебраических уравнений с n неизвестными:

(1)

Эту систему можно записать в матричном виде: ,

; ; .

где A - квадратная матрица коэффициентов, X - вектор-столбец неизвестных, B - вектор-столбец свободных членов.

Численные методы решения систем линейных уравнений делятся на прямые и итерационные. Первые используют конечные соотношения для вычисления неизвестных. Пример - метод Гаусса. Вторые основаны на последовательных приближениях. Примеры - метод простой итерации и метод Зейделя.

 

Метод Гаусса

Метод основан на приведении матрицы системы к треугольному виду. Это достигается последовательным исключением неизвестных из уравнений системы. Сначала с помощью первого уравнения исключается x₁ из всех последующих уравнений. Затем с помощью второго уравнения исключается x₂ из последующих и т.д. Этот процесс называется прямым ходом метода Гаусса и продолжается до тех пор, пока в левой части последнего n -го уравнения не останется лишь один член с неизвестным x _n. В результате прямого хода система принимает вид:

(2)

Обратный ход метода Гаусса состоит в последовательном вычислении искомых неизвестных, начиная с x_n и кончая x₁.

 

Реализация в пакете Excel

В качестве примера рассмотрим систему уравнений:

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

Оформить заголовок в строке 1 «Численные методы решения систем линейных уравнений».

В области D3:H6 ввести исходные данные, как показано на рисунке.

Ввести в ячейку F8 текст заголовка «Метод Гаусса» (выравнивание по центру).

Скопировать исходные данные E4:H6 в область B10:E12. Это - исходные данные для прямого хода метода Гаусса. Обозначим соответствующие строки A1,A2 и A3.

Подготовить место для первого прохода, обозначив в области G10:G12 названия строк B1,B2 и B3.

Ввести в ячейку H10 формулу «=B10/$B$10». Скопировать эту формулу на ячейки I10:K10. Это - нормировка на коэффициент a₁₁.

Ввести в ячейку H11 формулу «=B11-H10*$B$11». Скопировать эту формулу на ячейки I11:K11.

Ввести в ячейку H12 формулу «=B12-H10*$B$12». Скопировать эту формулу на ячейки I12:K12.

Подготовить место для второго прохода, обозначив в области A14:A16 названия строк C1, C2 и C3.

Ввести в ячейку B14 формулу «=H10». Скопировать эту формулу на ячейки C14:E14.

Ввести в ячейку B15 формулу «=H11/$I$11». Скопировать эту формулу на ячейки C15:E15.

Рис. 7

12. Ввести в ячейку В16 формулу «=Н12-В15*$I$12». Скопировать эту форму­лу на ячейки С16:Е16.

13. Подготовить место для третьего прохода, обозначив в области G14:G16 на­звания строк D1, D2 и D3.

14. Ввести в ячейку H14 формулу «=В14». Скопировать эту формулу на ячейки I14:К14.

15. Ввести в ячейку H15 формулу «=В15». Скопировать эту формулу на ячейки I15:К15.

16. Ввести в ячейку Н16 формулу «=B16/$D$16». Скопировать эту формулу на ячейки I16:К16.

17. Подготовить место для обратного хода метода Гаусса, введя в ячейки В18, E18 и H18 соответствующие тексты «х3=», «х2=» и «х1=».

18. Ввести в ячейку С18 формулу «=К16». Получим значение переменной х 3.

19. Ввести в ячейку F18 формулу «=К15-J15*К16». Получим значение перемен­ной х 2.

20.Ввести в ячейку I18 формулу «=K10-I10*F18-J10*C18». Получим значение переменной х 1.

21. Ввести в ячейку F21 текст заголовка «Метод простой итерации» (выравни­вание по центру).

22. Ввести в ячейку J21 текст «е=» (выравнивание по правому краю).

23. Ввести в ячейку К21 значение точности е (0,0001).

24. Обозначить в области А23:А25 названия переменных.

25. В области В23:В25 задать начальные значения переменных (нули).

26. Ввести в ячейку С23 формулу «=($H$4-$F$4*B24-$G$4*B25)/$E$4». Полу­чим значение переменной х 1 на первой итерации.

27. Ввести в ячейку С24 формулу «=($H$5-$E$5*B23-$G$5*B25)/$F$5». Полу­чим значение переменной х 2 на первой итерации.

28. Ввести в ячейку С25 формулу «=($H$6-$E$6*B23-$F$6*B24)/$G$6». Полу­чим значение переменной х 3 на первой итерации.

29. Ввести в ячейку С26 формулу «=ЕСЛИ(АВS(С23-В23)>$К$21;" "; ЕСЛИ(АВS(С24-В24)>$К$21;" ";ЕСЛИ(АВS(С25-В25)>$К$21;" "; '"корни")))». Это - проверка на достижение заданной точности (при этом печата­ется сообщение «корни»).

30. Выделить диапазон С23:С26 и скопировать его до столбца К, используя при­ем протаскивания. При появлении в строке 26 сообщения «корни» соответст­вующий столбец будет содержать приближенные значения переменных х 1, x 2, x 3, которые являются решением системы уравнений с заданной точно­стью.

31. В области А27:К42 построить диаграмму, показывающую процесс прибли­жения значений переменных х 1, х 2, x 3 к решению системы. Диаграмма стро­ится в режиме «График», где по оси абсцисс откладывается номер итерации.

32. Ввести в ячейку F43 текст заголовка «Метод Зейделя» (выравнивание по центру).

33. Ввести в ячейку J43 текст «е=» (выравнивание по правому краю).

34. Ввести в ячейку К43 значение точности е(0,0001).

35. Обозначить в области А45:А47 названия переменных.

36. В области В45:В47 задать начальные значения переменных (нули).

37.Ввести в ячейку С45 формулу «=($H$4-$F$4*B46-$G$4*B47)/$E$4». Полу­чим значение переменной х 1 на первой итерации.

38.Ввести в ячейку С46 формулу «=($H$5-$E$5*C45-$G$5*B47)/$F$5». Полу­чим значение переменной х 2 на первой итерации.

39. Ввести в ячейку С47 формулу «=($H$6-$E$6*C45-$F$6*C46)/$G$6». Полу­чим значение переменной x 3,на первой итерации.

40. Ввести в ячейку С48 формулу «=ЕСЛИ(АВ5(С45-В45)>$К$43;" "; ЕСЛИ(АВS(С46-В46)>$К$43;" ";ЕСЛИ{АВS(С47-В47)>$К$43;" ";"кор­ни")))».

41. Выделить диапазон С45:С48 и скопировать его до столбца К, используя при­ем протаскивания. При появлении в строке 26 сообщения «корни» соответст­вующий столбец будет содержать приближенные значения переменных х 1, х 2, x 3, которые являются решением системы уравнений с заданной точно­стью. Видно, что метод Зейделя сходится быстрее, чем метод простой итера­ции, то есть заданная точность здесь достигается за меньшее число итераций.

42. В области А49:К62 построить диаграмму, показывающую процесс прибли­жения значений переменных х1, х2, x3 к решению системы. Диаграмма стро­ится в режиме «График», где по оси абсцисс откладывается номер итерации.

 

3.5. Решение задачи межотраслевого баланса (модель Леонтьева)

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

Введем следующие обозначения у _i - конечный спрос на продукцию i -й от­расли, х _i - выпуск продукции i -й отрасли. c _ij - доля продукции отрасли i, по­требленной в процессе производства продукции отрасли j. В этом случае в со­ответствии с моделью Леонтьева имеем следующую систему линейных уравне­ний:

 

Задача состоит в нахождении неизвестных x ₁, x ₂, x ₃. Остальные величины считаются заданными. Заметим, что все коэффициенты c _ijизменяются в преде­лах от 0 до 0,3. Это обеспечивает сходимость при использовании итерационных методов.

Последовательность действий при реализации модели в пакете Excel с использованием метода простой итерации (рис. 8).

1. Ввести в ячейку H1 текст заголовка «Модель Леонтьева» (выравнивание по центру).

2. Ввести в ячейку H2 текст «Данные» (выравнивание по центру).

3. В области F4:J7 ввести исходные данные как показано на рисунке.

4. Обозначить в области А9:А12 номер итерации k и названия переменных х 1, х 2, x 3.

5. В области В9:В12 задать начальные значения переменных (нули).

6. В ячейку С9 ввести 1, выделить ячейки В9 и С9 и, используя прием протас­кивания, заполнить ряд до столбца О.

7. Ввести в ячейку С10 формулу «=($J$5+$H$5*B11+$I$5*B12)/(1-$G$5)». Получим значение переменной х 1 на первой итерации.

8. Ввести в ячейку С11 формулу «=($J$6+$G$6*B10+$I$6*B12)/(1-$H$6)». Получим значение переменной х 2 на первой итерации.

9. Ввести в ячейку С12 формулу «=($J$7+$G$7*B10+$H$7*B11)/(1-$I$7)». Получим значение переменной х 3 на первой итерации.

10. Выделить диапазон С10:С12 и скопировать его до столбца О, используя при­ем протаскивания

11. В области A14:O33 построить диаграмму, показывающую процесс прибли­жения значений переменных х 1, х 2, х 3 к решению системы. Диаграмма строится в режиме «Точечная», где по оси абсцисс откладывается номер итерации.

 

Рис. 8

Постановка задачи

В дискретные моменты времени х₁, х₂, …, х_n были проведены измерения некоторой физической величины Y. Результаты эксперимента представлены в таблице.

Таблица

Х
Y

 

Требуется определить значения физической величины на всем временном интервале x₁<=x<=x_n.

Задача определения значений физической величины на всем временном интервале сводится к задаче о приближении функции Y(x). Заданную таблицей функцию Y(x) заменим на функцию f(x; a₁, …,a_n) таким образом, чтобы отклонение функции Y(x) от приближающей функции f(x; a₁, …,a_n) на указанном множестве х₁, х₂, …, х_n было наименьшим. При этом функция f(x; a₁, …,a_n) в общем случае называется аппроксимирующей функцией. Если параметры a₁, …,a_n определяются из условия совпадения функции Y(x) и приближающей функции в точках х₁, х₂, …, х_n, т.е. из условия равенства f(x_i; a₁, …,a_n)=Y(x_i), то такую функцию f(x_i; a₁, …,a_n) называют интерполирующей.

Рассмотрим случаи линейной, квадратичной интерполяции и общий случай интерполирования полином Q_m(x)=a₀+a₁x+…+a_mx^m степени m, а также выполним аппроксимацию заданной экспериментальной зависимости полиномом 1-ой степени.

Для решения задачи будем использовать возможности, предоставляемые электронными таблицами Microsoft Excel. Перенесем исходную таблицу экспериментальных данных в рабочий лист книги Microsoft Excel для определенности располагая в ячейках A3:F4 (см. рис. 11).

 

Линейная интерполяция.

В случае линейного интерполирования искомая функция f(x) представляет собой ломаную линию. Уравнения каждого отрезка ломаной определяются из условия прохождения прямой через данную соседнюю пару точек. Например, неизвестные коэффициенты а₀, а₁ уравнения прямой y=a₀+a_1*x, проходящей через первую и вторую точки, могут быть найдены из системы линейных уравнений:

Первое уравнение системы - это условие прохождения прямой через точку с координатами (x₁,y₁), второе уравнение - условие прохождения прямой через точку с координатами (x₂,y₂). Таким образом, задача нахождения искомой функции, описывающей заданную табличную зависимость в случае линейного интерполирования сводиться к нахождению уравнений прямых, соединяющих точки 1 и 2, 2 и 3, 3 и 4, 4 и 5 соответственно.

Выполним линейную интерполяцию средствами электронных таблиц Excel. Для этого вначале построим график заданной экспериментальной зависимости. Для этого будем использовать Мастер диаграмм (см. кнопка Мастера диаграмм на панели инструментов Стандартная или пункт меню Вставка - Диаграмма). Экспериментальные данные будем отображать точками, для этого на первом шаге Мастера диаграмм необходимо выбрать точечный тип диаграммы и нажать на кнопку <Далее>. На втором шаге Мастера диаграмм необходимо перейти на вкладку Ряд и добавить ряды данных, по которым будет строиться диаграмма. Для этого необходимо нажать на кнопку <Добавить> и затем ввести значения Х и Y, соответствующие данному ряду значений. В случае линейной интерполяции мы имеем четыре ряда данных: Ряд1 формируется из координат точек 1 и 2, Ряд2 - из координат точек 2 и 3, Ряд3 - из координат точек 3 и 4, Ряд4 - из координат точек 4 и 5. Рассмотрим более подробно формирование Ряда 1. Диапазон значений Х и Y (ячейки, содержащие координаты х и у точек 1 и 2) может быть указан с помощью манипулятора мышь путем простого выделения соответствующего диапазона ячеек (если окно Мастера диаграмм загораживает необходимый диапазон ячеек, его можно отбуксировать, уцепившись "мышью" за заголовок окна). Результат представлен на рис.9. Проверьте, Ряд 1 имеет Значения Х - =Лист1!$B$3:$C$3, Значения Y - =Лист1!$B$4:$C$4.

Для того, чтобы начать формирование Ряда 2 необходимо нажать на кнопку Добавить. Значения Х и Y Ряда 2 - это диапазон ячеек с координатами x и y точек 2 и 3 (Значения Х – =Лист1!$C$3:$D$3, Значения Y - =Лист1!$C$4:$D$4). Аналогично формируются Ряды 3 и 4.

Сформировав ряды данных необходимо нажать на кнопку <Далее>. В третьем окне Мастера диаграмм необходимо указать название диаграммы и осей, во вкладке Легенда снять флажок Добавить легенду и нажать на кнопку <Готово>. На рабочем листе должна появиться диаграмма, отображающая заданную табличную зависимость.

Далее для выполнения линейного интерполирования по заданным точкам необходимо выполнить Меню Диаграмма - Добавить линию тренда. Пункт Диаграмма присутствует в меню Microsoft Exc