Table Of ContentCopyright ОАО «ЦКБ «БИБКОМ» & ООО «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.
Арифметические операторы:
(cid:190) сложение +;
(cid:190) вычитание –;
(cid:190) унарный минус –;
(cid:190) умножение *;
(cid:190) деление /;
(cid:190) возведение в степень ^;
(cid:190) процент %.
Порядок убывания приоритетов: унарный минус, процент, возведение в
степень, умножение и деление, сложение и вычитание. Операторы одной
ступени выполняются слева направо.
Операторы сравнения:
(cid:190) равно =;
(cid:190) больше >;
(cid:190) меньше <;
(cid:190) больше или равно >=;
(cid:190) меньше или равно <=;
(cid:190) не равно < >.
Например, ввод =5>3 в ячейку А1 дает:
Адресные операторы (операторы ссылок):
(cid:190) двоеточие – оператор диапазона;
(cid:190) точка с запятой – оператор объединения ссылок.
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 = x2 +3x−4.
Решение.
1. Оставляем ячейку А1 за переменной x.
2. Выделяем ячейку В1.
3. Устанавливаем курсор ввода в строке формул, нажимаем = и вводим
аналитическое выражение функции, заменяя x на а1. Ввод
автоматически дублируется в ячейке В1.
4. Щелчок Enter завершает ввод функции.
Можно вводить аналитическое выражение функции и непосредственно в
ячейку В1.
В ячейке b1 значение -4, равное значению функции при 0x = .
Если ввести x в ячейку А1, то в ячейке В1 будет y(x ). Например,
0 0
y(1) =0.
Пример 2. Ввести функцию z = x2 + y2.
Решение:
Под независимые переменные отведем ячейки А1 и В1:
Вычислим, например, z(3,4):
Copyright ОАО «ЦКБ «БИБКОМ» & ООО «Aгентство Kнига-Cервис»
5
Замечание. При вводе функции имена независимых переменных
(ячеек, в которых они находятся) не обязательно набирать с клавиатуры, они
вставляются в строку формул щелчками ЛКМ (левой кнопки мыши) по самим
ячейкам.
В Excel встроена не только библиотека важнейших функций, но и
уникальная система автоматических подсказок, по их конструкциям и
значениям параметров значительно упрощающая работу. Вычисления можно
проводить как через диалоговые окна, вызовом Мастера функций, так и без
них, непосредственным набором формулы функции в ячейку (в строку
формул).
Мастер функций вызывается через пункт меню Вставка→Функция:
Дальнейший порядок работы:
(cid:190) щелчок ЛКМ по выбранной категории открывает в окне Выберите
функцию соответствующий список;
(cid:190) нужная функция выделяется;
(cid:190) нажатие ОК приводит к следующему диалоговому окну Аргументы
функции, назначение которого понятно из названия.
π
Пример 3. Вычислить sin .
6
Решение.
1. Выделяем ячейку, в которую поместим результат, например ячейку А1.
2. Вызываем Мастер функций.
3. В списке категорий выделяем строку Математические.
4. В открывшемся списке Выберите функцию с помощью полосы
прокрутки находим функцию SIN и выделяем ее щелчком ЛКМ.
5. Нажатие ОК приводит к диалоговому окну:
Copyright ОАО «ЦКБ «БИБКОМ» & ООО «Aгентство Kнига-Cервис»
6
6. Вводим заданный угол, учитывая, что в Excel число π
задается как функция, с круглыми скобками:
7. Нажатие ОК вставляет результат в выбранную ячейку:
Функции, заданные несколькими аналитическими выражениями, вводятся
с помощью встроенной функции ЕСЛИ.
⎧ x2, при x< 2,
Пример 4. Ввести функцию y = ⎨
2x+1, при x ≥ 2.
⎩
Решение:
⎧ x2, при x < 2,
⎪
Задача 2. Ввести функцию y = ⎨2x+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,
ГРАДУСЫ, ЗНАК, КОРЕНЬ, МОБР, МОПРЕД, МУМНОЖ, НЕЧеТ,
ОКРВВЕРХ, ОКРВНИЗ, ОКРУГЛ, ОКРУГЛВВЕРХ, ОКРУГЛВНИЗ, ОСТАТ,
ОТБР, ПИ, ПРОИЗВЕД, ПРОМЕЖУТОЧНЫЕ ИТОГИ, РАДИАНЫ,
РИМСКОЕ, СЛЧИС, СТЕПЕНЬ, СУММ, СУММЕСЛИ, СУММКВ,
СУММКВРАЗН, СУММПРОИЗВ, СУММРАЗНКВ, СУММСУММКВ, ФАКТР,
ЦЕЛОЕ, ЧеТН, ЧИСЛКОМБ.
π 3π 5π 7π
Задача 3. Вычислить sin2 +cos2 +sin2 +cos2 .
8 8 8 8
Задача 4. Вычислить tg435(cid:68) + tg375(cid:68).
Copyright ОАО «ЦКБ «БИБКОМ» & ООО «Aгентство Kнига-Cервис»
7
3π 4
Задача 5. Вычислить sin( −2arctg ).
2 3
Задача 6. Упростить −log log 4 2 .
2 2
Задача 7. Упростить 36log65 +101−lg2 −3log936.
Основная особенность приложения Excel, превращающая его в
уникальный вычислительный инструмент, пользующийся заслуженной
популярностью во всем мире, проявляется при копировании формул,
содержащих имена ячеек.
Пример 5. Составьте таблицу значений функции y =(2x+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. Решить уравнение x2 +2x−3=0.
Решение.
Copyright ОАО «ЦКБ «БИБКОМ» & ООО «Aгентство Kнига-Cервис»
8
1. Полагаем x =А1 (но совсем не обязательно именно А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
Пример 8. Решить уравнение + x x + x = 22.
x
Решение. Ход решения как в предыдущей задаче, только, учитывая, что
x =0 не входит в ОДЗ (область допустимых значений) уравнения, в ячейку А1
помещаем значение 3. Результат подбора параметра на 1-м шаге:
Проверкой убеждаемся, что x = 4 - корень уравнения. Подбор параметра на
2-м шаге:
Проверкой убеждаемся, что x =1 - тоже корень уравнения. Продолжая
решение, приходим к тому, что других действительных корней уравнение не
имеет.
Ответ: 1, 4.
Задача 8. Решить уравнение lg5+lg(x+10)=1−lg(2x−1)+lg(21x−20).
3x2−10x+3
Задача 9. Решить уравнение x−3 =1.
Поиск решения
Надстройка Поиск решения - мощнейший вычислительный инструмент
приложения Excel. Если в списке меню Сервис он не указан, то необходимо
щелкнуть ЛКМ в нем по строке Надстройки и поставить галочку в строке
Поиск решения.
Пример 9. Решить уравнение x2 +2x−3=0.
Решение:
1. Принимаем x =А1.
2. В ячейку В1 вводим формулу левой части уравнения.
3. Открываем диалоговое окно Поиск решения и задаем сценарий
решения:
