close

Вход

Забыли?

вход по аккаунту

?

1166.Математические приложения в Excel

код для вставкиСкачать
Copyright ОАО «ЦКБ «БИБКОМ» & ООО «Aгентство Kнига-Cервис»
ФЕДЕРАЛЬНОЕ АГЕНТСТВО ПО ОБРАЗОВАНИЮ
ГОУ ВПО «ВОРОНЕЖСКИЙ ГОСУДАРСТВЕННЫЙ УНИВЕРСИТЕТ»
Математические приложения в Excel
Учебно-методическое пособие для вузов
Составитель:
П.В. Садчиков
Воронеж
2009
Copyright ОАО «ЦКБ «БИБКОМ» & ООО «Aгентство Kнига-Cервис»
2
Утверждено научно-методическим советом исторического факультета
25 сентября 2009 г., протокол №1
Рецензент: Махортов С.Д.
Пособие подготовлено на кафедре уравнений в частных производных и теории
вероятностей математического факультета Воронежского госуниверситета.
Рекомендуется для студентов 1 курса исторического факультета (отделение
«Социология»)
Для специальности: 020300 – Социология
Copyright ОАО «ЦКБ «БИБКОМ» & ООО «Aгентство Kнига-Cервис»
3
Введение
В этом пособии приводятся подробные пошаговые решения типовых
математических задач с помощью табличного процессора MS Excel.
Программа Excel отличается доступностью, простотой интерфейса и
универсальностью, имеет обширную библиотеку встроенных функций.
Цель данного учебно-методического пособия – помочь пользователям ПК
освоить технику обработки математических данных и построения диаграмм.
Здесь рассматриваются задачи на вычисление выражений, решение
алгебраических уравнений, построение двумерных и трехмерных графиков,
задачи с матрицами, на вычисление пределов, производных, нахождение
глобальных минимумов и максимумов, численное интегрирование. При
решении некоторых задача используются инструменты, входящие в надстройку
Поиск решения, которые можно назвать уникальными. Описано мощное
средство оптимизации вычислений в Excel – циклические ссылки
(итерационные вычисления).
Операторы
Вычисления в Excel проводятся с помощью арифметических операторов,
операторов сравнения, адресных операторов, встроенных функций и
инструментов Excel.
Арифметические операторы:
¾ сложение +;
¾ вычитание –;
¾ унарный минус –;
¾ умножение *;
¾ деление /;
¾ возведение в степень ^;
¾ процент %.
Порядок убывания приоритетов: унарный минус, процент, возведение в
степень, умножение и деление, сложение и вычитание. Операторы одной
ступени выполняются слева направо.
Операторы сравнения:
¾ равно =;
¾ больше >;
¾ меньше <;
¾ больше или равно >=;
¾ меньше или равно <=;
¾ не равно < >.
Например, ввод =5>3 в ячейку А1 дает:
Адресные операторы (операторы ссылок):
¾ двоеточие – оператор диапазона;
¾ точка с запятой – оператор объединения ссылок.
Copyright ОАО «ЦКБ «БИБКОМ» & ООО «Aгентство Kнига-Cервис»
4
Задача 1. Вычислить
(7 − 6,35) : 6,5 + 9,9
.
5 169
(1, 2 : 36 + 1, 2 : 0, 25 − 1 ) :
16 24
Встроенные функции
Ввод в ячейку аналитически заданной функции y = f ( x) начинается со
знака =. После него вводится аналитическое выражение заданной функции, в
котором x заменяется именем какой-либо ячейки, отличной от ячейки ввода.
Остается нажать кнопку Enter, что завершает ввод функции. Автоматически в
ячейке ввода появляется значение функции, вычисленное при том значении
независимой переменной, которое находится в ячейке, отведенной под
независимую переменную (по умолчанию 0). Введенная формула остается
только в строке формул, где она редактируется при необходимости.
Пример 1. Ввести квадратичную функцию y = x 2 + 3 x − 4 .
Решение.
1. Оставляем ячейку А1 за переменной x .
2. Выделяем ячейку В1.
3. Устанавливаем курсор ввода в строке формул, нажимаем = и вводим
аналитическое выражение функции, заменяя x на а1. Ввод
автоматически дублируется в ячейке В1.
4. Щелчок Enter завершает ввод функции.
Можно вводить аналитическое выражение функции и непосредственно в
ячейку В1.
В ячейке b1 значение -4, равное значению функции при x = 0 .
Если ввести x0 в ячейку А1, то в ячейке В1 будет y ( x0 ) . Например,
y (1) = 0 .
Пример 2. Ввести функцию z = x 2 + y 2 .
Решение:
Под независимые переменные отведем ячейки А1 и В1:
Вычислим, например, z (3,4) :
Copyright ОАО «ЦКБ «БИБКОМ» & ООО «Aгентство Kнига-Cервис»
5
Замечание. При вводе функции имена
независимых
переменных
(ячеек, в которых они находятся) не обязательно набирать с клавиатуры, они
вставляются в строку формул щелчками ЛКМ (левой кнопки мыши) по самим
ячейкам.
В Excel встроена не только библиотека важнейших функций, но и
уникальная система автоматических подсказок, по их конструкциям и
значениям параметров значительно упрощающая работу. Вычисления можно
проводить как через диалоговые окна, вызовом Мастера функций, так и без
них, непосредственным набором формулы функции в ячейку (в строку
формул).
Мастер функций вызывается через пункт меню Вставка→Функция:
Дальнейший порядок работы:
¾ щелчок ЛКМ по выбранной категории открывает в окне Выберите
функцию соответствующий список;
¾ нужная функция выделяется;
¾ нажатие ОК приводит к следующему диалоговому окну Аргументы
функции, назначение которого понятно из названия.
Пример 3. Вычислить sin
π
6
.
Решение.
1. Выделяем ячейку, в которую поместим результат, например ячейку А1.
2. Вызываем Мастер функций.
3. В списке категорий выделяем строку Математические.
4. В открывшемся списке Выберите функцию с помощью полосы
прокрутки находим функцию SIN и выделяем ее щелчком ЛКМ.
5. Нажатие ОК приводит к диалоговому окну:
Copyright ОАО «ЦКБ «БИБКОМ» & ООО «Aгентство Kнига-Cервис»
6
6. Вводим
заданный
угол,
учитывая, что в Excel число π
задается как функция, с круглыми скобками:
7. Нажатие ОК вставляет результат в выбранную ячейку:
Функции, заданные несколькими аналитическими выражениями, вводятся
с помощью встроенной функции ЕСЛИ.
⎧ x 2 , при x < 2,
Пример 4. Ввести функцию y = ⎨
⎩2 x + 1, при x ≥ 2.
Решение:
⎧ x2 ,
при x < 2,
⎪
Задача 2. Ввести функцию y = ⎨2 x + 1, при 2 ≤ x ≤ 3,
⎪ 10 − x,
при x > 3.
⎩
Полный список встроенных функций, относимых в Excel к
«математическим», имеет вид: ABS, ACOS, ACOSH, ASIN, ASINH, ATAN,
ATAN2, ATANH, COS, COSH, EXP, LN, LOG, LOG10, SIN, SINH, TAN, TANH,
ГРАДУСЫ, ЗНАК, КОРЕНЬ, МОБР, МОПРЕД, МУМНОЖ, НЕЧеТ,
ОКРВВЕРХ, ОКРВНИЗ, ОКРУГЛ, ОКРУГЛВВЕРХ, ОКРУГЛВНИЗ, ОСТАТ,
ОТБР, ПИ, ПРОИЗВЕД, ПРОМЕЖУТОЧНЫЕ ИТОГИ, РАДИАНЫ,
РИМСКОЕ, СЛЧИС, СТЕПЕНЬ, СУММ, СУММЕСЛИ, СУММКВ,
СУММКВРАЗН, СУММПРОИЗВ, СУММРАЗНКВ, СУММСУММКВ, ФАКТР,
ЦЕЛОЕ, ЧеТН, ЧИСЛКОМБ.
7π
π
3π
5π
Задача 3. Вычислить sin 2 + cos 2
+ sin 2
+ cos 2
.
8
8
8
8
Задача 4. Вычислить tg435 + tg375 .
Copyright ОАО «ЦКБ «БИБКОМ» & ООО «Aгентство Kнига-Cервис»
7
Задача 5. Вычислить sin(
3π
4
− 2arctg ) .
2
3
Задача 6. Упростить − log 2 log 2 4 2 .
Задача 7. Упростить 36log6 5 + 101−lg 2 − 3log9 36 .
Основная особенность приложения Excel, превращающая его в
уникальный вычислительный инструмент, пользующийся заслуженной
популярностью во всем мире, проявляется при копировании формул,
содержащих имена ячеек.
Пример 5. Составьте таблицу значений функции y = (2 x + 1) 2 на отрезке
[0,1] с шагом h = 0,1 .
Решение.
1. Вводим в ячейку А1 начальное значение 0.
2. В ячейку В1 вводим значение, увеличенное на шаг, то есть 0,1.
3. Выделяем обе ячейки.
4. Берем мышкой маркер заполнения рамки выделенного диапазона, когда
он примет вид +, и перемещаем его вправо, пока не появится цифра 1.
Отпуская ЛКМ, получаем в диапазоне А1:K1 значения независимой
переменной.
5. В ячейке А2 задаем формулу.
6. Берем мышкой маркер заполнения рамки ячейки А2 и методом
«протаскивания» маркера заполнения копируем введенную формулу в
остальные ячейки диапазона А2:К2. Все, таблица значений функции
построена:
Если необходимо, чтобы при копировании формула не изменялась, то
перед символами, образующими имена ячеек, входящих в формулу, ставятся
знаки $. В этом случае ссылки на ячейки – абсолютные.
Подбор параметра
В Excel встроены средства приближенного решения алгебраических и
трансцендентных уравнений. Одно из них Подбор параметра - поиск
приближенного значения одной ячейки при заданном значении другой,
ссылающейся на нее. В нем применяется метод последовательных
приближений, результат, вообще говоря, зависит от начального приближения.
Возвращается приближенное значение корня уравнения, ближайшего к
начальному приближению.
Данным инструментом достаточно эффективно решаются уравнения
элементарной математики, содержащие только рациональные корни, так как по
их приближенным значениям обычно нетрудно определить сами значения.
Пример 6. Решить уравнение x 2 + 2 x − 3 = 0 .
Решение.
Copyright ОАО «ЦКБ «БИБКОМ» & ООО «Aгентство Kнига-Cервис»
8
обязательно
именно
А1).
1. Полагаем x = А1 (но совсем не
Начальное приближение – значение ячейки А1 (по умолчанию 0).
2. В некоторую другую выбранную ячейку, например, В1, вводим
формулу левой части уравнения, заменяя x на А1:
3. С помощью пункта меню Сервис открываем диалоговое окно Подбор
параметра и вводим данные:
4. Нажатие ОК запускает вычисления. Результаты появляются в ячейках
А1, В1 и на панели Результаты подбора параметра:
5. Нажатием ОК закрываем панель Результат подбора параметра,
приближенный результат получен и находится в ячейке А1:
6. Проверяем, не является ли x = 1 корнем уравнения. Продолжаем
решение. Вводим в ячейку А1 значение 1 и видим (по значению ячейки
В1), что x = 1 корень:
Замечание. В случае когда визуально трудно определить точное значение
корня, соответствующее полученному приближению, следует воспользоваться
вкладкой Число диалогового окна Формат ячеек.
7. Удаляем содержимое ячейки А1, чтобы избежать сообщения о делении
на ноль.
8. Выделяем ячейку В1 и вносим изменения в формулу уравнения:
9. Снова открываем диалоговое окно Подбор параметра и заполняем его
поля, как раньше. Теперь нажатие ОК дает:
Copyright ОАО «ЦКБ «БИБКОМ» & ООО «Aгентство Kнига-Cервис»
9
Итак, корни уравнения найдены.
x2 + 1 x2 + 2
Пример 7. Решить уравнение
−
= −2 .
x +1 x − 2
Решение. Оставляем ячейку А1 за неизвестной величиной x , в ячейку В1
вводим формулу уравнения, предварительно переносим -2 в левую часть.
Открываем и заполняем диалоговое окно Подбор параметра:
Нажимая ОК, получаем:
Проверкой убеждаемся, что x = 1 - корень уравнения. Переходим к поиску
других корней уравнения. Он приводит к результату, представленному ниже:
Следовательно, других действительных корней уравнение не имеет.
Ответ: 1.
Copyright ОАО «ЦКБ «БИБКОМ» & ООО «Aгентство Kнига-Cервис»
10
20
+ x x + x = 22 .
x
Решение. Ход решения как в предыдущей задаче, только, учитывая, что
x = 0 не входит в ОДЗ (область допустимых значений) уравнения, в ячейку А1
помещаем значение 3. Результат подбора параметра на 1-м шаге:
Пример 8. Решить уравнение
Проверкой убеждаемся, что x = 4 - корень уравнения. Подбор параметра на
2-м шаге:
Проверкой убеждаемся, что x = 1 - тоже корень уравнения. Продолжая
решение, приходим к тому, что других действительных корней уравнение не
имеет.
Ответ: 1, 4.
Задача 8. Решить уравнение lg 5 + lg( x + 10) = 1 − lg(2 x − 1) + lg(21x − 20) .
3 x 2 −10 x +3
Задача 9. Решить уравнение x − 3
= 1.
Поиск решения
Надстройка Поиск решения - мощнейший вычислительный инструмент
приложения Excel. Если в списке меню Сервис он не указан, то необходимо
щелкнуть ЛКМ в нем по строке Надстройки и поставить галочку в строке
Поиск решения.
Пример 9. Решить уравнение x 2 + 2 x − 3 = 0 .
Решение:
1. Принимаем x = А1.
2. В ячейку В1 вводим формулу левой части уравнения.
3. Открываем диалоговое окно Поиск решения и задаем сценарий
решения:
Copyright ОАО «ЦКБ «БИБКОМ» & ООО «Aгентство Kнига-Cервис»
11
4. Нажатие кнопки Выполнить приводит к корню x = 1 :
5. Остается нажать ОК. Заметим, что, в отличие от средства Подбор
параметра, получен точный результат.
6. Находим второй корень уравнения:
Ответ: 1, -3.
1
1
10
+
= .
2
2
x
( x + 2)
9
Решение. ОДЗ уравнения: x ∈ (−∞, −2) ∪ (−2,0) ∪ (0, +∞) . Корни уравнения
следует искать на каждом из этих промежутков отдельно, выбирая из них
начальные приближения.
1. x ∈ (−∞, −2) , А1=-5. Результаты поиска решения:
Пример 10. Решить уравнение
Переходим к поиску других корней на этом промежутке:
Copyright ОАО «ЦКБ «БИБКОМ» & ООО «Aгентство Kнига-Cервис»
12
Проверкой убеждаемся, что полученный корень является посторонним.
2. x ∈ (−2,0) , А1=-1. На данном промежутке корней нет:
3. x ∈ (0, +∞) , А1=5. В этом случае получаем:
Убеждаемся, по аналогии со случаем 1, что других корней на данном
промежутке нет.
Ответ: -3, 1.
Перейдем к решению систем уравнений.
Пример 11. Решить систему уравнений
⎧ y 2 − xy = −12,
⎨ 2
⎩ x − xy = 28.
Решение. Составляем функцию z = ( y 2 − xy + 12) 2 + ( x 2 − xy − 28) 2 и ищем
значения x, y , при которых z = 0 .
1. В ячейку С1 вводим функцию z , заменяя x на А1, y на В1.
2. Открываем диалоговое окно Поиск решения и задаем сценарий
решения:
Copyright ОАО «ЦКБ «БИБКОМ» & ООО «Aгентство Kнига-Cервис»
13
3. По команде Выполнить получаем результаты поиска решения:
4. Проверкой убеждаемся, что (7, 3) – решение системы:
5. Изменяем формулу ячейки С1 и снова применяем надстройку Поиск
решения, что дает:
6. Проверкой убеждаемся, что (-7, -3) также является решением системы.
Дальнейшее применение надстройки Поиск решения показывает, что
других решений система не имеет.
Ответ: (7, 3), (-7, -3).
Задача 10. Решить систему
⎧2 x + y + z = 7,
⎪
⎨ x + 2 y + z = 8,
⎪
⎩ x + y + 2 z = 9.
Двумерная графика
График функции y = f ( x) строится с помощью Мастера диаграмм,
который запускается командой Вставка→Диаграмма.
Copyright ОАО «ЦКБ «БИБКОМ» & ООО «Aгентство Kнига-Cервис»
14
Пример 12. Построить график
функции y = x x + 1 .
Решение.
1. Задаем вертикальную (или горизонтальную) таблицу значений данной
функции, например, на отрезке [−2,2] с шагом 0,2 и выделяем ее (см.
пример 5).
2. Открываем диалоговое окно Мастер диаграмм, в нем выделяем тип
диаграммы – Точечная и вид диаграммы.
3. Нажатием Далее приходим ко второму диалоговому окну. В поле Имя
вкладки Ряд можно задать, например, уравнение зависимости.
4. Нажатием Далее приходим к третьему диалоговому окну. Уберем
линии сетки. Введем название графика и обозначение осей.
5. После нажатия Далее, нажимаем Готово и получаем график заданной
функции:
Copyright ОАО «ЦКБ «БИБКОМ» & ООО «Aгентство Kнига-Cервис»
15
При необходимости можно продолжить
его форматирование.
Устанавливается курсор, например, на оси Ох и, когда рядом появляется
всплывающая подсказка, делается щелчок ПКМ, вызывающий контекстное
меню, в котором выбирается Формат оси. Если установить курсор на графике,
то можно вызвать контекстное меню Формат рядов данных. Аналогично
вызываются меню Формат области построения и Формат области
диаграммы. Перечисленные возможности форматирования позволяют
выполнить построения на уровне художественной графики.
Задача 11. Построить графики двух функций y = x x + 1 и y = x + 1 .
Произвести форматирование на свой вкус.
Мастер диаграмм позволяет строить графики функций, заданных
параметрически, а также в полярных координатах.
Пример 13. Построить циклоиду
⎧ x = a (t − sin t ),
⎨
⎩ y = a(1 − cos t ),
t ∈ [0,2π ], a = 1. По циклоиде движется отмеченная на обруче точка, когда
обруч катится без скольжения по прямой. В данном случае координаты
отмеченной точки в начальный момент времени (0, 0), радиус обруча равен 1.
Решение.
1. Введем в ячейку А1 значение 0.
2. Командами Правка→Заполнить→Прогрессия откроем диалоговое окно
Прогрессия и установим в нем:
Copyright ОАО «ЦКБ «БИБКОМ» & ООО «Aгентство Kнига-Cервис»
16
3. После нажатия ОК появляется столбец значений переменной t .
4. В ячейки В1 и С1 соответственно вводим формулы правых частей
уравнений системы, заменяя t на А1.
5. Выделяем ячейки В1 и С1 и, используя маркер заполнения, заполняем
столбцы значений заданных функций.
6. По ним строим точечную диаграмму:
Пример 14. Построить кардиоиду r = cos ϕ + 1, ϕ ∈ [0,2π ] .
Решение.
1. Последовательность значений ϕ зададим так же, как задавали
последовательность значений t в предыдущем примере.
2. В ячейку В1 вводим формулу = cos(a1) + 1 и методом «протаскивания»
заполняем столбец значений функции.
3. По нему строим диаграмму лепесткового типа:
Copyright ОАО «ЦКБ «БИБКОМ» & ООО «Aгентство Kнига-Cервис»
17
Трехмерная графика
В Excel построение поверхности z = f ( x, y ), x ∈ [a, b], y ∈ [c, d ] начинается
с задания таблицы значений функции f ( x, y ) в узлах прямоугольной сетки,
соответствующих заданным (выбранным) шагам hx , hy . Далее оно проводится
пошаговым графическим редактором Мастер диаграмм, тип диаграммы –
Поверхность.
Пример 15. Построить параболоид вращения
z = x 2 + y 2 , x ∈ [−1,1], y ∈ [−1,1], h = 0,1 .
Решение.
1. В ячейку А2 вводим значение -1, а в ячейку А3 – значение -0,9.
Выделяем обе ячейки и методом «протаскивания» заполняем диапазон
А2:А22 значений переменной x .
2. Аналогично в ячейку В1 вводим значение -1, а в ячейку С1 –
значение -0,9 и заполняем диапазон В1:V1 значений другой
переменной.
3. В ячейке В2, учитывая, что значения независимых переменных x, y
должны браться из первого столбца и первой строки соответственно,
то есть ссылки на первый столбец и первую строку должны быть
абсолютными, задаем формулу =$A2^2+B$1^2.
4. Берем мышкой маркер заполнения рамки ячейки В2 в остальные
ячейки диапазона В2:V22. Получаем выделенную таблицу значений
заданной функции:
Далее с помощью
поверхность:
Мастера
диаграмм
строим
Парабалоид вращения
2
1,5
1,5-2
1-1,5
1
0,5-1
0,5
Р14
21
16
11
6
0
1
5.
Р1
0-0,5
требуемую
Copyright ОАО «ЦКБ «БИБКОМ» & ООО «Aгентство Kнига-Cервис»
18
На поверхности автоматически выделяются слои уровня. Имеются
дополнительные возможности форматирования, связанные с объемностью
изображения, которые можно использовать в контекстном меню Формат
области построения.
xy ( x 2 − y 2 )
. Поменять параметры
Задача 12. Построить поверхность z =
2
2
x +y
объемного изображения.
Матрицы
Простые диапазоны (массивы) Excel – матрицы.
⎛ 2 1 −1 ⎞
⎛ −2 1 0 ⎞
Пример 16. Вычислить 3А+2В, где А = ⎜
,
В
=
⎟
⎜ −3 2 2 ⎟ .
⎝ 0 1 −4 ⎠
⎝
⎠
Решение.
1. В диапазон А1:С2 вводим элементы матрицы А.
2. В диапазон D1:F2 вводим элементы матрицы В.
3. Выделяем диапазон G1:I2 и в окно формул вводим =3*a1:c2+2*d1:f2.
4. Нажимая F2, а затем Ctrl+Shift+Enter, получаем:
⎛ 2 5 −3 ⎞
Ответ: ⎜
⎟.
⎝ −6 7 −8 ⎠
⎛ 1 −3 2 ⎞⎛ 2 5 6 ⎞
⎟.
Пример 17. Вычислить ⎜⎜ 3 −4 1 ⎟⎜
1
2
5
⎟⎜
⎟
⎜ 2 −5 3 ⎟⎜ 1 3 2 ⎟
⎝
⎠⎝
⎠
Решение. Аналогично предыдущему примеру вводим матрицы и выделяем
диапазон под их произведение. Затем воспользуемся математической функцией
МУМНОЖ, и после выполнения 4-го пункта получим:
Для того чтобы транспонировать матрицу, используется функция
ТРАНСП. Определители матриц вычисляются встроенной функцией
МОПРЕД. Обратная матрица находится с помощью функции МОБР.
⎛ 2 −1 1 0 ⎞
⎜ 0 1 2 −1⎟
⎟ . Вычислить ее
Задача 13. Транспонировать матрицу ⎜
⎜ 3 −1 2 3 ⎟
⎜
⎟
⎝3 1 6 1 ⎠
определитель.
Copyright ОАО «ЦКБ «БИБКОМ» & ООО «Aгентство Kнига-Cервис»
19
⎛2 5 7 ⎞
Задача 14. Найти обратную
матрицу к матрице ⎜⎜ 6 3 4 ⎟⎟ .
⎜ 5 −2 −3 ⎟
⎝
⎠
Системы линейных уравнений
В Excel нет встроенной функции, возвращающей решения систем
∑a x
ij
j
= bi , i = 1,2,..., n, det A ≠ 0 ,
но решаются они без проблем.
Δi
, где Δ = det A, Δ i - i-й вспомогательный
Δ
определитель, равный определителю матрицы системы, в которой i-й
столбец заменен столбцом свободных членов.
Пример 18. Решить систему уравнений по формулам Крамера
⎧ 5 x1 + 8 x2 + x3 = 2,
⎪
⎨3 x1 − 2 x2 + 6 x3 = −7,
⎪ 2 x + x − x = −5.
⎩ 1 2 3
Решение. В диапазон А1:D3 введем расширенную матрицу системы, в
диапазон Е1:Е3 скопируем диапазон А1:А3, а в диапазон F1:F3 – диапазон
В1:В3. Тогда формулы Крамера запишутся в виде:
x1 =МОПРЕД(b1:d3)/МОПРЕД(a1:c3),
x2 = МОПРЕД(с1:e3)/МОПРЕД(a1:c3),
x3 = МОПРЕД(d1:f3)/МОПРЕД(a1:c3).
Значения неизвестных находятся в ячейках G1:G3:
1. По формулам Крамера xi =
2. Пример 19. Решить предыдущую систему матричным способом
X = A −1 B :
Ранг матрицы
Excel – уникальный тренажер по отработке навыков вычисления рангов
матриц приведением их элементарными преобразованиями к ступенчатому
виду.
Пример 20. Вычислить ранг матрицы методом элементарных
преобразований:
Copyright ОАО «ЦКБ «БИБКОМ» & ООО «Aгентство Kнига-Cервис»
20
⎛ 47 −67 35 201 155 ⎞
⎜ 26 98 23 −294 86 ⎟ .
⎜
⎟
⎜ 16 −428 1 1284 52 ⎟
⎝
⎠
Решение.
1. Вводим заданную матрицу в диапазон А1:Е13.
2. Устанавливаем дробный формат числа, со знаменателем до трех цифр.
3. Первую строку, которая изменяться не будет, копируем в диапазон
F1:J1.
4. В ячейку F2 вводим формулу =a2-a$1/$a$1*$a2 и копируем ее в
остальные ячейки диапазона F2:J3. После установки дробного формата
чисел фрагмент вычислений принимает вид:
5. Теперь значения ячеек диапазона F1:J2 изменяться не будут и их надо
перенести в диапазон К1:О2. Для этого копируем диапазон F1:J2 в
буфер обмена, выделяем диапазон К1:О2, ПКМ открываем контекстное
меню и, щелчком ЛКМ по строке Специальная вставка, открываем
одноименное диалоговое окно, где отмечаем Вставить значения и
форматы чисел. Остается нажать ОК.
6. В ячейку L3 вводим формулу =g3-g$2/$g$2*$g3 и, копируя ее в
остальные ячейки диапазона К3:О3, приводим заданную матрицу к
виду:
Ответ: 2.
Задача 15.
преобразований:
Вычислить
ранг
матрицы
методом
элементарных
⎛ 1 2 3 ⎞
⎜ 4 5 6 ⎟
⎜
⎟.
⎜ 7 8 9 ⎟
⎜
⎟
⎝10 11 12 ⎠
Пределы. Непрерывность
Мощным средством оптимизации вычислений в Excel являются
циклические ссылки. Так называют формулу, если в ней прямо или через
другие формулы используется имя ячейки, в которой она расположена. После
ввода, приводящего к циклическим ссылкам, появляется панель с
предостережением, закрывая которую, пользователь переходит в режим
циклических (итерационных) вычислений.
К неприятным особенностям циклических вычислений следует отнести то,
что после проведения расчетов, чтобы вернуться в обычный режим, Excel
приходится перезапускать.
Copyright ОАО «ЦКБ «БИБКОМ» & ООО «Aгентство Kнига-Cервис»
21
Параметры
циклических вычислений
устанавливаются
на
вкладке Вычисления диалогового окна Параметры, открываемой командами
Сервис→Параметры→Вычисления.
Пример 21. Найти сумму первых трех членов арифметической прогрессии,
первый член которой a1 = 2 , а разность d = 3 .
Решение.
1. В ячейке А1 зададим счетчик циклов вводом в нее формулы =а1+1.
Панель с предостережением о циклической ссылке закрываем.
2. В ячейку А2 вводим формулу общего члена данной арифметической
прогрессии =2+(а1-1)*3.
3. Сумму поместим в ячейку А3, куда введем формулу =а3+а2.
4. Откроем диалоговое окно Параметры на вкладке Вычисления,
поставим галочку в окошке итерации и зададим значение Предельное
число итераций, равное 3.
5. Щелкнув ОК, в ячейках А1, А2, А3 соответственно получаем число
итераций, третий член прогрессии и сумму первых трех членов:
Пример 22. Найти сумму первых трех членов геометрической прогрессии,
первый член которой b1 = 2 , а знаменатель q = 3 .
Решение. Поступаем так же, как в предыдущем примере, только формулу
пункта 2 заменяем формулой =2*3^(a1-1):
Пример 23. Вычислить первый замечательный предел:
sin x
lim .
x →0
x
Решение. 1 способ.
1. Вводим в диапазон А1:Е1, например, такую последовательность
значений 0,1n , n = 1, 2,...,5 , приближающихся к x = 0 .
2. В ячейку А2 вводим формулу функции, стоящей под знаком предела.
Методом «протаскивания» маркера заполнения копируем ее в ячейки
В2:Е2 и получаем , что предел равен 1:
2 способ.
1. В ячейке А1 задаем циклическую формулу =а1+1, появившуюся панель
закрываем.
2. В ячейке А2 задаем формулу двустороннего приближения к предельной
точке: =(-0,1)^а1.
3. Значения функции вычисляем по формуле =sin(а2)/а2 в ячейке А3.
Copyright ОАО «ЦКБ «БИБКОМ» & ООО «Aгентство Kнига-Cервис»
22
4. Открываем
вкладку
Вычисления в диалоговом
окне Параметры и отмечаем галочкой параметр итерации,
устанавливая предельное число итераций 10.
5. Нажатие ОК дает:
Задача 16. Вычислить предел
x2 − x .
lim
x −1
x →1
Пример 24. Найти точки разрыва функции
⎧ 2 x , 0 ≤ x ≤ 1,
⎪
f ( x) = ⎨ 4 − 2 x, 1 ≤ x ≤ 2,5,
⎪2 x − 7, 2,5 ≤ x ≤ 4.
⎩
Решение. Точками разрыва данной функции могут быть только точки x = 1
и x = 2,5 . Вычисляем в них односторонние пределы. В ячейки А1:А5
соответственно вводим:
=а1+1
=1-0,1^a1
=1+0,1^a1
=2,5-0,1^a1
=2,5+0,1^a1
В ячейках В2:В5, соответственно, задаем формулы:
=2*a2^(1/2)
=4-2*a3
=4-2*a4
=2*a5-7
Запустив циклические вычисления, получаем следующие результаты:
Они показывают, что в точке x = 1 функция непрерывна, а в точке x = 2,5
имеет разрыв 1-го рода.
Производные
С помощью циклических ссылок можно вычислять производные.
Пример 25. Вычислить производную в заданной точке
x 2 + 1 + sin x
f ( x) =
, x0 = 0.
cos x
Решение.
1. В ячейке А1 задаем формулу: =a1+1.
Copyright ОАО «ЦКБ «БИБКОМ» & ООО «Aгентство Kнига-Cервис»
23
2. В ячейку А2 вводим
приращение аргумента: =(0,1)^a1.
3. В ячейке А3 записываем отношение приращения функции
приращению аргумента.
4. Запуская циклические вычисления, получаем:
к
Ответ: 1.
Пример 26. Найти y′′(0) , если y ( x) = e 2 x sin 3x .
Решение.
Ответ: 12.
Глобальные минимумы и максимумы
В надстройке Поиск решения, благодаря режимам Максимальное
значение и Минимальное значение, успешно решаются задачи на нахождение
наибольшего (наименьшего) значения функции f ( x) на заданном отрезке
[a, b] . Вообще говоря, решение поставленной задачи следует начинать с
построения Мастером диаграмм графика заданной функции. Он позволяет
выбрать начальное приближение таким образом, чтобы результат был верным
не только локально, но и глобально.
Пример 27. Определить наибольшее М и наименьшее m значения функции
на указанном отрезке
1 − x + x2
, [0,1].
y=
1 + x − x2
Решение. Построим Мастером диаграмм график заданной функции:
Он показывает, что максимальное (минимальное) значение, получаемое в
надстройке Поиск решения, не зависит от начального приближения.
Найдем наибольшее значение функции на заданном отрезке.
Copyright ОАО «ЦКБ «БИБКОМ» & ООО «Aгентство Kнига-Cервис»
24
1. Оставляем ячейку А1 за
переменной x , в ячейку В1
вводим формулу заданной функции, заменяя x на А1.
2. Открываем диалоговое окно Поиск решения, где указываем:
3. Щелчком ЛКМ по кнопке Добавить секции Ограничения открываем
диалоговое окно Добавление ограничения и вводим условие a1 ≥ 0 :
Так как есть еще одно условие, то нажимаем кнопку Добавить и вводим
следующее условие a1 ≤ 1, после чего нажимаем ОК.
4. По команде Выполнить получаем результаты поиска решения
5. Переходим к поиску наименьшего значения функции на заданном
отрезке. В диалоговом окне Поиск решения заменяем максимальное
значение на минимальное значение. Получаем следующее:
Ответ: М=1, m=0,6.
Задача 17. Определить наибольшее и наименьшее значения функции на
указанном отрезке
Copyright ОАО «ЦКБ «БИБКОМ» & ООО «Aгентство Kнига-Cервис»
25
y = −3 x + 6 x , [−2, 2].
Численное интегрирование
Excel – идеальная среда для численных методов, в том числе для
численных методов интегрирования.
Формулы численного интегрирования.
Формула прямоугольников
b
n
b−a
h
f ( xi ), h =
, x0 = a − , xi = xi −1 + h, i = 1,2,..., n.
∫a f ( x)dx ≈ h∑
n
2
i =1
Формула трапеций
b
n −1
f (a ) + f (b)
b−a
+ ∑ f ( xi ), h =
, x0 = a, xi = xi −1 + h, i = 1,2,..., n.
∫a f ( x)dx ≈ h( 2
n
i =1
4
2
Формула Симпсона
b
∫
n
n −1
i =1
i =1
f ( x)dx ≈ h( f (a) + f (b) + 4∑ f ( x2i −1 ) + 2∑ f ( x2i ), h =
a
b−a
,
2n
x0 = a, xi = xi −1 + h, i = 1,2,...,2n.
1
Пример 28. Вычислить
∫
1 + x3 dx, h = 0,1.
0
Решение.
Метод прямоугольников
1. В ячейку А1 вводим 0,05 – середину первого частичного отрезка.
2. Открываем диалоговое окно Прогрессия и задаем в нем:
3. В ячейку В1 вводим формулу подынтегральной функции, заменяя x на
а1.
4. Методом «протаскивания» маркера заполнения копируем введенную
формулу в ячейки диапазона В2:В10 и тем самым получаем таблицу
значений функции в серединах частичных отрезков.
5. Находим сумму диапазона В1:В10:
Copyright ОАО «ЦКБ «БИБКОМ» & ООО «Aгентство Kнига-Cервис»
26
Умножая ее на 0,1, приходим к окончательному результату.
Ответ: ≈1,111006.
Метод трапеций
1. В диапазоне А1:А11, с помощью диалогового окна Прогрессия (шаг
0,1, предельное значение 1) задаем значения от 0 до 1 соответственно.
2. В ячейку В1 вводим формулу =(1+a1^3)^(1/2)/2 и копируем ее в ячейку
В11.
3. В ячейку В2 вводим формулу подынтегральной функции и методом
«протаскивания» маркера заполнения копируем ее в ячейки диапазона
В3:В10.
4. Находим сумму диапазона В1:В11 и умножаем на 0,1.
Ответ: ≈1,112332.
Метод Симпсона
1. Поступаем так же, как в шаге 1 метода трапеций.
2. В ячейку В1 вводим формулу подынтегральной функции и копируем ее
в ячейку В11.
3. В ячейку В2 вводим формулу =4*(1+а2^3)^(1/2), а в ячейку В3
соответственно =2*(1+a3^3)^(1/2).
4. Выделяем обе формулы и методом «протаскивания» заполняем
диапазон В4:В10.
5. Находим сумму элементов диапазона В1:В11.
6. Полученную сумму умножаем на 0,1/3 и записываем результат в ячейке
С1:
Ответ: ≈1,111446.
Copyright ОАО «ЦКБ «БИБКОМ» & ООО «Aгентство Kнига-Cервис»
27
Учебное издание
Математические приложения в Excel
Учебно-методическое пособие для вузов
Составитель
Садчиков Павел Валерьевич
Редактор
Документ
Категория
Информатика и программирование
Просмотров
90
Размер файла
755 Кб
Теги
1166, excel, математические, приложение
1/--страниц
Пожаловаться на содержимое документа