Министерство образования и науки РФ

Федеральное государственное бюджетное образовательное учреждение высшего профессионального образования

Тульский государственный университет

Кафедра «Финансы и менеджмент»

Региональный центр повышения квалификации и переподготовки кадров

«Автоматизированные системы обработки

экономической информации»

Курсовая работа на тему:

«Excel: решение уравнений, систем уравнений и задач оптимизации»

Вариант 14

ВЫПОЛНИЛ: студентка группы 760722в Скачкова А.М.

ПРОВЕРИЛ: к.э.н., доцент кафедры ФиМ Огнянович А.В.

Тула 2014

Оглавление

Введение 3

1Решение уравнения 4

1.1 Решение уравнения с помощью инструмента «Подбор параметра» 4

1.2 Решение уравнения с помощью инструмента «Поиск решения» 7

2Решение системы уравнений 9

3Решение оптимизационной задачи. 11

Заключение 13

Список использованных источников 14

Введение

Целью курсовой работы является практическое освоение методов решения уравнений, систем уравнений и оптимизационных задач с помощью табличного процессора Excel. Исходя из указанной цели, можно выделить частные задачи, поставленные в курсовой работе:

1. Решить уравнение с помощью инструментов Excel «Подбор параметра» и «Поиск решения»;

2. Решить систему двух линейных уравнений с помощью инструмента «Поиск решения»;

3. Решить оптимизационную задачу с помощью инструмента «Поиск решения».

Объектом исследования в курсовой работе является системы обработки информации, в частности табличный процессор Microsoft Excel.

Предметом исследования в курсовой работе являются функции и инструменты Excel для решения математических и экономических задач.

Методологической основой исследования в курсовой работе явились учебные пособия по информатике, АСОЭИ, МИАвМ.

Курсовая работа состоит из введения, основной части, заключения и списка использованной литературы.

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

Основная часть имеет практический характер, состоит из трех пунктов, соответствующих задачам курсовой работы. Каждый пункт содержит решение задачи с подробным теоретическим обоснованием и скриншотами соответствующих действий в Microsoft Excel.

В заключении даны обобщение результатов проведенной работы.

1. Решение уравнения

1.1 Решение уравнения с помощью инструмента «Подбор параметра»

Функция Подбор параметра позволяет находить одно значение аргумента, соответствующее заданному значению функции (например, 0).

Найдем решения уравнения sin(x)/x=0.

В ячейку А3 вводим произвольное значение, например 1, в ячейку В3 функцию (рисунок 1).

Рисунок 1

Выполнив команду Подбор параметра, необходимо заполнить поля диалогового окна следующим образом (рисунок 2):

Рисунок 2

В результате найденным корнем уравнения будет значение 6,5 в ячейке А3 (рисунок 3).

Рисунок 3

Однако, это не единственный корень. В этом можно убедиться построив график функции

Для построения графика следует:

1. в ячейки А4-А24 ввести значения от –10 до 10 с шагом 1; в ячейку В4 – ввести формулу (SIN(A4))/A4 и путём перетаскивания маркера заполнения заполнить этой формулой ячейки В5-В24;

2. выделив диапазон В4-В24, открыть вкладку Вставка, в группе Диаграммы выполнить команду График с маркерами;

3. в контекстной вкладке Работа с диаграммами перейти на вкладку Конструктор, в группе Данные выполнить команду Выбрать данные;

4. нажать на кнопку Изменить в области Подписи горизонтальной оси (категории) и задать диапазон А4-А24;

5. последовательными нажатиями кнопки ОК и ОК закрыть окно Выбор источника данных.

В результате будет построен график функции (рисунок 4)

Рисунок 4

Из графика видно, что уравнение имеет несколько корней, к тому же эти корни противоположны. Одни корень 3,14129 нам уже известен. Значит второй корень противоположен ему, т.е. -3,14129.

Найдем третий корень, используя вкладку «подбор параметра». Для этого изменим значение, например, в ячейке А4 на 6 (на графике видно, что это более близкое значение к ожидаемому корню). Выделим ячейку В4 и выполним команду Подбор параметра (рисунок 5), группы Работа с данными, вкладки Данные. Заполним поля запроса:

Рисунок 5

После щелчка по кнопке ОК (рисунок 6) в ячейке А4 получим значение третьего корня 6,27816 (четвертый корень соответственно -6,27816):

Рисунок 6

Аналогично найдем еще два корня: 9,42495 и -9,42495.

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

1.2 Решение уравнения с помощью инструмента «Поиск решения»

В ячейку А3 вводим произвольное значение, например 10, в ячейку В3 функцию (рисунок 7).

Рисунок 7

Выполнив команду Поиск решений, необходимо заполнить поля диалогового окна следующим образом (рисунок 8):

Рисунок 8

Нажимаем кнопку Выполнить. Для сохранения полученного решения необходимо использовать переключатель в открывшемся диалоговом окне Результаты поиска решения. После чего рабочий лист примет вид (рисунок 9):

Рисунок 9

Аналогично найдем второй корень, введя в ячейку А4 значение 5, и перетаскивая маркером, заполнить ячейку В4. Поля диалогового окна выглядят аналогично (рисунок 10):

Рисунок 10

После сохраниния результатов получим еще одно решение уравнения( рисунок 11):

Рисунок 11

Аналогичным образом можно найти еще бесконечно много решений уравнения, которые в общем виде можно записать как , где .

2. Решение системы уравнений

Используя инструмент Поиск решения, решим систему уравнений:

В ячейки А3:С5 вводим коэффициенты системы, в ячейки Д3:В5– свободные члены, ячейки A7:С7 – ячейки аргументов, их можно оставить пустыми (по умолчанию 0) или заполнить произвольными значениями (в нашем случае 1). В ячейки E3:E5 вводим формулы вычисления свободных членов (левая часть уравнения). (рисунок 12).

Рисунок 12

Выполнив команду Поиск решения, необходимо заполнить поля диалогового окна следующим образом (рисунок 13):

Рисунок 13

После нажатия кнопки Выполнить, в ячейках А7:С7 будут выведены полученные корни: (рисунок 14).

Рисунок 14

Решение системы уравнений: .

3. Решение оптимизационной задачи.

В ячейки А4:F7 вводим коэффициенты при аргументах, в ячейках G4:G7 – формулы вычисления ограничений, в ячейки H4:H7 – значения ограничений. Ячейки A9:F9 – ячейки аргументов, их можно оставить пустыми (по умолчанию 0) или заполнить произвольными значениями. В ячейку С2 вводим формулу расчета целевой функции (рисунок 15).

Рисунок 15

Через меню Данные / Поиск решения открываем окно поиска решения (рисунок 16). Во вкладке Параметры установим метод решения: метод сопряженных градиентов.

В поле ввода Установить целевую ячейку вводим ссылку на ячейку С2. В поле ввода Изменяя ячейки укажем ссылки на ячейки А9:F9. В поле ввода Ограничения введем ограничения, соответствующие ячейкам А9:F9 и ячейкам G4:G7.

Рисунок 16

После нажатия кнопки Выполнить, в ячейках А9:F9 будут выведены искомые значения переменных: (рисунок 17).

Рисунок 17

А в ячейке С2 появится максимальное значение целевой функции .

Заключение

Задания курсовой работы решались с помощью табличного процессора Microsoft Excel. Microsoft Excel – средство для работы с электронными таблицами, намного превышающее по своим возможностям существующие редакторы таблиц. Табличные процессоры - удобный инструмент для экономистов, бухгалтеров, научных работников - всех тех, кому приходится работать с большими массивами числовой информации. Эти программы позволяют создавать таблицы, которые являются динамическими, т. е. содержат так называемые вычисляемые поля, значения которых автоматически пересчитываются по заданным формулам при изменении значений исходных данных, содержащихся в других полях.

Табличный процессор Excel очень мощный инструмент для решения задач имеющих дело с массивами разнообразных данных, поэтому область его применения очень обширна.

Список использованных источников

1. Бельская Е.В., Огнянович А.В. Методические указания по оформлению контрольно-курсовых, курсовых, выпускных квалификационных и дипломных работ. Тула: ТулГУ, 2011. 47с.

2. Борисов А.Н. Информационные технологии в менеджменте: учебное пособие. Тула: ТулГУ, 2012. 14 с.

3. Борисов А.Н., Федоров А.В. Методы информационного анализа в менеджменте (часть 1): учеб. пособие. Тула: ТулГУ, 2013. 14 с.

4. Лебедев А.Н. Понятный самоучитель Excel 2013. СПб: Питер, 2014. 128с.