Рис.4.40. Вычисление функции Z
Для построения диаграммы на панели инструментов Стандартная необходимо нажать кнопку Мастер диаграмм. В появившемся диалоговом окне Мастер диаграмм (шаг 1 из 4): тип диаграммы указываем тип диаграммы - Поверхность, и вид - Проволочная (прозрачная) поверхность, п осле чего нажимаем кнопку Далее в диалоговом окне.
В появившемся диалоговом окне Мастер диаграмм (шаг 2 из 4): источник данных диаграммы необходимо выбрать вкладку Диапазон данных и в поле Диапазон мышью указать интервал данных B2:J14.
Далее необходимо указать в строках или столбцах расположены ряды данных. Это определит ориентацию осей х и у. В примере переключатель Ряды в с помощью указателя мыши установим в положение столбцах. Выбираем вкладку Ряд и в поле Подписи оси X указываем диапазон подписей, для чего щелкните в нем указателем мыши и введите диапазон подписей оси х - А2: A14.
Вводим значения подписей оси у, для чего в рабочем поле Ряд указываем первую запись Ряд 1 и в рабочее поле Имя, активизировав его указателем мыши, вводим первое значение переменной у: -2. Затем в поле Ряд указываем вторую запись Ряд 2 и в рабочее поле Имя вводим второе значение переменной у: –1,5. Повторяем таким образом до последней записи - Ряд 9 и после появления требуемых записей нажимаем кнопку Далее.
В третьем окне требуется ввести заголовок диаграммы и названия осей. Для этого необходимо выбрать вкладку Заголовки, щелкнув на ней указателем мыши. Щелкнув в рабочем поле Название диаграммы указателем мыши, ввести с клавиатуры в поле название: Эллипсоид. Затем аналогичным образом ввести в рабочие поля Ось X (категорий), Ось Y (рядов данных) и Ось Z (значений) соответствующие названия: х, у и z. Далее следует нажать кнопку Готово, и после небольшого редактирования будет получена следующая диаграммаэллипсоида (рис.4.41).
Рис.4.41. Построение эллипсоида
4.8.5. Решение нелинейного уравнения
Решение уравнений средствами Excel является одним из полезных прикладных применений для инженерных задач. Пусть задано следующее квадратное уравнение: 2х2 + 3х – 9 = 0, для определения корней которого необходимо выполнить следующее. Решение уравнения будем формировать в одной из ячеек листа Excel, например в В1, в которую введем искомое значение корня х=0. Исходное уравнение запишем в виде формулы в ячейку В2 (рис.4.42). Для получения решения уравнения вызовем средство Excel Подбор параметра, которое является частью блока задач, иногда называемым инструментом анализа «что-если». Под данным анализом понимают процесс изменения значений ячеек и последующий анализ влияния данных изменений на результат вычисления формулы.
Вызов средства Подбор параметра осуществляется из меню Сервис (рис.4.42).
Рис.4.42. Окно Подбор параметра
Для подбора определяемого параметра Excel в одной конкретной ячейке изменяет значение (в нашем примере это ячейка В1) до тех пор, пока формула, зависимая от этой ячейки, не возвращает требуемый результат, то есть 0 для рассматриваемого примера (рис.4.43).
Рис.4.43. Решение уравнения методом подбора результата
В результате решения уравнения Excel сформировал вместо точного результата х=1,5 приближенный результат х=1,499996529, что связано с использованием интерактивных приближенных методов вычислительной математики, полученная погрешность <0,00001. В качестве начального значения в ячейке В1 можно ввести и другое, например 0. Попробуйте самостоятельно рассмотреть и другие варианты.
4.8.6. Решение системы уравнений
Рассмотрим в качестве примера решение системы нелинейных уравнений с двумя неизвестными в ограниченных пределах изменения переменной Х, что часто требуется в экономических задачах:
У=Х2 +2;
Z=3/Х +2.
Решением системы уравнений будет являться точка пересечения двух функций У=F(X) и Z=F(X), а точность определяется выбранным шагом дискретизации переменной Х, переменная Х задана в интервале Х=(0,1-2) с шагом ∆Х=0,1 (рис.4.44).
Рис.4.44. Решение системы уравнений
Для построения данных функций в столбце А заданы значения переменной Х, в ячейках В2:В21введены формулы для вычисления функции У= Х2 +2, а в ячейках С2:С21 - формулы для вычисления функции Z=3/Х +2. Решением системы уравнений является значение Х= 1,44 на пересечении графиков функций У и Z. Как видно из графиков, более точное решение можно получить при использовании шага ∆Х=0,05.
4.8.7. Численное интегрирование функций
Вначале остановимся кратко на понятии определенный интеграл. Пусть на отрезке [a, b] задана функция y =f(х), и отрезок [a, b] разбит на n элементарных отрезков в точках х0., х1,..., хn.: а = х0<х1<х2<...<хп = b.
На каждом отрезке разбиения [xi-1., xi] выбрана некоторая точка ζi и положено, что ∆xi = xi - xi-1, где i = 1, 2,..., п. Тогда сумму вида:
называют интегральной суммой функции y =f(х) на [a, b]. Данная интегральная сумма определяется как способом разбиения отрезка [a, b], так и выбором точек ζ1, ζ2,... ζn = 0 на каждом из отрезков разбиения ∆xi = xi – xi-1, i = 1, 2,..., n. Обозначим через max ∆xi максимальную из длин отрезков [xi-1, xi], где i = 1, 2,..., n.
Тогда определенным интегралом от функции y =f(х) на [a, b] называют предел интегральной суммы при стремлении max xi, к нулю, если он существует, конечен и не зависит от способа выбора точек х1, х2,. и точек ζ1 ζ2. Определенный интеграл обозначается как:
а f(x) называют интегрируемой в пределах [a, b], то есть:
Число а называют нижним пределом определенного интеграла, число b - его верхним пределом.
Геометрический смысл определенного интеграла заключается в следующем. Если функция y =f(х) неотрицательна на отрезке [a, b], где а < b, то 
численно равен площади S под кривой у =f(x) на [a, b].
Действительно, отдельное слагаемое интегральной суммы (1) равно площади Si прямоугольника со сторонами ∆xi и f(x.) (согласно определению значение определенного интеграла не зависит от способа выбора точек ζ1, ζ2,...), где i= 1, 2,... n (рис. 4.45). Поэтому вся интегральная сумма (1) равна площади Si = Si+S2+...+Sn под ломаной, образованной на каждом из отрезков [xi-1, xi] прямыми, параллельными оси абсцисс. При стремлении max ∆xi к нулю ломаная неограниченно приближается к исходной кривой, а площадь под ломаной переходит в площадь под кривой Si = S.
Рис. 4.45. Графическая интерпретация определенного интеграла
В экономических приложениях определенный интеграл может выражать, например, объем произведенной продукции (и) при известной функции производительности труда f(t):
.
Обычно для нахождения определенного интеграла используется формула Ньютона-Лейбница:
.
Однако применение формулы (3) на практике связано с существенными трудностями, возникающими при нахождении первообразной в случае усложнения подынтегральной функции. Поэтому в приложениях используют так называемые численные методы, позволяющие найти приближенное значение искомого интеграла с требуемой точностью. Этот подход оказывается особенно предпочтительным при использовании компьютеров для нахождения интегралов.
Существует значительное количество численных методов вычисления интегралов. Они основаны на разных способах нахождения площади под кривой f(х):
как суммы элементарных трапеций - метод трапеций:
,как суммы элементарных прямоугольников - метод прямоугольников:
.
Существуют также метод Симпсона и ряд других.
Формула метода прямоугольников (4) получается, если отрезок интегрирования [a, b] разбить на п равных частей длиной:
На каждом из отрезков разбиения [xi-1, xi] участок кривой у=f(x) заменяется отрезком прямой, параллельным оси абсцисс. Тогда:
,где S1, S2,..., Sn - площади прямоугольников на каждом из отрезков разбиения. Отдельное слагаемое Si; равно площади прямоугольника со сторонами ∆x и f(x), где i = 1, 2,..., n. Метод прямоугольников является простейшим, но и наименее точным. Более точно определенный интеграл может быть вычислен по формуле трапеций. В этом случае, в отличие от метода прямоугольников, на каждом из отрезков разбиения [xi-1, xi] участок кривой y=f(x) заменяется хордами, стягивающими концевые точки. Тогда, отдельное слагаемое интегральной суммы Si, равно площади трапеции с основаниями f(xi) и f(xi-1) и высотой Ах, где i = 1, 2,..., n, то есть:
Складывая площади элементарных трапеций и приводя подобные члены, получаем формулу (5). Погрешность ∆ вычисления определенного интеграла по формуле трапеций S(n):
может быть оценена из выражения:
,
где М2 - максимальное значение модуля второй производной f"(x) подынтегральной функции y=f(х) на [a, b].
Рассмотрим пример вычисления интегралов по методу прямоугольников и методу трапеций. Пусть требуется вычислить интеграл с шагом ∆х=0,1:
Аналитически данный интеграл может быть вычислен просто:
.
Метод прямоугольников. Для нахождения определенного интеграла данным методом необходимо ввести значения подынтегральной функции f(x) в рабочую таблицу Excel в диапазоне х 
[0; 3] с заданным шагом ∆ x=0,1.
1. Составляем таблицу данных х и f(х). Пусть столбец А будет хранить значения х, а второй столбец В – значения функции f(x). Для этого в ячейку А1 вводим слово Аргумент, а в ячейку В1 - слово Функция, в ячейку А2 вводится первое значение аргумента - левая граница диапазона (0), а в ячейку A3 вводится второе значение аргумента - левая граница диапазона плюс шаг построения (0,1). Затем, выделив ячейки А2:АЗ, автозаполнением формируем все значения аргумента (за правый нижний угол блока А2:А3 курсор протягиваем до ячейки А32, до значения х=3).
2. Затем вводим значения подынтегральной функции, в ячейку В2 необходимо записать ее уравнение. Для этого табличный курсор необходимо установить в ячейку В2, ввести формулу =А2^2 и нажимаем Enter. В ячейке В2 появляется 0. Далее необходимо автозаполнением скопировать функцию из ячейки В2 в диапазон В2:В32 и в результате должна быть получена таблица данных для нахождения интеграла.
3. Теперь в ячейке ВЗЗ может быть найдено приближенное значение интеграла, для чего в ячейку ВЗЗ вводим формулу =0,1 *, затем вызываем Мастер функций. В поле Функция выбираем функцию Сумм и нажимаем кнопку ОК. В рабочее поле диалогового окна СУММ мышью определяем диапазон суммирования ВЗ:В32, заполняя поле Число1 и нажимаем кнопку ОК. В ячейке ВЗЗ появляется приближенное значение искомого интеграла (9,455). На рис.4.46. приведен фрагмент таблицы для вычисления интеграла.
Рис.4.46.Вычисление интеграла
Сравнивая полученное значение с истинным значением вычисления интеграла - 9 можно отметить, что ошибка метода прямоугольников довольно значительна - 0,455.
Метод трапеций. Для нахождения определенного интеграла методом трапеций, как и в случае использования метода прямоугольников, значения подынтегральной функции f(х) должны быть введены в рабочую таблицу в диапазоне х 
[0; 3) с заданным шагом ∆ х=0,1. Поэтому этапы 1-3 полностью аналогичны этапам предыдущего решения. Поскольку таблица данных для нахождения интеграла уже введена, обсудим только этап 3. В ячейке В34 нужно вычислить приближенное значение интеграла по методу трапеций. Для этого в ячейку В34 вводим формулу =0,1*((В2 + В32)/2, затем вызываем Мастер функций. В рабочем поле диалогового окна Сумм определяем диапазон суммирования ВЗ:В31 и нажимаем кнопку ОК. В результате в ячейке В34 формируется приближенное значение искомого интеграла (9,005). Сравнивая полученное значение интеграла с истинным, можно отметить, что ошибка вычисления методом трапеций является вполне приемлемой - 0,005.
4.8.8. Решение дифференциальных уравнений
Использование дифференциальные уравнений находит широкое применение для компьютерного моделирования динамических процессов в автоматизированных системах управления, в механике, других математических моделях. Практическое применение в различных областях науки и техники находят как обыкновенные дифференциальные уравнения, так и уравнения в частных производных. Ниже будет рассмотрен пример решения обыкновенных дифференциальных уравнений с заданными начальными условиями, то есть для таких уравнений значения функции и ее производных до n-1 порядка являются известными при Х=0. Для обыкновенных дифференциальных уравнений,как правило, решается задача Коши, то есть задаются уравнения вида y¢=f (x,y) с начальными условиями x0, y0 при x0 ≤ x ≤ xкон.
Численное решение таких уравнений заключается в разбиении интервала х точками xi с шагом h для i=1,2,…n и нахождении значений yi по формуле yi = yi-1 + ∆yi, где ∆yi - приращение переменной у на каждом шаге.
Формула данного приращения определяется выбранным численным методом, Для метода Эйлера ∆yi = h∙f(xi,yi), а в инженерных методах (Рунге-Кутта и других) она записывается более сложным образом. При этом уравнение 2-го и более высоких порядков предварительно должны быть сведены к нормальной системе обыкновенных дифференциальных уравнений 1-го порядка. Так уравнение y''=xy' +y2 с начальными условиями x0 =0, y0 =3, y' =1 преобразуется к системе двух уравнений 1-го порядка:
y1' =y2, y2' =xy2 + y12
с начальными условиями y10 = 3, y20 = 1.
Один из вариантов решения этой системы обыкновенных дифференциальных уравнений на интервале (0, 1) с шагом 0, 1 методом Эйлера приведен на рис.4.47.
Рис.4.47. Решение дифференциального уравнения
В столбце А создан диапазон изменения аргумента х от 0 до 1 с шагом 0, 1. Столбец В содержит вычисленные по формуле Эйлера значения Y1(=B2 + 0,1*C2), столбец С – значения Y2 (=C2 + 0,1*(A2*C2+B2^2)).
Графики изменения функций Y1(х) и Y2(х) представлены на диаграмме.
4.8.9. Финансовые вычисления в Excel
Для выполнения различных финансовых вычислений Excel содержит группу финансовых функций, реализуемых с помощью Мастера функций. Они включают такие функции как: вычисление амортизации активов, ставку доходности по вкладу, вычисление процентов за определенный инвестиционный период и многие другие. Ниже рассмотрим примеры реализации некоторых финансовых функций.
Расчет амортизационных отчислений
Для расчета амортизационных отчислений необходимо знать три основных параметра:
· начальная стоимость имущества;
· остаточная стоимость имущества - стоимость в конце периода амортизации, иногда называемая остаточной стоимостью имущества после завершения эксплуатации;
· время эксплуатации - количество периодов, в течение которых имущество амортизируется, иногда называемые периодами амортизации.
Для расчета амортизационных отчислений могут быть использованы различные подходы и способы и в зависимости от этого могут быть применены и различные финансовые функции. В наиболее простом подходе амортизация распределяется равномерно на каждый год эксплуатации имущества и для расчета величины амортизационных отчислений используют функцию АПЛ, которая имеет следующий синтаксис: АПЛ(А;В;С), где:
· А - начальная стоимость имущества;
· В - остаточная стоимость имущества;
· С - продолжительность эксплуатации.
Рассмотрим пример расчета амортизационных отчислений. Пусть имеется приобретенное имущество стоимостью 400000 руб., продолжительность его эксплуатации составляет 8 лет, а остаточная стоимость равняется 50000 руб. Величина амортизационных отчислений составит 43,500 руб. за каждый год эксплуатации
Рис.4.48. Вычисление амортизационных отчислений
Расчет процентных платежей
В простейших случаях для расчета процентных платежей можно использовать функцию ПЛТ. Данная функция вычисляет сумму периодического платежа для погашения займа на основе постоянных сумм платежей и фиксированной процентной ставки в течение определенного срока. Также функция ПТЛ обеспечивает вычисление периодических дополнительных вкладов под заданный процент для получения за определенный срок требуемой величины. Функция ПТЛ имеет следующий синтаксис:
ПЛТ(ставка;кпер; пс; бс; тип), где:
· ставка - процентная ставка по ссуде;
· кпер - общее число выплат по ссуде;
· пс - стоимость, приведенная к текущему моменту, которая на текущий момент равноценна некоторым будущим платежам;
· бс - искомое значение будущей стоимости;
· тип - число 1 или 0, которое определяет момент производимой выплаты: в начале периода (1) в конце периода (0).
Выплаты, возвращаемые функцией ПЛТ, включают основные платежи и платежи по процентам, но не включают налогов, резервных платежей или комиссий, иногда связываемых со ссудой.
При создании формулы можно не использовать все аргументы функции, тогда вместо отсутствующего аргумента в формуле помещается точка с запятой (;).
Рассмотрим пример расчета ежемесячного вложения исходя из 8% годовых, которые через 10 месяцев обеспечат выплату суммы кредита в 30000 руб.(Рис.4.49).
Рис. 4.49. Расчет ежемесячных процентных платежей
Результат вычисления получается отрицательным (-3111,10 руб.), так как данную сумму ежемесячно требуется выплачивать.
Расчет стоимости инвестиции
Для выполнения данного расчета может быть использована функция БС, определяющая стоимость инвестиций в будущем на основе постоянного периодического платежа, а также с учетом фиксированной процентной ставки. Функция имеет следующий синтаксис:
БС(ставка; кпер; плт; пс; тип), где:
· ставка- ставка за заданный период;
· кпер- количество периодических платежей;
· плт - выплата, производимая в каждый определенный период;
· пс – стоимость, равноценная определенным платежам в будущем на текущий момент;
· тип – число0 или 1, которое определяет момент требуемой выплату
Рассмотрим пример расчета будущей стоимости инвестиции при стоимости на текущий момент 500руб при 10 платежах и годовой ставкой 6% (Рис.4.50).
Рис.4.50. Расчет стоимости инвестиций
Годовая процентная ставка делится на 12, т. к. начисление сложных процентов производится ежемесячно.
Расчет продолжительности платежей
В простейших случаях для расчета можно использовать функцию КПЕР. Функция КПЕР вычисляет количество периодических выплат, необходимых для погашения займа, полученного под определенный процент. Эту же функцию можно использовать для вычисления количества периодических вложений под определенный процент, необходимых для достижения определенной величины вклада. Функция КПЕР имеет следующий синтаксис:
КПЕР(ставка;плт; пс; бс; тип), где:
· ставка – ставка в процентах;
· плт - выплаты, производимые периодически
· пс - приведенная к текущему моменту стоимость или общая сумма, которая на текущий момент равноценна ряду будущих платежей;
· бс – величина, необходимая для будущей стоимости;
· тип - число 1 или 0, определяет момент производимой выплаты.
Рассмотрим пример использования данной функции для определения периода выплаты инвестиции в 10000 руб, полученной под 12% годовых при условии выплат за каждый период, например в месяц 100 руб (Рис.4.51).
Рис.4.51.Расчет выплаты инвестиций
4.9. Тесты
1. Какой оператор не входит в группу арифметических операторов:
a. –
b. +
c. &
d. ^
2. Что из перечисленного не является характеристикой ячейки:
a. имя;
b. адрес;
c. размер;
d. значение.
3. Какое значение может принимать ячейка:
a. числовое;
b. текстовое;
c. возвращенное;
d. все перечисленные.
4. Какой адрес будет иметь ячейка В12, если добавить символ $ перед B:
a. относительный;
b. косвенный;
c. непосредственный;
d. абсолютный.
5. Что может являться аргументом функции:
a. ссылка;
b. константа;
c. функция;
d. все варианты верны.
6. Указание адреса ячейки в формуле называется:
a. ссылкой;
b. функцией;
c. оператором;
d. именем ячейки.
7. Программа Excel используется для:
a. создания текстовых документов;
b. создания электронных таблиц;
c. создание графических изображений;
d. все варианты верны.
8. С какого символа начинается формула в Excel:
a. =;
b. +;
c. пробел;
d. все равно с какого.
9. На основе чего строится любая диаграмма:
a. книги Excel;
b. графического файла;
c. текстового файла;
d. данных таблицы.
10.Выберите верную запись формулы для электронной таблицы:
a. 3C+4*D4;
b. C3=C1+2*C2;
c. A5B5+23;
d. =A2*A3-A4.
11. Минимальной составляющей таблицы является:
a. ячейка;
b. формула;
c. книга;
d. нет верного ответа.
12. Для чего используется функция СУММ:
a. для получения суммы квадратов указанных чисел;
b. для получения суммы указанных чисел;
c. для получения разности сумм чисел;
d. для получения квадрата указанных чисел.
13. Сколько существует видов адресации ячеек в Excel:
a. один;
b. два;
c. три;
d. четыре.
14. Что делает Excel, если в составленной формуле содержится ошибка:
a. возвращает 0 как значение ячейки;
b. выводит сообщение о типе ошибки как значение ячейки;
c. исправляет ошибку в формуле;
d. удаляет формулу с ошибкой.
15. Для чего используется диалоговое окно команды «Форма»:
a. для заполнения записей таблицы;
b. для форматирования таблицы;
c. для проверки орфографии на листе;
d. для фильтрации записей таблицы по условию.
16. Какая из ссылок является абсолютной:
a. C22;
b. R1C2;
c. $A$5;
d. #A#5.
17. Упорядочивание значений диапазона ячеек в определенной последовательности называют:
a. форматирование;
b. фильтрация;
c. группировка;
d. сортировка;
18. В электронной таблице выделен диапазон ячеек (А2:D4). В этот диапазон входит ….ячеек:
a. 12;
b. 9;
c. 14;
d. 8.
Ответы к тестам главы 4
| c | c | d | d | d | a | d | a | d | d | a | b | b | b | a | c | d | b |
Глава 5. Компьютерные сети, Интернет
5.1. Назначение и классификация компьютерных сетей
Под компьютерной сетью понимают группу компьютеров и необходимое периферийное оборудование, объединенных в пределах какого – либо региона проводными или беспроводными каналами передачи цифровых данных. Сеть служит для решения разнообразных задач и совместного использования программных и вычислительных ресурсов. Компьютерная сеть в пределах одного или нескольких зданий называется локальной сетью. Эти сети могут иметь технические устройства для выхода в Интернет. Для этого каждый компьютер, должен иметь сетевую плату, к которой подключается кабель связи. Кабели, подключенные к компьютерам, могут быть объединены в устройстве, называемом сетевым концентратором (switch, hub). Для объединения различных подсетей в более крупную сеть их сетевые концентраторы связываются друг с другом. Таким образом, обеспечивается прохождение сигналов между всеми устройствами, объединенными в сеть. Основными достоинствами использования компьютерных сетей являются следующие:
· быстрый обмен данными между компьютерами сети;
· обеспечение доступа с любого компьютера через коммуникационный узел глобальной сети к ресурсам Интернета;