Excel: решение задач с подбором параметров
Электронная таблица, так же как и обычная таблица, представляет собой набор числовых и текстовых данных, размещенных в ячейках. Данные, находящиеся в ячейке электронной таблицы, могут быть либо введены пользователем, либо определены (вычислены) по данным из других ячеек. На основе содержимого электронных таблиц могут создаваться диаграммы, служащие иллюстрацией числовой информации.
С помощью Ехсе1 можно создавать самые различные документы, выполнять самые различные задачи, например:
* составлять всевозможные списки, отчеты, ведомости, бланки;
* оперативно выполнять вычисления различной сложности;
* по данным таблиц строить динамически связанные с ними диаграммы, графики;
* решать сложные финансовые, экономические и математические задачи и, в том числе, задачи статистического анализа и т.д.
В данной работе представлено описание функции подбора параметра и решение задач с помощью этой функции .
Специальная функция подбор параметра позволяет определить параметр (аргумент) функции, если известно ее значение. При подборе параметра значение влияющей ячейки (параметра) изменяется до тех пор, пока формула, зависящая от этой ячейки, не возвратит заданное значение.
Например, средство «Подбор параметра» используется для изменения процентной ставки в ячейке B3 в сторону увеличения до тех пор, пока размер платежа в ячейке B4 не станет равен 900,00р. Рисунок 1
· В поле Установить в ячейке ввести ссылку на ячейку, содержащую необходимую формулу.
· В поле Изменяя значение ячейки ввести ссылку на ячейку, содержащую подбираемое значение.
Достаточно сложно правильно определить наиболее подходящее начальное значение. Чаще можно сделать какие-либо предположения об искомом параметре, например, параметр должен быть целым (тогда получаем первое решение нашего уравнения) или неположительным (второе решение).
Задачу поиска параметра при налагаемых граничных условиях поможет решить специальная надстройка Microsoft Excel Поиск решения.
2.1 Пример решения задач с использованием функции “подбор параметра”
Как известно, формулы в Microsoft Excel позволяют определить значение функции по ее аргументам. Однако может возникнуть ситуация, когда значение функции известно, а аргумент требуется найти (т.е. решить уравнение). Для решения подобных проблем предназначена специальная функция Подбор параметра. Рисунок 2
Если в качестве начального значения в данном примере указать -3, тогда будет найдено второе решение уравнения: -0,5.
Работа с обычными таблицами организована так: ввести данные, создать формулы, получить результат. Когда известен результат, который нужно получить с помощью вычислений по формулам, а исходное значения, необходимое для получения этого результата, неизвестно, следует использовать команду Подбор параметра, вместо метода проб и ошибок.
2. В ячейку В4 ввести текст Размер вклада, а в С4 его значение 150000р.
3. В ячейку В6 ввести текст Срок вклада, а в С6 его значение 20.
4. В ячейку В8 ввести текст Процентная ставка, а в С8 его значение 5%.
5. В ячейку В10 ввести текст Коэффициент наращения, а в С10 формулу его вычисления =(1+С8)^С6.
6. В ячейку В13 ввести текст Сумма выплат, а в С13 формулу его вычисления =С10*С4.
В результате получаем модель анализа суммы выплат по вкладу, с помощью которой можно установить, как влияют исходные значения на конечный результат. Рисунок 3
Используя Подбор параметра можно упростить процесс получения требуемого результата:
Нужно выделить ячейку C13, которая содержит формулу вычисления результата, и выбрать команду Подбор параметра меню Сервис.
В поле Значение нужно ввести целевое значение 500 000, а в поле Изменяя значение ячейки ссылку на ячейку С4 и нажать ОК.
Появится окно с результатами расчета, которые после нажатия кнопки ОК будут внесены в таблицу. Рисунок 4. Как видно для получения суммы выплат в 500 000 руб. при 5% годовых за 20 лет требуется положить 188445 руб. Результат выполнения задачи показан на рисунке 5
Раздел: Информатика, программирование
Количество знаков с пробелами: 15314
Количество таблиц: 0
Количество изображений: 13
Вопрос 51. Использование инструмента Подбор параметра в mso Excel.
Легенда – область, в которой расшифровываются цвета и узоры, соответствующие определенным данным в диаграмме. Для того, чтобы в легенде были осмысленные обозначения, необходимо выделять числовые данные вместе с заголовками строк и столбцов в таблице.
