3.2 Примеры решения задач, с использованием команд «подбор параметра»

Первым рассмотрим задачу характеризующую использования команды «подбор параметра». Рассмотрим процедуру поиска параметра на простом примере: решим уравнение 10 * x - 10 / x = 15. Здесь параметр (аргумент) - x. Пусть это будет ячейка A3. Введем в эту ячейку любое число, лежащее в области определения функции (в нашем примере это число не может быть равно нулю). Это значение будет использовано в качестве начального. Пусть это будет 3. Введем формулу =10*A3-10/A3, по которой должно быть получено требуемое значение, в какую-либо ячейку, например, B3. Теперь можно запустить функцию поиска параметра, выбрав команду Подбор параметра в меню Сервис. Введите параметры поиска (см рис 3.3).

Рисунок 3.3 Ввод параметров поиска для данной задачи

В поле Установить в ячейке введите ссылку на ячейку, содержащую необходимую формулу. Введите искомый результат в поле Значение. В поле Изменяя значение ячейке введите ссылку на ячейку, содержащую подбираемое значение. Кликните на клавише OK (см рис 3.4).

Рисунок 3.4 Результат подбора параметра

По окончании работы функции на экране появится окно, в котором будут отображены результаты поиска. Найденный параметр появится в ячейке, которая была для него зарезервирована. Обратите внимание на тот факт, что в нашем примере уравнение имеет два решения, а параметр подобран только один - это происходит потому, что параметр изменяется только до тех пор, пока требуемое значение не будет возвращено. Первый найденный таким образом аргумент и возвращается к нам в качестве результата поиска. Если в качестве начального значения в нашем примере указать -3, тогда будет найдено второе решение уравнения: -0,5.

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

Рисунок 3.5 Таблица премирования

Сделайте активной ячейку D17, воспользуемся командой меню Сервис - Подбор Параметра.

Рисунок 3.6 Ввод параметров для решения задачи

В поле ввода Значение введите с клавиатуры  сумму премиального фонда Установите курсор в поле ввода Изменяя значение ячейки и щелкните по ячейки B2 (см рис 3.6).

После нажатия кнопки OK отобразиться окно Результат подбора параметра, где отображаются ожидаемые результаты операции (см рис 3.7).

Рисунок 3.7 Результат подбора параметра

Если согласны с результат ответ (см рис 3.8).

Рисунок 3.8 Таблица премирования с учетом премиального фонда

ЗАКЛЮЧЕНИЕ

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

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

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

В курсовой работе мы рассмотрели разные примеры с использованием анализа «что-если», но так же с помощью этой функции можно решать и более сложные задачи такие как:

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

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

• Оптимизация инвестиционных вложений – необходимость грамотно распределить средства между несколькими банками, ценными бумагами или акциями предприятий с целью, опять же, максимизации прибыли или (если это более важно) минимизации рисков.