РЕШЕНИЕ УРАВНЕНИЙ В СРЕДЕ MS EXCEL. Решение линейных уравнений уравнений с помощью средства «Подбор параметра» Пример 1 Найти все корни уравнения 3cos2x-sinx. - презентация

1 РЕШЕНИЕ УРАВНЕНИЙ В СРЕДЕ MS EXCEL

2 Решение линейных уравнений уравнений с помощью средства «Подбор параметра» Пример 1 Найти все корни уравнения 3cos2x-sinx = 0 при x [0;3]

3 Шаг 1 Табулируем функцию 3cos2x-sinx = 0 с шагом 0,3 на отрезке [0;3] !!! При решении уравнений с помощью средства Подбор параметра значения переменной должны быть заданы числом

4 Из таблицы значений видно, что функция на [0;3] меняет знак два раза: при х [0,6;0,9] и х [2,4;2,7], на этих отрезках есть точки пересечения функции с осью Х

5 Найдем корни полинома методом последовательных приближений с помощью средства поиск решения: Сервис > Подбор параметра

6 Скопируйте формулу из ячейки В2 в F2 (теперь формула ссылается на пустую ячейку Е2, поэтому в F2 отражается 0) Установите в ячейку Е2 значение переменной из [0,6;0,9], например х=0,7

7 Зададим относительную погрешность вычислений 0,00001 и предельное число итераций 1000 Сервис > Параметры > Вычисления

8

9 В качестве начальных значений приближений к корням можно взять любые точки из отрезков локализации корней, например 0,7 и 2,5

10 Установите курсорную рамку в ячейку F2 и выполните Сервис, Подбор параметра Аналогично найдите второй корень уравнения

11 РЕШЕНИЕ СИСТЕМЫ НЕЛИНЕЙНЫХ УРАВНЕНИЙ В СРЕДЕ MS EXCEL С ИСПОЛЬЗОВАНИЕМ СРЕДСТВА «ПОИСК РЕШЕНИЯ»

12 Пара (х;у) является решением системы уравнений тогда и только тогда, когда она является решением следующего уравнения с двумя неизвестными: (х 2 +у 2 -3) 2 +(2х+3у-1) 2 =0

13 Решением системы - точки пересечения окружности r=3 и прямой уравнение имеет не более двух различных решений Определяемое значение нелинейной задачи зависит от начального приближения

14 Для локализации корней протабулируем левую часть уравнения (х 2 +у 2 -3) 2 + (2х+3у-1) 2 = 0 по переменным х и у на [-3;3] шагом 1,5

15 Протабулируем функцию с помощью таблицы подстановки F(x;y)=(х 2 +у 2 -3) 2 +(2х+3у-1) 2

16 Из таблицы видно, что начальное приближение к корню следует выбрать следующие пары значений (-1,5;1,5), (1,5;0) и (1,5;1,5)

17 Для нахождения корней уравнения введем соответствующие пары значений (х; у) для первого корня в ячейки в А10, А11 для второго корня в ячейки в А14,А15 для третьего корня в ячейки в А17,А18 F(x;y) соответственно в ячейки В13, В16, В19

18 Найдем первый корень. 1.Установить курсорную рамку в ячейке В15 2.Выполнить Сервис > Поиск Решения

19

20 В окне Поиск решения установить целевую ячейку В13, равной значению 0, изменяя ячейки $A$11:$A$12 Нажмите кнопку Параметры и убедитесь, что снят флажок Линейная модель

21 После нажатия кнопки Выполнить средство Поиск решения находит решение, которое помещает в ячейки А11, А12 Аналогично находим второй и третий корни. Решением уравнения будут две пары значений (-1,269;1,179) (1,576;-0,717)

22 РЕШЕНИЕ МАТРИЧНЫХ УРАВНЕНИЙ

23 Простейшие операции над массивами МАССИВ - объект Excel, используемый для получения нескольких значений в результате вычисления одной формулы или для работы с набором аргументов, расположенных в различных ячейках и сгруппированных по строкам или столбцам.

24 Два типа массивов Microsoft Excel : диапазон массива - непрерывный диапазон ячеек, использующих общую формулу; диапазон констант - набор констант, используемых в качестве аргументов функций.

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

26 Массив констант может включать: Числа (целые, с десятичной точкой или в экспоненциальном формате) Текст (должен быть взят в двойные кавычки) Логические значения (ИСТИНА, ЛОЖЬ или значения ошибок например #Н/Д) Элементы разного типа{1,3,4;ИСТИНА,ЛОЖЬ,ИСТИНА}. Массив констант не может содержать Формулы. $ (знак доллара) Скобки % (знак процента) Ссылки на ячейки Столбцы или строки разной длины

27 Для умножения (деления) массива на число: 1.Выделить диапазон ячеек того же размера 2.Ввести в первую ячейку диапазона формулу =Е1:G3*100 и нажать комбинацию клавиш SHIFT+ CTRL+ENTER Если в формуле используется ссылка на ячейку в которой хранится число, то ссылка на эту ячейку должна быть абсолютной

28 Формула массива обрабатывает несколько наборов значений (аргументов массива). Каждый аргумент массива должен включать одинаковое число строк и столбцов. Формула массива создается так же, как и другие формулы, только что для ввода такой формулы используются комбинация клавиш SHIFT+ CTRL+ENTER

29 Пример Перемножение массивов: 1.Выделить область такого же размера как перемножаемые массивы 2.Ввести в первую ячейку формулу =А1:С3*А1:С7 3.Для ввода массива нажать комбинацию SHIFT+ CTRL+ENTER Сложение, вычитание, деление, вычисление каждого элемента как результата некоторой функции производится аналогично. При вводе формулы массива Microsoft Excel автоматически заключает ее в фигурные скобки

30 Формула массива может выполнить несколько вычислений, а затем вернуть одно значение или группу значений. Пример Рассчитать суммарный балл оценки экспертом качества услуги по формуле: Si - суммарный балл Wi – вес критерия Ci – оценка критерия экспертом N – количество критериев

31 Способ решения 1 1.Введите в ячейку D2 формулу =В2*С2 и скопируйте ее в ячейки диапазона D3:D7 2.Введите в ячейку D8 формулу = СУММ(D2:D7) 3.В ячейке D9 вычислите значение S = D86

32 Функция – заранее созданная формула, позволяющая выполнять сложные вычисления, знак = в начале формулы означает, что это формула а не текст. Функция состоит из двух частей: Имя функции - описывает операцию Аргумент – задает значение или ячейки, используемые функцией. Аргумент всегда заключается в скобки При использовании в функции нескольких аргументов их разделяют знаком «;» (не более 30 аргументов) Между именем и аргументом пробелов или других символов не ставится, иначе в ячейке отображается сообщение об ошибке #ИМЯ? Общая длина формулы не более 1024 символа

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

34 Способ решения 2 Используем функцию Excel из категории Математические СУММПРОИЗВ(массив1;массив2;массив3;...) Массив1, массив2,… массив30 - перемножаются поэлементно, а затем произведения складываются. Аргументы, которые являются массивами, должны иметь одинаковые размерности, в противном случае функция СУММПРОИЗВ() возвращает значение ошибки #ЗНАЧ!. Нечисловые элементы массивов функция СУММПРОИЗВ() трактует как нулевые

35 Окно диалога функции Суммпроизв() Результат вычисления формулы - число

36 Функции для работы с массивами МУМНОЖ(массив1;массив2) - перемножает массивы. Массивы (матрицы) должны быть одной размерности и оба массива должны содержать только числа.

37 МОБР(массив)- возвращает обратную матрицу для матрицы, хранящейся в массиве

38 ТРАНСП(массив) - используется для того, чтобы поменять ориентацию массива на рабочем листе с вертикальной на горизонтальную и наоборот.

39 МОПРЕД(массив) - возвращает определитель матрицы (матрица хранится в массиве). Определитель матрицы - это число, вычисляемое на основе значений элементов массива. Для массива A1:C3, состоящего из трех сток и тех столбцов, определитель вычисляется следующим образом: = A1*(B2*C3-B3*C2) + A2*(B3*C1-B1*C3) + A3*(B1*C2-B2*C1)

40 СУММСУММКВ(массив_x;массив_y) - возвращает сумму сумм квадратов соответствующих элементов двух массивов. Сумма сумм квадратов - это распространенный термин во многих статистических вычислениях. Массив_x - это первый массив или интервал значений. Массив_y - это второй массив или интервал значений.

41 СРГЕОМ(число1;число2;...) - возвращает среднее геометрическое значений массива или интервала положительных чисел. Например, функцию СРГЕОМ можно использовать для вычисления средних темпов роста, если задан составной доход с переменными ставками. СУММКВРАЗН(массив_x;массив_y) - возвращает сумму квадратов разностей с оответствующих значений в двух массивах. Массив_x - это первый массив или интервал значений. Массив_y - это второй массив или интервал значений.

42 ЧИСЛСТОЛБ(массив) - возвращает количество столбцов в ссылке или массиве: =ЧИСЛСТОЛБ(A1:D9) в ячейке отображается число 4 ЧСТРОК(массив) - возвращает количество строк в ссылке или массиве. = ЧСТРОК (A1:D9) в ячейке отображается число 9 Статистические функции, который используются для прогнозирования Тенденция(), Рост(), Предсказ(), Линейн() также используют правило ввода значений массива

43 Решение матричных уравнений в EXCEL Найти решение уравнения А*Х=В А-матрица коэффициентов В- столбец (вектор) свободных членов Х-столбец (вектор)неизвестных Решение линейной системы имеет вид: Х=А -1 *В А -1 – обратная матрица

44 Шаг 1. Вычислим А -1 с помощью функции =МОБР(массив) Шаг 2. Выделить диапазон К2:К4 для элементов массива вектора Х и ввести формулу =МУМНОЖ(E2:G4;I2:I4) Для вставки массива нажать комбинацию клавиш SHIFT+ CTRL+ENTER

45 Шаг 3. Проверка. Умножим матрицу А на найденный вектор Х В результате мы должны получить вектор В Выделим диапазон М2:М4 и введем функцию = МУМНОЖ(А2:С4;К2:К4) Для вставки массива нажать комбинацию клавиш SHIFT+ CTRL+ENTER

46 Самостоятельно решить системы линейных уравнений А 2 *Х=В и А 3 *Х=В

47 Решить уравнение Z=Х т A X А-матрица, Х-вектор, Х T - транспонированный вектор Шаг1. Найти транспонированный вектор Х T Выделать диапазон G2:I2 и ввести формул =ТРАНСП(E2:E4) для ввода массива значений нажать SHIFT+ CTRL+ENTER

48 Шаг2. Умножить полученную строку Х T на матрицу Авыделить диапазон К2:М2 и ввести формулу =МУМНОЖ(G2:I2;A2:C4) Шаг 3. В отдельную ячейку введите формулу =МУМНОЖ(K2:M2;E2:E4) – результат вычисления число 227, но для ввода нажать SHIFT+ CTRL+ENTER

49 Это же решение можно получить путем ввода в ячейку одной формулы, содержащей вложенные функции: =МУМНОЖ(МУМНОЖ(ТРАНСП(E2:E4);A2:C4);E2:E4) Самостоятельно решить уравнения: 1. Z=Y т A т AY 2. Z=Y т A т A 2 Y

50 Решение системы линейных уравнений методом Гаусса

51 1. Ввести матрицу коэффициентов в ячейки рабочего листа MS Excel 2. Скопировать первую строчку (диапазон А1:Е6) в диапазоны А6:Е6 А11:Е11 А16:Е16

52

53 3. Выделить диапазон А7:Е7 и введите формулу, которая обращает в 0 коэффициент при х 1 во втором уравнении системы: =A2:E2-$A$1:$E$1*(A2/$A$1) Для вставки элементов массива нажать SHIFT+ CTRL+ENTER Выделить диапазон А7:Е7 и протащить маркер автозаполнения этого диапазона, чтобы заполнить диапазоны А7:Е7 в диапазон А8:Е8 и А9:Е9. Это обратит в 0 коэффициенты при х 1 в третьем и четвертом уравнениях системы.

54 4. Выделить диапазон А7:Е7 и скопируйте значения в буфер Выделите диапазон А12:Е12 и выполните вставку значений без формул используйте команду Правка, специальная вставка Аналогично вставьте значения в диапазон А17:Е17

55 5. Выделите диапазон А13:Е13 и введите формулу массива, которая обращает в 0 коэффициент при х 2 третьего и четвертого уравнений системы =A8:E8-$A$7:$E$7*(B8/$B$7) Для вставки элементов массива нажать SHIFT+ СTRL+ENTER Затем скопировать массив А13:Е13 в диапазон А14:Е14

56 5. Выделите диапазон А19:Е19 и введите формулу массива, которая обращает в 0 коэффициент при х 3 =A14:E14-$A$13:$E$13*(C14/$C$13) Для вставки элементов массива нажать SHIFT+ СTRL+ENTER Прямая прогонка метода Гаусса завершена

57 Обратная прогонка заключается в вводе формул : В диапазон G4:K4 =A19:E19/D19 В диапазон G3:K3 =(A18:E18-G4:K4*D18)/C18 В диапазон G2:K2 =(A17:E17-G4:K4*D17-G3:K3*C17)/B17 В диапазон G1:K1 =(A16:E16-G4:K4*D16-G3:K3*C16-G2:K2*B16)/A16

58 В диапазоне получено решение системы