Подбор нескольких параметра в excel

Как в Excel применить функцию «Подбор параметра»

Чтобы применить средство Подбор параметра, выполните команду Данные → Работа с данными → Подбор параметра. Откроется одноименное диалоговое окно, в котором надо заполнить все поля ввода, а затем щелкнуть на кнопке ОК. В результате появится диалоговое окно Результат подбора параметра.

Диалоговое окно Подбор параметра очень просто в использовании — в нем надо заполнить всего три поля ввода: Установить в ячейке, Значение и Изменяя значение ячейки, которые показаны на рис. 1.4.

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

Вот какую последовательность действий надо выполнить в открытом диалоговом окне Подбор параметра.

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

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

Обратите внимание на два числа, отображаемые в этом окне как Подбираемое значение и Текущее значение

Подбираемое значение, — это то значение, которое вы указали в поле Значение диалогового окна Подбор параметра, а Текущее значение — то значение, которое Excel смогла добиться от формулы (указанной в поле Установить в ячейке диалогового окна Подбор параметра) при подборе параметра, заданного в поле Изменяя значение ячейки того же окна Подбор параметра. Если числа Подбираемое значение и Текущее значение совпадают, это означает, что Excel действительно нашла решение задачи.

Для примера рассмотрим рабочий лист, показанный на рис. 1.5, где в ячейке А1 содержится значение градусов по Фаренгейту, а в ячейке А2 записана формула =ПРЕОБР(А1;»Р»;»С»), преобразующая значение градусов по Фаренгейту в значение градусов по Цельсию. Введите значение 100 в ячейку А1 и вы получите значение 37,8 в ячейке А2. Допустим, теперь вы хотите узнать, сколько градусов по Фаренгейту составляют 20 градусов по Цельсию.

Рис. 1.5. Преобразование значения температуры по Фаренгейту в значение температуры по Цельсию

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

1. Выберите команду Данные → Работа с данными → Подбор параметра. Откроется диалоговое окно Подбор параметра.
2. В поле ввода Установить в ячейке введите А2 или щелкните на ячейке А2.
3. В поле ввода Значение введите число 20.
4. В поле ввода Изменяя значение ячейки введите А1 или щелкните на ячейке А1.
5. Щелкните на кнопке ОК.

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

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

Чуть-чуть истории и теории.

Вы задумывались когда-нибудь — откуда и зачем в головах людей, живших в XVI…XVII веках, родились понятия дифференциалов, производных, интегралов? Объяснение, в общем-то, достаточно простое и понятное – эти ученые искали аналитические пути решения прикладных практических задач. И успешно находили.

Мне сегодня видится приблизительно такая «лестница» с качественными «ступенями инструментов» математики для решения практических и научных задач, которую изобрело человечество:

1. Арифметика — сложение, вычитание, умножение, деление.

2. Алгебра – применение элементарных функций (степенной, логарифмической, тригонометрической, …) и алгебраических уравнений функции одной переменной.

3. Гауссовские системы линейных уравнений.

4. Численные методы решения трансцендентных уравнений.

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

6. Дифференцирование и интегрирование функций одной переменной.

7. Дифференцирование и интегрирование функций нескольких переменных.

8. Системы дифференциальных и интегральных уравнений.

9. Масса разнообразных новых и старых специальных методик и подходов мне не известных и известных, но, безусловно, существующих и работающих.

Предлагаю остановиться и разобраться с достаточно высокой четвертой ступенью «лестницы».

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

Каждый из методов имеет свои достоинства и недостатки — они подробно описаны в литературе, и углубляться в них мы не будем. Скажу только, что из вышеперечисленных методов мне на практике довелось использовать все. При решении различных (в основном геометрических и теплотехнических) задач по разным причинам было удобно использовать то один, то другой подход. Метод Ньютона хорош своей быстрой сходимостью и простотой формулы. Комбинированный метод секущих-хорд на основе итерационной формулы Ньютона не требует нахождения производных, быстро «сходится», и главное – не требует анализа функции на сходимость. Метод половинного деления медленно сходится, но не требует никакого предварительного анализа функции.

Ищем оптимальное решение задачи с неизвестными параметрами в Excel

«Поиск решений» — функция Excel, которую используют для оптимизации параметров: прибыли, плана продаж, схемы доставки грузов, маркетингового бюджета или рентабельности. Она помогает составить расписание сотрудников, распределить расходы в бизнес-плане или инвестиционные вложения. Знание этой функции экономит много времени и сил.

Предположим, у вас есть задача: оптимизировать расходы на производство 1 000 изделий. На это есть 30 дней и четыре работника, для которых известна производительность и оплата за изделие.

Решить задачу можно тремя способами. Во-первых, вручную перебирать параметры, пока не найдется оптимальное соотношение. Во-вторых, составить уравнение с большим количеством неизвестных. В-третьих, вбить данные в Excel и использовать «Поиск решений». Последний способ самый быстрый — если знать, как использовать функцию.

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

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

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

При заполнении функции «Поиск решений» важно оставить ячейки пустыми — программа сама найдет значения

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

Ограничения – условия, которые необходимо учесть при оптимизации целевой функции. К ним относятся размеры инвестирования, срок реализации проекта или объем покупательского спроса. В нашем случае — количество дней и число работников.

Теперь перейдем к самой функции.

1) Чтобы включить «Поиск решений», выполните следующие шаги:

• нажмите «Параметры Excel», а затем выберите категорию «Надстройки»;
• в поле «Управление» выберите значение «Надстройки Excel» и нажмите кнопку «Перейти»;
• в поле «Доступные надстройки» установите флажок рядом с пунктом «Поиск решения» и нажмите кнопку ОК.

2) Теперь упорядочим данные в виде таблицы, отражающей связи между ячейками. Советуем использовать цветовые обозначения: на примере красным выделена целевая функция, бежевым — ограничения, а желтым – изменяемые ячейки.

Не забудьте ввести формулы. Стоимость заказа рассчитывается как «Оплата труда за 1 изделие» умножить на «Число заготовок, передаваемых в работу». Для того, чтобы узнать «Время на выполнение заказа», нужно «Число заготовок, передаваемых в работу» разделить на «Производительность».

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

4) Заполните параметры «Поиска решений» и нажмите «Найти решение».

Совокупная стоимость 1000 изделий рассчитывается как сумма стоимостей количества изделий от каждого работника. Данная ячейка (Е13) — это целевая функция. D9:D12 — изменяемые ячейки. «Поиск решений» определяет их оптимальные значения, чтобы целевая функция достигла минимума при заданных ограничениях.

В нашем примере следующие ограничения:

• общее количество изделий 1000 штук ($D$13 = $D$3);
• число заготовок, передаваемых в работу — целое и больше нуля либо равно нулю ($D$9:$D$12 = целое, $D$9:$D$12 > = 0);
• количество дней меньше либо равно 30 ($F$9:$F$12 > окажут вам помощь. Это отличный шанс вместе экспертом проработать проблемные вопросы и составить карьерный план.

Использование

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

1. Установить в ячейке – место, куда необходимо вставить ссылку на формулу.
2. Значение – сюда вводится числовое значение, которое необходимо получить в ходе расчетов.
3. Изменяя значение ячейки – ссылка на число, которое и будет решением задачи.

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

Рассмотрим примеры применения подбора параметра.

Задача 1.

Найти решение уравнения с одной неизвестной 2*x^2 — x/3=12

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

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

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

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

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

Задача 2.

Рассчитать процентную ставку по кредиту в 10000$ сроком на два с половиной года.

Запишем исходные данные в таблицу.

Чтобы посчитать сумму платежа, воспользуемся встроенной функцией excel – ПЛТ. Она состоит из процентной ставки, периода выплат и величины кредита. Значением процента задаемся произвольно.

Вызываете функцию подбор параметра и заполняете форму, при этом платеж будет составлять 400$. Поскольку это финансовая формула, то не забывайте знак минус.

В итоге получаете следующие результаты:

Как видите, существует несколько областей применения функции подбор параметра

Важно помнить, что для поиска решения можно использовать только один аргумент и желаемая величина должна быть выражена в виде формулы

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

Подбор параметра в MS EXCEL

Обычно при создании формулы пользователь задает значения параметров и формула (уравнение) возвращает результат. Например, имеется уравнение 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 решения), то инструмент решение найдет, но только одно. Причем, он найдет, то которое ближе к начальному значению (т.е. задавая разные начальные значения, можно найти оба корня уравнения).

Решим квадратное уравнение x2+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)).

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

Нажмите ОК.

Теперь, о том когда этот инструмент работает. 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 параметра. Если Вам нужно найти решение от нескольких параметров, то используйте инструмент Поиск решения . Точность подбора параметра можно задать через меню Кнопка офис/ Параметры Excel/ Формулы/ Параметры вычислений . Вопросом об единственности найденного решения Подбор параметра не занимается, вероятно выводится первое подходящее решение.

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

Трансцендентные уравнения. Два метода решения в Excel.

Если у вас на компьютере нет программы MS Excel, то расчеты можно выполнить в программе OOo Calc из бесплатного пакета Open Office.

Задач, которые требуют для получения ответа составления и решения трансцендентных уравнений, вокруг нас очень много. Это — задачи и физики, и теплотехники, и астрономии, и элементарной геометрии в обычной жизни… Инженерам-конструкторам и программистам в повседневной работе необходимо уметь составлять и быстро решать численными методами нелинейные уравнения. На мой взгляд — это один из критериев профессионализма. Более того, уравнения, которые решаются аналитически, сегодня иногда гораздо проще и быстрее при наличии вычислительной техники решить численными методами, поэтому нужно уметь это делать.

Вычисление угла зацепления зубчатой передачи методом Ньютона (методом касательных)

Рассмотрим пример из статьи «Расчет геометрии зубчатой передачи». Необходимо найти угол зацепления зубчатой передачи atw . Я обещал в той статье рассказать, как это делается. Выполняю обещание.

Если расстояние между центрами колеса и шестерни не задано, то угол зацепления можно вычислить путем решения трансцендентного уравнения:

inv ( atw )=tg ( atw ) — atw =2* xs *tg ( a )/( z2 + T * z1 )+ tg ( at ) — at

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

inv ( atw )=0,020910

f ( atw )=tg ( atw )— atw -0,020910=0

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

f’( atw )=1/(cos ( atw ))^2—1

atw (i+1) = atw i — f ( atw ) i/ f’( atw ) i

Открываем файл Excel и начинаем работу.

Исходные данные будем традиционно писать в ячейки со светло-бирюзовой заливкой. Результаты расчетов будем считывать в ячейках со светло-желтой заливкой.

1. Инволюту угла зацепления inv( atw ) заносим

в ячейку D3: 0,020910

2. Значение угла зацепления в нулевом приближении atw в радианах записываем

3. Итерационную формулу atw (i+1)= atw i— f( atw )i f’( atw )i заносим

в D5: =D4- (TAN (D4) -D4-$D$3)/(1/(COS (D4))^2-1) =0,591706

atw 1= atw 0- (tg ( atw 0) — atw 0- inv ( atw ))/(1/(cos ( atw 0))^2-1)

и копируем в ячейки D6… D14

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

atw =D13- (TAN (D13) -D13-$D$3)/(1/(COS (D13))^2-1) =0,389140

Решение найдено, расчет в Excel завершен!

Решение задачи ландшафтного дизайна с помощью сервиса «Подбор параметра» в Excel

Задача:

Вдоль отмостки стены дома длиной 14 метров необходимо разбить цветник в виде сегмента круга площадью ровно 16 квадратных метров. На сколько метров цветник будет отстоять от края отмостки по центру стены? Каким радиусом необходимо выполнить границу цветника?

1. Длину отмостки стены дома — хорды сегмента круга x в метрах записываем

в ячейку D17: 14,000

2. Площадь цветника – сегмента круга S в квадратных метрах вписываем

в D18: 16,000

3. Предположительное произвольное (не нулевое) значение центрального угла сегмента a в радианах пишем

Трансцендентное уравнение a / sin( a /2 ) -2*cos ( a /2) — (8* S / x ^2) *sin( a /2)=0 вводим

в объединенную ячейку E19F19: =D19/SIN (D19/2) -2*COS (D19/2) — (8*D18/D17^2)*SIN (D19/2)

Включаем сервис «Подбор параметра» в Excel: «Сервис» – «Подбор параметра». Пишем в появившемся окне все как на рисунке слева и нажимаем кнопку OK.

В появившемся новом окне видим, что решение найдено, снова нажимаем на кнопку OK.

Считываем искомое значение центрального угла сегмента a в радианах

в D19: 0,950057

При этом видим, что значение трансцендентного уравнения равно нулю; считываем

в объединенной ячейке E19F19: =D19/SIN (D19/2) -2*COS (D19/2) — (8*D18/D17^2)*SIN (D19/2) =0

4. Радиус наружной границы цветника – радиус сегмента круга r в метрах рассчитывается

в D20: =D17/2/SIN (D19/2) =15,305

r = x /2sin( a /2)

5. Максимальная ширина цветника – высота сегмента круга h в метрах рассчитывается

в ячейке D21: =D20*(1-COS (D19/2)) =1.695

h = r *(1- cos( a /2))

Ответы получены, вторая задача успешно решена!

Я не приводил вывода использованных формул потому, что это не по теме поста, и, думаю, с геометрией и тригонометрией вы легко разберетесь. Будут вопросы – обращайтесь.

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

Функции программы Microsoft Excel: подбор параметра

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

Скачать последнюю версию Excel

• Суть функции
• Применение функции на практике
• Решение уравнений

Суть функции

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

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

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

Применение функции на практике

Для того, чтобы понять, как работает данная функция, лучше всего объяснить её суть на практическом примере. Мы будем объяснять работу инструмента на примере программы Microsoft Excel 2010, но алгоритм действий практически идентичен и в более поздних версиях этой программы, и в версии 2007 года.

Имеем таблицу выплат заработной платы и премии работникам предприятия. Известны только премии работников. Например, премия одного из них — Николаева А. Д, составляет 6035,68 рублей. Также, известно, что премия рассчитывается путем умножения заработной платы на коэффициент 0,28. Нам предстоит найти заработную плату работников.

Для того, чтобы запустить функцию, находясь во вкладке «Данные», жмем на кнопку «Анализ «что если»», которая расположена в блоке инструментов «Работа с данными» на ленте. Появляется меню, в котором нужно выбрать пункт «Подбор параметра…».

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

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

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

В поле «Значение» требуется указать конкретное значение премии. В нашем случае, это будет 6035,68. В поле «Изменяя значения ячейки» вписываем ее адрес, содержащей исходные данные, которые нам нужно рассчитать, то есть сумму зарплаты работника. Это можно сделать теми же способами, о которых мы говорили выше: вбить координаты вручную, или кликнуть по соответствующей ячейке.

Когда все данные окна параметров заполнены, жмем на кнопку «OK».

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

Решение уравнений

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

Допустим, имеем уравнение: 15x+18x=46. Записываем его левую часть, как формулу, в одну из ячеек. Как и для любой формулы в Экселе, перед уравнением ставим знак «=». Но, при этом, вместо знака x устанавливаем адрес ячейки, куда будет выводиться результат искомого значения.

В нашем случае, формулу мы запишем в C2, а искомое значение будет выводиться в B2. Таким образом, запись в ячейке C2 будет иметь следующий вид: «=15*B2+18*B2».

Запускаем функцию тем же способом, как было описано выше, то есть, нажав на кнопку «Анализ «что если»» на ленте», и перейдя по пункту «Подбор параметра…».

В открывшемся окне подбора параметра, в поле «Установить в ячейке» указываем адрес, по которому мы записали уравнение (C2).

В поле «Значение» вписываем число 45, так как мы помним, что уравнение выглядит следующим образом: 15x+18x=46.

В поле «Изменяя значения ячейки» мы указываем адрес, куда будет выводиться значение x, то есть, собственно, решение уравнения (B2). После того, как мы ввели эти данные, жмем на кнопку «OK».

Как видим, программа Microsoft Excel успешно решила уравнение. Значение x будет равно 1,39 в периоде.

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

Подготовка таблицы

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

Коэффициент, который применяется для расчета суммы премии, нам предстоит вычислить с помощью функции Поиска решений. Ячейка, в которой он располагается, называется искомой.

Целевая и искомая ячейка должны быть связанны друг с другом с помощью формулы. В нашем конкретном случае, формула располагается в целевой ячейке, и имеет следующий вид: «=C10*$G$3», где $G$3 – абсолютный адрес искомой ячейки, а «C10» — общая сумма заработной платы, от которой производится расчет премии работникам предприятия.

Работа с гиперссылками и диаграммами в экселе

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

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

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

Чтобы добавить подписи осей в excel, используются значения столбцов. Для оси X применяются также заголовки столбцов или цифры из первого столбика. Числа в остальных столбцах – информация для создания подписи второй оси, Y. При построении графика используется информация в смежных и отдельных ячейках. Программа автоматически добавляет смежные ячейки таблицы. Чтобы использовать числа в отдельных ячейках, используется клавиша Ctrl. С ее помощью выбираются цифры или диапазоны.

После составления диаграммы переносим ее на лист, используя в новых редакторах вкладку Вставка. В области присутствует пункт Диаграммы. Во вкладке Charts в excel в 2013 находится пункт Рекомендуемые диаграммы. Здесь представлены разные типы графиков, в том числе объемные. Также доступен выбор других гистограмм. Пользователи могут сразу просмотреть все варианты, кликнув по кнопке See All Charts. Дополнительно в программе предусмотрена возможность объединения диаграмм.

В экселе до варианта 2010 года комбинированные графики составлялись с трудом. В excel в 2013, 2016 объединить информацию и представить ее на графике просто. Поскольку сразу во вкладке Диаграммы присутствует комбинированный вариант. следуя инструкциям, пользователь оперативно достигает цели.