Как посчитать уравнение в excel

Рассмотрим задачу нахождения корня уравнения методом Ньютона с использованием циклических ссылок. Возьмем для примера квадратное уравнение: х 2 — 5х + 6=0, графическое представление которого приведено на рис. 8. Найти корень этого (и любого другого) уравнения можно, используя всего одну ячейку Excel.

Выберем произвольную ячейку, присвоим ей новое имя, скажем — Х, и введем в нее рекуррентную формулу, задающую вычисления по методу Ньютона:

• В ячейку Хнач (В4) заносим начальное приближение — 5.
• В ячейку Хтекущ (С4) записываем формулу:
  =ЕСЛИ(Хтекущ=0;Хнач; Хтекущ-(Хтекущ^2-5*Хтекущ+6)/(2*Хтекущ-5)).
• В ячейку D4 помещаем формулу, задающую вычисление значения функции в точке Хтекущ, что позволит следить за процессом решения.
• Заметьте, что на первом шаге вычислений в ячейку Хтекущ будет помещено начальное значение, а затем уже начнется счет по формуле на последующих шагах.
• Чтобы сменить начальное приближение, недостаточно изменить содержимое ячейки Хнач и запустить процесс вычислений. В этом случае вычисления будут продолжены, начиная с последнего вычисленного
  

  Рис. 9. Определение начальных установок

2.2. Подбор параметра

Возьмем в качестве примера все то же квадратное уравнение х 2 -5х+6=0. Для нахождения корней уравнения выполним следующие действия:

В ячейку С3 (рис. 10) введем формулу для вычисления значения функции,

Рис. 10. Окно диалога Подбор параметра

Вернемся к примеру. Опять возникает вопрос: как получить второй корень? Как и в предыдущем случае необходимо задать начальное приближение. Это можно сделать следующим образом (рис. 11,а):

• В ячейку Х (С2) вводим начальное приближение.
• В ячейку Х_i (С3) вводим формулу для вычисления очередного приближения к корню, т.е.
  =X-(X^2-5*X+6)/(2*X-5).
• В ячейку С4 поместим формулу, задающую вычисление значения функции, стоящей в левой части исходного уравнения, в точке Х_i.
• После этого выбираем команду Подбор параметра, где в качестве изменяемой ячейки принимаем ячейку С2. Результат вычислений изображен на рис. 11,б (в ячейке С2 — конечное значение, а в ячейке С3 — предыдущее).

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

2.3. Поиск решения

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

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

• найти максимум целевой функции F(х₁, х₂, … , х_n);
• найти минимум целевой функции F(х₁, х₂, … , х_n);
• добиться того, чтобы целевая функция F(х₁, х₂, … , х_n) имела фиксированное значение: F(х₁, х₂, … , х_n) = a.

Функции G(х₁, х₂, … , х_n) называются ограничениями. Их можно задать как в виде равенств, так и неравенств. На регулируемые ячейки можно наложить дополнительные ограничения: неотрицательности и/или целочисленности, тогда искомое решение ищется в области положительных и/или целых чисел.

Выше для нахождения корней квадратного уравнения был применен метод Ньютона (п. 1.4) с использованием циклических ссылок (п. 2.1) и средство Подбор параметра (п. 2.2). Рассмотрим, как воспользоваться Поиском решения на примере того же квадратного уравнения.

После открытия диалога Поиск решения (рис. 12) необходимо выполнить следующие действия:

1. в поле Установить целевую ячейку ввести адрес ячейки, содержащей формулу для вычисления значений оптимизируемой функции, в нашем примере целевая ячейка — это С4, а формула в ней имеет вид: = C3^2 — 5*C3 + 6;
2. для максимизации значения целевой ячейки, установить переключатель максимальному значению в положение 8 , для минимизации используется переключатель минимальному значению, в нашем случае устанавливаем переключатель в положение значению и вводим значение 0;
3. в поле Изменяя ячейки ввести адреса изменяемых ячеек, т.е. аргументов целевой функции (С3), разделяя их знаком «;» (или щелкая мышью при нажатой клавише Сtrl на соответствующих ячейках), для автоматического поиска всех влияющих на решение ячеек используется кнопка Предположить;
4. в поле Ограничения с помощью кнопки Добавить ввести все ограничения, которым должен отвечать результат поиска: для нашего примера ограничений задавать не нужно;
5. для запуска процесса поиска решения нажать кнопку Выполнить.

Опции, управляющие работой Поиска решения, задаваемые в окне Параметры (окно появляется, если нажать на кнопку Параметры окна Поиск решения), следующие (рис. 14):

Сохранить модель поиска решения можно следующими способами:

1. при сохранении книги Excel после поиска решения все значения, введенные в окнах диалога Поиск решения, сохраняются вместе с данными рабочего листа. С каждым рабочим листом в рабочей книге можно сохранить один набор значений параметров Поиска решения;
2. если в пределах одного рабочего листа Excel необходимо рассмотреть несколько моделей оптимизации (например найти максимум и минимум одной функции, или максимальные значения нескольких функций), то удобнее сохранить эти модели, используя кнопку Параметры/Сохранить модель окна Поиск решения. Диапазон для сохраняемой модели содержит информацию о целевой ячейке, об изменяемых ячейках, о каждом из ограничений и все значения диалога Параметры. Выбор модели для решения конкретной оптимизационной задачи осуществляется с помощью кнопки Параметры/Загрузить модель диалога Поиск решения;
3. еще один способ сохранения параметров поиска — сохранение их в виде именованных сценариев. Для этого необходимо нажать на кнопку Сохранить сценарий диалогового окна Результаты поиска решений.

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

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

Исправляем ошибки: Нашли опечатку? Выделите ее мышкой и нажмите Ctrl+Enter

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

В точке безубыточности валовая прибыль равна валовым затратам, т.е. (В3*В4)-(В1+В2*В4)=0. Вызовите Подбор параметра , заполните параметры и нажмите ОК – в ячейке В4 будет вычислено значение 83.33 (рис. 17).

График функции в Excel: как построить? MS Office Excel — Работа на компьютере: инструкции и советы — Образование, воспитание и обучение — Сообщество взаимопомощи учителей

Заполняем матрицу числами, которые являются коэффициентами уравнения. Данные числа должны располагаться последовательно по порядку с учетом расположения каждого корня, которому они соответствуют. Если в каком-то выражении один из корней отсутствует, то в этом случае коэффициент считается равным нулю. Если коэффициент не обозначен в уравнении, но соответствующий корень имеется, то считается, что коэффициент равен 1. Обозначаем полученную таблицу, как вектор A.

Для того чтобы проверить, правильно ли решена система уравнений, необходимо умножить матрицу A на вектор x и получить в результате вектор b. Умножение матрицы A на вектор x осуществляется при помощи функции МУМНОЖ(В1:Е4;Н6:Н9), так как было описанной выше.

• В ячейку Хнач (В4) заносим начальное приближение — 5.
• В ячейку Хтекущ (С4) записываем формулу:
  =ЕСЛИ(Хтекущ=0;Хнач; Хтекущ-(Хтекущ^2-5*Хтекущ+6)/(2*Хтекущ-5)).
• В ячейку D4 помещаем формулу, задающую вычисление значения функции в точке Хтекущ, что позволит следить за процессом решения.
• Заметьте, что на первом шаге вычислений в ячейку Хтекущ будет помещено начальное значение, а затем уже начнется счет по формуле на последующих шагах.
• Чтобы сменить начальное приближение, недостаточно изменить содержимое ячейки Хнач и запустить процесс вычислений. В этом случае вычисления будут продолжены, начиная с последнего вычисленного
  

  Рис. 9. Определение начальных установок

Решение систем линейных уравнений с помощью функций Microsoft Excel

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

Пусть дана линейная система я уравнений с и неизвестными, где a_ij, b_i(i= 1,1. n; j = 1, 2. n) – произвольные числа, называемые, соответственно, коэффициентами при переменных и свободными членами уравнений.

Эта запись называется системой линейных уравнений в нормальной форме. Решением данной системы называется такая совокупность чисел (x₁=k₁, х₂=k₂, . х_n=k_n), при подстановке которых каждое уравнение системы обращается в верное равенство.

Система уравнений совместна, если она имеет хотя бы одно решение и несовместна, если она не имеет решений.

Если совместная система уравнений имеет единственное решение, она называется определенной; напротив, система уравнений называется неопределенной, если она имеет более одного решения.

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

где А — матрица коэффициентов при переменных, или матрица системы:

В развернутом виде систему можно представить следующим образом:

Умножая слева обе части матричного равенства на обратную матрицу А -1 ,

A -1 ×A×X=A -1 × B, E×X=A -1 ×B; E×X=X

отсюда решением системы методом обратной матрицы будет матрица-столбец:

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

Введём матрицу A и вектор b в рабочий лист MS Excel

Если поле Массив заполнено, можно нажать кнопку OK. В первой ячейке, выделенного под обратную матрицу диапазона, появится некое число. Для того чтобы получить всю обратную матрицу, необходимо нажать клавишу F2 для перехода в режим редактирования, а затем одновременно клавиши Ctrl+Shift+Enter. Рабочая книга MS Excel примет следующий вид:

Перейдём ко второму шагу мастера функций. Появившееся диалоговое окно содержит два поля ввода Массив1 и Массив2. В поле Массив1 необходимо ввести диапазон ячеек, в котором содержится первая из перемножаемых матриц, в нашем случае B6:E9 (обратная матрица), а в поле Массив2 ячейки, содержащие вторую матрицу, в нашем случае G1:G4 (вектор b).

Если поля ввода заполнены, можно нажать кнопку OK. В первой ячейке выделенного диапазона появится соответствующее число результирующего вектора. Для того чтобы получить весь вектор, необходимо нажать клавишу F2, а затем одновременно клавиши Ctrl+Shift+Enter. В нашем случае результаты вычислений (вектор х), находится в ячейках H6:H9.

Для того чтобы проверить, правильно ли решена система уравнений, необходимо умножить матрицу A на вектор x и получить в результате вектор b. Умножение матрицы A на вектор x осуществляется при помощи функции МУМНОЖ(В1:Е4;Н6:Н9), так как было описанной выше.

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

Программируемые макросы: запуск редактора Visual Basic for Applications (VBA), подпрограммы и функции, формы и модули

Самый простой способ автоматизировать задачу – просто записать макрос, и VBA сделает всю работу за Вас. Тем не менее, чтобы реализовать все возможности языка VBA, придется воспользоваться некоторыми средствами программирования, т.е. придется писать собственные макросы.

Преимущества создания макросов на основе уже записанных:

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

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

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

При первом открытии редактора Вы ничего особенного не увидите. В левой части редактора содержится два окна, которые называются Project (Проект) и Properties (Свойства). В окне Project отображается содержимое текущего проекта VBA. Если говорить просто, проектом называется файл любого приложения Office и все связанные с ним элементы VBA, включая макросы и пользовательские формы.

Редактор Visual Basic можно открыть, нажав комбинацию клавиш «Alt+F11». С помощью этой комбинации клавиш также можно переключаться между редактором и приложением, из которого он был вызван.

Чтобы что-то сделать в редакторе Visual Basic, сначала, как правило, нужно открыть модуль – элемент VBA, который содержит один или несколько макросов.

1. В окне Project раскройте папку Modules (Модули). Для этого щелкните на знаке «плюс (+)», который расположен слева от этой папки.

2. Дважды щелкните на имени модуля, который нужно открыть.

Object list (список объектов). В этом списке, расположенном слева, отображаются названия доступных объектов для того элемента проекта, с которым Вы работаете. Модули не могут содержать объектов, поэтому этот список включает только элемент (General), т.е. «общий».

Procedure list (Список процедур). Данный список расположен справа и содержит названия тех процедур и функций, которые входят в модуль. При выборе элемента из этого списка в окне модуля редактор отобразит выбранный элемент.

Если у Вас нет созданного ранее макроса, выполните команду Insert=>Module (Вставка>Модуль). Редактор Visual Basic присвоит этому модулю стандартное имя, такое как Module1 или Module2, но его можно переименовать в любое время.

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

1. Поместите курсор в ту часть модуля, где Вы хотите начать запись макроса (Убедитесь, что курсор не находится внутри уже созданного макроса).

2. Если хотите начать макрос с некоторых заметок (программисты называют их комментариями), в которых будет описано, что делает макрос, введите символ апострофа ‘ в начале каждой строки комментария.

3. Чтобы начать макрос, введите ключевое слово Sub, после чего введите пробел и название макроса. Название может состоять только из букв, цифр и символов подчеркивания «_». Использование в имени макроса пробелов и других символов не допускается.

4. Нажмите клавишу «Enter». Редактор автоматически добавит пару скобок в конце имени макроса. Он также добавит строку с текстом End Sub, чтобы обозначить конец процедуры.

5. Между строками Sub и End Sub введите операторы VBA, которые необходимо включить в макрос.

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

Выполняется запуск Мастера функций. Переходим в категорию «Математические». В представившемся списке ищем наименование «МОБР». После того, как оно отыскано, выделяем его и жмем на кнопку «OK».

Как посчитать уравнение в excel

Теперь нам нужно будет умножить обратную матрицу на матрицу B, которая состоит из одного столбца значений, расположенных после знака «равно» в выражениях. Для умножения таблиц в Экселе также имеется отдельная функция, которая называется МУМНОЖ. Данный оператор имеет следующий синтаксис:

Как Сделать Систему Уравнений в Excel

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

Матрица, состоящая из одной строки, называется матрицей (вектором)-строкой:

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

Транспонированной называется матрица (А Т ), в которой столбцы исходной матрицы (А) заменяются строками с соответствующими номерами.

В сокращенной записи, еслиА=( a_ij ) , то А Т =(а _ij ).

Из определения транспонированной матрицы следует, что если исходная матрица А имеет размер т × п, то транспонированная матрица А Т имеет размер п × т.

Для осуществления транспонирования в Excel используется функция ТРАНСП, которая входит в категорию Ссылки и массивы и позволяет поменять ориентацию массива на рабочем листе с вертикальной на горизонтальную и наоборот.

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

Важной характеристикой квадратных матриц является их определитель. Определитель матрицы — это число, вычисляемое на основе значений элементов массива. Определитель матрицы А обозначается как или ∆.

С ростом порядка матрицы п резко увеличивается число членов определителя ( n ! ). Например, при n = 4 имеем 24 слагаемых.

Существуют специальные правила, облегчающие вычисление определителей вручную, учитываются свойства определителей и т. п. Excel для вычисления определителя квадратной матрицы используется функция МОПРЕД.

Синтаксис: МОПРЕД(массив). Массив — это числовой массив, в котором хранится матрица с равным количеством строк и столбцов. При этом массив может быть задан как интервал ячеек, например, А1:СЗ; или как массив констант, например, .

Матрица А -1 называется обратной по отношению к квадратной матрицеА, если при умножении этой матрицы на данную как слева, так и справа получается единичная матрица: .

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

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

Функция имеет вид МОБР( массив ). Здесь массив — это числовой массив с равным количеством строк и столбцов. Массив может быть задан как диапазон ячеек, например А1:СЗ; как массив констант, например или как имя диапазона или массива.

Складывать (вычитать) можно матрицы одного размера. Суммой матриц А=(а _ij ) и В=( b_ij ) размера т × п называется матрица С=А+В, элементы которой c_ij = а _ij + b_ij для i = 1, 2, . m ; j = 1, 2, . n , (то есть матрицы складываются поэлементно).

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

Произведением матрицы А на число к называется матрица В = kA , элементы которой b_ij = ka_ij для i = 1, 2, . m ; j = 1, 2. п. Иначе говоря, при умножении матрицы на постоянную каждый элемент этой матрицы умножается на эту постоянную.

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

Произведение матриц определено, если число столбцов первой матрицы равно числу строк второй.

При этом матрица С (размера т × р) называется произведением матриц А и В, если каждый ее элемент с _ij равен сумме произведений элементов i -й строки матрицы A на соответствующие элементы j — г o столбца матрицы В.

Для нахождения произведения двух матриц в Excel используется функция МУМНОЖ, которая вычисляет произведение матриц (матрицы хранятся в массивах).

Систему уравнений можно записать в виде матричного уравнения:

А × Х= В, где А — матрица коэффициентов при переменных, или матрица системы, Х – матрица-столбец (вектор) неизвестных В — матрица-столбец (вектор) свободных членов:

Существует ряд методов решения системы, методы Крамера, Гаусса, обратной матрицы. С помощью MS Excel можно решить СЛАУ двумя методами: методом Крамера и обратной матрицы.

Суть метода Крамера заключается в следующем: если определитель ∆ системы n линейных алгебраических уравнений отличен от нуля ∆≠ 0, то эта система имеет единственное решение, которое находится по формулам Крамера:

Рассмотрим решение системы методом обратной матрицы. Будем считать, что квадратная матрица системы А является невырожденной, то есть ее определитель |А| ≠ 0. В этом случае существует обратная матрица A -1 .

Умножая слева обе части матричного равенства на обратную матрицу А получим:

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

Пропускаем строку после последней записи на листе. Выделяем первую ячейку в следующей строке. Кликаем правой кнопкой мыши. В открывшемся контекстном меню наводим курсор на пункт «Специальная вставка». В запустившемся дополнительном списке выбираем позицию «Значения».

ЗАНЯТИЕ 8. РЕШЕНИЕ СИСТЕМ ЛИНЕЙНЫХ АЛГЕБРАИЧЕСКИХ УРАВНЕНИЙ В EXCEL — ____Работа в MS Excel____

Решение задач – одно из важных применений Excel. Самый простой инструмент предназначен для подбора значений и называется «что-если» анализ: задается некоторая целевая функция и ее числовое значение, Excel автоматически подбирает параметры целевой функции до получения целевого значения. Формула в целевой функции должна логически зависеть от подбираемого параметра.