На этом шаге мы рассмотрим особенности формирования заказа и использование Поиска решения Excel.
Расчет количества коробок с корпусами
На рисунке 1 видно, что в ячейках G12 по корпусам КА сформирован текст Уменьшите количество корпусов, а в ячейке G13 по корпусам КВ - Увеличьте количество корпусов.
Рис.1. Рабочий лист Расчет
Для формирования полного количества ящиков уменьшите количество изготовляемых кассет AKALB на 50 штук, а кассет AKBLA увеличьте на 100 штук.
Количество контейнеров с корпусами близко к пяти, поэтому принимается решение увеличения заказа корпусов кассет какого-либо типа до полной загрузки последнего контейнера. Пусть это будут кассеты AKCLB. Тогда увеличьте количество планируемого заказа корпусов на эти аудиокассеты до тех пор пока не исчезнет текст в ячейке G24 Контейнер не заполнен. Подбор количества с 400000 штук до 426800 можно осуществлять вручную, а можно при помощи инструмента Excel Подбор параметра.
Применение инструмента Excel Подбор параметра
Для вызова диалогового окна Подбор параметра (рисунок 2) выделите ячейку С24 и выполните команду Сервис | Подбор параметра.
Рис.2. Диалоговое окно Подбор параметра
В полях:
- Установить в ячейке - будет указана ячейка, которую активизировали перед выполнением команды;
- Значение - укажите значение подбираемого параметра. Для рассматриваемого примера это значение равно пяти;
- Изменяя значение ячейки - установив курсор в этом поле, укажите ячейку Е8;
- нажмите кнопку ОК или клавишу Enter.
Через некоторое время при условии выполнения Excel расчетов по подбору параметра на экране монитора появится диалоговое окно (рисунок 3).
Рис.3. Диалоговое окно Результат подбора параметров с сообщением, что решение найдено
Если найденное решение устраивает пользователя, то нажмите кнопку ОК. Если нет - кнопку Отмена.
Расчет количества коробок с магнитной лентой
По-прежнему в ячейке G25 присутствует текст Лишние коробки, что говорит о необходимости уменьшения количества заказываемой магнитной ленты. Для этого введите в диапазон ячеек F18:F20 количество коробок выбранного типа магнитной ленты, на которые будет уменьшен заказ. Например, принято решение уменьшить заказ равномерно на все типы магнитной ленты. К сожалению в данном случае функция Excel Подбор параметра не сможет оказать никакой помощи, потому что нужно подобрать значения одновременно в трех ячейках. Для того чтобы вручную с клавиатуры не подбирать число коробок с магнитной лентой, на которые нужно уменьшить заказ, используйте инструмент Excel Поиск решения.
Инструмент Excel Поиск решения
Громадное преимущество инструмента Подбор параметра - простота и скорость получения заданного результата, как при вводе исходных параметров, так и в вычислениях. К сожалению, эти достоинства не относятся к функции Excel Поиск решения, которая является одним из самых сложных средств Excel. Применение этого инструмента на практике требует определенных навыков, но возможно, что время потраченное на овладение этим инструментом окупит себя в будущем. Основные достоинства функции Поиск решения:
- возможность указывать несколько адресов изменяющих значения ячеек;
- наложение ограничений на значения изменяемых ячеек;
- возможность получения нескольких решений задачи.
Основные недостатки:
- алгоритмы поиска решения, заложенные в эту функцию не всегда позволяют получить достоверный результат при более сложных задачах;
- довольно скрупулезно нужно относиться к вводу ограничений на изменяемые параметры, в противном случае возможно получение недостоверного результата;
- требуется значительное время на ввод ограничений и вычисление для более сложных задач.
Однако для поиска решения для нашего приложения эта функция гармонично вписывается. Вызов диалогового окна Поиск решения (рисунок 4) осуществляется командой Сервис | Поиск решения.
Рис.4. Диалоговое окно Поиск решения с заданными параметрами вычислений
В поле Установить целевую ячейку укажите адрес ячейки С25. В области Равной активизируйте переключатель Значению и в это поле введите значение 1. В поле Изменяя ячейки укажите диапазон ячеек F18:F20. Следующий, самый основной этап, на котором остановимся - ввод ограничений.
Очень важно уяснить, что при недостаточности заданных условий, функция Поиск решения выберет для себя наиболее простой путь. Например, если не задать условие, что результаты поиска решения должны находиться в одной области значений (в нашем примере отрицательной), то решение будет быстро найдено за счет положительного значения в одной из ячеек. Но если задать ограничение (больше или меньше нуля) для всех подбираемых ячеек, то увеличится время выполнения поиска решения. Следовательно, количество задаваемых ограничений должно быть минимально при условии их достаточности.
Достаточностью в нашем примере может служить принятое управленческое решение: снизить заказываемое количество коробок с магнитной лентой равномерно для всех типов. Тогда ограничением может служить условие, что все подбираемые значения должны быть равны. Если задать условие равенства подбираемых значений для всех ячеек, то это не позволит подобрать значения с разными знаками для отдельных ячеек. Но в таком случае возможно получение подбираемых значений с дробной частью.
Следовательно, следующий этап ввода ограничений - получение только целочисленных значений подбираемых параметров. Но в таком случае, при равных значениях в подбираемых ячейках и задании им условия не иметь дробной части, присутствует возможность, при которой эта функция будет не в состоянии решить поставленную перед ней задачу. У одной из ячеек должна присутствовать возможность изменять свое значение на две коробки в меньшую или большую сторону от значений двух других ячеек. Достаточные условия ограничений показаны на рисунке 4.
Для ввода ограничений нажмите на кнопку Добавить, что вызовет появление диалогового окна Добавить ограничения (рисунок 5).
Рис.5. Диалоговое окно Добавить ограничения
Для поиска решения нажмите кнопку Выполнить в диалоговом окне Поиск решения и, если решение задачи найдено, то появится диалоговое окно Результаты поиска решения, с помощью которого можете сформировать отчеты, выбрав их в поле Тип отчета.
Рис.6. Диалоговое окно Результаты поиска решения
Один из сформированных отчетов по поиску решения приведен на рисунке 7.
Рис.7. Отчет по результатам, сформированных после выполнения поиска решения
Немаловажная деталь, на которую хотелось бы обратить внимание. Поиск решения является надстройкой Excel и при стандартной инсталляции Excel команда на вызов ее в меню Сервис будет отсутствовать. Для загрузки надстройки выполните команду Сервис | Надстройки и в диалоговом окне Надстройки и активизируйте опцию Поиск решения и нажмите на кнопку ОК.
На следующем шаге мы рассмотрим результаты формирования заказа.
