Лабораторная работа, РГР на тему Работа в EXCEL № 2.5

Заключение:

Получены навыки работы с надстройками программы EXCEL: «поиск решения» и «подбор параметров».

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

Таким образом они позволяют проверять решения с помощью приблизительного результата, что значительно облегчает работу пользователя.

Фрагмент текста работы:

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

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

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

Во время подбора параметра в переменную ячейку непрерывно заносятся новые значения, пока не будет найдено решение поставленной задачи. Такой процесс называется итерацией, и продолжается он до тех пор, пока Excel не выполнит 100 попыток или не найдет решение, лежащее в пределах точности 0.001 от точного значения.

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

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

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

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

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

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

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

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

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

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

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

Пример использования поиска решений

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

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

• нажмите «Параметры Excel», а затем выберите категорию «Надстройки»;

• в поле «Управление» выберите значение «Надстройки Excel» и нажмите кнопку «Перейти»;

• в поле «Доступные надстройки» установите флажок рядом с пунктом «Поиск решения» и нажмите кнопку ОК.

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

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

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

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