Как решить уравнение в excel

Уравнения и задачи на подбор параметра в Excel

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

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

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

Данный инструмент следует применять для анализа данных с одним неизвестным (или изменяемым) условием. Например:

• y =7 является функцией x ;
• нам известно значение y , следует узнать при каком значении x мы получим y вычисляемый формулой.

Решим данную задачу встроенными вычислительными инструментами Excel для анализа данных:

1. Заполните ячейки листа, так как показано на рисунке:
2. Перейдите в ячейку B2 и выберите инструмент, где находится подбор параметра в Excel: «Данные»-«Работа с данными»-«Анализ что если»-«Подбор параметра».
3. В появившемся окне заполните поля значениями как показано на рисунке, и нажмите ОК:

В результате мы получили правильное значение 3.

Получили максимально точный результат: 2*3+1=7

Второй пример использования подбора параметра для уравнений

Немного усложним задачу. На этот раз формула выглядит следующим образом:

1. Заполните ячейку B2 формулой как показано на рисунке:
2. Выберите встроенный инструмент: «Данные»-«Работа с данными»-«Анализ что если»-«Подбор параметра» и снова заполните его параметрами как на рисунке (в этот раз значение 4):
3. Сравните 2 результата вычисления:

Обратите внимание! В первом примере мы получили максимально точный результат, а во втором – максимально приближенный.

Это простые примеры быстрого поиска решений формул с помощью Excel. Сегодня каждый школьник знает, как найти значение x. Например:

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

По умолчанию инструмент выполняет 100 повторений (итераций) с точностью 0.001. Если нужно увеличить количество повторений или повысить точность вычисления измените настройки: «Файл»-«Параметры»-«Формулы»-«Параметры вычислений»:

Таким образом, если нас не устраивает результат вычислений, можно:

1. Увеличить в настройках параметр предельного числа итераций.
2. Изменить относительную погрешность.
3. В ячейке переменной (как во втором примере, A3) ввести приблизительное значение для быстрого поиска решения. Если же ячейка будет пуста, то Excel начнет с любого числа (рандомно).

Используя эти способы настроек можно существенно облегчить и ускорить процесс поиска максимально точного решения.

О подборе нескольких параметров в Excel узнаем из примеров следующего урока.

Вычисление вложенной формулы по шагам

Иногда трудно понять, как вложенная формула вычисляет конечный результат, поскольку в ней выполняется несколько промежуточных вычислений и логических проверок. Но с помощью диалогового окна Вычисление формулы вы можете увидеть, как разные части вложенной формулы вычисляются в заданном порядке. Например, формулу =IF(AVERAGE(F2:F5)>50,SUM(G2:G5),0) проще понять, если вы увидите следующие промежуточные результаты:

Шаги, показанные в диалоговом окне

Сначала выводится вложенная формула. Функции СРЗНАЧ и СУММ вложены в функцию ЕСЛИ.

=ЕСЛИ(40>50;СУММ(G2:G5);0)

Диапазон ячеек F2:F5 содержит значения 55, 35, 45 и 25, поэтому функция СРЗНАЧ(F2:F5) возвращает результат 40.

=ЕСЛИ(Ложь;СУММ(G2:G5);0)

Значение 40 не больше 50, поэтому выражение в первом аргументе функции IF (аргумент logical_test) имеет значение false.

Функция ЕСЛИ возвращает значение третьего аргумента (аргумент значение_если_ложь). Функция СУММ не вычисляется, так как она является вторым аргументом функции IF (value_if_true аргумент) и возвращается только в том случае, если выражение имеет значение true.

1. Выделите ячейку, которую нужно вычислить. За один раз можно вычислить только одну ячейку.
2. На вкладке Формулы в группе Зависимости формул нажмите кнопку Вычислить формулу.

Примечание: Кнопка Шаг с заходом недоступна для ссылки, если ссылка используется в формуле во второй раз или если формула ссылается на ячейку в отдельной книге.

• Некоторые части формул, использующие функции IF и CHOOSE, не вычисляются, а #N/A отображается в поле Оценка .
• Если ссылка пуста, в поле Вычисление отображается нулевое значение (0).
• Формулы с циклическими ссылками могут не оцениваться должным образом. Если нужны циклические ссылки, можно включить итеративное вычисление.
• Следующие функции пересчитываются при каждом изменении листа и могут привести к тому, что средство «Оценка формулы » дает результаты, отличные от результатов, отображаемых в ячейке: RAND, OFFSET, CELL, INDIRECT, NOW, TODAY, RANDBETWEEN, INFO и SUMIF (в некоторых сценариях).

учимся
программировать

Программированию нельзя научить, можно только научится

Главная » Уроки по Численным методам » Урок 10. Матричные уравнения и их решение c помощью MS Excel

Урок №10. Матричные уравнения и их решение с помощью MS Excel

Матричные уравнения вида
Матричным уравнением называется выражение вида
=
или в краткой записи: =,
где: = = =
, заданные матрицы, det, неизвестная матрица, которую надо найти.

Под решением матричного уравнения будем понимать матрицу , которая обращает матричное уравнение в тождество.

Решение матричного уравнения: Искать решение матричного уравнения будем с помощью обратной матрицы = . Т.е. для того, чтобы найти решение матричного уравнения, необходимо найти обратную матрицу А и умножить ее на матрицу В.

Задание №1. Решить матричное уравнение
=

Ход решения:
Уравнение вида =. Искать решение будем по формуле: =
где: = = =

1. Введите исходные матрицы А и В на лист Excel (рис.1).

Рисунок 1. Исходные матрицы

2. Теперь необходимо найти обратную матрицу для матрицы . Найти ее самостоятельно, с помощью функции МОБР. Результат показан на рисунке 2.

Рисунок 2. Вычисленная с помощью функции МОБР обратная матрица

3. Далее необходимо найти произведение обратной матрицы и матрицы . Самостоятельно выполните произведение матриц с помощью функции МУМНОЖ. Результат показан на рисунке 3.

Рисунок 3. Умножение матриц и

4. Для ячеек H5 и H6 необходимо изменить формат. Для этого выделите эти две ячейки, щелкните правую кнопку мыши и из контекстного меню выберите пункт «Формат ячеек». В открывшемся окне на закладке «Число» выберите числовой формат «Числовой» и установите число десятичных знаков равным нулю (рис.4).

Рисунок 4. Формат ячеек

5. В итоге результат вычисления матричного уравнения есть матрица:

=

Составитель: Салий Н.А.

Excel Подбор параметра. Решение математических уравнений

Научимся решать сложные уравнение в Excel с помощью подбора параметра:

• 3 х + 9 х + 27 х = 14
• (x+1) 6 + (x+2) 6 + (x+2) 6 = 2

Решение

Разберем как решить первое уравнение. Сначала создадим таблицу (диапазон).

8	A	B
9	x (надо найти)	-1000
10	3 х	=3^D9
11	9 х	=9^D9
12	27 х	=27^D9
13	14	=D10+D11+D12

Ячейку B9 заполним любым числом. Это значение, которые мы будем подбирать.

Запускаем Подбор параметра: Меню → Данные → Анализ Что-Если → Подбор параметра.

• Установить в ячейке: B13

• Значение: 14

• Изменяя ячейку: B9

Примененные функции

• Подбор параметра

Курс Excel Подбор параметра

Номер урока	Урок	Описание
1	Excel Подбор параметра. Решение математических уравнений	Решем пару сложных математических уравнений с помощью подбора параметра.
2	Excel Подбор параметра. Решение математических задач	Через сколько часов один человек догонит другого. С помощью Подбора параметра решим 2 финансовые задачи. Сколько нужно откладывать, чтобы накопить нужную сумму. При какой ставке мы накопим нужную сумму, если будем откладывать, например, 20000 руб.
3	Excel Подбор параметра. Решение бизнес-задач	Сколько стаканов кофе нужно продать, чтобы выйти в ноль? Сколько стаканов кофе нужно продать, чтобы заработать 100000 рублей?