Инструмент анализа Подбор параметра

ЗАДАНИЕ. Создать книгу MS Excel. Сохранить файл с названием ПОДБОР_И_ПОИСК. В книге должно быть 8 листов по количеству задач. Приведите решения задач-примеров и решения задач для самостоятельного решения.

ПОДБОР ПАРАМЕТРА

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

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

Инструмент анализа Подбор параметра

1. Составить таблицу, отвечающую требованиям:

· ячейка, в которой должен быть получен желаемый результат (целевая ячейка), должна содержать формулу, а значение в ней должно быть наиболее близким к тому, которое требуется получить;

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

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

Задача 1.

На отпуск за год (учитывать только рабочие месяцы, т.е. 11) надо накопить определенную сумму денег (100000 р.). Определить величину ежемесячных вкладов на счет в банк под 10, 5% годовых.

1. В ячейке В1 – подбираемый параметр, установить в ячейке денежный формат.

2. В ячейках В2: В3 – исходные данные, в ячейке В3 установить процентный формат

3. В ячейке В4 – формула расчета ежемесячной выплаты: =БС(B3/11; B2; -B1)

установить в ячейке денежный формат.

4. Применить инструмент Подбор параметра. Для этого на ленте Данные в группе Работа с данными выбрать пиктограмму Анализ «что-если», а затем выбрать в списке пункт Подбор параметра .

5. В появившемся диалоговом окне задать параметры

в поле Установить в ячейке ввести ссылку на ячейку, содержащую необходимую формулу ($B$4); В поле Значение ввести искомый результат (100000); В поле Изменяя значение ячейки ввести ссылку на ячейку, значение которой нужно подобрать ($B$1).

6. Результат вычислений