В помощь школьнику / Игры / Квадратное уравнение в Excel. Старт в науке Поиск решения в excel покупка школьных принадлежностей

Квадратное уравнение в Excel. Старт в науке Поиск решения в excel покупка школьных принадлежностей

24.02.2024

Цель урока : продолжить формирование навыков работы с электронными таблицами.

обучающие:

развивающие:

воспитательные:

План урока.

Организационный момент.
Актуализация знаний учащихся.
Проверка домашнего задания.
Решение задач.
Самостоятельное решение задачи.
Подведение итогов. Оценки.
Домашнее задание.

Ход урока

1. Организационный момент.

Сообщить тему урока, сформулировать цели и задачи урока.

Сегодня мы вновь окажемся в гостях у маленького великана Васи в Сказочной стране. Ему, как всегда, требуется ваша помощь, ребята.

Сможете ли вы помочь Васе? Сейчас проверим!

2. Актуализация знаний учащихся.

1) Устно ответить на вопросы.

	A	B	C	D
1	2	1	=A1+3*B1	=A1^2+B1
2	4	6	=A2+3*B2	=A2^2+B2

Что такое электронная таблица?
Какие основные элементы электронной таблицы вам известны?
Как задается имя ячейки (строки, столбца) в электронной таблице?
Что может быть содержимым ячейки?
Число 1 находится в столбце..., в строке..., в ячейке с адресом...
Число 4 находится в ячейке с адресом...
Каковы правила записи формул в ячейках?
Чему равно значение, вычисляемое по формуле, в ячейке С1?
Чему равно значение, вычисляемое по формуле, в ячейке D2?

2) Какой результат будет получен в ячейках с формулами?

	А	В
1	25	4
2	2	=A1*B1/2
3

Ответ: 25*4/2=50

	A	B	C	D
1		5	2	1
2		6	8	3
3		8	3	4
4				=СУММ(B1:D3)

Что означает запись =СУММ(В1:D3)?
Сколько элементов содержит блок В1:D3? Ответ: 9.
Содержимое ячейки D3? Ответ: 5+2+1+6+8+3+8+3+4= 40

3) Проверка домашнего задания

Результаты соревнований по плаванию

Один ученик рассказывает, как он выполнил домашнее задание (через проектор).

№	Ф.И.О.	1	2	3	Лучшее время	Среднее время	Отклонение
1	Лягушкин	3.23	3.44	3.30
2	Моржов	3.21	3.22	3.24
3	Акулов	3.17	3.16	3.18
4	Рыбин	3.24	3.20	3.18
5	Черепахин	3.56	3.44	3.52



	Максимальное отклонение

Среднее время для каждого спортсмена находится как среднее арифметическое трех его заплывов.
В ячейку "Лучшее время" записывается минимальный результат из 3 заплывов.
В ячейку "Лучший результат соревнований" записывается минимальное время из столбца.
В столбец "Отклонение" записывается разность между лучшим временем спортсмена и лучшим результатом соревнований.
В ячейку "Максимальное отклонение" записывается максимальное значение столбца.

Результаты соревнований по плаванию
№	Ф.И.О.	1	2	3	Лучшее время	Среднее время	Отклонение
1	Лягушкин	3,23	3,44	3,30	3,23	3,32	0,07
2	Моржов	3,21	3,22	3,24	3,21	3,22	0,05
3	Акулов	3,17	3,16	3,18	3,16	3,17	0,00
4	Рыбин	3,24	3,20	3,18	3,18	3,21	0,02
5	Черепахин	3,56	3,44	3,52	3,44	3,51	0,28
	Лучший результат соревнований					3,16
	Среднее время участников соревнований					3,29
	Максимальное отклонение					0,28

4) Решение простых задач.

Маленький великан Вася решил отремонтировать забор вокруг своего огорода и вскопать его под посадку овощей (наступила очередная весна), разметить грядки прямоугольной формы. Для работы ему потребовалось найти длину забора и площадь участка. Но ведь в школе он никогда не учился. Поможем Васе.

№ 1. Вычислить периметр и площадь прямоугольника со сторонами:

а) 3 и 5; б) 6 и 8; в) 10 и 7.

Эту задачу обсуждаем совместно с детьми:

Как оформить таблицу?
Какие формулы использовать?
Как использовать уже записанные формулы для следующего прямоугольника?

Оформление таблицы – на доске и в тетрадях.

В то же время другой ученик самостоятельно решает следующую задачу и представляет свое решение учащимся (через проектор).

Обсудив решение задачи № 2, переходим к решению следующей.

Один ученик показывает, как работать с формулами, другой – как использовать функцию суммирования, числовой формат (общий, денежный) и т.д. (Таблица уже готова, ученикам предстоит ввести формулы, использовать суммирование и получить ответ).

№ 3. Посчитайте, используя ЭТ, хватит ли Васе 150 рублей, чтобы купить все продукты, которые ему заказала мама, и хватит ли на чипсы за 10 рублей? Сдачу мама разрешила положить в копилку. Сколько рублей попадет в копилку?

Предполагаемое решение:

№	Наименование	Цена в рублях	Количество	Стоимость
1	Хлеб	9,6	2	=C2*D2
2	Кофе	2,5	5	=C3*D3
3	Молоко	13,8	2	=C4*D4
4	Пельмени	51,3	1	=C5*D5
				=СУММ(E2:E5)
После покупок останется			=150-E6
После покупки чипсов останется			=D7-10

5) Самостоятельное решение задачи.

Маленький великан Вася часто бывал в гостях у жителей Цветочного города.

Собираясь на пляж, веселые человечки решили запастись прохладительными напитками. Незнайка взял с собой 2 литра кваса, 1 литр газировки и 1 литр малинового сиропа, Пончик – 3 литра газировки и 2 литра малинового сиропа, Торопыжка – 2 литра газировки, доктор Пилюлькин – 1 литр кваса и 1 литр касторки.

Сколько литров напитков каждого вида взяли все человечки вместе?
Сколько всего литров напитков взял с собой каждый из человечков?
Сколько всего литров напитков взяли все человечки вместе?

Оформите таблицу произвольно и сохраните в своей личной папке.

Результат работы.

Веселые человечки. Напитки.
Напиток	Незнайка	Пончик	Торопыжка	Пилюлькин	Всего
Квас, л	2	0	0	1	3
Газировка, л	1	3	2	0	6
Сироп, л	1	2	0	0	9
Касторка, л	0	0	0	1	1
ИТОГО:	4	5	2	2	13

7) Подведение итогов. Оценки.

8) Домашнее задание.

Подумайте и решите эту задачу, если известны еще следующие величины.

Как изменится таблица? Какие формулы появятся?

Известно, что 1 литр кваса в Цветочном городе стоит 1 монету, 1 литр газировки – 3 монеты, 1 литр малинового сиропа – 6 монет, 1 литр касторки – 2 монеты.

Сколько монет истратил на покупку напитков каждый человечек?
Сколько монет затрачено на покупку напитков каждого вида?
Сколько потрачено денег всеми человечками вместе?

Литература

Информатика. Задачник-практикум в 2 т. /Под ред. И.Г.Семакина, Е.К.Хеннера – М.: Лаборатория Базовых Знаний, 2010.
Ефимова О. Курс компьютерной технологии с основами информатики. – М.: ООО “издательство АСТ”; АВF, 2005.

Текст работы размещён без изображений и формул.
Полная версия работы доступна во вкладке "Файлы работы" в формате PDF

ВВЕДЕНИЕ

Постановка проблемы и актуальность исследования . Школьный курс математики, начиная с начальной ступени и до 11 класса, включает в себя большое количество способов решения различных видов уравнений и систем уравнений. Некоторые уравнения решаются нестандартными методами, которые применить может небольшая часть выпускников школ. Анализ изученной литературы показал, что уравнения и системы уравнений встречаются в различных отраслях промышленности и экономики. И как правило, эти уравнения выглядят не так привлекательно, как школьные, и имеют нецелые решения. Чтобы автоматизировать процесс решения уравнений и систем уравнений, мы решили найти способы с помощью электронных таблиц. Электронные таблицы широко используются в профессиональной деятельности специалистов самых разных областей науки, производства и сферы услуг, в различных государственных и коммерческих организациях и фирмах. Кроме того, электронные таблицы могут быть использованы при решении бытовых задач, таких, как создание домашней картотеки книг или компакт-дисков, ведение учета коммунальных платежей или домашнего бюджета и т.п.

К настоящему времени существует достаточное количество различных учебных материалов, где подробно раскрываются способы решения производственных задач с помощью уравнений и систем уравнений , а также методы их решения с помощью электронных таблиц .

Тем не менее, в ходе исследования было обнаружено, что недостаточно исследованы способы решения уравнений высших степеней, а также уравнений, имеющих бесконечное множество решений (например, тригонометрических).

Актуальность обозначенной проблемы определила выбор темы исследования: «Решение уравнений средствами приложения Microsoft Excel».

Цель работы : исследовать инструменты приложения Microsoft Excel для решения уравнений разного порядка.

Объект исследования : приложение Microsoft Excel.

Предмет исследования : применение инструментов ПОДБОР ПАРАМЕТРА и ПОИСК РЕШЕНИЯ приложения Microsoft Excel при решении уравнений.

Гипотеза исследования: использование инструментов приложения MS Excel ПОДБОР ПАРАМЕТРА и ПОИСК РЕШЕНИЯ значительно упрощает процесс решения уравнений различного вида.

Задачи исследования :

Изучить литературу по применению уравнений при решении производственных задач.

Изучить литературу по использованию приложения Microsoft Excel в практической деятельности.

Рассмотреть способы решения уравнений с помощью инструментов ПОДБОР ПАРАМЕТРА и ПОИСК РЕШЕНИЯ приложения Microsoft Excel.

Создать видеокурсы по решению различных видов уравнений.

Теоретическая значимость : проведен анализ ряда источников по возможностям приложения Microsoft Excel при решении уравнений разного порядка.

Практическая значимость : предложены способы решения уравнений высших порядков и тригонометрических уравнений с помощью приложения MS Excel, систематизирован и обобщен материал в форме видеокурсов.

Методы исследования : теоретический анализ и обобщение научной литературы и материалов сети Интернет; проведение экспериментов по решению уравнений различного вида с помощью инструментов Подбор параметра и Поиск решения; создание видеокурсов по использованию инструментов Подбор параметра и Поиск решения при решении различных уравнений.

УРАВНЕНИЯ В РАЗЛИЧНЫХ ОТРАСЛЯХ

В современном обществе уравнения нашли свое применение во многих отраслях хозяйства и производства, а также практически во всех новейших технологиях. Конечно, математика, как и любая другая наука, не стоит на месте. Уже выработано достаточно способов решения различных видов уравнений различных степеней. Появление компьютеров и стремительное развитие информационных технологий позволило в несколько раз упростить задачи нахождения корней различных уравнений. В данной главе, в качестве примеров, нами представлены виды уравнений, решаемых в некоторых отраслях хозяйства и производства.

1.1. Уравнения при решении экономических задач

Пример 1.1.1. Рассчитать, с какого возраста необходимо уплачивать по 1000 рублей в качестве дополнительных страховых взносов, чтобы получить прибавку к пенсии в 2000 рублей за счет участия в государственной программе софинансирования?

Входные данные:

ежемесячные отчисления - 1000 руб.;

период уплаты дополнительных страховых взносов - расчетная величина (пенсионный возраст (в примере - для мужчины) минус возраст участника программы на момент вступления);

пенсионные накопления - расчетная величина (накопленная за период участником сумма, увеличенная государством в 2 раза;

ожидаемый период выплаты трудовой пенсии - 228 мес. (19 лет);

желаемая прибавка к пенсии - 2000 руб.

пенсионные накопления - расчетная величина (накопленная за период участником сумма, увеличенная государством в 2 раза).

Пусть х - возраст, с которого необходимо производить отчисления. Тогда прибавка к пенсии (в размере 2000 рублей) будет рассчитана по формуле:

Получили линейное уравнение, в котором необходимо найти параметр x .

Пример 1.1.2. Пусть дана структура цены договора: собственные расходы, прибыль, НДС. Известно, что собственные расходы составляют 150 000,00 руб., НДС 18%, а целевая стоимость договора 200 000,00 руб. Необходимо подобрать такое значение прибыли, при которой стоимость договора равна Целевой (то есть Расхождение должно равняться нулю) .

Пусть х - прибыль. Тогда цену продукции будем рассчитывать как сумму Собственных расходов и Прибыли: 150000+х. НДС от цены продукции будет равен (150000+х)*0,18. Стоимость договора вычислим как сумму Цены продукции и НДС: (150000+х)+ (150000+х)*0,18=(150000+х)*1,18.

Итак, получили уравнение (150000+х)*1,18=2000.

Пример 1.1.3. , решение которого также сводится к линейному уравнению. Определить максимальную сумму кредита, которую мы можем себе позволить взять в банке, если известно, что ежемесячно мы можем выплачивать сумму в размере 1 800,00 руб. Известны также процентная ставка по кредиту и срок, на который мы хотим взять кредит (количество месяцев) .

Пример 1.1.4 , решение которого сводится к системе линейных уравнений. Предприятию для изготовления наборов елочных украшений необходимо изготовить их составные части - шар, колокольчик, мишура .

В свою очередь для изготовления этих составных частей необходимы три вида сырья - стекло (в г), папье-маше (в г), фольга (в г), потребности в котором отражены в таблице.

Требуется:

1) определить потребности в сырье для выполнения плана по изготовлению комплектов первого, второго, третьего и четвертого вида в количестве соответственно x 1 , x 2, x 3 и x 4 штук;

2) провести подсчеты для значений x 1 = 500, x 2 = 400, x 3 = 300 и x 4 =200.

Для решения данной задачи необходимо найти корни системы линейных уравнений:

y 1 = 5· (5x 1 + 6x 2 + 8x 3 + 10x 4) = 25x 1 + 30x 2 + 40x 3 + 50x 4

y 2 = 4· (3x 1 + 4x 2 + 6x 3) = 12x 1 + 16x 2 + 24x 3

y 3 = 3· (5x 1 + 6x 2 + 8x 3 + 10x 4) + 75· (3x 2 + 5x 3 + 8x 4) = 15x 1 + 243x 2 + 399x 3 + 630x 4

Уравнения в электроэнергетике

Рассмотрим применение уравнений в электроэнергетике .

Пример 1.2.1. Приведена схема электрической цепи постоянного тока. Найти токи в ветвях цепи.

Для решения данной задачи необходимо составить и решить систему линейных уравнений на основе законов Кирхгофа (здесь не рассматривается процесс составления системы уравнений):

Уравнения в транспортной отрасли

Пример 1.3.1. Для решения задач проектирования транспортных сооружений и принятия обоснованных решений при планировании, контроле и управлении технологическими процессами дорожного строительства необходимо выявлять взаимосвязи между параметрами, определяющими ход этих процессов, и представлять их в количественной форме - в виде математических моделей. В связи с этим на практике часто применяется регрессионный анализ.

Регрессионный анализ - метод моделирования измеряемых данных и исследования их свойств путем выявления взаимосвязи между зависимой переменной y и одной или несколькими независимыми переменными x 1, x 2, ..., xn.

Независимые переменные иначе называют факторами, аргументами, или регрессорами , а зависимые переменные - функциями, откликами, результирующими, объясняемыми.

На практике уравнение регрессии чаще всего подбирается в виде линейной и нелинейной функции (наиболее простые - гипербола, экспонента и парабола) .

Пример 1.3.2. Транспортная задача

Требуется составить план перевозок, при котором все запасы (строительных материалов или конструкций) поставщиков (АБЗ, ЦБЗ, карьеры) будут вывезены, спрос потребителей (объекты дорожных работ, участки) полностью удовлетворен, и при этом суммарные транспортные издержки будут минимальными (стоимость перевозок, сроки, другие ресурсы).

При решении данной задачи составляется система линейных уравнений относительно xij - количества груза (материалов), перевозимого из пункта i в пункт j .

Уравнения в строительной отрасли

Пример 1.4.1. Вычислить стрелу прогиба  (в середине) прямоугольной пластины. Прямоугольная пластина загружена равномерно распределенной нагрузкой интенсивностью q. Пластина защемлена по контуру, края неподвижны .

Стрела прогиба вычисляется как корень нелинейного уравнения на интервале :

Пример 1.4.2. Определить критическую силу для стальной колонны двутаврового сечения, если известны длина колонны L, модуль упругости стали Е, коэффициент жесткости упругой опоры С, момент инерции I.

Критическая сила вычисляется по формуле:

где  - коэффициент приведения длины колонны, который определяется по формуле

Параметр  находится из решения уравнения

на интервале .

ИСПОЛЬЗОВАНИЕ ИНСТРУМЕНТА ПОДБОР ПАРАМЕТРА ПРИ РЕШЕНИИ УРАВНЕНИЙ

При решении производственных задач достаточно часто возникает проблема подбора параметра. Например, в экономических расчётах применяются алгоритмы расчёта стоимости товара, расчёта фонда заработной платы, прибыли от деятельности предприятия, которые, в свою очередь, зависят от множества изменяемых и неизменяемых факторов .

Пример 2.1. Итак, сначала, с целью изучения принципа работы надстройки ПОДБОР ПАРАМЕТРА, рассмотрим решение линейного уравнения вида Ах+В=С с помощью приложения Microsoft Excel.

В ячейку В3 введем любое первоначальное значение переменной x , например, 0, а в ячейку С1 введем левую часть уравнения в виде формулы: =B1*B3+B2. Вызовем диалоговое окно ПОДБОР ПАРАМЕТРА с помощью команд Данные - Анализ «что-если» - Подбор параметра . В этом окне в поле Установить в ячейке введем ссылку на ячейку с формулой, в поле Значение - ожидаемый результат (т.е. 7), в поле Изменяя значение в ячейке - ссылку на ячейку, в которой будет храниться значение подбираемого параметра (содержимое этой ячейки не может быть формулой).

Рисунок 1 - Диалоговое окно ПОДБОР ПАРМЕТРА

После нажатия кнопки ОК , получим результат.

Рисунок 2 - Решение линейного уравнения с помощью диалогового окна ПОДБОР ПАРАМЕТРА

Известно, что инструмент Подбор параметра в основном используется при решении линейного уравнения. Если пытаться, например, решать с помощью Подбора параметра квадратное уравнение (которое имеет два корня), то инструмент найдет решение, но только одно, то, которое ближе к начальному значению.

Пример 2.2. Рассмотрим пример решения квадратного уравнения. Найдем корни квадратного уравнения. Сначала создадим первоначальную таблицу.

Рисунок 3 - Первоначальные данные квадратного уравнения

Зададим любое первоначальное значение х, например, 0. Далее воспользуемся инструментом ПОДБОР ПАРАМЕТРА .

Получили результат: 2.

Второй корень найдем, задав другое начальное значение, например, 5. И проделаем те же действия.

ИСПОЛЬЗОВАНИЕ НАДСТРОЙКИ ПОИСК РЕШЕНИЯ ПРИ РЕШЕНИИ УРАВНЕНИЙ

Пример 3.1. Рассмотрим решение квадратного уравнения (с предыдущей главы) с помощью инструмента ПОИСК РЕШЕНИЯ.

Введем начальные данные

Рисунок 4 - Первоначальные данные квадратного уравнения

Вызываем инструмент ПОИК РЕШЕНИЯ, выбрав команду ДАННЫЕ.

Рисунок 5 - Надстройка ПОИСК РЕШЕНИЯ при решении квадратного уравнения

В поле «Установить целевую ячейку» выбираем ячейку с формулой квадратного уравнения С1. Далее установим переключатель в положение «Равной значению 0». В поле «Изменяя ячейки» добавим ячейку В4. Нажмем кнопку «Выполнить». Получили решение.

Рисунок 6 - Решение квадратного уравнения, найденного с помощью надстройки ПОИСК РЕШЕНИЯ

При решении данным способом также получили только один корень.

Для нахождения второго корня зададим другое начальное значение переменной х, например, равно 1.

Однако, на любом производстве чаще всего приходится сталкиваться с уравнениями высших степеней .

Пример 3.2. Рассмотрим уравнение пятой степени -3x 5 +x 3 +2x 2 -3x-3=0.

Прежде чем находить корни уравнения (а у этого уравнения должно быть максимум 5 корней), выясним, в каких интервалах содержатся эти корни. Воспользуемся графиком функции, с помощью которого мы наглядно увидим промежутки расположения корней уравнения.

Построим график функции. Для этого в ячейке А1 введем «х», в ячейке В1 введем «у». Значения х внесем в ячейки А2:А22, значения у будем рассчитывать в ячейках В2:В22 соответственно.

Рисунок 7 - Формула уравнения пятой степени

Известно, что корень уравнения (уравнение записано в виде f(x )=0) - это такое значение аргумента, при котором значение функции равно нулю. В графическом представлении - это может быть точка пересечения или касания графика функции с осью абсцисс.

Построим график функции.

Рисунок 8 - График функции на промежутке [-10; 10] с шагом 1

График функции показывает, что уравнение, имеет единственный действительный корень (остальные - комплексные), который находится в промежутке [-1; 0].

Найдем его с помощью инструмента ПОИСК РЕШЕНИЯ. Для этого в таблице выберем точку, близкую к решению уравнения, например, -0,7.

Рисунок 9 - Нахождение корня уравнения с помощью надстройки

ПОИСК РЕШЕНИЯ

Установим с помощью команды «Формат ячеек» относительную погрешность 0,0001.

Итак, решением уравнения является х≈ -0,668.

Таким образом, получили алгоритм решения уравнения высшей степени:

поиск интервалов, в которых содержится только по одному корню;

уточнение корня в выбранном интервале (определением значения корня с заданной точностью).

Тригонометрические уравнения

Особенность тригонометрических уравнений заключается в том, что они имеют бесконечно много решений, и все решения отличаются между собой на определенный период.

Пример решения одного из тригонометрических уравнений подробно рассмотрен в Приложении 1.

В Приложении 2 также рассмотрен пример нахождения решений системы линейных уравнений.

ЗАКЛЮЧЕНИЕ

В результате проведенной исследовательской работы было выявлено, что решение различных уравнений и систем уравнений применяется во многих отраслях экономики и промышленности.

В ходе исследований мы научились находить корни уравнений и систем линейных уравнений с помощью инструментов ПОИСК РЕШЕНИЯ и ПОДБОР ПАРАМЕТРА приложения Microsoft Excel, создали видеокурсы по решению уравнений с помощью приложения Microsoft Excel.

Таким образом, поставленные цель и задачи данного исследования были выполнены.

Кроме того, экспериментальным путём, было выявлено, что использование инструментов ПОИСК РЕШЕНИЯ и ПОДБОР ПАРАМЕТРА приложения Microsoft Excel значительно упрощает процесс нахождения корней уравнений и систем уравнений. Таким образом, поставленная в начале исследования гипотеза подтвердилась.

Результаты выполненной работы позволят использовать возможности изученных инструментов в будущей профессиональной деятельности, особенно если выполнение задания будет содержать сложные расчеты.

Исследование может быть полезно не только учащимся в учебной деятельности, но и специалистам различных отраслей экономики и промышленности, занимающимся проектированием объектов.

Результаты проведенной работы могут быть использованы при изучении других возможностей приложения Microsoft Excel.

На этом исследование не закончено. Мы планируем продолжить рассмотреть способы решения систем нелинейных уравнений с помощью Microsoft Excel.

СПИСОК ИСПОЛЬЗОВАННЫХ ИСТОЧНИКОВ И ЛИТЕРАТУРЫ:

Богомолов, С.В. Экономико-математические методы проектирования транспортных сооружений [Электронный ресурс] : методические указания к практическим занятиям и самостоятельной работе для студентов специальности 270205 «Автомобильные дороги и аэродромы» всех форм обучения / С.В. Богомолов. - Электрон. дан. - Кемерово: КуГТУ, 2013. - 30 с.

Информатика для экономистов. Практикум: учебное пособие для бакалавров / под ред. В.П. Полякова, В.П. Косарева. - М.: Издательство Юрайт, 2013. - 343 с.

Митрофанов, С.В. Использование системы MathCAD при решении задач электротехники и электромеханики: методические указания к выполнению РГЗ по дисциплине «Прикладные задачи программирования» / С.В. Митрофанов, А.С. Падеев. - Оренбург: ГОУ ОГУ, 2005. - 40 с.

Репкин, Д.А. Применение MS EXCEL для решения прикладных задач в экономике: учебное пособие для студентов направления 080100 «Экономика» всех профилей подготовки, всех форм обучения / Д.А. Репкин. - Киров: ПРИП ФГБОУ ВПО «ВятГУ», 2012. [Электронный ресурс]

Федулов, С.В. Использование MS Excel в финансовых вычислениях: учеб.-метод. пособие / С.В. Федулов. - Екатеринбург: Изд-во УрГУПС, 2013. - 94 с.

Численные методы. Часть 1: Методические указания к лабораторным и самостоятельным работам по курсам «Информатика» и «Вычислительная математика» / Сост. Ф.Г. Ахмадиев, Ф.Г. Габбасов, Р.Ф. Гизяятов, И.В. Маланичев. - Казань: Изд-во казан. гос. архитект-строит. ун-та, 2013 - 34 с.

Решение нелинейных уравнений в Excel https://www.altstu.ru/media/f/lr3nelin-uravn.pdf - сайт Алтайского государственного технического университета им. И.И. Ползунова

http://excel2.ru/articles/podbor-parametra-v-ms-excel - сайт Excel2.ru

https://knowledge.allbest.ru/mathematics/3c0b65625b3ad68b4c43a89421306d37_0.html - сайт allbest

Приложение 1

Решение тригонометрического уравнения с помощью инструмента ПОИСК РЕШЕНИЯ

Найдем решения уравнения.

Решать данное уравнение будем аналогично примеру 3.1. То есть:

Протабулируем функцию и построим ее график;

Уточним корни уравнения.

Протабулируем функцию на промежутке [-10; 10]. Сначала в ячейках А2:А22 зададим значения аргумента x и найдем значения функции в данных точках, которые запишем в ячейки В2:В22.

В ячейке В2 укажем формулу: =A2*TAN(A2)-1

Рисунок 1 - Таблица значений аргумента и функции

на отрезке [-10; 10] с шагом 1

Построим график функции на данном отрезке.

Рисунок 2 - График заданной тригонометрической фнкции

Проанализировав график и таблицу значений функции, видим, что корни уравнения расположены в промежутках (-10; -9), (-7; -6); (-4; -3) и т.п., то есть на тех промежутках, где функция меняет знак и пересекает ось Ох.

Уточним первый корень уравнения. Для этого установим курсор в ячейке В2 и вызовем инструмент ПОИСК РЕШЕНИЯ.

Рисунок 3 - Надстройка ПОИСК РЕШЕНИЯ

Итак, первый корень получен.

Рисунок 4 - Решение тригонометрического уравнения

Аналогично, найдем корень уравнения, задав начальное значение х=-7 и х= -4.

Рисунок 5 - Три корня тригонометрического уравнения

Учитывая, что период функции тангенса равен π, найдем разницу между корнями уравнения: получили 3,04 и 3,01. Итак, разность между корнями равна примерно 3. Следовательно, следующие корни уравнения: - 0,4; 2,6; и т.п.

Таким образом, для нахождения корней тригонометрического уравнения, необходимо проделать те же действия, что и при решении уравнений высших степеней.

Приложение 2

Использование инструмента ПОИСК РЕШЕНИЯ при решении систем линейных уравнений

С помощью инструмента ПОИСК РЕШЕНИЯ можно решить и систему линейных уравнений .

Пример 4.1. Решим следующую систему линейных уравнений

Для этого зададим ячейки, где будут записаны решения системы уравнений. Пусть это будут ячейки A2:D2.

Рисунок 1 - Создание таблицы для решения системы линейных уравнений

Введем в ячейки, предназначенные для решения (А2:D2) произвольные величины, лежащие в области определения (начальные значения).

В ячейки (А3:D3) внесем формулы, по которым должны вычисляться правые части уравнений: (=8*A2+4*B2-6*C2; =-2*А2-4*С2-6*D2; =6*А2+4*В2+4*С2+6*D2; = 4*А2+6*В2+8*С2+8*D2)

Рисунок 2 - Первоначальная таблица для решения системы линейных уравнений

Запустим ПОИСК РЕШЕНИЯ из меню ДАННЫЕ. Выберем одну из ячеек, содержащих формулы, в качестве целевой ячейки (например, А3), сделаем её равной -18.

В поле ИЗМЕНЯЯ ЯЧЕЙКИ вставим ячейки А2:D2. Добавим ограничения, нажимая на кнопку ДОБАВИТЬ: В3=-2; С3=-14; D3=-6.

Рисунок 3 - Диалоговое окно надстройки ПОИСК РЕШЕНИЯ

Рисунок 4 - Диалоговое окно ДОБАВЛЕНИЕ ОГРАНИЧЕНИЯ

Нажмем на кнопку ВЫПОЛНИТЬ. Получим решение:

Рисунок 5 - Решение системы линейных уравнений

Таким образом, решение системы линейных уравнений найдено. Если проверить решение (х1=-5, х2=1, х3=-3, х4=4) подстановкой, то получим верные равенства.

Micrisoft Office Excel 2007 – специальная программа Windows, позволяющая составлять различные таблицы с вводимыми данными. Более того, данная программа позволяет решать уравнения.

Открываем Excel 2007. Для наиболее простого решения уравнения воспользуйтесь функцией «поиск решений». Правда, во многих стандартных пакетах Office данная надстройка не установлена. Чтобы установить, откройте параметры Office Excel, которые находятся в правом нижнем углу всплывающего нижнего диалогового окна. В открывшемся меню кликаем в следующей последовательности: «надстройки» - «Поиск решения» - «перейти».

После перехода установите галочку рядом с пунктом «поиск решения» и нажмите OK.

Затем Excel выполнит настройку программы.

Затем, чтобы решить уравнение, введите его в поле листа. Пусть ваше уравнение с двумя переменными: F(x1,x2)=3×1+2×2 – max, в случае определенных ограничений:

X1 - x2 ≥ -2
3×1 - 2×2 ≤ 6
2×1+3×2 ≥ 2
X2 ≤ 3
X1 ≥ 0
X2 ≤ 0

Введите в колонку А таблицы Excel переменные х1 и x2. Затем выделите синим цветом поле, где расположены полученные значения переменных. Затем в колонке А введите саму функцию F(x1, x2)=. А справа от нее выделите красным цветом ту ячейку, в которой будет находиться значение данной функции.

Затем в красное поле введите само уравнение 3×1+2×2. Учтите, что х1 – ячейка В1, а х2 – ячейка В2.

Теперь введите в поле все ограничения.

Затем перейдите в раздел «поиск решений» (папка данные). Найдите поле «установить целевую ячейку», куда нужно поставить красную ячейку. Напротив «=» пишем максимальное значение.
В поле «изменяя ячейки» добавьте синие ячейки – х1, х2.

Если вы ввели все ограничения, проверьте их правильность, после чего нажмите кнопку «выполнить». В случае если все данные введены верно, то программа должна рассчитать неизвестные. В нашем случае х1=4, ч2=3 и F(x1,x2)=18. Уравнение решено.

Значительная часть задач, которые решаются с помощью электронных таблиц, предполагают, что для обнаружения нужного результата у пользователя уже есть хоть какие-то исходные данные. Однако Exсel 2010 располагает необходимыми инструментами, с помощью которых можно решить эту задачу наоборот – подобрать нужные данные, чтобы получить необходимый результат.

«Поиск решения» и является одним из таких инструментов, максимально удобных для «задач оптимизации». И если ранее вам еще не приходилось его использовать, то сейчас самое время исправить это.

Итак – начинаем с установки данной надстройки (поскольку самостоятельно она не появится). К счастью сейчас сделать это можно достаточно просто и быстро – открываем меню «Сервис», а уже в нем «Надстройки»

Останется только в графе «Управление» указать «Надстройки Excel», а после нажать кнопочку «Перейти».

После этого несложного действия кнопка активации «Поиска решения» будет отображаться в «Данных». Как и показано на картинке

Давайте рассмотрим, как правильно используется поиск решений в Excel 2010, на нескольких простых примерах.

Пример первый .

Допустим, что вы занимаете пост начальника крупного отдела производства и необходимо правильно распределить премии сотрудникам. Допустим, общая сумма премий составляет 100 000 рублей, и необходимо, чтобы премии были пропорциональны окладам.

То есть, сейчас нам необходимо подобрать правильный коэффициент пропорциональности, чтобы определить размер премии относительно оклада.

В первую очередь необходимо быстро составить (если ее еще нет) таблицу, где будут хранится исходные формулы и данные, согласно которым и можно будет получить желаемый результат. Для нас этот результат – суммарная величина премии. А сейчас внимание – целевая ячейка С8 должна быть с помощью формул связана с искомой изменяемой ячейкой под адресом Е2. Это критично. В примере мы связываем их используя промежуточные формулы, которые и отвечают за высчитывание премии каждому сотруднику (С2:С7).

Теперь можно активировать «Поиск решений». Откроется новое окошко, в котором нам необходимо указать необходимые параметры.

Под «1 » обозначена наша целевая ячейка. Она может быть только одна.

«2 » — это возможные варианты оптимизации. Всего можно выбрать «Максимальное», «Минимальное» или «Конкретное» возможные значения. И если вам необходимо именно конкретное значение, то его нужно указать в соответствующей графе.

«3 » — изменяемых ячеек может быть несколько (целый диапазон или же отдельно указанные адреса). Ведь именно с ними и будет работать Excel, перебирая варианты так, чтобы получилось значение, заданное в целевой ячейке.

«4 » — Если понадобиться задать ограничения, то стоит воспользоваться кнопкой «Добавить», но мы это рассмотрим чуть позже.

«5 » — кнопка перехода к интерактивным вычислениям на основе заданной нами программы.

Но теперь вернемся к возможности изменять наше задание, воспользовавшись кнопкой «Добавить». Данный этап является довольно ответственным (не менее чем построение формул), поскольку именно ограничение позволяют получить правильный результат на выходе. Здесь все сделано максимально удобно, так что задать их вы сможете не только для всего диапазона сразу, но и для определенных ячеек.

Для этого можно использовать ряд определенных (и знакомых всем пользователям Excel 2010) знаков «=», «>=», «<=», а также варианты «цел» (от «целое»), «бин» («бинарное» или же «двоичное»), «раз» («все разные»).

Но в нашем примере ограничение может быть лишь одно – положительный коэффициент. Задать его, конечно, можно несколькими способами – либо используя «Добавить» (что называют «явно указать ограничение»), либо просто отметить действующей функцию «Сделать переменные без ограничений неотрицательными». Это можно сделать в надстройке «Поиск решения», нажав на кнопочку «Параметры».

Кстати, после подтверждения параметров и запуска программы (кнопочка «Выполнить»), вы сможете в таблице просмотреть полученный результат. Тогда программа продемонстрирует окошко «результатов поиска».

Если продемонстрированный результат полностью вам подходит, тогда останется только вновь подтвердить его (кнопочка «ОК»), что зафиксирует результат в вашей таблице. Если же что-то в расчетах вас не устраивает, то необходимо отменить результат (кнопочка «Отмена»), вернуться к предыдущему состоянию нашей таблицы и исправить допущенные ошибки.

Правильное решение задачи примера должно получиться вот таким

Очень важно — чтобы получить правильный результат даже при малейшем изменении исходных данных необходимо перезапустить «Поиск решений».

Чтобы более подробно взглянуть на то, как действует данная программа, давайте разберем еще один пример.

Допустим, вы являетесь владельцем крупного мебельного предприятия и необходимо наладить производство таким образом, чтобы получить максимально возможную прибыль. Вы производите только книжные полки, при этом всего двух моделей – «А» и «В», производство которых ограничивается исключительно наличием (или отсутствием) высококачественных досок, а также машинным временем (обработка на станке).

Модель «А» требует 3 м 3 досок, а модель «В» — на 1 м 3 больше (то есть – 4). От своих поставщиков вы за неделю получаете максимум 1700 м 3 досок. При этом модель «А» создается за 12 минут работы станка, а «В» — за 30 минут. Всего в неделю станок может работать не более 160 часов.

Вопрос – сколько всего изделий (и какой модели), должна выпускать фирма за неделю, чтобы получить максимально возможную прибыль, если полочка «А» дает 60 рублей прибыли, а «В» — 120?

Поскольку порядок действия известен, то начинаем создавать необходимую нам таблицу с данными и формулами. Расположение ячеек, как и ранее, вы можете установить на свое усмотрение. Или же воспользоваться нашим

Любым удобным способом запускаем наш «Поиск решений», вводим данные, производим настройку.

Итак, рассмотрим то, что мы имеем. В целевой ячейке F7 содержится формула, которая и рассчитает прибыль. Параметр оптимизации устанавливаем на максимум. Среди изменяемых ячеек у нас значится «F3:G3». Ограничения – все обнаруженные значения должны быть целыми числами, неотрицательными, общее количество потраченного машинного времени не превышает отметку 160 (наша ячейка D9), количество сырья не превышает 1700 (ячейка D8).

Конечно, в этом случае можно было не указывать адреса ячеек, а напрямую прописать необходимые цифровые значения, однако если использовать адреса, то изменения ограничений можно будет проводить и в таблице, что поможет рассчитывать прибыль этого предприятия в будущем, при смене исходных данных.

Активируем программу, и она подготавливает решение.

Впрочем, это не единственное решение и у вас вполне может выскочить другой результат. Это может произойти даже в том случае, если все данные были указаны верно и ошибок в формулах тоже не было

Да. Это может произойти даже в том случае, если мы сказали программе искать целое число. И если это вдруг произошло, то необходимо просто провести дополнительную настройку «Поиска решений». Открываем окно «Поиска решений» и входим в «Параметры».

Наш верхний параметр отвечает за точность. Чем он меньше, тем выше точность и в нашем случае это значительно повышает шансы получить целое число. Второй параметр («Игнорировать целочисленные ограничения») и дает ответ на вопрос, как мы смогли получить такой ответ с тем, что в запросе явно указали целое число. «Поиск решений» просто проигнорировал это ограничение в связи с тем, что так ему сказали расширенные настройки.

Так что будьте предельно внимательны в будущем.

Третий и, пожалуй, последний пример. Попробуем минимизировать затраты транспортной компании используя поиск решений в Excel 2010.

Итак, строительная компания дает заказ на перевозку песка, который берется от 3 поставщиков (карьеров). Его необходимо доставить 5 разным потребителям (которыми выступают строительные площадки). Стоимость доставки груза включена в себестоимость объекта, так что наша задача обеспечить доставку груза на стройплощадки с минимальными затратами.

Мы имеем – запас песка в карьере, потребность стройплощадок в песке, затрату на транспортировку «поставщик-потребитель».

Необходимо найти схему оптимальной перевозки груза (куда и откуда), при которой общая затрата на перевозку была бы минимальной.

Серые ячейки нашей таблицы содержат формулы суммы по столбцам и строкам, а целевая ячейка – формула для общего подсчета затраты на доставку груза. Запускаем наш «Поиск решения» и вносим необходимые настройки

После этого приступаем к поиску решения этой задачки

Впрочем, не будем забывать, что достаточно часто транспортные задачи могут быть усложнены некоторыми дополнительными ограничителями. Допустим, возникло осложнение на дороге и теперь из карьера 2 просто технически невозможно доставить груз на стройплощадку 3. Чтобы учесть это, необходимо просто дописать дополнительное ограничение «$D$13=0». И если теперь запустить программу, то результат будет иным

Напоследок осталось сказать только о выборе метода решения. И если задачка действительно очень сложная, то чтобы получить необходимый результат, скорее всего, понадобиться подобрать необходимый метод решения.

Вот и все по данному вопросу.

Мы выполнили поиск решений в Excel 2010 — для решения сложных задач

Решение нелинейных уравнений и систем»

Цель работы : Изучение возможностей пакета Ms Excel 2007 при решении нелинейных уравнений и систем. Приобретение навыков решения нелинейных уравнений и систем средствами пакета.

Задание1. Найти корни полинома x 3 - 0,01x 2 - 0,7044x + 0,139104 = 0.

Для начала решим уравнение графически. Известно, что графическим решением уравнения f(x)=0 является точка пересечения графика функции f(x) с осью абсцисс, т.е. такое значение x, при котором функция обращается в ноль.

Проведем табулирование нашего полинома на интервале от -1 до 1 с шагом 0,2. Результаты вычислений приведены на ри., где в ячейку В2 была введена формула: = A2^3 - 0,01*A2^2 - 0,7044*A2 + 0,139104. На графике видно, что функция три раза пересекает ось Оx, а так как полином третьей степени имеется не более трех вещественных корней, то графическое решение поставленной задачи найдено. Иначе говоря, была проведена локализация корней, т.е. определены интервалы, на которых находятся корни данного полинома: [-1,-0.8], и .

Теперь можно найти корни полинома методом последовательных приближений с помощью команды Данные→Работа с данными→Анализ «Что-Если» →Подбор параметра .

После ввода начальных приближений и значений функции можно обратиться к команде Данные→Работа с данными→Анализ «Что-Если» →Подбор параметра и заполнить диалоговое окно следующим образом.

В поле Установить в ячейке дается ссылка на ячейку, в которую введена формула, вычисляющая значение левой части уравнения (уравнение должно быть записано так, чтобы его правая часть не содержала переменную). В поле Значение вводим правую часть уравнения, а в поле Изменяя значения ячейки дается ссылка на ячейку, отведенную под переменную. Заметим, что вводить ссылки на ячейки в поля диалогового окна Подбор параметров удобнее не с клавиатуры, а щелчком на соответствующей ячейке.

После нажатия кнопки ОК появится диалоговое окно Результат подбора параметра с сообщением об успешном завершении поиска решения, приближенное значение корня будет помещено в ячейку А14.

Два оставшихся корня находим аналогично. Результаты вычислений будут помещены в ячейки А15 и А16.

Задание 2. Решить уравнение e x - (2x - 1) 2 = 0.

Проведем локализацию корней нелинейного уравнения.

Для этого представим его в виде f(x) = g(x) , т.е. e x = (2x - 1) 2 или f(x) = e x , g(x) = (2x - 1) 2 , и решим графически.

Графическим решением уравнения f(x) = g(x) будет точка пересечения линий f(x) и g(x).

Построим графики f(x) и g(x). Для этого в диапазон А3:А18 введем значения аргумента. В ячейку В3 введем формулу для вычисления значений функции f(x): = EXP(A3), а в С3 для вычисления g(x): = (2*A3-1)^2.

Результаты вычислений и построение графиков f(x) и g(x):

На графике видно, что линии f(x) и g(x) пересекаются дважды, т.е. данное уравнение имеет два решения. Одно из них тривиальное и может быть вычислено точно:

Для второго можно определить интервал изоляции корня: 1,5 < x < 2.

Теперь можно найти корень уравнения на отрезке методом последовательных приближений.

Введём начальное приближение в ячейку Н17 = 1,5, и само уравнение, со ссылкой на начальное приближение, в ячейку I17 = EXP(H17) - (2*H17-1)^2.

и заполним диалоговое окно Подбор параметра .

Результат поиска решения будет выведен в ячейку Н17.

Задание 3 . Решить систему уравнений:

Прежде чем воспользоваться описанными выше методами решения систем уравнений, найдем графическое решение этой системы. Отметим, что оба уравнения системы заданы неявно и для построения графиков, функций соответствующих этим уравнениям, необходимо разрешить заданные уравнения относительно переменной y.

Для первого уравнения системы имеем:

Выясним ОДЗ полученной функции:

Второе уравнение данной системы описывает окружность.

Фрагмент рабочего листа MS Excel с формулами, которые необходимо ввести в ячейки для построения линий, описанных уравнениями системы. Точки пересечения линий изображенных являются графическим решением системы нелинейных уравнений.

Не трудно заметить, что заданная система имеет два решения. Поэтому процедуру поиска решений системы необходимо выполнить дважды, предварительно определив интервал изоляции корней по осям Оx и Oy . В нашем случае первый корень лежит в интервалах (-0.5;0) x и (0.5;1) y , а второй - (0;0.5) x и (-0.5;-1) y . Далее поступим следующим образом. Введем начальные значения переменных x и y, формулы отображающие уравнения системы и функцию цели.