Как найти значение переменных методом подбора

«Подбор параметра» — ограниченный по функционалу вариант надстройки «Поиск решения». Это часть блока задач инструмента «Анализ «Что-Если»».

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

Где находится «Подбор параметра» в Excel

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

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

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

Когда условия задачи записаны, переходим на вкладку «Данные». «Работа с данными» — «Анализ «Что-Если»» — «Подбор параметра».

В поле «Установить в ячейке» задаем ссылку на ячейку с расчетной формулой (B4). Поле «Значение» предназначено для введения желаемого результата формулы. В нашем примере это сумма ежемесячных платежей. Допустим, -5 000 (чтобы формула работала правильно, ставим знак «минус», ведь эти деньги будут отдаваться). В поле «Изменяя значение ячейки» — абсолютная ссылка на ячейку с искомым параметром ($B$3).

После нажатия ОК на экране появится окно результата.

Чтобы сохранить, нажимаем ОК или ВВОД.

Функция «Подбор параметра» изменяет значение в ячейке В3 до тех пор, пока не получит заданный пользователем результат формулы, записанной в ячейке В4. Команда выдает только одно решение задачи.



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

Функция «Подбор параметра» идеально подходит для решения уравнений с одним неизвестным. Возьмем для примера выражение: 20 * х – 20 / х = 25. Аргумент х – искомый параметр. Пусть функция поможет решить уравнение подбором параметра и отобразит найденное значение в ячейке Е2.

В ячейку Е3 введем формулу: = 20 * Е2 – 20 / Е2.

А в ячейку Е2 поставим любое число, которое находится в области определения функции. Пусть это будет 2.

Запускам инструмент и заполняем поля:

«Установить в ячейке» — Е3 (ячейка с формулой);

«Значение» — 25 (результат уравнения);

«Изменяя значение ячейки» — $Е$2 (ячейка, назначенная для аргумента х).

Результат функции:

Найденный аргумент отобразится в зарезервированной для него ячейке.

Решение уравнения: х = 1,80.

Функция «Подбор параметра» возвращает в качестве результата поиска первое найденное значение. Вне зависимости от того, сколько уравнение имеет решений.

Если, например, в ячейку Е2 мы поставим начальное число -2, то решение будет иным.

Примеры подбора параметра в Excel

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

Пример 1. Метод подбора начальной суммы инвестиций (вклада).

Известные параметры:

• срок – 10 лет;
• доходность – 10%;
• коэффициент наращения – расчетная величина;
• сумма выплат в конце срока – желаемая цифра (500 000 рублей).

Внесем входные данные в таблицу:

Начальные инвестиции – искомая величина. В ячейке В4 (коэффициент наращения) – формула =(1+B3)^B2.

Вызываем окно команды «Подбор параметра». Заполняем поля:

После выполнения команды Excel выдает результат:

Чтобы через 10 лет получить 500 000 рублей при 10% годовых, требуется внести 192 772 рубля.

Пример 2. Рассчитаем возможную прибавку к пенсии по старости за счет участия в государственной программе софинансирования.

Входные данные:

• ежемесячные отчисления – 1000 руб.;
• период уплаты дополнительных страховых взносов – расчетная величина (пенсионный возраст (в примере – для мужчины) минус возраст участника программы на момент вступления);
• пенсионные накопления – расчетная величина (накопленная за период участником сумма, увеличенная государством в 2 раза);
• ожидаемый период выплаты трудовой пенсии – 228 мес.;
• желаемая прибавка к пенсии – 2000 руб.

С какого возраста необходимо уплачивать по 1000 рублей в качестве дополнительных страховых взносов, чтобы получить прибавку к пенсии в 2000 рублей:

1. Ячейка с формулой расчета прибавки к пенсии активна – вызываем команду «Подбор параметра». Заполняем поля в открывшемся меню.



2. Нажимаем ОК – получаем результат подбора.

Чтобы получить прибавку в 2000 руб., необходимо ежемесячно переводить на накопительную часть пенсии по 1000 рублей с 41 года.

Функция «Подбор параметра» работает правильно, если:

• значение желаемого результата выражено формулой;
• все формулы написаны полностью и без ошибок.

Как решать систему уравнений

О чем эта статья:

8 класс, 9 класс, ЕГЭ/ОГЭ

Основные понятия

Алгебра в 8 и 9 классе становится сложнее. Но если изучать темы последовательно и регулярно практиковаться в тетрадке и онлайн — ходить на уроки математики будет не так страшно.

Уравнение — это математическое равенство, в котором неизвестна одна или несколько величин. Значение неизвестных нужно найти так, чтобы при их подстановке в исходное уравнение получилось верное числовое равенство.

Например, возьмем 3 + 4 = 7. При вычислении левой части получается верное числовое равенство, то есть 7 = 7.

Уравнением можно назвать, например, равенство 3 + x = 7 с неизвестной переменной x, значение которой нужно найти. Результат должен быть таким, чтобы знак равенства был оправдан, и левая часть равнялась правой.

Система уравнений — это несколько уравнений, для которых надо найти значения неизвестных, каждое из которых соответствует данным уравнениям.

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

Линейное уравнение с двумя переменными

Уравнение вида ax + by + c = 0 называется линейным уравнением с двумя переменными x и y, где a, b, c — числа.

Решением этого уравнения называют любую пару чисел (x; y), которая соответствует этому уравнению и обращает его в верное числовое равенство.

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

Вот алгоритм построения графика ax + by + c = 0, где a ≠ 0, b ≠ 0:

Дать переменной 𝑥 конкретное значение x = x₁, и найти значение y = y₁ при ax₁ + by + c = 0.

Дать x другое значение x = x₂, и найти соответствующее значение y = y₂ при ax₂ + by + c = 0.

Построить на координатной плоскости xy точки: (x₁; y₁); (x₂; y₂).

Провести прямую через эти две точки и вуаля — график готов.

Нужно быстро привести знания в порядок перед экзаменом? Записывайтесь на курсы ЕГЭ по математике в Skysmart!

Система двух линейных уравнений с двумя переменными

Для ax + by + c = 0 можно сколько угодно раз брать произвольные значение для x и находить значения для y. Решений в таком случае может быть бесчисленное множество.

Система линейных уравнений (ЛУ) с двумя переменными образуется в случае, когда x и y связаны не одним, а двумя уравнениями. Такая система может иметь одно решение или не иметь решений совсем. Выглядит это вот так:

Из первого линейного уравнения a₁x + b₁y + c₁ = 0 можно получить линейную функцию, при условии если b₁ ≠ 0: y = k₁x + m₁. График — прямая линия.

Из второго ЛУ a₂x + b₂y + c₂ = 0 можно получить линейную функцию, если b₂ ≠ 0: y = k₂x + m₂. Графиком снова будет прямая линия.

Можно записать систему иначе:

Множеством решений первого ЛУ является множество точек, лежащих на определенной прямой, аналогично и для второго ЛУ. Если эти прямые пересекаются — у системы есть единственное решение. Это возможно при условии, если k₁ ≠ k₂.

Две прямые могут быть параллельны, а значит, они никогда не пересекутся и система не будет иметь решений. Это возможно при следующих условиях: k₁ = k₂ и m₁ ≠ m₂.

Две прямые могут совпасть, и тогда каждая точка будет решением, а у системы будет бесчисленное множество решений. Это возможно при следующих условиях: k₁ = k₂ и m₁ = m₂.

Метод подстановки

Разберем решение систем уравнений методом подстановки. Вот алгоритм при переменных x и y:

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

Подставить то, что получилось на место этой переменной в другое уравнение системы.

Решить полученное уравнение, найти одну из переменных.

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

Записать ответ. Ответ принято записывать в виде пар значений (x; y).

Потренируемся решать системы линейных уравнений методом подстановки.

Пример 1

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

x − y = 4
x + 2y = 10

Выразим x из первого уравнения:

x − y = 4
x = 4 + y

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

x + 2y = 10
4 + y + 2y = 10

Решим второе уравнение относительно переменной y:

4 + y + 2y = 10
4 + 3y = 10
3y = 10 − 4
3y = 6
y = 6 : 3
y = 2

Полученное значение подставим в первое уравнение вместо y и решим уравнение:

x − y = 4
x − 2 = 4
x = 4 + 2
x = 6

Ответ: (6; 2).

Пример 2

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

x + 5y = 7
3x = 4 + 2y

Сначала выразим переменную x из первого уравнения:

x + 5y = 7
x = 7 − 5y

Выражение 7 − 5y подставим вместо переменной x во второе уравнение:

3x = 4 + 2y
3 (7 − 5y) = 4 + 2y

Решим второе линейное уравнение в системе:

3 (7 − 5y) = 4 + 2y
21 − 15y = 4 + 2y
21 − 15y − 2y = 4
21 − 17y = 4
17y = 21 − 4
17y = 17
y = 17 : 17
y = 1

Подставим значение y в первое уравнение и найдем значение x:

x + 5y = 7
x + 5 = 7
x = 7 − 5
x = 2

Ответ: (2; 1).

Пример 3

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

x − 2y = 3
5x + y = 4

Из первого уравнения выразим x:

x − 2y = 3
x = 3 + 2y

Подставим 3 + 2y во второе уравнение системы и решим его:

5x + y = 4
5 (3 + 2y) + y = 4
15 + 10y + y = 4
15 + 11y = 4
11y = 4 − 15
11y = −11
y = −11 : 11
y = −1

Подставим получившееся значение в первое уравнение и решим его:

x − 2y = 3
x − 2 (−1) = 3
x + 2 = 3
x = 3 − 2
x = 1

Ответ: (1; −1).

Метод сложения

Теперь решим систему уравнений способом сложения. Алгоритм с переменными x и y:

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

Складываем почленно левые и правые части уравнений системы.

Решаем получившееся уравнение с одной переменной.

Находим соответствующие значения второй переменной.

Запишем ответ в в виде пар значений (x; y).

Система линейных уравнений с тремя переменными

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

Решений в таком случае может быть бесчисленное множество. Придавая двум переменным различные значения, можно найти третье значение. Ответ принято записывать в виде тройки значений (x; y; z).

Если x, y, z связаны между собой тремя уравнениями, то образуется система трех ЛУ с тремя переменными. Для решения такой системы можно применять метод подстановки и метод сложения.

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

Разберем примеры решения систем уравнений.

Задание 1. Как привести уравнение к к стандартному виду ах + by + c = 0?

5x − 8y = 4x − 9y + 3

5x − 8y = 4x − 9y + 3

5x − 8y − 4x + 9y = 3

Задание 2. Как решать систему уравнений способом подстановки

Выразить у из первого уравнения:

Подставить полученное выражение во второе уравнение:

Найти соответствующие значения у:

Задание 3. Как решать систему уравнений методом сложения

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

1. Решаем полученное квадратное уравнение любым способом. Находим его корни:

1. Найти у, подставив найденное значение в любое уравнение:

1. Ответ: (1; 1), (1; -1).

Задание 4. Решить систему уравнений

Решим второе уравнение и найдем х = 2, х = 5. Подставим значение переменной х в первое уравнение и найдем соответствующее значение у.

Задание 5. Как решить систему уравнений с двумя неизвестными

При у = -2 первое уравнение не имеет решений, при у = 2 получается:

Решение систем уравнений

Содержание:

Графический метод решения систем уравнений

Вспоминаем то, что знаем

Что такое график уравнения с двумя неизвестными?

Что представляет собой график линейного уравнения с двумя неизвестными?

Решите графическим методом систему линейных уравнений:

Открываем новые знания

Решите графическим методом систему уравнений:

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

В курсе алгебры 7-го класса вы изучали системы линейных уравнений.

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

По этой ссылке вы найдёте полный курс лекций по высшей математике:

Начнём с графического метода

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

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

Таким образом, решить графически систему уравнений — значит найти общие точки графиков уравнений, входящих в систему.

Возможно вам будут полезны данные страницы:

Примеры с решением

Пример 1:

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

Построим графики уравнений

Графиком первого уравнения является парабола, с вершиной в точке (0; 1) и ветвями, направленными вверх, графиком второго — прямая, проходящая через точки (0; 3) и (-3; 0).

Парабола и прямая пересекаются в точках А(2; 5) и В(— 1; 2).

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

Ответ: (2; 5) и (-1; 2).

Пример 2:

Выясним количество решений системы уравнений:

Построим графики уравнений

Графики этих уравнений — окружности. Центр первой окружности — начало координат, а её радиус равен 2; центр второй окружности — точка Р(1; — 1), её радиус равен 3.

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

Ответ: Два решения.

Решение систем уравнений методом подстановки

Вспоминаем то, что знаем

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

Решите систему линейных уравнений методом подстановки:

Открываем новые знания

Как вы думаете, можно ли применять метод подстановки при решении систем, где не все уравнения являются линейными? При каком условии это удастся сделать?

Решите систему уравнений методом подстановки:

Как решить систему двух уравнений с двумя неизвестными методом подстановки?

Всякую ли систему двух уравнений с двумя неизвестными можно решить методом подстановки?

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

Теперь познакомимся с системами, в которых хотя бы одно уравнение не является линейным. Как и прежде, распространённым методом решения систем является метод подстановки.

Пример 3:

Пусть (х; у) — решение системы.

Выразим х из уравнения

Подставим найденное выражение в первое уравнение:

Решим полученное уравнение:

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

Чуть сложнее дело обстоит в следующем примере.

Пример 4:

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

Пусть (х; у) — решение системы.

Выразим у из линейного уравнения:

Подставим найденное выражение в первое уравнение системы:

После преобразований получим:

Ответ: (-0,5; 0,5), (4; 5).

Если это целесообразно, то можно осуществлять подстановку некоторого выражения «в целом».

Пример 5:

Подставим во второе уравнение тогда его можно переписать в виде:

Теперь выразим х через у из первого уравнения системы:

Подставим в полученное ранее уравнение ху = 2:

Корни этого уравнения:

.

Иногда решить систему можно, используя метод алгебраического сложения.

Пример 6:

Сложим уравнения, предварительно умножив первое уравнение на —1. В результате получим:

.

Корни этого уравнения:

Подставим найденные значения в первое уравнение. Рассмотрим два случая:

1)

2) , получим уравнение корней нет.

Иногда упростить решение удаётся, используя различные варианты замены неизвестных.

Пример 7:

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

Обозначим

Второе уравнение системы примет вид:

Решим полученное уравнение. Получим, умножая обе части на 2а:

Осталось решить методом подстановки линейные системы:

Ответ: (2; 1), (1; 2). Решение задач с помощью систем уравнений Знакомимся с новыми знаниями

Напомним, что при решении задач обычно действуют следующим образом:

1) обозначают буквами какие-нибудь неизвестные величины, выражают через них другие величины, составляют систему уравнений;

2) решают полученную систему;

3) отвечают на вопрос задачи.

Пример 8:

Периметр прямоугольника равен 34 см, а его диагональ 13 см. Найдите стороны прямоугольника.

Пусть х см — длина, у см — ширина (х у), тогда периметр прямоугольника — см.

Воспользуемся теоремой Пифагора:

Решим систему. Выразим из первого уравнения у:

Подставим во второе уравнение:

Корни уравнения:

Найдём

С учётом условия получим ответ: длина — 12 см, ширина — 5 см.

Пример 9:

Если произведение двух положительных чисел увеличить на первое из них, то получится 128. Если это же произведение увеличить на второе из них то получится 135. Найдите эти числа.

Пусть х — первое число, у — второе число.

Тогда: — произведение, увеличенное на первое число, ху 4-у — произведение, увеличенное на второе число.

Вычтем из второго уравнения первое. Получим:

Дальше будем решать методом подстановки:

Подставим в первое уравнение выражение для у:

Корни уравнения: (не подходит по смыслу задачи).

Найдём у из уравнения:

Получим ответ: 16 и 7.

Симметричные системы уравнений с двумя неизвестными

Уравнение с двумя неизвестными называется симметричным, если при перестановке этих неизвестных местами уравнение не меняется. Например, уравнение симметричное, так как при перестановке входящих в него неизвестных оно приобретает вид , то есть не меняется. А вот уравнение не симметричное, так как при перестановке входящих в него неизвестных оно приобретает вид , то есть меняется.

Система двух уравнений с двумя неизвестными называется симметричной, если каждое уравнение этой системы симметричное.

ПРЕДУПРЕЖДЕНИЕ. В определении симметричной системы уравнений требуется, чтобы каждое уравнение в отдельности не менялось.

Например, если в системе уравнений

переставить местами неизвестные х и у, то получим систему:

Видно, что система в целом не изменилась (уравнения поменялись местами по сравнению с первоначальной системой). Но такая система не является симметричной, так как каждое из уравнений в отдельности изменилось.

Убедитесь, что симметричные системы с двумя неизвестными х и у можно решать с помощью замены неизвестных:

Сначала научитесь выражать через неизвестные выражения:

Присылайте задания в любое время дня и ночи в ➔

Официальный сайт Брильёновой Натальи Валерьевны преподавателя кафедры информатики и электроники Екатеринбургского государственного института.

Все авторские права на размещённые материалы сохранены за правообладателями этих материалов. Любое коммерческое и/или иное использование кроме предварительного ознакомления материалов сайта natalibrilenova.ru запрещено. Публикация и распространение размещённых материалов не преследует за собой коммерческой и/или любой другой выгоды.

Сайт предназначен для облегчения образовательного путешествия студентам очникам и заочникам по вопросам обучения . Наталья Брильёнова не предлагает и не оказывает товары и услуги.

Системы уравнений

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

Решить систему уравнений — это значит, найти общие решения для всех уравнений системы или убедиться, что решения нет.

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

Способ подстановки

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

Рассмотрим решение системы уравнений:

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

Так как x, на основании определения системы уравнений, имеет такое же значение и во втором уравнении, то подставляем его значение во второе уравнение и получаем уравнение с одним неизвестным:

Решаем полученное уравнение, чтобы найти, чему равен y. Как решать уравнения с одним неизвестным, вы можете посмотреть в соответствующей теме.

Мы определили что y = 1. Теперь, для нахождения численного значения x, подставим значение y в преобразованное первое уравнение, где мы ранее нашли, какому выражению равен x:

x = 2 + 4y = 2 + 4 · 1 = 2 + 4 = 6.

Способ сравнения

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

Например, для решение системы:

найдём в обоих уравнениях, чему равен y (можно сделать и наоборот — найти, чему равен x):

Составляем из полученных выражений уравнение:

Решаем уравнение, чтобы узнать значение x:

Теперь подставляем значение x в первое или второе уравнение системы и находим значение y:

Способ сложения или вычитания

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

Уравняем коэффициенты при неизвестном y, умножив все члены второго уравнения на -2:

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

Находим значение x (x = 6). Теперь, подставив значение x в любое уравнение системы, найдём y = 1.

Если уравнять коэффициенты у x, то, для исключения этого неизвестного, нужно было бы вычесть одно уравнение из другого.

Уравняем коэффициенты при неизвестном x, умножив все члены первого уравнения на 3:

(x — 4y) · 3 = 2 · 3

Теперь вычтем по частям второе уравнение из первого, чтобы получить уравнение с одним неизвестным:

Находим значение y (y = 1). Теперь, подставив значение y в любое уравнение системы, найдём x = 6:

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

Любое уравнение системы можно заменить на уравнение, получаемое путём сложения (или вычитания) уравнений, входящих в систему. При этом получается система уравнений, имеющая те же решения, что и исходная.

Обычно при создании формулы пользователь задает значения параметров и формула (уравнение) возвращает результат. Например, имеется уравнение 2*a+3*b=x, заданы параметры а=1, b=2, требуется найти x (2*1+3*2=8). Инструмент Подбор параметра позволяет решить обратную задачу: подобрать такое значение параметра, при котором уравнение возвращает желаемый целевой результат X. Например, при a=3, требуется найти такое значение параметра b, при котором X равен 21 (ответ b=5). Подбирать параметр вручную — скучное занятие, поэтому в MS EXCEL имеется инструмент Подбор параметра

.

В MS EXCEL 2007-2010 Подбор параметра находится на вкладке

Данные,

группа

Работа с данным

.

Простейший пример

Найдем значение параметра

b

в уравнении

2*а+3*b=x

, при котором

x=21

, параметр

а=

3

.

Подготовим исходные данные.

Значения параметров

а

и

b

введены в ячейках

B8

и

B9

. В ячейке

B10

введена формула

=2*B8+3*B9

(т.е. уравнение

2*а+3*b=x

).

Целевое значение x

в ячейке

B11

введено

для информации.

Выделите ячейку с формулой

B10

и вызовите

Подбор параметра (на вкладке

Данные

в группе

Работа с данными

выберите команду

Анализ «что-если?»

, а затем выберите в списке пункт

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

…)

.

В качестве целевого значения для ячейки

B10

укажите 21, изменять будем ячейку

B9

(параметр

b

).

Нажмите ОК.

Инструмент

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

подобрал значение параметра

b

равное 5.

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

B9

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

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

параметр будет подобрать сложно

.

Примечание

: Уравнение

2*а+3*b=x

является линейным, т.е. при заданных

a

и

х

существует только одно значение

b

, которое ему удовлетворяет. Поэтому инструмент

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

работает (именно для решения таких линейных уравнений он и создан). Если пытаться, например, решать с помощью Подбора параметра квадратное уравнение (имеет 2 решения), то инструмент решение найдет, но только одно. Причем, он найдет, то которое ближе к начальному значению (т.е. задавая разные начальные значения, можно найти оба корня уравнения). Решим квадратное уравнение x^2+2*x-3=0 (уравнение имеет 2 решения: x1=1 и x2=-3). Если в изменяемой ячейке введем -5 (начальное значение), то

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

найдет корень = -3 (т.к. -5 ближе к -3, чем к 1). Если в изменяемой ячейке введем 0 (или оставим ее пустой), то Подбор параметра найдет корень = 1 (т.к. 0 ближе к 1, чем к -3). Подробности в

файле примера

на листе

Простейший

.

Еще один путь нахождения неизвестного параметра b в уравнении 2*a+3*b=X — аналитический. Решение b=(X-2*a)/3) очевидно. Понятно, что не всегда удобно искать решение уравнения аналитическим способом, поэтому часто используют метод последовательных итераций, когда неизвестный параметр подбирают, задавая ему конкретные значения так, чтобы полученное значение х стало равно целевому X (или примерно равно с заданной точностью).

Калькуляция, подбираем значение прибыли

Еще пример. Пусть дана структура цены договора: Собственные расходы, Прибыль, НДС.

Известно, что Собственные расходы составляют 150 000 руб., НДС 18%, а Целевая стоимость договора 200 000 руб. (ячейка

С13

). Единственный параметр, который можно менять, это Прибыль. Подберем такое значение Прибыли (

С8

), при котором Стоимость договора равна Целевой, т.е. значение ячейки Расхождение (

С14

) равно 0.

В структуре цены в ячейке

С9

(Цена продукции) введена формула Собственные расходы + Прибыль (

=С7+С8

). Стоимость договора (ячейка

С11

) вычисляется как Цена продукции + НДС (=

СУММ(С9:C10)

).

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

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

.

Выделите ячейку

С14

, вызовите

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

(на вкладке

Данные

в группе

Работа с данными

выберите команду

Анализ «что-если?»

, а затем выберите в списке пункт

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

…). В качестве целевого значения для ячейки

С14

укажите 0, изменять будем ячейку

С8

(Прибыль).

Нажмите ОК.

Теперь, о том когда этот инструмент работает. 1. Изменяемая ячейка не должна содержать формулу, только значение.2. Необходимо найти только 1 значение, изменяя 1 ячейку. Если требуется найти 1 конкретное значение (или оптимальное значение), изменяя значения в НЕСКОЛЬКИХ ячейках, то используйте Поиск решения.3. Уравнение должно иметь решение, в нашем случае уравнением является зависимость стоимости от прибыли. Если целевая стоимость была бы равна 1000, то положительной прибыли бы у нас найти не удалось, т.к. расходы больше 150 тыс. Или например, если решать уравнение x2+4=0, то очевидно, что не удастся подобрать такое х, чтобы x2+4=0

Примечание

: В файле примера приведен алгоритм решения Квадратного уравнения с использованием Подбора параметра.

Подбор суммы кредита

Предположим, что нам необходимо

определить максимальную сумму кредита

, которую мы можем себе позволить взять в банке. Пусть нам известна сумма ежемесячного платежа в рублях (1800 руб./мес.), а также процентная ставка по кредиту (7,02%) и срок на который мы хотим взять кредит (180 мес).

В EXCEL существует функция

ПЛТ()

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

статьи про аннуитет

). Но эта функция нам не подходит, т.к. сумму ежемесячного платежа мы итак знаем, а вот сумму кредита (параметр функции

ПЛТ()

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

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

сумму займа пришлось бы подбирать в ручную с помощью функции

ПЛТ()

или использовать соответствующую формулу.

Введем в ячейку

B

6

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

B

7

, % ставку по кредиту введем в ячейку

B8,

а формулу

=ПЛТ(B8/12;B7;B6)

для расчета суммы ежемесячного платежа в ячейку

B9

(см.

файл примера

).

Чтобы найти сумму займа соответствующую заданным выплатам 1800 руб./мес., делаем следующее:

• на вкладке
  
  Данные
  
  в группе
  
  Работа с данными
  
  выберите команду
  
  Анализ «что-если?»
  
  , а затем выберите в списке пункт
  
  Подбор параметра
  
  …;
• в поле
  
  Установить
  
  введите ссылку на ячейку, содержащую формулу. В данном примере — это ячейка
  
  B9
  
  ;
• введите искомый результат в поле
  
  Значение
  
  . В данном примере он равен
  
  -1800
  
  ;
• В поле
  
  Изменяя значение ячейки
  
  введите ссылку на ячейку, значение которой нужно подобрать. В данном примере — это ячейка
  
  B6
  
  ;
• Нажмите ОК

Что же сделал

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

? Инструмент

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

изменял по своему внутреннему алгоритму сумму в ячейке

B6

до тех пор, пока размер платежа в ячейке

B9

не стал равен 1800,00 руб. Был получен результат — 200 011,83 руб. В принципе, этого результата можно было добиться, меняя сумму займа самостоятельно в ручную.

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

подбирает значения только для 1 параметра. Если Вам нужно найти решение от нескольких параметров, то используйте

инструмент

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

. Точность подбора параметра можно задать через меню

.

Вопросом об единственности найденного решения

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

не занимается, вероятно выводится первое подходящее решение.

Иными словами, инструмент

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

позволяет сэкономить несколько минут по сравнению с ручным перебором.

Уточнение корней средством “Подбор параметра”

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

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

MS
Excel
изменяет

значение

в одной
конкретной

ячейке
до
тех
пор,
пока
вычисления

по
формуле,

ссылающейся
на
эту ячейку,
не
дадут
нужного
результата.

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

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

СредствоПодбор
параметра
вызывается командой: лента Данные
→ группа Работа
с данными
→Анализ
«что-если»
→ Подбор
параметра (рис.8).

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

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

ПРИМЕР

Вычислить
корень уравнения f(x)
= -5х + 6 = 0.

Вячейку В2 введем любое число, например,
0.

В
ячейку В3 введем формулу =-5*В2+6.

Вызовем
диалоговое окно Подбор параметра и
заполним соответствующие поля. После
нажатия на кнопкуОК
Excel
выведет окно диалога Результат
подбора параметра. Если
подобранное значение необходимо
сохранить, то нажмите на ОК,
и результат будет сохранен в ячейке,
заданной ранее в поле Изменяя
значения ячейки.
Для восстановления значения, которое
было в ячейке В2 до использования команды
Подбор
параметра,
нажмите кнопку Отмена.

Как
видно из примера в ячейке B2
установилось точное значение корня
уравнения Х
=
1,2.

При подборе
параметра Excel
использует итерационный (циклический)
процесс. Количество итераций и точность
устанавливаются в меню Сервис
| Параметры … |
вкладка
Вычисления,
в которой
задается Предельное
число итераций
(по умолчанию 100) и Относительная
погрешность
(по умолчанию 0,001).

Рис. 9. Диалоговое
окно Параметры

Если Excel
выполняет сложную задачу подбора
параметра, можно нажать кнопку Пауза
в окне диалога Результат
подбора параметра
и прервать вычисление, а затем нажать
кнопку Шаг,
чтобы выполнить очередную итерацию и
посмотреть результат. При решении задачи
в пошаговом режиме появляется кнопка
Продолжить
− для возврата в обычный режим подбора
параметра.

Возьмем
в
качестве
примера
уравнение
(рис. 1)

f(x)
= Х⁵
+ 2Х⁴
+ 5Х³
+ 8Х²
− 7Х – 3 = 0.

Для нахождения
корней
уравнения
с помощью средства Подбор
параметра
выполним
следующие
действия:

— В таблице функции
(рис.1) выявляем интервалы локализации
корней уравнения (перемена знака в
значении функции): первый интервал
ячейки Е20:Е21, значение (-1,2698 и 3); второй
интервал ячейки Е37:Е38, значение (0,80096 и
-0,3012); третий интервал ячейки Е48:Е49,
значение (-1,6167 и 0,22688);

— В каждом интервале
выбираем то значение функции, которое
ближе к 0 и составляем пары ячеек
«аргумент-значение»: первый корень
D20:E20;
второй корень D38:E38;
третий корень D49:E49.

— Уточняем значения
корней средством Подбор
параметра (рис.
10, 11, 12).

Рис. 10. Корень
уравнения Х₁
= -2,073

Рис. 11. Корень
уравнения Х₂
= -0,32804

Рис. 12. Корень
уравнения Х₃
= 0,78934

Ответ: Х₁
= -2,073; Х₂
= -0,328; Х₃
= 0,789.

15

Соседние файлы в предмете [НЕСОРТИРОВАННОЕ]

• #
• #
• #
• #
• #
• #
• #
• #
• #
• #
• #