ФЕДЕРАЛЬНОЕ АГЕНТСТВО ПО ОБРАЗОВАНИЮ
ГОУ ВПО «ВОРОНЕЖСКИЙ ГОСУДАРСТВЕННЫЙ УНИВЕРСИТЕТ»
Математические приложения в Excel
Учебно-методическое пособие для вузов
Составитель:
П.В. Садчиков
Воронеж
2009
Стр.1
3
Введение
В этом пособии приводятся подробные пошаговые решения типовых
математических задач с помощью табличного процессора MS Excel.
Программа Excel отличается доступностью, простотой интерфейса и
универсальностью, имеет обширную библиотеку встроенных функций.
Цель данного учебно-методического пособия – помочь пользователям ПК
освоить технику обработки математических данных и построения диаграмм.
Здесь рассматриваются задачи на вычисление выражений, решение
алгебраических уравнений, построение двумерных и трехмерных графиков,
задачи с матрицами, на вычисление пределов, производных, нахождение
глобальных минимумов и максимумов, численное интегрирование. При
решении некоторых задача используются инструменты, входящие в надстройку
Поиск решения, которые можно назвать уникальными. Описано мощное
средство оптимизации вычислений в Excel – циклические ссылки
(итерационные вычисления).
Операторы
Вычисления в Excel проводятся с помощью арифметических операторов,
операторов сравнения, адресных операторов, встроенных функций и
инструментов Excel.
Арифметические операторы:
сложение +;
вычитание –;
унарный минус –;
умножение *;
деление /;
возведение в степень ^;
процент %.
Порядок убывания приоритетов: унарный минус, процент, возведение в
степень, умножение и деление, сложение и вычитание. Операторы одной
ступени выполняются слева направо.
Операторы сравнения:
равно =;
больше >;
меньше <;
больше или равно >=;
меньше или равно <=;
не равно < >.
Например, ввод =5>3 в ячейку А1 дает:
Адресные операторы (операторы ссылок):
двоеточие – оператор диапазона;
точка с запятой – оператор объединения ссылок.
Стр.3
6
6. Вводим
заданный
угол,
учитывая, что в Excel число π
задается как функция, с круглыми скобками:
7. Нажатие ОК вставляет результат в выбранную ячейку:
Функции, заданные несколькими аналитическими выражениями, вводятся
с помощью встроенной функции ЕСЛИ.
Пример 4. Ввести функцию
Решение:
y xx
xx
= ⎨ +
⎧
2
, при
⎩21, при
Задача 2. Ввести функцию
yx x
xx
= +≤⎨
⎪
⎩
−
при
⎧xx< 2,
⎪
2
,
при
21, при 23
10 ,
≤ ,
> 3.
Полный список встроенных функций, относимых в Excel к
«математическим», имеет вид: ABS, ACOS, ACOSH, ASIN, ASINH, ATAN,
ATAN2, ATANH, COS, COSH, EXP, LN, LOG, LOG10, SIN, SINH, TAN, TANH,
ГРАДУСЫ, ЗНАК, КОРЕНЬ, МОБР, МОПРЕД, МУМНОЖ, НЕЧеТ,
ОКРВВЕРХ, ОКРВНИЗ, ОКРУГЛ, ОКРУГЛВВЕРХ, ОКРУГЛВНИЗ, ОСТАТ,
ОТБР, ПИ, ПРОИЗВЕД, ПРОМЕЖУТОЧНЫЕ ИТОГИ, РАДИАНЫ,
РИМСКОЕ, СЛЧИС, СТЕПЕНЬ, СУММ, СУММЕСЛИ, СУММКВ,
СУММКВРАЗН, СУММПРОИЗВ, СУММРАЗНКВ, СУММСУММКВ, ФАКТР,
ЦЕЛОЕ, ЧеТН, ЧИСЛКОМБ.
Задача 3. Вычислить 22 2
Задача 4. Вычислить
tg435 tg375+
sin 35cos
88 cos8
sin
++ +
.
2 7
8
.
< 2,
≥ 2.
π
ππ
π
Стр.6
7
Задача 5. Вычислить 34sin( 2arctg )23
−
Задача 6. Упростить −log log 2 .
1lg 2
4
22
Задача 7. Упростить 69log 5
log 36
36 10 3
−
+
−
.
Основная особенность приложения Excel, превращающая его в
уникальный вычислительный инструмент, пользующийся заслуженной
популярностью во всем мире, проявляется при копировании формул,
содержащих имена ячеек.
[0,1] с шагом h 0,1=
Пример 5. Составьте таблицу значений функции yx+(2 1)
.
=
2
на отрезке
Решение.
1. Вводим в ячейку А1 начальное значение 0.
2. В ячейку В1 вводим значение, увеличенное на шаг, то есть 0,1.
3. Выделяем обе ячейки.
4. Берем мышкой маркер заполнения рамки выделенного диапазона, когда
он примет вид +, и перемещаем его вправо, пока не появится цифра 1.
Отпуская ЛКМ, получаем в диапазоне А1:K1 значения независимой
переменной.
5. В ячейке А2 задаем формулу.
6. Берем мышкой маркер заполнения рамки ячейки А2 и методом
«протаскивания» маркера заполнения копируем введенную формулу в
остальные ячейки диапазона А2:К2. Все, таблица значений функции
построена:
Если необходимо, чтобы при копировании формула не изменялась, то
перед символами, образующими имена ячеек, входящих в формулу, ставятся
знаки $. В этом случае ссылки на ячейки – абсолютные.
Подбор параметра
В Excel встроены средства приближенного решения алгебраических и
трансцендентных уравнений. Одно из них Подбор параметра - поиск
приближенного значения одной ячейки при заданном значении другой,
ссылающейся на нее. В нем применяется метод последовательных
приближений, результат, вообще говоря, зависит от начального приближения.
Возвращается приближенное значение корня уравнения, ближайшего к
начальному приближению.
Данным инструментом достаточно эффективно решаются уравнения
элементарной математики, содержащие только рациональные корни, так как по
их приближенным значениям обычно нетрудно определить сами значения.
Пример 6. Решить уравнение 2
Решение.
x 23 0
+ x−= .
.
π
Стр.7
8
1. Полагаем x =А1 (но совсем не
обязательно
именно А1).
Начальное приближение – значение ячейки А1 (по умолчанию 0).
2. В некоторую другую выбранную ячейку, например, В1, вводим
формулу левой части уравнения, заменяя x на А1:
3. С помощью пункта меню Сервис открываем диалоговое окно Подбор
параметра и вводим данные:
4. Нажатие ОК запускает вычисления. Результаты появляются в ячейках
А1, В1 и на панели Результаты подбора параметра:
5. Нажатием ОК закрываем панель Результат подбора параметра,
приближенный результат получен и находится в ячейке А1:
6. Проверяем, не является ли
x 1= корень:
x 1= корнем уравнения. Продолжаем
решение. Вводим в ячейку А1 значение 1 и видим (по значению ячейки
В1), что
Замечание. В случае когда визуально трудно определить точное значение
корня, соответствующее полученному приближению, следует воспользоваться
вкладкой Число диалогового окна Формат ячеек.
7. Удаляем содержимое ячейки А1, чтобы избежать сообщения о делении
на ноль.
8. Выделяем ячейку В1 и вносим изменения в формулу уравнения:
9. Снова открываем диалоговое окно Подбор параметра и заполняем его
поля, как раньше. Теперь нажатие ОК дает:
Стр.8