Коврижных А.Ю., Конончук Е.А. и др. Прикладное программное обеспечение для решения экономических задач
Подождите немного. Документ загружается.
11
на графике). Щелчком мыши устанавливаем флажок в поле Добавить легенду.
После чего нажать кнопку Далее.
Рис.1.5
5. Выбор места размещения. В четвертом окне «Мастер диаграмм
(шаг 4 из 4): размещение диаграммы» необходимо указать место
размещения диаграммы. Для этого переключатель Поместить диаграмму на
листе установить в нужное положение (на отдельном или текущем листе).
Рис.1.6
6. Завершение. Если диаграмма в демонстрационном поле имеет
желаемый вид, необходимо нажать кнопку Готово. В противном случае
следует нажать кнопку Назад и изменить установки.
В нашем примере нажимаем кнопку Готово и на текущем листе должна
появиться следующая диаграмма (рис 1.7).
12
Кривая Лоренца
0
0,1
0,2
0,3
0,4
0,5
0,6
0,7
0,8
0,9
1
0 0,2 0,4 0,6 0,8 1
Доля населения
Доля доходов населения
Рис.1.7
Таким же способом можно построить графики нескольких функций в
одной координатной сетке.
Пример 1.2.Рассмотрим построение графика двух функций на примере:
у1 =x
2
- 2x + 1 и у2 = 2sinx + 1.
Пусть необходимо построить графики для значений x из диапазона [-2,
3], если аргумент изменяется с шагом h = 0,25.
Решение. Пусть в рассматриваемом примере первый столбец будет значениями
х, второй и третий соответствующими значениями у1 и у2. Для этого в ячейку
А1 вводим текст: Значение аргумента, в ячейку B1 — текст: Значение
функции1, в ячейку C1 — текст: Значение функции2.
Начнем с введения значений аргумента. В ячейку А2 вводится первое
значение аргумента — левая граница диапазона (-2). В ячейку A3 вводится
второе значение аргумента — левая граница диапазона плюс шаг построения
(0,25). Затем, выделив блок ячеек А2:АЗ, с помощью автозаполнения,
получаем все значения аргумента.
Далее вводим значения функции1. В ячейку В2 вводим ее формулу:
=А2^
2
– 2*А2 + 1. Затем с помощью автозаполнения копируем эту формулу в
диапазон В2:В22. Вводим значения функции2: в ячейку С2 вводим ее
13
формулу: =2*sin(А2) + 1. Затем с помощью автозаполнения копируем эту
формулу в диапазон C2:C22.
В результате должна быть получена следующая таблица (рис. 1.8).
Рис.1.8
Выполним последовательно все перечисленные примере 1.1. этапы.
При отдельном размещении мы получим новый лист рабочей книги, который
по умолчанию называется «Диаграмма1». На нем представлены графики двух
заданных функций, различающиеся по цвету (рис.1.9).
Рис.1.9
14
Графическое решение систем уравнений
Системы уравнений с двумя неизвестными могут быть приближенно
решены графически. Их решением являются координаты точки пересечения
линий, соответствующих уравнениям системы. При этом точность решения
будет определяться величиной шага дискретизации (чем шаг меньше, тем
точность выше). Рассмотрим примеры графического решения системы двух
уравнений.
Пример 1.3. Пусть необходимо найти решение системы
⎪
⎩
⎪
⎨
⎧
=
=
2
sin
cos2
x
y
xy
Для 0 ≤ х ≤ 3, изменяющимся с шагом h = 0,2.
Решение. Для построения графиков функций правых частей первого и второго
уравнений воспользуемся приемом из примера 1.2. Получим следующую
картинку (рис.1.10).
Рис.1.10
Если подвести указатель мыши к точке пересечения графиков, всплывет
координата этой точки – значение x решения системы. Для уточнения решения
можно изменить параметры сетки, с помощью диалогового окна (рис.1.11).
Оно вызывается из контекстного меню, возникающего при щелчке правой
кнопкой по линии сетки.
15
Рис.1.11
Подобным образом можно изменить другие характеристики графика.
Пример 1.4. Зависимость спроса (у) на некоторый товар от его цены (х)
выражается уравнением
2
2
+=
x
y
,
а зависимость предложения от цены товара — уравнением
z = x
2
+ 1.
Необходимо найти точку равновесия в диапазоне x
∈
[0.2; 3] с шагом h = 0.2.
Решение. Точка равновесия — это точка пересечения кривых спроса и
предложения. Для построения этих кривых, прежде всего, необходимо ввести
данные в рабочую таблицу. Вводим в ячейку А1 слово Цена. Затем в ячейку
А2 — первое значение аргумента — 0.2. Далее будем вводить значения
аргумента с шагом 0.2.
Далее требуется ввести значения функции (спроса). В ячейку В1 заносим слово
Спрос и устанавливаем табличный курсор в ячейку В2. Здесь должно
оказаться значение спроса соответствующее значению цены в ячейке А2. Для
получения значения спроса вводим формулу, которая выглядит следующим
образом:
16
= 2/А2 + 2 Нажимаем клавишу Enter. В ячейке В2 появляется 12. Теперь
необходимо скопировать формулу из ячейки В2 в ячейки ВЗ:В1.
Осуществляем это автозаполнением.
Аналогично получаем значения предложения. В ячейку С1 вводим имя
функции — Предложение. Устанавливаем табличный курсор в ячейку С2. Для
получения значения предложения вводим формулу, которая выглядит
следующим образом: =А2^2 + 1. Нажимаем клавишу Enter. В ячейке С2
появляется 1,04. Теперь необходимо скопировать формулу из ячейки С2 в
ячейки С3:С16. Осуществляем это автозаполнением.
Далее по введенным в рабочую таблицу данным необходимо построить
диаграмму.
Делаем это подобно предыдущему примеру.
В результате получено изображение кривых спроса и предложения (рис.1.12).
Рис.1.12
Как видно из диаграммы, система имеет точку равновесия (это есть точка
пересечения), и она единственная (в заданном диапазоне имеется только одна
точка пересечения). Таким образом, решением системы в заданном диапазоне
являются координаты точки пересечения кривых. Для их нахождения
необходимо навести указатель мыши на точку пересечения и щелкнуть левой
кнопкой. Появляется надпись с указанием искомых координат. Итак,
17
приближенное решение — точка равновесия имеет координаты : x = 1.6;у =
3.25.
Построение плоскости.
MS Excel позволяет выполнять построение и пространственных объектов.
Рассмотрим эту возможность на примере построения плоскости
Уравнение вида:
Ах + Ву + Cz + D = 0 (1.1)
называется общим уравнением плоскости.
Мастер диаграмм может быть также использован и для построения
плоскостей. Для этого необходимо ввести точки плоскости в рабочую таблицу,
вызвать Мастер диаграмм, задать тип диаграммы, диапазоны данных и
подписей оси.
Пример 1.5. Рассмотрим построение плоскости в Excel, если она задана
уравнением вида (1.1):
2·х – 4·у – 2·z + 2 = 0.
Пусть необходимо построить часть плоскости, лежащей в первой четверти х
∈
[0; 6] с шагом h1 = 0.5, .у ∈ [0; 6] с шагом h2 = 1).
Решение. Вначале необходимо разрешить уравнение относительно переменной
z. В нашем примере z =·х – 2·у + 1.
1. Введем значения переменной х в столбец А (начиная с ячейки А2).
2. Значения переменной у вводим в строку 1 (начиная с ячейки В1).
3. Далее вводим значения переменной z. В ячейку В2 вводим ее уравнение
= $A2 + 2*В$1 + 1. Обращаем внимание, что символы $ предназначены для
фиксации адреса столбца А — переменной х и строки 1 — переменной у.
Затем автозаполненнем копируем эту формулу вначале в диапазон В2:Н2.
после чего — в диапазон ВЗ:Н14 (протягиванием вниз)
4. В результате должна быть получена следующая таблица (рис.1.13).
18
Рис.1.13
5. Выделяем мышью диапазон А1:Н14. Обращаемся к Мастеру диаграмм.
В появившемся диалоговом окне Мастер диаграмм (шаг 1 из 4): тип
диаграммы указываем тип диаграммы – Поверхность, и вид —
Проволочная (прозрачная) поверхность (правую верхнюю диаграмму в
правом окне). После чего нажимаем кнопку Далее.
Рис.1.14
6. В этом примере переключатель Ряды установим в положение «в
столбцах». Затем указываем название диаграммы и осей. Полученная
диаграмма имеет вид (рис.1.15)
19
0
1
2
3
4
5
6
0
3
6
0
2
4
6
8
10
12
14
16
18
20
значения z
значения y
значения x
Плоскость
Рис.1.15
2. Решение уравнений с помощью процедуры «подбор
параметра».
В MS Excel для решения уравнений вида f(x) = 0 используется удобный и
простой для понимания инструмент Подбор параметра.
Процесс решения с помощью процедуры Подбор параметра распадается на
два этапа:
1. Задание на рабочем листе ячейки, содержащей значение независимой
переменной решаемого уравнения (так называемой влияющей ячейки), и
ячейки содержащей формулу уравнения (зависящей или целевой
ячейки).
2. Ввод адресов влияющей и целевой ячеек в диалоговом окне Подбор
параметра и получение ответа (или сообщения о его отсутствии или
невозможности нахождения, поскольку уравнение может не иметь
решений или алгоритм решения (оптимизации) может оказаться
20
расходящимся в конкретных условиях).
Рассмотрим этот процесс на конкретном примере.
Пример 2.1. Найти решение уравнения ln(x)=0.
Решение:
Первый этап
1. Открываем новый рабочий лист (команда Вставка → Лист).
2. Заносим в ячейку А1 ориентировочное значение корня, например, 3.
3. Заносим в ячейку В1 левую часть уравнения, используя в качестве
независимой переменной ссылку на ячейку Al. Для этого нажимаем на
панели инструментов Стандартная кнопку Вставка функции; в
появившемся диалоговом окне Мастер функций в рабочем поле
Категория выбираем Математические, а в рабочем поле Функция имя
функции LN. После чего щелкаем на кнопке ОК; в рабочее поле Число
щелчком мыши на ячейке А1 вводим ее адрес. Затем, нажимаем на
кнопку ОК.
В ячейке В1 появляется число 1,098612(рис.2.1).
Рис.2.1
Второй этап
1. Вызываем процедуру Подбор параметра (команда Сервис → Подбор
параметра).
2. В поле Установить в ячейке мышью указываем В1, в поле Значение
задаем 0 (правая часть уравнения), в поле Изменяя значение ячейки
мышью указываем на А1 (рис.2.2).