1. Решение уравнений в Excel

1. Задаем искомую ячейку

2. Создаем целевую ячейку(f(x) = 0) с помощью адресов ячеек.

3. Ставим курсор на целевую ячейку. И выполняем Данные – Работа с данными – анализ что если – подбор параметра. Установить в ячейке 0 – изменяя значение ячейки.

Замечания:

1. Решение уравнений в excel ведется численными методами, поэтому значение корня приближенное. Нахождение корня ведется с точность 10^-3

2. Решать можно только простые уравнения. Системы уравнений нельзя.

3. Если корней несколько, то каждый корень надо искать отдельно. Подбор параметра находит ближайшее к заданному. Матрицы ( Ctrl ₊ Shift + Enter.)

10. Решение оптимизационных задач в Excel.

Оптимизационная задача – из множества решений выбирает оптимальное.

Такие задачи решается с помощью надстройки. С помощью excel, но требуется доп. установки

1. Надстройки excel.

Кнопка офиса – параметры excel – надстройки – управление надстройками excel перейти – данные – анализ – поиск решений.

1. Этапы решения оптимизационных задач.

• Безмашинный – построение математической модели, которая включает:

  • Искомые величины

  • Целевую функцию

  • Ограничения.

• Машинный

3. Решение оптимизационной задачи.

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

Тип оборудования	Затраты времени (станко-часов) на обработку одного изделия вида			Общий фонд рабочего времени оборудования (ч)
	А	В	С
фрезерное	2	4	5	120
Токарное	1	8	6	280
Сварочное	7	4	5	240
Шлифовальное	4	6	7	360
Прибыль (руб.)	100	140	120

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

Решение:

• Математический :

  • Х – количество А

  • У – количество В

  • Z - количество С

  • Целевая: =100х+140у+120z – max

• Строим таблицу данных

  • Строим таблицу

  • Количество

  • Целевая функция

  • Ограничения

  • Поиск решение. Добавить ограничения. Для целого «цел.» . параметры – «не отрицательные значения». «линейная модель». Выполнить.

Задача: стипендия студента 1365 рублей. Студент испытывает потребностях следующих благах: обед – 80 руб. Проезд – 23 рубля. Кино – 80 рублей. Бахилы – 3 рубля. Оптимально распределить стипендию.

• Количество обедов

• Количество проездов

Целевая функция: стипендия

Ограничения:

• Обед >4

• Проезд >4

• Кино = 1

• Бахилы >4 <10

11. Линейная регрессия в excel.

    1. Работа с экспериментальными данными.

С помощью задачи аппроксимации и линейной регрессии.

Линейная регрессия – замена облака экспериментальных точек, линейной функцией такой, что отклонение экспериментальных точек от теоретической прямой минимальное.

Задача линейной регрессии сводится к решению оптимизационной задачи.

Решение задачи:

Х – задаем

У – измеряем

Х	1	2	3	4	5
У	12	15	21	18	21

У= kx+b

Задача регрессии сводится к нахождению коэффициентов

У(теор) = kx+b

• Отклонения могут компенсировать друг друга. Нужно избавиться от знака. «метод наименьших квадратов»

• Ищем сумму квадратов отклонений. Это и есть целевая ячейка.

2 способ – графический.

• Выделяется диапазон значений.

• Вставка – точечная диаграмма

• ПКМ по точке,- добавить линию тренда – показывать уравнения на диаграмме