Использование табличного процессора 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.1. Контрольные вопросы

 

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

 

1. Запуск пакета Excel. Виды меню. Панели инструментов. Технология

работы с папками, файлами, книгами, листами.. Справочник

· Запуск программы Excel и открытие её окна.

· Создание файла новой рабочей книги в папке пользователя.

· Создание папки пользователя на данном диске и в данной папке.

· Переименование папки.

· Перемещение окна программы Excel.

· Закрытие окна активной программы Excel.

· Сворачивание и разворачивание окна программы Excel.

· Сворачивание и разворачивание окна рабочей книги.

· Просмотр списка открытых окон книг Excel.

· Одновременная работа с несколькими книгами.

· Вставка и удаление листов рабочей книги.

· Копирование и перемещение листов.

· Переименование листов.

· Главное меню программы Excel.

· Основные кнопки панели инструментов Форматирование.

· Основные кнопки панели инструментов Стандартная.

· Изменение состава панели инструментов окна программы Excel.

· Первоначальное сохранение файла.

· Переименование файла.

· Копирование и перемещение файла.

· Удаление файла или папки из данной папки.

· Состав справочной системы.

 

2. Работа с данными в пакете Excel. Редактирование таблицы

· Ввод, чтение и сохранение данных.

· Основные типы данных.

· Абсолютная и относительная адресация.

· Копирование данных.

· Работа с рядами.

· Форматы данных. Числовое форматирование.

· Создание пользовательских форматов.

· Изменение параметров выделенных объектов (высота строк, ширина столбцов, добавление и удаление строк и столбцов).

· Форматирование таблицы.

· Изменение шрифта.

· Выравнивание, рамки, фон.

· Копирование и перемещение данных.

· Работа с блоками ячеек. Копирование и перемещение блоков.

· Работа с буфером обмена.

· Использование команды «Специальная вставка».

 

3. Функции и формулы в пакете Excel

· Понятие формул и функций.

· Основные элементы строки формул.

· Классы табличных функций: математические, статистические, логические, финансовые, даты и времени и др.

· Ввод функций и формул.

· Мастер функций.

· Редактирование формул.

· Копирование и перемещение формул.

 

4. Графические возможности пакета Excel

· Мастер диаграмм. Типы диаграмм.

· Ряды данных для построения диаграмм.

· График, точечная, гистограммы.

· Построение диаграмм с помощью мастера диаграмм.

· Редактирование диаграмм.

· Легенда, номер ряда, заголовки осей.

· Форматирование диаграмм.

· Форматирование легенды, масштабирование осей, изменение шкалы, метки.

· Форматирование текста заголовков осей и названия диаграммы.

· Применение тренда.

 

5. Параметры страницы в пакете 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² - 4x + 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, x2, x3, которые являются решением системы уравнений с заданной точно­стью.

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

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». Полу­чим значение переменной x3, на первой итерации.

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

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

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, x3.

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) называют интерполирующей.

123456Следующая ⇒

Поделиться: