- •Решение задач средствами ms Excel
- •300700.65 Прикладная информатика в экономике
- •4.3. Варианты задания 43
- •4.4. Технология выполнения работы 44
- •6.2. Содержание работы 63
- •6.3. Варианты задания 66
- •8.2. Содержание работы 84
- •8.3. Варианты задания 85
- •Введение
- •1. Основные возможности и терминология ms excel
- •1.1. Назначение и функциональные возможности электронных таблиц
- •1.2. Основные элементы окна ms Excel
- •1.3. Структура электронных таблиц
- •1.4. Способы адресации ячеек
- •1.5. Ввод и редактирование данных
- •1.6. Конструирование формул и управление вычислениями
- •1.7. Функции рабочего листа
- •Математические (арифметические и тригонометрические) функции
- •Статистические функции
- •Логические функции
- •Функции ссылок
- •1.8. Возможные ошибки при использовании функций в формулах
- •2. Лабораторная работа № 1 «использование функций рабочего листа»
- •2.1. Создание новой рабочей книги
- •2.2. Ввод стандартных функций
- •2.3. Применение математических функций
- •Варианты задания
- •2.4. Применение стандартных функций с несколькими аргументами
- •Пример вычисления суммы ряда (вариант № 30)
- •Варианты задания
- •3. Лабораторная работа № 2 «аппроксимация функции»
- •3.1. Технология выполнения работы
- •3.2. Варианты задания
- •4. Лабораторная работа № 3 «табулирование функции»
- •4.1. Содержание работы
- •4.2. Анализ области определения функции
- •4.3. Варианты задания
- •4.4. Технология выполнения работы
- •Ввод исходных данных
- •Конструирование таблицы и построение графика
- •Форматирование рабочего листа
- •4.5. Оформление отчёта
- •Создание документа
- •Структура документа
- •5. Лабораторная работа № 4 «решение нелинейных уравнений и поиск экстремумов функции одной переменной»
- •5.1. Решение нелинейных уравнений
- •Подбор параметра
- •Пример оформления на рабочем листе
- •Циклические ссылки
- •Пример оформления на рабочем листе
- •Поиск решения
- •Пример оформления на рабочем листе
- •Варианты задания
- •5.2. Поиск экстремумов функции одной переменной
- •Поиск решения
- •Варианты задания
- •Пример оформления на рабочем листе
- •6. Лабораторная работа № 5 «решение обыкновенных дифференциальных уравнений»
- •6.1. Сущность и методы решения обыкновенных дифференциальных уравнений
- •6.2. Содержание работы
- •6.3. Варианты задания
- •7. Лабораторная работа № 6 «решение систем линейных алгебраических уравнений»
- •7.1. Системы линейных алгебраических уравнений
- •7.2. Табличные формулы и операции с матрицами
- •7.3. Решение линейных алгебраических систем
- •Группировка рабочих листов
- •Метод Крамера
- •Матричный способ решения
- •Поиск решения
- •7.4. Варианты задания
- •8. Лабораторная работа № 7 «простейшая база данных»
- •8.1. Создание списка
- •Пример структуры списка
- •8.2. Содержание работы
- •8.3. Варианты задания
- •8.4. Обработка списка
- •Сортировка
- •Запросы и фильтрация
- •Автофильтр
- •Список рекомендуемой литературы
- •Информатика Решение задач средствами ms Excel
Подбор параметра
При подборе параметра Excel использует итерационный (циклический) процесс. Количество итераций и точность (относительная погрешность) устанавливаются следующей последовательностью команд:
-
щёлкнуть мышью по кнопке меню Сервис;
-
в раскрывшемся меню щёлкнуть по строке Параметры…;
-
в появившемся диалоговом окне Параметры щёлкнуть мышью по вкладке Вычисления, где и установить значения Предельного числа итераций и Относительной погрешности;
-
щёлкнуть по кнопке ОK.
При подборе параметра Excel изменяет значение аргумента функции в одной конкретной ячейке до тех пор, пока значения функции, вычисляемые по формуле, ссылающейся на эту ячейку, не станут соответствовать установленным параметрам вычислений.
Уточнение корня уравнения этим способом сводится к следующим действиям.
-
Заданное уравнение преобразовать к виду f(x)=0. Левая часть уравнения и будет той функцией, нуль которой необходимо найти. Например, задано уравнение tg(x)=1/x. Приводим его к виду x×tg(x)=1, переносим единицу в левую часть уравнения и получаем x×tg(x)-1=0. Тогда функция, нуль которой предстоит найти, имеет вид f(x)=x×tg(x)-1.
-
В выбранную ячейку рабочего листа (например, B5) ввести текст x=.
-
В соседнюю справа ячейку (например, в ячейку C5) ввести любое начальное приближение к корню из заданного отрезка (можно использовать значение левой или правой границы).
-
В ячейку строкой ниже (например, B6) ввести текст f(x)=.
-
В соседнюю ячейку (справа от предыдущей) ввести выражение для вычисления значений функции, в качестве которой использовать левую часть уравнения (в нашем случае в ячейку C6 ввести формулу =C5*TAN(C5)-1). Ссылка в формуле вводится щелчком мыши по ячейке с начальным значением аргумента, то есть по ячейке C5.
-
Щёлкнуть мышью по ячейке с формулой для вычисления значений функции (C6).
-
Щёлкнуть мышью по строке меню Сервис.
-
В раскрывшемся меню щёлкнуть по строке Подбор параметра….
-
В появившемся диалоговом окне Подбор параметра удалить адрес текущей ячейки в окне Установить в ячейке:, если он не соответствует адресу ячейки с выражением для вычисления значений функции, и щёлкнуть мышью по ячейке с формулой (C6), в окно Значение: ввести 0 (нуль). Щелкнуть мышью в окне Изменяя значение ячейки:, а затем щёлкнуть мышью по ячейке со значением x (C5).
-
Щёлкнуть мышкой по кнопке ОK. Результат получен.
Пример оформления на рабочем листе
|
B |
С |
4 |
|
|
5 |
x= |
0,5 |
6 |
f(x)= |
=C5*TAN(C5)-1 |
Циклические ссылки
Если в ячейку рабочего листа введена формула, содержащая ссылку на эту же ячейку (может быть и не напрямую, а опосредованно – через цепочку других ссылок), то говорят, что имеет место циклическая ссылка (цикл). Именно вычисление по формулам с циклическими ссылками и использовано в рассматриваемом способе решения нелинейных уравнений. При этом число повторений цикла будет конечным.
Для включения режима циклических вычислений надо выполнить следующие действия:
-
щёлкнуть мышью по кнопке меню Сервис;
-
в раскрывшемся меню щёлкнуть мышью по строке Параметры…;
-
в диалоговом окне Параметры щёлкнуть мышью по вкладке Вычисления;
-
поставить флажок в окне итерации;
-
установить вариант вычислений автоматически;
-
щелкнуть мышью по кнопке ОK.
Уточнение корня уравнения этим способом сводится к следующим действиям.
-
Найти первую производную от функции f(x) из уравнения f(x)=0. В нашем примере производная от функции f(x)=x×tg(x)-1 будет иметь вид f /(x)=tg(x)+x/cos2(x).
-
В ячейку (например, F5) ввести текст Xнач=.
-
В ячейку ниже (F6) ввести текст x=.
-
В следующую ниже ячейку (F7) ввести текст f(x)=.
-
В ячейку G5 ввести начальное приближение x (число) к искомому корню. В качестве такого приближения можно использовать середину (левую или правую границу) заданного отрезка.
-
В ячейку G6 ввести рекуррентную формулу, задающую вычисление очередного приближения к корню по методу Ньютона . При этом необходимо использовать логическую функцию ЕСЛИ(). Применение этой функции позволит запустить итерационный процесс с начального приближения, записанного в G5. Таким образом, формула, которую надо ввести в ячейку G6, будет иметь вид: =ЕСЛИ(G6=0;G5;G6-(G6*TAN(G6)-1)/ (TAN(G6)+G6/COS(G6)^2)).
-
В ячейку G7 ввести выражение для вычисления значений функции из уравнения. Искомый результат (корень) будет получен в ячейке G6.
Для изменения начального приближения Xнач на другое необходимо выполнить следующие действия.
-
Из ячейки со значением Xнач (G5) удалить прежнее значение и ввести новое.
-
Дважды щёлкнуть мышью (перейти в режим редактирования содержимого) по ячейке с формулой (G6). После чего нажать клавишу Enter. Это приведёт к обнулению прежнего результата и повторному запуску итерационного процесса.