Как решить уравнение в эксель. Поиск решения MS EXCEL

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

Основные отличия между поиском решения и подбором параметра:

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

Примеры и задачи на поиск решения в Excel

Рассмотрим аналитические возможности надстройки. Например, Вам нужно накопить 14 000$ за 10 лет. На протяжении 10-ти лет вы хотите каждый год откладывать на депозитный счет в банке по 1000$ под 5% годовых. Ниже на рисунке построена таблица в Excel, по которой хорошо видно остаток накопленных средств на каждый год. Как видно при таких условиях депозитного счета и взносов накопления цель не будет достигнута даже через 10 лет. При решении данной задачи можно пойти двумя путями:

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

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

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

Как видно программа немного увеличила процентную ставку и сумму ежегодных взносов.

Ограничение параметров при поиске решений

Допустим, вы пошли в банк с этой таблицей, но банк отказывается поднять Вам процентную ставку. В таком случаи нам нужно узнать, насколько нам придется повысить сумму ежегодных вложений. Мы должны установить ограничение на ячейку с одним переменным значением. Но перед началом измените значения в переменных ячейках на исходные: в B1 на 5%, а в B2 на -1000$. А теперь делаем следующее.

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

Ход решения

Запустим программу Microsoft Office Excel. Я пользуюсь 2007 версией. Для начала объединим ячейки A1:A5 и запишем в них формулу квадратного уравнения в виде ax2+bx+c=0.Далее нам нужно возвести x в квадрат, для этого нужно сделать цифру 2 надстрочным интервалом. Выделим двойку и нажмем правой кнопкой мыши.

Получим формулу вида ax 2 +bx+c=0

В ячейке A2 введем текстовое значение a= , в ячейке A3 b= и в ячейке A4 с= соответственно. Эти значения будут вводиться с клавиатуры в следующих ячейках (B2,B3,B4).

Введем текст для значений, которые будут считаться. В ячейке C2 d=, C3 x 1 = C4 x 2 =. Подстрочный интервал для xсделаем аналогично надстрочному интервалу в x 2

Перейдем к вводу формул для решения

Дискриминант квадратного трехчлена равен b 2 -4ac

В ячейку D2 введем соответствующую формулу для возведения числа во вторую степень:

Квадратное уравнение имеет два корня, в случае если дискриминант больше нуля. В ячейку C3 введем формулу для x 1

ЕСЛИ(D2>0;(-B3+КОРЕНЬ(D2))/(2*B2);»Корней нет»)

Для расчета x2 введем похожую формулу, но со знаком плюс

ЕСЛИ(D2>0;(-B3-КОРЕНЬ(D2))/(2*B2);»Корней нет»)

Соответственно при введенных значениях a,b,c сначала считается дискриминант, если его значения меньше нуля выводится сообщение «Корней нет», иначе получаем значения x 1 и x 2 .

Защита листа в Excel

Нам нужно защитить лист, на котором мы производили расчеты. Без защиты нужно оставить ячейки, в которые можно вводить значения a,b,c, то есть ячейки B2 B3 B4. Для этого выделим данный диапазон и зайдем в формат ячеек, перейдем во вкладку Рецензирования, Защитить лист и уберем флажок с позиции Защищаемая ячейка. Нажмем кнопку OK, подтвердив внесенные изменения.

Этот диапазон ячеек будет не защищен при защите листа. Выполним защиту листа, для этого перейдем на вкладку Рецензирование пункт Защита листа. Пароль наберем 1234. Нажмем OK.

Теперь мы сможем изменять значения ячеек B2,B3,B4. При попытке изменения других ячеек мы получим сообщение следующего содержания: «Ячейка или диаграмма защищена от изменений. А так же совет по снятию защиты.

Так же вас может заинтересовать материал как закрепить .

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

Рассмотрим на примерах некоторые варианты решений.

Решение уравнений методом подбора параметров Excel

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

Путь к команде: «Данные» - «Работа с данными» - «Анализ «что-если»» - «Подбор параметра».

Рассмотрим на примере решение квадратного уравнения х 2 + 3х + 2 = 0. Порядок нахождения корня средствами Excel:

Для подбора параметра программа использует циклический процесс. Чтобы изменить число итераций и погрешность, нужно зайти в параметры Excel. На вкладке «Формулы» установить предельное количество итераций, относительную погрешность. Поставить галочку «включить итеративные вычисления».

Как решить систему уравнений матричным методом в Excel

Дана система уравнений:

Получены корни уравнений.

Решение системы уравнений методом Крамера в Excel

Возьмем систему уравнений из предыдущего примера:

Для их решения методом Крамера вычислим определители матриц, полученных заменой одного столбца в матрице А на столбец-матрицу В.

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

Рассчитаем также определитель матрицы А (массив – диапазон матрицы А).

Определитель системы больше 0 – решение можно найти по формуле Крамера (D x / |A|).

Для расчета Х 1: =U2/$U$1, где U2 – D1. Для расчета Х 2: =U3/$U$1. И т.д. Получим корни уравнений:

Решение систем уравнений методом Гаусса в Excel

Для примера возьмем простейшую систему уравнений:

3а + 2в – 5с = -1
2а – в – 3с = 13
а + 2в – с = 9

Коэффициенты запишем в матрицу А. Свободные члены – в матрицу В.

Для наглядности свободные члены выделим заливкой. Если в первой ячейке матрицы А оказался 0, нужно поменять местами строки, чтобы здесь оказалось отличное от 0 значение.

Примеры решения уравнений методом итераций в Excel

Вычисления в книге должны быть настроены следующим образом:

Делается это на вкладке «Формулы» в «Параметрах Excel». Найдем корень уравнения х – х 3 + 1 = 0 (а = 1, b = 2) методом итерации с применением циклических ссылок. Формула:

Х n+1 = X n – F (X n) / M, n = 0, 1, 2, … .

M – максимальное значение производной по модулю. Чтобы найти М, произведем вычисления:

f’ (1) = -2 * f’ (2) = -11.

Полученное значение меньше 0. Поэтому функция будет с противоположным знаком: f (х) = -х + х 3 – 1. М = 11.

В ячейку А3 введем значение: а = 1. Точность – три знака после запятой. Для расчета текущего значения х в соседнюю ячейку (В3) введем формулу: =ЕСЛИ(B3=0;A3;B3-(-B3+СТЕПЕНЬ(B3;3)-1/11)).

В ячейке С3 проконтролируем значение f (x): с помощью формулы =B3-СТЕПЕНЬ(B3;3)+1.

Корень уравнения – 1,179. Введем в ячейку А3 значение 2. Получим тот же результат:

Корень на заданном промежутке один.

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

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

Имеем классическую задачу когда фирма производит два вида продукции (товар А и товар Б) по определенной цене, на их производство требуется 4 вида ресурсов (ресурс 1, ресурс 2, ресурс 3, ресурс 4), которые есть в наличие на фирме в определенном количестве (Запас), также имеется информация сколько нужно каждого ресурса на производство единицы продукции, соответственно товара А и товара Б. Нужно найти, то количество товара А и товара Б, которое максимизирует доход (выручку) (см. рис.).

Далее нам надо сделать взаимосвязи между ограничениями, планом и целевой функцией. Для этого мы строим дополнительный столбец (Использовано), в котором вводим формулуСУММПРОИЗВ (Норма; План). Норма - это затраты определенного ресурса на производство единицы продукции товара А и Б, а План – количество продукции, которое мы ищем. В ячейки Доход вводим формулу СУММПРОИЗВ (Цена; План). Таким образом мы заполнили формулами столбец Использовано и ячейку Доход. Так как план это переменные от которых зависит количество использованных ресурсов и доход, то ячейки с формулами напрямую зависят от данных, которые там появятся в результате поиска решений. С выше сказанного можно сделать следующие выводы, что каждая задача оптимизации обязательно должна иметь три компоненты:

неизвестные (что ищем, то есть, план);

ограничение на неизвестные (область поиска);

целевая функция (цель, для которой ищем экстремум).

Мощным средством анализа данных Excel является надстройка Solver (Поиск решения) . С ее помощью можно определить, при каких значениях указанных влияющих ячеек формула в целевой ячейке принимает нужное значение (минимальное, максимальное или равное какой-либо величине). Для процедуры поиска решения можно задать ограничения, причем не обязательно, чтобы при этом использовались те же влияющие ячейки. Для расчета заданного значения применяются различные математические методы поиска. Вы можете установить режим, в котором полученные значения переменных автоматически заносятся в таблицу. Кроме того, результаты работы программы могут быть оформлены в виде отчета. Программа Поиск решений (в оригинале Excel Solver) – дополнительная надстройка табличного процессора MS Excel, которая предназначена для решения определенных систем уравнений, линейных та нелинейных задач оптимизации, используется с 1991 года. Размер задачи, которую можно решить с помощью базовой версии этой программы, ограничивается такими предельными показателями:

количество неизвестных (decision variable) – 200;

количество формульных ограничений (explicit constraint) на неизвестные – 100;

количество предельных условий (simple constraint) на неизвестные – 400.

Разработчик программы Solver компания Frontline System уже давно специализируется на разработке мощных и удобных способов оптимизации, встроенных в среду популярных табличных процессоров разнообразных фирм-производителей (MS Excel Solver, Adobe Quattro Pro, Lotus 1-2-3). Высокая эффективность их применения объясняется интеграциею программы оптимизации и табличного бизнес-документа. Благодаря мировой популярности табличного процессора MS Excel встроенная в его среду программа Solver есть наиболее распространенным инструментом для поиска оптимальных решений в сфере современного бизнеса. По умолчанию в Excel надстройка Поиск решения отключена. Чтобы активизировать ее в Excel 2007 , щелкните значок Кнопка Microsoft Office , щелкните Параметры Excel , а затем выберите категорию Надстройки . В поле Управление выберите значение Надстройки Excel и нажмите кнопку Перейти . В поле Доступные надстройки установите флажок рядом с пунктом Поиск решения и нажмите кнопку ОК .

В Excel 2003 и ниже выберите команду Сервис/Надстройки , в появившемся диалоговом окне Надстройки установите флажок Поиск решения и щелкните на кнопке ОК. Если вслед за этим на экране появится диалоговое окно с предложением подтвердить ваши намерения, щелкните на кнопке Да. (Возможно, вам понадобится установочный компакт-диск Office).

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

2. Выделите целевую ячейку, которая должна принять необходимое значение, и выберите команду: - В Excel 2007 Данные/Анализ /Поиск решения ;

В Excel 2003 и ниже Tools > Solver (Сервис > Поиск решения). Поле Set Target Cell (Установить целевую ячейку) открывшегося диалогового окна надстройки Solver (Поиск решения) будет содержать адрес целевой ячейки. 3. Установите переключатели Equal To (Равной), задающие значение целевой ячейки, - Мах (максимальному значению), Min (минимальному значению) или Value of (значению). В последнем случае введите значение в поле справа. 4. Укажите в поле By Changing Cells (Изменяя ячейки), в каких ячейках программа должна изменять значения в поисках оптимального результата. 5. Создайте ограничения в списке Subject to the Constraints (Ограничения). Для этого щелкните на кнопке Add (Добавить) и в диалоговом окне Add Constraint (Добавление ограничения) определите ограничение.

6. Щелкните на кнопке на кнопке Options (Параметры), и в появившемся окне установите переключатель Неотрицательные значения (если переменные должны быть позитивными числами), Линейная модель (если задача, которую вы решаете, относится к линейным моделям)

7. Щелкнув на кнопке Solver (Выполнить), запустите процесс поиска решения.

8. Когда появится диалоговое окно Solver Results (Результаты поиска решения), выберите переключатель Keep Solve Solution (Сохранить найденное решение) или Restore Original Values (Восстановить исходные значения). 9. Щелкните на кнопке ОК.

Параметры средства Поиск решения Максимальное время - служит для ограничения времени, отпущенного на поиск решения задачи. В этом поле можно ввести время в секундах, не превышающее 32 767 (примерно девять часов); значение 100, используемое по умолчанию, вполне приемлемо для решения большинства простых задач.

Предельное число итераций - управляет временем решения задачи путем ограничения числа вычислительных циклов (итераций). Относительная погрешность - определяет точность вычислений. Чем меньше значение этого параметра, тем выше точность вычислений. Допустимое отклонение - предназначен для задания допуска на отклонение от оптимального решения, если множество значений влияющей ячейки ограничено множеством целых чисел. Чем больше значение допуска, тем меньше времени требуется на поиск решения. Сходимость - применяется только к нелинейным задачам. Когда относительное изменение значения в целевой ячейке за последние пять итераций становится меньше числа, указанного в поле Сходимость, поиск прекращается. Линейная модель - служит для ускорения поиска решения путем применения к задаче оптимизации линейной модели. Нелинейные модели предполагают использование нелинейных функций, фактора роста и экспоненциального сглаживания, что замедляет вычисления. Неотрицательные значения - позволяет установить нулевую нижнюю границу для тех влияющих ячеек, для которых не было задано соответствующее ограничение в диалоговом окне Добавить ограничение. Автоматическое масштабирование - используется, когда числа в изменяемых ячейках и в целевой ячейке существенно различаются. Показывать результаты итераций - приостанавливает поиск решения для просмотра результатов отдельных итераций. Загрузить модель - после щелчка на этой кнопке отрывается одноименное диалоговое окно, в котором можно ввести ссылку на диапазон ячеек, содержащих модель оптимизации. Сохранить модель - служит для отображения на экране одноименного диалогового окна, в котором можно ввести ссылку на диапазон ячеек, предназначенный для хранения модели оптимизации. Оценка линейная - выберите этот переключатель для работы с линейной моделью. Оценка квадратичная - выберите этот переключатель для работы с нелинейной моделью. Разности прямые - используется в большинстве задач, где скорость изменения ограничений относительно невысока. Увеличивает скорость работы средства Поиск решения. Разности центральные - используется для функций, имеющих разрывную производную. Данный способ требует больше вычислений, однако его применение может быть оправданным, если выдано сообщение о том, что получить более точное решение не удается. Метод поиска Ньютона - требует больше памяти, но выполняет меньше итераций, чем в методе сопряженных градиентов. Метод поиска сопряженных градиентов - реализует метод сопряженных градиентов, для которого требуется меньше памяти, но выполняется больше итераций, чем в методе Ньютона. Данный метод следует использовать, если задача достаточно большая и необходимо экономить память или если итерации дают слишком малое отличие в последовательных приближениях.

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

Цель работы : Изучение возможностей пакета 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, формулы отображающие уравнения системы и функцию цели.